[Home] [Help]
PACKAGE BODY: APPS.PNT_CONT_PKG
Source
1 PACKAGE BODY PNT_CONT_PKG AS
2 -- $Header: PNTCONTB.pls 120.2 2005/12/01 03:35:35 appldev ship $
3
4 -------------------------------------------------------------------------------
5 -- NAME : check_unique_contact_name
6 -- DESCRIPTION : This procedure ensures that contact name is unique.
7 -- INVOKED FROM :
8 -- ARGUMENTS : IN : x_company_site_id, x_rowid, x_first_name,
9 -- x_last_name, x_warning_flag, x_org_id
10 -- REFERENCE : PN_COMMON.debug()
11 -- HISTORY :
12 -- 16-JUN-98 Neeraj Tandon o Created
13 -- 21-JUN-05 piagrawa o Bug 4284035 - Removed NVL
14 -------------------------------------------------------------------------------
15 PROCEDURE check_unique_contact_name ( x_rowid VARCHAR2,
16 x_company_site_id NUMBER,
17 x_first_name VARCHAR2,
18 x_last_name VARCHAR2,
19 x_warning_flag IN OUT NOCOPY VARCHAR2,
20 x_org_id IN NUMBER
21 ) IS
22 dummy number;
23
24 BEGIN
25
26 SELECT 1
27 INTO dummy
28 FROM dual
29 WHERE NOT EXISTS ( SELECT 1
30 FROM pn_contacts_all c
31 WHERE c.last_name = x_last_name
32 AND c.first_name = x_first_name
33 AND c.company_site_id = x_company_site_id
34 AND (( x_rowid is null) or (c.rowid <> x_rowid))
35 AND org_id = x_org_id
36 );
37 EXCEPTION
38
39 WHEN NO_DATA_FOUND then
40 fnd_message.set_name ('PN','PN_DUP_COMPANY_CONTACT_NAME');
41 x_warning_flag := 'W';
42
43 END check_unique_contact_name;
44
45 -------------------------------------------------------------------------------
46 -- NAME : check_primary
47 -- DESCRIPTION : This procedure ensures that there is only one primary contact
48 -- for the company site
49 -- INVOKED FROM :
50 -- ARGUMENTS : IN: p_contact_id, p_company_site_id, p_org_id
51 -- REFERENCE : PN_COMMON.debug()
52 -- HISTORY :
53 -- 16-JUN-98 Neeraj Tandon o Created
54 -- 21-JUN-05 piagrawa o Bug 4284035 - Removed NVL
55 -------------------------------------------------------------------------------
56 PROCEDURE check_primary ( p_contact_id IN NUMBER,
57 p_company_site_id IN NUMBER,
58 p_org_id IN NUMBER
59 ) IS
60
61 primary_count number;
62
63 BEGIN
64
65 SELECT count(1)
66 INTO primary_count
67 FROM pn_contacts_all pc
68 WHERE pc.company_site_id = p_company_site_id
69 AND pc.primary_flag = 'Y'
70 AND ((p_contact_id is null) or pc.contact_id <> p_contact_id )
71 AND org_id = p_org_id;
72
73 IF ( primary_count >= 1 ) THEN
74 fnd_message.set_name('PN','PN_COMP_SITE_ONE_PRIM_CONTACT');
75 APP_EXCEPTION.raise_exception;
76 END IF;
77
78 END check_primary;
79
80 -------------------------------------------------------------------------------
81 -- PROCDURE : INSERT_ROW
82 -- INVOKED FROM : insert_row procedure
83 -- PURPOSE : inserts the row
84 -- HISTORY :
85 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_contacts with _ALL table.
86 -- 28-NOV-05 pikhar o fetched org_id using cursor
87 -------------------------------------------------------------------------------
88 PROCEDURE insert_row ( x_rowid IN OUT NOCOPY VARCHAR2,
89 x_contact_id IN OUT NOCOPY NUMBER,
90 x_company_site_id NUMBER,
91 x_last_name VARCHAR2,
92 x_created_by NUMBER,
93 x_creation_date DATE,
94 x_last_updated_by NUMBER,
95 x_last_update_date DATE,
96 x_last_update_login NUMBER,
97 x_status VARCHAR2,
98 x_first_Name VARCHAR2,
99 x_job_title VARCHAR2,
100 x_mail_stop VARCHAR2,
101 x_email_address VARCHAR2,
102 x_primary_flag VARCHAR2,
103 x_company_or_location VARCHAR2,
104 x_attribute_category VARCHAR2,
105 x_attribute1 VARCHAR2,
106 x_attribute2 VARCHAR2,
107 x_attribute3 VARCHAR2,
108 x_attribute4 VARCHAR2,
109 x_attribute5 VARCHAR2,
110 x_attribute6 VARCHAR2,
111 x_attribute7 VARCHAR2,
112 x_attribute8 VARCHAR2,
113 x_attribute9 VARCHAR2,
114 x_attribute10 VARCHAR2,
115 x_attribute11 VARCHAR2,
116 x_attribute12 VARCHAR2,
117 x_attribute13 VARCHAR2,
118 x_attribute14 VARCHAR2,
119 x_attribute15 VARCHAR2,
120 x_org_id NUMBER
121 )
122 IS
123 CURSOR c IS
124 SELECT rowid
125 FROM pn_contacts_all
126 WHERE contact_id = x_contact_id;
127
128 CURSOR org_cur IS
129 SELECT org_id
130 FROM pn_company_sites_all
131 WHERE company_site_id = x_company_site_id;
132
133 l_org_id NUMBER;
134
135
136 BEGIN
137
138 -----------------------------------------------------------------
139 -- Allocate the sequence to the primary key contact_id
140 -----------------------------------------------------------------
141
142 IF x_org_id IS NULL AND x_company_site_id IS NOT NULL THEN
143 FOR rec IN org_cur LOOP
144 l_org_id := rec.org_id;
145 END LOOP;
146 ELSE
147 l_org_id := x_org_id;
148 END IF;
149
150 SELECT pn_contacts_s.nextval
151 INTO x_contact_id
152 FROM dual;
153
154 INSERT INTO pn_contacts_all
155 (
156 contact_id,
157 company_site_id,
158 last_name,
159 created_by,
160 creation_date,
161 last_updated_by,
162 last_update_date,
163 last_update_login,
164 status,
165 first_name,
166 job_title,
167 mail_stop,
168 email_address,
169 primary_flag,
170 attribute_category,
171 attribute1,
172 attribute2,
173 attribute3,
174 attribute4,
175 attribute5,
176 attribute6,
177 attribute7,
178 attribute8,
179 attribute9,
180 attribute10,
181 attribute11,
182 attribute12,
183 attribute13,
184 attribute14,
185 attribute15,
186 org_id
187 )
188 VALUES
189 (
190 x_contact_id,
191 x_company_site_id,
192 x_last_name,
193 x_created_by,
194 x_creation_date,
195 x_last_updated_by,
196 x_last_update_date,
197 x_last_update_login,
198 x_status,
199 x_first_name,
200 x_job_title,
201 x_mail_stop,
202 x_email_address,
203 x_primary_flag,
204 x_attribute_category,
205 x_attribute1,
206 x_attribute2,
207 x_attribute3,
208 x_attribute4,
209 x_attribute5,
210 x_attribute6,
211 x_attribute7,
212 x_attribute8,
213 x_attribute9,
214 x_attribute10,
215 x_attribute11,
216 x_attribute12,
217 x_attribute13,
218 x_attribute14,
219 x_attribute15,
220 l_org_id
221 );
222 OPEN C;
223 FETCH C INTO x_rowid;
224 IF (C%NOTFOUND) THEN
225 CLOSE C;
226 RAISE NO_DATA_FOUND;
227 END IF;
228 CLOSE C;
229
230 END insert_row;
231
232 -------------------------------------------------------------------------------
233 -- PROCDURE : update_row
234 -- INVOKED FROM : update_row procedure
235 -- PURPOSE : updates the row
236 -- HISTORY :
237 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_contacts with _ALL table.
238 -- Also updated the where clause
239 -------------------------------------------------------------------------------
240 PROCEDURE update_row ( x_rowid VARCHAR2,
241 x_contact_id NUMBER,
242 x_company_site_id NUMBER,
243 x_last_name VARCHAR2,
244 x_last_updated_by NUMBER,
245 x_last_update_date DATE,
246 x_last_update_login NUMBER,
247 x_status VARCHAR2,
248 x_first_Name VARCHAR2,
249 x_job_title VARCHAR2,
250 x_mail_stop VARCHAR2,
251 x_email_address VARCHAR2,
252 x_primary_flag VARCHAR2,
253 x_attribute_category VARCHAR2,
254 x_attribute1 VARCHAR2,
255 x_attribute2 VARCHAR2,
256 x_attribute3 VARCHAR2,
257 x_attribute4 VARCHAR2,
258 x_attribute5 VARCHAR2,
259 x_attribute6 VARCHAR2,
260 x_attribute7 VARCHAR2,
261 x_attribute8 VARCHAR2,
262 x_attribute9 VARCHAR2,
263 x_attribute10 VARCHAR2,
264 x_attribute11 VARCHAR2,
265 x_attribute12 VARCHAR2,
266 x_attribute13 VARCHAR2,
267 x_attribute14 VARCHAR2,
268 x_attribute15 VARCHAR2
269 )
270 IS
271 BEGIN
272
273 UPDATE pn_contacts_all
274 SET
275 last_name = x_last_name,
276 first_name = x_first_name,
277 status = x_status,
278 job_title = x_job_title,
279 mail_stop = x_mail_stop,
280 email_address = x_email_address,
281 primary_flag = x_primary_flag,
282 last_updated_by = x_last_updated_by,
283 last_update_date = x_last_update_date,
284 last_update_login = x_last_update_login,
285 attribute_category = x_attribute_category,
286 attribute1 = x_attribute1,
287 attribute2 = x_attribute2,
288 attribute3 = x_attribute3,
289 attribute4 = x_attribute4,
290 attribute5 = x_attribute5,
291 attribute6 = x_attribute6,
292 attribute7 = x_attribute7,
293 attribute8 = x_attribute8,
294 attribute9 = x_attribute9,
295 attribute10 = x_attribute10,
296 attribute11 = x_attribute11,
297 attribute12 = x_attribute12,
298 attribute13 = x_attribute13,
299 attribute14 = x_attribute14,
300 attribute15 = x_attribute15
301 WHERE contact_id = x_contact_id;
302
303 IF (SQL%NOTFOUND) THEN
304 RAISE NO_DATA_FOUND;
305 END IF;
306
307 END update_row;
308
309 -------------------------------------------------------------------------------
310 -- PROCDURE : lock_row
311 -- INVOKED FROM : lock_row procedure
312 -- PURPOSE : locks the row
313 -- HISTORY :
314 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_contacts with _ALL table.
315 -------------------------------------------------------------------------------
316 PROCEDURE lock_row ( x_rowid VARCHAR2,
317 x_contact_id NUMBER,
318 x_last_name VARCHAR2,
319 x_status VARCHAR2,
320 x_first_Name VARCHAR2,
321 x_job_title VARCHAR2,
322 x_mail_stop VARCHAR2,
323 x_email_address VARCHAR2,
324 x_primary_flag VARCHAR2,
325 x_attribute_category VARCHAR2,
326 x_attribute1 VARCHAR2,
327 x_attribute2 VARCHAR2,
328 x_attribute3 VARCHAR2,
329 x_attribute4 VARCHAR2,
330 x_attribute5 VARCHAR2,
331 x_attribute6 VARCHAR2,
332 x_attribute7 VARCHAR2,
333 x_attribute8 VARCHAR2,
334 x_attribute9 VARCHAR2,
335 x_attribute10 VARCHAR2,
336 x_attribute11 VARCHAR2,
337 x_attribute12 VARCHAR2,
338 x_attribute13 VARCHAR2,
339 x_attribute14 VARCHAR2,
340 x_attribute15 VARCHAR2
341 )
342 IS
343 CURSOR C IS
344 SELECT *
345 FROM pn_contacts_all
346 WHERE ROWID = x_rowid
347 FOR UPDATE OF contact_id NOWAIT;
348
349 Recinfo C%ROWTYPE;
350
351 BEGIN
352
353 OPEN C;
354 FETCH C INTO Recinfo;
355 if (C%NOTFOUND) then
356 CLOSE C;
357 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
358 APP_EXCEPTION.Raise_Exception;
359 end if;
360 CLOSE C;
361
362 IF NOT (recinfo.last_name = x_last_name) THEN
363 pn_var_rent_pkg.lock_row_exception('last_name',Recinfo.last_name);
364 END IF;
365 IF NOT (recinfo.status = x_status) THEN
366 pn_var_rent_pkg.lock_row_exception('status',Recinfo.status);
367 END IF;
368 IF NOT ( (recinfo.first_name = x_first_name)
369 or ((recinfo.first_name is null) and (x_first_name is null))) THEN
370 pn_var_rent_pkg.lock_row_exception('first_name',Recinfo.first_name);
371 END IF;
372 IF NOT ( (recinfo.job_title = x_job_title)
373 or ((recinfo.job_title is null) and (x_job_title is null))) THEN
374 pn_var_rent_pkg.lock_row_exception('job_title',Recinfo.job_title);
375 END IF;
376 IF NOT ( (recinfo.mail_stop = x_mail_stop)
380 IF NOT ( (recinfo.email_address = x_email_address)
377 or ((recinfo.mail_stop is null) and (x_mail_stop is null))) THEN
378 pn_var_rent_pkg.lock_row_exception('mail_stop',Recinfo.mail_stop);
379 END IF;
381 or ((recinfo.email_address is null) and (x_email_address is null))) THEN
382 pn_var_rent_pkg.lock_row_exception('email_address',Recinfo.email_address);
383 END IF;
384 IF NOT ( (recinfo.primary_flag = x_primary_flag)
385 or ((recinfo.primary_flag is null) and (x_primary_flag is null))) THEN
386 pn_var_rent_pkg.lock_row_exception('primary_flag',Recinfo.primary_flag);
387 END IF;
388 IF NOT ( (recinfo.attribute_category = x_attribute_category)
389 or ((recinfo.attribute_category is null) and (x_attribute_category is null))) THEN
390 pn_var_rent_pkg.lock_row_exception('attribute_category',Recinfo.attribute_category);
391 END IF;
392 IF NOT ( (recinfo.attribute1 = x_attribute1)
393 or ((recinfo.attribute1 is null) and (x_attribute1 is null))) THEN
394 pn_var_rent_pkg.lock_row_exception('attribute1',Recinfo.attribute1);
395 END IF;
396 IF NOT ( (recinfo.attribute2 = x_attribute2)
397 or ((recinfo.attribute2 is null) and (x_attribute2 is null))) THEN
398 pn_var_rent_pkg.lock_row_exception('attribute2',Recinfo.attribute2);
399 END IF;
400 IF NOT ( (recinfo.attribute3 = x_attribute3)
401 or ((recinfo.attribute3 is null) and (x_attribute3 is null))) THEN
402 pn_var_rent_pkg.lock_row_exception('attribute3',Recinfo.attribute3);
403 END IF;
404 IF NOT ( (recinfo.attribute4 = x_attribute4)
405 or ((recinfo.attribute4 is null) and (x_attribute4 is null))) THEN
406 pn_var_rent_pkg.lock_row_exception('attribute4',Recinfo.attribute4);
407 END IF;
408 IF NOT ( (recinfo.attribute5 = x_attribute5)
409 or ((recinfo.attribute5 is null) and (x_attribute5 is null))) THEN
410 pn_var_rent_pkg.lock_row_exception('attribute5',Recinfo.attribute5);
411 END IF;
412 IF NOT ( (recinfo.attribute6 = x_attribute6)
413 or ((recinfo.attribute6 is null) and (x_attribute6 is null))) THEN
414 pn_var_rent_pkg.lock_row_exception('attribute6',Recinfo.attribute6);
415 END IF;
416 IF NOT ( (recinfo.attribute7 = x_attribute7)
417 or ((recinfo.attribute7 is null) and (x_attribute7 is null))) THEN
418 pn_var_rent_pkg.lock_row_exception('attribute7',Recinfo.attribute7);
419 END IF;
420 IF NOT ( (recinfo.attribute8 = x_attribute8)
421 or ((recinfo.attribute8 is null) and (x_attribute8 is null))) THEN
422 pn_var_rent_pkg.lock_row_exception('attribute8',Recinfo.attribute8);
423 END IF;
424 IF NOT ( (recinfo.attribute9 = x_attribute9)
425 or ((recinfo.attribute9 is null) and (x_attribute9 is null))) THEN
426 pn_var_rent_pkg.lock_row_exception('attribute9',Recinfo.attribute9);
427 END IF;
428 IF NOT ( (recinfo.attribute10 = x_attribute10)
429 or ((recinfo.attribute10 is null) and (x_attribute10 is null))) THEN
430 pn_var_rent_pkg.lock_row_exception('attribute10',Recinfo.attribute10);
431 END IF;
432 IF NOT ( (recinfo.attribute11 = x_attribute11)
433 or ((recinfo.attribute11 is null) and (x_attribute11 is null))) THEN
434 pn_var_rent_pkg.lock_row_exception('attribute11',Recinfo.attribute11);
435 END IF;
436 IF NOT ( (recinfo.attribute12 = x_attribute12)
437 or ((recinfo.attribute12 is null) and (x_attribute12 is null))) THEN
438 pn_var_rent_pkg.lock_row_exception('attribute12',Recinfo.attribute12);
439 END IF;
440 IF NOT ( (recinfo.attribute13 = x_attribute13)
441 or ((recinfo.attribute13 is null) and (x_attribute13 is null))) THEN
442 pn_var_rent_pkg.lock_row_exception('attribute13',Recinfo.attribute13);
443 END IF;
444 IF NOT ( (recinfo.attribute14 = x_attribute14)
445 or ((recinfo.attribute14 is null) and (x_attribute14 is null))) THEN
446 pn_var_rent_pkg.lock_row_exception('attribute14',Recinfo.attribute14);
447 END IF;
448 IF NOT ( (recinfo.attribute15 = x_attribute15)
449 or ((recinfo.attribute15 is null) and (x_attribute15 is null))) THEN
450 pn_var_rent_pkg.lock_row_exception('attribute15',Recinfo.attribute15);
451 END IF;
452
453 END lock_row;
454
455 -------------------------------------------------------------------------------
456 -- PROCDURE : check_delete
457 -- INVOKED FROM :
458 -- PURPOSE : This procedure ensures that contact row cannot be deleted if
459 -- phones exist for the contact (in pn_phones)
460 -- ARGUMENTS : IN: p_contact_id
461 -- RETURNS : NONE
462 -- NOTES : Used by client side code in PNSUCOMP.pll
463 -- HISTORY :
464 -- 14-JUL-98 Neeraj o Created
465 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_phones with _ALL table.
466 -------------------------------------------------------------------------------
467 PROCEDURE check_delete ( x_contact_id NUMBER )
468 IS
469 CURSOR pn_phones_cur IS
470 SELECT 1
471 FROM pn_phones_all
472 WHERE contact_id = x_contact_id;
473
474 BEGIN
475 FOR i in pn_phones_cur
476 LOOP
477 fnd_message.set_name ('PN', 'PN_PHONES_EXIST_CANNOT_DELETE');
478 app_exception.raise_exception;
479 END LOOP;
480
481 END check_delete;
482
483 -------------------------------------------------------------------------------
484 -- PROCDURE : delete_row
485 -- INVOKED FROM : delete_row procedure
486 -- PURPOSE : deletes the row
487 -- HISTORY :
488 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_contacts,pn_phones with _ALL
489 -- table.
490 -------------------------------------------------------------------------------
491 PROCEDURE delete_row
492 (
493 x_contact_id NUMBER
494 )
495 IS
496 BEGIN
497
498 DELETE FROM pn_phones_all
499 WHERE contact_id = x_contact_id;
500
501 DELETE FROM pn_contacts_all
502 WHERE contact_id = x_contact_id;
503
504 IF (SQL%NOTFOUND) THEN
505 RAISE NO_DATA_FOUND;
506 END IF;
507
508 END delete_row;
509 --
510 --
511 END PNT_CONT_PKG;