CREATE VIEW S_PRS.V_PERSONNE_PHYSIQUE
AS
SELECT PG.*, PSP_NOM , PSP_PRENOM, PSP_DATE_NAISSANCE
FROM   S_PRS.T_PERSONNE_GENERIQUE_PSG AS PG
       INNER JOIN S_PRS.T_PERSONNE_PHYSIQUE_PSP AS PP
          ON PG.PSG_ID = PP.PSG_ID;

CREATE VIEW S_PRS.V_PERSONNE_MORALE
AS
SELECT PG.*,PSM_RAISON_SOCIALE, PSM_ENSEIGNE, PSM_SIRET
FROM   S_PRS.T_PERSONNE_GENERIQUE_PSG AS PG
       INNER JOIN S_PRS.T_PERSONNE_MORALE_PSM AS PM
          ON PG.PSG_ID = PM.PSG_ID;

CREATE VIEW S_PRS.V_PERSONNE
AS
SELECT PG.*, CASE 
                WHEN PP.PSG_ID IS NULL 
                   THEN 'Morale' 
                ELSE 'Physique' 
             END AS TYPE_PERSONNE,
       COALESCE(PSP_NOM, PSM_RAISON_SOCIALE) AS NOM,
       COALESCE(PSP_PRENOM, PSM_ENSEIGNE) AS PRENOM_OU_ENSEIGNE,
       PSP_DATE_NAISSANCE AS DATE_NAISSANCE_PERSONNE_PHYSIQUE,
       PSM_SIRET AS SNUMERO_SIRET_PERSONNE_MORALE
FROM   S_PRS.T_PERSONNE_GENERIQUE_PSG AS PG
       LEFT OUTER JOIN S_PRS.T_PERSONNE_PHYSIQUE_PSP AS PP
          ON PG.PSG_ID = PP.PSG_ID
       LEFT OUTER JOIN S_PRS.T_PERSONNE_MORALE_PSM AS PM
          ON PG.PSG_ID = PM.PSG_ID;
