[Home] [Help]
PACKAGE BODY: APPS.CSI_T_TXN_SYSTEMS_PKG
Source
1 PACKAGE BODY CSI_T_TXN_SYSTEMS_PKG AS
2 /* $Header: csittsyb.pls 115.2 2002/11/12 00:26:21 rmamidip noship $ */
3 -- Start of Comments
4 -- Package name : CSI_T_TXN_SYSTEMS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- END of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_T_TXN_SYSTEMS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csittsyb.pls';
13
14 PROCEDURE Insert_Row(
15 px_transaction_system_id IN OUT NOCOPY NUMBER,
16 p_transaction_line_id NUMBER,
17 p_system_name VARCHAR2,
18 p_description VARCHAR2,
19 p_system_type_code VARCHAR2,
20 p_system_number VARCHAR2,
21 p_customer_id NUMBER,
22 p_bill_to_contact_id NUMBER,
23 p_ship_to_contact_id NUMBER,
24 p_technical_contact_id NUMBER,
25 p_service_admin_contact_id NUMBER,
26 p_ship_to_site_use_id NUMBER,
27 p_bill_to_site_use_id NUMBER,
28 p_install_site_use_id NUMBER,
29 p_coterminate_day_month VARCHAR2,
30 p_config_system_type VARCHAR2,
31 p_start_date_active DATE ,
32 p_end_date_active DATE ,
33 p_context VARCHAR2,
34 p_attribute1 VARCHAR2,
35 p_attribute2 VARCHAR2,
36 p_attribute3 VARCHAR2,
37 p_attribute4 VARCHAR2,
38 p_attribute5 VARCHAR2,
39 p_attribute6 VARCHAR2,
40 p_attribute7 VARCHAR2,
41 p_attribute8 VARCHAR2,
42 p_attribute9 VARCHAR2,
43 p_attribute10 VARCHAR2,
44 p_attribute11 VARCHAR2,
45 p_attribute12 VARCHAR2,
46 p_attribute13 VARCHAR2,
47 p_attribute14 VARCHAR2,
48 p_attribute15 VARCHAR2,
49 p_created_by NUMBER,
50 p_creation_date DATE,
51 p_last_updated_by NUMBER,
52 p_last_update_date DATE,
53 p_last_update_login NUMBER,
54 p_object_version_number NUMBER)
55
56 IS
57 CURSOR c2 IS SELECT csi_t_txn_systems_s.nextval FROM sys.dual;
58 BEGIN
59 IF (px_transaction_system_id IS NULL) OR (px_transaction_system_id = fnd_api.g_miss_num) THEN
60 OPEN c2;
61 FETCH c2 INTO px_transaction_system_id;
62 CLOSE c2;
63 END IF;
64 INSERT INTO csi_t_txn_systems(
65 transaction_system_id,
66 transaction_line_id,
67 system_name,
68 description,
69 system_type_code,
70 system_number,
71 customer_id,
72 bill_to_contact_id,
73 ship_to_contact_id,
74 technical_contact_id,
75 service_admin_contact_id,
76 ship_to_site_use_id,
77 bill_to_site_use_id,
78 install_site_use_id,
79 coterminate_day_month,
80 config_system_type,
81 start_date_active ,
82 end_date_active ,
83 context,
84 attribute1,
85 attribute2,
86 attribute3,
87 attribute4,
88 attribute5,
89 attribute6,
90 attribute7,
91 attribute8,
92 attribute9,
93 attribute10,
94 attribute11,
95 attribute12,
96 attribute13,
97 attribute14,
98 attribute15,
99 created_by,
100 creation_date,
101 last_updated_by,
102 last_update_date,
103 last_update_login,
104 object_version_number
105 ) VALUES (
106 px_transaction_system_id,
107 DECODE( p_transaction_line_id, fnd_api.g_miss_num, NULL, p_transaction_line_id),
108 DECODE( p_system_name, fnd_api.g_miss_char, NULL, p_system_name),
109 DECODE( p_description, fnd_api.g_miss_char, NULL, p_description),
110 DECODE( p_system_type_code, fnd_api.g_miss_char, NULL, p_system_type_code),
111 DECODE( p_system_number, fnd_api.g_miss_char, NULL, p_system_number),
112 DECODE( p_customer_id, fnd_api.g_miss_num, NULL, p_customer_id),
113 DECODE( p_bill_to_contact_id, fnd_api.g_miss_num, NULL, p_bill_to_contact_id),
114 DECODE( p_ship_to_contact_id, fnd_api.g_miss_num, NULL, p_ship_to_contact_id),
115 DECODE( p_technical_contact_id, fnd_api.g_miss_num, NULL, p_technical_contact_id),
116 DECODE( p_service_admin_contact_id, fnd_api.g_miss_num, NULL, p_service_admin_contact_id),
117 DECODE( p_ship_to_site_use_id, fnd_api.g_miss_num, NULL, p_ship_to_site_use_id),
118 DECODE( p_bill_to_site_use_id, fnd_api.g_miss_num, NULL, p_bill_to_site_use_id),
119 DECODE( p_install_site_use_id, fnd_api.g_miss_num, NULL, p_install_site_use_id),
120 DECODE( p_coterminate_day_month, fnd_api.g_miss_char, NULL, p_coterminate_day_month),
121 DECODE( p_config_system_type, fnd_api.g_miss_char, NULL, p_config_system_type),
122 DECODE( p_start_date_active, fnd_api.g_miss_date, NULL, p_start_date_active),
123 DECODE( p_end_date_active, fnd_api.g_miss_date, NULL, p_end_date_active),
124 DECODE( p_context, fnd_api.g_miss_char, NULL, p_context),
125 DECODE( p_attribute1, fnd_api.g_miss_char, NULL, p_attribute1),
126 DECODE( p_attribute2, fnd_api.g_miss_char, NULL, p_attribute2),
127 DECODE( p_attribute3, fnd_api.g_miss_char, NULL, p_attribute3),
128 DECODE( p_attribute4, fnd_api.g_miss_char, NULL, p_attribute4),
129 DECODE( p_attribute5, fnd_api.g_miss_char, NULL, p_attribute5),
130 DECODE( p_attribute6, fnd_api.g_miss_char, NULL, p_attribute6),
131 DECODE( p_attribute7, fnd_api.g_miss_char, NULL, p_attribute7),
132 DECODE( p_attribute8, fnd_api.g_miss_char, NULL, p_attribute8),
133 DECODE( p_attribute9, fnd_api.g_miss_char, NULL, p_attribute9),
134 DECODE( p_attribute10, fnd_api.g_miss_char, NULL, p_attribute10),
135 DECODE( p_attribute11, fnd_api.g_miss_char, NULL, p_attribute11),
136 DECODE( p_attribute12, fnd_api.g_miss_char, NULL, p_attribute12),
137 DECODE( p_attribute13, fnd_api.g_miss_char, NULL, p_attribute13),
138 DECODE( p_attribute14, fnd_api.g_miss_char, NULL, p_attribute14),
139 DECODE( p_attribute15, fnd_api.g_miss_char, NULL, p_attribute15),
140 DECODE( p_created_by, fnd_api.g_miss_num, NULL, p_created_by),
141 DECODE( p_creation_date, fnd_api.g_miss_date, to_date(NULL), p_creation_date),
142 DECODE( p_last_updated_by, fnd_api.g_miss_num, NULL, p_last_updated_by),
143 DECODE( p_last_update_date, fnd_api.g_miss_date, to_date(NULL), p_last_update_date),
144 DECODE( p_last_update_login, fnd_api.g_miss_num, NULL, p_last_update_login),
145 DECODE( p_object_version_number, fnd_api.g_miss_num, NULL, p_object_version_number));
146 END insert_row;
147
148 PROCEDURE Update_Row(
149 p_transaction_system_id NUMBER,
150 p_transaction_line_id NUMBER,
151 p_system_name VARCHAR2,
152 p_description VARCHAR2,
153 p_system_type_code VARCHAR2,
154 p_system_number VARCHAR2,
155 p_customer_id NUMBER,
156 p_bill_to_contact_id NUMBER,
157 p_ship_to_contact_id NUMBER,
158 p_technical_contact_id NUMBER,
159 p_service_admin_contact_id NUMBER,
160 p_ship_to_site_use_id NUMBER,
161 p_bill_to_site_use_id NUMBER,
162 p_install_site_use_id NUMBER,
163 p_coterminate_day_month VARCHAR2,
164 p_config_system_type VARCHAR2,
165 p_start_date_active DATE ,
166 p_end_date_active DATE ,
167 p_context VARCHAR2,
168 p_attribute1 VARCHAR2,
169 p_attribute2 VARCHAR2,
170 p_attribute3 VARCHAR2,
171 p_attribute4 VARCHAR2,
172 p_attribute5 VARCHAR2,
173 p_attribute6 VARCHAR2,
174 p_attribute7 VARCHAR2,
175 p_attribute8 VARCHAR2,
176 p_attribute9 VARCHAR2,
177 p_attribute10 VARCHAR2,
178 p_attribute11 VARCHAR2,
179 p_attribute12 VARCHAR2,
180 p_attribute13 VARCHAR2,
181 p_attribute14 VARCHAR2,
182 p_attribute15 VARCHAR2,
183 p_created_by NUMBER,
184 p_creation_date DATE,
185 p_last_updated_by NUMBER,
186 p_last_update_date DATE,
187 p_last_update_login NUMBER,
188 p_object_version_number NUMBER)
189
190 IS
191 BEGIN
192 UPDATE csi_t_txn_systems
193 SET
194 transaction_line_id = DECODE( p_transaction_line_id, fnd_api.g_miss_num, transaction_line_id, p_transaction_line_id),
195 system_name = DECODE( p_system_name, fnd_api.g_miss_char, system_name, p_system_name),
196 description = DECODE( p_description, fnd_api.g_miss_char, description, p_description),
197 system_type_code = DECODE( p_system_type_code, fnd_api.g_miss_char, system_type_code, p_system_type_code),
198 system_number = DECODE( p_system_number, fnd_api.g_miss_char, system_number, p_system_number),
199 customer_id = DECODE( p_customer_id, fnd_api.g_miss_num, customer_id, p_customer_id),
200 bill_to_contact_id = DECODE( p_bill_to_contact_id, fnd_api.g_miss_num, bill_to_contact_id, p_bill_to_contact_id),
201 ship_to_contact_id = DECODE( p_ship_to_contact_id, fnd_api.g_miss_num, ship_to_contact_id, p_ship_to_contact_id),
202 technical_contact_id = DECODE( p_technical_contact_id, fnd_api.g_miss_num, technical_contact_id, p_technical_contact_id),
203 service_admin_contact_id = DECODE( p_service_admin_contact_id, fnd_api.g_miss_num, service_admin_contact_id, p_service_admin_contact_id),
204 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),
205 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),
206 install_site_use_id = DECODE( p_install_site_use_id, fnd_api.g_miss_num, install_site_use_id, p_install_site_use_id),
207 coterminate_day_month = DECODE( p_coterminate_day_month, fnd_api.g_miss_char, coterminate_day_month, p_coterminate_day_month),
208 config_system_type = DECODE( p_config_system_type, fnd_api.g_miss_char, config_system_type, p_config_system_type),
209 start_date_active = DECODE( p_start_date_active, fnd_api.g_miss_date, start_date_active, p_start_date_active),
213 attribute2 = DECODE( p_attribute2, fnd_api.g_miss_char, attribute2, p_attribute2),
210 end_date_active = DECODE( p_end_date_active, fnd_api.g_miss_date, end_date_active, p_end_date_active),
211 context = DECODE( p_context, fnd_api.g_miss_char, context, p_context),
212 attribute1 = DECODE( p_attribute1, fnd_api.g_miss_char, attribute1, p_attribute1),
214 attribute3 = DECODE( p_attribute3, fnd_api.g_miss_char, attribute3, p_attribute3),
215 attribute4 = DECODE( p_attribute4, fnd_api.g_miss_char, attribute4, p_attribute4),
216 attribute5 = DECODE( p_attribute5, fnd_api.g_miss_char, attribute5, p_attribute5),
217 attribute6 = DECODE( p_attribute6, fnd_api.g_miss_char, attribute6, p_attribute6),
218 attribute7 = DECODE( p_attribute7, fnd_api.g_miss_char, attribute7, p_attribute7),
219 attribute8 = DECODE( p_attribute8, fnd_api.g_miss_char, attribute8, p_attribute8),
220 attribute9 = DECODE( p_attribute9, fnd_api.g_miss_char, attribute9, p_attribute9),
221 attribute10 = DECODE( p_attribute10, fnd_api.g_miss_char, attribute10, p_attribute10),
222 attribute11 = DECODE( p_attribute11, fnd_api.g_miss_char, attribute11, p_attribute11),
223 attribute12 = DECODE( p_attribute12, fnd_api.g_miss_char, attribute12, p_attribute12),
224 attribute13 = DECODE( p_attribute13, fnd_api.g_miss_char, attribute13, p_attribute13),
225 attribute14 = DECODE( p_attribute14, fnd_api.g_miss_char, attribute14, p_attribute14),
226 attribute15 = DECODE( p_attribute15, fnd_api.g_miss_char, attribute15, p_attribute15),
227 created_by = DECODE( p_created_by, fnd_api.g_miss_num, created_by, p_created_by),
228 creation_date = DECODE( p_creation_date, fnd_api.g_miss_date, creation_date, p_creation_date),
229 last_updated_by = DECODE( p_last_updated_by, fnd_api.g_miss_num, last_updated_by, p_last_updated_by),
230 last_update_date = DECODE( p_last_update_date, fnd_api.g_miss_date, last_update_date, p_last_update_date),
234
231 last_update_login = DECODE( p_last_update_login, fnd_api.g_miss_num, last_update_login, p_last_update_login),
232 object_version_number = object_version_number+1
233 WHERE transaction_system_id = p_transaction_system_id;
235 IF (SQL%NOTFOUND) THEN
236 RAISE no_data_found;
237 END IF;
238 END Update_Row;
239
240 PROCEDURE Delete_Row(
241 p_TRANSACTION_SYSTEM_ID NUMBER)
242 IS
243 BEGIN
244 DELETE FROM csi_t_txn_systems
245 WHERE transaction_system_id = p_transaction_system_id;
246 IF (SQL%NOTFOUND) THEN
247 RAISE no_data_found;
248 END IF;
249 END Delete_Row;
250
251 PROCEDURE Lock_Row(
252 p_transaction_system_id NUMBER,
253 p_transaction_line_id NUMBER,
254 p_system_name VARCHAR2,
255 p_description VARCHAR2,
256 p_system_type_code VARCHAR2,
257 p_system_number VARCHAR2,
258 p_customer_id NUMBER,
259 p_bill_to_contact_id NUMBER,
260 p_ship_to_contact_id NUMBER,
261 p_technical_contact_id NUMBER,
262 p_service_admin_contact_id NUMBER,
263 p_ship_to_site_use_id NUMBER,
264 p_bill_to_site_use_id NUMBER,
265 p_install_site_use_id NUMBER,
266 p_coterminate_day_month VARCHAR2,
267 p_config_system_type VARCHAR2,
268 p_start_date_active DATE ,
269 p_end_date_active DATE ,
270 p_context VARCHAR2,
271 p_attribute1 VARCHAR2,
272 p_attribute2 VARCHAR2,
273 p_attribute3 VARCHAR2,
274 p_attribute4 VARCHAR2,
275 p_attribute5 VARCHAR2,
276 p_attribute6 VARCHAR2,
277 p_attribute7 VARCHAR2,
278 p_attribute8 VARCHAR2,
279 p_attribute9 VARCHAR2,
280 p_attribute10 VARCHAR2,
281 p_attribute11 VARCHAR2,
282 p_attribute12 VARCHAR2,
283 p_attribute13 VARCHAR2,
284 p_attribute14 VARCHAR2,
285 p_attribute15 VARCHAR2,
286 p_created_by NUMBER,
287 p_creation_date DATE,
288 p_last_updated_by NUMBER,
289 p_last_update_date DATE,
290 p_last_update_login NUMBER,
291 p_object_version_number NUMBER)
292
293 IS
294 CURSOR c IS
295 SELECT *
296 FROM csi_t_txn_systems
297 WHERE transaction_system_id = p_transaction_system_id
298 FOR UPDATE of transaction_system_id NOWAIT;
299 recinfo c%ROWTYPE;
300 BEGIN
304 CLOSE c;
301 OPEN c;
302 FETCH c INTO recinfo;
303 IF (c%notfound) THEN
305 fnd_message.set_name('fnd', 'form_record_deleted');
306 app_exception.raise_exception;
307 END IF;
308 CLOSE c;
309 IF (
310 ( recinfo.transaction_system_id = p_transaction_system_id)
311 AND ( ( recinfo.transaction_line_id = p_transaction_line_id)
312 OR ( ( recinfo.transaction_line_id IS NULL )
313 AND ( p_transaction_line_id IS NULL )))
314 AND ( ( recinfo.system_name = p_system_name)
315 OR ( ( recinfo.system_name IS NULL )
316 AND ( p_system_name IS NULL )))
317 AND ( ( recinfo.description = p_description)
318 OR ( ( recinfo.description IS NULL )
319 AND ( p_description IS NULL )))
320 AND ( ( recinfo.system_type_code = p_system_type_code)
321 OR ( ( recinfo.system_type_code IS NULL )
322 AND ( p_system_type_code IS NULL )))
323 AND ( ( recinfo.system_number = p_system_number)
324 OR ( ( recinfo.system_number IS NULL )
325 AND ( p_system_number IS NULL )))
326 AND ( ( recinfo.customer_id = p_customer_id)
327 OR ( ( recinfo.customer_id IS NULL )
328 AND ( p_customer_id IS NULL )))
329 AND ( ( recinfo.bill_to_contact_id = p_bill_to_contact_id)
330 OR ( ( recinfo.bill_to_contact_id IS NULL )
331 AND ( p_bill_to_contact_id IS NULL )))
332 AND ( ( recinfo.ship_to_contact_id = p_ship_to_contact_id)
333 OR ( ( recinfo.ship_to_contact_id IS NULL )
334 AND ( p_ship_to_contact_id IS NULL )))
335 AND ( ( recinfo.technical_contact_id = p_technical_contact_id)
336 OR ( ( recinfo.technical_contact_id IS NULL )
337 AND ( p_technical_contact_id IS NULL )))
338 AND ( ( recinfo.service_admin_contact_id = p_service_admin_contact_id)
339 OR ( ( recinfo.service_admin_contact_id IS NULL )
340 AND ( p_service_admin_contact_id IS NULL )))
341 AND ( ( recinfo.ship_to_site_use_id = p_ship_to_site_use_id)
342 OR ( ( recinfo.ship_to_site_use_id IS NULL )
343 AND ( p_ship_to_site_use_id IS NULL )))
344 AND ( ( recinfo.bill_to_site_use_id = p_bill_to_site_use_id)
345 OR ( ( recinfo.bill_to_site_use_id IS NULL )
346 AND ( p_bill_to_site_use_id IS NULL )))
347 AND ( ( recinfo.install_site_use_id = p_install_site_use_id)
348 OR ( ( recinfo.install_site_use_id IS NULL )
349 AND ( p_install_site_use_id IS NULL )))
350 AND ( ( recinfo.coterminate_day_month = p_coterminate_day_month)
351 OR ( ( recinfo.coterminate_day_month IS NULL )
352 AND ( p_coterminate_day_month IS NULL )))
353 AND ( ( recinfo.config_system_type = p_config_system_type)
354 OR ( ( recinfo.config_system_type IS NULL )
355 AND ( p_config_system_type IS NULL )))
356 AND ( ( recinfo.context = p_context)
357 OR ( ( recinfo.context IS NULL )
358 AND ( p_context IS NULL )))
359 AND ( ( recinfo.attribute1 = p_attribute1)
360 OR ( ( recinfo.attribute1 IS NULL )
361 AND ( p_attribute1 IS NULL )))
362 AND ( ( recinfo.attribute2 = p_attribute2)
363 OR ( ( recinfo.attribute2 IS NULL )
364 AND ( p_attribute2 IS NULL )))
365 AND ( ( recinfo.attribute3 = p_attribute3)
366 OR ( ( recinfo.attribute3 IS NULL )
367 AND ( p_attribute3 IS NULL )))
368 AND ( ( recinfo.attribute4 = p_attribute4)
372 OR ( ( recinfo.attribute5 IS NULL )
369 OR ( ( recinfo.attribute4 IS NULL )
370 AND ( p_attribute4 IS NULL )))
371 AND ( ( recinfo.attribute5 = p_attribute5)
373 AND ( p_attribute5 IS NULL )))
374 AND ( ( recinfo.attribute6 = p_attribute6)
375 OR ( ( recinfo.attribute6 IS NULL )
376 AND ( p_attribute6 IS NULL )))
377 AND ( ( recinfo.attribute7 = p_attribute7)
378 OR ( ( recinfo.attribute7 IS NULL )
379 AND ( p_attribute7 IS NULL )))
380 AND ( ( recinfo.attribute8 = p_attribute8)
381 OR ( ( recinfo.attribute8 IS NULL )
382 AND ( p_attribute8 IS NULL )))
383 AND ( ( recinfo.attribute9 = p_attribute9)
384 OR ( ( recinfo.attribute9 IS NULL )
385 AND ( p_attribute9 IS NULL )))
386 AND ( ( recinfo.attribute10 = p_attribute10)
387 OR ( ( recinfo.attribute10 IS NULL )
388 AND ( p_attribute10 IS NULL )))
389 AND ( ( recinfo.attribute11 = p_attribute11)
390 OR ( ( recinfo.attribute11 IS NULL )
391 AND ( p_attribute11 IS NULL )))
395 AND ( ( recinfo.attribute13 = p_attribute13)
392 AND ( ( recinfo.attribute12 = p_attribute12)
393 OR ( ( recinfo.attribute12 IS NULL )
394 AND ( p_attribute12 IS NULL )))
396 OR ( ( recinfo.attribute13 IS NULL )
397 AND ( p_attribute13 IS NULL )))
398 AND ( ( recinfo.attribute14 = p_attribute14)
399 OR ( ( recinfo.attribute14 IS NULL )
400 AND ( p_attribute14 IS NULL )))
401 AND ( ( recinfo.attribute15 = p_attribute15)
402 OR ( ( recinfo.attribute15 IS NULL )
403 AND ( p_attribute15 IS NULL )))
404 AND ( ( recinfo.created_by = p_created_by)
405 OR ( ( recinfo.created_by IS NULL )
406 AND ( p_created_by IS NULL )))
407 AND ( ( recinfo.creation_date = p_creation_date)
408 OR ( ( recinfo.creation_date IS NULL )
409 AND ( p_creation_date IS NULL )))
410 AND ( ( recinfo.last_updated_by = p_last_updated_by)
411 OR ( ( recinfo.last_updated_by IS NULL )
412 AND ( p_last_updated_by IS NULL )))
413 AND ( ( recinfo.last_update_date = p_last_update_date)
414 OR ( ( recinfo.last_update_date IS NULL )
415 AND ( p_last_update_date IS NULL )))
416 AND ( ( recinfo.last_update_login = p_last_update_login)
417 OR ( ( recinfo.last_update_login IS NULL )
418 AND ( p_last_update_login IS NULL )))
419 AND ( ( recinfo.object_version_number = p_object_version_number)
420 OR ( ( recinfo.object_version_number IS NULL )
421 AND ( p_object_version_number IS NULL )))
422 ) THEN
423 RETURN;
424 ELSE
425 fnd_message.set_name('fnd', 'form_record_changed');
426 app_exception.raise_exception;
427 END IF;
428 END Lock_Row;
429
430 END CSI_T_TXN_SYSTEMS_PKG;