[Home] [Help]
PACKAGE BODY: APPS.CS_CP_AUDIT_PKG
Source
1 PACKAGE BODY CS_CP_AUDIT_PKG as
2 /* $Header: csxciaub.pls 115.2 2000/07/18 21:43:34 pkm ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
5
6 X_Customer_Product_Id NUMBER,
7 X_Last_Update_Date DATE,
8 X_Last_Updated_By NUMBER,
9 X_Creation_Date DATE,
10 X_Created_By NUMBER,
11 X_Last_Update_Login NUMBER,
12 X_Customer_Changed_Flag VARCHAR2,
13 X_Old_Customer_Id NUMBER,
14 X_Current_Customer_Id NUMBER,
15 X_System_Changed_Flag VARCHAR2,
16 X_Old_System_Id NUMBER,
17 X_Current_System_Id NUMBER,
18 X_Prd_Agreement_Changed_Flag VARCHAR2,
19 X_Old_Product_Agreement_Id NUMBER,
20 X_Current_Product_Agreement_Id NUMBER,
21 X_Serv_Agreement_Changed_Flag VARCHAR2,
22 X_Old_Service_Agreement_Id NUMBER,
23 X_Current_Service_Agreement_Id NUMBER,
24 X_Attribute1 VARCHAR2,
25 X_Attribute2 VARCHAR2,
26 X_Attribute3 VARCHAR2,
27 X_Attribute4 VARCHAR2,
28 X_Attribute5 VARCHAR2,
29 X_Attribute6 VARCHAR2,
30 X_Attribute7 VARCHAR2,
31 X_Attribute8 VARCHAR2,
32 X_Attribute9 VARCHAR2,
33 X_Attribute10 VARCHAR2,
34 X_Context VARCHAR2,
35 X_Attribute11 VARCHAR2,
36 X_Attribute12 VARCHAR2,
37 X_Attribute13 VARCHAR2,
38 X_Attribute14 VARCHAR2,
39 X_Attribute15 VARCHAR2,
40 X_Comments VARCHAR2,
41 X_Cp_Status_Changed_Flag VARCHAR2,
42 X_Old_Cp_Status_Id NUMBER,
43 X_Current_Cp_Status_Id NUMBER,
44 X_Type_Code_Changed_Flag VARCHAR2,
45 X_Old_Type_Code VARCHAR2,
46 X_Current_Type_Code VARCHAR2
47 ) IS
48 l_cp_audit_id NUMBER;
49 CURSOR C IS SELECT rowid FROM CS_CP_AUDIT
50 WHERE customer_product_id = X_Customer_Product_Id;
51
52 BEGIN
53 select cs_cp_audit_s.NEXTVAL
54 into l_cp_audit_id
55 from dual;
56
57 INSERT INTO CS_CP_AUDIT(
58 cp_audit_id,
59 customer_product_id,
60 last_update_date,
61 last_updated_by,
62 creation_date,
63 created_by,
64 last_update_login,
65 customer_changed_flag,
66 old_customer_id,
67 current_customer_id,
68 system_changed_flag,
69 old_system_id,
70 current_system_id,
71 product_agreement_changed_flag,
72 old_product_agreement_id,
73 current_product_agreement_id,
74 service_agreement_changed_flag,
75 old_service_agreement_id,
76 current_service_agreement_id,
77 attribute1,
78 attribute2,
79 attribute3,
80 attribute4,
81 attribute5,
82 attribute6,
83 attribute7,
84 attribute8,
85 attribute9,
86 attribute10,
87 context,
88 attribute11,
89 attribute12,
90 attribute13,
91 attribute14,
92 attribute15,
93 comments,
94 cp_status_changed_flag,
95 old_cp_status_id,
96 current_cp_status_id,
97 type_code_changed_flag,
98 old_type_code,
99 current_type_code
100 ) VALUES (
101 l_cp_audit_id,
102 X_Customer_Product_Id,
103 X_Last_Update_Date,
104 X_Last_Updated_By,
105 X_Creation_Date,
106 X_Created_By,
107 X_Last_Update_Login,
108 X_Customer_Changed_Flag,
109 X_Old_Customer_Id,
110 X_Current_Customer_Id,
111 X_System_Changed_Flag,
112 X_Old_System_Id,
113 X_Current_System_Id,
114 X_Prd_Agreement_Changed_Flag,
115 X_Old_Product_Agreement_Id,
116 X_Current_Product_Agreement_Id,
117 X_Serv_Agreement_Changed_Flag,
118 X_Old_Service_Agreement_Id,
119 X_Current_Service_Agreement_Id,
120 X_Attribute1,
121 X_Attribute2,
122 X_Attribute3,
123 X_Attribute4,
124 X_Attribute5,
125 X_Attribute6,
126 X_Attribute7,
127 X_Attribute8,
128 X_Attribute9,
129 X_Attribute10,
130 X_Context,
131 X_Attribute11,
132 X_Attribute12,
133 X_Attribute13,
134 X_Attribute14,
135 X_Attribute15,
136 X_Comments,
137 X_Cp_Status_Changed_Flag,
138 X_Old_Cp_Status_Id,
139 X_Current_Cp_Status_Id,
140 X_Type_Code_Changed_Flag,
141 X_Old_Type_Code,
142 X_Current_Type_Code
143
144 );
145
146 OPEN C;
147 FETCH C INTO X_Rowid;
148 if (C%NOTFOUND) then
149 CLOSE C;
150 Raise NO_DATA_FOUND;
151 end if;
152 CLOSE C;
153 END Insert_Row;
154
155
156 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
157
158 X_Customer_Product_Id NUMBER,
159 X_Customer_Changed_Flag VARCHAR2,
160 X_Old_Customer_Id NUMBER,
161 X_Current_Customer_Id NUMBER,
162 X_System_Changed_Flag VARCHAR2,
163 X_Old_System_Id NUMBER,
164 X_Current_System_Id NUMBER,
165 X_Prd_Agreement_Changed_Flag VARCHAR2,
166 X_Old_Product_Agreement_Id NUMBER,
167 X_Current_Product_Agreement_Id NUMBER,
168 X_Serv_Agreement_Changed_Flag VARCHAR2,
169 X_Old_Service_Agreement_Id NUMBER,
170 X_Current_Service_Agreement_Id NUMBER,
171 X_Attribute1 VARCHAR2,
172 X_Attribute2 VARCHAR2,
173 X_Attribute3 VARCHAR2,
174 X_Attribute4 VARCHAR2,
175 X_Attribute5 VARCHAR2,
176 X_Attribute6 VARCHAR2,
177 X_Attribute7 VARCHAR2,
178 X_Attribute8 VARCHAR2,
179 X_Attribute9 VARCHAR2,
180 X_Attribute10 VARCHAR2,
181 X_Context VARCHAR2,
182 X_Attribute11 VARCHAR2,
183 X_Attribute12 VARCHAR2,
184 X_Attribute13 VARCHAR2,
185 X_Attribute14 VARCHAR2,
186 X_Attribute15 VARCHAR2,
187 X_Comments VARCHAR2,
188 X_Cp_Status_Changed_Flag VARCHAR2,
189 X_Old_Cp_Status_Id NUMBER,
190 X_Current_Cp_Status_Id NUMBER,
191 X_Type_Code_Changed_Flag VARCHAR2,
192 X_Old_Type_Code VARCHAR2,
193 X_Current_Type_Code VARCHAR2
194 ) IS
195 CURSOR C IS
196 SELECT *
197 FROM CS_CP_AUDIT
198 WHERE rowid = X_Rowid
199 FOR UPDATE of Customer_Product_Id NOWAIT;
200 Recinfo C%ROWTYPE;
201
202
203 BEGIN
204 OPEN C;
205 FETCH C INTO Recinfo;
206 if (C%NOTFOUND) then
207 CLOSE C;
208 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
209 APP_EXCEPTION.Raise_Exception;
210 end if;
211 CLOSE C;
212 if (
213
214 (Recinfo.customer_product_id = X_Customer_Product_Id)
215 AND (Recinfo.customer_changed_flag = X_Customer_Changed_Flag)
216 AND ( (Recinfo.old_customer_id = X_Old_Customer_Id)
217 OR ( (Recinfo.old_customer_id IS NULL)
218 AND (X_Old_Customer_Id IS NULL)))
219 AND ( (Recinfo.current_customer_id = X_Current_Customer_Id)
220 OR ( (Recinfo.current_customer_id IS NULL)
221 AND (X_Current_Customer_Id IS NULL)))
222 AND (Recinfo.system_changed_flag = X_System_Changed_Flag)
223 AND ( (Recinfo.old_system_id = X_Old_System_Id)
224 OR ( (Recinfo.old_system_id IS NULL)
225 AND (X_Old_System_Id IS NULL)))
226 AND ( (Recinfo.current_system_id = X_Current_System_Id)
227 OR ( (Recinfo.current_system_id IS NULL)
228 AND (X_Current_System_Id IS NULL)))
229 AND (Recinfo.product_agreement_changed_flag = X_Prd_Agreement_Changed_Flag)
230 AND ( (Recinfo.old_product_agreement_id = X_Old_Product_Agreement_Id)
231 OR ( (Recinfo.old_product_agreement_id IS NULL)
232 AND (X_Old_Product_Agreement_Id IS NULL)))
233 AND ( (Recinfo.current_product_agreement_id = X_Current_Product_Agreement_Id)
234 OR ( (Recinfo.current_product_agreement_id IS NULL)
235 AND (X_Current_Product_Agreement_Id IS NULL)))
236 AND (Recinfo.service_agreement_changed_flag = X_Serv_Agreement_Changed_Flag)
237 AND ( (Recinfo.old_service_agreement_id = X_Old_Service_Agreement_Id)
238 OR ( (Recinfo.old_service_agreement_id IS NULL)
239 AND (X_Old_Service_Agreement_Id IS NULL)))
240 AND ( (Recinfo.current_service_agreement_id = X_Current_Service_Agreement_Id)
241 OR ( (Recinfo.current_service_agreement_id IS NULL)
242 AND (X_Current_Service_Agreement_Id IS NULL)))
243 AND ( (Recinfo.attribute1 = X_Attribute1)
244 OR ( (Recinfo.attribute1 IS NULL)
245 AND (X_Attribute1 IS NULL)))
246 AND ( (Recinfo.attribute2 = X_Attribute2)
247 OR ( (Recinfo.attribute2 IS NULL)
248 AND (X_Attribute2 IS NULL)))
249 AND ( (Recinfo.attribute3 = X_Attribute3)
250 OR ( (Recinfo.attribute3 IS NULL)
251 AND (X_Attribute3 IS NULL)))
252 AND ( (Recinfo.attribute4 = X_Attribute4)
253 OR ( (Recinfo.attribute4 IS NULL)
254 AND (X_Attribute4 IS NULL)))
255 AND ( (Recinfo.attribute5 = X_Attribute5)
256 OR ( (Recinfo.attribute5 IS NULL)
257 AND (X_Attribute5 IS NULL)))
258 AND ( (Recinfo.attribute6 = X_Attribute6)
259 OR ( (Recinfo.attribute6 IS NULL)
260 AND (X_Attribute6 IS NULL)))
261 AND ( (Recinfo.attribute7 = X_Attribute7)
262 OR ( (Recinfo.attribute7 IS NULL)
263 AND (X_Attribute7 IS NULL)))
264 AND ( (Recinfo.attribute8 = X_Attribute8)
265 OR ( (Recinfo.attribute8 IS NULL)
266 AND (X_Attribute8 IS NULL)))
267 AND ( (Recinfo.attribute9 = X_Attribute9)
268 OR ( (Recinfo.attribute9 IS NULL)
269 AND (X_Attribute9 IS NULL)))
270 AND ( (Recinfo.attribute10 = X_Attribute10)
271 OR ( (Recinfo.attribute10 IS NULL)
272 AND (X_Attribute10 IS NULL)))
273 AND ( (Recinfo.context = X_Context)
274 OR ( (Recinfo.context IS NULL)
275 AND (X_Context IS NULL)))
276 AND ( (Recinfo.attribute11 = X_Attribute11)
277 OR ( (Recinfo.attribute11 IS NULL)
278 AND (X_Attribute11 IS NULL)))
279 AND ( (Recinfo.attribute12 = X_Attribute12)
280 OR ( (Recinfo.attribute12 IS NULL)
281 AND (X_Attribute12 IS NULL)))
282 AND ( (Recinfo.attribute13 = X_Attribute13)
283 OR ( (Recinfo.attribute13 IS NULL)
284 AND (X_Attribute13 IS NULL)))
285 AND ( (Recinfo.attribute14 = X_Attribute14)
286 OR ( (Recinfo.attribute14 IS NULL)
287 AND (X_Attribute14 IS NULL)))
288 AND ( (Recinfo.attribute15 = X_Attribute15)
289 OR ( (Recinfo.attribute15 IS NULL)
290 AND (X_Attribute15 IS NULL)))
291 AND ( (Recinfo.comments = X_Comments)
292 OR ( (Recinfo.comments IS NULL)
293 AND (X_Comments IS NULL)))
294 AND (Recinfo.cp_status_changed_flag = X_Cp_Status_Changed_Flag)
295 AND ( (Recinfo.old_cp_status_id = X_Old_Cp_Status_Id)
296 OR ( (Recinfo.old_cp_status_id IS NULL)
297 AND (X_Old_Cp_Status_Id IS NULL)))
298 AND ( (Recinfo.current_cp_status_id = X_Current_Cp_Status_Id)
299 OR ( (Recinfo.current_cp_status_id IS NULL)
300 AND (X_Current_Cp_Status_Id IS NULL)))
304 AND (X_Old_Type_Code IS NULL)))
301 AND (Recinfo.type_code_changed_flag = X_Type_Code_Changed_Flag)
302 AND ( (Recinfo.old_type_code = X_Old_Type_Code)
303 OR ( (Recinfo.old_type_code IS NULL)
305 AND ( (Recinfo.current_type_code = X_Current_Type_Code)
306 OR ( (Recinfo.current_type_code IS NULL)
307 AND (X_Current_Type_Code IS NULL)))
308 ) then
309 return;
310 else
311 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
312 APP_EXCEPTION.Raise_Exception;
313 end if;
314 END Lock_Row;
315
316
317
318 PROCEDURE Update_Row(X_Rowid VARCHAR2,
319
320 X_Customer_Product_Id NUMBER,
321 X_Last_Update_Date DATE,
322 X_Last_Updated_By NUMBER,
323 X_Last_Update_Login NUMBER,
324 X_Customer_Changed_Flag VARCHAR2,
325 X_Old_Customer_Id NUMBER,
326 X_Current_Customer_Id NUMBER,
327 X_System_Changed_Flag VARCHAR2,
328 X_Old_System_Id NUMBER,
329 X_Current_System_Id NUMBER,
330 X_Prd_Agreement_Changed_Flag VARCHAR2,
331 X_Old_Product_Agreement_Id NUMBER,
332 X_Current_Product_Agreement_Id NUMBER,
333 X_Serv_Agreement_Changed_Flag VARCHAR2,
334 X_Old_Service_Agreement_Id NUMBER,
335 X_Current_Service_Agreement_Id NUMBER,
336 X_Attribute1 VARCHAR2,
337 X_Attribute2 VARCHAR2,
338 X_Attribute3 VARCHAR2,
339 X_Attribute4 VARCHAR2,
340 X_Attribute5 VARCHAR2,
341 X_Attribute6 VARCHAR2,
342 X_Attribute7 VARCHAR2,
343 X_Attribute8 VARCHAR2,
344 X_Attribute9 VARCHAR2,
345 X_Attribute10 VARCHAR2,
346 X_Context VARCHAR2,
347 X_Attribute11 VARCHAR2,
348 X_Attribute12 VARCHAR2,
349 X_Attribute13 VARCHAR2,
350 X_Attribute14 VARCHAR2,
351 X_Attribute15 VARCHAR2,
352 X_Comments VARCHAR2,
353 X_Cp_Status_Changed_Flag VARCHAR2,
354 X_Old_Cp_Status_Id NUMBER,
355 X_Current_Cp_Status_Id NUMBER,
356 X_Type_Code_Changed_Flag VARCHAR2,
357 X_Old_Type_Code VARCHAR2,
358 X_Current_Type_Code VARCHAR2
359
360 ) IS
361 BEGIN
362 UPDATE CS_CP_AUDIT
363 SET
364 customer_product_id = X_Customer_Product_Id,
365 last_update_date = X_Last_Update_Date,
366 last_updated_by = X_Last_Updated_By,
367 last_update_login = X_Last_Update_Login,
368 customer_changed_flag = X_Customer_Changed_Flag,
369 old_customer_id = X_Old_Customer_Id,
370 current_customer_id = X_Current_Customer_Id,
371 system_changed_flag = X_System_Changed_Flag,
372 old_system_id = X_Old_System_Id,
373 current_system_id = X_Current_System_Id,
374 product_agreement_changed_flag = X_Prd_Agreement_Changed_Flag,
375 old_product_agreement_id = X_Old_Product_Agreement_Id,
376 current_product_agreement_id = X_Current_Product_Agreement_Id,
377 service_agreement_changed_flag = X_Serv_Agreement_Changed_Flag,
378 old_service_agreement_id = X_Old_Service_Agreement_Id,
379 current_service_agreement_id = X_Current_Service_Agreement_Id,
380 attribute1 = X_Attribute1,
381 attribute2 = X_Attribute2,
382 attribute3 = X_Attribute3,
383 attribute4 = X_Attribute4,
384 attribute5 = X_Attribute5,
385 attribute6 = X_Attribute6,
386 attribute7 = X_Attribute7,
387 attribute8 = X_Attribute8,
388 attribute9 = X_Attribute9,
389 attribute10 = X_Attribute10,
390 context = X_Context,
391 attribute11 = X_Attribute11,
392 attribute12 = X_Attribute12,
393 attribute13 = X_Attribute13,
394 attribute14 = X_Attribute14,
395 attribute15 = X_Attribute15,
399 current_cp_status_id = X_Current_Cp_Status_Id,
396 comments = X_Comments,
397 cp_status_changed_flag = X_Cp_Status_Changed_Flag,
398 old_cp_status_id = X_Old_Cp_Status_Id,
400 type_code_changed_flag = X_Type_Code_Changed_Flag,
401 old_type_code = X_Old_Type_Code,
402 current_type_code = X_Current_Type_Code
403 WHERE rowid = X_Rowid;
404
405 if (SQL%NOTFOUND) then
406 Raise NO_DATA_FOUND;
407 end if;
408 END Update_Row;
409
410 END CS_CP_AUDIT_PKG;