[Home] [Help]
PACKAGE BODY: APPS.CS_TRANSACTION_TYPES_PKG
Source
1 package body CS_TRANSACTION_TYPES_PKG AS
2 /* $Header: cssedttb.pls 120.1 2007/12/16 07:19:27 bkanimoz ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_TRANSACTION_TYPE_ID in NUMBER,
6 X_LINE_ORDER_CATEGORY_CODE VARCHAR2,
7 X_SEEDED_FLAG in VARCHAR2,
8 X_REVISION_FLAG in VARCHAR2,
9 X_END_DATE_ACTIVE in DATE,
10 X_START_DATE_ACTIVE in DATE,
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_CONTEXT in VARCHAR2,
22 X_INSTALLED_CP_STATUS_ID in NUMBER,
23 X_ATTRIBUTE11 in VARCHAR2,
24 X_ATTRIBUTE12 in VARCHAR2,
25 X_INSTALLED_STATUS_CODE in VARCHAR2,
26 X_ATTRIBUTE13 in VARCHAR2,
27 X_ATTRIBUTE14 in VARCHAR2,
28 X_ATTRIBUTE15 in VARCHAR2,
29 X_INSTALLED_CP_RETURN_REQUIRED in VARCHAR2,
30 X_NO_CHARGE_FLAG in VARCHAR2,
31 X_CREATE_CHARGE_FLAG IN VARCHAR2, --
32 X_CREATE_COST_FLAG IN VARCHAR2,----Added by bkanimoz for Service Costing
33 X_DEPOT_REPAIR_FLAG in VARCHAR2,
34 X_NEW_CP_STATUS_ID in NUMBER,
35 X_NEW_CP_STATUS_CODE in VARCHAR2,
36 X_TRANSFER_SERVICE in VARCHAR2,
37 X_NEW_CP_RETURN_REQUIRED in VARCHAR2,
38 X_NAME in VARCHAR2,
39 X_DESCRIPTION in VARCHAR2,
40 X_CREATION_DATE in DATE,
41 X_CREATED_BY in NUMBER,
42 X_LAST_UPDATE_DATE in DATE,
43 X_LAST_UPDATED_BY in NUMBER,
44 X_LAST_UPDATE_LOGIN in NUMBER,
45 X_MOVE_COUNTERS_FLAG in VARCHAR2,
46 X_OBJECT_VERSION_NUMBER in NUMBER,
47 X_INTERFACE_TO_OE_FLAG in VARCHAR2
48 ) is
49 cursor C is select ROWID from CS_TRANSACTION_TYPES_B
50 where TRANSACTION_TYPE_ID = X_TRANSACTION_TYPE_ID
51 ;
52 begin
53 insert into CS_TRANSACTION_TYPES_B (
54 TRANSACTION_TYPE_ID,
55 LINE_ORDER_CATEGORY_CODE,
56 SEEDED_FLAG,
57 REVISION_FLAG,
58 END_DATE_ACTIVE,
59 START_DATE_ACTIVE,
60 ATTRIBUTE1,
61 ATTRIBUTE2,
62 ATTRIBUTE3,
63 ATTRIBUTE4,
64 ATTRIBUTE5,
65 ATTRIBUTE6,
66 ATTRIBUTE7,
67 ATTRIBUTE8,
68 ATTRIBUTE9,
69 ATTRIBUTE10,
70 CONTEXT,
71 INSTALLED_CP_STATUS_ID,
72 ATTRIBUTE11,
73 ATTRIBUTE12,
74 INSTALLED_STATUS_CODE,
75 ATTRIBUTE13,
76 ATTRIBUTE14,
77 ATTRIBUTE15,
78 INSTALLED_CP_RETURN_REQUIRED,
79 NO_CHARGE_FLAG,
80 CREATE_CHARGE_FLAG ,
81 CREATE_COST_FLAG,----Added by bkanimoz for Service Costing
82 DEPOT_REPAIR_FLAG,
83 NEW_CP_STATUS_ID,
84 NEW_CP_STATUS_CODE,
85 TRANSFER_SERVICE,
86 NEW_CP_RETURN_REQUIRED,
87 CREATION_DATE,
88 CREATED_BY,
89 LAST_UPDATE_DATE,
90 LAST_UPDATED_BY,
91 LAST_UPDATE_LOGIN,
92 MOVE_COUNTERS_FLAG,
93 OBJECT_VERSION_NUMBER,
94 INTERFACE_TO_OE_FLAG
95 ) values (
96 X_TRANSACTION_TYPE_ID,
97 X_LINE_ORDER_CATEGORY_CODE,
98 X_SEEDED_FLAG,
99 X_REVISION_FLAG,
100 X_END_DATE_ACTIVE,
101 X_START_DATE_ACTIVE,
102 X_ATTRIBUTE1,
103 X_ATTRIBUTE2,
104 X_ATTRIBUTE3,
105 X_ATTRIBUTE4,
106 X_ATTRIBUTE5,
107 X_ATTRIBUTE6,
108 X_ATTRIBUTE7,
109 X_ATTRIBUTE8,
110 X_ATTRIBUTE9,
111 X_ATTRIBUTE10,
112 X_CONTEXT,
113 X_INSTALLED_CP_STATUS_ID,
114 X_ATTRIBUTE11,
115 X_ATTRIBUTE12,
116 X_INSTALLED_STATUS_CODE,
117 X_ATTRIBUTE13,
118 X_ATTRIBUTE14,
119 X_ATTRIBUTE15,
120 X_INSTALLED_CP_RETURN_REQUIRED,
121 X_NO_CHARGE_FLAG,
122 X_CREATE_CHARGE_FLAG ,
123 X_CREATE_COST_FLAG,----Added by bkanimoz for Service Costing
124 X_DEPOT_REPAIR_FLAG,
125 X_NEW_CP_STATUS_ID,
126 X_NEW_CP_STATUS_CODE,
127 X_TRANSFER_SERVICE,
128 X_NEW_CP_RETURN_REQUIRED,
129 X_CREATION_DATE,
130 X_CREATED_BY,
131 X_LAST_UPDATE_DATE,
132 X_LAST_UPDATED_BY,
133 X_LAST_UPDATE_LOGIN,
134 X_MOVE_COUNTERS_FLAG,
135 X_OBJECT_VERSION_NUMBER,
136 X_INTERFACE_TO_OE_FLAG
137 );
138
139 insert into CS_TRANSACTION_TYPES_TL (
140 LAST_UPDATE_LOGIN,
141 DESCRIPTION,
142 LAST_UPDATE_DATE,
143 LAST_UPDATED_BY,
144 CREATION_DATE,
145 CREATED_BY,
146 TRANSACTION_TYPE_ID,
147 NAME,
148 LANGUAGE,
149 SOURCE_LANG
150 ) select
151 X_LAST_UPDATE_LOGIN,
152 X_DESCRIPTION,
153 X_LAST_UPDATE_DATE,
154 X_LAST_UPDATED_BY,
155 X_CREATION_DATE,
156 X_CREATED_BY,
157 X_TRANSACTION_TYPE_ID,
158 X_NAME,
159 L.LANGUAGE_CODE,
160 userenv('LANG')
161 from FND_LANGUAGES L
162 where L.INSTALLED_FLAG in ('I', 'B')
163 and not exists
164 (select NULL
165 from CS_TRANSACTION_TYPES_TL T
166 where T.TRANSACTION_TYPE_ID = X_TRANSACTION_TYPE_ID
167 and T.LANGUAGE = L.LANGUAGE_CODE);
168
169 open c;
170 fetch c into X_ROWID;
171 if (c%notfound) then
172 close c;
173 raise no_data_found;
174 end if;
175 close c;
176
177 end INSERT_ROW;
178
179 procedure LOCK_ROW (
180 X_ROWID in VARCHAR2 default NULL,
181 X_TRANSACTION_TYPE_ID in NUMBER,
182 X_LINE_ORDER_CATEGORY_CODE in VARCHAR2,
183 X_SEEDED_FLAG in VARCHAR2,
184 X_REVISION_FLAG in VARCHAR2,
185 X_END_DATE_ACTIVE in DATE,
186 X_START_DATE_ACTIVE in DATE,
187 X_ATTRIBUTE1 in VARCHAR2,
188 X_ATTRIBUTE2 in VARCHAR2,
189 X_ATTRIBUTE3 in VARCHAR2,
190 X_ATTRIBUTE4 in VARCHAR2,
191 X_ATTRIBUTE5 in VARCHAR2,
192 X_ATTRIBUTE6 in VARCHAR2,
193 X_ATTRIBUTE7 in VARCHAR2,
194 X_ATTRIBUTE8 in VARCHAR2,
195 X_ATTRIBUTE9 in VARCHAR2,
196 X_ATTRIBUTE10 in VARCHAR2,
197 X_CONTEXT in VARCHAR2,
198 X_INSTALLED_CP_STATUS_ID in NUMBER,
199 X_ATTRIBUTE11 in VARCHAR2,
200 X_ATTRIBUTE12 in VARCHAR2,
201 X_INSTALLED_STATUS_CODE in VARCHAR2,
202 X_ATTRIBUTE13 in VARCHAR2,
203 X_ATTRIBUTE14 in VARCHAR2,
204 X_ATTRIBUTE15 in VARCHAR2,
205 X_INSTALLED_CP_RETURN_REQUIRED in VARCHAR2,
206 X_NO_CHARGE_FLAG in VARCHAR2,
207 X_CREATE_CHARGE_FLAG IN VARCHAR2, --
208 X_CREATE_COST_FLAG IN VARCHAR2,----Added by bkanimoz for Service Costing
209 X_DEPOT_REPAIR_FLAG in VARCHAR2,
210 X_NEW_CP_STATUS_ID in NUMBER,
211 X_NEW_CP_STATUS_CODE in VARCHAR2,
212 X_TRANSFER_SERVICE in VARCHAR2,
213 X_NEW_CP_RETURN_REQUIRED in VARCHAR2,
214 X_NAME in VARCHAR2,
215 X_DESCRIPTION in VARCHAR2,
216 X_MOVE_COUNTERS_FLAG IN VARCHAR2,
217 X_OBJECT_VERSION_NUMBER IN NUMBER,
218 X_INTERFACE_TO_OE_FLAG IN VARCHAR2
219 ) is
220 cursor c is select
221 LINE_ORDER_CATEGORY_CODE,
222 SEEDED_FLAG,
223 REVISION_FLAG,
224 END_DATE_ACTIVE,
225 START_DATE_ACTIVE,
226 ATTRIBUTE1,
227 ATTRIBUTE2,
228 ATTRIBUTE3,
229 ATTRIBUTE4,
230 ATTRIBUTE5,
231 ATTRIBUTE6,
232 ATTRIBUTE7,
233 ATTRIBUTE8,
234 ATTRIBUTE9,
235 ATTRIBUTE10,
236 CONTEXT,
237 INSTALLED_CP_STATUS_ID,
238 ATTRIBUTE11,
239 ATTRIBUTE12,
240 INSTALLED_STATUS_CODE,
241 ATTRIBUTE13,
242 ATTRIBUTE14,
243 ATTRIBUTE15,
244 INSTALLED_CP_RETURN_REQUIRED,
245 NO_CHARGE_FLAG,
246 CREATE_CHARGE_FLAG , --
247 CREATE_COST_FLAG ,----Added by bkanimoz for Service Costing
248 DEPOT_REPAIR_FLAG,
249 NEW_CP_STATUS_ID,
250 NEW_CP_STATUS_CODE,
251 TRANSFER_SERVICE,
252 NEW_CP_RETURN_REQUIRED,
253 MOVE_COUNTERS_FLAG,
254 OBJECT_VERSION_NUMBER,
255 INTERFACE_TO_OE_FLAG
256 from CS_TRANSACTION_TYPES_B
257 where TRANSACTION_TYPE_ID = X_TRANSACTION_TYPE_ID
258 for update of TRANSACTION_TYPE_ID nowait;
259 recinfo c%rowtype;
260
261 cursor c1 is select
262 NAME,
263 DESCRIPTION,
264 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
265 from CS_TRANSACTION_TYPES_TL
266 where TRANSACTION_TYPE_ID = X_TRANSACTION_TYPE_ID
267 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
268 for update of TRANSACTION_TYPE_ID nowait;
269 begin
270 open c;
271 fetch c into recinfo;
272 if (c%notfound) then
273 close c;
274 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
275 app_exception.raise_exception;
276 end if;
277 close c;
278 if ( (recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
279 AND ((recinfo.REVISION_FLAG = X_REVISION_FLAG)
280 OR ((recinfo.REVISION_FLAG is null) AND (X_REVISION_FLAG is null)))
281 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
282 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
283 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
284 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
285 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
286 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
287 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
288 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
289 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
290 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
291 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
292 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
293 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
294 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
295 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
296 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
297 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
298 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
299 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
300 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
301 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
302 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
303 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
304 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
305 AND ((recinfo.CONTEXT = X_CONTEXT)
306 OR ((recinfo.CONTEXT is null) AND (X_CONTEXT is null)))
307 AND ((recinfo.INSTALLED_CP_STATUS_ID = X_INSTALLED_CP_STATUS_ID)
308 OR ((recinfo.INSTALLED_CP_STATUS_ID is null) AND (X_INSTALLED_CP_STATUS_ID is null)))
309 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
310 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
311 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
312 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
313 AND ((recinfo.INSTALLED_STATUS_CODE = X_INSTALLED_STATUS_CODE)
314 OR ((recinfo.INSTALLED_STATUS_CODE is null) AND (X_INSTALLED_STATUS_CODE is null)))
315 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
316 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
317 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
318 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
319 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
320 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
321 AND ((recinfo.INSTALLED_CP_RETURN_REQUIRED = X_INSTALLED_CP_RETURN_REQUIRED)
322 OR ((recinfo.INSTALLED_CP_RETURN_REQUIRED is null) AND (X_INSTALLED_CP_RETURN_REQUIRED is null)))
323 AND ((recinfo.NO_CHARGE_FLAG = X_NO_CHARGE_FLAG)
324 OR ((recinfo.NO_CHARGE_FLAG is null) AND (X_NO_CHARGE_FLAG is null)))
325 AND ((recinfo.CREATE_CHARGE_FLAG = X_CREATE_CHARGE_FLAG) -- start added by bkanimoz
326 OR ((recinfo.CREATE_CHARGE_FLAG is null) AND (X_CREATE_CHARGE_FLAG is null)))
327 AND ((recinfo.create_cost_flag = X_create_cost_flag)
328 OR ((recinfo.create_cost_flag is null) AND (X_create_cost_flag is null)))-- end
329 AND ((recinfo.DEPOT_REPAIR_FLAG = X_DEPOT_REPAIR_FLAG)
330 OR ((recinfo.NO_CHARGE_FLAG is null) AND (X_DEPOT_REPAIR_FLAG is null)))
331 AND ((recinfo.NEW_CP_STATUS_ID = X_NEW_CP_STATUS_ID)
332 OR ((recinfo.NEW_CP_STATUS_ID is null) AND (X_NEW_CP_STATUS_ID is null)))
333 AND ((recinfo.NEW_CP_STATUS_CODE = X_NEW_CP_STATUS_CODE)
334 OR ((recinfo.NEW_CP_STATUS_CODE is null) AND (X_NEW_CP_STATUS_CODE is null)))
335 AND ((recinfo.TRANSFER_SERVICE = X_TRANSFER_SERVICE)
336 OR ((recinfo.TRANSFER_SERVICE is null) AND (X_TRANSFER_SERVICE is null)))
337 AND ((recinfo.NEW_CP_RETURN_REQUIRED = X_NEW_CP_RETURN_REQUIRED)
338 OR ((recinfo.NEW_CP_RETURN_REQUIRED is null) AND (X_NEW_CP_RETURN_REQUIRED is null)))
339 AND ((recinfo.MOVE_COUNTERS_FLAG = X_MOVE_COUNTERS_FLAG)
340 OR ((recinfo.MOVE_COUNTERS_FLAG is null) AND (X_MOVE_COUNTERS_FLAG is null)))
341 -- AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
342 AND ((recinfo.INTERFACE_TO_OE_FLAG = X_INTERFACE_TO_OE_FLAG)
343 OR ((recinfo.INTERFACE_TO_OE_FLAG is null) AND (X_INTERFACE_TO_OE_FLAG is null)))
344 ) then
345 null;
346 else
347 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
348 app_exception.raise_exception;
349 end if;
350
351 for tlinfo in c1 loop
352 if (tlinfo.BASELANG = 'Y') then
353 if ( (tlinfo.NAME = X_NAME)
354 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
355 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
356 ) then
357 null;
358 else
359 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
360 app_exception.raise_exception;
361 end if;
362 end if;
363 end loop;
364 return;
365 end LOCK_ROW;
366
367 procedure UPDATE_ROW (
368 X_ROWID in VARCHAR2 default NULL,
369 X_TRANSACTION_TYPE_ID in NUMBER,
370 X_LINE_ORDER_CATEGORY_CODE VARCHAR2,
371 X_SEEDED_FLAG in VARCHAR2,
372 X_REVISION_FLAG in VARCHAR2,
373 X_END_DATE_ACTIVE in DATE,
374 X_START_DATE_ACTIVE in DATE,
375 X_ATTRIBUTE1 in VARCHAR2,
376 X_ATTRIBUTE2 in VARCHAR2,
377 X_ATTRIBUTE3 in VARCHAR2,
378 X_ATTRIBUTE4 in VARCHAR2,
379 X_ATTRIBUTE5 in VARCHAR2,
380 X_ATTRIBUTE6 in VARCHAR2,
381 X_ATTRIBUTE7 in VARCHAR2,
382 X_ATTRIBUTE8 in VARCHAR2,
383 X_ATTRIBUTE9 in VARCHAR2,
384 X_ATTRIBUTE10 in VARCHAR2,
385 X_CONTEXT in VARCHAR2,
386 X_INSTALLED_CP_STATUS_ID in NUMBER,
387 X_ATTRIBUTE11 in VARCHAR2,
388 X_ATTRIBUTE12 in VARCHAR2,
389 X_INSTALLED_STATUS_CODE in VARCHAR2,
390 X_ATTRIBUTE13 in VARCHAR2,
391 X_ATTRIBUTE14 in VARCHAR2,
392 X_ATTRIBUTE15 in VARCHAR2,
393 X_INSTALLED_CP_RETURN_REQUIRED in VARCHAR2,
394 X_NO_CHARGE_FLAG in VARCHAR2,
395 X_CREATE_CHARGE_FLAG IN VARCHAR2, --
396 X_CREATE_COST_FLAG IN VARCHAR2,----Added by bkanimoz for Service Costing
397 X_DEPOT_REPAIR_FLAG in VARCHAR2,
398 X_NEW_CP_STATUS_ID in NUMBER,
399 X_NEW_CP_STATUS_CODE in VARCHAR2,
400 X_TRANSFER_SERVICE in VARCHAR2,
401 X_NEW_CP_RETURN_REQUIRED in VARCHAR2,
402 X_NAME in VARCHAR2,
403 X_DESCRIPTION in VARCHAR2,
404 X_LAST_UPDATE_DATE in DATE,
405 X_LAST_UPDATED_BY in NUMBER,
406 X_LAST_UPDATE_LOGIN in NUMBER,
407 X_MOVE_COUNTERS_FLAG in VARCHAR2,
408 X_OBJECT_VERSION_NUMBER in NUMBER,
409 X_INTERFACE_TO_OE_FLAG in VARCHAR2
410 ) is
411 begin
412 update CS_TRANSACTION_TYPES_B set
413 LINE_ORDER_CATEGORY_CODE = X_LINE_ORDER_CATEGORY_CODE,
414 SEEDED_FLAG = X_SEEDED_FLAG,
415 REVISION_FLAG = X_REVISION_FLAG,
416 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
417 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
418 ATTRIBUTE1 = X_ATTRIBUTE1,
419 ATTRIBUTE2 = X_ATTRIBUTE2,
423 ATTRIBUTE6 = X_ATTRIBUTE6,
420 ATTRIBUTE3 = X_ATTRIBUTE3,
421 ATTRIBUTE4 = X_ATTRIBUTE4,
422 ATTRIBUTE5 = X_ATTRIBUTE5,
424 ATTRIBUTE7 = X_ATTRIBUTE7,
425 ATTRIBUTE8 = X_ATTRIBUTE8,
426 ATTRIBUTE9 = X_ATTRIBUTE9,
427 ATTRIBUTE10 = X_ATTRIBUTE10,
428 CONTEXT = X_CONTEXT,
429 INSTALLED_CP_STATUS_ID = X_INSTALLED_CP_STATUS_ID,
430 ATTRIBUTE11 = X_ATTRIBUTE11,
431 ATTRIBUTE12 = X_ATTRIBUTE12,
432 INSTALLED_STATUS_CODE = X_INSTALLED_STATUS_CODE,
433 ATTRIBUTE13 = X_ATTRIBUTE13,
434 ATTRIBUTE14 = X_ATTRIBUTE14,
435 ATTRIBUTE15 = X_ATTRIBUTE15,
436 INSTALLED_CP_RETURN_REQUIRED = X_INSTALLED_CP_RETURN_REQUIRED,
437 NO_CHARGE_FLAG = X_NO_CHARGE_FLAG,
438 CREATE_CHARGE_FLAG = X_CREATE_CHARGE_FLAG ,--
439 CREATE_COST_FLAG = X_CREATE_COST_FLAG ,----Added by bkanimoz for Service Costing
440 DEPOT_REPAIR_FLAG = X_DEPOT_REPAIR_FLAG,
441 NEW_CP_STATUS_ID = X_NEW_CP_STATUS_ID,
442 NEW_CP_STATUS_CODE = X_NEW_CP_STATUS_CODE,
443 TRANSFER_SERVICE = X_TRANSFER_SERVICE,
444 NEW_CP_RETURN_REQUIRED = X_NEW_CP_RETURN_REQUIRED,
445 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
446 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
447 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
448 MOVE_COUNTERS_FLAG = X_MOVE_COUNTERS_FLAG,
449 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
450 INTERFACE_TO_OE_FLAG = X_INTERFACE_TO_OE_FLAG
451 where TRANSACTION_TYPE_ID = X_TRANSACTION_TYPE_ID;
452
453 if (sql%notfound) then
454 raise no_data_found;
455 end if;
456
457 update CS_TRANSACTION_TYPES_TL set
458 NAME = X_NAME,
459 DESCRIPTION = X_DESCRIPTION,
460 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
461 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
462 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
463 SOURCE_LANG = userenv('LANG')
464 where TRANSACTION_TYPE_ID = X_TRANSACTION_TYPE_ID
465 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
466
467 if (sql%notfound) then
468 raise no_data_found;
469 end if;
470 end UPDATE_ROW;
471
472 procedure DELETE_ROW (
473 X_TRANSACTION_TYPE_ID in NUMBER
474 ) is
475 begin
476 delete from CS_TRANSACTION_TYPES_TL
477 where TRANSACTION_TYPE_ID = X_TRANSACTION_TYPE_ID;
478
479 if (sql%notfound) then
480 raise no_data_found;
481 end if;
482
483 delete from CS_TRANSACTION_TYPES_B
484 where TRANSACTION_TYPE_ID = X_TRANSACTION_TYPE_ID;
485
486 if (sql%notfound) then
487 raise no_data_found;
488 end if;
489 end DELETE_ROW;
490
491 procedure ADD_LANGUAGE
492 is
493 begin
494 delete from CS_TRANSACTION_TYPES_TL T
495 where not exists
496 (select NULL
497 from CS_TRANSACTION_TYPES_B B
498 where B.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID
499 );
500
501 update CS_TRANSACTION_TYPES_TL T set (
502 NAME,
503 DESCRIPTION
504 ) = (select
505 B.NAME,
506 B.DESCRIPTION
507 from CS_TRANSACTION_TYPES_TL B
508 where B.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID
509 and B.LANGUAGE = T.SOURCE_LANG)
510 where (
511 T.TRANSACTION_TYPE_ID,
512 T.LANGUAGE
513 ) in (select
514 SUBT.TRANSACTION_TYPE_ID,
515 SUBT.LANGUAGE
516 from CS_TRANSACTION_TYPES_TL SUBB, CS_TRANSACTION_TYPES_TL SUBT
517 where SUBB.TRANSACTION_TYPE_ID = SUBT.TRANSACTION_TYPE_ID
518 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
519 and (SUBB.NAME <> SUBT.NAME
520 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
521 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
522 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
523 ));
524
525 insert into CS_TRANSACTION_TYPES_TL (
526 LAST_UPDATE_LOGIN,
527 DESCRIPTION,
528 LAST_UPDATE_DATE,
529 LAST_UPDATED_BY,
530 CREATION_DATE,
531 CREATED_BY,
532 TRANSACTION_TYPE_ID,
533 NAME,
534 LANGUAGE,
535 SOURCE_LANG
536 ) select
537 B.LAST_UPDATE_LOGIN,
538 B.DESCRIPTION,
539 B.LAST_UPDATE_DATE,
540 B.LAST_UPDATED_BY,
541 B.CREATION_DATE,
542 B.CREATED_BY,
543 B.TRANSACTION_TYPE_ID,
544 B.NAME,
545 L.LANGUAGE_CODE,
546 B.SOURCE_LANG
547 from CS_TRANSACTION_TYPES_TL B, FND_LANGUAGES L
548 where L.INSTALLED_FLAG in ('I', 'B')
549 and B.LANGUAGE = userenv('LANG')
550 and not exists
551 (select NULL
552 from CS_TRANSACTION_TYPES_TL T
553 where T.TRANSACTION_TYPE_ID = B.TRANSACTION_TYPE_ID
554 and T.LANGUAGE = L.LANGUAGE_CODE);
555 end ADD_LANGUAGE;
556
557 procedure TRANSLATE_ROW (
558 X_TRANSACTION_TYPE_ID in VARCHAR2,
559 X_NAME in VARCHAR2,
560 X_OWNER in VARCHAR2,
561 X_DESCRIPTION in VARCHAR2 ) is
562 begin
563 update CS_TRANSACTION_TYPES_TL set
564 name = X_NAME,
565 DESCRIPTION=X_DESCRIPTION,
566 last_update_date = sysdate,
567 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
568 last_update_login = 0,
569 source_lang = userenv('LANG')
570 where TRANSACTION_TYPE_ID = to_number(X_TRANSACTION_TYPE_ID)
574
571 and userenv('LANG') in (language, source_lang);
572
573 end TRANSLATE_ROW;
575
576 procedure LOAD_ROW (
577 X_TRANSACTION_TYPE_ID in VARCHAR2,
578 X_LINE_ORDER_CATEGORY_CODE in VARCHAR2,
579 X_SEEDED_FLAG in VARCHAR2,
580 X_REVISION_FLAG in VARCHAR2,
581 X_END_DATE_ACTIVE in VARCHAR2,
582 X_START_DATE_ACTIVE in VARCHAR2,
583 X_ATTRIBUTE1 in VARCHAR2,
584 X_ATTRIBUTE2 in VARCHAR2,
585 X_ATTRIBUTE3 in VARCHAR2,
586 X_ATTRIBUTE4 in VARCHAR2,
587 X_ATTRIBUTE5 in VARCHAR2,
588 X_ATTRIBUTE6 in VARCHAR2,
589 X_ATTRIBUTE7 in VARCHAR2,
590 X_ATTRIBUTE8 in VARCHAR2,
591 X_ATTRIBUTE9 in VARCHAR2,
592 X_ATTRIBUTE10 in VARCHAR2,
593 X_CONTEXT in VARCHAR2,
594 X_INSTALLED_CP_STATUS_ID in VARCHAR2,
595 X_ATTRIBUTE11 in VARCHAR2,
596 X_ATTRIBUTE12 in VARCHAR2,
597 X_INSTALLED_STATUS_CODE in VARCHAR2,
598 X_ATTRIBUTE13 in VARCHAR2,
599 X_ATTRIBUTE14 in VARCHAR2,
600 X_ATTRIBUTE15 in VARCHAR2,
601 X_INSTALLED_CP_RETURN_REQUIRED in VARCHAR2,
602 X_NO_CHARGE_FLAG in VARCHAR2,
603 X_CREATE_CHARGE_FLAG in VARCHAR2,--added by bkanimoz
604 X_CREATE_COST_FLAG in VARCHAR2,--
605 X_DEPOT_REPAIR_FLAG in VARCHAR2,
606 X_NEW_CP_STATUS_ID in VARCHAR2,
607 X_NEW_CP_STATUS_CODE in VARCHAR2,
608 X_TRANSFER_SERVICE in VARCHAR2,
609 X_NEW_CP_RETURN_REQUIRED in VARCHAR2,
610 X_NAME in VARCHAR2,
611 X_DESCRIPTION in VARCHAR2,
612 X_OWNER in VARCHAR2,
613 X_MOVE_COUNTERS_FLAG in VARCHAR2,
614 X_INTERFACE_TO_OE_FLAG in VARCHAR2
615 ) IS
616 begin
617 declare
618 l_user_id number := 0 ;
619 l_row_id varchar2(64);
620 l_object_ver number := 1;
621 luby number := null;
622
623 begin
624 if (X_OWNER = 'SEED') then
625 l_user_id := 1;
626 end if;
627
628 select last_updated_by into luby
629 from cs_transaction_types_b
630 where transaction_type_id = x_transaction_type_id;
631
632 IF luby = 1 THEN
633 select object_version_number,rowid into l_object_ver,l_row_id
634 from cs_transaction_types_b where transaction_type_id = x_transaction_type_id
635 for update nowait;
636 l_object_ver := l_object_ver + 1;
637
638 UPDATE_ROW (
639 '',
640 X_TRANSACTION_TYPE_ID => to_number(X_TRANSACTION_TYPE_ID),
641 X_LINE_ORDER_CATEGORY_CODE => X_LINE_ORDER_CATEGORY_CODE,
642 X_SEEDED_FLAG => X_SEEDED_FLAG,
643 X_REVISION_FLAG => X_REVISION_FLAG,
644 X_END_DATE_ACTIVE => to_date(X_END_DATE_ACTIVE, 'YYYY/MM/DD'),
645 X_START_DATE_ACTIVE => to_date(X_START_DATE_ACTIVE, 'YYYY/MM/DD'),
646 X_ATTRIBUTE1 => X_ATTRIBUTE1,
647 X_ATTRIBUTE2 => X_ATTRIBUTE2,
648 X_ATTRIBUTE3 => X_ATTRIBUTE3,
649 X_ATTRIBUTE4 => X_ATTRIBUTE4,
650 X_ATTRIBUTE5 => X_ATTRIBUTE5,
651 X_ATTRIBUTE6 => X_ATTRIBUTE6,
652 X_ATTRIBUTE7 => X_ATTRIBUTE7,
653 X_ATTRIBUTE8 => X_ATTRIBUTE8,
654 X_ATTRIBUTE9 => X_ATTRIBUTE9,
655 X_ATTRIBUTE10 => X_ATTRIBUTE10,
656 X_CONTEXT => X_CONTEXT,
657 X_INSTALLED_CP_STATUS_ID => to_number(X_INSTALLED_CP_STATUS_ID),
658 X_ATTRIBUTE11 => X_ATTRIBUTE11,
659 X_ATTRIBUTE12 => X_ATTRIBUTE12,
660 X_INSTALLED_STATUS_CODE => X_INSTALLED_STATUS_CODE,
661 X_ATTRIBUTE13 => X_ATTRIBUTE13,
662 X_ATTRIBUTE14 => X_ATTRIBUTE14,
663 X_ATTRIBUTE15 => X_ATTRIBUTE15,
664 X_INSTALLED_CP_RETURN_REQUIRED => X_INSTALLED_CP_RETURN_REQUIRED,
665 X_NO_CHARGE_FLAG => X_NO_CHARGE_FLAG,
666 X_CREATE_CHARGE_FLAG =>X_CREATE_CHARGE_FLAG,--added by bkanimoz
667 X_CREATE_COST_FLAG =>X_CREATE_COST_FLAG ,--
668 X_DEPOT_REPAIR_FLAG => X_DEPOT_REPAIR_FLAG,
669 X_NEW_CP_STATUS_ID => to_number(X_NEW_CP_STATUS_ID),
670 X_NEW_CP_STATUS_CODE => X_NEW_CP_STATUS_CODE,
671 X_TRANSFER_SERVICE => X_TRANSFER_SERVICE,
672 X_NEW_CP_RETURN_REQUIRED => X_NEW_CP_RETURN_REQUIRED,
673 X_NAME => X_NAME,
674 X_DESCRIPTION => X_DESCRIPTION,
675 X_LAST_UPDATE_DATE => sysdate,
676 X_LAST_UPDATED_BY => l_user_id,
677 X_LAST_UPDATE_LOGIN => 0,
678 X_MOVE_COUNTERS_FLAG => X_MOVE_COUNTERS_FLAG,
679 X_OBJECT_VERSION_NUMBER => l_object_ver,
680 X_INTERFACE_TO_OE_FLAG => X_INTERFACE_TO_OE_FLAG
681 );
682 END IF; -- if luby = 1
683
684 exception
685 when NO_DATA_FOUND then
686 INSERT_ROW (
687 X_ROWID => l_row_id,
688 X_TRANSACTION_TYPE_ID => to_number(X_TRANSACTION_TYPE_ID),
689 X_LINE_ORDER_CATEGORY_CODE => X_LINE_ORDER_CATEGORY_CODE,
690 X_SEEDED_FLAG => X_SEEDED_FLAG,
691 X_REVISION_FLAG => X_REVISION_FLAG,
692 X_END_DATE_ACTIVE => to_date(X_END_DATE_ACTIVE, 'YYYY/MM/DD'),
693 X_START_DATE_ACTIVE => to_date(X_START_DATE_ACTIVE, 'YYYY/MM/DD'),
694 X_ATTRIBUTE1 => X_ATTRIBUTE1,
695 X_ATTRIBUTE2 => X_ATTRIBUTE2,
696 X_ATTRIBUTE3 => X_ATTRIBUTE3,
697 X_ATTRIBUTE4 => X_ATTRIBUTE4,
698 X_ATTRIBUTE5 => X_ATTRIBUTE5,
699 X_ATTRIBUTE6 => X_ATTRIBUTE6,
700 X_ATTRIBUTE7 => X_ATTRIBUTE7,
701 X_ATTRIBUTE8 => X_ATTRIBUTE8,
702 X_ATTRIBUTE9 => X_ATTRIBUTE9 ,
703 X_ATTRIBUTE10 => X_ATTRIBUTE10,
704 X_CONTEXT => X_CONTEXT,
705 X_INSTALLED_CP_STATUS_ID => to_number(X_INSTALLED_CP_STATUS_ID),
706 X_ATTRIBUTE11 => X_ATTRIBUTE11,
707 X_ATTRIBUTE12 => X_ATTRIBUTE12,
708 X_INSTALLED_STATUS_CODE => X_INSTALLED_STATUS_CODE,
709 X_ATTRIBUTE13 => X_ATTRIBUTE13,
710 X_ATTRIBUTE14 => X_ATTRIBUTE14,
711 X_ATTRIBUTE15 => X_ATTRIBUTE15,
712 X_INSTALLED_CP_RETURN_REQUIRED => X_INSTALLED_CP_RETURN_REQUIRED,
713 X_NO_CHARGE_FLAG => X_NO_CHARGE_FLAG,
714 X_CREATE_CHARGE_FLAG =>X_CREATE_CHARGE_FLAG,--
715 X_CREATE_COST_FLAG =>X_CREATE_COST_FLAG ,--
716 X_DEPOT_REPAIR_FLAG => X_DEPOT_REPAIR_FLAG,
717 X_NEW_CP_STATUS_ID => to_number(X_NEW_CP_STATUS_ID),
718 X_NEW_CP_STATUS_CODE => X_NEW_CP_STATUS_CODE,
719 X_TRANSFER_SERVICE => X_TRANSFER_SERVICE,
720 X_NEW_CP_RETURN_REQUIRED => X_NEW_CP_RETURN_REQUIRED,
721 X_NAME => X_NAME,
722 X_DESCRIPTION => X_DESCRIPTION,
723 X_CREATION_DATE => sysdate,
724 X_CREATED_BY => l_user_id ,
725 X_LAST_UPDATE_DATE => sysdate,
726 X_LAST_UPDATED_BY => l_user_id,
727 X_LAST_UPDATE_LOGIN => 0,
728 X_MOVE_COUNTERS_FLAG =>X_MOVE_COUNTERS_FLAG,
729 X_OBJECT_VERSION_NUMBER => l_object_ver,
730 X_INTERFACE_TO_OE_FLAG => X_INTERFACE_TO_OE_FLAG
731 );
732 end;
733 end LOAD_ROW;
734
735 end CS_TRANSACTION_TYPES_PKG;