[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Migration request is created by Developer and contains the list of all models selected
* for Migration from the source's Configurator Repository, target Instance name and
* target Repository Folder.
* @param p_userid Standard parameters required for locking. Represent calling user.
* @param p_respid Standard parameters required for locking. Represent calling responsibility.
* @param p_applid Standard parameters required for locking. Represent calling application.
* @param p_run_id Number identifying the session. If left NULL, the API will generate the number and
* return it in x_run_id.
* @param x_run_id Output parameter containing internally generated session identifier if p_run_id
* was NULL, otherwise equal to p_run_id.
*/
PROCEDURE migrate_models(p_request_id IN NUMBER,
p_user_id IN NUMBER,
p_resp_id IN NUMBER,
p_appl_id IN NUMBER,
p_run_id IN NUMBER,
x_run_id OUT NOCOPY NUMBER,
x_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'migrate_models';
SELECT cz_xfr_run_infos_s.NEXTVAL INTO x_run_id FROM DUAL;
FOR c_pub IN (SELECT publication_id FROM cz_model_publications
WHERE migration_group_id = p_request_id AND deleted_flag = '0')LOOP
cz_pb_mgr.publish_model(c_pub.publication_id, x_run_id, l_status);
* Migration request is created by Developer and contains the list of all models selected
* for Migration from the source's Configurator Repository, target Instance name and
* target Repository Folder.
*/
PROCEDURE migrate_models_cp(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_request_id IN NUMBER
) IS
l_status VARCHAR2(3);
FOR c_pub IN (SELECT publication_id FROM cz_model_publications
WHERE migration_group_id = p_request_id AND deleted_flag = '0')LOOP
l_mig_group_found :=TRUE;
UPDATE CZ_PERSISTENT_REC_IDS SET max_persistent_rec_id = max_persistent_rec_id + 1
WHERE devl_project_id= (select devl_project_id from cz_devl_projects
where deleted_flag = '0' and devl_project_id=p_model_id
AND post_migr_change_flag <> 'L') and deleted_flag=0
--If no record returned, the value of the variable will not change.
RETURNING max_persistent_rec_id INTO x_new_record_id;
SELECT NVL(TO_NUMBER(value), 50000) INTO m_commit_size
FROM cz_db_settings
WHERE upper(SECTION_NAME) = 'SCHEMA' AND upper(SETTING_ID) = 'BATCHSIZE';
SELECT MODEL_TYPE_NAME_ERR INTO l_model_type FROM cz_ps_nodes
WHERE deleted_flag = '0' AND devl_project_id IN
(SELECT component_id FROM cz_model_ref_expls
WHERE deleted_flag = '0' AND model_id = p_model_id
AND (ps_node_type = PS_NODE_TYPE_REFERENCE OR parent_expl_node_id IS NULL))
AND name IS NULL AND rownum < 2;
FOR i IN (SELECT persistent_node_id, COUNT(distinct name)
FROM cz_ps_nodes
WHERE deleted_flag = '0' AND devl_project_id IN
(SELECT component_id FROM cz_model_ref_expls
WHERE deleted_flag = '0' AND model_id = p_model_id
AND (ps_node_type = PS_NODE_TYPE_REFERENCE OR
parent_expl_node_id IS NULL))
GROUP BY persistent_node_id
HAVING COUNT(DISTINCT name) > 1)
LOOP
l_model_type := MODEL_TYPE_ABNORMAL;
FOR i IN (SELECT devl_project_id, persistent_node_id, name, ps_node_type, reference_id
FROM cz_ps_nodes
WHERE deleted_flag = '0' AND devl_project_id in
(SELECT component_id FROM cz_model_ref_expls
WHERE deleted_flag = '0' AND model_id = p_model_id
AND (ps_node_type = PS_NODE_TYPE_REFERENCE OR parent_expl_node_id IS NULL)))
LOOP
l_prj_pid_name_map(i.devl_project_id)(i.persistent_node_id) := i.name;
SELECT config_hdr_id, config_rev_nbr, component_id
BULK COLLECT INTO l_hdr_tbl, l_rev_tbl, l_mdl_tbl
FROM cz_config_hdrs hdr
WHERE deleted_flag = '0'
AND creation_date >= NVL(p_begin_date, cz_utils.EPOCH_BEGIN_)
AND creation_date <= NVL(p_end_date, SYSDATE)
AND persistent_component_id = (SELECT persistent_project_id
FROM cz_devl_projects
WHERE devl_project_id = p_model_id)
AND EXISTS (SELECT NULL FROM cz_config_items
WHERE config_hdr_id = hdr.config_hdr_id
AND config_rev_nbr = hdr.config_rev_nbr
AND (parent_config_item_id IS NULL OR parent_config_item_id = -1)
AND ps_node_name IS NULL);
SELECT name INTO l_model_name
FROM cz_devl_projects
WHERE devl_project_id = p_model_id;
SELECT devl_project_id INTO l_model_id
FROM cz_devl_projects
WHERE devl_project_id = l_mdl_tbl(i) AND deleted_flag = '0';
SELECT p_model_id INTO l_model_id
FROM cz_model_ref_expls re
WHERE deleted_flag = '0' AND model_id = l_mdl_tbl(i)
AND ps_node_type = PS_NODE_TYPE_REFERENCE
AND NOT EXISTS (SELECT 1 FROM cz_devl_projects
WHERE deleted_flag = '0' AND devl_project_id = re.component_id)
AND ROWNUM < 2;
l_miss_item_tbl.DELETE;
UPDATE cz_config_items item
SET ps_node_name =
(SELECT name FROM cz_ps_nodes psn
WHERE deleted_flag = '0' AND persistent_node_id = item.ps_node_id
AND EXISTS (SELECT 1 FROM cz_model_ref_expls
WHERE deleted_flag = '0' AND model_id = l_model_id
AND (ps_node_type = PS_NODE_TYPE_REFERENCE OR parent_expl_node_id IS NULL)
AND component_id = psn.devl_project_id)
AND rownum < 2)
WHERE config_hdr_id = l_hdr_tbl(i) AND config_rev_nbr = l_rev_tbl(i);
SELECT config_item_id BULK COLLECT INTO l_miss_item_tbl
FROM cz_config_items
WHERE config_hdr_id = l_hdr_tbl(i) AND config_rev_nbr = l_rev_tbl(i)
AND deleted_flag = '0' AND ps_node_name IS NULL;
l_item_tbl.DELETE;
l_name_tbl.DELETE;
l_item_prj_map.DELETE;
FOR j IN (SELECT config_item_id, ps_node_id, parent_config_item_id
FROM cz_config_items
WHERE deleted_flag = '0'
START WITH (parent_config_item_id IS NULL OR parent_config_item_id = -1)
AND config_hdr_id = l_hdr_tbl(i) AND config_rev_nbr = l_rev_tbl(i)
CONNECT BY PRIOR config_item_id = parent_config_item_id
AND config_hdr_id = l_hdr_tbl(i) AND config_rev_nbr = l_rev_tbl(i)
AND deleted_flag = '0')
LOOP
IF j.parent_config_item_id IS NULL OR j.parent_config_item_id = -1 THEN
l_prj_id := l_model_id;
UPDATE cz_config_items
SET ps_node_name = l_name_tbl(j)
WHERE config_hdr_id = l_hdr_tbl(i) AND config_rev_nbr = l_rev_tbl(i)
AND config_item_id = l_item_tbl(j);
* configuration to be updated.
* @param p_config_end_date Optional, if present, indicates the date of the newest
* configuration to be updated.
*/
PROCEDURE upgrade_configs_by_items_cp
(errbuf OUT NOCOPY VARCHAR2
,retcode OUT NOCOPY NUMBER
,p_organization_code IN VARCHAR2
,p_organization_id IN NUMBER
,p_top_inv_item_from IN VARCHAR2
,p_top_inv_item_to IN VARCHAR2
,p_application_id IN NUMBER
,p_config_begin_date IN VARCHAR2
,p_config_end_date IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'upgrade_configs_by_items';
SELECT DISTINCT top_item_id, organization_id
BULK COLLECT INTO l_item_tbl, l_org_tbl
FROM cz_model_publications
WHERE deleted_flag = '0' AND object_type = 'PRJ'
AND source_target_flag = 'T' AND export_status = 'OK'
AND top_item_id IS NOT NULL AND organization_id IS NOT NULL;
SELECT DISTINCT top_item_id BULK COLLECT INTO l_item_tbl
FROM cz_model_publications
WHERE deleted_flag = '0' AND object_type = 'PRJ'
AND source_target_flag = 'T' AND export_status = 'OK'
AND organization_id = p_organization_id;
SELECT inventory_item_id BULK COLLECT INTO l_item_tbl
FROM mtl_system_items_vl item
WHERE organization_id = p_organization_id
AND concatenated_segments BETWEEN NVL(p_top_inv_item_from, p_top_inv_item_to)
AND NVL(p_top_inv_item_to, p_top_inv_item_from)
AND bom_item_type = BOM_ITEM_TYPE_MODEL
AND exists (SELECT NULL FROM cz_model_publications
WHERE deleted_flag = '0' AND object_type = 'PRJ'
AND source_target_flag = 'T' AND export_status = 'OK'
AND top_item_id = item.inventory_item_id
AND organization_id = p_organization_id);
* configuration to be updated.
* @param p_config_end_date Optional, if present, indicates the date of the newest
* configuration to be updated.
*/
PROCEDURE upgrade_configs_by_product_cp
(errbuf OUT NOCOPY VARCHAR2
,retcode OUT NOCOPY NUMBER
,p_product_key IN VARCHAR2
,p_application_id IN NUMBER
,p_config_begin_date IN VARCHAR2
,p_config_end_date IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'upgrade_configs_by_product';
SELECT 'Y' migrated
FROM cz_devl_projects dp
WHERE dp.devl_project_id = l_model_id
AND dp.post_migr_change_flag IS NOT NULL
AND dp.post_migr_change_flag <> 'L';
SELECT converted_target_flag
INTO l_converted_target_flag
FROM cz_servers
WHERE local_name = 'LOCAL';
update cz_servers
set converted_target_flag = '1' where
LOCAL_NAME = 'LOCAL';
update cz_servers
set source_server_flag = '0' where
source_server_flag = '1';
SELECT cz_model_publications_s.NEXTVAL, publication_id
BULK COLLECT
INTO l_new_pb_id, l_old_pb_id
FROM cz_model_publications
WHERE SOURCE_TARGET_FLAG = 'T'
AND cz_model_publications.deleted_flag = '0';
l_inst_str:= 'INSERT INTO cz_model_publications ' ||
' (PUBLICATION_ID ' ||
' ,MODEL_ID ' ||
' ,OBJECT_ID ' ||
' ,OBJECT_TYPE ' ||
' ,SERVER_ID ' ||
' ,ORGANIZATION_ID ' ||
' ,TOP_ITEM_ID ' ||
' ,PRODUCT_KEY ' ||
' ,PUBLICATION_MODE ' ||
' ,UI_DEF_ID ' ||
' ,UI_STYLE ' ||
' ,APPLICABLE_FROM ' ||
' ,APPLICABLE_UNTIL ' ||
' ,EXPORT_STATUS ' ||
' ,MODEL_PERSISTENT_ID ' ||
' ,DELETED_FLAG ' ||
' ,MODEL_LAST_STRUCT_UPDATE ' ||
' ,MODEL_LAST_LOGIC_UPDATE ' ||
' ,MODEL_LAST_UPDATED ' ||
' ,SOURCE_TARGET_FLAG ' ||
' ,REMOTE_PUBLICATION_ID ' ||
' ,CONTAINER ' ||
' ,PAGE_LAYOUT ' ||
' ,disabled_flag ' ||
' ,converted_target_flag ' ||
' ) ' ||
' SELECT :1 ' ||
' ,MODEL_ID ' ||
' ,OBJECT_ID ' ||
' ,OBJECT_TYPE ' ||
' ,SERVER_ID ' ||
' ,ORGANIZATION_ID ' ||
' ,TOP_ITEM_ID ' ||
' ,PRODUCT_KEY ' ||
' ,PUBLICATION_MODE ' ||
' ,UI_DEF_ID ' ||
' ,UI_STYLE ' ||
' ,APPLICABLE_FROM ' ||
' ,APPLICABLE_UNTIL ' ||
' ,EXPORT_STATUS ' ||
' ,MODEL_PERSISTENT_ID ' ||
' ,DELETED_FLAG ' ||
' ,MODEL_LAST_STRUCT_UPDATE ' ||
' ,MODEL_LAST_LOGIC_UPDATE ' ||
' ,MODEL_LAST_UPDATED ' ||
' ,''S'' ' ||
' ,PUBLICATION_ID ' ||
' ,CONTAINER ' ||
' ,PAGE_LAYOUT ' ||
' ,disabled_flag ' ||
' ,''1'' ' ||
' FROM cz_model_publications ' ||
' WHERE publication_id = :2 ';
SELECT server_local_id
INTO l_server_id
FROM cz_servers
WHERE UPPER (local_name) = UPPER (r_instance_name);
SELECT '1'
INTO obselete_exists
FROM DUAL
WHERE EXISTS (
SELECT publication_id
FROM cz_model_publications
WHERE source_target_flag = 'S'
AND deleted_flag = '0'
AND export_status <> MODEL_PUBLICATION_OBSELETE
AND server_id = l_server_id);
UPDATE cz_model_publications
set export_status = MODEL_PUBLICATION_OBSELETE
WHERE SOURCE_TARGET_FLAG = 'S'
AND deleted_flag = '0'
AND export_status <> MODEL_PUBLICATION_OBSELETE
AND server_id = l_server_id;
'SELECT NVL(source_server_flag,''0''), local_name FROM cz_servers@' || p_link_name || ' ' ||
'WHERE source_server_flag = ''1'' ';
SELECT instance_name
INTO local_instance_name
FROM cz_servers
WHERE local_name = 'LOCAL';
OPEN gl_ref_cursor FOR 'SELECT converted_target_flag
FROM cz_servers@'||p_link_name || ' where converted_target_flag = ''1''
and local_name = ''LOCAL'' ';
SELECT fndnam_link_name, instance_name
BULK COLLECT
INTO l_link_name_tbl, l_instance_name_tbl
FROM cz_servers
WHERE local_name <> 'LOCAL' AND FNDNAM_LINK_NAME IS NOT NULL ;