Agrippa e la Cartografia

Immaginate di essere nell’antica Roma e che Agrippa (noto anche per aver restaurato e costruito numerosi acquedotti romani) vi avesse chiesto di riportare su una bella mappa dell’epoca la posizione degli acquedotti.

Immaginate anche di avere con voi anche il nuovissimo telefono Aifon Ics con rilevatore GPS preciso al decimo di millimetro e capace di visualizzabile perfettamente i dati rilevati in Gogulus Maps.

Rilevate i dati e consegnate ad Agrippa un elenco di punti da riportare sulla bella mappa dell’epoca seguendo le indicazioni del GPS.

Agrippa vi fa notare che lui non sa cosa siano le coordinate GPS perché usano dita piedi palmi gomiti ma non numeri con 8 cifre decimali (perché Agrippa usava i numeri romani ma conosceva il sistema posizionale degli indiani).

Voi sorridete dell’ingenuità di Agrippa e fate notare che è possibile convertire le coordinate GPS in quello che si vuole tramite opportune trasformazioni matematiche note come proiezioni.

Agrippa è affascinato da questa nuova matematica ma replica osservando che la sua bella mappa dell’epoca non è proprio in scala. Lui non ha Gogulus Maps ed il disegno è fatto un po’ a occhio: a volte un dito sulla mappa corrisponde ad un miglio reale a volte ad un miglio e mezzo. La mappa infatti ha un grosso valore dal punto di vista topologico e ha solo i dettagli che interessano ma non per misurare tutuo e nel massimo dettaglio.

A questo punto voi, un po’ meno sicuro di prima, proponete ad Agrippa di comperare un sacco di Aifon Ics in modo da vedere perfettamente i punti in Gogulus Maps.

Agrippa un po’ perplesso vi risponde che non cambierà mai la bella mappa dell’epoca perché c’è segnata già un sacco di roba e poi non saprebbero neanche dove attaccare i caricatori che hanno una sola presa al Foro Romano.

Vi ringrazia quindi per le coordinate GPS da tenere per un futuro prossimo quando tutti avranno l’Aifon IcsIcs ma per ora, per quanto precise possano essere le coordinate GPS,serve un omino che guardi il punto sul telefono e lo ricopia A MANO sulla bella mappa dell’epoca (poiché èlei a non essere precisa).

Perché questa storia? Perché se vi viene in mente una soluzione migliore ho il numero personale di Agrippa che così ci inserisce tutti i dati degli acquedotti in cartografia.

P.S. Si ringrazia Trenitalia che con la sua ora di ritardo mi ha permesso di scrivere questa storia in stazione!

APEX Tiny CRM 09 The End

Next Article: APEX Tiny CRM 08 Apply Branches.

Our Tiny CRM is finished. Clearly this is only a Proof to test Blueprint feature in Apex. I think it’s very powerful and I hope it will improve more and more in the future.

Here the app Tiny CRM!!!

A Recap for the code:

The draw.io Diagram (copy in an xml file and upload):

<mxfile userAgent="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36" version="7.3.5" editor="www.draw.io">
<diagram id="aace5e1d-7d24-7a26-dab0-8faea3a7f178" name="Page-1">7Zptb9owEIB/DV+nvPLyEdJ07QS0AqZpnyqXmMSqiZExBfrr5yR2Qux0Yi0jkZoWIXw+n8/3nLk4pGN768N3CjbRhAQQdywjOHTsm45lubbB3xPBMRM4hpkJQoqCTHQimKM3KIRiXLhDAdyWFBkhmKFNWbgkcQyXrCQDlJJ9WW1FcHnWDQihJpgvAdalv1DAokzat3qF/A6iMJIzm91B1vMMli8hJbtYzNex7FX6l3WvgbQlFrqNQED2JyLb79geJYRln9YHD+IktDJs2bjbd3pzvymM2TkDrGzAK8A7sfSh5z38nC6Ee+woQ7LdozUGMW+NViRmc9Fj8PYyQjgYgyPZJXNuGY+BbI0iQtEb1weYd5lcwLspE8StbmINYewRTGg6jw2N5L80cp5YFHNRuOVjH+UCTUU0AYeS4hhsmfSSYAw2W/Sc+p0MXAMaonhEGCNroSRXeVt2SjC0RwCjMOayJZ8L8s6RiB+kDB7eZWDmZPmGgWQNGT1yFTGgK3Ih3ytZc18knilVotOkMxyR8CLZw9xyAZx/EMyr+dsa/+lw4mvw+eJYyo6SFyjjEpMsG05CJUTbDViiOBzDVeKrU0hmwv1ERHjQVjhN/ggFAYwTaIQBBp7zzNoQFLN0ee6Iv3gUPOOb23G5Tx5vm0WbvxJ1yjwSczcBSmMPOf49TFLgPFBWNShBhqfrWWSk3mfAOBoY737xuwVTCca1rgfG1cA83j1M2y1TTabXvx6Zrr5lHqaLodfWstpqWc76KsWspyXA7f1svnj60iWt25iS1tfwjIctnabUtYFGx58M78ctmdrrmjwUn15yzB5++G1hq6+wWf1rFjbT1DLgS39rmkY1qxqKmqnfQbkZLvyn+WI40/dnC+jqdc3Ub3GkgPzpTYunAcVNv9Eh7kA+tae3axc52ylXud7gvESQ+/lTiaAfD2QitFc7dSeC2T+zpF4kE/SjiPgqaDOh9kw4+8L3Eplg6ScfDT0MQih58zUhdpxBDBgisV/0jCK2loxhHAyTnwh505/xQr4gExAf82gl9v4eKz492dGl0BIe8cQJodRyqkN6GjKj8qwghDRdwWvZj6pAijkek+uHE2Z2mZkr4yhNZP6LUdbJr4OKIUcx5KiGslVrhlKu+cLPQ60fcRqHuts81I6poHY+iNpVDDmqoQui1o9MjUNdtav7Ne9qS0Hd++iuVgw5qqELotYPX41DLRO/Uax7Cmvzo6wVQ45q6IKs9ZNc41hXfYMPakat3FnMn536Z9TqcySqoQui1n8Ebxzqym1dN2vnQtXacf5XtebN4umzTL14ws/2/wA=</diagram>
</mxfile>

The Quick SQL Code:

# apex: true
# auditcols: true
# drop:true
# prefix: "crm"


account /insert 4
  name /nn
  city /nn
  phone

contact /insert 4
  first name /nn
  last name  /nn
  email

project /insert 4
  name /nn
  date start /nn
  date end

account contact /insert 4
  account id /nn
  contact id /nn

account project /insert 4
  account id /nn
  project id /nn

contact project /insert 4
  contact id /nn
  project id /nn

view account_contact_vw account account_contact contact
view account_project_vw account account_project project
view contact_project_vw contact contact_project project

The Blueprint:

