**************************-CDT XSD-**************************************** 

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="urn:com:appian:types" targetNamespace="urn:com:appian:types">
  <xsd:complexType name="SSB_IAL_EMP_DETAILS_VIEW">
    <xsd:annotation>
      <xsd:appinfo source="appian.jpa">@Table(name = "SSB_IAL_EMP_DET_GRID_VIEW")</xsd:appinfo>
      <xsd:documentation><![CDATA[CDT for Employee View.]]></xsd:documentation>
    </xsd:annotation>
    <xsd:sequence>
      <xsd:element name="id" nillable="true" type="xsd:int">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Id @Column(name="ID", columnDefinition="INT(11)")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="empId" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="EMP_ID", columnDefinition="VARCHAR(255)")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="fullName" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="FULL_NAME", columnDefinition="VARCHAR(511)")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="deptId" nillable="true" type="xsd:int">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="DEPT_ID", columnDefinition="INT(11)")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="department" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="DEPT_NAME", columnDefinition="VARCHAR(255)")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="skills" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="SKILLS", columnDefinition="TEXT")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="doj" nillable="true" type="xsd:date">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="DOJ", columnDefinition="DATE")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="contactAddress" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="CONTACT_ADD", columnDefinition="VARCHAR(511)")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="permanentAddress" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(name="PERMANENT_ADD", columnDefinition="VARCHAR(511)")</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>



************************** DB View *************************************

CREATE OR REPLACE VIEW SSB_IAL_EMP_DET_GRID_VIEW AS 
SELECT ED.id "ID", ED.empid "EMP_ID", CONCAT(ED.fname, " ", ED.lname) "FULL_NAME", D.deptid "DEPT_ID", D.deptname "DEPT_NAME", 
GROUP_CONCAT(SK.elt) "SKILLS", ED.doj "DOJ", 
CONCAT(AD1.line1, ", ", IF(AD1.line2 IS NULL, "", AD1.line2 | ", "), 
C1.name, ", ", S1.name, ", ", CO1.name, " - ", AD1.pin) "CONTACT_ADD",
CONCAT(AD2.line1, ", ", IF(AD2.line2 IS NULL, "", AD2.line2 | ", "), 
C2.name, ", ", S2.name, ", ", CO2.name, " - ", AD2.pin) "PERMANENT_ADD"
FROM SSB_IAL_employeedetails ED
JOIN ssbialdepartment D ON D.deptid = ED.ssbildprtmnt_dprtmnt_deptid
LEFT JOIN ssbialemplyeedetails_skills SK ON SK.ssbilemplyedetils_skills_id = ED.id
LEFT JOIN ssbialemployeeaddress AD1 ON AD1.ssbilemplyedetils_ddress_id = ED.id AND AD1.ispermanent IN (NULL, FALSE)
LEFT JOIN IAL_CITIES C1 ON C1.id = AD1.ssbialcities_city_id
LEFT JOIN IAL_STATES S1 ON S1.id = C1.stateid
LEFT JOIN IAL_COUNTRIES CO1 ON CO1.id = S1.country_id
LEFT JOIN ssbialemployeeaddress AD2 ON AD2.ssbilemplyedetils_ddress_id = ED.id AND AD2.ispermanent IN (NULL, TRUE)
LEFT JOIN IAL_CITIES C2 ON C2.id = AD2.ssbialcities_city_id
LEFT JOIN IAL_STATES S2 ON S2.id = C2.stateid
LEFT JOIN IAL_COUNTRIES CO2 ON CO2.id = S2.country_id
GROUP BY ED.id
ORDER BY ED.id;