1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125
|
type ncDiagQuId_table_type is table of NC_DIAG_QU.NCDIAGQUID%TYPE
index by binary_integer;
type ncQuestionCode_table_type is table of NC_DIAG_QU.NCQUESTIONCODE%TYPE
index by binary_integer;
type ncShortQuestion_table_type is table of NC_DIAG_QU.NCSHORTQUESTION%TYPE
index by binary_integer;
type swDateCreated_table_type is table of VARCHAR2(10)
index by binary_integer;
type ncDiagCodeId_table_type is table of NC_DIAG_CODE.NCDIAGCODEID%TYPE
index by binary_integer;
type ncDiagCode_table_type is table of NC_DIAG_CODE.NCDIAGCODE%TYPE
index by binary_integer;
type ncMotif_table_type is table of NC_MOTIF.NCMOTIF%TYPE
index by binary_integer;
type ncSousMotif_table_type is table of NC_MOTIF.NCSOUSMOTIF%TYPE
index by binary_integer;
type ncActionType_table_type is table of NC_ACTION_TYPE.NCACTIONTYPE%TYPE
index by binary_integer;
PROCEDURE NCSP_DIAG_CODE_NON_USED
(
o_ncDiagCode OUT ncDiagCode_table_type,
o_ncMotif OUT ncMotif_table_type,
o_ncSousMotif OUT ncSousMotif_table_type,
o_ncActionType OUT ncActionType_table_type,
o_swDateCreated OUT swDateCreated_table_type,
o_ncDiagCodeId OUT ncDiagCodeId_table_type,
batch_size IN int,
out_batch_size IN OUT int,
status OUT int
) IS
i binary_integer;
j binary_integer;
n binary_integer;
f_ncDiagCode NC_DIAG_CODE.NCDIAGCODE%TYPE;
f_ncMotif NC_MOTIF.NCMOTIF%TYPE;
f_ncSousMotif NC_MOTIF.NCSOUSMOTIF%TYPE;
f_ncActionType NC_ACTION_TYPE.NCACTIONTYPE%TYPE;
f_swDateCreated VARCHAR2(10);
f_ncDiagCodeId NC_DIAG_CODE.NCDIAGCODEID%TYPE;
BEGIN
out_batch_size := 0;
status := 0;
j := 1;
IF not curDiagCode%ISOPEN THEN
open curDiagCode;
END IF;
for i in 1..batch_size
LOOP
/*fetch curDiagCode INTO o_ncDiagCode(i),
o_ncMotif(i),
o_ncSousMotif(i),
o_ncActionType(i),
o_swDateCreated(i),
o_ncDiagCodeId(i);*/
fetch curDiagCode INTO f_ncDiagCode,
f_ncMotif,
f_ncSousMotif,
f_ncActionType,
f_swDateCreated,
f_ncDiagCodeId;
IF curDiagCode%NOTFOUND THEN
close curDiagCode;
exit;
ELSE
SELECT count(q.ncDiagQuId) into n
FROM NC_DIAG_QU q
WHERE q.ncDiagCodeId1 = f_ncDiagCodeId
OR q.ncDiagCodeId2 = f_ncDiagCodeId
OR q.ncDiagCodeId3 = f_ncDiagCodeId
OR q.ncDiagCodeId4 = f_ncDiagCodeId
OR q.ncDiagCodeId5 = f_ncDiagCodeId
OR q.ncDiagCodeId6 = f_ncDiagCodeId
OR q.ncDiagCodeId7 = f_ncDiagCodeId
OR q.ncDiagCodeId8 = f_ncDiagCodeId
OR q.ncDiagCodeId9 = f_ncDiagCodeId
OR q.ncDiagCodeId10 = f_ncDiagCodeId;
if n = 0 then
o_ncDiagCode(j):= f_ncDiagCode;
o_ncMotif(j):=f_ncMotif;
o_ncSousMotif(j):=f_ncSousMotif;
o_ncActionType(j):=f_ncActionType;
o_swDateCreated(j):=f_swDateCreated;
o_ncDiagCodeId(j):=f_ncDiagCodeId;
out_batch_size := out_batch_size+1;
j:= j+1;
end if;
end if;
END LOOP;
END NCSP_DIAG_CODE_NON_USED; |
Partager