Проектирование и разработка БД Oracle для информатизации объектов культуры

Blocker

DB3

Поддержка объектов

на немецком языке

Проверка создания, редактирования и удаления объектов на немецком языках.

PL\SQL Developer 7.1.5.1398

1. Выбрать объектный тип, в котором есть атрибуты на немецком языке.

2.Создать объект такого объектного типа.

3.Заполнить значения атрибутов объектного типа литералами на немецком языке.

4.Сохранить в БД.

5. Выбрать этот объект из БД.

Наличие объектов и корректный вывод

значений атрибутов объекта на немецком языке.

Blocker

DB4

Поддержка переключения работы информационной системы с русского на английский язык

Проверка функционирования переключения ра-боты информа-ционной системы с русского на ан-глийский язык

Internet Explorer 6.0.

1. Установить в приложении русский язык.

2. Выбрать объект.

3.Переключиться на английский язык.

4. Выбрать объект.

Корректное отображение

значений атрибутов объекта как русском так и на английских языках

Critical

DB5

Поддержка переключения работы информационной системы с английского на русский язык

Проверка функционирования переключения ра-боты информа-ционной системы с русского на ан-глийский язык

PL\SQL Developer 7.1.5.1398

1. Установить в приложении английский язык.

2. Выбрать объект.

3.Переключиться на русский язык.

4. Выбрать объект

Корректное отображение значений атрибутов объекта как русском так и на английских языках

Critical

DB6

Поддержка без-языкового типа MULTILANG

Проверка функционирования безязыкового типа MULTILANG

PL\SQL Developer 7.1.5.1398

1. Выбрать объектный тип, в котором есть атрибуты типа данных MULTI-LANG.

2.Создать объект та-кого объектного типа.

3.Заполнить значения атрибутов объектного типа. 4.Сохранить в БД.

5. Выбрать этот объект из БД.

Наличие объектов и корректный вывод значения атрибута.

Major

3.3 Сводная таблица тестирования (test log)

На основе данных этой таблицы таблицы 3.3. Test cases составлен test log. Данные тестлога приведены в таблице 3.4. На основании данных тестлога можно проследить примерный график разработки БД. В таблице если стоит Ok - то тогда тест-кейс успешно выполнен, иначе нет. Все тест-кейсы были выполнены, по итогам тест-лога видно, что БД разработана.

Таблица3.4. Test Log.

Test case	Build 1 01.09.09	Вuild 2 01.10.09	Build 3 01.11.09	Build 4 01.12.09
DB1		Ok	Ok	Ok
DB2	Ok	Ok	Ok	Ok
DB3		Ok	Ok	Ok
DB4			Ok	Ok
DB5			Ok	Ok
DB6				Ok

Заключение

B данной дипломной работе была спроектирована и разработана База Данных для информационной системы архива, в которой должны храниться и обрабатываться айдиофайлы, фотофайлы и текстовая информация, характеризуемая сильной разреженностью данных. В ходе разработки была предложена и выбрана в качестве модели БД - модель Entity-Attribute-Value. В данной работе был решен ряд интересных задач, а именно: реализация системы разграничения прав пользователей на объекты с помощью пользовательских представлений и пакета SecurityData. Кроме того, со стороны БД система обеспечена всей необходимой функциональностью: в частности, поисковым механизмом, поддержка логирования пользовательских данных и восстановления утерянных данных.

Реализованная БД позволяет модифицировать пользовательские объекты без блокировки системы. Напомню, что в СУБД Oracle при любой DDL операции производится окончание транзакции и блокировка всех таблиц, над которыми производится данная DDL операция. Эта проблема успешно обойдена использованием EAV-модели. К EAV-модели для ускорения работы выполнения запросов к пользовательским данным была добавлена обычная реляционная модель в виде плоских реляционных таблиц.

База данных осуществляет полноценную поддержку 3 языков (русский, английский и немецкий), а также безязыкового символьного типа, т.е. типа, значения которого уникально как для русской, так и английской и немецкой версии БД. Этот тип нужен, например, для шифра, который уникален и одинаков на любом языке. Кроме того, БД генерирует XML документы для клиентского приложения, т.к. пользовательскому GUI-интерфейсу информационной системы для динамического построения списков данных нужно возвращать динамически генерируемые XML-документы для их последующего вывода в клиентском приложении.

В целом, считаю, что поставленные задачи в списке исходных требований были выполнены, а дипломную работу - завершенной.

Список использованной литературы

1. Змеев О.А., Новиков Д.В., Моисеев А.Н. К вопросу проектирования уровня хранения в виде ООРБД // Вестник ТГУ. Приложение №1 (II), сентябрь 2002. Доклады IV Всероссийской конференции с международным участием «Новые информационные технологии в исследовании сложных структур», Томск, 10-13 сентября 2002. С. 363-367.

2. Ларман К. Применение UML и шаблонов проектирования. 2-е изд. М.: Изд. дом «Вильямс», 2002. 624 с.

3. Тенцер А. База данных - хранилище объектов // КомпьютерПресс. 2001. №8.

4. Войтиков К.Ю., Змеев О.А., Моисеев А.Н. Объектный подход к проблеме проектирова-ния подсистемы нормативно-справочной информации // Обработка данных и управление в сложных системах. Томск: Изд-во Том. ун-та, 2002. Вып. 4. С. 13-20.

5. Том Кайт. Oracle для профессионалов. Книга 1. Архитектура и основные особенности. Третье издание, переработанное и дополненное: Пер. с англ./ Том Кайт - СПб.: ООО “ДиаСофтЮП”, 2005 - С.312.

6. Дейт, К. Дж. Введение в системы баз данных = Introduction to Database Systems. -- 8-е изд. -- М.: “Вильямс”, 2006. -- 1328 с.

7. Томас Коннолли, Каролин Бегг Базы данных. Проектирование, реализация и сопровождение. Теория и практика = Database Systems: A Practical Approach to Design, Implementation, and Management Third Edition. -- 3-е изд. -- М. Вильямс”:, 2003. -- 1436 с.

8. Кузнецов Сергей Дмитриевич “Основы баз данных” -- 1-е изд. -- М.: «Интернет-университет информационных технологий - ИНТУИТ.ру», 2005. -- 488 с.

9. Дейт, К. Дж. Введение в системы баз данных, 8-е издание.: Пер. с англ. -- М.: Издательский дом "Вильямс", 2005. - 103-104 с.

10. Дейт, К. Дж. Введение в системы баз данных, 8-е издание.: Пер. с англ. -- М.: Издательский дом "Вильямс", 2005. - 108 с.

11. С.Д. Кузнецов. Основы современных баз данных, информационно-аналитические материалы Центра информационных материалов. Глава 22.

12. Дуглас Крамер. Платформа Java. Официальное издание. - JavaSoft, Sun Microsystems Inc. 5-7 c.

Приложение 1. Листинги пакетов

Листинг пакета DATAMODIFICATION

Пакет DATAMODIFICATION предназначен для вывода аудита по объекту, объектному типу или атрибуту в виде XML-документа, а также подсчета количества транзакций проводимых над атрибутом, объектом или объектным типом, вывода прав на объект в виде XML-документа. Вывод аудита в виде XML-документа возможен в постраничном виде - это необходимо для отображения большого количества транзакций в приложении. Код заголовка пакета и комментарии к заголовку пакета (см. таб.Пр.1.1.) ниже:

