Address and Tax Validation API Use

This chapter covers the following topics:

Location Service APIs
Adapter APIs
Tax Assignment APIs

Location Service APIs

PL/SQL Package Name: HZ_LOCATION_SERVICES_PUB

Address Validation API

Description

This API sends XML document to vendor adapter to validate and receives validated address in XML format. The API depends on adapter_id or country code to call address validation against different adapter.

PL/SQL Procedure

PROCEDURE submit_addrval_doc(
    p_addrval_doc                 IN OUT NOCOPY NCLOB,
    p_adapter_id                  IN NUMBER DEFAULT NULL,
    p_country_code                IN VARCHAR2 DEFAULT NULL,
    p_module                      IN VARCHAR2 DEFAULT NULL,
    p_module_id                   IN NUMBER DEFAULT NULL,
    x_return_status               OUT NOCOPY VARCHAR2,
    x_msg_count                   OUT NOCOPY NUMBER,
    x_msg_data                    OUT NOCOPY VARCHAR2
)

Parameter Description and Validation

The following table lists information about the parameters in the Address Validation API. The table includes the parameter names, the type of each parameter, the data type of each parameter, the necessity of the parameter, and other information about the parameter such as validation, defaults, and other comments.

Parameter Name	Type	Data Type	Required	Validation, Default, Comment
p_addrval_doc	IN OUT	NCLOB	Yes	Comment: XML document that contains locations for address Validation: API returns validated locations in XML format.
p_adapter_id	IN	NUMBER	No	Validation: Validate adapter_id in the HZ_ADAPTERS table Comment: Identifier of an adapter used to validate locations in p_addrval_doc.
p_country_code	IN	VARCHAR2	No	Comment : Country code used to find adapter information for address validation, if p_adapter_id is not passed.
p_module	IN	VARCHAR2	No	Comment : Name of the module calling address validation. The API does not validate this parameter. This parameter is used for record keeping only.
p_module_id	IN	NUMBER	No	Comment : Identifier of the module calling address validation. The API does not validate this parameter. This parameter is used for record keeping only.
x_return_status	OUT	VARCHAR2	Yes	Comment : API return status
x_msg_count	OUT	NUMBER	Yes	Comment : Number of messages in stack
x_msg_data	OUT	VARCHAR2	Yes	Comment : Message text if x_msg_count = 1

Adapter APIs

PL/SQL Package Name: HZ_ADAPTER_PUB

PL/SQL Record Structure for Adapter

TYPE adapter_rec_type IS RECORD( 
    adapter_id                    NUMBER, 
    adapter_content_source        VARCHAR2(30), 
    adapter_meaning               VARCHAR2(80),
    adapter_description           VARCHAR2(240)   DEFAULT NULL, 
    message_format_code           VARCHAR2(30), 
    synchronous_flag              VARCHAR2(1)     DEFAULT 'Y', 
    invoke_method_code            VARCHAR2(30), 
    host_address                  VARCHAR2(240), 
    enabled_flag                  VARCHAR2(1)     DEFAULT 'Y', 
    maximum_batch_size            NUMBER, 
    default_batch_size            NUMBER, 
    default_replace_status_level  VARCHAR2(30),
    username                      VARCHAR2(100), 
    encrypted_password            VARCHAR2(100) 
)
TYPE adapter_terr_rec_type IS RECORD(

    adapter_id                   NUMBER, 
    territory_code               VARCHAR2(30), 
    enabled_flag                 VARCHAR2(1)      DEFAULT 'Y', 
    default_flag                 VARCHAR2(1), 
)

Create Adapter API

Description

This API is used to create adapter. The record holds information about an adapter, such as name, meaning, description, host address, xml format and communication protocol. When creating an adapter, a new lookup code, meaning and description will be added under lookup type CONTENT_SOURCE_TYPE.

PL/SQL Procedure

   PROCEDURE create_adapter ( 
    p_adapter_rec                 IN ADAPTER_REC_TYPE, 
    x_adapter_id                  OUT NOCOPY NUMBER, 
    x_return_status               OUT NOCOPY VARCHAR2, 
    x_msg_count                   OUT NOCOPY NUMBER, 
    x_msg_data                    OUT NOCOPY VARCHAR2 
)

Parameter Description and Validation

The following table lists information about the parameters in the Create Adapter API. The table includes the parameter names, the type of each parameter, the data type of each parameter, the necessity of the parameter, and other information about the parameter such as validation, defaults, and other comments.

