[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FOR c_devl IN (SELECT devl_project_id, orig_sys_ref FROM cz_devl_projects p
WHERE deleted_flag = flag_not_deleted
AND devl_project_id <> 0
AND orig_sys_ref is not NULL
AND EXISTS (SELECT NULL FROM cz_rp_entries
WHERE object_id = p.devl_project_id
AND object_type = 'PRJ'
AND deleted_flag = flag_not_deleted)
AND NOT EXISTS (SELECT NULL FROM cz_xfr_project_bills
WHERE model_ps_node_id = p.devl_project_id )
)
LOOP
INSERT INTO cz_xfr_project_bills (model_ps_node_id,
organization_id,
top_item_id,
explosion_type,
deleted_flag,
source_server,
last_import_date)
SELECT c_devl.devl_project_id,
substr(c_devl.orig_sys_ref, instr(c_devl.orig_sys_ref, ':') + 1, instr(c_devl.orig_sys_ref, ':', 1, 2) - instr(c_devl.orig_sys_ref, ':') - 1),
substr(c_devl.orig_sys_ref, instr(c_devl.orig_sys_ref, ':', -1, 1) + 1),
substr(c_devl.orig_sys_ref, 1, instr(c_devl.orig_sys_ref, ':', 1) - 1),
'0',
0,
sysdate
FROM dual;
SELECT publication_id
FROM cz_model_publications
WHERE cz_model_publications.export_status = 'PEN'
AND cz_model_publications.product_key like '%:%';
SELECT ui_def_id, ui_style
FROM cz_ui_defs
WHERE cz_ui_defs.devl_project_id = v_models
AND deleted_flag = '0'
order by LAST_UPDATE_DATE desc;
SELECT distinct application_id, application_short_name
FROM cz_ext_applications_v
WHERE preferred_ui_style = v_preferred_ui_style;
SELECT UPPER(language_code) INTO x_base_lang
FROM fnd_languages
WHERE fnd_languages.installed_flag IN ('B');
PROCEDURE insert_publication(p_ui_def_id NUMBER,
p_ui_style VARCHAR2,
p_models cz_devl_projects.devl_project_id%TYPE) AS
v_application_id cz_ext_applications_v.application_id%TYPE;
INSERT INTO cz_model_publications(
PUBLICATION_ID,
MODEL_ID,
SERVER_ID,
PRODUCT_KEY,
organization_id,
top_item_id,
PUBLICATION_MODE,
ui_def_id,
UI_STYLE,
APPLICABLE_FROM,
APPLICABLE_UNTIL,
EXPORT_STATUS,
DELETED_FLAG,
SOURCE_TARGET_FLAG,
REMOTE_PUBLICATION_ID
)
VALUES ( cz_model_publications_s.NEXTVAL,
p_models,
p_server_id,
v_product_key,
v_org_id,
v_top_item_id,
'P',
p_ui_def_id,
p_ui_style,
sysdate,
CZ_UTILS.EPOCH_END,
'PEN',
'0',
'S',
null
);
INSERT INTO cz_pb_client_apps( PUBLICATION_ID,
FND_APPLICATION_ID,
APPLICATION_SHORT_NAME,
NOTES
)
VALUES (cz_model_publications_s.CURRVAL,
v_application_id,
v_application_short_name,
NULL
);
INSERT INTO cz_pb_languages (publication_id,language)
values (cz_model_publications_s.CURRVAL,v_base_language);
INSERT INTO cz_publication_usages ( PUBLICATION_ID,
USAGE_ID
)
VALUES (cz_model_publications_s.CURRVAL,
-1
);
v_models_to_be_published.DELETE;
SELECT devl_project_id, orig_sys_ref, last_import_date
BULK
COLLECT
INTO v_models_to_be_published,
v_orig_sys_ref,
v_last_import_date
FROM cz_devl_projects, cz_rp_entries, cz_xfr_project_bills
WHERE cz_devl_projects.ORIG_SYS_REF IS NOT NULL
AND cz_devl_projects.deleted_flag = '0'
AND cz_devl_projects.devl_project_id = cz_rp_entries.object_id
AND cz_rp_entries.object_type = 'PRJ' and cz_rp_entries.deleted_flag = '0'
AND cz_devl_projects.devl_project_id = cz_xfr_project_bills.model_ps_node_id(+)
ORDER BY orig_sys_ref, last_import_date DESC;
select substr(v_orig_sys_ref(i), instr(v_orig_sys_ref(i), ':')+1) into
v_product_key from dual;
select substr(v_product_key, instr(v_product_key, ':')+1) into
v_top_item_id from dual;
select substr(v_product_key, 1, instr(v_product_key, ':')-1) into
v_org_id from dual;
insert_publication(v_applet_ui_def_id, APPLET, v_models_to_be_published(i));
insert_publication(v_dhtml_ui_def_id, APPLET, v_models_to_be_published(i));
update cz_model_publications set ui_style = DHTML where ui_def_id
= v_dhtml_ui_def_id;
insert_publication(v_dhtml_ui_def_id, DHTML, v_models_to_be_published(i));
INSERT INTO cz_model_publications(
PUBLICATION_ID
,MODEL_ID
,SERVER_ID
,PRODUCT_KEY
,organization_id
,top_item_id
,PUBLICATION_MODE
,ui_def_id
,UI_STYLE
,APPLICABLE_FROM
,APPLICABLE_UNTIL
,EXPORT_STATUS
,DELETED_FLAG
,SOURCE_TARGET_FLAG
,REMOTE_PUBLICATION_ID
)
VALUES ( cz_model_publications_s.NEXTVAL,
v_models_to_be_published(i),
p_server_id,
v_product_key,
v_org_id,
v_top_item_id,
'P',
v_ui_def_id,
APPLET,
sysdate,
CZ_UTILS.EPOCH_END,
'PEN',
'0',
'S',
null
);
SELECT application_id
INTO v_application_id
FROM fnd_application
WHERE application_short_name = 'ONT';
INSERT INTO cz_pb_client_apps( PUBLICATION_ID
,FND_APPLICATION_ID
,APPLICATION_SHORT_NAME
,NOTES
)
VALUES ( cz_model_publications_s.CURRVAL,
v_application_id,
'ONT',
null
);
INSERT INTO cz_publication_usages ( PUBLICATION_ID
,USAGE_ID
)
VALUES (cz_model_publications_s.CURRVAL,
-1
);
INSERT INTO cz_pb_languages (publication_id,language)
values (cz_model_publications_s.CURRVAL,v_base_language);
-- insert cz_xfr_project_bills records for references
generate_xfr_reference_bills;
SELECT LPAD(TO_CHAR(p_number),p_sort_width,'0') INTO ret FROM dual;
FOR i IN (SELECT ps_node_id,parent_id,tree_seq,ps_node_type,
component_sequence_id,component_sequence_path FROM CZ_PS_NODES
WHERE parent_id=p_ps_node_id AND ps_node_type IN(263,436,437,438)
AND deleted_flag='0')
LOOP
IF p_string2='' OR p_string2 IS NULL THEN
var_token:='';
UPDATE CZ_PS_NODES SET bom_sort_order=var_string1
WHERE ps_node_id=i.ps_node_id AND bom_sort_order is NULL;
UPDATE CZ_PS_NODES SET component_sequence_path=var_string2
WHERE ps_node_id=i.ps_node_id AND component_sequence_path is NULL;
UPDATE CZ_PS_NODES SET component_sequence_path=NULL,bom_sort_order=var_bom_sort
WHERE ps_node_id = p_model_id;
SELECT cz_model_ref_expls_s.NEXTVAL INTO base_expl_id FROM dual;
SELECT cz_ps_nodes_s.NEXTVAL INTO base_node_id FROM dual;
UPDATE cz_expression_nodes SET consequent_flag = flag_not_consequent;
UPDATE cz_expression_nodes SET consequent_flag = flag_is_consequent
WHERE expr_node_id IN
(SELECT child1.expr_node_id
FROM cz_rules rule, cz_expression_nodes parent, cz_expression_nodes child1,
cz_expression_nodes child2
WHERE
--Parent is not deleted and is operator dot
parent.deleted_flag = flag_not_deleted
AND parent.expr_type = expr_node_type_operator
AND parent.expr_subtype = operator_dot
--Rule is not deleted or disabled
AND rule.deleted_flag = flag_not_deleted
AND rule.disabled_flag = flag_not_disabled
--Both children are not deleted and are children of the parent
AND child1.deleted_flag = flag_not_deleted
AND child2.deleted_flag = flag_not_deleted
AND child1.expr_parent_id = parent.expr_node_id
AND child2.expr_parent_id = parent.expr_node_id
--Parent is the consequent expression for the rule
AND rule.consequent_id = parent.express_id
--One child is a node expression node
AND child1.expr_type = expr_node_type_node
--Another child is system property, min or max
AND child2.expr_type = expr_node_type_sysprop
AND child2.expr_subtype IN (sys_prop_min, sys_prop_max)
);
UPDATE cz_ps_nodes SET virtual_flag = flag_virtual
WHERE ps_node_type IN (ps_node_type_product, ps_node_type_component, ps_node_type_bom_model);
UPDATE cz_ps_nodes SET virtual_flag = flag_non_virtual WHERE ps_node_id IN (
SELECT structure.ps_node_id
FROM cz_ps_nodes structure, cz_ps_nodes parent
WHERE structure.ps_node_type IN (ps_node_type_product, ps_node_type_component, ps_node_type_bom_model)
AND parent.ps_node_id = structure.parent_id
AND (structure.ps_node_type IN (ps_node_type_product, ps_node_type_component) OR parent.ps_node_type <> ps_node_type_product)
AND structure.deleted_flag = flag_not_deleted
AND (structure.ps_node_type = ps_node_type_bom_model OR (
(structure.minimum <> 1 OR structure.maximum <> 1 OR EXISTS
--Expressions are joined to bring in project
(SELECT NULL
FROM cz_expressions expr, cz_expression_nodes node
WHERE expr.devl_project_id = structure.devl_project_id
AND node.ps_node_id = structure.ps_node_id
AND expr.deleted_flag = flag_not_deleted
AND node.deleted_flag = flag_not_deleted
--Consequent flag '1' guarantees existence of a rule
AND node.consequent_flag = flag_is_consequent
)))));
FOR c_devl IN (SELECT devl_project_id, orig_sys_ref FROM cz_devl_projects p
WHERE deleted_flag = flag_not_deleted
AND devl_project_id <> 0
AND EXISTS (
SELECT NULL FROM cz_ps_nodes
WHERE deleted_flag = flag_not_deleted
AND ps_node_type = ps_node_type_product
AND devl_project_id = p.devl_project_id)
AND NOT EXISTS
(SELECT NULL FROM cz_model_ref_expls
WHERE model_id = p.devl_project_id)
) LOOP
IF(c_devl.orig_sys_ref IS NOT NULL)THEN
UPDATE cz_devl_projects SET
orig_sys_ref =
(SELECT nvl(substr(parent.orig_sys_ref,instr(parent.orig_sys_ref,':',-1,3)+1),
substr(child.orig_sys_ref,instr(child.orig_sys_ref,':',-1,3)+1))
FROM cz_ps_nodes parent, cz_ps_nodes child
WHERE parent.ps_node_id = c_devl.devl_project_id
AND parent.ps_node_type = 258
AND child.ps_node_type IN (258, 436)
AND parent.ps_node_id = child.parent_id
AND ROWNUM = 1)
WHERE devl_project_id = c_devl.devl_project_id;
UPDATE cz_ps_nodes SET
orig_sys_ref =
(SELECT orig_sys_ref FROM cz_devl_projects WHERE devl_project_id = c_devl.devl_project_id)
WHERE ps_node_id = c_devl.devl_project_id
returning orig_sys_ref INTO v_origsysref;
INSERT INTO cz_xfr_project_bills (model_ps_node_id,
organization_id,
top_item_id,
explosion_type,
deleted_flag,
source_server,
last_import_date) -- fix for bug # 2406244
SELECT c_devl.devl_project_id,
substr(v_origsysref, instr(v_origsysref, ':') + 1, instr(v_origsysref, ':', 1, 2) - instr(v_origsysref, ':') - 1),
substr(v_origsysref, instr(v_origsysref, ':', -1, 1) + 1),
substr(v_origsysref, 1, instr(v_origsysref, ':') - 1),
'0',
0,
sysdate -- fix for bug # 2406244
FROM dual
WHERE NOT EXISTS (SELECT NULL FROM cz_xfr_project_bills
WHERE model_ps_node_id = c_devl.devl_project_id);
TYPE tminimumsel IS TABLE OF cz_ps_nodes.minimum_selected%TYPE INDEX BY BINARY_INTEGER;
TYPE tmaximumsel IS TABLE OF cz_ps_nodes.maximum_selected%TYPE INDEX BY BINARY_INTEGER;
SELECT cz_model_ref_expls_s.NEXTVAL INTO base_expl_id FROM dual;
SELECT ps_node_type, virtual_flag INTO npsnodetype, svirtualflag
FROM cz_ps_nodes WHERE ps_node_id = incomponentid;
INSERT INTO cz_model_ref_expls
(model_ref_expl_id, parent_expl_node_id, component_id, model_id,
node_depth, virtual_flag, deleted_flag, ps_node_type)
SELECT
current_expl_id,
decode(npsnodetype, ps_node_type_bom_model, NULL, /*PS_NODE_TYPE_PRODUCT, NULL,*/ inparentexplid),
incomponentid,
localmodelid,
decode(npsnodetype, ps_node_type_bom_model, 0, /*PS_NODE_TYPE_PRODUCT, 0,*/ inlogicnetlevel),
decode(inlogicnetlevel, 0, flag_virtual, svirtualflag),
flag_not_deleted, npsnodetype
FROM dual WHERE NOT EXISTS
(SELECT NULL FROM cz_model_ref_expls WHERE model_id = localmodelid AND component_id = incomponentid);
FOR expl IN (SELECT model_ref_expl_id, node_depth, model_id FROM cz_model_ref_expls
WHERE component_id = inparentcomponentid) LOOP
BEGIN
local_expl_id := next_expl_id;
SELECT cz_model_ref_expls_s.NEXTVAL INTO base_expl_id FROM dual;
INSERT INTO cz_model_ref_expls
(model_ref_expl_id, parent_expl_node_id, node_depth, ps_node_type, virtual_flag,
component_id, model_id, referring_node_id, child_model_expl_id, deleted_flag)
SELECT
local_expl_id, expl.model_ref_expl_id, expl.node_depth + 1, ps_node_type_reference,
flag_virtual, incomponentid, expl.model_id, inreferringnodeid, current_expl_id,
flag_not_deleted
FROM dual WHERE NOT EXISTS
(SELECT NULL FROM cz_model_ref_expls
WHERE component_id = incomponentid
AND model_id = expl.model_id
AND referring_node_id = inreferringnodeid
AND child_model_expl_id = current_expl_id);
SELECT ps_node_id, parent_id, name, intl_text_id, tree_seq,
minimum, maximum, ps_node_type, initial_value, initial_num_value, -- sselahi
virtual_flag, feature_type, bom_required_flag, reference_id, orig_sys_ref,
effective_from, effective_until, component_sequence_id
bulk collect INTO ntpsnodeid, ntparentid, ntname, ntdescriptionid, nttreeseq,
ntminimumsel, ntmaximumsel, ntpsnodetype, ntinitialvalue, ntinitnumval, -- sselahi
ntvirtualflag, ntfeaturetype, ntbomrequired, ntreferenceid, ntorigsysref,
nteffectivefrom, nteffectiveuntil, ntcomponentsequenceid
FROM cz_ps_nodes
WHERE deleted_flag = flag_not_deleted
START WITH parent_id = incomponentid
CONNECT BY
(PRIOR virtual_flag IS NULL OR PRIOR virtual_flag = flag_virtual)
AND PRIOR ps_node_id = parent_id;
UPDATE cz_expression_nodes SET model_ref_expl_id = correct_expl_id
WHERE ps_node_id = incomponentid AND deleted_flag = flag_not_deleted;
UPDATE cz_func_comp_specs SET model_ref_expl_id = correct_expl_id
WHERE component_id = incomponentid AND deleted_flag = flag_not_deleted;
UPDATE cz_combo_features SET model_ref_expl_id = correct_expl_id
WHERE feature_id = incomponentid AND deleted_flag = flag_not_deleted;
UPDATE cz_des_chart_features SET model_ref_expl_id = correct_expl_id
WHERE feature_id = incomponentid AND deleted_flag = flag_not_deleted;
UPDATE cz_des_chart_cells SET secondary_feat_expl_id = correct_expl_id
WHERE secondary_feature_id = incomponentid AND deleted_flag = flag_not_deleted;
UPDATE cz_ui_nodes SET model_ref_expl_id = correct_expl_id
WHERE ps_node_id = incomponentid AND deleted_flag = flag_not_deleted;
UPDATE cz_expression_nodes SET model_ref_expl_id = correct_expl_id
WHERE ps_node_id = ntpsnodeid(i) AND deleted_flag = flag_not_deleted;
UPDATE cz_func_comp_specs SET model_ref_expl_id = correct_expl_id
WHERE component_id = ntpsnodeid(i) AND deleted_flag = flag_not_deleted;
UPDATE cz_combo_features SET model_ref_expl_id = correct_expl_id
WHERE feature_id = ntpsnodeid(i) AND deleted_flag = flag_not_deleted;
UPDATE cz_des_chart_features SET model_ref_expl_id = correct_expl_id
WHERE feature_id = ntpsnodeid(i) AND deleted_flag = flag_not_deleted;
UPDATE cz_des_chart_cells SET secondary_feat_expl_id = correct_expl_id
WHERE secondary_feature_id = ntpsnodeid(i) AND deleted_flag = flag_not_deleted;
UPDATE cz_ui_nodes SET model_ref_expl_id = correct_expl_id
WHERE ps_node_id = ntpsnodeid(i) AND deleted_flag = flag_not_deleted;
SELECT object_id INTO ncount FROM cz_rp_entries
WHERE deleted_flag = flag_not_deleted
AND object_type = 'PRJ'
AND name = ntname(i);
SELECT MAX(cz_utils.conv_num(substr(name, 7, instr(name, ')') - 7))) INTO ncount
FROM cz_rp_entries
WHERE deleted_flag = flag_not_deleted
AND object_type = 'PRJ'
AND name LIKE 'Copy (%) of ' || ntname(i);
SELECT cz_ps_nodes_s.NEXTVAL INTO base_node_id FROM dual;
INSERT INTO cz_ps_nodes
(ps_node_id, parent_id, ps_node_type, minimum, maximum, minimum_selected, maximum_selected,
name, tree_seq, deleted_flag, devl_project_id, virtual_flag, reference_id,
system_node_flag, ui_omit, effective_from, effective_until, orig_sys_ref,
component_sequence_id)
SELECT
current_node_id, ntparentid(i), ps_node_type_reference, 1, 1, ntminimumsel(i), ntmaximumsel(i),
genname, nttreeseq(i), flag_not_deleted, localmodelid, flag_virtual, modelpsnodeid,
'0', '0', nteffectivefrom(i), nteffectiveuntil(i), ntorigsysref(i),
ntcomponentsequenceid(i)
FROM dual;
UPDATE cz_ps_nodes SET
parent_id = NULL,
minimum = 0,
maximum = -1,
tree_seq = 1,
component_sequence_id = NULL,
virtual_flag = flag_virtual
WHERE ps_node_id = ntpsnodeid(i);
UPDATE cz_ps_nodes SET
devl_project_id = ntpsnodeid(i),
--orig_sys_ref = SUBSTR(orig_sys_ref, INSTR(orig_sys_ref, '-', 1, inLogicNetLevel + 2) + 1)
orig_sys_ref = substr(substr(orig_sys_ref, ncutstart),1,instr(substr(orig_sys_ref, ncutstart),':',-1,1)) || stopitemid
WHERE ps_node_id IN
(SELECT ps_node_id FROM cz_ps_nodes
WHERE deleted_flag = flag_not_deleted
START WITH ps_node_id = ntpsnodeid(i)
CONNECT BY PRIOR ps_node_id = parent_id);
INSERT INTO cz_rule_folders
(rule_folder_id,name,tree_seq,devl_project_id,created_by,last_updated_by,
creation_date,last_update_date,deleted_flag)
SELECT cz_rule_folders_s.NEXTVAL,ntname(i)||' Rules',0,
ntpsnodeid(i),UID,UID,SYSDATE,SYSDATE,'0'
FROM dual WHERE NOT EXISTS
(SELECT 1 FROM cz_rule_folders WHERE
devl_project_id=ntpsnodeid(i) AND
parent_rule_folder_id IS NULL AND name=ntname(i)||' Rules');
INSERT INTO cz_devl_projects
(devl_project_id, name, persistent_project_id, deleted_flag, orig_sys_ref)
SELECT
ntpsnodeid(i), genname, ntpsnodeid(i), flag_not_deleted,
substr(ntorigsysref(i),instr(ntorigsysref(i),':',-1,3)+1,instr(ntorigsysref(i),':',-1,2)-instr(ntorigsysref(i),':',-1,3)-1) || ':' ||
substr(ntorigsysref(i),instr(ntorigsysref(i),':',-1,2)+1,instr(ntorigsysref(i),':',-1,1)-instr(ntorigsysref(i),':',-1,2)-1) || ':' ||
stopitemid /*substr(ntOrigSysRef(i),instr(ntOrigSysRef(i),':',-1,1)+1)*/
FROM dual WHERE NOT EXISTS
(SELECT NULL FROM cz_devl_projects WHERE devl_project_id = ntpsnodeid(i));
INSERT INTO cz_rp_entries
(object_type,object_id,enclosing_folder,name,description,deleted_flag)
SELECT 'PRJ',ntpsnodeid(i),0,
genname,genname,'0'
FROM dual WHERE NOT EXISTS
(SELECT 1 FROM cz_rp_entries WHERE
(object_type='PRJ' AND object_id=ntpsnodeid(i)) OR
(enclosing_folder=0 AND name=genname));
SELECT value
INTO cz_upgrade.v_schema_version
FROM cz_db_settings
WHERE cz_db_settings.setting_id = MAJOR_SCHEMA_VERSION;
SELECT cz_xfr_run_infos_s.nextval into v_run_id FROM dual;
INSERT INTO cz_db_logs (LOGTIME,LOGUSER,URGENCY,CALLER,STATUSCODE,MESSAGE,CREATED_BY,CREATION_DATE,SESSION_ID
,MESSAGE_ID,RUN_ID)
VALUES (sysdate,'upgrade_logic_user',1,v_caller,0,v_message,-1,sysdate,1,1,v_run_id);
------------select all source models to generate logic
SELECT devl_project_id
BULK
COLLECT
INTO devlProjectId
FROM cz_devl_projects
WHERE cz_devl_projects.deleted_flag = '0'
AND cz_devl_projects.devl_project_id IN (SELECT object_id
FROM cz_rp_entries
WHERE cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.object_type = 'PRJ');
-- delete cz_lce_headers for component_id = devl_project_id with model_ref_expl_flag = -1
-- Bug #2369725
update cz_lce_headers set deleted_flag = '1'
where component_id = devlProjectId(i)
and deleted_flag = '0'
and model_ref_expl_id = -1;
SELECT count(*)
INTO v_count
FROM cz_lce_load_specs
WHERE cz_lce_load_specs.lce_header_id = p_lce_header_id
AND cz_lce_load_specs.deleted_flag = '0';
SELECT count(*)
INTO v_count
FROM cz_lce_load_specs
WHERE cz_lce_load_specs.lce_header_id = p_lce_header_id
AND cz_lce_load_specs.attachment_expl_id > 0
AND cz_lce_load_specs.required_expl_id > 0
AND cz_lce_load_specs.attachment_comp_id > 0
AND cz_lce_load_specs.model_id > 0
AND cz_lce_load_specs.net_type > 0
AND cz_lce_load_specs.deleted_flag = '0';
update cz_lce_load_specs
set deleted_flag = '1'
where cz_lce_load_specs.lce_header_id = p_lce_header_id
and cz_lce_load_specs.deleted_flag = '0';
SELECT lce_header_id
BULK
COLLECT
INTO x_lce_header_tbl
FROM cz_lce_headers
WHERE cz_lce_headers.deleted_flag = '0'
AND cz_lce_headers.component_id IN
(
SELECT ps_node_id
FROM cz_ps_nodes
WHERE cz_ps_nodes.deleted_flag = '0'
AND cz_ps_nodes.devl_project_id IN
(
SELECT object_id
FROM cz_rp_entries
WHERE cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.object_type = 'PRJ'
)
);
SELECT model_id
BULK
COLLECT
INTO v_published_root_models_tbl
FROM cz_model_publications
WHERE cz_model_publications.deleted_flag = '0'
AND cz_model_publications.export_status = 'OK'
AND cz_model_publications.source_target_flag = 'T';
v_all_published_models_tbl.DELETE;
v_child_models_tbl.DELETE;
SELECT component_id
BULK
COLLECT
INTO v_child_models_tbl
FROM cz_model_ref_expls
WHERE cz_model_ref_expls.model_id = v_published_root_models_tbl(rootModel)
AND cz_model_ref_expls.deleted_flag = '0';
SELECT lce_header_id
BULK
COLLECT
INTO v_lce_hdrs_tbl
FROM cz_lce_headers
WHERE cz_lce_headers.component_id = v_all_published_models_tbl(pubLceHdr)
AND cz_lce_headers.deleted_flag = '0';
SELECT lce_header_id
,component_id
,model_ref_expl_id
,net_type
,devl_project_id
INTO v_lce_header_id
,v_component_id
,v_model_ref_expl_id
,v_net_type
,v_devl_project_id
FROM cz_lce_headers
WHERE cz_lce_headers.lce_header_id = p_lce_header_id
AND cz_lce_headers.deleted_flag = '0';
SELECT devl_project_id
INTO v_model_id
FROM cz_ps_nodes
WHERE cz_ps_nodes.deleted_flag = '0'
AND cz_ps_nodes.ps_node_id = v_component_id;
-----get explosion id for above selected v_model_id
BEGIN
SELECT model_ref_expl_id
INTO v_model_ref_expl_id
FROM cz_model_ref_expls
WHERE cz_model_ref_expls.model_id = v_model_id
AND cz_model_ref_expls.component_id = v_component_id
AND cz_model_ref_expls.deleted_flag = '0';
SELECT component_id
INTO v_component_id
FROM cz_model_ref_expls
WHERE cz_model_ref_expls.model_ref_expl_id = v_model_ref_expl_id
AND cz_model_ref_expls.deleted_flag = '0';
SELECT devl_project_id
INTO v_model_id
FROM cz_ps_nodes
WHERE cz_ps_nodes.deleted_flag = '0'
AND cz_ps_nodes.ps_node_id = v_component_id;
insert into cz_lce_load_specs ( ATTACHMENT_EXPL_ID
,LCE_HEADER_ID
,REQUIRED_EXPL_ID
,ATTACHMENT_COMP_ID
,MODEL_ID
,NET_TYPE )
values ( v_model_ref_expl_id
,v_lce_header_id
,v_model_ref_expl_id
,v_component_id
,v_model_id
,v_net_type );
UPDATE cz_lce_headers
SET model_ref_expl_id = v_model_ref_expl_id,
devl_project_id = v_model_id,
NBR_REQUIRED_EXPLS = 0
WHERE lce_header_id = v_lce_header_id;
v_all_lce_headers.DELETE;
v_attachment_expl_id_tbl.DELETE;
SELECT distinct attachment_expl_id
BULK
COLLECT
INTO v_attachment_expl_id_tbl
FROM cz_lce_load_specs
WHERE cz_lce_load_specs.lce_header_id = v_all_lce_headers(allLceHeader)
AND cz_lce_load_specs.deleted_flag = '0';
v_net_type_tbl.DELETE;
SELECT distinct net_type
BULK
COLLECT
INTO v_net_type_tbl
FROM cz_lce_load_specs
WHERE cz_lce_load_specs.lce_header_id = v_all_lce_headers(allLceHeader)
AND cz_lce_load_specs.deleted_flag = '0';
v_required_expl_tbl.DELETE;
SELECT distinct required_expl_id
BULK
COLLECT
INTO v_required_expl_tbl
FROM cz_lce_load_specs
WHERE cz_lce_load_specs.lce_header_id = v_all_lce_headers(allLceHeader)
AND cz_lce_load_specs.deleted_flag = '0';
SELECT attachment_expl_id
INTO v_attachment_expl_id
FROM cz_lce_load_specs
WHERE cz_lce_load_specs.required_expl_id = v_required_expl_tbl(reqdExplId)
AND cz_lce_load_specs.lce_header_id = v_all_lce_headers(allLceHeader)
AND cz_lce_load_specs.deleted_flag = '0';
SELECT count(*)
INTO v_required_expl_chk_count
FROM cz_model_ref_expls
WHERE cz_model_ref_expls.model_id = (SELECT model_id
FROM cz_model_ref_expls t
WHERE t.model_ref_expl_id = v_attachment_expl_id
AND t.deleted_flag = '0')
AND cz_model_ref_expls.model_ref_expl_id = v_required_expl_tbl(reqdExplId)
AND cz_model_ref_expls.deleted_flag = '0';
x_logic_status := 'Verification logic has errors for some lce headers. Check cz_db_logs using the query select message from cZ_db_logs where caller = VERIFY_LOGIC';
TYPE tMinimumSel IS TABLE OF cz_ps_nodes.minimum_selected%TYPE INDEX BY BINARY_INTEGER;
TYPE tMaximumSel IS TABLE OF cz_ps_nodes.maximum_selected%TYPE INDEX BY BINARY_INTEGER;
INSERT INTO cz_db_logs (message, statuscode, caller, urgency, run_id)
VALUES (SUBSTR(inMessage, 1, 2000), nDebug, 'Rules Verification', inUrgency, thisRunId);
SELECT rule_id, rule_type, antecedent_id, consequent_id, name, reason_id,
expr_rule_type, rule_folder_id
FROM cz_rules
WHERE devl_project_id = inComponentId
AND deleted_flag = FLAG_NOT_DELETED
AND disabled_flag = FLAG_NOT_DISABLED;
FOR folder IN (SELECT name FROM cz_rule_folders
WHERE deleted_flag = FLAG_NOT_DELETED
AND parent_rule_folder_id IS NOT NULL
START WITH rule_folder_id = nRuleFolderId
CONNECT BY PRIOR parent_rule_folder_id = rule_folder_id)LOOP
IF(LENGTH(folder.name) + LENGTH(vQualified) + 1 < 2000 - nRuleName)THEN
vQualified := '.' || folder.name || vQualified;
v_NodeIndexPath.DELETE; --reset the table
v_tExplNodeId.DELETE;
v_tExprType.DELETE;
v_tExprSubtype.DELETE;
v_InstByLevel.DELETE;
v_Assignable.DELETE;
v_Participant.DELETE;
v_DistinctIndex.DELETE;
v_ParticipantIndex.DELETE;
v_BeneathNodes.DELETE;
v_BeneathCount.DELETE;
v_MarkLoadCondition.DELETE;
v_LoadConditionId.DELETE;
v_tExprId.DELETE;
v_tExprParentId.DELETE;
v_tExpressId.DELETE;
v_tExprPsNodeId.DELETE;
v_tExprDataValue.DELETE;
v_tExprDataNumValue.DELETE; -- sselahi
v_tExprPropertyId.DELETE;
v_tGridColId.DELETE;
v_ChildrenIndex.DELETE;
v_NodeUpPath.DELETE;
v_IndexByExprNodeId.DELETE;
v_NumberOfChildren.DELETE;
SELECT model_ref_expl_id, expr_type, expr_node_id, expr_parent_id, -- sselahi
express_id, expr_subtype, ps_node_id, data_value, data_num_value, property_id, consequent_flag
BULK COLLECT INTO v_tExplNodeId, v_tExprType, v_tExprId, v_tExprParentId,
v_tExpressId, v_tExprSubtype, v_tExprPsNodeId,
v_tExprDataValue, v_tExprDataNumValue, v_tExprPropertyId, v_tConsequentFlag
FROM cz_expression_nodes
WHERE express_id IN (nAntecedentId, nConsequentId)
AND expr_type <> EXPR_NODE_TYPE_PUNCT
AND deleted_flag = FLAG_NOT_DELETED
ORDER BY expr_parent_id, seq_nbr;
SELECT model_ref_expl_id, feature_id, grid_col_id, EXPR_NODE_TYPE_NODE
BULK COLLECT INTO v_tExplNodeId, v_tExprPsNodeId, v_tGridColId, v_tExprType
FROM cz_combo_features
WHERE rule_id = nRuleId
AND deleted_flag = FLAG_NOT_DELETED;
SELECT model_ref_expl_id, expr_type, expr_node_id, expr_parent_id, -- sselahi
express_id, expr_subtype, ps_node_id, data_value, data_num_value, property_id
BULK COLLECT INTO v_tExplNodeId, v_tExprType, v_tExprId, v_tExprParentId,
v_tExpressId, v_tExprSubtype, v_tExprPsNodeId,
v_tExprDataValue, v_tExprDataNumValue, v_tExprPropertyId
FROM cz_expression_nodes
WHERE express_id = nAntecedentId
AND expr_type <> EXPR_NODE_TYPE_PUNCT
AND deleted_flag = FLAG_NOT_DELETED
ORDER BY expr_parent_id, seq_nbr;
SELECT model_ref_expl_id, feature_id, feature_type, EXPR_NODE_TYPE_NODE
BULK COLLECT INTO v_tExplNodeId, v_tExprPsNodeId, v_tFeatureType, v_tExprType
FROM cz_des_chart_features
WHERE rule_id = nRuleId
AND deleted_flag = FLAG_NOT_DELETED;
SELECT model_ref_expl_id, parent_expl_node_id, node_depth,
ps_node_type, virtual_flag, component_id, referring_node_id,
child_model_expl_id, expl_node_type
BULK COLLECT INTO v_NodeId, v_tParentId, v_tNodeDepth,
v_tNodeType, v_tVirtualFlag, v_tPsNodeId, v_tReferringId,
v_tChildModelExpl, v_tExplNodeType
FROM cz_model_ref_expls
WHERE model_id = inComponentId and deleted_flag = FLAG_NOT_DELETED;
SELECT name INTO errorMessage
FROM cz_devl_projects
WHERE devl_project_id = inProjectId;
SELECT ps_node_id, parent_id, item_id, minimum, maximum, name, intl_text_id,
minimum_selected, maximum_selected, ps_node_type, initial_value, initial_num_value, -- sselahi
virtual_flag, feature_type, bom_required_flag, reference_id, persistent_node_id,
effective_from, effective_until, effective_usage_mask, effectivity_set_id, decimal_qty_flag
BULK COLLECT INTO ntPsNodeId, ntParentId, ntItemId, ntMinimum, ntMaximum, ntName, ntDescriptionId,
ntMinimumSel, ntMaximumSel, ntPsNodeType, ntInitialValue, ntInitNumVal, -- sselahi
ntVirtualFlag, ntFeatureType, ntBomRequired, ntReferenceId, ntPersistentId,
dtEffFrom, dtEffUntil, vtUsageMask, ntEffSetId, ntDecimalQty
FROM cz_ps_nodes
WHERE deleted_flag = FLAG_NOT_DELETED
START WITH ps_node_id = inComponentId
CONNECT BY
(PRIOR virtual_flag IS NULL OR PRIOR virtual_flag = FLAG_VIRTUAL OR
PRIOR ps_node_id = inComponentId)
AND PRIOR ps_node_id = parent_id;
SELECT cz_xfr_run_infos_s.NEXTVAL INTO thisRunId FROM DUAL;
SELECT LPAD(TO_CHAR(p_number),p_sort_width,'0') INTO ret FROM dual;
FOR i IN (SELECT config_item_id
FROM cz_config_items
WHERE parent_config_item_id = p_config_item_id
AND config_hdr_id = p_config_hdr_id
AND config_rev_nbr = p_config_rev_nbr
AND (ps_node_id IS NULL OR ps_node_id < 0))
LOOP
var_string1 := p_string1 || getNum(sequenceNbr);
FOR c_native IN (SELECT config_hdr_id,
config_rev_nbr,
config_item_id
FROM cz_config_details_v c
WHERE (ps_node_id IS NULL OR ps_node_id < 0)
AND bom_sort_order IS NULL
AND parent_config_item_id NOT IN
(SELECT config_item_id FROM cz_config_details_v
WHERE config_hdr_id = c.config_hdr_id
AND config_rev_nbr = c.config_rev_nbr))LOOP
globalIndex := globalIndex + 1;
UPDATE cz_config_items
SET bom_sort_order = tabBomSortOrder(i)
WHERE config_item_id = tabConfigItemId(i)
AND config_hdr_id = tabConfigHdrId(i)
AND config_rev_nbr = tabConfigRevNbr(i)
AND bom_sort_order is NULL
AND deleted_flag = '0';
t_model_names_tbl.DELETE;
t_model_ids_tbl.DELETE;
t_models_tbl.DELETE;
SELECT object_id
BULK
COLLECT
INTO t_models_tbl
FROM cz_rp_entries
WHERE object_type = 'PRJ'
AND deleted_flag = '0'
AND name = t_model_names_tbl(i);
t_publ_ids_ref.DELETE;
t_publ_ids_tbl.DELETE;
SELECT publication_id
BULK
COLLECT
INTO t_publ_ids_tbl
FROM cz_model_publications
WHERE deleted_flag = '0'
AND source_target_flag = 'S'
AND export_status = 'OK'
AND trunc(creation_date) < TO_DATE('12/31/2002', 'mm/dd/yyyy')
AND object_id = t_model_ids_tbl(i);
t_uis_ref.DELETE;
SELECT ui_def_id
BULK
COLLECT
INTO t_uis_ref
FROM cz_ui_defs
WHERE cz_ui_defs.devl_project_id IN (SELECT COMPONENT_ID
FROM CZ_MODEL_REF_EXPLS
WHERE model_id = t_model_ids_tbl(i)
AND deleted_flag = '0')
AND cz_ui_defs.deleted_flag = '0'
AND cz_ui_defs.ui_style = '0';
update cz_model_publications set remote_publication_id = NULL where publication_id = t_publ_ids_ref(i);
update cz_model_publications set export_status = 'PEN' where publication_id = t_publ_ids_ref(i);
update cz_model_publications set creation_date = sysdate where publication_id = t_publ_ids_ref(i);
delete from cz_pb_model_exports where publication_id = t_publ_ids_ref(i);
delete from cZ_model_publications where remote_publication_id = t_publ_ids_ref(i);
insert into cz_db_logs (LOGTIME,message,caller)
values (sysdate,l_message,'PBVISIONMODELS');