PJF_PERSON_COA_ALL_V

Details

• Schema: FUSION

• Object owner: PJF

• Object type: VIEW

Columns

Name

PERSON_NUMBER PERSON_NUMBER_1 PERSON_NUMBER_2 PERSON_NUMBER_3 PERSON_NUMBER_4 PERSON_NUMBER_5 PERSON_NUMBER_6 PERSON_NUMBER_7 PERSON_NUMBER_8 PERSON_NUMBER_9 PERSON_NUMBER_10 PERSON_NAME SUMMARY_FLAG FLEX_VALUE_ATTRIBUTE1 FLEX_VALUE_ATTRIBUTE2 FLEX_VALUE_ATTRIBUTE3 FLEX_VALUE_ATTRIBUTE4 FLEX_VALUE_ATTRIBUTE5 FLEX_VALUE_ATTRIBUTE6 FLEX_VALUE_ATTRIBUTE7 FLEX_VALUE_ATTRIBUTE8 FLEX_VALUE_ATTRIBUTE9 FLEX_VALUE_ATTRIBUTE10 FLEX_VALUE_ATTRIBUTE11 FLEX_VALUE_ATTRIBUTE12 FLEX_VALUE_ATTRIBUTE13 FLEX_VALUE_ATTRIBUTE14 FLEX_VALUE_ATTRIBUTE15 FLEX_VALUE_ATTRIBUTE16 FLEX_VALUE_ATTRIBUTE17 FLEX_VALUE_ATTRIBUTE18 FLEX_VALUE_ATTRIBUTE19 FLEX_VALUE_ATTRIBUTE20 CREATION_DATE LAST_UPDATE_DATE

Query

SQL_Statement

SELECT DISTINCT CAST(REVERSE( SUBSTR(REVERSE(PersonDPEO.PERSON_NUMBER),1,25)) AS VARCHAR2(25) ) PERSON_NUMBER, CAST(SUBSTR('0000000000' ||PersonDPEO.PERSON_NUMBER, (LENGTH('0000000000' ||PersonDPEO.PERSON_NUMBER))) AS VARCHAR2(25)) PERSON_NUMBER_1, CAST(SUBSTR('0000000000' ||PersonDPEO.PERSON_NUMBER, (LENGTH('0000000000' ||PersonDPEO.PERSON_NUMBER)-1)) AS VARCHAR2(25)) PERSON_NUMBER_2, CAST(SUBSTR('0000000000' ||PersonDPEO.PERSON_NUMBER, (LENGTH('0000000000' ||PersonDPEO.PERSON_NUMBER)-2)) AS VARCHAR2(25)) PERSON_NUMBER_3, CAST(SUBSTR('0000000000' ||PersonDPEO.PERSON_NUMBER, (LENGTH('0000000000' ||PersonDPEO.PERSON_NUMBER)-3)) AS VARCHAR2(25)) PERSON_NUMBER_4, CAST(SUBSTR('0000000000' ||PersonDPEO.PERSON_NUMBER, (LENGTH('0000000000' ||PersonDPEO.PERSON_NUMBER)-4)) AS VARCHAR2(25)) PERSON_NUMBER_5, CAST(SUBSTR('0000000000' ||PersonDPEO.PERSON_NUMBER, (LENGTH('0000000000' ||PersonDPEO.PERSON_NUMBER)-5)) AS VARCHAR2(25)) PERSON_NUMBER_6, CAST(SUBSTR('0000000000' ||PersonDPEO.PERSON_NUMBER, (LENGTH('0000000000' ||PersonDPEO.PERSON_NUMBER)-6)) AS VARCHAR2(25)) PERSON_NUMBER_7, CAST(SUBSTR('0000000000' ||PersonDPEO.PERSON_NUMBER, (LENGTH('0000000000' ||PersonDPEO.PERSON_NUMBER)-7)) AS VARCHAR2(25)) PERSON_NUMBER_8, CAST(SUBSTR('0000000000' ||PersonDPEO.PERSON_NUMBER, (LENGTH('0000000000' ||PersonDPEO.PERSON_NUMBER)-8)) AS VARCHAR2(25)) PERSON_NUMBER_9, CAST(SUBSTR('0000000000' ||PersonDPEO.PERSON_NUMBER, (LENGTH('0000000000' ||PersonDPEO.PERSON_NUMBER)-9)) AS VARCHAR2(25)) PERSON_NUMBER_10, CAST(PersonNameDPEO2.LIST_NAME AS VARCHAR2(240)) PERSON_NAME, CAST('N' AS VARCHAR2(30)) SUMMARY_FLAG, CAST('Y' AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE1, CAST( 'Y' AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE2, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE3, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE4, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE5, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE6, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE7, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE8, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE9, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE10, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE11, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE12, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE13, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE14, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE15, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE16, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE17, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE18, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE19, CAST( NULL AS VARCHAR2(30)) FLEX_VALUE_ATTRIBUTE20, PersonNameDPEO2.CREATION_DATE CREATION_DATE, PersonNameDPEO2.LAST_UPDATE_DATE LAST_UPDATE_DATE FROM (SELECT pp.Person_number, MAX(pp.person_id) person_id FROM PER_ALL_PEOPLE_F pp, PER_ALL_ASSIGNMENTS_M a, PER_PERSON_TYPES_VL b WHERE a.PERSON_TYPE_ID = b.PERSON_TYPE_ID AND b.SYSTEM_PERSON_TYPE IN ( 'EMP', 'EX_EMP', 'CWK','EX_CWK') AND a.person_id = pp.person_id GROUP BY person_number ) PersonDPEO, (SELECT PERSON_ID , MAX(rowid) person_name_rowid FROM PER_PERSON_NAMES_F_V GROUP BY PERSON_ID ) PersonNameDPEO, PER_PERSON_NAMES_F_V PersonNameDPEO2 WHERE PersonDPEO.person_id = PersonNameDPEO.PERSON_ID AND PersonNameDPEO.person_name_rowid = PersonNameDPEO2.rowid UNION SELECT '0', '0', '00', '000', '0000', '00000', '000000', '0000000', '00000000', '000000000', '0000000000', '0', 'N', 'Y', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, TO_DATE('01/01/1950', 'DD/MM/YYYY'), TO_DATE('01/01/1950', 'DD/MM/YYYY') FROM dual WHERE NOT EXISTS (SELECT 1 FROM PER_ALL_PEOPLE_F WHERE person_number = '0' )