c3A05Ymt

1create table cc_activity (
2	id int4 not null,
3	createddate timestamp,
4	isactive char(1),
5	updateddate timestamp,
6	code varchar(15),
7	createdby_id int4,
8	updated_id int4,
9	primary key (id)
10);
11
12create table cc_activity_AUD (
13	id int4 not null,
14	REV int4 not null,
15	REVTYPE int2,
16	createddate timestamp,
17	isactive char(1),
18	updateddate timestamp,
19	code varchar(15),
20	createdby_id int4,
21	updated_id int4,
22	primary key (id, REV)
23);
24
25create table cc_activitylang (
26	id int4 not null,
27	createddate timestamp,
28	isactive char(1),
29	updateddate timestamp,
30	name varchar(50) not null,
31	createdby_id int4,
32	updated_id int4,
33	activity_id int4 not null,
34	language_id int4 not null,
35	primary key (id)
36);
37
38create table cc_activitylang_AUD (
39	id int4 not null,
40	REV int4 not null,
41	REVTYPE int2,
42	createddate timestamp,
43	isactive char(1),
44	updateddate timestamp,
45	name varchar(50),
46	createdby_id int4,
47	updated_id int4,
48	activity_id int4,
49	language_id int4,
50	primary key (id, REV)
51);
52
53alter table cc_activitylang
54	drop constraint if exists UK_rdbdd2ue85xrsywxv9o7u7jhg;
55
56alter table cc_activitylang
57	add constraint UK_rdbdd2ue85xrsywxv9o7u7jhg  unique (language_id, activity_id);
58
59alter table cc_activity
60	add constraint FK_rx7am7fa5a0g2gp6l96chan48
61	foreign key (createdby_id)
62	references cc_useraccess;
63
64alter table cc_activity
65	add constraint FK_rshsifxwg85ngv7ping7cgpbk
66	foreign key (updated_id)
67	references cc_useraccess;
68
69alter table cc_activity_AUD
70	add constraint FK_hh9043wi52a2xglem0ekgkddu
71	foreign key (REV)
72	references cc_changelog;
73
74alter table cc_activitylang
75	add constraint FK_1rf5j9b3q4g5ptw3c6glep1a7
76	foreign key (createdby_id)
77	references cc_useraccess;
78
79alter table cc_activitylang
80	add constraint FK_adktm3lu52opssdo9wjmx6fpo
81	foreign key (updated_id)
82	references cc_useraccess;
83
84alter table cc_activitylang
85	add constraint FK_cf0acv2412u6hm5y4xjmywh7u
86	foreign key (activity_id)
87	references cc_activity;
88
89alter table cc_activitylang
90	add constraint FK_qspcc0sp0ca299d3g4wfe0cs1
91	foreign key (language_id)
92	references cc_language;
93
94alter table cc_activitylang_AUD
95	add constraint FK_oaxxpk0cahyk3oqhtogpquxl7
96	foreign key (REV)
97	references cc_changelog;
98
99create sequence cc_activitylangseq;
100
101create sequence cc_activityseq;
102
103alter table cc_party
104	add column activity_id int4;
105
106alter table cc_party_AUD
107	add column activity_id int4;
108
109alter table cc_party
110	add constraint FK_krqbutfpm0vm6byxlxwnynudm
111	foreign key (activity_id)
112	references cc_activity;
113
114alter table cc_contact
115	add column externalcode varchar(255);
116
117alter table cc_contact_AUD
118	add column externalcode varchar(255);
119
120alter table cc_contact
121	add column contacttype_id int4;
122
123alter table cc_contact_AUD
124	add column contacttype_id int4;
125
126create table cc_contacttype (
127	id int4 not null,
128	createddate timestamp,
129	isactive char(1),
130	updateddate timestamp,
131	code varchar(15),
132	createdby_id int4,
133	updated_id int4,
134	primary key (id)
135);
136
137create table cc_contacttype_AUD (
138	id int4 not null,
139	REV int4 not null,
140	REVTYPE int2,
141	createddate timestamp,
142	isactive char(1),
143	updateddate timestamp,
144	code varchar(15),
145	createdby_id int4,
146	updated_id int4,
147	primary key (id, REV)
148);
149
150create table cc_contacttypelang (
151	id int4 not null,
152	createddate timestamp,
153	isactive char(1),
154	updateddate timestamp,
155	name varchar(50) not null,
156	createdby_id int4,
157	updated_id int4,
158	contacttype_id int4 not null,
159	language_id int4 not null,
160	primary key (id)
161);
162
163create table cc_contacttypelang_AUD (
164	id int4 not null,
165	REV int4 not null,
166	REVTYPE int2,
167	createddate timestamp,
168	isactive char(1),
169	updateddate timestamp,
170	name varchar(50),
171	createdby_id int4,
172	updated_id int4,
173	contacttype_id int4,
174	language_id int4,
175	primary key (id, REV)
176);
177
178alter table cc_contacttypelang
179	drop constraint if exists UK_j5nisjfr247vr7fv39v7rvv4;
180
181alter table cc_contacttypelang
182	add constraint UK_j5nisjfr247vr7fv39v7rvv4  unique (language_id, contacttype_id);
183
184alter table cc_contact
185	add constraint FK_1rap1tymnnnmuqn3152yvoskp
186	foreign key (contacttype_id)
187	references cc_contacttype;
188
189alter table cc_contacttype
190	add constraint FK_g41x82x469a32ob3c9ghihcf1
191	foreign key (createdby_id)
192	references cc_useraccess;
193
194alter table cc_contacttype
195	add constraint FK_3bjgp4038r4m8j5dnw8rypvo0
196	foreign key (updated_id)
197	references cc_useraccess;
198
199alter table cc_contacttype_AUD
200	add constraint FK_kg98lp1q1e0uf5kpmkpmf72hj
201	foreign key (REV)
202	references cc_changelog;
203
204alter table cc_contacttypelang
205	add constraint FK_rifbgmuj6yo0f5esctgspqq3q
206	foreign key (createdby_id)
207	references cc_useraccess;
208
209alter table cc_contacttypelang
210	add constraint FK_pjy65hvd3xkrins0fl4nwo5sx
211	foreign key (updated_id)
212	references cc_useraccess;
213
214alter table cc_contacttypelang
215	add constraint FK_aiw8ppqernqfsndih05g9noua
216	foreign key (contacttype_id)
217	references cc_contacttype;
218
219alter table cc_contacttypelang
220	add constraint FK_c7kg15dmqsvv20ej73g77vu5a
221	foreign key (language_id)
222	references cc_language;
223
224alter table cc_contacttypelang_AUD
225	add constraint FK_ksolvrof8uuqq3t91vicwweti
226	foreign key (REV)
227	references cc_changelog;
228
229create sequence cc_contacttypelangseq;
230
231create sequence cc_contacttypeseq;
232
233create table cc_partycertification (
234	id int4 not null,
235	createddate timestamp,
236	isactive char(1),
237	updateddate timestamp,
238	validto timestamp,
239	createdby_id int4,
240	updated_id int4,
241	party_id int4 not null,
242	partycertificationtype_id int4 not null,
243	primary key (id)
244);
245
246create table cc_partycertification_AUD (
247	id int4 not null,
248	REV int4 not null,
249	REVTYPE int2,
250	createddate timestamp,
251	isactive char(1),
252	updateddate timestamp,
253	validto timestamp,
254	createdby_id int4,
255	updated_id int4,
256	party_id int4,
257	partycertificationtype_id int4,
258	primary key (id, REV)
259);
260
261create table cc_partycertificationtype (
262	id int4 not null,
263	createddate timestamp,
264	isactive char(1),
265	updateddate timestamp,
266	code varchar(15),
267	createdby_id int4,
268	updated_id int4,
269	primary key (id)
270);
271
272create table cc_partycertificationtype_AUD (
273	id int4 not null,
274	REV int4 not null,
275	REVTYPE int2,
276	createddate timestamp,
277	isactive char(1),
278	updateddate timestamp,
279	code varchar(15),
280	createdby_id int4,
281	updated_id int4,
282	primary key (id, REV)
283);
284
285create table cc_partycertificationtypelang (
286	id int4 not null,
287	createddate timestamp,
288	isactive char(1),
289	updateddate timestamp,
290	name varchar(50) not null,
291	createdby_id int4,
292	updated_id int4,
293	language_id int4 not null,
294	partycertificationtype_id int4 not null,
295	primary key (id)
296);
297
298create table cc_partycertificationtypelang_AUD (
299	id int4 not null,
300	REV int4 not null,
301	REVTYPE int2,
302	createddate timestamp,
303	isactive char(1),
304	updateddate timestamp,
305	name varchar(50),
306	createdby_id int4,
307	updated_id int4,
308	language_id int4,
309	partycertificationtype_id int4,
310	primary key (id, REV)
311);
312
313alter table cc_partycertificationtypelang
314	drop constraint if exists UK_lh5xox7rorqy4baus8fexexro;
315
316alter table cc_partycertificationtypelang
317	add constraint UK_lh5xox7rorqy4baus8fexexro  unique (language_id, partycertificationtype_id);
318
319alter table cc_partycertification
320	add constraint FK_blo07hn9y1lg5yavm83v0mk1k
321	foreign key (createdby_id)
322	references cc_useraccess;
323
324alter table cc_partycertification
325	add constraint FK_2mqjvl4orgp89jjf7t3y6x53m
326	foreign key (updated_id)
327	references cc_useraccess;
328
329alter table cc_partycertification
330	add constraint FK_nweuoy79955g8uvjxyn2n6nn3
331	foreign key (party_id)
332	references cc_party
333	on delete cascade;
334
335alter table cc_partycertification
336	add constraint FK_bjmgdlddc186dgvhjhld2ppps
337	foreign key (partycertificationtype_id)
338	references cc_partycertificationtype;
339
340alter table cc_partycertification_AUD
341	add constraint FK_agigmmapyu1ojw61tjf5yh8b
342	foreign key (REV)
343	references cc_changelog;
344
345alter table cc_partycertificationtype
346	add constraint FK_1bccy56hg72qq80vhhy0xegfe
347	foreign key (createdby_id)
348	references cc_useraccess;
349
350alter table cc_partycertificationtype
351	add constraint FK_2jw2o20442y9sep294by9rxw5
352	foreign key (updated_id)
353	references cc_useraccess;
354
355alter table cc_partycertificationtype_AUD
356	add constraint FK_9uxe9f3g98lu32ecw5nh1hcp4
357	foreign key (REV)
358	references cc_changelog;
359
360alter table cc_partycertificationtypelang
361	add constraint FK_pa1wcytm5x39s1egjq8515ov9
362	foreign key (createdby_id)
363	references cc_useraccess;
364
365alter table cc_partycertificationtypelang
366	add constraint FK_aln3p8g71mmp32gi0gql16hhn
367	foreign key (updated_id)
368	references cc_useraccess;
369
370alter table cc_partycertificationtypelang
371	add constraint FK_3tpg07sjuaiyrs799md8p72pl
372	foreign key (language_id)
373	references cc_language;
374
375alter table cc_partycertificationtypelang
376	add constraint FK_lxxl3v7jim159bt9yuwlouhfi
377	foreign key (partycertificationtype_id)
378	references cc_partycertificationtype
379	on delete cascade;
380
381alter table cc_partycertificationtypelang_AUD
382	add constraint FK_4199w3tuwp60ct0x9tllijpf1
383	foreign key (REV)
384	references cc_changelog;
385
386create sequence cc_partycertificationseq;
387
388create sequence cc_partycertificationtypelangseq;
389
390create sequence cc_partycertificationtypeseq;
391
392alter table cc_partycertificationtype
393	add column ismandatory char(1) not null;
394
395alter table cc_partycertificationtype_AUD
396	add column ismandatory char(1);
397
398alter table cc_partycertificationtype
399	add column certificationclass varchar(5);
400
401alter table cc_partycertificationtype_AUD
402	add column certificationclass varchar(5);
403
404insert into cc_partycertificationtype (id, createddate, isactive, createdby_id, ismandatory, certificationclass) values(1, now(), 'Y', (select id from cc_useraccess where code = 'SYSADMIN'), 'Y', 'OEA');
405insert into cc_partycertificationtypelang (id, partycertificationtype_id, createddate, isactive, createdby_id, language_id, name) values(1, 1, now(), 'Y', (select id from cc_useraccess where code = 'SYSADMIN'), (select id from cc_language where code = 'ES'), 'OEA');
406insert into cc_partycertificationtypelang (id, partycertificationtype_id, createddate, isactive, createdby_id, language_id, name) values(2, 1, now(), 'Y', (select id from cc_useraccess where code = 'SYSADMIN'), (select id from cc_language where code = 'EN'), 'OEA');
407insert into cc_partycertificationtypelang (id, partycertificationtype_id, createddate, isactive, createdby_id, language_id, name) values(3, 1, now(), 'Y', (select id from cc_useraccess where code = 'SYSADMIN'), (select id from cc_language where code = 'FR'), 'OEA');
408
409insert into cc_partycertificationtype (id, createddate, isactive, createdby_id, ismandatory, certificationclass) values(2, now(), 'Y', (select id from cc_useraccess where code = 'SYSADMIN'), 'Y', 'ISO');
410insert into cc_partycertificationtypelang (id, partycertificationtype_id, createddate, isactive, createdby_id, language_id, name) values(4, 2, now(), 'Y', (select id from cc_useraccess where code = 'SYSADMIN'), (select id from cc_language where code = 'ES'), 'ISO 14000 (MEDIOAMBIENTAL)');
411insert into cc_partycertificationtypelang (id, partycertificationtype_id, createddate, isactive, createdby_id, language_id, name) values(5, 2, now(), 'Y', (select id from cc_useraccess where code = 'SYSADMIN'), (select id from cc_language where code = 'EN'), 'ISO 14000 (ENVIRONMENTAL)');
412insert into cc_partycertificationtypelang (id, partycertificationtype_id, createddate, isactive, createdby_id, language_id, name) values(6, 2, now(), 'Y', (select id from cc_useraccess where code = 'SYSADMIN'), (select id from cc_language where code = 'FR'), 'ISO 14000 (ENVIRONNEMENTAL)');
413
414insert into cc_partycertificationtype (id, createddate, isactive, createdby_id, ismandatory, certificationclass) values(3, now(), 'Y', (select id from cc_useraccess where code = 'SYSADMIN'), 'Y', 'ISO');
415insert into cc_partycertificationtypelang (id, partycertificationtype_id, createddate, isactive, createdby_id, language_id, name) values(7, 3, now(), 'Y', (select id from cc_useraccess where code = 'SYSADMIN'), (select id from cc_language where code = 'ES'), 'ISO 9001 (CALIDAD)');
416insert into cc_partycertificationtypelang (id, partycertificationtype_id, createddate, isactive, createdby_id, language_id, name) values(8, 3, now(), 'Y', (select id from cc_useraccess where code = 'SYSADMIN'), (select id from cc_language where code = 'EN'), 'ISO 9001 (CALIDAD)');
417insert into cc_partycertificationtypelang (id, partycertificationtype_id, createddate, isactive, createdby_id, language_id, name) values(9, 3, now(), 'Y', (select id from cc_useraccess where code = 'SYSADMIN'), (select id from cc_language where code = 'FR'), 'ISO 9001 (CALIDAD)');
418
419alter table cc_partyaddresscomment
420	add column isfirecustoms char(1);
421
422alter table cc_partyaddresscomment_AUD
423	add column isfirecustoms char(1);
424
425update cc_partyaddresscomment set isfirecustoms = 'N';
426
427ALTER TABLE cc_partyaddresscomment
428   ALTER COLUMN isfirecustoms SET NOT NULL;
429
430ALTER TABLE cc_partyaddresscomment
431   ALTER COLUMN body TYPE character varying(2048);
432
433ALTER TABLE cc_partyaddresscomment_aud
434   ALTER COLUMN body TYPE character varying(2048);
435
436alter table cc_partybankaccount
437	add column interfacecode varchar(50);
438
439alter table cc_partybankaccount
440	add column ismandatereceived char(1);
441
442alter table cc_partybankaccount_AUD
443	add column interfacecode varchar(50);
444
445alter table cc_partybankaccount_AUD
446	add column ismandatereceived char(1);
447
448create table cc_partyaddressanagralastvalues (
449	id int4 not null,
450	createddate timestamp,
451	isactive char(1),
452	updateddate timestamp,
453	altprv varchar(2) not null,
454	lngcod varchar(2) not null,
455	nasprv varchar(2) not null,
456	nazion varchar(4) not null,
457	provin varchar(2) not null,
458	createdby_id int4,
459	updated_id int4,
460	partyaddress_id int4 not null,
461	primary key (id)
462);
463
464create table cc_partyaddressanagralastvalues_AUD (
465	id int4 not null,
466	REV int4 not null,
467	REVTYPE int2,
468	createddate timestamp,
469	isactive char(1),
470	updateddate timestamp,
471	altprv varchar(2),
472	lngcod varchar(2),
473	nasprv varchar(2),
474	nazion varchar(4),
475	provin varchar(2),
476	createdby_id int4,
477	updated_id int4,
478	partyaddress_id int4,
479	primary key (id, REV)
480);
481
482alter table cc_partyaddressanagralastvalues
483	drop constraint if exists UK_j83qxvp59108oacnvc5xa8bhh;
484
485alter table cc_partyaddressanagralastvalues
486	add constraint UK_j83qxvp59108oacnvc5xa8bhh  unique (partyaddress_id);
487
488alter table cc_partyaddressanagralastvalues
489	add constraint FK_nebpwvj9wd9jat54c0ibdjwvp
490	foreign key (createdby_id)
491	references cc_useraccess;
492
493alter table cc_partyaddressanagralastvalues
494	add constraint FK_knoc1n0y2ejdibskiuyx26ryk
495	foreign key (updated_id)
496	references cc_useraccess;
497
498alter table cc_partyaddressanagralastvalues
499	add constraint FK_j83qxvp59108oacnvc5xa8bhh
500	foreign key (partyaddress_id)
501	references cc_partyaddress
502	on delete cascade;
503
504alter table cc_partyaddressanagralastvalues_AUD
505	add constraint FK_cs0s7bay0cu54aomo2q36anq9
506	foreign key (REV)
507	references cc_changelog;
508
509create sequence cc_partyaddressanagralastvaluesseq;
510
511alter table cc_filetraffic
512	add column cargoimpstatus varchar(10);
513
514alter table cc_filetraffic_AUD
515	add column cargoimpstatus varchar(10);
516
517UPDATE cc_filetraffic set cargoimpstatus = 'PENDING';
518
519alter table cc_filetraffic
520	add column cargoimperror varchar(512);
521
522alter table cc_filetraffic_AUD
523	add column cargoimperror varchar(512);
524
525ALTER TABLE cc_filetraffic
526   ALTER COLUMN cargoimperror TYPE character varying(2048);
527
528ALTER TABLE cc_filetraffic_AUD
529   ALTER COLUMN cargoimperror TYPE character varying(2048);
530
531update cc_commercialofferserviceprice set costcurrency_id = salecurrency_id where overridecost = 'Y' and costcurrency_id IS NULL and salecurrency_id IS NOT NULL;
532
533alter table cc_party
534	add column externalcodeascustomer varchar(255);
535
536alter table cc_party
537	add column externalcodeasprovider varchar(255);
538
539alter table cc_party_AUD
540	add column externalcodeascustomer varchar(255);
541
542alter table cc_party_AUD
543	add column externalcodeasprovider varchar(255);
544
545update cc_commercialofferheader set origin_id = (select portorigin_id from cc_commercialofferservice ser where ser.commercialofferheader_id = cc_commercialofferheader.id and portorigin_id IS NOT NULL LIMIT 1)
546 WHERE (select COUNT(distinct ser.portorigin_id) from cc_commercialofferservice ser WHERE ser.commercialofferheader_id = cc_commercialofferheader.id and ser.portorigin_id IS NOT NULL) = 1;
547
548
549 update cc_commercialofferheader set destination_id = (select portdestination_id from cc_commercialofferservice ser where ser.commercialofferheader_id = cc_commercialofferheader.id and portdestination_id IS NOT NULL LIMIT 1)
550 WHERE (select COUNT(distinct ser.portdestination_id) from cc_commercialofferservice ser WHERE ser.commercialofferheader_id = cc_commercialofferheader.id and ser.portdestination_id IS NOT NULL) = 1;
551
552 update cc_commercialofferheader set airportorigin_id = (select airportorigin_id from cc_commercialofferservice ser where ser.commercialofferheader_id = cc_commercialofferheader.id and airportorigin_id IS NOT NULL LIMIT 1)
553 WHERE (select COUNT(distinct ser.airportorigin_id) from cc_commercialofferservice ser WHERE ser.commercialofferheader_id = cc_commercialofferheader.id and ser.airportorigin_id IS NOT NULL) = 1;
554
555 update cc_commercialofferheader set airportdestination_id = (select airportdestination_id from cc_commercialofferservice ser where ser.commercialofferheader_id = cc_commercialofferheader.id and airportdestination_id IS NOT NULL LIMIT 1)
556 WHERE (select COUNT(distinct ser.airportdestination_id) from cc_commercialofferservice ser WHERE ser.commercialofferheader_id = cc_commercialofferheader.id and ser.airportdestination_id IS NOT NULL) = 1;