1 PACKAGE BODY IEX_SCORE_HISTORIES_PKG AS
2 /* $Header: iextschb.pls 120.0 2004/01/24 03:22:40 appldev noship $ */
3
4 /* Insert_Row procedure */
5 PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
6
7 PROCEDURE Insert_Row(x_rowid IN OUT NOCOPY VARCHAR2
8 ,p_SCORE_HISTORY_ID NUMBER
9 ,p_OBJECT_VERSION_NUMBER NUMBER
10 ,p_PROGRAM_ID NUMBER DEFAULT NULL
11 ,p_LAST_UPDATE_DATE DATE
12 ,p_LAST_UPDATED_BY NUMBER
13 ,p_LAST_UPDATE_LOGIN NUMBER DEFAULT NULL
14 ,p_CREATION_DATE DATE
15 ,p_CREATED_BY NUMBER
16 ,p_SCORE_VALUE NUMBER DEFAULT NULL
17 ,p_SCORE_ID NUMBER DEFAULT NULL
18 ,p_PARTY_ID NUMBER DEFAULT NULL
19 ,p_ICO_ID NUMBER DEFAULT NULL
20 ,p_DEL_DELINQUENCY_ID NUMBER DEFAULT NULL
21 ,p_SECURITY_GROUP_ID NUMBER DEFAULT NULL
22 ,p_SCORE_OBJECT_ID NUMBER DEFAULT NULL
23 ,p_SCORE_OBJECT_CODE VARCHAR2 DEFAULT NULL
24 ,p_REQUEST_ID NUMBER DEFAULT NULL
25 ,p_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
26 ,p_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
27 ) IS
28 CURSOR C IS SELECT ROWID FROM IEX_SCORE_HISTORIES
29 WHERE SCORE_HISTORY_ID = p_SCORE_HISTORY_ID;
30
31 BEGIN
32 INSERT INTO IEX_SCORE_HISTORIES
33 (
34 SCORE_HISTORY_ID
35 ,OBJECT_VERSION_NUMBER
36 ,PROGRAM_ID
37 ,LAST_UPDATE_DATE
38 ,LAST_UPDATED_BY
39 ,LAST_UPDATE_LOGIN
40 ,CREATION_DATE
41 ,CREATED_BY
42 ,SCORE_VALUE
43 ,SCORE_ID
44 ,PARTY_ID
45 ,ICO_ID
46 ,DEL_DELINQUENCY_ID
47 ,SECURITY_GROUP_ID
48 ,SCORE_OBJECT_ID
49 ,SCORE_OBJECT_CODE
50 ,REQUEST_ID
51 ,PROGRAM_APPLICATION_ID
52 ,PROGRAM_UPDATE_DATE
53 ) VALUES (
54 p_SCORE_HISTORY_ID
55 ,p_OBJECT_VERSION_NUMBER
56 ,p_PROGRAM_ID
57 ,p_LAST_UPDATE_DATE
58 ,p_LAST_UPDATED_BY
59 ,p_LAST_UPDATE_LOGIN
60 ,p_CREATION_DATE
61 ,p_CREATED_BY
62 ,p_SCORE_VALUE
63 ,p_SCORE_ID
64 ,p_PARTY_ID
65 ,p_ICO_ID
66 ,p_DEL_DELINQUENCY_ID
67 ,p_SECURITY_GROUP_ID
68 ,p_SCORE_OBJECT_ID
69 ,p_SCORE_OBJECT_CODE
70 ,p_REQUEST_ID
71 ,p_PROGRAM_APPLICATION_ID
72 ,p_PROGRAM_UPDATE_DATE
73 );
74
75 OPEN C;
76 FETCH C INTO x_rowid;
77 IF (C%NOTFOUND) THEN
78 CLOSE C;
79 RAISE NO_DATA_FOUND;
80 END IF;
81 CLOSE C;
82 END Insert_Row;
83
84 /* Update_Row procedure */
85 PROCEDURE Update_Row(x_rowid VARCHAR2
86 ,p_SCORE_HISTORY_ID NUMBER
87 ,p_OBJECT_VERSION_NUMBER NUMBER
88 ,p_PROGRAM_ID NUMBER DEFAULT NULL
89 ,p_LAST_UPDATE_DATE DATE
90 ,p_LAST_UPDATED_BY NUMBER
91 ,p_LAST_UPDATE_LOGIN NUMBER DEFAULT NULL
92 ,p_CREATION_DATE DATE
93 ,p_CREATED_BY NUMBER
94 ,p_SCORE_VALUE NUMBER DEFAULT NULL
95 ,p_SCORE_ID NUMBER DEFAULT NULL
96 ,p_PARTY_ID NUMBER DEFAULT NULL
97 ,p_ICO_ID NUMBER DEFAULT NULL
98 ,p_DEL_DELINQUENCY_ID NUMBER DEFAULT NULL
99 ,p_SECURITY_GROUP_ID NUMBER DEFAULT NULL
100 ,p_SCORE_OBJECT_ID NUMBER DEFAULT NULL
101 ,p_SCORE_OBJECT_CODE VARCHAR2 DEFAULT NULL
102 ,p_REQUEST_ID NUMBER DEFAULT NULL
103 ,p_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
104 ,p_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
105 ) IS
106 BEGIN
107 UPDATE IEX_SCORE_HISTORIES SET
108 SCORE_HISTORY_ID = p_SCORE_HISTORY_ID
109 ,OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER
110 ,PROGRAM_ID = p_PROGRAM_ID
111 ,LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
112 ,LAST_UPDATED_BY = p_LAST_UPDATED_BY
113 ,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
114 ,CREATION_DATE = p_CREATION_DATE
115 ,CREATED_BY = p_CREATED_BY
116 ,SCORE_VALUE = p_SCORE_VALUE
117 ,SCORE_ID = p_SCORE_ID
118 ,PARTY_ID = p_PARTY_ID
119 ,ICO_ID = p_ICO_ID
120 ,DEL_DELINQUENCY_ID = p_DEL_DELINQUENCY_ID
121 ,SECURITY_GROUP_ID = p_SECURITY_GROUP_ID
122 ,SCORE_OBJECT_ID = p_SCORE_OBJECT_ID
123 ,SCORE_OBJECT_CODE = p_SCORE_OBJECT_CODE
124 ,REQUEST_ID = p_REQUEST_ID
125 ,PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID
126 ,PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE
127 WHERE rowid = x_rowid;
128
129 if (sql%notfound) then
130 raise no_data_found;
131 end if;
132 END Update_Row;
133
134 /* Delete_Row procedure */
135 PROCEDURE Delete_Row(x_rowid VARCHAR2) IS
136 BEGIN
137 DELETE FROM IEX_SCORE_HISTORIES
138 WHERE rowid = x_rowid;
139
140 if (sql%notfound) then
141 raise no_data_found;
142 end if;
143 END Delete_Row;
144
145 /* Lock_Row procedure */
146 PROCEDURE Lock_Row(x_rowid VARCHAR2
147 ,p_SCORE_HISTORY_ID NUMBER
148 ,p_OBJECT_VERSION_NUMBER NUMBER
149 ,p_PROGRAM_ID NUMBER DEFAULT NULL
150 ,p_LAST_UPDATE_DATE DATE
151 ,p_LAST_UPDATED_BY NUMBER
152 ,p_LAST_UPDATE_LOGIN NUMBER DEFAULT NULL
153 ,p_CREATION_DATE DATE
154 ,p_CREATED_BY NUMBER
155 ,p_SCORE_VALUE NUMBER DEFAULT NULL
156 ,p_SCORE_ID NUMBER DEFAULT NULL
157 ,p_PARTY_ID NUMBER DEFAULT NULL
158 ,p_ICO_ID NUMBER DEFAULT NULL
159 ,p_DEL_DELINQUENCY_ID NUMBER DEFAULT NULL
160 ,p_SECURITY_GROUP_ID NUMBER DEFAULT NULL
161 ,p_SCORE_OBJECT_ID NUMBER DEFAULT NULL
162 ,p_SCORE_OBJECT_CODE VARCHAR2 DEFAULT NULL
163 ,p_REQUEST_ID NUMBER DEFAULT NULL
164 ,p_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
165 ,p_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
166 ) IS
167 CURSOR C IS SELECT * FROM IEX_SCORE_HISTORIES
168 WHERE rowid = x_rowid
169 FOR UPDATE of SCORE_HISTORY_ID NOWAIT;
170 Recinfo C%ROWTYPE;
171 BEGIN
172 OPEN C;
173 FETCH C INTO Recinfo;
174 IF (C%NOTFOUND) THEN
175 CLOSE C;
176 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
177 APP_EXCEPTION.Raise_Exception;
178 END IF;
179 CLOSE C;
180
181 IF (
182 (Recinfo.SCORE_HISTORY_ID = p_SCORE_HISTORY_ID)
183 AND ( (Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
184 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
185 AND (p_OBJECT_VERSION_NUMBER IS NULL)))
186 AND ( (Recinfo.PROGRAM_ID = p_PROGRAM_ID)
187 OR ( (Recinfo.PROGRAM_ID IS NULL)
188 AND (p_PROGRAM_ID IS NULL)))
189 AND ( (Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
190 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
191 AND (p_LAST_UPDATE_DATE IS NULL)))
192 AND ( (Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
193 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
194 AND (p_LAST_UPDATED_BY IS NULL)))
195 AND ( (Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
196 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
197 AND (p_LAST_UPDATE_LOGIN IS NULL)))
198 AND ( (Recinfo.CREATION_DATE = p_CREATION_DATE)
199 OR ( (Recinfo.CREATION_DATE IS NULL)
200 AND (p_CREATION_DATE IS NULL)))
201 AND ( (Recinfo.CREATED_BY = p_CREATED_BY)
202 OR ( (Recinfo.CREATED_BY IS NULL)
203 AND (p_CREATED_BY IS NULL)))
204 AND ( (Recinfo.SCORE_VALUE = p_SCORE_VALUE)
205 OR ( (Recinfo.SCORE_VALUE IS NULL)
206 AND (p_SCORE_VALUE IS NULL)))
207 AND ( (Recinfo.SCORE_ID = p_SCORE_ID)
208 OR ( (Recinfo.SCORE_ID IS NULL)
209 AND (p_SCORE_ID IS NULL)))
210 AND ( (Recinfo.PARTY_ID = p_PARTY_ID)
211 OR ( (Recinfo.PARTY_ID IS NULL)
212 AND (p_PARTY_ID IS NULL)))
213 AND ( (Recinfo.ICO_ID = p_ICO_ID)
214 OR ( (Recinfo.ICO_ID IS NULL)
215 AND (p_ICO_ID IS NULL)))
216 AND ( (Recinfo.DEL_DELINQUENCY_ID = p_DEL_DELINQUENCY_ID)
217 OR ( (Recinfo.DEL_DELINQUENCY_ID IS NULL)
218 AND (p_DEL_DELINQUENCY_ID IS NULL)))
219 AND ( (Recinfo.SECURITY_GROUP_ID = p_SECURITY_GROUP_ID)
220 OR ( (Recinfo.SECURITY_GROUP_ID IS NULL)
221 AND (p_SECURITY_GROUP_ID IS NULL)))
222 AND ( (Recinfo.SCORE_OBJECT_ID = p_SCORE_OBJECT_ID)
223 OR ( (Recinfo.SCORE_OBJECT_ID IS NULL)
224 AND (p_SCORE_OBJECT_ID IS NULL)))
225 AND ( (Recinfo.SCORE_OBJECT_CODE = p_SCORE_OBJECT_CODE)
226 OR ( (Recinfo.SCORE_OBJECT_CODE IS NULL)
227 AND (p_SCORE_OBJECT_CODE IS NULL)))
228 AND ( (Recinfo.REQUEST_ID = p_REQUEST_ID)
229 OR ( (Recinfo.REQUEST_ID IS NULL)
230 AND (p_REQUEST_ID IS NULL)))
231 AND ( (Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
232 OR ( (Recinfo.PROGRAM_APPLICATION_ID IS NULL)
233 AND (p_PROGRAM_APPLICATION_ID IS NULL)))
234 AND ( (Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
235 OR ( (Recinfo.PROGRAM_UPDATE_DATE IS NULL)
236 AND (p_PROGRAM_UPDATE_DATE IS NULL)))
237 ) THEN
238 return;
239 ELSE
240 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
241 APP_EXCEPTION.Raise_Exception;
242 END IF;
243 END Lock_Row;
244 END;
245
[Home] [Help]