Таблица Пр.1.1. Комментарии к заголовку пакета DATAMODIFICATION.

номер строчки кода	Комментарии
3	Функция showAuditByAttribute предназначена для вывода аудита по конкретному атрибуту.
5	Функция showAuditByObject предназначена для вывода аудита по конкретному объекту.
6	Функция showAuditByObjectType предназначена для вывода аудита по конкретному объектному типу.
7	Функция countTransactionsByAttribute предназначена для вывода количества транзакций по конкретному атрибуту.
8	Функция countTransactionsByObject предназначена для вывода количества транзакций по конкретному объекту.
9	Функция countTransactionsByObjectType предназначена для вывода количества транзакций по конкретному объектному типу.
10	Функция pageAuditByAttribute предназначена для вывода аудита по конкретному атрибуту в постраничном виде.
11	Функция pageAuditByObject предназначена для вывода аудита по конкретному объекту в постраничном виде.
12	Функция pageAuditByObjectType предназначена для вывода аудита по конкретному объектному типу в постраничном виде.
13	Функция showObjectRights предназначена для вывода прав на объект в виде XML-документа.
14	Функция showFoundObjectRights предназначена для вывода прав на найденные объекты в таблице Tempfoundobjects в виде XML-документа

SQL> create or replace package DATAMODIFICATION as

2

3 FUNCTION showAuditByAttribute(nIDAttribute attributes.id%TYPE, nIdLanguage NUMBER) RETURN XMLTYPE;

4

5 function showAuditByObject(nIDObject objects.id%TYPE, nIdLanguage NUMBER) RETURN XMLTYPE;

6 function showAuditByObjectType(nIDObjectType objecttypes.id%TYPE, nIdLanguage NUMBER) RETURN XMLTYPE;

7 function countTransactionsByAttribute(nIDAttribute attributes.id%TYPE, nIdLanguage NUMBER) RETURN NUMBER;

8 function countTransactionsByObject(nIDObject objects.id%TYPE, nIdLanguage NUMBER) RETURN NUMBER;

9 function countTransactionsByObjectType(nIDObjectType objecttypes.id%TYPE, nIdLanguage NUMBER) RETURN NUMBER;

10 FUNCTION pageAuditByAttribute(nIDAttribute attributes.id%TYPE, nIdLanguage NUMBER, nPageNumber NUMBER, nAmountPerPage NUMBER) RETURN XMLTYPE;

11 FUNCTION pageAuditByObject(nIDObject objects.id%TYPE, nIdLanguage NUMBER, nPageNumber NUMBER, nAmountPerPage NUMBER) RETURN XMLTYPE;

12 FUNCTION pageAuditByObjectType(nIDObjectType objecttypes.id%TYPE, nIdLanguage NUMBER, nPageNumber NUMBER, nAmountPerPage NUMBER) RETURN XMLTYPE;

13 function showObjectRights(nIDObject objects.id%TYPE) RETURN XMLTYPE;

14 FUNCTION showFoundObjectRights RETURN XMLTYPE;

15

16 end DATAMODIFICATION;

17 /

Тело пакета DATAMODIFICATION представлено ниже:

SQL> create or replace package body DATAMODIFICATION as

2

3 FUNCTION showAuditByAttribute(nIDAttribute attributes.id%TYPE, nIdLanguage NUMBER) RETURN XMLTYPE AS

4 nDataType attributes.iddatatype%TYPE;

5 xDocument XMLTYPE;

6 vName datatypes.name%TYPE;

7 BEGIN

8 SELECT idDataType INTO nDataType FROM attributes WHERE id = nIDAttribute;

9 SELECT name INTO vName FROM datatypes WHERE id = nDataType;

10 IF vName = 'MULTILANG' THEN

11

12 SELECT

13 XMLElement("attribute_summary", XMLAttributes(nIDAttribute AS id),

14 XMLAgg(XMLElement("transaction",

15 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

16 XMLAgg(

17 XMLConcat(

18 XMLElement("attribute",

19 XMLAttributes(

20 nIDAttribute AS id_attribute,

21 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

22 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

23 'MULTILANG' AS attribute_datatype

24),

25 XMLElement("old_value_attribute",A.old_value),

26 XMLElement("new_value_attribute",A.new_value)

27)

28)--XMLConcat

29)--XMLAgg

30)--XMLAttributes

31 order by A.modified_time desc

32)

33)

34 INTO xDocument

35 FROM (

36 SELECT a.*, uml.*, s.*, rownum r

37 FROM attributes a, usermultilangvalueslog uml, systemdatavalues s

38 WHERE a.id = nIDAttribute and uml.id_attribute = nIDAttribute AND uml.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

39)A

40 GROUP BY A.modified_time, A.MODIFIED_USER;

41

42 RETURN xDocument;

43 ELSIF vName = 'VARCHAR2' THEN

44

45 SELECT

46 XMLElement("attribute_summary", XMLAttributes(nIDAttribute AS id),

47 XMLAgg(XMLElement("transaction",

48 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

49 XMLAgg(

50 XMLConcat(

51 XMLElement("attribute",

52 XMLAttributes(

53 nIDAttribute AS id_attribute,

54 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

55 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

56 'STRING' AS attribute_datatype

57),

58 XMLElement("old_value_attribute",A.old_value),

59 XMLElement("new_value_attribute",A.new_value)

60)

61)--XMLConcat

62)--XMLAgg

63)--XMLAttributes

64 order by A.modified_time desc

65)

66)

67 INTO xDocument

68 FROM (

69 SELECT a.*, usl.*, s.*, rownum r

70 FROM attributes a, userstringvalueslog usl, systemdatavalues s

71 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

72)A

73 GROUP BY A.modified_time, A.MODIFIED_USER;

74

75 RETURN xDocument;

76

77 ELSIF vName = 'NUMBER' THEN

78

79 SELECT

80 XMLElement("attribute_summary", XMLAttributes(nIDAttribute AS id),

81 XMLAgg(XMLElement("transaction",

82 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

83 XMLAgg(

84 XMLConcat(

85 XMLElement("attribute",

86 XMLAttributes(

87 nIDAttribute AS id_attribute,

88 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

89 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

90 'NUMBER' AS attribute_datatype

91),

92 XMLElement("old_value_attribute",A.old_value),

93 XMLElement("new_value_attribute",A.new_value)

94)

95)--XMLConcat

96)--XMLAgg

97)--XMLAttributes

98 order by A.modified_time desc

99)

100)

101 INTO xDocument

102 FROM (

103 SELECT a.*, usl.*, s.*, rownum r

104 FROM attributes a, usernumbervalueslog usl, systemdatavalues s

105 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

106)A

107 GROUP BY A.modified_time, A.MODIFIED_USER;

108

109 RETURN xDocument;

110

111 ELSIF vName = 'DATE' THEN

112

113 SELECT

114 XMLElement("attribute_summary", XMLAttributes(nIDAttribute AS id),

115 XMLAgg(XMLElement("transaction",

116 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

117 XMLAgg(

118 XMLConcat(

119 XMLElement("attribute",

120 XMLAttributes(

121 nIDAttribute AS id_attribute,

122 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

123 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

124 'DATE' AS attribute_datatype

125),

126 XMLElement("old_value_attribute",A.old_value),

127 XMLElement("new_value_attribute",A.new_value)

128)

129)--XMLConcat

130)--XMLAgg

131)--XMLAttributes

132 order by A.modified_time desc

133)

134)