Parameter Name	Type	Data Type	Required	Validation, Default, Comment
adapter_id	IN	NUMBER	No	Validation : Unique if passed in, else generated from sequence
adapter_content_source	IN	VARCHAR2	Yes	Validation : Mandatory attribute validated against CONTENT_SOURCE_TYPE lookup type
adapter_meaning	IN	VARCHAR2	Yes	Validation : Mandatory attribute validated against CONTENT_SOURCE_TYPE lookup type for duplicate meaning
adapter_description	IN	VARCHAR2	No
message_format_code	IN	VARCHAR2	Yes	Validation : Mandatory attribute validated against HZ_MESSAGE_FORMAT lookup type
synchronous_flag	IN	VARCHAR2	Yes	Validation : Mandatory attribute; Y or N Default: Y
invoke_method_code	IN	VARCHAR2	Yes	Validation : Mandatory attribute validated against HZ_INVOKE_METHOD lookup type
host_address	IN	VARCHAR2	No
enabled_flag	IN	VARCHAR2	Yes	Validation : Mandatory attribute; Y or N Default: Y
maximum_batch_size	IN	NUMBER	Yes	Validation : Mandatory attribute that must be greater than 0
default_batch_size	IN	NUMBER	Yes	Validation : Mandatory attribute that must be greater than 0
default_replace_status_code	IN	VARCHAR2	No
username	IN	VARCHAR2	No
encrypted_password	IN	VARCHAR2	No
x_adapter_id	OUT	NUMBER	Yes	Comment : Return adapter_id of the record created
x_return_status	OUT	VARCHAR2	Yes	Comment : API return status
x_msg_count	OUT	NUMBER	Yes	Comment : Number of messages in stack
x_msg_data	OUT	VARCHAR2	Yes	Comment : Message text if x_msg_count = 1

Other Validations

There can be only one record for a given adapter_content_source. There can be only one record for a given adapter_meaning.

Update Adapter API

Description

This API is used to update adapter. It updates a record in the HZ_ADAPTERS table. If meaning and description are changed, the API will update corresponding lookup code under lookup type CONTENT_SOURCE_TYPE.

PL/SQL Procedure

   PROCEDURE update_adapter ( 
    p_adapter_rec                 IN ADAPTER_REC_TYPE, 
    px_object_version_number    IN OUT NOCOPY NUMBER, 
    x_return_status               OUT NOCOPY VARCHAR2, 
    x_msg_count                   OUT NOCOPY NUMBER, 
    x_msg_data                    OUT NOCOPY VARCHAR2 
)

Parameter Description and Validation

The following table lists information about the parameters in the Update Adapter API. The table includes the parameter names, the type of each parameter, the data type of each parameter, the necessity of the parameter, and other information about the parameter such as validation, defaults, and other comments.

Parameter Name	Type	Data Type	Required	Validation, Default, Comment
adapter_id	IN	NUMBER	Yes	Validation : Valid adapter_id should be passed in Comment: Pass the adapter_id from HZ_ADAPTERS table
adapter_content_source	IN	VARCHAR2	Yes	Validation : Mandatory attribute, Validated against CONTENT_SOURCE_TYPE lookup type
adapter_meaning	IN	VARCHAR2	Yes	Validation : Mandatory attribute validated against CONTENT_SOURCE_TYPE lookup type for duplicate meaning
adapter_description	IN	VARCHAR2	No
message_format_code	IN	VARCHAR2	Yes	Validation : Mandatory attribute validated against HZ_MESSAGE_FORMAT lookup type
synchronous_flag	IN	VARCHAR2	Yes	Validation : Mandatory attribute; Y or N Default: Y
invoke_method_code	IN	VARCHAR2	Yes	Validation : Mandatory attribute validated against HZ_INVOKE_METHOD lookup type
host_address	IN	VARCHAR2	No
enabled_flag	IN	VARCHAR2	Yes	Validation : Mandatory attribute, Y or N Default: Y
maximum_batch_size	IN	NUMBER	Yes	Validation : Mandatory attribute, Must be greater than 0
default_batch_size	IN	NUMBER	Yes	Validation : Mandatory attribute, Must be greater than 0
default_replace_status_code	IN	VARCHAR2	No
username	IN	VARCHAR2	No
encrypted_password	IN	VARCHAR2	No
px_object_version_number	IN OUT	NUMBER	Yes	Validation: Mandatory attribute validated against value in the database for the existing adapter record
x_return_status	OUT	VARCHAR2	Yes	Comment : API return status
x_msg_count	OUT	NUMBER	Yes	Comment : Number of messages in stack
x_msg_data	OUT	VARCHAR2	Yes	Comment : Message text if x_msg_count = 1

