[Home] [Help]
PACKAGE BODY: APPS.CSD_SC_RECOMMENDATIONS_PKG
Source
1 package body CSD_SC_RECOMMENDATIONS_PKG as
2 /* $Header: csdtscrb.pls 120.0 2005/10/26 12:53:18 swai noship $ */
3
4 procedure INSERT_ROW (
5 -- P_ROWID in out nocopy VARCHAR2,
6 PX_SC_RECOMMENDATION_ID in out nocopy NUMBER,
7 P_SC_DOMAIN_ID in NUMBER,
8 P_RECOMMENDATION_TYPE_CODE in VARCHAR2,
9 P_ACTIVE_FROM in DATE,
10 P_ACTIVE_TO in DATE,
11 P_OBJECT_VERSION_NUMBER in NUMBER,
12 P_SC_RECOMMENDATION_NAME in VARCHAR2,
13 P_CREATION_DATE in DATE,
14 P_CREATED_BY in NUMBER,
15 P_LAST_UPDATE_DATE in DATE,
16 P_LAST_UPDATED_BY in NUMBER,
17 P_LAST_UPDATE_LOGIN in NUMBER
18 ) is
19
20 P_ROWID ROWID;
21
22 cursor C is select ROWID from CSD_SC_RECOMMENDATIONS_B
23 where SC_RECOMMENDATION_ID = PX_SC_RECOMMENDATION_ID
24 ;
25
26 begin
27
28 select CSD_SC_RECOMMENDATIONS_S1.nextval
29 into PX_SC_RECOMMENDATION_ID
30 from dual;
31
32 insert into CSD_SC_RECOMMENDATIONS_B (
33 SC_DOMAIN_ID,
34 RECOMMENDATION_TYPE_CODE,
35 ACTIVE_FROM,
36 ACTIVE_TO,
37 OBJECT_VERSION_NUMBER,
38 SC_RECOMMENDATION_ID,
39 CREATION_DATE,
40 CREATED_BY,
41 LAST_UPDATE_DATE,
42 LAST_UPDATED_BY,
43 LAST_UPDATE_LOGIN
44 ) values (
45 P_SC_DOMAIN_ID,
46 P_RECOMMENDATION_TYPE_CODE,
47 P_ACTIVE_FROM,
48 P_ACTIVE_TO,
49 P_OBJECT_VERSION_NUMBER,
50 PX_SC_RECOMMENDATION_ID,
51 P_CREATION_DATE,
52 P_CREATED_BY,
53 P_LAST_UPDATE_DATE,
54 P_LAST_UPDATED_BY,
55 P_LAST_UPDATE_LOGIN
56 );
57
58 insert into CSD_SC_RECOMMENDATIONS_TL (
59 SC_RECOMMENDATION_ID,
60 SC_RECOMMENDATION_NAME,
61 CREATED_BY,
62 CREATION_DATE,
63 LAST_UPDATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATE_LOGIN,
66 LANGUAGE,
67 SOURCE_LANG
68 ) select
69 PX_SC_RECOMMENDATION_ID,
70 P_SC_RECOMMENDATION_NAME,
71 P_CREATED_BY,
72 P_CREATION_DATE,
73 P_LAST_UPDATED_BY,
74 P_LAST_UPDATE_DATE,
75 P_LAST_UPDATE_LOGIN,
76 L.LANGUAGE_CODE,
77 userenv('LANG')
78 from FND_LANGUAGES L
79 where L.INSTALLED_FLAG in ('I', 'B')
80 and not exists
81 (select NULL
82 from CSD_SC_RECOMMENDATIONS_TL T
83 where T.SC_RECOMMENDATION_ID = PX_SC_RECOMMENDATION_ID
84 and T.LANGUAGE = L.LANGUAGE_CODE);
85
86 open c;
87 fetch c into P_ROWID;
88 if (c%notfound) then
89 close c;
90 raise no_data_found;
91 end if;
92 close c;
93
94 end INSERT_ROW;
95
96 procedure LOCK_ROW (
97 P_SC_RECOMMENDATION_ID in NUMBER,
98 P_OBJECT_VERSION_NUMBER in NUMBER
99 ) is
100 cursor c is select
101 OBJECT_VERSION_NUMBER
102 from CSD_SC_RECOMMENDATIONS_B
103 where SC_RECOMMENDATION_ID = P_SC_RECOMMENDATION_ID
104 for update of SC_RECOMMENDATION_ID nowait;
105 recinfo c%rowtype;
106
107 begin
108
109 open c;
110 fetch c into recinfo;
111 if (c%notfound) then
112 close c;
113 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
114 app_exception.raise_exception;
115 end if;
116 close c;
117
118 if (recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER) then
119 null;
120 else
121 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
122 app_exception.raise_exception;
123 end if;
124
125 /*
126 for tlinfo in c1 loop
127 if (tlinfo.BASELANG = 'Y') then
128 if ( ((tlinfo.DESCRIPTION = P_DESCRIPTION)
129 OR ((tlinfo.DESCRIPTION is null) AND (P_DESCRIPTION is null)))
130 ) then
131 null;
132 else
133 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
134 app_exception.raise_exception;
135 end if;
136 end if;
137 end loop;
138 */
139
140 return;
141 end LOCK_ROW;
142
143 procedure UPDATE_ROW (
144 P_SC_RECOMMENDATION_ID in NUMBER,
145 P_SC_DOMAIN_ID in NUMBER,
146 P_RECOMMENDATION_TYPE_CODE in VARCHAR2,
147 P_ACTIVE_FROM in DATE,
148 P_ACTIVE_TO in DATE,
149 P_SC_RECOMMENDATION_NAME in VARCHAR2,
150 P_OBJECT_VERSION_NUMBER in NUMBER,
151 P_LAST_UPDATE_DATE in DATE,
152 P_LAST_UPDATED_BY in NUMBER,
153 P_LAST_UPDATE_LOGIN in NUMBER
154 ) is
155 begin
156 update CSD_SC_RECOMMENDATIONS_B set
157 SC_DOMAIN_ID = P_SC_DOMAIN_ID,
158 RECOMMENDATION_TYPE_CODE = P_RECOMMENDATION_TYPE_CODE,
159 ACTIVE_FROM = P_ACTIVE_FROM,
160 ACTIVE_TO = P_ACTIVE_TO,
161 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER + 1,
162 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
163 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
164 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
165 where SC_RECOMMENDATION_ID = P_SC_RECOMMENDATION_ID AND
166 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER;
167
168 if (sql%notfound) then
169 raise no_data_found;
170 end if;
171
172 update CSD_SC_RECOMMENDATIONS_TL set
173 SC_RECOMMENDATION_NAME = P_SC_RECOMMENDATION_NAME,
174 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
175 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
176 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
177 SOURCE_LANG = userenv('LANG')
178 where SC_RECOMMENDATION_ID = P_SC_RECOMMENDATION_ID
179 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
180
181 if (sql%notfound) then
182 raise no_data_found;
183 end if;
184 end UPDATE_ROW;
185
186 procedure DELETE_ROW (
187 P_SC_RECOMMENDATION_ID in NUMBER
188 ) is
189 begin
190 delete from CSD_SC_RECOMMENDATIONS_TL
191 where SC_RECOMMENDATION_ID = P_SC_RECOMMENDATION_ID;
192
193 if (sql%notfound) then
194 raise no_data_found;
195 end if;
196
197 delete from CSD_SC_RECOMMENDATIONS_B
198 where SC_RECOMMENDATION_ID = P_SC_RECOMMENDATION_ID;
199
200 if (sql%notfound) then
201 raise no_data_found;
202 end if;
203 end DELETE_ROW;
204
205 procedure ADD_LANGUAGE
206 is
207 begin
208 delete from CSD_SC_RECOMMENDATIONS_TL T
209 where not exists
210 (select NULL
211 from CSD_SC_RECOMMENDATIONS_B B
212 where B.SC_RECOMMENDATION_ID = T.SC_RECOMMENDATION_ID
213 );
214
215 update CSD_SC_RECOMMENDATIONS_TL T set (
216 SC_RECOMMENDATION_NAME
217 ) = (select
218 B.SC_RECOMMENDATION_NAME
219 from CSD_SC_RECOMMENDATIONS_TL B
220 where B.SC_RECOMMENDATION_ID = T.SC_RECOMMENDATION_ID
221 and B.LANGUAGE = T.SOURCE_LANG)
222 where (
223 T.SC_RECOMMENDATION_ID,
224 T.LANGUAGE
225 ) in (select
226 SUBT.SC_RECOMMENDATION_ID,
227 SUBT.LANGUAGE
228 from CSD_SC_RECOMMENDATIONS_TL SUBB, CSD_SC_RECOMMENDATIONS_TL SUBT
229 where SUBB.SC_RECOMMENDATION_ID = SUBT.SC_RECOMMENDATION_ID
230 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
231 and (SUBB.SC_RECOMMENDATION_NAME <> SUBT.SC_RECOMMENDATION_NAME
232 or (SUBB.SC_RECOMMENDATION_NAME is null and SUBT.SC_RECOMMENDATION_NAME is not null)
233 or (SUBB.SC_RECOMMENDATION_NAME is not null and SUBT.SC_RECOMMENDATION_NAME is null)
234 ));
235
236 insert into CSD_SC_RECOMMENDATIONS_TL (
237 SC_RECOMMENDATION_ID,
238 SC_RECOMMENDATION_NAME,
239 CREATED_BY,
240 CREATION_DATE,
241 LAST_UPDATED_BY,
242 LAST_UPDATE_DATE,
243 LAST_UPDATE_LOGIN,
244 LANGUAGE,
245 SOURCE_LANG
246 ) select /*+ ORDERED */
247 B.SC_RECOMMENDATION_ID,
248 B.SC_RECOMMENDATION_NAME,
249 B.CREATED_BY,
250 B.CREATION_DATE,
251 B.LAST_UPDATED_BY,
252 B.LAST_UPDATE_DATE,
253 B.LAST_UPDATE_LOGIN,
254 L.LANGUAGE_CODE,
255 B.SOURCE_LANG
256 from CSD_SC_RECOMMENDATIONS_TL B, FND_LANGUAGES L
257 where L.INSTALLED_FLAG in ('I', 'B')
258 and B.LANGUAGE = userenv('LANG')
259 and not exists
260 (select NULL
261 from CSD_SC_RECOMMENDATIONS_TL T
262 where T.SC_RECOMMENDATION_ID = B.SC_RECOMMENDATION_ID
263 and T.LANGUAGE = L.LANGUAGE_CODE);
264 end ADD_LANGUAGE;
265
266 end CSD_SC_RECOMMENDATIONS_PKG;