135 INTO xDocument

136 FROM (

137 SELECT a.*, usl.*, s.*, rownum r

138 FROM attributes a, userdatevalueslog usl, systemdatavalues s

139 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

140)A

141 GROUP BY A.modified_time, A.MODIFIED_USER;

142

143 RETURN xDocument;

144

145 ELSIF vName = 'CLOB' THEN

146

147 SELECT

148 XMLElement("attribute_summary", XMLAttributes(nIDAttribute AS id),

149 XMLAgg(XMLElement("transaction",

150 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

151 XMLAgg(

152 XMLConcat(

153 XMLElement("attribute",

154 XMLAttributes(

155 nIDAttribute AS id_attribute,

156 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

157 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

158 'CLOB' AS attribute_datatype

159),

160 XMLElement("old_value_attribute",A.old_value),

161 XMLElement("new_value_attribute",A.new_value)

162)

163)--XMLConcat

164)--XMLAgg

165)--XMLAttributes

166 order by A.modified_time desc

167)

168)

169 INTO xDocument

170 FROM (

171 SELECT a.*, usl.*, s.*, rownum r

172 FROM attributes a, userclobvalueslog usl, systemdatavalues s

173 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

174)A

175 GROUP BY A.modified_time, A.MODIFIED_USER;

176

177 RETURN xDocument;

178

179 ELSIF vName = 'DICTIONARY' THEN

180

181 SELECT

182 XMLElement("attribute_summary", XMLAttributes(nIDAttribute AS id),

183 XMLAgg(XMLElement("transaction",

184 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

185 XMLAgg(

186 XMLConcat(

187 XMLElement("attribute",

188 XMLAttributes(

189 nIDAttribute AS id_attribute,

190 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

191 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

192 'DICTIONARY' AS attribute_datatype

193),

194 XMLElement("old_value_attribute",A.old_value),

195 XMLElement("new_value_attribute",A.new_value)

196)

197)--XMLConcat

198)--XMLAgg

199)--XMLAttributes

200 order by A.modified_time desc

201)

202)

203 INTO xDocument

204 FROM (

205 SELECT a.*, usl.*, s.*, rownum r

206 FROM attributes a, userdictionaryvalueslog usl, systemdatavalues s

207 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

208)A

209 GROUP BY A.modified_time, A.MODIFIED_USER;

210

211 RETURN xDocument;

212

213 END IF;

214 RETURN NULL;

215 END showAuditByAttribute;

216

217 FUNCTION showAuditByObject(nIDObject objects.id%TYPE, nIdLanguage NUMBER) RETURN XMLTYPE AS

218 xDocument XMLTYPE;

219 BEGIN

220

221 SELECT

222 XMLElement("object_summary", XMLAttributes(nIDObject AS id),

223 XMLAgg(XMLElement("transaction",

224 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

225 XMLAgg(

226 XMLConcat(

227 XMLElement("attribute",

228 XMLAttributes(

229 A.new_idattribute AS id_attribute,

230 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

231 A.type_of_operation AS type_of_operation, A.attribute_note AS attribute_note,

232 A.attribute_datatype AS attribute_datatype, A.datatype_length AS datatype_length

233),

234 XMLElement("old_value_attribute",A.old_value),

235 XMLElement("new_value_attribute",A.new_value)

236),

237 XMLElement("object",

238 XMLAttributes(

239 A.OLD_IDPARENTOBJECT AS OLD_IDPARENTOBJECT,

240 A.NEW_IDPARENTOBJECT AS NEW_IDPARENTOBJECT,

241 A.OBJECTTYPE NEW_OBJECTTYPE,

242 A.OLD_OWNER OLD_OWNER,

243 A.NEW_OWNER NEW_OWNER,

244 A.OLD_ORIGHTS OLD_ORIGHTS,

245 A.NEW_ORIGHTS NEW_ORIGHTS,

246 A.OLD_ARIGHTS OLD_ARIGHTS,

247 A.NEW_ARIGHTS NEW_ARIGHTS,

248 A.OPERATION OPERATION,

249 A.OBJECTNOTE OBJECTNOTE

250)

251)

252)--XMLConcat

253)--XMLAgg

254)--XMLAttributes

255 order by A.modified_time desc

256)

257)

258 INTO xDocument