Create Adapter Territory API

Description

You can use this API to assign a territory to an adapter. This API creates a record in the HZ_ADAPTER_TERRITORIES table. The adapter must be created before you can assign its territory. You can create multiple territory records for an adapter with different territory codes. The API can specify the default adapter for a territory.

PL/SQL Procedure

PROCEDURE create_adapter_terr ( 
    p_adapter_terr_rec                 IN ADAPTER_TERR_REC_TYPE, 
    x_return_status               OUT NOCOPY VARCHAR2, 
    x_msg_count                   OUT NOCOPY NUMBER, 
    x_msg_data                    OUT NOCOPY VARCHAR2 
)

Parameter Description and Validation

The following table lists information about the parameters in the Create Adapter Territory API. The table includes the parameter names, the type of each parameter, the data type of each parameter, the necessity of the parameter, and other information about the parameter such as validation, defaults, and other comments.

Parameter Name	Type	Data Type	Required	Validation, Default, Comment
adapter_id	IN	NUMBER	Yes	Validation : Unique if passed in, otherwise generated from sequence
territory_code	IN	VARCHAR2	Yes	Validation : Mandatory attribute, Validated against FND_TERRITORIES table
enabled_flag	IN	VARCHAR2	Yes	Validation : Mandatory attribute; Y or N Default: Y
default_flag	IN	VARCHAR2	No	Validation : Y or N
x_return_status	OUT	VARCHAR2	Yes	Comment : API return status
x_msg_count	OUT	NUMBER	Yes	Comment : Number of messages in stack
x_msg_data	OUT	VARCHAR2	Yes	Comment : Message text if x_msg_count = 1

Other Validations

There can be only one record for a given adapter_id, territory_code.

Update Adapter Territory API

Description

This API is used to update territory for an adapter. This API updates a record in HZ_ADAPTER_TERRITORIES table. You cannot update the territory code but you can change the default and enabled flag of the territory record.

PL/SQL Procedure

PROCEDURE create_adapter_terr ( 
    p_adapter_terr_rec        IN ADAPTER_TERR_REC_TYPE, 
    px_object_version_number  IN OUT   NOCOPY    NUMBER, 
    x_return_status           OUT      NOCOPY    VARCHAR2, 
    x_msg_count               OUT      NOCOPY    NUMBER, 
    x_msg_data                OUT      NOCOPY    VARCHAR2 
)

Parameter Description and Validation

Parameter Name	Type	Data Type	Required	Validation, Default, Comment
adapter_id	IN	NUMBER	Yes	Validation : Unique if passed in, otherwise generated from sequence
territory_code	IN	VARCHAR2	Yes	Validation : Mandatory attribute, Validated against FND_TERRITORIES table
enabled_flag	IN	VARCHAR2	Yes	Validation : Mandatory attribute; Y or N Default: Y
default_flag	IN	VARCHAR2	No	Validation : Y or N
x_return_status	OUT	VARCHAR2	Yes	Comment : API return status
x_msg_count	OUT	NUMBER	Yes	Comment : Number of messages in stack
x_msg_data	OUT	VARCHAR2	Yes	Comment : Message text if x_msg_count = 1

Other Validations

You cannot change enabled_flag to 'N' if the default flag of the record is 'Y'.

Tax Assignment APIs

PL/SQL Package Name: HZ_TAX_ASSIGNMENT_V2PUB

Java Class Name: HzTaxAssignmentV2Pub

Create Location Assignment API

Description

This routine is used to populate loc_id after a location is created or modified. It also creates or updates a record in the HZ_LOCATIONS_PROFILES table. This is called when a customer account site is created. This is to ensure tax validation. It creates a record in the HZ_LOC_ASSIGNMENTS table with location_id (foreign key to HZ_LOCATIONS.LOCATION_ID), loc_id (foreign key to AR_LOCATION_COMBINATIONS.LOCATION_ID) and org_id (operating unit identifier).

PL/SQL Procedure

