Migrating APO Data

APO Import Process

Most agencies receive the APO-related data from different sources like the geo spatial applications, county, city, tax departments, etc. This source data needs to be curated to specific formats as prescribed by the Oracle Permitting and Licensing APO Import Process before it can be loaded into the Oracle Permitting and Licensing application. The conversion of the raw APO data into this specific format involves a series of transformation and mapping activities which may get complex depending upon the nature of the source data.

If you already have your APO data in the specific format, then you can use the Import Management tool to import the data onto the Oracle Permitting and Licensing application.

Reference architecture for APO import process

If you don’t have your APO data in the prescribed format, you can use an end-to-end APO data import process using the Oracle Integration Cloud - Data Integration (OCI - Data Integration). With OCI – Data Integration you can easily transform your raw APO data. This transformed data can now be imported using the Import Management tool onto the Oracle Permitting and Licensing application.

Reference architecture for Oracle Permitting and Licensing end-to-end APO import process — Reference architecture for Public Sector Licensing and Permitting end-to-end APO import process

Note: You must have the appropriate subscriptions/licenses for Oracle Cloud Infrastructure (OCI) services like Oracle Data Integration Cloud, Oracle Object Storage Cloud and Oracle Integration Cloud (OIC) services to complete this process.

APO Import Process Using the Import Management

Use Import Management from the Tools work area, to import all your external APO data from CSV files. The Import Management job performs the APO data import after validation of the data with a pre-defined set of rules and Import Options configurations. You can create or update the APO records through import. The import options and other details differ by the type of data that you are importing.

Notes:

Before importing, understand how the data in your file maps to the attributes in Public Sector Licensing and Permitting APO objects and what values are expected in the import file.
The data records in the CSV source file may not be processed in the listed order.
In Import Management, each of the six types of APO data are in separate source files. If the existing data file combines either two or more objects data (for e.g. parcel, address and owner data), then you must separate these into different files before starting the import process. You can either perform a manual transformation/translations or use any suitable tool to prepare a separate source file for each object in an object's template format. Or you can use the end-to-end APO import process detailed in this document to help you with data transformation.
You can schedule an APO Import Process using the Oracle Permitting and Licensing application, if you already have your APO data in the prescribed format (that is, transformed data ready for import) and the appropriate subscription/license for Oracle Integration Cloud (OIC). For more details, see the section on Scheduling an APO Import Process Using the Import Property Information Process.

Manage APO Import Objects

Import APO Objects correlate with the APO master tables in Oracle Applications. Understand APO import objects and their attributes to manage the data that you want to import.

Import APO Object Details

Click Tools > Import Management > Import Objects.

To see the details of APO import objects, select a specific APO object on the Import Object Details page. The table shows the details about each importable object on the Import Object Details page.

Display Name	Language Independent Code	User Key
Parcel	PublicSectorParcel	ParcelIdEntered
Parcel Address	PublicSectorParcelAddress	ParcelIdEntered
Parcel Owner	PublicSectorParcelOwner	ParcelIdEntered
Parcel Value	PublicSectorParcelValue	ParcelIdEntered
Parcel District	PublicSectorParcelDistrict	ParcelIdEntered
Address District	PublicSectorAddressDistrict	DistrictId

