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.AS_INTEREST_TYPES_PKG

Source


1 package body AS_INTEREST_TYPES_PKG as
2 /* $Header: asxvitpb.pls 120.1 2005/08/23 05:01:56 appldev ship $ */
3 
4 
5 -- This will be called by the Interest Types Forms UI
6 
7 PROCEDURE INSERT_ROW (
8   X_ROWID out nocopy VARCHAR2,
9   X_INTEREST_TYPE_ID in NUMBER,
10   X_MASTER_ENABLED_FLAG in VARCHAR2,
11   X_ENABLED_FLAG in VARCHAR2,
12   X_COMPANY_CLASSIFICATION_FLAG in VARCHAR2,
13   X_CONTACT_INTEREST_FLAG in VARCHAR2,
14   X_LEAD_CLASSIFICATION_FLAG in VARCHAR2,
15   X_EXPECTED_PURCHASE_FLAG in VARCHAR2,
16   X_CURRENT_ENVIRONMENT_FLAG in VARCHAR2,
17   X_ORG_ID in NUMBER,
18   X_INTEREST_TYPE in VARCHAR2,
19   X_DESCRIPTION in VARCHAR2,
20   X_CREATION_DATE in DATE,
21   X_CREATED_BY in NUMBER,
22   X_LAST_UPDATE_DATE in DATE,
23   X_LAST_UPDATED_BY in NUMBER,
24   X_LAST_UPDATE_LOGIN in NUMBER,
25   X_PROD_CAT_SET_ID in NUMBER,
26   X_PROD_CAT_ID in NUMBER
27 ) IS
28 
29 CURSOR C is
30 	SELECT ROWID
31 	FROM AS_INTEREST_TYPES_B
32 	WHERE INTEREST_TYPE_ID = X_INTEREST_TYPE_ID ;
33 
34 BEGIN
35 
36 -- There are three table _B, _TL and _ALL to be inserted
37 
38 -- Inserting into _B table
39 
40 	BEGIN
41 
42 	  INSERT INTO AS_INTEREST_TYPES_B
43   	(
44     		INTEREST_TYPE_ID,
45     		ENABLED_FLAG,
46     		COMPANY_CLASSIFICATION_FLAG,
47     		CONTACT_INTEREST_FLAG,
48     		LEAD_CLASSIFICATION_FLAG,
49     		EXPECTED_PURCHASE_FLAG,
50     		CURRENT_ENVIRONMENT_FLAG,
51     		CREATION_DATE,
52     		CREATED_BY,
53     		LAST_UPDATE_DATE,
54     		LAST_UPDATED_BY,
55     		LAST_UPDATE_LOGIN,
56             PRODUCT_CAT_SET_ID,
57             PRODUCT_CATEGORY_ID
58   	)
59   	VALUES
60   	(
61     		X_INTEREST_TYPE_ID,
62     		X_MASTER_ENABLED_FLAG,
63     		X_COMPANY_CLASSIFICATION_FLAG,
64     		X_CONTACT_INTEREST_FLAG,
65     		X_LEAD_CLASSIFICATION_FLAG,
66     		X_EXPECTED_PURCHASE_FLAG,
67     		X_CURRENT_ENVIRONMENT_FLAG,
68     		X_CREATION_DATE,
69     		X_CREATED_BY,
70     		X_LAST_UPDATE_DATE,
71     		X_LAST_UPDATED_BY,
72     		X_LAST_UPDATE_LOGIN,
73             X_PROD_CAT_SET_ID,
74             X_PROD_CAT_ID
75   	);
76 
77 
78 	EXCEPTION
79 	WHEN OTHERS THEN
80 		RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
81 	END;
82 
83 -- Inserting into _ALL table
84 
85 
86     /* AS_INTEREST_TYPES_ALL Obsoleted
87  	BEGIN
88 
89   		INSERT INTO AS_INTEREST_TYPES_ALL
90   		(
91     			INTEREST_TYPE_ID,
92     			ENABLED_FLAG,
93     			ORG_ID,
94     			CREATION_DATE,
95     			CREATED_BY,
96     			LAST_UPDATE_DATE,
97     			LAST_UPDATED_BY,
98     			LAST_UPDATE_LOGIN
99   		)
100   		VALUES
101   		(
102     		X_INTEREST_TYPE_ID,
103     		X_ENABLED_FLAG,
104     		X_ORG_ID,
105     		X_CREATION_DATE,
106     		X_CREATED_BY,
107     		X_LAST_UPDATE_DATE,
108     		X_LAST_UPDATED_BY,
109     		X_LAST_UPDATE_LOGIN
110   		);
111 
112 	EXCEPTION
113 	WHEN OTHERS THEN
114 		RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115 	END;
116     */
117 
118 
119 -- Inserting the _TL table
120 
121   BEGIN
122 
123   	INSERT INTO AS_INTEREST_TYPES_TL
124   	(
125     	INTEREST_TYPE_ID,
126     	LAST_UPDATE_DATE,
127     	LAST_UPDATED_BY,
128     	CREATION_DATE,
129     	CREATED_BY,
130     	LAST_UPDATE_LOGIN,
131     	INTEREST_TYPE,
132     	DESCRIPTION,
133     	LANGUAGE,
134     	SOURCE_LANG
135   	)
136   	SELECT
137     		X_INTEREST_TYPE_ID,
138     		X_LAST_UPDATE_DATE,
139     		X_LAST_UPDATED_BY,
140     		X_CREATION_DATE,
141     		X_CREATED_BY,
142     		X_LAST_UPDATE_LOGIN,
143     		rtrim(ltrim(X_INTEREST_TYPE)),
144     		X_DESCRIPTION,
145     		L.LANGUAGE_CODE,
146     		userenv('LANG')
147   		FROM FND_LANGUAGES L
148   		WHERE L.INSTALLED_FLAG in ('I', 'B')
149   		and not exists
150     		(select NULL
151     			from AS_INTEREST_TYPES_TL T
152     			where T.INTEREST_TYPE_ID = X_INTEREST_TYPE_ID
153     			and T.LANGUAGE = L.LANGUAGE_CODE);
154 
155 	  	open c;
156   		fetch c into X_ROWID;
157   		if (c%notfound) then
158     			close c;
159     			raise no_data_found;
160   		end if;
161   	close c;
162 
163 	EXCEPTION
164 	WHEN OTHERS THEN
165 		RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
166 	END;
167 
168 
169 
170 EXCEPTION
171 WHEN OTHERS THEN
172 	RAISE NO_DATA_FOUND;
173 END INSERT_ROW;
174 
175 procedure LOCK_ROW (
176   X_INTEREST_TYPE_ID in NUMBER,
177   X_MASTER_ENABLED_FLAG in VARCHAR2,
178   X_ENABLED_FLAG in VARCHAR2,
179   X_COMPANY_CLASSIFICATION_FLAG in VARCHAR2,
180   X_CONTACT_INTEREST_FLAG in VARCHAR2,
181   X_LEAD_CLASSIFICATION_FLAG in VARCHAR2,
182   X_EXPECTED_PURCHASE_FLAG in VARCHAR2,
183   X_CURRENT_ENVIRONMENT_FLAG in VARCHAR2,
184   X_ORG_ID in NUMBER,
185   X_INTEREST_TYPE in VARCHAR2,
186   X_DESCRIPTION in VARCHAR2,
187   X_PROD_CAT_SET_ID in NUMBER,
188   X_PROD_CAT_ID in NUMBER
189 ) is
190   cursor cb is select
191 	 LAST_UPDATE_DATE,
192 	 LAST_UPDATED_BY,
193 	 CREATION_DATE,
194 	 CREATED_BY,
195 	 LAST_UPDATE_LOGIN,
196 	 ENABLED_FLAG,
197 	COMPANY_CLASSIFICATION_FLAG,
198 	CONTACT_INTEREST_FLAG,
199 	LEAD_CLASSIFICATION_FLAG,
200 	EXPECTED_PURCHASE_FLAG,
201 	CURRENT_ENVIRONMENT_FLAG,
202     PRODUCT_CAT_SET_ID, PRODUCT_CATEGORY_ID
203     from AS_INTEREST_TYPES_B
204     where INTEREST_TYPE_ID = X_INTEREST_TYPE_ID
205     for update of INTEREST_TYPE_ID nowait;
206   binfo cb%rowtype;
207 
208   /* AS_INTEREST_TYPES_ALL Obsoleted
209   cursor c is select
210       ENABLED_FLAG,
211 	 ORG_ID
212     from AS_INTEREST_TYPES_ALL
213     where INTEREST_TYPE_ID = X_INTEREST_TYPE_ID
214     and   ORG_ID = X_ORG_ID
215     for update of INTEREST_TYPE_ID nowait;
216 
217   recinfo c%rowtype;
218   */
219 
220   cursor c1 is select
221       INTEREST_TYPE,
222       DESCRIPTION,
223       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
224     from AS_INTEREST_TYPES_TL
225     where INTEREST_TYPE_ID = X_INTEREST_TYPE_ID
226     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
227     for update of INTEREST_TYPE_ID nowait;
228 
229 BEGIN
230   OPEN cb;
231   FETCH cb INTO binfo;
232   IF (cb%notfound) THEN
233     CLOSE cb;
234     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
235     app_exception.raise_exception;
236   END IF;
237   CLOSE cb;
238 
239   /* AS_INTEREST_TYPES_ALL Obsoleted
240   OPEN c;
241   FETCH c INTO recinfo;
242   IF (c%notfound) THEN
243     CLOSE c;
244     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
245     app_exception.raise_exception;
246   END IF;
247   CLOSE c;
248 
249   IF (  (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
250       AND (recinfo.ORG_ID = X_ORG_ID)
251   ) THEN
252     NULL;
253   ELSE
254     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
255     app_exception.raise_exception;
256   END IF;
257   */
258 
259   IF (  (binfo.ENABLED_FLAG = X_MASTER_ENABLED_FLAG)
260       AND (binfo.COMPANY_CLASSIFICATION_FLAG = X_COMPANY_CLASSIFICATION_FLAG)
261       AND (binfo.CONTACT_INTEREST_FLAG = X_CONTACT_INTEREST_FLAG)
262       AND (binfo.LEAD_CLASSIFICATION_FLAG = X_LEAD_CLASSIFICATION_FLAG)
263       AND (binfo.EXPECTED_PURCHASE_FLAG = X_EXPECTED_PURCHASE_FLAG)
264       AND (binfo.CURRENT_ENVIRONMENT_FLAG = X_CURRENT_ENVIRONMENT_FLAG)
265       AND ((binfo.PRODUCT_CATEGORY_ID = X_PROD_CAT_ID)
266         OR ((binfo.PRODUCT_CATEGORY_ID is null) AND
267             (X_PROD_CAT_ID is null)))
268       AND ((binfo.PRODUCT_CAT_SET_ID = X_PROD_CAT_SET_ID)
269         OR ((binfo.PRODUCT_CAT_SET_ID is null) AND
270             (X_PROD_CAT_SET_ID is null)))
271   ) THEN
272     NULL;
273   ELSE
274     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
275     app_exception.raise_exception;
276   END IF;
277 
278   FOR tlinfo IN c1 LOOP
279     IF (tlinfo.BASELANG = 'Y') THEN
280       IF (    (tlinfo.INTEREST_TYPE = X_INTEREST_TYPE)
281           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
282                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
283       ) THEN
284         NULL;
285       ELSE
286         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
287         app_exception.raise_exception;
288       END IF;
289     END IF;
290   END LOOP;
291   RETURN;
292 END LOCK_ROW;
293 
294 PROCEDURE UPDATE_ROW (
295   X_INTEREST_TYPE_ID in NUMBER,
296   X_MASTER_ENABLED_FLAG in VARCHAR2,
297   X_ENABLED_FLAG in VARCHAR2,
298   X_COMPANY_CLASSIFICATION_FLAG in VARCHAR2,
299   X_CONTACT_INTEREST_FLAG in VARCHAR2,
300   X_LEAD_CLASSIFICATION_FLAG in VARCHAR2,
301   X_EXPECTED_PURCHASE_FLAG in VARCHAR2,
302   X_CURRENT_ENVIRONMENT_FLAG in VARCHAR2,
303   X_ORG_ID in NUMBER,
304   X_INTEREST_TYPE in VARCHAR2,
305   X_DESCRIPTION in VARCHAR2,
306   X_LAST_UPDATE_DATE in DATE,
307   X_LAST_UPDATED_BY in NUMBER,
308   X_LAST_UPDATE_LOGIN in NUMBER,
309   X_PROD_CAT_SET_ID in NUMBER,
310   X_PROD_CAT_ID in NUMBER
311 ) is
312 
313 BEGIN
314  BEGIN
315   	UPDATE AS_INTEREST_TYPES_B set
316     		LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
317     		LAST_UPDATED_BY = X_LAST_UPDATED_BY,
318     		LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
319     		ENABLED_FLAG = X_MASTER_ENABLED_FLAG,
320     		COMPANY_CLASSIFICATION_FLAG = X_COMPANY_CLASSIFICATION_FLAG,
321     		CONTACT_INTEREST_FLAG = X_CONTACT_INTEREST_FLAG,
322     		LEAD_CLASSIFICATION_FLAG = X_LEAD_CLASSIFICATION_FLAG,
323     		EXPECTED_PURCHASE_FLAG = X_EXPECTED_PURCHASE_FLAG,
324     		CURRENT_ENVIRONMENT_FLAG = X_CURRENT_ENVIRONMENT_FLAG,
325             PRODUCT_CAT_SET_ID =  X_PROD_CAT_SET_ID ,
326             PRODUCT_CATEGORY_ID = X_PROD_CAT_ID
327   		WHERE INTEREST_TYPE_ID = X_INTEREST_TYPE_ID;
328 
329   	IF (sql%notfound) THEN
330     		RAISE NO_DATA_FOUND;
331   	END IF;
332 
333 
334 	EXCEPTION
335 	WHEN OTHERS THEN
336 		RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
337 	END;
338 
339  /* AS_INTEREST_TYPES_ALL Obsoleted
340  BEGIN
341   UPDATE AS_INTEREST_TYPES_ALL
342   SET
343     ENABLED_FLAG = X_ENABLED_FLAG,
344     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
345     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
346     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
347   WHERE INTEREST_TYPE_ID = X_INTEREST_TYPE_ID
348   AND 	ORG_ID = X_ORG_ID;
349 
350   IF (sql%notfound) THEN
351  --   RAISE NO_DATA_FOUND;
352  -- If _ALL table cannot find the row, then create a record in _ALL
353      INSERT INTO AS_INTEREST_TYPES_ALL
354      ( INTEREST_TYPE_ID,
355        ENABLED_FLAG,
356        ORG_ID,
357        CREATION_DATE,
358        CREATED_BY,
359        LAST_UPDATE_DATE,
360        LAST_UPDATED_BY,
361        LAST_UPDATE_LOGIN )
362      VALUES
363      ( X_INTEREST_TYPE_ID,
364        X_ENABLED_FLAG,
365        X_ORG_ID,
366        SYSDATE,
367        X_LAST_UPDATED_BY,
368        SYSDATE,
369        X_LAST_UPDATED_BY,
370        X_LAST_UPDATE_LOGIN
371      );
372 
373   END IF;
374 
375 	EXCEPTION
376 	WHEN OTHERS THEN
377 		RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
378 	END;
379     */
380 
381  BEGIN
382   UPDATE AS_INTEREST_TYPES_TL SET
383     INTEREST_TYPE = X_INTEREST_TYPE,
384     DESCRIPTION = X_DESCRIPTION,
385     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
386     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
387     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
388     SOURCE_LANG = userenv('LANG')
389   WHERE INTEREST_TYPE_ID = X_INTEREST_TYPE_ID
390   AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
391 
392   IF (sql%notfound) THEN
393     RAISE NO_DATA_FOUND;
394   END IF;
395 
396 
397 	EXCEPTION
398 	WHEN OTHERS THEN
399 		RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
400 	END;
401 
402 END UPDATE_ROW;
403 
404 procedure DELETE_ROW (
405   X_INTEREST_TYPE_ID in NUMBER
406 ) is
407 begin
408   delete from AS_INTEREST_TYPES_TL
409   where INTEREST_TYPE_ID = X_INTEREST_TYPE_ID;
410 
411   if (sql%notfound) then
412     raise no_data_found;
413   end if;
414 
415   /* AS_INTEREST_TYPES_ALL Obsoleted
416   delete from AS_INTEREST_TYPES_ALL
417   where INTEREST_TYPE_ID = X_INTEREST_TYPE_ID;
418 
419   if (sql%notfound) then
420     raise no_data_found;
421   end if;
422   */
423 
424   delete from AS_INTEREST_TYPES_B
425   where INTEREST_TYPE_ID = X_INTEREST_TYPE_ID;
426 
427   if (sql%notfound) then
428     raise no_data_found;
429   end if;
430 end DELETE_ROW;
431 
432 procedure TRANSLATE_ROW (
433   X_INTEREST_TYPE_ID in NUMBER,
434   X_INTEREST_TYPE in VARCHAR2,
435   X_DESCRIPTION in VARCHAR2,
436   X_OWNER in VARCHAR2)
437 IS
438 begin
439   -- only update rows that have not been altered by user
443          source_lang = userenv('LANG'),
440    update AS_INTEREST_TYPES_TL
441      set INTEREST_TYPE=X_INTEREST_TYPE,
442 	 DESCRIPTION=X_DESCRIPTION,
444 	    last_update_date = sysdate,
445 	    last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
446 	    last_update_login = 0
447       where INTEREST_TYPE_ID = X_INTEREST_TYPE_ID
448 	 and userenv('LANG') in (language, source_lang);
449 end TRANSLATE_ROW;
450 
451 procedure ADD_LANGUAGE is
452 
453 begin
454   delete from AS_INTEREST_TYPES_TL T
455   where not exists
456     (select NULL
457     from AS_INTEREST_TYPES_B B
458     where B.INTEREST_TYPE_ID = T.INTEREST_TYPE_ID
459     );
460 
461   update AS_INTEREST_TYPES_TL T
462 	set ( INTEREST_TYPE,
463 		DESCRIPTION) = ( select
464 						B.INTEREST_TYPE,
465       					B.DESCRIPTION
466 					  from
467 						AS_INTEREST_TYPES_TL B
468 					  where
469 						B.INTEREST_TYPE_ID = T.INTEREST_TYPE_ID and
470 						B.LANGUAGE = T.SOURCE_LANG )
471 	where
472 	( T.INTEREST_TYPE_ID,
473 	  T.LANGUAGE ) in ( select
474 						SUBT.INTEREST_TYPE_ID,
475 						SUBT.LANGUAGE
476 					from
477 						AS_INTEREST_TYPES_TL SUBB,
478 						AS_INTEREST_TYPES_TL SUBT
479 					where
480 						SUBB.INTEREST_TYPE_ID = SUBT.INTEREST_TYPE_ID and
481 						SUBB.LANGUAGE = SUBT.SOURCE_LANG and
482 						(SUBB.INTEREST_TYPE <> SUBT.INTEREST_TYPE or
483 						SUBB.DESCRIPTION <> SUBT.DESCRIPTION or
484 							(SUBB.DESCRIPTION is null and
485 							SUBT.DESCRIPTION is not null) or
486 						(SUBB.DESCRIPTION is not null and
487 						SUBT.DESCRIPTION is null)));
488 
489   insert into AS_INTEREST_TYPES_TL (
490     INTEREST_TYPE_ID,
491     LAST_UPDATE_DATE,
492     LAST_UPDATED_BY,
493     CREATION_DATE,
494     CREATED_BY,
495     LAST_UPDATE_LOGIN,
496     INTEREST_TYPE,
497     DESCRIPTION,
498     LANGUAGE,
499     SOURCE_LANG
500   )
501   select
502     B.INTEREST_TYPE_ID,
503     B.LAST_UPDATE_DATE,
504     B.LAST_UPDATED_BY,
505     B.CREATION_DATE,
506     B.CREATED_BY,
507     B.LAST_UPDATE_LOGIN,
508     B.INTEREST_TYPE,
509     B.DESCRIPTION,
510     L.LANGUAGE_CODE,
511     B.SOURCE_LANG
512   from
513 	AS_INTEREST_TYPES_TL B,
514 	FND_LANGUAGES L
515   where
516 	L.INSTALLED_FLAG in ('I', 'B') and
517 	B.LANGUAGE = userenv('LANG')
518   and not exists
519     (select NULL
520     from AS_INTEREST_TYPES_TL T
521     where T.INTEREST_TYPE_ID = B.INTEREST_TYPE_ID
522     and T.LANGUAGE = L.LANGUAGE_CODE);
523 END ADD_LANGUAGE;
524 
525 PROCEDURE insert_as_int_types_all (
526   X_INTEREST_TYPE_ID in NUMBER,
527   X_ENABLED_FLAG in VARCHAR2,
528   X_ORG_ID in NUMBER,
529   X_CREATION_DATE in DATE,
530   X_CREATED_BY in NUMBER,
531   X_LAST_UPDATE_DATE in DATE,
532   X_LAST_UPDATED_BY in NUMBER,
533   X_LAST_UPDATE_LOGIN in NUMBER) IS
534 
535 BEGIN
536 
537     /* AS_INTEREST_TYPES_ALL Obsoleted
538   	INSERT INTO AS_INTEREST_TYPES_ALL
539   		(
540     			INTEREST_TYPE_ID,
541     			ENABLED_FLAG,
542     			ORG_ID,
543     			CREATION_DATE,
544     			CREATED_BY,
545     			LAST_UPDATE_DATE,
546     			LAST_UPDATED_BY,
547     			LAST_UPDATE_LOGIN
548   		)
549   	VALUES
550   	(
551     		X_INTEREST_TYPE_ID,
552     		X_ENABLED_FLAG,
553     		X_ORG_ID,
554     		X_CREATION_DATE,
555     		X_CREATED_BY,
556     		X_LAST_UPDATE_DATE,
557     		X_LAST_UPDATED_BY,
558     		X_LAST_UPDATE_LOGIN
559   	);
560     */
561     NULL;
562 
563 
564 EXCEPTION
565 	WHEN OTHERS THEN
566 		RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
567 END insert_as_int_types_all ;
568 
569 END AS_INTEREST_TYPES_PKG;