{
"application": {
  "name": "Tiny CRM",
  "appShortDescription": "",
  "appDescription": "",
  "schema": "ROBERTOCAPANCIONI",
  "features": {
    "accessControl": false,
    "activityReporting": false,
    "applicationSettings": false,
    "configurationOptions": false,
    "emailFramework": false,
    "feedback": false,
    "helpPages": false,
    "notifications": false,
    "themeSelection": false,
    "timeZoneSupport": false,
    "userProfiles": false
    },
  "appearance": {
    "themeStyle": "Vita",
    "navigation": "SIDE",
    "icon": "app-icon-bar-line-chart",
    "iconBackgroundClass": "app-color-8",
    "iconColorHEX": "#6E8598"
    },
  "settings": {
    "frameworkTablePrefix": "CRM_",
    "baseTablePrefix": "",
    "primaryLanguage": "EN",
    "translations": "",
    "authentication": "Application Express"
    },
  "pages": [
    {
      "page": "10",
      "pageType": "blank",
      "pageName": "Home",
      "pageIcon": "fa-home",
      "pageIsHomePage": true,
      "help": ""
    }
    ,
    {
      "page": "20",
      "pageType": "interactiveReport",
      "pageName": "LIST ACCOUNT",
      "pageIcon": "fa-table",
      "reportImplementation": "TABLE",
      "table": "CRM_ACCOUNT",
      "includeFormWithReport": true,
      "drillDownLinkColumn": "NAME",
      "drillDownLinkKey": "ID",
      "linkToPage": "50",
      "linkToKey": "ID",
      "help": ""
    }
    ,
    {
      "page": "30",
      "pageType": "interactiveReport",
      "pageName": "LIST CONTACT",
      "pageIcon": "fa-table",
      "reportImplementation": "TABLE",
      "table": "CRM_CONTACT",
      "includeFormWithReport": true,
      "drillDownLinkColumn": "LAST_NAME",
      "drillDownLinkKey": "ID",
      "linkToPage": "60",
      "linkToKey": "ID",
      "help": ""
    }
    ,
    {
      "page": "40",
      "pageType": "interactiveReport",
      "pageName": "LIST PROJECT",
      "pageIcon": "fa-table",
      "reportImplementation": "TABLE",
      "table": "CRM_PROJECT",
      "includeFormWithReport": true,
      "drillDownLinkColumn": "NAME",
      "drillDownLinkKey": "ID",
      "linkToPage": "70",
      "linkToKey": "ID",
      "help": ""
    }
    ,
    {
      "page": "50",
      "pageType": "masterDetailForm",
      "pageName": "ACCOUNT",
      "pageIcon": "fa-layout-header-sidebar-left",
      "formImplementation": "sidebarSelector",
      "table": "CRM_ACCOUNT",
      "masterLabelColumnPrimary": "NAME",
      "masterLabelColumnSecondary": "CITY",
      "detailTable1": "CRM_ACCOUNT_CONTACT",
      "detailTable2": "CRM_ACCOUNT_PROJECT",
      "help": ""
    }
    ,
    {
      "page": "60",
      "pageType": "masterDetailForm",
      "pageName": "CONTACT",
      "pageIcon": "fa-layout-header-sidebar-left",
      "formImplementation": "sidebarSelector",
      "table": "CRM_CONTACT",
      "masterLabelColumnPrimary": "LAST_NAME",
      "masterLabelColumnSecondary": "FIRST_NAME",
      "detailTable1": "CRM_ACCOUNT_CONTACT",
      "detailTable2": "CRM_CONTACT_PROJECT",
      "help": ""
    }
    ,
    {
      "page": "70",
      "pageType": "masterDetailForm",
      "pageName": "PROJECT",
      "pageIcon": "fa-layout-header-sidebar-left",
      "formImplementation": "sidebarSelector",
      "table": "CRM_PROJECT",
      "masterLabelColumnPrimary": "NAME",
      "masterLabelColumnSecondary": "DATE_START",
      "detailTable1": "CRM_ACCOUNT_PROJECT",
      "detailTable2": "CRM_CONTACT_PROJECT",
      "help": ""
    }
    ],
  "generalSettings": {
    "learnAppDefaults": false,
    "version": "Release 1.0",
    "logging": true,
    "debugging": true
    },
  "globalizationSettings": {
    "documentDirection": "left-to-right",
    "dateFormat": "DD-MON-YYYY",
    "dateTimeFormat": "DD-MON-YYYY HH24:MI",
    "timestampFormat": "DD-MON-YYYY HH24:MI",
    "timestampTimeZoneFormat": "DD-MON-YYYY HH24:MI"
    },
  "securitySettings": {
    "rejoinSessions": true,
    "deepLinking": false,
    "maximumSessionSeconds": "",
    "maximumSessionIdleSeconds": ""
    }
  }
}

The LOV:

CRM_ACCOUNT_LOV

  select NAME ||' ( ' || city || ' )' d
       , ID r
    from CRM_ACCOUNT
order by 1

CRM_CONTACT_LOV

 select last_name || ' ' || first_name d
      , id r
   from crm_contact
order by 1

CRM_PROJECT_LOV:

  select name || ' ( ' || 
         to_char(date_start, 'yyyy-mm-dd' ) || ' -> ' ||
         to_char(date_end, 'yyyy-mm-dd' ) || ' )'  d
       , id r
    from crm_project
order by 1

Views in Classic Reports

Page 50:

select ACCOUNT_CONTACT_ID ID,
       ACCOUNT_ID,
       CONTACT_ID,
       FIRST_NAME,
       LAST_NAME,
       EMAIL
from CRM_ACCOUNT_CONTACT_VW
where ACCOUNT_ID = :P50_ID
select ACCOUNT_PROJECT_ID ID,
       ACCOUNT_ID,
       PROJECT_ID,
       PROJECT_NAME,
       DATE_START,
       DATE_END
from CRM_ACCOUNT_PROJECT_VW
where ACCOUNT_ID = :P50_ID 

Page 60:

select ACCOUNT_CONTACT_ID ID,
       ACCOUNT_ID,
       NAME,
       CITY,
       PHONE,
       CONTACT_ID
from CRM_ACCOUNT_CONTACT_VW
where CONTACT_ID = :P60_ID
select CONTACT_PROJECT_ID ID,
       CONTACT_ID,
       PROJECT_ID,
       NAME,
       DATE_START,
       DATE_END
from CRM_CONTACT_PROJECT_VW
where CONTACT_ID = :P60_ID

Page 70:

select ACCOUNT_PROJECT_ID ID,
       ACCOUNT_ID,
       ACCOUNT_NAME,
       CITY,
       PHONE,
       PROJECT_ID
from CRM_ACCOUNT_PROJECT_VW
where PROJECT_ID = :P70_ID
select CONTACT_PROJECT_ID ID,
       CONTACT_ID,
       FIRST_NAME,
       LAST_NAME,
       EMAIL,
       PROJECT_ID
