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