[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_METHODOLOGY_PKG
Source
1 PACKAGE body AS_SALES_METHODOLOGY_PKG as
2 /* $Header: asxsmohb.pls 120.1 2005/06/05 22:52:49 appldev $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5 X_SALES_METHODOLOGY_ID IN NUMBER,
6 X_SALES_METHODOLOGY_NAME IN VARCHAR2,
7 X_START_DATE_ACTIVE IN DATE,
8 X_END_DATE_ACTIVE IN DATE,
9 X_AUTOCREATETASK_FLAG IN VARCHAR2,
10 X_DESCRIPTION IN VARCHAR2,
11 X_ATTRIBUTE1 IN VARCHAR2,
12 X_ATTRIBUTE2 IN VARCHAR2,
13 X_ATTRIBUTE3 IN VARCHAR2,
14 X_ATTRIBUTE4 IN VARCHAR2,
15 X_ATTRIBUTE5 IN VARCHAR2,
16 X_ATTRIBUTE6 IN VARCHAR2,
17 X_ATTRIBUTE7 IN VARCHAR2,
18 X_ATTRIBUTE8 IN VARCHAR2,
19 X_ATTRIBUTE9 IN VARCHAR2,
20 X_ATTRIBUTE10 IN VARCHAR2,
21 X_ATTRIBUTE11 IN VARCHAR2,
22 X_ATTRIBUTE12 IN VARCHAR2,
23 X_ATTRIBUTE13 IN VARCHAR2,
24 X_ATTRIBUTE14 IN VARCHAR2,
25 X_ATTRIBUTE15 IN VARCHAR2,
26 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
27 X_CREATION_DATE IN DATE,
28 X_CREATED_BY IN NUMBER,
29 X_LAST_UPDATE_DATE IN DATE,
30 X_LAST_UPDATED_BY IN NUMBER,
31 X_LAST_UPDATE_LOGIN IN NUMBER)
32 IS
33 CURSOR C IS SELECT ROWID FROM AS_SALES_METHODOLOGY_B
34 WHERE SALES_METHODOLOGY_ID = X_SALES_METHODOLOGY_ID
35 ;
36 BEGIN
37 INSERT INTO AS_SALES_METHODOLOGY_B (
38 SALES_METHODOLOGY_ID,
39 START_DATE_ACTIVE,
40 END_DATE_ACTIVE,
41 AUTOCREATETASK_FLAG,
42 ATTRIBUTE1,
43 ATTRIBUTE2,
44 ATTRIBUTE3,
45 ATTRIBUTE4,
46 ATTRIBUTE5,
47 ATTRIBUTE6,
48 ATTRIBUTE7,
49 ATTRIBUTE8,
50 ATTRIBUTE9,
51 ATTRIBUTE10,
52 ATTRIBUTE11,
53 ATTRIBUTE12,
54 ATTRIBUTE13,
55 ATTRIBUTE14,
56 ATTRIBUTE15,
57 ATTRIBUTE_CATEGORY,
58 CREATION_DATE,
59 CREATED_BY,
60 LAST_UPDATE_DATE,
61 LAST_UPDATED_BY,
62 LAST_UPDATE_LOGIN,
63 OBJECT_VERSION_NUMBER
64 ) VALUES (
65 X_SALES_METHODOLOGY_ID,
66 X_START_DATE_ACTIVE,
67 X_END_DATE_ACTIVE,
68 X_AUTOCREATETASK_FLAG,
69 X_ATTRIBUTE1,
70 X_ATTRIBUTE2,
71 X_ATTRIBUTE3,
72 X_ATTRIBUTE4,
73 X_ATTRIBUTE5,
74 X_ATTRIBUTE6,
75 X_ATTRIBUTE7,
76 X_ATTRIBUTE8,
77 X_ATTRIBUTE9,
78 X_ATTRIBUTE10,
79 X_ATTRIBUTE11,
80 X_ATTRIBUTE12,
81 X_ATTRIBUTE13,
82 X_ATTRIBUTE14,
83 X_ATTRIBUTE15,
84 X_ATTRIBUTE_CATEGORY,
85 X_CREATION_DATE,
86 X_CREATED_BY,
87 X_LAST_UPDATE_DATE,
88 X_LAST_UPDATED_BY,
89 X_LAST_UPDATE_LOGIN,
90 1
91 );
92
93 INSERT INTO AS_SALES_METHODOLOGY_TL (
94 SALES_METHODOLOGY_ID,
95 SALES_METHODOLOGY_NAME,
96 DESCRIPTION,
97 CREATED_BY,
98 CREATION_DATE,
99 LAST_UPDATED_BY,
100 LAST_UPDATE_DATE,
101 LAST_UPDATE_LOGIN,
102 LANGUAGE,
103 SOURCE_LANG
104 ) SELECT
105 X_SALES_METHODOLOGY_ID,
106 X_SALES_METHODOLOGY_NAME,
107 X_DESCRIPTION,
108 X_CREATED_BY,
109 X_CREATION_DATE,
110 X_LAST_UPDATED_BY,
111 X_LAST_UPDATE_DATE,
112 X_LAST_UPDATE_LOGIN,
113 L.LANGUAGE_CODE,
114 userenv('LANG')
115 FROM FND_LANGUAGES L
116 WHERE L.INSTALLED_FLAG IN ('I', 'B')
117 AND NOT EXISTS
118 (SELECT NULL
119 FROM AS_SALES_METHODOLOGY_TL T
120 WHERE T.SALES_METHODOLOGY_ID = X_SALES_METHODOLOGY_ID
121 AND T.LANGUAGE = L.LANGUAGE_CODE);
122
123 OPEN c;
124 FETCH c INTO X_ROWID;
125 IF (c%NOTFOUND) THEN
126 CLOSE c;
127 RAISE no_data_found;
128 END IF;
129 CLOSE c;
130
131 END INSERT_ROW;
132
133 procedure LOCK_ROW (
134 X_SALES_METHODOLOGY_ID in NUMBER,
135 X_OBJECT_VERSION_NUMBER in NUMBER
136 ) is
137 cursor c is select
138 OBJECT_VERSION_NUMBER
139 from AS_SALES_METHODOLOGY_B
140 where SALES_METHODOLOGY_ID = X_SALES_METHODOLOGY_ID
141 and OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
142 for update of SALES_METHODOLOGY_ID nowait;
143 recinfo c%rowtype;
144
145
146 begin
147 open c;
148 fetch c into recinfo;
149 if (c%notfound) then
150 close c;
151 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
152 app_exception.raise_exception;
153 end if;
154
155 close c;
156
157 if recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER then
158 null;
159 else
160 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
161 app_exception.raise_exception;
162 end if;
163
164 end LOCK_ROW;
165
166 PROCEDURE UPDATE_ROW (
167 X_SALES_METHODOLOGY_ID IN NUMBER,
168 X_OBJECT_VERSION_NUMBER IN NUMBER,
169 X_SALES_METHODOLOGY_NAME IN VARCHAR2,
170 X_START_DATE_ACTIVE IN DATE,
171 X_END_DATE_ACTIVE IN DATE,
172 X_AUTOCREATETASK_FLAG IN VARCHAR2,
173 X_DESCRIPTION IN VARCHAR2,
174 X_ATTRIBUTE1 IN VARCHAR2,
175 X_ATTRIBUTE2 IN VARCHAR2,
176 X_ATTRIBUTE3 IN VARCHAR2,
177 X_ATTRIBUTE4 IN VARCHAR2,
178 X_ATTRIBUTE5 IN VARCHAR2,
179 X_ATTRIBUTE6 IN VARCHAR2,
180 X_ATTRIBUTE7 IN VARCHAR2,
181 X_ATTRIBUTE8 IN VARCHAR2,
182 X_ATTRIBUTE9 IN VARCHAR2,
183 X_ATTRIBUTE10 IN VARCHAR2,
184 X_ATTRIBUTE11 IN VARCHAR2,
185 X_ATTRIBUTE12 IN VARCHAR2,
186 X_ATTRIBUTE13 IN VARCHAR2,
187 X_ATTRIBUTE14 IN VARCHAR2,
188 X_ATTRIBUTE15 IN VARCHAR2,
189 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
190 X_LAST_UPDATE_DATE IN DATE,
191 X_LAST_UPDATED_BY IN NUMBER,
192 X_LAST_UPDATE_LOGIN IN NUMBER
193 ) IS
194 BEGIN
195 UPDATE AS_SALES_METHODOLOGY_B SET
196 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1,
197 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
198 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
199 AUTOCREATETASK_FLAG = X_AUTOCREATETASK_FLAG,
200 ATTRIBUTE1 = X_ATTRIBUTE1,
201 ATTRIBUTE2 = X_ATTRIBUTE2,
202 ATTRIBUTE3 = X_ATTRIBUTE3,
203 ATTRIBUTE4 = X_ATTRIBUTE4,
204 ATTRIBUTE5 = X_ATTRIBUTE5,
205 ATTRIBUTE6 = X_ATTRIBUTE6,
206 ATTRIBUTE7 = X_ATTRIBUTE7,
207 ATTRIBUTE8 = X_ATTRIBUTE8,
208 ATTRIBUTE9 = X_ATTRIBUTE9,
209 ATTRIBUTE10 = X_ATTRIBUTE10,
210 ATTRIBUTE11 = X_ATTRIBUTE11,
211 ATTRIBUTE12 = X_ATTRIBUTE12,
212 ATTRIBUTE13 = X_ATTRIBUTE13,
213 ATTRIBUTE14 = X_ATTRIBUTE14,
214 ATTRIBUTE15 = X_ATTRIBUTE15,
215 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
216 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
217 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
218 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
219 WHERE SALES_METHODOLOGY_ID = X_SALES_METHODOLOGY_ID ;
220
221 IF (SQL%NOTFOUND) THEN
222 RAISE no_data_found;
223 END IF;
224
225 UPDATE AS_SALES_METHODOLOGY_TL SET
226 SALES_METHODOLOGY_NAME = X_SALES_METHODOLOGY_NAME,
227 DESCRIPTION = X_DESCRIPTION,
228 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
229 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
230 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
231 SOURCE_LANG = userenv('LANG')
232 WHERE SALES_METHODOLOGY_ID = X_SALES_METHODOLOGY_ID
233 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
234
235 IF (SQL%NOTFOUND) THEN
236 RAISE no_data_found;
237 END IF;
238 END UPDATE_ROW;
239
240 procedure DELETE_ROW (
241 X_SALES_METHODOLOGY_ID in NUMBER
242 ) IS
243 BEGIN
244 DELETE FROM AS_SALES_METHODOLOGY_TL
245 WHERE SALES_METHODOLOGY_ID = X_SALES_METHODOLOGY_ID;
246
247
248 IF (SQL%NOTFOUND) THEN
249 RAISE no_data_found;
250 END IF;
251
252 DELETE FROM AS_SALES_METHODOLOGY_B
253 WHERE SALES_METHODOLOGY_ID = X_SALES_METHODOLOGY_ID ;
254
255 IF (SQL%NOTFOUND) THEN
256 RAISE no_data_found;
257 END IF;
258 END DELETE_ROW;
259
260 procedure ADD_LANGUAGE
261 IS
262 BEGIN
263 DELETE FROM AS_SALES_METHODOLOGY_TL T
264 WHERE NOT EXISTS
265 (SELECT NULL
266 FROM AS_SALES_METHODOLOGY_B B
267 WHERE B.SALES_METHODOLOGY_ID = T.SALES_METHODOLOGY_ID
268 );
269
270 update AS_SALES_METHODOLOGY_TL T set (
271 SALES_METHODOLOGY_NAME,
272 DESCRIPTION
273 ) = (select
274 B.SALES_METHODOLOGY_NAME,
275 B.DESCRIPTION
276 from AS_SALES_METHODOLOGY_TL B
277 where B.SALES_METHODOLOGY_ID = T.SALES_METHODOLOGY_ID
278 and B.LANGUAGE = T.SOURCE_LANG)
279 where (
280 T.SALES_METHODOLOGY_ID,
281 T.LANGUAGE
282 ) in (select
283 SUBT.SALES_METHODOLOGY_ID,
284 SUBT.LANGUAGE
285 from AS_SALES_METHODOLOGY_TL SUBB, AS_SALES_METHODOLOGY_TL SUBT
286 where SUBB.SALES_METHODOLOGY_ID = SUBT.SALES_METHODOLOGY_ID
287 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
288 and (SUBB.SALES_METHODOLOGY_NAME <> SUBT.SALES_METHODOLOGY_NAME
289 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
290 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
291 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
292 ));
293
294 insert into AS_SALES_METHODOLOGY_TL (
295 SALES_METHODOLOGY_ID,
296 SALES_METHODOLOGY_NAME,
297 DESCRIPTION,
298 CREATED_BY,
299 CREATION_DATE,
300 LAST_UPDATED_BY,
301 LAST_UPDATE_DATE,
302 LAST_UPDATE_LOGIN,
303 LANGUAGE,
304 SOURCE_LANG
305 ) select
306 B.SALES_METHODOLOGY_ID,
307 B.SALES_METHODOLOGY_NAME,
308 B.DESCRIPTION,
309 B.CREATED_BY,
310 B.CREATION_DATE,
311 B.LAST_UPDATED_BY,
312 B.LAST_UPDATE_DATE,
313 B.LAST_UPDATE_LOGIN,
314 L.LANGUAGE_CODE,
315 B.SOURCE_LANG
316 from AS_SALES_METHODOLOGY_TL B, FND_LANGUAGES L
317 where L.INSTALLED_FLAG in ('I', 'B')
318 and B.LANGUAGE = userenv('LANG')
319 and not exists
320 (select NULL
321 from AS_SALES_METHODOLOGY_TL T
322 where T.SALES_METHODOLOGY_ID = B.SALES_METHODOLOGY_ID
323 and T.LANGUAGE = L.LANGUAGE_CODE);
324 end ADD_LANGUAGE;
325
326 procedure TRANSLATE_ROW(
327 X_SALES_METHODOLOGY_ID in number,
328 X_SALES_METHODOLOGY_NAME in varchar2,
329 X_DESCRIPTION in varchar2,
330 X_OWNER in varchar2) is
331 begin
332 update AS_SALES_METHODOLOGY_TL set
333 SALES_METHODOLOGY_NAME= nvl(X_SALES_METHODOLOGY_NAME,SALES_METHODOLOGY_NAME),
334 DESCRIPTION= nvl(X_DESCRIPTION,DESCRIPTION),
335 LAST_UPDATE_DATE = sysdate,
336 LAST_UPDATED_BY = decode(X_OWNER, 'SEED', 1, 0),
337 LAST_UPDATE_LOGIN = 0,
338 SOURCE_LANG = userenv('LANG')
339 where SALES_METHODOLOGY_ID = X_SALES_METHODOLOGY_ID
340 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
341
342 if (sql%notfound) then
343 raise no_data_found;
344 end if;
345 end TRANSLATE_ROW;
346
347 end AS_SALES_METHODOLOGY_PKG;