from CRM_CONTACT_PROJECT_VW
where PROJECT_ID = :P70_ID

The Video Playlist:

APEX Tiny CRM 08 Apply Branches

Previous Article: APEX Tiny CRM 07 Apply Views.

 

n the left side menu we have three Items Accounts, Contact and Project, for each element we have a Master-Detail Page with:

  • 1 Form (Master)
  • 2 Classic Report (Detail)

So we have 3 Pages x 2 Classic Report. In total we have:

6 Classic Report but with fields but WITH NO LINKS TO THEIR RESPECTIVE PAGES!!!

To do this, no written instructions, watch the video!!

Previous Article: APEX Tiny CRM 09 The End.

APEX Tiny CRM 07 Apply Views

Next Article: APEX Tiny CRM 06 Apply LOV.

In the left side menu we have three Items Accounts, Contact and Project, for each element we have a Master-Detail Page with:

  • 1 Form (Master)
  • 2 Classic Report (Detail)

So we have 3 Pages x 2 Classic Report. In total we have:

6 Classic Report but WITH NO VISIBLE FIELDS!!!

This because every reports is bound with one of these table:

account_contact
account_project
contact_project

But we’d like to replace them with an SQL bused on our views:

view account_contact_vw
view account_project_vw
view contact_project_vw

So in page 50 we can replace the two queries with:

select ACCOUNT_CONTACT_ID ID,
       ACCOUNT_ID,
       CONTACT_ID,
       FIRST_NAME,
       LAST_NAME,
       EMAIL
from CRM_ACCOUNT_CONTACT_VW
where ACCOUNT_ID = :P50_ID

select ACCOUNT_PROJECT_ID ID,
       ACCOUNT_ID,
       PROJECT_ID,
       PROJECT_NAME,
       DATE_START,
       DATE_END
from CRM_ACCOUNT_PROJECT_VW
where ACCOUNT_ID = :P50_ID 

In page 60 with:

select ACCOUNT_CONTACT_ID ID,
       ACCOUNT_ID,
       NAME,
       CITY,
       PHONE,
       CONTACT_ID
from CRM_ACCOUNT_CONTACT_VW
where CONTACT_ID = :P60_ID

select CONTACT_PROJECT_ID ID,
       CONTACT_ID,
       PROJECT_ID,
       NAME,
       DATE_START,
       DATE_END
from CRM_CONTACT_PROJECT_VW
where CONTACT_ID = :P60_ID

In page 70 with:

select ACCOUNT_PROJECT_ID ID,
       ACCOUNT_ID,
       ACCOUNT_NAME,
       CITY,
       PHONE,
       PROJECT_ID
from CRM_ACCOUNT_PROJECT_VW
where PROJECT_ID = :P70_ID

select CONTACT_PROJECT_ID ID,
       CONTACT_ID,
       FIRST_NAME,
       LAST_NAME,
       EMAIL,
       PROJECT_ID
from CRM_CONTACT_PROJECT_VW
where PROJECT_ID = :P70_ID

 

Next Article: APEX Tiny CRM 08 Apply Branches.

APEX Tiny CRM 06 Apply LOV

Previous Article: APEX Tiny CRM 05 Create LOV.

We have the App and we have the three List of Values, now let’s match them:

In the left side menu we have three Items Accounts, Contact and Project, for each element we have a Master-Detail Page with:

  • 1 Form (Master)
  • 2 Classic Report (Detail)
  • 1 Modal Form for each Classic Report
  • 2 Select List Item for each Modal Form

So we have 3 Pages x 2 Classic Report x 1 Modal Form x 2 Select List Item. In total we have:

12 Item to configure with select List

Is it an hard work? In the video it takes me 6 minutes 🙂

Next Article: APEX Tiny CRM 07 Apply Views.

 

APEX Tiny CRM 05 Create LOV

Previous Article: APEX Tiny CRM 04 Create APEX App.

The Blueprint wizard has just created our App but we need some customization.

The App needs three List of Values (LOV) one for Account one for Contact and one for Project but the wizard create only a LOV (a Bug??). So we have to:

  • Create 2 LOV (Contact,Project)
  • Update and rename the only LOV present (Account)

Let’s go into Shared Components -> List of Values  and click CRM_ACCOUNT_CONTACT.ID.LOOKUP

rename in CRM_ACCOUNT_LOV and let’s change the query in:

  select NAME ||' ( ' || city || ' )' d
       , ID r
    from CRM_ACCOUNT
order by 1

then let’s create CRM_CONTACT_LOV:

 select last_name || ' ' || first_name d
      , id r
   from crm_contact
order by 1

and CRM_PROJECT_LOV:

  select name || ' ( ' || 
         to_char(date_start, 'yyyy-mm-dd' ) || ' -> ' ||
         to_char(date_end, 'yyyy-mm-dd' ) || ' )'  d
       , id r
    from crm_project
order by 1

 

 

Next Article: APEX Tiny CRM 06 Apply LOV.

APEX Tiny CRM 04 Create APEX App

Previous Article: APEX Tiny CRM 03 Execute DDL.

Login into http://apex.oracle.com 

In App Builder let’s Create a new Application: Tiny CRM, open Blueprint and do some stuff:

Add Page -> Master Detail

fill the form

Page Name                : ACCOUNT
Table                    : CRM_ACCOUNT
Primary   Display Column : NAME,
Secondary Display Column : CITY
Detail Table1            : CRM_ACCOUNT_CONTACT
Detail Table2            : CRM_ACCOUNT_PROJECT

Add Page -> Report

fill the form

Page Name                : LIST ACCOUNT
Table or View            : CRM_ACCOUNT
Include Form             : Y

into Drill Down	
Link Column              : NAME
Link Key                 : ID
Link to Page             : ACCOUNT
Target Key               : ID

We can repeat these 2 steps for Contact and Project (like I do in the video below) or we can look at the Blueprint code:

Let’s open View Blueprint to search for the JSON script of our two pages just created

    ,
    {
      "page": "20",
      "pageType": "masterDetailForm",
      "pageName": "ACCOUNT",
      "pageIcon": "fa-layout-header-sidebar-left",
      "formImplementation": "sidebarSelector",
      "table": "CRM_ACCOUNT",
      "masterLabelColumnPrimary": "NAME",
      "masterLabelColumnSecondary": "CITY",
      "detailTable1": "CRM_ACCOUNT_CONTACT",
      "detailTable2": "CRM_ACCOUNT_PROJECT",
      "help": ""
    }
    ,
    {
      "page": "30",
      "pageType": "interactiveReport",
      "pageName": "LIST ACCOUNT",
      "pageIcon": "fa-table",
      "reportImplementation": "TABLE",
      "table": "CRM_ACCOUNT",
      "includeFormWithReport": true,
      "drillDownLinkColumn": "NAME",
      "drillDownLinkKey": "ID",
      "linkToPage": "20",
      "linkToKey": "ID",
      "help": ""
    }

