[Home] [Help]
PACKAGE BODY: APPS.ARP_CREL_PKG
Source
1 PACKAGE BODY arp_crel_pkg as
2 /* $Header: AROCRELB.pls 120.1 2005/09/13 18:08:03 mantani noship $ */
3 PROCEDURE check_unique(x_customer_id in number ,x_related_customer_id in number ) is
4 /*
5 --
6 duplicate_count number(15);
7 --
8 -- BUG Fix 1283492. Fix made to allow users to in-activate a relationship between
9 -- two customers, and be able to create a new relationship. Without the fix, users
10 -- get the message that the relationship already exists, even though it is inactive.
11 -- Fix is to check for only active duplicate relationships.
12 */
13
14 begin
15 null;
16 /*
17 select count(1)
18 into duplicate_count
19 from ra_customer_relationships
20 where customer_id = x_customer_id
21 and status = 'A'
22 and related_customer_id = x_related_customer_id;
23
24 if (duplicate_count >= 1 ) then
25 fnd_message.set_name('AR','AR_CUST_REL_ALREADY_EXISTS');
26 app_exception.raise_exception;
27 end if;
28 */
29 end check_unique;
30 --
31 --
32 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
33 X_Created_By NUMBER,
34 X_Creation_Date DATE,
35 X_Customer_Id NUMBER,
36 X_Customer_Reciprocal_Flag VARCHAR2,
37 X_relationship_type VARCHAR2,
38 X_Last_Updated_By NUMBER,
39 X_Last_Update_Date DATE,
40 X_Related_Customer_Id NUMBER,
41 X_Status VARCHAR2,
42 X_Comments VARCHAR2,
43 X_Last_Update_Login NUMBER,
44 X_Attribute_Category VARCHAR2,
45 X_Attribute1 VARCHAR2,
46 X_Attribute2 VARCHAR2,
47 X_Attribute3 VARCHAR2,
48 X_Attribute4 VARCHAR2,
49 X_Attribute5 VARCHAR2,
50 X_Attribute6 VARCHAR2,
51 X_Attribute7 VARCHAR2,
52 X_Attribute8 VARCHAR2,
53 X_Attribute9 VARCHAR2,
54 X_Attribute10 VARCHAR2,
55 X_Attribute11 VARCHAR2,
56 X_Attribute12 VARCHAR2,
57 X_Attribute13 VARCHAR2,
58 X_Attribute14 VARCHAR2,
59 X_Attribute15 VARCHAR2
60 ) IS
61 /*
62 CURSOR C IS SELECT rowid FROM RA_CUSTOMER_RELATIONSHIPS
63 structure WHERE related_customer_id = X_Related_Customer_Id
64 AND customer_id = X_Customer_Id;
65 --
66 l_row_id varchar2(240);
67 --
68 */
69 BEGIN
70 --
71 null;
72 /*
73 check_unique(x_customer_id,x_related_customer_id);
74 --
75 INSERT INTO RA_CUSTOMER_RELATIONSHIPS(
76 created_by,
77 creation_date,
78 customer_id,
79 customer_reciprocal_flag,
80 last_updated_by,
81 last_update_date,
82 related_customer_id,
83 status,
84 comments,
85 last_update_login,
86 relationship_type,
87 attribute_category,
88 attribute1,
89 attribute2,
90 attribute3,
91 attribute4,
92 attribute5,
93 attribute6,
94 attribute7,
95 attribute8,
96 attribute9,
97 attribute10,
98 attribute11,
99 attribute12,
100 attribute13,
101 attribute14,
102 attribute15
103 ) VALUES (
104
105 X_Created_By,
106 X_Creation_Date,
107 X_Customer_Id,
108 X_Customer_Reciprocal_Flag,
109 X_Last_Updated_By,
110 X_Last_Update_Date,
111 X_Related_Customer_Id,
112 X_Status,
113 X_Comments,
114 X_Last_Update_Login,
115 x_relationship_type,
116 X_Attribute_Category,
117 X_Attribute1,
118 X_Attribute2,
119 X_Attribute3,
120 X_Attribute4,
121 X_Attribute5,
122 X_Attribute6,
123 X_Attribute7,
124 X_Attribute8,
125 X_Attribute9,
126 X_Attribute10,
127 X_Attribute11,
128 X_Attribute12,
129 X_Attribute13,
130 X_Attribute14,
131 X_Attribute15
132 );
133 --
134 if ( x_customer_reciprocal_flag = 'Y' ) then
135 --
136 -- Attempt to update the reciprocal relationship.
137 -- If it exists then update it
138 -- else insert a relationship
139 --
140 update ra_customer_relationships
141 set customer_reciprocal_flag = 'Y'
142 where customer_ID = x_related_customer_id
143 and related_customer_id = x_customer_id;
144 --
145 --
146 --
147 if ( SQL%NOTFOUND ) then
148 --
149 insert into ra_customer_relationships
150 ( related_customer_id,
151 last_update_date,
152 last_updated_by,
153 creation_date,
154 created_by,
155 last_update_login,
156 customer_id,
157 relationship_type,
158 comments,
159 attribute_category,
160 attribute1,
161 attribute2,
162 attribute3,
163 attribute4,
164 attribute5,
165 attribute6,
166 attribute7,
167 attribute8,
168 attribute9,
169 attribute10,
170 attribute11,
171 attribute12,
172 attribute13,
173 attribute14,
174 attribute15,
175 customer_reciprocal_flag,
176 status)
177 values
178 ( x_customer_id,
179 x_last_update_date,
180 x_last_updated_by,
181 x_creation_date,
182 x_created_by,
183 x_last_update_login,
184 x_related_customer_id,
185 x_relationship_type,
186 x_comments,
187 x_attribute_category,
188 x_attribute1,
189 x_attribute2,
190 x_attribute3,
191 x_attribute4,
192 x_attribute5,
193 x_attribute6,
194 x_attribute7,
195 x_attribute8,
196 x_attribute9,
197 x_attribute10,
198 x_attribute11,
199 x_attribute12,
200 x_attribute13,
201 x_attribute14,
202 x_attribute15,
203 x_customer_reciprocal_flag,
204 x_status);
205 --
206 end if;
207 --
208 --
209 end if;
210 --
211 OPEN C;
212 FETCH C INTO X_Rowid;
213 if (C%NOTFOUND) then
214 CLOSE C;
215 Raise NO_DATA_FOUND;
216 end if;
217 CLOSE C;
218 */
219 END Insert_Row;
220
221
222 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
223 X_Customer_Id NUMBER,
224 X_Customer_Reciprocal_Flag VARCHAR2,
225 X_Related_Customer_Id NUMBER,
226 X_Status VARCHAR2,
227 X_Comments VARCHAR2,
228 X_Attribute_Category VARCHAR2,
229 X_Attribute1 VARCHAR2,
230 X_Attribute2 VARCHAR2,
231 X_Attribute3 VARCHAR2,
232 X_Attribute4 VARCHAR2,
233 X_Attribute5 VARCHAR2,
234 X_Attribute6 VARCHAR2,
235 X_Attribute7 VARCHAR2,
236 X_Attribute8 VARCHAR2,
237 X_Attribute9 VARCHAR2,
238 X_Attribute10 VARCHAR2,
239 X_Attribute11 VARCHAR2,
240 X_Attribute12 VARCHAR2,
241 X_Attribute13 VARCHAR2,
242 X_Attribute14 VARCHAR2,
243 X_Attribute15 VARCHAR2
244 ) IS
245 /*
246 CURSOR C IS
247 SELECT *
248 FROM RA_CUSTOMER_RELATIONSHIPS
249 WHERE rowid = X_Rowid
250 FOR UPDATE of Related_Customer_Id NOWAIT;
251 Recinfo C%ROWTYPE;
252 */
253
254 BEGIN
255 null;
256 /*
257 OPEN C;
258 FETCH C INTO Recinfo;
259 if (C%NOTFOUND) then
260 CLOSE C;
261 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
262 APP_EXCEPTION.Raise_Exception;
263 end if;
264 CLOSE C;
265 if (
266 (Recinfo.customer_id = X_Customer_Id)
267 AND (Recinfo.customer_reciprocal_flag = X_Customer_Reciprocal_Flag)
268 AND (Recinfo.related_customer_id = X_Related_Customer_Id)
269 AND (Recinfo.status = X_Status)
270 AND ( (Recinfo.comments = X_Comments)
271 OR ( (Recinfo.comments IS NULL)
272 AND (X_Comments IS NULL)))
273 AND ( (Recinfo.attribute_category = X_Attribute_Category)
274 OR ( (Recinfo.attribute_category IS NULL)
275 AND (X_Attribute_Category IS NULL)))
276 AND ( (Recinfo.attribute1 = X_Attribute1)
277 OR ( (Recinfo.attribute1 IS NULL)
278 AND (X_Attribute1 IS NULL)))
279 AND ( (Recinfo.attribute2 = X_Attribute2)
280 OR ( (Recinfo.attribute2 IS NULL)
281 AND (X_Attribute2 IS NULL)))
282 AND ( (Recinfo.attribute3 = X_Attribute3)
283 OR ( (Recinfo.attribute3 IS NULL)
284 AND (X_Attribute3 IS NULL)))
285 AND ( (Recinfo.attribute4 = X_Attribute4)
286 OR ( (Recinfo.attribute4 IS NULL)
287 AND (X_Attribute4 IS NULL)))
288 AND ( (Recinfo.attribute5 = X_Attribute5)
289 OR ( (Recinfo.attribute5 IS NULL)
290 AND (X_Attribute5 IS NULL)))
291 AND ( (Recinfo.attribute6 = X_Attribute6)
292 OR ( (Recinfo.attribute6 IS NULL)
293 AND (X_Attribute6 IS NULL)))
294 AND ( (Recinfo.attribute7 = X_Attribute7)
295 OR ( (Recinfo.attribute7 IS NULL)
296 AND (X_Attribute7 IS NULL)))
297 AND ( (Recinfo.attribute8 = X_Attribute8)
298 OR ( (Recinfo.attribute8 IS NULL)
299 AND (X_Attribute8 IS NULL)))
300 AND ( (Recinfo.attribute9 = X_Attribute9)
301 OR ( (Recinfo.attribute9 IS NULL)
302 AND (X_Attribute9 IS NULL)))
303 AND ( (Recinfo.attribute10 = X_Attribute10)
304 OR ( (Recinfo.attribute10 IS NULL)
305 AND (X_Attribute10 IS NULL)))
306 AND ( (Recinfo.attribute11 = X_Attribute11)
307 OR ( (Recinfo.attribute11 IS NULL)
308 AND (X_Attribute11 IS NULL)))
309 AND ( (Recinfo.attribute12 = X_Attribute12)
310 OR ( (Recinfo.attribute12 IS NULL)
311 AND (X_Attribute12 IS NULL)))
312 AND ( (Recinfo.attribute13 = X_Attribute13)
313 OR ( (Recinfo.attribute13 IS NULL)
314 AND (X_Attribute13 IS NULL)))
315 AND ( (Recinfo.attribute14 = X_Attribute14)
316 OR ( (Recinfo.attribute14 IS NULL)
317 AND (X_Attribute14 IS NULL)))
318 AND ( (Recinfo.attribute15 = X_Attribute15)
319 OR ( (Recinfo.attribute15 IS NULL)
320 AND (X_Attribute15 IS NULL)))
321 ) then
322 return;
323 else
324 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
325 APP_EXCEPTION.Raise_Exception;
326 end if;
327 */
328 END Lock_Row;
329
330
331
332 PROCEDURE Update_Row(X_Rowid VARCHAR2,
333 X_Customer_Id NUMBER,
334 X_Customer_Reciprocal_Flag VARCHAR2,
335 X_Last_Updated_By NUMBER,
336 X_Last_Update_Date DATE,
337 X_Related_Customer_Id NUMBER,
338 X_Status VARCHAR2,
339 X_Comments VARCHAR2,
340 X_Last_Update_Login NUMBER,
341 X_Attribute_Category VARCHAR2,
342 X_Attribute1 VARCHAR2,
343 X_Attribute2 VARCHAR2,
344 X_Attribute3 VARCHAR2,
345 X_Attribute4 VARCHAR2,
346 X_Attribute5 VARCHAR2,
347 X_Attribute6 VARCHAR2,
348 X_Attribute7 VARCHAR2,
349 X_Attribute8 VARCHAR2,
350 X_Attribute9 VARCHAR2,
351 X_Attribute10 VARCHAR2,
352 X_Attribute11 VARCHAR2,
353 X_Attribute12 VARCHAR2,
354 X_Attribute13 VARCHAR2,
355 X_Attribute14 VARCHAR2,
356 X_Attribute15 VARCHAR2
357 ) IS
358 BEGIN
359 null;
360 /*
361 -- Bug fix 1283492. Check for unique relationship only if updating status to active
362
363 if (x_status = 'A') then
364 check_unique(x_customer_id,x_related_customer_id);
365 end if;
366
367 UPDATE RA_CUSTOMER_RELATIONSHIPS
368 SET
369 customer_id = X_Customer_Id,
370 customer_reciprocal_flag = X_Customer_Reciprocal_Flag,
371 last_updated_by = X_Last_Updated_By,
372 last_update_date = X_Last_Update_Date,
373 related_customer_id = X_Related_Customer_Id,
374 status = X_Status,
375 comments = X_Comments,
376 last_update_login = X_Last_Update_Login,
377 attribute_category = X_Attribute_Category,
378 attribute1 = X_Attribute1,
379 attribute2 = X_Attribute2,
380 attribute3 = X_Attribute3,
381 attribute4 = X_Attribute4,
382 attribute5 = X_Attribute5,
383 attribute6 = X_Attribute6,
384 attribute7 = X_Attribute7,
385 attribute8 = X_Attribute8,
386 attribute9 = X_Attribute9,
387 attribute10 = X_Attribute10,
388 attribute11 = X_Attribute11,
389 attribute12 = X_Attribute12,
393 WHERE rowid = X_Rowid;
390 attribute13 = X_Attribute13,
391 attribute14 = X_Attribute14,
392 attribute15 = X_Attribute15
394
395 if (SQL%NOTFOUND) then
396 Raise NO_DATA_FOUND;
397 end if;
398 --
399 -- Update the reciprocal relationship.
400 -- if it exist.
401 --
402 update ra_customer_relationships
403 set customer_reciprocal_flag = decode(x_status,
404 'I','N',
405 'A','Y'
406 )
407 where customer_id = x_related_customer_id
408 and related_customer_id = x_customer_id;
409 --
410 --
411 */
412 END Update_Row;
413
414 END arp_crel_pkg;