Display Name	Required	Validation
Parcel	ParcelIdEntered EnabledFlag ParcelStatus	ParcelStatus should be in ('ORA_FIN','ORA_RET','ORA_PROV') ParcelSizeOneUnit/ParcelSizeTwoUnit/StructureSizeOneUnit/StructureSizeTwoUnit value should be from configured UOM in the system. EnabledFlag (Y/N) (Value will be updated as 'N' if no value in the file) Cannot modify the status of reconciled parcels.
Parcel Address	ParcelIdEntered EnabledFlag InputSource Country Postal Code	ParcelIdEntered (should be valid if not null) Mark the row for update as error if it returns more than one row in the Parcel Address table for that key combination (setup in the parcel import option). PrimaryFlag (Y/N) (Value will be updated as 'N' if no value in the file) Country should be two character code setup in the system. Eg. US EnabledFlag (Y/N) (Value will be updated as 'N' if no value in the file) InputSource (U/S) (Value will be updated as 'U' if no value in the file) Address1 (Will be derived based on concatenation of individual street columns: Street Number, Street Direction, Street Name, Street Type, Street Suffix and Unit Number. Ensure that you do not leave these fields blank in the input file. Street Name will always be entered by the user.)
Parcel Owner	ParcelIdEntered EnabledFlag Country Address1 Postal Code	Mark the row for update as error, if it returns more than one row in the Parcel Owner table for that key combination (setup in the parcel import option). ParcelIdEntered should be valid that exists in the system. EnabledFlag (Y/N) (Value will be updated as 'N' if no value in the file) PrimaryFlag (Y/N) (Value will be updated as 'N' if no value in the file) Country should be two character code setup in the system. Example: US
Parcel Value	ParcelIdEntered EffectiveStartDate EffectiveEndDate	ParcelIdEntered should be valid that exists in the system. EffectiveEndDate should be greater than EffectiveStartDate. Note: If EffectiveStartDate is not in the input import file, then the import job will flag that row as error. ParcelSizeOneUnit/ParcelSizeTwoUnit value should be from configured UOM in the system. Currency Code should be from configured Currency in the system. Note: If the system has the same combination of EffectiveStartDate, EffectiveEndDate and ParcelIdEntered values in the import file, then update is performed. If the EffectiveStartDate is different, then that row will be constituted as a new row and the import job will perform the insert.
Parcel District	ParcelIdEntered DistrictId	ParcelIdEntered should be valid that exists in the system. DistrictId should be valid that exists in the system.
Address District	Address Combination as configured in the import options for functional key. DistrictId	Address combination should be valid and not mapped to a parcel. DistrictId should be valid that exists in the system. Note: The input import file for Address District will have all address columns in it for the user to populate. The Address District import job checks for matching combination of address field values in the system. If present, the address district row is created. If not, the row is marked as error and address row is not created.

If you enter values for surrogate keys like ParcelId, AddressId and OwnerId in the input file, these will not be considered. Rather the program generates the surrogate key value for new records.

For creating Address without parcel, you should leave the Parcel number blank in the input import file.

Bulk Insert/Update has been implemented with the APO high volume import jobs. So you would need to complete the insert first before trying to update the records. For example, within the same input file you cannot have two rows for the same parcel, first one for insert and the second one for update. As that new parcel is not yet created in the system, the second row will always be considered as insert and not update.

Import Data Using Templates

Templates make it easy for you to use the auto mapping feature. For auto mapping to work, the column headers in the source file must match the names of the columns (attributes) of the objects imported. The column headers shown in the first row of the downloadable templates are the same as the column names of the corresponding objects. Therefore, building source files from the templates allows the auto mapping process.

For example, if your source file has a column "ParcelIdEntered" then it's mapped to the Parcel ID Entered column. However if your source file's column header is "APN", then auto mapping fails and you should map manually.

Download Import Object Templates

Click Tools > Import Management > Import Objects.
On the Manage Import Objects page, click the Download icon for the object, such as Parcel, for which you want to download the template.

Populate Import Templates

Extract the contents of the ZIP file to a folder. The extracted folder contains the template text files for each of the parent and child objects. The read me text file within the folder contains instructions on how to use the template files to create source file for importing data. Here are the steps to import data using the downloaded template:

Open the CSV template file for the object you want to import.
The file for each object contains two rows. The first row contains all the column names in a format that's compatible with the automatic mapping functionality. The second row contains the display names as shown on the import mapping user interface.
Before populating the CSV template file with the data to be imported, delete the display name values in the second row, and populate the columns with the data to import.
Use the updated file as the source when you're creating an import activity on the import user interface.

The automatic mapping functionality maps all the columns automatically. You can see both the mapped column names and the corresponding column names in your language on the Map Fields page.

Manage APO Import Mappings

An import mapping lets you quickly map your source object attributes to the object attributes in Oracle Permitting and Licensing. You must be an administrator or setup user to create import mappings.

You can create an import mapping in one of the following two ways:

Create an import mapping manually.
Generate import maps from the sample source file.

Create Import Mapping Manually

Click Tools > Import Management > Import Objects.
Click the Parcel link under the Display Name column to create a mapping for the Parcel object.
On the Manage Mapping page, click the Create Import Mapping button.
On the Edit Import Mapping page, enter the name of the mapping, and provide a description for the mapping.
The Target Attributes section displays the attributes of the Parcel object that should map to the source file columns.
Check the Required column to see if the attribute is required or is a user key.
In the Source File section, click the Add Column button to insert a column to the mapping file.
Click the Save and Close button to save the mapping.
To edit or download the mapping file, click the mapping number link for your mapping on the Manage Mapping page.
On the Edit Import Mapping page, click Download Map from the Actions menu.

Generate Import Maps from Source File

