--东芝健诊系统
1 CREATE OR REPLACE PACKAGE TOS_SP_FREQUENCYMNGDATA
2 AS
3 TYPE CursorType IS REF CURSOR;
4 TYPE AssocArrayVarchar2_t IS TABLE OF VARCHAR2(1024) INDEX BY BINARY_INTEGER;
5 TYPE AssocArrayChar_t IS TABLE OF CHAR INDEX BY BINARY_INTEGER;
6 TYPE AssocArrayDate_t IS TABLE OF DATE INDEX BY BINARY_INTEGER;
7 PROCEDURE GET_FREQUENCYMNGDATA(v_FacilityTypeId IN VARCHAR2,v_TypeId IN VARCHAR2,v_StaffId IN VARCHAR2,v_DepartmentId IN VARCHAR2, v_MedCode IN VARCHAR2,V_COLUMNLIST IN VARCHAR2, v_currentDt IN VARCHAR2, v_UseCodeVers IN VARCHAR2,v_frequency IN VARCHAR2,v_compareFlg IN VARCHAR2,V_ACCUMULATESTARTMONTH IN VARCHAR2,V_ACCUMULATEENDMONTH IN VARCHAR2,V_YEARSAMOUNTFLAG IN NUMBER,V_USAGEAMOUNTFLAG IN NUMBER,v_searchNumber IN NUMBER, v_withdata IN VARCHAR2, RefCur_MngdataBO OUT CursorType, v_totalCount OUT VARCHAR2);
8 PROCEDURE SET_FREQUENCYMNGDATA(
9 --V_FREQUENCY_MNG_ID IN AssocArrayVarchar2_t,
10 --V_NEW_FREQUENCY_MNG_ID IN AssocArrayVarchar2_t,
11 V_TYPE_ID IN AssocArrayVarchar2_t,
12 V_NEW_TYPE_ID IN AssocArrayVarchar2_t,
13 V_ACCUMULATE_MONTH IN AssocArrayVarchar2_t,
14 V_NEW_ACCUMULATE_MONTH IN AssocArrayVarchar2_t,
15 V_SEQ IN AssocArrayVarchar2_t,
16 V_NEW_SEQ IN AssocArrayVarchar2_t,
17 V_STAFF_ID IN AssocArrayVarchar2_t,
18 V_DEPARTMENT_ID IN AssocArrayVarchar2_t,
19 V_FACILITY_TYPE_ID IN AssocArrayVarchar2_t,
20 V_ORDER_TYPE IN AssocArrayVarchar2_t,
21 V_MED_ITEM_CODE IN AssocArrayVarchar2_t,
22 V_USE_ITEM_CODE IN AssocArrayVarchar2_t,
23 V_INPUT_CONTENT IN AssocArrayVarchar2_t,
24 V_STAFF_NAME IN AssocArrayVarchar2_t,
25 V_DEPARTMENT_NAME IN AssocArrayVarchar2_t,
26 V_VALID_DATE_BEGIN IN AssocArrayVarchar2_t,
27 V_VALID_DATE_END IN AssocArrayVarchar2_t,
28 V_FREQUENCY IN AssocArrayVarchar2_t,
29 V_CREATE_STAFF_ID IN AssocArrayVarchar2_t,
30 V_CREATE_STAFF_NAME IN AssocArrayVarchar2_t,
31 V_CREATE_DTIME IN AssocArrayVarchar2_t,
32 V_UPDATE_STAFF_ID IN AssocArrayVarchar2_t,
33 V_UPDATE_STAFF_NAME IN AssocArrayVarchar2_t,
34 V_UPDATE_DTIME IN AssocArrayVarchar2_t,
35 V_ACTIONFLAG IN AssocArrayChar_t,
36 RegistrationType IN VARCHAR2,
37 ErrorInfo OUT CLOB,
38 SystemDate OUT DATE
39 ) ;
40 PROCEDURE DEL_FREQUENCYMNGDATA(
41 --V_FREQUENCY_MNG_ID IN AssocArrayVarchar2_t,
42 V_TYPE_ID IN AssocArrayVarchar2_t,
43 V_ACCUMULATE_MONTH IN AssocArrayVarchar2_t,
44 V_SEQ IN AssocArrayVarchar2_t,
45 RegistrationType IN VARCHAR2,
46 ErrorInfo OUT CLOB
47 ) ;
48 END;
49 /
50
51 CREATE OR REPLACE PACKAGE BODY TOS_SP_FREQUENCYMNGDATA
52 AS
53 ------------------------------------PROCEDURE----------------------------------------------------------------------------
54 -- Name : GET_MEDUSAGEMNGDATASC
55 -- Description : To GET the MEDUSAGEFREQUENCYLISTMNGDATASC data details
56 -- Author : ChenFei
57 -- Created on : 8/8/2011
58 --
59 -- Version Modified by Date Description Verified by
60 -------------------------------------------------------------------------------------------------------------------------
61 -------------------------------------------------------------------------------------------------------------------------
62 --update by 2011/09/27 start
63 --PROCEDURE GET_FREQUENCYMNGDATA(v_medCode IN VARCHAR2, v_currentDt IN VARCHAR2, v_useCode IN VARCHAR2,v_frequency IN VARCHAR2,v_compareFlg IN VARCHAR2,v_searchNumber IN VARCHAR2, v_withdata IN VARCHAR2, RefCur_MngdataBO OUT CursorType, v_totalCount OUT VARCHAR2)
64 --update by 2011/09/27 end
65 PROCEDURE GET_FREQUENCYMNGDATA(v_FacilityTypeId IN VARCHAR2,v_TypeId IN VARCHAR2,v_StaffId IN VARCHAR2,v_DepartmentId IN VARCHAR2, v_MedCode IN VARCHAR2,V_COLUMNLIST IN VARCHAR2, v_currentDt IN VARCHAR2, v_UseCodeVers IN VARCHAR2,v_frequency IN VARCHAR2,v_compareFlg IN VARCHAR2,V_ACCUMULATESTARTMONTH IN VARCHAR2,V_ACCUMULATEENDMONTH IN VARCHAR2,V_YEARSAMOUNTFLAG IN NUMBER,V_USAGEAMOUNTFLAG IN NUMBER,v_searchNumber IN NUMBER, v_withdata IN VARCHAR2, RefCur_MngdataBO OUT CursorType, v_totalCount OUT VARCHAR2)
66 IS
67 --strCondition VARCHAR2(32767) := '';
68 strCondition VARCHAR2(4000) := '';
69 strAnd VARCHAR2(5) := '';
70 --strQuery VARCHAR2(32767) := '';
71 strQuery VARCHAR2(4000) := '';
72 --strDate VARCHAR2(1000) := '';
73 strDate VARCHAR2(16) := '';
74 strReplace VARCHAR2(4000) := '';
75 --20111124 CF Add Strat
76 strQuery1 VARCHAR2(32767) := '';
77 strSelectFields VARCHAR2(32767) :='';
78 strSelectFields1 VARCHAR2(32767) :='';
79 strSelectFields2 VARCHAR2(32767) :='';
80 strSelectFields3 VARCHAR2(32767) :='';
81 strSelectFieldsend VARCHAR2(32767) :='';
82 strOntimestring VARCHAR2(32767) :='';
83 strTYPE_ID VARCHAR2(32767) :='';
84 strFACILITY_TYPE_ID VARCHAR2(32767) :='';
85 strUSE_ITEM_CODE VARCHAR2(32767) :='';
86 strMED_ITEM_CODE VARCHAR2(32767) :='';
87 strACCUMULATE_MONTH VARCHAR2(32767) :='';
88 --20111124 CF Add End
89 BEGIN
90 --add by 2011/09/27 start
91 IF v_FacilityTypeId IS NOT NULL THEN
92 strCondition := strCondition || strAnd || 'FACILITY_TYPE_ID = '|| v_FacilityTypeId ;
93 strAnd := ' AND ';
94 END IF;
95 --add by 2011/09/27 end
96
97 --add by : 2011/10/27 start
98 IF v_TypeId IS NOT NULL THEN
99 strCondition := strCondition || strAnd || 'TYPE_ID = '|| v_TypeId ;
100 strAnd := ' AND ';
101 END IF;
102
103 IF v_StaffId IS NOT NULL THEN
104 strCondition := strCondition || strAnd || 'STAFF_ID = '|| v_StaffId ;
105 strAnd := ' AND ';
106 END IF;
107
108 IF v_DepartmentId IS NOT NULL THEN
109 strCondition := strCondition || strAnd || 'DEPARTMENT_ID = '|| v_DepartmentId ;
110 strAnd := ' AND ';
111 END IF;
112
113 --add by : 2011/10/27 start
114
115 --update by 2011/09/27 start
116 /* IF v_medCode IS NOT NULL THEN
117 strCondition := strCondition || strAnd || ' MED_ITEM_CODE = ''' || v_MedCodeVers|| '''' ;
118 strAnd := ' AND ';
119 END IF;*/
120 IF v_MedCode IS NOT NULL THEN
121 strReplace := REPLACE(v_MedCode, 'FieldName', 'MED_ITEM_CODE');
122 strCondition := strCondition || strAnd || strReplace ;
123 strAnd := ' AND ';
124 END IF;
125 --update by 2011/09/27 end
126 if v_currentDt is not null then
127 --update by 2011/09/14 start
128 IF v_currentDt != '0001-01-01T00:00:00' THEN
129 strDate := substr(v_currentDt, 1, 10);
130 ELSE
131 strDate:=v_currentDt;
132 END IF;
133 strCondition := strCondition || strAnd || ' TO_DATE(TOS_D_FREQUENCY_MNG.VALID_DATE_BEGIN,''YYYYMMDD'') <= TO_DATE(''' || strDate ||''', ''YYYYMMDD'') AND TO_DATE(TOS_D_FREQUENCY_MNG.VALID_DATE_END,''YYYYMMDD'') >= TO_DATE(''' || strDate || ''', ''YYYYMMDD'')' ;
134 strAnd := ' AND ';
135 --update by 2011/09/14 end
136 END IF;
137 --20111124 CF Add Mantices:2555 Start
138 IF V_ACCUMULATESTARTMONTH IS NOT NULL THEN
139 strCondition:= strCondition || strAnd || ' TOS_D_FREQUENCY_MNG.ACCUMULATE_MONTH >= ' || V_ACCUMULATESTARTMONTH;
140 strAnd := ' AND ';
141 IF V_ACCUMULATEENDMONTH IS NOT NULL THEN
142 strCondition:= strCondition || strAnd || ' TOS_D_FREQUENCY_MNG.ACCUMULATE_MONTH <= ' || V_ACCUMULATEENDMONTH ;
143 strAnd := ' AND ';
144 END IF;
145 END IF;
146 --20111124 CF Add Mantices:2555 End
147 --update by 2011/09/27 start
148 /*IF v_useCode IS NOT NULL THEN
149 strCondition := strCondition || strAnd || ' USE_ITEM_CODE = ''' || v_useCode|| '''';
150 strAnd := 'AND ';
151 END IF;*/
152 IF v_UseCodeVers IS NOT NULL THEN
153 strReplace := REPLACE(v_UseCodeVers, 'FieldName', 'USE_ITEM_CODE');
154 strCondition := strCondition || strAnd || strReplace ;
155 strAnd := ' AND ';
156 END IF;
157 --update by 2011/09/27 end
158
159 IF v_frequency IS NOT NULL THEN
160 IF v_compareFlg IS NOT NULL THEN
161 IF v_compareFlg ='0' THEN
162 strCondition := strCondition || strAnd || ' FREQUENCY > ' || v_frequency ;
163 strAnd := ' AND ';
164 ELSIF v_compareFlg ='2' THEN
165 strCondition := strCondition || strAnd || ' FREQUENCY = ' || v_frequency;
166 strAnd := ' AND ';
167 ELSIF v_compareFlg ='1' THEN
168 strCondition := strCondition || strAnd || ' FREQUENCY < ' || v_frequency;
169 strAnd := ' AND ';
170 ----add by : 2011/09/27 start
171 ELSIF v_compareFlg ='3' THEN
172 strCondition := strCondition || strAnd || ' FREQUENCY >= '|| v_frequency;
173 strAnd := ' AND ';
174 ELSIF v_compareFlg ='4' THEN
175 strCondition := strCondition || strAnd || ' FREQUENCY <= '|| v_frequency;
176 strAnd := ' AND ';
177 ----add by : 2011/09/27 end
178 END IF;
179 --add by : 2011/09/15 start
180 ELSe
181 strCondition := strCondition || strAnd || ' FREQUENCY > ' || v_frequency;
182 strAnd := ' AND ';
183 --add by : 2011/09/15 end
184 END IF;
185 END IF;
186 IF v_searchNumber IS NOT NULL THEN
187 strCondition := strCondition || strAnd || ' ROWNUM <= ' || v_searchNumber;
188 strAnd := ' AND ';
189 END IF;
190 IF Length(strAnd) > 0 THEN
191 strCondition := ' WHERE ' || strCondition;
192 END IF ;
193 -- To get the total count of records matching the condition
194 strQuery := ' SELECT COUNT(1) FROM TOS_D_FREQUENCY_MNG ' || strCondition;
195 EXECUTE IMMEDIATE strQuery INTO v_totalCount;
196
197 --20111124 CF Add Mantices:2555 Start
198 strSelectFields := strSelectFields || '
199 TYPE_ID TypeId ,
200 ACCUMULATE_MONTH AccumulateMonth ,
201 SEQ Seq ,
202 STAFF_ID StaffID ,
203 DEPARTMENT_ID DepartmentID ,
204 FACILITY_TYPE_ID FacilityTypeID ,
205 ORDER_TYPE OrderType ,
206 MED_ITEM_CODE MedCode ,
207 USE_ITEM_CODE UseCode ,
208 INPUT_CONTENT INPUTCONTENT ,
209 STAFF_NAME StaffName ,
210 DEPARTMENT_NAME DepartmentName ,
211 VALID_DATE_BEGIN ValidDateBegin ,
212 VALID_DATE_END ValidDateEnd ,
213 FREQUENCY Frequency ,
214 CREATE_STAFF_ID CreateStaffID ,
215 CREATE_STAFF_NAME CreateStaffName ,
216 CREATE_DTIME CreateDT ,
217 UPDATE_STAFF_ID UpdateStaffID ,
218 UPDATE_STAFF_NAME UpdateStaffName ,
219 UPDATE_DTIME UpdateDT
220 ';
221 strSelectFieldsend := strSelectFieldsend || '
222 Tableff.TYPE_ID TypeId ,
223 TableFREQUENCY.Frequency Frequency ,
224 Tableff.ACCUMULATE_MONTH AccumulateMonth ,
225 Tableff.SEQ Seq ,
226 Tableff.STAFF_ID StaffID ,
227 Tableff.DEPARTMENT_ID DepartmentID ,
228 Tableff.FACILITY_TYPE_ID FacilityTypeID ,
229 Tableff.ORDER_TYPE OrderType ,
230 Tableff.MED_ITEM_CODE MedCode ,
231 Tableff.USE_ITEM_CODE UseCode ,
232 Tableff.INPUT_CONTENT INPUTCONTENT ,
233 Tableff.STAFF_NAME StaffName ,
234 Tableff.DEPARTMENT_NAME DepartmentName ,
235 Tableff.VALID_DATE_BEGIN ValidDateBegin ,
236 Tableff.VALID_DATE_END ValidDateEnd ,
237 Tableff.CREATE_STAFF_ID CreateStaffID ,
238 Tableff.CREATE_STAFF_NAME CreateStaffName ,
239 Tableff.CREATE_DTIME CreateDT ,
240 TableFREQUENCY.strTYPE_ID strTYPE_ID ,
241 TableFREQUENCY.strFACILITY_TYPE_ID strFACILITY_TYPE_ID ,
242 TableFREQUENCY.strUSE_ITEM_CODE strUSE_ITEM_CODE ,
243 Tableff.UPDATE_STAFF_ID UpdateStaffID ,
244 Tableff.UPDATE_STAFF_NAME UpdateStaffName ,
245 Tableff.UPDATE_DTIME UpdateDT
246 ';
247 strSelectFields2 := strSelectFields2 || '
248 TYPE_ID,
249 FACILITY_TYPE_ID,
250 MED_ITEM_CODE,
251 USE_ITEM_CODE
252 ';
253 strSelectFields3 := strSelectFields3 || '
254 TYPE_ID,
255 FACILITY_TYPE_ID,
256 ACCUMULATE_MONTH,
257 USE_ITEM_CODE
258 ';
259 strSelectFields1 := strSelectFields1 || '
260 TYPE_ID,
261 FACILITY_TYPE_ID,
262 USE_ITEM_CODE
263 ';
264 -- TableF.FREQUENCY Frequency ,
265 --check is clear
266 IF strQuery1 IS NULL THEN
267 strQuery1 := '
268 SELECT ' ||strSelectFields ||' FROM TOS_D_FREQUENCY_MNG ORDER BY FREQUENCY DESC ';
269 END IF;
270 IF V_TYPEID =2 OR V_TYPEID =3 OR V_TYPEID=4 THEN
271 strQuery1 := '
272 select * from (SELECT ' ||strSelectFields ||' FROM TOS_D_FREQUENCY_MNG ' || strCondition || ' ORDER BY UPDATE_DTIME DESC ) where ROWNUM<21 ' ;
273 ELSIF V_TYPEID =1 THEN /*OR
274 TO_DATE('TOS_D_FREQUENCY_MNG.VALID_DATE_BEGIN','YYYYMMDD') <= TO_DATE('|| strDate ||', 'YYYYMMDD') AND
275 TO_DATE('TOS_D_FREQUENCY_MNG.VALID_DATE_END','YYYYMMDD') >= TO_DATE( '|| strDate ||', 'YYYYMMDD')*/
276 strQuery1 := '
277 SELECT ' ||strSelectFields ||' FROM TOS_D_FREQUENCY_MNG ' || strCondition || ' ORDER BY UPDATE_DTIME DESC ' ;
278 END IF;
279 --question Start
280 IF V_YEARSAMOUNTFLAG =1 THEN
281 --strFrequencySum :='select sum(FREQUENCYSUM) from (select sum(FREQUENCY) FREQUENCYSUM from tos_d_frequency_mng group by med_item_code,use_item_code)'||strCondition;
282 --strFrequencySum :='select sum(FREQUENCYSUM) FREQUENCY from ( select sum(FREQUENCY) FREQUENCYSUM from tos_d_frequency_mng '||strCondition||' group by MED_ITEM_CODE,USE_ITEM_CODE)';
283 --strQuery1 := '
284 --SELECT ' ||strSelectFields2 ||' FROM TOS_D_FREQUENCY_MNG TableF, ('||strFrequencySum||') TableFF ';
285 --strQuery1 := 'select '||strSelectFields2||', sum(FREQUENCY) FREQUENCY from tos_d_frequency_mng '||strCondition||'
286 --group by '||strSelectFields2;
287 strOntimestring :='select TYPE_ID strTYPE_ID,FACILITY_TYPE_ID strFACILITY_TYPE_ID 丆USE_ITEM_CODE strUSE_ITEM_CODE,MED_ITEM_CODE strMED_ITEM_CODE,sum(FREQUENCY) Frequency FROM TOS_D_FREQUENCY_MNG '||strCondition||' group by '||strSelectFields2;
288 strTYPE_ID:='select TYPE_ID FROM TOS_D_FREQUENCY_MNG '||strCondition||' group by '||strSelectFields2;
289 strFACILITY_TYPE_ID:='select FACILITY_TYPE_ID FROM TOS_D_FREQUENCY_MNG '||strCondition||' group by '||strSelectFields2;
290 strUSE_ITEM_CODE:='select USE_ITEM_CODE FROM TOS_D_FREQUENCY_MNG '||strCondition||' group by '||strSelectFields2;
291 strMED_ITEM_CODE:='select MED_ITEM_CODE FROM TOS_D_FREQUENCY_MNG '||strCondition||' group by '||strSelectFields2;
292 strQuery1 := '
293 select
294 A.TypeId,
295 A.FacilityTypeID,
296 A.MedCode,
297 A.UseCode,
298 A.Frequency,
299 B.*
300 from
301 (
302 select '||strSelectFieldsEnd||' from TOS_D_FREQUENCY_MNG Tableff ,('||strOntimestring||') TableFREQUENCY where TYPE_ID in ('||strTYPE_ID||') and FACILITY_TYPE_ID in ('||strFACILITY_TYPE_ID||') and USE_ITEM_CODE in ('||strUSE_ITEM_CODE||') and MED_ITEM_CODE in ('||strMED_ITEM_CODE||') and
303 strTYPE_ID =TYPE_ID
304 and
305 strFACILITY_TYPE_ID =FACILITY_TYPE_ID
306 and strUSE_ITEM_CODE=USE_ITEM_CODE
307 and strMED_ITEM_CODE=MED_ITEM_CODE
308 )A
309 left outer join
310 (
311 select
312 ACCUMULATE_MONTH AccumulateMonth ,
313 SEQ Seq ,
314 STAFF_ID StaffID ,
315 DEPARTMENT_ID DepartmentID ,
316 ORDER_TYPE OrderType ,
317 INPUT_CONTENT INPUTCONTENT ,
318 STAFF_NAME StaffName ,
319 DEPARTMENT_NAME DepartmentName ,
320 VALID_DATE_BEGIN ValidDateBegin ,
321 VALID_DATE_END ValidDateEnd ,
322 CREATE_STAFF_ID CreateStaffID ,
323 CREATE_STAFF_NAME CreateStaffName ,
324 CREATE_DTIME CreateDT ,
325 UPDATE_STAFF_ID UpdateStaffID ,
326 UPDATE_STAFF_NAME UpdateStaffName ,
327 UPDATE_DTIME UpdateDT
328 from TOS_D_FREQUENCY_MNG where 1=0
329 )B
330 on 1>0
331 ';
332 ELSIF V_USAGEAMOUNTFLAG =1 THEN
333 --strFrequencySum :='select sum(FREQUENCYSUM) from (select sum(FREQUENCY) FREQUENCYSUM from tos_d_frequency_mng group by use_item_code,ACCUMULATE_MONTH)'||strCondition;
334 --strFrequencySum :='select sum(FREQUENCYSUM) FREQUENCY from ( select sum(FREQUENCY) FREQUENCYSUM from tos_d_frequency_mng '||strCondition||' group by USE_ITEM_CODE,ACCUMULATE_MONTH)';
335 --strQuery1 := '
336 --SELECT ' ||strSelectFields3 ||' FROM TOS_D_FREQUENCY_MNG TableF, ('||strFrequencySum||') TableFF ';
337 --strQuery1 := 'select '||strSelectFields3||', sum(FREQUENCY) FREQUENCY from tos_d_frequency_mng '||strCondition||'
338 --group by '||strSelectFields3;
339 strOntimestring :='select TYPE_ID strTYPE_ID,FACILITY_TYPE_ID strFACILITY_TYPE_ID丆USE_ITEM_CODE strUSE_ITEM_CODE,ACCUMULATE_MONTH strACCUMULATE_MONTH,sum(FREQUENCY) Frequency FROM TOS_D_FREQUENCY_MNG '||strCondition||' group by '||strSelectFields3;
340 strTYPE_ID:='select TYPE_ID FROM TOS_D_FREQUENCY_MNG '||strCondition||' group by '||strSelectFields3;
341 strFACILITY_TYPE_ID:='select FACILITY_TYPE_ID FROM TOS_D_FREQUENCY_MNG '||strCondition||' group by '||strSelectFields3;
342 strUSE_ITEM_CODE:='select USE_ITEM_CODE FROM TOS_D_FREQUENCY_MNG '||strCondition||' group by '||strSelectFields3;
343 strACCUMULATE_MONTH:='select ACCUMULATE_MONTH FROM TOS_D_FREQUENCY_MNG '||strCondition||' group by '||strSelectFields3;
344 strQuery1 := '
345 select
346 A.TypeId,
347 A.FacilityTypeID,
348 A.UseCode,
349 A.AccumulateMonth,
350 A.Frequency,
351 B.*
352 from
353 (
354 select '||strSelectFieldsEnd||' from TOS_D_FREQUENCY_MNG Tableff ,('||strOntimestring||') TableFREQUENCY where TYPE_ID in ('||strTYPE_ID||') and FACILITY_TYPE_ID in ('||strFACILITY_TYPE_ID||') and USE_ITEM_CODE in ('||strUSE_ITEM_CODE||') and ACCUMULATE_MONTH in ('||strACCUMULATE_MONTH||') and
355 strTYPE_ID =TYPE_ID
356 and
357 strFACILITY_TYPE_ID =FACILITY_TYPE_ID
358 and strUSE_ITEM_CODE=USE_ITEM_CODE
359 and strACCUMULATE_MONTH=ACCUMULATE_MONTH
360 )A
361 left outer join
362 (
363 select
364 STAFF_ID StaffID ,
365 SEQ Seq ,
366 DEPARTMENT_ID DepartmentID ,
367 ORDER_TYPE OrderType ,
368 MED_ITEM_CODE MedCode ,
369 INPUT_CONTENT INPUTCONTENT ,
370 STAFF_NAME StaffName ,
371 DEPARTMENT_NAME DepartmentName ,
372 VALID_DATE_BEGIN ValidDateBegin ,
373 VALID_DATE_END ValidDateEnd ,
374 CREATE_STAFF_ID CreateStaffID ,
375 CREATE_STAFF_NAME CreateStaffName ,
376 CREATE_DTIME CreateDT ,
377 UPDATE_STAFF_ID UpdateStaffID ,
378 UPDATE_STAFF_NAME UpdateStaffName ,
379 UPDATE_DTIME UpdateDT
380 from TOS_D_FREQUENCY_MNG where 1=0
381 )B
382 on 1>0
383 ';
384 END IF;
385 --question End
386 IF V_YEARSAMOUNTFLAG =1 AND V_USAGEAMOUNTFLAG =1 THEN
387 --strFrequencySum :='SELECT SUM(Frequency) FROM TOS_D_FREQUENCY_MNG '||strCondition;
388 --strFrequencySum :='Select sum(FREQUENCY) from TOS_D_FREQUENCY_MNG where type_id=3 group by type_id';
389 --strFrequencySum :='select sum(FREQUENCYSUM) FREQUENCY from ( select sum(FREQUENCY) FREQUENCYSUM from tos_d_frequency_mng '||strCondition||' group by USE_ITEM_CODE)';
390 --strQuery1 := 'select '||strSelectFields1||', sum(FREQUENCY) FREQUENCY from tos_d_frequency_mng '||strCondition||'
391 -- group by '||strSelectFields1;
392 strOntimestring :='select TYPE_ID strTYPE_ID,FACILITY_TYPE_ID strFACILITY_TYPE_ID 丆USE_ITEM_CODE strUSE_ITEM_CODE,sum(FREQUENCY) Frequency FROM TOS_D_FREQUENCY_MNG '||strCondition||' group by '||strSelectFields1;
393 strTYPE_ID:='select TYPE_ID FROM TOS_D_FREQUENCY_MNG '||strCondition||' group by '||strSelectFields1;
394 strFACILITY_TYPE_ID:='select FACILITY_TYPE_ID FROM TOS_D_FREQUENCY_MNG '||strCondition||' group by '||strSelectFields1;
395 strUSE_ITEM_CODE:='select USE_ITEM_CODE FROM TOS_D_FREQUENCY_MNG '||strCondition||' group by '||strSelectFields1;
396 --A.repalce setting values
397 strQuery1 := '
398 select
399 A.TypeId,
400 A.FacilityTypeID,
401 A.UseCode,
402 A.Frequency,
403 B.*
404 from
405 (
406 select '||strSelectFieldsEnd||' from TOS_D_FREQUENCY_MNG Tableff ,('||strOntimestring||') TableFREQUENCY where TYPE_ID in ('||strTYPE_ID||') and FACILITY_TYPE_ID in ('||strFACILITY_TYPE_ID||') and USE_ITEM_CODE in ('||strUSE_ITEM_CODE||') and
407 strTYPE_ID =TYPE_ID
408 and
409 strFACILITY_TYPE_ID =FACILITY_TYPE_ID
410 and strUSE_ITEM_CODE=USE_ITEM_CODE
411 )A
412 left outer join
413 (
414 select
415 ACCUMULATE_MONTH AccumulateMonth ,
416 SEQ Seq ,
417 STAFF_ID StaffID ,
418 DEPARTMENT_ID DepartmentID ,
419 ORDER_TYPE OrderType ,
420 MED_ITEM_CODE MedCode ,
421 INPUT_CONTENT INPUTCONTENT ,
422 STAFF_NAME StaffName ,
423 DEPARTMENT_NAME DepartmentName ,
424 VALID_DATE_BEGIN ValidDateBegin ,
425 VALID_DATE_END ValidDateEnd ,
426 CREATE_STAFF_ID CreateStaffID ,
427 CREATE_STAFF_NAME CreateStaffName ,
428 CREATE_DTIME CreateDT ,
429 UPDATE_STAFF_ID UpdateStaffID ,
430 UPDATE_STAFF_NAME UpdateStaffName ,
431 UPDATE_DTIME UpdateDT
432 from TOS_D_FREQUENCY_MNG where 1=0
433 )B
434 on 1>0
435 ';
436 --strQuery1 := 'select '||strSelectFieldsEnd||' from table TOS_D_FREQUENCY_MNG Tableff inner join table ('||strOntimestring||') TableFREQUENCY
437 --on TYPE_ID in ('||strTYPE_ID||') and FACILITY_TYPE_ID in ('||strFACILITY_TYPE_ID||') and USE_ITEM_CODE in ('||strUSE_ITEM_CODE||')';
438 END IF;
439 -- CF Add Mantices:2555 End
440 IF v_withdata IS NOT NULL THEN
441 IF v_withdata ='1' THEN
442 IF V_COLUMNLIST IS NOT NULL THEN
443 --20111124 CF Mantis:2555 Start
444 /*OPEN RefCur_MngdataBO FOR ' SELECT ' ||V_COLUMNLIST|| ' FROM TOS_D_FREQUENCY_MNG ' || strCondition || ' ORDER BY FREQUENCY desc '; */
445 OPEN RefCur_MngdataBO FOR 'SELECT ' ||v_columnlist || ' FROM ( ' ||strQuery1|| ') ORDER BY FREQUENCY DESC';
446 ELSE
447 /*
448 OPEN RefCur_MngdataBO FOR
449 ' SELECT
450 TYPE_ID TypeId ,
451 ACCUMULATE_MONTH AccumulateMonth ,
452 SEQ Seq ,
453 STAFF_ID StaffID ,
454 DEPARTMENT_ID DepartmentID ,
455 FACILITY_TYPE_ID FacilityTypeID ,
456 ORDER_TYPE OrderType ,
457 MED_ITEM_CODE MedCode ,
458 USE_ITEM_CODE UseCode ,
459 INPUT_CONTENT INPUTCONTENT ,
460 STAFF_NAME StaffName ,
461 DEPARTMENT_NAME DepartmentName ,
462 VALID_DATE_BEGIN ValidDateBegin ,
463 VALID_DATE_END ValidDateEnd ,
464 FREQUENCY Frequency ,
465 CREATE_STAFF_ID CreateStaffID ,
466 CREATE_STAFF_NAME CreateStaffName ,
467 CREATE_DTIME CreateDT ,
468 UPDATE_STAFF_ID UpdateStaffID ,
469 UPDATE_STAFF_NAME UpdateStaffName ,
470 UPDATE_DTIME UpdateDT
471 FROM TOS_D_FREQUENCY_MNG ' || strCondition || ' ORDER BY FREQUENCY desc ';
472 */
473 OPEN RefCur_MngdataBO FOR
474 ' SELECT * FROM ( ' ||strQuery1|| ') ORDER BY FREQUENCY desc';
475 --20111124 CF Mantis:2555 End
476 END IF;
477 ELSE
478 -- OPEN RefCur_MngdataBO FOR 'SELECT DEPARTMENT_ID丆FACILITY_TYPE_ID丆MED_ITEM_CODE丆USE_ITEM_CODE丆DEPARTMENT_NAME丆VALID_DATE_BEGIN丆VALID_DATE_END丆FREQUENCY丆CREATE_STAFF_ID丆CREATE_STAFF_NAME丆CREATE_DTIME丆UPDATE_STAFF_ID丆UPDATE_STAFF_NAME丆UPDATE_DTIME FROM TOS_D_FREQUENCY_MNG Where 1 = 0';
479 OPEN RefCur_MngdataBO FOR
480 ' SELECT
481 TYPE_ID TypeId ,
482 ACCUMULATE_MONTH AccumulateMonth ,
483 SEQ Seq ,
484 STAFF_ID StaffID ,
485 DEPARTMENT_ID DepartmentID ,
486 FACILITY_TYPE_ID FacilityTypeID ,
487 ORDER_TYPE OrderType,
488 MED_ITEM_CODE MedCode ,
489 USE_ITEM_CODE UseCode ,
490 INPUT_CONTENT INPUTCONTENT ,
491 STAFF_NAME StaffName ,
492 DEPARTMENT_NAME DepartmentName ,
493 VALID_DATE_BEGIN ValidDateBegin ,
494 VALID_DATE_END ValidDateEnd ,
495 FREQUENCY Frequency ,
496 CREATE_STAFF_ID CreateStaffID ,
497 CREATE_STAFF_NAME CreateStaffName ,
498 CREATE_DTIME CreateDT ,
499 UPDATE_STAFF_ID UpdateStaffID ,
500 UPDATE_STAFF_NAME UpdateStaffName ,
501 UPDATE_DTIME UpdateDT
502 FROM TOS_D_FREQUENCY_MNG Where 1 = 0 ';
503 END IF;
504 END IF;
505 END GET_FREQUENCYMNGDATA;
506 ------------------------------------PROCEDURE----------------------------------------------------------------------------
507 -- Name : SET_MEDUSAGEMNGDATASC
508 -- Description : To INSERT/UPDATE/DELETE the MEDUSAGEFREQUENCYLISTMNGDATASC data details
509 -- Author : ChenFei
510 -- Created on : 8/8/2011
511 --
512 -- Version Modified by Date Description Verified by
513 -------------------------------------------------------------------------------------------------------------------------
514 -------------------------------------------------------------------------------------------------------------------------
515 PROCEDURE SET_FREQUENCYMNGDATA(
516 --V_FREQUENCY_MNG_ID IN AssocArrayVarchar2_t,
517 --V_NEW_FREQUENCY_MNG_ID IN AssocArrayVarchar2_t,
518 V_TYPE_ID IN AssocArrayVarchar2_t,
519 V_NEW_TYPE_ID IN AssocArrayVarchar2_t,
520 V_ACCUMULATE_MONTH IN AssocArrayVarchar2_t,
521 V_NEW_ACCUMULATE_MONTH IN AssocArrayVarchar2_t,
522 V_SEQ IN AssocArrayVarchar2_t,
523 V_NEW_SEQ IN AssocArrayVarchar2_t,
524 V_STAFF_ID IN AssocArrayVarchar2_t,
525 V_DEPARTMENT_ID IN AssocArrayVarchar2_t,
526 V_FACILITY_TYPE_ID IN AssocArrayVarchar2_t,
527 --add STAFF_NAME [Mantis1913] by -ZhuYayun 2011-11-22 start
528 V_ORDER_TYPE IN AssocArrayVarchar2_t,
529 --add STAFF_NAME [Mantis1913] by -ZhuYayun 2011-11-22 end
530 V_MED_ITEM_CODE IN AssocArrayVarchar2_t,
531 V_USE_ITEM_CODE IN AssocArrayVarchar2_t,
532 V_INPUT_CONTENT IN AssocArrayVarchar2_t,
533 V_STAFF_NAME IN AssocArrayVarchar2_t,
534 V_DEPARTMENT_NAME IN AssocArrayVarchar2_t,
535 V_VALID_DATE_BEGIN IN AssocArrayVarchar2_t,
536 V_VALID_DATE_END IN AssocArrayVarchar2_t,
537 V_FREQUENCY IN AssocArrayVarchar2_t,
538 V_CREATE_STAFF_ID IN AssocArrayVarchar2_t,
539 V_CREATE_STAFF_NAME IN AssocArrayVarchar2_t,
540 V_CREATE_DTIME IN AssocArrayVarchar2_t,
541 V_UPDATE_STAFF_ID IN AssocArrayVarchar2_t,
542 V_UPDATE_STAFF_NAME IN AssocArrayVarchar2_t,
543 V_UPDATE_DTIME IN AssocArrayVarchar2_t,
544 V_ACTIONFLAG IN AssocArrayChar_t,
545 RegistrationType IN VARCHAR2,
546 ErrorInfo OUT CLOB,
547 SystemDate OUT DATE
548 )
549 IS
550 --strFREQUENCY_MNG_ID NUMBER(14) :=0;
551 --strNEW_FREQUENCY_MNG_ID NUMBER(14) :=0;
552 strTYPE_ID VARCHAR2(1) := '';
553 strNEW_TYPE_ID VARCHAR2(1) := '';
554 strACCUMULATE_MONTH NUMBER(4) :=0;
555 strNEW_ACCUMULATE_MONTH NUMBER(4) :=0;
556 strSEQ NUMBER(14) :=0;
557 strNEW_SEQ NUMBER(14) :=0;
558 strSTAFF_ID NUMBER(10) :=0;
559 strDEPARTMENT_ID NUMBER(2) :=0;
560 strFACILITY_TYPE_ID NUMBER(2) :=0;
561 strORDER_TYPE NUMBER(2) :=0;
562 strMED_ITEM_CODE NUMBER(10) :=0;
563 strUSE_ITEM_CODE NUMBER(10) :=0;
564 strINPUT_CONTENT NUMBER(10) :=0;
565 strSTAFF_NAME NVARCHAR2(30) :='';
566 strDEPARTMENT_NAME VARCHAR2(16) :='';
567 strVALID_DATE_BEGIN VARCHAR2(8) :='';
568 strVALID_DATE_END VARCHAR2(8) :='';
569 strFREQUENCY NUMBER(10) :=0;
570 strCREATE_STAFF_ID NUMBER(10) :=0;
571 strCREATE_STAFF_NAME NVARCHAR2(30) :='';
572 strCREATE_DTIME VARCHAR2(20) :='';
573 strUPDATE_STAFF_ID NUMBER(10) :=0;
574 strUPDATE_STAFF_NAME NVARCHAR2(30) :='';
575 strUPDATE_DTIME VARCHAR2(20) :='';
576 actionFlag CHAR(1) :='';
577 -- ( 0 - Invalid argument exception 1 - Insert, 2 - Update, 3 - Delete)
578 -- Stores the error information in the form of
579 -- <rowIndex>*<ErrorNumber>*<ErrorMessage>
580 strErrMess VARCHAR2(1000) := '';
581 strsysdate Date;
582 stroldyear VARCHAR2(4) := '';
583 BEGIN
584 SystemDate :=sysdate;
585 strsysdate :=sysdate;
586
587 DBMS_LOB.CREATETEMPORARY(ErrorInfo, TRUE);
588 --FOR i IN 1..V_FREQUENCY_MNG_ID.COUNT
589 FOR i IN 1..V_TYPE_ID.COUNT
590 LOOP
591 BEGIN
592 --strFREQUENCY_MNG_ID := V_FREQUENCY_MNG_ID(i);
593 --strNEW_FREQUENCY_MNG_ID := V_NEW_FREQUENCY_MNG_ID(i);
594 strTYPE_ID := V_TYPE_ID(i);
595 strNEW_TYPE_ID := V_NEW_TYPE_ID(i);
596 strACCUMULATE_MONTH := V_ACCUMULATE_MONTH(i);
597 strNEW_ACCUMULATE_MONTH := V_NEW_ACCUMULATE_MONTH(i);
598 strSEQ := V_SEQ(i);
599 strNEW_SEQ := V_NEW_SEQ(i);
600 strSTAFF_ID := V_STAFF_ID(i);
601 strDEPARTMENT_ID := V_DEPARTMENT_ID(i);
602 strFACILITY_TYPE_ID := V_FACILITY_TYPE_ID(i);
603 strORDER_TYPE := V_ORDER_TYPE(i);
604 strMED_ITEM_CODE := V_MED_ITEM_CODE(i);
605 strUSE_ITEM_CODE := V_USE_ITEM_CODE(i);
606 strINPUT_CONTENT := V_INPUT_CONTENT(i);
607 strSTAFF_NAME := V_STAFF_NAME(i);
608 strDEPARTMENT_NAME := V_DEPARTMENT_NAME(i);
609 strVALID_DATE_BEGIN := V_VALID_DATE_BEGIN(i);
610 strVALID_DATE_END := V_VALID_DATE_END (i);
611 strFREQUENCY := V_FREQUENCY (i);
612 strCREATE_STAFF_ID := V_CREATE_STAFF_ID(i);
613 strCREATE_STAFF_NAME := V_CREATE_STAFF_NAME(i);
614 strCREATE_DTIME := V_CREATE_DTIME(i);
615 strUPDATE_STAFF_ID := V_UPDATE_STAFF_ID(i);
616 strUPDATE_STAFF_NAME := V_UPDATE_STAFF_NAME(i);
617 strUPDATE_DTIME := V_UPDATE_DTIME (i);
618 actionFlag := V_ACTIONFLAG (i);
619 /*IF strCREATE_DTIME = '0001-01-01 00:00:00' THEN
620 strCREATE_DTIME := '';
621 END IF;
622 IF strUPDATE_DTIME = '0001-01-01 00:00:00' THEN
623 strUPDATE_DTIME := '';
624 END IF;*/
625 IF actionFlag='2' THEN
626 BEGIN
627 UPDATE
628 TOS_D_FREQUENCY_MNG
629 SET
630 --FREQUENCY_MNG_ID = strNEW_FREQUENCY_MNG_ID,
631 TYPE_ID = strNEW_TYPE_ID,
632 ACCUMULATE_MONTH = strNEW_ACCUMULATE_MONTH,
633 SEQ = strNEW_SEQ,
634 STAFF_ID = strSTAFF_ID,
635 DEPARTMENT_ID = strDEPARTMENT_ID,
636 FACILITY_TYPE_ID = strFACILITY_TYPE_ID,
637 ORDER_TYPE = strORDER_TYPE,
638 MED_ITEM_CODE = strMED_ITEM_CODE ,
639 USE_ITEM_CODE = strUSE_ITEM_CODE,
640 INPUT_CONTENT = strINPUT_CONTENT,
641 STAFF_NAME = strSTAFF_NAME,
642 DEPARTMENT_NAME = strDEPARTMENT_NAME,
643 VALID_DATE_BEGIN = strVALID_DATE_BEGIN,
644 VALID_DATE_END = strVALID_DATE_END,
645 FREQUENCY = strFREQUENCY ,
646 UPDATE_STAFF_ID = strUPDATE_STAFF_ID ,
647 UPDATE_STAFF_NAME = strUPDATE_STAFF_NAME,
648 UPDATE_DTIME = strsysdate
649 WHERE
650 --FREQUENCY_MNG_ID=strFREQUENCY_MNG_ID;
651 TYPE_ID = strTYPE_ID AND
652 ACCUMULATE_MONTH = strACCUMULATE_MONTH AND
653 SEQ = strSEQ;
654 IF SQL % ROWCOUNT = 0 THEN
655 raise_application_error (-20012,' No rows updated ');
656 END IF; END;
657 ELSIF actionFlag ='1' THEN
658 BEGIN
659 INSERT INTO
660 TOS_D_FREQUENCY_MNG
661 (
662 --FREQUENCY_MNG_ID ,
663 TYPE_ID ,
664 ACCUMULATE_MONTH ,
665 SEQ ,
666 STAFF_ID ,
667 DEPARTMENT_ID ,
668 FACILITY_TYPE_ID ,
669 ORDER_TYPE,
670 MED_ITEM_CODE ,
671 USE_ITEM_CODE ,
672 INPUT_CONTENT ,
673 STAFF_NAME ,
674 DEPARTMENT_NAME ,
675 VALID_DATE_BEGIN ,
676 VALID_DATE_END ,
677 FREQUENCY ,
678 CREATE_STAFF_ID ,
679 CREATE_STAFF_NAME ,
680 CREATE_DTIME ,
681 UPDATE_STAFF_ID ,
682 UPDATE_STAFF_NAME ,
683 UPDATE_DTIME
684 )
685 VALUES
686 (
687 --strFREQUENCY_MNG_ID,
688 strTYPE_ID,
689 strACCUMULATE_MONTH,
690 strSEQ,
691 strSTAFF_ID,
692 strDEPARTMENT_ID,
693 strFACILITY_TYPE_ID,
694 strORDER_TYPE,
695 strMED_ITEM_CODE ,
696 strUSE_ITEM_CODE,
697 strINPUT_CONTENT,
698 strSTAFF_NAME,
699 strDEPARTMENT_NAME,
700 strVALID_DATE_BEGIN,
701 strVALID_DATE_END,
702 strFREQUENCY ,
703 strCREATE_STAFF_ID,
704 strCREATE_STAFF_NAME,
705 strsysdate,
706 strUPDATE_STAFF_ID ,
707 strUPDATE_STAFF_NAME,
708 strsysdate
709 );
710 END;
711 ELSIF actionFlag='3' THEN
712 BEGIN
713 DELETE
714 FROM
715 TOS_D_FREQUENCY_MNG
716 WHERE
717 --FREQUENCY_MNG_ID=strFREQUENCY_MNG_ID ;
718 TYPE_ID=strTYPE_ID AND
719 ACCUMULATE_MONTH=strACCUMULATE_MONTH AND
720 SEQ=strSEQ ;
721 IF SQL % ROWCOUNT = 0 THEN
722 raise_application_error (-20011,' No rows deleted ');
723 END IF; END; -- To raise exception when the primary key value has been -- modified before delete.
724 ELSIF actionFlag = '0' THEN
725 raise_application_error(-20013,'Invalid argument') ;
726 END IF; -- The variable strErrMess stores the error information in the form of
727 -- <rowIndex>*<ErrorNumber>*<ErrorMessage>
728 -- When the row has been registered successfully
729 -- <ErrorMessage> is set as null and <ErrorNumber> is set as 0.
730 -- The character '^' is used to separate the strErrMess of each rowIndex
731
732
733 --//[][Mantis2555] CF Start 20111124
734 stroldyear :=to_char(sysdate,'yy')-1 ||to_char(sysdate,'mm');
735 DELETE FROM
736 TOS_D_FREQUENCY_MNG
737 WHERE
738 TYPE_ID='1' AND
739 ACCUMULATE_MONTH < stroldyear AND
740 MED_ITEM_CODE =strMED_ITEM_CODE AND
741 FACILITY_TYPE_ID=strFACILITY_TYPE_ID AND
742 (STAFF_ID =strSTAFF_ID OR DEPARTMENT_ID= strDEPARTMENT_ID);
743 --//[][Mantis2555] CF End 20111124
744
745 EXCEPTION
746 WHEN OTHERS THEN
747 -- To get the error message
748 strErrMess := SQLERRM;
749 -- To get the row index, Sqlcode and error message separated
750 -- by the character *.
751 strErrMess := i - 1 || '*' || SQLCODE || '*' || strErrMess || '^';
752 -- ErroInfo contains index/Sqlcode/errorMessage combination of all the rows
753 -- which threw exception. The character '^' is used for separating
754 -- index/Sqlcode/errorMessage of all rows.
755 DBMS_LOB.WRITEAPPEND(ErrorInfo,length(strErrMess), strErrMess);
756 IF RegistrationTYPE = 'PART' THEN
757 EXIT;
758 END IF;
759 END;
760 END LOOP;
761 END SET_FREQUENCYMNGDATA;
762 ------------------------------------PROCEDURE----------------------------------------------------------------------------
763 -- Name : DEL_MEDUSAGEMNGDATASC
764 -- Description : To DELETE the MEDUSAGEFREQUENCYLISTMNGDATASC data details
765 -- Author : ChenFei
766 -- Created on : 8/8/2011
767 --
768 -- Version Modified by Date Description Verified by
769 -------------------------------------------------------------------------------------------------------------------------
770 -------------------------------------------------------------------------------------------------------------------------
771 PROCEDURE DEL_FREQUENCYMNGDATA(
772 --V_FREQUENCY_MNG_ID IN AssocArrayVarchar2_t,
773 V_TYPE_ID IN AssocArrayVarchar2_t,
774 V_ACCUMULATE_MONTH IN AssocArrayVarchar2_t,
775 V_SEQ IN AssocArrayVarchar2_t,
776 RegistrationType IN VARCHAR2,
777 ErrorInfo OUT CLOB
778 )
779 IS
780 strErrM VARCHAR2(200) :='';
781 strErrMess VARCHAR2(1000) :='';
782 errors NUMBER(12,0) :=0;BEGIN
783 DBMS_LOB.CREATETEMPORARY(ErrorInfo, TRUE);
784 IF RegistrationTYPE = 'PART' THEN
785 --FORALL i IN 1..V_FREQUENCY_MNG_ID.COUNT
786 FORALL i IN 1..V_TYPE_ID.COUNT
787 DELETE
788 FROM
789 TOS_D_FREQUENCY_MNG
790 WHERE
791 --FREQUENCY_MNG_ID = V_FREQUENCY_MNG_ID(i)
792 TYPE_ID = V_TYPE_ID(i) AND
793 ACCUMULATE_MONTH = V_ACCUMULATE_MONTH(i) AND
794 SEQ = V_SEQ(i)
795 AND
796 1 = 1/(SELECT COUNT(*) FROM DUAL
797 WHERE EXISTS
798 (
799 SELECT * FROM TOS_D_FREQUENCY_MNG
800 WHERE
801 --FREQUENCY_MNG_ID = V_FREQUENCY_MNG_ID(i)
802 TYPE_ID = V_TYPE_ID(i) AND
803 ACCUMULATE_MONTH = V_ACCUMULATE_MONTH(i) AND
804 SEQ = V_SEQ(i)
805 )
806 );
807 ELSE
808 --FORALL i IN 1..V_FREQUENCY_MNG_ID.COUNT SAVE EXCEPTIONS
809 FORALL i IN 1..V_TYPE_ID.COUNT SAVE EXCEPTIONS
810 DELETE
811 FROM
812 TOS_D_FREQUENCY_MNG
813 WHERE
814 --FREQUENCY_MNG_ID = V_FREQUENCY_MNG_ID(i)
815 TYPE_ID = V_TYPE_ID(i) AND
816 ACCUMULATE_MONTH = V_ACCUMULATE_MONTH(i) AND
817 SEQ = V_SEQ(i)
818 AND
819 1 = 1/(SELECT COUNT(*) FROM DUAL
820 WHERE EXISTS
821 (
822 SELECT * FROM TOS_D_FREQUENCY_MNG
823 WHERE
824 --FREQUENCY_MNG_ID = V_FREQUENCY_MNG_ID(i)
825 TYPE_ID = V_TYPE_ID(i) AND
826 ACCUMULATE_MONTH = V_ACCUMULATE_MONTH(i) AND
827 SEQ = V_SEQ(i)
828 )
829 );
830 END IF;
831 EXCEPTION
832 WHEN ZERO_DIVIDE THEN
833 IF ErrorInfo is NULL THEN
834 DBMS_LOB.CREATETEMPORARY(ErrorInfo,TRUE);
835 END IF; errors := SQL%BULK_EXCEPTIONS.COUNT;
836 FOR i IN 1..errors LOOP
837 strErrM := ' No rows deleted ';
838 -- To get the row index, Sqlcode and error message separated
839 -- BY the character *.
840 strErrMess := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX -1 || '*' || '-20011'|| '*' || ' No rows deleted ' || '^';
841 DBMS_LOB.WRITEAPPEND(ErrorInfo,length(strErrMess), strErrMess);
842 END LOOP; WHEN OTHERS THEN
843 IF ErrorInfo is NULL THEN
844 DBMS_LOB.CREATETEMPORARY(ErrorInfo,TRUE);
845 END IF; errors := SQL%BULK_EXCEPTIONS.COUNT;
846 FOR i IN 1..errors LOOP
847 strErrM := SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
848 -- To get the row index, Sqlcode and error message separated
849 -- BY the character *.
850 strErrMess := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX -1 || '*' || SQLCODE || '*' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE) || '^';
851 DBMS_LOB.WRITEAPPEND(ErrorInfo,length(strErrMess), strErrMess);
852 END LOOP;
853 END DEL_FREQUENCYMNGDATA;
854 END TOS_SP_FREQUENCYMNGDATA;
855 /
浙公网安备 33010602011771号