== IDENTITY ==
import java.io.Serializable;
public interface Identity<T extends Serializable> extends Serializable{
T getId();
void setId(T id);
}
==== ABSTRACT IDENTITY ===
import java.io.Serializable;
public abstract class AbstractIdentity<T extends Serializable> implements Identity<T> {
protected T id;
public void setId(T id) {
this.id = id;
}
@Override
public int hashCode(){
return id == null ? 32 : id.hashCode();
}
@Override
public boolean equals(Object other){
if(this == other)
return true;
if(other == null)
return false;
if(this.getClass() != other.getClass())
return false;
Identity<T> oIdentity = (Identity<T>)other;
if(oIdentity.getId() == null && this.getId() == null){
return true;
}
return this.id != null ? this.id.equals(oIdentity.getId())
: oIdentity.getId().equals(this.id);
}
}
==== LANGUAGE ENUM ===
public enum Language {
en, de;
}
==== PERSONE ENTITY =====
import javax.persistence.*;
@Entity
@Table(name = "PERSON_INFO")
public class PersonInfo extends AbstractIdentity<Long>{
private String firstName;
private String lastName;
private String email;
private String phone;
private Language language;
private UserProfile user;
@Id
@SequenceGenerator(name = "PERSON_INFO_ID_GENERATOR", sequenceName = "SEQ_PERSON_INFO", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "PERSON_INFO_ID_GENERATOR")
@Override
public Long getId() {
return id;
}
@Column(name = "FIRST_NAME", nullable = false)
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
@Column(name = "LAST_NAME", nullable = false)
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
@Column(name = "EMAIL", nullable = false, unique = true)
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Column(name = "LANGUAGE", length = 2)
@Enumerated(EnumType.STRING)
public Language getLanguage() {
return language;
}
public void setLanguage(Language language) {
this.language = language;
}
@Column(name = "PHONE")
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@OneToOne(mappedBy = "personInfo", fetch = FetchType.EAGER)
public UserProfile getUser() {
return user;
}
public void setUser(UserProfile user) {
this.user = user;
}
}
==== SQL MOCK ===
--insert into TEST_BEAN(id ,field1, field2, intField) VALUES (SEQ_USER.nextval, 'string123','string1234', 123);
--####################AUTH###########################################
--Entitlements
insert into ENTITLEMENT(id, name ) VALUES (1, 'EMIR_Perm_COBAdmin');
insert into ENTITLEMENT(id, name ) VALUES (2, 'EMIR_Perm_COBUser');
insert into ENTITLEMENT(id, name ) VALUES (3, 'EMIR_Perm_Client');
insert into ENTITLEMENT(id, name ) VALUES (4, 'EMIR_Perm_RelationshipManagers');
--Permissions
insert into PERMISSION(id, name) VALUES (1, 'viewAllClients');
insert into PERMISSION(id, name) VALUES (2, 'viewAssociatedClients');
insert into PERMISSION(id, name) VALUES (3, 'viewCOBDashboard');
insert into PERMISSION(id, name) VALUES (4, 'viewAssignedToMeRequests');
insert into PERMISSION(id, name) VALUES (5, 'viewAssignedToMyTeamRequests');
insert into PERMISSION(id, name) VALUES (6, 'viewAllOpenedRequests');
insert into PERMISSION(id, name) VALUES (7, 'viewAllClosedRequests');
insert into PERMISSION(id, name) VALUES (8, 'viewCRdsImport');
insert into PERMISSION(id, name) VALUES (9, 'viewDtccImport');
insert into PERMISSION(id, name) VALUES (10, 'viewDistribution');
insert into PERMISSION(id, name) VALUES (11, 'viewCRdsMapping');
insert into PERMISSION(id, name) VALUES (12, 'viewSettings');
insert into PERMISSION(id, name) VALUES (13, 'viewUsers');
insert into PERMISSION(id, name) VALUES (14, 'viewMis');
--COBAdmin
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (1, 1);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (1, 3);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (1, 4);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (1, 5);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (1, 6);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (1, 7);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (1, 8);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (1, 9);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (1, 10);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (1, 11);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (1, 12);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (1, 13);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (1, 14);
--COBUser
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (2, 1);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (2, 3);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (2, 4);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (2, 5);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (2, 6);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (2, 7);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (2, 13);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (2, 14);
--RM
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (4, 2);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (4, 4);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (4, 5);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (4, 6);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (4, 7);
--Client
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (3, 2);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (3, 4);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (3, 5);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (3, 6);
insert into ENTITLEMENT_PERMISSION(ENTITLEMENT_ID, PERMISSION_ID) VALUES (3, 7);
---------##################STATIC DATA####################---------------
insert into COUNTRY(CODE, NAME) VALUES ('UK', 'United Kingdom');
insert into COUNTRY(CODE, NAME) VALUES ('DE', 'Germany');
insert into PRODUCT_LINE(ID, NAME) VALUES (1, 'AWM');
insert into PRODUCT_LINE(ID, NAME) VALUES (2, 'CMTS');
insert into PRODUCT_LINE(ID, NAME) VALUES (3, 'CMTS / ETD');
insert into PRODUCT_LINE(ID, NAME) VALUES (4, 'CMTS Czech Republic');
insert into PRODUCT_LINE(ID, NAME) VALUES (5, 'CMTS Italy');
insert into PRODUCT_LINE(ID, NAME) VALUES (6, 'CMTS Poland');
insert into PRODUCT_LINE(ID, NAME) VALUES (7, 'CMTS Spain');
insert into PRODUCT_LINE(ID, NAME) VALUES (8, 'Commodities');
insert into PRODUCT_LINE(ID, NAME) VALUES (9, 'ETD');
insert into PRODUCT_LINE(ID, NAME) VALUES (11, 'FXPB');
insert into PRODUCT_LINE(ID, NAME) VALUES (12, 'Germany ETD');
insert into PRODUCT_LINE(ID, NAME) VALUES (13, 'Germany OTC');
insert into PRODUCT_LINE(ID, NAME) VALUES (14, 'Germany OTC & ETD');
insert into PRODUCT_LINE(ID, NAME) VALUES (15, 'GPF');
insert into PRODUCT_LINE(ID, NAME) VALUES (16, 'GPF (LD)');
insert into PRODUCT_LINE(ID, NAME) VALUES (17, 'GTB');
insert into PRODUCT_LINE(ID, NAME) VALUES (18, 'ICG');
insert into PRODUCT_LINE(ID, NAME) VALUES (19, 'ICG (GPF)');
insert into PRODUCT_LINE(ID, NAME) VALUES (20, 'ICG (LD)');
insert into PRODUCT_LINE(ID, NAME) VALUES (21, 'LD');
insert into PRODUCT_LINE(ID, NAME) VALUES (22, 'LD & GPF (LD)');
insert into PRODUCT_LINE(ID, NAME) VALUES (23, 'LD (GPF)');
insert into PRODUCT_LINE(ID, NAME) VALUES (24, 'OTC');
insert into PRODUCT_LINE(ID, NAME) VALUES (25, 'Unknown');
insert into TEAM(ID, NAME) VALUES (1, 'UK');
insert into TEAM(ID, NAME) VALUES (2, 'Germany - OTC & ETD');
insert into TEAM(ID, NAME) VALUES (3, 'Czech Republic - CMTS');
insert into TEAM(ID, NAME) VALUES (4, 'Germany - CMTS');
insert into TEAM(ID, NAME) VALUES (5, 'Italy - CMTS');
insert into TEAM(ID, NAME) VALUES (6, 'Poland - CMTS');
insert into TEAM(ID, NAME) VALUES (7, 'Spain - CMTS');
insert into PRIORITY(ID, NAME) VALUES (1, 'ICG 50');
insert into PRIORITY(ID, NAME) VALUES (2, 'Platinum');
insert into PRIORITY(ID, NAME) VALUES (3, 'No');
insert into COUNTERPARTY_IDENTIFIER_TYPE(ID, NAME) VALUES (1, 'DBAG Clearing Ledger');
insert into COUNTERPARTY_IDENTIFIER_TYPE(ID, NAME) VALUES (2, 'Alert Acronym and Access Code');
insert into COUNTERPARTY_IDENTIFIER_TYPE(ID, NAME) VALUES (3, 'Coded Account Number');
insert into COUNTERPARTY_IDENTIFIER_TYPE(ID, NAME) VALUES (4, 'Account Names');
------------CLIENTS
select SEQ_LEGAL_ENTITY.nextval from dual;
insert into LEGAL_ENTITY(ID, LEGAL_TYPE, CRDS_ID, LEI, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY_CODE)
VALUES (SEQ_LEGAL_ENTITY.currval, 'client', 1111, 'asdfghjklq0111111111', 'C Legal 1', 'London addr1', 'London addr2', 'London addr3', 'London addr4', 'City 123', 'State 123', 'ZIP_CODE 1', 'UK');
insert into CLIENT_LEGAL_ENTITY(ID, ONBOARDING_STATUS, PRODUCT_LINE_ID, RESTRICTED_CONTACT, TEAM_ID, PRINCIPAL_AGENT, PRIORITY_ID)
VALUES (SEQ_LEGAL_ENTITY.currval, 'PARTIALLY_ONBOARDED', 1, 1, 5, 'PRINCIPAL', 1);
insert into CLIENT_CONTACT(ID, FIRST_NAME, LAST_NAME, ADDRESS, TELEPHONE, FAX, LANGUAGE, POSITION, SEND_CONFIDENTIAL, CLIENT_ID)
VALUES (SEQ_CLIENT_CONTACT.nextval, 'FirstN12', 'LastN12', 'London 123', '+40899', '+40899', 'uk', 'manager', 0, SEQ_LEGAL_ENTITY.currval);
insert into CLIENT_CONTACT(ID, FIRST_NAME, LAST_NAME, ADDRESS, TELEPHONE, FAX, LANGUAGE, POSITION, SEND_CONFIDENTIAL, CLIENT_ID)
VALUES (SEQ_CLIENT_CONTACT.nextval, 'FirstN', 'LastN', 'London 3', '+4089902', '+4089903', 'uk', 'manager', 0, SEQ_LEGAL_ENTITY.currval);
select SEQ_LEGAL_ENTITY.nextval from dual;
insert into LEGAL_ENTITY(ID, LEGAL_TYPE, CRDS_ID, LEI, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY_CODE)
VALUES (SEQ_LEGAL_ENTITY.currval, 'client', 1112, 'asdfghjklq0111111112', 'C Legal 2', 'London addr1', 'London addr23', 'London addr33', 'London addr4', 'City 1233', 'State 1233', 'ZIP_CODE 3', 'UK');
insert into CLIENT_LEGAL_ENTITY(ID, PRODUCT_LINE_ID) VALUES (SEQ_LEGAL_ENTITY.currval, 5);
select SEQ_LEGAL_ENTITY.nextval from dual;
insert into LEGAL_ENTITY(ID, LEGAL_TYPE, CRDS_ID, LEI, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY_CODE)
VALUES (SEQ_LEGAL_ENTITY.currval, 'client', 1113, 'asdfghjklq0111111113', 'C Legal 3', 'London addr14', 'London addr24', 'London addr34', 'London addr44', 'City 1234', 'State 1234', 'ZIP_CODE 4', 'UK');
insert into CLIENT_LEGAL_ENTITY(ID) VALUES (SEQ_LEGAL_ENTITY.currval);
select SEQ_LEGAL_ENTITY.nextval from dual;
insert into LEGAL_ENTITY(ID, LEGAL_TYPE, CRDS_ID, LEI, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY_CODE)
VALUES (SEQ_LEGAL_ENTITY.currval, 'client', 1114, 'asdfghjklq0111111114', 'C Legal 4', 'London addr1', 'London addr2', 'London addr3', 'London addr4', 'City 123', 'State 123', 'ZIP_CODE 1', 'DE');
insert into CLIENT_LEGAL_ENTITY(ID) VALUES (SEQ_LEGAL_ENTITY.currval);
select SEQ_LEGAL_ENTITY.nextval from dual;
insert into LEGAL_ENTITY(ID, LEGAL_TYPE, CRDS_ID, LEI, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY_CODE)
VALUES (SEQ_LEGAL_ENTITY.currval, 'client', 1115, 'asdfghjklq0111111115', 'C Legal 5', 'London addr1', 'London addr25', 'London addr35', 'London addr45', 'City 1235', 'State 1235', 'ZIP_CODE 5', 'UK');
insert into CLIENT_LEGAL_ENTITY(ID) VALUES (SEQ_LEGAL_ENTITY.currval);
select SEQ_LEGAL_ENTITY.nextval from dual;
insert into LEGAL_ENTITY(ID, LEGAL_TYPE, CRDS_ID, LEI, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY_CODE)
VALUES (SEQ_LEGAL_ENTITY.currval, 'client', 1116, 'asdfghjklq0111111116', 'C Legal 6', 'London addr1', 'London addr2', 'London addr3', 'London addr4', 'City 123', 'State 123', 'ZIP_CODE 1', 'DE');
insert into CLIENT_LEGAL_ENTITY(ID) VALUES (SEQ_LEGAL_ENTITY.currval);
select SEQ_LEGAL_ENTITY.nextval from dual;
insert into LEGAL_ENTITY(ID, LEGAL_TYPE, CRDS_ID, LEI, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY_CODE)
VALUES (SEQ_LEGAL_ENTITY.currval, 'client', 1117, 'asdfghjklq0111111117', 'C Legal 7', 'London addr1', 'London addr2', 'London addr3', 'London addr4', 'City 123sdd', 'State 123qwer', 'ZIP_CODE 3', 'UK');
insert into CLIENT_LEGAL_ENTITY(ID) VALUES (SEQ_LEGAL_ENTITY.currval);
select SEQ_LEGAL_ENTITY.nextval from dual;
insert into LEGAL_ENTITY(ID, LEGAL_TYPE, CRDS_ID, LEI, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY_CODE)
VALUES (SEQ_LEGAL_ENTITY.currval, 'client', 1118, 'asdfghjklq0111111118', 'C Legal 8', 'London addr1', 'London addr2', 'London addr3', 'London addr4', 'City 123', 'State 123', 'ZIP_CODE 1', 'DE');
insert into CLIENT_LEGAL_ENTITY(ID) VALUES (SEQ_LEGAL_ENTITY.currval);
select SEQ_LEGAL_ENTITY.nextval from dual;
insert into LEGAL_ENTITY(ID, LEGAL_TYPE, CRDS_ID, LEI, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY_CODE)
VALUES (SEQ_LEGAL_ENTITY.currval, 'client', 1119, 'asdfghjklq0111111119', 'C Legal 9', 'London addr1as', 'London addr12342', 'London addr1233', 'London addr4', 'City 123', 'State 123', 'ZIP_CODE 1', 'UK');
insert into CLIENT_LEGAL_ENTITY(ID) VALUES (SEQ_LEGAL_ENTITY.currval);
select SEQ_LEGAL_ENTITY.nextval from dual;
insert into LEGAL_ENTITY(ID, LEGAL_TYPE, CRDS_ID, LEI, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY_CODE)
VALUES (SEQ_LEGAL_ENTITY.currval, 'client', 1110, 'asdfghjklq0111111110', 'C Legal 10', 'London addr1', 'London addr2', 'London addr3', 'London addr4', 'City 123', 'State 123', 'ZIP_CODE 1', 'UK');
insert into CLIENT_LEGAL_ENTITY(ID) VALUES (SEQ_LEGAL_ENTITY.currval);
--counter parties
---------------------------------------------------
select SEQ_LEGAL_ENTITY.nextval from dual;
insert into LEGAL_ENTITY(ID, LEGAL_TYPE, CRDS_ID, LEI, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY_CODE)
VALUES (SEQ_LEGAL_ENTITY.currval, 'counterparty', 2211, 'counterparty55555551', 'Counter party 1', 'London addr1', 'London addr2', 'London addr3', 'London addr4', 'City 123', 'State 123', 'ZIP_CODE 1', 'UK');
insert into COUNTERPARTY_LEGAL_ENTITY(ID, IS_DTCC, IS_RATES, IS_CREDIT, IS_EQUITIES, IS_FX, IS_COMMODITIES, IS_EDT, IS_OTC_SV, EDT_SV)
VALUES (SEQ_LEGAL_ENTITY.currval, 0, 0, 0, 0, 0, 0, 0, 0, 1);
select SEQ_COUNTERPARTY_IDENTIFIER.nextval from dual;
insert into COUNTERPARTY_IDENTIFIER(ID, CP_IDENTIFIER_TYPE_SID, VALUE, COUNTERPARTY_SID)
VALUES (SEQ_COUNTERPARTY_IDENTIFIER.currval, 1, 'value 123', SEQ_LEGAL_ENTITY.currval);
select SEQ_COUNTERPARTY_IDENTIFIER.nextval from dual;
insert into COUNTERPARTY_IDENTIFIER(ID, CP_IDENTIFIER_TYPE_SID, VALUE, COUNTERPARTY_SID)
VALUES (SEQ_COUNTERPARTY_IDENTIFIER.currval, 2, 'value 1233', SEQ_LEGAL_ENTITY.currval);
select SEQ_COUNTERPARTY_IDENTIFIER.nextval from dual;
insert into COUNTERPARTY_IDENTIFIER(ID, CP_IDENTIFIER_TYPE_SID, VALUE, COUNTERPARTY_SID)
VALUES (SEQ_COUNTERPARTY_IDENTIFIER.currval, 4, 'value 12334', SEQ_LEGAL_ENTITY.currval);
insert into LINK_ONBOARDED_CP(CLIENT_SID, COUNTERPARTY_SID) VALUES (1, SEQ_LEGAL_ENTITY.currval);
-----------------------------------------------------
select SEQ_LEGAL_ENTITY.nextval from dual;
insert into LEGAL_ENTITY(ID, LEGAL_TYPE, CRDS_ID, LEI, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY_CODE)
VALUES (SEQ_LEGAL_ENTITY.currval, 'counterparty', 2212, 'counterparty55555552', 'Counter party 2', 'London addr1', 'London addr2', 'London addr3', 'London addr4', 'City 123', 'State 123', 'ZIP_CODE 1', 'UK');
insert into COUNTERPARTY_LEGAL_ENTITY(ID, IS_DTCC, IS_RATES, IS_CREDIT, IS_EQUITIES, IS_FX, IS_COMMODITIES, IS_EDT, IS_OTC_SV, EDT_SV)
VALUES (SEQ_LEGAL_ENTITY.currval , 0, 0, 0, 0, 0, 0, 1, 1, 1);
insert into LINK_ONBOARDED_CP(CLIENT_SID, COUNTERPARTY_SID) VALUES (1, SEQ_LEGAL_ENTITY.currval);
-----------------------------------------------------
select SEQ_LEGAL_ENTITY.nextval from dual;
insert into LEGAL_ENTITY(ID, LEGAL_TYPE, CRDS_ID, LEI, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY_CODE)
VALUES (SEQ_LEGAL_ENTITY.currval, 'counterparty', 2213, 'counterparty55555553', 'Counter party 3', 'London addr1', 'London addr2', 'London addr3', 'London addr4', 'City 123', 'State 123', 'ZIP_CODE 1', 'UK');
insert into COUNTERPARTY_LEGAL_ENTITY(ID, IS_DTCC, IS_RATES, IS_CREDIT, IS_EQUITIES, IS_FX, IS_COMMODITIES, IS_EDT, IS_OTC_SV, EDT_SV)
VALUES (SEQ_LEGAL_ENTITY.currval, 1, 1, 0, 0, 0, 0, 1, 0, 1);
insert into LINK_ONBOARDED_CP(CLIENT_SID, COUNTERPARTY_SID) VALUES (1, SEQ_LEGAL_ENTITY.currval);
----------------------------------------------------
select SEQ_LEGAL_ENTITY.nextval from dual;
insert into LEGAL_ENTITY(ID, LEGAL_TYPE, CRDS_ID, LEI, NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY_CODE)
VALUES (SEQ_LEGAL_ENTITY.currval, 'counterparty', 2213, 'counterparty55555554', 'Counter party 4', 'London addr13', 'London addr23', 'London addr34', 'London addr42', 'City 123231', 'State 1213', 'ZIP_CODE 1', 'UK');
insert into COUNTERPARTY_LEGAL_ENTITY(ID, IS_DTCC, IS_RATES, IS_CREDIT, IS_EQUITIES, IS_FX, IS_COMMODITIES, IS_EDT, IS_OTC_SV, EDT_SV)
VALUES (SEQ_LEGAL_ENTITY.currval, 1, 1, 0, 0, 0, 0, 1, 0, 1);
----------------------------------------------------
--profiles
insert into PERSON_INFO(ID, EMAIL, FIRST_NAME, LAST_NAME, PHONE, LANGUAGE)
VALUES (SEQ_PERSON_INFO.nextval, 'client1@gbsemir.com', 'First client1', 'Last client1', '+4444444', 'en');
insert into USER_PROFILE(ID, PERSON_ID, CONFIDENTIAL_OK) VALUES(SEQ_USER.nextval, SEQ_PERSON_INFO.currval, 1);
insert into USER_CLIENT(USER_ID, CLIENT_ID) VALUES (SEQ_USER.currval, 1);
insert into PERSON_INFO(ID, EMAIL, FIRST_NAME, LAST_NAME, PHONE, LANGUAGE)
VALUES (SEQ_PERSON_INFO.nextval, 'rm1@gbsemir.com', 'First RM1', 'Last RM1', '+5555555', 'en');
insert into USER_PROFILE(ID, PERSON_ID, CONFIDENTIAL_OK) VALUES(SEQ_USER.nextval, SEQ_PERSON_INFO.currval, 1);
insert into USER_CLIENT(USER_ID, CLIENT_ID) VALUES (SEQ_USER.currval, 1);
insert into USER_CLIENT(USER_ID, CLIENT_ID) VALUES (SEQ_USER.currval, 2);
insert into USER_CLIENT(USER_ID, CLIENT_ID) VALUES (SEQ_USER.currval, 3);
insert into USER_CLIENT(USER_ID, CLIENT_ID) VALUES (SEQ_USER.currval, 4);
insert into USER_CLIENT(USER_ID, CLIENT_ID) VALUES (SEQ_USER.currval, 5);
insert into USER_CLIENT(USER_ID, CLIENT_ID) VALUES (SEQ_USER.currval, 6);
=== EXAMPLKE OF SCHEMA-DDL.SQL =====
create table CLIENT_CONTACT (
id number(19,0) not null,
ADDRESS varchar2(50 char),
FAX varchar2(24 char),
FIRST_NAME varchar2(80 char),
LANGUAGE varchar2(24 char),
LAST_NAME varchar2(80 char),
POSITION varchar2(50 char),
SEND_CONFIDENTIAL number(1,0),
TELEPHONE varchar2(24 char),
CLIENT_ID number(19,0),
primary key (id)
);
create table CLIENT_LEGAL_ENTITY (
ONBOARDING_STATUS varchar2(30 char),
PRINCIPAL_AGENT varchar2(30 char),
RESTRICTED_CONTACT number(1, 0) default 1 not null,
id number(19,0) not null,
PRIORITY_ID number(19,0),
PRODUCT_LINE_ID number(19,0),
TEAM_ID number(19,0),
primary key (id)
);
create table COUNTERPARTY_IDENTIFIER (
id number(19,0) not null,
VALUE varchar2(255 char),
CP_IDENTIFIER_TYPE_SID number(19,0),
COUNTERPARTY_SID number(19,0),
primary key (id)
);
create table COUNTERPARTY_IDENTIFIER_TYPE (
id number(19,0) not null,
NAME varchar2(100 char) not null,
primary key (id)
);
create table COUNTERPARTY_LEGAL_ENTITY (
IS_COMMODITIES number(1,0),
IS_CREDIT number(1,0),
IS_DTCC number(1,0),
IS_EDT number(1,0),
EDT_SV number(1,0),
IS_EQUITIES number(1,0),
IS_FX number(1,0),
IS_OTC_SV number(1,0),
IS_RATES number(1,0),
id number(19,0) not null,
INSTITUTION_ID number(19,0),
PARENT_ID number(19,0),
primary key (id)
);
create table COUNTRY (
code varchar2(2 char) not null,
name varchar2(100 char) not null,
primary key (code)
);
create table ENTITLEMENT (
id number(19,0) not null,
name varchar2(100 char) not null,
primary key (id)
);
create table ENTITLEMENT_PERMISSION (
ENTITLEMENT_ID number(19,0) not null,
PERMISSION_ID number(19,0) not null
);
create table INSTITUTION_LEGAL_ENTITY (
SPECIFIC_FIELD varchar2(255 char),
id number(19,0) not null,
PARENT_ID number(19,0),
primary key (id)
);
create table LEGAL_ENTITY (
LEGAL_TYPE varchar2(31 char) not null,
id number(19,0) not null,
ADDRESS1 varchar2(100 char),
ADDRESS2 varchar2(100 char),
ADDRESS3 varchar2(100 char),
ADDRESS4 varchar2(100 char),
CITY varchar2(30 char),
CRDS_ID number(19,0) not null,
COMMENT varchar2(255 char),
LEI varchar2(20 char) not null,
LEI_VALID varchar2(255 char),
LEI_VALIDATED_BY varchar2(255 char),
NAME varchar2(255 char) not null,
PARAGON_ID varchar2(255 char),
SHORT_CODE varchar2(255 char),
STATE varchar2(100 char),
ZIP varchar2(255 char),
COUNTRY_CODE varchar2(2 char),
primary key (id)
);
create table LINK_ONBOARDED_CP (
CLIENT_SID number(19,0) not null,
COUNTERPARTY_SID number(19,0) not null
);
create table PERMISSION (
id number(19,0) not null,
name varchar2(100 char) not null,
primary key (id)
);
create table PERSON_INFO (
id number(19,0) not null,
EMAIL varchar2(255 char) not null,
FIRST_NAME varchar2(255 char) not null,
LANGUAGE varchar2(2 char),
LAST_NAME varchar2(255 char) not null,
PHONE varchar2(255 char),
primary key (id)
);
create table PRIORITY (
id number(19,0) not null,
NAME varchar2(30 char) not null,
primary key (id)
);
create table PRODUCT_LINE (
id number(19,0) not null,
NAME varchar2(30 char) not null,
primary key (id)
);
create table REQUEST (
id number(19,0) not null,
ACTIVE number(1,0),
DATE_COMPLETED timestamp,
DATE_INITIATED timestamp,
INITIATOR varchar2(255 char) not null,
PROCESS_TYPE varchar2(30 char),
STATUS varchar2(255 char),
version number(19,0),
CLIENT_ID number(19,0),
primary key (id)
);
create table TEAM (
id number(19,0) not null,
NAME varchar2(30 char) not null,
primary key (id)
);
create table USER_CLIENT (
USER_ID number(19,0) not null,
CLIENT_ID number(19,0) not null
);
create table USER_PROFILE (
id number(19,0) not null,
CONFIDENTIAL_OK number(1,0),
PERSON_ID number(19,0),
primary key (id)
);
alter table LEGAL_ENTITY
add constraint UK_le9d5nlm7eyjw0nc1movg1wpq unique (LEI);
alter table PERSON_INFO
add constraint UK_tnb2t5qlpgftqiy71tjgcq061 unique (EMAIL);
alter table USER_PROFILE
add constraint UK_lxednllfytn2kxu60yqrf58ue unique (PERSON_ID);
alter table CLIENT_CONTACT
add constraint FK_93jnip3vky6uman6ignr402lp
foreign key (CLIENT_ID)
references CLIENT_LEGAL_ENTITY;
alter table CLIENT_LEGAL_ENTITY
add constraint FK_aukqu6ts0irwmwapko1pnibd0
foreign key (PRIORITY_ID)
references PRIORITY;
alter table CLIENT_LEGAL_ENTITY
add constraint FK_jn8yu4kehq203d2pqg0n8e768
foreign key (PRODUCT_LINE_ID)
references PRODUCT_LINE;
alter table CLIENT_LEGAL_ENTITY
add constraint FK_o5chnc95t58hda1qcpx9j1nl9
foreign key (TEAM_ID)
references TEAM;
alter table CLIENT_LEGAL_ENTITY
add constraint FK_d9bj4sdht55xd5y1dq546010x
foreign key (id)
references LEGAL_ENTITY;
alter table COUNTERPARTY_IDENTIFIER
add constraint FK_awkvx81eesikdacnw9jvn25ho
foreign key (CP_IDENTIFIER_TYPE_SID)
references COUNTERPARTY_IDENTIFIER_TYPE;
alter table COUNTERPARTY_IDENTIFIER
add constraint FK_c3wpjr6dlvtc12q1nbopuwgrf
foreign key (COUNTERPARTY_SID)
references COUNTERPARTY_LEGAL_ENTITY;
alter table COUNTERPARTY_LEGAL_ENTITY
add constraint FK_2u2y8yxk9v6vdl2klmij6inw9
foreign key (id)
references LEGAL_ENTITY;
alter table COUNTERPARTY_LEGAL_ENTITY
add constraint FK_9aduaqvawmfkevi895fphoo4m
foreign key (INSTITUTION_ID)
references INSTITUTION_LEGAL_ENTITY;
alter table COUNTERPARTY_LEGAL_ENTITY
add constraint FK_oata93qfpbe4vm0qws9w62rmb
foreign key (PARENT_ID)
references CLIENT_LEGAL_ENTITY;
alter table ENTITLEMENT_PERMISSION
add constraint FK_a750k81frommyqu9rq29tglix
foreign key (PERMISSION_ID)
references PERMISSION;
alter table ENTITLEMENT_PERMISSION
add constraint FK_bxjc4busbl6g23na9hvrtwppp
foreign key (ENTITLEMENT_ID)
references ENTITLEMENT;
alter table INSTITUTION_LEGAL_ENTITY
add constraint FK_e0pig3guapeoe9o8mfc05to8w
foreign key (id)
references LEGAL_ENTITY;
alter table INSTITUTION_LEGAL_ENTITY
add constraint FK_kj81ore2wfkjjo87jepv39cq1
foreign key (PARENT_ID)
references CLIENT_LEGAL_ENTITY;
alter table LEGAL_ENTITY
add constraint FK_lva8ouqjj4t2sbh076bqwnfq9
foreign key (COUNTRY_CODE)
references COUNTRY;
alter table LINK_ONBOARDED_CP
add constraint FK_96gjyfnh57ryoq717jki0ekp1
foreign key (COUNTERPARTY_SID)
references COUNTERPARTY_LEGAL_ENTITY;
alter table LINK_ONBOARDED_CP
add constraint FK_9f2itrphgb509ix31d5sfkjlj
foreign key (CLIENT_SID)
references CLIENT_LEGAL_ENTITY;
alter table REQUEST
add constraint FK_nhrganblp21x1olfgy6olflf5
foreign key (CLIENT_ID)
references CLIENT_LEGAL_ENTITY;
alter table USER_CLIENT
add constraint FK_dohfw51ghmgbf1jem98qbvr8p
foreign key (CLIENT_ID)
references CLIENT_LEGAL_ENTITY;
alter table USER_CLIENT
add constraint FK_s8mev937dpvu5p5yxs07l023w
foreign key (USER_ID)
references USER_PROFILE;
alter table USER_PROFILE
add constraint FK_lxednllfytn2kxu60yqrf58ue
foreign key (PERSON_ID)
references PERSON_INFO;
create sequence SEQ_CLIENT_CONTACT;
create sequence SEQ_COUNTERPARTY_IDENTIFIER;
create sequence SEQ_LEGAL_ENTITY;
create sequence SEQ_PERSON_INFO;
create sequence SEQ_REQUEST;
create sequence SEQ_USER;
No comments:
Post a Comment