259 FROM (

260 --usermultilangvalueslog

261 SELECT uml.id_attribute AS new_idattribute,

262 to_clob(uml.old_value) AS old_value,

263 to_clob(uml.new_value) AS new_value,

264 uml.old_serialnumber AS old_serialnumber,

265 uml.new_serialnumber AS new_serialnumber,

266 uml.modified_user AS modified_user,

267 uml.modified_time AS modified_time,

268 uml.type_of_operation AS type_of_operation,

269 s.value AS attribute_note,

270 'MULTILANG' AS attribute_datatype,

271 a.length AS datatype_length,

272 null OLD_IDPARENTOBJECT,

273 null NEW_IDPARENTOBJECT,

274 null OBJECTTYPE,

275 null OLD_OWNER,

276 null NEW_OWNER,

277 null OLD_ORIGHTS,

278 null NEW_ORIGHTS,

279 null OLD_ARIGHTS,

280 null NEW_ARIGHTS,

281 null OPERATION,

282 null OBJECTNOTE

283 FROM objects o, attributes a, objecttypes ot, usermultilangvalueslog uml, systemdatavalues s

284 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

285 AND

286 (uml.id_object = o.id and uml.id_attribute = a.id)

287

288 UNION ALL

289 --userstringvalueslog

290 SELECT uml.id_attribute AS new_idattribute,

291 to_clob(uml.old_value) AS old_value,

292 to_clob(uml.new_value) AS new_value,

293 uml.old_serialnumber AS old_serialnumber,

294 uml.new_serialnumber AS new_serialnumber,

295 uml.modified_user AS modified_user,

296 uml.modified_time AS modified_time,

297 uml.type_of_operation AS type_of_operation,

298 s.value AS attribute_note,

299 'STRING' AS attribute_datatype,

300 a.length AS datatype_length,

301 null OLD_IDPARENTOBJECT,

302 null NEW_IDPARENTOBJECT,

303 null OBJECTTYPE,

304 null OLD_OWNER,

305 null NEW_OWNER,

306 null OLD_ORIGHTS,

307 null NEW_ORIGHTS,

308 null OLD_ARIGHTS,

309 null NEW_ARIGHTS,

310 null OPERATION,

311 null OBJECTNOTE

312 FROM objects o, attributes a, objecttypes ot, userstringvalueslog uml, systemdatavalues s

313 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

314 AND

315 (uml.id_object = o.id and uml.id_attribute = a.id)

316

317 UNION ALL

318 --userdatevalueslog

319 SELECT uml.id_attribute AS new_idattribute,

320 to_clob(uml.old_value) AS old_value,

321 to_clob(uml.new_value) AS new_value,

322 uml.old_serialnumber AS old_serialnumber,

323 uml.new_serialnumber AS new_serialnumber,

324 uml.modified_user AS modified_user,

325 uml.modified_time AS modified_time,

326 uml.type_of_operation AS type_of_operation,

327 s.value AS attribute_note,

328 'DATE' AS attribute_datatype,

329 a.length AS datatype_length,

330 null OLD_IDPARENTOBJECT,

331 null NEW_IDPARENTOBJECT,

332 null OBJECTTYPE,

333 null OLD_OWNER,

334 null NEW_OWNER,

335 null OLD_ORIGHTS,

336 null NEW_ORIGHTS,

337 null OLD_ARIGHTS,

338 null NEW_ARIGHTS,

339 null OPERATION,

340 null OBJECTNOTE

341 FROM objects o, attributes a, objecttypes ot, userdatevalueslog uml, systemdatavalues s

342 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

343 AND

344 (uml.id_object = o.id and uml.id_attribute = a.id)

345

346 UNION ALL

347 --userclobvalueslog

348 SELECT uml.id_attribute AS new_idattribute,

349 to_clob(uml.old_value) AS old_value,

350 to_clob(uml.new_value) AS new_value,

351 uml.old_serialnumber AS old_serialnumber,

352 uml.new_serialnumber AS new_serialnumber,

353 uml.modified_user AS modified_user,

354 uml.modified_time AS modified_time,

355 uml.type_of_operation AS type_of_operation,

356 s.value AS attribute_note,

357 'CLOB' AS attribute_datatype,

358 a.length AS datatype_length,

359 null OLD_IDPARENTOBJECT,

360 null NEW_IDPARENTOBJECT,

361 null OBJECTTYPE,

362 null OLD_OWNER,

363 null NEW_OWNER,

364 null OLD_ORIGHTS,

365 null NEW_ORIGHTS,

366 null OLD_ARIGHTS,

367 null NEW_ARIGHTS,

368 null OPERATION,

369 null OBJECTNOTE

370 FROM objects o, attributes a, objecttypes ot, userclobvalueslog uml, systemdatavalues s

371 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

372 AND

373 (uml.id_object = o.id and uml.id_attribute = a.id)

374

375 UNION ALL

376 --usernumbervalueslog

377 SELECT uml.id_attribute AS new_idattribute,

378 to_clob(uml.old_value) AS old_value,

379 to_clob(uml.new_value) AS new_value,

380 uml.old_serialnumber AS old_serialnumber,

381 uml.new_serialnumber AS new_serialnumber,

382 uml.modified_user AS modified_user,

383 uml.modified_time AS modified_time,

384 uml.type_of_operation AS type_of_operation,

385 s.value AS attribute_note,

386 'CLOB' AS attribute_datatype,

387 a.length AS datatype_length,

388 null OLD_IDPARENTOBJECT,

389 null OBJECTTYPE,

390 null NEW_OBJECTTYPE,

391 null OLD_OWNER,

392 null NEW_OWNER,

393 null OLD_ORIGHTS,

394 null NEW_ORIGHTS,

395 null OLD_ARIGHTS,

396 null NEW_ARIGHTS,

397 null OPERATION,

398 null OBJECTNOTE

399 FROM objects o, attributes a, objecttypes ot, usernumbervalueslog uml, systemdatavalues s

400 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

401 AND

402 (uml.id_object = o.id and uml.id_attribute = a.id)

403

404 UNION ALL

405 --userdictionaryvalueslog

406 SELECT uml.id_attribute AS new_idattribute,

407 to_clob(uml.old_value) AS old_value,

408 to_clob(uml.new_value) AS new_value,

409 uml.old_serialnumber AS old_serialnumber,

410 uml.new_serialnumber AS new_serialnumber,

411 uml.modified_user AS modified_user,

412 uml.modified_time AS modified_time,

413 uml.type_of_operation AS type_of_operation,

414 s.value AS attribute_note,

415 'CLOB' AS attribute_datatype,

416 a.length AS datatype_length,

417 null OLD_IDPARENTOBJECT,

418 null NEW_IDPARENTOBJECT,

419 null OBJECTTYPE,

420 null OLD_OWNER,

421 null NEW_OWNER,

422 null OLD_ORIGHTS,

423 null NEW_ORIGHTS,

424 null OLD_ARIGHTS,

425 null NEW_ARIGHTS,

426 null OPERATION,

427 null OBJECTNOTE

428 FROM objects o, attributes a, objecttypes ot, userdictionaryvalueslog uml, systemdatavalues s

429 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

430 AND

431 (uml.id_object = o.id and uml.id_attribute = a.id)

432

433 UNION ALL

434

435 SELECT null AS new_idattribute,

436 null AS old_value,

437 null AS new_value,

438 null AS old_serialnumber,

439 null AS new_serialnumber,

440 OL.MODIFIED_USER AS modified_user,

441 OL.MODIFIED_TIME AS modified_time,

442 null AS type_of_operation,

443 null AS attribute_note,

444 null AS attribute_datatype,

445 null AS datatype_length,

446 OL.OLD_IDPARENTOBJECT OLD_IDPARENTOBJECT,

447 OL.NEW_IDPARENTOBJECT NEW_IDPARENTOBJECT,

448 OL.NEW_OBJECTTYPE NEW_OBJECTTYPE,

449 OL.OLD_OWNER OLD_OWNER,

450 OL.NEW_OWNER NEW_OWNER,

451 OL.OLD_ORIGHTS OLD_ORIGHTS,

452 OL.NEW_ORIGHTS NEW_ORIGHTS,

453 OL.OLD_ARIGHTS OLD_ARIGHTS,

454 OL.NEW_ARIGHTS NEW_ARIGHTS,

455 OL.TYPE_OF_OPERATION OPERATION,

456 s.value OBJECTNOTE

457 FROM objectslog OL, systemdatavalues s

458 WHERE OL.ID_OBJECT = nIDObject and s.idparent = OL.ID_OBJECT AND S.IDLANGUAGE = nIdLanguage

459

460) A

461 GROUP BY A.modified_time, A.MODIFIED_USER;

462

463 RETURN xDocument;

464

465 END showAuditByObject;

466

467 function showAuditByObjectType(nIDObjectType objecttypes.id%TYPE, nIdLanguage NUMBER) RETURN XMLTYPE IS

468 xDocument XMLTYPE;

469 BEGIN

470

471 SELECT XMLElement("object_type",

472 XMLAttributes(1 AS idObjectType),

473 XMLElement("objects_summary",

474 XMLAgg(XMLElement("transaction",

475 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

476 XMLAgg(

477 XMLElement("object_info",

478 XMLAttributes(A.id AS "object_id"),

479 XMLElement("attribute",

480 XMLAttributes(

481 A.new_idattribute AS id_attribute,

482 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

483 A.type_of_operation AS type_of_operation, A.attribute_note AS attribute_note,

484 A.attribute_datatype AS attribute_datatype, A.datatype_length AS datatype_length

485),

486 XMLElement("old_value_attribute",A.old_value),

487 XMLElement("new_value_attribute",A.new_value)

488),

489 XMLElement("object_change",

490 XMLAttributes(

491 A.OLD_IDPARENTOBJECT AS OLD_IDPARENTOBJECT,

492 A.NEW_IDPARENTOBJECT AS NEW_IDPARENTOBJECT,

493 A.OBJECTTYPE NEW_OBJECTTYPE,

494 A.OLD_OWNER OLD_OWNER,

495 A.NEW_OWNER NEW_OWNER,

496 A.OLD_ORIGHTS OLD_ORIGHTS,

497 A.NEW_ORIGHTS NEW_ORIGHTS,

498 A.OLD_ARIGHTS OLD_ARIGHTS,

499 A.NEW_ARIGHTS NEW_ARIGHTS,

500 A.OPERATION OPERATION,

501 A.OBJECTNOTE OBJECTNOTE

502)

503)

504)--XMLConcat

505)--XMLAgg

506)--XMLAttributes

507 order by A.modified_time desc

508)

509)

510)

