[Home] [Help]
PACKAGE BODY: APPS.CSI_SYSTEMS_B_PKG
Source
1 PACKAGE BODY csi_systems_b_pkg AS
2 /* $Header: csitsysb.pls 120.0.12010000.2 2008/11/06 20:30:23 mashah ship $ */
3 -- start of comments
4 -- package name : csi_systems_b_pkg
5 -- purpose :
6 -- history :
7 -- note :
8 -- end of comments
9
10
11 g_pkg_name constant VARCHAR2(30):= 'csi_systems_b_pkg';
12 g_file_name constant VARCHAR2(12) := 'csitsysb.pls';
13
14 PROCEDURE insert_row(
15 px_system_id IN OUT NOCOPY NUMBER ,
16 p_customer_id NUMBER ,
17 p_system_type_code VARCHAR2,
18 p_system_number VARCHAR2,
19 p_parent_system_id NUMBER ,
20 p_ship_to_contact_id NUMBER ,
21 p_bill_to_contact_id NUMBER ,
22 p_technical_contact_id NUMBER ,
23 p_service_admin_contact_id NUMBER ,
24 p_ship_to_site_use_id NUMBER ,
25 p_bill_to_site_use_id NUMBER ,
26 p_install_site_use_id NUMBER ,
27 p_coterminate_day_month VARCHAR2,
28 p_autocreated_from_system_id NUMBER ,
29 p_config_system_type VARCHAR2,
30 p_start_date_active DATE ,
31 p_end_date_active DATE ,
32 p_context VARCHAR2,
33 p_attribute1 VARCHAR2,
34 p_attribute2 VARCHAR2,
35 p_attribute3 VARCHAR2,
36 p_attribute4 VARCHAR2,
37 p_attribute5 VARCHAR2,
38 p_attribute6 VARCHAR2,
39 p_attribute7 VARCHAR2,
40 p_attribute8 VARCHAR2,
41 p_attribute9 VARCHAR2,
42 p_attribute10 VARCHAR2,
43 p_attribute11 VARCHAR2,
44 p_attribute12 VARCHAR2,
45 p_attribute13 VARCHAR2,
46 p_attribute14 VARCHAR2,
47 p_attribute15 VARCHAR2,
48 p_created_by NUMBER ,
49 p_creation_date DATE ,
50 p_last_updated_by NUMBER ,
51 p_last_update_date DATE ,
52 p_last_update_login NUMBER ,
53 p_object_version_number NUMBER ,
54 p_name VARCHAR2,
55 p_description VARCHAR2,
56 p_operating_unit_id NUMBER ,
57 p_request_id NUMBER ,
58 p_program_application_id NUMBER ,
59 p_program_id NUMBER ,
60 p_program_update_date DATE
61 )
62
63
64 is
65 CURSOR c2 IS SELECT csi_systems_s.NEXTVAL FROM sys.dual;
66 BEGIN
67 IF (px_system_id IS NULL) OR (px_system_id = fnd_api.g_miss_num) THEN
68 OPEN c2;
69 FETCH c2 INTO px_system_id;
70 CLOSE c2;
71 END IF;
72 INSERT INTO csi_systems_b(
73 system_id,
74 customer_id,
75 system_type_code,
76 system_number,
77 parent_system_id,
78 ship_to_contact_id,
79 bill_to_contact_id,
80 technical_contact_id,
81 service_admin_contact_id,
82 ship_to_site_use_id,
83 bill_to_site_use_id,
84 install_site_use_id,
85 coterminate_day_month,
86 autocreated_from_system_id,
87 config_system_type,
88 start_date_active,
89 end_date_active,
90 context,
91 attribute1,
92 attribute2,
93 attribute3,
94 attribute4,
95 attribute5,
96 attribute6,
97 attribute7,
98 attribute8,
99 attribute9,
100 attribute10,
101 attribute11,
102 attribute12,
103 attribute13,
104 attribute14,
105 attribute15,
106 created_by,
107 creation_date,
108 last_updated_by,
109 last_update_date,
110 last_update_login,
111 object_version_number,
112 operating_unit_id,
113 request_id,
114 program_application_id,
115 program_id,
116 program_update_date
117 ) VALUES (
118 px_system_id,
119 decode( p_customer_id, fnd_api.g_miss_num, NULL, p_customer_id),
120 decode( p_system_type_code, fnd_api.g_miss_char, NULL, p_system_type_code),
121 decode( p_system_number, fnd_api.g_miss_char, NULL, p_system_number),
122 decode( p_parent_system_id, fnd_api.g_miss_num, NULL, p_parent_system_id),
123 decode( p_ship_to_contact_id, fnd_api.g_miss_num, NULL, p_ship_to_contact_id),
124 decode( p_bill_to_contact_id, fnd_api.g_miss_num, NULL, p_bill_to_contact_id),
125 decode( p_technical_contact_id, fnd_api.g_miss_num, NULL, p_technical_contact_id),
126 decode( p_service_admin_contact_id, fnd_api.g_miss_num, NULL, p_service_admin_contact_id),
127 decode( p_ship_to_site_use_id, fnd_api.g_miss_num, NULL, p_ship_to_site_use_id),
128 decode( p_bill_to_site_use_id, fnd_api.g_miss_num, NULL, p_bill_to_site_use_id),
129 decode( p_install_site_use_id, fnd_api.g_miss_num, NULL, p_install_site_use_id),
130 decode( p_coterminate_day_month, fnd_api.g_miss_char, NULL, p_coterminate_day_month),
131 decode( p_autocreated_from_system_id,fnd_api.g_miss_num, NULL, p_autocreated_from_system_id),
132 decode( p_config_system_type, fnd_api.g_miss_char, NULL, p_config_system_type),
133 decode( p_start_date_active, fnd_api.g_miss_date, to_date(NULL), p_start_date_active),
134 decode( p_end_date_active, fnd_api.g_miss_date, to_date(NULL), p_end_date_active),
135 decode( p_context, fnd_api.g_miss_char, NULL, p_context),
136 decode( p_attribute1, fnd_api.g_miss_char, NULL, p_attribute1),
137 decode( p_attribute2, fnd_api.g_miss_char, NULL, p_attribute2),
138 decode( p_attribute3, fnd_api.g_miss_char, NULL, p_attribute3),
139 decode( p_attribute4, fnd_api.g_miss_char, NULL, p_attribute4),
140 decode( p_attribute5, fnd_api.g_miss_char, NULL, p_attribute5),
141 decode( p_attribute6, fnd_api.g_miss_char, NULL, p_attribute6),
142 decode( p_attribute7, fnd_api.g_miss_char, NULL, p_attribute7),
143 decode( p_attribute8, fnd_api.g_miss_char, NULL, p_attribute8),
144 decode( p_attribute9, fnd_api.g_miss_char, NULL, p_attribute9),
145 decode( p_attribute10, fnd_api.g_miss_char, NULL, p_attribute10),
146 decode( p_attribute11, fnd_api.g_miss_char, NULL, p_attribute11),
147 decode( p_attribute12, fnd_api.g_miss_char, NULL, p_attribute12),
148 decode( p_attribute13, fnd_api.g_miss_char, NULL, p_attribute13),
149 decode( p_attribute14, fnd_api.g_miss_char, NULL, p_attribute14),
150 decode( p_attribute15, fnd_api.g_miss_char, NULL, p_attribute15),
151 decode( p_created_by, fnd_api.g_miss_num, NULL, p_created_by),
152 decode( p_creation_date, fnd_api.g_miss_date, to_date(NULL), p_creation_date),
153 decode( p_last_updated_by, fnd_api.g_miss_num, NULL, p_last_updated_by),
154 decode( p_last_update_date, fnd_api.g_miss_date, to_date(NULL), p_last_update_date),
155 decode( p_last_update_login, fnd_api.g_miss_num, NULL, p_last_update_login),
156 decode( p_object_version_number, fnd_api.g_miss_num, NULL, p_object_version_number),
157 decode( p_operating_unit_id, fnd_api.g_miss_num, NULL, p_operating_unit_id),
158 decode( p_request_id, fnd_api.g_miss_num, NULL, p_request_id),
159 decode( p_program_application_id, fnd_api.g_miss_num, NULL, p_program_application_id),
160 decode( p_program_id, fnd_api.g_miss_num, NULL, p_program_id),
161 decode( p_program_update_date, fnd_api.g_miss_date, to_date(NULL), p_program_update_date)
162 );
163
164 INSERT INTO csi_systems_tl(
165 system_id ,
166 language ,
167 source_lang ,
168 name ,
169 description ,
170 created_by ,
171 creation_date ,
172 last_updated_by ,
173 last_update_date ,
174 last_update_login
175 )
176 SELECT
177 px_system_id,
178 L.language_code,
179 userenv('LANG'),
180 decode( p_name, fnd_api.g_miss_char, NULL, p_name),
181 decode( p_description, fnd_api.g_miss_char, NULL, p_description),
182 decode( p_created_by, fnd_api.g_miss_num, NULL, p_created_by),
183 decode( p_creation_date, fnd_api.g_miss_date, to_date(NULL), p_creation_date),
184 decode( p_last_updated_by, fnd_api.g_miss_num, NULL, p_last_updated_by),
185 decode( p_last_update_date, fnd_api.g_miss_date, to_date(NULL), p_last_update_date),
186 decode( p_last_update_login, fnd_api.g_miss_num, NULL, p_last_update_login)
187 FROM fnd_languages L
188 WHERE L.installed_flag IN ('I','B')
189 AND NOT EXISTS
190 (SELECT NULL
191 FROM csi_systems_tl T
192 WHERE T.system_id=px_system_id
193 AND T.language = L.language_code);
194
195 END insert_row;
196
197 PROCEDURE update_row(
198 p_system_id NUMBER := fnd_api.g_miss_num ,
199 p_customer_id NUMBER := fnd_api.g_miss_num ,
200 p_system_type_code VARCHAR2 := fnd_api.g_miss_char,
201 p_system_number VARCHAR2 := fnd_api.g_miss_char,
202 p_parent_system_id NUMBER := fnd_api.g_miss_num ,
203 p_ship_to_contact_id NUMBER := fnd_api.g_miss_num ,
204 p_bill_to_contact_id NUMBER := fnd_api.g_miss_num ,
205 p_technical_contact_id NUMBER := fnd_api.g_miss_num ,
206 p_service_admin_contact_id NUMBER := fnd_api.g_miss_num ,
207 p_ship_to_site_use_id NUMBER := fnd_api.g_miss_num ,
208 p_bill_to_site_use_id NUMBER := fnd_api.g_miss_num ,
209 p_install_site_use_id NUMBER := fnd_api.g_miss_num ,
210 p_coterminate_day_month VARCHAR2 := fnd_api.g_miss_char,
211 p_autocreated_from_system_id NUMBER := fnd_api.g_miss_num ,
212 p_config_system_type VARCHAR2 := fnd_api.g_miss_char,
213 p_start_date_active DATE := fnd_api.g_miss_date,
214 p_end_date_active DATE := fnd_api.g_miss_date,
215 p_context VARCHAR2 := fnd_api.g_miss_char,
216 p_attribute1 VARCHAR2 := fnd_api.g_miss_char,
217 p_attribute2 VARCHAR2 := fnd_api.g_miss_char,
218 p_attribute3 VARCHAR2 := fnd_api.g_miss_char,
219 p_attribute4 VARCHAR2 := fnd_api.g_miss_char,
220 p_attribute5 VARCHAR2 := fnd_api.g_miss_char,
221 p_attribute6 VARCHAR2 := fnd_api.g_miss_char,
222 p_attribute7 VARCHAR2 := fnd_api.g_miss_char,
223 p_attribute8 VARCHAR2 := fnd_api.g_miss_char,
224 p_attribute9 VARCHAR2 := fnd_api.g_miss_char,
225 p_attribute10 VARCHAR2 := fnd_api.g_miss_char,
226 p_attribute11 VARCHAR2 := fnd_api.g_miss_char,
227 p_attribute12 VARCHAR2 := fnd_api.g_miss_char,
228 p_attribute13 VARCHAR2 := fnd_api.g_miss_char,
229 p_attribute14 VARCHAR2 := fnd_api.g_miss_char,
230 p_attribute15 VARCHAR2 := fnd_api.g_miss_char,
231 p_created_by NUMBER := fnd_api.g_miss_num ,
232 p_creation_date DATE := fnd_api.g_miss_date,
233 p_last_updated_by NUMBER := fnd_api.g_miss_num ,
234 p_last_update_date DATE := fnd_api.g_miss_date,
235 p_last_update_login NUMBER := fnd_api.g_miss_num ,
236 p_object_version_number NUMBER := fnd_api.g_miss_num ,
237 p_name VARCHAR2 := fnd_api.g_miss_char,
238 p_description VARCHAR2 := fnd_api.g_miss_char,
239 p_operating_unit_id NUMBER := fnd_api.g_miss_num,
240 p_request_id NUMBER ,
241 p_program_application_id NUMBER ,
242 p_program_id NUMBER ,
243 p_program_update_date DATE )
244 IS
245 BEGIN
246 update csi_systems_b
247 set
248 customer_id = decode( p_customer_id, fnd_api.g_miss_num, customer_id, p_customer_id),
249 system_type_code = decode( p_system_type_code, fnd_api.g_miss_char, system_type_code, p_system_type_code),
250 system_number = decode( p_system_number, fnd_api.g_miss_char, system_number, p_system_number),
251 parent_system_id = decode( p_parent_system_id, fnd_api.g_miss_num, parent_system_id, p_parent_system_id),
252 ship_to_contact_id = decode( p_ship_to_contact_id, fnd_api.g_miss_num, ship_to_contact_id, p_ship_to_contact_id),
253 bill_to_contact_id = decode( p_bill_to_contact_id, fnd_api.g_miss_num, bill_to_contact_id, p_bill_to_contact_id),
254 technical_contact_id = decode( p_technical_contact_id, fnd_api.g_miss_num, technical_contact_id, p_technical_contact_id),
255 service_admin_contact_id = decode( p_service_admin_contact_id, fnd_api.g_miss_num, service_admin_contact_id, p_service_admin_contact_id),
256 ship_to_site_use_id = decode( p_ship_to_site_use_id, fnd_api.g_miss_num, ship_to_site_use_id, p_ship_to_site_use_id),
257 bill_to_site_use_id = decode( p_bill_to_site_use_id, fnd_api.g_miss_num, bill_to_site_use_id, p_bill_to_site_use_id),
258 install_site_use_id = decode( p_install_site_use_id, fnd_api.g_miss_num, install_site_use_id, p_install_site_use_id),
259 coterminate_day_month = decode( p_coterminate_day_month, fnd_api.g_miss_char, coterminate_day_month, p_coterminate_day_month),
260 autocreated_from_system_id = decode( p_autocreated_from_system_id,fnd_api.g_miss_num, autocreated_from_system_id, p_autocreated_from_system_id),
261 config_system_type = decode(p_config_system_type,fnd_api.g_miss_char, config_system_type, p_config_system_type),
262 start_date_active = decode( p_start_date_active, fnd_api.g_miss_date, start_date_active, p_start_date_active),
263 end_date_active = decode( p_end_date_active, fnd_api.g_miss_date, end_date_active, p_end_date_active),
264 context = decode( p_context, fnd_api.g_miss_char, context, p_context),
265 attribute1 = decode( p_attribute1, fnd_api.g_miss_char, attribute1, p_attribute1),
266 attribute2 = decode( p_attribute2, fnd_api.g_miss_char, attribute2, p_attribute2),
267 attribute3 = decode( p_attribute3, fnd_api.g_miss_char, attribute3, p_attribute3),
268 attribute4 = decode( p_attribute4, fnd_api.g_miss_char, attribute4, p_attribute4),
269 attribute5 = decode( p_attribute5, fnd_api.g_miss_char, attribute5, p_attribute5),
270 attribute6 = decode( p_attribute6, fnd_api.g_miss_char, attribute6, p_attribute6),
271 attribute7 = decode( p_attribute7, fnd_api.g_miss_char, attribute7, p_attribute7),
272 attribute8 = decode( p_attribute8, fnd_api.g_miss_char, attribute8, p_attribute8),
273 attribute9 = decode( p_attribute9, fnd_api.g_miss_char, attribute9, p_attribute9),
274 attribute10 = decode( p_attribute10, fnd_api.g_miss_char, attribute10, p_attribute10),
275 attribute11 = decode( p_attribute11, fnd_api.g_miss_char, attribute11, p_attribute11),
276 attribute12 = decode( p_attribute12, fnd_api.g_miss_char, attribute12, p_attribute12),
277 attribute13 = decode( p_attribute13, fnd_api.g_miss_char, attribute13, p_attribute13),
278 attribute14 = decode( p_attribute14, fnd_api.g_miss_char, attribute14, p_attribute14),
279 attribute15 = decode( p_attribute15, fnd_api.g_miss_char, attribute15, p_attribute15),
280 created_by = decode( p_created_by, fnd_api.g_miss_num, created_by, p_created_by),
281 creation_date = decode( p_creation_date, fnd_api.g_miss_date, creation_date, p_creation_date),
285 object_version_number = object_version_number+1,
282 last_updated_by = decode( p_last_updated_by, fnd_api.g_miss_num, last_updated_by, p_last_updated_by),
283 last_update_date = decode( p_last_update_date, fnd_api.g_miss_date, last_update_date, p_last_update_date),
284 last_update_login = decode( p_last_update_login, fnd_api.g_miss_num, last_update_login, p_last_update_login),
286 operating_unit_id = decode(p_operating_unit_id,fnd_api.g_miss_num,operating_unit_id,p_operating_unit_id),
287 request_id = decode(p_request_id,fnd_api.g_miss_num,request_id,p_request_id),
288 program_application_id = decode(p_program_application_id,fnd_api.g_miss_num,program_application_id,p_program_application_id),
289 program_id = decode(p_program_id,fnd_api.g_miss_num,program_id,p_program_id),
290 program_update_date = decode( p_program_update_date, fnd_api.g_miss_date, program_update_date, p_program_update_date)
291 WHERE system_id = p_system_id;
292
293 UPDATE csi_systems_tl
294 SET source_lang = userenv('LANG'),
295 name = decode( p_name, fnd_api.g_miss_char, name, p_name),
296 description = decode( p_description, fnd_api.g_miss_char, description, p_description),
297 created_by = decode( p_created_by, fnd_api.g_miss_num, created_by, p_created_by),
298 creation_date = decode( p_creation_date, fnd_api.g_miss_date, creation_date, p_creation_date),
299 last_updated_by = decode( p_last_updated_by, fnd_api.g_miss_num, last_updated_by, p_last_updated_by),
300 last_update_date = decode( p_last_update_date, fnd_api.g_miss_date, last_update_date, p_last_update_date),
301 last_update_login = decode( p_last_update_login, fnd_api.g_miss_num, last_update_login, p_last_update_login)
302 WHERE system_id = p_system_id
303 AND userenv('LANG') IN (LANGUAGE,SOURCE_LANG);
304
305 IF (SQL%NOTFOUND) THEN
306 RAISE NO_DATA_FOUND;
307 END IF;
308 END update_row;
309
310
311 PROCEDURE update_row_for_mu(
312 p_system_id NUMBER := fnd_api.g_miss_num ,
313 p_customer_id NUMBER := fnd_api.g_miss_num ,
314 p_system_type_code VARCHAR2 := fnd_api.g_miss_char,
315 p_system_number VARCHAR2 := fnd_api.g_miss_char,
316 p_parent_system_id NUMBER := fnd_api.g_miss_num ,
317 p_ship_to_contact_id NUMBER := fnd_api.g_miss_num ,
318 p_bill_to_contact_id NUMBER := fnd_api.g_miss_num ,
319 p_technical_contact_id NUMBER := fnd_api.g_miss_num ,
320 p_service_admin_contact_id NUMBER := fnd_api.g_miss_num ,
321 p_ship_to_site_use_id NUMBER := fnd_api.g_miss_num ,
322 p_bill_to_site_use_id NUMBER := fnd_api.g_miss_num ,
323 p_install_site_use_id NUMBER := fnd_api.g_miss_num ,
324 p_coterminate_day_month VARCHAR2 := fnd_api.g_miss_char,
325 p_autocreated_from_system_id NUMBER := fnd_api.g_miss_num ,
326 p_config_system_type VARCHAR2 := fnd_api.g_miss_char,
327 p_start_date_active DATE := fnd_api.g_miss_date,
328 p_end_date_active DATE := fnd_api.g_miss_date,
329 p_context VARCHAR2 := fnd_api.g_miss_char,
330 p_attribute1 VARCHAR2 := fnd_api.g_miss_char,
331 p_attribute2 VARCHAR2 := fnd_api.g_miss_char,
332 p_attribute3 VARCHAR2 := fnd_api.g_miss_char,
333 p_attribute4 VARCHAR2 := fnd_api.g_miss_char,
334 p_attribute5 VARCHAR2 := fnd_api.g_miss_char,
335 p_attribute6 VARCHAR2 := fnd_api.g_miss_char,
336 p_attribute7 VARCHAR2 := fnd_api.g_miss_char,
337 p_attribute8 VARCHAR2 := fnd_api.g_miss_char,
338 p_attribute9 VARCHAR2 := fnd_api.g_miss_char,
339 p_attribute10 VARCHAR2 := fnd_api.g_miss_char,
340 p_attribute11 VARCHAR2 := fnd_api.g_miss_char,
341 p_attribute12 VARCHAR2 := fnd_api.g_miss_char,
342 p_attribute13 VARCHAR2 := fnd_api.g_miss_char,
343 p_attribute14 VARCHAR2 := fnd_api.g_miss_char,
344 p_attribute15 VARCHAR2 := fnd_api.g_miss_char,
345 p_created_by NUMBER := fnd_api.g_miss_num ,
346 p_creation_date DATE := fnd_api.g_miss_date,
347 p_last_updated_by NUMBER := fnd_api.g_miss_num ,
348 p_last_update_date DATE := fnd_api.g_miss_date,
349 p_last_update_login NUMBER := fnd_api.g_miss_num ,
350 p_object_version_number NUMBER := fnd_api.g_miss_num ,
351 p_name VARCHAR2 := fnd_api.g_miss_char,
352 p_description VARCHAR2 := fnd_api.g_miss_char,
353 p_operating_unit_id NUMBER := fnd_api.g_miss_num,
354 p_request_id NUMBER ,
355 p_program_application_id NUMBER ,
356 p_program_id NUMBER ,
357 p_program_update_date DATE )
358 IS
359 BEGIN
360 update csi_systems_b
361 set
362 customer_id = decode( p_customer_id, fnd_api.g_miss_num, customer_id, p_customer_id),
363 system_type_code = decode( p_system_type_code, fnd_api.g_miss_char, system_type_code, p_system_type_code),
364 system_number = decode( p_system_number, fnd_api.g_miss_char, system_number, p_system_number),
368 technical_contact_id = null,
365 parent_system_id = decode( p_parent_system_id, fnd_api.g_miss_num, parent_system_id, p_parent_system_id),
366 ship_to_contact_id = null,
367 bill_to_contact_id = null,
369 service_admin_contact_id = null,
370 ship_to_site_use_id = null,
371 bill_to_site_use_id = null,
372 install_site_use_id = null,
373 coterminate_day_month = decode( p_coterminate_day_month, fnd_api.g_miss_char, coterminate_day_month, p_coterminate_day_month),
374 autocreated_from_system_id = decode( p_autocreated_from_system_id,fnd_api.g_miss_num, autocreated_from_system_id, p_autocreated_from_system_id),
375 config_system_type = decode(p_config_system_type,fnd_api.g_miss_char, config_system_type, p_config_system_type),
376 start_date_active = decode( p_start_date_active, fnd_api.g_miss_date, start_date_active, p_start_date_active),
377 end_date_active = decode( p_end_date_active, fnd_api.g_miss_date, end_date_active, p_end_date_active),
378 context = decode( p_context, fnd_api.g_miss_char, context, p_context),
379 attribute1 = decode( p_attribute1, fnd_api.g_miss_char, attribute1, p_attribute1),
380 attribute2 = decode( p_attribute2, fnd_api.g_miss_char, attribute2, p_attribute2),
381 attribute3 = decode( p_attribute3, fnd_api.g_miss_char, attribute3, p_attribute3),
382 attribute4 = decode( p_attribute4, fnd_api.g_miss_char, attribute4, p_attribute4),
383 attribute5 = decode( p_attribute5, fnd_api.g_miss_char, attribute5, p_attribute5),
384 attribute6 = decode( p_attribute6, fnd_api.g_miss_char, attribute6, p_attribute6),
385 attribute7 = decode( p_attribute7, fnd_api.g_miss_char, attribute7, p_attribute7),
386 attribute8 = decode( p_attribute8, fnd_api.g_miss_char, attribute8, p_attribute8),
387 attribute9 = decode( p_attribute9, fnd_api.g_miss_char, attribute9, p_attribute9),
388 attribute10 = decode( p_attribute10, fnd_api.g_miss_char, attribute10, p_attribute10),
389 attribute11 = decode( p_attribute11, fnd_api.g_miss_char, attribute11, p_attribute11),
390 attribute12 = decode( p_attribute12, fnd_api.g_miss_char, attribute12, p_attribute12),
391 attribute13 = decode( p_attribute13, fnd_api.g_miss_char, attribute13, p_attribute13),
392 attribute14 = decode( p_attribute14, fnd_api.g_miss_char, attribute14, p_attribute14),
393 attribute15 = decode( p_attribute15, fnd_api.g_miss_char, attribute15, p_attribute15),
394 created_by = decode( p_created_by, fnd_api.g_miss_num, created_by, p_created_by),
395 creation_date = decode( p_creation_date, fnd_api.g_miss_date, creation_date, p_creation_date),
396 last_updated_by = decode( p_last_updated_by, fnd_api.g_miss_num, last_updated_by, p_last_updated_by),
397 last_update_date = decode( p_last_update_date, fnd_api.g_miss_date, last_update_date, p_last_update_date),
398 last_update_login = decode( p_last_update_login, fnd_api.g_miss_num, last_update_login, p_last_update_login),
399 object_version_number = object_version_number+1,
400 operating_unit_id = decode(p_operating_unit_id,fnd_api.g_miss_num,operating_unit_id,p_operating_unit_id),
401 request_id = decode(p_request_id,fnd_api.g_miss_num,request_id,p_request_id),
402 program_application_id = decode(p_program_application_id,fnd_api.g_miss_num,program_application_id,p_program_application_id),
403 program_id = decode(p_program_id,fnd_api.g_miss_num,program_id,p_program_id),
404 program_update_date = decode( p_program_update_date, fnd_api.g_miss_date, program_update_date, p_program_update_date)
405 WHERE system_id = p_system_id;
406
407 UPDATE csi_systems_tl
408 SET source_lang = userenv('LANG'),
409 name = decode( p_name, fnd_api.g_miss_char, name, p_name),
410 description = decode( p_description, fnd_api.g_miss_char, description, p_description),
411 created_by = decode( p_created_by, fnd_api.g_miss_num, created_by, p_created_by),
412 creation_date = decode( p_creation_date, fnd_api.g_miss_date, creation_date, p_creation_date),
413 last_updated_by = decode( p_last_updated_by, fnd_api.g_miss_num, last_updated_by, p_last_updated_by),
414 last_update_date = decode( p_last_update_date, fnd_api.g_miss_date, last_update_date, p_last_update_date),
415 last_update_login = decode( p_last_update_login, fnd_api.g_miss_num, last_update_login, p_last_update_login)
416 WHERE system_id = p_system_id
417 AND userenv('LANG') IN (LANGUAGE,SOURCE_LANG);
418
419 IF (SQL%NOTFOUND) THEN
420 RAISE NO_DATA_FOUND;
421 END IF;
422 END update_row_for_mu;
423
424 PROCEDURE delete_row(
425 p_system_id NUMBER)
426 is
427 BEGIN
428 delete FROM csi_systems_b
429 WHERE system_id = p_system_id;
430 if (sql%notfound) THEN
431 raise no_data_found;
432 END IF;
433 end delete_row;
434
435 PROCEDURE lock_row(
436 p_system_id NUMBER,
437 p_customer_id NUMBER,
438 p_system_type_code VARCHAR2,
439 p_system_number VARCHAR2,
440 p_parent_system_id NUMBER,
441 p_ship_to_contact_id NUMBER,
442 p_bill_to_contact_id NUMBER,
443 p_technical_contact_id NUMBER,
444 p_service_admin_contact_id NUMBER,
445 p_ship_to_site_use_id NUMBER,
446 p_bill_to_site_use_id NUMBER,
447 p_install_site_use_id NUMBER,
448 p_coterminate_day_month VARCHAR2,
449 p_start_date_active DATE,
450 p_end_date_active DATE,
454 p_attribute3 VARCHAR2,
451 p_context VARCHAR2,
452 p_attribute1 VARCHAR2,
453 p_attribute2 VARCHAR2,
455 p_attribute4 VARCHAR2,
456 p_attribute5 VARCHAR2,
457 p_attribute6 VARCHAR2,
458 p_attribute7 VARCHAR2,
459 p_attribute8 VARCHAR2,
460 p_attribute9 VARCHAR2,
461 p_attribute10 VARCHAR2,
462 p_attribute11 VARCHAR2,
463 p_attribute12 VARCHAR2,
464 p_attribute13 VARCHAR2,
465 p_attribute14 VARCHAR2,
466 p_attribute15 VARCHAR2,
467 p_created_by NUMBER,
468 p_creation_date DATE,
469 p_last_updated_by NUMBER,
470 p_last_update_date DATE,
471 p_last_update_login NUMBER,
472 p_object_version_number NUMBER,
473 p_name VARCHAR2,
474 p_description VARCHAR2,
475 p_operating_unit_id NUMBER)
476
477 is
478 CURSOR c IS
479 SELECT *
480 FROM csi_systems_b
481 WHERE system_id = p_system_id
482 FOR UPDATE OF system_id NOWAIT;
483 recinfo c%rowtype;
484
485 CURSOR c1 IS
486 SELECT name,
487 description,
488 decode(language, userenv('LANG'), 'Y', 'N') baselang
489 FROM csi_systems_tl
490 WHERE system_id = p_system_id
491 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
492 FOR UPDATE OF system_id NOWAIT;
493 BEGIN
494 OPEN c;
495 FETCH c INTO recinfo;
496 IF (c%notfound) THEN
497 CLOSE c;
498 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
499 app_exception.raise_exception;
500 END IF;
501 CLOSE c;
502
503
504 IF (recinfo.object_version_number=p_object_version_number)
505 THEN
506 RETURN;
507 ELSE
508 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
509 app_exception.raise_exception;
510 END IF;
511
512 FOR tlinfo IN c1 LOOP
513 IF (tlinfo.baselang = 'Y') THEN
514 IF ( (tlinfo.name = p_name)
515 AND ((tlinfo.description = p_description)
516 OR ((tlinfo.description IS NULL) AND (p_description IS NULL)))
517 ) THEN
518 NULL;
519 ELSE
520 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
521 app_exception.raise_exception;
522 END IF;
523 END IF;
524 END LOOP;
525 RETURN;
526
527 END lock_row;
528
529
530 PROCEDURE add_language
531 IS
532 BEGIN
533 /********* COMMENTED FOR BUG 4238439 (Refer 3723612 for solution)
534 DELETE FROM csi_systems_tl t
535 WHERE NOT EXISTS
536 (SELECT NULL
537 FROM csi_systems_b b
538 WHERE b.system_id = t.system_id
539 );
540
541 UPDATE csi_systems_tl t
542 SET (name,description) =
543 (SELECT b.name,
544 b.description
545 FROM csi_systems_tl b
546 WHERE b.system_id = t.system_id
547 AND b.language = t.source_lang)
548 WHERE (t.system_id,t.language) IN
549 (SELECT subt.system_id,
550 subt.language
551 FROM csi_systems_tl subb, csi_systems_tl subt
552 WHERE subb.system_id = subt.system_id
553 AND subb.language = subt.source_lang
554 AND (subb.name <> subt.name
555 OR subb.description <> subt.description
556 OR (subb.description IS NULL AND subt.description IS NOT NULL)
557 OR (subb.description iS NOT NULL AND subt.description IS NULL)
558 )
559 );
560 *********** END OF COMMENT **********/
561
562 INSERT /*+ append parallel(tt) */ INTO csi_systems_tl tt (system_id,
563 name,
564 description,
565 last_update_date,
566 last_updated_by,
567 creation_date,
568 created_by,
569 last_update_login,
570 language,
571 source_lang
572 )
573 SELECT /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
574 ( SELECT /*+ no_merge ordered parallel(b) */ b.system_id,
575 b.name,
576 b.description,
577 b.last_update_date,
578 b.last_updated_by,
579 b.creation_date,
580 b.created_by,
581 b.last_update_login,
582 l.language_code,
583 b.source_lang
584 FROM csi_systems_tl b, fnd_languages l
585 WHERE l.installed_flag in ('I', 'B')
586 AND b.language = userenv('LANG')
587 ) v, csi_systems_tl t
588 WHERE t.system_id(+) = v.system_id
589 AND t.language(+) = v.language_code
590 AND t.system_id IS NULL;
591 /***** COMMENTED AND NOT EXISTS
592 (SELECT NULL
593 FROM csi_systems_tl t
594 WHERE t.system_id = b.system_id
595 AND t.language = l.language_code); *****/
596 END add_language;
597
598 PROCEDURE translate_row (
599 p_system_id IN NUMBER ,
600 p_name IN VARCHAR2,
601 p_description IN VARCHAR2,
602 p_owner IN VARCHAR2
603 ) IS
604 BEGIN
605 UPDATE csi_systems_tl
606 SET name = p_name,
607 description = p_description,
608 last_update_date = sysdate,
609 last_updated_by = decode(p_owner, 'SEED', 1, 0),
610 last_update_login = 0,
611 source_lang = userenv('LANG')
612 WHERE system_id = p_system_id
613 AND userenv('LANG') IN (language, source_lang);
614 END translate_row;
615
616
617
618 end csi_systems_b_pkg;