[Home] [Help]
PACKAGE BODY: APPS.PNT_COMP_PKG
Source
1 PACKAGE BODY PNT_COMP_PKG AS
2 -- $Header: PNTCOMPB.pls 120.2 2005/12/01 03:34:19 appldev ship $
3
4 /*===========================================================================+
5 |
6 | PROCEDURE : check_unique_company_name
7 | DESCRIPTION : This procedure determins if the dame company_name
8 | already_exists
9 | SCOPE : PUBLIC
10 | INVOKED FROM :
11 | ARGUMENTS : IN : p_rowid - rowid of row
12 | p_company_name
13 | p_org_id
14 | OUT: p_warning_flag - Tells calling routine that there
15 | is a non fatal warning on the message stack
16 | RETURNS : null
17 | HISTORY
18 | 16-JUN-98 NTandon o Created
19 | 13-MAY-02 DThota o Multi-Org changes- added p_org_id parameter
20 +===========================================================================*/
21
22 PROCEDURE check_unique_company_name ( p_rowid IN VARCHAR2,
23 p_company_name IN VARCHAR2,
24 p_warning_flag IN OUT NOCOPY VARCHAR2,
25 p_org_id IN NUMBER
26 ) IS
27 dummy number;
28
29 BEGIN
30
31 select 1
32 into dummy
33 from dual
34 where not exists ( select 1
35 from pn_companies_all
36 where name = p_company_name
37 and (( p_rowid is null ) or (rowid <> p_rowid))
38 and org_id = p_org_id
39 );
40 EXCEPTION
41
42 WHEN NO_DATA_FOUND THEN
43 FND_MESSAGE.set_name ('PN','PN_COMP_NAME_ALREADY_EXISTS');
44 p_warning_flag := 'W';
45
46 END check_unique_company_name;
47
48 /*===========================================================================+
49 | PROCEDURE : check_unique_company_number
50 | DESCRIPTION : Raises fatal error if company number is a duplicate
51 | SCOPE : PUBLIC
52 | INVOKED FROM :
53 | ARGUMENTS : IN : p_rowid - rowid of row
54 | p_company_number
55 | p_org_id
56 | OUT : NONE
57 | RETURNS : NONE
58 | NOTES : Use this procedure call in the
59 | PRE-QUERY, POST-QUERY, WHEN-CREATE-RECORD
60 | WHEN-NEW-RECORD-INSTANCE
61 | triggers of the block where OU is exposed
62 | HISTORY
63 | 16-JUN-98 NTandon o Created
64 | 13-MAY-02 DThota o Multi-Org changes- added p_org_id parameter
65 | 28-NOV-05 pikhar o removed nvl around org_id
66 +===========================================================================*/
67
68 PROCEDURE check_unique_company_number ( p_rowid IN VARCHAR2,
69 p_company_number IN VARCHAR2,
70 p_org_id IN NUMBER
71 ) IS
72 dummy number;
73
74 BEGIN
75
76 SELECT 1
77 INTO dummy
78 FROM dual
79 WHERE NOT EXISTS ( SELECT 1
80 FROM pn_companies_all
81 WHERE company_number = p_company_number
82 AND (( p_rowid is null ) or (rowid <> p_rowid))
83 AND org_id = p_org_id
84 );
85
86 EXCEPTION
87
88 WHEN NO_DATA_FOUND THEN
89 FND_MESSAGE.set_name ('PN','PN_COMP_NUMBER_ALREADY_EXISTS');
90 APP_EXCEPTION.raise_exception;
91
92 END check_unique_company_number;
93
94 /*===========================================================================+
95 | PROCEDURE : insert_row
96 | DESCRIPTION : inserts a row in pn_companies_all
97 | SCOPE : PUBLIC
98 | INVOKED FROM :
99 | ARGUMENTS : IN : x_rowid, x_company_id, x_company_number,
100 | x_last_update_date, x_last_updated_by, x_creation_date
101 | x_created_by, x_last_update_login, x_name,
102 | x_enabled_flag, x_parent_company_id ,
103 | x_attribute_category, x_attribute1, x_attribute2,
104 | x_attribute3, x_attribute4 , x_attribute5, x_attribute6
105 | x_attribute7, x_attribute8, x_attribute9, x_attribute10
106 | x_attribute11, x_attribute12, x_attribute13,
107 | x_attribute14, x_attribute15, x_org_id
108 | OUT : NONE
109 | RETURNS : NONE
110 | HISTORY :
111 | 26-APR-05 piagrawa o Modified the select statements to retrieve values
112 | from pn_companies_all instead of pn_companies_all
113 | 28-NOV-05 pikhar o fetched org_id using cursor
114 +===========================================================================*/
115 PROCEDURE insert_row ( x_rowid IN OUT NOCOPY VARCHAR2,
116 x_company_id IN OUT NOCOPY NUMBER,
117 x_company_number IN OUT NOCOPY VARCHAR2,
118 x_last_update_date DATE,
119 x_last_updated_by NUMBER,
120 x_creation_date DATE,
121 x_created_by NUMBER,
122 x_last_update_login NUMBER,
123 x_name VARCHAR2,
124 x_enabled_flag VARCHAR2,
125 x_parent_company_id NUMBER,
126 x_attribute_category VARCHAR2,
127 x_attribute1 VARCHAR2,
128 x_attribute2 VARCHAR2,
129 x_attribute3 VARCHAR2,
130 x_attribute4 VARCHAR2,
131 x_attribute5 VARCHAR2,
132 x_attribute6 VARCHAR2,
133 x_attribute7 VARCHAR2,
134 x_attribute8 VARCHAR2,
135 x_attribute9 VARCHAR2,
136 x_attribute10 VARCHAR2,
137 x_attribute11 VARCHAR2,
138 x_attribute12 VARCHAR2,
139 x_attribute13 VARCHAR2,
140 x_attribute14 VARCHAR2,
141 x_attribute15 VARCHAR2,
142 x_org_id NUMBER
143 ) IS
144
145 CURSOR C is
146 SELECT rowid
147 FROM pn_companies_all
148 WHERE company_id = x_company_id;
149
150 BEGIN
151
152 check_unique_company_number ( x_rowid,
153 x_company_number,
154 x_org_id
155 );
156 -----------------------------------------------------------------
157 -- Allocate the sequence to the primary key company_id
158 -----------------------------------------------------------------
159 select pn_companies_s.nextval
160 into x_company_id
161 from dual;
162
163 IF x_company_number is null then
164
165 select pn_companies_num_s.nextval
166 into x_company_number
167 from dual;
168
169 END IF;
170
171 insert into pn_companies_all (
172 company_id,
173 company_number,
174 last_update_date,
175 last_updated_by,
176 creation_date,
177 created_by,
178 last_update_login,
179 name,
180 enabled_flag,
181 parent_company_id,
182 attribute_category,
183 attribute1,
184 attribute2,
185 attribute3,
186 attribute4,
187 attribute5,
188 attribute6,
189 attribute7,
190 attribute8,
191 attribute9,
192 attribute10,
193 attribute11,
194 attribute12,
195 attribute13,
196 attribute14,
197 attribute15,
198 org_id
199 )
200 values
201 (
202 x_company_id,
203 x_company_number,
204 x_last_update_date,
205 x_last_updated_by,
206 x_creation_date,
207 x_created_by,
208 x_last_update_login,
209 x_name,
210 x_enabled_flag,
211 x_parent_company_id,
212 x_attribute_category,
213 x_attribute1,
214 x_attribute2,
215 x_attribute3,
216 x_attribute4,
217 x_attribute5,
218 x_attribute6,
219 x_attribute7,
220 x_attribute8,
221 x_attribute9,
222 x_attribute10,
223 x_attribute11,
224 x_attribute12,
225 x_attribute13,
226 x_attribute14,
227 x_attribute15,
228 x_org_id
229 );
230
231 OPEN C;
232 FETCH C INTO x_rowid;
233 if (C%NOTFOUND) then
234 CLOSE C;
235 Raise NO_DATA_FOUND;
236 end if;
237 CLOSE C;
238
239 END insert_row;
240
241 -------------------------------------------------------------------------------
242 -- PROCDURE : update_row
243 -- INVOKED FROM : update_row procedure
244 -- PURPOSE : updates the row
245 -- HISTORY :
246 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_companies with _ALL table.
247 -- Also changed the where clause.
248 -------------------------------------------------------------------------------
249 PROCEDURE update_row ( x_rowid VARCHAR2,
250 x_company_id NUMBER,
251 x_company_number VARCHAR2,
252 x_last_update_date DATE,
253 x_last_updated_by NUMBER,
254 x_last_update_login NUMBER,
255 x_name VARCHAR2,
256 x_enabled_flag VARCHAR2,
257 x_parent_company_id NUMBER,
258 x_attribute_category VARCHAR2,
259 x_attribute1 VARCHAR2,
260 x_attribute2 VARCHAR2,
261 x_attribute3 VARCHAR2,
262 x_attribute4 VARCHAR2,
263 x_attribute5 VARCHAR2,
264 x_attribute6 VARCHAR2,
265 x_attribute7 VARCHAR2,
266 x_attribute8 VARCHAR2,
267 x_attribute9 VARCHAR2,
268 x_attribute10 VARCHAR2,
269 x_attribute11 VARCHAR2,
270 x_attribute12 VARCHAR2,
271 x_attribute13 VARCHAR2,
272 x_attribute14 VARCHAR2,
273 x_attribute15 VARCHAR2
274 )
275 IS
276 l_org_id NUMBER;
277
278 BEGIN
279
280 SELECT org_id
281 INTO l_org_id
282 FROM pn_companies_all
283 WHERE company_id = x_company_id;
284
285 check_unique_company_number ( x_rowid,
286 x_company_number,
287 l_org_id
288 );
289 UPDATE pn_companies_all
290 SET company_number = x_company_number,
291 last_update_date = x_last_update_date,
292 last_updated_by = x_last_updated_by,
293 last_update_login = x_last_update_login,
294 name = x_name,
295 enabled_flag = x_enabled_flag,
296 parent_company_id = x_parent_company_id,
297 attribute_category = x_attribute_category,
298 attribute1 = x_attribute1,
299 attribute2 = x_attribute2,
300 attribute3 = x_attribute3,
301 attribute4 = x_attribute4,
302 attribute5 = x_attribute5,
303 attribute6 = x_attribute6,
304 attribute7 = x_attribute7,
305 attribute8 = x_attribute8,
306 attribute9 = x_attribute9,
307 attribute10 = x_attribute10,
308 attribute11 = x_attribute11,
309 attribute12 = x_attribute12,
310 attribute13 = x_attribute13,
311 attribute14 = x_attribute14,
312 attribute15 = x_attribute15
313 WHERE company_id = x_company_id;
314
315 IF (SQL%NOTFOUND) THEN
316 RAISE NO_DATA_FOUND;
317 END IF;
318
319 END update_row;
320
321 -------------------------------------------------------------------------------
322 -- PROCDURE : lock_row
323 -- INVOKED FROM : lock_row procedure
324 -- PURPOSE : loacks the row
325 -- HISTORY :
326 -- 21-JUN-05 piagrawa o Bug 4284035 - Replaced pn_companies with _ALL table.
327 -------------------------------------------------------------------------------
328 PROCEDURE lock_row ( x_rowid VARCHAR2,
329 x_company_id NUMBER,
330 x_company_number VARCHAR2,
331 x_name VARCHAR2,
332 x_enabled_flag VARCHAR2,
333 x_parent_company_id NUMBER,
334 x_attribute_category VARCHAR2,
335 x_attribute1 VARCHAR2,
336 x_attribute2 VARCHAR2,
337 x_attribute3 VARCHAR2,
338 x_attribute4 VARCHAR2,
339 x_attribute5 VARCHAR2,
340 x_attribute6 VARCHAR2,
341 x_attribute7 VARCHAR2,
342 x_attribute8 VARCHAR2,
343 x_attribute9 VARCHAR2,
344 x_attribute10 VARCHAR2,
345 x_attribute11 VARCHAR2,
346 x_attribute12 VARCHAR2,
347 x_attribute13 VARCHAR2,
348 x_attribute14 VARCHAR2,
349 x_attribute15 VARCHAR2
350 ) IS
351 CURSOR C IS
352 SELECT *
353 FROM pn_companies_all
354 WHERE rowid = x_rowid
355 FOR UPDATE OF company_id NOWAIT;
356
357 Recinfo C%ROWTYPE;
358
359
360 BEGIN
361
362 OPEN C;
363 FETCH C INTO Recinfo;
364 IF (C%NOTFOUND) then
365 CLOSE C;
366 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
367 APP_EXCEPTION.Raise_Exception;
368 END IF;
369 CLOSE C;
370
371 IF NOT (Recinfo.company_id = x_company_id) THEN
372 pn_var_rent_pkg.lock_row_exception('company_id',Recinfo.company_id);
373 END IF;
374 IF NOT (Recinfo.company_number = x_company_number) THEN
375 pn_var_rent_pkg.lock_row_exception('company_number',Recinfo.company_number);
376 END IF;
377 IF NOT (Recinfo.name = x_name) THEN
378 pn_var_rent_pkg.lock_row_exception('name',Recinfo.name);
379 END IF;
380 IF NOT (Recinfo.enabled_flag = x_enabled_flag) THEN
381 pn_var_rent_pkg.lock_row_exception('enabled_flag',Recinfo.enabled_flag);
382 END IF;
383 IF NOT ((Recinfo.parent_company_id = x_parent_company_id)
384 or ((Recinfo.parent_company_id is null) and (x_parent_company_id is null))) THEN
385 pn_var_rent_pkg.lock_row_exception('parent_company_id',Recinfo.parent_company_id);
386 END IF;
387 IF NOT ((Recinfo.attribute11 = x_attribute11)
388 or ((Recinfo.attribute11 is null) and (x_attribute11 is null))) THEN
389 pn_var_rent_pkg.lock_row_exception('attribute11',Recinfo.attribute11);
390 END IF;
391 IF NOT ((Recinfo.attribute12 = x_attribute12)
392 or ((Recinfo.attribute12 is null) and (x_attribute12 is null))) THEN
393 pn_var_rent_pkg.lock_row_exception('attribute12',Recinfo.attribute12);
394 END IF;
395 IF NOT ((Recinfo.attribute13 = x_attribute13)
396 or ((Recinfo.attribute13 is null) and (x_attribute13 is null))) THEN
397 pn_var_rent_pkg.lock_row_exception('attribute13',Recinfo.attribute13);
398 END IF;
399 IF NOT ((Recinfo.attribute14 = x_attribute14)
400 or ((Recinfo.attribute14 is null) and (x_attribute14 is null))) THEN
401 pn_var_rent_pkg.lock_row_exception('attribute14',Recinfo.attribute14);
402 END IF;
403 IF NOT ((Recinfo.attribute15 = x_attribute15)
404 or ((Recinfo.attribute15 is null) and (x_attribute15 is null))) THEN
405 pn_var_rent_pkg.lock_row_exception('attribute15',Recinfo.attribute15);
406 END IF;
407 IF NOT ((Recinfo.attribute_category = x_attribute_category)
408 or ((Recinfo.attribute_category is null) and (x_attribute_category is null))) THEN
409 pn_var_rent_pkg.lock_row_exception('attribute_category',Recinfo.attribute_category);
410 END IF;
411 IF NOT ((Recinfo.attribute1 = x_attribute1)
412 or ((Recinfo.attribute1 is null) and (x_attribute1 is null))) THEN
413 pn_var_rent_pkg.lock_row_exception('attribute1',Recinfo.attribute1);
414 END IF;
415 IF NOT ((Recinfo.attribute2 = x_attribute2)
416 or ((Recinfo.attribute2 is null) and (x_attribute2 is null))) THEN
417 pn_var_rent_pkg.lock_row_exception('attribute2',Recinfo.attribute2);
418 END IF;
419 IF NOT ((Recinfo.attribute3 = x_attribute3)
420 or ((Recinfo.attribute3 is null) and (x_attribute3 is null))) THEN
421 pn_var_rent_pkg.lock_row_exception('attribute3',Recinfo.attribute3);
422 END IF;
423 IF NOT ((Recinfo.attribute4 = x_attribute4)
424 or ((Recinfo.attribute4 is null) and (x_attribute4 is null))) THEN
425 pn_var_rent_pkg.lock_row_exception('attribute4',Recinfo.attribute4);
426 END IF;
427 IF NOT ((Recinfo.attribute5 = x_attribute5)
428 or ((Recinfo.attribute5 is null) and (x_attribute5 is null))) THEN
429 pn_var_rent_pkg.lock_row_exception('attribute5',Recinfo.attribute5);
430 END IF;
431 IF NOT ((Recinfo.attribute6 = x_attribute6)
432 or ((Recinfo.attribute6 is null) and (x_attribute6 is null))) THEN
433 pn_var_rent_pkg.lock_row_exception('attribute6',Recinfo.attribute6);
434 END IF;
435 IF NOT ((Recinfo.attribute7 = x_attribute7)
436 or ((Recinfo.attribute7 is null) and (x_attribute7 is null))) THEN
437 pn_var_rent_pkg.lock_row_exception('attribute7',Recinfo.attribute7);
438 END IF;
439 IF NOT ((Recinfo.attribute8 = x_attribute8)
440 or ((Recinfo.attribute8 is null) and (x_attribute8 is null))) THEN
441 pn_var_rent_pkg.lock_row_exception('attribute8',Recinfo.attribute8);
442 END IF;
443 IF NOT ((Recinfo.attribute9 = x_attribute9)
444 or ((Recinfo.attribute9 is null) and (x_attribute9 is null))) THEN
445 pn_var_rent_pkg.lock_row_exception('attribute9',Recinfo.attribute9);
446 END IF;
447 IF NOT ((Recinfo.attribute10 = x_attribute10)
448 or ((Recinfo.attribute10 is null) and (x_attribute10 is null))) THEN
449 pn_var_rent_pkg.lock_row_exception('attribute10',Recinfo.attribute10);
450 END IF;
451
452 END lock_row;
453
454 END PNT_COMP_PKG;