511 INTO xDocument

512 --showAuditByObject(id, nIdLanguage)

513 --) AS objects)

514 FROM (

515 SELECT B.*, rownum r

516 FROM (

517 --usermultilangvalueslog

518 SELECT

519 o.id AS id,

520 uml.id_attribute AS new_idattribute,

521 to_clob(uml.old_value) AS old_value,

522 to_clob(uml.new_value) AS new_value,

523 uml.old_serialnumber AS old_serialnumber,

524 uml.new_serialnumber AS new_serialnumber,

525 uml.modified_user AS modified_user,

526 uml.modified_time AS modified_time,

527 uml.type_of_operation AS type_of_operation,

528 s.value AS attribute_note,

529 'MULTILANG' AS attribute_datatype,

530 a.length AS datatype_length,

531 null OLD_IDPARENTOBJECT,

532 null NEW_IDPARENTOBJECT,

533 null OBJECTTYPE,

534 null OLD_OWNER,

535 null NEW_OWNER,

536 null OLD_ORIGHTS,

537 null NEW_ORIGHTS,

538 null OLD_ARIGHTS,

539 null NEW_ARIGHTS,

540 null OPERATION,

541 null OBJECTNOTE

542 FROM objects o, attributes a, objecttypes ot, usermultilangvalueslog uml, systemdatavalues s

543 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

544 AND

545 (uml.id_object = o.id and uml.id_attribute = a.id)

546

547 UNION ALL

548 --userstringvalueslog

549 SELECT

550 o.id AS id,

551 uml.id_attribute AS new_idattribute,

552 to_clob(uml.old_value) AS old_value,

553 to_clob(uml.new_value) AS new_value,

554 uml.old_serialnumber AS old_serialnumber,

555 uml.new_serialnumber AS new_serialnumber,

556 uml.modified_user AS modified_user,

557 uml.modified_time AS modified_time,

558 uml.type_of_operation AS type_of_operation,

559 s.value AS attribute_note,

560 'STRING' AS attribute_datatype,

561 a.length AS datatype_length,

562 null OLD_IDPARENTOBJECT,

563 null NEW_IDPARENTOBJECT,

564 null OBJECTTYPE,

565 null OLD_OWNER,

566 null NEW_OWNER,

567 null OLD_ORIGHTS,

568 null NEW_ORIGHTS,

569 null OLD_ARIGHTS,

570 null NEW_ARIGHTS,

571 null OPERATION,

572 null OBJECTNOTE

573 FROM objects o, attributes a, objecttypes ot, userstringvalueslog uml, systemdatavalues s

574 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

575 AND

576 (uml.id_object = o.id and uml.id_attribute = a.id)

577

578 UNION ALL

579 --userdatevalueslog

580 SELECT

581 o.id AS id,

582 uml.id_attribute AS new_idattribute,

583 to_clob(uml.old_value) AS old_value,

584 to_clob(uml.new_value) AS new_value,

585 uml.old_serialnumber AS old_serialnumber,

586 uml.new_serialnumber AS new_serialnumber,

587 uml.modified_user AS modified_user,

588 uml.modified_time AS modified_time,

589 uml.type_of_operation AS type_of_operation,

590 s.value AS attribute_note,

591 'DATE' AS attribute_datatype,

592 a.length AS datatype_length,

593 null OLD_IDPARENTOBJECT,

594 null NEW_IDPARENTOBJECT,

595 null OBJECTTYPE,

596 null OLD_OWNER,

597 null NEW_OWNER,

598 null OLD_ORIGHTS,

599 null NEW_ORIGHTS,

600 null OLD_ARIGHTS,

601 null NEW_ARIGHTS,

602 null OPERATION,

603 null OBJECTNOTE

604 FROM objects o, attributes a, objecttypes ot, userdatevalueslog uml, systemdatavalues s

605 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

606 AND

607 (uml.id_object = o.id and uml.id_attribute = a.id)

608

609 UNION ALL

610 --userclobvalueslog

611 SELECT

612 o.id AS id,

613 uml.id_attribute AS new_idattribute,

614 to_clob(uml.old_value) AS old_value,

615 to_clob(uml.new_value) AS new_value,

616 uml.old_serialnumber AS old_serialnumber,

617 uml.new_serialnumber AS new_serialnumber,

618 uml.modified_user AS modified_user,

619 uml.modified_time AS modified_time,

620 uml.type_of_operation AS type_of_operation,

621 s.value AS attribute_note,

622 'CLOB' AS attribute_datatype,

623 a.length AS datatype_length,

624 null OLD_IDPARENTOBJECT,

625 null NEW_IDPARENTOBJECT,

626 null OBJECTTYPE,

627 null OLD_OWNER,

628 null NEW_OWNER,

629 null OLD_ORIGHTS,

630 null NEW_ORIGHTS,

631 null OLD_ARIGHTS,

632 null NEW_ARIGHTS,

633 null OPERATION,

634 null OBJECTNOTE

635 FROM objects o, attributes a, objecttypes ot, userclobvalueslog uml, systemdatavalues s

636 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

637 AND

638 (uml.id_object = o.id and uml.id_attribute = a.id)

639

640 UNION ALL

641 --usernumbervalueslog

642 SELECT

643 o.id AS id,

644 uml.id_attribute AS new_idattribute,

645 to_clob(uml.old_value) AS old_value,

646 to_clob(uml.new_value) AS new_value,

647 uml.old_serialnumber AS old_serialnumber,

648 uml.new_serialnumber AS new_serialnumber,

649 uml.modified_user AS modified_user,

650 uml.modified_time AS modified_time,

651 uml.type_of_operation AS type_of_operation,

652 s.value AS attribute_note,

653 'CLOB' AS attribute_datatype,

654 a.length AS datatype_length,

655 null OLD_IDPARENTOBJECT,

656 null OBJECTTYPE,

657 null NEW_OBJECTTYPE,

658 null OLD_OWNER,

659 null NEW_OWNER,

660 null OLD_ORIGHTS,

661 null NEW_ORIGHTS,

662 null OLD_ARIGHTS,

663 null NEW_ARIGHTS,

664 null OPERATION,

665 null OBJECTNOTE

666 FROM objects o, attributes a, objecttypes ot, usernumbervalueslog uml, systemdatavalues s

667 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

668 AND

669 (uml.id_object = o.id and uml.id_attribute = a.id)

670

671 UNION ALL

672 --userdictionaryvalueslog

673 SELECT

674 o.id AS id,

675 uml.id_attribute AS new_idattribute,

676 to_clob(uml.old_value) AS old_value,

677 to_clob(uml.new_value) AS new_value,

678 uml.old_serialnumber AS old_serialnumber,

679 uml.new_serialnumber AS new_serialnumber,

680 uml.modified_user AS modified_user,

681 uml.modified_time AS modified_time,

682 uml.type_of_operation AS type_of_operation,

683 s.value AS attribute_note,

684 'CLOB' AS attribute_datatype,

685 a.length AS datatype_length,

686 null OLD_IDPARENTOBJECT,

687 null NEW_IDPARENTOBJECT,

688 null OBJECTTYPE,

689 null OLD_OWNER,

690 null NEW_OWNER,

691 null OLD_ORIGHTS,

692 null NEW_ORIGHTS,

693 null OLD_ARIGHTS,

694 null NEW_ARIGHTS,

695 null OPERATION,

696 null OBJECTNOTE

697 FROM objects o, attributes a, objecttypes ot, userdictionaryvalueslog uml, systemdatavalues s

698 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

699 AND

700 (uml.id_object = o.id and uml.id_attribute = a.id)

701

702 UNION ALL

703

704 SELECT

705 o.id AS id,

706 null AS new_idattribute,

707 null AS old_value,

708 null AS new_value,

709 null AS old_serialnumber,

710 null AS new_serialnumber,

711 OL.MODIFIED_USER AS modified_user,

712 OL.MODIFIED_TIME AS modified_time,

713 null AS type_of_operation,

714 null AS attribute_note,

715 null AS attribute_datatype,

716 null AS datatype_length,

717 OL.OLD_IDPARENTOBJECT OLD_IDPARENTOBJECT,

718 OL.NEW_IDPARENTOBJECT NEW_IDPARENTOBJECT,

719 OL.NEW_OBJECTTYPE NEW_OBJECTTYPE,

720 OL.OLD_OWNER OLD_OWNER,

721 OL.NEW_OWNER NEW_OWNER,

722 OL.OLD_ORIGHTS OLD_ORIGHTS,

723 OL.NEW_ORIGHTS NEW_ORIGHTS,

724 OL.OLD_ARIGHTS OLD_ARIGHTS,

725 OL.NEW_ARIGHTS NEW_ARIGHTS,

726 OL.TYPE_OF_OPERATION OPERATION,

727 s.value OBJECTNOTE

728 FROM objectslog OL, systemdatavalues s, objects o

729 WHERE o.idobjecttype = nIDObjectType AND OL.ID_OBJECT = o.id and s.idparent = OL.ID_OBJECT AND S.IDLANGUAGE = nIdLanguage

730 -- ORDER BY modified_time

731

732) B)A

