1 PACKAGE BODY IEX_SCORE_COMP_DET_PKG AS
2 /* $Header: iextscdb.pls 120.2 2004/10/28 16:06:46 clchang ship $ */
3
4 /* Insert_Row procedure */
5 PG_DEBUG NUMBER(2) ;
6
7 /* clchang updated 09/20/2004
8 * in 11i.IEX.H, the data type of VALUE updated to VARCHAR2(2000);
9 ******************************************************************/
10
11 PROCEDURE Insert_Row(x_rowid IN OUT NOCOPY VARCHAR2
12 ,p_SCORE_COMP_DET_ID NUMBER
13 ,p_OBJECT_VERSION_NUMBER NUMBER
14 ,p_PROGRAM_ID NUMBER
15 ,p_LAST_UPDATE_DATE DATE
16 ,p_LAST_UPDATED_BY NUMBER
17 ,p_LAST_UPDATE_LOGIN NUMBER
18 ,p_CREATION_DATE DATE
19 ,p_CREATED_BY NUMBER
20 ,p_VALUE NUMBER
21 ,p_NEW_VALUE VARCHAR2
22 ,p_RANGE_LOW NUMBER
23 ,p_RANGE_HIGH NUMBER
24 ,p_SCORE_COMPONENT_ID NUMBER
25 ) IS
26 CURSOR C IS SELECT ROWID FROM IEX_SCORE_COMP_DET
27 WHERE SCORE_COMP_DET_ID = p_SCORE_COMP_DET_ID;
28
29 BEGIN
30 INSERT INTO IEX_SCORE_COMP_DET
31 (
32 SCORE_COMP_DET_ID
33 ,OBJECT_VERSION_NUMBER
34 ,PROGRAM_ID
35 ,LAST_UPDATE_DATE
36 ,LAST_UPDATED_BY
37 ,LAST_UPDATE_LOGIN
38 ,CREATION_DATE
39 ,CREATED_BY
40 ,VALUE
41 ,NEW_VALUE
42 ,RANGE_LOW
43 ,RANGE_HIGH
44 ,SCORE_COMPONENT_ID
45 ) VALUES (
46 p_SCORE_COMP_DET_ID
47 ,p_OBJECT_VERSION_NUMBER
48 ,p_PROGRAM_ID
49 ,p_LAST_UPDATE_DATE
50 ,p_LAST_UPDATED_BY
51 ,p_LAST_UPDATE_LOGIN
52 ,p_CREATION_DATE
53 ,p_CREATED_BY
54 ,p_VALUE
55 ,p_NEW_VALUE
56 ,p_RANGE_LOW
57 ,p_RANGE_HIGH
58 ,p_SCORE_COMPONENT_ID
59 );
60
61 OPEN C;
62 FETCH C INTO x_rowid;
63 IF (C%NOTFOUND) THEN
64 CLOSE C;
65 RAISE NO_DATA_FOUND;
66 END IF;
67 CLOSE C;
68 END Insert_Row;
69
70 /* Update_Row procedure */
71 PROCEDURE Update_Row(x_rowid VARCHAR2
72 ,p_SCORE_COMP_DET_ID NUMBER
73 ,p_OBJECT_VERSION_NUMBER NUMBER
74 ,p_PROGRAM_ID NUMBER
75 ,p_LAST_UPDATE_DATE DATE
76 ,p_LAST_UPDATED_BY NUMBER
77 ,p_LAST_UPDATE_LOGIN NUMBER
78 ,p_CREATION_DATE DATE
79 ,p_CREATED_BY NUMBER
80 ,p_VALUE NUMBER
81 ,p_NEW_VALUE VARCHAR2
82 ,p_RANGE_LOW NUMBER
83 ,p_RANGE_HIGH NUMBER
84 ,p_SCORE_COMPONENT_ID NUMBER
85 ) IS
86 BEGIN
87 UPDATE IEX_SCORE_COMP_DET SET
88 SCORE_COMP_DET_ID = p_SCORE_COMP_DET_ID
89 ,OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER
90 ,PROGRAM_ID = p_PROGRAM_ID
91 ,LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
92 ,LAST_UPDATED_BY = p_LAST_UPDATED_BY
93 ,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
94 ,CREATION_DATE = p_CREATION_DATE
95 ,CREATED_BY = p_CREATED_BY
96 ,VALUE = p_VALUE
97 ,NEW_VALUE = p_NEW_VALUE
98 ,RANGE_LOW = p_RANGE_LOW
99 ,RANGE_HIGH = p_RANGE_HIGH
100 ,SCORE_COMPONENT_ID = p_SCORE_COMPONENT_ID
101 WHERE rowid = x_rowid;
102
103 if (sql%notfound) then
104 raise no_data_found;
105 end if;
106 END Update_Row;
107
108 /* Delete_Row procedure */
109 PROCEDURE Delete_Row(x_rowid VARCHAR2) IS
110 BEGIN
111 DELETE FROM IEX_SCORE_COMP_DET
112 WHERE rowid = x_rowid;
113
114 if (sql%notfound) then
115 raise no_data_found;
116 end if;
117 END Delete_Row;
118
119 /* Lock_Row procedure */
120 PROCEDURE Lock_Row(x_rowid VARCHAR2
121 ,p_SCORE_COMP_DET_ID NUMBER
122 ,p_OBJECT_VERSION_NUMBER NUMBER
123 ,p_PROGRAM_ID NUMBER
124 ,p_LAST_UPDATE_DATE DATE
125 ,p_LAST_UPDATED_BY NUMBER
126 ,p_LAST_UPDATE_LOGIN NUMBER
127 ,p_CREATION_DATE DATE
128 ,p_CREATED_BY NUMBER
129 ,p_VALUE NUMBER
130 ,p_NEW_VALUE VARCHAR2
131 ,p_RANGE_LOW NUMBER
132 ,p_RANGE_HIGH NUMBER
133 ,p_SCORE_COMPONENT_ID NUMBER
134 ) IS
135 CURSOR C IS SELECT * FROM IEX_SCORE_COMP_DET
136 WHERE rowid = x_rowid
137 FOR UPDATE of SCORE_COMP_DET_ID NOWAIT;
138 Recinfo C%ROWTYPE;
139 BEGIN
140 OPEN C;
141 FETCH C INTO Recinfo;
142 IF (C%NOTFOUND) THEN
143 CLOSE C;
144 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
145 APP_EXCEPTION.Raise_Exception;
146 END IF;
147 CLOSE C;
148
149 IF (
150 (Recinfo.SCORE_COMP_DET_ID = p_SCORE_COMP_DET_ID)
151 AND ( (Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
152 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
153 AND (p_OBJECT_VERSION_NUMBER IS NULL)))
154 AND ( (Recinfo.PROGRAM_ID = p_PROGRAM_ID)
155 OR ( (Recinfo.PROGRAM_ID IS NULL)
156 AND (p_PROGRAM_ID IS NULL)))
157 AND ( (Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
158 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
159 AND (p_LAST_UPDATE_DATE IS NULL)))
160 AND ( (Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
161 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
162 AND (p_LAST_UPDATED_BY IS NULL)))
163 AND ( (Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
164 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
165 AND (p_LAST_UPDATE_LOGIN IS NULL)))
166 AND ( (Recinfo.CREATION_DATE = p_CREATION_DATE)
167 OR ( (Recinfo.CREATION_DATE IS NULL)
168 AND (p_CREATION_DATE IS NULL)))
169 AND ( (Recinfo.CREATED_BY = p_CREATED_BY)
170 OR ( (Recinfo.CREATED_BY IS NULL)
171 AND (p_CREATED_BY IS NULL)))
172 AND ( (Recinfo.VALUE = p_VALUE)
173 OR ( (Recinfo.VALUE IS NULL)
174 AND (p_VALUE IS NULL)))
175 AND ( (Recinfo.NEW_VALUE = p_NEW_VALUE)
176 OR ( (Recinfo.NEW_VALUE IS NULL)
177 AND (p_NEW_VALUE IS NULL)))
178 AND ( (Recinfo.RANGE_LOW = p_RANGE_LOW)
179 OR ( (Recinfo.RANGE_LOW IS NULL)
180 AND (p_RANGE_LOW IS NULL)))
181 AND ( (Recinfo.RANGE_HIGH = p_RANGE_HIGH)
182 OR ( (Recinfo.RANGE_HIGH IS NULL)
183 AND (p_RANGE_HIGH IS NULL)))
184 AND ( (Recinfo.SCORE_COMPONENT_ID = p_SCORE_COMPONENT_ID)
185 OR ( (Recinfo.SCORE_COMPONENT_ID IS NULL)
186 AND (p_SCORE_COMPONENT_ID IS NULL)))
187 ) THEN
188 return;
189 ELSE
190 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
191 APP_EXCEPTION.Raise_Exception;
192 END IF;
193 END Lock_Row;
194
195 BEGIN
196 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
197
198 END;
199
[Home] [Help]