eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.FA_INS_POLICIES_PKG

Source


1 PACKAGE BODY FA_INS_POLICIES_PKG AS
2 /* $Header: faxinspb.pls 120.3 2005/07/25 10:00:56 yyoon ship $ */
3 
4 
5 PROCEDURE Get_Calculation_Method (X_Vendor_id   NUMBER,
6 						    X_Policy_number VARCHAR2,
7 						    X_Asset_policy_id IN OUT NOCOPY NUMBER,
8 						    X_Calculation_method IN OUT NOCOPY VARCHAR2,
9 					         X_Vendor_site_code IN OUT NOCOPY VARCHAR2,
10 						    X_Vendor_Site_id IN OUT NOCOPY NUMBER,
11 				 p_log_level_rec        IN     FA_API_TYPES.log_level_rec_type default null) IS
12 
13  CURSOR C (X_vendor_id NUMBER, X_Policy_number VARCHAR2) IS
14 	SELECT pol.calculation_method,
15 		  pol.Vendor_Site_code,
16 		  pol.vendor_site_id,
17 		  nvl(pol.asset_policy_id,0)
18     FROM    fa_ins_mst_pols_v pol
19     WHERE   pol.vendor_id = X_Vendor_id
20     AND     pol.policy_number = X_Policy_number
21     ;
22 
23 
24 BEGIN
25 
26   OPEN C (X_Vendor_id, X_Policy_number);
27 
28   FETCH C into X_Calculation_method, X_Vendor_site_code, X_vendor_site_id,
29 			X_Asset_policy_id;
30 
31   IF SQL%NOTFOUND THEN
32 	X_Asset_policy_id := 0;
33   END IF;
34 
35   CLOSE C;
36 
37 END Get_Calculation_Method;
38 
39 PROCEDURE Insert_Row(   X_Rowid 		  IN OUT NOCOPY VARCHAR2,
40                         X_Asset_policy_id         IN OUT NOCOPY NUMBER,
41                         X_Asset_id                VARCHAR2,
42                         X_Book_type_code          VARCHAR2,
43                         X_Policy_number           VARCHAR2,
44                         X_Vendor_site_id          NUMBER,
45                         X_Vendor_id               NUMBER,
46                         X_Calculation_method      VARCHAR2,
47 				    X_Current_insurance_value NUMBER,
48                         X_Base_insurance_value    NUMBER,
49                         X_Base_index_date         DATE,
50                         X_Current_price_index_id  NUMBER,
51                         X_Last_indexation_id      NUMBER,
52                         X_Insured_amount          NUMBER,
53 			X_Swiss_Building	  VARCHAR2,
54 			X_Last_update_date	  DATE,
55 			X_Last_updated_by	  VARCHAR2,
56 			X_Last_update_login	  VARCHAR2,
57 			X_Creation_date		  DATE,
58 			X_Created_by		  VARCHAR2,
59 			X_Attribute_category	  VARCHAR2,
60 			X_Attribute1 		  VARCHAR2,
61 			X_Attribute2 		  VARCHAR2,
62 			X_Attribute3		  VARCHAR2,
63 			X_Attribute4		  VARCHAR2,
64 			X_Attribute5		  VARCHAR2,
65 			X_Attribute6		  VARCHAR2,
66 			X_Attribute7		  VARCHAR2,
67 			X_Attribute8		  VARCHAR2,
68 			X_Attribute9		  VARCHAR2,
69 			X_Attribute10   	  VARCHAR2,
70 			X_Attribute11 		  VARCHAR2,
71 			X_Attribute12 		  VARCHAR2,
72 			X_Attribute13		  VARCHAR2,
73 			X_Attribute14 		  VARCHAR2,
74 			X_Attribute15 		  VARCHAR2,
75 			X_Attribute16 		  VARCHAR2,
76 			X_Attribute17 		  VARCHAR2,
77 			X_Attribute18 		  VARCHAR2,
78 			X_Attribute19 		  VARCHAR2,
79 			X_Attribute20 		  VARCHAR2,
80 			p_log_level_rec        IN FA_API_TYPES.log_level_rec_type default null) IS
81 
82 
83 CURSOR C IS SELECT rowid FROM fa_ins_policies
84     WHERE asset_policy_id = X_Asset_policy_id
85     AND policy_number = X_Policy_number
86     AND asset_id = X_Asset_id;
87 
88 CURSOR C1 (X_Policy_Number Varchar2, X_vendor_id NUMBER) IS
89 		   SELECT asset_policy_id
90 		   FROM fa_ins_mst_pols
91              WHERE policy_number = X_Policy_number
92 		   AND Vendor_id = X_Vendor_id;
93 
94 l_asset_policy_id NUMBER;
95 
96 BEGIN
97 
98   OPEN C1(X_Policy_number, X_Vendor_id);
99 
100   FETCH C1 INTO l_asset_policy_id;
101 
102   CLOSE C1;
103 
104   IF l_asset_policy_id is null THEN
105 
106 	SELECT FA_INS_POLICY_S.nextval
107         INTO     X_Asset_policy_id
108 	FROM     dual;
109 
110      l_asset_policy_id := X_Asset_policy_id;
111 
112 	INSERT INTO fa_ins_mst_pols
113 	(asset_policy_id,
114 	 policy_number,
115 	 calculation_method,
116 	 vendor_id,
117 	 vendor_site_id
118      )
119      VALUES
120 	(
121 	 X_asset_policy_id,
122 	 X_Policy_number,
123 	 X_Calculation_method,
124 	 X_Vendor_id,
125 	 X_Vendor_site_id
126 	 );
127 
128   END IF;
129 
130   INSERT INTO fa_ins_policies
131 	(
132 	asset_policy_id,
133 	asset_id,
134 	book_type_code,
135 	policy_number,
136 	current_insurance_value,
137 	base_insurance_value,
138 	base_index_date,
139 	current_price_index_id,
140 	last_indexation_id,
141 	insured_amount,
142 	swiss_building,
143 	last_update_date,
144 	last_updated_by,
145 	last_update_login,
146 	creation_date,
147 	created_by,
148         attribute_category,
149         attribute1,
150         attribute2,
151         attribute3,
152         attribute4,
153         attribute5,
154         attribute6,
155         attribute7,
156         attribute8,
157         attribute9,
158         attribute10,
159         attribute11,
160         attribute12,
161         attribute13,
162         attribute14,
163         attribute15,
164         attribute16,
165         attribute17,
166         attribute18,
167         attribute19,
168         attribute20
169 	)
170 	VALUES
171 	(
172 	L_Asset_policy_id,
173      X_Asset_id,
174 	X_Book_type_code,
175 	X_Policy_number,
176 	X_Current_insurance_value,
177 	X_Base_insurance_value,
178 	X_Base_index_date,
179 	X_Current_price_index_id,
180 	X_last_indexation_id,
181 	X_Insured_amount,
182 	X_Swiss_building,
183 	X_Last_update_date,
184 	X_Last_updated_by,
185 	X_Last_update_login,
186 	X_Creation_date,
187 	X_Created_by,
188         X_Attribute_category,
189         X_Attribute1,
190         X_Attribute2,
191         X_Attribute3,
192         X_Attribute4,
193         X_Attribute5,
194         X_Attribute6,
195         X_Attribute7,
196         X_Attribute8,
197         X_Attribute9,
198         X_Attribute10,
199         X_Attribute11,
200         X_Attribute12,
201         X_Attribute13,
202         X_Attribute14,
203         X_Attribute15,
204         X_Attribute16,
205         X_Attribute17,
206         X_Attribute18,
207         X_Attribute19,
208         X_Attribute20
209 	);
210 
211 -- Check to see whether insert was successful
212 
213 OPEN C;
214 
215 FETCH C INTO X_Rowid;
216 
217 IF C%NOTFOUND THEN
218    CLOSE C;
219    X_Asset_policy_id := NULL;
220    RAISE NO_DATA_FOUND;
221 END IF;
222 
223 CLOSE C;
224 
225 END Insert_Row;
226 
227 
228 PROCEDURE Lock_Row(     X_Rowid                  VARCHAR2,
229                         X_Asset_policy_id        NUMBER,
230                         X_Asset_id               VARCHAR2,
231                         X_Book_type_code         VARCHAR2,
232                         X_Policy_number          VARCHAR2,
233                         X_Vendor_site_id         NUMBER,
234                         X_Vendor_id              NUMBER,
235                         X_Calculation_method     VARCHAR2,
236                         X_Current_insurance_value NUMBER,
237                         X_Base_insurance_value   NUMBER,
238                         X_Base_index_date        DATE,
239                         X_Current_price_index_id NUMBER,
240                         X_Last_indexation_id     NUMBER,
241                         X_Insured_amount         NUMBER,
242 			X_Swiss_building	 VARCHAR2,
243                         X_Attribute_category     VARCHAR2,
244                         X_Attribute1             VARCHAR2,
245                         X_Attribute2             VARCHAR2,
246                         X_Attribute3             VARCHAR2,
247                         X_Attribute4             VARCHAR2,
248                         X_Attribute5             VARCHAR2,
249                         X_Attribute6             VARCHAR2,
250                         X_Attribute7             VARCHAR2,
251                         X_Attribute8             VARCHAR2,
252                         X_Attribute9             VARCHAR2,
253                         X_Attribute10            VARCHAR2,
254                         X_Attribute11            VARCHAR2,
255                         X_Attribute12            VARCHAR2,
256                         X_Attribute13            VARCHAR2,
257                         X_Attribute14            VARCHAR2,
258                         X_Attribute15            VARCHAR2,
259                         X_Attribute16            VARCHAR2,
260                         X_Attribute17            VARCHAR2,
261                         X_Attribute18            VARCHAR2,
262                         X_Attribute19            VARCHAR2,
263                         X_Attribute20            VARCHAR2,
264 			p_log_level_rec   IN     FA_API_TYPES.log_level_rec_type default null) IS
265 
266 		      CURSOR C IS
267 			SELECT 	*
268 			FROM	fa_ins_policies
269 			WHERE	rowid = X_Rowid
270 			FOR UPDATE OF policy_number NOWAIT;
271 
272 		      recinfo C%ROWTYPE;
273 
274 BEGIN
275   OPEN C;
276   FETCH C INTO recinfo;
277   IF C%NOTFOUND THEN
278      CLOSE C;
279      FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
280      APP_EXCEPTION.Raise_Exception;
281   END IF;
282   CLOSE C;
283   IF (
284        	     (recinfo.asset_policy_id = X_Asset_policy_id)
285 	AND  (	(recinfo.asset_id = X_Asset_id)
286 	     OR (   (recinfo.asset_id IS NULL)
287 		AND (X_asset_id IS NULL)))
288 	AND  (	(recinfo.book_type_code = X_Book_type_code)
289 	     OR (   (recinfo.book_type_code IS NULL)
290 		AND (X_Book_type_code IS NULL)))
291 	AND  (	(recinfo.policy_number = X_Policy_number)
292 	     OR (   (recinfo.policy_number IS NULL)
293 		AND (X_Policy_number IS NULL)))
294         AND  (  (recinfo.current_insurance_value = X_current_insurance_value)
295              OR (   (recinfo.current_insurance_value IS NULL)
296                 AND (X_current_insurance_value IS NULL)))
297 	AND  (  (recinfo.base_insurance_value = X_Base_insurance_value)
298 	     OR (   (recinfo.base_insurance_value IS NULL)
299 		AND (X_Base_insurance_value IS NULL)))
300 	AND  (  (recinfo.base_index_date = X_Base_index_date)
301 	     OR (   (recinfo.base_index_date IS NULL)
302 		AND (X_Base_index_date IS NULL)))
303 	AND  (  (recinfo.current_price_index_id = X_Current_price_index_id)
304 	     OR (   (recinfo.current_price_index_id IS NULL)
305 		AND (X_Current_price_index_id IS NULL)))
306 	AND  (  (recinfo.last_indexation_id = X_last_indexation_id)
307 	     OR (   (recinfo.last_indexation_id IS NULL)
308 		AND (X_Last_indexation_id IS NULL)))
309 	AND  (	(recinfo.insured_amount = X_Insured_amount)
310 	     OR (   (recinfo.insured_amount IS NULL)
311 		AND (X_Insured_amount IS NULL)))
312         AND  (  (recinfo.swiss_building = X_Swiss_building)
313              OR (   (recinfo.swiss_building IS NULL)
314                 AND (X_Swiss_building IS NULL)))
315 	AND  (	(recinfo.attribute_category = X_Attribute_category)
316 	     OR (   (recinfo.attribute_category IS NULL)
317 		AND (X_Attribute_category IS NULL)))
318 	AND  (  (recinfo.attribute1 = X_attribute1)
319   	     OR (   (recinfo.attribute1 IS NULL)
320 	        AND (X_Attribute1 IS NULL)))
321 	AND  (  (recinfo.attribute2 = X_attribute2)
322   	     OR (   (recinfo.attribute2 IS NULL)
323 	        AND (X_Attribute2 IS NULL)))
324 	AND  (  (recinfo.attribute3 = X_attribute3)
325   	     OR (   (recinfo.attribute3 IS NULL)
326 	        AND (X_Attribute3 IS NULL)))
327 	AND  (  (recinfo.attribute4 = X_attribute4)
328   	     OR (   (recinfo.attribute4 IS NULL)
329 	        AND (X_Attribute4 IS NULL)))
330 	AND  (  (recinfo.attribute5 = X_attribute5)
331   	     OR (   (recinfo.attribute5 IS NULL)
332 	        AND (X_Attribute5 IS NULL)))
333 	AND  (  (recinfo.attribute6 = X_attribute6)
334   	     OR (   (recinfo.attribute6 IS NULL)
335 	        AND (X_Attribute6 IS NULL)))
336 	AND  (  (recinfo.attribute7 = X_attribute7)
337   	     OR (   (recinfo.attribute7 IS NULL)
338 	        AND (X_Attribute7 IS NULL)))
339 	AND  (  (recinfo.attribute8 = X_attribute8)
340   	     OR (   (recinfo.attribute8 IS NULL)
341 	        AND (X_Attribute8 IS NULL)))
342 	AND  (  (recinfo.attribute9 = X_attribute9)
343   	     OR (   (recinfo.attribute9 IS NULL)
344 	        AND (X_Attribute9 IS NULL)))
345 	AND  (  (recinfo.attribute10 = X_attribute10)
346   	     OR (   (recinfo.attribute10 IS NULL)
347 	        AND (X_Attribute10 IS NULL)))
348 	AND  (  (recinfo.attribute11 = X_attribute11)
349   	     OR (   (recinfo.attribute11 IS NULL)
350 	        AND (X_Attribute11 IS NULL)))
351 	AND  (  (recinfo.attribute12 = X_attribute12)
352   	     OR (   (recinfo.attribute12 IS NULL)
353 	        AND (X_Attribute12 IS NULL)))
354 	AND  (  (recinfo.attribute13 = X_attribute13)
355   	     OR (   (recinfo.attribute13 IS NULL)
356 	        AND (X_Attribute13 IS NULL)))
357 	AND  (  (recinfo.attribute14 = X_attribute14)
358   	     OR (   (recinfo.attribute14 IS NULL)
359 	        AND (X_Attribute14 IS NULL)))
360 	AND  (  (recinfo.attribute15 = X_attribute15)
361   	     OR (   (recinfo.attribute15 IS NULL)
362 	        AND (X_Attribute15 IS NULL)))
363 	AND  (  (recinfo.attribute16 = X_attribute16)
364   	     OR (   (recinfo.attribute16 IS NULL)
365 	        AND (X_Attribute16 IS NULL)))
366 	AND  (  (recinfo.attribute17 = X_attribute17)
367   	     OR (   (recinfo.attribute17 IS NULL)
368 	        AND (X_Attribute17 IS NULL)))
369 	AND  (  (recinfo.attribute18 = X_attribute18)
370   	     OR (   (recinfo.attribute18 IS NULL)
371 	        AND (X_Attribute18 IS NULL)))
372 	AND  (  (recinfo.attribute19 = X_attribute19)
373   	     OR (   (recinfo.attribute19 IS NULL)
374 	        AND (X_Attribute19 IS NULL)))
375 	AND  (  (recinfo.attribute20 = X_attribute20)
376   	     OR (   (recinfo.attribute20 IS NULL)
377 	        AND (X_Attribute20 IS NULL)))) THEN
378      RETURN;
379   ELSE
380      FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
381      APP_EXCEPTION.Raise_Exception;
382   END IF;
383 END Lock_Row;
384 
385 
386 PROCEDURE Update_Row(   X_Rowid                  VARCHAR2,
387                         X_Asset_policy_id        NUMBER,
388                         X_Asset_id               VARCHAR2,
389                         X_Book_type_code         VARCHAR2,
390                         X_Policy_number          VARCHAR2,
391                         X_Vendor_site_id         NUMBER,
392                         X_Vendor_id              NUMBER,
393                         X_Calculation_method     VARCHAR2,
394                         X_Base_insurance_value   NUMBER,
395 			X_Current_insurance_value NUMBER,
396                         X_Base_index_date        DATE,
397                         X_Current_price_index_id NUMBER,
398                         X_Last_indexation_id     NUMBER,
399                         X_Insured_amount         NUMBER,
400 			X_Swiss_building	 VARCHAR2,
401                         X_Last_update_date       DATE,
402                         X_Last_updated_by        VARCHAR2,
403                         X_Last_update_login      VARCHAR2,
404                         X_Creation_date          DATE,
405                         X_Created_by             VARCHAR2,
406                         X_Attribute_category     VARCHAR2,
407                         X_Attribute1              VARCHAR2,
408                         X_Attribute2              VARCHAR2,
409                         X_Attribute3              VARCHAR2,
410                         X_Attribute4              VARCHAR2,
411                         X_Attribute5              VARCHAR2,
412                         X_Attribute6              VARCHAR2,
413                         X_Attribute7              VARCHAR2,
414                         X_Attribute8              VARCHAR2,
415                         X_Attribute9              VARCHAR2,
416                         X_Attribute10             VARCHAR2,
417                         X_Attribute11             VARCHAR2,
418                         X_Attribute12             VARCHAR2,
419                         X_Attribute13             VARCHAR2,
420                         X_Attribute14             VARCHAR2,
421                         X_Attribute15             VARCHAR2,
422                         X_Attribute16             VARCHAR2,
423                         X_Attribute17             VARCHAR2,
424                         X_Attribute18             VARCHAR2,
425                         X_Attribute19             VARCHAR2,
426                         X_Attribute20             VARCHAR2,
427 			p_log_level_rec     IN    FA_API_TYPES.log_level_rec_type default null) IS
428 
429 BEGIN
430 
431   UPDATE fa_ins_policies
432   SET
433      asset_policy_id		= X_Asset_policy_id,
434      asset_id			= X_Asset_id,
435      book_type_code		= X_Book_type_code,
436      policy_number		= X_Policy_number,
437      current_insurance_value    = X_Current_insurance_value,
438      base_insurance_value	= X_Base_insurance_value,
439      base_index_date		= X_Base_index_date,
440      current_price_index_id	= X_Current_price_index_id,
441      last_indexation_id		= X_Last_indexation_id,
442      insured_amount		= X_Insured_amount,
443      swiss_building		= X_Swiss_building,
444      last_update_date		= X_Last_update_date,
445      last_updated_by		= X_Last_updated_by,
446      last_update_login		= X_Last_update_login,
447      creation_date		= X_Creation_date,
448      created_by			= X_Created_by,
449      attribute_category		= X_Attribute_category,
450      attribute1			= X_Attribute1,
451      attribute2			= X_Attribute2,
452      attribute3			= X_Attribute3,
453      attribute4			= X_Attribute4,
454      attribute5			= X_Attribute5,
455      attribute6			= X_Attribute6,
456      attribute7			= X_Attribute7,
457      attribute8			= X_Attribute8,
458      attribute9			= X_Attribute9,
459      attribute10		= X_Attribute10,
460      attribute11		= X_Attribute11,
461      attribute12		= X_Attribute12,
462      attribute13		= X_Attribute13,
463      attribute14		= X_Attribute14,
464      attribute15		= X_Attribute15,
465      attribute16		= X_Attribute16,
466      attribute17		= X_Attribute17,
467      attribute18		= X_Attribute18,
468      attribute19		= X_Attribute19,
469      attribute20		= X_Attribute20
470   WHERE rowid = X_Rowid;
471 
472   IF (SQL%NOTFOUND) THEN
473      RAISE NO_DATA_FOUND;
474   END IF;
475 
476 END Update_Row;
477 
478 
479 PROCEDURE Delete_Row(	X_Rowid		VARCHAR2,
480 			p_log_level_rec IN  FA_API_TYPES.log_level_rec_type default null) IS
481 
482 BEGIN
483 
484 	DELETE FROM fa_ins_policies
485 	WHERE rowid = X_Rowid;
486 
487 	IF (SQL%NOTFOUND) THEN
488 	   RAISE NO_DATA_FOUND;
489 	END IF;
490 
491 END Delete_Row;
492 
493 END FA_INS_POLICIES_PKG;