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