User:Mmunch
Create Licensee
Overview
[edit]The creation of a licensee is a 4 part process which involves:
- The creation of the Business Entity Record (TYPE="LICENSEE")
- The creation of PRIMARY Address
- the creation of Network records (related to Address record)
- The creation of Relationships with a Controlling Company and other Licensees (if necessary)
Create Business Entity record (type="LICENSEE")
[edit]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")
[edit]Create Licensee PRIMARY Address record in GLB_BEA_BE_ADDRESS
[edit]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
[edit]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:
- Create Network record in GLB_BNW_BE_NETWORK
- Create record in GLB_BNR_BE_ADDRESS_NET_REL to relate Network record back to Address
Create "LICENSEE CONTACT PHONE"
[edit]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
[edit]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"
[edit]Repeat Create "LICENSEE CONTACT PHONE" process above to create LICENSEE CONTACT FAX and related record to Address
Relate Network Record to Address
[edit]Repeat Relate Network Records to Address process described above to relate Contact Fax to Primary Address.
Create Licensee Relationships
[edit]A business entity has 2 mandatory relationship TYPES recorded in the GLB_BER_BE_REL table
- CONTROLLED BY a Business Entity of TYPE="CONTROLLING COMPANY"
- 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
[edit]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
[edit]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)
[edit]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