Stored Proc For Generating Surrogate Keys

Exec Stored Proc With Parameters
Oracle Stored Proc
Stored Proc For Generating Surrogate Keys In Windows 10

Jun 24, 2012 It is made the primary key of the table and is used to join a dimension to a fact table. Among other benefits, surrogate keys allow you to maintain history in a dimension table. Despite of the their popularity, SSIS doesn’t have a built in solution for generating surrogate keys. Let’s take a look at a few alternatives in this post.

Temporary tables created statically within the stored procedure do not cause this behavior. That is, if two sessions invoke outersp there be will be one entry each in the cache of innersp for the two sessions. Say now that you have a busy system where there are thousand sessions that invoke outersp. Mar 19, 2012 The surrogate keys can't be re-generated using the stored procedure because the process could generate new surrogate keys different from the original ones because of the uniqueness value. The surrogate keys need to be copied from the target tables into the key table or restored from the latest backup. Feb 06, 2014 The SEQUENCE statement introduced in SQL Server 2012 brings the ANSI SQL 2003 standard method of generating IDs. This is a great relief to database professionals as it solves some problems what are awkward to solve with the IDENTITY property. Joe Celko explains the basics of using a. These include both foreign surrogate keys and surrogate primary keys. All CZDBSETTINGS values are stored as VARCHAR2(255) in the VALUE field. Set to YES forces the optimizer to update the internal statistics on the entire CZ schema before running queries in the CZ schema. Generating statistics allows the optimizer to choose a better.

-->

APPLIES TO: SQL Server 2016 and later Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

You can define a primary key in SQL Server by using SQL Server Management Studio or Transact-SQL. Creating a primary key automatically creates a corresponding unique clustered index, or a nonclustered index if specified as such.

Before You Begin

Limitations and Restrictions

A table can contain only one PRIMARY KEY constraint.
All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. If nullability is not specified, all columns participating in a PRIMARY KEY constraint have their nullability set to NOT NULL.

Security

Permissions

Creating a new table with a primary key requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.

Creating a primary key in an existing table requires ALTER permission on the table.

Using SQL Server Management Studio

To create a primary key

In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design.
In Table Designer, click the row selector for the database column you want to define as the primary key. If you want to select multiple columns, hold down the CTRL key while you click the row selectors for the other columns.
Right-click the row selector for the column and select Set Primary Key.

Caution

If you want to redefine the primary key, any relationships to the existing primary key must be deleted before the new primary key can be created. A message will warn you that existing relationships will be automatically deleted as part of this process.

A primary key column is identified by a primary key symbol in its row selector.

If a primary key consists of more than one column, duplicate values are allowed in one column, but each combination of values from all the columns in the primary key must be unique.

If you define a compound key, the order of columns in the primary key matches the order of columns as shown in the table. However, you can change the order of columns after the primary key is created. For more information, see Modify Primary Keys.

Using Transact-SQL

To create a primary key in an existing table

The following example creates a primary key on the column TransactionID in the AdventureWorks database.

To create a primary key in a new table

The following example creates a table and defines a primary key on the column TransactionID in the AdventureWorks database.

To create a primary key with clustered index in a new table

The following example creates a table and defines a primary key on the column CustomerID and a clustered index on TransactionID in the AdventureWorks database.

Overview

This chapter describes the basic characteristics of the CZ schema, the schema settings and how they are used, and provides some schema maintenance tips:

Characteristics of the Oracle CZ Schema

For a description of the CZ schema, see Oracle CZ Schema.

Online Tables and Integration Tables

The CZ schema contains online and integration tables. The online and integration tables are organized into subschemas for storing the data of configuration models and saved configurations.

The online tables contain the data that is used by Oracle Configurator Developer and the runtime Oracle Configurator. Every online table that receives imported data has a corresponding import table. For example, CZ_ITEM_TYPES is populated with data from the CZ_IMP_ITEM_TYPE table during the import process. See CZ Subschemas for more information about the CZ subschemas.

The integration tables consist of import and control tables. See Import Tables for information about the import tables and Control Tables for information about control tables. See Populating the CZ Schema for information about using the integration tables.

CZ Subschemas

Both the online and integration tables of the CZ schema are organized into subschemas:

ADMN - Administrative
CNFG - Saved Configurations
ITEM - Item Master
LCE - Logic for Configuration (Generate Logic)
PB - Publication
PROJ - Project Structure
RP - Repository
RULE - Rule
TXT - Text
TYP - Data Typing
UI - User Interface
XFR - Transfer specifications and control

Additionally, there are some key table views:

CZ_CONFIG_DETAILS_V stores selected BOM Model node records.
CZ_CONFIG_ITEMS_V stores all selected node records for both BOM Models and Oracle Developer Models

See CZ Subschemas for a listing of tables in each subschema. For table details, see the CZ eTRM on MetaLink, Oracle’s technical support Web site.

Public Synonyms

The CZ schema does not use public synonyms.

Schema Customization

Customizing the data model of the CZ schema is not recommended, because such customizations may not be preserved during an upgrade or migration.

Various user expansion fields in the CZ schema, such as USERNUMn and USERSTRn in the CZ_PS_NODES table, are available for custom use. The data in the user expansion fields is preserved during a schema upgrade or migration. For more information, see the CZ eTRM on MetaLink, Oracle’s technical support Web site.