PROCEDURE create_loc_assignment(
    p_init_msg_list                      IN         VARCHAR2:= FND_API.G_FALSE,
    p_location_id                        IN         NUMBER, 
    p_lock_flag                          IN         VARCHAR2:= FND_API.G_FALSE,
    p_created_by_module                  IN         VARCHAR2,
    p_application_id                     IN         NUMBER,
    x_return_status                      IN OUT     VARCHAR2, 
    x_msg_count                          OUT        NUMBER,
    x_msg_data                           OUT        VARCHAR2,
    x_loc_id                             OUT        NUMBER
)

Java Method

public static void createLocAssignment(
    OracleConnection_connection,
    String                               p_init_msg_list,
    BigDecimal                           p_location_id,
    String                               p_lock_flag,
    String                               p_created_by_module,
    BigDecimal                           p_application_id,
    String [ ]                           x_return_status,
    BigDecimal [ ]                       x_msg_count,
    String [ ]                           x_msg_data,
    BigDecimal [ ]                       x_loc_id
) throws SQLException;

Parameter Description and Validation

The following table lists information about the parameters in the Create Location Assignment API. The table includes the parameter names, the type of each parameter, the data type of each parameter, the necessity of the parameter, and other information about the parameter such as validation, defaults, and other comments.

Parameter Name	Type	Data Type	Required	Validation Default Comment
p_location_id	IN	NUMBER	Yes	Validation: Mandatory attribute valid location_id from the HZ_LOCATIONS table should be passed in.
p_lock_flag	IN	NUMBER	No
p_created_by_module	IN	VARCHAR2	Yes	Validation: Mandatory attribute. Validated against AR lookup type HZ_CREATED_BY_MODULE.
p_application_id	IN	NUMBER	No	Comment: Text indicating which module initiated the creation of a record.
x_loc_id	OUT	NUMBER	No	Comment: Returns loc_id generated

Other Validations

Org context should be set while calling this API

Update Location Assignment API

Description

This routine can be called to populate loc_id after a location is created or modified. It also creates or updates a record in the HZ_LOCATIONS_PROFILES table. This to ensure tax validation. It creates a record in the HZ_LOC_ASSIGNMENTS table with location_id (foreign key to HZ_LOCATIONS), loc_id (foreign key to AR_LOCATION_COMBINATIONS) and org_id (operating unit identifier).

PL/SQL Procedure

PROCEDURE update_loc_assignment(
    p_init_msg_list                     IN         VARCHAR2:= FND_API.G_FALSE,
    p_location_id                       IN         NUMBER,
    p_lock_flag                         IN         VARCHAR2:= FND_API.G_TRUE,
    p_created_by_module                 IN         VARCHAR2,
    p_application_id                    IN         NUMBER,
    x_return_status                     IN OUT     VARCHAR2, 
    x_msg_count                         OUT        NUMBER,
    x_msg_data                          OUT        VARCHAR2,
    x_loc_id                            OUT        NUMBER
)

Java Method

public static void updateLocAssignment(
    OracleConnection_connection,
    String                               p_init_msg_list,
    BigDecimal                           p_location_id,
    String                               p_lock_flag,
    String                               p_created_by_module,
    BigDecimal                           p_application_id,
    String [ ]                           x_return_status,
    BigDecimal [ ]                       x_msg_count,
    String [ ]                           x_msg_data,
    BigDecimal [ ]                       x_loc_id
) throws SQLException;

Note: p_lock_flag indicates whether to lock location record with p_location_id passed in. If value equals to HzConstant.getGTrue(), we will try to lock location record before we proceed.

Parameter Description and Validation

The following table lists information about the parameters in the Update Location Assignment API. The table includes the parameter names, the type of each parameter, the data type of each parameter, the necessity of the parameter, and other information about the parameter such as validation, defaults, and other comments,

Parameter Name	Type	Data Type	Required	Validation, Default, Comment
p_location_id	IN	NUMBER	Yes	Validation: Mandatory attribute Valid location_id from the HZ_LOCATIONS table should be passed in.
p_lock_flag	IN	NUMBER	No
p_created_by_module	IN	VARCHAR2	No	Validation: Non updateable if value exists, else validated against AR lookup type HZ_CREATED_BY_MODULE.
p_application_id	IN	NUMBER	No	Validation: Non updateable if value exists.
x_loc_id	OUT	NUMBER	No	Comment: Returns loc_id generated.