let’s copy twice and adjust the field for the other objects

Here all the JSON script of the Blueprint with:

  • updated row to set the Table Prefix: “frameworkTablePrefix”: “CRM_”
  • my schema: “schema”: “ROBERTOCAPANCIONI” (change with yours)
  • reordered pages
{
"application": {
  "name": "Tiny CRM",
  "appShortDescription": "",
  "appDescription": "",
  "schema": "ROBERTOCAPANCIONI",
  "features": {
    "accessControl": false,
    "activityReporting": false,
    "applicationSettings": false,
    "configurationOptions": false,
    "emailFramework": false,
    "feedback": false,
    "helpPages": false,
    "notifications": false,
    "themeSelection": false,
    "timeZoneSupport": false,
    "userProfiles": false
    },
  "appearance": {
    "themeStyle": "Vita",
    "navigation": "SIDE",
    "icon": "app-icon-bar-line-chart",
    "iconBackgroundClass": "app-color-8",
    "iconColorHEX": "#6E8598"
    },
  "settings": {
    "frameworkTablePrefix": "CRM_",
    "baseTablePrefix": "",
    "primaryLanguage": "EN",
    "translations": "",
    "authentication": "Application Express"
    },
  "pages": [
    {
      "page": "10",
      "pageType": "blank",
      "pageName": "Home",
      "pageIcon": "fa-home",
      "pageIsHomePage": true,
      "help": ""
    }
    ,
    {
      "page": "20",
      "pageType": "interactiveReport",
      "pageName": "LIST ACCOUNT",
      "pageIcon": "fa-table",
      "reportImplementation": "TABLE",
      "table": "CRM_ACCOUNT",
      "includeFormWithReport": true,
      "drillDownLinkColumn": "NAME",
      "drillDownLinkKey": "ID",
      "linkToPage": "50",
      "linkToKey": "ID",
      "help": ""
    }
    ,
    {
      "page": "30",
      "pageType": "interactiveReport",
      "pageName": "LIST CONTACT",
      "pageIcon": "fa-table",
      "reportImplementation": "TABLE",
      "table": "CRM_CONTACT",
      "includeFormWithReport": true,
      "drillDownLinkColumn": "LAST_NAME",
      "drillDownLinkKey": "ID",
      "linkToPage": "60",
      "linkToKey": "ID",
      "help": ""
    }
    ,
    {
      "page": "40",
      "pageType": "interactiveReport",
      "pageName": "LIST PROJECT",
      "pageIcon": "fa-table",
      "reportImplementation": "TABLE",
      "table": "CRM_PROJECT",
      "includeFormWithReport": true,
      "drillDownLinkColumn": "NAME",
      "drillDownLinkKey": "ID",
      "linkToPage": "70",
      "linkToKey": "ID",
      "help": ""
    }
    ,
    {
      "page": "50",
      "pageType": "masterDetailForm",
      "pageName": "ACCOUNT",
      "pageIcon": "fa-layout-header-sidebar-left",
      "formImplementation": "sidebarSelector",
      "table": "CRM_ACCOUNT",
      "masterLabelColumnPrimary": "NAME",
      "masterLabelColumnSecondary": "CITY",
      "detailTable1": "CRM_ACCOUNT_CONTACT",
      "detailTable2": "CRM_ACCOUNT_PROJECT",
      "help": ""
    }
    ,
    {
      "page": "60",
      "pageType": "masterDetailForm",
      "pageName": "CONTACT",
      "pageIcon": "fa-layout-header-sidebar-left",
      "formImplementation": "sidebarSelector",
      "table": "CRM_CONTACT",
      "masterLabelColumnPrimary": "LAST_NAME",
      "masterLabelColumnSecondary": "FIRST_NAME",
      "detailTable1": "CRM_ACCOUNT_CONTACT",
      "detailTable2": "CRM_CONTACT_PROJECT",
      "help": ""
    }
    ,
    {
      "page": "70",
      "pageType": "masterDetailForm",
      "pageName": "PROJECT",
      "pageIcon": "fa-layout-header-sidebar-left",
      "formImplementation": "sidebarSelector",
      "table": "CRM_PROJECT",
      "masterLabelColumnPrimary": "NAME",
      "masterLabelColumnSecondary": "DATE_START",
      "detailTable1": "CRM_ACCOUNT_PROJECT",
      "detailTable2": "CRM_CONTACT_PROJECT",
      "help": ""
    }
    ],
  "generalSettings": {
    "learnAppDefaults": false,
    "version": "Release 1.0",
    "logging": true,
    "debugging": true
    },
  "globalizationSettings": {
    "documentDirection": "left-to-right",
    "dateFormat": "DD-MON-YYYY",
    "dateTimeFormat": "DD-MON-YYYY HH24:MI",
    "timestampFormat": "DD-MON-YYYY HH24:MI",
    "timestampTimeZoneFormat": "DD-MON-YYYY HH24:MI"
    },
  "securitySettings": {
    "rejoinSessions": true,
    "deepLinking": false,
    "maximumSessionSeconds": "",
    "maximumSessionIdleSeconds": ""
    }
  }
}

OK Let’s Create the Application !!

Next Article: APEX Tiny CRM 05 Create LOV.

APEX Tiny CRM 03 Execute DDL

Previous Article: APEX Tiny CRM 02 Create DDL.

Now we are ready to login into http://apex.oracle.com 

go to SQL Workshop -> SQL Scripts

create and run the script with our SQL code created in the previous article:

-- drop objects
drop table crm_account cascade constraints;
drop table crm_contact cascade constraints;
drop table crm_project cascade constraints;
drop table crm_account_contact cascade constraints;
drop table crm_account_project cascade constraints;
drop table crm_contact_project cascade constraints;
drop view crm_account_contact_vw;
drop view crm_account_project_vw;
drop view crm_contact_project_vw;