Import Tables

Every import table corresponds to an online table both structurally and relationally. Each import table contains the same fields as the corresponding online table, as well as additional fields to manage the import and correlate the data with the existing data in the online table.

Import tables consist of:

Because import tables are meant to capture as much data as possible, all fields are nullable and there are no integrity constraints such as primary-key definitions, unique indexes, or foreign-key references. The import tables allow batch population of the CZ schema’s online tables.

Each import table’s name is similar to its online counterpart. Import tables have CZ_IMP prefix instead of just CZ_. For example, the imported data in CZ_IMP_PROPERTY populates CZ_PROPERTIES, and CZ_IMP_ITEM_TYPE populates CZ_ITEM_TYPES.

The import tables temporarily store extracted or legacy data that concurrent programs access when creating, updating, or deleting records in the CZ schema. The CZ_IMP tables are populated by running the Populate or Refresh Configuration Models concurrent programs. For more information see Populate and Refresh Configuration Models Concurrent Programs.

For more information about:

How data moves from sources outside the CZ schema through the import tables to the online tables, see Populating the CZ Schema
Dependencies among import tables and import table codes, see Dependencies Among Import Tables.
You could also burn ISO image file to disc freely. Burn Blu-ray/DVD with 180+ Formats SupportedAs an all-inclusive Blu-ray DVD burner software combo, Leawo Blu-ray Creator could burn video to Blu-ray/DVD, and burn Blu-ray/DVD photo slideshow from 180+ file formats, including: MP4, AVI, MKV, FLV, MOV, WMV, MPEG, VOB, MTS, JPG, BMP, etc. It could handle Blu-ray/DVD burning tasks like AVI to Blu-ray/DVD, MKV to Blu-ray, MP4 to Blu-ray/DVD, and others at the same time. You could burn video to Blu-ray/DVD disc, folder and/or ISO image file as you like in BD50, BD25, DVD5 or DVD9. Leawo dvd creator 5.2.0.0 key generator reviews. It enables you to burn video to Blu-ray/DVD from various origins like camcorder video, smartphone recorded footage, downloaded online videos, saved screen activities, etc.

Stored Proc For Generating Surrogate Keys

Import Control Fields

Import control fields contain data that is used to manage the import process for each record. Import control data is not transferred to the online tables and is not used to resolve key values or anything else. Import Control Fields describes the import control fields.

The following table shows the Import Control Fields including field name, data type, and description.

Import Control Fields
Field Name	Type	Description
RUN_ID	INTEGER	Input field that associates a record with an import run.
REC_NBR	INTEGER	Input field that is a one-up sequence number uniquely identifying each record within a RUN_ID.
DISPOSITION	CHAR(1)	Output field that indicates whether the record was inserted, modified, unchanged, or rejected after an import: I = Insert M = Modify N = No change R = Rejected Null indicates that the record’s disposition has not been determined. Importing rule data sets DISPOSITION in CZ_IMP_RULES and CZ_IMP_LOCALIZED_TEXTS. The success or failure of rule processing stages sets the DISPOSITION field accordingly: P = Passed R = Rejected During the key resolution stage of rule import (REC_STATUS=KRS), DISPOSITION can be: I = Rule is new in the database instance. M = Rule has previously been imported. For additional rule import information, see Rule Import.
REC_STATUS	VARCHAR(4)	Output field that indicates the record’s validation status: DUPL indicates the record is a duplicate. ERR indicates the record has not been modified or inserted into the target database table because of an error in the transfer stage. Fnnn indicates the nnn field is an invalid foreign-key reference. Nnnn indicates the required nnn field has null data. NULL indicates the record status is open. Once this status is set, further processing of the record is suppressed. OK indicates the data in the record now exists in the online database table. PASS indicates the record is marked for either modification or insertion after the key resolution stage. Importing rule data sets REC_STATUS in CZ_IMP_RULES and CZ_IMP_LOCALIZED_TEXTS. The rule processing stage is tracked in REC_STATUS. The following are the stages of processing rule data: CND indicates the first stage of processing rule data. This stage verifies that all required columns are populated and assigns default values for other columns. See Rule Validation for a list of the required columns. KRS indicates the second stage of processing rule data if the data passes the CND stage (DISPOSITION=P). The KRS (key resolution) stage verifies and resolves all foreign key relationships among tables that are involved in the import. XFR indicates the third stage of processing rule data. This stage transfers the rule data to the CZ online tables. OK indicates that the rule has been successfully imported. This is the final reporting stage. ERR indicates that the rule failed parsing. This is the final reporting stage. For additional rule import information, see Rule Import.

Online Data Fields

The import tables’ data fields exactly match the fields in the corresponding online table and are used to hold the data to be put into the online table.

Surrogate Key Fields

Surrogate key fields in the import tables hold the customer-provided extrinsic identifications for data to be imported. These include both foreign surrogate keys and surrogate primary keys.