Click Tools > Import Management > Import Objects.
Click the Parcel link under the Display Name column to create a mapping for the Parcel object.
On the Manage Mapping page, click the Create Import Mapping button.
On the Edit Import Mapping page, enter name of the mapping, select the object such as Parcel, and provide a description for the mapping.
The Target Attributes section displays the attributes of the Parcel object that should map to the source file columns. Also, check the Required column to see if the attribute is required or is a user key.
From the Actions menu under Source File section, click Select Source File.
In the Source File dialog box, choose the CSV file that should be the source for your mapping file. This creates the mapping file based on the column header and example values in the source file.
Click Save and Close button.

Import Data

To import data:

Click Tools > Import Management.
On the Manage Imports page, click the Create Import Activity button.

On the Enter Import Options page, provide values for each field as shown in the following table:

Field	Description
Name	Name of the import.
Object	Object that you are importing. Public Sector Licensing and Permitting APO objects: Parcel Parcel Address Parcel Owner Parcel Value Parcel District Address District If you can't find your object, then search for it by clicking the Search link. In the Search and Select dialog box, enter the object name in the Object text box and click the Search button. Select your object from the result list and click OK. You can also use the Advanced Search option by clicking the Advanced button. Here, you can search based on various filter criteria such as object name, attachment supported, creation date, and so on. If the object you're importing isn't listed, then verify if you have the roles and privileges required to import the object.
File Name	Browse and select a text file in CSV format. The first row of the source file is treated as the header row. Provide a file name within 40 characters.

Field

Description

Name

Name of the import.

Object

Object that you are importing.

Public Sector Licensing and Permitting APO objects:

Parcel
Parcel Address
Parcel Owner
Parcel Value
Parcel District
Address District

If you can't find your object, then search for it by clicking the Search link. In the Search and Select dialog box, enter the object name in the Object text box and click the Search button. Select your object from the result list and click OK.

You can also use the Advanced Search option by clicking the Advanced button. Here, you can search based on various filter criteria such as object name, attachment supported, creation date, and so on.

If the object you're importing isn't listed, then verify if you have the roles and privileges required to import the object.

File Name

Browse and select a text file in CSV format. The first row of the source file is treated as the header row. Provide a file name within 40 characters.

Optionally, to set additional import configurations, click the Advanced Options section.
Here, you can configure settings under Source File, Import Options, or Create Schedule sections.

In the Source File and Import Options regions, some of the options in the following table might not be available depending on the object that you are importing:

Option	Description
Import Mode	The mode of import, that is, whether it should be an update or create, is determined by the data in the import file. Update and create – A new record is created if a matching record is not found. Create – All records in the file are new records which have to be created. If there are any existing matching records, then they are marked as errors.
Enable High-Volume Import	The high-volume import mode is designed to import millions of records at once. All the APO objects are delivered for high volume import. By default, this mode is enabled for the six objects.
Notification email	The email of the individual who receives import processing notifications. The user submitting the import receives an email notification automatically. To have more than one email recipient, separate the email addresses with a semicolon.
Delimiter	If your file doesn't use a comma to separate values, then select the correct delimiter in the Delimiter drop-down list. Possible values are: Caret Symbol Closing Curly Bracket Closing Parenthesis Closing Square Bracket Colon Comma Exclamatory Mark Minus Opening Curly Bracket Opening Parenthesis Opening Square Bracket Pipe Symbol Plus Question Mark Semi-Colon Star Symbol Tilde Symbol
Decimal Separator	The decimal separator used in your import file.
Date Format	The format of the date fields in your file.
Time Stamp Format	The format of the time fields in your file.
File Encoding	The format in which your source file is encoded.

In the Create Schedule region, schedule the import to run immediately or at a future date. If you select a future date, then provide the date and time to start the import. This is a one-off schedule and cannot be used for recurring scheduling.
Click Next.
The Map Fields page shows the first row of the data from your source file. By default, the application tries to automatically map each of the source file columns to the appropriate target object attribute. The target attribute cannot be dragged to attribute display name in the import activity.
Note: The data in the unmapped columns are not imported.
You select a predefined mapping from the list of available mappings under the Import Mapping drop-down list. If you are reusing an import mapping, then both the source and target columns are already populated.
Leave the Attribute Display Name field blank for any column that you don't want to import.
In the case of hierarchical import, you can see the mapping information for child objects in the subsequent tabs. Note that data validation isn't done for these objects.
Click Next.
On the Review and Submit page, review the import activity configuration. If you had not run the pre-validation on the Map Fields page, then you get a notification message to run the validation process. You can view any unmapped columns of parent or child objects on this page.
Validation errors, if any, are displayed on the Mapping Validation screen. If you get warnings about unmapped columns, then you can ignore these columns, and proceed to submit the import job.
Click Submit to queue the import.