-- create tables
create table crm_account (
    id                             number not null constraint crm_account_id_pk primary key,
    name                           varchar2(255) not null,
    city                           varchar2(255) not null,
    phone                          varchar2(30),
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

create table crm_contact (
    id                             number not null constraint crm_contact_id_pk primary key,
    first_name                     varchar2(255) not null,
    last_name                      varchar2(255) not null,
    email                          varchar2(255),
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

create table crm_project (
    id                             number not null constraint crm_project_id_pk primary key,
    name                           varchar2(255) not null,
    date_start                     date not null,
    date_end                       date,
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

create table crm_account_contact (
    id                             number not null constraint crm_account_contac_id_pk primary key,
    account_id                     number
                                   constraint crm_account_con_account_id_fk
                                   references crm_account on delete cascade not null,
    contact_id                     number
                                   constraint crm_account_con_contact_id_fk
                                   references crm_contact on delete cascade not null,
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

create table crm_account_project (
    id                             number not null constraint crm_account_projec_id_pk primary key,
    account_id                     number
                                   constraint crm_account_pro_account_id_fk
                                   references crm_account on delete cascade not null,
    project_id                     number
                                   constraint crm_account_pro_project_id_fk
                                   references crm_project on delete cascade not null,
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

create table crm_contact_project (
    id                             number not null constraint crm_contact_projec_id_pk primary key,
    contact_id                     number
                                   constraint crm_contact_pro_contact_id_fk
                                   references crm_contact on delete cascade not null,
    project_id                     number
                                   constraint crm_contact_pro_project_id_fk
                                   references crm_project on delete cascade not null,
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;


-- triggers
create or replace trigger crm_account_biu
    before insert or update 
    on crm_account
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end crm_account_biu;
/

create or replace trigger crm_contact_biu
    before insert or update 
    on crm_contact
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end crm_contact_biu;
/

create or replace trigger crm_project_biu
    before insert or update 
    on crm_project
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end crm_project_biu;
/

create or replace trigger crm_account_contact_biu
    before insert or update 
    on crm_account_contact
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end crm_account_contact_biu;
/

create or replace trigger crm_account_project_biu
    before insert or update 
    on crm_account_project
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end crm_account_project_biu;
/

create or replace trigger crm_contact_project_biu
    before insert or update 
    on crm_contact_project
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end crm_contact_project_biu;
/


-- indexes
create index crm_account_contact_i1 on crm_account_contact (account_id);
create index crm_account_contact_i2 on crm_account_contact (contact_id);
create index crm_account_project_i1 on crm_account_project (account_id);
create index crm_account_project_i2 on crm_account_project (project_id);
create index crm_contact_project_i1 on crm_contact_project (contact_id);
create index crm_contact_project_i2 on crm_contact_project (project_id);

-- create views
create or replace view crm_account_contact_vw as 
select 
    account.id                                         account_id,
    account.name                                       name,
    account.city                                       city,
    account.phone                                      phone,
    account.created                                    account_created,
    account.created_by                                 account_created_by,
    account.updated                                    account_updated,
    account.updated_by                                 account_updated_by,
    account_contact.id                                 account_contact_id,
    account_contact.created                            account_contact_created,
    account_contact.created_by                         account_contact_created_by,
    account_contact.updated                            account_contact_updated,
    account_contact.updated_by                         account_contact_updated_by,
    contact.id                                         contact_id,
    contact.first_name                                 first_name,
    contact.last_name                                  last_name,
    contact.email                                      email,
    contact.created                                    contact_created,
    contact.created_by                                 contact_created_by,
    contact.updated                                    contact_updated,
    contact.updated_by                                 contact_updated_by
from 
    crm_account account,
    crm_account_contact account_contact,
    crm_contact contact
where
    account_contact.account_id = account.id and
    account_contact.contact_id = contact.id
/

create or replace view crm_account_project_vw as 
select 
    account.id                                         account_id,
    account.name                                       account_name,
    account.city                                       city,
    account.phone                                      phone,
    account.created                                    account_created,
    account.created_by                                 account_created_by,
    account.updated                                    account_updated,
    account.updated_by                                 account_updated_by,
    account_project.id                                 account_project_id,
    account_project.created                            account_project_created,
    account_project.created_by                         account_project_created_by,
    account_project.updated                            account_project_updated,
    account_project.updated_by                         account_project_updated_by,
    project.id                                         project_id,
    project.name                                       project_name,
    project.date_start                                 date_start,
    project.date_end                                   date_end,
    project.created                                    project_created,
    project.created_by                                 project_created_by,
    project.updated                                    project_updated,
    project.updated_by                                 project_updated_by
from 
    crm_account account,
    crm_account_project account_project,
    crm_project project
where
    account_project.account_id = account.id and
    account_project.project_id = project.id
/

create or replace view crm_contact_project_vw as 
select 
    contact.id                                         contact_id,
    contact.first_name                                 first_name,
    contact.last_name                                  last_name,
    contact.email                                      email,
    contact.created                                    contact_created,
    contact.created_by                                 contact_created_by,
    contact.updated                                    contact_updated,
    contact.updated_by                                 contact_updated_by,
    contact_project.id                                 contact_project_id,
    contact_project.created                            contact_project_created,
    contact_project.created_by                         contact_project_created_by,
    contact_project.updated                            contact_project_updated,
    contact_project.updated_by                         contact_project_updated_by,
    project.id                                         project_id,
    project.name                                       name,
    project.date_start                                 date_start,
    project.date_end                                   date_end,
    project.created                                    project_created,
    project.created_by                                 project_created_by,
    project.updated                                    project_updated,
    project.updated_by                                 project_updated_by
from 
    crm_contact contact,
    crm_contact_project contact_project,
    crm_project project
where
    contact_project.contact_id = contact.id and
    contact_project.project_id = project.id
/

insert into crm_account (
    id,
    name,
    city,
    phone
) values (
    117184769414855594799963243736550100140,
    'Shipping Process Review',
    'Tanquecitos',
    'Faucibus Orci Luctus'
);

insert into crm_account (
    id,
    name,
    city,
    phone
) values (
    117184769414856803725782858365724806316,
    'Buying Activity Analysis',
    'Sugarloaf',
    'Nam Semper Diam'
);

insert into crm_account (
    id,
    name,
    city,
    phone
) values (
    117184769414858012651602472994899512492,
    'Claim Reduction Plan',
    'Dale City',
    'Pharetra Id Mattis'
);

insert into crm_account (
    id,
    name,
    city,
    phone
) values (
    117184769414859221577422087624074218668,
    'Overseas Relocation',
    'Grosvenor',
    'Primis In Faucibus'
);

-- load data
insert into crm_contact (
    id,
    first_name,
    last_name,
    email
) values (
    117184769414860430503241702253248924844,
    'Gricelda',
    'Luebbers',
    'gricelda.luebbers@aalf.com'
);

insert into crm_contact (
    id,
    first_name,
    last_name,
    email
) values (
    117184769414861639429061316882423631020,
    'Dean',
    'Bollich',
    'dean.bollich@aalg.com'
);

insert into crm_contact (
    id,
    first_name,
    last_name,
    email
) values (
    117184769414862848354880931511598337196,
    'Milo',
    'Manoni',
    'milo.manoni@aalh.com'
);

insert into crm_contact (
    id,
    first_name,
    last_name,
    email
) values (
    117184769414864057280700546140773043372,
    'Laurice',
    'Karl',
    'laurice.karl@aali.com'
);

insert into crm_project (
    id,
    name,
    date_start,
    date_end
) values (
    117184769414865266206520160769947749548,
    'New Feature Verification',
    sysdate - 66,
    sysdate - 73
);

insert into crm_project (
    id,
    name,
    date_start,
    date_end
) values (
    117184769414866475132339775399122455724,
    'Night Call Reduction',
    sysdate - 82,
    sysdate - 28
);

insert into crm_project (
    id,
    name,
    date_start,
    date_end
) values (
    117184769414867684058159390028297161900,
    'Course Development',
    sysdate - 77,
    sysdate - 43
);

insert into crm_project (
    id,
    name,
    date_start,
    date_end
) values (
    117184769414868892983979004657471868076,
    'Buying Activity Analysis',
    sysdate - 59,
    sysdate - 12
);

insert into crm_account_contact (
    id,
    account_id,
    contact_id
) values (
    117184769414870101909798619286646574252,
    117184769414858012651602472994899512492,
    117184769414864057280700546140773043372
);

insert into crm_account_contact (
    id,
    account_id,
    contact_id
) values (
    117184769414871310835618233915821280428,
    117184769414858012651602472994899512492,
    117184769414864057280700546140773043372
);

insert into crm_account_contact (
    id,
    account_id,
    contact_id
) values (
    117184769414872519761437848544995986604,
    117184769414856803725782858365724806316,
    117184769414860430503241702253248924844
);

insert into crm_account_contact (
    id,
    account_id,
    contact_id
) values (
    117184769414873728687257463174170692780,
    117184769414856803725782858365724806316,
    117184769414860430503241702253248924844
);

insert into crm_account_project (
    id,
    account_id,
    project_id
) values (
    117184769414874937613077077803345398956,
    117184769414855594799963243736550100140,
    117184769414868892983979004657471868076
);

insert into crm_account_project (
    id,
    account_id,
    project_id
) values (
    117184769414876146538896692432520105132,
    117184769414856803725782858365724806316,
    117184769414865266206520160769947749548
);

insert into crm_account_project (
    id,
    account_id,
    project_id
) values (
    117184769414877355464716307061694811308,
    117184769414855594799963243736550100140,
    117184769414867684058159390028297161900
);

insert into crm_account_project (
    id,
    account_id,
    project_id
) values (
    117184769414878564390535921690869517484,
    117184769414855594799963243736550100140,
    117184769414865266206520160769947749548
);

insert into crm_contact_project (
    id,
    contact_id,
    project_id
) values (
    117184769414879773316355536320044223660,
    117184769414860430503241702253248924844,
    117184769414867684058159390028297161900
);

insert into crm_contact_project (
    id,
    contact_id,
    project_id
) values (
    117184769414880982242175150949218929836,
    117184769414860430503241702253248924844,
    117184769414867684058159390028297161900
);

insert into crm_contact_project (
    id,
    contact_id,
    project_id
) values (
    117184769414882191167994765578393636012,
    117184769414860430503241702253248924844,
    117184769414867684058159390028297161900
);

insert into crm_contact_project (
    id,
    contact_id,
    project_id
) values (
    117184769414883400093814380207568342188,
    117184769414861639429061316882423631020,
    117184769414868892983979004657471868076
);

 
-- Generated by Quick SQL Friday September 01, 2017  22:12:03
 
/*
# apex: true# drop:true
# prefix: "crm"


account /insert 4
  name /nn
  city /nn
  phone

contact /insert 4
  first name /nn
  last name  /nn
  email

project /insert 4
  name /nn
  date start /nn
  date end

account contact /insert 4
  account id /nn
  contact id /nn

account project /insert 4
  account id /nn
  project id /nn

contact project /insert 4
  contact id /nn
  project id /nn

view account_contact_vw account account_contact contact
view account_project_vw account account_project project
view contact_project_vw contact contact_project project

# settings = { prefix: "CRM", PK: "TRIG", auditCols: true, drop: true, language: "EN", APEX: true }
*/

 

Next Article: APEX Tiny CRM 04 Create APEX App.

APEX Tiny CRM 02 Create DDL

Previous article: APEX Tiny CRM 01 ER Diagram

Login into http://apex.oracle.com/quicksql to create all the database objects:

we want apex settings, a prefix for all the objects (the prefix is crm) and audit columns into the tables, so:

# apex: true
# auditcols: true
# drop:true
# prefix: "crm"

then let’s create the objects  and sample data:

account /insert 4
  name /nn
  city /nn
  phone

contact /insert 4
  first name /nn
  last name  /nn
  email

project /insert 4
  name /nn
  date start /nn
  date end

let’s create also relations between objects:

account contact /insert 4
  account id /nn
  contact id /nn

account project /insert 4
  account id /nn
  project id /nn

contact project /insert 4
  contact id /nn
  project id /nn

and some views

view account_contact_vw account account_contact contact
view account_project_vw account account_project project
view contact_project_vw contact contact_project project

so at the end we have:

# apex: true
# auditcols: true
# drop:true
# prefix: "crm"


account /insert 4
  name /nn
  city /nn
  phone

contact /insert 4
  first name /nn
  last name  /nn
  email

project /insert 4
  name /nn
  date start /nn
  date end

account contact /insert 4
  account id /nn
  contact id /nn

account project /insert 4
  account id /nn
  project id /nn

contact project /insert 4
  contact id /nn
  project id /nn

view account_contact_vw account account_contact contact
view account_project_vw account account_project project
view contact_project_vw contact contact_project project

Quick SQL is really great because transforms 35 clean rows in more than 500 SQL rows. A little fix: in the 3 views we want only INNER join so we’re going to delete all the  6 (+) occurences

-- drop objects
drop table crm_account cascade constraints;
drop table crm_contact cascade constraints;
drop table crm_project cascade constraints;
drop table crm_account_contact cascade constraints;
drop table crm_account_project cascade constraints;
drop table crm_contact_project cascade constraints;
drop view crm_account_contact_vw;
drop view crm_account_project_vw;
drop view crm_contact_project_vw;

-- create tables
create table crm_account (
    id                             number not null constraint crm_account_id_pk primary key,
    name                           varchar2(255) not null,
    city                           varchar2(255) not null,
    phone                          varchar2(30),
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

create table crm_contact (
    id                             number not null constraint crm_contact_id_pk primary key,
    first_name                     varchar2(255) not null,
    last_name                      varchar2(255) not null,
    email                          varchar2(255),
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

create table crm_project (
    id                             number not null constraint crm_project_id_pk primary key,
    name                           varchar2(255) not null,
    date_start                     date not null,
    date_end                       date,
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

create table crm_account_contact (
    id                             number not null constraint crm_account_contac_id_pk primary key,
    account_id                     number
                                   constraint crm_account_con_account_id_fk
                                   references crm_account on delete cascade not null,
    contact_id                     number
                                   constraint crm_account_con_contact_id_fk
                                   references crm_contact on delete cascade not null,
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

create table crm_account_project (
    id                             number not null constraint crm_account_projec_id_pk primary key,
    account_id                     number
                                   constraint crm_account_pro_account_id_fk
                                   references crm_account on delete cascade not null,
    project_id                     number
                                   constraint crm_account_pro_project_id_fk
                                   references crm_project on delete cascade not null,
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

create table crm_contact_project (
    id                             number not null constraint crm_contact_projec_id_pk primary key,
    contact_id                     number
                                   constraint crm_contact_pro_contact_id_fk
                                   references crm_contact on delete cascade not null,
    project_id                     number
                                   constraint crm_contact_pro_project_id_fk
                                   references crm_project on delete cascade not null,
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;


-- triggers
create or replace trigger crm_account_biu
    before insert or update 
    on crm_account
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end crm_account_biu;
/

create or replace trigger crm_contact_biu
    before insert or update 
    on crm_contact
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end crm_contact_biu;
/

create or replace trigger crm_project_biu
    before insert or update 
    on crm_project
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end crm_project_biu;
/

create or replace trigger crm_account_contact_biu
    before insert or update 
    on crm_account_contact
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end crm_account_contact_biu;
/

create or replace trigger crm_account_project_biu
    before insert or update 
    on crm_account_project
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end crm_account_project_biu;
/

create or replace trigger crm_contact_project_biu
    before insert or update 
    on crm_contact_project
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end crm_contact_project_biu;
/


-- indexes
create index crm_account_contact_i1 on crm_account_contact (account_id);
create index crm_account_contact_i2 on crm_account_contact (contact_id);
create index crm_account_project_i1 on crm_account_project (account_id);
create index crm_account_project_i2 on crm_account_project (project_id);
create index crm_contact_project_i1 on crm_contact_project (contact_id);
create index crm_contact_project_i2 on crm_contact_project (project_id);

-- create views
create or replace view crm_account_contact_vw as 
select 
    account.id                                         account_id,
    account.name                                       name,
    account.city                                       city,
    account.phone                                      phone,
    account.created                                    account_created,
    account.created_by                                 account_created_by,
    account.updated                                    account_updated,
    account.updated_by                                 account_updated_by,
    account_contact.id                                 account_contact_id,
    account_contact.created                            account_contact_created,
    account_contact.created_by                         account_contact_created_by,
    account_contact.updated                            account_contact_updated,
    account_contact.updated_by                         account_contact_updated_by,
    contact.id                                         contact_id,
    contact.first_name                                 first_name,
    contact.last_name                                  last_name,
    contact.email                                      email,
    contact.created                                    contact_created,
    contact.created_by                                 contact_created_by,
    contact.updated                                    contact_updated,
    contact.updated_by                                 contact_updated_by
from 
    crm_account account,
    crm_account_contact account_contact,
    crm_contact contact
where
    account_contact.account_id = account.id and
    account_contact.contact_id = contact.id
/

create or replace view crm_account_project_vw as 
select 
    account.id                                         account_id,
    account.name                                       account_name,
    account.city                                       city,
    account.phone                                      phone,
    account.created                                    account_created,
    account.created_by                                 account_created_by,
    account.updated                                    account_updated,
    account.updated_by                                 account_updated_by,
    account_project.id                                 account_project_id,
    account_project.created                            account_project_created,
    account_project.created_by                         account_project_created_by,
    account_project.updated                            account_project_updated,
    account_project.updated_by                         account_project_updated_by,
    project.id                                         project_id,
    project.name                                       project_name,
    project.date_start                                 date_start,
    project.date_end                                   date_end,
    project.created                                    project_created,
    project.created_by                                 project_created_by,
    project.updated                                    project_updated,
    project.updated_by                                 project_updated_by
from 
    crm_account account,
    crm_account_project account_project,
    crm_project project
where
    account_project.account_id = account.id and
    account_project.project_id = project.id
/

create or replace view crm_contact_project_vw as 
select 
    contact.id                                         contact_id,
    contact.first_name                                 first_name,
    contact.last_name                                  last_name,
    contact.email                                      email,
    contact.created                                    contact_created,
    contact.created_by                                 contact_created_by,
    contact.updated                                    contact_updated,
    contact.updated_by                                 contact_updated_by,
    contact_project.id                                 contact_project_id,
    contact_project.created                            contact_project_created,
    contact_project.created_by                         contact_project_created_by,
    contact_project.updated                            contact_project_updated,
    contact_project.updated_by                         contact_project_updated_by,
    project.id                                         project_id,
    project.name                                       name,
    project.date_start                                 date_start,
    project.date_end                                   date_end,
    project.created                                    project_created,
    project.created_by                                 project_created_by,
    project.updated                                    project_updated,
    project.updated_by                                 project_updated_by
from 
    crm_contact contact,
    crm_contact_project contact_project,
    crm_project project
where
    contact_project.contact_id = contact.id and
    contact_project.project_id = project.id
/

insert into crm_account (
    id,
    name,
    city,
    phone
) values (
    117184769414855594799963243736550100140,
    'Shipping Process Review',
    'Tanquecitos',
    'Faucibus Orci Luctus'
);

insert into crm_account (
    id,
    name,
    city,
    phone
) values (
    117184769414856803725782858365724806316,
    'Buying Activity Analysis',
    'Sugarloaf',
    'Nam Semper Diam'
);

insert into crm_account (
    id,
    name,
    city,
    phone
) values (
    117184769414858012651602472994899512492,
    'Claim Reduction Plan',
    'Dale City',
    'Pharetra Id Mattis'
);

insert into crm_account (
    id,
    name,
    city,
    phone
) values (
    117184769414859221577422087624074218668,
    'Overseas Relocation',
    'Grosvenor',
    'Primis In Faucibus'
);

-- load data
insert into crm_contact (
    id,
    first_name,
    last_name,
    email
) values (
    117184769414860430503241702253248924844,
    'Gricelda',
    'Luebbers',
    'gricelda.luebbers@aalf.com'
);

insert into crm_contact (
    id,
    first_name,
    last_name,
    email
) values (
    117184769414861639429061316882423631020,
    'Dean',
    'Bollich',
    'dean.bollich@aalg.com'
);

insert into crm_contact (
    id,
    first_name,
    last_name,
    email
) values (
    117184769414862848354880931511598337196,
    'Milo',
    'Manoni',
    'milo.manoni@aalh.com'
);

insert into crm_contact (
    id,
    first_name,
    last_name,
    email
) values (
    117184769414864057280700546140773043372,
    'Laurice',
    'Karl',
    'laurice.karl@aali.com'
);

insert into crm_project (
    id,
    name,
    date_start,
    date_end
) values (
    117184769414865266206520160769947749548,
    'New Feature Verification',
    sysdate - 66,
    sysdate - 73
);

insert into crm_project (
    id,
    name,
    date_start,
    date_end
) values (
    117184769414866475132339775399122455724,
    'Night Call Reduction',
    sysdate - 82,
    sysdate - 28
);

insert into crm_project (
    id,
    name,
    date_start,
    date_end
) values (
    117184769414867684058159390028297161900,
    'Course Development',
    sysdate - 77,
    sysdate - 43
);

insert into crm_project (
    id,
    name,
    date_start,
    date_end
) values (
    117184769414868892983979004657471868076,
    'Buying Activity Analysis',
    sysdate - 59,
    sysdate - 12
);

insert into crm_account_contact (
    id,
    account_id,
    contact_id
) values (
    117184769414870101909798619286646574252,
    117184769414858012651602472994899512492,
    117184769414864057280700546140773043372
);

insert into crm_account_contact (
    id,
    account_id,
    contact_id
) values (
    117184769414871310835618233915821280428,
    117184769414858012651602472994899512492,
    117184769414864057280700546140773043372
);

insert into crm_account_contact (
    id,
    account_id,
    contact_id
) values (
    117184769414872519761437848544995986604,
    117184769414856803725782858365724806316,
    117184769414860430503241702253248924844
);

insert into crm_account_contact (
    id,
    account_id,
    contact_id
) values (
    117184769414873728687257463174170692780,
    117184769414856803725782858365724806316,
    117184769414860430503241702253248924844
);

insert into crm_account_project (
    id,
    account_id,
    project_id
) values (
    117184769414874937613077077803345398956,
    117184769414855594799963243736550100140,
    117184769414868892983979004657471868076
);

insert into crm_account_project (
    id,
    account_id,
    project_id
) values (
    117184769414876146538896692432520105132,
    117184769414856803725782858365724806316,
    117184769414865266206520160769947749548
);

insert into crm_account_project (
    id,
    account_id,
    project_id
) values (
    117184769414877355464716307061694811308,
    117184769414855594799963243736550100140,
    117184769414867684058159390028297161900
);

insert into crm_account_project (
    id,
    account_id,
    project_id
) values (
    117184769414878564390535921690869517484,
    117184769414855594799963243736550100140,
    117184769414865266206520160769947749548
);

insert into crm_contact_project (
    id,
    contact_id,
    project_id
) values (
    117184769414879773316355536320044223660,
    117184769414860430503241702253248924844,
    117184769414867684058159390028297161900
);

insert into crm_contact_project (
    id,
    contact_id,
    project_id
) values (
    117184769414880982242175150949218929836,
    117184769414860430503241702253248924844,
    117184769414867684058159390028297161900
);

insert into crm_contact_project (
    id,
    contact_id,
    project_id
) values (
    117184769414882191167994765578393636012,
    117184769414860430503241702253248924844,
    117184769414867684058159390028297161900
);

insert into crm_contact_project (
    id,
    contact_id,
    project_id
) values (
    117184769414883400093814380207568342188,
    117184769414861639429061316882423631020,
    117184769414868892983979004657471868076
);

 
-- Generated by Quick SQL Friday September 01, 2017  22:12:03
 
/*
# apex: true# drop:true
# prefix: "crm"


account /insert 4
  name /nn
  city /nn
  phone

contact /insert 4
  first name /nn
  last name  /nn
  email

project /insert 4
  name /nn
  date start /nn
  date end

account contact /insert 4
  account id /nn
  contact id /nn

account project /insert 4
  account id /nn
  project id /nn

contact project /insert 4
  contact id /nn
  project id /nn

view account_contact_vw account account_contact contact
view account_project_vw account account_project project
view contact_project_vw contact contact_project project

# settings = { prefix: "CRM", PK: "TRIG", auditCols: true, drop: true, language: "EN", APEX: true }
*/

Next Article: APEX Tiny CRM 03 Execute DDL.

APEX Tiny CRM 01 ER Diagram

We want to create an APEX Application called Tiny CRM with 3 only objects

account
contact
project

all related among them in a many-to-many way thanks to:

account_contact
account_project
contact_project

Let’s design a simple ER Diagram with draw.io:

If you want you can copy the code below in an xml file and upload to draw.io to have the same diagram.

<mxfile userAgent="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36" version="7.3.5" editor="www.draw.io">
<diagram id="aace5e1d-7d24-7a26-dab0-8faea3a7f178" name="Page-1">7Zptb9owEIB/DV+nvPLyEdJ07QS0AqZpnyqXmMSqiZExBfrr5yR2Qux0Yi0jkZoWIXw+n8/3nLk4pGN768N3CjbRhAQQdywjOHTsm45lubbB3xPBMRM4hpkJQoqCTHQimKM3KIRiXLhDAdyWFBkhmKFNWbgkcQyXrCQDlJJ9WW1FcHnWDQihJpgvAdalv1DAokzat3qF/A6iMJIzm91B1vMMli8hJbtYzNex7FX6l3WvgbQlFrqNQED2JyLb79geJYRln9YHD+IktDJs2bjbd3pzvymM2TkDrGzAK8A7sfSh5z38nC6Ee+woQ7LdozUGMW+NViRmc9Fj8PYyQjgYgyPZJXNuGY+BbI0iQtEb1weYd5lcwLspE8StbmINYewRTGg6jw2N5L80cp5YFHNRuOVjH+UCTUU0AYeS4hhsmfSSYAw2W/Sc+p0MXAMaonhEGCNroSRXeVt2SjC0RwCjMOayJZ8L8s6RiB+kDB7eZWDmZPmGgWQNGT1yFTGgK3Ih3ytZc18knilVotOkMxyR8CLZw9xyAZx/EMyr+dsa/+lw4mvw+eJYyo6SFyjjEpMsG05CJUTbDViiOBzDVeKrU0hmwv1ERHjQVjhN/ggFAYwTaIQBBp7zzNoQFLN0ee6Iv3gUPOOb23G5Tx5vm0WbvxJ1yjwSczcBSmMPOf49TFLgPFBWNShBhqfrWWSk3mfAOBoY737xuwVTCca1rgfG1cA83j1M2y1TTabXvx6Zrr5lHqaLodfWstpqWc76KsWspyXA7f1svnj60iWt25iS1tfwjIctnabUtYFGx58M78ctmdrrmjwUn15yzB5++G1hq6+wWf1rFjbT1DLgS39rmkY1qxqKmqnfQbkZLvyn+WI40/dnC+jqdc3Ub3GkgPzpTYunAcVNv9Eh7kA+tae3axc52ylXud7gvESQ+/lTiaAfD2QitFc7dSeC2T+zpF4kE/SjiPgqaDOh9kw4+8L3Eplg6ScfDT0MQih58zUhdpxBDBgisV/0jCK2loxhHAyTnwh505/xQr4gExAf82gl9v4eKz492dGl0BIe8cQJodRyqkN6GjKj8qwghDRdwWvZj6pAijkek+uHE2Z2mZkr4yhNZP6LUdbJr4OKIUcx5KiGslVrhlKu+cLPQ60fcRqHuts81I6poHY+iNpVDDmqoQui1o9MjUNdtav7Ne9qS0Hd++iuVgw5qqELotYPX41DLRO/Uax7Cmvzo6wVQ45q6IKs9ZNc41hXfYMPakat3FnMn536Z9TqcySqoQui1n8Ebxzqym1dN2vnQtXacf5XtebN4umzTL14ws/2/wA=</diagram>
</mxfile>

 

Next Article: APEX Tiny CRM 02 Create DDL.