[Home] [Help]
PACKAGE BODY: APPS.IEX_DISPUTES_PKG
Source
1 PACKAGE BODY IEX_DISPUTES_PKG AS
2 /* $Header: iextdisb.pls 120.0 2004/01/24 03:21:51 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_DISPUTE_ID NUMBER
9 ,p_LAST_UPDATE_DATE DATE
10 ,p_LAST_UPDATED_BY NUMBER
11 ,p_CREATION_DATE DATE
12 ,p_CREATED_BY NUMBER
13 ,p_LAST_UPDATE_LOGIN NUMBER
14 ,p_REQUEST_ID NUMBER
15 ,p_PROGRAM_APPLICATION_ID NUMBER
16 ,p_PROGRAM_ID NUMBER
17 ,p_PROGRAM_UPDATE_DATE DATE
18 ,p_CM_REQUEST_ID NUMBER
19 ,p_DISPUTE_SECTION VARCHAR2
20 ,p_CAMPAIGN_SCHED_ID NUMBER
21 ,p_ATTRIBUTE_CATEGORY VARCHAR2
22 ,p_ATTRIBUTE1 VARCHAR2
23 ,p_ATTRIBUTE2 VARCHAR2
24 ,p_ATTRIBUTE3 VARCHAR2
25 ,p_ATTRIBUTE4 VARCHAR2
26 ,p_ATTRIBUTE5 VARCHAR2
27 ,p_ATTRIBUTE6 VARCHAR2
28 ,p_ATTRIBUTE7 VARCHAR2
29 ,p_ATTRIBUTE8 VARCHAR2
30 ,p_ATTRIBUTE9 VARCHAR2
31 ,p_ATTRIBUTE10 VARCHAR2
32 ,p_ATTRIBUTE11 VARCHAR2
33 ,p_ATTRIBUTE12 VARCHAR2
34 ,p_ATTRIBUTE13 VARCHAR2
35 ,p_ATTRIBUTE14 VARCHAR2
36 ,p_ATTRIBUTE15 VARCHAR2
37 ,p_DELINQUENCY_ID NUMBER
38 ) IS
39 CURSOR C IS SELECT ROWID FROM IEX_DISPUTES
40 WHERE DISPUTE_ID = p_DISPUTE_ID;
41
42 BEGIN
43 INSERT INTO IEX_DISPUTES
44 (
45 DISPUTE_ID
46 ,LAST_UPDATE_DATE
47 ,LAST_UPDATED_BY
48 ,CREATION_DATE
49 ,CREATED_BY
50 ,LAST_UPDATE_LOGIN
51 ,REQUEST_ID
52 ,PROGRAM_APPLICATION_ID
53 ,PROGRAM_ID
54 ,PROGRAM_UPDATE_DATE
55 ,CM_REQUEST_ID
56 ,DISPUTE_SECTION
57 ,CAMPAIGN_SCHED_ID
58 ,ATTRIBUTE_CATEGORY
59 ,ATTRIBUTE1
60 ,ATTRIBUTE2
61 ,ATTRIBUTE3
62 ,ATTRIBUTE4
63 ,ATTRIBUTE5
64 ,ATTRIBUTE6
65 ,ATTRIBUTE7
66 ,ATTRIBUTE8
67 ,ATTRIBUTE9
68 ,ATTRIBUTE10
69 ,ATTRIBUTE11
70 ,ATTRIBUTE12
71 ,ATTRIBUTE13
72 ,ATTRIBUTE14
73 ,ATTRIBUTE15
74 ,DELINQUENCY_ID
75 ) VALUES (
76 p_DISPUTE_ID
77 ,p_LAST_UPDATE_DATE
78 ,p_LAST_UPDATED_BY
79 ,p_CREATION_DATE
80 ,p_CREATED_BY
81 ,p_LAST_UPDATE_LOGIN
82 ,p_REQUEST_ID
83 ,p_PROGRAM_APPLICATION_ID
84 ,p_PROGRAM_ID
85 ,p_PROGRAM_UPDATE_DATE
86 ,p_CM_REQUEST_ID
87 ,p_DISPUTE_SECTION
88 ,p_CAMPAIGN_SCHED_ID
89 ,p_ATTRIBUTE_CATEGORY
90 ,p_ATTRIBUTE1
91 ,p_ATTRIBUTE2
92 ,p_ATTRIBUTE3
93 ,p_ATTRIBUTE4
94 ,p_ATTRIBUTE5
95 ,p_ATTRIBUTE6
96 ,p_ATTRIBUTE7
97 ,p_ATTRIBUTE8
98 ,p_ATTRIBUTE9
99 ,p_ATTRIBUTE10
100 ,p_ATTRIBUTE11
101 ,p_ATTRIBUTE12
102 ,p_ATTRIBUTE13
103 ,p_ATTRIBUTE14
104 ,p_ATTRIBUTE15
105 ,p_DELINQUENCY_ID
106 );
107
108 OPEN C;
109 FETCH C INTO x_rowid;
110 IF (C%NOTFOUND) THEN
111 CLOSE C;
112 RAISE NO_DATA_FOUND;
113 END IF;
114 CLOSE C;
115 END Insert_Row;
116
117 /* Update_Row procedure */
118 PROCEDURE Update_Row(x_rowid VARCHAR2
119 ,p_DISPUTE_ID NUMBER
120 ,p_LAST_UPDATE_DATE DATE
121 ,p_LAST_UPDATED_BY NUMBER
122 ,p_CREATION_DATE DATE
123 ,p_CREATED_BY NUMBER
124 ,p_LAST_UPDATE_LOGIN NUMBER
125 ,p_REQUEST_ID NUMBER
126 ,p_PROGRAM_APPLICATION_ID NUMBER
127 ,p_PROGRAM_ID NUMBER
128 ,p_PROGRAM_UPDATE_DATE DATE
129 ,p_CM_REQUEST_ID NUMBER
130 ,p_DISPUTE_SECTION VARCHAR2
131 ,p_CAMPAIGN_SCHED_ID NUMBER
132 ,p_ATTRIBUTE_CATEGORY VARCHAR2
133 ,p_ATTRIBUTE1 VARCHAR2
134 ,p_ATTRIBUTE2 VARCHAR2
135 ,p_ATTRIBUTE3 VARCHAR2
136 ,p_ATTRIBUTE4 VARCHAR2
137 ,p_ATTRIBUTE5 VARCHAR2
138 ,p_ATTRIBUTE6 VARCHAR2
139 ,p_ATTRIBUTE7 VARCHAR2
140 ,p_ATTRIBUTE8 VARCHAR2
141 ,p_ATTRIBUTE9 VARCHAR2
142 ,p_ATTRIBUTE10 VARCHAR2
143 ,p_ATTRIBUTE11 VARCHAR2
144 ,p_ATTRIBUTE12 VARCHAR2
145 ,p_ATTRIBUTE13 VARCHAR2
146 ,p_ATTRIBUTE14 VARCHAR2
147 ,p_ATTRIBUTE15 VARCHAR2
148 ,p_DELINQUENCY_ID NUMBER
149 ) IS
150 BEGIN
151 UPDATE IEX_DISPUTES SET
152 DISPUTE_ID = p_DISPUTE_ID
153 ,LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
154 ,LAST_UPDATED_BY = p_LAST_UPDATED_BY
155 ,CREATION_DATE = p_CREATION_DATE
156 ,CREATED_BY = p_CREATED_BY
157 ,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
158 ,REQUEST_ID = p_REQUEST_ID
159 ,PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID
160 ,PROGRAM_ID = p_PROGRAM_ID
161 ,PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE
162 ,CM_REQUEST_ID = p_CM_REQUEST_ID
163 ,DISPUTE_SECTION = p_DISPUTE_SECTION
164 ,CAMPAIGN_SCHED_ID = p_CAMPAIGN_SCHED_ID
165 ,ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY
166 ,ATTRIBUTE1 = p_ATTRIBUTE1
167 ,ATTRIBUTE2 = p_ATTRIBUTE2
168 ,ATTRIBUTE3 = p_ATTRIBUTE3
169 ,ATTRIBUTE4 = p_ATTRIBUTE4
170 ,ATTRIBUTE5 = p_ATTRIBUTE5
171 ,ATTRIBUTE6 = p_ATTRIBUTE6
172 ,ATTRIBUTE7 = p_ATTRIBUTE7
173 ,ATTRIBUTE8 = p_ATTRIBUTE8
174 ,ATTRIBUTE9 = p_ATTRIBUTE9
175 ,ATTRIBUTE10 = p_ATTRIBUTE10
176 ,ATTRIBUTE11 = p_ATTRIBUTE11
177 ,ATTRIBUTE12 = p_ATTRIBUTE12
178 ,ATTRIBUTE13 = p_ATTRIBUTE13
179 ,ATTRIBUTE14 = p_ATTRIBUTE14
180 ,ATTRIBUTE15 = p_ATTRIBUTE15
181 ,DELINQUENCY_ID = p_DELINQUENCY_ID
182 WHERE rowid = x_rowid;
183
184 if (sql%notfound) then
185 raise no_data_found;
186 end if;
187 END Update_Row;
188
189 /* Delete_Row procedure */
190 PROCEDURE Delete_Row(x_rowid VARCHAR2) IS
191 BEGIN
192 DELETE FROM IEX_DISPUTES
193 WHERE rowid = x_rowid;
194
195 if (sql%notfound) then
196 raise no_data_found;
197 end if;
198 END Delete_Row;
199
200 /* Lock_Row procedure */
201 PROCEDURE Lock_Row(x_rowid VARCHAR2
202 ,p_DISPUTE_ID NUMBER
203 ,p_LAST_UPDATE_DATE DATE
204 ,p_LAST_UPDATED_BY NUMBER
205 ,p_CREATION_DATE DATE
206 ,p_CREATED_BY NUMBER
207 ,p_LAST_UPDATE_LOGIN NUMBER
208 ,p_REQUEST_ID NUMBER
209 ,p_PROGRAM_APPLICATION_ID NUMBER
210 ,p_PROGRAM_ID NUMBER
211 ,p_PROGRAM_UPDATE_DATE DATE
212 ,p_CM_REQUEST_ID NUMBER
213 ,p_DISPUTE_SECTION VARCHAR2
214 ,p_CAMPAIGN_SCHED_ID NUMBER
215 ,p_ATTRIBUTE_CATEGORY VARCHAR2
216 ,p_ATTRIBUTE1 VARCHAR2
217 ,p_ATTRIBUTE2 VARCHAR2
218 ,p_ATTRIBUTE3 VARCHAR2
219 ,p_ATTRIBUTE4 VARCHAR2
220 ,p_ATTRIBUTE5 VARCHAR2
221 ,p_ATTRIBUTE6 VARCHAR2
222 ,p_ATTRIBUTE7 VARCHAR2
223 ,p_ATTRIBUTE8 VARCHAR2
224 ,p_ATTRIBUTE9 VARCHAR2
225 ,p_ATTRIBUTE10 VARCHAR2
226 ,p_ATTRIBUTE11 VARCHAR2
227 ,p_ATTRIBUTE12 VARCHAR2
228 ,p_ATTRIBUTE13 VARCHAR2
229 ,p_ATTRIBUTE14 VARCHAR2
230 ,p_ATTRIBUTE15 VARCHAR2
231 ,p_DELINQUENCY_ID NUMBER
232 ) IS
233 CURSOR C IS SELECT * FROM IEX_DISPUTES
234 WHERE rowid = x_rowid
235 FOR UPDATE of DISPUTE_ID NOWAIT;
236 Recinfo C%ROWTYPE;
237 BEGIN
238 OPEN C;
239 FETCH C INTO Recinfo;
240 IF (C%NOTFOUND) THEN
241 CLOSE C;
242 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
243 APP_EXCEPTION.Raise_Exception;
244 END IF;
245 CLOSE C;
246
247 IF (
248 (Recinfo.DISPUTE_ID = p_DISPUTE_ID)
249 AND ( (Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
250 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
251 AND (p_LAST_UPDATE_DATE IS NULL)))
252 AND ( (Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
253 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
254 AND (p_LAST_UPDATED_BY IS NULL)))
255 AND ( (Recinfo.CREATION_DATE = p_CREATION_DATE)
256 OR ( (Recinfo.CREATION_DATE IS NULL)
257 AND (p_CREATION_DATE IS NULL)))
258 AND ( (Recinfo.CREATED_BY = p_CREATED_BY)
259 OR ( (Recinfo.CREATED_BY IS NULL)
260 AND (p_CREATED_BY IS NULL)))
261 AND ( (Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
262 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
263 AND (p_LAST_UPDATE_LOGIN IS NULL)))
264 AND ( (Recinfo.REQUEST_ID = p_REQUEST_ID)
265 OR ( (Recinfo.REQUEST_ID IS NULL)
266 AND (p_REQUEST_ID IS NULL)))
267 AND ( (Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
268 OR ( (Recinfo.PROGRAM_APPLICATION_ID IS NULL)
269 AND (p_PROGRAM_APPLICATION_ID IS NULL)))
270 AND ( (Recinfo.PROGRAM_ID = p_PROGRAM_ID)
271 OR ( (Recinfo.PROGRAM_ID IS NULL)
272 AND (p_PROGRAM_ID IS NULL)))
273 AND ( (Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
274 OR ( (Recinfo.PROGRAM_UPDATE_DATE IS NULL)
275 AND (p_PROGRAM_UPDATE_DATE IS NULL)))
276 AND ( (Recinfo.CM_REQUEST_ID = p_CM_REQUEST_ID)
277 OR ( (Recinfo.CM_REQUEST_ID IS NULL)
278 AND (p_CM_REQUEST_ID IS NULL)))
279 AND ( (Recinfo.DISPUTE_SECTION = p_DISPUTE_SECTION)
280 OR ( (Recinfo.DISPUTE_SECTION IS NULL)
281 AND (p_DISPUTE_SECTION IS NULL)))
282 AND ( (Recinfo.CAMPAIGN_SCHED_ID = p_CAMPAIGN_SCHED_ID)
283 OR ( (Recinfo.CAMPAIGN_SCHED_ID IS NULL)
284 AND (p_CAMPAIGN_SCHED_ID IS NULL)))
285 AND ( (Recinfo.ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY)
286 OR ( (Recinfo.ATTRIBUTE_CATEGORY IS NULL)
287 AND (p_ATTRIBUTE_CATEGORY IS NULL)))
288 AND ( (Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
289 OR ( (Recinfo.ATTRIBUTE1 IS NULL)
290 AND (p_ATTRIBUTE1 IS NULL)))
291 AND ( (Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
292 OR ( (Recinfo.ATTRIBUTE2 IS NULL)
293 AND (p_ATTRIBUTE2 IS NULL)))
294 AND ( (Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
295 OR ( (Recinfo.ATTRIBUTE3 IS NULL)
296 AND (p_ATTRIBUTE3 IS NULL)))
297 AND ( (Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
298 OR ( (Recinfo.ATTRIBUTE4 IS NULL)
299 AND (p_ATTRIBUTE4 IS NULL)))
300 AND ( (Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
301 OR ( (Recinfo.ATTRIBUTE5 IS NULL)
302 AND (p_ATTRIBUTE5 IS NULL)))
303 AND ( (Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
304 OR ( (Recinfo.ATTRIBUTE6 IS NULL)
305 AND (p_ATTRIBUTE6 IS NULL)))
306 AND ( (Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
307 OR ( (Recinfo.ATTRIBUTE7 IS NULL)
308 AND (p_ATTRIBUTE7 IS NULL)))
309 AND ( (Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
310 OR ( (Recinfo.ATTRIBUTE8 IS NULL)
311 AND (p_ATTRIBUTE8 IS NULL)))
312 AND ( (Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
313 OR ( (Recinfo.ATTRIBUTE9 IS NULL)
314 AND (p_ATTRIBUTE9 IS NULL)))
315 AND ( (Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
316 OR ( (Recinfo.ATTRIBUTE10 IS NULL)
317 AND (p_ATTRIBUTE10 IS NULL)))
318 AND ( (Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
319 OR ( (Recinfo.ATTRIBUTE11 IS NULL)
320 AND (p_ATTRIBUTE11 IS NULL)))
321 AND ( (Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
322 OR ( (Recinfo.ATTRIBUTE12 IS NULL)
323 AND (p_ATTRIBUTE12 IS NULL)))
324 AND ( (Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
325 OR ( (Recinfo.ATTRIBUTE13 IS NULL)
326 AND (p_ATTRIBUTE13 IS NULL)))
327 AND ( (Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
328 OR ( (Recinfo.ATTRIBUTE14 IS NULL)
329 AND (p_ATTRIBUTE14 IS NULL)))
330 AND ( (Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
331 OR ( (Recinfo.ATTRIBUTE15 IS NULL)
332 AND (p_ATTRIBUTE15 IS NULL)))
333 AND ( (Recinfo.DELINQUENCY_ID = p_DELINQUENCY_ID)
334 OR ( (Recinfo.DELINQUENCY_ID IS NULL)
335 AND (p_DELINQUENCY_ID IS NULL)))
336 ) THEN
337 return;
338 ELSE
339 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
340 APP_EXCEPTION.Raise_Exception;
341 END IF;
342 END Lock_Row;
343 END;
344