733 GROUP BY A.modified_time, A.MODIFIED_USER

734 ORDER BY A.modified_time;

735

736 RETURN xDocument;

737

738 END showAuditByObjectType;

739

740 function countTransactionsByAttribute(nIDAttribute attributes.id%TYPE, nIdLanguage NUMBER) RETURN NUMBER IS

741 nAmountTransactions NUMBER;

742 nDataType attributes.iddatatype%TYPE;

743 vName datatypes.name%TYPE;

744 BEGIN

745 SELECT idDataType INTO nDataType FROM attributes WHERE id = nIDAttribute;

746 SELECT name INTO vName FROM datatypes WHERE id = nDataType;

747 IF vName = 'MULTILANG' THEN

748

749 SELECT count(*)

750 INTO nAmountTransactions

751 FROM

752 (SELECT modified_time

753 FROM usermultilangvalueslog u, systemdatavalues s

754 WHERE u.id_attribute = nIDAttribute AND u.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

755 GROUP BY u.modified_time) A;

756

757 RETURN nAmountTransactions;

758

759 ELSIF vName = 'VARCHAR2' THEN

760

761 SELECT count(*)

762 INTO nAmountTransactions

763 FROM

764 (SELECT modified_time

765 FROM userstringvalueslog u, systemdatavalues s

766 WHERE u.id_attribute = nIDAttribute AND u.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

767 GROUP BY u.modified_time) A;

768

769 RETURN nAmountTransactions;

770

771 ELSIF vName = 'NUMBER' THEN

772

773 SELECT count(*)

774 INTO nAmountTransactions

775 FROM

776 (SELECT modified_time

777 FROM usernumbervalueslog u, systemdatavalues s

778 WHERE u.id_attribute = nIDAttribute AND u.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

779 GROUP BY u.modified_time) A;

780

781 RETURN nAmountTransactions;

782

783 ELSIF vName = 'DATE' THEN

784

785 SELECT count(*)

786 INTO nAmountTransactions

787 FROM

788 (SELECT modified_time

789 FROM userdatevalueslog u, systemdatavalues s

790 WHERE u.id_attribute = nIDAttribute AND u.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

791 GROUP BY u.modified_time) A;

792

793 RETURN nAmountTransactions;

794

795 ELSIF vName = 'CLOB' THEN

796

797 SELECT count(*)

798 INTO nAmountTransactions

799 FROM

800 (SELECT modified_time

801 FROM userclobvalueslog u, systemdatavalues s

802 WHERE u.id_attribute = nIDAttribute AND u.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

803 GROUP BY u.modified_time) A;

804

805 RETURN nAmountTransactions;

806

807 ELSIF vName = 'DICTIONARY' THEN

808

809 SELECT count(*)

810 INTO nAmountTransactions

811 FROM

812 (SELECT modified_time

813 FROM userdictionaryvalueslog u, systemdatavalues s

814 WHERE u.id_attribute = nIDAttribute AND u.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

815 GROUP BY u.modified_time) A;

816

817 RETURN nAmountTransactions;

818

819 END IF;

820 RETURN 0;

821

822 RETURN nAmountTransactions;

823 EXCEPTION WHEN NO_DATA_FOUND THEN

824 RETURN 0;

825 END countTransactionsByAttribute;

826

827 function countTransactionsByObject(nIDObject objects.id%TYPE, nIdLanguage NUMBER) RETURN NUMBER IS

828 nAmountTransactions NUMBER;

829 BEGIN

