22: FOR c_devl IN (SELECT devl_project_id, orig_sys_ref FROM cz_devl_projects p
23: WHERE deleted_flag = flag_not_deleted
24: AND devl_project_id <> 0
25: AND orig_sys_ref is not NULL
26: AND EXISTS (SELECT NULL FROM cz_rp_entries
27: WHERE object_id = p.devl_project_id
28: AND object_type = 'PRJ'
29: AND deleted_flag = flag_not_deleted)
30: AND NOT EXISTS (SELECT NULL FROM cz_xfr_project_bills
199: COLLECT
200: INTO v_models_to_be_published,
201: v_orig_sys_ref,
202: v_last_import_date
203: FROM cz_devl_projects, cz_rp_entries, cz_xfr_project_bills
204: WHERE cz_devl_projects.ORIG_SYS_REF IS NOT NULL
205: AND cz_devl_projects.deleted_flag = '0'
206: AND cz_devl_projects.devl_project_id = cz_rp_entries.object_id
207: AND cz_rp_entries.object_type = 'PRJ' and cz_rp_entries.deleted_flag = '0'
202: v_last_import_date
203: FROM cz_devl_projects, cz_rp_entries, cz_xfr_project_bills
204: WHERE cz_devl_projects.ORIG_SYS_REF IS NOT NULL
205: AND cz_devl_projects.deleted_flag = '0'
206: AND cz_devl_projects.devl_project_id = cz_rp_entries.object_id
207: AND cz_rp_entries.object_type = 'PRJ' and cz_rp_entries.deleted_flag = '0'
208: AND cz_devl_projects.devl_project_id = cz_xfr_project_bills.model_ps_node_id(+)
209: ORDER BY orig_sys_ref, last_import_date DESC;
210:
203: FROM cz_devl_projects, cz_rp_entries, cz_xfr_project_bills
204: WHERE cz_devl_projects.ORIG_SYS_REF IS NOT NULL
205: AND cz_devl_projects.deleted_flag = '0'
206: AND cz_devl_projects.devl_project_id = cz_rp_entries.object_id
207: AND cz_rp_entries.object_type = 'PRJ' and cz_rp_entries.deleted_flag = '0'
208: AND cz_devl_projects.devl_project_id = cz_xfr_project_bills.model_ps_node_id(+)
209: ORDER BY orig_sys_ref, last_import_date DESC;
210:
211: IF (v_models_to_be_published.COUNT > 0 ) THEN
851:
852: genname := ntname(i);
853:
854: BEGIN
855: SELECT object_id INTO ncount FROM cz_rp_entries
856: WHERE deleted_flag = flag_not_deleted
857: AND object_type = 'PRJ'
858: AND name = ntname(i);
859:
859:
860: ncount := NULL;
861: BEGIN
862: SELECT MAX(cz_utils.conv_num(substr(name, 7, instr(name, ')') - 7))) INTO ncount
863: FROM cz_rp_entries
864: WHERE deleted_flag = flag_not_deleted
865: AND object_type = 'PRJ'
866: AND name LIKE 'Copy (%) of ' || ntname(i);
867: EXCEPTION
944: stopitemid /*substr(ntOrigSysRef(i),instr(ntOrigSysRef(i),':',-1,1)+1)*/
945: FROM dual WHERE NOT EXISTS
946: (SELECT NULL FROM cz_devl_projects WHERE devl_project_id = ntpsnodeid(i));
947:
948: --Insert into cz_rp_entries
949:
950: INSERT INTO cz_rp_entries
951: (object_type,object_id,enclosing_folder,name,description,deleted_flag)
952: SELECT 'PRJ',ntpsnodeid(i),0,
946: (SELECT NULL FROM cz_devl_projects WHERE devl_project_id = ntpsnodeid(i));
947:
948: --Insert into cz_rp_entries
949:
950: INSERT INTO cz_rp_entries
951: (object_type,object_id,enclosing_folder,name,description,deleted_flag)
952: SELECT 'PRJ',ntpsnodeid(i),0,
953: genname,genname,'0'
954: FROM dual WHERE NOT EXISTS
951: (object_type,object_id,enclosing_folder,name,description,deleted_flag)
952: SELECT 'PRJ',ntpsnodeid(i),0,
953: genname,genname,'0'
954: FROM dual WHERE NOT EXISTS
955: (SELECT 1 FROM cz_rp_entries WHERE
956: (object_type='PRJ' AND object_id=ntpsnodeid(i)) OR
957: (enclosing_folder=0 AND name=genname));
958:
959: EXCEPTION
1043: INTO devlProjectId
1044: FROM cz_devl_projects
1045: WHERE cz_devl_projects.deleted_flag = '0'
1046: AND cz_devl_projects.devl_project_id IN (SELECT object_id
1047: FROM cz_rp_entries
1048: WHERE cz_rp_entries.deleted_flag = '0'
1049: AND cz_rp_entries.object_type = 'PRJ');
1050:
1051: IF (devlProjectId.COUNT > 0) THEN
1044: FROM cz_devl_projects
1045: WHERE cz_devl_projects.deleted_flag = '0'
1046: AND cz_devl_projects.devl_project_id IN (SELECT object_id
1047: FROM cz_rp_entries
1048: WHERE cz_rp_entries.deleted_flag = '0'
1049: AND cz_rp_entries.object_type = 'PRJ');
1050:
1051: IF (devlProjectId.COUNT > 0) THEN
1052: FOR i IN devlProjectId.FIRST..devlProjectId.LAST
1045: WHERE cz_devl_projects.deleted_flag = '0'
1046: AND cz_devl_projects.devl_project_id IN (SELECT object_id
1047: FROM cz_rp_entries
1048: WHERE cz_rp_entries.deleted_flag = '0'
1049: AND cz_rp_entries.object_type = 'PRJ');
1050:
1051: IF (devlProjectId.COUNT > 0) THEN
1052: FOR i IN devlProjectId.FIRST..devlProjectId.LAST
1053: LOOP
1137: WHERE cz_ps_nodes.deleted_flag = '0'
1138: AND cz_ps_nodes.devl_project_id IN
1139: (
1140: SELECT object_id
1141: FROM cz_rp_entries
1142: WHERE cz_rp_entries.deleted_flag = '0'
1143: AND cz_rp_entries.object_type = 'PRJ'
1144: )
1145: );
1138: AND cz_ps_nodes.devl_project_id IN
1139: (
1140: SELECT object_id
1141: FROM cz_rp_entries
1142: WHERE cz_rp_entries.deleted_flag = '0'
1143: AND cz_rp_entries.object_type = 'PRJ'
1144: )
1145: );
1146: EXCEPTION
1139: (
1140: SELECT object_id
1141: FROM cz_rp_entries
1142: WHERE cz_rp_entries.deleted_flag = '0'
1143: AND cz_rp_entries.object_type = 'PRJ'
1144: )
1145: );
1146: EXCEPTION
1147: WHEN OTHERS THEN
3148: t_model_names_tbl(1) := 'Envoy Custom Laptop(204 143)';
3149: t_model_names_tbl(2) := 'Sentinal Custom Desktop(204 137)';
3150: t_model_names_tbl(3) := 'Server System(204 3791)';
3151:
3152: -----get model id(s) from cz_rp_entries
3153: IF (t_model_names_tbl.COUNT > 0) THEN
3154: t_model_ids_tbl.DELETE;
3155: FOR I IN t_model_names_tbl.FIRST..t_model_names_tbl.LAST
3156: LOOP
3159: SELECT object_id
3160: BULK
3161: COLLECT
3162: INTO t_models_tbl
3163: FROM cz_rp_entries
3164: WHERE object_type = 'PRJ'
3165: AND deleted_flag = '0'
3166: AND name = t_model_names_tbl(i);
3167: EXCEPTION