Foreign Surrogate Key – A foreign surrogate key is a reference to a different table made through that table’s surrogate primary key rather than through the online table’s integer key value. A foreign surrogate key consists of one or more fields that resolve references from one import table to another. These keys are named FSK_table_refno_fldnum, where table is the name of the referenced table, refno is the number of the table-to-table reference, and fldnum is the position of the referenced surrogate-key field in the referenced import table. Note that refno is required to keep unique names for tables with multiple references to the same table, and generally, the fldnum is 1.

Surrogate Primary Key – As a rule, imported tables contain a single field named ORIG_SYS_REF, which is used to hold the external value that uniquely identifies each record. In some cases, however, the online CZ table has a primary key consisting entirely of references to other tables. In this case, the surrogate primary key actually consists of the foreign surrogate keys that correspond to the native foreign keys in the online table.

Dependencies Among Import Tables

Dependencies among import tables must be heeded especially when custom importing single tables. Dependencies Among CZ Schema Import Tables, 'Foreign Surrogate Key' lists the column in the import table whose value is dependent on the table listed in 'Depends on'. For example, the FSK_ITEMTYPE_1_1 column in CZ_IMP_ITEM_MASTER gets its value from CZ_IMP_ITEM_TYPE.NAME and helps in key resolution. FSK_ITEMTYPE_1_1 (default) is populated depending on the PK_USEEXPANSION indicator (0, 1, or 2) in CZ_XFR_TABLES. See Populating Import Tables for the order in which the CZ_IMP tables are populated.

Note: Oracle recommends that limited usage of FSK_***_EXT columns as these columns will eventually be desupported.

A strong dependency means a value is required to successfully import that record. If Default is YES, then there is a default value in that column and import succeeds even if the dependency is strong and no value is imported. The following Dependencies Among CZ Schema Import Tables lists the dependencies.

The following table shows the dependencies between the import tables.

Dependencies Among CZ Schema Import Tables
Import Table Name	Depends on	Foreign Surrogate Key	Type of dependency	Default
CZ_IMP_DEVL_PROJECT	CZ_IMP_INTL_TEXT.TEXT_STR	FSK_INTLTEXT_1_1	STRONG	NO
CZ_IMP_LOCALIZED_TEXTS	CZ_IMP_DEVL_PROJECT.ORIG_SYS_REF	FSK_DEVLPROJECT_1_1	STRONG	N/A
CZ_IMP_ITEM_MASTER	CZ_IMP_ITEM_TYPE.NAME	FSK_ITEMTYPE_1_1	STRONG	YES
CZ_IMP_ITEM_PROPERTY_VALUE	CZ_IMP_PROPERTY.NAME	FSK_PROPERTY_1_1	STRONG	NO
CZ_IMP_ITEM_PROPERTY_VALUE	CZ_IMP_ITEM_MASTER.REF_PART_NBR	FSK_ITEMMASTER_2_1	STRONG	NO
CZ_IMP_ITEM_TYPE	NO	NO	NO	NO
CZ_IMP_ITEM_TYPE_PROPERTY	CZ_IMP_ITEM_TYPE.NAME	FSK_ITEMTYPE_1_1	STRONG	NO
CZ_IMP_ITEM_TYPE_PROPERTY	CZ_IMP_PROPERTY.NAME	FSK_PROPERTY_2_1	STRONG	NO
CZ_IMP_PROPERTY	NO	NO	NO	NO
CZ_IMP_PS_NODES	CZ_IMP_INTL_TEXT.TEXT_STR	FSK_INTLTEXT_1_1	STRONG	NO
CZ_IMP_PS_NODES	CZ_IMP_ITEM_MASTER.ORIG_SYS_REF	FSK_ITEMMASTER_2_1	STRONG	NO
CZ_IMP_PS_NODES	CZ_IMP_PS_NODES.ORIG_SYS_REF	FSK_PSNODE_3_1	STRONG	N/A
CZ_IMP_PS_NODES	CZ_PS_NODES.PARENT_ID	FSK_PSNODE_4_1	STRONG	N/A
CZ_IMP_PS_NODES	CZ_IMP_DEVL_PROJECT.ORIG_SYS_REF	FSK_DEVLPROJECT_5_1	STRONG	NO
CZ_IMP_PS_NODES	CZ_MODEL_REF_EXPLS	FSK_EXPLNODE_1_1	STRONG	N/A
CZ_IMP_PS_NODES	CZ_PS_NODES.REFERENCE_ID	FSK_PSNODE_6_1	STRONG	NA/
CZ_IMP_PS_NODES	CZ_EFFECTIVITY_SETS.EFFECTIVITY_SET_ID	FSK_EFFSET_7_1	STRONG	N/A
CZ_IMP_PS_NODES	SRC_APPLICATION_ID	FSK_ITEMMASTER_2_2	STRONG	N/A
CZ_IMP_PS_NODES	CZ_IMP_DEVL_PROJECT.ORIG_SYS_REF	FSK_DEVLPROJECT_5_1	STRONG	N/A

Control Tables

The control tables provide the mechanism for controlling what data is imported or refreshed when populating the CZ schema import tables with data from outside sources. The control table names are prefixed with CZ_XFR.

When running Oracle Configurator Populate and Refresh Configuration Models Concurrent Programs, records in the CZ_XFR tables determine which import tables are enabled for import, what data is imported, and how the data is imported.

The following tables control the import process at the table and field level:

CZ_XFR_FIELDS
CZ_XFR_PROJECT_BILLS
CZ_XFR_TABLES

The following tables contain import information:

CZ_XFR_RUN_INFOS
CZ_XFR_RUN_RESULTS
CZ_XFR_STATUS_CODES

CZ_XFR_TABLES identifies the mapping of the import table to the online table, as well as the rules for importing data into the CZ schema.

CZ_XFR_FIELDS identifies the transfer rules for the fields that are transferred during the Populate or Refresh Configuration Models concurrent programs. Every field is updated during import or refresh, but the update can be retracted by using the NOUPDATE flag in the CZ_XFR_FIELDS table. If a field that is transferred does not have an entry in the CZ_XFR_FIELDS table, then that field is updated.

For example, setting the NOUPDATE flag to 1 in the CZ_XFR_FIELDS table for CZ_ITEM_MASTERS.DESC_TEXT, inhibits the updating of the Item Master description in CZ_ITEM_MASTERS.DESC_TEXT when a Model is refreshed. Setting a value in the CZ_XFR_FIELDS Table shows how to set the field in the CZ_XFR_FIELDS table so that changes made to the BOM Model’s Item description do not appear in Oracle Configurator Developer.

Setting a value in the CZ_XFR_FIELDS Table

CZ_DB_SETTINGS Table

The CZ_DB_SETTINGS table provides parameters that affect certain applications and CZ schema processes.

Only one CZ_DB_SETTINGS table exists in a CZ schema.

Accessing the CZ_DB_SETTINGS Table

A user’s responsibility determines whether they can view or edit the CZ_DB_SETTINGS table. A user must have the Oracle Configurator Administrator responsibility to edit the CZ_DB_SETTINGS table through concurrent programs. For more information, see View Configurator Parameters and Modify Configurator Parameters.

Organization of the CZ_DB_SETTINGS Table

The parameters in the CZ_DB_SETTINGS table are mapped to a particular section of the CZ schema. The particular section is identified in the SECTION_NAME field and contains relevant database parameters. The sections are:

IMPORT - Controls how BOM Model data is imported into the CZ schema
LogicGen - Governs how the Model’s logic is generated
ORAAPPS_INTEGRATE - Controls how Oracle Configurator integrates with other Oracle Applications
SCHEMA - Sets general parameters that control the CZ schema
UISERVER - Governs the behavior of the runtime Oracle Configurator user interface

Each parameter contains the following fields:

DATA_TYPE specifies the parameter’s datatype. All CZ_DB_SETTINGS values are stored as VARCHAR2(255) in the VALUE field. If the DATA_TYPE is an integer, then the Configurator converts the data in the VALUE field to an integer before using it. For example, the Batchsize default value is stored as string 10000, but Configurator interprets string 10000 as integer 10000.
SETTING_ID identifies the parameter.
VALUE is the parameter’s data. This value may be set during an installation or upgrade of the database instance. The Oracle Configurator Administrator can modify a value by running the Modify Configurator Parameters concurrent program.

CZ_DB_SETTINGS Parameters

Some of the CZ_DB_SETTINGS parameter values are predefined during an installation or upgrade of Oracle Configurator. The Oracle Configurator Administrator can modify the values of these parameters by running the Modify Configurator Parameters concurrent program. For information on running concurrent programs, see Running Configurator Concurrent Programs. For specific information on modifying the parameters in the CZ_DB_SETTINGS table, see Settings in CZ_DB_SETTINGS Table that lists the parameters in the CZ_DB_SETTINGS table that can be modified.

Settings in CZ_DB_SETTINGS Table
SETTING_ID	SECTION_NAME	DATA_TYPE	Default VALUE	More information in..
AltBatchValidateURL	ORAAPPS_INTEGRATE	string	n/a	AltBatchValidateURL
BadItemPropertyValue	IMPORT	T/F	F	BadItemPropertyValue
BatchSize	SCHEMA	string	10000	BatchSize
BOM_REVISION	ORAAPPS_INTEGRATE	string	n/a	BOM_REVISION
CommitSize	IMPORT	integer	500	CommitSize
DISPLAY_INSTANCE_NAME	UISERVER	string	n/a	DISPLAY_INSTANCE_NAME
FREEZE_REVISION	SCHEMA	string	System setting	FREEZE_REVISION
GenerateGatedCombo	LogicGen	YES/NO	YES	GenerateGatedCombo
GenerateUpdatedOnly	LogicGen	YES/NO	YES	GenerateUpdatedOnly
GenStatisticsBOM	IMPORT	YES/NO	NO	GenStatisticsBOM
GenStatisticsCZ	IMPORT	YES/NO	NO	GenStatisticsCZ
MAJOR_VERSION	SCHEMA	integer	System setting	MAJOR_VERSION
MaximumErrors	IMPORT	integer	10000	MaximumErrors
MemoryBulkSize	IMPORT	integer	50000	MemoryBulkSize
MINOR_VERSION	SCHEMA	string	System setting	MINOR_VERSION
MULTISESSION	IMPORT	integer	0	MULTISESSION
OracleSequenceIncr	SCHEMA	integer	20	OracleSequenceIncr
PsNodeName	ORAAPPS_INTEGRATE	string	RefPartNbr	PsNodeName
PublicationLogging	ORAAPPS_INTEGRATE	YES/NO	NO	PublicationLogging
PublishingCopyRules	ORAAPPS_INTEGRATE	YES/NO	YES	PublishingCopyRules
PublicationLocalBOMSynch	PUBLICATION	YES/NO	NO	PublicationLocalBOMSynch
PurgeDeleteConfigBatchsize	SCHEMA	integer	100	PurgeDeleteConfigBatchsize
RefPartNbr	ORAAPPS_INTEGRATE	string	CONCATENATED_SEGMENTS	RefPartNbr
ResolvePropertyDataType	ORAAPPS_INTEGRATE	YES/NO	1-integer, 2-decimal, 3-boolean, 4-text	ResolvePropertyDataType
RestoredConfigDefaultModelLookupDate	ORAAPPS_INTEGRATE	string	config_creation_date	RestoredConfigDefaultModelLookupDate
Revision Date/User	SCHEMA	any string	-	Revision Date and User
RUN_BILL_EXPLODER	ORAAPPS_INTEGRATE	YES/NO	YES	RUN_BILL_EXPLODER
SuppressSuccessMessage	UISERVER	YES/NO	NO	SuppressSuccessMessage
TimeImport	IMPORT	string	TimeImport
UI_NODE_NAME_CONCAT_CHARS	ORAAPPS_INTEGRATE	string	n/a	UI_NODE_NAME_CONCAT_CHARS
UseLocalTableInExtractionViews	IMPORT	YES/NO	NO	UseLocalTableInExtractionViews
UtlHttpTransferTimeout	SCHEMA	integer	n/a	UtlHttpTransferTimeout

