User:Mmunch

Create Licensee

Overview

The creation of a licensee is a 4 part process which involves:

1. The creation of the Business Entity Record (TYPE="LICENSEE")
2. The creation of PRIMARY Address
3. the creation of Network records (related to Address record)
4. The creation of Relationships with a Controlling Company and other Licensees (if necessary)

Create Business Entity record (type="LICENSEE")

Create Record in GLB_BUE_BUSINESS_ENTITY table

Inputs

Name	Data Type	Constraints	Comments
R_BUE_BE_TYPE_ID	RAW(16)	PK	FK to GLB_BTP_BE_TYPE table where R_BTP_BE_TYPE_ID is equal to that of "LICENSEE"
D_BUE_CREATION_TIMESTAMP	DATE	FK	Date/Time record was created - preserve original date/time of migrated records
V_BUE_NAME	VARCHAR2(2000)	.	.
N_BUE_PREFERRED_LANGUAGE_ID	NUMBER(16)	FK	Default to id corresponding to "ENG"
R_BUE_PREFERRED_TIMEZONE_ID	RAW(16)	FK	Default to id corresponding to "PST"
R_BUE_EXTERNAL_SYS_ID	RAW(16)	.	ID corresponding to the system the record came from (database name)
R_BUE_EXTERNAL_SYS_SITE_ID	RAW(16)	.	.
V_BUE_STATUS	VARCHAR2(32)	.	Default to "ACTIVE"

Outputs:

IF Success

Name	Data Type	Constraints	Comments
R_BUE_BE_ID	RAW(16)	PK	.

IF Err

Error message

Create Business Entity Address Record (type="PRIMARY")

Create Licensee PRIMARY Address record in GLB_BEA_BE_ADDRESS

Inputs

Name	Data Type	Constraints	Comments
R_BEA_BE_ID	RAW(16)	FK	BE ID of Licensee
V_BEA_STREET	VARCHAR2(32)	.	.
V_BEA_CITY	VARCHAR2(32)	.	.
R_BEA_REGION_ID	RAW(16)	FK	FK to State/Province in GLB_RGN_REGION
V_BEA_ZIP	VARCHAR2(2000)	.	.
R_BEA_COUNTRY_ID	RAW(16)	FK	FK to Country_ID in GLB_CNT_COUNTRY table
V_BEA_CONTACT_NAME	VARCHAR2(32)	.	.
V_BEA_CONTACT_COMPANY	VARCHAR2(32)	.	.
R_BEA_ADDRESS_TYPE_ID	RAW(16)	FK	FK to GLB_ADT_ADDRESS_TYPE table where ID corresponds to "PRIMARY"

Outputs

If Success:

Name	Data Type	Constraints	Comments
R_BEA_BE_ADDRESS_ID	RAW(16)	PK	.

IF Err:

Error message

Create Business Entity Network Records and relate records to Address

Create Licensee Contact Phone and Licensee Contact Fax records in GLB_BNW_BE_NETWORK and related entries in GLB_BNR_BE_ADDRESS_NET_REL

At creation, a licensee has requires 2 types of entries in the Network table (a "LICENSEE CONTACT PHONE" and a "LICENSEE CONTACT FAX"), thus it requires two entries in the GLB_BNR_BE_ADDRESS_NET_REL table.

The creation of Licensee Network Records is a two part process:

1. Create Network record in GLB_BNW_BE_NETWORK
2. Create record in GLB_BNR_BE_ADDRESS_NET_REL to relate Network record back to Address

Create "LICENSEE CONTACT PHONE"

Licensee Contact Phone Inputs

Name	Data Type	Constraints	Comments
R_BNW_NETWORK_TYPE_ID	RAW(16)	FK	TYPE_ID corresponding to "LICENSEE CONTACT PHONE" in GLB_NTY_NETWORK_TYPE table
R_BNW_BE_ID	RAW(16)	FK	ID of Business Entity
V_BNW_NETWORK_NAME	VARCHAR2(2000)	.	the phone number
V_BNW_STATUS	VARCHAR2(32)	.	ACTIVE

Outputs

If Success:

Name	Data Type	Constraints	Comments
N_BNW_BE_NETWORK_ID	RAW(16)	PK	.

IF Err:

Error message

Relate Network Records to Address

Input

Name	Data Type	Primary	Comments
R_BNR_BE_ADDRESS_ID	RAW(16)	PK, FK	PK of address record related to a network record (from output above)
N_BNR_BE_NETWORK_ID	NUMBER(16)	PK, FK	PK of Network record related to an Address record

Output:

Err Message if applicable

Create "LICENSEE CONTACT FAX"

Repeat Create "LICENSEE CONTACT PHONE" process above to create LICENSEE CONTACT FAX and related record to Address

Relate Network Record to Address

Repeat Relate Network Records to Address process described above to relate Contact Fax to Primary Address.

Create Licensee Relationships

A business entity has 2 mandatory relationship TYPES recorded in the GLB_BER_BE_REL table

1. CONTROLLED BY a Business Entity of TYPE="CONTROLLING COMPANY"
2. PROCESSED BY a Business of TYPE="CONTROLLING COMPANY" or "LICENSEE" (Licensee IEE processes its own Month end statements and therefore is processed by itself)

A licensee may also have a relationship type "SUB-LICENSEE OF"

Create CONTROLLED BY relationship

Precondition: Controlling Company must exist

Business Rule: R_BER_S_BE_ID must be FK to BE of type "CONTROLLING COMPANY"

Input

Name	Data Type	Constraints	Comments
R_BER_P_BE_ID	RAW(16)	FK	ID of the new Licensee
R_BER_S_BE_ID	RAW(16)	FK	ID of the applicable CONTROLLING COMPANY (SB or WGI)
R_BER_PART_REL_TYPE_ID	RAW(16)	FK	ID of the Relationship type = "CONTROLLED BY"

Output Success or Error message

Create PROCESSED BY relationship

Precondition: Processing Controlling Company or Licenee must exist

Input

Name	Data Type	Constraints	Comments
R_BER_P_BE_ID	RAW(16)	FK	ID of the new Licensee
R_BER_S_BE_ID	RAW(16)	FK	ID of the applicable CONTROLLING COMPANY (SB or WGI) or "LICENSEE"
R_BER_PART_REL_TYPE_ID	RAW(16)	FK	ID of the Relationship type = "PROCESSED BY"

Output

Success or Error message

Create is SUB-LICENSEE OF relationship (if Applicable)

Precondition: Parent Licensee must exist

Input

Name	Data Type	Constraints	Comments
R_BER_P_BE_ID	RAW(16)	FK	ID of the new Licensee
R_BER_S_BE_ID	RAW(16)	FK	ID of the "LICENSEE" this sub-licensee reports to
R_BER_PART_REL_TYPE_ID	RAW(16)	FK	ID of the Relationship type = "SUB-LICENSEE OF"

Output

Success or Error message