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