AltBatchValidateURL

AltBatchValidateURL allows the batch validation process to bypass the URL that is normally used for batch validation. This might be necessary if your database cannot communicate with your Web server.

If Oracle Configurator uses Secure Sockets Layer (SSL), then you can enable batch validation by creating an additional non-SSL-enabled (HTTP) servlet port and specifying its URL as the value of AltBatchValidateURL. For additional SSL information, see MetaLink, Oracle’s technical support Web site.

If your configurator servlet is set up to use HTTPS, then you can set AltBatchValidateURL to be a servlet using HTTP, and avoid some SSL encryption and handshaking overhead. Since the communication is between the Application database and the Application middle tier, communication does not cross the internet and thus HTTPS may not be necessary. This configuration does require extra setup and is not required.

You can also enable batch validation by using your existing SSL-enabled port for Oracle Configurator and setting up the Oracle Wallet for use by Oracle Configurator, as described on OracleMetaLink. In this case, you do not set any value for AltBatchValidateURL.

If you use a firewall, have your database setup in a DMZ, or have some other network configuration where the database cannot communicate with the web server, then you should set up an internal web server. After setting up an internal web server, you must then set the AltBatchValidateURL setting in the CZ_DB_SETTINGS table to be the URL for the configurator servlet on your internal web server.

For more information regarding DMZ setup, see MetaLink, Oracle’s technical support Web site.

To insert the AltBatchValidateURL into the CZ_DB_SETTINGS table, use the SQL INSERT statement shown in Adding AltBatchValidateURL to CZ_DB_SETTINGS.

Adding AltBatchValidateURL to CZ_DB_SETTINGS

BadItemPropertyValue

BadItemPropertyValue indicates the action that is taken when an Item’s PROPERTY _VALUE in the CZ_IMP_ITEM_PROPERTY_VALUES table does not match the DATA_TYPE in the CZ_PROPERTIES online table. The default value (F) forces the record to be updated to include the PROPERTY_VALUE so that it is imported into the CZ_ITEM_PROPERTY_VALUES online table. Valid Values for the BadItemPropertyValue Setting lists the valid values for BadItemPropertyValue setting and the disposition:

The following table lists the valid values for the BadItemProperty value setting, along with a description of the value.

Valid Values for the BadItemPropertyValue Setting
Value	Disposition
R	Reject the record in the import table and use the old PROPERTY_VALUE
F	Force the record to be updated to include the PROPERTY_VALUE from the import table
K	Update all information in the record except the Item PROPERTY_VALUE
X	Reject the record and logically delete any matching Item property value record in the CZ_ITEM_PROPERTY_VALUES table. The Item property value defaults to the property default value in the CZ_ITEM_PROPERTY_VALUES table.

BatchSize

BatchSize indicates the number of records that are modified before committing a transaction in batch operations. The BatchSize setting is also used during a purge operation.

Ordinarily a database stored procedure runs as a single transaction that is considered pending until the calling operation commits the transaction. The pending changes are lined up in a rollback segment. If the calling operation is cancelled, then the transaction is rolled back. If the calling operation encounters an error, then the pending changes in the rollback segment are discarded. However, some batch operations, such as import, can involve many more records than the database can handle as a single transaction. If the transaction is too big, then the database fails an operation with a rollback-segment error. To avoid a rollback_segment error, import and other batch-like operations count up the modified records in the database and when the count matches the BatchSize value, the operation commits the transaction and resets the counter. Every record is not committed individually because it is considerably more economical to commit many updates at once.

BOM_REVISION

