Package gb_address

Package gb_address_rules

Field level business rules validation for the ADDRESS API (GB_ADDRESS).

Program units

p_register_entity	Registers the attribute/value pairs to the message cache for delete operations.
p_register_entity	Registers the attribute/value pairs to the message cache for create and update operations.
p_validate	Validates address items

p_register_entity

PROCEDURE p_register_entity(p_operation_type     NUMBER,
                            p_internal_record_id gb_common.internal_record_id_type)

Registers the attribute/value pairs to the message cache for delete operations.
This signature uses the ROWID only, for the delete operations

Parameters

	p_operation_type	Messaging operation type. Use one of gb_event.CREATE_OPERATION, gb_event.UPDATE_OPERATION gb_event.DELETE_OPERATION.
	p_internal_record_id	Database ROWID

p_register_entity

PROCEDURE p_register_entity(p_operation_type     NUMBER,
                            p_pidm               spraddr.spraddr_pidm%TYPE,
                            p_atyp_code          spraddr.spraddr_atyp_code%TYPE,
                            p_seqno              spraddr.spraddr_seqno%TYPE,
                            p_from_date          spraddr.spraddr_from_date%TYPE,
                            p_to_date            spraddr.spraddr_to_date%TYPE,
                            p_street_line1       spraddr.spraddr_street_line1%TYPE,
                            p_street_line2       spraddr.spraddr_street_line2%TYPE,
                            p_street_line3       spraddr.spraddr_street_line3%TYPE,
                            p_city               spraddr.spraddr_city%TYPE,
                            p_stat_code          spraddr.spraddr_stat_code%TYPE,
                            p_zip                spraddr.spraddr_zip%TYPE,
                            p_cnty_code          spraddr.spraddr_cnty_code%TYPE,
                            p_natn_code          spraddr.spraddr_natn_code%TYPE,
                            p_status_ind         spraddr.spraddr_status_ind%TYPE,
                            p_user               spraddr.spraddr_user%TYPE,
                            p_asrc_code          spraddr.spraddr_asrc_code%TYPE,
                            p_delivery_point     spraddr.spraddr_delivery_point%TYPE,
                            p_correction_digit   spraddr.spraddr_correction_digit%TYPE,
                            p_carrier_route      spraddr.spraddr_carrier_route%TYPE,
                            p_gst_tax_id         spraddr.spraddr_gst_tax_id%TYPE,
                            p_reviewed_ind       spraddr.spraddr_reviewed_ind%TYPE,
                            p_reviewed_user      spraddr.spraddr_reviewed_user%TYPE,
                            p_data_origin        spraddr.spraddr_data_origin%TYPE,
                            p_ctry_code_phone    spraddr.spraddr_ctry_code_phone%TYPE,
                            p_house_number       spraddr.spraddr_house_number%TYPE,
                            p_street_line4       spraddr.spraddr_street_line4%TYPE,
                            p_internal_record_id gb_common.internal_record_id_type)

Registers the attribute/value pairs to the message cache for create and update operations.
This signature registers the all parameters

Parameters

p_operation_type

Messaging operation type. Use one of gb_event.CREATE_OPERATION, gb_event.UPDATE_OPERATION gb_event.DELETE_OPERATION.

p_pidm

Internal identification number of the person. NUMBER(8) Required Key

p_atyp_code

Type of address associated with the person. VARCHAR2(2) Required Key

p_seqno

Assigns an internal sequence number to each address type associated with the person. NUMBER(2) Required Key

p_from_date

Effective start date of the address associated with the person. DATE

p_to_date

Effective end date of the address associated with the person. DATE

p_street_line1

First line of the address associated with the person. VARCHAR2(75)

p_street_line2

Second line of the address associated with the person. VARCHAR2(75)

p_street_line3

Third line of the address associated with the person. VARCHAR2(75)

p_city

City associated with the address of the person. VARCHAR2(50) Required

p_stat_code

State associated with the address of the person. VARCHAR2(3)

p_zip

ZIP code associated with the address of the person. VARCHAR2(30)

p_cnty_code

County associated with the address of the person. VARCHAR2(5)

p_natn_code

Nation/Country associated with the address of the person. VARCHAR2(5)

p_status_ind

Identifies if the address information is inactive. VARCHAR2(1) Key

	NULL	Active
	I	Inactive

p_user

The ID for the User who created or last updated the record. VARCHAR2(30)

p_asrc_code

Address source code. VARCHAR2(4)

p_delivery_point

Designates the delivery point for mail as established by the Postal Service. NUMBER(2)

p_correction_digit

The Correction Digit field is defined by the Postal Service and is used to determine the digits used for delivery related to the ZIP code. NUMBER(1)

p_carrier_route

The addresses to which a carrier delivers mail. In common usage, carrier route includes city routes, rural routes, highway contract routes, post office box sections, and general delivery units. VARCHAR2(4)

p_gst_tax_id

