1 PACKAGE BODY CZ_IMP_PS_NODE as
2 /* $Header: czipsnb.pls 120.11 2008/04/15 14:05:00 skudryav ship $ */
3
4 CAPTION_RULE_TYPE CONSTANT NUMBER:=700;
5 JAVA_SYS_PROP_RULE_TYPE CONSTANT NUMBER:=501;
6
7 G_CONFIG_ENGINE_TYPE VARCHAR2(10);
8
9 /*--INTL_TEXT IMPORT SECTION START------------------------------------------*/
10 ------------------------------------------------------------------------------
11 PROCEDURE KRS_INTL_TEXT(inRUN_ID IN PLS_INTEGER,
12 COMMIT_SIZE IN PLS_INTEGER,
13 MAX_ERR IN PLS_INTEGER,
14 INSERTS IN OUT NOCOPY PLS_INTEGER,
15 UPDATES IN OUT NOCOPY PLS_INTEGER,
16 FAILED IN OUT NOCOPY PLS_INTEGER,
17 DUPS IN OUT NOCOPY PLS_INTEGER,
18 inXFR_GROUP IN VARCHAR2
19 ) IS
20 CURSOR c_imp_intl_text IS
21 SELECT DISTINCT orig_sys_ref, fsk_devlproject_1_1
22 FROM CZ_IMP_LOCALIZED_TEXTS
23 WHERE rec_status IS NULL AND Run_ID = inRUN_ID
24 ORDER BY orig_sys_ref;
25
26 /* cursor's data found indicator */
27 x_imp_intl_text_f BOOLEAN:=FALSE;
28 x_imp_localized_text_f BOOLEAN:=FALSE;
29 x_onl_intl_text_f BOOLEAN:=FALSE;
30 x_onl_intl_text_2_f BOOLEAN:=FALSE;
31 x_translated_intl_text_f BOOLEAN:=FALSE;
32 x_intl_text_found BOOLEAN := FALSE;
33 x_text_not_matching BOOLEAN := TRUE;
34 x_error BOOLEAN:=FALSE;
35
36 TYPE tImpIntlTextId IS TABLE OF CZ_IMP_LOCALIZED_TEXTS.INTL_TEXT_ID%TYPE INDEX BY BINARY_INTEGER;
37 TYPE tImpLocalizedStr IS TABLE OF CZ_IMP_LOCALIZED_TEXTS.LOCALIZED_STR%TYPE INDEX BY BINARY_INTEGER;
38 TYPE tImpLanguage IS TABLE OF CZ_IMP_LOCALIZED_TEXTS.LANGUAGE%TYPE INDEX BY BINARY_INTEGER;
39 TYPE tImpSourceLang IS TABLE OF CZ_IMP_LOCALIZED_TEXTS.SOURCE_LANG%TYPE INDEX BY BINARY_INTEGER;
40 TYPE tImpFSKDevlProject IS TABLE OF CZ_IMP_LOCALIZED_TEXTS.FSK_DEVLPROJECT_1_1%TYPE INDEX BY BINARY_INTEGER;
41 TYPE tImpOrigSysRef IS TABLE OF CZ_IMP_LOCALIZED_TEXTS.ORIG_SYS_REF%TYPE INDEX BY BINARY_INTEGER;
42 TYPE tNumber IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
43
44 nImpIntlTextId tImpIntlTextId;
45 sImpLocalizedStr tImpLocalizedStr;
46 sImpLanguage tImpLanguage;
47 sImpSourceLang tImpSourceLang;
48 sImpFSKDevlProject tImpFSKDevlProject;
49 sImpOrigSysRef tImpOrigSysRef;
50
51 nOnlIntlTextId CZ_IMP_LOCALIZED_TEXTS.INTL_TEXT_ID%TYPE;
52 nOnlIntlText CZ_IMP_LOCALIZED_TEXTS.LOCALIZED_STR%TYPE;
53 nOnlLanguage CZ_IMP_LOCALIZED_TEXTS.LANGUAGE%TYPE;
54 nOnlSourceLangn CZ_IMP_LOCALIZED_TEXTS.SOURCE_LANG%TYPE;
55 nOnlModelId CZ_IMP_LOCALIZED_TEXTS.MODEL_ID%TYPE;
56 nModelId CZ_IMP_LOCALIZED_TEXTS.MODEL_ID%TYPE;
57 nOnlOrigSysRef CZ_IMP_LOCALIZED_TEXTS.ORIG_SYS_REF%TYPE;
58
59 /* Internal vars */
60 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
61 nErrorCount PLS_INTEGER:=0; /*Error index */
62 nInsertCount PLS_INTEGER:=0; /*Inserts */
63 nUpdateCount PLS_INTEGER:=0; /*Updates */
64 nFailed PLS_INTEGER:=0; /*Failed records */
65 nDups PLS_INTEGER:=0; /*Dupl records */
66 nAllocateBlock PLS_INTEGER:=1;
67 nAllocateCounter PLS_INTEGER;
68 nNextValue NUMBER;
69 nNextId NUMBER;
70 nCount NUMBER;
71 l_msg VARCHAR2(2000);
72
73 v_settings_id VARCHAR2(40);
74 v_section_name VARCHAR2(30);
75 var_tl_prop NUMBER;
76 BEGIN
77
78 v_settings_id := 'OracleSequenceIncr';
79 v_section_name := 'SCHEMA';
80
81 BEGIN
82 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
83 WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
84 EXCEPTION
85 WHEN OTHERS THEN
86 nAllocateBlock:=1;
87 END;
88 nAllocateCounter:=nAllocateBlock-1;
89
90 OPEN c_imp_intl_text;
91
92 LOOP
93
94 FETCH c_imp_intl_text
95 bulk collect
96 into sImpOrigSysRef, sImpFSKDevlProject
97 limit COMMIT_SIZE;
98 EXIT WHEN c_imp_intl_text%NOTFOUND AND sImpOrigSysRef.COUNT = 0;
99
100 FOR I in 1..sImpOrigSysRef.COUNT LOOP
101
102 /* Return if MAX_ERR is reached */
103 IF(FAILED >= MAX_ERR) THEN
104 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_PS_NODE.KRS_INTL_TEXT:MAX',11276,inRun_Id);
105 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
106 END IF;
107
108 DECLARE
109
110 -- check online table for this text in this language for this model
111
112 CURSOR c_onl_intl_text IS
113 SELECT ol.intl_text_id,ol.localized_str, ol.model_id, ol.orig_sys_ref
114 FROM cz_localized_texts ol, cz_devl_projects od, cz_ps_nodes op,
115 cz_imp_ps_nodes ip, cz_imp_devl_project id
116 WHERE ol.orig_sys_ref = sImpOrigSysRef(i)
117 AND ip.fsk_intltext_1_1 = sImpOrigSysRef(i)
118 AND op.intl_text_id = ol.intl_text_id
119 AND ip.orig_sys_ref = op.orig_sys_ref
120 AND ol.model_id = od.devl_project_id
121 AND id.devl_project_id = od.devl_project_id
122 AND id.orig_sys_ref = sImpFSKDevlProject(i)
123 AND op.devl_project_id = ol.model_id
124 AND ip.fsk_devlproject_5_1 = od.orig_sys_ref
125 AND ol.deleted_flag = '0'
126 AND op.deleted_flag = '0'
127 AND od.deleted_flag = '0'
128 AND ip.run_id = inRUN_ID
129 AND id.run_id = inRUN_ID
130 AND id.rec_status='OK';
131
132 BEGIN
133 OPEN c_onl_intl_text;
134 FETCH c_onl_intl_text INTO nOnlIntlTextId,nOnlIntlText,nOnlModelId,nOnlOrigSysRef;
135 x_onl_intl_text_f:=c_onl_intl_text%FOUND;
136 CLOSE c_onl_intl_text;
137
138 IF x_onl_intl_text_f THEN /* update */
139
140 -- get the model_id from this import job
141
142 UPDATE cz_imp_localized_texts
143 SET intl_text_id = nOnlIntlTextId,
144 model_id = nOnlModelId,
145 disposition = 'M',
146 rec_status = 'PASS'
147 WHERE orig_sys_ref = sImpOrigSysRef(i)
148 AND fsk_devlproject_1_1 = sImpFSKDevlProject(i)
149 AND run_id = inRUN_ID;
150
151 UPDATE CZ_IMP_PS_NODES
152 SET INTL_TEXT_ID =nOnlIntlTextId
153 WHERE fsk_intltext_1_1 = sImpOrigSysRef(i)
154 AND fsk_devlproject_5_1 = sImpFSKDevlProject(i)
155 AND RUN_ID = inRUN_ID;
156
157 nUpdateCount:=nUpdateCount+1;
158
159 ELSE /* insert */
160
161 BEGIN
162 SELECT devl_project_id INTO nModelId
163 FROM cz_imp_devl_project
164 WHERE orig_sys_ref = sImpFSKDevlProject(i)
165 AND run_id = inRUN_ID
166 AND rec_status = 'OK';
167
168 nAllocateCounter:=nAllocateCounter+1;
169 IF(nAllocateCounter=nAllocateBlock)THEN
170 nAllocateCounter:=0;
171 SELECT CZ_INTL_TEXTS_S.NEXTVAL INTO nNextValue FROM DUAL;
172 END IF;
173 nNextId := nNextValue + nAllocateCounter;
174
175 UPDATE cz_imp_localized_texts
176 SET intl_text_id = nNextId,
177 model_id = nModelId,
178 disposition = 'I',
179 rec_status = 'PASS'
180 WHERE orig_sys_ref = sImpOrigSysRef(i)
181 AND fsk_devlproject_1_1 = sImpFSKDevlProject(i)
182 AND run_id = inRUN_ID;
183
184 UPDATE CZ_IMP_PS_NODES
185 SET INTL_TEXT_ID =nNextId
186 WHERE fsk_intltext_1_1 = sImpOrigSysRef(i)
187 AND fsk_devlproject_5_1 = sImpFSKDevlProject(i)
188 AND RUN_ID = inRUN_ID;
189
190 nInsertCount:=nInsertCount+1;
191
192 EXCEPTION
193 WHEN NO_DATA_FOUND THEN
194 FAILED:=FAILED+1;
195 UPDATE cz_imp_localized_texts
196 SET disposition='R',
197 rec_status='FAIL'
198 WHERE orig_sys_ref = sImpOrigSysRef(i)
199 AND fsk_devlproject_1_1 = sImpFSKDevlProject(i)
200 AND run_id = inRUN_ID;
201 END;
202
203 END IF;
204
205 nCommitCount:=nCommitCount+1;
206 /* COMMIT if the buffer size is reached */
207 IF(nCommitCount>= COMMIT_SIZE) THEN
208 COMMIT;
209 nCommitCount:=0;
210 END IF;
211 END;
212
213
214 DECLARE
215
216 -- check online table for this text in this language for this model
217
218 CURSOR c_tl_onl_intl_text IS
219 SELECT ol.intl_text_id,ol.localized_str, ol.orig_sys_ref
220 FROM cz_localized_texts ol
221 WHERE ol.orig_sys_ref = sImpOrigSysRef(i)
222 AND EXISTS(SELECT NULL FROM cz_imp_item_property_value
223 WHERE run_id = inRUN_ID AND FSK_LOCALIZEDTEXT_3_1=ol.ORIG_SYS_REF)
224 AND ol.deleted_flag = '0';
225
226 BEGIN
227 OPEN c_tl_onl_intl_text;
228 FETCH c_tl_onl_intl_text INTO nOnlIntlTextId,nOnlIntlText,nOnlOrigSysRef;
229 x_onl_intl_text_f:=c_tl_onl_intl_text%FOUND;
230 CLOSE c_tl_onl_intl_text;
231
232 IF x_onl_intl_text_f THEN /* update */
233 -- get the model_id from this import job
234
235 UPDATE cz_imp_localized_texts
236 SET intl_text_id = nOnlIntlTextId,
237 model_id = 0,
238 disposition = 'M',
239 rec_status = 'PASS'
240 WHERE orig_sys_ref = sImpOrigSysRef(i)
241 AND run_id = inRUN_ID;
242
243 nUpdateCount:=nUpdateCount+1;
244
245 ELSE /* insert */
246
247 BEGIN --#####
248
249 SELECT COUNT(*) INTO var_tl_prop FROM cz_imp_item_property_value
250 WHERE run_id = inRUN_ID AND FSK_LOCALIZEDTEXT_3_1=sImpOrigSysRef(i) AND rownum<2;
251
252 IF var_tl_prop > 0 THEN
253
254 nAllocateCounter:=nAllocateCounter+1;
255
256 IF(nAllocateCounter=nAllocateBlock)THEN
257 nAllocateCounter:=0;
258 SELECT CZ_INTL_TEXTS_S.NEXTVAL INTO nNextValue FROM DUAL;
259 END IF;
260 nNextId := nNextValue + nAllocateCounter;
261
262 UPDATE cz_imp_localized_texts
263 SET intl_text_id = nNextId,
264 model_id = 0,
265 disposition = 'I',
266 rec_status = 'PASS'
267 WHERE orig_sys_ref = sImpOrigSysRef(i)
268 AND run_id = inRUN_ID;
269
270 nInsertCount:=nInsertCount+1;
271 END IF;
272
273 EXCEPTION
274 WHEN NO_DATA_FOUND THEN
275 FAILED:=FAILED+1;
276 UPDATE cz_imp_localized_texts
277 SET disposition='R',
278 rec_status='FAIL'
279 WHERE orig_sys_ref = sImpOrigSysRef(i)
280 AND run_id = inRUN_ID;
281 END;
282
283 END IF;
284
285 nCommitCount:=nCommitCount+1;
286 /* COMMIT if the buffer size is reached */
287 IF(nCommitCount>= COMMIT_SIZE) THEN
288 COMMIT;
289 nCommitCount:=0;
290 END IF;
291 END;
292
293
294 END LOOP;
295 sImpOrigSysRef.DELETE;
296 sImpFSKDevlProject.DELETE;
297 sImpLanguage.DELETE;
298 sImpSourceLang.DELETE;
299
300 END LOOP;
301 CLOSE c_imp_intl_text;
302
303 INSERTS:=nInsertCount;
304 UPDATES:=nUpdateCount;
305 DUPS:=nDups;
306
307 EXCEPTION
308 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
309 RAISE;
310 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
311 RAISE;
312 WHEN OTHERS THEN
313 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.KRS_INTL_TEXT',11276,inRun_Id);
314 RAISE;
315 END KRS_INTL_TEXT;
316 ------------------------------------------------------------------------------
317 PROCEDURE CND_INTL_TEXT(inRUN_ID IN PLS_INTEGER,
318 COMMIT_SIZE IN PLS_INTEGER,
319 MAX_ERR IN PLS_INTEGER,
320 FAILED IN OUT NOCOPY PLS_INTEGER
321 ) IS
322 -- passing records of this phase
323 CURSOR l_csr_1 IS
324 SELECT DISTINCT orig_sys_ref FROM cz_imp_localized_texts
325 WHERE rec_status IS NULL AND run_id = inRUN_ID AND orig_sys_ref IS NOT NULL
326 AND fsk_devlproject_1_1 IS NOT NULL AND language IS NOT NULL AND source_lang IS NOT NULL;
327
328 -- failing records of this phase
329 CURSOR l_csr_2 IS
330 SELECT DISTINCT orig_sys_ref FROM cz_imp_localized_texts
331 WHERE rec_status IS NULL AND run_id = inRUN_ID
332 AND (orig_sys_ref IS NULL OR language IS NULL OR source_lang IS NULL);
333 -- APC changes AND (orig_sys_ref IS NULL OR fsk_devlproject_1_1 IS NULL OR language IS NULL OR source_lang IS NULL);
334
335
336 TYPE orig_sys_ref_tbl_type IS TABLE OF cz_imp_localized_texts.orig_sys_ref%TYPE INDEX BY BINARY_INTEGER;
337
338 l_orig_sys_ref_tbl orig_sys_ref_tbl_type;
339 x_error BOOLEAN:=FALSE;
340 l_msg VARCHAR2(2000);
341
342 BEGIN
343 OPEN l_csr_1;
344 LOOP
345 l_orig_sys_ref_tbl.DELETE;
346 FETCH l_csr_1 BULK COLLECT INTO l_orig_sys_ref_tbl
347 LIMIT COMMIT_SIZE;
348 EXIT WHEN l_csr_1%NOTFOUND AND l_orig_sys_ref_tbl.COUNT = 0;
349 IF l_orig_sys_ref_tbl.COUNT > 0 THEN
350 FORALL i IN l_orig_sys_ref_tbl.FIRST..l_orig_sys_ref_tbl.LAST
351 UPDATE cz_imp_localized_texts
352 SET deleted_flag = '0'
353 WHERE orig_sys_ref = l_orig_sys_ref_tbl(i)
354 AND run_id = inRUN_ID
355 AND deleted_flag IS NULL;
356 COMMIT;
357 END IF;
358 END LOOP;
359 CLOSE l_csr_1;
360
361 OPEN l_csr_2;
362 LOOP
363 l_msg := CZ_UTILS.GET_TEXT('CZ_IMP_INTLTXT_REQ_COLS');
364 l_orig_sys_ref_tbl.DELETE;
365 FETCH l_csr_2 BULK COLLECT INTO l_orig_sys_ref_tbl
366 LIMIT COMMIT_SIZE;
367 EXIT WHEN l_csr_2%NOTFOUND AND l_orig_sys_ref_tbl.COUNT = 0;
368 IF l_orig_sys_ref_tbl.COUNT > 0 THEN
369 FORALL i IN l_orig_sys_ref_tbl.FIRST..l_orig_sys_ref_tbl.LAST
370 UPDATE cz_imp_localized_texts
371 SET disposition = 'R',
372 rec_status = 'FAIL',
373 message = l_msg
374 WHERE orig_sys_ref = l_orig_sys_ref_tbl(i) AND run_id = inRUN_ID;
375 COMMIT;
376 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_INTLTXT_FAIL',
377 'COUNT' , SQL%ROWCOUNT);
378 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_INTL_TEXT',11276,inRun_Id);
379 FAILED := FAILED + SQL%ROWCOUNT;
380
381 /* Return if MAX_ERR is reached */
382 IF(FAILED >= MAX_ERR) THEN
383 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,
384 'CZ_IMP_PS_NODE.CND_INTL_TEXT',11276,inRun_Id);
385 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
386 END IF;
387 END IF;
388 END LOOP;
389 CLOSE l_csr_2;
390
391 EXCEPTION
392 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
393 IF l_csr_1%ISOPEN THEN CLOSE l_csr_1; END IF;
394 IF l_csr_2%ISOPEN THEN CLOSE l_csr_2; END IF;
395 RAISE;
396 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
397 IF l_csr_1%ISOPEN THEN CLOSE l_csr_1; END IF;
398 IF l_csr_2%ISOPEN THEN CLOSE l_csr_2; END IF;
399 RAISE;
400 WHEN OTHERS THEN
401 IF l_csr_1%ISOPEN THEN CLOSE l_csr_1; END IF;
402 IF l_csr_2%ISOPEN THEN CLOSE l_csr_2; END IF;
403 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.CND_INTL_TEXT',11276,inRun_Id);
404 RAISE;
405 END CND_INTL_TEXT;
406 ------------------------------------------------------------------------------
407 PROCEDURE MAIN_INTL_TEXT(inRUN_ID IN PLS_INTEGER,
408 COMMIT_SIZE IN PLS_INTEGER,
409 MAX_ERR IN PLS_INTEGER,
410 INSERTS IN OUT NOCOPY PLS_INTEGER,
411 UPDATES IN OUT NOCOPY PLS_INTEGER,
412 FAILED IN OUT NOCOPY PLS_INTEGER,
413 DUPS IN OUT NOCOPY PLS_INTEGER,
414 inXFR_GROUP IN VARCHAR2
415 ) IS
416 /* Internal vars */
417 nCommitCount PLS_INTEGER:=0; /* COMMIT buffer index */
418 nErrorCount PLS_INTEGER:=0; /* Error index */
419 nXfrInsertCount PLS_INTEGER:=0; /* Inserts */
420 nXfrUpdateCount PLS_INTEGER:=0; /* Updates */
421 nFailed PLS_INTEGER:=0; /* Failed records */
422 nDups PLS_INTEGER:=0; /* Dupl records */
423 x_error BOOLEAN:=FALSE;
424 dummy CHAR(1);
425
426 st_time number;
427 end_time number;
428 loop_end_time number;
429 insert_end_time number;
430 d_str varchar2(255);
431
432 BEGIN
433
434 BEGIN
435 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
436
437 UPDATE CZ_XFR_RUN_INFOS SET
438 STARTED=SYSDATE,
439 LAST_ACTIVITY=SYSDATE
440 WHERE RUN_ID=inRUN_ID;
441
442 EXCEPTION
443 WHEN NO_DATA_FOUND THEN
444 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
445 VALUES(inRUN_ID,SYSDATE,SYSDATE);
446 WHEN OTHERS THEN
447 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.MAIN_INTL_TEXT:RUNID',11276,inRun_Id);
448 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
449 END;
450
451 if (CZ_IMP_ALL.get_time) then
452 st_time := dbms_utility.get_time();
453 end if;
454
455 CND_INTL_TEXT(inRun_ID,COMMIT_SIZE,MAX_ERR,FAILED);
456
457 if (CZ_IMP_ALL.get_time) then
458 end_time := dbms_utility.get_time();
459 d_str := inRun_id || ' CND intl text :' || (end_time-st_time)/100.00;
460 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'CND',11299,inRun_Id);
461 end if;
462
463 KRS_INTL_TEXT(inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,inXFR_GROUP);
464
465 if (CZ_IMP_ALL.get_time) then
466 end_time := dbms_utility.get_time();
467 d_str := inRun_id || ' KRS intl text :' || (end_time-st_time)/100.00;
468 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'KRS',11299,inRun_Id);
469 end if;
470
471 /* Make sure that the error count has not been reached */
472
473 XFR_INTL_TEXT(inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
474
475 if (CZ_IMP_ALL.get_time) then
476 end_time := dbms_utility.get_time();
477 d_str := inRun_id || ' XFR intl text :' || (end_time-st_time)/100.00;
478 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'XFR',11299,inRun_Id);
479 end if;
480
481 /* Report Insert Errors */
482 IF(nXfrInsertCount<> INSERTS) THEN
483 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IMP_PS_NODE.MAIN_INTL_TEXT:INSERTS ',11276,inRun_Id);
484 END IF;
485 /* Report Update Errors */
486 IF(nXfrUpdateCount<> UPDATES) THEN
487 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IMP_PS_NODE.MAIN_INTL_TEXT:UPDATES ',11276,inRun_Id);
488 END IF;
489 /* Return the transferred number of rows and not the number of rows with keys resolved */
490 INSERTS:=nXfrInsertCount;
491 UPDATES:=nXfrUpdateCount;
492
493 CZ_IMP_PS_NODE.RPT_INTL_TEXT(inRUN_ID);
494
495 END MAIN_INTL_TEXT;
496 ------------------------------------------------------------------------------
497
498 PROCEDURE XFR_INTL_TEXT(inRUN_ID IN PLS_INTEGER,
499 COMMIT_SIZE IN PLS_INTEGER,
500 MAX_ERR IN PLS_INTEGER,
501 INSERTS IN OUT NOCOPY PLS_INTEGER,
502 UPDATES IN OUT NOCOPY PLS_INTEGER,
503 FAILED IN OUT NOCOPY PLS_INTEGER,
504 inXFR_GROUP IN VARCHAR2
505 ) IS
506 CURSOR c_xfr_intl_text IS
507 SELECT intl_text_id, localized_str, language, source_lang, deleted_flag,creation_date
508 last_update_date, created_by, last_updated_by,orig_sys_ref, model_id, disposition,
509 rec_status, ROWID
510 FROM CZ_IMP_LOCALIZED_TEXTS
511 WHERE Run_ID=inRUN_ID AND rec_status = 'PASS';
512
513 x_xfr_intl_text_f BOOLEAN:=FALSE;
514 x_error BOOLEAN:=FALSE;
515 p_xfr_intl_text c_xfr_intl_text%ROWTYPE;
516
517 sIntlTextId CZ_IMP_LOCALIZED_TEXTS.INTL_TEXT_ID%TYPE;
518
519 -- Internal vars --
520 nCommitCount PLS_INTEGER:=0; -- COMMIT buffer index --
521 nInsertCount PLS_INTEGER:=0; -- Inserts --
522 nUpdateCount PLS_INTEGER:=0; -- Updates --
523 nFailed PLS_INTEGER:=0; -- Failed records --
524
525 l_row_id ROWID;
526
527 NOUPDATE_ORIG_SYS_REF NUMBER;
528 NOUPDATE_LOCALIZED_STR NUMBER;
529 NOUPDATE_LANGUAGE NUMBER;
530 NOUPDATE_SOURCE_LANG NUMBER;
531 NOUPDATE_CREATION_DATE NUMBER;
532 NOUPDATE_LAST_UPDATE_DATE NUMBER;
533 NOUPDATE_CREATED_BY NUMBER;
534 NOUPDATE_LAST_UPDATED_BY NUMBER;
535 NOUPDATE_DELETED_FLAG NUMBER;
536
537 -- Make sure that the DataSet exists
538 BEGIN
539 -- Get the Update Flags for each column
540 NOUPDATE_ORIG_SYS_REF := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','ORIG_SYS_REF',inXFR_GROUP);
541 NOUPDATE_LOCALIZED_STR := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','LOCALIZED_STR',inXFR_GROUP);
542 NOUPDATE_LANGUAGE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','LANGUAGE',inXFR_GROUP);
543 NOUPDATE_SOURCE_LANG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','SOURCE_LANG',inXFR_GROUP);
544 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','CREATION_DATE',inXFR_GROUP);
545 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','LAST_UPDATE_DATE',inXFR_GROUP);
546 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','CREATED_BY',inXFR_GROUP);
547 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','LAST_UPDATED_BY',inXFR_GROUP);
548 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_LOCALIZED_TEXTS','DELETED_FLAG',inXFR_GROUP);
549
550 OPEN c_xfr_intl_text;
551 LOOP
552 IF(nCommitCount>= COMMIT_SIZE) THEN
553 BEGIN
554 COMMIT;
555 nCommitCount:=0;
556 END;
557 ELSE
558 nCOmmitCount:=nCommitCount+1;
559 END IF;
560
561 FETCH c_xfr_intl_text INTO p_xfr_intl_text;
562 x_xfr_intl_text_f:=c_xfr_intl_text%FOUND;
563 EXIT WHEN NOT x_xfr_intl_text_f;
564
565 IF(FAILED >= MAX_ERR) THEN
566 ROLLBACK;
567 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_PS_NODE.XFR_INTL_TEXT:MAX',11276,inRun_Id);
568 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
569 END IF;
570
571 IF(p_xfr_intl_text.disposition = 'I') THEN
572 BEGIN
573
574 INSERT INTO cz_localized_texts (intl_text_id, localized_str, language, source_lang, deleted_flag,
575 creation_date, last_update_date, created_by, last_updated_by,orig_sys_ref, model_id)
576 VALUES
577 (p_xfr_intl_text.intl_text_id,
578 p_xfr_intl_text.localized_str,
579 p_xfr_intl_text.language,
580 p_xfr_intl_text.source_lang,
581 p_xfr_intl_text.deleted_flag,
582 sysdate, sysdate, -UID, -UID,p_xfr_intl_text.orig_sys_ref, p_xfr_intl_text.model_id);
583
584 nInsertCount:=nInsertCount+1;
585
586 UPDATE cz_imp_localized_texts
587 SET intl_text_id=p_xfr_intl_text.intl_text_id,
588 REC_STATUS='OK'
589 WHERE ROWID = p_xfr_intl_text.ROWID;
590
591 EXCEPTION
592 WHEN DUP_VAL_ON_INDEX THEN
593 UPDATE cz_imp_localized_texts
594 SET DISPOSITION='R',
595 REC_STATUS='DUPL'
596 WHERE ROWID = p_xfr_intl_text.ROWID;
597 WHEN OTHERS THEN
598 FAILED:=FAILED +1;
599 UPDATE cz_imp_localized_texts
600 SET REC_STATUS='ERR'
601 WHERE ROWID = p_xfr_intl_text.ROWID;
602 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.XFR_INTL_TEXT:INSERT',11276,inRun_Id);
603 END;
604 ELSIF(p_xfr_intl_text.disposition = 'M') THEN
605 BEGIN
606 UPDATE cz_localized_texts SET
607 localized_str=DECODE(NOUPDATE_LOCALIZED_STR,0,p_xfr_intl_text.localized_str,localized_str),
608 deleted_flag=DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_intl_text.deleted_flag,deleted_flag),
609 source_lang=DECODE(NOUPDATE_SOURCE_LANG,0,p_xfr_intl_text.source_lang,source_lang),
610 LAST_UPDATE_DATE=DECODE(NOUPDATE_LAST_UPDATE_DATE,0,sysdate,LAST_UPDATE_DATE),
611 LAST_UPDATED_BY=DECODE(NOUPDATE_LAST_UPDATED_BY,0,-UID,LAST_UPDATED_BY)
612 WHERE intl_text_id=p_xfr_intl_text.intl_text_id
613 AND model_id = p_xfr_intl_text.model_id
614 AND language = p_xfr_intl_text.language;
615
616 IF(SQL%NOTFOUND) THEN
617 FAILED:=FAILED+1;
618 ELSE
619 nUpdateCount:=nUpdateCount+1;
620 UPDATE cz_imp_localized_texts
621 SET REC_STATUS='OK'
622 WHERE ROWID = p_xfr_intl_text.ROWID;
623 END IF;
624
625 EXCEPTION
626 WHEN OTHERS THEN
627 FAILED:=FAILED +1;
628 UPDATE cz_imp_localized_texts
629 SET REC_STATUS='ERR'
630 WHERE ROWID = p_xfr_intl_text.ROWID;
631 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.XFR_INTL_TEXT:UPDATE',11276,inRun_Id);
632 END;
633
634 END IF;
635 END LOOP;
636
637 CLOSE c_xfr_intl_text;
638 COMMIT;
639 INSERTS:=nInsertCount;
640 UPDATES:=nUpdateCount;
641 EXCEPTION
642 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
643 RAISE;
644 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
645 RAISE;
646 WHEN OTHERS THEN
647 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.XFR_INTL_TEXT',11276,inRun_Id);
648 RAISE;
649 END XFR_INTL_TEXT;
650
651 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
652 PROCEDURE RPT_INTL_TEXT ( inRUN_ID IN PLS_INTEGER ) AS
653 x_error BOOLEAN:=FALSE;
654
655 v_table_name VARCHAR2(30) := 'CZ_LOCALIZED_TEXTS';
656 v_ok VARCHAR2(4) := 'OK';
657 v_completed VARCHAR2(1) := '1';
658
659 CURSOR c_xfr_run_result IS
660 SELECT DISPOSITION,REC_STATUS,COUNT(*)
661 FROM cz_imp_localized_texts
662 WHERE RUN_ID = inRUN_ID
663 GROUP BY DISPOSITION,REC_STATUS;
664
665 ins_disposition CZ_XFR_RUN_RESULTS.disposition%TYPE;
666 ins_rec_status CZ_XFR_RUN_RESULTS.rec_status%TYPE ;
667 ins_rec_count CZ_XFR_RUN_RESULTS.records%TYPE ;
668
669 BEGIN
670
671 BEGIN
672 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
673 EXCEPTION
674 WHEN NO_DATA_FOUND THEN NULL;
675 END;
676
677 OPEN c_xfr_run_result;
678 LOOP
679 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
680 EXIT WHEN c_xfr_run_result%NOTFOUND;
681 INSERT INTO CZ_XFR_RUN_RESULTS (RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
682 VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
683 END LOOP;
684 CLOSE c_xfr_run_result;
685 COMMIT;
686
687 DECLARE
688 nErrors PLS_INTEGER;
689 CURSOR c_get_nErrors IS
690 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
691 WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
692 AND IMP_TABLE=v_table_name;
693 BEGIN
694 OPEN c_get_nErrors;
695 FETCH c_get_nErrors INTO nErrors;
696 CLOSE c_get_nErrors;
697 UPDATE CZ_XFR_RUN_INFOS
698 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
699 COMPLETED=v_completed
700 WHERE RUN_ID=inRUN_ID;
701 COMMIT;
702 EXCEPTION
703 WHEN OTHERS THEN
704 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.RPT_INTL_TEXT',11276,inRun_Id);
705 END;
706 EXCEPTION
707 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
708 RAISE;
709 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
710 RAISE;
711 WHEN OTHERS THEN
712 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.RPT_INTL_TEXT',11276,inRun_Id);
713 RAISE;
714 END;
715 /*--INTL_TEXT IMPORT SECTION END--------------------------------------------*/
716
717 /*--DEVL_PROJECT IMPORT SECTION START---------------------------------------*/
718 ------------------------------------------------------------------------------
719 PROCEDURE KRS_DEVL_PROJECT(inRUN_ID IN PLS_INTEGER,
720 COMMIT_SIZE IN PLS_INTEGER,
721 MAX_ERR IN PLS_INTEGER,
722 INSERTS IN OUT NOCOPY PLS_INTEGER,
723 UPDATES IN OUT NOCOPY PLS_INTEGER,
724 FAILED IN OUT NOCOPY PLS_INTEGER,
725 DUPS IN OUT NOCOPY PLS_INTEGER,
726 inXFR_GROUP IN VARCHAR2
727 ) IS
728 CURSOR c_imp_devl_project IS
729 SELECT plan_level,orig_sys_ref,name,fsk_intltext_1_1,organization_id,
730 top_item_id,explosion_type,model_id,model_type, ROWID
731 FROM CZ_IMP_DEVL_PROJECT
732 WHERE rec_status IS NULL AND Run_ID = inRUN_ID
733 ORDER BY model_id,plan_level,ORIG_SYS_REF,NAME,ROWID;
734
735 /* cursor's data found indicator */
736 x_imp_devl_project_f BOOLEAN:=FALSE;
737 x_onl_devl_project_f BOOLEAN:=FALSE;
738 x_onl_intl_text_f BOOLEAN:=FALSE;
739 x_onl_root_f BOOLEAN:=FALSE;
740 x_onl_child_f BOOLEAN:=FALSE;
741 x_error BOOLEAN:=FALSE;
742
743 sOrigSysRef CZ_IMP_DEVL_PROJECT.ORIG_SYS_REF%TYPE;
744 sName CZ_IMP_DEVL_PROJECT.NAME%TYPE;
745 onlName CZ_DEVL_PROJECTS.NAME%TYPE;
746 sFskIntlText11 CZ_IMP_DEVL_PROJECT.FSK_INTLTEXT_1_1%TYPE;
747 nPlanLevel CZ_IMP_DEVL_PROJECT.PLAN_LEVEL%TYPE;
748 nOnlDevlProjectId CZ_IMP_DEVL_PROJECT.DEVL_PROJECT_ID%TYPE;
749 REFRESH_MODEL_ID CZ_IMP_DEVL_PROJECT.DEVL_PROJECT_ID%TYPE;
750 nPersistentProjectId CZ_IMP_DEVL_PROJECT.PERSISTENT_PROJECT_ID%TYPE;
751 nOnlIntlTextId CZ_IMP_DEVL_PROJECT.INTL_TEXT_ID%TYPE;
752 sRecStatus CZ_IMP_DEVL_PROJECT.REC_STATUS%TYPE;
753 sDisposition CZ_IMP_DEVL_PROJECT.DISPOSITION%TYPE;
754 cDeletedFlag CZ_DEVL_PROJECTS.DELETED_FLAG%TYPE;
755 nOrgId CZ_IMP_DEVL_PROJECT.ORGANIZATION_ID%TYPE;
756 nTopId CZ_IMP_DEVL_PROJECT.TOP_ITEM_ID%TYPE;
757 sExplType CZ_IMP_DEVL_PROJECT.EXPLOSION_TYPE%TYPE;
758 nModelId CZ_DEVL_PROJECTS.DEVL_PROJECT_ID%TYPE;
759 sModelId CZ_DEVL_PROJECTS.DEVL_PROJECT_ID%TYPE;
760 nExplId NUMBER;
761 COPY_CHILD_MODELS CZ_XFR_PROJECT_BILLS.COPY_ADDL_CHILD_MODELS%TYPE;
762 nModelType CZ_IMP_DEVL_PROJECT.MODEL_TYPE%TYPE;
763 sModelType CZ_IMP_DEVL_PROJECT.MODEL_TYPE%TYPE;
764
765 /* Internal vars */
766 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
767 nErrorCount PLS_INTEGER:=0; /*Error index */
768 nInsertCount PLS_INTEGER:=0; /*Inserts */
769 nUpdateCount PLS_INTEGER:=0; /*Updates */
770 nFailed PLS_INTEGER:=0; /*Failed records */
771 nDups PLS_INTEGER:=0; /*Dupl records */
772 nAllocateBlock PLS_INTEGER:=1;
773 nAllocateCounter PLS_INTEGER;
774 nNextValue NUMBER;
775 nDummy NUMBER;
776 nInstances NUMBER;
777
778 TYPE tLastFSK1 IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
779 TYPE tLastModel IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
780 sLastFSK1 tLastFSK1;
781 sLastModel tLastModel;
782
783 thisRowId ROWID;
784 l_err_msg VARCHAR2(255);
785
786 v_settings_id VARCHAR2(40);
787 v_section_name VARCHAR2(30);
788 BEGIN
789
790 v_settings_id := 'OracleSequenceIncr';
791 v_section_name := 'SCHEMA';
792
793 BEGIN
794 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
795 WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
796 EXCEPTION
797 WHEN OTHERS THEN
798 nAllocateBlock:=1;
799 END;
800 nAllocateCounter:=nAllocateBlock-1;
801
802 OPEN c_imp_devl_project;
803
804 LOOP
805 /* COMMIT if the buffer size is reached */
806 IF(nCommitCount>= COMMIT_SIZE) THEN
807 BEGIN
808 COMMIT;
809 nCommitCount:=0;
810 END;
811 ELSE
812 nCommitCount:=nCommitCount+1;
813 END IF;
814
815 sOrigSysRef:=NULL; sFskIntlText11:=NULL;
816 FETCH c_imp_devl_project INTO nPlanLevel,sOrigSysRef,sName,sFskIntlText11,
817 nOrgId,nTopId,sExplType,REFRESH_MODEL_ID,nModelType,thisRowId;
818
819 sLastFSK1(sLastFSK1.COUNT + 1) := sOrigSysRef;
820 sLastModel(sLastModel.COUNT + 1) := REFRESH_MODEL_ID;
821
822 x_imp_devl_project_f:=c_imp_devl_project%FOUND;
823 EXIT WHEN NOT x_imp_devl_project_f;
824
825 /*Get the COPY_CHILD_MODELS value*/
826
827 BEGIN
828
829 SELECT NVL(copy_addl_child_models,'0') INTO COPY_CHILD_MODELS
830 FROM cz_xfr_project_bills
831 WHERE model_ps_node_id = REFRESH_MODEL_ID;
832
833 EXCEPTION
834 WHEN OTHERS THEN
835 COPY_CHILD_MODELS := '0';
836 END;
837
838 /* Check the online database */
839 DECLARE
840 CURSOR c_onl_devl_project IS
841 SELECT devl_project_id FROM cz_devl_projects
842 WHERE ORIG_SYS_REF=sOrigSysRef
843 AND DEVL_PROJECT_ID=PERSISTENT_PROJECT_ID
844 AND DELETED_FLAG='0';
845 BEGIN
846 nOnlDevlProjectId := NULL;
847 OPEN c_onl_devl_project;
848 FETCH c_onl_devl_project INTO nOnlDevlProjectId;
849 x_onl_devl_project_f:=c_onl_devl_project%FOUND;
850 CLOSE c_onl_devl_project;
851 nPersistentProjectId := nOnlDevlProjectId;
852
853 IF(REFRESH_MODEL_ID IS NULL OR REFRESH_MODEL_ID < 0)THEN
854 IF(nPlanLevel = 0 OR COPY_CHILD_MODELS = '1')THEN
855 x_onl_devl_project_f := FALSE;
856 END IF;
857 ELSE
858 DECLARE
859 CURSOR c_onl_model_root IS
860 SELECT NULL FROM cz_devl_projects
861 WHERE devl_project_id = REFRESH_MODEL_ID
862 AND deleted_flag = '0';
863 CURSOR c_onl_model_id IS
864 SELECT d.devl_project_id, e.model_ref_expl_id FROM cz_devl_projects d, cz_model_ref_expls e
865 WHERE d.deleted_flag = '0'
866 AND e.deleted_flag = '0'
867 AND d.orig_sys_ref = sOrigSysRef
868 AND e.model_id = REFRESH_MODEL_ID
869 AND d.devl_project_id = e.component_id;
870 BEGIN
871 IF(nPlanLevel = 0)THEN
872 x_onl_root_f := FALSE;
873 OPEN c_onl_model_root;
874 FETCH c_onl_model_root INTO nModelId;
875 x_onl_root_f := c_onl_model_root%FOUND;
876 CLOSE c_onl_model_root;
877 x_onl_devl_project_f := x_onl_root_f;
878 nOnlDevlProjectId := REFRESH_MODEL_ID;
879 ELSE
880 IF(NOT x_onl_root_f)THEN
881 x_onl_devl_project_f := FALSE;
882 ELSE
883 IF(COPY_CHILD_MODELS = '1')THEN
884 x_onl_devl_project_f := FALSE;
885 ELSE
886 nModelId := NULL; nExplId := NULL;
887 OPEN c_onl_model_id;
888 FETCH c_onl_model_id INTO nModelId, nExplId;
889 x_onl_child_f := c_onl_model_id%FOUND;
890 CLOSE c_onl_model_id;
891
892 IF(x_onl_child_f)THEN
893 nOnlDevlProjectId := nModelId;
894 x_onl_devl_project_f := TRUE;
895 END IF;
896 END IF;
897 END IF;
898 END IF;
899 END;
900 END IF;
901 END;
902
903 IF(NOT x_onl_devl_project_f)THEN
904 DECLARE
905 CURSOR c_check IS
906 SELECT null FROM cz_rp_entries rp, cz_devl_projects dv
907 WHERE rp.deleted_flag = '0'
908 AND rp.object_type = 'PRJ'
909 AND rp.name = sName
910 AND dv.deleted_flag = '0'
911 AND dv.orig_sys_ref = sOrigSysRef
912 AND rp.object_id = dv.devl_project_id;
913 BEGIN
914
915 OPEN c_check;
916 FETCH c_check INTO nDummy;
917 IF(c_check%FOUND)THEN
918
919 BEGIN
920 SELECT MAX(cz_utils.conv_num(SUBSTR(rp.name, 7, INSTR(rp.name, ')') - 7))) INTO nDummy
921 FROM cz_rp_entries rp, cz_devl_projects dv
922 WHERE rp.deleted_flag = '0'
923 AND rp.object_type = 'PRJ'
924 AND rp.name like 'Copy (%) of ' || sName
925 AND dv.deleted_flag = '0'
926 AND dv.orig_sys_ref = sOrigSysRef
927 AND rp.object_id = dv.devl_project_id;
928
929 IF(nDummy IS NULL)THEN nDummy := 0; END IF;
930 sName := 'Copy (' || TO_CHAR(nDummy + 1) || ') of ' || sName;
931
932 EXCEPTION
933 WHEN OTHERS THEN
934 NULL;
935 END;
936 END IF;
937 CLOSE c_check;
938 END;
939
940 ELSE
941
942 BEGIN
943 SELECT name, model_type INTO onlName, sModelType
944 FROM cz_devl_projects WHERE devl_project_id = nOnlDevlProjectId;
945
946 IF(SUBSTR(onlName,1,6) = 'Copy (')THEN
947 sName := SUBSTR(onlName,1,INSTR(onlName,') of')+4) || sName;
948 END IF;
949
950 EXCEPTION
951 WHEN OTHERS THEN
952 NULL;
953 END;
954 END IF;
955
956 sRecStatus := NULL;
957 IF(sOrigSysRef IS NULL)THEN
958 sRecStatus:='N7';
959 sDisposition:='R';
960 ELSE
961 IF(sLastFSK1.COUNT > 1)THEN
962 FOR i IN 1..sLastFSK1.COUNT - 1 LOOP
963 IF(sLastFSK1(i) = sOrigSysRef AND sLastModel(i) = REFRESH_MODEL_ID)THEN
964 /* This is a duplicate record */
965 sRecStatus:='DUPL';
966 sDisposition:='R';
967 nDups:=nDups+1;
968 EXIT;
969 END IF;
970 END LOOP;
971 END IF;
972 END IF;
973
974 IF(sRecStatus IS NULL)THEN
975 sRecStatus:='PASS';
976 IF(x_onl_devl_project_f)THEN
977 sDisposition:='M';
978 nUpdateCount:=nUpdateCount+1;
979
980 --If the configuration model type is changed from the 'Container Model', log a warning.
981
982 IF(sModelType = 'N' AND nModelType <> 'N')THEN
983
984 --'The Configuration Model Type for ''%MODELNAME'' has changed from ''Container'' to ''Standard''.'
985 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_CONTAINER_REFRESH', 'MODELNAME', sName), 1, 'CZ_IMP_PS_NODE.KRS_DEVL_PROJECT ', 11276,inRun_Id);
986 cz_imp_all.setReturnCode(cz_imp_all.CONCURRENT_WARNING, CZ_UTILS.GET_TEXT('CZ_IMP_CONTAINER_REFRESH', 'MODELNAME', sName));
987 END IF;
988
989 -- Don't update model_type if it is 'P'
990
991 IF (sModelType = 'P' AND nModelType NOT IN ('P', 'N')) THEN
992
993 CZ_REFS.SolutionBasedModelCheck(nOnlDevlProjectId, nInstances);
994
995 if (nInstances > 0) then
996 begin
997 sModeltype := 'P';
998 /*If the model has multiple instances, then do not refresh this or its parent model */
999 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_CANNOT_REFRESH_PTO', 'MODELNAME', sName), 1, 'CZ_IMP_PS_NODE.KRS_DEVL_PROJECT ', 11276,inRun_Id);
1000 sRecStatus := 'N8';
1001 sDisposition := 'R';
1002 end;
1003 else sModelType := nModelType; end if;
1004 END IF;
1005 ELSE
1006 /* Insert */
1007 sDisposition:='I';
1008 nInsertCount:=nInsertCount+1;
1009 nAllocateCounter:=nAllocateCounter+1;
1010 IF(nAllocateCounter=nAllocateBlock)THEN
1011 nAllocateCounter:=0;
1012 SELECT CZ_PS_NODES_S.NEXTVAL INTO nNextValue FROM DUAL;
1013 END IF;
1014 nOnlDevlProjectId:=nNextValue+nAllocateCounter;
1015 END IF;
1016 END IF;
1017
1018 UPDATE CZ_IMP_DEVL_PROJECT SET
1019 DEVL_PROJECT_ID=DECODE(sDisposition,'R',DEVL_PROJECT_ID,nOnlDevlProjectId),
1020 PERSISTENT_PROJECT_ID=DECODE(sDisposition,'I',NVL(nPersistentProjectId,nOnlDevlProjectId),PERSISTENT_PROJECT_ID),
1021 INTL_TEXT_ID=DECODE(sDisposition,'R',INTL_TEXT_ID,nOnlIntlTextId),
1022 NAME=DECODE(sDisposition,'R',NAME,sName),
1023 DISPOSITION=sDisposition,
1024 REC_STATUS=sRecStatus,
1025 MODEL_TYPE = DECODE(sDisposition,'M',DECODE(sModelType,'P',DECODE(nModelType,'N',nModelType,sModelType),nModelType),nModelType)
1026 WHERE ROWID = thisRowId;
1027
1028 /* If PTO is being changed to an ATO model, reject import of the model's parents and children */
1029 IF(nPlanLevel > 0 AND sDisposition = 'R' AND sRecStatus = 'N8')THEN
1030
1031 UPDATE CZ_IMP_DEVL_PROJECT SET
1032 DISPOSITION=sDisposition,
1033 REC_STATUS=sRecStatus
1034 WHERE MODEL_ID = REFRESH_MODEL_ID;
1035
1036 l_err_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PTOTOATO_DISALLOWED','MODELID', REFRESH_MODEL_ID);
1037 x_error:=CZ_UTILS.LOG_REPORT(l_err_msg,1,'KRS_DEVL_PROJECT:TYPE',11276,inRun_Id);
1038 FAILED:=FAILED+1;
1039 END IF;
1040
1041 IF(sDisposition<>'R')THEN
1042
1043 UPDATE CZ_IMP_PS_NODES SET
1044 DEVL_PROJECT_ID=nOnlDevlProjectId
1045 WHERE fsk_devlproject_5_1 = sOrigSysRef
1046 AND RUN_ID=inRUN_ID;
1047
1048 END IF;
1049
1050 /* Return if MAX_ERR is reached */
1051 IF(FAILED >= MAX_ERR) THEN
1052 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_PS_NODE.KRS_DEVL_PROJECT:MAX',11276,inRun_Id);
1053 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
1054 END IF;
1055
1056 sDisposition:=NULL; sRecStatus:=NULL;
1057
1058 END LOOP;
1059 /* No more data */
1060
1061 CLOSE c_imp_devl_project;
1062 COMMIT;
1063
1064 INSERTS:=nInsertCount;
1065 UPDATES:=nUpdateCount;
1066 DUPS:=nDups;
1067
1068 EXCEPTION
1069 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1070 RAISE;
1071 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
1072 RAISE;
1073 WHEN OTHERS THEN
1074 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.KRS_DEVL_PROJECT',11276,inRun_Id);
1075 RAISE;
1076 END KRS_DEVL_PROJECT;
1077 ------------------------------------------------------------------------------
1078 PROCEDURE CND_DEVL_PROJECT(inRUN_ID IN PLS_INTEGER,
1079 COMMIT_SIZE IN PLS_INTEGER,
1080 MAX_ERR IN PLS_INTEGER,
1081 FAILED IN OUT NOCOPY PLS_INTEGER
1082 ) IS
1083
1084 CURSOR c_imp_devl_project IS
1085 SELECT DELETED_FLAG, bom_caption_rule_id, nonbom_caption_rule_id,
1086 orig_sys_ref, name, model_id, model_type, seeded_flag, ROWID FROM CZ_IMP_DEVL_PROJECT
1087 WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
1088 /* Internal vars */
1089 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
1090 nErrorCount PLS_INTEGER:=0; /*Error index */
1091 nFailed PLS_INTEGER:=0; /*Failed records */
1092 nDups PLS_INTEGER:=0; /*Duplicate records */
1093 x_error BOOLEAN:=FALSE;
1094
1095 /*Cursor Var for Import */
1096 p_imp_devl_project c_imp_devl_project%ROWTYPE;
1097 x_imp_devl_project_f BOOLEAN:=FALSE;
1098 l_msg VARCHAR2(2000);
1099 l_nbr NUMBER;
1100 l_disposition cz_imp_devl_project.disposition%TYPE;
1101 l_rec_status cz_imp_devl_project.rec_status%TYPE;
1102
1103 FUNCTION is_rule_id_valid(p_id NUMBER, p_type NUMBER, p_orig_sys_ref IN VARCHAR2) RETURN BOOLEAN
1104 IS
1105 l_nbr number;
1106 BEGIN
1107 BEGIN
1108 SELECT 1 INTO l_nbr
1109 FROM cz_rules a
1110 WHERE rule_id = p_id
1111 AND rule_type = p_type
1112 AND deleted_flag = '0'
1113 AND (devl_project_id = 0
1114 OR
1115 (devl_project_id <> 0 AND EXISTS
1116 (SELECT 1 FROM cz_devl_projects
1117 WHERE deleted_flag = '0'
1118 AND devl_project_id = a.devl_project_id
1119 AND orig_sys_ref = p_orig_sys_ref)))
1120 AND ROWNUM < 2;
1121
1122 EXCEPTION
1123 WHEN NO_DATA_FOUND THEN
1124 RETURN FALSE;
1125 END;
1126 RETURN TRUE;
1127 END is_rule_id_valid;
1128
1129 BEGIN
1130 OPEN c_imp_devl_project;
1131 LOOP
1132
1133 l_rec_status := NULL;
1134 l_disposition := NULL;
1135
1136 FETCH c_imp_devl_project INTO p_imp_devl_project;
1137 x_imp_devl_project_f:=c_imp_devl_project%FOUND;
1138
1139 EXIT WHEN NOT x_imp_devl_project_f;
1140
1141 IF(FAILED >= MAX_ERR) THEN
1142 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_PS_NODE.CND_DEVL_PROJECT:MAX',11276,inRun_Id);
1143 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
1144 END IF;
1145 -- only seeded models from contracts can be imported
1146 IF (p_imp_devl_project.seeded_flag = '1' AND NOT gContractsModel) THEN
1147 l_rec_status := 'FAIL';
1148 l_disposition := 'R';
1149 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PRJ_SEEDED_FLG',
1150 'MODELNAME', p_imp_devl_project.name,
1151 'MODLELOSR', p_imp_devl_project.orig_sys_ref
1152 );
1153 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_DEVL_PROJECT',11276,inRun_Id);
1154 END IF;
1155 -- validate only for generic import, for BOM we populate during extraction so no need for validation
1156 IF (p_imp_devl_project.bom_caption_rule_id IS NOT NULL AND p_imp_devl_project.model_type NOT IN ('A', 'P', 'N')) THEN
1157 IF NOT (is_rule_id_valid(p_imp_devl_project.bom_caption_rule_id,
1158 CAPTION_RULE_TYPE,
1159 p_imp_devl_project.orig_sys_ref)
1160 OR
1161 is_rule_id_valid(p_imp_devl_project.bom_caption_rule_id,
1162 JAVA_SYS_PROP_RULE_TYPE,
1163 p_imp_devl_project.orig_sys_ref)
1164 ) THEN
1165 l_rec_status := 'FAIL';
1166 l_disposition := 'R';
1167 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PRJ_BOM_CPTN_RULE',
1168 'MODELNAME', p_imp_devl_project.name,
1169 'MODLELOSR', p_imp_devl_project.orig_sys_ref
1170 );
1171 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_DEVL_PROJECT',11276,inRun_Id);
1172 END IF;
1173 END IF;
1174 -- validate only for generic import, for BOM we populate during extraction so no need for validation
1175 IF (p_imp_devl_project.nonbom_caption_rule_id IS NOT NULL AND p_imp_devl_project.model_type NOT IN ('A', 'P', 'N')) THEN
1176 IF NOT (is_rule_id_valid(p_imp_devl_project.nonbom_caption_rule_id,
1177 CAPTION_RULE_TYPE,
1178 p_imp_devl_project.orig_sys_ref)
1179 OR
1180 is_rule_id_valid(p_imp_devl_project.nonbom_caption_rule_id,
1181 JAVA_SYS_PROP_RULE_TYPE,
1182 p_imp_devl_project.orig_sys_ref)
1183 ) THEN
1184 l_rec_status := 'FAIL';
1185 l_disposition := 'R';
1186 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PRJ_NONBOM_CPTN_RULE',
1187 'MODELNAME', p_imp_devl_project.name,
1188 'MODLELOSR', p_imp_devl_project.orig_sys_ref
1189 );
1190 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_DEVL_PROJECT',11276,inRun_Id);
1191 END IF;
1192 END IF;
1193
1194 UPDATE CZ_IMP_DEVL_PROJECT SET
1195 DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG),
1196 SEEDED_FLAG=DECODE(SEEDED_FLAG,NULL,'0',SEEDED_FLAG),
1197 DISPOSITION=l_disposition,
1198 REC_STATUS=l_rec_status
1199 WHERE ROWID = p_imp_devl_project.ROWID;
1200
1201 nCommitCount:=nCommitCount+1;
1202 /* COMMIT if the buffer size is reached */
1203 IF(nCommitCount>= COMMIT_SIZE) THEN
1204 COMMIT;
1205 nCommitCount:=0;
1206 END IF;
1207
1208 END LOOP;
1209 CLOSE c_imp_devl_project;
1210
1211 EXCEPTION
1212 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1213 RAISE;
1214 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
1215 RAISE;
1216 WHEN OTHERS THEN
1217 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.CND_DEVL_PROJECT',11276,inRun_Id);
1218 RAISE;
1219 END CND_DEVL_PROJECT;
1220 ------------------------------------------------------------------------------
1221 PROCEDURE MAIN_DEVL_PROJECT(inRUN_ID IN PLS_INTEGER,
1222 COMMIT_SIZE IN PLS_INTEGER,
1223 MAX_ERR IN PLS_INTEGER,
1224 INSERTS IN OUT NOCOPY PLS_INTEGER,
1225 UPDATES IN OUT NOCOPY PLS_INTEGER,
1226 FAILED IN OUT NOCOPY PLS_INTEGER,
1227 DUPS IN OUT NOCOPY PLS_INTEGER,
1228 inXFR_GROUP IN VARCHAR2,
1229 p_rp_folder_id IN NUMBER
1230 ) IS
1231
1232 /* Internal vars */
1233 nCommitCount PLS_INTEGER:=0; /* COMMIT buffer index */
1234 nErrorCount PLS_INTEGER:=0; /* Error index */
1235 nXfrInsertCount PLS_INTEGER:=0; /* Inserts */
1236 nXfrUpdateCount PLS_INTEGER:=0; /* Updates */
1237 nFailed PLS_INTEGER:=0; /* Failed records */
1238 nDups PLS_INTEGER:=0; /* Dupl records */
1239 x_error BOOLEAN:=FALSE;
1240 dummy CHAR(1);
1241
1242 st_time number;
1243 end_time number;
1244 loop_end_time number;
1245 insert_end_time number;
1246 d_str varchar2(255);
1247
1248 BEGIN
1249
1250 BEGIN
1251 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
1252
1253 UPDATE CZ_XFR_RUN_INFOS SET
1254 STARTED=SYSDATE,
1255 LAST_ACTIVITY=SYSDATE
1256 WHERE RUN_ID=inRUN_ID;
1257
1258 EXCEPTION
1259 WHEN NO_DATA_FOUND THEN
1260 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
1261 VALUES(inRUN_ID,SYSDATE,SYSDATE);
1262 END;
1263
1264 if (CZ_IMP_ALL.get_time) then
1265 st_time := dbms_utility.get_time();
1266 end if;
1267
1268 CND_DEVL_PROJECT(inRun_ID,COMMIT_SIZE,MAX_ERR,FAILED);
1269
1270 if (CZ_IMP_ALL.get_time) then
1271 end_time := dbms_utility.get_time();
1272 d_str := inRun_id || ' CND projects :' || (end_time-st_time)/100.00;
1273 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'CND',11299,inRun_Id);
1274 end if;
1275
1276 if (CZ_IMP_ALL.get_time) then
1277 st_time := dbms_utility.get_time();
1278 end if;
1279
1280 KRS_DEVL_PROJECT(inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,inXFR_GROUP);
1281
1282 if (CZ_IMP_ALL.get_time) then
1283 end_time := dbms_utility.get_time();
1284 d_str := inRun_id || ' KRS projects :' || (end_time-st_time)/100.00;
1285 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'KRS',11299,inRun_Id);
1286 end if;
1287
1288 if (CZ_IMP_ALL.get_time) then
1289 st_time := dbms_utility.get_time();
1290 end if;
1291 XFR_DEVL_PROJECT(inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,
1292 nXfrUpdateCount,FAILED,inXFR_GROUP, p_rp_folder_id);
1293 if (CZ_IMP_ALL.get_time) then
1294 end_time := dbms_utility.get_time();
1295 d_str := inRun_id || ' XFR projects :' || (end_time-st_time)/100.00;
1296 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'XFR',11299,inRun_Id);
1297 end if;
1298
1299 /* Report Insert Errors */
1300 IF(nXfrInsertCount<> INSERTS) THEN
1301 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IMP_PS_NODE.MAIN_DEVL_PROJECT:INSERTS ',11276,inRun_Id);
1302 END IF;
1303 /* Report Update Errors */
1304 IF(nXfrUpdateCount<> UPDATES) THEN
1305 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IMP_PS_NODE.MAIN_DEVL_PROJECT:UPDATES ',11276,inRun_Id);
1306 END IF;
1307
1308 /* Return the transferred number of rows and not the number of rows with keys resolved */
1309 INSERTS:=nXfrInsertCount;
1310 UPDATES:=nXfrUpdateCount;
1311
1312 CZ_IMP_PS_NODE.RPT_DEVL_PROJECT(inRUN_ID);
1313
1314 END MAIN_DEVL_PROJECT;
1315 ------------------------------------------------------------------------------
1316 PROCEDURE XFR_DEVL_PROJECT(inRUN_ID IN PLS_INTEGER,
1317 COMMIT_SIZE IN PLS_INTEGER,
1318 MAX_ERR IN PLS_INTEGER,
1319 INSERTS IN OUT NOCOPY PLS_INTEGER,
1320 UPDATES IN OUT NOCOPY PLS_INTEGER,
1321 FAILED IN OUT NOCOPY PLS_INTEGER,
1322 inXFR_GROUP IN VARCHAR2,
1323 p_rp_folder_id IN NUMBER
1324 ) IS
1325
1326 CURSOR c_xfr_devl_project IS
1327 SELECT * FROM CZ_IMP_DEVL_PROJECT
1328 WHERE Run_ID=inRUN_ID AND rec_status='PASS'
1329 ORDER BY model_id, plan_level;
1330
1331 x_xfr_devl_project_f BOOLEAN:=FALSE;
1332 x_error BOOLEAN:=FALSE;
1333 p_xfr_devl_project c_xfr_devl_project%ROWTYPE;
1334
1335 copy_child_models cz_xfr_project_bills.copy_addl_child_models%TYPE;
1336 server_id cz_xfr_project_bills.source_server%TYPE;
1337
1338 dbModelType cz_devl_projects.model_type%TYPE;
1339
1340 -- Internal vars --
1341 nCommitCount PLS_INTEGER:=0; -- COMMIT buffer index --
1342 nInsertCount PLS_INTEGER:=0; -- Inserts --
1343 nUpdateCount PLS_INTEGER:=0; -- Updates --
1344 nFailed PLS_INTEGER:=0; -- Failed records --
1345
1346 NOUPDATE_NAME NUMBER;
1347 NOUPDATE_VERSION NUMBER;
1348 NOUPDATE_INTL_TEXT_ID NUMBER;
1349 NOUPDATE_CREATION_DATE NUMBER;
1350 NOUPDATE_LAST_UPDATE_DATE NUMBER;
1351 NOUPDATE_CREATED_BY NUMBER;
1352 NOUPDATE_LAST_UPDATED_BY NUMBER;
1353 NOUPDATE_DELETED_FLAG NUMBER;
1354 NOUPDATE_ORIG_SYS_REF NUMBER;
1355 NOUPDATE_DESC_TEXT NUMBER;
1356 NOUPDATE_MODEL_TYPE NUMBER;
1357 NOUPDATE_PRODUCT_KEY NUMBER;
1358 NOUPDATE_ORGANIZATION_ID NUMBER;
1359 NOUPDATE_INVENTORY_ITEM_ID NUMBER;
1360 NOUPDATE_BOM_CPTN_RULE_ID NUMBER;
1361 NOUPDATE_NONBOM_CPTN_RULE_ID NUMBER;
1362 NOUPDATE_BOM_CPTN_TEXT_ID NUMBER;
1363 NOUPDATE_NONBOM_CPTN_TEXT_ID NUMBER;
1364
1365 BEGIN
1366
1367 -- Get the Update Flags for each column
1368 NOUPDATE_NAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','NAME',inXFR_GROUP);
1369 NOUPDATE_VERSION := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','VERSION',inXFR_GROUP);
1370 NOUPDATE_INTL_TEXT_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','INTL_TEXT_ID',inXFR_GROUP);
1371 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','CREATION_DATE',inXFR_GROUP);
1372 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','LAST_UPDATE_DATE',inXFR_GROUP);
1373 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','CREATED_BY',inXFR_GROUP);
1374 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','LAST_UPDATED_BY',inXFR_GROUP);
1375 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','DELETED_FLAG',inXFR_GROUP);
1376 NOUPDATE_ORIG_SYS_REF := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','ORIG_SYS_REF',inXFR_GROUP);
1377 NOUPDATE_DESC_TEXT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','DESC_TEXT',inXFR_GROUP);
1378 NOUPDATE_MODEL_TYPE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','MODEL_TYPE',inXFR_GROUP);
1379 NOUPDATE_INVENTORY_ITEM_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','INVENTORY_ITEM_ID',inXFR_GROUP);
1380 NOUPDATE_ORGANIZATION_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','ORGANIZATION_ID',inXFR_GROUP);
1381 NOUPDATE_PRODUCT_KEY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','PRODUCT_KEY',inXFR_GROUP);
1382 NOUPDATE_BOM_CPTN_RULE_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','BOM_CAPTION_RULE_ID',inXFR_GROUP);
1383 NOUPDATE_NONBOM_CPTN_RULE_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','NONBOM_CAPTION_RULE_ID',inXFR_GROUP);
1384 NOUPDATE_BOM_CPTN_TEXT_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','BOM_CAPTION_TEXT_ID',inXFR_GROUP);
1385 NOUPDATE_NONBOM_CPTN_TEXT_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_DEVL_PROJECTS','NONBOM_CAPTION_TEXT_ID',inXFR_GROUP);
1386
1387 OPEN c_xfr_devl_project;
1388 LOOP
1389 IF(nCommitCount>= COMMIT_SIZE) THEN
1390 BEGIN
1391 COMMIT;
1392 nCommitCount:=0;
1393 END;
1394 ELSE
1395 nCOmmitCount:=nCommitCount+1;
1396 END IF;
1397
1398 FETCH c_xfr_devl_project INTO p_xfr_devl_project;
1399 x_xfr_devl_project_f:=c_xfr_devl_project%FOUND;
1400 EXIT WHEN NOT x_xfr_devl_project_f;
1401
1402 IF(FAILED >= MAX_ERR) THEN
1403 ROLLBACK;
1404 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_PS_NODE.XFR_DEVL_PROJECT:MAX',11276,inRun_Id);
1405 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
1406 END IF;
1407
1408 --
1409 -- check corectness of CZ_IMP_DEVL_PROJECT.config_engine_type
1410 --
1411 IF inXFR_GROUP='GENERIC' THEN
1412 G_CONFIG_ENGINE_TYPE := p_xfr_devl_project.config_engine_type;
1413 IF p_xfr_devl_project.config_engine_type NOT IN('F', 'L') THEN
1414 ROLLBACK;
1415 UPDATE cz_imp_devl_project
1416 SET REC_STATUS='ERR'
1417 WHERE DEVL_PROJECT_ID=p_xfr_devl_project.DEVL_PROJECT_ID AND RUN_ID=inRUN_ID
1418 AND DISPOSITION=p_xfr_devl_project.disposition;
1419
1420 x_error:=CZ_UTILS.LOG_REPORT('Incorrect value of config_engine_type="'||p_xfr_devl_project.config_engine_type||'"',1,'CZ_IMP_PS_NODE.XFR_DEVL_PROJECT:INSERT',11276,inRun_Id);
1421 COMMIT;
1422
1423 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
1424
1425 END IF;
1426
1427 END IF;
1428
1429 IF(p_xfr_devl_project.disposition = 'I') THEN
1430 BEGIN
1431
1432 INSERT INTO cz_devl_projects (devl_project_id, intl_text_id,
1433 name, version, deleted_flag, orig_sys_ref, desc_text,
1434 creation_date, last_update_date, created_by, last_updated_by,
1435 persistent_project_id, model_type, organization_id, inventory_item_id, product_key,
1436 bom_caption_rule_id, nonbom_caption_rule_id, config_engine_type)
1437 VALUES
1438 (p_xfr_devl_project.devl_project_id,
1439 p_xfr_devl_project.intl_text_id,
1440 p_xfr_devl_project.name,
1441 p_xfr_devl_project.version,
1442 p_xfr_devl_project.deleted_flag,
1443 p_xfr_devl_project.orig_sys_ref,
1444 p_xfr_devl_project.desc_text,
1445 sysdate, sysdate, -UID, -UID, p_xfr_devl_project.persistent_project_id,
1446 p_xfr_devl_project.model_type, p_xfr_devl_project.organization_id,
1447 p_xfr_devl_project.inventory_item_id, p_xfr_devl_project.product_key,
1448 p_xfr_devl_project.bom_caption_rule_id, p_xfr_devl_project.nonbom_caption_rule_id, p_xfr_devl_project.config_engine_type);
1449
1450 nInsertCount:=nInsertCount+1;
1451
1452 UPDATE cz_imp_devl_project
1453 SET REC_STATUS='OK'
1454 WHERE DEVL_PROJECT_ID=p_xfr_devl_project.DEVL_PROJECT_ID AND RUN_ID=inRUN_ID
1455 AND DISPOSITION='I';
1456
1457 INSERT INTO CZ_RULE_FOLDERS
1458 (RULE_FOLDER_ID,NAME,TREE_SEQ,DEVL_PROJECT_ID,CREATED_BY,LAST_UPDATED_BY,
1459 CREATION_DATE,LAST_UPDATE_DATE,DELETED_FLAG)
1460 SELECT CZ_RULE_FOLDERS_S.NEXTVAL,p_xfr_devl_project.name||' Rules',0,
1461 p_xfr_devl_project.devl_project_id,UID,UID,sysdate,sysdate,'0'
1462 FROM DUAL WHERE NOT EXISTS
1463 (SELECT 1 FROM CZ_RULE_FOLDERS WHERE
1464 DEVL_PROJECT_ID=p_xfr_devl_project.devl_project_id AND
1465 PARENT_RULE_FOLDER_ID IS NULL AND NAME=p_xfr_devl_project.name||' Rules'
1466 AND deleted_flag = '0');
1467
1468 INSERT INTO CZ_RP_ENTRIES
1469 (OBJECT_TYPE,OBJECT_ID,ENCLOSING_FOLDER,NAME,DESCRIPTION,DELETED_FLAG,SEEDED_FLAG)
1470 SELECT 'PRJ',p_xfr_devl_project.devl_project_id,p_rp_folder_id,
1471 p_xfr_devl_project.name,p_xfr_devl_project.desc_text,'0',p_xfr_devl_project.seeded_flag
1472 FROM DUAL WHERE NOT EXISTS
1473 (SELECT 1 FROM CZ_RP_ENTRIES WHERE deleted_flag = '0' AND (
1474 (OBJECT_TYPE='PRJ' AND OBJECT_ID=p_xfr_devl_project.devl_project_id) OR
1475 (ENCLOSING_FOLDER=p_rp_folder_id AND NAME=p_xfr_devl_project.name)));
1476
1477
1478 IF(p_xfr_devl_project.plan_level = 0)THEN
1479 UPDATE CZ_XFR_PROJECT_BILLS SET
1480 MODEL_PS_NODE_ID=p_xfr_devl_project.devl_project_id,
1481 DESCRIPTION=p_xfr_devl_project.desc_text,
1482 COMPONENT_ITEM_ID=p_xfr_devl_project.top_item_id,
1483 LAST_IMPORT_RUN_ID=inRUN_ID,
1484 LAST_IMPORT_DATE=SYSDATE
1485 WHERE ORGANIZATION_ID=p_xfr_devl_project.ORGANIZATION_ID AND
1486 TOP_ITEM_ID=p_xfr_devl_project.TOP_ITEM_ID AND
1487 EXPLOSION_TYPE=p_xfr_devl_project.EXPLOSION_TYPE AND
1488 MODEL_PS_NODE_ID = p_xfr_devl_project.model_id
1489 RETURNING copy_addl_child_models,source_server INTO copy_child_models, server_id;
1490
1491 ELSE
1492 INSERT INTO cz_xfr_project_bills
1493 (model_ps_node_id, description, component_item_id, last_import_run_id,
1494 last_import_date, organization_id, top_item_id, explosion_type,
1495 copy_addl_child_models, source_server, deleted_flag)
1496 SELECT p_xfr_devl_project.devl_project_id, p_xfr_devl_project.desc_text,
1497 p_xfr_devl_project.top_item_id, inRUN_ID, SYSDATE,
1498 NVL(p_xfr_devl_project.ORGANIZATION_ID, 0), NVL(p_xfr_devl_project.TOP_ITEM_ID, 0),
1499 NVL(p_xfr_devl_project.EXPLOSION_TYPE, 'GENERIC'), '0', NVL(server_id, 0), '0'
1500 FROM DUAL WHERE NOT EXISTS
1501 (SELECT NULL FROM cz_xfr_project_bills
1502 WHERE model_ps_node_id = p_xfr_devl_project.devl_project_id
1503 AND deleted_flag = '0');
1504 END IF;
1505
1506 EXCEPTION
1507 WHEN OTHERS THEN
1508 FAILED:=FAILED +1;
1509 UPDATE cz_imp_devl_project SET REC_STATUS='ERR'
1510 WHERE DEVL_PROJECT_ID=p_xfr_devl_project.DEVL_PROJECT_ID AND RUN_ID=inRUN_ID
1511 AND DISPOSITION='I';
1512 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.XFR_DEVL_PROJECT:INSERT',11276,inRun_Id);
1513 END;
1514
1515 ELSIF(p_xfr_devl_project.disposition = 'M') THEN
1516 BEGIN
1517
1518 UPDATE cz_devl_projects SET
1519 intl_text_id=DECODE(NOUPDATE_INTL_TEXT_ID,0,p_xfr_devl_project.intl_text_id,intl_text_id),
1520 name=DECODE(NOUPDATE_NAME,0,p_xfr_devl_project.name,name),
1521 version=DECODE(NOUPDATE_VERSION,0,p_xfr_devl_project.version,version),
1522 deleted_flag=DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_devl_project.deleted_flag,deleted_flag),
1523 orig_sys_ref=DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_devl_project.orig_sys_ref,orig_sys_ref),
1524 desc_text=DECODE(NOUPDATE_DESC_TEXT,0,p_xfr_devl_project.desc_text,desc_text),
1525 LAST_UPDATE_DATE=DECODE(NOUPDATE_LAST_UPDATE_DATE,0,sysdate,LAST_UPDATE_DATE),
1526 LAST_UPDATED_BY=DECODE(NOUPDATE_LAST_UPDATED_BY,0,-UID,LAST_UPDATED_BY),
1527 MODEL_TYPE = DECODE(NOUPDATE_MODEL_TYPE,0,p_xfr_devl_project.model_type),
1528 organization_id=DECODE(NOUPDATE_ORGANIZATION_ID,0,p_xfr_devl_project.organization_id,organization_id),
1529 inventory_item_id=DECODE(NOUPDATE_INVENTORY_ITEM_ID,0,p_xfr_devl_project.inventory_item_id,inventory_item_id),
1530 product_key= DECODE(NOUPDATE_PRODUCT_KEY,0,p_xfr_devl_project.product_key,product_key),
1531 bom_caption_rule_id= DECODE(NOUPDATE_BOM_CPTN_RULE_ID,0,p_xfr_devl_project.bom_caption_rule_id,bom_caption_rule_id),
1532 nonbom_caption_rule_id= DECODE(NOUPDATE_NONBOM_CPTN_RULE_ID,0,p_xfr_devl_project.nonbom_caption_rule_id,nonbom_caption_rule_id)
1533 WHERE devl_project_id=p_xfr_devl_project.devl_project_id;
1534
1535 IF(SQL%NOTFOUND) THEN
1536 FAILED:=FAILED+1;
1537 ELSE
1538 nUpdateCount:=nUpdateCount+1;
1539 UPDATE cz_imp_devl_project
1540 SET REC_STATUS='OK'
1541 WHERE DEVL_PROJECT_ID=p_xfr_devl_project.DEVL_PROJECT_ID AND RUN_ID=inRUN_ID
1542 AND DISPOSITION='M';
1543 END IF;
1544
1545 UPDATE CZ_RP_ENTRIES SET
1546 NAME = DECODE(NOUPDATE_NAME,0,p_xfr_devl_project.name,name),
1547 DESCRIPTION = DECODE(NOUPDATE_DESC_TEXT,0,p_xfr_devl_project.desc_text,description),
1548 DELETED_FLAG = '0',
1549 SEEDED_FLAG = p_xfr_devl_project.seeded_flag
1550 WHERE OBJECT_TYPE='PRJ' AND OBJECT_ID=p_xfr_devl_project.devl_project_id
1551 AND NOT EXISTS
1552 (SELECT 1 FROM CZ_RP_ENTRIES
1553 WHERE ENCLOSING_FOLDER=0
1554 AND NAME=p_xfr_devl_project.name
1555 AND deleted_flag = '0');
1556 -- dbms_output.put_line ('Updating .. M : ' || p_xfr_devl_project.devl_project_id);
1557
1558 UPDATE CZ_XFR_PROJECT_BILLS SET
1559 DESCRIPTION=DECODE(NOUPDATE_DESC_TEXT,0,p_xfr_devl_project.desc_text,description),
1560 COMPONENT_ITEM_ID=p_xfr_devl_project.top_item_id,
1561 LAST_IMPORT_RUN_ID=inRUN_ID,
1562 LAST_IMPORT_DATE=SYSDATE,
1563 deleted_flag = '0'
1564 WHERE ORGANIZATION_ID=p_xfr_devl_project.ORGANIZATION_ID AND
1565 TOP_ITEM_ID=p_xfr_devl_project.TOP_ITEM_ID AND
1566 EXPLOSION_TYPE=p_xfr_devl_project.EXPLOSION_TYPE AND
1567 MODEL_PS_NODE_ID = p_xfr_devl_project.devl_project_id
1568 RETURNING copy_addl_child_models, source_server INTO copy_child_models, server_id;
1569
1570 --This was necessary because cz_refs won't update explosions when creating new copies of
1571 --child models. However, this brings in problems when refreshing BOM models with
1572 --eventually created rules. As the functionality of creating new copies of child models
1573 --is frozen, we can go without this thus not having problems refreshing with rules.
1574
1575 --DELETE FROM cz_model_ref_expls WHERE model_id = p_xfr_devl_project.devl_project_id
1576 --AND parent_expl_node_id IS NOT NULL;
1577
1578 EXCEPTION
1579 WHEN OTHERS THEN
1580 FAILED:=FAILED +1;
1581
1582 UPDATE cz_imp_devl_project SET REC_STATUS='ERR'
1583 WHERE DEVL_PROJECT_ID=p_xfr_devl_project.DEVL_PROJECT_ID AND RUN_ID=inRUN_ID
1584 AND DISPOSITION='M';
1585 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.XFR_DEVL_PROJECT:UPDATE',11276,inRun_Id);
1586 END ;
1587 END IF;
1588 END LOOP;
1589
1590 CLOSE c_xfr_devl_project;
1591 COMMIT;
1592 INSERTS:=nInsertCount;
1593 UPDATES:=nUpdateCount;
1594 EXCEPTION
1595 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1596 RAISE;
1597 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
1598 RAISE;
1599 WHEN OTHERS THEN
1600 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.XFR_DEVL_PROJECT',11276,inRun_Id);
1601 RAISE;
1602 END XFR_DEVL_PROJECT;
1603
1604 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1605 PROCEDURE RPT_DEVL_PROJECT ( inRUN_ID IN PLS_INTEGER ) AS
1606 x_error BOOLEAN:=FALSE;
1607
1608 v_table_name VARCHAR2(30) := 'CZ_DEVL_PROJECTS';
1609 v_ok VARCHAR2(4) := 'OK';
1610 v_completed VARCHAR2(1) := '1';
1611
1612 CURSOR c_xfr_run_result IS
1613 SELECT DISPOSITION,REC_STATUS,COUNT(*)
1614 FROM cz_imp_devl_project
1615 WHERE RUN_ID = inRUN_ID
1616 GROUP BY DISPOSITION,REC_STATUS;
1617
1618 ins_disposition CZ_XFR_RUN_RESULTS.disposition%TYPE;
1619 ins_rec_status CZ_XFR_RUN_RESULTS.rec_status%TYPE ;
1620 ins_rec_count CZ_XFR_RUN_RESULTS.records%TYPE ;
1621
1622 BEGIN
1623 BEGIN
1624 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
1625 EXCEPTION
1626 WHEN NO_DATA_FOUND THEN NULL;
1627 END;
1628
1629 OPEN c_xfr_run_result;
1630 LOOP
1631 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
1632 EXIT WHEN c_xfr_run_result%NOTFOUND;
1633
1634 INSERT INTO CZ_XFR_RUN_RESULTS (RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
1635 VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
1636 END LOOP;
1637 CLOSE c_xfr_run_result;
1638 COMMIT;
1639
1640 DECLARE
1641 nErrors PLS_INTEGER;
1642 CURSOR c_get_nErrors IS
1643 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
1644 WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
1645 AND IMP_TABLE=v_table_name;
1646 BEGIN
1647 OPEN c_get_nErrors;
1648 FETCH c_get_nErrors INTO nErrors;
1649 CLOSE c_get_nErrors;
1650 UPDATE CZ_XFR_RUN_INFOS
1651 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
1652 COMPLETED=v_completed
1653 WHERE RUN_ID=inRUN_ID;
1654 COMMIT;
1655 EXCEPTION
1656 WHEN OTHERS THEN
1657 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.RPT_DEVL_PROJECT',11276,inRun_Id);
1658 END;
1659 EXCEPTION
1660 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1661 RAISE;
1662 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
1663 RAISE;
1664 WHEN OTHERS THEN
1665 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.RPT_DEVL_PROJECT',11276,inRun_Id);
1666 RAISE;
1667 END;
1668 /*--DEVL_PROJECT IMPORT SECTION END-----------------------------------------*/
1669
1670 /*--PS_NODE IMPORT SECTION START--------------------------------------------*/
1671 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1672 PROCEDURE KRS_PS_NODE (inRUN_ID IN PLS_INTEGER,
1673 COMMIT_SIZE IN PLS_INTEGER,
1674 MAX_ERR IN PLS_INTEGER,
1675 INSERTS IN OUT NOCOPY PLS_INTEGER,
1676 UPDATES IN OUT NOCOPY PLS_INTEGER,
1677 FAILED IN OUT NOCOPY PLS_INTEGER,
1678 DUPS IN OUT NOCOPY PLS_INTEGER,
1679 inXFR_GROUP IN VARCHAR2
1680 ) IS
1681
1682 TYPE tStringArray IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
1683
1684 CURSOR c_imp_psnode (x_usesurr_psnode PLS_INTEGER,
1685 x_usesurr_intltext PLS_INTEGER,
1686 x_usesurr_itemmaster PLS_INTEGER,
1687 x_usesurr_devlproject PLS_INTEGER)
1688 IS
1689 SELECT PLAN_LEVEL,ORIG_SYS_REF,USER_STR03,DEVL_PROJECT_ID,PS_NODE_TYPE,NAME,
1690 FSK_INTLTEXT_1_1,FSK_INTLTEXT_1_EXT,
1691 FSK_ITEMMASTER_2_1,FSK_ITEMMASTER_2_EXT,
1692 FSK_PSNODE_3_1,FSK_PSNODE_3_EXT,
1693 FSK_PSNODE_4_1,FSK_PSNODE_4_EXT,
1694 FSK_DEVLPROJECT_5_1,FSK_DEVLPROJECT_5_EXT,
1695 fsk_psnode_6_1, COMPONENT_SEQUENCE_PATH, ROWID, MINIMUM, MAXIMUM,
1696 nvl(SRC_APPLICATION_ID, cnDefSrcAppId),
1697 nvl(FSK_ITEMMASTER_2_2, cnDefSrcAppId)
1698 FROM CZ_IMP_PS_NODES WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
1699 ORDER BY PLAN_LEVEL,
1700 DECODE(x_usesurr_psnode,0,ORIG_SYS_REF,1,USER_STR03),
1701 DECODE(x_usesurr_intltext,0,FSK_INTLTEXT_1_1,1,FSK_INTLTEXT_1_EXT),
1702 DECODE(x_usesurr_itemmaster,0,FSK_ITEMMASTER_2_1,1,FSK_ITEMMASTER_2_EXT),
1703 DECODE(x_usesurr_psnode,0,FSK_PSNODE_3_1,1,FSK_PSNODE_3_EXT),
1704 DECODE(x_usesurr_devlproject,0,FSK_DEVLPROJECT_5_1,1,FSK_DEVLPROJECT_5_EXT),
1705 ROWID;
1706
1707 /* cursor's data found indicator */
1708 x_imp_psnode_psnodeid_f BOOLEAN:=FALSE;
1709 x_onl_intltext_intltextid_f BOOLEAN:=FALSE;
1710 X_ONL_ITEMMASTER_ITEMID_F BOOLEAN:=FALSE;
1711 x_onl_psnode_parentid_f BOOLEAN:=FALSE;
1712 x_onl_psnode_psnodeid_f BOOLEAN:=FALSE;
1713 x_onl_devlprj_devlprjid_f BOOLEAN:=FALSE;
1714 x_onl_reference_f BOOLEAN:=FALSE;
1715 x_error BOOLEAN:=FALSE;
1716 x_project_f BOOLEAN:=FALSE;
1717 sImpOrigsysref CZ_IMP_PS_NODES.ORIG_SYS_REF%TYPE;
1718 sImpUserstr03 CZ_IMP_PS_NODES.USER_STR03%TYPE;
1719 nOnlPsnodeId CZ_IMP_PS_NODES.PS_NODE_ID%TYPE;
1720 nImpParentId CZ_IMP_PS_NODES.PARENT_ID%TYPE;
1721 nOnlParentId CZ_IMP_PS_NODES.PARENT_ID%TYPE;
1722 nImpPlanLevel CZ_IMP_PS_NODES.PLAN_LEVEL%TYPE;
1723 nOnlItemId CZ_IMP_PS_NODES.ITEM_ID%TYPE;
1724 nReferredItemId CZ_IMP_PS_NODES.ITEM_ID%TYPE;
1725 nOnlIntlTextId CZ_IMP_PS_NODES.INTL_TEXT_ID%TYPE;
1726 nOnlDevlProjectId CZ_IMP_PS_NODES.DEVL_PROJECT_ID%TYPE;
1727 nOnlReference CZ_IMP_PS_NODES.PS_NODE_ID%TYPE;
1728 localName CZ_IMP_PS_NODES.NAME%TYPE;
1729 nImpTreeSeq CZ_IMP_PS_NODES.COMPONENT_SEQUENCE_PATH%TYPE;
1730 sFSKINTLTEXT11 CZ_IMP_PS_NODES.FSK_INTLTEXT_1_1%TYPE;
1731 sFSKINTLTEXT1EXT CZ_IMP_PS_NODES.FSK_INTLTEXT_1_EXT%TYPE;
1732 sFSKITEMMASTER21 CZ_IMP_PS_NODES.FSK_ITEMMASTER_2_1%TYPE;
1733 sFSKITEMMASTER2EXT CZ_IMP_PS_NODES.FSK_ITEMMASTER_2_EXT%TYPE;
1734 nFSKITEMMASTER22 CZ_IMP_PS_NODES.FSK_ITEMMASTER_2_2%TYPE;
1735 nImpSrcApplicationId CZ_IMP_PS_NODES.SRC_APPLICATION_ID%TYPE;
1736 sFSKPSNODE31 CZ_IMP_PS_NODES.FSK_PSNODE_3_1%TYPE;
1737 sFSKPSNODE3EXT CZ_IMP_PS_NODES.FSK_PSNODE_3_EXT%TYPE;
1738 sFSKPSNODE41 CZ_IMP_PS_NODES.FSK_PSNODE_4_1%TYPE;
1739 sFSKPSNODE4EXT CZ_IMP_PS_NODES.FSK_PSNODE_4_EXT%TYPE;
1740 sFSKDEVLPROJECT51 CZ_IMP_PS_NODES.FSK_DEVLPROJECT_5_1%TYPE;
1741 sFSKDEVLPROJECT5EXT CZ_IMP_PS_NODES.FSK_DEVLPROJECT_5_EXT%TYPE;
1742 sFSKREFERENCE CZ_IMP_PS_NODES.fsk_psnode_6_1%TYPE;
1743 sLastFSK CZ_IMP_PS_NODES.NAME%TYPE;
1744 sThisFSK CZ_IMP_PS_NODES.NAME%TYPE;
1745 nLastTreeSeq CZ_IMP_PS_NODES.COMPONENT_SEQUENCE_PATH%TYPE;
1746 nThisTreeSeq CZ_IMP_PS_NODES.COMPONENT_SEQUENCE_PATH%TYPE;
1747 sRecStatus CZ_IMP_PS_NODES.REC_STATUS%TYPE;
1748 sDisposition CZ_IMP_PS_NODES.DISPOSITION%TYPE;
1749 sOnlItemRefPartNbr CZ_IMP_PS_NODES.NAME%TYPE;
1750 nDevlProjectId CZ_IMP_PS_NODES.DEVL_PROJECT_ID%TYPE;
1751 nPsNodeType CZ_IMP_PS_NODES.PS_NODE_TYPE%TYPE;
1752 sPsNodeName CZ_DB_SETTINGS.VALUE%TYPE;
1753 cDeletedFlag CZ_DEVL_PROJECTS.DELETED_FLAG%TYPE;
1754 cDummyDeletedFlag CZ_DEVL_PROJECTS.DELETED_FLAG%TYPE;
1755
1756 sMinimum CZ_IMP_PS_NODES.MINIMUM%TYPE;
1757 sMaximum CZ_IMP_PS_NODES.MAXIMUM%TYPE;
1758 impMinimum CZ_IMP_PS_NODES.MINIMUM%TYPE;
1759 impMaximum CZ_IMP_PS_NODES.MAXIMUM%TYPE;
1760 nModelType CZ_DEVL_PROJECTS.MODEL_TYPE%TYPE;
1761 ibTrackable cz_imp_ps_nodes.ib_trackable%TYPE;
1762
1763 /* Internal vars */
1764 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
1765 nErrorCount PLS_INTEGER:=0; /*Error index */
1766 nInsertCount PLS_INTEGER:=0; /*Inserts */
1767 nUpdateCount PLS_INTEGER:=0; /*Updates */
1768 nFailed PLS_INTEGER:=0; /*Failed records */
1769 nDups PLS_INTEGER:=0; /*Dupl records */
1770 x_usesurr_psnode PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_PS_NODES',inXFR_GROUP);
1771 x_usesurr_intltext PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_INTL_TEXTS',inXFR_GROUP);
1772 x_usesurr_itemmaster PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_ITEM_MASTERS',inXFR_GROUP);
1773 x_usesurr_devlproject PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_DEVL_PROJECTS',inXFR_GROUP);
1774 nAllocateBlock PLS_INTEGER:=1;
1775 nAllocateCounter PLS_INTEGER;
1776 nNextValue NUMBER;
1777 nNextId NUMBER;
1778 NamePrefix VARCHAR2(8);
1779 p_out_err INTEGER;
1780
1781 thisRowId ROWID;
1782
1783 nDebug PLS_INTEGER := 1000;
1784 nCounter PLS_INTEGER;
1785
1786 tabName tStringArray;
1787 tabParentRef tStringArray;
1788
1789
1790 nOnlMaxtreeSeq_forParent CZ_PS_NODES.TREE_SEQ%TYPE;
1791 nNextTreeSeq number;
1792 nSameParentTreeSeq number;
1793 thisParentId number;
1794
1795 v_settings_id VARCHAR2(40);
1796 v_section_name VARCHAR2(30);
1797 BEGIN
1798
1799 nDebug := 1001;
1800
1801 v_settings_id := 'OracleSequenceIncr';
1802 v_section_name := 'SCHEMA';
1803
1804 BEGIN
1805 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
1806 WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
1807 EXCEPTION
1808 WHEN OTHERS THEN
1809 nAllocateBlock:=1;
1810 END;
1811
1812 v_settings_id := 'PsNodeName';
1813 v_section_name := 'ORAAPPS_INTEGRATE';
1814
1815 BEGIN
1816 SELECT VALUE INTO sPsNodeName FROM CZ_DB_SETTINGS
1817 WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
1818 EXCEPTION
1819 WHEN OTHERS THEN
1820 NULL;
1821 END;
1822 nAllocateCounter:=nAllocateBlock-1;
1823
1824 nDebug := 1002;
1825
1826 OPEN c_imp_psnode(x_usesurr_psnode,x_usesurr_intltext,x_usesurr_itemmaster,x_usesurr_devlproject);
1827
1828 nDebug := 1003;
1829
1830 LOOP
1831 nDebug := 1004;
1832 /* COMMIT if the buffer size is reached */
1833 IF (nCommitCount>= COMMIT_SIZE) THEN
1834 BEGIN
1835 COMMIT;
1836 nCommitCount:=0;
1837 END;
1838 ELSE
1839 nCOmmitCount:=nCommitCount+1;
1840 END IF;
1841 nDebug := 1005;
1842 nImpPlanLevel:=NULL; sImpUserstr03:=NULL; sImpOrigsysref:=NULL;
1843 nDevlProjectId:=NULL; sFSKINTLTEXT11:=NULL; sFSKINTLTEXT1EXT:=NULL;
1844 sFSKITEMMASTER21:=NULL; sFSKITEMMASTER2EXT:=NULL;
1845 sFSKPSNODE31:=NULL; sFSKPSNODE3EXT:=NULL; localName := NULL;
1846 sFSKPSNODE41:=NULL; sFSKPSNODE4EXT:=NULL; nPsNodeType := NULL;
1847 sFSKDEVLPROJECT51:=NULL; sFSKDEVLPROJECT5EXT:=NULL; sFSKREFERENCE := NULL;
1848 nImpTreeSeq := NULL;nImpSrcApplicationId:=NULL; nFSKITEMMASTER22:=NULL;
1849 FETCH c_imp_psnode INTO
1850 nImpPlanLevel,sImpOrigsysref,sImpUserstr03,nDevlProjectId, nPsNodeType, localName,
1851 sFSKINTLTEXT11,sFSKINTLTEXT1EXT,sFSKITEMMASTER21,sFSKITEMMASTER2EXT,
1852 sFSKPSNODE31,sFSKPSNODE3EXT,sFSKPSNODE41,sFSKPSNODE4EXT,
1853 sFSKDEVLPROJECT51,sFSKDEVLPROJECT5EXT,sFSKREFERENCE,nImpTreeSeq, thisRowId,
1854 impMinimum, impMaximum, nImpSrcApplicationId, nFSKITEMMASTER22;
1855 IF(x_usesurr_psnode=1) THEN
1856 sThisFSK:=sImpUserStr03;
1857 ELSE
1858 sThisFSK:=sImpOrigsysref;
1859 END IF;
1860 nThisTreeSeq := nImpTreeSeq;
1861 nDebug := 1006;
1862 x_imp_psnode_psnodeid_f:=c_imp_psnode%FOUND;
1863 EXIT WHEN NOT x_imp_psnode_psnodeid_f;
1864
1865 /* Check that the Online data exists for the 2 FSK */
1866 DECLARE
1867 CURSOR c_onl_itemmaster IS
1868 SELECT ITEM_ID,REF_PART_NBR FROM CZ_ITEM_MASTERS
1869 WHERE ORIG_SYS_REF=sFSKITEMMASTER21 AND deleted_flag = '0'
1870 AND SRC_APPLICATION_ID=nFSKITEMMASTER22;
1871 CURSOR c_onl_itemmaster_usesurr IS
1872 SELECT ITEM_ID,REF_PART_NBR FROM CZ_ITEM_MASTERS
1873 WHERE ORIG_SYS_REF=sFSKITEMMASTER2EXT AND deleted_flag = '0'
1874 AND SRC_APPLICATION_ID=nFSKITEMMASTER22;
1875 BEGIN
1876 nDebug := 1009;
1877 nonlitemid:=NULL; sOnlItemRefPartNbr:=NULL;
1878 IF( x_usesurr_itemmaster = 0 ) THEN
1879 OPEN c_onl_itemmaster;
1880 FETCH c_onl_itemmaster INTO nonlitemid,sOnlItemRefPartNbr;
1881 x_onl_itemmaster_itemid_f:=c_onl_itemmaster%FOUND;
1882 CLOSE c_onl_itemmaster;
1883 ELSE
1884 OPEN c_onl_itemmaster_usesurr;
1885 FETCH c_onl_itemmaster_usesurr INTO nonlitemid,sOnlItemRefPartNbr;
1886 x_onl_itemmaster_itemid_f:=c_onl_itemmaster_usesurr%FOUND;
1887 CLOSE c_onl_itemmaster_usesurr;
1888 END IF;
1889 nDebug := 1010;
1890 END;
1891
1892 /* Check that the Online data exists for the 3 FSK */
1893 DECLARE
1894 CURSOR c_onl_psnode_parentid IS
1895 SELECT PS_NODE_ID, PLAN_LEVEL FROM CZ_IMP_PS_NODES WHERE ORIG_SYS_REF=DECODE(x_usesurr_psnode, 0, sFSKPSNODE31, 1, sFSKPSNODE3EXT)
1896 AND DEVL_PROJECT_ID=nDevlProjectId AND RUN_ID=inRUN_ID
1897 AND ps_node_id IS NOT NULL
1898 AND src_application_id = nImpSrcApplicationId
1899 AND NVL(COMPONENT_SEQUENCE_PATH, -1) = NVL(SUBSTR(nImpTreeSeq, 1, INSTR(nImpTreeSeq, '-', -1, 1) - 1), -1);
1900 BEGIN
1901 nDebug := 1011;
1902 OPEN c_onl_psnode_parentid;
1903 nImpparentid:=NULL;
1904 FETCH c_onl_psnode_parentid INTO nImpparentid, nImpPlanLevel;
1905 x_onl_psnode_parentid_f:=c_onl_psnode_parentid%FOUND;
1906 CLOSE c_onl_psnode_parentid;
1907 nDebug := 1012;
1908 END;
1909
1910 /* Check the PSNODE Data from Online Dbase */
1911 DECLARE
1912 CURSOR c_onl_psnode IS
1913 SELECT PS_NODE_ID,PARENT_ID,NAME FROM CZ_PS_NODES
1914 WHERE ORIG_SYS_REF=DECODE(x_usesurr_psnode,0, sImpOrigsysref, 1, sImpUserstr03)
1915 AND DEVL_PROJECT_ID = nDevlProjectId
1916 AND NVL(COMPONENT_SEQUENCE_PATH, -1) = NVL(nImpTreeSeq, -1)
1917 AND deleted_flag = '0'
1918 AND src_application_id = nImpSrcApplicationId;
1919 BEGIN
1920 nDebug := 1013;
1921 OPEN c_onl_psnode;
1922 nOnlPsnodeId:=NULL;
1923 FETCH c_onl_psnode INTO nOnlPsnodeId,nOnlParentId,localName;
1924 x_onl_psnode_psnodeId_f:=c_onl_psnode%FOUND;
1925 CLOSE c_onl_psnode;
1926 nDebug := 1014;
1927 END;
1928
1929 IF(nPsNodeType = cnReference)THEN
1930 DECLARE
1931 CURSOR c_onl_reference IS
1932 SELECT PS_NODE_ID, devl_project_id, item_id, ib_trackable FROM CZ_IMP_PS_NODES
1933 WHERE ORIG_SYS_REF = sFSKREFERENCE
1934 AND RUN_ID=inRun_ID
1935 AND ps_node_id IS NOT NULL;
1936 CURSOR c_model_type IS
1937 SELECT model_type FROM cz_imp_devl_project
1938 WHERE devl_project_id = nDevlProjectId
1939 AND RUN_ID=inRun_ID;
1940 CURSOR c_onl_name IS
1941 SELECT name FROM cz_imp_devl_project
1942 WHERE devl_project_id = nOnlDevlProjectId
1943 AND RUN_ID=inRun_ID;
1944 CURSOR c_onl_refpartnbr IS
1945 SELECT ref_part_nbr FROM cz_item_masters
1946 WHERE item_id = nReferredItemId;
1947 CURSOR c_reference IS
1948 SELECT p.PS_NODE_ID, p.devl_project_id, p.item_id FROM CZ_PS_NODES p, CZ_DEVL_PROJECTS d
1949 WHERE p.ORIG_SYS_REF = sFSKREFERENCE
1950 AND p.ps_node_id = p.persistent_node_id
1951 AND p.deleted_flag = '0'
1952 AND p.ps_node_id = d.devl_project_id
1953 AND d.deleted_flag = '0';
1954
1955 BEGIN
1956 nDebug := 1015;
1957 OPEN c_onl_reference;
1958 nOnlReference := NULL;
1959 FETCH c_onl_reference INTO nOnlReference, nOnlDevlProjectId, nReferredItemId, ibTrackable;
1960 x_onl_reference_f := c_onl_reference%FOUND;
1961 CLOSE c_onl_reference;
1962 nDebug := 1016;
1963 IF(x_onl_reference_f)THEN
1964
1965 OPEN c_model_type;
1966 FETCH c_model_type INTO nModelType;
1967 CLOSE c_model_type;
1968
1969 IF(sPsNodeName = 'DESCRIPTION')THEN
1970 OPEN c_onl_name;
1971 FETCH c_onl_name INTO localName;
1972 CLOSE c_onl_name;
1973 ELSE
1974 OPEN c_onl_refpartnbr;
1975 FETCH c_onl_refpartnbr INTO localName;
1976 CLOSE c_onl_refpartnbr;
1977 END IF;
1978
1979 tabName(tabName.COUNT + 1) := localName;
1980 tabParentRef(tabParentRef.COUNT + 1) := sFSKPSNODE31;
1981
1982 nCounter := 0;
1983
1984 FOR i IN 1..tabName.COUNT LOOP
1985 IF(tabName(i) = localName AND tabParentRef(i) = sFSKPSNODE31)THEN
1986 nCounter := nCounter + 1;
1987 END IF;
1988 END LOOP;
1989
1990 IF(nCounter > 1)THEN
1991 localName := localName || ' (' || TO_CHAR(nCounter) || ')';
1992 END IF;
1993
1994 -- Stellar bug
1995 ELSE
1996 -- get reference_id, name into localName
1997 -- nOnlReference := cz_imp_single.childModelExists(inRun_Id,inOrgId,inTopId,inExplType);
1998
1999 OPEN c_reference;
2000 nOnlReference := NULL;
2001 FETCH c_reference INTO nOnlReference, nOnlDevlProjectId, nReferredItemId;
2002 x_onl_reference_f := c_reference%FOUND;
2003 CLOSE c_reference;
2004
2005 tabName(tabName.COUNT + 1) := localName;
2006 tabParentRef(tabParentRef.COUNT + 1) := sFSKPSNODE31;
2007
2008 nCounter := 0;
2009
2010 FOR i IN 1..tabName.COUNT LOOP
2011 IF(tabName(i) = localName AND tabParentRef(i) = sFSKPSNODE31)THEN
2012 nCounter := nCounter + 1;
2013 END IF;
2014 END LOOP;
2015
2016 IF(nCounter > 1)THEN
2017 localName := localName || ' (' || TO_CHAR(nCounter) || ')';
2018 END IF;
2019 -- Stellar bug end
2020 END IF;
2021 END;
2022 ELSE
2023 x_onl_reference_f := TRUE;
2024 END IF;
2025
2026 nDebug := 1017;
2027
2028 IF((NOT x_onl_reference_f OR (nPsNodeType = cnReference AND sFSKREFERENCE IS NULL)) OR
2029 (NOT x_onl_itemmaster_itemid_f AND ((x_usesurr_itemmaster=0 AND sFSKITEMMASTER21 IS NOT NULL) OR
2030 (x_usesurr_itemmaster=1 AND sFSKITEMMASTER2EXT IS NOT NULL))
2031 AND nImpPlanLevel>1) OR
2032 (NOT x_onl_psnode_parentid_f AND nImpPlanLevel<>0) OR
2033 (x_usesurr_psnode=1 AND sImpUserstr03 IS NULL) OR
2034 (x_usesurr_psnode=0 AND sImpOrigsysref IS NULL) OR
2035 nDevlProjectId IS NULL
2036 /*OR (NOT x_onl_devlprj_devlprjid_f) OR
2037 (x_usesurr_devlproject=0 AND sFSKDEVLPROJECT51 IS NULL) OR
2038 (x_usesurr_devlproject=1 AND sFSKDEVLPROJECT5EXT IS NULL)*/) THEN
2039 BEGIN
2040 FAILED:=FAILED+1;
2041 IF(x_usesurr_psnode=1 AND sImpUserstr03 IS NULL) THEN
2042 sRecStatus:='N35';
2043 ELSIF(x_usesurr_psnode=0 AND sImpOrigsysref IS NULL ) THEN
2044 sRecStatus:='N9';
2045 ELSIF(nDevlProjectId IS NULL) THEN
2046 sRecStatus:='N3';
2047 ELSIF(NOT x_onl_itemmaster_itemid_f AND x_usesurr_itemmaster=1 AND sFSKITEMMASTER2EXT IS NOT NULL) THEN
2048 sRecStatus:='F47';
2049 ELSIF(NOT x_onl_itemmaster_itemid_f AND x_usesurr_itemmaster=0 AND sFSKITEMMASTER21 IS NOT NULL) THEN
2050 sRecStatus:='F46';
2051 ELSIF(NOT x_onl_psnode_parentid_f AND nImpPlanLevel<>0 AND x_usesurr_psnode=1 AND sFSKPSNODE3EXT IS NULL) THEN
2052 sRecStatus:='N49';
2053 ELSIF(NOT x_onl_psnode_parentid_f AND nImpPlanLevel<>0 AND x_usesurr_psnode=1) THEN
2054 sRecStatus:='F49';
2055 ELSIF(NOT x_onl_psnode_parentid_f AND nImpPlanLevel<>0 AND x_usesurr_psnode=0 AND sFSKPSNODE31 IS NULL) THEN
2056 sRecStatus:='N48';
2057 ELSIF(NOT x_onl_psnode_parentid_f AND nImpPlanLevel<>0 AND x_usesurr_psnode=0) THEN
2058 sRecStatus:='F48';
2059 ELSIF(NOT x_onl_reference_f AND sFSKREFERENCE IS NULL)THEN
2060 sRecStatus:='N52';
2061 ELSIF(NOT x_onl_reference_f AND sFSKREFERENCE IS NOT NULL)THEN
2062 sRecStatus:='F52';
2063 ELSE
2064 sRecStatus:='XXX';
2065 END IF;
2066 sDisposition:='R';
2067 END;
2068 ELSE
2069 nDebug := 1018;
2070 /* Insert or update */
2071 BEGIN
2072 IF((sLastFSK IS NOT NULL AND sLastFSK=sThisFSK) AND
2073 ((nLastTreeSeq IS NULL AND nPsNodeType = bomModel) OR
2074 (nLastTreeSeq IS NOT NULL AND nLastTreeSeq = nThisTreeSeq))) THEN
2075 /* This is a duplicate record */
2076 sRecStatus:='DUPL';
2077 sDisposition:='R';
2078 nDups:=nDups+1;
2079 ELSE
2080 nDebug := 1019;
2081 BEGIN
2082 sRecStatus:='PASS';
2083
2084 NamePrefix := NULL;
2085
2086 IF(x_onl_psnode_psnodeid_f)THEN
2087 nDebug := 1020;
2088 /* We cannot recreate references because we currently have no mechanism
2089 to update model_ref_expl_id of rules' participants
2090
2091 IF(nPsNodeType = cnReference)THEN
2092
2093 UPDATE cz_ps_nodes SET deleted_flag='1' WHERE ps_node_id = nOnlPsnodeId;
2094 cz_refs.delete_Node(nOnlPsnodeId, cnReference, p_out_err, '1');
2095 sDisposition:='I';
2096 nInsertCount:=nInsertCount+1;
2097 nAllocateCounter:=nAllocateCounter+1;
2098 IF(nAllocateCounter=nAllocateBlock)THEN
2099 nAllocateCounter:=0;
2100 SELECT CZ_PS_NODES_S.NEXTVAL INTO nNextValue FROM DUAL;
2101 END IF;
2102 nNextId := nNextValue+nAllocateCounter;
2103 nDebug := 1021;
2104 ELSE
2105 */
2106 sDisposition:='M';
2107 nUpdateCount:=nUpdateCount+1;
2108
2109 --END IF;
2110
2111 ELSE
2112 /*Insert */
2113 nDebug := 1022;
2114 sDisposition:='I';
2115 nInsertCount:=nInsertCount+1;
2116
2117 IF(nPsNodeType NOT IN (cnModel, bomModel) AND
2118 (nPsNodeType NOT IN (cnProduct, cnComponent) OR nImpParentId IS NOT NULL))THEN
2119
2120 nAllocateCounter:=nAllocateCounter+1;
2121 IF(nAllocateCounter=nAllocateBlock)THEN
2122 nAllocateCounter:=0;
2123 SELECT CZ_PS_NODES_S.NEXTVAL INTO nNextValue FROM DUAL;
2124 END IF;
2125 nNextId := nNextValue+nAllocateCounter;
2126 nDebug := 1023;
2127 ELSE
2128
2129 nNextId := nDevlProjectId;
2130 END IF;
2131 END IF;
2132 END;
2133 END IF;
2134 END;
2135 END IF;
2136 nDebug := 1024;
2137
2138 -- If the reference model is trackable and if its immediate parent BOM
2139 -- is a Container model, set its min/max or 0/N on insert. Don't update on refresh.
2140
2141 IF(nPsNodeType = cnReference AND ibTrackable = '1' AND nModelType = 'N')THEN
2142
2143 IF(sDisposition = 'M')THEN
2144 SELECT nvl(MINIMUM,0), nvl(MAXIMUM,-1) INTO sMinimum, sMaximum
2145 FROM CZ_PS_NODES
2146 WHERE PS_NODE_ID = nOnlPsnodeId
2147 AND DELETED_FLAG = '0';
2148 ELSE
2149 sMinimum := 0;
2150 sMaximum := -1;
2151 END IF;
2152 END IF;
2153
2154 -- Don't update min/max for references in a model (PTO)
2155 -- For an existing reference, get min/max values from cz_ps_nodes
2156
2157 DECLARE
2158 CURSOR c_node IS
2159 SELECT model_type
2160 FROM CZ_DEVL_PROJECTS
2161 WHERE DEVL_PROJECT_ID IN (SELECT PARENT_ID FROM CZ_PS_NODES
2162 WHERE PS_NODE_ID = nOnlPsnodeId
2163 AND DELETED_FLAG = '0')
2164 AND DELETED_FLAG = '0';
2165 BEGIN
2166 x_project_f:=false;
2167 IF (nPsNodeType = cnReference) THEN
2168
2169 IF(sDisposition = 'M')THEN
2170 BEGIN
2171 OPEN c_node;
2172 FETCH c_node INTO nModelType;
2173 x_project_f:=c_node%FOUND;
2174 CLOSE c_node;
2175 END;
2176 END IF;
2177
2178 --Bug #2804225. Decided to make import never restore the number of instances,
2179 --therefore removing all the conditions on model_type. nModelType can be equal
2180 --to 'C' for generic import and in this case we want to use the values from
2181 --the import table.
2182
2183 IF (x_project_f AND (nModelType <> 'C')) THEN
2184 SELECT nvl(MINIMUM,1), nvl(MAXIMUM,1) INTO sMinimum, sMaximum
2185 FROM CZ_PS_NODES
2186 WHERE PS_NODE_ID = nOnlPsnodeId
2187 AND DELETED_FLAG = '0';
2188 ELSE
2189 sMinimum := impMinimum;
2190 sMaximum := impMaximum;
2191 END IF;
2192 END IF;
2193 EXCEPTION
2194 WHEN OTHERS THEN null;
2195 END;
2196
2197 /* This Code is added to Preserve the user modified tree_seq in a mixed tree bug # 3495030*/
2198 nOnlMaxtreeSeq_forParent := NULL;
2199 nNextTreeSeq := NULL;
2200 IF (sDisposition = 'I' ) THEN
2201 IF (nvl(thisParentid,0) <> nImpparentid ) THEN
2202 BEGIN
2203 thisParentid := nImpparentid ;
2204
2205 select max(tree_seq) into nOnlMaxtreeSeq_forParent
2206 from cz_ps_nodes
2207 where parent_id = nImpparentid
2208 and deleted_flag = '0';
2209
2210 nNextTreeSeq := nOnlMaxtreeSeq_forParent + 1;
2211 nSameParentTreeSeq := nNextTreeSeq;
2212
2213 EXCEPTION
2214 WHEN OTHERS THEN null;
2215
2216
2217 END;
2218 ELSE
2219 nNextTreeSeq := nSameParentTreeSeq +1;
2220 END IF;
2221 END IF;
2222
2223 /* End Fix for bug # 3495030 */
2224
2225 UPDATE CZ_IMP_PS_NODES SET
2226 ORIG_SYS_REF=DECODE(x_usesurr_psnode,1,DECODE(sDISPOSITION,NULL,sImpUserStr03,ORIG_SYS_REF), ORIG_SYS_REF),
2227 PS_NODE_ID=DECODE(sDISPOSITION,'R',PS_NODE_ID,'I',
2228 DECODE(PS_NODE_TYPE,bomModel,DEVL_PROJECT_ID,cnModel,DEVL_PROJECT_ID,nNextId),nOnlPsnodeId),
2229 ITEM_ID=DECODE(sDISPOSITION,'R',ITEM_ID,nonlitemid),
2230 NAME=DECODE(inXFR_GROUP,'GENERIC',NAME,
2231 NamePrefix || DECODE(sDISPOSITION,'R',NAME,
2232 DECODE(nPsNodeType,cnReference,DECODE(localName,NULL,NAME,localName),
2233 DECODE(sOnlItemRefPartNbr,NULL,NAME,
2234 DECODE(sPsNodeName,'DESCRIPTION',NAME,sOnlItemRefPartNbr))))),
2235 PARENT_ID=DECODE(sDISPOSITION,'R',PARENT_ID,nImpparentid),
2236 REFERENCE_ID=DECODE(sDISPOSITION,'R',REFERENCE_ID,DECODE(PS_NODE_TYPE,cnReference,nOnlReference,REFERENCE_ID)),
2237 MINIMUM = DECODE(nPsNodeType,cnReference,sMinimum, MINIMUM),
2238 MAXIMUM = DECODE(nPsNodeType,cnReference,sMaximum, MAXIMUM),
2239 --------------------bug3495030
2240 TREE_SEQ = DECODE(sDISPOSITION,'I',nvl(nNextTreeSeq,TREE_SEQ),TREE_SEQ),
2241 DISPOSITION=sDisposition,
2242 REC_STATUS=sRecStatus
2243 WHERE ROWID = thisRowId;
2244
2245 nDebug := 1025;
2246 IF(x_usesurr_psnode=1) THEN
2247 sLastFSK:=sImpUserStr03;
2248 ELSE
2249 sLastFSK:=sImpOrigsysref;
2250 END IF;
2251 nLastTreeSeq := nThisTreeSeq;
2252 nDebug := 1026;
2253 /* Return if MAX_ERR is reached */
2254 IF (FAILED >= MAX_ERR) THEN
2255 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_PS_NODE.KRS_PS_NODE',11276,inRun_Id);
2256 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
2257 END IF;
2258 sDisposition:=NULL; sRecStatus:=NULL;
2259
2260 END LOOP;
2261 /* No more data */
2262
2263 CLOSE c_imp_psnode;
2264 COMMIT;
2265 nDebug := 1027;
2266 INSERTS:=nInsertCount;
2267 UPDATES:=nUpdateCount;
2268 DUPS:=nDups;
2269
2270 EXCEPTION
2271 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
2272 RAISE;
2273 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
2274 RAISE;
2275 WHEN OTHERS THEN
2276 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.KRS_PS_NODE',nDebug,inRun_ID);
2277 RAISE;
2278 END KRS_PS_NODE;
2279
2280 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
2281
2282 PROCEDURE CND_PS_NODE(inRUN_ID IN PLS_INTEGER,
2283 COMMIT_SIZE IN PLS_INTEGER,
2284 MAX_ERR IN PLS_INTEGER,
2285 FAILED IN OUT NOCOPY PLS_INTEGER
2286 ) IS
2287
2288 CURSOR c_imp_psnode IS
2289 SELECT DELETED_FLAG, SYSTEM_NODE_FLAG, SRC_APPLICATION_ID, ORIG_SYS_REF,
2290 PS_NODE_TYPE, MINIMUM, MAXIMUM, INSTANTIABLE_FLAG, NAME, FSK_DEVLPROJECT_5_1,
2291 FSK_PSNODE_3_1, FSK_PSNODE_3_EXT, REFERENCE_ID, INITIAL_NUM_VALUE, DECIMAL_QTY_FLAG,
2292 MINIMUM_SELECTED, MAXIMUM_SELECTED, UI_OMIT, DISPLAY_IN_SUMMARY_FLAG, ROWID
2293 FROM CZ_IMP_PS_NODES
2294 WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
2295 /* Internal vars */
2296 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
2297 nErrorCount PLS_INTEGER:=0; /*Error index */
2298 nFailed PLS_INTEGER:=0; /*Failed records */
2299 nDups PLS_INTEGER:=0; /*Dupl records */
2300 x_error BOOLEAN:=FALSE;
2301
2302
2303 /*Cursor Var for Import */
2304 p_imp_psnode c_imp_psnode%ROWTYPE;
2305 x_imp_psnode_f BOOLEAN:=FALSE;
2306 l_disposition cz_imp_ps_nodes.disposition%TYPE;
2307 l_rec_status cz_imp_ps_nodes.rec_status%TYPE;
2308
2309 sInstantiableFlag cz_ps_nodes.instantiable_flag%type;
2310 l_msg VARCHAR2(255);
2311 l_model_name cz_devl_projects.name%type;
2312 l_debug NUMBER;
2313 l_minimum cz_imp_ps_nodes.minimum%TYPE;
2314 l_maximum cz_imp_ps_nodes.maximum%TYPE;
2315 l_minimum_selected cz_imp_ps_nodes.minimum_selected%TYPE;
2316 l_maximum_selected cz_imp_ps_nodes.maximum_selected%TYPE;
2317
2318 BEGIN
2319
2320 --
2321 -- All dups are rejected because we don't know which to accept, GENERIC IMPORT ONLY
2322 --
2323
2324 UPDATE cz_imp_ps_nodes a
2325 SET disposition = 'R', rec_status = 'DUP'
2326 WHERE run_id = inRun_ID
2327 AND rec_status IS NULL
2328 AND EXISTS (SELECT count(*), orig_sys_ref,fsk_devlproject_5_1,src_application_id
2329 FROM cz_imp_ps_nodes
2330 WHERE run_id = a.run_id
2331 AND rec_status IS NULL
2332 AND orig_sys_ref = a.orig_sys_ref
2333 AND fsk_devlproject_5_1 = a.fsk_devlproject_5_1
2334 AND src_application_id = a.src_application_id
2335 AND src_application_id <> 702
2336 GROUP BY orig_sys_ref, fsk_devlproject_5_1, src_application_id
2337 HAVING count(*) > 1);
2338
2339 IF (SQL%ROWCOUNT > 0 ) THEN
2340 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PSNODE_DUPS', 'PSNODES', SQL%ROWCOUNT);
2341 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_PS_NODE',11276,inRun_Id);
2342 END IF;
2343
2344 OPEN c_imp_psnode;
2345 LOOP
2346 FETCH c_imp_psnode INTO p_imp_psnode;
2347 x_imp_psnode_f:=c_imp_psnode%FOUND;
2348
2349 EXIT WHEN NOT x_imp_psnode_f;
2350
2351 IF(FAILED >= MAX_ERR) THEN
2352 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_PS_NODE.CND_PS_NODE',11276,inRun_Id);
2353 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
2354 END IF;
2355
2356 l_disposition := NULL;
2357 l_rec_status := NULL;
2358 l_minimum_selected := p_imp_psnode.minimum_selected;
2359 l_maximum_selected := p_imp_psnode.maximum_selected;
2360
2361 -- validate min and max for root nodes
2362 IF (p_imp_psnode.FSK_PSNODE_3_1 IS NULL AND p_imp_psnode.FSK_PSNODE_3_EXT IS NULL
2363 AND p_imp_psnode.PS_NODE_TYPE <> bomModel) THEN
2364 IF (p_imp_psnode.MINIMUM IS NOT NULL AND p_imp_psnode.MINIMUM <> 1) OR
2365 (p_imp_psnode.MAXIMUM IS NOT NULL AND p_imp_psnode.MAXIMUM <> 1) THEN
2366
2367 l_disposition:='R';
2368
2369 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PSNODE_ROOT_MINMAX',
2370 'MODELNAME', p_imp_psnode.FSK_DEVLPROJECT_5_1,
2371 'OSR', p_imp_psnode.ORIG_SYS_REF
2372 );
2373 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_PS_NODE',11276,inRun_Id);
2374 END IF;
2375 IF p_imp_psnode.minimum IS NULL THEN
2376 l_minimum:=1;
2377 ELSE
2378 l_minimum:=p_imp_psnode.minimum;
2379 END IF;
2380 IF p_imp_psnode.maximum IS NULL THEN
2381 l_maximum:=1;
2382 ELSE
2383 l_maximum:=p_imp_psnode.maximum;
2384 END IF;
2385
2386 -- ui_omit flag cannot be '1' for root nodes
2387
2388 IF p_imp_psnode.ui_omit = '1' THEN
2389
2390 l_disposition:='R';
2391
2392 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PSNODE_ROOT_UI_OMIT',
2393 'MODELNAME', p_imp_psnode.FSK_DEVLPROJECT_5_1,
2394 'OSR', p_imp_psnode.ORIG_SYS_REF
2395 );
2396 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_PS_NODE',11276,inRun_Id);
2397 END IF;
2398
2399 ELSE
2400 l_minimum:=p_imp_psnode.minimum;
2401 l_maximum:=p_imp_psnode.maximum;
2402 END IF;
2403
2404 -- validate min/max and initial values for BOM standard items and BOM option calsses,
2405 -- and convert to integers if decimal
2406
2407 IF (p_imp_psnode.PS_NODE_TYPE IN (bomStandard, bomOptionClass)) THEN
2408 IF (p_imp_psnode.DECIMAL_QTY_FLAG = '0') THEN
2409 l_minimum := CEIL(p_imp_psnode.minimum);
2410 IF (p_imp_psnode.maximum <> -1) THEN
2411 l_maximum := FLOOR(p_imp_psnode.maximum);
2412 ELSE
2413 l_maximum := p_imp_psnode.maximum;
2414 END IF;
2415
2416 IF (p_imp_psnode.initial_num_value IS NOT NULL AND
2417 MOD(p_imp_psnode.initial_num_value,FLOOR(p_imp_psnode.initial_num_value)) <> 0) THEN
2418 l_disposition := 'R';
2419 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PSNODE_INV_INIT_VALUE',
2420 'MODELNAME', p_imp_psnode.FSK_DEVLPROJECT_5_1,
2421 'OSR', p_imp_psnode.ORIG_SYS_REF
2422 );
2423 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_PS_NODE',11276,inRun_Id);
2424 END IF;
2425
2426 IF (l_minimum > l_maximum AND l_maximum <> -1) THEN
2427 l_disposition := 'R';
2428 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PSNODE_INV_MINMAX',
2429 'MODELNAME', p_imp_psnode.FSK_DEVLPROJECT_5_1,
2430 'OSR', p_imp_psnode.ORIG_SYS_REF
2431 );
2432 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_PS_NODE',11276,inRun_Id);
2433 END IF;
2434 END IF;
2435 END IF;
2436
2437 -- validate min/max and initial values for references,
2438 -- and convert to integers if decimal
2439
2440 IF (p_imp_psnode.PS_NODE_TYPE = cnReference) THEN
2441 IF (p_imp_psnode.DECIMAL_QTY_FLAG = '0') THEN
2442 l_minimum_selected := CEIL(p_imp_psnode.minimum_selected);
2443 IF (p_imp_psnode.maximum_selected <> -1) THEN
2444 l_maximum_selected := FLOOR(p_imp_psnode.maximum_selected);
2445 ELSE
2446 l_maximum_selected := p_imp_psnode.maximum_selected;
2447 END IF;
2448
2449 IF (p_imp_psnode.initial_num_value IS NOT NULL AND
2450 MOD(p_imp_psnode.initial_num_value,FLOOR(p_imp_psnode.initial_num_value)) <> 0) THEN
2451 l_disposition := 'R';
2452 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PSNODE_INV_INIT_VALUE',
2453 'MODELNAME', p_imp_psnode.FSK_DEVLPROJECT_5_1,
2454 'OSR', p_imp_psnode.ORIG_SYS_REF
2455 );
2456 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_PS_NODE',11276,inRun_Id);
2457 END IF;
2458
2459 IF (l_minimum_selected > l_maximum_selected AND l_maximum_selected <> -1) THEN
2460 l_disposition := 'R';
2461 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PSNODE_INV_MINMAX',
2462 'MODELNAME', p_imp_psnode.FSK_DEVLPROJECT_5_1,
2463 'OSR', p_imp_psnode.ORIG_SYS_REF
2464 );
2465 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_PS_NODE',11276,inRun_Id);
2466 END IF;
2467 END IF;
2468 END IF;
2469
2470 IF p_imp_psnode.PS_NODE_TYPE NOT IN (cnComponent,cnFeature,cnOption,cnReference,cnConnector,
2471 cnTotal,cnResource,bomModel,bomOptionClass,bomStandard) THEN
2472 l_disposition := 'R';
2473
2474 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PSNODE_TYPE_INVALID',
2475 'MODELNAME', p_imp_psnode.FSK_DEVLPROJECT_5_1,
2476 'OSR', p_imp_psnode.ORIG_SYS_REF
2477 );
2478 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_PS_NODE',11276,inRun_Id);
2479 END IF;
2480
2481 IF(p_imp_psnode.NAME IS NULL) THEN
2482
2483 l_disposition:='R';
2484
2485 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PSNODE_NAME_IS_NULL',
2486 'MODELNAME', p_imp_psnode.FSK_DEVLPROJECT_5_1,
2487 'OSR', p_imp_psnode.ORIG_SYS_REF
2488 );
2489 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_PS_NODE',11276,inRun_Id);
2490 END IF;
2491
2492 IF(p_imp_psnode.PS_NODE_TYPE NOT IN (cnReference,cnConnector) AND
2493 p_imp_psnode.REFERENCE_ID IS NOT NULL) THEN
2494
2495 l_disposition := 'R';
2496
2497 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PSNODE_REFID_NULL',
2498 'MODELNAME', p_imp_psnode.FSK_DEVLPROJECT_5_1,
2499 'OSR', p_imp_psnode.ORIG_SYS_REF
2500 );
2501 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_PS_NODE',11276,inRun_Id);
2502 END IF;
2503
2504 IF(p_imp_psnode.INSTANTIABLE_FLAG IS NULL) THEN
2505 sInstantiableFlag := NULL;
2506 IF(p_imp_psnode.PS_NODE_TYPE = bomModel)THEN
2507 sInstantiableFlag := '2';
2508 ELSIF(p_imp_psnode.PS_NODE_TYPE IN (cnReference, cnComponent, cnProduct)) THEN
2509 IF(p_imp_psnode.MINIMUM = 1 AND p_imp_psnode.MAXIMUM = 1)THEN
2510 sInstantiableFlag := '2';
2511 ELSIF(p_imp_psnode.MINIMUM = 0 AND p_imp_psnode.MAXIMUM = 1)THEN
2512 sInstantiableFlag := '1';
2513 ELSE
2514 sInstantiableFlag := '4';
2515 END IF;
2516 END IF;
2517 ELSE
2518 IF(p_imp_psnode.PS_NODE_TYPE IN (cnReference, cnComponent, cnProduct)) THEN
2519 IF (p_imp_psnode.MINIMUM = 1 AND p_imp_psnode.MAXIMUM = 1 AND p_imp_psnode.INSTANTIABLE_FLAG <> '2') THEN
2520 l_disposition := 'R';
2521 ELSIF(p_imp_psnode.MINIMUM = 0 AND p_imp_psnode.MAXIMUM = 1 AND p_imp_psnode.INSTANTIABLE_FLAG <> '1') THEN
2522 l_disposition := 'R';
2523 END IF;
2524 ELSIF(p_imp_psnode.PS_NODE_TYPE = bomModel AND p_imp_psnode.INSTANTIABLE_FLAG <> '2') THEN
2525 l_disposition := 'R';
2526 END IF;
2527
2528 IF (l_disposition = 'R') THEN
2529 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PSNODE_INSTFLAG',
2530 'MODELNAME', p_imp_psnode.FSK_DEVLPROJECT_5_1,
2531 'OSR', p_imp_psnode.ORIG_SYS_REF
2532 );
2533 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_PS_NODE',11276,inRun_Id);
2534 END IF;
2535 END IF;
2536
2537 IF (p_imp_psnode.DISPLAY_IN_SUMMARY_FLAG NOT IN (NULL,'1')) THEN
2538 l_disposition := 'R';
2539 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_PSNODE_SUMRY_FLAG',
2540 'MODELNAME', p_imp_psnode.FSK_DEVLPROJECT_5_1,
2541 'OSR', p_imp_psnode.ORIG_SYS_REF
2542 );
2543 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_PS_NODE.CND_PS_NODE',11276,inRun_Id);
2544 END IF;
2545
2546 IF l_disposition='R' THEN
2547 FAILED:=FAILED+1;
2548 l_rec_status:='FAIL';
2549 END IF;
2550
2551 UPDATE cz_imp_ps_nodes SET
2552 deleted_flag=DECODE(deleted_flag,NULL,'0',deleted_flag),
2553 system_node_flag=DECODE(system_node_flag,NULL,'0',SYSTEM_NODE_FLAG),
2554 instantiable_flag=DECODE(instantiable_flag,NULL,sInstantiableFlag,instantiable_flag),
2555 src_application_id=DECODE(src_application_id,NULL,cnDefSrcAppId,src_application_id),
2556 minimum=l_minimum,
2557 maximum=l_maximum,
2558 minimum_selected=l_minimum_selected,
2559 maximum_selected=l_maximum_selected,
2560 disposition=l_disposition,
2561 rec_status=l_rec_status
2562 WHERE ROWID = p_imp_psnode.ROWID;
2563 nCOmmitCount:=nCommitCount+1;
2564 /* COMMIT if the buffer size is reached */
2565 IF (nCommitCount>= COMMIT_SIZE) THEN
2566 COMMIT;
2567 nCommitCount:=0;
2568 END IF;
2569
2570
2571 END LOOP;
2572 CLOSE c_imp_psnode;
2573
2574 EXCEPTION
2575 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
2576 RAISE;
2577 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
2578 RAISE;
2579 WHEN OTHERS THEN
2580 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.CND_PS_NODE',11276,inRun_Id);
2581 RAISE;
2582 END CND_PS_NODE ;
2583
2584 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
2585 PROCEDURE MAIN_PS_NODE (inRUN_ID IN PLS_INTEGER,
2586 COMMIT_SIZE IN PLS_INTEGER,
2587 MAX_ERR IN PLS_INTEGER,
2588 INSERTS IN OUT NOCOPY PLS_INTEGER,
2589 UPDATES IN OUT NOCOPY PLS_INTEGER,
2590 FAILED IN OUT NOCOPY PLS_INTEGER,
2591 DUPS IN OUT NOCOPY PLS_INTEGER,
2592 inXFR_GROUP IN VARCHAR2
2593 ) IS
2594
2595 /* Internal vars */
2596 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
2597 nErrorCount PLS_INTEGER:=0; /*Error index */
2598 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
2599 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
2600 nFailed PLS_INTEGER:=0; /*Failed records */
2601 nDups PLS_INTEGER:=0; /*Dupl records */
2602 x_error BOOLEAN:=FALSE;
2603 dummy CHAR(1);
2604
2605 st_time number;
2606 end_time number;
2607 loop_end_time number;
2608 insert_end_time number;
2609 d_str varchar2(255);
2610
2611 BEGIN
2612
2613 BEGIN
2614 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
2615
2616 UPDATE CZ_XFR_RUN_INFOS SET
2617 STARTED=SYSDATE,
2618 LAST_ACTIVITY=SYSDATE
2619 WHERE RUN_ID=inRUN_ID;
2620
2621 EXCEPTION
2622 WHEN NO_DATA_FOUND THEN
2623 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
2624 VALUES(inRUN_ID,SYSDATE,SYSDATE);
2625 WHEN OTHERS THEN
2626 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.MAIN_PS_NODE',11276,inRun_Id);
2627 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
2628 END;
2629
2630 if (CZ_IMP_ALL.get_time) then
2631 st_time := dbms_utility.get_time();
2632 end if;
2633
2634 CND_PS_NODE (inRun_ID,COMMIT_SIZE,MAX_ERR,FAILED);
2635
2636 if (CZ_IMP_ALL.get_time) then
2637 end_time := dbms_utility.get_time();
2638 d_str := inRun_id || ' CND ps :' || (end_time-st_time)/100.00;
2639 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'CND',11299,inRun_Id);
2640 end if;
2641
2642 KRS_PS_NODE (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,inXFR_GROUP);
2643
2644 if (CZ_IMP_ALL.get_time) then
2645 end_time := dbms_utility.get_time();
2646 d_str := inRun_id || ' KRS ps :' || (end_time-st_time)/100.00;
2647 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'KRS',11299,inRun_Id);
2648 end if;
2649
2650 XFR_PS_NODE (inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
2651
2652 if (CZ_IMP_ALL.get_time) then
2653 end_time := dbms_utility.get_time();
2654 d_str := inRun_id || ' XFR ps :' || (end_time-st_time)/100.00;
2655 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'XFR',11299,inRun_Id);
2656 end if;
2657
2658 /* Report Insert Errors */
2659 IF (nXfrInsertCount<> INSERTS) THEN
2660 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'IMP_IM_PS_NODE.MAIN_PS_NODE',11276,inRun_Id);
2661 END IF;
2662
2663 /* Report Update Errors */
2664 IF (nXfrUpdateCount<> UPDATES) THEN
2665 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'IMP_IM_PS_NODE.MAIN_PS_NODE',11276,inRun_Id);
2666 END IF;
2667
2668 /* Return the transferred number of rows and not the number of rows with keys resolved*/
2669 INSERTS:=nXfrInsertCount;
2670 UPDATES:=nXfrUpdateCount;
2671
2672 CZ_IMP_PS_NODE.RPT_PS_NODE(inRUN_ID);
2673
2674 END MAIN_PS_NODE ;
2675
2676 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
2677
2678 PROCEDURE XFR_PS_NODE (inRUN_ID IN PLS_INTEGER,
2679 COMMIT_SIZE IN PLS_INTEGER,
2680 MAX_ERR IN PLS_INTEGER,
2681 INSERTS IN OUT NOCOPY PLS_INTEGER,
2682 UPDATES IN OUT NOCOPY PLS_INTEGER,
2683 FAILED IN OUT NOCOPY PLS_INTEGER,
2684 inXFR_GROUP IN VARCHAR2
2685 ) IS
2686
2687 TYPE tPsNodeId IS TABLE OF cz_imp_ps_nodes.ps_node_id%TYPE INDEX BY BINARY_INTEGER;
2688 TYPE tDevlProjectId IS TABLE OF cz_imp_ps_nodes.devl_project_id%TYPE INDEX BY BINARY_INTEGER;
2689 TYPE tFromPopulatorId IS TABLE OF cz_imp_ps_nodes.from_populator_id%TYPE INDEX BY BINARY_INTEGER;
2690 TYPE tPropertyBackptr IS TABLE OF cz_imp_ps_nodes.property_backptr%TYPE INDEX BY BINARY_INTEGER;
2691 TYPE tItemTypeBackptr IS TABLE OF cz_imp_ps_nodes.item_type_backptr%TYPE INDEX BY BINARY_INTEGER;
2692 TYPE tIntlTextId IS TABLE OF cz_imp_ps_nodes.intl_text_id%TYPE INDEX BY BINARY_INTEGER;
2693 TYPE tSubConsId IS TABLE OF cz_imp_ps_nodes.sub_cons_id%TYPE INDEX BY BINARY_INTEGER;
2694 TYPE tItemId IS TABLE OF cz_imp_ps_nodes.item_id%TYPE INDEX BY BINARY_INTEGER;
2695 TYPE tName IS TABLE OF cz_imp_ps_nodes.name%TYPE INDEX BY BINARY_INTEGER;
2696 TYPE tResourceFlag IS TABLE OF cz_imp_ps_nodes.resource_flag%TYPE INDEX BY BINARY_INTEGER;
2697 TYPE tInitialValue IS TABLE OF cz_imp_ps_nodes.initial_value%TYPE INDEX BY BINARY_INTEGER;
2698 TYPE tInitialNumValue IS TABLE OF cz_imp_ps_nodes.initial_num_value%TYPE INDEX BY BINARY_INTEGER;
2699 TYPE tParentId IS TABLE OF cz_imp_ps_nodes.parent_id%TYPE INDEX BY BINARY_INTEGER;
2700 TYPE tMinimum IS TABLE OF cz_imp_ps_nodes.minimum%TYPE INDEX BY BINARY_INTEGER;
2701 TYPE tMaximum IS TABLE OF cz_imp_ps_nodes.maximum%TYPE INDEX BY BINARY_INTEGER;
2702 TYPE tPsNodeType IS TABLE OF cz_imp_ps_nodes.ps_node_type%TYPE INDEX BY BINARY_INTEGER;
2703 TYPE tFeatureType IS TABLE OF cz_imp_ps_nodes.feature_type%TYPE INDEX BY BINARY_INTEGER;
2704 TYPE tProductFlag IS TABLE OF cz_imp_ps_nodes.product_flag%TYPE INDEX BY BINARY_INTEGER;
2705 TYPE tReferenceId IS TABLE OF cz_imp_ps_nodes.reference_id%TYPE INDEX BY BINARY_INTEGER;
2706 TYPE tMultiConfigFlag IS TABLE OF cz_imp_ps_nodes.multi_config_flag%TYPE INDEX BY BINARY_INTEGER;
2707 TYPE tOrderSeqFlag IS TABLE OF cz_imp_ps_nodes.order_seq_flag%TYPE INDEX BY BINARY_INTEGER;
2708 TYPE tSystemNodeFlag IS TABLE OF cz_imp_ps_nodes.system_node_flag%TYPE INDEX BY BINARY_INTEGER;
2709 TYPE tTreeSeq IS TABLE OF cz_imp_ps_nodes.tree_seq%TYPE INDEX BY BINARY_INTEGER;
2710 TYPE tCountedOptionsFlag IS TABLE OF cz_imp_ps_nodes.counted_options_flag%TYPE INDEX BY BINARY_INTEGER;
2711 TYPE tUiOmit IS TABLE OF cz_imp_ps_nodes.ui_omit%TYPE INDEX BY BINARY_INTEGER;
2712 TYPE tUiSection IS TABLE OF cz_imp_ps_nodes.ui_section%TYPE INDEX BY BINARY_INTEGER;
2713 TYPE tBomTreatment IS TABLE OF cz_imp_ps_nodes.bom_treatment%TYPE INDEX BY BINARY_INTEGER;
2714 TYPE tOrigSysRef IS TABLE OF cz_imp_ps_nodes.orig_sys_ref%TYPE INDEX BY BINARY_INTEGER;
2715 TYPE tCheckoutUser IS TABLE OF cz_imp_ps_nodes.checkout_user%TYPE INDEX BY BINARY_INTEGER;
2716 TYPE tDisposition IS TABLE OF cz_imp_ps_nodes.disposition%TYPE INDEX BY BINARY_INTEGER;
2717 TYPE tRecStatus IS TABLE OF cz_imp_ps_nodes.rec_status%TYPE INDEX BY BINARY_INTEGER;
2718 TYPE tDeletedFlag IS TABLE OF cz_imp_ps_nodes.deleted_flag%TYPE INDEX BY BINARY_INTEGER;
2719 TYPE tEffectiveFrom IS TABLE OF cz_imp_ps_nodes.effective_from%TYPE INDEX BY BINARY_INTEGER;
2720 TYPE tEffectiveUntil IS TABLE OF cz_imp_ps_nodes.effective_until%TYPE INDEX BY BINARY_INTEGER;
2721 TYPE tEffectiveUsageMask IS TABLE OF cz_imp_ps_nodes.EFFECTIVE_USAGE_MASK%TYPE INDEX BY BINARY_INTEGER;
2722 TYPE tUserStr01 IS TABLE OF cz_imp_ps_nodes.USER_STR01%TYPE INDEX BY BINARY_INTEGER;
2723 TYPE tUserStr02 IS TABLE OF cz_imp_ps_nodes.USER_STR02%TYPE INDEX BY BINARY_INTEGER;
2724 TYPE tUserStr03 IS TABLE OF cz_imp_ps_nodes.USER_STR03%TYPE INDEX BY BINARY_INTEGER;
2725 TYPE tUserStr04 IS TABLE OF cz_imp_ps_nodes.USER_STR04%TYPE INDEX BY BINARY_INTEGER;
2726 TYPE tUserNum01 IS TABLE OF cz_imp_ps_nodes.USER_NUM01%TYPE INDEX BY BINARY_INTEGER;
2727 TYPE tUserNum02 IS TABLE OF cz_imp_ps_nodes.USER_NUM02%TYPE INDEX BY BINARY_INTEGER;
2728 TYPE tUserNum03 IS TABLE OF cz_imp_ps_nodes.USER_NUM03%TYPE INDEX BY BINARY_INTEGER;
2729 TYPE tUserNum04 IS TABLE OF cz_imp_ps_nodes.USER_NUM04%TYPE INDEX BY BINARY_INTEGER;
2730 TYPE tCreationDate IS TABLE OF cz_imp_ps_nodes.CREATION_DATE%TYPE INDEX BY BINARY_INTEGER;
2731 TYPE tLastUpdateDate IS TABLE OF cz_imp_ps_nodes.LAST_UPDATE_DATE%TYPE INDEX BY BINARY_INTEGER;
2732 TYPE tCreatedBy IS TABLE OF cz_imp_ps_nodes.CREATED_BY%TYPE INDEX BY BINARY_INTEGER;
2733 TYPE tLastUpdatedBy IS TABLE OF cz_imp_ps_nodes.LAST_UPDATED_BY%TYPE INDEX BY BINARY_INTEGER;
2734 TYPE tSecurityMask IS TABLE OF cz_imp_ps_nodes.SECURITY_MASK%TYPE INDEX BY BINARY_INTEGER;
2735 TYPE tPlanLevel IS TABLE OF cz_imp_ps_nodes.PLAN_LEVEL%TYPE INDEX BY BINARY_INTEGER;
2736 TYPE tSoItemTypeCode IS TABLE OF cz_imp_ps_nodes.SO_ITEM_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
2737 TYPE tMinimumSelected IS TABLE OF cz_imp_ps_nodes.MINIMUM_SELECTED%TYPE INDEX BY BINARY_INTEGER;
2738 TYPE tMaximumSelected IS TABLE OF cz_imp_ps_nodes.MAXIMUM_SELECTED%TYPE INDEX BY BINARY_INTEGER;
2739 TYPE tBomRequired IS TABLE OF cz_imp_ps_nodes.BOM_REQUIRED%TYPE INDEX BY BINARY_INTEGER;
2740 TYPE tComponentSequenceId IS TABLE OF cz_imp_ps_nodes.COMPONENT_SEQUENCE_ID%TYPE INDEX BY BINARY_INTEGER;
2741 TYPE tOrganizationId IS TABLE OF cz_imp_ps_nodes.ORGANIZATION_ID%TYPE INDEX BY BINARY_INTEGER;
2742 TYPE tTopItemId IS TABLE OF cz_imp_ps_nodes.TOP_ITEM_ID%TYPE INDEX BY BINARY_INTEGER;
2743 TYPE tExplosionType IS TABLE OF cz_imp_ps_nodes.EXPLOSION_TYPE%TYPE INDEX BY BINARY_INTEGER;
2744 TYPE tDecimalQtyFlag IS TABLE OF cz_imp_ps_nodes.DECIMAL_QTY_FLAG%TYPE INDEX BY BINARY_INTEGER;
2745 TYPE tInstantiableFlag IS TABLE OF cz_imp_ps_nodes.INSTANTIABLE_FLAG%TYPE INDEX BY BINARY_INTEGER;
2746 TYPE tQuoteableFlag IS TABLE OF cz_imp_ps_nodes.QUOTEABLE_FLAG%TYPE INDEX BY BINARY_INTEGER;
2747 TYPE tPrimaryUomCode IS TABLE OF cz_imp_ps_nodes.PRIMARY_UOM_CODE%TYPE INDEX BY BINARY_INTEGER;
2748 TYPE tBomSortOrder IS TABLE OF cz_imp_ps_nodes.BOM_SORT_ORDER%TYPE INDEX BY BINARY_INTEGER;
2749 TYPE tComponentSequencePath IS TABLE OF cz_imp_ps_nodes.COMPONENT_SEQUENCE_PATH%TYPE INDEX BY BINARY_INTEGER;
2750 TYPE tIbTrackable IS TABLE OF cz_imp_ps_nodes.IB_TRACKABLE%TYPE INDEX BY BINARY_INTEGER;
2751 TYPE tSrcApplicationId IS TABLE OF cz_imp_ps_nodes.SRC_APPLICATION_ID%TYPE INDEX BY BINARY_INTEGER;
2752 TYPE tChar_tbl IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
2753 TYPE tModelType IS TABLE OF cz_imp_devl_project.MODEL_TYPE%TYPE INDEX BY BINARY_INTEGER;
2754 TYPE tDisplayInSummaryFlag IS TABLE OF cz_imp_ps_nodes.DISPLAY_IN_SUMMARY_FLAG%TYPE INDEX BY BINARY_INTEGER;
2755 TYPE tIBLinkItemFlag IS TABLE OF cz_imp_ps_nodes.IB_LINK_ITEM_FLAG%TYPE INDEX BY BINARY_INTEGER;
2756
2757 TYPE tPSShippableItemFlag IS TABLE OF cz_imp_ps_nodes.shippable_item_flag%TYPE INDEX BY BINARY_INTEGER;
2758 TYPE tInventoryTransactableFlag IS TABLE OF cz_imp_ps_nodes.inventory_transactable_flag%TYPE INDEX BY BINARY_INTEGER;
2759 TYPE tAssembleToOrderFlag IS TABLE OF cz_imp_ps_nodes.assemble_to_order_flag%TYPE INDEX BY BINARY_INTEGER;
2760 TYPE tSerializableItemFlag IS TABLE OF cz_imp_ps_nodes.serializable_item_flag%TYPE INDEX BY BINARY_INTEGER;
2761
2762 iPsNodeId tPsNodeId;
2763 iDevlProjectId tDevlProjectId;
2764 iFromPopulatorId tFromPopulatorId;
2765 iPropertyBackptr tPropertyBackptr;
2766 iItemTypeBackptr tItemTypeBackptr;
2767 iIntlTextid tIntlTextId;
2768 iSubConsid tSubConsId;
2769 iItemId tItemId;
2770 iName tName;
2771 iresourceFlag tResourceFlag;
2772 iInitialValue tInitialValue;
2773 iInitialNumValue tInitialNumValue;
2774 iParentId tParentId;
2775 iMinimum tMinimum;
2776 iMaximum tMaximum;
2777 iPsNodeType tPsNodeType;
2778 iFeatureType tFeatureType;
2779 iProductFlag tProductFlag;
2780 iReferenceId tReferenceId;
2781 iMultiConfigflag tmultiConfigFlag;
2782 iOrderSeqFlag tOrderSeqFlag;
2783 iSystemNodeFlag tSystemNodeFlag;
2784 iTreeSeq tTreeSeq;
2785 iCountedOptionsFlag tCountedOptionsFlag;
2786 iUiOmit tUiOmit;
2787 iUiSection tUiSection;
2788 iBomTreatment tBomTreatment;
2789 iOrigSysRef tOrigSysRef;
2790 iCheckoutUser tCheckoutUser;
2791 iDisposition tDisposition;
2792 iDeletedFlag tDeletedFlag;
2793 iEffectivefrom tEffectiveFrom;
2794 iEffectiveUntil tEffectiveUntil;
2795 iEffectiveUsageMask tEffectiveUsageMask;
2796 iUserStr01 tUserStr01;
2797 iUserStr02 tUserStr03;
2798 iUserStr03 tUserStr03;
2799 iUserStr04 tUserStr04;
2800 iUserNum01 tUserNum01;
2801 iUserNum02 tUserNum02;
2802 iUserNum03 tUserNum03;
2803 iUserNum04 tUserNum04;
2804 iCreationDate tCreationDate;
2805 iLastUpdateDate tLastUpdateDate;
2806 iCreatedBy tCreatedBy;
2807 iLastUpdatedBy tLastUpdatedBy;
2808 iSecurityMask tSecurityMask;
2809 iPlanLevel tPlanLevel;
2810 iSoItemTypeCode tSoItemTypeCode;
2811 iMinimumSelected tMinimumSelected;
2812 iMaximumSelected tMaximumSelected;
2813 iBomRequired tBomRequired;
2814 iComponentSequenceId tComponentSequenceId;
2815 iOrganizationid torganizationId;
2816 iTopItemId tTopItemId;
2817 iexplosionType tExplosionType;
2818 iDecimalQtyFlag tDecimalQtyFlag;
2819 iInstantiableFlag tinstantiableFlag;
2820 iQuoteableFlag tQuoteableFlag;
2821 iPrimaryUomCode tPrimaryUomCode;
2822 iBomSortorder tBomSortOrder;
2823 iComponentSequencePath tComponentSequencePath;
2824 iIbTrackable tIbTrackable;
2825 iSrcApplicationid tSrcApplicationId;
2826 iDisplayInSummaryFlag tDisplayInSummaryFlag;
2827 iIBLinkItemFlag tIBLinkItemFlag;
2828
2829 iShippableItemFlag tPSShippableItemFlag;
2830 iInventoryTransactableFlag tInventoryTransactableFlag;
2831 iAssembleToOrder tAssembleToOrderFlag;
2832 iSerializableItemFlag tSerializableItemFlag;
2833
2834 -- PS nodes that are references, connectors or componentns, NOT ROOTS
2835 CURSOR l_model_refs_csr IS
2836 SELECT plan_level, ps_node_id, devl_project_id, reference_id,
2837 minimum, maximum, ps_node_type, parent_id, disposition
2838 FROM cz_imp_ps_nodes
2839 WHERE ps_node_type IN (cnReference,cnConnector,cnComponent)
2840 AND deleted_flag='0' AND rec_status='OK' AND run_id=inRun_ID
2841 ORDER BY plan_level, devl_project_id, reference_id;
2842
2843 -- root nodes containing NO refs but ARE referenced, order by reference_id
2844 CURSOR C1 IS
2845 SELECT REFS.plan_level, ROOTS.devl_project_id, ROOTS.ps_node_id, ROOTS.disposition, REFS.devl_project_id AS REFERRING_MODEL_ID,
2846 ROOTS.ps_node_type, ROOTS.minimum, ROOTS.maximum, ROOTS.rec_status, ROOTS.name, d.model_type
2847 FROM cz_imp_ps_nodes ROOTS, cz_imp_ps_nodes REFS,cz_imp_devl_project d
2848 WHERE (ROOTS.ps_node_id = ROOTS.devl_project_id OR (ROOTS.parent_id IS NULL AND ROOTS.plan_level=0))
2849 AND ROOTS.devl_project_id=REFS.reference_id
2850 AND NOT EXISTS (SELECT 1 FROM cz_imp_ps_nodes
2851 WHERE run_id=inRUN_ID
2852 AND rec_status='PASS'
2853 AND devl_project_id=ROOTS.devl_project_id
2854 AND ps_node_type IN (cnReference,cnConnector))
2855 AND ROOTS.run_id = inRUN_ID
2856 AND ROOTS.rec_status='PASS'
2857 AND ROOTS.devl_project_id = d.devl_project_id
2858 AND d.rec_status = 'OK'
2859 AND d.run_id = inRun_ID
2860 AND REFS.run_id = inRUN_ID
2861 AND REFS.rec_status = 'PASS'
2862 ORDER BY ROOTS.devl_project_id;
2863
2864 -- root nodes that ARE referenced by other models AND have references to others
2865 CURSOR C2 IS
2866 SELECT REFS.plan_level, ROOTS.devl_project_id, ROOTS.ps_node_id, ROOTS.disposition,
2867 REFS.devl_project_id AS REFERRING_MODEL_ID, ROOTS.ps_node_type,
2868 ROOTS.minimum, ROOTS.maximum, ROOTS.rec_status, ROOTS.name, d.model_type
2869 FROM cz_imp_ps_nodes ROOTS, cz_imp_ps_nodes REFS, cz_imp_devl_project d
2870 WHERE (ROOTS.ps_node_id = ROOTS.devl_project_id OR (ROOTS.parent_id IS NULL AND ROOTS.plan_level=0))
2871 AND ROOTS.ps_node_id = REFS.reference_id
2872 AND REFS.ps_node_type IN (cnReference,cnConnector)
2873 AND EXISTS (SELECT 1 FROM cz_imp_ps_nodes
2874 WHERE run_id=inRUN_ID
2875 AND rec_status='PASS'
2876 AND devl_project_id=ROOTS.devl_project_id
2877 AND ps_node_type IN (cnReference,cnConnector))
2878 AND ROOTS.run_id=inRUN_ID
2879 AND ROOTS.rec_status='PASS'
2880 AND REFS.rec_status='PASS'
2881 AND REFS.run_id=inRUN_ID
2882 AND ROOTS.devl_project_id = d.devl_project_id
2883 AND d.run_id=inRUN_ID
2884 AND d.rec_status='OK'
2885 ORDER BY ROOTS.devl_project_id;
2886
2887 -- root nodes that are NOT referenced but DO contain references to other models
2888 CURSOR C3 IS
2889 SELECT ROOTS.plan_level, ROOTS.devl_project_id, ROOTS.ps_node_id, ROOTS.disposition,
2890 REFS.reference_id, ROOTS.ps_node_type, ROOTS.minimum, ROOTS.maximum, ROOTS.rec_status, d.model_type
2891 FROM cz_imp_ps_nodes ROOTS, cz_imp_ps_nodes REFS, cz_imp_devl_project d
2892 WHERE (ROOTS.ps_node_id = ROOTS.devl_project_id OR (ROOTS.parent_id IS NULL AND ROOTS.plan_level=0))
2893 AND ROOTS.ps_node_id = REFS.devl_project_id
2894 AND REFS.ps_node_type IN (cnReference,cnConnector)
2895 AND ROOTS.run_id=inRUN_ID
2896 AND ROOTS.rec_status='PASS'
2897 AND ROOTS.devl_project_id = d.devl_project_id
2898 AND d.run_id=inRUN_ID
2899 AND d.rec_status='OK'
2900 AND REFS.rec_status='PASS'
2901 AND REFS.run_id=inRUN_ID
2902 AND NOT EXISTS (SELECT 1 FROM cz_imp_ps_nodes
2903 WHERE run_id=inRUN_ID
2904 AND rec_status='PASS'
2905 AND reference_id=ROOTS.devl_project_id
2906 AND ps_node_type IN (cnReference,cnConnector))
2907 ORDER BY ROOTS.devl_project_id;
2908
2909 -- Remaining models if any (NOT referenced AND with NO references)
2910 CURSOR C4 IS
2911 SELECT p.plan_level, p.devl_project_id, p.ps_node_id, p.disposition,
2912 p.ps_node_type, p.minimum, p.maximum, p.rec_status, d.model_type
2913 FROM cz_imp_ps_nodes p, cz_imp_devl_project d
2914 WHERE p.run_id=inRUN_ID
2915 AND p.rec_status='PASS'
2916 AND (p.ps_node_id = p.devl_project_id OR (p.parent_id IS NULL AND p.plan_level=0))
2917 AND p.devl_project_id=d.devl_project_id
2918 AND d.run_id=inRUN_ID
2919 AND d.rec_status='OK';
2920
2921 /* used to load l_model_csr */
2922 l_PsNodeId tPsNodeId;
2923 l_DevlProjectId tDevlProjectId;
2924 l_PsNodeType tPsNodeType;
2925 l_PlanLevel tPlanLevel;
2926 l_referenceId tDevlProjectId;
2927 l_parentId tParentId;
2928 l_Minimum tMinimum;
2929 l_Maximum tMaximum;
2930 l_dis tDisposition;
2931 l_this_model_id number;
2932
2933 l_c1_prj_id_tbl tDevlProjectId;
2934 l_c1_node_id_tbl tPsNodeId;
2935 l_c1_plan_level_tbl tPlanLevel;
2936 l_c1_dis_tbl tDisposition;
2937 l_c1_ref_model_id_tbl tDevlProjectId;
2938 l_c1_max_tbl tMaximum;
2939 l_c1_min_tbl tMinimum;
2940 l_c1_nodetype_tbl tPsNodeType;
2941 l_c1_rec_status_tbl tRecStatus;
2942 l_c1_name_tbl tName;
2943 l_c1_model_type tModelType;
2944
2945 l_c2_prj_id_tbl tDevlProjectId;
2946 l_c2_node_id_tbl tPsNodeId;
2947 l_c2_plan_level_tbl tPlanLevel;
2948 l_c2_dis_tbl tDisposition;
2949 l_c2_ref_model_id_tbl tDevlProjectId;
2950 l_c2_max_tbl tMaximum;
2951 l_c2_min_tbl tMinimum;
2952 l_c2_nodetype_tbl tPsNodeType;
2953 l_c2_rec_status_tbl tRecStatus;
2954 l_c2_name_tbl tName;
2955 l_c2_model_type tModelType;
2956
2957 l_c3_prj_id_tbl tDevlProjectId;
2958 l_c3_node_id_tbl tPsNodeId;
2959 l_c3_plan_level_tbl tPlanLevel;
2960 l_c3_dis_tbl tDisposition;
2961 l_c3_ref_id_tbl tDevlProjectId;
2962 l_c3_max_tbl tMaximum;
2963 l_c3_min_tbl tMinimum;
2964 l_c3_nodetype_tbl tPsNodeType;
2965 l_c3_rec_status_tbl tRecStatus;
2966 l_c3_model_type tModelType;
2967
2968 l_c4_prj_id_tbl tDevlProjectId;
2969 l_c4_node_id_tbl tPsNodeId;
2970 l_c4_plan_level_tbl tPlanLevel;
2971 l_c4_dis_tbl tDisposition;
2972 l_c4_ref_id_tbl tDevlProjectId;
2973 l_c4_max_tbl tMaximum;
2974 l_c4_min_tbl tMinimum;
2975 l_c4_nodetype_tbl tPsNodeType;
2976 l_c4_rec_status_tbl tRecStatus;
2977 l_c4_model_type tModelType;
2978
2979 l_root_node_id_tbl tPsNodeId;
2980 l_root_dis_tbl tDisposition;
2981 l_ref_node_id_tbl tPsNodeId;
2982 l_ref_model_id_tbl tDevlProjectId;
2983 l_ref_plan_level_tbl tPlanLevel;
2984 l_ref_dis_tbl tDisposition;
2985 l_ref_model_id_tbl tDevlProjectId;
2986
2987 -- parametic cursor: model_id and disposition
2988 CURSOR c_xfr_psnode (inModelId NUMBER, inDisposition VARCHAR2)IS
2989 SELECT PS_NODE_ID,DEVL_PROJECT_ID,FROM_POPULATOR_ID,PROPERTY_BACKPTR,
2990 ITEM_TYPE_BACKPTR,INTL_TEXT_ID,SUB_CONS_ID,ITEM_ID,NAME,RESOURCE_FLAG,
2991 INITIAL_VALUE,initial_num_value, PARENT_ID,MINIMUM,MAXIMUM,PS_NODE_TYPE,FEATURE_TYPE,
2992 PRODUCT_FLAG,REFERENCE_ID,MULTI_CONFIG_FLAG,ORDER_SEQ_FLAG,SYSTEM_NODE_FLAG,TREE_SEQ,
2993 COUNTED_OPTIONS_FLAG,UI_OMIT,UI_SECTION,BOM_TREATMENT,ORIG_SYS_REF,CHECKOUT_USER,
2994 DISPOSITION,DELETED_FLAG,EFFECTIVE_FROM,EFFECTIVE_UNTIL,EFFECTIVE_USAGE_MASK,USER_STR01,USER_STR02,USER_STR03,
2995 USER_STR04,USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,CREATION_DATE,LAST_UPDATE_DATE,
2996 CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK, PLAN_LEVEL, SO_ITEM_TYPE_CODE,
2997 MINIMUM_SELECTED,MAXIMUM_SELECTED,BOM_REQUIRED,COMPONENT_SEQUENCE_ID,
2998 ORGANIZATION_ID,TOP_ITEM_ID,EXPLOSION_TYPE,DECIMAL_QTY_FLAG,INSTANTIABLE_FLAG,
2999 QUOTEABLE_FLAG,PRIMARY_UOM_CODE,BOM_SORT_ORDER,COMPONENT_SEQUENCE_PATH,IB_TRACKABLE, SRC_APPLICATION_ID,DISPLAY_IN_SUMMARY_FLAG,
3000 IB_LINK_ITEM_FLAG,
3001 SHIPPABLE_ITEM_FLAG,
3002 INVENTORY_TRANSACTABLE_FLAG,
3003 ASSEMBLE_TO_ORDER_FLAG,
3004 SERIALIZABLE_ITEM_FLAG
3005 FROM CZ_IMP_PS_NODES
3006 WHERE CZ_IMP_PS_NODES.RUN_ID = inRUN_ID AND REC_STATUS='PASS'
3007 AND devl_project_id=inModelId AND disposition=inDisposition
3008 ORDER BY PLAN_LEVEL,USER_NUM04 DESC;
3009
3010 x_xfr_psnode_f BOOLEAN:=FALSE;
3011 x_error BOOLEAN:=FALSE;
3012
3013 p_xfr_psnode c_xfr_psnode%ROWTYPE;
3014
3015 /* Internal vars */
3016 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
3017 nInsertCount PLS_INTEGER:=0; /*Inserts */
3018 nUpdateCount PLS_INTEGER:=0; /*Updates */
3019 nFailed PLS_INTEGER:=0; /*Failed records */
3020 p_out_err INTEGER;
3021 p_out_virtual_flag INTEGER;
3022 p_parent_id cz_ps_nodes.parent_id%TYPE;
3023
3024 NOUPDATE_PS_NODE_ID NUMBER;
3025 NOUPDATE_DEVL_PROJECT_ID NUMBER;
3026 NOUPDATE_FROM_POPULATOR_ID NUMBER;
3027 NOUPDATE_PROPERTY_BACKPTR NUMBER;
3028 NOUPDATE_ITEM_TYPE_BACKPTR NUMBER;
3029 NOUPDATE_INTL_TEXT_ID NUMBER;
3030 NOUPDATE_SUB_CONS_ID NUMBER;
3031 NOUPDATE_ITEM_ID NUMBER;
3032 NOUPDATE_NAME NUMBER;
3033 NOUPDATE_RESOURCE_FLAG NUMBER;
3034 NOUPDATE_INITIAL_VALUE NUMBER;
3035 NOUPDATE_INITIAL_NUM_VALUE NUMBER;
3036 NOUPDATE_PARENT_ID NUMBER;
3037 NOUPDATE_MINIMUM NUMBER;
3038 NOUPDATE_MAXIMUM NUMBER;
3039 NOUPDATE_PS_NODE_TYPE NUMBER;
3040 NOUPDATE_FEATURE_TYPE NUMBER;
3041 NOUPDATE_PRODUCT_FLAG NUMBER;
3042 NOUPDATE_REFERENCE_ID NUMBER;
3043 NOUPDATE_MULTI_CONFIG_FLAG NUMBER;
3044 NOUPDATE_ORDER_SEQ_FLAG NUMBER;
3045 NOUPDATE_SYSTEM_NODE_FLAG NUMBER;
3046 NOUPDATE_TREE_SEQ NUMBER;
3047 NOUPDATE_COUNTED_OPTIONS_FLAG NUMBER;
3048 NOUPDATE_UI_OMIT NUMBER;
3049 NOUPDATE_UI_SECTION NUMBER;
3050 NOUPDATE_BOM_TREATMENT NUMBER;
3051 NOUPDATE_ORIG_SYS_REF NUMBER;
3052 NOUPDATE_CHECKOUT_USER NUMBER;
3053 NOUPDATE_DELETED_FLAG NUMBER;
3054 NOUPDATE_EFF_FROM NUMBER;
3055 NOUPDATE_EFF_TO NUMBER;
3056 NOUPDATE_EFF_MASK NUMBER;
3057 NOUPDATE_USER_STR01 NUMBER;
3058 NOUPDATE_USER_STR02 NUMBER;
3059 NOUPDATE_USER_STR03 NUMBER;
3060 NOUPDATE_USER_STR04 NUMBER;
3061 NOUPDATE_USER_NUM01 NUMBER;
3062 NOUPDATE_USER_NUM02 NUMBER;
3063 NOUPDATE_USER_NUM03 NUMBER;
3064 NOUPDATE_USER_NUM04 NUMBER;
3065 NOUPDATE_CREATION_DATE NUMBER;
3066 NOUPDATE_LAST_UPDATE_DATE NUMBER;
3067 NOUPDATE_CREATED_BY NUMBER;
3068 NOUPDATE_LAST_UPDATED_BY NUMBER;
3069 NOUPDATE_SECURITY_MASK NUMBER;
3070 NOUPDATE_SO_ITEM_TYPE_CODE NUMBER;
3071 NOUPDATE_MINIMUM_SELECTED NUMBER;
3072 NOUPDATE_MAXIMUM_SELECTED NUMBER;
3073 NOUPDATE_BOM_REQUIRED NUMBER;
3074 NOUPDATE_COMPONENT_SEQUENCE_ID NUMBER;
3075 NOUPDATE_DECIMAL_QTY_FLAG NUMBER;
3076 NOUPDATE_QUOTEABLE_FLAG NUMBER;
3077 NOUPDATE_PRIMARY_UOM_CODE NUMBER;
3078 NOUPDATE_BOM_SORT_ORDER NUMBER;
3079 NOUPDATE_SEQUENCE_PATH NUMBER;
3080 NOUPDATE_IB_TRACKABLE NUMBER;
3081 NOUPDATE_DSPLY_SMRY_FLG NUMBER;
3082 NOUPDATE_IBLINKITEM_FLG NUMBER;
3083 NOUPDATE_INSTANTIABLE_FLAG NUMBER;
3084
3085 -- TSO changes --
3086 NOUPDATE_SHIPPABLE_ITEM_FLAG NUMBER;
3087 NOUPDATE_INV_TXN_FLAG NUMBER;
3088 NOUPDATE_ASM_TO_ORDER_FLAG NUMBER;
3089 NOUPDATE_SERIAL_ITEM_FLAG NUMBER;
3090
3091
3092 sVirtualFlag cz_ps_nodes.virtual_flag%type := '1';
3093 l_last_model_id cz_ps_nodes.devl_project_id%type := 0;
3094 l_msg VARCHAR2(2000);
3095 l_model_name cz_devl_projects.name%type;
3096 l_disposition cz_imp_ps_nodes.disposition%type;
3097 i integer;
3098 j integer;
3099 l_debug number:=0;
3100 l_retcode number;
3101
3102 -- Private prcedure that inserts all PS nodes for the model passed in, it bulk fetches and
3103 -- tries to bulk insert, if bulk insert fails, then it inserts row by row
3104 -- it rollbacks if root node fails to insert other wise it logs rows failed to insert
3105 -- and continues untill all ps nodes are inserted
3106
3107 -- Returns 0 if the model ps nodes prcessed successfully (the root ps node is inserted successfully)
3108 -- Returns 1 if error: when root node fails to insert
3109
3110 PROCEDURE insert_ps_nodes(p_model_id IN NUMBER, x_retcode OUT NOCOPY NUMBER)
3111 IS
3112 BEGIN
3113 nCommitCount:=0;
3114 x_retcode := 0;
3115
3116 IF c_xfr_psnode%ISOPEN THEN
3117 CLOSE c_xfr_psnode;
3118 END IF;
3119
3120 OPEN c_xfr_psnode (p_model_id, 'I');
3121
3122 <<OUTER_LOOP>>
3123 LOOP -- bulk fetch for insert
3124
3125 iPSNODEID.DELETE; iDEVLPROJECTID.DELETE; iFROMPOPULATORID.DELETE; iPROPERTYBACKPTR.DELETE;
3126 iITEMTYPEBACKPTR.DELETE; iINTLTEXTID.DELETE; iSUBCONSID.DELETE; iITEMID.DELETE; iNAME.DELETE; iRESOURCEFLAG.DELETE;
3127 iINITIALVALUE.DELETE; iInitialnumvalue.DELETE; iPARENTID.DELETE; iMINIMUM.DELETE; iMAXIMUM.DELETE; iPSNODETYPE.DELETE;
3128 iFEATURETYPE.DELETE; iPRODUCTFLAG.DELETE; iREFERENCEID.DELETE; iMULTICONFIGFLAG.DELETE; iORDERSEQFLAG.DELETE;
3129 iSYSTEMNODEFLAG.DELETE; iTREESEQ.DELETE; iCOUNTEDOPTIONSFLAG.DELETE; iUIOMIT.DELETE; iUISECTION.DELETE; iBOMTREATMENT.DELETE;
3130 iORIGSYSREF.DELETE; iCHECKOUTUSER.DELETE; iDISPOSITION.DELETE; iDELETEDFLAG.DELETE; iEFFECTIVEFROM.DELETE;
3131 iEFFECTIVEUNTIL.DELETE; iEFFECTIVEUSAGEMASK.DELETE; iUSERSTR01.DELETE; iUSERSTR02.DELETE;
3132 iUSERSTR03.DELETE; iUSERSTR04.DELETE; iUSERNUM01.DELETE; iUSERNUM02.DELETE; iUSERNUM03.DELETE; iUSERNUM04.DELETE;
3133 iCREATIONDATE.DELETE; iLASTUPDATEDATE.DELETE; iCREATEDBY.DELETE; iLASTUPDATEDBY.DELETE; iSECURITYMASK.DELETE;
3134 iPLANLEVEL.DELETE; iSOITEMTYPECODE.DELETE; iMINIMUMSELECTED.DELETE; iMAXIMUMSELECTED.DELETE; iBOMREQUIRED.DELETE;
3135 iCOMPONENTSEQUENCEID.DELETE; iORGANIZATIONID.DELETE; iTOPITEMID.DELETE; iEXPLOSIONTYPE.DELETE; iDECIMALQTYFLAG.DELETE;
3136 iINSTANTIABLEFLAG.DELETE; iQUOTEABLEFLAG.DELETE; iPRIMARYUOMCODE.DELETE; iBOMSORTORDER.DELETE;
3137 iCOMPONENTSEQUENCEPATH.DELETE;iIBTRACKABLE.DELETE;iSRCAPPLICATIONID.DELETE;iDisplayInSummaryFlag.DELETE;iIBLinkItemFlag.DELETE;
3138
3139 iShippableItemFlag.DELETE;
3140 iInventoryTransactableFlag.DELETE;
3141 iAssembleToOrder.DELETE;
3142 iSerializableItemFlag.DELETE;
3143
3144 FETCH c_xfr_psnode BULK COLLECT INTO
3145 iPSNODEID,iDEVLPROJECTID,iFROMPOPULATORID,iPROPERTYBACKPTR,
3146 iITEMTYPEBACKPTR,iINTLTEXTID,iSUBCONSID,iITEMID,iNAME,iRESOURCEFLAG,
3147 iINITIALVALUE,iINITIALNUMVALUE,iPARENTID,iMINIMUM,iMAXIMUM,iPSNODETYPE,iFEATURETYPE,
3148 iPRODUCTFLAG,iREFERENCEID,iMULTICONFIGFLAG,iORDERSEQFLAG,iSYSTEMNODEFLAG,iTREESEQ,
3149 iCOUNTEDOPTIONSFLAG,iUIOMIT,iUISECTION,iBOMTREATMENT,iORIGSYSREF,iCHECKOUTUSER,
3150 iDISPOSITION,iDELETEDFLAG,iEFFECTIVEFROM,iEFFECTIVEUNTIL,iEFFECTIVEUSAGEMASK,iUSERSTR01,iUSERSTR02,iUSERSTR03,
3151 iUSERSTR04,iUSERNUM01,iUSERNUM02,iUSERNUM03,iUSERNUM04,iCREATIONDATE,iLASTUPDATEDATE,
3152 iCREATEDBY,iLASTUPDATEDBY,iSECURITYMASK, iPLANLEVEL, iSOITEMTYPECODE,
3153 iMINIMUMSELECTED,iMAXIMUMSELECTED,iBOMREQUIRED,iCOMPONENTSEQUENCEID,
3154 iORGANIZATIONID,iTOPITEMID,iEXPLOSIONTYPE,iDECIMALQTYFLAG,iINSTANTIABLEFLAG,
3155 iQUOTEABLEFLAG,iPRIMARYUOMCODE,iBOMSORTORDER,iCOMPONENTSEQUENCEPATH,iIBTRACKABLE,iSRCAPPLICATIONID,iDisplayInSummaryFlag,
3156 iIBLinkItemFlag,
3157 iShippableItemFlag,
3158 iInventoryTransactableFlag,
3159 iAssembleToOrder,
3160 iSerializableItemFlag
3161 LIMIT COMMIT_SIZE;
3162
3163 EXIT WHEN (c_xfr_psnode%NOTFOUND AND iPSNODEID.COUNT=0);
3164
3165 IF iPsNodeId.COUNT > 0 THEN
3166 --
3167 -- changes for Solver
3168 -- raise an exception if maximum value is not specified in case of Generic Import plus FCE, CZ_BOM_DEFAULT_QTY_DOMN='N'
3169 --
3170 IF G_CONFIG_ENGINE_TYPE='F' AND FND_PROFILE.VALUE('CZ_BOM_DEFAULT_QTY_DOMN')='N' AND
3171 inXFR_GROUP='GENERIC' THEN
3172 FOR j IN iPsNodeID.FIRST..iPsNodeId.LAST
3173 LOOP
3174 IF (iPSNODETYPE(j)=263 AND (iMAXIMUMSELECTED(j) IS NULL OR iMAXIMUMSELECTED(j) IN(0,-1))) OR
3175 (iPSNODETYPE(j)<>263 AND (iMAXIMUM(j) IS NULL OR iMAXIMUM(j) IN(0,-1))) THEN
3176 ROLLBACK;
3177 x_error:=CZ_UTILS.LOG_REPORT('Fatal Error : CZ_IMP_PS_NODES.MAXIMUM should be specified in Generic Import',1,'CZ_IMP_PS_NODE.XFR_PS_NODE',11276,inRun_Id);
3178 UPDATE CZ_IMP_PS_NODES
3179 SET REC_STATUS='ERR'
3180 WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
3181 AND DISPOSITION='I';
3182 COMMIT;
3183 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
3184 END IF;
3185 END LOOP;
3186 END IF;
3187
3188 BEGIN -- bulk insert
3189 FORALL j IN iPsNodeID.FIRST..iPsNodeId.LAST
3190 INSERT INTO CZ_PS_NODES (PS_NODE_ID,
3191 DEVL_PROJECT_ID,
3192 FROM_POPULATOR_ID,
3193 PROPERTY_BACKPTR,
3194 ITEM_TYPE_BACKPTR,
3195 INTL_TEXT_ID,
3196 SUB_CONS_ID,
3197 ITEM_ID,
3198 NAME,
3199 RESOURCE_FLAG,
3200 INITIAL_VALUE,
3201 initial_num_value,
3202 PARENT_ID,
3203 MINIMUM,
3204 MAXIMUM,
3205 PS_NODE_TYPE,
3206 FEATURE_TYPE,
3207 PRODUCT_FLAG,
3208 REFERENCE_ID,
3209 MULTI_CONFIG_FLAG,
3210 ORDER_SEQ_FLAG,
3211 SYSTEM_NODE_FLAG,
3212 TREE_SEQ,
3213 COUNTED_OPTIONS_FLAG,
3214 UI_OMIT,UI_SECTION,
3215 BOM_TREATMENT,
3216 ORIG_SYS_REF,
3217 CHECKOUT_USER,
3218 USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,USER_STR01,USER_STR02,USER_STR03,USER_STR04,
3219 CREATION_DATE,
3220 LAST_UPDATE_DATE,
3221 DELETED_FLAG,
3222 EFFECTIVE_FROM,
3223 EFFECTIVE_UNTIL,
3224 CREATED_BY,
3225 LAST_UPDATED_BY,
3226 SECURITY_MASK,
3227 --EFFECTIVE_USAGE_MASK,
3228 SO_ITEM_TYPE_CODE,
3229 MINIMUM_SELECTED,
3230 MAXIMUM_SELECTED,
3231 BOM_REQUIRED_FLAG,
3232 COMPONENT_SEQUENCE_ID,
3233 DECIMAL_QTY_FLAG,
3234 QUOTEABLE_FLAG,
3235 PRIMARY_UOM_CODE,
3236 BOM_SORT_ORDER,
3237 COMPONENT_SEQUENCE_PATH,
3238 IB_TRACKABLE,
3239 SRC_APPLICATION_ID,
3240 VIRTUAL_FLAG,
3241 INSTANTIABLE_FLAG,
3242 DISPLAY_IN_SUMMARY_FLAG,
3243 IB_LINK_ITEM_FLAG,
3244 SHIPPABLE_ITEM_FLAG,
3245 INVENTORY_TRANSACTABLE_FLAG,
3246 ASSEMBLE_TO_ORDER_FLAG,
3247 SERIALIZABLE_ITEM_FLAG)
3248 VALUES
3249 ( iPSNODEID(j),
3250 iDEVLPROJECTID(j),
3251 iFROMPOPULATORID(j), iPROPERTYBACKPTR(j),
3252 iITEMTYPEBACKPTR(j), iINTLTEXTID(j),
3253 iSUBCONSID(j), iITEMID(j),
3254 iNAME(j),iRESOURCEFLAG(j),
3255 iINITIALVALUE(j), iINITIALNUMVALUE(j),
3256 iPARENTID(j),
3257 iMINIMUM(j), iMAXIMUM(j),
3258 iPSNODETYPE(j), iFEATURETYPE(j),
3259 iPRODUCTFLAG(j),iREFERENCEID(j),iMULTICONFIGFLAG(j),iORDERSEQFLAG(j),
3260 iSYSTEMNODEFLAG(j),iTREESEQ(j), iCOUNTEDOPTIONSFLAG(j), iUIOMIT(j),
3261 iUISECTION(j), iBOMTREATMENT(j),
3262 iORIGSYSREF(j), iCHECKOUTUSER (j),iUSERNUM01(j),iUSERNUM02(j),
3263 iUSERNUM03(j), iUSERNUM04(j),
3264 iUSERSTR01(j), iUSERSTR02(j), iUSERSTR03(j), iUSERSTR04(j),
3265 SYSDATE, SYSDATE,
3266 iDELETEDFLAG(j),
3267 iEFFECTIVEFROM (j), iEFFECTIVEUNTIL (j),
3268 -UID, -UID, NULL,
3269 -- iEFFECTIVEUSAGEMASK(j),
3270 iSOITEMTYPECODE(j),
3271 iMINIMUMSELECTED(j),
3272 iMAXIMUMSELECTED(j),
3273 iBOMREQUIRED(j),
3274 iCOMPONENTSEQUENCEID(j),
3275 iDECIMALQTYFLAG(j),
3276 iQUOTEABLEFLAG(j),
3277 iPRIMARYUOMCODE(j),
3278 iBOMSORTORDER(j),
3279 iCOMPONENTSEQUENCEPATH(j),
3280 iIBTRACKABLE(j),
3281 iSRCAPPLICATIONID(j),
3282 sVirtualFlag,
3283 iINSTANTIABLEFLAG(j),
3284 iDisplayInSummaryFlag(j),
3285 iIBLinkItemFlag(j),
3286 iShippableItemFlag(j),
3287 iInventoryTransactableFlag(j),
3288 iAssembleToOrder(j),
3289 iSerializableItemFlag(j));
3290
3291 nInsertCount:= nInsertCount + SQL%ROWCOUNT;
3292 nCommitCount:= nCommitCount + SQL%ROWCOUNT;
3293
3294 BEGIN
3295 FORALL j IN iPsNodeID.FIRST..iPsNodeId.LAST
3296 UPDATE CZ_IMP_PS_NODES
3297 SET REC_STATUS='OK'
3298 WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
3299 AND DISPOSITION='I';
3300
3301 /* COMMIT if the buffer size is reached */
3302 IF(nCommitCount>= COMMIT_SIZE) THEN
3303 COMMIT;
3304 nCommitCount:=0;
3305 END IF;
3306
3307 EXCEPTION
3308 WHEN OTHERS THEN
3309 l_debug:=0861;
3310 ROLLBACK; -- need to insert row by row to log errors
3311 l_msg:=p_model_id||':'||SQLERRM;
3312 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'IMP_IM_XFR.XFR_PS_NODE('||l_debug||')',11276,inRun_Id);
3313 RAISE;
3314 END;
3315
3316 EXCEPTION -- bulk insert
3317 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
3318 RAISE;
3319 WHEN OTHERS THEN
3320
3321 l_debug:=0862;
3322 l_msg:=p_model_id||':'||SQLERRM;
3323 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'IMP_IM_XFR.XFR_PS_NODE('||l_debug||')',11276,inRun_Id);
3324 --
3325 -- changes for Solver
3326 -- raise an exception if maximum value is not specified in case of Generic Import plus FCE, CZ_BOM_DEFAULT_QTY_DOMN='N'
3327 --
3328 IF G_CONFIG_ENGINE_TYPE='F' AND FND_PROFILE.VALUE('CZ_BOM_DEFAULT_QTY_DOMN')='N' AND
3329 inXFR_GROUP='GENERIC' THEN
3330 FOR j IN iPsNodeID.FIRST..iPsNodeId.LAST
3331 LOOP
3332 IF (iPSNODETYPE(j)=263 AND (iMAXIMUMSELECTED(j) IS NULL OR iMAXIMUMSELECTED(j) IN(0,-1))) OR
3333 (iPSNODETYPE(j)<>263 AND (iMAXIMUM(j) IS NULL OR iMAXIMUM(j) IN(0,-1))) THEN
3334
3335 ROLLBACK;
3336 x_error:=CZ_UTILS.LOG_REPORT('Fatal Error : CZ_IMP_PS_NODES.MAXIMUM should be specified in Generic Import',1,'CZ_IMP_PS_NODE.XFR_PS_NODE',11276,inRun_Id);
3337 UPDATE CZ_IMP_PS_NODES
3338 SET REC_STATUS='ERR'
3339 WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
3340 AND DISPOSITION='I';
3341 COMMIT;
3342 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
3343 END IF;
3344 END LOOP;
3345 END IF;
3346
3347 FOR j IN iPsNodeID.FIRST..iPsNodeId.LAST LOOP -- singe row loop
3348
3349 IF(FAILED >= MAX_ERR) THEN
3350 ROLLBACK;
3351 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_PS_NODE.XFR_PS_NODE',11276,inRun_Id);
3352 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
3353 END IF;
3354
3355 BEGIN -- single row insert
3356 INSERT INTO CZ_PS_NODES (PS_NODE_ID,
3357 DEVL_PROJECT_ID,
3358 FROM_POPULATOR_ID,
3359 PROPERTY_BACKPTR,
3360 ITEM_TYPE_BACKPTR,
3361 INTL_TEXT_ID,
3362 SUB_CONS_ID,
3363 ITEM_ID,
3364 NAME,
3365 RESOURCE_FLAG,
3366 INITIAL_VALUE,
3367 initial_num_value,
3368 PARENT_ID,
3369 MINIMUM,
3370 MAXIMUM,
3371 PS_NODE_TYPE,
3372 FEATURE_TYPE,
3373 PRODUCT_FLAG,
3374 REFERENCE_ID,
3375 MULTI_CONFIG_FLAG,
3376 ORDER_SEQ_FLAG,
3377 SYSTEM_NODE_FLAG,
3378 TREE_SEQ,
3379 COUNTED_OPTIONS_FLAG,
3380 UI_OMIT,UI_SECTION,
3381 BOM_TREATMENT,
3382 ORIG_SYS_REF,
3383 CHECKOUT_USER,
3384 USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,USER_STR01,USER_STR02,USER_STR03,USER_STR04,
3385 CREATION_DATE,
3386 LAST_UPDATE_DATE,
3387 DELETED_FLAG,
3388 EFFECTIVE_FROM,
3389 EFFECTIVE_UNTIL,
3390 CREATED_BY,
3391 LAST_UPDATED_BY,
3392 SECURITY_MASK,
3393 --EFFECTIVE_USAGE_MASK,
3394 SO_ITEM_TYPE_CODE,
3395 MINIMUM_SELECTED,
3396 MAXIMUM_SELECTED,
3397 BOM_REQUIRED_FLAG,
3398 COMPONENT_SEQUENCE_ID,
3399 DECIMAL_QTY_FLAG,
3400 QUOTEABLE_FLAG,
3401 PRIMARY_UOM_CODE,
3402 BOM_SORT_ORDER,
3403 COMPONENT_SEQUENCE_PATH,
3404 IB_TRACKABLE,
3405 SRC_APPLICATION_ID,
3406 VIRTUAL_FLAG,
3407 INSTANTIABLE_FLAG,
3408 DISPLAY_IN_SUMMARY_FLAG,
3409 IB_LINK_ITEM_FLAG,
3410 SHIPPABLE_ITEM_FLAG,
3411 INVENTORY_TRANSACTABLE_FLAG,
3412 ASSEMBLE_TO_ORDER_FLAG,
3413 SERIALIZABLE_ITEM_FLAG)
3414 VALUES
3415 ( iPSNODEID(j),
3416 iDEVLPROJECTID(j),
3417 iFROMPOPULATORID(j), iPROPERTYBACKPTR(j),
3418 iITEMTYPEBACKPTR(j), iINTLTEXTID(j),
3419 iSUBCONSID(j), iITEMID(j),
3420 iNAME(j),iRESOURCEFLAG(j),
3421 iINITIALVALUE(j), iINITIALNUMVALUE(j),
3422 iPARENTID(j),
3423 iMINIMUM(j), iMAXIMUM(j),
3424 iPSNODETYPE(j), iFEATURETYPE(j),
3425 iPRODUCTFLAG(j),iREFERENCEID(j),iMULTICONFIGFLAG(j),iORDERSEQFLAG(j),
3426 iSYSTEMNODEFLAG(j),iTREESEQ(j), iCOUNTEDOPTIONSFLAG(j), iUIOMIT(j),
3427 iUISECTION(j), iBOMTREATMENT(j),
3428 iORIGSYSREF(j), iCHECKOUTUSER (j),iUSERNUM01(j),iUSERNUM02(j),
3429 iUSERNUM03(j), iUSERNUM04(j),
3430 iUSERSTR01(j), iUSERSTR02(j), iUSERSTR03(j), iUSERSTR04(j),
3431 SYSDATE, SYSDATE,
3432 iDELETEDFLAG(j),
3433 iEFFECTIVEFROM (j), iEFFECTIVEUNTIL (j),
3434 -UID, -UID, NULL,
3435 -- iEFFECTIVEUSAGEMASK(j),
3436 iSOITEMTYPECODE(j),
3437 iMINIMUMSELECTED(j),
3438 iMAXIMUMSELECTED(j),
3439 iBOMREQUIRED(j),
3440 iCOMPONENTSEQUENCEID(j),
3441 iDECIMALQTYFLAG(j),
3442 iQUOTEABLEFLAG(j),
3443 iPRIMARYUOMCODE(j),
3444 iBOMSORTORDER(j),
3445 iCOMPONENTSEQUENCEPATH(j),
3446 iIBTRACKABLE(j),
3447 iSRCAPPLICATIONID(j),
3448 sVirtualFlag,
3449 iINSTANTIABLEFLAG(j),
3450 iDisplayInSummaryFlag(j),
3451 iIBLinkItemFlag(j),
3452 iShippableItemFlag(j),
3453 iInventoryTransactableFlag(j),
3454 iAssembleToOrder(j),
3455 iSerializableItemFlag(j));
3456
3457
3458 nInsertCount:=nInsertCount+1;
3459
3460 UPDATE CZ_IMP_PS_NODES
3461 SET REC_STATUS='OK'
3462 WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
3463 AND DISPOSITION='I';
3464
3465 nCommitCount:=nCommitCount+1;
3466 /* COMMIT if the buffer size is reached */
3467 IF(nCommitCount>= COMMIT_SIZE) THEN
3468 COMMIT;
3469 nCommitCount:=0;
3470 END IF;
3471
3472
3473 EXCEPTION -- single row insert
3474 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
3475 RAISE;
3476 WHEN OTHERS THEN
3477 IF(iPSNODEID(j) = iDEVLPROJECTID(j))THEN
3478 x_retcode := 1;
3479
3480 -- get model name for message log
3481 SELECT name INTO l_model_name
3482 FROM cz_imp_devl_project
3483 WHERE devl_project_id=p_model_id
3484 AND deleted_flag='0'
3485 AND run_id=inRun_ID
3486 AND rec_status='OK'
3487 AND rownum <2;
3488
3489 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_XFR_ROOT_NODE_I',
3490 'MODELNAME', l_model_name,
3491 'NODENAME', iName(j),
3492 'NODEID', iPsNodeId(j),
3493 'ERRORTEXT', SQLERRM);
3494
3495 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
3496 COMMIT;
3497
3498 EXIT OUTER_LOOP;
3499 ELSE
3500 -- not a root node, so import continues
3501 FAILED:=FAILED +1;
3502 UPDATE CZ_IMP_PS_NODES
3503 SET REC_STATUS='ERR'
3504 WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
3505 AND DISPOSITION='I';
3506
3507 -- get model name
3508 SELECT name INTO l_model_name
3509 FROM cz_imp_devl_project
3510 WHERE devl_project_id=p_model_id
3511 AND deleted_flag='0'
3512 AND run_id=inRun_ID
3513 AND rec_status='OK'
3514 AND rownum <2;
3515
3516 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_XFR_NODE_I',
3517 'MODELNAME', l_model_name,
3518 'NODENAME', iName(j),
3519 'NODEID', iPsNodeId(j),
3520 'ERRORTEXT', SQLERRM);
3521 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
3522 END IF;
3523
3524 END; --single row insert
3525 END lOOP; -- single row for loop
3526 END; -- bulk insert
3527 END IF; -- if count > 0
3528 END LOOP; -- bulk fetch for insert
3529 IF c_xfr_psnode%ISOPEN THEN
3530 CLOSE c_xfr_psnode;
3531 END IF;
3532
3533 END insert_ps_nodes;
3534
3535 -- Private prcedure that update all PS nodes for the model passed in, it bulk fetches and
3536 -- tries to bulk update, if bulk insert fails, then it updates row by row
3537 -- and logs rows failed to update and continues untill all ps nodes are processed
3538
3539 -- returns nothing
3540
3541 PROCEDURE update_ps_nodes(p_model_id IN NUMBER)
3542 IS
3543 BEGIN
3544
3545
3546 IF c_xfr_psnode%ISOPEN THEN
3547 CLOSE c_xfr_psnode;
3548 END IF;
3549
3550 OPEN c_xfr_psnode (p_model_id, 'M');
3551
3552 LOOP -- bulk fetch for update
3553
3554 iPSNODEID.DELETE; iDEVLPROJECTID.DELETE; iFROMPOPULATORID.DELETE; iPROPERTYBACKPTR.DELETE;
3555 iITEMTYPEBACKPTR.DELETE; iINTLTEXTID.DELETE; iSUBCONSID.DELETE; iITEMID.DELETE; iNAME.DELETE; iRESOURCEFLAG.DELETE;
3556 iINITIALVALUE.DELETE; iInitialnumvalue.DELETE; iPARENTID.DELETE; iMINIMUM.DELETE; iMAXIMUM.DELETE; iPSNODETYPE.DELETE;
3557 iFEATURETYPE.DELETE; iPRODUCTFLAG.DELETE; iREFERENCEID.DELETE; iMULTICONFIGFLAG.DELETE; iORDERSEQFLAG.DELETE;
3558 iSYSTEMNODEFLAG.DELETE; iTREESEQ.DELETE; iCOUNTEDOPTIONSFLAG.DELETE; iUIOMIT.DELETE; iUISECTION.DELETE; iBOMTREATMENT.DELETE;
3559 iORIGSYSREF.DELETE; iCHECKOUTUSER.DELETE; iDISPOSITION.DELETE; iDELETEDFLAG.DELETE; iEFFECTIVEFROM.DELETE;
3560 iEFFECTIVEUNTIL.DELETE; iEFFECTIVEUSAGEMASK.DELETE; iUSERSTR01.DELETE; iUSERSTR02.DELETE;
3561 iUSERSTR03.DELETE; iUSERSTR04.DELETE; iUSERNUM01.DELETE; iUSERNUM02.DELETE; iUSERNUM03.DELETE; iUSERNUM04.DELETE;
3562 iCREATIONDATE.DELETE; iLASTUPDATEDATE.DELETE; iCREATEDBY.DELETE; iLASTUPDATEDBY.DELETE; iSECURITYMASK.DELETE;
3563 iPLANLEVEL.DELETE; iSOITEMTYPECODE.DELETE; iMINIMUMSELECTED.DELETE; iMAXIMUMSELECTED.DELETE; iBOMREQUIRED.DELETE;
3564 iCOMPONENTSEQUENCEID.DELETE; iORGANIZATIONID.DELETE; iTOPITEMID.DELETE; iEXPLOSIONTYPE.DELETE; iDECIMALQTYFLAG.DELETE;
3565 iINSTANTIABLEFLAG.DELETE; iQUOTEABLEFLAG.DELETE; iPRIMARYUOMCODE.DELETE; iBOMSORTORDER.DELETE;
3566 iCOMPONENTSEQUENCEPATH.DELETE;iIBTRACKABLE.DELETE;iSRCAPPLICATIONID.DELETE;iDisplayInSummaryFlag.DELETE;iIBLinkItemFlag.DELETE;
3567
3568 iShippableItemFlag.DELETE;
3569 iInventoryTransactableFlag.DELETE;
3570 iAssembleToOrder.DELETE;
3571 iSerializableItemFlag.DELETE;
3572
3573 FETCH c_xfr_psnode BULK COLLECT INTO
3574 iPSNODEID,iDEVLPROJECTID,iFROMPOPULATORID,iPROPERTYBACKPTR,
3575 iITEMTYPEBACKPTR,iINTLTEXTID,iSUBCONSID,iITEMID,iNAME,iRESOURCEFLAG,
3576 iINITIALVALUE,iInitialnumvalue,iPARENTID,iMINIMUM,iMAXIMUM,iPSNODETYPE,iFEATURETYPE,
3577 iPRODUCTFLAG,iREFERENCEID,iMULTICONFIGFLAG,iORDERSEQFLAG,iSYSTEMNODEFLAG,iTREESEQ,
3578 iCOUNTEDOPTIONSFLAG,iUIOMIT,iUISECTION,iBOMTREATMENT,iORIGSYSREF,iCHECKOUTUSER,
3579 iDISPOSITION,iDELETEDFLAG,iEFFECTIVEFROM,iEFFECTIVEUNTIL,iEFFECTIVEUSAGEMASK,iUSERSTR01,iUSERSTR02,iUSERSTR03,
3580 iUSERSTR04,iUSERNUM01,iUSERNUM02,iUSERNUM03,iUSERNUM04,iCREATIONDATE,iLASTUPDATEDATE,
3581 iCREATEDBY,iLASTUPDATEDBY,iSECURITYMASK, iPLANLEVEL, iSOITEMTYPECODE,
3582 iMINIMUMSELECTED,iMAXIMUMSELECTED,iBOMREQUIRED,iCOMPONENTSEQUENCEID,
3583 iORGANIZATIONID,iTOPITEMID,iEXPLOSIONTYPE,iDECIMALQTYFLAG,iINSTANTIABLEFLAG,
3584 iQUOTEABLEFLAG,iPRIMARYUOMCODE,iBOMSORTORDER,iCOMPONENTSEQUENCEPATH,iIBTRACKABLE,iSRCAPPLICATIONID,iDisplayInSummaryFlag,
3585 iIBLinkItemFlag,
3586 iShippableItemFlag,
3587 iInventoryTransactableFlag,
3588 iAssembleToOrder,
3589 iSerializableItemFlag
3590 LIMIT COMMIT_SIZE;
3591 EXIT WHEN (c_xfr_psnode%NOTFOUND AND iPSNODEID.COUNT=0);
3592
3593 IF iPsNodeId.COUNT > 0 THEN
3594 --
3595 -- changes for Solver
3596 -- raise an exception if maximum value is not specified in case of Generic Import plus FCE, CZ_BOM_DEFAULT_QTY_DOMN='N'
3597 --
3598 IF G_CONFIG_ENGINE_TYPE='F' AND FND_PROFILE.VALUE('CZ_BOM_DEFAULT_QTY_DOMN')='N' AND
3599 inXFR_GROUP='GENERIC' THEN
3600 FOR j IN iPsNodeID.FIRST..iPsNodeId.LAST
3601 LOOP
3602 IF (iPSNODETYPE(j)=263 AND (iMAXIMUMSELECTED(j) IS NULL OR iMAXIMUMSELECTED(j) IN(0,-1))) OR
3603 (iPSNODETYPE(j)<>263 AND (iMAXIMUM(j) IS NULL OR iMAXIMUM(j) IN(0,-1))) THEN
3604 ROLLBACK;
3605 x_error:=CZ_UTILS.LOG_REPORT('Fatal Error : CZ_IMP_PS_NODES.MAXIMUM should be specified in Generic Import',1,'CZ_IMP_PS_NODE.XFR_PS_NODE',11276,inRun_Id);
3606 UPDATE CZ_IMP_PS_NODES
3607 SET REC_STATUS='ERR'
3608 WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
3609 AND DISPOSITION='M';
3610 COMMIT;
3611 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
3612 END IF;
3613 END LOOP;
3614 END IF;
3615
3616 BEGIN -- bulk update
3617 FORALL j IN iPsNodeID.FIRST..iPsNodeId.LAST
3618 UPDATE CZ_PS_NODES SET
3619 DEVL_PROJECT_ID= DECODE(NOUPDATE_DEVL_PROJECT_ID,0,iDEVLPROJECTID(j),DEVL_PROJECT_ID),
3620 FROM_POPULATOR_ID= DECODE(NOUPDATE_FROM_POPULATOR_ID,0,iFROMPOPULATORID(j),FROM_POPULATOR_ID),
3621 PROPERTY_BACKPTR= DECODE(NOUPDATE_PROPERTY_BACKPTR,0,iPROPERTYBACKPTR(j),PROPERTY_BACKPTR),
3622 ITEM_TYPE_BACKPTR= DECODE(NOUPDATE_ITEM_TYPE_BACKPTR,0,iITEMTYPEBACKPTR(j),ITEM_TYPE_BACKPTR),
3623 INTL_TEXT_ID= DECODE(NOUPDATE_INTL_TEXT_ID,0,iINTLTEXTID(j),INTL_TEXT_ID),
3624 SUB_CONS_ID= DECODE(NOUPDATE_SUB_CONS_ID,0,iSUBCONSID(j),SUB_CONS_ID),
3625 ITEM_ID= DECODE(NOUPDATE_ITEM_ID,0,iITEMID(j),ITEM_ID),
3626 NAME= DECODE(NOUPDATE_NAME,0,iNAME(j),NAME),
3627 RESOURCE_FLAG= DECODE(NOUPDATE_RESOURCE_FLAG,0,iRESOURCEFLAG(j),RESOURCE_FLAG),
3628 INITIAL_VALUE= DECODE(NOUPDATE_INITIAL_VALUE,0,iINITIALVALUE(j),INITIAL_VALUE),
3629 initial_num_value= DECODE(NOUPDATE_initial_num_value,0,iINITIALNUMVALUE(j),initial_num_value),
3630 PARENT_ID=DECODE(NOUPDATE_PARENT_ID,0,DECODE(iPLANLEVEL(j),0,PARENT_ID,iPARENTID(j)),PARENT_ID),
3631 MINIMUM= DECODE(NOUPDATE_MINIMUM,0,iMINIMUM(j),MINIMUM),
3632 MAXIMUM= DECODE(NOUPDATE_MAXIMUM,0,iMAXIMUM(j),MAXIMUM),
3633 PS_NODE_TYPE= DECODE(NOUPDATE_PS_NODE_TYPE,0,iPSNODETYPE(j),PS_NODE_TYPE),
3634 FEATURE_TYPE= DECODE(NOUPDATE_FEATURE_TYPE,0,iFEATURETYPE(j),FEATURE_TYPE),
3635 PRODUCT_FLAG= DECODE(NOUPDATE_PRODUCT_FLAG,0,iPRODUCTFLAG(j),PRODUCT_FLAG),
3636 REFERENCE_ID= DECODE(NOUPDATE_REFERENCE_ID,0,iREFERENCEID(j),REFERENCE_ID),
3637 MULTI_CONFIG_FLAG= DECODE(NOUPDATE_MULTI_CONFIG_FLAG,0,iMULTICONFIGFLAG(j),MULTI_CONFIG_FLAG),
3638 ORDER_SEQ_FLAG= DECODE(NOUPDATE_ORDER_SEQ_FLAG,0,iORDERSEQFLAG(j),ORDER_SEQ_FLAG),
3639 SYSTEM_NODE_FLAG= DECODE(NOUPDATE_SYSTEM_NODE_FLAG,0,iSYSTEMNODEFLAG(j),SYSTEM_NODE_FLAG),
3640 TREE_SEQ= DECODE(NOUPDATE_TREE_SEQ,0,iTREESEQ(j),TREE_SEQ),
3641 COUNTED_OPTIONS_FLAG= DECODE(NOUPDATE_COUNTED_OPTIONS_FLAG,0,iCOUNTEDOPTIONSFLAG(j),COUNTED_OPTIONS_FLAG),
3642 UI_OMIT= DECODE(NOUPDATE_UI_OMIT,0,iUIOMIT(j),UI_OMIT),
3643 UI_SECTION= DECODE(NOUPDATE_UI_SECTION,0,iUISECTION(j),UI_SECTION),
3644 BOM_TREATMENT= DECODE(NOUPDATE_BOM_TREATMENT,0,iBOMTREATMENT(j),BOM_TREATMENT),
3645 ORIG_SYS_REF= DECODE(NOUPDATE_ORIG_SYS_REF,0,iORIGSYSREF(j),ORIG_SYS_REF),
3646 CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,iCHECKOUTUSER(j),CHECKOUT_USER),
3647 DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,iDELETEDFLAG(j),DELETED_FLAG),
3648 USER_NUM01= DECODE(NOUPDATE_USER_NUM01,0,iUSERNUM01(j),USER_NUM01),
3649 USER_NUM02= DECODE(NOUPDATE_USER_NUM02,0,iUSERNUM02(j),USER_NUM02),
3650 USER_NUM03= DECODE(NOUPDATE_USER_NUM03,0,iUSERNUM03(j),USER_NUM03),
3651 USER_NUM04= DECODE(NOUPDATE_USER_NUM04,0,iUSERNUM04(j),USER_NUM04),
3652 USER_STR01= DECODE(NOUPDATE_USER_STR01,0,iUSERSTR01(j),USER_STR01),
3653 USER_STR02= DECODE(NOUPDATE_USER_STR02,0,iUSERSTR02(j),USER_STR02),
3654 USER_STR03= DECODE(NOUPDATE_USER_STR03,0,iUSERSTR03(j),USER_STR03),
3655 USER_STR04= DECODE(NOUPDATE_USER_STR04,0,iUSERSTR04(j),USER_STR04),
3656 --CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
3657 LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
3658 EFFECTIVE_FROM= DECODE(NOUPDATE_EFF_FROM,0,iEFFECTIVEFROM(j),EFFECTIVE_FROM),
3659 EFFECTIVE_UNTIL= DECODE(NOUPDATE_EFF_TO,0,iEFFECTIVEUNTIL(j),EFFECTIVE_UNTIL),
3660 --CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,-UID,CREATED_BY),
3661 LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,-UID,LAST_UPDATED_BY),
3662 SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
3663 --EFFECTIVE_USAGE_MASK= DECODE(NOUPDATE_EFF_MASK,0,iEFFECTIVEUSAGEMASK(j),EFFECTIVE_USAGE_MASK),
3664 SO_ITEM_TYPE_CODE= DECODE(NOUPDATE_SO_ITEM_TYPE_CODE,0,iSOITEMTYPECODE(j),SO_ITEM_TYPE_CODE),
3665 MINIMUM_SELECTED= DECODE(NOUPDATE_MINIMUM_SELECTED,0,iMINIMUMSELECTED(j),MINIMUM_SELECTED),
3666 MAXIMUM_SELECTED= DECODE(NOUPDATE_MAXIMUM_SELECTED,0,iMAXIMUMSELECTED(j),MAXIMUM_SELECTED),
3667 BOM_REQUIRED_FLAG= DECODE(NOUPDATE_BOM_REQUIRED,0,iBOMREQUIRED(j),BOM_REQUIRED_FLAG),
3668 COMPONENT_SEQUENCE_ID= DECODE(NOUPDATE_COMPONENT_SEQUENCE_ID,0,iCOMPONENTSEQUENCEID(j),COMPONENT_SEQUENCE_ID),
3669 DECIMAL_QTY_FLAG= DECODE(NOUPDATE_DECIMAL_QTY_FLAG,0,iDECIMALQTYFLAG(j),DECIMAL_QTY_FLAG),
3670 QUOTEABLE_FLAG= DECODE(NOUPDATE_QUOTEABLE_FLAG,0,iQUOTEABLEFLAG(j),QUOTEABLE_FLAG),
3671 PRIMARY_UOM_CODE= DECODE(NOUPDATE_PRIMARY_UOM_CODE,0,iPRIMARYUOMCODE(j),PRIMARY_UOM_CODE),
3672 BOM_SORT_ORDER= DECODE(NOUPDATE_BOM_SORT_ORDER,0,iBOMSORTORDER(j),BOM_SORT_ORDER),
3673 COMPONENT_SEQUENCE_PATH=DECODE(NOUPDATE_SEQUENCE_PATH,0,iCOMPONENTSEQUENCEPATH(j),COMPONENT_SEQUENCE_PATH),
3674 IB_TRACKABLE= DECODE(NOUPDATE_IB_TRACKABLE,0,iIBTRACKABLE(j),IB_TRACKABLE),
3675 SRC_APPLICATION_ID= iSRCAPPLICATIONID(j),
3676 DISPLAY_IN_SUMMARY_FLAG=DECODE(NOUPDATE_DSPLY_SMRY_FLG,0,iDisplayInSummaryFlag(j),DISPLAY_IN_SUMMARY_FLAG),
3677 IB_LINK_ITEM_FLAG=DECODE(NOUPDATE_IBLINKITEM_FLG,0,iIBLinkItemFlag(j),IB_LINK_ITEM_FLAG),
3678 INSTANTIABLE_FLAG=DECODE(NOUPDATE_INSTANTIABLE_FLAG,0,iINSTANTIABLEFLAG(j),INSTANTIABLE_FLAG),
3679 SHIPPABLE_ITEM_FLAG = DECODE(NOUPDATE_SHIPPABLE_ITEM_FLAG,0,iShippableItemFlag(j), SHIPPABLE_ITEM_FLAG),
3680 INVENTORY_TRANSACTABLE_FLAG = DECODE(NOUPDATE_INV_TXN_FLAG, 0, iInventoryTransactableFlag(j), INVENTORY_TRANSACTABLE_FLAG),
3681 ASSEMBLE_TO_ORDER_FLAG = DECODE(NOUPDATE_ASM_TO_ORDER_FLAG, 0, iAssembleToOrder(j), ASSEMBLE_TO_ORDER_FLAG),
3682 SERIALIZABLE_ITEM_FLAG = DECODE(NOUPDATE_SERIAL_ITEM_FLAG, 0, iSerializableItemFlag(j), SERIALIZABLE_ITEM_FLAG)
3683 WHERE PS_NODE_ID=iPSNODEID(j);
3684
3685 nUpdateCount:= nUpdateCount + SQL%ROWCOUNT;
3686 nCommitCount:= nCommitCount + SQL%ROWCOUNT;
3687
3688 BEGIN -- bulk update
3689 FORALL j IN iPsNodeID.FIRST..iPsNodeId.LAST
3690 UPDATE CZ_IMP_PS_NODES
3691 SET REC_STATUS='OK'
3692 WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
3693 AND DISPOSITION='M';
3694
3695 /* COMMIT if the buffer size is reached */
3696 IF(nCommitCount>= COMMIT_SIZE) THEN
3697 COMMIT;
3698 nCommitCount:=0;
3699 END IF;
3700
3701 EXCEPTION
3702 WHEN OTHERS THEN
3703 l_debug:=0871;
3704 ROLLBACK; -- need to insert row by row to log error messages
3705 l_msg:=p_model_id||':'||SQLERRM;
3706 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'IMP_IM_XFR.XFR_PS_NODE('||l_debug||')',11276,inRun_Id);
3707 RAISE;
3708 END;
3709
3710 EXCEPTION -- bulk update
3711 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
3712 RAISE;
3713 WHEN OTHERS THEN
3714 l_debug:=0872;
3715 l_msg:=p_model_id||':'||SQLERRM;
3716 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'IMP_IM_XFR.XFR_PS_NODE('||l_debug||')',11276,inRun_Id);
3717 --
3718 -- changes for Solver
3719 -- raise an exception if maximum value is not specified in case of Generic Import plus FCE, CZ_BOM_DEFAULT_QTY_DOMN='N'
3720 --
3721 IF G_CONFIG_ENGINE_TYPE='F' AND FND_PROFILE.VALUE('CZ_BOM_DEFAULT_QTY_DOMN')='N' AND
3722 inXFR_GROUP='GENERIC' THEN
3723 FOR j IN iPsNodeID.FIRST..iPsNodeId.LAST
3724 LOOP
3725 IF (iPSNODETYPE(j)=263 AND (iMAXIMUMSELECTED(j) IS NULL OR iMAXIMUMSELECTED(j) IN(0,-1))) OR
3726 (iPSNODETYPE(j)<>263 AND (iMAXIMUM(j) IS NULL OR iMAXIMUM(j) IN(0,-1))) THEN
3727 ROLLBACK;
3728 x_error:=CZ_UTILS.LOG_REPORT('Fatal Error : CZ_IMP_PS_NODES.MAXIMUM should be specified in Generic Import',1,'CZ_IMP_PS_NODE.XFR_PS_NODE',11276,inRun_Id);
3729 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
3730 END IF;
3731 END LOOP;
3732 END IF;
3733
3734 FOR j IN iPsNodeID.FIRST..iPsNodeId.LAST LOOP -- single row loop
3735
3736 IF (FAILED >= MAX_ERR) THEN
3737 ROLLBACK;
3738 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_PS_NODE.XFR_PS_NODE',11276,inRun_Id);
3739 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
3740 END IF;
3741
3742
3743 BEGIN -- single row update
3744 UPDATE CZ_PS_NODES SET
3745 DEVL_PROJECT_ID= DECODE(NOUPDATE_DEVL_PROJECT_ID,0,iDEVLPROJECTID(j),DEVL_PROJECT_ID),
3746 FROM_POPULATOR_ID= DECODE(NOUPDATE_FROM_POPULATOR_ID,0,iFROMPOPULATORID(j),FROM_POPULATOR_ID),
3747 PROPERTY_BACKPTR= DECODE(NOUPDATE_PROPERTY_BACKPTR,0,iPROPERTYBACKPTR(j),PROPERTY_BACKPTR),
3748 ITEM_TYPE_BACKPTR= DECODE(NOUPDATE_ITEM_TYPE_BACKPTR,0,iITEMTYPEBACKPTR(j),ITEM_TYPE_BACKPTR),
3749 INTL_TEXT_ID= DECODE(NOUPDATE_INTL_TEXT_ID,0,iINTLTEXTID(j),INTL_TEXT_ID),
3750 SUB_CONS_ID= DECODE(NOUPDATE_SUB_CONS_ID,0,iSUBCONSID(j),SUB_CONS_ID),
3751 ITEM_ID= DECODE(NOUPDATE_ITEM_ID,0,iITEMID(j),ITEM_ID),
3752 NAME= DECODE(NOUPDATE_NAME,0,iNAME(j),NAME),
3753 RESOURCE_FLAG= DECODE(NOUPDATE_RESOURCE_FLAG,0,iRESOURCEFLAG(j),RESOURCE_FLAG),
3754 INITIAL_VALUE= DECODE(NOUPDATE_INITIAL_VALUE,0,iINITIALVALUE(j),INITIAL_VALUE),
3755 initial_num_value= DECODE(NOUPDATE_initial_num_value,0,iINITIALNUMVALUE(j),initial_num_value),
3756 PARENT_ID=DECODE(NOUPDATE_PARENT_ID,0,DECODE(iPLANLEVEL(j),0,PARENT_ID,iPARENTID(j)),PARENT_ID),
3757 MINIMUM= DECODE(NOUPDATE_MINIMUM,0,iMINIMUM(j),MINIMUM),
3758 MAXIMUM= DECODE(NOUPDATE_MAXIMUM,0,iMAXIMUM(j),MAXIMUM),
3759 PS_NODE_TYPE= DECODE(NOUPDATE_PS_NODE_TYPE,0,iPSNODETYPE(j),PS_NODE_TYPE),
3760 FEATURE_TYPE= DECODE(NOUPDATE_FEATURE_TYPE,0,iFEATURETYPE(j),FEATURE_TYPE),
3761 PRODUCT_FLAG= DECODE(NOUPDATE_PRODUCT_FLAG,0,iPRODUCTFLAG(j),PRODUCT_FLAG),
3762 REFERENCE_ID= DECODE(NOUPDATE_REFERENCE_ID,0,iREFERENCEID(j),REFERENCE_ID),
3763 MULTI_CONFIG_FLAG= DECODE(NOUPDATE_MULTI_CONFIG_FLAG,0,iMULTICONFIGFLAG(j),MULTI_CONFIG_FLAG),
3764 ORDER_SEQ_FLAG= DECODE(NOUPDATE_ORDER_SEQ_FLAG,0,iORDERSEQFLAG(j),ORDER_SEQ_FLAG),
3765 SYSTEM_NODE_FLAG= DECODE(NOUPDATE_SYSTEM_NODE_FLAG,0,iSYSTEMNODEFLAG(j),SYSTEM_NODE_FLAG),
3766 TREE_SEQ= DECODE(NOUPDATE_TREE_SEQ,0,iTREESEQ(j),TREE_SEQ),
3767 COUNTED_OPTIONS_FLAG= DECODE(NOUPDATE_COUNTED_OPTIONS_FLAG,0,iCOUNTEDOPTIONSFLAG(j),COUNTED_OPTIONS_FLAG),
3768 UI_OMIT= DECODE(NOUPDATE_UI_OMIT,0,iUIOMIT(j),UI_OMIT),
3769 UI_SECTION= DECODE(NOUPDATE_UI_SECTION,0,iUISECTION(j),UI_SECTION),
3770 BOM_TREATMENT= DECODE(NOUPDATE_BOM_TREATMENT,0,iBOMTREATMENT(j),BOM_TREATMENT),
3771 ORIG_SYS_REF= DECODE(NOUPDATE_ORIG_SYS_REF,0,iORIGSYSREF(j),ORIG_SYS_REF),
3772 CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,iCHECKOUTUSER(j),CHECKOUT_USER),
3773 DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,iDELETEDFLAG(j),DELETED_FLAG),
3774 USER_NUM01= DECODE(NOUPDATE_USER_NUM01,0,iUSERNUM01(j),USER_NUM01),
3775 USER_NUM02= DECODE(NOUPDATE_USER_NUM02,0,iUSERNUM02(j),USER_NUM02),
3776 USER_NUM03= DECODE(NOUPDATE_USER_NUM03,0,iUSERNUM03(j),USER_NUM03),
3777 USER_NUM04= DECODE(NOUPDATE_USER_NUM04,0,iUSERNUM04(j),USER_NUM04),
3778 USER_STR01= DECODE(NOUPDATE_USER_STR01,0,iUSERSTR01(j),USER_STR01),
3779 USER_STR02= DECODE(NOUPDATE_USER_STR02,0,iUSERSTR02(j),USER_STR02),
3780 USER_STR03= DECODE(NOUPDATE_USER_STR03,0,iUSERSTR03(j),USER_STR03),
3781 USER_STR04= DECODE(NOUPDATE_USER_STR04,0,iUSERSTR04(j),USER_STR04),
3782 --CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
3783 LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
3784 EFFECTIVE_FROM= DECODE(NOUPDATE_EFF_FROM,0,iEFFECTIVEFROM(j),EFFECTIVE_FROM),
3785 EFFECTIVE_UNTIL= DECODE(NOUPDATE_EFF_TO,0,iEFFECTIVEUNTIL(j),EFFECTIVE_UNTIL),
3786 --CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,-UID,CREATED_BY),
3787 LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,-UID,LAST_UPDATED_BY),
3788 SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
3789 --EFFECTIVE_USAGE_MASK= DECODE(NOUPDATE_EFF_MASK,0,iEFFECTIVEUSAGEMASK(j),EFFECTIVE_USAGE_MASK),
3790 SO_ITEM_TYPE_CODE= DECODE(NOUPDATE_SO_ITEM_TYPE_CODE,0,iSOITEMTYPECODE(j),SO_ITEM_TYPE_CODE),
3791 MINIMUM_SELECTED= DECODE(NOUPDATE_MINIMUM_SELECTED,0,iMINIMUMSELECTED(j),MINIMUM_SELECTED),
3792 MAXIMUM_SELECTED= DECODE(NOUPDATE_MAXIMUM_SELECTED,0,iMAXIMUMSELECTED(j),MAXIMUM_SELECTED),
3793 BOM_REQUIRED_FLAG= DECODE(NOUPDATE_BOM_REQUIRED,0,iBOMREQUIRED(j),BOM_REQUIRED_FLAG),
3794 COMPONENT_SEQUENCE_ID= DECODE(NOUPDATE_COMPONENT_SEQUENCE_ID,0,iCOMPONENTSEQUENCEID(j),COMPONENT_SEQUENCE_ID),
3795 DECIMAL_QTY_FLAG= DECODE(NOUPDATE_DECIMAL_QTY_FLAG,0,iDECIMALQTYFLAG(j),DECIMAL_QTY_FLAG),
3796 QUOTEABLE_FLAG= DECODE(NOUPDATE_QUOTEABLE_FLAG,0,iQUOTEABLEFLAG(j),QUOTEABLE_FLAG),
3797 PRIMARY_UOM_CODE= DECODE(NOUPDATE_PRIMARY_UOM_CODE,0,iPRIMARYUOMCODE(j),PRIMARY_UOM_CODE),
3798 BOM_SORT_ORDER= DECODE(NOUPDATE_BOM_SORT_ORDER,0,iBOMSORTORDER(j),BOM_SORT_ORDER),
3799 COMPONENT_SEQUENCE_PATH=DECODE(NOUPDATE_SEQUENCE_PATH,0,iCOMPONENTSEQUENCEPATH(j),COMPONENT_SEQUENCE_PATH),
3800 IB_TRACKABLE= DECODE(NOUPDATE_IB_TRACKABLE,0,iIBTRACKABLE(j),IB_TRACKABLE),
3801 SRC_APPLICATION_ID= iSRCAPPLICATIONID(j),
3802 DISPLAY_IN_SUMMARY_FLAG=DECODE(NOUPDATE_DSPLY_SMRY_FLG,0,iDisplayInSummaryFlag(j),DISPLAY_IN_SUMMARY_FLAG),
3803 IB_LINK_ITEM_FLAG=DECODE(NOUPDATE_IBLINKITEM_FLG,0,iIBLinkItemFlag(j),IB_LINK_ITEM_FLAG),
3804 INSTANTIABLE_FLAG=DECODE(NOUPDATE_INSTANTIABLE_FLAG,0,iINSTANTIABLEFLAG(j),INSTANTIABLE_FLAG),
3805 SHIPPABLE_ITEM_FLAG = DECODE(NOUPDATE_SHIPPABLE_ITEM_FLAG,0,iShippableItemFlag(j), SHIPPABLE_ITEM_FLAG),
3806 INVENTORY_TRANSACTABLE_FLAG = DECODE(NOUPDATE_INV_TXN_FLAG, 0, iInventoryTransactableFlag(j), INVENTORY_TRANSACTABLE_FLAG),
3807 ASSEMBLE_TO_ORDER_FLAG = DECODE(NOUPDATE_ASM_TO_ORDER_FLAG, 0, iAssembleToOrder(j), ASSEMBLE_TO_ORDER_FLAG),
3808 SERIALIZABLE_ITEM_FLAG = DECODE(NOUPDATE_SERIAL_ITEM_FLAG, 0, iSerializableItemFlag(j), SERIALIZABLE_ITEM_FLAG)
3809 WHERE PS_NODE_ID=iPSNODEID(j);
3810
3811 nUpdateCount:= nUpdateCount + 1;
3812
3813 UPDATE CZ_IMP_PS_NODES -- single row update
3814 SET REC_STATUS='OK'
3815 WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
3816 AND DISPOSITION='M';
3817
3818 nCommitCount:=nCommitCount+1;
3819 /* COMMIT if the buffer size is reached */
3820 IF(nCommitCount>= COMMIT_SIZE) THEN
3821 COMMIT;
3822 nCommitCount:=0;
3823 END IF;
3824
3825 EXCEPTION -- single row update
3826 WHEN OTHERS THEN
3827 FAILED:=FAILED +1;
3828 UPDATE CZ_IMP_PS_NODES
3829 SET REC_STATUS='ERR'
3830 WHERE PS_NODE_ID=iPSNODEID(j) AND RUN_ID=inRUN_ID
3831 AND DISPOSITION='M';
3832
3833 -- get model name
3834 SELECT name INTO l_model_name
3835 FROM cz_imp_devl_project
3836 WHERE devl_project_id=p_model_id
3837 AND deleted_flag='0'
3838 AND run_id=inRun_ID
3839 AND rec_status='OK'
3840 AND rownum <2;
3841
3842 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_XFR_NODE_M',
3843 'MODELNAME', l_model_name,
3844 'NODENAME', iName(j),
3845 'NODEID', iPsNodeId(j),
3846 'ERRORTEXT', SQLERRM);
3847 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
3848 END; -- single row update
3849 END LOOP; -- single row loop
3850 END; -- bulk update
3851 END IF; -- IF count > 0
3852 END LOOP; -- bulk fetch for update
3853
3854 IF c_xfr_psnode%ISOPEN THEN
3855 CLOSE c_xfr_psnode;
3856 END IF;
3857
3858 END update_ps_nodes;
3859
3860
3861 -- private procdure to delete PS nodes that are no longer present in the cz_imp_ps_nodes of this run id
3862 -- deletes the nodes logically and calls the cz_refs.delete_node logically to delete the expls
3863 -- for refs, connectors and non-virtual components
3864
3865 PROCEDURE delete_ps_nodes(p_model_id IN NUMBER, x_retcode OUT NOCOPY NUMBER)
3866 IS
3867 l_ps_node_id tPsNodeId;
3868 l_ps_node_type tPsNodeType;
3869
3870 CURSOR C1 IS
3871 SELECT ps_node_id, ps_node_type
3872 FROM cz_ps_nodes a
3873 WHERE deleted_flag = '0'
3874 AND devl_project_id = p_model_id
3875 AND ps_node_type IN (cnReference, cnConnector, cnComponent)
3876 AND NOT EXISTS (SELECT NULL FROM cz_imp_ps_nodes
3877 WHERE orig_sys_ref = a.orig_sys_ref
3878 AND devl_project_id = p_model_id
3879 AND src_application_id = a.src_application_id
3880 AND run_id=inRun_Id);
3881 BEGIN
3882 x_retcode := 0;
3883 OPEN C1;
3884 FETCH C1 BULK COLLECT INTO l_ps_node_id,l_ps_node_type;
3885 CLOSE C1;
3886
3887 UPDATE cz_ps_nodes a
3888 SET deleted_flag = '1'
3889 WHERE devl_project_id = p_model_id
3890 AND NOT EXISTS (SELECT NULL FROM cz_imp_ps_nodes
3891 WHERE orig_sys_ref = a.orig_sys_ref
3892 AND devl_project_id = p_model_id
3893 AND src_application_id = a.src_application_id
3894 AND run_id=inRun_Id);
3895
3896 IF (l_ps_node_id.COUNT > 0) THEN
3897 FOR i IN l_ps_node_id.FIRST..l_ps_node_id.LAST LOOP
3898 cz_refs.delete_Node(l_ps_node_id(i),l_ps_node_type(i), p_out_err, '1');
3899 IF (p_out_err > 0) THEN
3900 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_CZREFS_DELNODE',
3901 'MODELID', p_model_id,
3902 'NODEID', l_ps_node_id(i),
3903 'RUNID', p_out_err);
3904 x_error:=CZ_UTILS.LOG_REPORT(l_msg ,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
3905 x_retcode := 1;
3906 END IF;
3907 END LOOP;
3908 END IF;
3909 END delete_ps_nodes;
3910
3911 BEGIN
3912 -- Get the Update Flags for each column
3913 NOUPDATE_PS_NODE_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','PS_NODE_ID',inXFR_GROUP);
3914 NOUPDATE_DEVL_PROJECT_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','DEVL_PROJECT_ID',inXFR_GROUP);
3915 NOUPDATE_FROM_POPULATOR_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','FROM_POPULATOR_ID',inXFR_GROUP);
3916 NOUPDATE_PROPERTY_BACKPTR := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','PROPERTY_BACKPTR',inXFR_GROUP);
3917 NOUPDATE_ITEM_TYPE_BACKPTR := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','ITEM_TYPE_BACKPTR',inXFR_GROUP);
3918 NOUPDATE_INTL_TEXT_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','INTL_TEXT_ID',inXFR_GROUP);
3919 NOUPDATE_SUB_CONS_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','SUB_CONS_ID',inXFR_GROUP);
3920 NOUPDATE_ITEM_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','ITEM_ID',inXFR_GROUP);
3921 NOUPDATE_NAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','NAME',inXFR_GROUP);
3922 NOUPDATE_RESOURCE_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','RESOURCE_FLAG',inXFR_GROUP);
3923 NOUPDATE_INITIAL_VALUE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','INITIAL_VALUE',inXFR_GROUP);
3924 NOUPDATE_initial_num_value := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','initial_num_value',inXFR_GROUP);
3925 NOUPDATE_PARENT_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','PARENT_ID',inXFR_GROUP);
3926 NOUPDATE_MINIMUM := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','MINIMUM',inXFR_GROUP);
3927 NOUPDATE_MAXIMUM := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','MAXIMUM',inXFR_GROUP);
3928 NOUPDATE_PS_NODE_TYPE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','PS_NODE_TYPE',inXFR_GROUP);
3929 NOUPDATE_FEATURE_TYPE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','FEATURE_TYPE',inXFR_GROUP);
3930 NOUPDATE_PRODUCT_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','PRODUCT_FLAG',inXFR_GROUP);
3931 NOUPDATE_REFERENCE_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','REFERENCE_ID',inXFR_GROUP);
3932 NOUPDATE_MULTI_CONFIG_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','MULTI_CONFIG_FLAG',inXFR_GROUP);
3933 NOUPDATE_ORDER_SEQ_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','ORDER_SEQ_FLAG',inXFR_GROUP);
3934 NOUPDATE_SYSTEM_NODE_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','SYSTEM_NODE_FLAG',inXFR_GROUP);
3935 NOUPDATE_TREE_SEQ := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','TREE_SEQ',inXFR_GROUP);
3936 NOUPDATE_COUNTED_OPTIONS_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','COUNTED_OPTIONS_FLAG',inXFR_GROUP);
3937 NOUPDATE_UI_OMIT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','UI_OMIT',inXFR_GROUP);
3938 NOUPDATE_UI_SECTION := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','UI_SECTION',inXFR_GROUP);
3939 NOUPDATE_BOM_TREATMENT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','BOM_TREATMENT',inXFR_GROUP);
3940 NOUPDATE_ORIG_SYS_REF := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','ORIG_SYS_REF',inXFR_GROUP);
3941 NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','CHECKOUT_USER',inXFR_GROUP);
3942 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','DELETED_FLAG',inXFR_GROUP);
3943 NOUPDATE_EFF_FROM := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','EFFECTIVE_FROM',inXFR_GROUP);
3944 NOUPDATE_EFF_TO := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','EFFECTIVE_UNTIL',inXFR_GROUP);
3945 NOUPDATE_EFF_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','EFFECTIVE_USAGE_MASK',inXFR_GROUP);
3946 NOUPDATE_USER_STR01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_STR01',inXFR_GROUP);
3947 NOUPDATE_USER_STR02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_STR02',inXFR_GROUP);
3948 NOUPDATE_USER_STR03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_STR03',inXFR_GROUP);
3949 NOUPDATE_USER_STR04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_STR04',inXFR_GROUP);
3950 NOUPDATE_USER_NUM01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_NUM01',inXFR_GROUP);
3951 NOUPDATE_USER_NUM02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_NUM02',inXFR_GROUP);
3952 NOUPDATE_USER_NUM03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_NUM03',inXFR_GROUP);
3953 NOUPDATE_USER_NUM04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','USER_NUM04',inXFR_GROUP);
3954 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','CREATION_DATE',inXFR_GROUP);
3955 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','LAST_UPDATE_DATE',inXFR_GROUP);
3956 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','CREATED_BY',inXFR_GROUP);
3957 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','LAST_UPDATED_BY',inXFR_GROUP);
3958 NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','SECURITY_MASK',inXFR_GROUP);
3959 NOUPDATE_SO_ITEM_TYPE_CODE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','SO_ITEM_TYPE_CODE',inXFR_GROUP);
3960 NOUPDATE_MINIMUM_SELECTED := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','MINIMUM_SELECTED',inXFR_GROUP);
3961 NOUPDATE_MAXIMUM_SELECTED := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','MAXIMUM_SELECTED',inXFR_GROUP);
3962 NOUPDATE_BOM_REQUIRED := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','BOM_REQUIRED_FLAG',inXFR_GROUP);
3963 NOUPDATE_COMPONENT_SEQUENCE_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','COMPONENT_SEQUENCE_ID',inXFR_GROUP);
3964 NOUPDATE_DECIMAL_QTY_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','DECIMAL_QTY_FLAG',inXFR_GROUP);
3965 NOUPDATE_QUOTEABLE_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','QUOTEABLE_FLAG',inXFR_GROUP);
3966 NOUPDATE_PRIMARY_UOM_CODE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','PRIMARY_UOM_CODE',inXFR_GROUP);
3967 NOUPDATE_BOM_SORT_ORDER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','BOM_SORT_ORDER',inXFR_GROUP);
3968 NOUPDATE_SEQUENCE_PATH := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','COMPONENT_SEQUENCE_PATH',inXFR_GROUP);
3969 NOUPDATE_IB_TRACKABLE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','IB_TRACKABLE',inXFR_GROUP);
3970 NOUPDATE_DSPLY_SMRY_FLG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','DISPLAY_IN_SUMMARY_FLAG',inXFR_GROUP);
3971 NOUPDATE_IBLINKITEM_FLG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','IB_LINK_ITEM_FLAG',inXFR_GROUP);
3972 NOUPDATE_INSTANTIABLE_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','INSTANTIABLE_FLAG',inXFR_GROUP);
3973
3974 --Updates of instantiable_flag are always prohibited for BOM import.
3975
3976 IF(inXFR_GROUP = 'IMPORT')THEN NOUPDATE_INSTANTIABLE_FLAG := 1; END IF;
3977
3978 NOUPDATE_SHIPPABLE_ITEM_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','SHIPPABLE_ITEM_FLAG',inXFR_GROUP);
3979 NOUPDATE_INV_TXN_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','INVENTORY_TRANSACTABLE_FLAG',inXFR_GROUP);
3980 NOUPDATE_ASM_TO_ORDER_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','ASSEMBLE_TO_ORDER_FLAG ',inXFR_GROUP);
3981 NOUPDATE_SERIAL_ITEM_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PS_NODES','SERIALIZABLE_ITEM_FLAG',inXFR_GROUP);
3982
3983 OPEN C1;
3984 FETCH C1 BULK COLLECT INTO
3985 l_c1_plan_level_tbl,l_c1_prj_id_tbl,l_c1_node_id_tbl, l_c1_dis_tbl,l_c1_ref_model_id_tbl,
3986 l_c1_nodetype_tbl,l_c1_min_tbl,l_c1_max_tbl,l_c1_rec_status_tbl,l_c1_name_tbl,l_c1_model_type;
3987 CLOSE C1;
3988
3989 OPEN C2;
3990 FETCH C2 BULK COLLECT INTO
3991 l_c2_plan_level_tbl,l_c2_prj_id_tbl,l_c2_node_id_tbl,l_c2_dis_tbl,l_c2_ref_model_id_tbl,
3992 l_c2_nodetype_tbl,l_c2_min_tbl,l_c2_max_tbl,l_c2_rec_status_tbl,l_c2_name_tbl,l_c2_model_type;
3993 CLOSE C2;
3994
3995 OPEN C3;
3996 FETCH c3 BULK COLLECT INTO
3997 l_c3_plan_level_tbl,l_c3_prj_id_tbl,l_c3_node_id_tbl,l_c3_dis_tbl,l_c3_ref_id_tbl,
3998 l_c3_nodetype_tbl,l_c3_min_tbl,l_c3_max_tbl,l_c3_rec_status_tbl,l_c3_model_type;
3999 CLOSE C3;
4000
4001 IF (l_c1_prj_id_tbl.COUNT = 0) THEN
4002 GOTO PROCESS_C2;
4003 END IF;
4004 -- processing C1: models referenced but have no refs
4005
4006 -- if a new model fails to insert, it is marked 'ERR' and
4007 -- any referencing models in C2 and C3 will be marked 'SKP'
4008
4009 FOR i IN l_c1_prj_id_tbl.FIRST..l_c1_prj_id_tbl.LAST LOOP
4010
4011 l_retcode := 0;
4012 p_out_err := 0;
4013
4014 -- process any ps nodes with disposition of insert
4015
4016 IF l_c1_prj_id_tbl(i) <> l_last_model_id THEN
4017 insert_ps_nodes(l_c1_prj_id_tbl(i), l_retcode);
4018 END IF;
4019
4020 IF l_retcode = 1 THEN
4021
4022 l_c1_rec_status_tbl(i):='ERR';
4023
4024 -- also set rec_status for for any references this model in C2 and C3
4025
4026 IF l_c2_prj_id_tbl.COUNT > 0 THEN
4027 -- also set rec_status for for any nodes referencing this model
4028 FOR j IN l_c2_prj_id_tbl.FIRST..l_c2_prj_id_tbl.LAST LOOP
4029 IF l_c2_prj_id_tbl(j) = l_c1_ref_model_id_tbl(i) THEN
4030 l_c2_rec_status_tbl(j):='SKP';
4031 END IF;
4032 END LOOP;
4033 END IF;
4034
4035 IF l_c3_prj_id_tbl.COUNT > 0 THEN
4036 FOR j IN l_c3_prj_id_tbl.FIRST..l_c3_prj_id_tbl.LAST LOOP
4037 IF l_c3_prj_id_tbl(j)=l_c1_ref_model_id_tbl(i) THEN
4038 l_c3_rec_status_tbl(j):='SKP';
4039 END IF;
4040 END LOOP;
4041 END IF;
4042
4043 ELSE
4044
4045 -- process any ps nodes with disposition of update for this model
4046
4047 IF l_c1_prj_id_tbl(i) <> l_last_model_id THEN
4048
4049 update_ps_nodes(l_c1_prj_id_tbl(i));
4050
4051 -- call cz_refs.check_node for ROOT only, for now
4052
4053 IF l_c1_prj_id_tbl(i)=l_c1_node_id_tbl(i) THEN
4054
4055 cz_refs.check_Node(l_c1_prj_id_tbl(i),
4056 l_c1_node_id_tbl(i),
4057 l_c1_max_tbl(i),
4058 l_c1_min_tbl(i),
4059 NULL,
4060 p_out_err,
4061 p_out_virtual_flag,
4062 '0',
4063 null,
4064 l_c1_nodetype_tbl(i),
4065 NULL);
4066 END IF;
4067
4068 IF (p_out_err > 0) THEN
4069
4070 l_c1_rec_status_tbl(i):='ERR';
4071
4072 IF l_c2_prj_id_tbl.COUNT > 0 THEN
4073 -- also set rec_status for for any nodes referencing this model
4074 FOR j IN l_c2_prj_id_tbl.FIRST..l_c2_prj_id_tbl.LAST LOOP
4075 IF l_c2_prj_id_tbl(j)=l_c1_ref_model_id_tbl(i) THEN
4076 l_c2_rec_status_tbl(j):='SKP';
4077 END IF;
4078 END LOOP;
4079 END IF;
4080
4081 IF l_c3_ref_id_tbl.COUNT > 0 THEN
4082 FOR j IN l_c3_prj_id_tbl.FIRST..l_c3_prj_id_tbl.LAST LOOP
4083 IF l_c3_prj_id_tbl(j)=l_c1_ref_model_id_tbl(i) THEN
4084 l_c3_rec_status_tbl(j):='SKP';
4085 END IF;
4086 END LOOP;
4087 END IF;
4088
4089 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_ROOT_CHECKNODE',
4090 'MODELID', l_c1_prj_id_tbl(i),
4091 'PSNODEID', l_c1_node_id_tbl(i),
4092 'REFID', NULL);
4093 x_error:=CZ_UTILS.LOG_REPORT(l_msg ,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
4094
4095 ELSE
4096 -- Nodes not in this run id are assumed deleted - non-BOM models only
4097 IF l_c1_model_type(i) NOT IN ('A','P','N') THEN
4098 l_retcode := 0;
4099 delete_ps_nodes(l_c1_prj_id_tbl(i),l_retcode);
4100 IF (l_retcode = 1) THEN
4101 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_RB_DELNODE', 'MODELID', l_c1_prj_id_tbl(i));
4102 x_error:=CZ_UTILS.LOG_REPORT(l_msg ,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
4103 l_c1_rec_status_tbl(i):='ERR';
4104 ROLLBACK;
4105 END IF;
4106 END IF;
4107 IF (l_retcode = 0) THEN
4108 l_c1_rec_status_tbl(i):='OK';
4109 COMMIT;
4110 -- get model name
4111 SELECT name INTO l_model_name
4112 FROM cz_imp_devl_project
4113 WHERE devl_project_id=l_c1_prj_id_tbl(i)
4114 AND deleted_flag='0'
4115 AND run_id=inRun_ID
4116 AND rec_status='OK'
4117 AND rownum <2;
4118
4119 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_MODEL_IMPORTED',
4120 'MODELNAME', l_model_name,
4121 'MODELID', l_c1_prj_id_tbl(i));
4122 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
4123 END IF;
4124 END IF; -- if p_out_err
4125 END IF; -- last
4126 END IF; -- if retcode
4127
4128 l_last_model_id := l_c1_prj_id_tbl(i);
4129
4130 END LOOP; -- c1 models
4131
4132 <<PROCESS_C2>>
4133
4134 -- process C2
4135
4136 -- before processing PS nodes, check the status of each model in C2 and if not to be processed (SKP or ERR)
4137 -- then mark the models referencing it in both C2 and C3 to SKP
4138
4139 IF (l_c2_prj_id_tbl.COUNT = 0) THEN
4140 GOTO PROCESS_C3;
4141 END IF;
4142
4143 FOR i IN l_c2_prj_id_tbl.FIRST..l_c2_prj_id_tbl.LAST LOOP
4144
4145 l_retcode := 0;
4146 p_out_err := 0;
4147
4148 IF l_c2_rec_status_tbl(i) IN ('ERR','SKP') THEN
4149
4150 FOR j IN l_c2_prj_id_tbl.FIRST..l_c2_prj_id_tbl.LAST LOOP
4151 IF l_c2_prj_id_tbl(j)=l_c2_ref_model_id_tbl(i) THEN
4152 l_c2_rec_status_tbl(j):= 'SKP';
4153 END IF;
4154 END LOOP;
4155
4156 IF l_c3_prj_id_tbl.COUNT > 0 THEN
4157 FOR j IN l_c3_prj_id_tbl.FIRST..l_c3_prj_id_tbl.LAST LOOP
4158 IF l_c3_prj_id_tbl(j)=l_c2_ref_model_id_tbl(i) THEN
4159 l_c3_rec_status_tbl(j):='SKP';
4160 END IF;
4161 END LOOP;
4162 END IF;
4163
4164 ELSE
4165
4166 -- to be processed, so process the insert it not already done so
4167
4168 IF l_c2_prj_id_tbl(i) <> l_last_model_id THEN
4169 l_retcode:=0;
4170 insert_ps_nodes(l_c2_prj_id_tbl(i), l_retcode);
4171 END IF;
4172
4173 -- if this C2 model errored out then update status of this model to 'ERR' and search
4174 -- for any references to this model in C2 and C3 and set the status of those to 'SKP'
4175
4176 IF l_retcode = 1 THEN
4177
4178 l_c2_rec_status_tbl(i):='ERR';
4179
4180 FOR j IN l_c2_prj_id_tbl.FIRST..l_c2_prj_id_tbl.LAST LOOP
4181 IF l_c2_prj_id_tbl(j)=l_c2_prj_id_tbl(i) THEN
4182 l_c2_rec_status_tbl(j):='ERR';
4183 END IF;
4184 END LOOP;
4185
4186 IF l_c3_prj_id_tbl.COUNT > 0 THEN
4187 FOR j IN l_c3_ref_id_tbl.FIRST..l_c3_ref_id_tbl.LAST LOOP
4188 IF l_c3_ref_id_tbl(j)=l_c2_prj_id_tbl(i) THEN
4189 l_c3_rec_status_tbl(j):='SKP';
4190 END IF;
4191 END LOOP;
4192 END IF;
4193
4194 ELSE
4195
4196 -- process the PS nodes with disposition of update, if already not done so
4197
4198 IF l_c2_prj_id_tbl(i) <> l_last_model_id THEN
4199
4200 update_ps_nodes(l_c2_prj_id_tbl(i));
4201
4202 -- call check_node for ROOT node only
4203
4204 IF l_c2_prj_id_tbl(i)=l_c2_node_id_tbl(i) THEN
4205
4206 cz_refs.check_Node(l_c2_prj_id_tbl(i),
4207 l_c2_node_id_tbl(i),
4208 l_c2_max_tbl(i),
4209 l_c2_min_tbl(i),
4210 NULL,
4211 p_out_err,
4212 p_out_virtual_flag,
4213 '0',
4214 null,
4215 l_c2_nodetype_tbl(i),
4216 NULL);
4217 END IF;
4218
4219 IF (p_out_err > 0) THEN
4220
4221 l_c2_rec_status_tbl(i):='ERR';
4222
4223 FOR j IN l_c2_prj_id_tbl.FIRST..l_c2_prj_id_tbl.LAST LOOP
4224 IF l_c2_prj_id_tbl(j)=l_c2_prj_id_tbl(i) THEN
4225 l_c2_rec_status_tbl(j):='ERR';
4226 END IF;
4227 END LOOP;
4228
4229 IF l_c3_prj_id_tbl.COUNT > 0 THEN
4230 FOR j IN l_c3_ref_id_tbl.FIRST..l_c3_ref_id_tbl.LAST LOOP
4231 IF l_c3_ref_id_tbl(j)=l_c2_prj_id_tbl(i) THEN
4232 l_c3_rec_status_tbl(j):='SKP';
4233 END IF;
4234 END LOOP;
4235 END IF;
4236
4237 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_ROOT_CHECKNODE',
4238 'MODELID', l_c2_prj_id_tbl(i),
4239 'PSNODEID', l_c2_node_id_tbl(i),
4240 'REFID', NULL);
4241 x_error:=CZ_UTILS.LOG_REPORT(l_msg ,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
4242 ELSE
4243 -- Nodes not in this run id are assumed deleted - non-BOM models only
4244 IF l_c2_model_type(i) NOT IN ('A','P','N') THEN
4245 l_retcode := 0;
4246 delete_ps_nodes(l_c2_prj_id_tbl(i),l_retcode);
4247 IF (l_retcode = 1) THEN
4248 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_RB_DELNODE', 'MODELID', l_c2_prj_id_tbl(i));
4249 x_error:=CZ_UTILS.LOG_REPORT(l_msg ,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
4250 l_c2_rec_status_tbl(i):='ERR';
4251 ROLLBACK;
4252 END IF;
4253 END IF;
4254 IF (l_retcode = 0) THEN
4255 l_c2_rec_status_tbl(i):='OK';
4256 COMMIT;
4257 SELECT name INTO l_model_name
4258 FROM cz_imp_devl_project
4259 WHERE devl_project_id=l_c2_prj_id_tbl(i)
4260 AND deleted_flag='0'
4261 AND run_id=inRun_ID
4262 AND rec_status='OK'
4263 AND rownum <2;
4264 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_MODEL_IMPORTED',
4265 'MODELNAME', l_model_name,
4266 'MODELID', l_c2_prj_id_tbl(i));
4267 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
4268 END IF;
4269 END IF; -- if p_out_err
4270 END IF; -- last
4271 END IF; -- if retcode
4272
4273 END IF; -- if rec status
4274
4275 l_last_model_id := l_c2_prj_id_tbl(i);
4276
4277 END LOOP; -- c2 models
4278
4279 <<PROCESS_C3>>
4280
4281 IF (l_c3_prj_id_tbl.COUNT = 0) THEN
4282 GOTO UPDATE_IMP_TABLE;
4283 END IF;
4284
4285 -- Process C3 models
4286
4287 -- The models here are not referenced, the PS nodes will be processed, and if root node fails to insert
4288 -- the model will be set to 'ERR'
4289 -- But before processing the PS nodes, chekc the status of each model here and if it is 'SKP' then
4290 -- do not process
4291
4292 FOR i IN l_c3_prj_id_tbl.FIRST..l_c3_prj_id_tbl.LAST LOOP
4293
4294 l_retcode := 0;
4295 p_out_err := 0;
4296
4297 -- porcess if not 'ERR' or 'SKP'
4298
4299 IF l_c3_rec_status_tbl(i) NOT IN ('ERR','SKP') THEN
4300
4301 -- process PS nodes with disposition of insert if haven't already done so
4302 IF l_c3_prj_id_tbl(i) <> l_last_model_id THEN
4303 insert_ps_nodes(l_c3_prj_id_tbl(i), l_retcode);
4304 END IF;
4305
4306 IF l_retcode = 1 THEN
4307 l_c3_rec_status_tbl(i):='ERR';
4308 ELSE
4309
4310 IF l_c3_prj_id_tbl(i) <> l_last_model_id THEN
4311
4312 -- process PS nodes with disposition of update if haven't already done so
4313
4314 update_ps_nodes(l_c3_prj_id_tbl(i));
4315
4316 -- call check_node for ROOT only, for now
4317 IF l_c3_prj_id_tbl(i)=l_c3_node_id_tbl(i) THEN
4318
4319 cz_refs.check_Node(l_c3_prj_id_tbl(i),
4320 l_c3_node_id_tbl(i),
4321 l_c3_max_tbl(i),
4322 l_c3_min_tbl(i),
4323 NULL,
4324 p_out_err,
4325 p_out_virtual_flag,
4326 '0',
4327 null,
4328 l_c3_nodetype_tbl(i),
4329 NULL);
4330 END IF;
4331
4332 IF (p_out_err > 0) THEN
4333
4334 l_c3_rec_status_tbl(i):='ERR';
4335 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_ROOT_CHECKNODE',
4336 'MODELID', l_c3_prj_id_tbl(i),
4337 'PSNODEID', l_c3_node_id_tbl(i),
4338 'REFID', NULL);
4339 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
4340
4341 ELSE
4342 -- Nodes not in this run id are assumed deleted - non-BOM models only
4343 IF l_c3_model_type(i) NOT IN ('A','P','N') THEN
4344 l_retcode := 0;
4345 delete_ps_nodes(l_c3_prj_id_tbl(i),l_retcode);
4346 IF (l_retcode = 1) THEN
4347 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_RB_DELNODE', 'MODELID', l_c3_prj_id_tbl(i));
4348 x_error:=CZ_UTILS.LOG_REPORT(l_msg ,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
4349 l_c3_rec_status_tbl(i):='ERR';
4350 ROLLBACK;
4351 END IF;
4352 END IF;
4353 IF (l_retcode = 0) THEN
4354 l_c3_rec_status_tbl(i):='OK';
4355 COMMIT;
4356 SELECT name INTO l_model_name
4357 FROM cz_imp_devl_project
4358 WHERE devl_project_id=l_c3_prj_id_tbl(i)
4359 AND deleted_flag='0'
4360 AND run_id=inRun_ID
4361 AND rec_status='OK'
4362 AND rownum <2;
4363 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_MODEL_IMPORTED',
4364 'MODELNAME', l_model_name,
4365 'MODELID', l_c3_prj_id_tbl(i));
4366 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
4367 END IF;
4368 END IF; -- if p_out_err
4369 END IF; -- last
4370 END IF; -- if retcode
4371 END IF; -- if rec status
4372
4373 l_last_model_id := l_c3_prj_id_tbl(i);
4374
4375 END LOOP; -- c3 models
4376
4377 <<UPDATE_IMP_TABLE>>
4378
4379 -- Now update the imp tables for failed models of C1
4380
4381 IF l_c1_prj_id_tbl.COUNT > 0 THEN
4382 FOR i IN l_c1_prj_id_tbl.FIRST..l_c1_prj_id_tbl.LAST LOOP
4383 IF l_c1_rec_status_tbl(i)='ERR' THEN
4384
4385 UPDATE CZ_IMP_PS_NODES
4386 SET REC_STATUS='ERR'
4387 WHERE DEVL_PROJECT_ID=l_c1_prj_id_tbl(i)
4388 AND RUN_ID=inRUN_ID;
4389
4390 UPDATE CZ_IMP_DEVL_PROJECT
4391 SET REC_STATUS='ERR'
4392 WHERE DEVL_PROJECT_ID=l_c1_prj_id_tbl(i)
4393 AND RUN_ID=inRUN_ID
4394 AND DISPOSITION=l_c1_dis_tbl(i);
4395
4396 -- delete if failed model was a new model
4397
4398 IF l_c1_dis_tbl(i) = 'I' THEN
4399 DELETE FROM cz_devl_projects
4400 WHERE devl_project_id = l_c1_prj_id_tbl(i);
4401 DELETE FROM cz_rp_entries
4402 WHERE object_id = l_c1_prj_id_tbl(i)
4403 AND object_type = 'PRJ';
4404 END IF;
4405 END IF;
4406 END LOOP;
4407 END IF;
4408
4409 -- Now update the imp tables for failed or skipped models of C2
4410
4411 IF l_c2_prj_id_tbl.COUNT > 0 THEN
4412 FOR i IN l_c2_prj_id_tbl.FIRST..l_c2_prj_id_tbl.LAST LOOP
4413 IF l_c2_rec_status_tbl(i) IN ('SKP','ERR') THEN
4414
4415 UPDATE CZ_IMP_PS_NODES
4416 SET REC_STATUS=DECODE(l_c2_rec_status_tbl(i),'SKP','PASS',l_c2_rec_status_tbl(i))
4417 WHERE DEVL_PROJECT_ID=l_c2_prj_id_tbl(i)
4418 AND RUN_ID=inRUN_ID;
4419
4420 UPDATE CZ_IMP_DEVL_PROJECT
4421 SET REC_STATUS=DECODE(l_c2_rec_status_tbl(i),'SKP','PASS',l_c2_rec_status_tbl(i))
4422 WHERE DEVL_PROJECT_ID=l_c2_prj_id_tbl(i)
4423 AND RUN_ID=inRUN_ID
4424 AND DISPOSITION=l_c2_dis_tbl(i);
4425
4426 -- delete if failed model was a new model
4427
4428 IF l_c2_dis_tbl(i) = 'I' THEN
4429 DELETE FROM cz_devl_projects
4430 WHERE devl_project_id = l_c2_prj_id_tbl(i);
4431 DELETE FROM cz_rp_entries
4432 WHERE object_id = l_c2_prj_id_tbl(i)
4433 AND object_type = 'PRJ';
4434 END IF;
4435 END IF;
4436 END LOOP;
4437 END IF;
4438
4439 -- Now update the imp tables for failed or skipped models of C3
4440
4441 IF l_c3_prj_id_tbl.COUNT > 0 THEN
4442 FOR i IN l_c3_prj_id_tbl.FIRST..l_c3_prj_id_tbl.LAST LOOP
4443 IF l_c3_rec_status_tbl(i) IN ('SKP','ERR') THEN
4444
4445 UPDATE CZ_IMP_PS_NODES
4446 SET REC_STATUS=DECODE(l_c3_rec_status_tbl(i),'SKP','PASS',l_c3_rec_status_tbl(i))
4447 WHERE DEVL_PROJECT_ID=l_c3_prj_id_tbl(i)
4448 AND RUN_ID=inRUN_ID;
4449
4450 UPDATE CZ_IMP_DEVL_PROJECT
4451 SET REC_STATUS=DECODE(l_C3_rec_status_tbl(i),'SKP','PASS',l_c3_rec_status_tbl(i))
4452 WHERE DEVL_PROJECT_ID=l_c3_prj_id_tbl(i)
4453 AND RUN_ID=inRUN_ID
4454 AND DISPOSITION=l_c3_dis_tbl(i);
4455
4456 -- delete if failed model was a new model
4457
4458 IF l_c3_dis_tbl(i) = 'I' THEN
4459 DELETE FROM cz_devl_projects
4460 WHERE devl_project_id = l_c3_prj_id_tbl(i);
4461 DELETE FROM cz_rp_entries
4462 WHERE object_id = l_c3_prj_id_tbl(i)
4463 AND object_type = 'PRJ';
4464 END IF;
4465 END IF;
4466 END LOOP;
4467 END IF;
4468
4469 -- Process C4 models - models with no refs and not referenced
4470
4471 OPEN C4;
4472 FETCH C4 BULK COLLECT INTO
4473 l_c4_plan_level_tbl,l_c4_prj_id_tbl,l_c4_node_id_tbl,l_c4_dis_tbl,
4474 l_c4_nodetype_tbl,l_c4_min_tbl,l_c4_max_tbl,l_c4_rec_status_tbl,l_c4_model_type;
4475 CLOSE C4;
4476
4477 IF (l_c4_prj_id_tbl.COUNT = 0) THEN
4478 GOTO PROCESS_REFS;
4479 END IF;
4480
4481 FOR i IN l_c4_prj_id_tbl.FIRST..l_c4_prj_id_tbl.LAST LOOP
4482
4483 l_retcode := 0;
4484 p_out_err := 0;
4485
4486 -- process PS nodes with disposition of insert
4487
4488 insert_ps_nodes(l_c4_prj_id_tbl(i), l_retcode);
4489
4490 IF l_retcode = 1 THEN
4491 l_c4_rec_status_tbl(i):='ERR';
4492 ELSE
4493
4494 -- process PS nodes with disposition of update
4495 update_ps_nodes(l_c4_prj_id_tbl(i));
4496
4497 -- call check node for ROOT node for now
4498 IF l_c4_prj_id_tbl(i)=l_c4_node_id_tbl(i) THEN
4499
4500 cz_refs.check_Node(l_c4_prj_id_tbl(i),
4501 l_c4_node_id_tbl(i),
4502 l_c4_max_tbl(i),
4503 l_c4_min_tbl(i),
4504 NULL,
4505 p_out_err,
4506 p_out_virtual_flag,
4507 '0',
4508 null,
4509 l_c4_nodetype_tbl(i),
4510 NULL);
4511 END IF;
4512
4513 IF (p_out_err > 0) THEN
4514
4515 l_c4_rec_status_tbl(i):='ERR';
4516 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_ROOT_CHECKNODE',
4517 'MODELID', l_c4_prj_id_tbl(i),
4518 'PSNODEID', l_c4_node_id_tbl(i),
4519 'REFID', NULL);
4520 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
4521 ELSE
4522 -- Nodes not in this run id are assumed deleted - non-BOM models only
4523 IF l_c4_model_type(i) NOT IN ('A','P','N') THEN
4524 l_retcode := 0;
4525 delete_ps_nodes(l_c4_prj_id_tbl(i),l_retcode);
4526 IF (l_retcode = 1) THEN
4527 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_RB_DELNODE', 'MODELID', l_c4_prj_id_tbl(i));
4528 x_error:=CZ_UTILS.LOG_REPORT(l_msg ,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
4529 l_c4_rec_status_tbl(i):='ERR';
4530 ROLLBACK;
4531 END IF;
4532 END IF;
4533 IF (l_retcode = 0) THEN
4534 l_c4_rec_status_tbl(i):='OK';
4535 COMMIT;
4536 SELECT name INTO l_model_name
4537 FROM cz_imp_devl_project
4538 WHERE devl_project_id=l_c4_prj_id_tbl(i)
4539 AND deleted_flag='0'
4540 AND run_id=inRun_ID
4541 AND rec_status='OK'
4542 AND rownum <2;
4543 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_MODEL_IMPORTED',
4544 'MODELNAME', l_model_name,
4545 'MODELID', l_c4_prj_id_tbl(i));
4546 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
4547 END IF;
4548 END IF; -- p_out_err
4549 END IF; -- if retcode ..
4550 END LOOP; -- c4 models
4551
4552 -- Now update the imp tables for failed models of C4
4553
4554 IF l_c4_prj_id_tbl.COUNT > 0 THEN
4555 FOR i IN l_c4_prj_id_tbl.FIRST..l_c4_prj_id_tbl.LAST LOOP
4556 IF l_c4_rec_status_tbl(i)='ERR' THEN
4557
4558 UPDATE CZ_IMP_PS_NODES
4559 SET REC_STATUS='ERR'
4560 WHERE DEVL_PROJECT_ID=l_c4_prj_id_tbl(i)
4561 AND RUN_ID=inRUN_ID;
4562
4563 UPDATE CZ_IMP_DEVL_PROJECT
4564 SET REC_STATUS='PASS'
4565 WHERE DEVL_PROJECT_ID=l_c4_prj_id_tbl(i)
4566 AND RUN_ID=inRUN_ID
4567 AND DISPOSITION=l_c4_dis_tbl(i);
4568
4569 -- delete if failed model was a new model
4570
4571 IF l_c4_dis_tbl(i) = 'I' THEN
4572 DELETE FROM cz_devl_projects
4573 WHERE devl_project_id = l_c4_prj_id_tbl(i);
4574 DELETE FROM cz_rp_entries
4575 WHERE object_id = l_c4_prj_id_tbl(i)
4576 AND object_type = 'PRJ';
4577 END IF;
4578 END IF;
4579 END LOOP;
4580 END IF;
4581
4582 <<PROCESS_REFS>>
4583 ---------------------------------------------------------------------------------------------------------------
4584 -- DONE transferring PS nodes for all models in this run id -- call check nodes for refs and components only
4585 ---------------------------------------------------------------------------------------------------------------
4586 OPEN l_model_refs_csr;
4587 FETCH l_model_refs_csr BULK COLLECT INTO
4588 l_PlanLevel,l_PsNodeId,l_DevlProjectId,l_ReferenceId,
4589 l_minimum,l_maximum,l_PsNodeType,l_ParentId,l_dis;
4590 CLOSE l_model_refs_csr;
4591
4592 IF l_PsNodeId.COUNT > 0 THEN
4593
4594 FOR i IN l_PsNodeId.FIRST..l_PsNodeId.LAST LOOP
4595
4596 p_out_err := 0;
4597
4598 cz_refs.check_Node(l_PsNodeId(i),
4599 l_DevlProjectId(i),
4600 l_Maximum(i),
4601 l_Minimum(i),
4602 l_ReferenceId(i),
4603 p_out_err,
4604 p_out_virtual_flag,
4605 '0',
4606 null,
4607 l_PsNodeType(i),
4608 NULL);
4609
4610 IF (p_out_err > 0) THEN
4611 FAILED:=FAILED +1;
4612 UPDATE CZ_IMP_PS_NODES
4613 SET REC_STATUS='ERR'
4614 WHERE PS_NODE_ID=l_PsNodeId(i)
4615 AND RUN_ID=inRUN_ID
4616 AND DISPOSITION=l_dis(i);
4617
4618 SELECT name INTO l_model_name
4619 FROM cz_imp_devl_project
4620 WHERE devl_project_id=l_DevlProjectId(i)
4621 AND deleted_flag='0'
4622 AND run_id=inRun_ID
4623 AND rec_status='OK'
4624 AND rownum <2;
4625 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_CHECKNODE',
4626 'MODELID', l_DevlProjectId(i),
4627 'PSNODEID', l_PsNodeId(i),
4628 'REFID', l_ReferenceId(i));
4629 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
4630 END IF;
4631 END LOOP;
4632 END IF;
4633
4634 --Fix for the bug #3040079. We need to call a new cz_refs procedure for every model we inserted.
4635 FOR c_model IN (SELECT devl_project_id FROM cz_imp_devl_project
4636 WHERE run_id = inRUN_ID
4637 AND rec_status = 'OK')LOOP
4638 cz_refs.populate_component_id(c_model.devl_project_id);
4639 END LOOP;
4640
4641 COMMIT;
4642 INSERTS:=nInsertCount;
4643 UPDATES:=nUpdateCount;
4644 EXCEPTION
4645 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
4646 IF c_xfr_psnode%ISOPEN THEN close c_xfr_psnode; END IF;
4647 RAISE;
4648 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
4649 RAISE;
4650 WHEN OTHERS THEN
4651 IF c_xfr_psnode%ISOPEN THEN close c_xfr_psnode; END IF;
4652 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'IMP_IM_XFR.XFR_PS_NODE',11276,inRun_Id);
4653 END XFR_PS_NODE;
4654
4655 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
4656 PROCEDURE RPT_PS_NODE ( inRUN_ID IN PLS_INTEGER ) AS
4657 x_error BOOLEAN:=FALSE;
4658
4659 v_table_name VARCHAR2(30) := 'CZ_PS_NODES';
4660 v_ok VARCHAR2(4) := 'OK';
4661 v_completed VARCHAR2(1) := '1';
4662
4663 BEGIN
4664 BEGIN
4665 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
4666
4667 EXCEPTION
4668 WHEN NO_DATA_FOUND THEN NULL;
4669 END;
4670
4671 DECLARE
4672 CURSOR c_xfr_run_result IS
4673 SELECT DISPOSITION,REC_STATUS,COUNT(*)
4674 FROM CZ_IMP_PS_NODES
4675 WHERE RUN_ID = inRUN_ID
4676 GROUP BY DISPOSITION,REC_STATUS;
4677
4678 ins_disposition CZ_XFR_RUN_RESULTS.disposition%TYPE;
4679 ins_rec_status CZ_XFR_RUN_RESULTS.rec_status%TYPE ;
4680 ins_rec_count CZ_XFR_RUN_RESULTS.records%TYPE ;
4681
4682 BEGIN
4683
4684 OPEN c_xfr_run_result;
4685 LOOP
4686 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
4687 EXIT WHEN c_xfr_run_result%NOTFOUND;
4688
4689 INSERT INTO CZ_XFR_RUN_RESULTS (RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
4690 VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
4691
4692 END LOOP;
4693 CLOSE c_xfr_run_result;
4694 COMMIT;
4695
4696 EXCEPTION
4697 WHEN OTHERS THEN
4698 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.RPT_PS_NODE',11276,inRun_Id);
4699 END;
4700
4701 DECLARE
4702 nErrors PLS_INTEGER;
4703 CURSOR c_get_nErrors IS
4704 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
4705 WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
4706 AND IMP_TABLE=v_table_name;
4707 BEGIN
4708 OPEN c_get_nErrors;
4709 FETCH c_get_nErrors INTO nErrors;
4710 CLOSE c_get_nErrors;
4711 UPDATE CZ_XFR_RUN_INFOS
4712 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
4713 COMPLETED=v_completed
4714 WHERE RUN_ID=inRUN_ID;
4715 COMMIT;
4716 EXCEPTION
4717 WHEN OTHERS THEN
4718 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.RPT_PS_NODE',11276,inRun_Id);
4719 END;
4720 EXCEPTION
4721 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
4722 RAISE;
4723 WHEN CZ_ADMIN.IMP_UNEXP_SQL_ERROR THEN
4724 RAISE;
4725 WHEN OTHERS THEN
4726 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_PS_NODE.RPT_PS_NODE',11276,inRun_Id);
4727 END;
4728 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
4729
4730 /*--PS_NODE IMPORT SECTION END----------------------------------------------*/
4731 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
4732 END CZ_IMP_PS_NODE;
[Home] [Help]