nccPWGNG

1
2prompt  --  Победённый Алексей Владимирович,  30.05.2012 10:47
3declare
4  v_c number(3);
5begin
6  select 1 into v_c from cols where table_name = 'DOC' and column_name = 'ARCHIVE';
7  execute immediate 'alter table RouteContext add (ARCHIVE NUMBER(1, 0) DEFAULT 0 NOT NULL)';
8  execute immediate 'update routecontext rc set archive=1 where exists (select d.docid from doc d where rc.docid = d.docid and d.archive = 1)';
9  execute immediate 'alter table doc drop (archive)';
10exception when no_data_found then null;
11end;
12/
13
14prompt  --  Соболев Антон Александрович,  19.06.2012 10:46,  SUFDCORE-8981
15create or replace view V_DQ_OUT as
16SELECT /*+ ORDERED INDEX(queue_document IX_QUEUE_DOCUMENT_IDC)
17PUSH_SUBQ(@SUB1) */ *
18       FROM ( SELECT this_.guid,
19       this_.contentObjectGuid,
20       this_.priority,
21       this_.resend_count,
22       this_.queue_item,
23       this_.direction,
24       this_.contentClassName,
25       this_.errorCode,
26       this_.error_message,
27       this_.create_org_sys_name,
28       this_.item_status,
29       this_.creation_date,
30       this_.change_status_date,
31       this_.TARGET_COMPLEX_TYPE,
32       this_.IS_SYSTEM_TRANSIT,
33       this_.DOCTYPE_NAME,
34       THIS_.DOC_LOG_PARENTDOC_GUID,
35       THIS_.DOC_LOG_VERSION,
36       THIS_.BLOB_SIZE
37       FROM queue_document this_
38       WHERE this_.item_status = 'SEND_READY' AND this_.direction = 'OUT'
39       AND this_.guid NOT IN
40         (SELECT /*+ NO_UNNEST QB_NAME(SUB1) */ this_.guid AS y0_
41         FROM QUEUE_DOCUMENT_OUT_IDS this_)
42         )
43       ORDER BY priority ASC;
44
45create or replace view V_DQ_IN as
46SELECT /*+ ORDERED INDEX(queue_document IX_QUEUE_DOCUMENT_IDC)
47PUSH_SUBQ(@SUB1) */ *
48       FROM ( SELECT this_.guid,
49       this_.contentObjectGuid,
50       this_.priority,
51       this_.resend_count,
52       this_.queue_item,
53       this_.direction,
54       this_.contentClassName,
55       this_.errorCode,
56       this_.error_message,
57       this_.create_org_sys_name,
58       this_.item_status,
59       this_.creation_date,
60       this_.change_status_date,
61       this_.TARGET_COMPLEX_TYPE,
62       this_.IS_SYSTEM_TRANSIT,
63       this_.DOCTYPE_NAME,
64       THIS_.DOC_LOG_PARENTDOC_GUID,
65       this_.DOC_LOG_VERSION,
66       THIS_.BLOB_SIZE
67       FROM queue_document this_
68       WHERE this_.item_status = 'RECEIVED' AND this_.direction = 'IN'
69       AND this_.guid NOT IN
70         (SELECT /*+ NO_UNNEST QB_NAME(SUB1) */ this_.guid AS y0_
71         FROM QUEUE_DOCUMENT_IN_IDS this_)
72         )
73       ORDER BY priority ASC;
74
75create or replace view V_PQ_OUT as
76  select *   from
77         ( select
78             this_.id,
79             this_.url,
80             this_.createDate,
81             this_.change_status_date,
82             this_.seqNum,
83             this_.sequenceSize,
84             this_.guid,
85             this_.seqGuid,
86             this_.file_size,
87             this_.priority,
88             this_.contentClassName,
89             this_.status,
90             this_.blobContent,
91             this_.errorCode,
92             THIS_.ERRORMESSAGE,
93             THIS_.TO_COMPLEX_ID,
94             THIS_.OWNER_DOCS_COUNT
95         from
96             queue_packet_out this_
97         where
98             this_.status='SEND_READY'
99             and this_.id not in (
100                 select
101                     this_.packetId as y0_
102                 from
103                     QUEUE_PACKET_OUT_IDS this_
104             ) )
105order by
106   PRIORITY ASC,
107   createDate asc;
108
109prompt  --  Тришечкин Евгений Владимирович,  20.06.2012 15:57,  SUFDCORE-7614
110declare
111  v_c number(3);
112  cursor cr_usertoorg is select userinfoid, orgid, list_index from usertoorg where list_index is null order by userinfoid, orgid;
113  TYPE usertoorgs is ref cursor return cr_usertoorg%ROWTYPE;
114  links usertoorgs;
115  currentuserid number;
116  currentindex number;
117begin
118  select 1 into v_c from cols where table_name = 'USERTOORG' and column_name = 'LIST_INDEX' and nullable = 'Y';
119  update usertoorg set list_index = null where userinfoid in (select distinct userinfoid from usertoorg where list_index is null);
120  commit;
121begin
122currentuserid := -9999;
123for links in cr_usertoorg
124loop
125   if (links.userinfoid <> currentuserid) then
126       currentuserid := links.userinfoid;
127       currentindex := 0; 
128   end if;
129   update usertoorg set list_index = currentindex where usertoorg.userinfoid = links.userinfoid and usertoorg.orgid = links.orgid;
130   COMMIT;
131   currentindex := currentindex + 1;
132end loop;
133COMMIT;
134end;
135exception when no_data_found then null;
136end;
137/
138
139declare
140  v_c number(3);
141begin
142  select 1 into v_c from cols where table_name = 'USERTOORG' and column_name = 'LIST_INDEX' and nullable = 'Y';
143  execute immediate 'alter table usertoorg modify list_index not null';
144exception when no_data_found then null;
145end;
146/
147
148
149
150
151
152
153--DELIM ;
154prompt  --ОБНОВЛЕНИЕ ВЕРСИИ
155INSERT INTO VERSIONS ( CORE_VERSION, UPDATE_DATE) VALUES ( '005.004.049.000',sysdate);
156
157COMMIT;