1 PACKAGE BODY CZ_BOM_SYNCH AS
2 /* $Header: czbomsyb.pls 120.12.12020000.3 2013/01/31 20:32:35 smanna ship $ */
3 ---------------------------------------------------------------------------------------
4 TYPE typeNumberTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5 TYPE typeIntegerTable_vc2 IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(15); --Bug9941274
6 TYPE typeStringTable IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
7 TYPE typeRefCursor IS REF CURSOR;
8
9 component_item_id_map typeNumberTable;
10 component_seq_id_map typeIntegerTable_vc2; --SKM: Bug16190145 (Int2Long)
11 catalog_group_id_map typeNumberTable;
12 organization_id_map typeNumberTable;
13 APC_APPL_ID CONSTANT NUMBER:=431; --Application ID for Advanced Product Catalog Application
14 g_target_instance cz_servers.server_local_id%TYPE;
15 GenHeader VARCHAR2(100) := '$Header: czbomsyb.pls 120.12.12020000.3 2013/01/31 20:32:35 smanna ship $';
16 ---------------------------------------------------------------------------------------
17 --Synchronizes all the models in one instance to corresponding bills in the specified
18 --target instance. Stops and rolls back at the very first error or warning.
19
20 PROCEDURE synchronize_all_models_cp(errbuf OUT NOCOPY VARCHAR2,
21 retcode OUT NOCOPY NUMBER,
22 p_target_name IN VARCHAR2) IS
23
24 RunId PLS_INTEGER;
25 ErrorFlag VARCHAR2(1);
26
27 BEGIN
28
29 retcode := CONCURRENT_SUCCESS;
30 errbuf := '';
31
32 build_structure_map(NULL, p_target_name, EXECUTION_MODE_SYNC, LOG_LEVEL_MESSAGES, ErrorFlag, RunId);
33
34 IF(ErrorFlag = ERROR_FLAG_ERROR)THEN
35
36 retcode := CONCURRENT_ERROR;
37 --'Syncronization failed, please see log for details'
38 errbuf := cz_utils.get_text('CZ_SYNC_CONCURRENT_ERROR');
39 END IF;
40 END;
41 ---------------------------------------------------------------------------------------
42 --Verifies the specified model against the corresponding bill in the specified target
43 --instance without actual synchronization. Reports all the problems .
44
45 PROCEDURE report_model_cp(errbuf OUT NOCOPY VARCHAR2,
46 retcode OUT NOCOPY NUMBER,
47 p_target_name IN VARCHAR2,
48 p_model_id IN NUMBER) IS
49
50 RunId PLS_INTEGER;
51 ErrorFlag VARCHAR2(1);
52
53 BEGIN
54
55 retcode := CONCURRENT_SUCCESS;
56 errbuf := '';
57
58 build_structure_map(p_model_id, p_target_name, EXECUTION_MODE_REPORT, LOG_LEVEL_MESSAGES, ErrorFlag, RunId);
59
60 IF(ErrorFlag = ERROR_FLAG_ERROR)THEN
61
62 retcode := CONCURRENT_ERROR;
63 --'Syncronization failed, please see log for details'
64 errbuf := cz_utils.get_text('CZ_SYNC_CONCURRENT_ERROR');
65 END IF;
66 END;
67 ---------------------------------------------------------------------------------------
68 --Verifies all the models in one instance against to corresponding bills in the specified
69 --target instance without actual synchronization. Reports all the problems.
70
71 PROCEDURE report_all_models_cp(errbuf OUT NOCOPY VARCHAR2,
72 retcode OUT NOCOPY NUMBER,
73 p_target_name IN VARCHAR2) IS
74
75 RunId PLS_INTEGER;
76 ErrorFlag VARCHAR2(1);
77
78 BEGIN
79
80 retcode := CONCURRENT_SUCCESS;
81 errbuf := '';
82
83 build_structure_map(NULL, p_target_name, EXECUTION_MODE_REPORT, LOG_LEVEL_MESSAGES, ErrorFlag, RunId);
84
85 IF(ErrorFlag = ERROR_FLAG_ERROR)THEN
86
87 retcode := CONCURRENT_ERROR;
88 --'Syncronization failed, please see log for details'
89 errbuf := cz_utils.get_text('CZ_SYNC_CONCURRENT_ERROR');
90 END IF;
91 END;
92 ---------------------------------------------------------------------------------------
93 --Verifies the specified model against the corresponding bill in the specified target
94 --instance without actual synchronization. Stops at the very first error.
95
96 PROCEDURE verify_model(p_model_id IN NUMBER,
97 p_target_name IN VARCHAR2,
98 p_error_flag IN OUT NOCOPY VARCHAR2,
99 p_run_id IN OUT NOCOPY NUMBER) IS
100 BEGIN
101
102 build_structure_map(p_model_id, p_target_name, EXECUTION_MODE_VERIFY, LOG_LEVEL_MESSAGES, p_error_flag, p_run_id);
103
104 END;
105 ---------------------------------------------------------------------------------------
106 --build_structure_map
107
108 --The root procedure. Only concurrent procedures and synchronizators are external to
109 --this procedure. The concurrent procedures call build_structure_map with different
110 --combinations of parameters.
111
112 PROCEDURE build_structure_map(p_model_id IN NUMBER,
113 p_target_name IN VARCHAR2,
114 p_execution_mode IN NUMBER,
115 p_log_level IN NUMBER,
116 p_error_flag IN OUT NOCOPY VARCHAR2,
117 p_run_id IN OUT NOCOPY NUMBER)
118 IS
119
120 TYPE typePsNodeId IS TABLE OF cz_ps_nodes.ps_node_id%TYPE INDEX BY BINARY_INTEGER;
121 TYPE typePsNodeType IS TABLE OF cz_ps_nodes.ps_node_type%TYPE INDEX BY BINARY_INTEGER;
122 TYPE typeInitialValue IS TABLE OF cz_ps_nodes.initial_value%TYPE INDEX BY BINARY_INTEGER;
123 TYPE typeInitNumVal IS TABLE OF cz_ps_nodes.initial_num_value%TYPE INDEX BY BINARY_INTEGER; -- sselahi
124 TYPE typeParentId IS TABLE OF cz_ps_nodes.parent_id%TYPE INDEX BY BINARY_INTEGER;
125 TYPE typeItemId IS TABLE OF cz_ps_nodes.item_id%TYPE INDEX BY BINARY_INTEGER;
126 TYPE typeMinimum IS TABLE OF cz_ps_nodes.minimum%TYPE INDEX BY BINARY_INTEGER;
127 TYPE typeMaximum IS TABLE OF cz_ps_nodes.maximum%TYPE INDEX BY BINARY_INTEGER;
128 TYPE typeMinimumSelected IS TABLE OF cz_ps_nodes.minimum_selected%TYPE INDEX BY BINARY_INTEGER;
129 TYPE typeMaximumSelected IS TABLE OF cz_ps_nodes.maximum_selected%TYPE INDEX BY BINARY_INTEGER;
130 TYPE typeReferenceId IS TABLE OF cz_ps_nodes.reference_id%TYPE INDEX BY BINARY_INTEGER;
131 TYPE typeEffectiveFrom IS TABLE OF cz_ps_nodes.effective_from%TYPE INDEX BY BINARY_INTEGER;
132 TYPE typeEffectiveUntil IS TABLE OF cz_ps_nodes.effective_until%TYPE INDEX BY BINARY_INTEGER;
133 TYPE typePsNodeName IS TABLE OF cz_ps_nodes.name%TYPE INDEX BY BINARY_INTEGER;
134 TYPE typeOrigSysRef IS TABLE OF cz_ps_nodes.orig_sys_ref%TYPE INDEX BY BINARY_INTEGER;
135 TYPE typeOrigSysRef_vc2 IS TABLE OF cz_ps_nodes.orig_sys_ref%TYPE INDEX BY VARCHAR2(15); --Bug9941274
136 TYPE typeSequencePath IS TABLE OF cz_ps_nodes.component_sequence_path%TYPE INDEX BY BINARY_INTEGER;
137 TYPE typeSequencePath_vc2 IS TABLE OF cz_ps_nodes.component_sequence_path%TYPE INDEX BY VARCHAR2(15); --Bug9941274
138 TYPE typeSequenceId IS TABLE OF cz_ps_nodes.component_sequence_id%TYPE INDEX BY BINARY_INTEGER;
139 TYPE typeSequenceId_vc2 IS TABLE OF cz_ps_nodes.component_sequence_id%TYPE INDEX BY VARCHAR2(15); --Bug9941274
140 TYPE typeBomRequiredFlag IS TABLE OF cz_ps_nodes.bom_required_flag%TYPE INDEX BY BINARY_INTEGER;
141
142 TYPE typeRefPartNbr IS TABLE OF cz_item_masters.ref_part_nbr%TYPE INDEX BY BINARY_INTEGER;
143 TYPE typeItemOrigSysRef IS TABLE OF cz_item_masters.orig_sys_ref%TYPE INDEX BY BINARY_INTEGER;
144
145 TYPE typeItemTypeId IS TABLE OF cz_item_types.item_type_id%TYPE INDEX BY BINARY_INTEGER;
146 TYPE typeItemTypeName IS TABLE OF cz_item_types.name%TYPE INDEX BY BINARY_INTEGER;
147 TYPE typeTypeOrigSysRef IS TABLE OF cz_item_types.orig_sys_ref%TYPE INDEX BY BINARY_INTEGER;
148
149 TYPE typeIntlTextId IS TABLE OF cz_localized_texts.intl_text_id%TYPE INDEX BY BINARY_INTEGER;
150 TYPE typeTextOrigSysRef IS TABLE OF cz_localized_texts.orig_sys_ref%TYPE INDEX BY BINARY_INTEGER;
151 TYPE typeTextOrigSysRef_vc2 IS TABLE OF cz_localized_texts.orig_sys_ref%TYPE INDEX BY VARCHAR2(15); --Bug9941274
152
153 TYPE typeDevlProjectId IS TABLE OF cz_devl_projects.devl_project_id%TYPE INDEX BY BINARY_INTEGER;
154 TYPE typeDevlOrigSysRef_vc2 IS TABLE OF cz_devl_projects.orig_sys_ref%TYPE INDEX BY VARCHAR2(15); --Bug9941274
155
156 TYPE typeModelPsNodeId IS TABLE OF cz_xfr_project_bills.model_ps_node_id%TYPE INDEX BY BINARY_INTEGER;
157 TYPE typeOrganizationId_vc2 IS TABLE OF cz_xfr_project_bills.organization_id%TYPE INDEX BY VARCHAR2(15); --Bug9941274
158 TYPE typeTopItemId_vc2 IS TABLE OF cz_xfr_project_bills.top_item_id%TYPE INDEX BY VARCHAR2(15); --Bug9941274
159 TYPE typeComponentId_vc2 IS TABLE OF cz_xfr_project_bills.component_item_id%TYPE INDEX BY VARCHAR2(15); --Bug9941274
160 TYPE typeSourceServer_vc2 IS TABLE OF cz_xfr_project_bills.source_server%TYPE INDEX BY VARCHAR2(15); --Bug9941274
161
162 TYPE typePublicationId IS TABLE OF cz_model_publications.publication_id%TYPE INDEX BY BINARY_INTEGER;
163 TYPE typePubOrganizationId IS TABLE OF cz_xfr_project_bills.organization_id%TYPE INDEX BY BINARY_INTEGER;
164 TYPE typePubTopItemId IS TABLE OF cz_model_publications.top_item_id%TYPE INDEX BY BINARY_INTEGER;
165 TYPE typePubProductKey IS TABLE OF cz_model_publications.product_key%TYPE INDEX BY BINARY_INTEGER;
166
167 TYPE typeInventoryItemId IS TABLE OF mtl_system_items.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
168 TYPE typePropertyId IS TABLE OF CZ_ITEM_TYPE_PROPERTIES.PROPERTY_ID%TYPE INDEX BY BINARY_INTEGER;
169
170 thisVersionString user_source.text%TYPE;
171
172 nDebug PLS_INTEGER := 1000;
173 messageId PLS_INTEGER := 1000;
174 sourceLinkVerified PLS_INTEGER := ORACLE_NO;
175 sourceLinkName user_db_links.db_link%TYPE;
176 targetLinkName user_db_links.db_link%TYPE;
177 sourceServer cz_servers.server_local_id%TYPE;
178
179 alreadyVerified typeIntegerTable_vc2; --Bug9941274
180
181 baseLanguageCode fnd_languages.language_code%TYPE;
182 numberOfLanguages PLS_INTEGER;
183 VerifyItemProperties PLS_INTEGER;
184
185 tabItemTypeId typeItemTypeId;
186 tabItemTypeName typeItemTypeName;
187 tabItemTypeOrigSysRef typeTypeOrigSysRef;
188 hashItemTypeId typeNumberTable;
189 hashItemTypeName typeItemTypeName;
190
191 DaysTillEpochEnd NUMBER;
192 EpochEndLine DATE;
193
194 --Synchronization update and rollback implementation parameters section
195
196 tabCandidateNode typePsNodeId;
197 tabCandidateItem typeItemId;
198 tabCandidateDevl typeDevlProjectId;
199 tabCandidateText typeIntlTextId;
200 tabCandidateProj typeModelPsNodeId;
201 tabCandidateType typeItemTypeId;
202 tabCandidatePubl typePublicationId;
203
204 hashRbNodeOrigSysRef typeOrigSysRef_vc2; --Bug9941274
205 hashRbNodeSequencePath typeSequencePath_vc2; --Bug9941274
206 hashRbNodeSequenceId typeSequenceId_vc2; --Bug9941274
207
208 hashRbItemOrigSysRef typeItemOrigSysRef;
209 hashRbDevlOrigSysRef typeDevlOrigSysRef_vc2; --Bug9941274
210 hashRbTextOrigSysRef typeTextOrigSysRef_vc2; --Bug9941274
211 hashRbTypeOrigSysRef typeTypeOrigSysRef;
212
213 hashRbOrganizationId typeOrganizationId_vc2; --Bug9941274
214 hashRbTopItemId typeTopItemId_vc2; --Bug9941274
215 hashRbComponentItemId typeComponentId_vc2; --Bug9941274
216 hashRbSourceServer typeSourceServer_vc2; --Bug9941274
217
218 hashRbPubOrganizationId typePubOrganizationId;
219 hashRbPubTopItemId typePubTopItemId;
220 hashRbPubProductKey typePubProductKey;
221
222 tabRbItmPropValPropId typePropertyId;
223 tabRbItmTypPropId typePropertyId;
224
225 tabRbItmPropValOrigSysRef typeOrigSysRef;
226 tabRbItmTypPropOrigSysRef typeOrigSysRef;
227
228 tabRbItmPropValItemId typeItemId;
229 tabRbItmTypPropItTypeId typeItemTypeId;
230
231 nodeRollback PLS_INTEGER := 0;
232 itemRollback PLS_INTEGER := 0;
233 devlRollback PLS_INTEGER := 0;
234 textRollback PLS_INTEGER := 0;
235 typeRollback PLS_INTEGER := 0;
236 projRollback PLS_INTEGER := 0;
237 publRollback PLS_INTEGER := 0;
238 itemTypePropRollback PLS_INTEGER := 0;
239 itemPropValRollback PLS_INTEGER := 0;
240 ---------------------------------------------------------------------------------------
241 --build_structure_map->report
242
243 --The reporting procedure. Runs in an autonomous transaction, because we don't want to
244 --commit the current transaction when we are just verifying a model, but we don't want
245 --our error messages to be rolled back either.
246
247 PROCEDURE report(inMessage IN VARCHAR2, inUrgency IN PLS_INTEGER) IS
248 l_log_level NUMBER;
249 BEGIN
250 IF (inUrgency = URGENCY_ERROR OR inUrgency = URGENCY_WARNING) THEN
251 l_log_level := fnd_log.LEVEL_ERROR;
252 ELSIF (inUrgency = URGENCY_MESSAGE) THEN
253 l_log_level := fnd_log.LEVEL_PROCEDURE;
254 ELSE
255 l_log_level := fnd_log.LEVEL_STATEMENT;
256 END IF;
257
258 -- passing null routime name and nDebug
259 cz_utils.log_report('cz_bom_synch', 'BOM Synchronization', nDebug, inMessage, l_log_level);
260
261 --Bug #4318949.
262 IF((inUrgency <= p_log_level + 1) AND (FND_GLOBAL.CONC_REQUEST_ID > 0))THEN
263 FND_FILE.PUT_LINE(FND_FILE.LOG, inMessage);
264 END IF;
265
266 --When executing in the synchronization mode we want to stop at the first error or warning.
267
268 IF(p_execution_mode > EXECUTION_MODE_REPORT AND inUrgency < URGENCY_MESSAGE)THEN
269 RAISE CZ_SYNC_GENERAL_EXCEPTION;
270 END IF;
271 END;
272 ---------------------------------------------------------------------------------------
273 --build_structure_map->debug
274
275 --Calls the report procedure with a particular urgency level for debug information.
276
277 PROCEDURE debug(inMessage IN VARCHAR2) IS
278 BEGIN
279 report(inMessage, URGENCY_DEBUG);
280 END;
281 ---------------------------------------------------------------------------------------
282 --build_structure_map->report_on_exit
283
284 --Need this version to log a message in the most outer exception handler. It is always
285 --URGENCY_ERROR and never re-raises any exceptions.
286
287 PROCEDURE report_on_exit(inMessage IN VARCHAR2) IS
288 BEGIN
289
290 -- INSERT INTO cz_db_logs (message, statuscode, caller, urgency, run_id, logtime, message_id)
291 -- VALUES (inMessage, nDebug, 'BOM Synchronization', URGENCY_ERROR, p_run_id, SYSDATE, messageId);
292 -- messageId := messageId + 1;
293
294 -- COMMIT;
295 report(inMessage, URGENCY_ERROR);
296 END;
297 ---------------------------------------------------------------------------------------
298 --build_structure_map->verify_database_link
299
300 --Verifies a database link by quering user_db_links and remote org_organization_definitions.
301
302 PROCEDURE verify_database_link(inLinkName IN VARCHAR2) IS
303
304 nGeneric PLS_INTEGER;
305
306 BEGIN
307
308 --This block is commented OUT NOCOPY as a fix for the bug #2195164. When the global name contains
309 --a not empty domain part, it will be automatically appended in user_db_links table to the
310 --link name. As the same operation does not automatically occur in cz_servers table, names
311 --of the links may not match.
312
313 --BEGIN
314
315 -- SELECT NULL INTO nGeneric
316 -- FROM user_db_links
317 -- WHERE UPPER(db_link) = UPPER(inLinkName);
318
319 --EXCEPTION
320 -- WHEN NO_DATA_FOUND THEN
321 -- RAISE CZ_SYNC_NO_DATABASE_LINK;
322 --END;
323
324 BEGIN
325
326 --org_organization_definitions is always a reasonbly short table, so this check should
327 --work fast enough. Can be replaced with any other check to verify that the link is in
328 --a working condition.
329
330 EXECUTE IMMEDIATE 'SELECT count(*) FROM org_organization_definitions@' || inLinkName
331 INTO nGeneric;
332
333 EXCEPTION
334 WHEN OTHERS THEN
335 --'Problem accessing database link ''%LINKNAME'': %ERRORTEXT'
336 report(CZ_UTILS.GET_TEXT('CZ_SYNC_BAD_DATABASE_LINK', 'LINKNAME', inLinkName, 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
337 RAISE CZ_SYNC_GENERAL_EXCEPTION;
338 END;
339 END; --verify_database_link
340 ---------------------------------------------------------------------------------------
341 --build_structure_map->verify_source_server
342
343 --Verifies the existence and working condition of the link assigned to server sourceServer.
344 --Does this only once per session.
345
346 PROCEDURE verify_source_server IS
347
348 instanceName cz_servers.local_name%TYPE;
349 linkName cz_servers.fndnam_link_name%TYPE;
350
351 BEGIN
352 IF(sourceLinkVerified = ORACLE_NO)THEN
353
354 BEGIN
355
356 SELECT fndnam_link_name, local_name INTO linkName, instanceName
357 FROM cz_servers
358 WHERE server_local_id = sourceServer;
359
360 EXCEPTION
361 WHEN NO_DATA_FOUND THEN
362 --'Unable to resolve import source server with server id: %SERVERID'
363 report(CZ_UTILS.GET_TEXT('CZ_SYNC_INCORRECT_SOURCE', 'SERVERID', sourceServer), URGENCY_ERROR);
364 RAISE CZ_SYNC_GENERAL_EXCEPTION;
365 END;
366
367 IF(linkName IS NOT NULL)THEN
368
369 verify_database_link(linkName);
370 linkName := '@' || linkName;
371
372 END IF;
373
374 --The link is verified, assign the global variable to be used in the code.
375
376 sourceLinkName := linkName;
377 sourceLinkVerified := ORACLE_YES;
378 END IF;
379
380 EXCEPTION
381 WHEN CZ_SYNC_NO_DATABASE_LINK THEN
382 --'Database link does not exist for the import source instance ''%TARGETNAME'''
383 report(CZ_UTILS.GET_TEXT('CZ_SYNC_NO_SOURCE_LINK', 'TARGETNAME', instanceName), URGENCY_ERROR);
384 RAISE CZ_SYNC_GENERAL_EXCEPTION;
385 END;
386 ---------------------------------------------------------------------------------------
387 --build_structure_map->execute_model
388
389 --The basic procedure called by build_structure_map after the preparatory work is completed.
390 --Finds the BOM model down the stream if p_model_id is not a BOM model (recursively),
391 --verifies the source server if necessary, populates the organizations hash table and
392 --rollback tables for cz_devl_projects and cz_xfr_project_bills, and calls execute_structure_map.
393
394 PROCEDURE execute_model(p_model_id IN NUMBER) IS
395
396 sourceOrgId PLS_INTEGER;
397 sourceTopId PLS_INTEGER;
398 targetOrgId PLS_INTEGER;
399
400 modelName cz_ps_nodes.name%TYPE;
401 modelOrigSysRef cz_devl_projects.orig_sys_ref%TYPE;
402 modelEngineType cz_devl_projects.config_engine_type%TYPE;
403
404 modelNameStack typeStringTable;
405 ---------------------------------------------------------------------------------------
406 --build_structure_map->execute_model->execute_structure_map
407
408 --Reads the product structure for the specified model, builds parent-child control tables,
409 --determines the actual BOM root and calls verify_children_list for it. Recursively follows
410 --the references.
411
412 PROCEDURE execute_structure_map(p_model_id IN NUMBER) IS
413
414 tabPsNodeId typePsNodeId;
415 tabPsNodeType typePsNodeType;
416 tabInitialValue typeInitialValue;
417 tabInitNumVal typeInitNumVal;
418 tabParentId typeParentId;
419 tabItemId typeItemId;
420 tabMinimum typeMinimum;
421 tabMaximum typeMaximum;
422 tabMinimumSelected typeMinimumSelected;
423 tabMaximumSelected typeMaximumSelected;
424 tabReferenceId typeReferenceId;
425 tabEffectiveFrom typeEffectiveFrom;
426 tabEffectiveUntil typeEffectiveUntil;
427 tabPsNodeName typePsNodeName;
428 tabOrigSysRef typeOrigSysRef;
429 tabSequencePath typeSequencePath;
430 tabSequenceId typeSequenceId;
431 tabIntlTextId typeIntlTextId;
432 tabTextOrigSysRef typeTextOrigSysRef;
433 tabItemMasterTypeId typeItemTypeId;
434 tabBomRequiredFlag typeBomRequiredFlag;
435
436 tabRefPartNbr typeRefPartNbr;
437 tabItemOrigSysRef typeItemOrigSysRef;
438
439 jhashNodeFirstChild typeIntegerTable_vc2; --Bug9941274
440 jhashNodeLastChild typeIntegerTable_vc2; --Bug9941274
441 jRootNode INTEGER;
442 ---------------------------------------------------------------------------------------
443 --build_structure_map->execute_model->execute_structure_map->generate_name
444
445 --Generates full name for a referenced model.
446
447 FUNCTION generate_name RETURN VARCHAR2 IS
448 name VARCHAR2(2000);
449 BEGIN
450
451 FOR i IN 1..modelNameStack.COUNT LOOP
452 IF(i > 1)THEN name := name || NAME_PATH_SEPARATOR; END IF;
453 name := name || modelNameStack(i);
454 END LOOP;
455
456 RETURN name;
457 END; --generate_name
458 ---------------------------------------------------------------------------------------
459 --build_structure_map->execute_model->execute_structure_map->extract_item_id
460
461 --Extracts item_id from cz_item_masters.orig_sys_ref
462
463 FUNCTION extract_item_id(j IN PLS_INTEGER) RETURN PLS_INTEGER IS
464 BEGIN
465
466 --The return value can only be a not null valid number.
467
468 RETURN TO_NUMBER(NVL(SUBSTR(tabItemOrigSysRef(j), 1, INSTR(tabItemOrigSysRef(j), ORIGINAL_SEPARATOR) - 1), 'NULL'));
469 EXCEPTION
470 WHEN OTHERS THEN
471 --'Unable to extract item id for item ''%ITEMNAME'' in configuration model ''%MODELNAME'''
472 report(CZ_UTILS.GET_TEXT('CZ_SYNC_INVALID_ITEM_ID', 'ITEMNAME', tabRefPartNbr(j), 'MODELNAME', generate_name), URGENCY_WARNING);
473 RETURN NULL;
474 END; --extract_item_id
475 ---------------------------------------------------------------------------------------
476 --build_structure_map->execute_model->execute_structure_map->extract_project_reference
477
478 --Extracts cz_devl_projects.orig_sys_ref part of cz_ps_nodes.orig_sys_ref. Uses the fact
479 --the first is always a part of the second.
480
481 FUNCTION extract_project_reference(j IN PLS_INTEGER) RETURN VARCHAR2 IS
482 BEGIN
483
484 RETURN SUBSTR(tabOrigSysRef(j), INSTR(tabOrigSysRef(j), ORIGINAL_SEPARATOR, -1, 3) + 1);
485 END; --extract_project_reference
486 ---------------------------------------------------------------------------------------
490
487 --build_structure_map->execute_model->execute_structure_map->verify_children_list
488
489 --Makes the actual comparisons intersecting effectivity ranges. Recurse on every option class.
491 PROCEDURE verify_children_list(j IN PLS_INTEGER, inEffectivityDate IN DATE, inDisableDate IN DATE) IS
492
493 localPsNodeId cz_ps_nodes.ps_node_id%TYPE := tabPsNodeId(j);
494 localParentName cz_ps_nodes.name%TYPE := tabPsNodeName(j);
495 localParentType cz_ps_nodes.ps_node_type%TYPE := tabPsNodeType(j);
496 bomRequiredFlag cz_ps_nodes.bom_required_flag%TYPE;
497 tabInventoryItemId typeInventoryItemId;
498
499 l_item_type_msg VARCHAR2(4000);
500 l_parent_type_msg VARCHAR2(4000);
501
502 getBillChildren typeRefCursor;
503 childrenFirst PLS_INTEGER := 1;
504 childrenLast PLS_INTEGER := 0;
505 slidePointer PLS_INTEGER;
506 startPointer PLS_INTEGER;
507 localString VARCHAR2(2000);
508 bomQuantity NUMBER;
509 nodeQuantity NUMBER;
510
511 lastConcatSegments mtl_system_items_vl.concatenated_segments%TYPE := NULL;
512 itemConcatSegments mtl_system_items_vl.concatenated_segments%TYPE;
513 itemInventoryId mtl_system_items.inventory_item_id%TYPE;
514 itemCatalogGroupId mtl_system_items.item_catalog_group_id%TYPE;
515
516 billSequenceId bom_bill_of_materials.bill_sequence_id%TYPE;
517 billCommonSequenceId bom_bill_of_materials.common_bill_sequence_id%TYPE;
518 useSequenceId bom_bill_of_materials.bill_sequence_id%TYPE;
519
520 bomEffectivityDate bom_inventory_components.effectivity_date%TYPE;
521 bomDisableDate bom_inventory_components.disable_date%TYPE;
522 bomComponentQuantity bom_inventory_components.component_quantity%TYPE;
523 bomComponentSeqId bom_inventory_components.component_sequence_id%TYPE;
524 bomHighQuantity bom_inventory_components.high_quantity%TYPE;
525 bomLowQuantity bom_inventory_components.low_quantity%TYPE;
526 bomMutuallyExclusive bom_inventory_components.mutually_exclusive_options%TYPE;
527 bomItemType bom_inventory_components.bom_item_type%TYPE;
528 bomOptional bom_inventory_components.optional%TYPE;
529 ---------------------------------------------------------------------------------------
530 --build_structure_map->execute_model->execute_structure_map->verify_children_list->hash_item
531
532 --Adds the item that passed all the comparisons to the corresponding hash tables and populates
533 --rollback hash tables for cz_ps_nodes, cz_item_masters and cz_localized_texts.
534
535 PROCEDURE hash_item(j IN PLS_INTEGER) IS
536
537 itemId PLS_INTEGER;
538 BEGIN
539
540 itemId := extract_item_id(j);
541
542 IF(itemId IS NOT NULL)THEN
543
544 debug('Hash map entry: key item_id = ' || itemId || ', inventory_item_id = ' || itemInventoryId || ', sequence_id = ' || bomComponentSeqId);
545
546 component_item_id_map(itemId) := itemInventoryId;
547
548 --SKM: Bug16190145 (Int2Long). Already changed the type of component_seq_id_map to vc2 .
549 IF(tabSequenceId(j) IS NOT NULL)THEN
550 component_seq_id_map(tabSequenceId(j)) := bomComponentSeqId;
551 END IF;
552
553 debug('Update candidate entry: ps_node_id = ' || tabPsNodeId(j) || ', item_id = ' || tabItemId(j) || ', intl_text_id = ' || tabIntlTextId(j));
554
555 IF(NOT hashRbNodeOrigSysRef.EXISTS(tabPsNodeId(j)))THEN
556
557 tabCandidateNode(tabCandidateNode.COUNT + 1) := tabPsNodeId(j);
558 hashRbNodeOrigSysRef(tabPsNodeId(j)) := tabOrigSysRef(j);
559 hashRbNodeSequencePath(tabPsNodeId(j)) := tabSequencePath(j);
560 hashRbNodeSequenceId(tabPsNodeId(j)) := tabSequenceId(j);
561
562 IF(tabItemId(j) IS NOT NULL)THEN
563 IF(NOT hashRbItemOrigSysRef.EXISTS(tabItemId(j)))THEN
564 tabCandidateItem(tabCandidateItem.COUNT + 1) := tabItemId(j);
565 hashRbItemOrigSysRef(tabItemId(j)) := tabItemOrigSysRef(j);
566 END IF;
567 END IF;
568
569 IF(tabIntlTextId(j) IS NOT NULL)THEN
570 IF(NOT hashRbTextOrigSysRef.EXISTS(tabIntlTextId(j)))THEN
571 tabCandidateText(tabCandidateText.COUNT + 1) := tabIntlTextId(j);
572 hashRbTextOrigSysRef(tabIntlTextId(j)) := tabTextOrigSysRef(j);
573 END IF;
574 END IF;
575 END IF;
576 END IF;
577 END; --hash_item
578 ---------------------------------------------------------------------------------------
579 --build_structure_map->execute_model->execute_structure_map->verify_children_list->verify_item_properties
580
581 --All imported item propeties should be present as descriptive elements of the corresponding bom item
582 --and the values should match.
583
584 PROCEDURE verify_item_properties(j IN PLS_INTEGER) IS
585
586 TL_TEXT_TYPE CONSTANT NUMBER := 8;
587 elementValue cz_exv_apc_properties.default_value%TYPE; --bug 10207827;
588 l_val VARCHAR2(4000);
589 l_flag PLS_INTEGER;
590 position NUMBER;
591 l_src_application_id cz_properties.src_application_id%TYPE;
592 l_item_catalog_group_id cz_exv_item_master.item_catalog_group_id%TYPE;
593 l_database_column cz_exv_apc_properties.database_column%TYPE;
594 l_attr_group_id cz_exv_apc_properties.attr_group_id%TYPE;
595 l_attr_id cz_exv_apc_properties.attr_id%TYPE;
596
597 BEGIN
598 FOR c_prop IN (SELECT name, data_type, property_value, property_num_value , p.src_application_id
599 FROM cz_properties p, cz_item_property_values v
600 WHERE p.deleted_flag = FLAG_NOT_DELETED
601 AND p.orig_sys_ref IS NOT NULL
605 AND p.data_type NOT IN(TL_TEXT_TYPE)
602 AND v.deleted_flag = FLAG_NOT_DELETED
603 AND v.item_id = tabItemId(j)
604 AND p.property_id = v.property_id
606 UNION
607 SELECT name, data_type,def_value as property_value,def_num_value as property_num_value,src_application_id
608 FROM cz_properties p
609 WHERE p.property_id IN(
610 SELECT it.property_id FROM cz_item_type_properties it
611 WHERE it.item_type_id IN(SELECT im.item_type_id FROM CZ_ITEM_MASTERS im
612 WHERE im.item_id=tabItemId(j) AND
613 im.deleted_flag=FLAG_NOT_DELETED) AND
614 it.deleted_flag=FLAG_NOT_DELETED
615 ) AND
616 NOT EXISTS(SELECT NULL FROM cz_item_property_values iv
617 WHERE iv.property_id=p.property_id AND iv.item_id=tabItemId(j) AND
618 iv.deleted_flag=FLAG_NOT_DELETED) AND
619 p.deleted_flag = FLAG_NOT_DELETED AND
620 p.src_application_id = APC_APPL_ID AND --Bug13829564 checking the data which comes from PIM
621 p.orig_sys_ref IS NOT NULL AND
622 p.data_type NOT IN(TL_TEXT_TYPE)
623 ) LOOP
624 BEGIN
625 l_src_application_id:=c_prop.src_application_id;
626 IF c_prop.src_application_id = APC_APPL_ID THEN
627 --This is an APC attribute
628 EXECUTE IMMEDIATE
629 'SELECT distinct item_catalog_group_id '||
630 ' FROM mtl_system_items_b'|| targetLinkName ||
631 ' WHERE organization_id = :1 '||
632 ' AND inventory_item_id = :2 '||
633 ' AND item_catalog_group_id IS NOT NULL'
634 INTO l_item_catalog_group_id
635 USING targetOrgId,itemInventoryId;
636
637 position := INSTR(c_prop.name,'.');
638
639 EXECUTE IMMEDIATE
640 'SELECT distinct database_column, attr_group_id, attr_id'||
641 ' FROM cz_exv_apc_properties'|| targetLinkName ||
642 ' WHERE application_id = :1 '||
643 ' AND attr_group_name = :2 '||
644 ' AND attr_name = :3 '||
645 ' AND item_catalog_group_id IN (SELECT item_catalog_group_id '||
646 ' FROM mtl_item_catalog_groups'|| targetLinkName ||' CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id '||
647 ' START WITH item_catalog_group_id = :4 )'
648 INTO l_database_column , l_attr_group_id ,l_attr_id
649 USING APC_APPL_ID , substr(c_prop.name,1,position-1) , substr(c_prop.name,position+1) ,l_item_catalog_group_id;
650
651
652 EXECUTE IMMEDIATE
653 'SELECT to_char('|| l_database_column ||') FROM CZ_EXV_ITEM_APC_PROP_VALUES' || targetLinkName ||
654 ' WHERE INVENTORY_ITEM_ID= :1 and ORGANIZATION_ID = :2 and ITEM_CATALOG_GROUP_ID =:3 '||
655 ' and ATTR_GROUP_ID =:4'
656 INTO elementValue
657 USING itemInventoryId ,targetOrgId,l_item_catalog_group_id,l_attr_group_id ;
658
659 IF elementValue IS NULL THEN
660 EXECUTE IMMEDIATE
661 'SELECT default_value '||
662 ' FROM cz_exv_apc_properties'|| targetLinkName ||
663 ' WHERE application_id = :1 '||
664 ' AND attr_group_id = :2 '||
665 ' AND attr_id = :3 '
666 INTO elementValue
667 USING APC_APPL_ID , l_attr_group_id , l_attr_id;
668 END IF;
669
670 ELSE
671 -- not an APC attribute
672 ---Bug13829564
673 EXECUTE IMMEDIATE
674 'SELECT NVL(element_value, 0) FROM mtl_descr_element_values' || targetLinkName ||
675 ' WHERE inventory_item_id = :1' ||
676 ' AND element_name = :2'
677 INTO elementValue
678 USING itemInventoryId, c_prop.name;
679
680 END IF;
681
682 l_val := c_prop.property_value;
683 l_flag := 0;
684 ---Bug13829564 In case if text property value is NULL and elementValue has become '0' from the above query
685 -- and then reverting elementValue back to NULL.
686 IF((l_val = NULL) AND (elementValue = 0) AND (c_prop.data_type = 4)) THEN
687 elementValue:= NULL;
688 END IF;
689 ---Bug13829564
690 IF(c_prop.data_type IN (1,2))THEN
691
692 l_val := TO_CHAR(NVL(c_prop.property_num_value, 0));
693
694 BEGIN
695
696 elementValue := TO_CHAR(TO_NUMBER(elementValue));
697
698 EXCEPTION
699
700 WHEN INVALID_NUMBER THEN
701 l_flag := 1;
702 WHEN VALUE_ERROR THEN
703 l_flag := 1;
704 END;
705 END IF;
706 IF((l_flag = 1) OR ((l_val IS NULL) <> (elementValue IS NULL)) OR (RTRIM(l_val) <> RTRIM(elementValue)))THEN
707 IF (l_src_application_id=APC_APPL_ID) THEN
708 --The value of the user-defined attribute ATTRIBUTENAME for Inventory Item ITEMNAME with parent PARENTNAME does not match the corresponding Property value.
712 report(CZ_UTILS.GET_TEXT('CZ_SYNC_VALUE_NO_MATCH', 'ITEMNAME', itemConcatSegments, 'PARENTNAME', localParentName, 'ELEMENTNAME', c_prop.name), URGENCY_WARNING);
709 report(CZ_UTILS.GET_TEXT('CZ_SYN_USR_ATTR_PROP_NO_MATCH', 'ITEMNAME', itemConcatSegments, 'PARENTNAME', localParentName, 'ATTRIBUTENAME', c_prop.name), URGENCY_WARNING);
710 ELSE
711 --'Value of descriptive element ''%ELEMENTNAME'' for inventory item ''%ITEMNAME'' with parent ''%PARENTNAME'' does not match with corresponding property value'
713 END IF;
714 END IF;
715
716 EXCEPTION
717 WHEN NO_DATA_FOUND THEN
718 IF (l_src_application_id=APC_APPL_ID) THEN
719 --The Inventory Item ITEMNAME with parent PARENTNAME does not contain the user-defined attribute ATTRIBUTENAME.
720 report(CZ_UTILS.GET_TEXT('CZ_SYN_USR_ATTR_NOT_PRESENT', 'ITEMNAME', itemConcatSegments, 'PARENTNAME', localParentName, 'ATTRIBUTENAME', c_prop.name), URGENCY_WARNING);
721 ELSE
722 --'Inventory item ''%ITEMNAME'' with parent ''%PARENTNAME'' does not have descriptive element ''%ELEMENTNAME'''
723 report(CZ_UTILS.GET_TEXT('CZ_SYNC_NO_SUCH_ELEMENT', 'ITEMNAME', itemConcatSegments, 'PARENTNAME', localParentName, 'ELEMENTNAME', c_prop.name), URGENCY_WARNING);
724 END IF;
725 END;
726 END LOOP;
727 END; --verify_item_properties
728 ---------------------------------------------------------------------------------------
729 --build_structure_map->execute_model->execute_structure_map->verify_children_list->hash_catalog_group
730
731 --If a configuration item is assigned to an imported item type, the corresponding bom item should be
732 --assigned to some catalog group. Verifies that and populates hash and rollback tables.
733
734 PROCEDURE hash_catalog_group(j IN PLS_INTEGER) IS
735
736 catalogId PLS_INTEGER := hashItemTypeId(tabItemMasterTypeId(j));
737 typeName cz_item_types.name%TYPE := hashItemTypeName(tabItemMasterTypeId(j));
738 BEGIN
739
740 IF(itemCatalogGroupId IS NULL)THEN
741
742 --'Inventory item ''%ITEMNAME'' with parent ''%PARENTNAME'' is not assigned to any catalog group. Its corresponding item in model ''%MODELNAME'' is assigned to type ''%TYPENAME'''
743 report(CZ_UTILS.GET_TEXT('CZ_SYNC_NO_CATALOG_GROUP', 'ITEMNAME', itemConcatSegments, 'PARENTNAME', localParentName, 'MODELNAME', generate_name, 'TYPENAME', typeName), URGENCY_WARNING);
744 RETURN;
745 END IF;
746
747 catalog_group_id_map(catalogId) := itemCatalogGroupId;
748 tabCandidateType(tabCandidateType.COUNT + 1) := tabItemMasterTypeId(j);
749 hashRbTypeOrigSysRef(tabItemMasterTypeId(j)) := TO_CHAR(catalogId);
750 END; --hash_catalog_group
751 ---------------------------------------------------------------------------------------
752 BEGIN
753
754 --Query information for the model/option class itself.
755
756 BEGIN
757
758 EXECUTE IMMEDIATE
759 'SELECT b.bill_sequence_id, b.common_bill_sequence_id, i.inventory_item_id, i.item_catalog_group_id' ||
760 ' FROM bom_bill_of_materials' || targetLinkName || ' b, mtl_system_items_vl' || targetLinkName || ' i' ||
761 ' WHERE i.concatenated_segments = :1' ||
762 ' AND i.organization_id = :2' ||
763 ' AND b.assembly_item_id = i.inventory_item_id' ||
764 ' AND b.organization_id = i.organization_id' ||
765 ' AND b.alternate_bom_designator IS NULL'
766 INTO billSequenceId, billCommonSequenceId, itemInventoryId, itemCatalogGroupId
767 USING tabRefPartNbr(j), targetOrgId;
768
769 debug('Values received: bill_sequence_id = ' || billSequenceId || ', common_bill_sequence_id = ' || billCommonSequenceId);
770
771 EXCEPTION
772 WHEN NO_DATA_FOUND THEN
773
774 --Report differently depending on whether this is a root model or not.
775
776 IF(j = jRootNode)THEN
777
778 --'There is no root bill for configuration model ''%MODELNAME'', unable to verify the model'
779 report(CZ_UTILS.GET_TEXT('CZ_SYNC_MODEL_NO_BILL', 'MODELNAME', generate_name), URGENCY_WARNING);
780 ELSE
781
782 --'Item ''%ITEMNAME'' in configuration model ''%MODELNAME'' has no corresponding bill'
783 report(CZ_UTILS.GET_TEXT('CZ_SYNC_ITEM_NO_BILL', 'ITEMNAME', tabRefPartNbr(j), 'MODELNAME', generate_name), URGENCY_WARNING);
784 END IF;
785 RETURN;
786 END;
787
788 --If this is the root model, add the item to the hash tables here because there will be no
789 --other chance to do this.
790
791 IF(j = jRootNode)THEN hash_item(j); END IF;
792
793 --Set the parameters for the comparison algorythm. Note, that the way the variables are
794 --initialized, if the node has no children, all the cycles would be from 1 to 0, empty.
795
796 IF(jhashNodeFirstChild.EXISTS(localPsNodeId))THEN childrenFirst := jhashNodeFirstChild(localPsNodeId); END IF;
797 IF(jhashNodeLastChild.EXISTS(localPsNodeId))THEN childrenLast := jhashNodeLastChild(localPsNodeId); END IF;
798
799 debug('Children pointers: first = ' || childrenFirst || ', last = ' || childrenLast);
800
801 --startPointer points to the first model item eligible for processing. All model items before
802 --this pointer have already got all the processing they may need.
803
804 startPointer := childrenFirst;
805
806 --Use the common bill if common_bill_sequence_id is not null, otherwise use bill_sequence_id.
807
808 useSequenceId := NVL(billCommonSequenceId, billSequenceId);
809
810 --Read the bom children list.
811 --Ordering by effectivity_date provides that components with earlier effectivity dates will
812 --come up first. This should be consistent with ordering by effective_from when quering the
813 --product structure.
814
815 OPEN getBillChildren FOR
819 ' FROM mtl_system_items_vl' || targetLinkName || ' i, bom_inventory_components' || targetLinkName || ' b' ||
816 'SELECT i.inventory_item_id, i.concatenated_segments, b.effectivity_date, b.disable_date, b.component_quantity,' ||
817 ' b.component_sequence_id, b.high_quantity, b.low_quantity, b.mutually_exclusive_options,' ||
818 ' b.bom_item_type, b.optional, i.item_catalog_group_id' ||
820 ' WHERE b.bill_sequence_id = :1' ||
821 ' AND b.implementation_date IS NOT NULL' ||
822 ' AND i.organization_id = :2' ||
823 ' AND (b.optional = :3 OR b.bom_item_type <= :4)' ||
824 ' AND i.inventory_item_id = b.component_item_id' ||
825 ' ORDER BY i.concatenated_segments, b.effectivity_date'
826 USING useSequenceId, targetOrgId, ORACLE_YES, ORACLE_BOM_OPTIONCLASS;
827
828 LOOP
829
830 FETCH getBillChildren INTO itemInventoryId, itemConcatSegments, bomEffectivityDate,
831 bomDisableDate, bomComponentQuantity, bomComponentSeqId,
832 bomHighQuantity, bomLowQuantity, bomMutuallyExclusive,
833 bomItemType, bomOptional, itemCatalogGroupId;
834 EXIT WHEN getBillChildren%NOTFOUND;
835
836 --Handle the effectivity ranges. Need to account for bug #1710684.
837 --Effectivity date is not nullable.
838
839 IF(bomDisableDate IS NULL OR bomDisableDate > EpochEndDate)THEN bomDisableDate := EpochEndDate; END IF;
840 IF(bomEffectivityDate < EpochBeginDate)THEN bomEffectivityDate := EpochBeginDate; END IF;
841
842 --bom_inventory_components.optional can be null which means optional. Also we need to
843 --invert the value to compare to cz_ps_nodes.bom_required_flag.
844
845 IF(bomOptional = ORACLE_NO)THEN
846 bomRequiredFlag := FLAG_BOM_REQUIRED;
847 ELSE
848 bomRequiredFlag := FLAG_BOM_OPTIONAL;
849 END IF;
850
851 --Intersect the effectivity range with the parent's.
852
853 IF(bomEffectivityDate < inEffectivityDate)THEN bomEffectivityDate := inEffectivityDate; END IF;
854 IF(bomDisableDate > inDisableDate)THEN bomDisableDate := inDisableDate; END IF;
855
856 --The item will we skipped if its effectivity range doesn't intersect with its parent range.
857
858 IF(bomEffectivityDate <= bomDisableDate)THEN
859
860 debug('Current start pointer: ' || startPointer || '. Processing item: item_id = ' || itemInventoryId || ', concatenated_segments = ' || itemConcatSegments);
861
862 --Set the sliding pointer. Always start with the first model item eligible for processing.
863
864 slidePointer := startPointer;
865
866 --Scan the model items until match with the current bom item. Do not report all the items scanned
867 --as not having the match at this point because if no match will be found,we will have to process
868 --these items again. Do not skip references except for minimum/maximum verification.
869 --Ref part number is not null, so it is safe to use <not equal> here.
870
871 WHILE(slidePointer <= childrenLast AND (tabRefPartNbr(slidePointer) IS NULL OR tabRefPartNbr(slidePointer) <> itemConcatSegments))LOOP
872 slidePointer := slidePointer + 1;
873 END LOOP;
874
875 --Now the pointer points to the first model item matched with the current bom item. If the pointer
876 --is greater than the index of the last model item, no match was found. If so, report the bom item
877 --as not having match in the configuration model.
878
879 IF(slidePointer > childrenLast)THEN
880
881 IF(bomItemType = ORACLE_BOM_MODEL)THEN
882
883 l_item_type_msg := CZ_UTILS.GET_TEXT('CZ_DEV_TEXT_BOM_MODEL');
884 ELSIF(bomItemType = ORACLE_BOM_OPTIONCLASS)THEN
885
886 l_item_type_msg := CZ_UTILS.GET_TEXT('CZ_DEV_TEXT_OPTION_CLASS');
887 ELSIF(bomItemType = ORACLE_BOM_STANDARD)THEN
888
889 l_item_type_msg := CZ_UTILS.GET_TEXT('CZ_DEV_TEXT_STD_ITEM');
890 ELSE
891
892 l_item_type_msg := CZ_UTILS.GET_TEXT('CZ_DEV_TEXT_UNKNOWN_BOM');
893 END IF;
894
895 IF(localParentType = PS_NODE_TYPE_BOM_MODEL)THEN
896
897 l_parent_type_msg := CZ_UTILS.GET_TEXT('CZ_DEV_TEXT_BOM_MODEL');
898 ELSIF(localParentType = PS_NODE_TYPE_BOM_OPTIONCLASS)THEN
899
900 l_parent_type_msg := CZ_UTILS.GET_TEXT('CZ_DEV_TEXT_OPTION_CLASS');
901 ELSIF(localParentType = PS_NODE_TYPE_BOM_STANDARD)THEN
902
903 l_parent_type_msg := CZ_UTILS.GET_TEXT('CZ_DEV_TEXT_STD_ITEM');
904 ELSE
905
906 l_parent_type_msg := CZ_UTILS.GET_TEXT('CZ_DEV_TEXT_NON_BOM');
907 END IF;
908
909 --'%ITEMTYPE ''%ITEMNAME'' with parent %PARENTTYPE ''%PARENTNAME'' has no match in configuration model ''%MODELNAME'''
910 report(CZ_UTILS.GET_TEXT('CZ_SYNC_INV_NO_MATCH', 'ITEMTYPE', l_item_type_msg, 'ITEMNAME', itemConcatSegments,
911 'PARENTTYPE', l_parent_type_msg, 'PARENTNAME', localParentName, 'MODELNAME', generate_name),
912 URGENCY_WARNING);
913
914 ELSE
915
916 --The match has been found. Now report all the configuration items skipped in the cycle above
917 --because we will never get back to process them.
918
919 FOR i IN startPointer..slidePointer - 1 LOOP
920
921 --'Item ''%ITEMNAME'' with parent ''%PARENTNAME'' in configuration model ''%MODELNAME'' cannot be matched with any inventory item'
922 report(CZ_UTILS.GET_TEXT('CZ_SYNC_ITEM_NO_MATCH', 'ITEMNAME', tabRefPartNbr(i), 'PARENTNAME', localParentName, 'MODELNAME', generate_name), URGENCY_WARNING);
923
924 END LOOP;
925
929
926 debug('Match found at position: ' || slidePointer || ', comparing items...');
927
928 --A match has been found, so we reset the startPointer.
930 startPointer := slidePointer + 1;
931
932 --Now make the actual comparison.
933
934 localString := NVL(TO_CHAR(bomComponentQuantity), '0');
935 IF(localString = '0')THEN localString := '1'; END IF;
936 IF(localString <> tabInitNumVal(slidePointer))THEN -- sselahi
937
938 --'Initial value does not match for item ''%ITEMNAME'' with parent ''%PARENTNAME'' in configuration model ''%MODELNAME'''
939 report(CZ_UTILS.GET_TEXT('CZ_SYNC_INITIAL_VALUE', 'ITEMNAME', tabRefPartNbr(slidePointer), 'PARENTNAME', localParentName, 'MODELNAME', generate_name), URGENCY_WARNING);
940 END IF;
941
942 IF ( modelEngineType = 'L' OR bomLowQuantity IS NOT NULL ) THEN
943
944 bomQuantity := NVL(bomLowQuantity, 0);
945 IF(tabPsNodeType(slidePointer) = PS_NODE_TYPE_REFERENCE)THEN
946 nodeQuantity := NVL(tabMinimumSelected(slidePointer), 0);
947 ELSE
948 nodeQuantity := NVL(tabMinimum(slidePointer), 0);
949 END IF;
950
951 IF(bomQuantity <> nodeQuantity)THEN
952
953 --'Minimum value does not match for item ''%ITEMNAME'' with parent ''%PARENTNAME'' in configuration model ''%MODELNAME'''
954 report(CZ_UTILS.GET_TEXT('CZ_SYNC_MINIMUM_VALUE', 'ITEMNAME', tabRefPartNbr(slidePointer), 'PARENTNAME', localParentName, 'MODELNAME', generate_name), URGENCY_WARNING);
955 END IF;
956 END IF;
957
958 IF ( modelEngineType = 'L' OR bomHighQuantity IS NOT NULL ) THEN
959
960 bomQuantity := NVL(bomHighQuantity, 0);
961 IF(tabPsNodeType(slidePointer) = PS_NODE_TYPE_REFERENCE)THEN
962 nodeQuantity := NVL(tabMaximumSelected(slidePointer), 0);
963 ELSE
964 nodeQuantity := NVL(tabMaximum(slidePointer), 0);
965 END IF;
966 IF(bomQuantity = 0)THEN bomQuantity := -1; END IF;
967 IF(nodeQuantity = 0)THEN nodeQuantity := -1; END IF;
968
969 IF(bomQuantity <> nodeQuantity)THEN
970
971 --'Maximum value does not match for item ''%ITEMNAME'' with parent ''%PARENTNAME'' in configuration model ''%MODELNAME'''
972 report(CZ_UTILS.GET_TEXT('CZ_SYNC_MAXIMUM_VALUE', 'ITEMNAME', tabRefPartNbr(slidePointer), 'PARENTNAME', localParentName, 'MODELNAME', generate_name), URGENCY_WARNING);
973 END IF;
974 END IF;
975
976 IF(bomMutuallyExclusive = ORACLE_YES AND
977 (tabMaximumSelected(slidePointer) IS NULL OR tabMaximumSelected(slidePointer) < 1))THEN
978
979 --'Maximum selected value does not match for item ''%ITEMNAME'' with parent ''%PARENTNAME'' in configuration model ''%MODELNAME'''
980 report(CZ_UTILS.GET_TEXT('CZ_SYNC_MAXIMUM_SELECTED', 'ITEMNAME', tabRefPartNbr(slidePointer), 'PARENTNAME', localParentName, 'MODELNAME', generate_name), URGENCY_WARNING);
981 END IF;
982
983 IF(bomRequiredFlag <> tabBomRequiredFlag(slidePointer))THEN
984
985 --'Required when parent is selected property does not match for item ''%ITEMNAME'' with parent ''%PARENTNAME'' in configuration model ''%MODELNAME'''
986 report(CZ_UTILS.GET_TEXT('CZ_SYNC_BOM_REQUIRED', 'ITEMNAME', tabRefPartNbr(slidePointer), 'PARENTNAME', localParentName, 'MODELNAME', generate_name), URGENCY_WARNING);
987 END IF;
988
989 IF(lastConcatSegments = itemConcatSegments)THEN
990
991 --This is one of the components, not the first one, with the same concatenated_segments,
992 --so both start and end dates should be the same.
993
994 IF(bomEffectivityDate <> tabEffectiveFrom(slidePointer) OR
995 bomDisableDate <> tabEffectiveUntil(slidePointer))THEN
996
997 --'Effectivity range does not match for item ''%ITEMNAME'' with parent ''%PARENTNAME'' in configuration model ''%MODELNAME'''
998 report(CZ_UTILS.GET_TEXT('CZ_SYNC_EFFECTIVITY_RANGE', 'ITEMNAME', tabRefPartNbr(slidePointer), 'PARENTNAME', localParentName, 'MODELNAME', generate_name), URGENCY_WARNING);
999 END IF;
1000 ELSE
1001
1002 --If there are more than one items with the same concatenated_segments, then this is the
1003 --first one, end dates should match, start dates have some logic.
1004
1005 IF(((bomEffectivityDate > SYSDATE OR tabEffectiveFrom(slidePointer) > SYSDATE) AND
1006 bomEffectivityDate <> tabEffectiveFrom(slidePointer)) OR
1007 (bomDisableDate <> tabEffectiveUntil(slidePointer)))THEN
1008
1009 --'Effectivity range does not match for item ''%ITEMNAME'' with parent ''%PARENTNAME'' in configuration model ''%MODELNAME'''
1010 report(CZ_UTILS.GET_TEXT('CZ_SYNC_EFFECTIVITY_RANGE', 'ITEMNAME', tabRefPartNbr(slidePointer), 'PARENTNAME', localParentName, 'MODELNAME', generate_name), URGENCY_WARNING);
1011 END IF;
1012 END IF;
1013
1014 --If a configuration item belongs to an imported item type, the corresponding bom item should
1015 --belong to some catalog group.
1016
1017 IF(tabItemMasterTypeId(slidePointer) IS NOT NULL AND hashItemTypeId.EXISTS(tabItemMasterTypeId(slidePointer)))THEN
1018
1019 hash_catalog_group(slidePointer);
1020 END IF;
1021
1022 IF(VerifyItemProperties = 1)THEN
1023
1024 verify_item_properties(slidePointer);
1025 END IF;
1026
1027 --Add the item to the hash tables.
1028
1029 hash_item(slidePointer);
1030 lastConcatSegments := itemConcatSegments;
1031
1032 --For bom option classes call the procedure recursively.
1033
1034 IF(bomItemType = ORACLE_BOM_OPTIONCLASS)THEN
1035
1036 debug('Ready to verify children for item id ' || itemInventoryId || ', item name ''' || itemConcatSegments || '''');
1037
1038 verify_children_list(slidePointer, bomEffectivityDate, bomDisableDate);
1039 END IF;
1040 END IF; --item has been matched
1041 END IF; --effectivity ranges intersect
1042 END LOOP;
1043 CLOSE getBillChildren;
1044
1045 --Report all the not matched children at the end of the list.
1046
1047 FOR i IN startPointer..childrenLast LOOP
1048
1049 --'Item ''%ITEMNAME'' with parent ''%PARENTNAME'' in configuration model ''%MODELNAME'' cannot be matched with any inventory item'
1050 report(CZ_UTILS.GET_TEXT('CZ_SYNC_ITEM_NO_MATCH', 'ITEMNAME', tabRefPartNbr(i), 'PARENTNAME', localParentName, 'MODELNAME', generate_name), URGENCY_WARNING);
1051
1052 END LOOP;
1053
1054 debug('Returning to caller after processing children of item id ' || tabPsNodeId(j) || ', item name ''' || tabRefPartNbr(j) || '''');
1055
1056 EXCEPTION
1057 WHEN OTHERS THEN
1058
1059 --Just see if the cursor variable is open and close it if necessary.
1060
1061 IF(getBillChildren%ISOPEN)THEN CLOSE getBillChildren; END IF;
1062 RAISE;
1063 END; --verify_children_list
1064 ---------------------------------------------------------------------------------------
1065 BEGIN --execute_structure_map
1066
1067 debug('Entering model with model id ' || p_model_id);
1068
1069 --We don't want to verify the same model more than once during the session.
1070
1071 IF(alreadyVerified.EXISTS(p_model_id))THEN
1072
1073 debug('Model already processed before, exiting...');
1074 RETURN;
1075 END IF;
1076 alreadyVerified(p_model_id) := 1;
1077
1078 debug('Reading the product structure:');
1079
1080 --The statement uses ordering by parent_id to ensure that for every node all of its
1081 --children will follow it in a dense list.
1082
1083 SELECT p.ps_node_id, p.parent_id, p.ps_node_type, p.item_id, p.effective_from,
1084 p.effective_until, p.minimum, maximum, p.minimum_selected, p.maximum_selected,
1085 p.initial_value, p.initial_num_value, p.reference_id, p.name, p.orig_sys_ref, p.component_sequence_path, --sselahi
1086 p.component_sequence_id, p.intl_text_id, p.bom_required_flag, t.orig_sys_ref,
1087 i.ref_part_nbr, i.orig_sys_ref, i.item_type_id
1088 BULK COLLECT INTO tabPsNodeId, tabParentId, tabPsNodeType, tabItemId, tabEffectiveFrom,
1089 tabEffectiveUntil, tabMinimum, tabMaximum, tabMinimumSelected, tabMaximumSelected,
1090 tabInitialValue, tabInitNumVal, tabReferenceId, tabPsNodeName, tabOrigSysRef, tabSequencePath, -- sselahi
1091 tabSequenceId, tabIntlTextId, tabBomRequiredFlag, tabTextOrigSysRef,
1092 tabRefPartNbr, tabItemOrigSysRef, tabItemMasterTypeId
1093 FROM cz_item_masters i, cz_ps_nodes p, cz_localized_texts t
1094 WHERE p.devl_project_id = p_model_id
1095 AND p.deleted_flag = FLAG_NOT_DELETED
1096 AND p.orig_sys_ref IS NOT NULL
1097 AND p.src_application_id = 702
1098 AND t.language (+) = baseLanguageCode
1099 AND p.item_id = i.item_id (+)
1100 AND p.intl_text_id = t.intl_text_id (+)
1101 ORDER BY p.parent_id, i.ref_part_nbr, p.effective_from;
1102
1103 --Populate auxiliary arrays and values:
1104 --jhashNodeFirstChild - index of the first node's child;
1105 --jhashNodeLastChild - index of the last node's child;
1106 --jRootNode - index of the root node.
1107
1108 FOR i IN 1..tabPsNodeId.COUNT LOOP
1109
1110 --Fix the effective_until date (bug #2006980).
1111
1112 IF(tabEffectiveUntil(i) > EpochEndLine)THEN tabEffectiveUntil(i) := EpochEndDate; END IF;
1113
1114 IF(tabParentId(i) IS NOT NULL)THEN
1115
1116 IF(NOT jhashNodeFirstChild.EXISTS(tabParentId(i)))THEN
1117
1118 --This is populated only for the first child.
1119
1120 jhashNodeFirstChild(tabParentId(i)) := i;
1121 END IF;
1122
1123 --This is always rolled forward to the next child.
1124
1125 jhashNodeLastChild(tabParentId(i)) := i;
1126
1127 ELSE
1128
1129 --This is the root node.
1130
1131 jRootNode := i;
1132 END IF;
1133
1134 debug('j = ' || i || ', ps_node_id = ' || tabPsNodeId(i) || ', parent_id = ' || NVL(TO_CHAR(tabParentId(i)), '<null>') ||
1135 ', ps_node_type = ' || tabPsNodeType(i) || ', reference_id = ' || tabReferenceId(i) || ', ref_part_nbr = ' || tabRefPartNbr(i) ||
1136 ', orig_sys_ref = ' || tabItemOrigSysRef(i));
1137 END LOOP;
1138
1139 --Step down from the root node until we find the root BOM model.
1140
1141 WHILE(tabPsNodeType(jRootNode) <> PS_NODE_TYPE_BOM_MODEL)LOOP
1142
1143 --Raise the error if the node has more than one child because this is an unexpected
1144 --structure. If there is only one child, step down to it.
1145
1146 IF(jhashNodeFirstChild(tabPsNodeId(jRootNode)) - jhashNodeLastChild(tabPsNodeId(jRootNode)) > 0)THEN
1147 RAISE CZ_SYNC_UNEXPECTED_STRUCTURE;
1148 END IF;
1149 jRootNode := jhashNodeFirstChild(tabPsNodeId(jRootNode));
1150 END LOOP;
1151
1155 hashRbDevlOrigSysRef(p_model_id) := extract_project_reference(jRootNode);
1152 --Populate candidate tables and rollback hash tables for cz_devl_projects and cz_xfr_project_bills.
1153
1154 tabCandidateDevl(tabCandidateDevl.COUNT + 1) := p_model_id;
1156
1157 tabCandidateProj(tabCandidateProj.COUNT + 1) := p_model_id;
1158 hashRbOrganizationId(p_model_id) := sourceOrgId;
1159 hashRbTopItemId(p_model_id) := extract_item_id(jRootNode);
1160 hashRbComponentItemId(p_model_id) := hashRbTopItemId(p_model_id);
1161 hashRbSourceServer(p_model_id) := sourceServer;
1162
1163 --Maintain the stack of model names in order to report full paths.
1164
1165 modelNameStack(modelNameStack.COUNT + 1) := tabPsNodeName(jRootNode);
1166
1167 debug('Root resolved, root index ' || jRootNode || ', model name ''' || generate_name || '''');
1168
1169 --Follow the references.
1170
1171 FOR i IN 1..tabPsNodeId.COUNT LOOP
1172
1173 IF(tabPsNodeType(i) = PS_NODE_TYPE_REFERENCE)THEN
1174
1175 debug('Following the reference to model id ' || tabReferenceId(i));
1176
1177 execute_structure_map(tabReferenceId(i));
1178 END IF;
1179 END LOOP;
1180
1181 debug('Ready to verify children starting with the root model id ' || p_model_id || ', model name ''' || generate_name || '''');
1182
1183 verify_children_list(jRootNode, EpochBeginDate, EpochEndDate);
1184 modelNameStack.DELETE(modelNameStack.COUNT);
1185
1186 debug('Returning to caller after processing model with id ' || p_model_id);
1187
1188 --This exception block handles all the exceptions that should terminate the process for
1189 --the current model but go on for other models.
1190 --Fatal exceptions may also be caught here to make some scope-specific processing, then
1191 --they should be re-raised.
1192
1193 EXCEPTION
1194 WHEN CZ_SYNC_UNEXPECTED_STRUCTURE THEN
1195 --'Configuration model ''%MODELNAME'' has incorrect structure and cannot be synchronized'
1196 report(CZ_UTILS.GET_TEXT('CZ_SYNC_UNEXPECTED_STRUCTURE', 'MODELNAME', tabPsNodeName(jRootNode)), URGENCY_ERROR);
1197 WHEN OTHERS THEN
1198 RAISE;
1199 END; --execute_structure_map
1200 ---------------------------------------------------------------------------------------
1201 --build_structure_map->execute_model->extract_organization_id
1202
1203 --Extracts organization_id from cz_devl_projects.orig_sys_ref.
1204
1205 FUNCTION extract_organization_id RETURN PLS_INTEGER IS
1206
1207 startPos PLS_INTEGER;
1208 endPos PLS_INTEGER;
1209
1210 BEGIN
1211
1212 startPos := INSTR(modelOrigSysRef, ORIGINAL_SEPARATOR, -1, 2) + 1;
1213 endPos := INSTR(modelOrigSysRef, ORIGINAL_SEPARATOR, -1, 1);
1214
1215 --The return value can only be a not null valid number.
1216
1217 RETURN TO_NUMBER(NVL(SUBSTR(modelOrigSysRef, startPos, endPos - startPos), 'NULL'));
1218 EXCEPTION
1219 WHEN OTHERS THEN
1220 --'Unable to extract organization id for model ''%MODELNAME'', original system reference ''%ORIGSYSREF'''
1221 report(CZ_UTILS.GET_TEXT('CZ_SYNC_INVALID_ORG_ID', 'MODELNAME', modelName, 'ORIGSYSREF', modelOrigSysRef), URGENCY_WARNING);
1222 RAISE CZ_SYNC_NO_ORGANIZATION_ID;
1223 END;
1224 ---------------------------------------------------------------------------------------
1225 BEGIN --execute_model
1226
1227 BEGIN
1228
1229 SELECT orig_sys_ref, name, NVL ( config_engine_type, 'L' ) INTO modelOrigSysRef, modelName, modelEngineType
1230 FROM cz_devl_projects
1231 WHERE deleted_flag = FLAG_NOT_DELETED
1232 AND devl_project_id = p_model_id;
1233
1234 EXCEPTION
1235 WHEN NO_DATA_FOUND THEN
1236 RAISE CZ_SYNC_INCORRECT_MODEL;
1237 END;
1238
1239 IF(modelOrigSysRef IS NOT NULL)THEN
1240
1241 --This is a BOM model.
1242
1243 IF(sourceServer IS NULL)THEN
1244
1245 --This procedure is called for a single model, so we will get and verify the source server
1246 --for this particular model here. If not null, the source server has already been verified
1247 --from verify_source_instance procedure.
1248 --Also this model has to be an original model, not a publishing target model, so it has to
1249 --have its own record in cz_xfr_project_bills.
1250
1251 BEGIN
1252
1253 SELECT source_server INTO sourceServer
1254 FROM cz_xfr_project_bills
1255 WHERE model_ps_node_id = p_model_id
1256 AND deleted_flag = FLAG_NOT_DELETED;
1257
1258 EXCEPTION
1259 WHEN NO_DATA_FOUND THEN
1260 RAISE CZ_SYNC_INCORRECT_MODEL;
1261 END;
1262
1263 verify_source_server;
1264 END IF;
1265
1266 --We always need organization_id. We cannot always read it from cz_xfr_project_bills
1267 --because publishing targets will not have records there. This why we extracting the
1268 --value from orig_sys_ref rather than reading it from a table.
1269
1270 sourceOrgId := extract_organization_id;
1271
1272 --Add to the organization hash table for the resolved model if not there yet.
1273
1274 IF(NOT organization_id_map.EXISTS(sourceOrgId))THEN
1275 BEGIN
1276
1277 EXECUTE IMMEDIATE 'SELECT organization_id FROM org_organization_definitions' || targetLinkName ||
1278 ' WHERE UPPER(organization_name) = ' ||
1279 ' (SELECT UPPER(organization_name) FROM org_organization_definitions' || sourceLinkName ||
1280 ' WHERE organization_id = :1)'
1281 INTO targetOrgId USING sourceOrgId;
1282
1283 organization_id_map(sourceOrgId) := targetOrgId;
1284
1285 EXCEPTION
1286 WHEN NO_DATA_FOUND THEN
1287 RAISE CZ_SYNC_NO_ORGANIZATION_ID;
1288 END;
1289 ELSE
1290 targetOrgId := organization_id_map(sourceOrgId);
1291 END IF;
1295
1292
1293 --Populate the candidate and rollback tables for cz_model_publications. We need to do that
1294 --only in the SYNC mode, because we have nothing to verify.
1296 IF(p_execution_mode = EXECUTION_MODE_SYNC)THEN
1297
1298 FOR c_pub IN (SELECT publication_id, top_item_id, organization_id, product_key
1299 FROM cz_model_publications
1300 WHERE model_id = p_model_id
1301 AND deleted_flag = FLAG_NOT_DELETED)LOOP
1302
1303 tabCandidatePubl(tabCandidatePubl.COUNT + 1) := c_pub.publication_id;
1304 hashRbPubOrganizationId(c_pub.publication_id) := c_pub.organization_id;
1305 hashRbPubTopItemId(c_pub.publication_id) := c_pub.top_item_id;
1306 hashRbPubProductKey(c_pub.publication_id) := c_pub.product_key;
1307 END LOOP;
1308 END IF;
1309
1310 --Start the process.
1311
1312 execute_structure_map(p_model_id);
1313
1314 ELSE
1315
1316 --This is a non-BOM model, but we still need to follow the references.
1317
1318 FOR c_model IN (SELECT component_id FROM cz_model_ref_expls
1319 WHERE model_id = p_model_id
1320 AND deleted_flag = FLAG_NOT_DELETED
1321 AND ps_node_type = PS_NODE_TYPE_REFERENCE
1322 AND node_depth = 1)LOOP
1323
1324 execute_model(c_model.component_id);
1325 END LOOP;
1326 END IF;
1327
1328 EXCEPTION
1329 WHEN CZ_SYNC_INCORRECT_MODEL THEN
1330 --'Unable to resolve the specified model id: %MODELID'
1331 report(CZ_UTILS.GET_TEXT('CZ_SYNC_INCORRECT_MODEL', 'MODELID', p_model_id), URGENCY_WARNING);
1332 WHEN CZ_SYNC_NO_ORGANIZATION_ID THEN
1333 --'Unable to resolve source organization for the configuration model ''%MODELNAME'''
1334 report(CZ_UTILS.GET_TEXT('CZ_SYNC_NO_ORGANIZATION_ID', 'MODELNAME', modelName), URGENCY_WARNING);
1335 END; --execute_model
1336 ---------------------------------------------------------------------------------------
1337 --build_structure_map->verify_target_instance
1338
1339 --Reads and verifies the link to the instance specified as target.
1340
1341 PROCEDURE verify_target_instance IS
1342
1343 linkName cz_servers.fndnam_link_name%TYPE;
1344
1345 BEGIN
1346
1347 --Read the link name from cz_servers for the specified target instance and verify the link.
1348
1349 BEGIN
1350
1351 SELECT fndnam_link_name, server_local_id INTO linkName, g_target_instance
1352 FROM cz_servers
1353 WHERE UPPER(local_name) = UPPER(p_target_name);
1354
1355 EXCEPTION
1356 WHEN NO_DATA_FOUND THEN
1357 --'Unable to resolve the specified target instance name: %TARGETNAME'
1358 report(CZ_UTILS.GET_TEXT('CZ_SYNC_INCORRECT_TARGET', 'TARGETNAME', p_target_name), URGENCY_ERROR);
1359 RAISE CZ_SYNC_GENERAL_EXCEPTION;
1360 END;
1361
1362 IF(linkName IS NOT NULL)THEN
1363
1364 verify_database_link(linkName);
1365 linkName := '@' || linkName;
1366
1367 END IF;
1368
1369 --The link is verified, assign the global variable to be used over the code.
1370
1371 targetLinkName := linkName;
1372
1373 EXCEPTION
1374 WHEN CZ_SYNC_NO_DATABASE_LINK THEN
1375 --'Database link does not exist for the specified target instance ''%TARGETNAME'''
1376 report(CZ_UTILS.GET_TEXT('CZ_SYNC_NO_DATABASE_LINK', 'TARGETNAME', p_target_name), URGENCY_ERROR);
1377 RAISE CZ_SYNC_GENERAL_EXCEPTION;
1378 END; --verify_target_instance
1379 ---------------------------------------------------------------------------------------
1380 --build_structure_map->verify_source_instance
1381
1382 --Verified that there is only one import source server for all models, there is no
1383 --pending publications and the target instance is different from the import source
1384 --server.
1385
1386 PROCEDURE verify_source_instance IS
1387 BEGIN
1388 BEGIN
1389
1390 SELECT DISTINCT source_server INTO sourceServer
1391 FROM cz_xfr_project_bills b, cz_devl_projects r
1392 WHERE b.deleted_flag = FLAG_NOT_DELETED
1393 AND r.deleted_flag = FLAG_NOT_DELETED
1394 AND b.model_ps_node_id = r.devl_project_id;
1395
1396 EXCEPTION
1397 WHEN NO_DATA_FOUND THEN
1398 --'Unable to find any imported models to synchronize, import control table is empty'
1399 report(CZ_UTILS.GET_TEXT('CZ_SYNC_NO_MODELS'), URGENCY_ERROR);
1400 RAISE CZ_SYNC_GENERAL_EXCEPTION;
1401 WHEN TOO_MANY_ROWS THEN
1402 --'Multiple import source servers found, unable to synchronize'
1403 report(CZ_UTILS.GET_TEXT('CZ_SYNC_TOO_MANY_SERVERS'), URGENCY_ERROR);
1404 RAISE CZ_SYNC_GENERAL_EXCEPTION;
1405 END;
1406
1407 --Check if any publication is currently processing.
1408
1409 FOR c_pub IN (SELECT publication_id FROM cz_model_publications
1410 WHERE deleted_flag = FLAG_NOT_DELETED
1411 AND export_status = PUBLICATION_STATUS_PROCESSING)LOOP
1412
1413 --'Synchronization cannot be done while a publication is processing. At least one publication (%PUBLICATIONID) is currently in processing status'
1414 report(CZ_UTILS.GET_TEXT('CZ_SYNC_PUB_PROCESSING'), URGENCY_ERROR);
1415 RAISE CZ_SYNC_GENERAL_EXCEPTION;
1416 END LOOP;
1417
1418 IF(sourceServer = g_target_instance)THEN
1419 --'Import source server and synchronization target instance are the same, synchronization is not required'
1420 report(CZ_UTILS.GET_TEXT('CZ_SYNC_SAME_INSTANCE'), URGENCY_WARNING);
1421 RAISE CZ_SYNC_NORMAL_EXCEPTION;
1422 END IF;
1423
1424 verify_source_server;
1425 END; --verify_source_instance
1426 ---------------------------------------------------------------------------------------
1427 --build_structure_map->clear_structure_maps
1428
1429 --Clears global hash tables.
1430
1434 component_item_id_map.DELETE;
1431 PROCEDURE clear_structure_maps IS
1432 BEGIN
1433
1435 component_seq_id_map.DELETE;
1436 catalog_group_id_map.DELETE;
1437 organization_id_map.DELETE;
1438
1439 targetLinkName := NULL;
1440 g_target_instance := NULL;
1441 END;
1442 ---------------------------------------------------------------------------------------
1443 --build_structure_map->rollback_structure
1444
1445 --Rollback procedure.
1446
1447 PROCEDURE rollback_structure IS
1448 BEGIN
1449
1450 FOR i IN 1..nodeRollback LOOP
1451 UPDATE cz_ps_nodes SET
1452 orig_sys_ref = hashRbNodeOrigSysRef(tabCandidateNode(i)),
1453 component_sequence_path = hashRbNodeSequencePath(tabCandidateNode(i)),
1454 component_sequence_id = hashRbNodeSequenceId(tabCandidateNode(i))
1455 WHERE ps_node_id = tabCandidateNode(i);
1456 COMMIT;
1457 END LOOP;
1458
1459 debug('Table cz_ps_nodes updates rolled back');
1460
1461 FOR i IN 1..itemRollback LOOP
1462 UPDATE cz_item_masters SET
1463 orig_sys_ref = hashRbItemOrigSysRef(tabCandidateItem(i))
1464 WHERE item_id = tabCandidateItem(i);
1465 COMMIT;
1466 END LOOP;
1467
1468 debug('Table cz_item_masters updates rolled back');
1469
1470 FOR i IN 1..devlRollback LOOP
1471 UPDATE cz_devl_projects SET
1472 orig_sys_ref = hashRbDevlOrigSysRef(tabCandidateDevl(i))
1473 WHERE devl_project_id = tabCandidateDevl(i);
1474 COMMIT;
1475 END LOOP;
1476
1477 debug('Table cz_devl_projects updates rolled back');
1478
1479 FOR i IN 1..textRollback LOOP
1480 UPDATE cz_localized_texts SET
1481 orig_sys_ref = hashRbTextOrigSysRef(tabCandidateText(i))
1482 WHERE intl_text_id = tabCandidateText(i);
1483 COMMIT;
1484 END LOOP;
1485
1486 debug('Table cz_localized_texts updates rolled back');
1487
1488 FOR i IN 1..typeRollback LOOP
1489 UPDATE cz_item_types SET
1490 orig_sys_ref = hashRbTypeOrigSysRef(tabCandidateType(i))
1491 WHERE item_type_id = tabCandidateType(i);
1492 COMMIT;
1493 END LOOP;
1494
1495 debug('Table cz_item_types updates rolled back');
1496
1497 FOR i IN 1..projRollback LOOP
1498 UPDATE cz_xfr_project_bills SET
1499 organization_id = hashRbOrganizationId(tabCandidateProj(i)),
1500 top_item_id = hashRbTopItemId(tabCandidateProj(i)),
1501 component_item_id = hashRbComponentItemId(tabCandidateProj(i)),
1502 source_server = hashRbSourceServer(tabCandidateProj(i))
1503 WHERE model_ps_node_id = tabCandidateProj(i);
1504 COMMIT;
1505 END LOOP;
1506
1507 debug('Table cz_xfr_project_bills updates rolled back');
1508
1509 FOR i IN 1..publRollback LOOP
1510 UPDATE cz_model_publications SET
1511 organization_id = hashRbPubOrganizationId(tabCandidatePubl(i)),
1512 top_item_id = hashRbPubTopItemId(tabCandidatePubl(i)),
1513 product_key = hashRbPubProductKey(tabCandidatePubl(i))
1514 WHERE publication_id = tabCandidatePubl(i);
1515 COMMIT;
1516 END LOOP;
1517
1518 debug('Table cz_model_publications updates rolled back');
1519 FOR i IN 1..tabRbItmPropValItemId.count LOOP
1520 UPDATE cz_item_property_values SET
1521 orig_sys_ref = tabRbItmPropValOrigSysRef(i)
1522 WHERE item_id = tabRbItmPropValItemId(i)
1523 and property_id=tabRbItmPropValPropId(i)
1524 and deleted_flag='0';
1525 COMMIT;
1526 END LOOP;
1527
1528 debug('Table cz_item_property_values updates rolled back');
1529
1530 FOR i IN 1..tabRbItmTypPropItTypeId.count LOOP
1531 UPDATE CZ_ITEM_TYPE_PROPERTIES SET
1532 orig_sys_ref = tabRbItmTypPropOrigSysRef(i)
1533 WHERE item_type_id = tabRbItmTypPropItTypeId(i)
1534 and property_id=tabRbItmTypPropId(i)
1535 and deleted_flag='0';
1536 COMMIT;
1537 END LOOP;
1538
1539 debug('Table CZ_ITEM_TYPE_PROPERTIES updates rolled back');
1540
1541 END; --rollback_structure;
1542 ---------------------------------------------------------------------------------------
1543 --build_structure_map->synchronize_structure
1544
1545 --Performs the actual database update for synchronization.
1546
1547 PROCEDURE synchronize_structure IS
1548
1549 CommitBlockSize PLS_INTEGER;
1550 textCommitBlockSize PLS_INTEGER;
1551 segmentStart PLS_INTEGER;
1552 segmentEnd PLS_INTEGER;
1553 localCount PLS_INTEGER;
1554 loopCount PLS_INTEGER;
1555 l_tabRbItmPropValPropId typePropertyId;
1556 l_tabRbItmTypPropId typePropertyId;
1557
1558 l_tabRbItmPropValOrigSysRef typeOrigSysRef;
1559 l_tabRbItmTypPropOrigSysRef typeOrigSysRef;
1560
1561 l_tabRbItmPropValItemId typeItemId;
1562 l_tabRbItmTypPropItTypeId typeItemTypeId;
1563
1564 BEGIN
1565
1566 --Read the commit block size.
1567
1568 BEGIN
1569
1570 SELECT TO_NUMBER(value) INTO CommitBlockSize
1571 FROM cz_db_settings
1572 WHERE UPPER(setting_id) = COMMIT_BLOCK_SETTING_ID
1573 AND UPPER(section_name) = DBSETTINGS_SECTION_NAME;
1574
1575 EXCEPTION
1576 WHEN OTHERS THEN
1577 CommitBlockSize := DEFAULT_COMMIT_BLOCK_SIZE;
1578 END;
1579
1580 --Decrease the commit block size for cz_localized_texts according to the number of
1581 --installed languages.
1582
1583 textCommitBlockSize := CommitBlockSize / numberOfLanguages;
1584
1585 --Update cz_ps_nodes table.
1586
1587 localCount := tabCandidateNode.COUNT;
1591
1588 segmentStart := 1;
1589
1590 debug('Updating cz_ps_nodes table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
1592 --Have to use NVL in the following update statements, because FORALL construct is extremely
1593 --tolerant to any exceptions occurring inside the functions, including data not found. When
1594 --the exception occurs it is just like the function returned null. This may result in lost
1595 --data because of the code internal bugs. When the code is known to work correctly, NVL can
1596 --be removed if they decrease performance.
1597
1598 WHILE(segmentStart <= localCount)LOOP
1599
1600 segmentEnd := segmentStart + CommitBlockSize - 1;
1601 IF(segmentEnd > localCount)THEN segmentEnd := localCount; END IF;
1602 nodeRollback := segmentEnd;
1603
1604 FORALL i IN segmentStart..segmentEnd
1605 UPDATE cz_ps_nodes SET
1606 orig_sys_ref = NVL(psnode_origSysRef(orig_sys_ref), orig_sys_ref),
1607 component_sequence_path = NVL(psnode_compSeqPath(component_sequence_path), component_sequence_path),
1608 component_sequence_id = NVL(psnode_compSeqId(component_sequence_id), component_sequence_id)
1609 WHERE ps_node_id = tabCandidateNode(i);
1610
1611 COMMIT;
1612 segmentStart := segmentEnd + 1;
1613
1614 END LOOP;
1615
1616 --Update cz_item_masters table.
1617
1618 localCount := tabCandidateItem.COUNT;
1619 segmentStart := 1;
1620
1621 debug('Updating cz_item_masters table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
1622
1623 WHILE(segmentStart <= localCount)LOOP
1624
1625 segmentEnd := segmentStart + CommitBlockSize - 1;
1626 IF(segmentEnd > localCount)THEN segmentEnd := localCount; END IF;
1627 itemRollback := segmentEnd;
1628
1629 FORALL i IN segmentStart..segmentEnd
1630 UPDATE cz_item_masters SET
1631 orig_sys_ref = NVL(itemMaster_origSysRef(orig_sys_ref), orig_sys_ref)
1632 WHERE item_id = tabCandidateItem(i);
1633
1634 COMMIT;
1635 segmentStart := segmentEnd + 1;
1636
1637 END LOOP;
1638
1639 --Update cz_devl_projects table.
1640
1641 localCount := tabCandidateDevl.COUNT;
1642 segmentStart := 1;
1643
1644 debug('Updating cz_devl_projects table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
1645
1646 WHILE(segmentStart <= localCount)LOOP
1647
1648 segmentEnd := segmentStart + CommitBlockSize - 1;
1649 IF(segmentEnd > localCount)THEN segmentEnd := localCount; END IF;
1650 devlRollback := segmentEnd;
1651
1652 FORALL i IN segmentStart..segmentEnd
1653 UPDATE cz_devl_projects SET
1654 orig_sys_ref = NVL(devlProject_origSysRef(orig_sys_ref), orig_sys_ref)
1655 WHERE devl_project_id = tabCandidateDevl(i);
1656
1657 COMMIT;
1658 segmentStart := segmentEnd + 1;
1659
1660 END LOOP;
1661
1662 --Update cz_localized_texts table.
1663
1664 localCount := tabCandidateText.COUNT;
1665 segmentStart := 1;
1666
1667 debug('Updating cz_localized_texts table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
1668
1669 WHILE(segmentStart <= localCount)LOOP
1670
1671 segmentEnd := segmentStart + textCommitBlockSize - 1;
1672 IF(segmentEnd > localCount)THEN segmentEnd := localCount; END IF;
1673 textRollback := segmentEnd;
1674
1675 FORALL i IN segmentStart..segmentEnd
1676 UPDATE cz_localized_texts SET
1677 orig_sys_ref = NVL(locText_origSysRef(orig_sys_ref), orig_sys_ref)
1678 WHERE intl_text_id = tabCandidateText(i);
1679
1680 COMMIT;
1681 segmentStart := segmentEnd + 1;
1682
1683 END LOOP;
1684
1685 --Update cz_item_types table.
1686
1687 localCount := tabCandidateType.COUNT;
1688 segmentStart := 1;
1689
1690 debug('Updating cz_item_types table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
1691
1692 WHILE(segmentStart <= localCount)LOOP
1693
1694 segmentEnd := segmentStart + textCommitBlockSize - 1;
1695 IF(segmentEnd > localCount)THEN segmentEnd := localCount; END IF;
1696 typeRollback := segmentEnd;
1697
1698 FORALL i IN segmentStart..segmentEnd
1699 UPDATE cz_item_types SET
1700 orig_sys_ref = NVL(itemtype_origSysRef(orig_sys_ref), orig_sys_ref)
1701 WHERE item_type_id = tabCandidateType(i);
1702
1703 COMMIT;
1704 segmentStart := segmentEnd + 1;
1705
1706 END LOOP;
1707
1708 --Update cz_xfr_project_bills table.
1709
1710 localCount := tabCandidateProj.COUNT;
1711 segmentStart := 1;
1712
1713 debug('Updating cz_xfr_project_bills table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
1714
1715 WHILE(segmentStart <= localCount)LOOP
1716
1717 segmentEnd := segmentStart + CommitBlockSize - 1;
1718 IF(segmentEnd > localCount)THEN segmentEnd := localCount; END IF;
1719 projRollback := segmentEnd;
1720
1721 FORALL i IN segmentStart..segmentEnd
1722 UPDATE cz_xfr_project_bills SET
1723 organization_id = NVL(projectBill_orgId(organization_id), organization_id),
1724 top_item_id = NVL(projectBill_topItemId(top_item_id), top_item_id),
1725 component_item_id = NVL(projectBill_compItemId(component_item_id), component_item_id),
1726 source_server = NVL(projectBill_sourceServer(source_server), source_server)
1727 WHERE model_ps_node_id = tabCandidateProj(i);
1728
1732 END LOOP;
1729 COMMIT;
1730 segmentStart := segmentEnd + 1;
1731
1733
1734 --Update cz_model_publications table.
1735
1736 localCount := tabCandidatePubl.COUNT;
1737 segmentStart := 1;
1738
1739 debug('Updating cz_model_publications table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
1740
1741 WHILE(segmentStart <= localCount)LOOP
1742
1743 segmentEnd := segmentStart + CommitBlockSize - 1;
1744 IF(segmentEnd > localCount)THEN segmentEnd := localCount; END IF;
1745 publRollback := segmentEnd;
1746
1747 FORALL i IN segmentStart..segmentEnd
1748 UPDATE cz_model_publications SET
1749 organization_id = NVL(modelPublication_orgId(organization_id), organization_id),
1750 top_item_id = NVL(modelPublication_topItemId(top_item_id), top_item_id),
1751 product_key = NVL(modelPublication_productKey(product_key), product_key)
1752 WHERE publication_id = tabCandidatePubl(i);
1753
1754 COMMIT;
1755 segmentStart := segmentEnd + 1;
1756
1757 END LOOP;
1758
1759 --Update cz_item_property_values table.
1760
1761 localCount := tabCandidateItem.COUNT;
1762 segmentStart := 1;
1763
1764 debug('Updating cz_item_property_values table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
1765
1766 WHILE(segmentStart <= localCount)LOOP
1767
1768 segmentEnd := segmentStart + CommitBlockSize - 1;
1769 IF(segmentEnd > localCount)THEN segmentEnd := localCount; END IF;
1770 itemPropValRollback := segmentEnd;
1771
1772 for i IN segmentStart..segmentEnd LOOP
1773
1774 select ITEM_ID,PROPERTY_ID,ORIG_SYS_REF
1775 BULK COLLECT INTO l_tabRbItmPropValItemId,l_tabRbItmPropValPropId,l_tabRbItmPropValOrigSysRef
1776 FROM cz_item_property_values where item_id = tabCandidateItem(i)
1777 and deleted_flag='0';
1778
1779 loopCount:=tabRbItmPropValItemId.count;
1780 for j in 1..l_tabRbItmPropValItemId.COUNT
1781 LOOP
1782
1783 loopCount:=loopCount+1;
1784 tabRbItmPropValItemId(loopCount):=l_tabRbItmPropValItemId(j);
1785 tabRbItmPropValPropId(loopCount):=l_tabRbItmPropValPropId(j);
1786 tabRbItmPropValOrigSysRef(loopCount):=l_tabRbItmPropValOrigSysRef(j);
1787
1788 END LOOP;
1789 END LOOP;
1790
1791 FORALL i IN segmentStart..segmentEnd
1792
1793 UPDATE cz_item_property_values SET
1794 orig_sys_ref = NVL(itemPropValues_origSysRef(orig_sys_ref),orig_sys_ref)
1795 WHERE item_id = tabCandidateItem(i)
1796 and deleted_flag='0';
1797
1798 COMMIT;
1799 segmentStart := segmentEnd + 1;
1800 END LOOP;
1801
1802 --Update CZ_ITEM_TYPE_PROPERTIES table.
1803
1804 localCount := tabCandidateType.COUNT;
1805 segmentStart := 1;
1806
1807 debug('Updating CZ_ITEM_TYPE_PROPERTIES table, ' || localCount || ' update candidate records, time started: ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
1808
1809 WHILE(segmentStart <= localCount)LOOP
1810
1811 segmentEnd := segmentStart + CommitBlockSize - 1;
1812 IF(segmentEnd > localCount)THEN segmentEnd := localCount; END IF;
1813 itemTypePropRollback := segmentEnd;
1814
1815 for i IN segmentStart..segmentEnd LOOP
1816 select ITEM_TYPE_ID,PROPERTY_ID,ORIG_SYS_REF
1817 BULK COLLECT INTO l_tabRbItmTypPropItTypeId,l_tabRbItmTypPropId,l_tabRbItmTypPropOrigSysRef
1818 FROM cz_item_type_properties where item_type_id = tabCandidateType(i)
1819 and deleted_flag='0';
1820
1821 loopCount:=tabRbItmTypPropItTypeId.count;
1822 for j in 1..l_tabRbItmTypPropItTypeId.COUNT
1823 LOOP
1824
1825 loopCount:=loopCount+1;
1826 tabRbItmTypPropItTypeId(loopCount):=l_tabRbItmTypPropItTypeId(j);
1827 tabRbItmTypPropId(loopCount):=l_tabRbItmTypPropId(j);
1828 tabRbItmTypPropOrigSysRef(loopCount):=l_tabRbItmTypPropOrigSysRef(j);
1829
1830 END LOOP;
1831 END LOOP;
1832
1833 FORALL i IN segmentStart..segmentEnd
1834 UPDATE CZ_ITEM_TYPE_PROPERTIES SET
1835 orig_sys_ref = NVL(itemTypeProp_origSysRef(orig_sys_ref),orig_sys_ref)
1836 WHERE item_type_id = tabCandidateType(i)
1837 and deleted_flag='0';
1838
1839 COMMIT;
1840 segmentStart := segmentEnd + 1;
1841 END LOOP;
1842
1843 EXCEPTION
1844 WHEN OTHERS THEN
1845 rollback_structure;
1846 RAISE;
1847 END; --synchronize_structure;
1848 ---------------------------------------------------------------------------------------
1849 BEGIN --build_structure_map
1850
1851 p_error_flag := ERROR_FLAG_SUCCESS;
1852
1853 --Take care of the run_id.
1854
1855 IF(p_run_id IS NULL)THEN
1856 SELECT cz_xfr_run_infos_s.NEXTVAL INTO p_run_id FROM DUAL;
1857 END IF;
1858
1859 --Read the version of the software and log the header message.
1860 --We do not want to stop just because an error occurs at this point, so we use the
1861 --unconditional exception handler. As the only known problem that may occur inside
1862 --this exception block is obviously a server bug (substr(substr(,),) does not work
1863 --when selecting from user_source) we are not logging any messages.
1864
1865 --Bug #4865406. There is no need to query against user_source.
1866
1867 BEGIN
1868
1869 thisVersionString := SUBSTR(SUBSTR(GenHeader, INSTR(GenHeader, THIS_FILE_NAME) + LENGTH(THIS_FILE_NAME) + 1), 1,
1870 INSTR(SUBSTR(GenHeader, INSTR(GenHeader, THIS_FILE_NAME) + LENGTH(THIS_FILE_NAME) + 1), ' ') - 1);
1871
1872 --'Synchronization software version %VERSION started %DATETIME, session run ID: %RUNID'
1873 report(CZ_UTILS.GET_TEXT('CZ_SYNC_VERSION_INFO', 'VERSION', thisVersionString, 'DATETIME',
1877 WHEN OTHERS THEN
1874 TO_CHAR(SYSDATE, THIS_DATE_FORMAT),'RUNID', p_run_id), URGENCY_MESSAGE);
1875
1876 EXCEPTION
1878 NULL;
1879 END;
1880
1881 --Read the database settings.
1882
1883 BEGIN
1884
1885 --Get the flag determining whether to verify item properties, default - yes.
1886
1887 SELECT DECODE(UPPER(value), '1', 1, 'ON', 1, 'Y', 1, 'YES', 1,'TRUE', 1, 'ENABLE', 1,
1888 '0', 0, 'OFF', 0, 'N', 0, 'NO', 0,'FALSE', 0, 'DISABLE', 0,
1889 1) --default value
1890 INTO VerifyItemProperties
1891 FROM cz_db_settings
1892 WHERE UPPER(setting_id) = VERIFY_PROPERTIES_SETTING_ID
1893 AND UPPER(section_name) = DBSETTINGS_SECTION_NAME;
1894
1895 EXCEPTION
1896 WHEN OTHERS THEN
1897 VerifyItemProperties := 1; --enforce the default value
1898 END;
1899
1900 BEGIN
1901
1902 --Get the number of days from now after which a date is considered to be the epoch end date.
1903
1904 SELECT TO_NUMBER(value) INTO DaysTillEpochEnd
1905 FROM cz_db_settings
1906 WHERE UPPER(setting_id) = DAYSTILLEPOCHEND_SETTING_ID
1907 AND UPPER(section_name) = DBSETTINGS_SECTION_NAME;
1908
1909 EXCEPTION
1910 WHEN OTHERS THEN
1911 DaysTillEpochEnd := DEFAULT_DAYSTILLEPOCHEND; --enforce the default value
1912 END;
1913
1914 --Calculate the date after which any date becomes the epoch end date.
1915
1916 EpochEndLine := SYSDATE + DaysTillEpochEnd;
1917 IF(EpochEndLine > EpochEndDate)THEN EpochEndLine := EpochEndDate; END IF;
1918
1919 --Read the base/installed languages information.
1920
1921 BEGIN
1922
1923 SELECT language_code INTO baseLanguageCode
1924 FROM fnd_languages
1925 WHERE installed_flag = FND_LANGUAGES_BASE;
1926
1927 SELECT count(*) + 1 INTO numberOfLanguages
1928 FROM fnd_languages
1929 WHERE installed_flag = FND_LANGUAGES_INSTALLED;
1930
1931 EXCEPTION
1932 WHEN OTHERS THEN
1933 --'Language information is not available'
1934 report(CZ_UTILS.GET_TEXT('CZ_SYNC_NO_LANGUAGE_INFO'), URGENCY_WARNING);
1935 END;
1936
1937 clear_structure_maps;
1938 verify_target_instance;
1939
1940 --Cash the item types data. Consider only imported item types and make sure orig_sys_ref
1941 --can be resolved to an integer.
1942
1943 BEGIN
1944
1945 SELECT item_type_id, name, orig_sys_ref
1946 BULK COLLECT INTO tabItemTypeId, tabItemTypeName, tabItemTypeOrigSysRef
1947 FROM cz_item_types
1948 WHERE deleted_flag = FLAG_NOT_DELETED
1949 AND orig_sys_ref IS NOT NULL
1950 AND REPLACE(TRANSLATE(orig_sys_ref, '0123456789', '0000000000'), '0', NULL) IS NULL;
1951
1952 FOR i IN 1..tabItemTypeId.COUNT LOOP
1953
1954 hashItemTypeId(tabItemTypeId(i)) := TO_NUMBER(tabItemTypeOrigSysRef(i));
1955 hashItemTypeName(tabItemTypeId(i)) := tabItemTypeName(i);
1956 END LOOP;
1957
1958 EXCEPTION
1959 WHEN OTHERS THEN
1960 --'Error while reading item types: %ERRORTEXT'
1961 report(CZ_UTILS.GET_TEXT('CZ_SYNC_NO_ITEM_TYPES', 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
1962 END;
1963
1964 IF(p_model_id IS NOT NULL)THEN
1965
1966 --Verify the single model specified.
1967
1968 execute_model(p_model_id);
1969
1970 ELSE
1971
1972 verify_source_instance;
1973
1974 --Verify all the eligible models. All the repository models are eligible anyway.
1975
1976 FOR c_model IN (SELECT object_id FROM cz_rp_entries
1977 WHERE deleted_flag = FLAG_NOT_DELETED
1978 AND object_type = REPOSITORY_TYPE_PROJECT) LOOP
1979
1980 execute_model(c_model.object_id);
1981 END LOOP;
1982
1983 --If we will be synchronizing to the local instance, we also need to verify all
1984 --the models created by publishing.
1985
1986 IF(g_target_instance = LOCAL_SERVER_SEED_ID)THEN
1987
1988 FOR c_model IN (SELECT model_id FROM cz_model_publications
1989 WHERE deleted_flag = FLAG_NOT_DELETED
1990 AND source_target_flag = PUBLICATION_TARGET_FLAG
1991 AND export_status = PUBLICATION_STATUS_OK) LOOP
1992
1993 execute_model(c_model.model_id);
1994 END LOOP;
1995 END IF;
1996 END IF;
1997
1998 --Synchronize if called in the synchronization mode.
1999
2000 IF(p_execution_mode = EXECUTION_MODE_SYNC)THEN
2001
2002 synchronize_structure;
2003
2004 --Mark deleted all the imported items that are not referenced from the product structure,
2005 --because these items have not been synchronize and may cause problems for consequtive
2006 --BOM import.
2007 --Bug #3634107.
2008
2009 UPDATE cz_item_masters item SET deleted_flag = '1'
2010 WHERE deleted_flag = FLAG_NOT_DELETED
2011 AND src_application_id = 401
2012 AND NOT EXISTS
2013 (SELECT NULL FROM cz_ps_nodes
2014 WHERE deleted_flag = FLAG_NOT_DELETED
2015 AND item_id = item.item_id);
2016
2017 COMMIT;
2018 END IF;
2019
2020 --Here we handle all the user-defined fatal exception that terminate the execution and
2021 --all other exceptions that may occur.
2022
2023 EXCEPTION
2024 WHEN CZ_SYNC_NORMAL_EXCEPTION THEN
2025 --This exception is used when the program needs to terminate immediately without error.
2026 --An appropriate message has already been logged, so just set the flag.
2027 p_error_flag := ERROR_FLAG_SUCCESS;
2028 WHEN CZ_SYNC_GENERAL_EXCEPTION THEN
2029 --The error has already been logged because this exception is raised from anywhere
2033 p_error_flag := ERROR_FLAG_ERROR;
2030 --in the code in order to terminate the execution. So, just set the flag.
2031 p_error_flag := ERROR_FLAG_ERROR;
2032 WHEN OTHERS THEN
2034 --'Unable to continue because of %ERRORTEXT'
2035 report_on_exit(CZ_UTILS.GET_TEXT('CZ_G_GENERAL_ERROR', 'ERRORTEXT', SQLERRM));
2036 END; --build_structure_map
2037 ---------------------------------------------------------------------------------------
2038 ---------------------------------------------------------------------------------------
2039 ---------------------------------------------------------------------------------------
2040 FUNCTION psnode_origSysRef(p_orig_sys_ref IN VARCHAR2)
2041 RETURN VARCHAR2
2042 IS
2043 v_str VARCHAR2(1200) := p_orig_sys_ref;
2044 v_comp_code VARCHAR2(1000);
2045 v_expl_type VARCHAR2(20);
2046 v_org_id VARCHAR2(20);
2047 v_item_id VARCHAR2(20);
2048 v_delim_pos INTEGER;
2049 v_ret_val VARCHAR2(1200);
2050
2051 BEGIN
2052 -- format of cz_ps_nodes.orig_sys_ref: comp_code:expl_type:org_id:top_item_id
2053 IF (p_orig_sys_ref IS NOT NULL) THEN
2054 v_delim_pos := instr(v_str, ':', -1);
2055 v_item_id := TO_CHAR(component_item_id_map(TO_NUMBER(substr(v_str, v_delim_pos + 1))));
2056 v_str := substr(v_str, 1, v_delim_pos - 1);
2057
2058 v_delim_pos := instr(v_str, ':', -1);
2059 v_org_id := TO_CHAR(organization_id_map(TO_NUMBER(substr(v_str, v_delim_pos + 1))));
2060 v_str := substr(v_str, 1, v_delim_pos - 1);
2061
2062 v_delim_pos := instr(v_str, ':');
2063 IF (v_delim_pos = 0) THEN
2064 v_ret_val := v_str || ':' || v_org_id || ':' || v_item_id;
2065 ELSE
2066 v_expl_type := substr(v_str, v_delim_pos);
2067 v_str := substr(v_str, 1, v_delim_pos - 1);
2068
2069 v_delim_pos := instr(v_str, '-');
2070 WHILE (v_delim_pos <> 0) LOOP
2071 v_comp_code := v_comp_code || '-' || TO_CHAR(component_item_id_map(TO_NUMBER(substr(v_str, 1, v_delim_pos - 1))));
2072 v_str := substr(v_str, v_delim_pos + 1);
2073 v_delim_pos := instr(v_str, '-');
2074 END LOOP;
2075 v_comp_code := v_comp_code || '-' || TO_CHAR(component_item_id_map(TO_NUMBER(v_str)));
2076 v_ret_val := substr(v_comp_code, 2) || v_expl_type || ':' || v_org_id || ':' || v_item_id;
2077 END IF;
2078 END IF;
2079
2080 RETURN v_ret_val;
2081
2082 END psnode_origSysRef;
2083
2084 ---------------------------------------------------------------------------------------
2085 FUNCTION psnode_compSeqPath(p_component_seq_path IN VARCHAR2)
2086 RETURN VARCHAR2
2087 IS
2088 v_str VARCHAR2(2000) := p_component_seq_path;
2089 v_delim_pos INTEGER;
2090 v_ret_val VARCHAR2(2000);
2091
2092 BEGIN
2093 IF (p_component_seq_path IS NOT NULL) THEN
2094 v_delim_pos := instr(v_str, '-');
2095 WHILE (v_delim_pos <> 0) LOOP
2096 v_ret_val := v_ret_val || '-' || TO_CHAR(component_seq_id_map(TO_NUMBER(substr(v_str, 1, v_delim_pos - 1))));
2097 v_str := substr(v_str, v_delim_pos + 1);
2098 v_delim_pos := instr(v_str, '-');
2099 END LOOP;
2100
2101 v_ret_val := v_ret_val || '-' || TO_CHAR(component_seq_id_map(TO_NUMBER(v_str)));
2102 RETURN substr(v_ret_val, 2);
2103 END IF;
2104
2105 RETURN NULL;
2106 END psnode_compSeqPath;
2107
2108 ---------------------------------------------------------------------------------------
2109 FUNCTION psnode_compSeqId(p_component_id IN NUMBER)
2110 RETURN NUMBER
2111 IS
2112
2113 BEGIN
2114 IF (p_component_id IS NOT NULL) THEN
2115 RETURN component_seq_id_map(p_component_id);
2116 END IF;
2117
2118 RETURN NULL;
2119 END psnode_compSeqId;
2120
2121 ---------------------------------------------------------------------------------------
2122 FUNCTION itemMaster_origSysRef(p_orig_sys_ref IN VARCHAR2)
2123 RETURN VARCHAR2
2124 IS
2125 v_delim_pos INTEGER;
2126 v_item_id VARCHAR2(20);
2127 v_org_id VARCHAR2(20);
2128
2129 BEGIN
2130 --format of cz_item_mstater.orig_sys_ref: inv_item_id:org_id
2131 IF (p_orig_sys_ref IS NOT NULL) THEN
2132 v_delim_pos := instr(p_orig_sys_ref, ':');
2133 v_item_id := TO_CHAR(component_item_id_map(TO_NUMBER(substr(p_orig_sys_ref, 1, v_delim_pos - 1))));
2134 v_org_id := TO_CHAR(organization_id_map(TO_NUMBER(substr(p_orig_sys_ref, v_delim_pos + 1))));
2135 RETURN (v_item_id || ':' || v_org_id);
2136 END IF;
2137
2138 RETURN NULL;
2139 END itemMaster_origSysRef;
2140
2141 ---------------------------------------------------------------------------------------
2142 FUNCTION itemPropValues_origSysRef(p_orig_sys_ref IN VARCHAR2)
2143 RETURN VARCHAR2
2144 IS
2145 v_inv_id_pos INTEGER;
2146 v_org_id_pos INTEGER;
2147 v_item_id VARCHAR2(20);
2148 v_org_id VARCHAR2(20);
2149
2150 BEGIN
2151 --format of cz_item_property_values.orig_sys_ref: inv_item_id:property_id
2152 IF (p_orig_sys_ref IS NOT NULL) THEN
2153 v_inv_id_pos := instr(p_orig_sys_ref, ':',1,1);
2154 v_org_id_pos := instr(p_orig_sys_ref, ':',1,2);
2155 v_item_id := TO_CHAR(component_item_id_map(TO_NUMBER(substr(p_orig_sys_ref, 1, v_inv_id_pos - 1))));
2156 v_org_id := TO_CHAR(organization_id_map(TO_NUMBER(substr(p_orig_sys_ref,v_inv_id_pos+1,v_org_id_pos-(v_inv_id_pos+1)))));
2157 RETURN (v_item_id || ':' || v_org_id||substr(p_orig_sys_ref,v_org_id_pos));
2158 END IF;
2159
2160 RETURN NULL;
2161 END itemPropValues_origSysRef;
2162
2163 FUNCTION itemTypeProp_origSysRef(p_orig_sys_ref IN VARCHAR2)
2164 RETURN VARCHAR2
2165 IS
2166 v_inv_id_pos INTEGER;
2167 v_catalog_grp_id VARCHAR2(20);
2168
2169 BEGIN
2170 --format of CZ_ITEM_TYPE_PROPERTIES.orig_sys_ref: catalog_group_id:..
2174 RETURN (v_catalog_grp_id ||substr(p_orig_sys_ref,v_inv_id_pos));
2171 IF (p_orig_sys_ref IS NOT NULL) THEN
2172 v_inv_id_pos := instr(p_orig_sys_ref, ':',1,1);
2173 v_catalog_grp_id := TO_CHAR(catalog_group_id_map(TO_NUMBER(substr(p_orig_sys_ref, 1, v_inv_id_pos - 1))));
2175 END IF;
2176
2177 RETURN NULL;
2178 END itemTypeProp_origSysRef;
2179
2180 ---------------------------------------------------------------------------------------
2181 FUNCTION itemType_origSysRef(p_orig_sys_ref IN VARCHAR2)
2182 RETURN VARCHAR2
2183 IS
2184
2185 BEGIN
2186 IF (p_orig_sys_ref IS NOT NULL) THEN
2187 RETURN TO_CHAR(catalog_group_id_map(TO_NUMBER(p_orig_sys_ref)));
2188 END IF;
2189
2190 RETURN NULL;
2191 END itemType_origSysRef;
2192
2193 ---------------------------------------------------------------------------------------
2194 FUNCTION devlProject_origSysRef(p_orig_sys_ref IN VARCHAR2)
2195 RETURN VARCHAR2
2196 IS
2197 v_str VARCHAR2(255) := p_orig_sys_ref;
2198 v_delim_pos INTEGER;
2199 v_item_id VARCHAR2(20);
2200 v_org_id VARCHAR2(20);
2201 v_expl_type VARCHAR2(20);
2202
2203 BEGIN
2204 -- format of cz_devl_projects.orig_sys_ref: expl_type:org_id:top_item_id
2205 IF (p_orig_sys_ref IS NOT NULL) THEN
2206 v_delim_pos := instr(v_str, ':');
2207 v_expl_type := substr(v_str, 1, v_delim_pos);
2208 v_str := substr(v_str, v_delim_pos + 1);
2209
2210 v_delim_pos := instr(v_str, ':');
2211 v_org_id := TO_CHAR(organization_id_map(TO_NUMBER(substr(v_str, 1, v_delim_pos - 1))));
2212 v_item_id := TO_CHAR(component_item_id_map(TO_NUMBER(substr(v_str, v_delim_pos + 1))));
2213
2214 RETURN (v_expl_type || v_org_id || ':' || v_item_id);
2215 END IF;
2216
2217 RETURN NULL;
2218 END devlProject_origSysRef;
2219
2220 ---------------------------------------------------------------------------------------
2221 FUNCTION locText_origSysRef(p_orig_sys_ref IN VARCHAR2)
2222 RETURN VARCHAR2
2223 IS
2224 v_delim_pos INTEGER;
2225 v_str VARCHAR2(255) := p_orig_sys_ref;
2226 v_item_id VARCHAR2(20);
2227 v_org_id VARCHAR2(20);
2228 v_compSeqId VARCHAR2(20);
2229 v_expl_type VARCHAR2(20);
2230
2231 BEGIN
2232 -- format of cz_localized_texts.orig_sys_ref: item_id:expl_type:org_id:comp_seq_id
2233 IF (p_orig_sys_ref IS NOT NULL) THEN
2234 v_delim_pos := instr(v_str, ':');
2235 v_item_id := TO_CHAR(component_item_id_map(TO_NUMBER(substr(v_str, 1, v_delim_pos - 1))));
2236 v_str := substr(v_str, v_delim_pos);
2237
2238 v_delim_pos := instr(v_str, ':', 2);
2239 v_expl_type := substr(v_str, 1, v_delim_pos);
2240 v_str := substr(v_str, v_delim_pos+1);
2241 v_delim_pos := instr(v_str, ':', -1);
2242 v_org_id := TO_CHAR(organization_id_map(TO_NUMBER(substr(v_str,1, v_delim_pos -1))));
2243 v_compSeqId := TO_CHAR(component_seq_id_map(TO_NUMBER(substr(v_str, v_delim_pos+1))));
2244 RETURN (v_item_id || v_expl_type || v_org_id || ':' || v_compSeqId);
2245 END IF;
2246 RETURN NULL;
2247 EXCEPTION WHEN OTHERS THEN
2248 RETURN NULL;
2249 END locText_origSysRef;
2250
2251 ---------------------------------------------------------------------------------------
2252 FUNCTION projectBill_orgId(p_organization_id IN NUMBER)
2253 RETURN NUMBER
2254 IS
2255
2256 BEGIN
2257 IF (p_organization_id IS NOT NULL) THEN
2258 RETURN organization_id_map(p_organization_id);
2259 END IF;
2260
2261 RETURN NULL;
2262 END projectBill_orgId;
2263
2264 ---------------------------------------------------------------------------------------
2265 FUNCTION projectBill_topItemId(p_top_item_id IN NUMBER)
2266 RETURN NUMBER
2267 IS
2268
2269 BEGIN
2270 IF (p_top_item_id IS NOT NULL) THEN
2271 RETURN component_item_id_map(p_top_item_id);
2272 END IF;
2273
2274 RETURN NULL;
2275 END projectBill_topItemId;
2276
2277 ---------------------------------------------------------------------------------------
2278 FUNCTION projectBill_compItemId(p_component_item_id IN NUMBER)
2279 RETURN NUMBER
2280 IS
2281
2282 BEGIN
2283 IF (p_component_item_id IS NOT NULL) THEN
2284 RETURN component_item_id_map(p_component_item_id);
2285 END IF;
2286
2287 RETURN NULL;
2288 END projectBill_compItemId;
2289
2290 ---------------------------------------------------------------------------------------
2291 FUNCTION projectBill_sourceServer(p_server_id IN NUMBER)
2292 RETURN NUMBER
2293 IS
2294
2295 BEGIN
2296 RETURN g_target_instance;
2297 END projectBill_sourceServer;
2298
2299 ---------------------------------------------------------------------------------------
2300 FUNCTION modelPublication_productKey(p_product_key IN VARCHAR2)
2301 RETURN VARCHAR2
2302 IS
2303 v_delim_pos INTEGER;
2304 v_item_id VARCHAR2(20);
2305 v_org_id VARCHAR2(20);
2306
2307 BEGIN
2308 --format of cz_model_publications.product_key: org_id:inv_item_id
2309 IF (p_product_key IS NOT NULL) THEN
2310 v_delim_pos := instr(p_product_key, ':');
2311 v_org_id := TO_CHAR(organization_id_map(TO_NUMBER(substr(p_product_key, 1, v_delim_pos - 1))));
2312 v_item_id := TO_CHAR(component_item_id_map(TO_NUMBER(substr(p_product_key, v_delim_pos + 1))));
2313 RETURN (v_org_id || ':' || v_item_id);
2314 END IF;
2315
2316 RETURN NULL;
2317 END modelPublication_productKey;
2318
2319 ---------------------------------------------------------------------------------------
2320 FUNCTION modelPublication_topItemId(p_top_item_id IN NUMBER)
2321 RETURN NUMBER
2322 IS
2323
2324 BEGIN
2325 IF (p_top_item_id IS NOT NULL) THEN
2326 RETURN component_item_id_map(p_top_item_id);
2327 END IF;
2328
2329 RETURN NULL;
2330 END modelPublication_topItemId;
2331
2332 ---------------------------------------------------------------------------------------
2333 FUNCTION modelPublication_orgId(p_organization_id IN NUMBER)
2334 RETURN NUMBER
2335 IS
2336
2337 BEGIN
2338 IF (p_organization_id IS NOT NULL) THEN
2339 RETURN organization_id_map(p_organization_id);
2340 END IF;
2341
2342 RETURN NULL;
2343 END modelPublication_orgId;
2344
2345 ---------------------------------------------------------------------------------------
2346 FUNCTION devlProject_invId(p_inventory_item_id IN NUMBER)
2347 RETURN NUMBER
2348 IS
2349
2350 BEGIN
2351 IF (p_inventory_item_id IS NOT NULL) THEN
2352 RETURN component_item_id_map(p_inventory_item_id);
2353 END IF;
2354
2355 RETURN NULL;
2356 END devlProject_invId;
2357
2358 ----------------------------
2359 FUNCTION devlProject_orgId(p_organization_id IN NUMBER)
2360 RETURN NUMBER
2361 IS
2362
2363 BEGIN
2364 IF (p_organization_id IS NOT NULL) THEN
2365 RETURN organization_id_map(p_organization_id);
2366 END IF;
2367
2368 RETURN NULL;
2369 END devlProject_orgId;
2370
2371 ----------------------------
2372 FUNCTION devlProject_productKey(p_product_key IN VARCHAR2)
2373 RETURN VARCHAR2
2374 IS
2375 v_delim_pos INTEGER;
2376 v_item_id VARCHAR2(20);
2377 v_org_id VARCHAR2(20);
2378
2379 BEGIN
2380 --format of cz_model_publications.product_key: org_id:inv_item_id
2381 IF (p_product_key IS NOT NULL) THEN
2382 v_delim_pos := instr(p_product_key, ':');
2383 v_org_id := TO_CHAR(organization_id_map(TO_NUMBER(substr(p_product_key, 1, v_delim_pos - 1))));
2384 v_item_id := TO_CHAR(component_item_id_map(TO_NUMBER(substr(p_product_key, v_delim_pos + 1))));
2385 RETURN (v_org_id || ':' || v_item_id);
2386 END IF;
2387
2388 RETURN NULL;
2389 END devlProject_productKey;
2390
2391 -----------------------------------------------------------------
2392 END CZ_BOM_SYNCH;
[Home] [Help]