[Home] [Help]
PACKAGE BODY: APPS.PNT_PHONE_PKG
Source
1 PACKAGE BODY PNT_PHONE_PKG as
2 -- $Header: PNTPHONB.pls 120.2 2005/12/01 08:27:13 appldev ship $
3
4
5 -------------------------------------------------------------------------------
6 -- NAME : check_primary
7 -- DESCRIPTION : This procedure ensures that there is only one primary phone
8 -- for the contact.
9 -- SCOPE : PUBLIC
10 -- INVOKED FROM :
11 -- ARGUMENTS : IN: p_phone_id, p_contact_id, p_org_id
12 -- REFERENCE : PN_COMMON.debug()
13 -- HISTORY :
14 -- 16-JUN-98 Neeraj Tandon o Created
15 -- 14-MAY-02 Daniel Thota o added parameter p_org_id for multi-org
16 -- 21-JUN-05 piagrawa o Bug 4284035 - Removed nvl for org id.
17 -------------------------------------------------------------------------------
18 PROCEDURE check_primary ( p_phone_id IN NUMBER,
19 p_contact_id IN NUMBER,
20 p_org_id IN NUMBER default NULL
21 )
22 IS
23 primary_count number;
24 BEGIN
25
26 SELECT count(1)
27 INTO primary_count
28 FROM pn_phones_all ph
29 WHERE ph.contact_id = p_contact_id
30 AND ph.primary_flag = 'Y'
31 AND ((p_phone_id is null) or ph.phone_id <> p_phone_id )
32 AND org_id = p_org_id;
33
34 IF ( primary_count >= 1 ) THEN
35 fnd_message.set_name('PN','PN_CONT_ONE_PRIMARY_PHONE');
36 APP_EXCEPTION.raise_exception;
37 END IF;
38
39 END check_primary;
40
41
42 -------------------------------------------------------------------------------
43 -- PROCDURE : Insert_Row
44 -- INVOKED FROM : Insert_Row procedure
45 -- PURPOSE : inserts the row
46 -- HISTORY :
47 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_phones with _ALL table.
48 -- Also changed the where clause
49 -- 01-DEC-05 pikhar o fetched org_id using cursor
50 -------------------------------------------------------------------------------
51 PROCEDURE Insert_Row ( X_Rowid IN OUT NOCOPY VARCHAR2,
52 X_Phone_Id IN OUT NOCOPY NUMBER,
53 X_Last_Update_Date DATE,
54 X_Last_Updated_By NUMBER,
55 X_Creation_Date DATE,
56 X_Created_By NUMBER,
57 X_Phone_Number VARCHAR2,
58 X_Status VARCHAR2,
59 X_Phone_Type VARCHAR2,
60 X_Last_Update_Login NUMBER,
61 X_Contact_Id NUMBER,
62 X_Area_Code VARCHAR2,
63 X_Extension VARCHAR2,
64 X_Primary_Flag VARCHAR2,
65 X_Attribute_Category VARCHAR2,
66 X_Attribute1 VARCHAR2,
67 X_Attribute2 VARCHAR2,
68 X_Attribute3 VARCHAR2,
69 X_Attribute4 VARCHAR2,
70 X_Attribute5 VARCHAR2,
71 X_Attribute6 VARCHAR2,
72 X_Attribute7 VARCHAR2,
73 X_Attribute8 VARCHAR2,
74 X_Attribute9 VARCHAR2,
75 X_Attribute10 VARCHAR2,
76 X_Attribute11 VARCHAR2,
77 X_Attribute12 VARCHAR2,
78 X_Attribute13 VARCHAR2,
79 X_Attribute14 VARCHAR2,
80 X_Attribute15 VARCHAR2,
81 X_Org_id NUMBER default NULL
82 )
83 IS
84 CURSOR C IS
85 SELECT rowid
86 FROM pn_phones_all
87 WHERE phone_id = X_Phone_Id;
88
89 l_id number;
90
91 CURSOR org_cur IS
92 SELECT org_id
93 FROM pn_contacts_all
94 WHERE contact_id = x_contact_id;
95
96 l_org_id NUMBER;
97
98
99 BEGIN
100
101 IF x_org_id IS NULL THEN
102 FOR rec IN org_cur LOOP
103 l_org_id := rec.org_id;
104 END LOOP;
105 ELSE
106 l_org_id := x_org_id;
107 END IF;
108
109
110 SELECT pn_phones_s.nextval
111 INTO x_phone_id
112 FROM dual;
113
114 INSERT INTO pn_phones_all
115 (
116 phone_id,
117 last_update_date,
118 last_updated_by,
119 creation_date,
120 created_by,
121 phone_number,
122 status,
123 phone_type,
124 last_update_login,
125 contact_id,
126 area_code,
127 extension,
128 primary_flag,
129 attribute_category,
130 attribute1,
131 attribute2,
132 attribute3,
133 attribute4,
134 attribute5,
135 attribute6,
136 attribute7,
137 attribute8,
138 attribute9,
139 attribute10,
140 attribute11,
141 attribute12,
142 attribute13,
143 attribute14,
144 attribute15,
145 org_id
146 )
147 VALUES
148 (
149 X_Phone_Id,
150 X_Last_Update_Date,
151 X_Last_Updated_By,
152 X_Creation_Date,
153 X_Created_By,
154 X_Phone_Number,
155 X_Status,
156 X_Phone_Type,
157 X_Last_Update_Login,
158 X_Contact_Id,
159 X_Area_Code,
160 X_Extension,
161 X_Primary_Flag,
162 X_Attribute_Category,
163 X_Attribute1,
164 X_Attribute2,
165 X_Attribute3,
166 X_Attribute4,
167 X_Attribute5,
168 X_Attribute6,
169 X_Attribute7,
170 X_Attribute8,
171 X_Attribute9,
172 X_Attribute10,
173 X_Attribute11,
174 X_Attribute12,
175 X_Attribute13,
176 X_Attribute14,
177 X_Attribute15,
178 l_Org_id
179 );
180
181 OPEN C;
182 FETCH C INTO X_Rowid;
183 IF (C%NOTFOUND) THEN
184 CLOSE C;
185 RAISE NO_DATA_FOUND;
186 END IF;
187 CLOSE C;
188
189 END Insert_Row;
190
191 -------------------------------------------------------------------------------
192 -- PROCDURE : Lock_Row
193 -- INVOKED FROM : Lock_Row procedure
194 -- PURPOSE : locks the row
195 -- HISTORY :
196 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_phones with _ALL table.
197 -- Also changed the where clause
198 -------------------------------------------------------------------------------
199 PROCEDURE Lock_Row ( X_Rowid VARCHAR2,
200 X_Phone_Number VARCHAR2,
201 X_Status VARCHAR2,
202 X_Phone_Type VARCHAR2,
203 X_Area_Code VARCHAR2,
204 X_Extension VARCHAR2,
205 X_Primary_Flag VARCHAR2,
206 X_Attribute_Category VARCHAR2,
207 X_Attribute1 VARCHAR2,
208 X_Attribute2 VARCHAR2,
209 X_Attribute3 VARCHAR2,
210 X_Attribute4 VARCHAR2,
211 X_Attribute5 VARCHAR2,
212 X_Attribute6 VARCHAR2,
213 X_Attribute7 VARCHAR2,
214 X_Attribute8 VARCHAR2,
215 X_Attribute9 VARCHAR2,
216 X_Attribute10 VARCHAR2,
217 X_Attribute11 VARCHAR2,
218 X_Attribute12 VARCHAR2,
219 X_Attribute13 VARCHAR2,
220 X_Attribute14 VARCHAR2,
221 X_Attribute15 VARCHAR2
222 ) IS
223 CURSOR C IS
224 SELECT *
225 FROM pn_phones_all
226 WHERE rowid = X_Rowid
227 FOR UPDATE of Phone_Id NOWAIT;
228 Recinfo C%ROWTYPE;
229
230
231 BEGIN
232 OPEN C;
233 FETCH C INTO Recinfo;
234 if (C%NOTFOUND) then
235 CLOSE C;
236 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
237 APP_EXCEPTION.Raise_Exception;
238 end if;
239 CLOSE C;
240
241 IF NOT (Recinfo.phone_number = X_Phone_Number) THEN
242 pn_var_rent_pkg.lock_row_exception('phone_number',Recinfo.phone_number);
243 END IF;
244 IF NOT (Recinfo.status = X_Status) THEN
245 pn_var_rent_pkg.lock_row_exception('status',Recinfo.status);
246 END IF;
247 IF NOT (Recinfo.phone_type = X_Phone_Type) THEN
248 pn_var_rent_pkg.lock_row_exception('phone_type',Recinfo.phone_type);
249 END IF;
250 IF NOT ( (Recinfo.area_code = X_Area_Code)
251 OR ((Recinfo.area_code IS NULL) AND (X_Area_Code IS NULL))) THEN
252 pn_var_rent_pkg.lock_row_exception('area_code',Recinfo.area_code);
253 END IF;
254 IF NOT ( (Recinfo.extension = X_Extension)
255 OR ((Recinfo.extension IS NULL) AND (X_Extension IS NULL))) THEN
256 pn_var_rent_pkg.lock_row_exception('extension',Recinfo.extension);
257 END IF;
258 IF NOT ( (Recinfo.primary_flag = X_Primary_Flag)
259 OR ((Recinfo.primary_flag IS NULL) AND (X_Primary_Flag IS NULL))) THEN
260 pn_var_rent_pkg.lock_row_exception('primary_flag',Recinfo.primary_flag);
261 END IF;
262 IF NOT ( (Recinfo.attribute_category = X_Attribute_Category)
263 OR ((Recinfo.attribute_category IS NULL) AND (X_Attribute_Category IS NULL))) THEN
264 pn_var_rent_pkg.lock_row_exception('attribute_category',Recinfo.attribute_category);
265 END IF;
266 IF NOT ( (Recinfo.attribute1 = X_Attribute1)
267 OR ((Recinfo.attribute1 IS NULL) AND (X_Attribute1 IS NULL))) THEN
268 pn_var_rent_pkg.lock_row_exception('attribute1',Recinfo.attribute1);
269 END IF;
270 IF NOT ( (Recinfo.attribute2 = X_Attribute2)
271 OR ((Recinfo.attribute2 IS NULL) AND (X_Attribute2 IS NULL))) THEN
272 pn_var_rent_pkg.lock_row_exception('attribute2',Recinfo.attribute2);
273 END IF;
274 IF NOT ( (Recinfo.attribute3 = X_Attribute3)
275 OR ((Recinfo.attribute3 IS NULL) AND (X_Attribute3 IS NULL))) THEN
276 pn_var_rent_pkg.lock_row_exception('attribute3',Recinfo.attribute3);
277 END IF;
278 IF NOT ( (Recinfo.attribute4 = X_Attribute4)
279 OR ((Recinfo.attribute4 IS NULL) AND (X_Attribute4 IS NULL))) THEN
280 pn_var_rent_pkg.lock_row_exception('attribute4',Recinfo.attribute4);
281 END IF;
282 IF NOT ( (Recinfo.attribute5 = X_Attribute5)
283 OR ((Recinfo.attribute5 IS NULL) AND (X_Attribute5 IS NULL))) THEN
284 pn_var_rent_pkg.lock_row_exception('attribute5',Recinfo.attribute5);
285 END IF;
286 IF NOT ( (Recinfo.attribute6 = X_Attribute6)
287 OR ((Recinfo.attribute6 IS NULL) AND (X_Attribute6 IS NULL))) THEN
288 pn_var_rent_pkg.lock_row_exception('attribute6',Recinfo.attribute6);
289 END IF;
290 IF NOT ( (Recinfo.attribute7 = X_Attribute7)
291 OR ((Recinfo.attribute7 IS NULL) AND (X_Attribute7 IS NULL))) THEN
292 pn_var_rent_pkg.lock_row_exception('attribute7',Recinfo.attribute7);
293 END IF;
294 IF NOT ( (Recinfo.attribute8 = X_Attribute8)
295 OR ((Recinfo.attribute8 IS NULL) AND (X_Attribute8 IS NULL))) THEN
296 pn_var_rent_pkg.lock_row_exception('attribute8',Recinfo.attribute8);
297 END IF;
298 IF NOT ( (Recinfo.attribute9 = X_Attribute9)
299 OR ((Recinfo.attribute9 IS NULL) AND (X_Attribute9 IS NULL))) THEN
300 pn_var_rent_pkg.lock_row_exception('attribute9',Recinfo.attribute9);
301 END IF;
302 IF NOT ( (Recinfo.attribute10 = X_Attribute10)
303 OR ((Recinfo.attribute10 IS NULL) AND (X_Attribute10 IS NULL))) THEN
304 pn_var_rent_pkg.lock_row_exception('attribute10',Recinfo.attribute10);
305 END IF;
306 IF NOT ( (Recinfo.attribute11 = X_Attribute11)
307 OR ((Recinfo.attribute11 IS NULL) AND (X_Attribute11 IS NULL))) THEN
308 pn_var_rent_pkg.lock_row_exception('attribute11',Recinfo.attribute11);
309 END IF;
310 IF NOT ( (Recinfo.attribute12 = X_Attribute12)
311 OR ((Recinfo.attribute12 IS NULL) AND (X_Attribute12 IS NULL))) THEN
312 pn_var_rent_pkg.lock_row_exception('attribute12',Recinfo.attribute12);
313 END IF;
314 IF NOT ( (Recinfo.attribute13 = X_Attribute13)
315 OR ((Recinfo.attribute13 IS NULL) AND (X_Attribute13 IS NULL))) THEN
316 pn_var_rent_pkg.lock_row_exception('attribute13',Recinfo.attribute13);
317 END IF;
318 IF NOT ( (Recinfo.attribute14 = X_Attribute14)
319 OR ((Recinfo.attribute14 IS NULL) AND (X_Attribute14 IS NULL))) THEN
320 pn_var_rent_pkg.lock_row_exception('attribute14',Recinfo.attribute14);
321 END IF;
322 IF NOT ( (Recinfo.attribute15 = X_Attribute15)
323 OR ((Recinfo.attribute15 IS NULL) AND (X_Attribute15 IS NULL))) THEN
324 pn_var_rent_pkg.lock_row_exception('attribute15',Recinfo.attribute15);
325 END IF;
326
327 END Lock_Row;
328
329 -------------------------------------------------------------------------------
330 -- PROCDURE : Update_Row
331 -- INVOKED FROM : Update_Row procedure
332 -- PURPOSE : updates the row
333 -- HISTORY :
334 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_phones with _ALL table.
335 -- Also changed the where clause
336 -------------------------------------------------------------------------------
337 PROCEDURE Update_Row ( X_Rowid VARCHAR2,
338 X_phone_id NUMBER,
339 X_Last_Update_Date DATE,
340 X_Last_Updated_By NUMBER,
341 X_Phone_Number VARCHAR2,
342 X_Status VARCHAR2,
343 X_Phone_Type VARCHAR2,
344 X_Last_Update_Login NUMBER,
345 X_Contact_Id NUMBER,
346 X_Area_Code VARCHAR2,
347 X_Extension VARCHAR2,
348 X_Primary_Flag VARCHAR2,
349 X_Attribute_Category VARCHAR2,
350 X_Attribute1 VARCHAR2,
351 X_Attribute2 VARCHAR2,
352 X_Attribute3 VARCHAR2,
353 X_Attribute4 VARCHAR2,
354 X_Attribute5 VARCHAR2,
355 X_Attribute6 VARCHAR2,
356 X_Attribute7 VARCHAR2,
357 X_Attribute8 VARCHAR2,
358 X_Attribute9 VARCHAR2,
359 X_Attribute10 VARCHAR2,
360 X_Attribute11 VARCHAR2,
361 X_Attribute12 VARCHAR2,
362 X_Attribute13 VARCHAR2,
363 X_Attribute14 VARCHAR2,
364 X_Attribute15 VARCHAR2
365 ) IS
366 l_id NUMBER;
367
368 BEGIN
369 --
370 --
371 UPDATE pn_phones_all
372 SET
373 last_update_date = X_Last_Update_Date,
374 last_updated_by = X_Last_Updated_By,
375 phone_number = X_Phone_Number,
376 status = X_Status,
377 phone_type = X_Phone_Type,
378 last_update_login = X_Last_Update_Login,
379 area_code = X_Area_Code,
380 extension = X_Extension,
381 primary_flag = X_Primary_Flag,
382 attribute_category = X_Attribute_Category,
383 attribute1 = X_Attribute1,
384 attribute2 = X_Attribute2,
385 attribute3 = X_Attribute3,
386 attribute4 = X_Attribute4,
387 attribute5 = X_Attribute5,
388 attribute6 = X_Attribute6,
389 attribute7 = X_Attribute7,
390 attribute8 = X_Attribute8,
391 attribute9 = X_Attribute9,
392 attribute10 = X_Attribute10,
393 attribute11 = X_Attribute11,
394 attribute12 = X_Attribute12,
395 attribute13 = X_Attribute13,
396 attribute14 = X_Attribute14,
397 attribute15 = X_Attribute15
398 WHERE phone_id = X_phone_id;
399
400 if (SQL%NOTFOUND) then
401 Raise NO_DATA_FOUND;
402 end if;
403 END Update_Row;
404
405 -------------------------------------------------------------------------------
406 -- PROCDURE : delete_row
407 -- INVOKED FROM : delete_row procedure
408 -- PURPOSE : deletes the row
409 -- HISTORY :
410 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_phones with _ALL table.
411 -------------------------------------------------------------------------------
412 PROCEDURE delete_row ( x_rowid VARCHAR2)
413 IS
414 BEGIN
415
416 DELETE FROM pn_phones_all
417 WHERE rowid = x_rowid;
418
419 IF (SQL%NOTFOUND) THEN
420 RAISE NO_DATA_FOUND;
421 END IF;
422
423 END delete_row;
424
425 -------------------------------------------------------------------------------
426 -- PROCDURE : delete_row
427 -- INVOKED FROM : delete_row procedure
428 -- PURPOSE : deletes the row
429 -- NOTE : overloaded this procedure to take PK as In parameter
430 -- HISTORY :
431 -- 04-JUL-05 piagrawa o Bug 4284035 - Created
432 -------------------------------------------------------------------------------
433 PROCEDURE delete_row (x_phone_id IN NUMBER)
434 IS
435 BEGIN
436
437 DELETE FROM pn_phones_all
438 WHERE phone_id = x_phone_id;
439
440 IF (SQL%NOTFOUND)
441 THEN
442 RAISE NO_DATA_FOUND;
443 END IF;
444
445 END delete_row;
446
447 END PNT_PHONE_PKG;