[Home] [Help]
PACKAGE BODY: APPS.CZ_POPULATORS_PKG
Source
1 PACKAGE BODY CZ_POPULATORS_PKG AS
2 /* $Header: czpopb.pls 120.10.12020000.2 2012/08/17 15:27:12 smanna ship $ */
3
4 ITEMS_POP CONSTANT INTEGER:=1;
5 ITEM_TYPES_POP CONSTANT INTEGER:=2;
6 PROPERTIES_POP CONSTANT INTEGER:=3;
7 PROPERTY_VALUES_POP CONSTANT INTEGER:=4;
8
9 BEGINSWITH_OPERATOR CONSTANT INTEGER:=300;
10 ENDSWITH_OPERATOR CONSTANT INTEGER:=301;
11 CONTAINS_OPERATOR CONSTANT INTEGER:=303;
12 MATCHES_OPERATOR CONSTANT INTEGER:=304;
13 OPERATOR_IN CONSTANT INTEGER:=502;
14
15 PS_FEATURE_TYPE CONSTANT INTEGER:=261;
16
17 NULL_VALUE CONSTANT INTEGER:=-1;
18 EPOCH_BEGIN CONSTANT DATE:=CZ_UTILS.EPOCH_BEGIN_;
19 EPOCH_END CONSTANT DATE:=CZ_UTILS.EPOCH_END_;
20
21 ISTYPEOF CONSTANT VARCHAR2(3):=315;
22 ISPROPERTYOF CONSTANT VARCHAR2(3):=305;
23 ISPROPERTYVAL CONSTANT VARCHAR2(3):=318;
24 OPERATOR_EQUAL CONSTANT VARCHAR2(50):='=';
25 OPERATOR_LIKE CONSTANT VARCHAR2(50):='like';
26 OPERATOR_SQL_IN CONSTANT VARCHAR2(50):='in';
27
28 USER_VIEW CONSTANT VARCHAR2(50):='*';
29
30 mRUN_ID INTEGER:=0;
31
32 mUSE_LOCKING VARCHAR(255) := '1';
33 mDB_SETTING_USE_SECURITY BOOLEAN := TRUE;
34 mALWAYS_REGENERATE VARCHAR2(255) := 'N';
35
36 mCOUNTER INTEGER:=0;
37
38 FAILED_TO_LOCK_MODEL EXCEPTION;
39
40 mPS_NODE_SEQUENCE VARCHAR2(255) := 'CZ_PS_NODES_S';
41 mINTL_TEXT_SEQUENCE VARCHAR2(255) := 'CZ_INTL_TEXTS_S';
42
43 mINCREMENT NUMBER := 20;
44
45 mNext_PS_Node_Id NUMBER;
46 mBase_PS_Node_Id NUMBER;
47
48 mNext_Text_Id NUMBER;
49 mBase_Text_Id NUMBER;
50
51 TYPE INTL_ID_DESC_TEXT_TYPE IS TABLE OF VARCHAR2(2000) INDEX BY VARCHAR2(15);
52 INTL_ID_DESC_TEXT_ARRAY INTL_ID_DESC_TEXT_TYPE;
53
54 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
55 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
56
57 PROCEDURE LOG_REPORT
58 (p_caller IN VARCHAR2,
59 p_error_message IN VARCHAR2) IS
60 PRAGMA AUTONOMOUS_TRANSACTION;
61 var_error BOOLEAN;
62 var_status INTEGER;
63 BEGIN
64 var_status:=11276;
65 INSERT INTO CZ_DB_LOGS
66 (RUN_ID,
67 LOGTIME,
68 LOGUSER,
69 URGENCY,
70 CALLER,
71 STATUSCODE,
72 MESSAGE)
73 VALUES (mRUN_ID,
74 SYSDATE,
75 USER,
76 1,
77 p_caller,
78 var_status,
79 p_error_message);
80 COMMIT;
81
82 END LOG_REPORT;
83
84 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
85 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
86
87 --
88 -- this method add log message to the CZ_DB_LOGS table
89 --
90 PROCEDURE SECURITY_REPORT
91 (p_run_id IN VARCHAR2,
92 p_count IN NUMBER DEFAULT NULL) IS
93 PRAGMA AUTONOMOUS_TRANSACTION;
94 BEGIN
95 IF (p_count>0) THEN
96 FOR i IN 1..p_count
97 LOOP
98 mCOUNTER:=mCOUNTER+1;
99 INSERT INTO CZ_DB_LOGS
100 (RUN_ID,
101 LOGTIME,
102 LOGUSER,
103 URGENCY,
104 CALLER,
105 STATUSCODE,
106 MESSAGE,
107 MESSAGE_ID)
108 VALUES
109 (p_run_id,
110 SYSDATE,
111 USER,
112 1,
113 'CZ_POPULATORS_PKG',
114 11276,
115 fnd_msg_pub.GET(i,fnd_api.g_false),
116 mCOUNTER);
117 COMMIT;
118 END LOOP;
119 END IF;
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 NULL;
124 END SECURITY_REPORT;
125
126 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
127 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
128
129 PROCEDURE Get_Seq_Increment IS
130 BEGIN
131 SELECT TO_NUMBER(value) INTO mINCREMENT FROM cz_db_settings
132 WHERE UPPER(setting_id)=UPPER('OracleSequenceIncr') AND section_name='SCHEMA';
133 END Get_Seq_Increment;
134
135 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
136 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
137
138 PROCEDURE Initialize_Sequence(p_seq_name IN VARCHAR2) IS
139 BEGIN
140
141 Get_Seq_Increment;
142
143 IF p_seq_name=mPS_NODE_SEQUENCE THEN
144 SELECT CZ_PS_NODES_S.NEXTVAL INTO mNext_PS_Node_Id FROM dual;
145 mBase_PS_Node_Id:=mNext_PS_Node_Id;
146 ELSIF p_seq_name=mINTL_TEXT_SEQUENCE THEN
147 SELECT CZ_INTL_TEXTS_S.NEXTVAL INTO mNext_Text_Id FROM dual;
148 mBase_Text_Id := mNext_Text_Id;
149 END IF;
150
151 END Initialize_Sequence;
152
153 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
154 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
155
156 FUNCTION get_Next_Seq_Id(p_seq_name IN VARCHAR2) RETURN NUMBER IS
157 BEGIN
158
159 IF p_seq_name=mPS_NODE_SEQUENCE THEN
160 IF (mNext_PS_Node_Id < mBase_PS_Node_Id+mINCREMENT-1) THEN
161 mNext_PS_Node_Id := mNext_PS_Node_Id + 1;
162 ELSE
163 SELECT CZ_PS_NODES_S.nextval INTO mBase_PS_Node_Id FROM dual;
164 mNext_PS_Node_Id:=mBase_PS_Node_Id;
165 END IF;
166 RETURN mNext_PS_Node_Id;
167 ELSIF p_seq_name=mINTL_TEXT_SEQUENCE THEN
168 IF (mNext_Text_Id < mBase_Text_Id+mINCREMENT-1) THEN
169 mNext_Text_Id := mNext_Text_Id + 1;
170 ELSE
171 SELECT CZ_INTL_TEXTS_S.nextval INTO mBase_Text_Id FROM dual;
172 mNext_Text_Id:=mBase_Text_Id;
173 END IF;
174 RETURN mNext_Text_Id;
175 END IF;
176 END get_Next_Seq_Id;
177
178 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
179 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
180
181 PROCEDURE lock_Model(p_model_id IN NUMBER,
182 p_locked_entities OUT NOCOPY cz_security_pvt.number_type_tbl) IS
183 PRAGMA AUTONOMOUS_TRANSACTION;
184 l_lock_status VARCHAR2(255);
185 l_msg_count NUMBER;
186 l_msg_index NUMBER;
187 l_msg_data VARCHAR2(4000);
188 BEGIN
189 cz_security_pvt.lock_model(1.0, p_model_id,FND_API.G_FALSE,FND_API.G_FALSE,
190 p_locked_entities,
191 l_lock_status,l_msg_count,l_msg_data);
192 IF (l_lock_status <> FND_API.G_RET_STS_SUCCESS) THEN
193 ROLLBACK;
194 l_msg_index := 1;
195 WHILE l_msg_count > 0
196 LOOP
197 l_msg_data := fnd_msg_pub.get(l_msg_index,fnd_api.g_false);
198 LOG_REPORT('CZ_POPULATORS_PKG.lock_Model',l_msg_data);
199 l_msg_index := l_msg_index + 1;
200 l_msg_count := l_msg_count - 1;
201 END LOOP;
202 FND_MSG_PUB.initialize;
203 RAISE FAILED_TO_LOCK_MODEL;
204 ELSE
205 COMMIT;
206 END IF;
207
208 END lock_Model;
209
210 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
211 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
212
213 PROCEDURE unlock_Model(p_model_id IN NUMBER,
214 p_locked_entities IN OUT NOCOPY cz_security_pvt.number_type_tbl) IS
215 PRAGMA AUTONOMOUS_TRANSACTION;
216 l_lock_status VARCHAR2(255);
217 l_msg_count NUMBER;
218 l_msg_data VARCHAR2(4000);
219 BEGIN
220 cz_security_pvt.unlock_model(1.0, FND_API.G_FALSE,
221 p_locked_entities,l_lock_status,
222 l_msg_count,l_msg_data);
223 COMMIT;
224 END unlock_Model;
225
226 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
227 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
228
229 FUNCTION get_Where(p_rule_id IN INTEGER) RETURN VARCHAR2 IS
230 TYPE t_arr IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
231 t_where t_arr;
232 var_data_value_tbl t_arr;
233 var_where VARCHAR2(4000);
234 var_field_name CZ_EXPRESSION_NODES.field_name%TYPE;
235 var_data_value CZ_EXPRESSION_NODES.data_value%TYPE;
236 var_filter VARCHAR2(4000);
237 var_operator VARCHAR2(50);
238 var_counter NUMBER:=0;
239 BEGIN
240 FOR i IN(SELECT expr_node_id,field_name,expr_subtype,seq_nbr FROM CZ_EXPRESSION_NODES
241 WHERE rule_id=p_rule_id AND (field_name IS NULL AND data_value IS NULL)
242 AND deleted_flag='0' ORDER BY seq_nbr)
243 LOOP
244 BEGIN
245 SELECT DECODE(field_name,'ref_part_nbr','item_master_name',field_name)
246 INTO var_field_name FROM CZ_EXPRESSION_NODES
247 WHERE expr_parent_id=i.expr_node_id AND field_name IS NOT NULL;
248 BEGIN
249 SELECT REPLACE(data_value,'''','''''') BULK COLLECT INTO var_data_value_tbl FROM CZ_EXPRESSION_NODES
250 WHERE expr_parent_id=i.expr_node_id AND data_value IS NOT NULL
251 AND deleted_flag='0';
252 IF var_data_value_tbl.COUNT>0 THEN
253 var_data_value := var_data_value_tbl(1);
254 END IF;
255 EXCEPTION
256 WHEN NO_DATA_FOUND THEN
257 IF i.expr_subtype=ENDSWITH_OPERATOR THEN
258 var_data_value:='';
259 END IF;
260 END;
261
262 IF i.expr_subtype=BEGINSWITH_OPERATOR THEN
263 var_filter:=var_data_value||'%';
264 var_operator:=OPERATOR_LIKE;
265 ELSIF i.expr_subtype=ENDSWITH_OPERATOR THEN
266 var_filter:='%'||var_data_value;
267 var_operator:=OPERATOR_LIKE;
268 ELSIF i.expr_subtype=CONTAINS_OPERATOR THEN
269 var_filter:='%'||var_data_value||'%';
270 var_operator:=OPERATOR_LIKE;
271 ELSIF i.expr_subtype=MATCHES_OPERATOR THEN
272 var_filter:=var_data_value;
273 var_operator:=OPERATOR_EQUAL;
274 ELSIF i.expr_subtype=ISTYPEOF THEN
275 var_filter:=var_data_value;
276 var_operator:=OPERATOR_EQUAL;
277 ELSIF i.expr_subtype IN(ISPROPERTYOF,ISPROPERTYVAL) THEN
278 var_filter:=var_data_value;
279 var_operator:=OPERATOR_EQUAL;
280 ELSIF i.expr_subtype=OPERATOR_IN THEN
281
282 FOR n IN var_data_value_tbl.First..var_data_value_tbl.Last
283 LOOP
284 IF var_filter IS NULL THEN
285 var_filter:=var_data_value_tbl(n);
286 ELSE
287 var_filter:=var_filter||','||var_data_value_tbl(n);
288 END IF;
289 END LOOP;
290
291 var_filter := '('||var_filter||')';
292 var_operator:=OPERATOR_SQL_IN;
293
294 END IF;
295 var_filter := UPPER(var_filter);
296 t_where(t_where.Count+1):=' UPPER('||var_field_name||') '||var_operator||' '''||var_filter||'''';
297 EXCEPTION
298 WHEN NO_DATA_FOUND THEN
299 NULL;
300 WHEN OTHERS THEN
301 NULL;
302 END;
303 END LOOP;
304
305 var_where:=t_where(1);
306 IF t_where.Count>1 THEN
307 FOR k IN 2..t_where.Last
308 LOOP
309 var_where:=var_where||' and '||t_where(k);
310 END LOOP;
311 END IF;
312
313 RETURN var_where;
314 EXCEPTION
315 WHEN OTHERS THEN
316 RETURN ' 0=1 ';
317 END;
318
319 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
320 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
321
322 -- create sql query or view based on data --
323 -- from CZ_FILTER_SETS,CZ_EXPRESSION_NODES --
324
325 PROCEDURE Regenerate_unsec
326 (p_populator_id IN INTEGER,
327 p_view_name IN OUT NOCOPY VARCHAR2,
328 p_sql_query IN OUT NOCOPY VARCHAR2,
329 p_err OUT NOCOPY INTEGER) IS
330 var_view_name CZ_POPULATORS.view_name%TYPE;
331 var_based_view CZ_POPULATORS.view_name%TYPE;
332 var_sql CZ_POPULATORS.query_syntax%TYPE;
333 var_filter_set_id CZ_FILTER_SETS.filter_set_id%TYPE;
334 var_source_type CZ_FILTER_SETS.source_type%TYPE;
335 var_rule_id CZ_FILTER_SETS.express_id%TYPE;
336 var_operator CZ_EXPRESSION_NODES.expr_subtype%TYPE;
337 var_data_value CZ_EXPRESSION_NODES.data_value%TYPE;
338 var_name CZ_PS_NODES.name%TYPE;
339 var_pop_name CZ_POPULATORS.name%TYPE;
340 var_property_id CZ_PROPERTIES.property_id%TYPE;
341 var_item_type_id CZ_ITEM_TYPES.item_type_id%TYPE;
342 var_item_id CZ_ITEM_MASTERS.item_id%TYPE;
343 var_desc_text CZ_ITEM_MASTERS.desc_text%TYPE;
344 var_primary_uom_code CZ_ITEM_MASTERS.primary_uom_code%TYPE;
345 var_quoteable_flag CZ_ITEM_MASTERS.quoteable_flag%TYPE;
346 var_ps_node_type CZ_PS_NODES.ps_node_type%TYPE;
347 var_model_id CZ_PS_NODES.devl_project_id%TYPE;
348 var_ps_node_id CZ_POPULATORS.owned_by_node_id%TYPE;
349 var_level INTEGER;
350 var_current VARCHAR2(1);
351 var_filter VARCHAR2(4000);
352 var_filter1 VARCHAR2(4000);
353 var_filter2 VARCHAR2(4000);
354 var_level_column VARCHAR2(20):='';
355 var_where VARCHAR2(4000);
356 there_is_no_seed_data BOOLEAN:=FALSE;
357
358 WRONG_SQL EXCEPTION;
359 DELETED_EXPRESSION EXCEPTION;
360
361 BEGIN
362
363 p_err:=0;
364
365 SELECT filter_set_id,view_name,result_type,var_ps_node_id,query_syntax,name
366 INTO var_filter_set_id,var_view_name,var_ps_node_type,var_ps_node_id,var_sql,var_pop_name
367 FROM CZ_POPULATORS
368 WHERE populator_id=p_populator_id;
369
370 BEGIN
371 SELECT a.source_type,a.rule_id,b.view_name
372 INTO var_source_type,var_rule_id,var_based_view
373 FROM CZ_FILTER_SETS a, CZ_POPULATORS b
374 WHERE a.filter_set_id=var_filter_set_id AND a.source_type=b.populator_id;
375 EXCEPTION
376 WHEN OTHERS THEN
377 there_is_no_seed_data:=TRUE;
378 END;
379
380 IF there_is_no_seed_data THEN
381 SELECT source_type,rule_id
382 INTO var_source_type,var_rule_id
383 FROM CZ_FILTER_SETS
384 WHERE filter_set_id=var_filter_set_id;
385 IF var_source_type=1 THEN
386 var_based_view:='CZ_ITEM_ITEM_POP_V';
387 ELSIF var_source_type=2 THEN
388 var_based_view:='CZ_ITEM_ITEMTYPE_POP_V';
389 ELSIF var_source_type=3 THEN
390 var_based_view:='CZ_ITEM_PROPERTY_POP_V';
391 ELSIF var_source_type=4 THEN
392 var_based_view:='CZ_ITEM_ITEMVAL_POP_V';
393 ELSE
394 var_based_view:=USER_VIEW;
395 END IF;
396 END IF;
397
398 --
399 -- create Populator sql query based on data from CZ_EXPRESSION_NODES table --
400 --
401 IF var_based_view<>USER_VIEW THEN
402 var_sql:='select * from '||var_based_view||' where '||get_Where(var_rule_id);
403 END IF;
404
405 UPDATE CZ_POPULATORS SET query_syntax=var_sql, last_generation_date=SYSDATE
406 WHERE populator_id=p_populator_id;
407
408 --
409 -- this approach is very slow --
410 -- but sometimes views can be useful for DEBUGing --
411 --
412 IF mCREATE_DEBUG_VIEWS IN('Y','YES') THEN
413 IF p_view_name IS NULL OR p_view_name='' THEN
414 p_view_name:='CZ_POP_'||TO_CHAR(p_populator_id)||'_V';
415 END IF;
416 EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW '||p_view_name||' AS '||var_sql;
417 UPDATE CZ_POPULATORS SET view_name=p_view_name WHERE populator_id=p_populator_id;
418 END IF;
419
420 p_sql_query:=var_sql;
421
422 EXCEPTION
423 WHEN DELETED_EXPRESSION THEN
424 p_err:=mRUN_ID;
425 LOG_REPORT('CZ_POPULATORS_PKG.Regenerate','Regenerate populator "'||var_pop_name||'" : definition was deleted.');
426 WHEN WRONG_SQL THEN
427 p_err:=mRUN_ID;
428 LOG_REPORT('CZ_POPULATORS_PKG.Regenerate','Regenerate populator "'||var_pop_name||'" -wrong SQL query is used : '||SQLERRM);
429 WHEN OTHERS THEN
430 p_err:=mRUN_ID;
431 LOG_REPORT('CZ_POPULATORS_PKG.Regenerate','Regenerate populator "'||var_pop_name||'" : '||SQLERRM);
432 END Regenerate_unsec;
433
434 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
435 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
436
437 PROCEDURE Regenerate
438 (p_populator_id IN INTEGER,
439 p_view_name IN OUT NOCOPY VARCHAR2,
440 p_sql_query IN OUT NOCOPY VARCHAR2,
441 p_err OUT NOCOPY INTEGER,
442 p_init_fnd_stack IN VARCHAR2 DEFAULT NULL) IS
443
444 l_model_id NUMBER;
445 l_locked_entities_tbl cz_security_pvt.number_type_tbl;
446 l_has_priveleges VARCHAR2(255);
447 l_msg_data VARCHAR2(32000);
448 l_lock_status VARCHAR2(255);
449 l_return_status VARCHAR2(255);
450 l_msg_count NUMBER;
451
452 BEGIN
453
454 p_err := 0;
455
456 IF UPPER(p_init_fnd_stack) IN('1','Y','YES') THEN
457 FND_MSG_PUB.initialize;
458 END IF;
459
460 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO mRUN_ID FROM dual;
461
462 --
463 -- check global flag that equals '1' if model is already locked
464 -- by calling sequirity package
465 --
466 IF mDB_SETTING_USE_SECURITY THEN
467
468 SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
469 WHERE ps_node_id IN(SELECT owned_by_node_id FROM CZ_POPULATORS
470 WHERE populator_id=p_populator_id) AND deleted_flag='0' AND rownum<2;
471
472 lock_Model(l_model_id, l_locked_entities_tbl);
473
474 END IF;
475
476 Regenerate_unsec(p_populator_id => p_populator_id,
477 p_view_name => p_view_name,
478 p_sql_query => p_sql_query,
479 p_err => p_err);
480
481 IF l_locked_entities_tbl.COUNT>0 AND mDB_SETTING_USE_SECURITY THEN
482
483 unlock_Model(l_model_id, l_locked_entities_tbl);
484
485 END IF;
486
487 EXCEPTION
488 WHEN FAILED_TO_LOCK_MODEL THEN
489 p_err := mRUN_ID;
490 END Regenerate;
491
492 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
493 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
494
495 PROCEDURE Preview_unsec
496 (p_populator_id IN INTEGER,
497 p_run_id OUT NOCOPY INTEGER,
498 p_err OUT NOCOPY INTEGER) IS
499 TYPE rec_cols IS RECORD (col_name VARCHAR2(255),col_num NUMBER);
500 TYPE t_rec_cols IS TABLE OF rec_cols INDEX BY BINARY_INTEGER;
501 TYPE NamesArray IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
502 t_names NamesArray;
503 t_col t_rec_cols;
504 var_pop_name CZ_POPULATORS.name%TYPE;
505 var_view_name CZ_POPULATORS.view_name%TYPE;
506 var_last_update CZ_POPULATORS.last_update_date%TYPE;
507 var_result_type CZ_POPULATORS.result_type%TYPE;
508 var_ps_node_type CZ_PS_NODES.ps_node_type%TYPE;
509 var_sql CZ_POPULATORS.query_syntax%TYPE;
510 var_has_level CZ_POPULATORS.has_level%TYPE;
511 var_has_item CZ_POPULATORS.has_item%TYPE;
512 var_has_item_type CZ_POPULATORS.has_item_type%TYPE;
513 var_has_property CZ_POPULATORS.has_property%TYPE;
514 var_filter_set_id CZ_FILTER_SETS.filter_set_id%TYPE;
515 var_express_id CZ_EXPRESSIONS.express_id%TYPE;
516 var_ps_node_id CZ_PS_NODES.ps_node_id%TYPE;
517 var_project_id CZ_PS_NODES.devl_project_id%TYPE;
518 var_intl_text_id CZ_PS_NODES.intl_text_id%TYPE;
519 var_new_text_id CZ_PS_NODES.intl_text_id%TYPE;
520 var_feature_type CZ_PS_NODES.feature_type%TYPE;
521 var_property_ptr CZ_PS_NODES.property_backptr%TYPE;
522 var_item_type_ptr CZ_PS_NODES.item_type_backptr%TYPE;
523 var_name CZ_PS_NODES.name%TYPE;
524 var_desc_text CZ_ITEM_TYPES.desc_text%TYPE;
525 var_property_id CZ_PROPERTIES.property_id%TYPE;
526 var_item_type_id CZ_ITEM_TYPES.item_type_id%TYPE;
527 var_item_id CZ_ITEM_MASTERS.item_id%TYPE;
528 var_primary_uom_code CZ_ITEM_MASTERS.primary_uom_code%TYPE;
529 var_quoteable_flag CZ_ITEM_MASTERS.quoteable_flag%TYPE;
530 var_tree_seq CZ_PS_NODES.tree_seq%TYPE;
531 var_node_name CZ_PS_NODES.name%TYPE;
532 var_key CZ_PS_NODES.user_str03%TYPE;
533 var_fk_key CZ_IMP_PS_NODES.FSK_PSNODE_3_EXT%TYPE;
534 var_instantiable_flag CZ_PS_NODES.instantiable_flag%TYPE;
535 var_minimum CZ_PS_NODES.minimum%TYPE;
536 var_maximum CZ_PS_NODES.maximum%TYPE;
537 var_text_str CZ_INTL_TEXTS.text_str%TYPE;
538 var_level NUMBER;
539 var_cursor NUMBER;
540 var_exec NUMBER;
541 var_ind NUMBER;
542 var_ps_node_key NUMBER;
543 var_parent_key NUMBER;
544 col_cnt INTEGER;
545 var_counter INTEGER;
546 rec_tab dbms_sql.desc_tab;
547 var_rec dbms_sql.desc_rec;
548 var_col_name VARCHAR2(255);
549 var_col_num NUMBER;
550 var_col_length NUMBER;
551 var_rule_id NUMBER;
552 var_curr_date DATE;
553 var_tree_flag BOOLEAN:=FALSE;
554 exists_in_t_names BOOLEAN;
555 ps_node_already_exists BOOLEAN;
556 var_level_column VARCHAR2(20):='';
557 rcode VARCHAR2(4000);
558 var_counted_options_flag VARCHAR2(1);
559 var_current_lang CZ_LOCALIZED_TEXTS.language%TYPE := USERENV('LANG');
560 SKIP_IT EXCEPTION;
561 WRONG_SQL EXCEPTION;
562 WRONG_COLUMN_TYPE EXCEPTION;
563
564 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
565 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
566 -- Bug6826702 : Returns true if the ps_node already exists in the model structure created out of the
567 -- same populator
568 --Bug8584377 commenting the below function.
569 /*FUNCTION check_for_ps_node_existence
570 (p_populator_id IN INTEGER,
571 p_parent_ps_node_id IN INTEGER,
572 p_property_ptr IN INTEGER,
573 p_item_type_ptr IN INTEGER,
574 p_item_id IN INTEGER,
575 p_ps_node_type IN INTEGER
576 ) RETURN BOOLEAN IS
577 rec_ps_node CZ_PS_NODES.ps_node_id%TYPE;
578 v_found BOOLEAN;
579 CURSOR cur_ps_node IS
580 SELECT ps_node_id
581 FROM cz_ps_nodes
582 WHERE parent_id=p_parent_ps_node_id
583 AND from_populator_id=p_populator_id
584 AND NVL(property_backptr,NULL_VALUE)=NVL(p_property_ptr,NULL_VALUE)
585 AND NVL(item_type_backptr,NULL_VALUE)=NVL(p_item_type_ptr,NULL_VALUE)
586 AND NVL(item_id,NULL_VALUE)=NVL(p_item_id,NULL_VALUE)
587 AND NVL(ps_node_type,NULL_VALUE)=NVL(p_ps_node_type,NULL_VALUE)
588 AND deleted_flag='0';
589 BEGIN
590 OPEN cur_ps_node;
591 FETCH cur_ps_node INTO rec_ps_node;
592 IF (cur_ps_node%FOUND) THEN
593 v_found := TRUE;
594 ELSE
595 v_found := FALSE;
596 END IF;
597 CLOSE cur_ps_node;
598 RETURN v_found;
599 END check_for_ps_node_existence;*/
600 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
601 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
602
603 BEGIN
604
605 p_err:=0;
606
607 Initialize_Sequence(mINTL_TEXT_SEQUENCE);
608
609 --
610 -- find various data for a given Populator --
611 --
612 SELECT filter_set_id,last_generation_date,view_name,owned_by_node_id,
613 result_type,query_syntax,has_level,feature_type,name
614 INTO var_filter_set_id,var_last_update,var_view_name,var_ps_node_id,
615 var_result_type,var_sql,var_has_level,var_feature_type,var_pop_name
616 FROM CZ_POPULATORS
617 WHERE populator_id=p_populator_id;
618
619 --
620 -- convert result_type to ps_node_type
621 -- * Developer sets result_type = signature_id
622 --
623 CZ_TYPES.get_Ps_Node_Type(p_signature_id => var_result_type,
624 x_ps_node_type => var_ps_node_type,
625 x_ps_node_subtype => var_feature_type);
626
627 --
628 -- find express_id which corresponds with Populator definition --
629 --
630 SELECT rule_id INTO var_rule_id FROM CZ_FILTER_SETS
631 WHERE filter_set_id=var_filter_set_id;
632
633 --
634 -- find Timestamp of Populator definition --
635 --
636 SELECT LAST_UPDATE_DATE INTO var_curr_date FROM CZ_EXPRESSION_NODES
637 WHERE rule_id=var_rule_id AND expr_parent_id IS NULL AND deleted_flag='0';
638
639 --
640 -- if definition has been changed then --
641 -- regenerate Populator --
642 --
643
644 -- the condition is commented out temporary
645 -- because of the problem in Developer with
646 -- properly setting of cz_expression_nodes.last_update_date
647 --
648 IF var_last_update<=var_curr_date OR var_last_update IS NULL OR mALWAYS_REGENERATE IN('1','Y') THEN
649 Regenerate_unsec(p_populator_id => p_populator_id,
650 p_view_name => var_view_name,
651 p_sql_query => var_sql,
652 p_err => p_err);
653 END IF;
654
655 --
656 -- find devl_project_id of populator's owner --
657 --
658 SELECT devl_project_id INTO var_project_id FROM CZ_PS_NODES
659 WHERE ps_node_id=var_ps_node_id;
660
661 --
662 -- Allocate run_id for CZ_IMP_PS_NODES
663 --
664 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO p_run_id FROM dual;
665
666 --
667 -- by defualt feature has a type "List of Options" --
668 --
669 IF var_ps_node_type=PS_FEATURE_TYPE AND var_feature_type IS NULL THEN
670 var_feature_type:='0';
671 END IF;
672
673 IF var_ps_node_type=PS_FEATURE_TYPE AND var_feature_type='0' THEN
674 var_counted_options_flag := '0';
675 END IF;
676
677 var_sql:='('||var_sql||')';
678 IF mCREATE_DEBUG_VIEWS IN('1','Y','YES') THEN
679 var_sql:=var_view_name;
680 END IF;
681
682 BEGIN
683 var_cursor := DBMS_SQL.OPEN_CURSOR;
684 DBMS_SQL.PARSE(var_cursor, 'SELECT * FROM '||var_sql||' ORDER BY name', dbms_sql.native);
685 var_exec := DBMS_SQL.EXECUTE(var_cursor);
686 DBMS_SQL.DESCRIBE_COLUMNS(var_cursor, col_cnt, rec_tab);
687 EXCEPTION
688 WHEN OTHERS THEN
689 rcode:=SQLERRM;
690 RAISE WRONG_SQL;
691 END;
692
693 var_col_num := rec_tab.first;
694 IF (var_col_num is not null) THEN
695 LOOP
696 var_col_name:=LOWER(rec_tab(var_col_num).col_name);
697 var_col_length:=rec_tab(var_col_num).col_max_len;
698 t_col(var_col_num).col_name:=var_col_name;
699 t_col(var_col_num).col_num:=var_col_num;
700
701 IF var_col_name='property_id' THEN
702 DBMS_SQL.DEFINE_COLUMN(var_cursor,var_col_num,var_property_ptr);
703 END IF;
704 IF var_col_name='name' THEN
705 DBMS_SQL.DEFINE_COLUMN(var_cursor,var_col_num,var_name,var_col_length);
706 END IF;
707 IF var_col_name='item_type_id' THEN
708 DBMS_SQL.DEFINE_COLUMN(var_cursor,var_col_num,var_item_type_ptr);
709 END IF;
710 IF var_col_name='item_id' THEN
711 DBMS_SQL.DEFINE_COLUMN(var_cursor,var_col_num,var_item_id);
712 END IF;
713 IF var_col_name='desc_text' THEN
714 DBMS_SQL.DEFINE_COLUMN(var_cursor,var_col_num,var_desc_text,var_col_length);
715 END IF;
716 IF var_col_name='primary_uom_code' THEN
717 DBMS_SQL.DEFINE_COLUMN(var_cursor,var_col_num,var_primary_uom_code,var_col_length);
718 END IF;
719 IF var_col_name='quoteable_flag' THEN
720 DBMS_SQL.DEFINE_COLUMN(var_cursor,var_col_num,var_quoteable_flag,var_col_length);
721 END IF;
722 var_col_num := rec_tab.next(var_col_num);
723 EXIT WHEN (var_col_num is null);
724 END LOOP;
725 END IF;
726
727 var_tree_seq := 0;
728
729 LOOP
730 BEGIN
731 IF DBMS_SQL.FETCH_ROWS(var_cursor)=0 THEN
732 EXIT;
733 ELSE
734 IF t_col.Count>0 THEN
735 FOR i IN t_col.First..t_col.Last
736 LOOP
737 BEGIN
738 var_col_name:=t_col(i).col_name;
739 var_col_num:=t_col(i).col_num;
740 rcode:=var_col_name;
741 IF var_col_name='property_id' THEN
742 DBMS_SQL.COLUMN_VALUE(var_cursor,var_col_num,var_property_ptr);
743 END IF;
744 IF var_col_name='name' THEN
745 DBMS_SQL.COLUMN_VALUE(var_cursor,var_col_num,var_name);
746 END IF;
747 IF var_col_name='item_type_id' THEN
748 DBMS_SQL.COLUMN_VALUE(var_cursor,var_col_num,var_item_type_ptr);
749 END IF;
750 IF var_col_name='item_id' THEN
751 DBMS_SQL.COLUMN_VALUE(var_cursor,var_col_num,var_item_id);
752 END IF;
753 IF var_col_name='desc_text' THEN
754 DBMS_SQL.COLUMN_VALUE(var_cursor,var_col_num,var_desc_text);
755 END IF;
756 IF var_col_name='primary_uom_code' THEN
757 DBMS_SQL.COLUMN_VALUE(var_cursor,var_col_num,var_primary_uom_code);
758 END IF;
759 IF var_col_name='quoteable_flag' THEN
760 DBMS_SQL.COLUMN_VALUE(var_cursor,var_col_num,var_quoteable_flag);
761 END IF;
762 EXCEPTION
763 WHEN OTHERS THEN
764 RAISE WRONG_COLUMN_TYPE;
765 END;
766 END LOOP;
767 END IF;
768
769 --Bug6826702 : Moved code piece down
770
771 BEGIN
772 SELECT intl_text_id INTO var_new_text_id
773 FROM CZ_PS_NODES
774 WHERE parent_id=var_ps_node_id AND
775 FROM_POPULATOR_ID=p_populator_id AND
776 NVL(PROPERTY_BACKPTR,NULL_VALUE)=NVL(var_property_ptr,NULL_VALUE) AND
777 NVL(ITEM_TYPE_BACKPTR,NULL_VALUE)=NVL(var_item_type_ptr,NULL_VALUE) AND
778 NVL(ITEM_ID,NULL_VALUE)=NVL(var_item_id,NULL_VALUE) AND
779 NVL(PS_NODE_TYPE,NULL_VALUE)=NVL(var_ps_node_type,NULL_VALUE) AND deleted_flag='0';
780
781 ps_node_already_exists := TRUE; --Bug8584377
782
783 SELECT text_str INTO var_text_str FROM CZ_INTL_TEXTS
784 WHERE intl_text_id=var_new_text_id;
785
786 IF NVL(var_text_str,'0') <> NVL(var_desc_text,'0') THEN
787
788 --
789 -- set localized_str to var_desc_text ( = new for current language = var_current_lang )
790 -- and set source_lang to current language
791 -- for those localized texts wfor which language <> current language and
792 -- source language <> current language
793 --
794 UPDATE CZ_LOCALIZED_TEXTS
795 SET localized_str=var_desc_text,
796 source_lang=var_current_lang
797 WHERE intl_text_id=var_new_text_id;
798
799 END IF;
800
801 EXCEPTION
802 WHEN NO_DATA_FOUND THEN
803 ps_node_already_exists := FALSE; --Bug8584377
804 --Bug8584377 Moved the insert into cz_intl_texts query to Execute_unsec procedure.
805 END;
806
807 var_key:=TO_CHAR(NVL(var_property_ptr,NULL_VALUE))||':'||
808 TO_CHAR(NVL(var_item_type_ptr,NULL_VALUE))||':'||
809 TO_CHAR(NVL(var_item_id,NULL_VALUE))||':'||
810 TO_CHAR(NVL(var_ps_node_type,NULL_VALUE));
811
812 /*
813 IF var_has_item_type='0' AND var_item_type_ptr IS NOT NULL THEN
814 var_has_item_type:='1';
815 END IF;
816
817 IF var_has_item='0' AND var_item_id IS NOT NULL THEN
818 var_has_item:='1';
819 END IF;
820
821 IF var_has_property='0' AND var_property_ptr IS NOT NULL THEN
822 var_has_property:='1';
823 END IF;
824 */
825 IF var_ps_node_type IN (258,259,263) THEN
826 var_instantiable_flag := 2;
827 var_minimum := 1;
828 var_maximum := 1;
829 ELSIF var_ps_node_type = 264 THEN
830 var_instantiable_flag := 3;
831 var_minimum := 1;
832 var_maximum := 1;
833 ELSIF var_ps_node_type = 261 AND var_feature_type <> '0' THEN
834 var_instantiable_flag := NULL;
835 var_minimum := NULL;
836 var_maximum := NULL;
837 ELSE
838 var_instantiable_flag := NULL;
839 var_minimum := 1;
840 var_maximum := 1;
841 END IF;
842
843 --Bug6826702 : Code change to avert the deletion of an existing eligible ps_node, during repopulation
844 --vsingava 08th Sep 2008
845 --Bug8584377 commenting the below call,as this is achieved even without the check_for_ps_node_existence function call.
846 --ps_node_already_exists := check_for_ps_node_existence(p_populator_id ,var_ps_node_id, var_property_ptr, var_item_type_ptr,
847 -- var_item_id, var_ps_node_type);
848
849 exists_in_t_names := FALSE;
850 IF t_names.Count>0 THEN
851 FOR h IN t_names.First..t_names.Last
852 LOOP
853 IF t_names(h)=var_name THEN
854 exists_in_t_names := TRUE;
855 END IF;
856 END LOOP;
857 END IF;
858
859 IF ps_node_already_exists AND exists_in_t_names THEN
860
861 -- Need to update the previous entry
862 --LOG_REPORT('CZ_POPULATORS_PKG.preview','Updating previous entry');
863 UPDATE CZ_IMP_PS_NODES
864 SET PROPERTY_BACKPTR = var_property_ptr,ITEM_TYPE_BACKPTR = var_item_type_ptr,INTL_TEXT_ID = var_new_text_id,
865 ITEM_ID = var_item_id,USER_STR03 = var_key,FSK_PSNODE_3_EXT = var_fk_key,
866 PRIMARY_UOM_CODE = var_primary_uom_code,
867 QUOTEABLE_FLAG = var_quoteable_flag,
868 INSTANTIABLE_FLAG = var_instantiable_flag,
869 COUNTED_OPTIONS_FLAG = var_counted_options_flag
870 WHERE RUN_ID = p_run_id AND DEVL_PROJECT_ID = var_project_id AND
871 PARENT_ID = var_ps_node_id AND NAME = var_name;
872 -- Implies we encountered the new node later to old node or another new node
873 ELSE
874 IF(NOT ps_node_already_exists) AND exists_in_t_names THEN-- Implies we encountered the new node later to old node or another new node
875 --LOG_REPORT('CZ_POPULATORS_PKG.preview','Skipping entry :'||var_name);
876 RAISE SKIP_IT;
877 ELSE
878 -- We reach here, when import_table_existence_flag is alone FALSE. May be inserting the new one or the old one
879 -- Need to INSERT
880 t_names(t_names.Count+1):=var_name;
881 var_tree_seq:=var_tree_seq+1;
882 --LOG_REPORT('CZ_POPULATORS_PKG.preview','Inserting entry :'||var_name);
883
884 --Bug8584377
885 IF (NOT ps_node_already_exists) THEN
886 var_new_text_id := get_Next_Seq_Id(mINTL_TEXT_SEQUENCE);
887 INTL_ID_DESC_TEXT_ARRAY(var_new_text_id) := var_desc_text;
888 END IF;
889 INSERT INTO CZ_IMP_PS_NODES
890 (RUN_ID,
891 PS_NODE_ID,
892 PARENT_ID,
893 DEVL_PROJECT_ID,
894 NAME,
895 FROM_POPULATOR_ID,
896 PROPERTY_BACKPTR,
897 ITEM_TYPE_BACKPTR,
898 INTL_TEXT_ID,
899 SUB_CONS_ID,
900 ITEM_ID,
901 MINIMUM,
902 MAXIMUM,
903 PS_NODE_TYPE,
904 FEATURE_TYPE,
905 PRODUCT_FLAG,
906 ORDER_SEQ_FLAG,
907 SYSTEM_NODE_FLAG,
908 TREE_SEQ,
909 UI_OMIT,
910 SO_ITEM_TYPE_CODE,
911 EFFECTIVE_USAGE_MASK,
912 EFFECTIVE_FROM,
913 EFFECTIVE_UNTIL,
914 UI_SECTION,
915 DELETED_FLAG,
916 USER_STR03,
917 DECIMAL_QTY_FLAG,
918 FSK_PSNODE_3_EXT,
919 PRIMARY_UOM_CODE,
920 QUOTEABLE_FLAG,
921 MULTI_CONFIG_FLAG,
922 INSTANTIABLE_FLAG,
923 COUNTED_OPTIONS_FLAG)
924 VALUES
925 (p_run_id,
926 0,
927 var_ps_node_id,
928 var_project_id,
929 var_name,
930 p_populator_id,
931 var_property_ptr,
932 var_item_type_ptr,
933 var_new_text_id,
934 NULL,
935 var_item_id,
936 var_minimum,
937 var_maximum,
938 var_ps_node_type,
939 var_feature_type,
940 '0',
941 '0',
942 '0',
943 var_tree_seq,
944 '0',
945 NULL,
946 '0000000000000000',
947 EPOCH_BEGIN,
948 EPOCH_END,
949 '0',
950 '0',
951 var_key,
952 '0',
953 var_fk_key,
954 var_primary_uom_code,
955 var_quoteable_flag,
956 '1',
957 var_instantiable_flag,
958 var_counted_options_flag);
959 END IF;
960 END IF;
961 END IF;
962 EXCEPTION
963 WHEN SKIP_IT THEN
964 NULL;
965 END;
966 END LOOP;
967 DBMS_SQL.CLOSE_CURSOR(var_cursor);
968
969 --
970 -- set descriptive fields --
971 --
972 --UPDATE CZ_POPULATORS SET has_item_type=var_has_item_type,
973 -- has_item=var_has_item,
974 -- has_property=var_has_property
975 --WHERE populator_id=p_populator_id;
976
977 EXCEPTION
978 WHEN WRONG_COLUMN_TYPE THEN
979 IF DBMS_SQL.IS_OPEN(var_cursor) THEN
980 DBMS_SQL.CLOSE_CURSOR(var_cursor);
981 END IF;
982 p_err:=mRUN_ID;
983 LOG_REPORT('CZ_POPULATORS_PKG.Preview','Preview populator "'||var_pop_name||'" - wrong type of column '||rcode);
984 WHEN WRONG_SQL THEN
985 IF DBMS_SQL.IS_OPEN(var_cursor) THEN
986 DBMS_SQL.CLOSE_CURSOR(var_cursor);
987 END IF;
988 p_err:=mRUN_ID;
989 LOG_REPORT('CZ_POPULATORS_PKG.Preview','Preview populator "'||var_pop_name||'" - wrong SQL query is used : '||rcode);
990 WHEN OTHERS THEN
991 IF DBMS_SQL.IS_OPEN(var_cursor) THEN
992 DBMS_SQL.CLOSE_CURSOR(var_cursor);
993 END IF;
994 p_err:=mRUN_ID;
995 LOG_REPORT('CZ_POPULATORS_PKG.Preview','Preview populator "'||var_pop_name||'" failed :'||SQLERRM);
996 END Preview_unsec;
997
998 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
999 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1000
1001 PROCEDURE Preview
1002 (p_populator_id IN INTEGER,
1003 p_run_id OUT NOCOPY INTEGER,
1004 p_err OUT NOCOPY INTEGER,
1005 p_init_fnd_stack IN VARCHAR2 DEFAULT NULL) IS
1006
1007 l_model_id NUMBER;
1008 l_locked_entities_tbl cz_security_pvt.number_type_tbl;
1009 l_has_priveleges VARCHAR2(255);
1010 l_msg_data VARCHAR2(32000);
1011 l_lock_status VARCHAR2(255);
1012 l_return_status VARCHAR2(255);
1013 l_msg_count NUMBER;
1014
1015 BEGIN
1016
1017 p_err := 0;
1018
1019 IF UPPER(p_init_fnd_stack) IN('1','Y','YES') THEN
1020 FND_MSG_PUB.initialize;
1021 END IF;
1022
1023 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO mRUN_ID FROM dual;
1024
1025 --
1026 -- check global flag that equals '1' if model is already locked
1027 -- by calling sequirity package
1028 --
1029 IF mDB_SETTING_USE_SECURITY THEN
1030
1031 SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
1032 WHERE ps_node_id IN(SELECT owned_by_node_id FROM CZ_POPULATORS
1033 WHERE populator_id=p_populator_id) AND deleted_flag='0' AND rownum<2;
1034
1035 lock_Model(l_model_id, l_locked_entities_tbl);
1036
1037 END IF;
1038
1039 Preview_unsec(p_populator_id => p_populator_id,
1040 p_run_id => p_run_id,
1041 p_err => p_err);
1042
1043
1044 IF l_locked_entities_tbl.COUNT>0 AND mDB_SETTING_USE_SECURITY THEN
1045 unlock_Model(l_model_id, l_locked_entities_tbl);
1046 END IF;
1047
1048 EXCEPTION
1049 WHEN FAILED_TO_LOCK_MODEL THEN
1050 p_err := mRUN_ID;
1051 END Preview;
1052
1053 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1054 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1055
1056 --
1057 -- populate PS Tree :
1058 -- insert/update data from CZ_IMP_PS_NODES into CZ_PS_NODES
1059 --
1060 PROCEDURE Execute_unsec
1061 (p_populator_id IN INTEGER,
1062 p_run_id IN OUT NOCOPY INTEGER,
1063 p_err OUT NOCOPY INTEGER) IS
1064
1065 TYPE IntArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1066 t_ps_node_id IntArray;
1067 t_devl_project IntArray;
1068 t_intl_text_id IntArray;
1069 var_inserts INTEGER;
1070 var_updates INTEGER;
1071 var_failed INTEGER;
1072 var_dups INTEGER;
1073 var_nochange INTEGER;/*Meant for nochange in disposition*/
1074 var_tree_seq INTEGER;
1075 var_name_counter INTEGER;
1076 var_pop_name CZ_POPULATORS.name%TYPE;
1077 var_name CZ_PS_NODES.name%TYPE;
1078 var_view CZ_POPULATORS.view_name%TYPE;
1079 var_sql CZ_POPULATORS.query_syntax%TYPE;
1080 var_devl_project_id CZ_PS_NODES.devl_project_id%TYPE;
1081 var_parent_id CZ_PS_NODES.ps_node_id%TYPE;
1082 var_component_id CZ_PS_NODES.component_id%TYPE;
1083 var_new_ps_id CZ_PS_NODES.ps_node_id%TYPE;
1084 STOP_IT EXCEPTION;
1085
1086 BEGIN
1087
1088 p_err:=0;
1089
1090 Initialize_Sequence(mPS_NODE_SEQUENCE);
1091
1092 --
1093 -- if p_run_id is empty then we need Preview first --
1094 --
1095 IF p_run_id IS NULL OR p_run_id=0 THEN
1096 IF mUSE_IMPORT IN('1','Y','YES') THEN
1097 Regenerate_unsec(p_populator_id,var_view,var_sql,p_err);
1098 END IF;
1099 Preview_unsec(p_populator_id,p_run_id,p_err);
1100 END IF;
1101
1102 --
1103 -- find owner of Populator --
1104 --
1105 SELECT owned_by_node_id,name INTO var_parent_id,var_pop_name
1106 FROM CZ_POPULATORS WHERE populator_id=p_populator_id;
1107
1108 SELECT component_id INTO var_component_id FROM CZ_PS_NODES
1109 WHERE ps_node_id=var_parent_id;
1110
1111 IF mUSE_IMPORT IN('1','Y','YES') THEN
1112 --
1113 -- delete those PS Tree nodes which don't satisfy --
1114 -- a Populator condition anymore --
1115 --
1116 UPDATE CZ_PS_NODES SET deleted_flag='1'
1117 WHERE parent_id=var_parent_id
1118 AND deleted_flag='0' AND
1119 USER_STR03 NOT IN
1120 (SELECT USER_STR03
1121 FROM CZ_IMP_PS_NODES WHERE run_id=p_run_id) AND FROM_POPULATOR_ID=p_populator_id
1122 RETURNING devl_project_id,ps_node_id,intl_text_id
1123 BULK COLLECT INTO t_devl_project,t_ps_node_id,t_intl_text_id;
1124
1125 --
1126 -- delete an associated non-virtual components --
1127 --
1128 IF t_ps_node_id.Count>0 THEN
1129 FORALL i IN t_ps_node_id.First..t_ps_node_id.Last
1130 UPDATE CZ_MODEL_REF_EXPLS SET deleted_flag='1'
1131 WHERE model_id=t_devl_project(i) AND
1132 model_ref_expl_id
1133 IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
1134 WHERE deleted_flag='0'
1135 START WITH component_id=t_ps_node_id(i)
1136 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id);
1137
1138 FORALL i IN t_intl_text_id.First..t_intl_text_id.Last
1139 UPDATE CZ_LOCALIZED_TEXTS
1140 SET deleted_flag='1'
1141 WHERE intl_text_id=t_intl_text_id(i);
1142 END IF;
1143
1144
1145 CZ_IMP_PS_NODE.KRS_PS_NODE(p_run_id,
1146 100,
1147 1000,
1148 var_inserts,
1149 var_updates,
1150 var_failed,
1151 var_dups,
1152 var_nochange,
1153 mXFR_PROJECT_GROUP);
1154 CZ_IMP_PS_NODE.XFR_PS_NODE(p_run_id,
1155 100,
1156 1000,
1157 var_inserts,
1158 var_updates,
1159 var_failed,
1160 var_nochange,
1161 mXFR_PROJECT_GROUP);
1162
1163 IF var_failed>0 THEN
1164 p_err:=mRUN_ID;
1165 LOG_REPORT('CZ_POPULATORS_PKG.Execute','Populator failed ...');
1166 END IF;
1167 RAISE STOP_IT;
1168 END IF;
1169
1170 --
1171 -- delete those PS Tree nodes which don't satisfy --
1172 -- a Populator condition anymore --
1173 --
1174 UPDATE CZ_PS_NODES SET deleted_flag='1'
1175 WHERE parent_id=var_parent_id
1176 AND deleted_flag='0' AND
1177 (NVL(PROPERTY_BACKPTR,NULL_VALUE),
1178 NVL(ITEM_TYPE_BACKPTR,NULL_VALUE),NVL(ITEM_ID,NULL_VALUE),
1179 NVL(PS_NODE_TYPE,NULL_VALUE))
1180 NOT IN
1181 (SELECT NVL(PROPERTY_BACKPTR,NULL_VALUE),
1182 NVL(ITEM_TYPE_BACKPTR,NULL_VALUE),NVL(ITEM_ID,NULL_VALUE),
1183 NVL(PS_NODE_TYPE,NULL_VALUE)
1184 FROM CZ_IMP_PS_NODES WHERE run_id=p_run_id) AND FROM_POPULATOR_ID=p_populator_id
1185 RETURNING devl_project_id,ps_node_id,intl_text_id
1186 BULK COLLECT INTO t_devl_project,t_ps_node_id,t_intl_text_id;
1187
1188 --
1189 -- delete an associated non-virtual components --
1190 --
1191 IF t_ps_node_id.Count>0 THEN
1192 FORALL i IN t_ps_node_id.First..t_ps_node_id.Last
1193 UPDATE CZ_MODEL_REF_EXPLS SET deleted_flag='1'
1194 WHERE model_id=t_devl_project(i) AND
1195 model_ref_expl_id
1196 IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
1197 WHERE deleted_flag='0'
1198 START WITH component_id=t_ps_node_id(i)
1199 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id);
1200
1201 FORALL i IN t_intl_text_id.First..t_intl_text_id.Last
1202 UPDATE CZ_LOCALIZED_TEXTS
1203 SET deleted_flag='1'
1204 WHERE intl_text_id=t_intl_text_id(i);
1205
1206 END IF;
1207
1208 --
1209 -- find MAX tree_seq under ps_node_id of owner of Populator --
1210 --
1211 SELECT NVL(MAX(tree_seq),0) INTO var_tree_seq FROM CZ_PS_NODES
1212 WHERE parent_id=var_parent_id AND deleted_flag='0';
1213
1214 --
1215 -- general loop : --
1216 -- update PS Tree Node if there is a correlated node in CZ_PS_NODES --
1217 -- otherwise just insert from CZ_IMP_PS_NODES into CZ_PS_NODES --
1218 -- the following CZ_PS_NODES columns are used for checking : --
1219 -- FROM_POPULATOR_ID --
1220 -- PROPERTY_BACKPTR --
1221 -- ITEM_TYPE_BACKPTR --
1222 -- ITEM_ID --
1223 -- PS_NODE_TYPE --
1224 --
1225 FOR i IN(SELECT INTL_TEXT_ID,DEVL_PROJECT_ID,FROM_POPULATOR_ID,PROPERTY_BACKPTR,
1226 ITEM_TYPE_BACKPTR,ITEM_ID,PS_NODE_TYPE,FEATURE_TYPE,QUOTEABLE_FLAG,NAME
1227 FROM CZ_IMP_PS_NODES WHERE run_id=p_run_id)
1228 LOOP
1229
1230 var_name := i.name;
1231
1232 SELECT COUNT(*) INTO var_name_counter FROM CZ_PS_NODES
1233 WHERE parent_id=var_parent_id AND deleted_flag='0' AND
1234 (name=i.name OR name like 'Copy (%) of '||i.name);
1235
1236 IF var_name_counter>1 THEN
1237 var_name := 'Copy ('||TO_CHAR(var_name_counter+1)||') of '||i.name;
1238 END IF;
1239
1240 UPDATE CZ_PS_NODES
1241 SET name=var_name,intl_text_id=i.intl_text_id,
1242 feature_type=i.feature_type,quoteable_flag=i.quoteable_flag
1243 WHERE parent_id=var_parent_id AND
1244 FROM_POPULATOR_ID=p_populator_id AND
1245 NVL(PROPERTY_BACKPTR,NULL_VALUE)=NVL(i.PROPERTY_BACKPTR,NULL_VALUE) AND
1246 NVL(ITEM_TYPE_BACKPTR,NULL_VALUE)=NVL(i.ITEM_TYPE_BACKPTR,NULL_VALUE) AND
1247 NVL(ITEM_ID,NULL_VALUE)=NVL(i.ITEM_ID,NULL_VALUE) AND
1248 NVL(PS_NODE_TYPE,NULL_VALUE)=NVL(i.PS_NODE_TYPE,NULL_VALUE) AND deleted_flag='0';
1249
1250 IF SQL%ROWCOUNT=0 THEN
1251 IF var_name_counter>0 THEN
1252 var_name := 'Copy ('||TO_CHAR(var_name_counter)||') of '||i.name;
1253 END IF;
1254
1255 var_new_ps_id := get_Next_Seq_Id(mPS_NODE_SEQUENCE);
1256 var_tree_seq := var_tree_seq + 1;
1257 INSERT INTO CZ_PS_NODES
1258 (PS_NODE_ID,
1259 PARENT_ID,
1260 DEVL_PROJECT_ID,
1261 NAME,
1262 FROM_POPULATOR_ID,
1263 PROPERTY_BACKPTR,
1264 ITEM_TYPE_BACKPTR,
1265 INTL_TEXT_ID,
1266 SUB_CONS_ID,
1267 ITEM_ID,
1268 MINIMUM,
1269 MAXIMUM,
1270 PS_NODE_TYPE,
1271 FEATURE_TYPE,
1272 PRODUCT_FLAG,
1273 ORDER_SEQ_FLAG,
1274 SYSTEM_NODE_FLAG,
1275 TREE_SEQ,
1276 UI_OMIT,
1277 SO_ITEM_TYPE_CODE,
1278 EFFECTIVE_USAGE_MASK,
1279 EFFECTIVE_FROM,
1280 EFFECTIVE_UNTIL,
1281 UI_SECTION,
1282 DELETED_FLAG,
1283 USER_STR03,
1284 DECIMAL_QTY_FLAG,
1285 PRIMARY_UOM_CODE,
1286 QUOTEABLE_FLAG,
1287 MULTI_CONFIG_FLAG,
1288 VIRTUAL_FLAG,
1289 PERSISTENT_NODE_ID,
1290 INSTANTIABLE_FLAG,
1291 COMPONENT_ID,
1292 COUNTED_OPTIONS_FLAG)
1293 SELECT
1294 var_new_ps_id,
1295 PARENT_ID,
1296 DEVL_PROJECT_ID,
1297 var_name,
1298 p_populator_id,
1299 PROPERTY_BACKPTR,
1300 ITEM_TYPE_BACKPTR,
1301 INTL_TEXT_ID,
1302 SUB_CONS_ID,
1303 ITEM_ID,
1304 MINIMUM,
1305 MAXIMUM,
1306 PS_NODE_TYPE,
1307 FEATURE_TYPE,
1308 PRODUCT_FLAG,
1309 ORDER_SEQ_FLAG,
1310 SYSTEM_NODE_FLAG,
1311 var_tree_seq,
1312 UI_OMIT,
1313 SO_ITEM_TYPE_CODE,
1314 EFFECTIVE_USAGE_MASK,
1315 EFFECTIVE_FROM,
1316 EFFECTIVE_UNTIL,
1317 UI_SECTION,
1318 DELETED_FLAG,
1319 USER_STR03,
1320 DECIMAL_QTY_FLAG,
1321 PRIMARY_UOM_CODE,
1322 QUOTEABLE_FLAG,
1323 MULTI_CONFIG_FLAG,
1324 '1',
1325 var_new_ps_id,
1326 INSTANTIABLE_FLAG,
1327 var_component_id,
1328 COUNTED_OPTIONS_FLAG
1329 FROM CZ_IMP_PS_NODES
1330 WHERE run_id=p_run_id AND
1331 NVL(FROM_POPULATOR_ID,NULL_VALUE)=NVL(i.FROM_POPULATOR_ID,NULL_VALUE) AND
1332 NVL(PROPERTY_BACKPTR,NULL_VALUE)=NVL(i.PROPERTY_BACKPTR,NULL_VALUE) AND
1333 NVL(ITEM_TYPE_BACKPTR,NULL_VALUE)=NVL(i.ITEM_TYPE_BACKPTR,NULL_VALUE) AND
1334 NVL(ITEM_ID,NULL_VALUE)=NVL(i.ITEM_ID,NULL_VALUE) AND
1335 NVL(PS_NODE_TYPE,NULL_VALUE)=NVL(i.PS_NODE_TYPE,NULL_VALUE);
1336 --Bug8584377 Moved the insert code from Preview_unsec procedure.
1337 INSERT INTO CZ_INTL_TEXTS(
1338 INTL_TEXT_ID,
1339 TEXT_STR,
1340 MODEL_ID,
1341 UI_DEF_ID,
1342 DELETED_FLAG)
1343 VALUES(
1344 i.INTL_TEXT_ID,
1345 INTL_ID_DESC_TEXT_ARRAY(i.INTL_TEXT_ID),
1346 i.DEVL_PROJECT_ID,
1347 NULL,
1348 '0');
1349 END IF;
1350 END LOOP;
1351
1352 EXCEPTION
1353 WHEN STOP_IT THEN
1354 NULL;
1355 WHEN OTHERS THEN
1356 p_err:=mRUN_ID;
1357 LOG_REPORT('CZ_POPULATORS_PKG.Execute','Execute populator "'||var_pop_name||'" failed : '||SQLERRM);
1358 END Execute_unsec;
1359
1360 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1361 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1362
1363 PROCEDURE Execute
1364 (p_populator_id IN INTEGER,
1365 p_run_id IN OUT NOCOPY INTEGER,
1366 p_err OUT NOCOPY INTEGER,
1367 p_init_fnd_stack IN VARCHAR2 DEFAULT NULL) IS
1368
1369 l_model_id NUMBER;
1370 l_locked_entities_tbl cz_security_pvt.number_type_tbl;
1371 l_has_priveleges VARCHAR2(255);
1372 l_msg_data VARCHAR2(32000);
1373 l_lock_status VARCHAR2(255);
1374 l_return_status VARCHAR2(255);
1375 l_msg_count NUMBER;
1376
1377 BEGIN
1378
1379 p_err := 0;
1380
1381 IF UPPER(p_init_fnd_stack) IN('1','Y','YES') THEN
1382 FND_MSG_PUB.initialize;
1383 END IF;
1384
1385 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO mRUN_ID FROM dual;
1386
1387 --
1388 -- check global flag that equals '1' if model is already locked
1389 -- by calling sequirity package
1390 --
1391 IF mDB_SETTING_USE_SECURITY THEN
1392
1393 SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
1394 WHERE ps_node_id IN(SELECT owned_by_node_id FROM CZ_POPULATORS
1395 WHERE populator_id=p_populator_id) AND deleted_flag='0' AND rownum<2;
1396
1397 lock_Model(l_model_id, l_locked_entities_tbl);
1398
1399 END IF;
1400
1401 Execute_unsec(p_populator_id => p_populator_id,
1402 p_run_id => p_run_id,
1403 p_err => p_err);
1404
1405 IF l_locked_entities_tbl.COUNT>0 AND mDB_SETTING_USE_SECURITY THEN
1406 unlock_Model(l_model_id, l_locked_entities_tbl);
1407 END IF;
1408
1409 EXCEPTION
1410 WHEN FAILED_TO_LOCK_MODEL THEN
1411 p_err := mRUN_ID;
1412 END Execute;
1413
1414 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1415 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1416
1417 --
1418 -- repopulate all Populators for a given Model --
1419 --
1420 PROCEDURE Repopulate_unsec
1421 (p_model_id IN INTEGER,
1422 p_regenerate_all IN VARCHAR2,
1423 p_handle_invalid IN VARCHAR2,
1424 p_handle_broken IN VARCHAR2,
1425 p_err OUT NOCOPY INTEGER) IS
1426 TYPE IntArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1427 t_errors IntArray;
1428 var_model_id INTEGER;
1429 var_run_id INTEGER;
1430 var_err INTEGER;
1431 var_err_id INTEGER;
1432 var_counter INTEGER:=0;
1433 var_view VARCHAR2(100);
1434 var_sql CZ_POPULATORS.query_syntax%TYPE;
1435 SKIP_IT EXCEPTION;
1436 MODEL_DOES_NOT_EXIST EXCEPTION;
1437 BEGIN
1438
1439 p_err:=0;
1440
1441 --
1442 -- this is an additional checking for using within CZ_MODEL_OPERATIONS
1443 --
1444 BEGIN
1445 SELECT devl_project_id INTO var_model_id
1446 FROM CZ_DEVL_PROJECTS WHERE devl_project_id=p_model_id AND deleted_flag='0';
1447 EXCEPTION
1448 WHEN NO_DATA_FOUND THEN
1449 RAISE MODEL_DOES_NOT_EXIST;
1450 END;
1451
1452 FOR i IN(SELECT populator_id FROM CZ_POPULATORS a,CZ_PS_NODES b
1453 WHERE a.owned_by_node_id=b.ps_node_id AND b.devl_project_id=p_model_id AND
1454 a.deleted_flag='0' AND b.deleted_flag='0')
1455 LOOP
1456 var_counter:=var_counter+1;
1457 BEGIN
1458 IF p_regenerate_all='1' THEN
1459 Regenerate_unsec(i.populator_id,var_view,var_sql,var_err);
1460 IF var_err>0 THEN
1461 t_errors(t_errors.Count+1):=var_err;
1462 END IF;
1463 IF p_handle_broken='0' THEN
1464 RAISE SKIP_IT;
1465 END IF;
1466 IF var_err>0 AND p_handle_invalid='0' THEN
1467 RAISE SKIP_IT;
1468 END IF;
1469 END IF;
1470
1471 Preview_unsec(i.populator_id,var_run_id,var_err);
1472
1473 IF var_err>0 AND (p_handle_invalid='0' OR p_handle_broken='0') THEN
1474 RAISE SKIP_IT;
1475 END IF;
1476
1477 Execute_unsec(i.populator_id,var_run_id,var_err);
1478 IF var_err>0 THEN
1479 t_errors(t_errors.Count+1):=var_err;
1480 END IF;
1481
1482 EXCEPTION
1483 WHEN SKIP_IT THEN
1484 NULL;
1485 END;
1486 END LOOP;
1487
1488 --
1489 -- just one CZ_DB_LOGS.run_id should be used by Developer
1490 --
1491 IF t_errors.Count>0 THEN
1492 FORALL i IN 1..t_errors.Count
1493 UPDATE CZ_DB_LOGS SET run_id=mRUN_ID
1494 WHERE run_id=t_errors(i);
1495 p_err:=var_err_id;
1496 END IF;
1497
1498 EXCEPTION
1499 WHEN MODEL_DOES_NOT_EXIST THEN
1500 p_err:=mRUN_ID;
1501 LOG_REPORT('CZ_POPULATORS_PKG.Repopulate',
1502 'Model with model_id='||TO_CHAR(p_model_id)||' does not exist.');
1503 END Repopulate_unsec;
1504
1505 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1506 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1507
1508 PROCEDURE Repopulate
1509 (p_model_id IN INTEGER,
1510 p_regenerate_all IN VARCHAR2,
1511 p_handle_invalid IN VARCHAR2,
1512 p_handle_broken IN VARCHAR2,
1513 p_err OUT NOCOPY INTEGER,
1514 p_init_fnd_stack IN VARCHAR2 DEFAULT NULL) IS
1515
1516 l_locked_entities_tbl cz_security_pvt.number_type_tbl;
1517 l_has_priveleges VARCHAR2(255);
1518 l_msg_data VARCHAR2(32000);
1519 l_lock_status VARCHAR2(255);
1520 l_return_status VARCHAR2(255);
1521 l_msg_count NUMBER;
1522
1523 BEGIN
1524
1525 p_err := 0;
1526
1527 IF UPPER(p_init_fnd_stack) IN('1','Y','YES') THEN
1528 FND_MSG_PUB.initialize;
1529 END IF;
1530
1531 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO mRUN_ID FROM dual;
1532
1533 --
1534 -- check global flag that equals '1' if model is already locked
1535 -- by calling sequirity package
1536 --
1537 IF mDB_SETTING_USE_SECURITY THEN
1538 lock_Model(p_model_id, l_locked_entities_tbl);
1539 END IF;
1540
1541 Repopulate_unsec(p_model_id => p_model_id,
1542 p_regenerate_all => p_regenerate_all,
1543 p_handle_invalid => p_handle_invalid,
1544 p_handle_broken => p_handle_broken,
1545 p_err => p_err);
1546
1547 IF l_locked_entities_tbl.COUNT>0 AND mDB_SETTING_USE_SECURITY THEN
1548 unlock_Model(p_model_id, l_locked_entities_tbl);
1549 END IF;
1550
1551 EXCEPTION
1552 WHEN FAILED_TO_LOCK_MODEL THEN
1553 p_err := mRUN_ID;
1554 END Repopulate;
1555
1556 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1557 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1558
1559 BEGIN
1560 BEGIN
1561 SELECT UPPER(value) INTO mCREATE_DEBUG_VIEWS FROM CZ_DB_SETTINGS
1562 WHERE UPPER(setting_id)='CREATEPOPVIEWS';
1563 EXCEPTION
1564 WHEN OTHERS THEN
1565 mCREATE_DEBUG_VIEWS:='N';
1566 END;
1567 BEGIN
1568 SELECT UPPER(value) INTO mUSE_IMPORT FROM CZ_DB_SETTINGS
1569 WHERE UPPER(setting_id)='USEIMPORT';
1570 EXCEPTION
1571 WHEN OTHERS THEN
1572 mUSE_IMPORT:='N';
1573 END;
1574
1575 BEGIN
1576 SELECT value INTO mUSE_LOCKING FROM CZ_DB_SETTINGS
1577 WHERE setting_id = 'USE_LOCKING' AND rownum<2;
1578
1579 IF UPPER(mUSE_LOCKING) IN('0','N','NO') THEN
1580 mDB_SETTING_USE_SECURITY := FALSE;
1581 END IF;
1582 EXCEPTION
1583 WHEN OTHERS THEN
1584 mUSE_LOCKING := '1';
1585 mDB_SETTING_USE_SECURITY := TRUE;
1586 END;
1587
1588 BEGIN
1589 SELECT UPPER(value) INTO mALWAYS_REGENERATE FROM CZ_DB_SETTINGS
1590 WHERE UPPER(setting_id)='ALWAYS_REGENERATE_POPULATORS';
1591 EXCEPTION
1592 WHEN OTHERS THEN
1593 mALWAYS_REGENERATE := 'N';
1594 END;
1595
1596
1597 END CZ_POPULATORS_PKG;