830 SELECT count(*) INTO nAmountTransactions FROM (

831 SELECT a.modified_time

832 FROM (

833 --usermultilangvalueslog

834 SELECT uml.id_attribute AS new_idattribute,

835 to_clob(uml.old_value) AS old_value,

836 to_clob(uml.new_value) AS new_value,

837 uml.old_serialnumber AS old_serialnumber,

838 uml.new_serialnumber AS new_serialnumber,

839 uml.modified_user AS modified_user,

840 uml.modified_time AS modified_time,

841 uml.type_of_operation AS type_of_operation,

842 s.value AS attribute_note,

843 'MULTILANG' AS attribute_datatype,

844 a.length AS datatype_length,

845 null OLD_IDPARENTOBJECT,

846 null NEW_IDPARENTOBJECT,

847 null OBJECTTYPE,

848 null OLD_OWNER,

849 null NEW_OWNER,

850 null OLD_ORIGHTS,

851 null NEW_ORIGHTS,

852 null OLD_ARIGHTS,

853 null NEW_ARIGHTS,

854 null OPERATION,

855 null OBJECTNOTE

856 FROM objects o, attributes a, objecttypes ot, usermultilangvalueslog uml, systemdatavalues s

857 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

858 AND

859 (uml.id_object = o.id and uml.id_attribute = a.id)

860

861 UNION ALL

862 --userstringvalueslog

863 SELECT uml.id_attribute AS new_idattribute,

864 to_clob(uml.old_value) AS old_value,

865 to_clob(uml.new_value) AS new_value,

866 uml.old_serialnumber AS old_serialnumber,

867 uml.new_serialnumber AS new_serialnumber,

868 uml.modified_user AS modified_user,

869 uml.modified_time AS modified_time,

870 uml.type_of_operation AS type_of_operation,

871 s.value AS attribute_note,

872 'STRING' AS attribute_datatype,

873 a.length AS datatype_length,

874 null OLD_IDPARENTOBJECT,

875 null NEW_IDPARENTOBJECT,

876 null OBJECTTYPE,

877 null OLD_OWNER,

878 null NEW_OWNER,

879 null OLD_ORIGHTS,

880 null NEW_ORIGHTS,

881 null OLD_ARIGHTS,

882 null NEW_ARIGHTS,

883 null OPERATION,

884 null OBJECTNOTE

885 FROM objects o, attributes a, objecttypes ot, userstringvalueslog uml, systemdatavalues s

886 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

887 AND

888 (uml.id_object = o.id and uml.id_attribute = a.id)

889

890 UNION ALL

891 --userdatevalueslog

892 SELECT uml.id_attribute AS new_idattribute,

893 to_clob(uml.old_value) AS old_value,

894 to_clob(uml.new_value) AS new_value,

895 uml.old_serialnumber AS old_serialnumber,

896 uml.new_serialnumber AS new_serialnumber,

897 uml.modified_user AS modified_user,

898 uml.modified_time AS modified_time,

899 uml.type_of_operation AS type_of_operation,

900 s.value AS attribute_note,

901 'DATE' AS attribute_datatype,

902 a.length AS datatype_length,

903 null OLD_IDPARENTOBJECT,

904 null NEW_IDPARENTOBJECT,

905 null OBJECTTYPE,

906 null OLD_OWNER,

907 null NEW_OWNER,

908 null OLD_ORIGHTS,

909 null NEW_ORIGHTS,

910 null OLD_ARIGHTS,

911 null NEW_ARIGHTS,

912 null OPERATION,

913 null OBJECTNOTE

914 FROM objects o, attributes a, objecttypes ot, userdatevalueslog uml, systemdatavalues s

915 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

916 AND

917 (uml.id_object = o.id and uml.id_attribute = a.id)

918

919 UNION ALL

920 --userclobvalueslog

921 SELECT uml.id_attribute AS new_idattribute,

922 to_clob(uml.old_value) AS old_value,

923 to_clob(uml.new_value) AS new_value,

924 uml.old_serialnumber AS old_serialnumber,

925 uml.new_serialnumber AS new_serialnumber,

926 uml.modified_user AS modified_user,

927 uml.modified_time AS modified_time,

928 uml.type_of_operation AS type_of_operation,

929 s.value AS attribute_note,

930 'CLOB' AS attribute_datatype,

931 a.length AS datatype_length,

932 null OLD_IDPARENTOBJECT,

933 null NEW_IDPARENTOBJECT,

934 null OBJECTTYPE,

935 null OLD_OWNER,

936 null NEW_OWNER,

937 null OLD_ORIGHTS,

938 null NEW_ORIGHTS,

939 null OLD_ARIGHTS,

940 null NEW_ARIGHTS,

941 null OPERATION,

942 null OBJECTNOTE

943 FROM objects o, attributes a, objecttypes ot, userclobvalueslog uml, systemdatavalues s

944 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

945 AND

946 (uml.id_object = o.id and uml.id_attribute = a.id)

947

948 UNION ALL

949 --usernumbervalueslog

950 SELECT uml.id_attribute AS new_idattribute,

951 to_clob(uml.old_value) AS old_value,

952 to_clob(uml.new_value) AS new_value,

953 uml.old_serialnumber AS old_serialnumber,

954 uml.new_serialnumber AS new_serialnumber,

955 uml.modified_user AS modified_user,

956 uml.modified_time AS modified_time,

957 uml.type_of_operation AS type_of_operation,

958 s.value AS attribute_note,

959 'CLOB' AS attribute_datatype,

960 a.length AS datatype_length,

961 null OLD_IDPARENTOBJECT,

962 null OBJECTTYPE,

963 null NEW_OBJECTTYPE,

964 null OLD_OWNER,

965 null NEW_OWNER,

966 null OLD_ORIGHTS,

967 null NEW_ORIGHTS,

968 null OLD_ARIGHTS,

969 null NEW_ARIGHTS,

970 null OPERATION,

971 null OBJECTNOTE

972 FROM objects o, attributes a, objecttypes ot, usernumbervalueslog uml, systemdatavalues s

973 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

974 AND

975 (uml.id_object = o.id and uml.id_attribute = a.id)

976

977 UNION ALL

978 --userdictionaryvalueslog

979 SELECT uml.id_attribute AS new_idattribute,

980 to_clob(uml.old_value) AS old_value,

981 to_clob(uml.new_value) AS new_value,

982 uml.old_serialnumber AS old_serialnumber,

983 uml.new_serialnumber AS new_serialnumber,

984 uml.modified_user AS modified_user,

985 uml.modified_time AS modified_time,

986 uml.type_of_operation AS type_of_operation,

987 s.value AS attribute_note,

988 'CLOB' AS attribute_datatype,

989 a.length AS datatype_length,

990 null OLD_IDPARENTOBJECT,

991 null NEW_IDPARENTOBJECT,

992 null OBJECTTYPE,

993 null OLD_OWNER,

994 null NEW_OWNER,

995 null OLD_ORIGHTS,

996 null NEW_ORIGHTS,

997 null OLD_ARIGHTS,

998 null NEW_ARIGHTS,

999 null OPERATION,

1000 null OBJECTNOTE

1001 FROM objects o, attributes a, objecttypes ot, userdictionaryvalueslog uml, systemdatavalues s

1002 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

1003 AND

1004 (uml.id_object = o.id and uml.id_attribute = a.id)

1005

1006 UNION ALL

1007

1008 SELECT null AS new_idattribute,

1009 null AS old_value,

1010 null AS new_value,

1011 null AS old_serialnumber,

1012 null AS new_serialnumber,

1013 OL.MODIFIED_USER AS modified_user,

1014 OL.MODIFIED_TIME AS modified_time,

1015 null AS type_of_operation,

1016 null AS attribute_note,

1017 null AS attribute_datatype,

1018 null AS datatype_length,

1019 OL.OLD_IDPARENTOBJECT OLD_IDPARENTOBJECT,

1020 OL.NEW_IDPARENTOBJECT NEW_IDPARENTOBJECT,

1021 OL.NEW_OBJECTTYPE NEW_OBJECTTYPE,

1022 OL.OLD_OWNER OLD_OWNER,

1023 OL.NEW_OWNER NEW_OWNER,

1024 OL.OLD_ORIGHTS OLD_ORIGHTS,

1025 OL.NEW_ORIGHTS NEW_ORIGHTS,

1026 OL.OLD_ARIGHTS OLD_ARIGHTS,

1027 OL.NEW_ARIGHTS NEW_ARIGHTS,

1028 OL.TYPE_OF_OPERATION OPERATION,

1029 s.value OBJECTNOTE

1030 FROM objectslog OL, systemdatavalues s

1031 WHERE OL.ID_OBJECT = nIDObject and s.idparent = OL.ID_OBJECT AND S.IDLANGUAGE = nIdLanguage

1032

1033) A

1034 GROUP BY A.modified_time) B;

1035

1036 RETURN nAmountTransactions;

1037 END countTransactionsByObject;

1038

1039 function countTransactionsByObjectType(nIDObjectType objecttypes.id%TYPE, nIdLanguage NUMBER) RETURN NUMBER IS

1040 nAmountTransactions NUMBER;

1041 BEGIN

1042 SELECT sum(countTransactionsByObject(id, nIdLanguage))

