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