APO Import Options

To access Parcel Import Options page, click Property Information > Parcel Import Options.
Use the Parcel Import Options to configure the following for the APO high volume import jobs.

Option	Description
Import Files	Toggles are provided for all eight jobs. The import process can be initiated for a particular object through orchestration or Import Management UI, only if the toggle is ON. The user can pre select import mapping for all objects. The Default import mapping is the automatic mapping based on the input file template.
Parcel Options	The Retire Parcels Not Found in Import File setting can be used to retire those parcels not included in the input parcel file. This option should only be used when the parcel input file contains a complete set of active parcels, including parcels with no changes.
Address Options	The Disable Addresses Not Found in Import File setting can be used to disable those parcel addresses not included in the input parcel address file. This option should only be used when the address input file contains a complete set of addresses for each parcel, including addresses with no changes. The Select Fields to Identify New Addresses setting can be used to decide the functional key that will determine Insert vs Update. If the combination of selected address field values in the address import file also exists in the database for the same parcel, the existing address will be updated. If not, a new address will be created. An existing address will be updated only if all the field values match.
Owner Options	The Disable Owners Not Found in Import File setting can be used to disable those parcel owners not included in the input parcel address file. This option should only be used when the owner input file contains a complete set of owners for each parcel, including owners with no changes. The Select Fields to Identify New Owners can be used to decide the functional key that will determine Insert vs Update. If the combination of selected owner field values in the owner import file also exists in the database for the same parcel, the existing owner will be updated. If not, a new owner will be created. An existing owner will be updated only if all the field values match.
District Options	The Delete District Assignments Not Found in Import File setting can be used to delete those Parcel Districts or Address Districts not included in the input Parcel District or Address District file. This option should be used only when the district import file contains a complete set of districts for each parcel or address without parcel.
Parcel Condition Options	The Resolve Parcel Conditions Options Not Found in Import File setting can be used to resolve the parcel conditions in your database that are not in the parcel condition import file. This option should only be used when the parcel condition import file contains a complete set of active parcel conditions. The import process resolves only those parcel conditions which has the Allow Updates by Parcel Import option turned on in the Condition Details page. For more information, see Setting Up Conditions.
Address Condition Options	The Resolve Address Conditions Options Not Found in Import File setting can be used to resolve those address conditions in your database that are not in the address condition import file. This option should only be used when the address condition import file contains a complete set of active address conditions. The import process resolves only those address conditions which has the Allow Updates by Parcel Import option turned on in the Condition Details page. For more information, see Setting Up Conditions.
Processing Options	Use the Preview Mode switch to check how many rows will be successfully processed by the import process. When the import process is run on the preview mode, actual upsert will not be performed on the base tables. Additional post processes can be added in the Optional Processes.

APO Import Error Review

The Import Wizard captures the following errors:

Errors that occur during data load into interface table. Common errors like data type mismatch and data length mismatch errors are trapped here.
Errors that occur due to validation business logic coded in the pl sql. The data loaded in the interface table from the input file are validated for all errors.

Rows that fail the validation are flagged with appropriate error codes in the interface table. Import wizard will create a rejected csv file with the errored rows for the user to correct. The errors would be sorted by the record number from the input file.

For example, Parcel District input file has three rows, out of which errors are reported on two rows.

End-to-End APO Import Using OCI – Data Integration, OIC AND Import Management

In the end-to-end APO import process, the OCI – Data Integration helps you transform your raw APO data as received from different sources and then import the transformed data onto the Public Sector Licensing and Permitting application using Import Management.

Listed here are the various products and services from Oracle that will help you to seamlessly perform this end-to-end APO import process:

Oracle Integration Cloud is used to perform orchestration across various components in the import process.
OCI – Data Integration is used to perform transformation and mapping of raw data into formatted data that can be loaded into Public Sector Licensing and Permitting APO Import Process.
Object Storage is used to store copy of external data files for processing and to store formatted data after transformation and mapping.
APO Import Process is the Import Management job which is designed to perform Public Sector Licensing and Permitting APO data import after validation of the data with pre-defined set of rules and Import Options configurations.

Note: You must procure appropriate subscriptions/licenses to OCI services like Oracle Data Integration Cloud, Oracle Object Storage Cloud and Oracle Integration Cloud services to complete this process.

For detailed steps on how to perform an end-to-end APO import process, refer Migrating APO Data to Oracle Permitting and Licensing.