Goods and Services Tax Identification of vendor at this address. VARCHAR2(15)

p_reviewed_ind

Reviewed Indicator, Y indicates the address has been reviewed. VARCHAR2(1)

p_reviewed_user

Reviewed User, indicates the user who reviewed the address. VARCHAR2(30)

p_data_origin

Source system that generated the data. VARCHAR2(30)

p_ctry_code_phone

Country Code associated with telephone. VARCHAR2(04)

p_house_number

House Number associated with address. VARCHAR2(10)

p_street_line4

Fourth line of the address associated with the person. VARCHAR2(75)

p_internal_record_id

Database ROWID

p_validate

PROCEDURE p_validate(p_pidm             spraddr.spraddr_pidm%TYPE,
                     p_atyp_code        spraddr.spraddr_atyp_code%TYPE,
                     p_seqno            spraddr.spraddr_seqno%TYPE,
                     p_street_line1     spraddr.spraddr_street_line1%TYPE,
                     p_city             spraddr.spraddr_city%TYPE,
                     p_stat_code        spraddr.spraddr_stat_code%TYPE,
                     p_zip              spraddr.spraddr_zip%TYPE,
                     p_cnty_code        spraddr.spraddr_cnty_code%TYPE,
                     p_natn_code        spraddr.spraddr_natn_code%TYPE,
                     p_status_ind       spraddr.spraddr_status_ind%TYPE,
                     p_asrc_code        spraddr.spraddr_asrc_code%TYPE,
                     p_delivery_point   spraddr.spraddr_delivery_point%TYPE,
                     p_correction_digit spraddr.spraddr_correction_digit%TYPE,
                     p_reviewed_ind     spraddr.spraddr_reviewed_ind%TYPE,
                     p_reviewed_user    spraddr.spraddr_reviewed_user%TYPE,
                     p_ctry_code_phone  spraddr.spraddr_ctry_code_phone%TYPE DEFAULT NULL,
                     p_house_number     spraddr.spraddr_house_number%TYPE DEFAULT NULL,
                     p_street_line4     spraddr.spraddr_street_line4%TYPE DEFAULT NULL,
                     p_from_date        spraddr.spraddr_from_date%TYPE DEFAULT NULL,
                     p_to_date          spraddr.spraddr_to_date%TYPE DEFAULT NULL,
                     p_finance_rules    VARCHAR2 DEFAULT 'N')

Validates address items

Required values are checked, plus the following other checks are done.

PIDM is required and is valid.
Address type code is required and is valid (exists in the STVATYP table)
Address sequence is required.
If Reviewed indicator is not null, the reviewed user must be provided.
City is required.
If State is provided, then ZIP is required.
State code must exist in the STVSTAT table.
If ZIP is provided then state or nation is required.
If ZIP is null then State or Nation is required.
ZIP, State and Nation cannot all be null.
County code, if provided, must exist in the STVCNTY table.
Nation code, if provided, must exist in the STVNATN table.
Status indicator must be Null or 'I'.
Address source code, if provided, must exist in the STVASRC table.
Delivery point, if provided, must be between 0 and 99.
Correction digit, if provided, must be between 0 and 9.

Parameters

p_pidm

Internal identification number of the person. NUMBER(8) Required Key

p_atyp_code

Type of address associated with the person. VARCHAR2(2) Required Key

p_seqno

Assigns an internal sequence number to each address type associated with the person. NUMBER(2) Required Key

p_street_line1

First line of the address associated with the person. VARCHAR2(30)

p_city

City associated with the address of the person. VARCHAR2(20) Required

p_stat_code

State associated with the address of the person. VARCHAR2(3)

p_zip

ZIP code associated with the address of the person. VARCHAR2(10)

p_cnty_code

County associated with the address of the person. VARCHAR2(5)

p_natn_code

Nation/Country associated with the address of the person. VARCHAR2(5)

p_status_ind

Identifies if the address information is inactive. VARCHAR2(1) Key

	NULL	Active
	I	Inactive

p_asrc_code

Address source code. VARCHAR2(4)

p_delivery_point

Designates the delivery point for mail as established by the Postal Service. NUMBER(2)

p_correction_digit

The Correction Digit field is defined by the Postal Service and is used to determine the digits used for delivery related to the ZIP code. NUMBER(1)

p_reviewed_ind

Reviewed Indicator, Y indicates the address has been reviewed. VARCHAR2(1)

p_reviewed_user

The Oracle user ID of the person who reviewed the address. VARCHAR2(04)

p_ctry_code_phone

No current validation. VARCHAR2(30)

p_house_number

No current validation. VARCHAR2(10)

p_street_line4

No current validation. VARCHAR2(75)

p_from_date

Effective start date of the address associated with the person. DATE

p_to_date

Effective end date of the address associated with the person. DATE

p_finance_rules

If set to 'Y' then no date checking is done, and sequence number can be passed in. Default is 'N'.