BOM_REVISION indicates the BOM revision in the Oracle Applications database from which data is being imported into the CZ schema. This setting is checked to ensure that the correct date format is used in the call to the BOM Model explosion procedure.

The value of BOM_REVISION is the Oracle Applications revision number used to determine which explosion date format to use. . Valid values are 5.0.628 for Release 10.7, 11.0.28 for Release 11.0, and 11.5.0 for both Release 11i and Release 12. If the value is null (default), then 11.5.0 is used. The call to the BOM Model explosion procedure checks up to the second decimal point of this value.

If the value is 11.5.n or Release 12, then the date format YYYY-MM-DD is used. Otherwise, DD/MON/RR is used for Release 10.7 or 11.0.

CommitSize

CommitSize indicates the number of import records in each database transaction between commits. CommitSize has the same purpose as BatchSize. for more information, see BatchSize. CommitSize is used during import.

DISPLAY_INSTANCE_NAME

DISPLAY_INSTANCE_NAME determines whether an Instance Name column appears in the Oracle Configurator Summary page. Oracle Configurator checks this setting only if multiple instances of one or more components exist in the configuration.

If DISPLAY_INSTANCE_NAME is set to TRUE and at least one component in the configuration has multiple instances, then the Instance Name column appears and displays the name of each instance.

If DISPLAY_INSTANCE_NAME is set to FALSE or there are no components with multiple instances in the configuration, then the Instance Name column does not appear. If set to False but there are multiple instances in the configuration, then instance names appear in the Description column (instead of each Item’s description).

FREEZE_REVISION

FREEZE_REVISION indicates the revision number at the freeze stage. This parameter is used to capture the revision levels for the implementation of database package bodies and views. For example, if a table is tuned to improve performance, but the fields and the data returned are the same, then there is no need to change the MAJOR_VERSION or MINOR_VERSION but the FREEZE_REVISION value reflects the reworked view. This setting is read-only and populated when applying a patch.

GenerateGatedCombo

GenerateGatedCombo determines how a FALSE logic state is propagated in Explicit Compatibility, Property-based Compatibility and Design Chart Rules. See the Oracle Configurator Developer User’s Guide for additional information about Gated Combinations.

GenerateUpdatedOnly

GenerateUpdatedOnly set to YES, causes logic generation to skip all referenced Models whose logic is up-to-date. GenerateUpdatedOnly set to NO causes the logic of all referenced Models to be generated even if their logic is up-to-date.

GenStatisticsBOM

GenStatisticsBOM set to YES forces the optimizer to update the internal statistics on the BOM_EXPLOSIONS table before running queries in the CZ schema. Generating statistics allows the optimizer to choose a better execution plan based on the current data structure in a table.

GenStatisticsCZ

GenStatisticsCZ set to YES forces the optimizer to update the internal statistics on the entire CZ schema before running queries in the CZ schema. Generating statistics allows the optimizer to choose a better execution plan based on the current data structure in a table.

MAJOR_VERSION

MAJOR_VERSION indicates the major version label for the CZ schema. This setting is read-only and is populated when upgrading the schema.

MaximumErrors

MaximumErrors indicates the limit of errors allowed before an import run is terminated. If you have a large amount of data to import, or you are not concerned with the process stopping once a certain number of errors is reached, then set this parameter to an extremely large number.

MemoryBulkSize

MemoryBulkSize regulates the memory usage of import. The smaller the setting, the less memory is required for import. This number is used during import for the cz_ps_nodes extraction procedure for specifying the number of records that are processed in the same pass. If the value entered is less than the total number of records to be imported, then the specified number of records is loaded and processed, and then the next group of records is loaded and processed. If there is no value entered, then the MemoryBulkSize is set to 10000000.

MINOR_VERSION

Exec Stored Proc With Parameters

MINOR_VERSION indicates the minor version label for the CZ schema. This value is read-only and is populated when applying a patch. The MINOR_VERSION does not change during a particular family pack release.

MULTISESSION

MULTISESSION indicates the way in which a new import session interacts with other import sessions.

A positive value indicates the number of seconds to wait while another import session is running. The current state is checked every second. After the number of seconds has elapsed, control goes to the waiting import session if no other session is active, or an exception is raised if another import session is still running.
A value of 0 means do not wait if another import session is running, and immediately raise an exception if a session is already running.
A negative value means ignore other import sessions and run this import session immediately without raising an exception. Setting this parameter to a negative number is equivalent to disabling it. If a session is currently running and a new import session begins, then the first session is not aborted and there is the risk of data corruption.

When MULTISESSION is missing from the CZ_DB_SETTINGS table, it is equivalent to the default 0.

If an import session is terminated, then the CZ_XFR_RUN_INFOS table may end up in an inconsistent state with the value of COMPLETED something other than 1.

OracleSequenceIncr

OracleSequenceIncr indicates the number of primary-key values allocated by each use of a sequence. The default setting means that keys are assigned in increments of 20. Both runtime Oracle Configurator and Configurator Developer ask for a sequence value once, and then manage the sequence value minus 1 in memory. When the block is used up, runtime Oracle Configurator and Configurator Developer again call for a sequence value. Keeping the default value at 20 saves round trips to the database.

Warning: Changing the default OracleSequenceIncr setting of 20 is likely to have adverse effects. The value of OracleSequenceIncr should not be modified.

