1 package body CS_SR_SAVED_SEARCHES_PKG as
2 /* $Header: csxtssmb.pls 120.0 2006/01/10 11:48:14 jngeorge noship $*/
3
4 procedure INSERT_ROW ( X_ROWID in out nocopy VARCHAR2,
5 X_SEARCH_ID in out nocopy NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_USER_ID in VARCHAR2,
8 X_NAME in VARCHAR2,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_LOGIN in NUMBER) is
14
15 cursor C is select ROWID from CS_SR_SAVED_SEARCHES_B where SEARCH_ID = X_SEARCH_ID ;
16
17 begin
18 select CS_SR_SAVED_SEARCHES_S.NEXTVAL into x_Search_id from dual;
19
20 Insert into CS_SR_SAVED_SEARCHES_B (
21 OBJECT_VERSION_NUMBER,
22 SEARCH_ID,
23 USER_ID,
24 CREATION_DATE,
25 CREATED_BY,
26 LAST_UPDATE_DATE,
27 LAST_UPDATED_BY,
28 LAST_UPDATE_LOGIN )
29 values (
30 X_OBJECT_VERSION_NUMBER,
31 X_SEARCH_ID,
32 X_USER_ID,
33 X_CREATION_DATE,
34 X_CREATED_BY,
35 X_LAST_UPDATE_DATE,
36 X_LAST_UPDATED_BY,
37 X_LAST_UPDATE_LOGIN );
38
39 insert into CS_SR_SAVED_SEARCHES_TL (
40 CREATION_DATE,
41 CREATED_BY,
42 LAST_UPDATE_LOGIN,
43 NAME,
44 SEARCH_ID,
45 LAST_UPDATE_DATE,
46 LAST_UPDATED_BY,
47 LANGUAGE,
48 SOURCE_LANG )
49 select X_CREATION_DATE,
50 X_CREATED_BY,
51 X_LAST_UPDATE_LOGIN,
52 X_NAME,
53 X_SEARCH_ID,
54 X_LAST_UPDATE_DATE,
55 X_LAST_UPDATED_BY,
56 L.LANGUAGE_CODE,
57 userenv('LANG')
58 from FND_LANGUAGES L where L.INSTALLED_FLAG in ('I', 'B')
59 and not exists ( select NULL from CS_SR_SAVED_SEARCHES_TL T
60 where T.SEARCH_ID = X_SEARCH_ID
61 and T.LANGUAGE = L.LANGUAGE_CODE);
62
63 open c;
64 fetch c into X_ROWID;
65
66 if (c%notfound) then
67 close c;
68 raise no_data_found;
69 end if;
70
71 close c;
72
73 end INSERT_ROW;
74
75 procedure DELETE_ROW ( X_SEARCH_ID in NUMBER) is
76 begin
77
78 delete from CS_SR_SAVED_SEARCHES_TL where SEARCH_ID = X_SEARCH_ID;
79
80 if (sql%notfound) then
81 raise no_data_found;
82 end if;
83
84 delete from CS_SR_SAVED_SEARCHES_B where SEARCH_ID = X_SEARCH_ID;
85
86 if (sql%notfound) then
87 raise no_data_found;
88 end if ;
89
90 commit;
91
92 end DELETE_ROW;
93
94 PROCEDURE ADD_LANGUAGE is
95 BEGIN
96
97 delete from CS_SR_SAVED_SEARCHES_TL T
98 where not exists
99 (select NULL from CS_SR_SAVED_SEARCHES_B B
100 where B.SEARCH_ID = T.SEARCH_ID );
101
102 update CS_SR_SAVED_SEARCHES_TL T set (
103 NAME ) = ( select B.NAME from CS_SR_SAVED_SEARCHES_TL B
104 where B.SEARCH_ID = T.SEARCH_ID
105 and B.LANGUAGE = T.SOURCE_LANG)
106 where ( T.SEARCH_ID,T.LANGUAGE ) in
107 (select SUBT.SEARCH_ID, SUBT.LANGUAGE
108 from CS_SR_SAVED_SEARCHES_TL SUBB,
109 CS_SR_SAVED_SEARCHES_TL SUBT
110 where SUBB.SEARCH_ID = SUBT.SEARCH_ID
111 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
112 and (SUBB.NAME <> SUBT.NAME or
113 (SUBB.NAME is null and SUBT.NAME is not null)
114 or (SUBB.NAME is not null and SUBT.NAME is null) ));
115
116 insert into CS_SR_SAVED_SEARCHES_TL (
117 CREATION_DATE,
118 CREATED_BY,
119 LAST_UPDATE_LOGIN,
120 NAME,
121 SECURITY_GROUP_ID,
122 SEARCH_ID,
123 LAST_UPDATE_DATE,
124 LAST_UPDATED_BY,
125 LANGUAGE,
126 SOURCE_LANG )
127 select B.CREATION_DATE,
128 B.CREATED_BY,
129 B.LAST_UPDATE_LOGIN,
130 B.NAME,
131 B.SECURITY_GROUP_ID,
132 B.SEARCH_ID,
133 B.LAST_UPDATE_DATE,
134 B.LAST_UPDATED_BY,
135 L.LANGUAGE_CODE,
136 B.SOURCE_LANG
137 from CS_SR_SAVED_SEARCHES_TL B, FND_LANGUAGES L
138 where L.INSTALLED_FLAG in ('I', 'B') and B.LANGUAGE = userenv('LANG')
139 and not exists (select NULL from CS_SR_SAVED_SEARCHES_TL T
140 where T.SEARCH_ID = B.SEARCH_ID
141 and T.LANGUAGE =L.LANGUAGE_CODE);
142 end ADD_LANGUAGE;
143 end CS_SR_SAVED_SEARCHES_PKG;
[Home] [Help]