1043 INTO nAmountTransactions

1044 FROM objects o

1045 WHERE o.idobjecttype = nIDObjectType

1046 AND (EXISTS (SELECT * FROM userclobvalueslog u WHERE u.id_object = o.id)

1047 OR EXISTS (SELECT * FROM userdatevalueslog u WHERE u.id_object = o.id)

1048 OR EXISTS (SELECT * FROM userdictionaryvalueslog u WHERE u.id_object = o.id)

1049 OR EXISTS (SELECT * FROM usermultilangvalueslog u WHERE u.id_object = o.id)

1050 OR EXISTS (SELECT * FROM usernumbervalueslog u WHERE u.id_object = o.id)

1051 OR EXISTS (SELECT * FROM userstringvalueslog u WHERE u.id_object = o.id));

1052 RETURN nAmountTransactions;

1053 END countTransactionsByObjectType;

1054

1055 FUNCTION pageAuditByAttribute(nIDAttribute attributes.id%TYPE, nIdLanguage NUMBER, nPageNumber NUMBER, nAmountPerPage NUMBER) RETURN XMLTYPE AS

1056 nDataType attributes.iddatatype%TYPE;

1057 xDocument XMLTYPE;

1058 vName datatypes.name%TYPE;

1059 BEGIN

1060 SELECT idDataType INTO nDataType FROM attributes WHERE id = nIDAttribute;

1061 SELECT name INTO vName FROM datatypes WHERE id = nDataType;

1062 IF vName = 'MULTILANG' THEN

1063

1064 SELECT

1065 XMLElement("attribute_summary", XMLAttributes(nIDAttribute AS id),

1066 XMLAgg(XMLElement("transaction",

1067 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

1068 XMLAgg(

1069 XMLConcat(

1070 XMLElement("attribute",

1071 XMLAttributes(

1072 nIDAttribute AS id_attribute,

1073 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

1074 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

1075 'MULTILANG' AS attribute_datatype

1076),

1077 XMLElement("old_value_attribute",A.old_value),

1078 XMLElement("new_value_attribute",A.new_value)

1079)

1080)--XMLConcat

1081)--XMLAgg

1082)--XMLAttributes

1083 order by A.modified_time desc

1084)

1085)

1086 INTO xDocument

1087 FROM (

1088 SELECT a.*, uml.*, s.*, rownum r

1089 FROM attributes a, usermultilangvalueslog uml, systemdatavalues s

1090 WHERE a.id = nIDAttribute and uml.id_attribute = nIDAttribute AND uml.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

1091)A

1092 WHERE (A.r between ((nPageNumber-1)*nAmountPerPage+1) and (nPageNumber*nAmountPerPage))

1093 GROUP BY A.modified_time, A.MODIFIED_USER;

1094

1095 RETURN xDocument;

1096 ELSIF vName = 'VARCHAR2' THEN

1097

1098 SELECT

1099 XMLElement("attribute_summary", XMLAttributes(nIDAttribute AS id),

1100 XMLAgg(XMLElement("transaction",

1101 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

1102 XMLAgg(

1103 XMLConcat(

1104 XMLElement("attribute",

1105 XMLAttributes(

1106 nIDAttribute AS id_attribute,

1107 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

1108 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

1109 'STRING' AS attribute_datatype

1110),

1111 XMLElement("old_value_attribute",A.old_value),

1112 XMLElement("new_value_attribute",A.new_value)

1113)

1114)--XMLConcat

1115)--XMLAgg

1116)--XMLAttributes

1117 order by A.modified_time desc

1118)

1119)

1120 INTO xDocument

1121 FROM (

1122 SELECT a.*, usl.*, s.*, rownum r

1123 FROM attributes a, userstringvalueslog usl, systemdatavalues s

1124 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

1125)A

1126 WHERE (A.r between ((nPageNumber-1)*nAmountPerPage+1) and (nPageNumber*nAmountPerPage))

1127 GROUP BY A.modified_time, A.MODIFIED_USER;

1128

1129 RETURN xDocument;

1130

1131 ELSIF vName = 'NUMBER' THEN

1132

1133 SELECT

1134 XMLElement("attribute_summary", XMLAttributes(nIDAttribute AS id),

1135 XMLAgg(XMLElement("transaction",

1136 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

1137 XMLAgg(

1138 XMLConcat(

1139 XMLElement("attribute",

1140 XMLAttributes(

1141 nIDAttribute AS id_attribute,

1142 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

1143 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

1144 'NUMBER' AS attribute_datatype

1145),

1146 XMLElement("old_value_attribute",A.old_value),

1147 XMLElement("new_value_attribute",A.new_value)

1148)

1149)--XMLConcat

1150)--XMLAgg

1151)--XMLAttributes

1152 order by A.modified_time desc

1153)

1154)

1155 INTO xDocument

1156 FROM (

1157 SELECT a.*, usl.*, s.*, rownum r

1158 FROM attributes a, usernumbervalueslog usl, systemdatavalues s

1159 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

1160)A

1161 WHERE (A.r between ((nPageNumber-1)*nAmountPerPage+1) and (nPageNumber*nAmountPerPage))

1162 GROUP BY A.modified_time, A.MODIFIED_USER;

1163

1164 RETURN xDocument;

1165

1166 ELSIF vName = 'DATE' THEN

1167

1168 SELECT

1169 XMLElement("attribute_summary", XMLAttributes(nIDAttribute AS id),

1170 XMLAgg(XMLElement("transaction",

1171 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

1172 XMLAgg(

1173 XMLConcat(

1174 XMLElement("attribute",

1175 XMLAttributes(

1176 nIDAttribute AS id_attribute,

1177 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

1178 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

1179 'DATE' AS attribute_datatype

1180),

1181 XMLElement("old_value_attribute",A.old_value),

1182 XMLElement("new_value_attribute",A.new_value)

1183)

1184)--XMLConcat

1185)--XMLAgg

1186)--XMLAttributes

1187 order by A.modified_time desc

1188)

1189)

1190 INTO xDocument

1191 FROM (

1192 SELECT a.*, usl.*, s.*, rownum r

1193 FROM attributes a, userdatevalueslog usl, systemdatavalues s

1194 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

1195)A

1196 WHERE (A.r between ((nPageNumber-1)*nAmountPerPage+1) and (nPageNumber*nAmountPerPage))

1197 GROUP BY A.modified_time, A.MODIFIED_USER;

1198

1199 RETURN xDocument;

1200

1201 ELSIF vName = 'CLOB' THEN

1202

1203 SELECT

1204 XMLElement("attribute_summary", XMLAttributes(nIDAttribute AS id),

1205 XMLAgg(XMLElement("transaction",

1206 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

1207 XMLAgg(

1208 XMLConcat(

1209 XMLElement("attribute",

1210 XMLAttributes(

1211 nIDAttribute AS id_attribute,

1212 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

1213 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

1214 'CLOB' AS attribute_datatype

1215),

1216 XMLElement("old_value_attribute",A.old_value),

1217 XMLElement("new_value_attribute",A.new_value)

1218)

1219)--XMLConcat

1220)--XMLAgg

1221)--XMLAttributes

1222 order by A.modified_time desc

1223)

1224)

1225 INTO xDocument

1226 FROM (

1227 SELECT a.*, usl.*, s.*, rownum r

1228 FROM attributes a, userclobvalueslog usl, systemdatavalues s

1229 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1