PsNodeName

PsNodeName indicates the source field to be loaded into the NAME field in the CZ_PS_NODES table. The source field is either the RefPartNbr or the DESCRIPTION field in the MTL_SYSTEM_ITEMS table. RefPartNbr is the default so that the name loaded into the Model structure in Oracle Configurator Developer matches the name in CZ_ITEM_MASTERS.

PublicationLocalBOMSynch

PublicationLocalBOMSynch controls whether the BOM Synchronization process runs automatically when a publication is created for a Model that was imported from a remote server, on the same instance in which Configurator Developer is running. If BOM Synchronization does not run in this scenario, it is possible for the publication lookup process to fail if the host application is also running on the local instance that launched Oracle Configurator.

To automatically run BOM Synchronization in the scenario described above, set the CZ_DB_SETTINGS parameter PublicationLocalBOMSynch to YES by running the Modify Configurator Parameters concurrent program. Enter the following parameters when submitting this program:

Section Name: Publication
Setting ID: PublicationLocalBOMSynch
Value: YES
Type: 4

PublicationLogging

PublicationLogging indicates whether a trace of the publication process is logged in the CZ_DB_LOGS table. The trace is helpful for debugging purposes and can be viewed in the log file. For more information about viewing log files, see Viewing Log Files.

PublishingCopyRules

PublishingCopyRules indicates whether or not configuration rules are copied during publishing. If PublishingCopyRules is set to NO, then only Configurator Extension rules are copied during publishing. The publishing process is faster when PublishingCopyRules is set to NO.

If the PublishingCopyRules is set to YES, then all rules are copied and both the source and published Models have the same rules.

Note: Setting 'PublishingCopyRules' to 'NO' only affects you if changes are made to logic generation that are incompatible with previous versions of Oracle Configurator. If the rules for a published Model are not copied, then you cannot generate logic for the published Models. Using the NO setting requires republishing all published Models.

PurgeDeleteConfigBatchsize

When you run the concurrent program Purge Configurator Tables, you can control its commit behavior by setting this parameter, which specifies how often the purge program issues a commit, in terms of a number of configurations. For example, a value of 200 specifies a commit after deleting a batch of 200 configurations.

RefPartNbr

RefPartNbr identifies the source fields that are loaded from the MTL_SYSTEM_ITEMS table into CZ_ITEM_MASTERS.REF_PART_NBR. This is a segment from the System Item key flexfield definition.

RefPartNbr determines what name is displayed for each imported Model structure node. The default value ’CONCATENATED_SEGMENTS’ enables the BOM Model import process to construct BOM Model node names using multi-segment part numbers.

When RefPartNbr is set to ’SEGMENT1’, only MTL_SYSTEM_ITEMS.SEGMENT1 is the source of the node names in the imported Model structure. If you want to use only the first segment of a part number as the node name, the Oracle Configurator Administrator must manually set RefPartNbr to ’SEGMENT1’ by running the Modify Configurator Parameters concurrent program.

Any value for RefPartNbr other than ’CONCATENATED_SEGMENTS’ or ’SEGMENT1’ causes the import process to retrieve the value of the DESCRIPTION column from MTL_SYSTEM_ITEMS and displays the Item description as the node name in Configurator Developer.

Warning: Examine MTL_SYSTEM_ITEMS_VL. CONCATENATED_SEGMENTS to verify that the field is correctly populated. If the field is incorrectly populated, then the entry in Oracle Inventory may be wrong. If the entry is correct, check CZ_IMP_ITEM_MASTER.REF_PART_NBR to see that the value is the same as that in MTL_SYSTEM_ITEMS_VL. CONCATENATED_SEGMENTS.

Concatenated segments, including separators, must not exceed 1000 characters, which is the limit of the CZ_PS_NODES.NAME field. Any description longer than 1000 characters is truncated. The default separator is a dot (.). Other valid separators are , -, or a custom value. See the Oracle Inventory User’s Guide for more information about setting up part numbers.

You can enter multi-segment Items in the From Item and To Item input fields when you run either the Populate or Refresh Configuration Models concurrent program. You must include any separators that exist in the Item’s part number when you enter multi-segment Item names.

Warning: When updating an existing Model in Configurator Developer to use multi- segment part numbers, you must either reimport or refresh the BOM Model. Confirm that the BOM Model is getting re-exploded during import. The CZ_DB_SETTINGS.RUN_BILL_EXPLODER should be Yes.

ResolvePropertyDataType

ResolvePropertyDataType controls whether Item Catalog Descriptive Elements are imported into Configurator Developer as Item Properties with a data type of Text or Decimal Number. If the value for this setting is NO, all imported Item Properties have a data type of Text in Configurator Developer.

If the value of this setting is YES, then all Descriptive Elements whose value is a number are imported as Item Properties and have a data type of Decimal Number. All Descriptive Elements whose value is text (for example, Weight) have a data type of Text.

If ResolvePropertyDataType is null, then all Descriptive Elements are imported into Configurator Developer as Item Properties with a data type of Text.

ResolvePropertyDataType Setting table illustrates how ResolvePropertyDataType affects how Descriptive Elements values are imported into Oracle Configurator Developer.

ResolvePropertyDataType Setting
ResolvePropertyDataType Setting	Item Catalog Descriptive Element Value	Data Type in Oracle Configurator Developer
YES	15	Decimal Number
YES	Length	Text
NO	15	Text
YES	Length	Text
null	’Length’ or ’15’	Text

Item Property is a protected field in the CZ schema (the NOUPDATE flag is set during import). Once you import a BOM Model, you cannot change an Item Property’s data type simply by modifying the ResolvePropertyDataType setting and then refreshing the BOM Model.

RestoredConfigDefaultModelLookupDate

RestoredConfigDefaultModelLookupDate setting controls which publication Oracle Configurator uses on an order when called from Order Management. If this setting is config_creation_date, then Oracle Configurator uses the order line creation date. If this setting is null, then Oracle Configurator uses sysdate.

For more information, see DEFAULT_RESTORED_CFG_DATES.

Revision Date and User

Revision Date and User is read-only and documents the date and time at which the CZ schema was last upgraded, and the username of the user who performed the task.

RUN_BILL_EXPLODER

RUN_BILL_EXPLODER is a YES/NO flag (default=YES) that indicates whether the Oracle Applications Bills of Material exploder should be run on each bill that is marked for import in the CZ_XFR_PROJECT_BILLS table in the CZ schema at the time of import. See Populating the CZ Schema for more information on exploding a BOM Model.

The Oracle Configurator Populate or Refresh Configuration Models concurrent programs load bills and Items based on top bills listed in the CZ_XFR_PROJECT_BILLS table in the CZ schema. Before extracting, if the RUN_BILL_EXPLODER setting is set to YES, then the procedure calls the BOM Model exploder to refresh data in BOM_EXPLOSIONS for each record in the CZ_XFR_PROJECT_BILLS table. If RUN_BILL_EXPLODER is set to NO, then the concurrent program transfers the BOM Models that are flagged for import in the CZ_XFR_PROJECT_BILLS table without running the BOM Model exploder first.

Note: The Populate or Refresh Configuration Models concurrent programs do not explode BOM Models when importing from a remote server. See Exploding BOM Models in Oracle Applications for details.

CZ_INTL_TEXTS contains the text string from the DESCRIPTION field in the BOM_EXPLOSIONS table for each imported BOM Model structure node.

The Oracle Configurator SQL*Plus scripts and concurrent programs target all or a subset of BOM Models exploded in the BOM_EXPLOSIONS table in the Oracle Applications database. Selected BOM Model Items come from the BOM_BILL_OF_MATERIAL and the BOM_INVENTORY_COMPONENTS tables.

Note: Importing a BOM Model from a remote instance may fail if RUN_BILL_EXPLODER is set to YES in the local instance. (In this case, an error message similar to the following appears: 'ORA-03113: end-of-file on communication channel.') If this occurs, set RUN_BILL_EXPLODER flag to No and then re-submit the import concurrent program. The new setting should enable the process to complete successfully.

SuppressSuccessMessage

The SuppressSuccessMessage setting affects runtime Oracle Configurator behavior by suppressing messages that would normally be shown. The setting determines whether a message is displayed after fixing a validation error.

If SuppressSuccessMessage is set to NO, then after fixing a validation error a runtime success message is displayed. If SuppressSuccessMessage is set to YES, then after fixing a validation error a runtime success message is not displayed.

To insert SuppressSucessMessage into CZ_DB_SETTINGS, use the SQL*Plus INSERT statement shown in Adding SuppressSuccessMessage to CZ_DB_SETTINGS.

Adding SuppressSuccessMessage to CZ_DB_SETTINGS

TimeImport

TimeImport enables the collection of timing information during import.

UI_NODE_NAME_CONCAT_CHARS

UI_NODE_NAME_CONCAT_CHARS sets the concatenation character that is used when generating UI captions using both the node name and description. The default concatenation character separating each text string is a comma surrounded by two spaces. (For example: 'AT62431 , Sentinal Custom Laptop'). The Oracle Configurator Administrator can change the concatenation character that separates each string by running the Modify Configurator Parameters concurrent program.

UseLocalTableInExtractionViews

UseLocalTableInExtractionViews is a YES/NO flag. If UseLocalTableInExtractionViews is set to YES, then definitions of some import extraction views include the DUAL table in the join. The UseLocalTableInExtractionViews setting is ignored if the import source server is local.

Note: If you are importing or refreshing from a remote database instance and the database instance is version 8i, then UseLocalTableInExtractionViews must be set to YES. This is because of an RDBMS bug. If this setting is not YES, then the following error appears in the cz_db_logs table after running the Populate and Refresh Configuration Models Concurrent Programs :'ORA-01025: UPI parameter out of range'

UtlHttpTransferTimeout

UtlHttpTransferTimeout allows modification of the timeout length that is used inside the call to the UTL_HTTP.REQUEST procedure during batch validation. The value is the number of seconds. Once the call completes, the timeout is set back to its original value.

Oracle Stored Proc

To insert UtlHttpTransferTimeout into the CZ_DB_SETTINGS, use the SQL*Plus INSERT statement shown in Adding UtlHttpTransferTimeout to CZ_DB_SETTINGS.

Adding UtlHttpTransferTimeout to CZ_DB_SETTINGS