[Home] [Help]
PACKAGE BODY: APPS.CSD_REPAIR_TYPES_PKG
Source
1 package body CSD_REPAIR_TYPES_PKG as
2 /* $Header: csdtdrtb.pls 120.7.12020000.2 2013/02/27 23:17:18 vicli ship $ */
3
4 l_debug NUMBER := csd_gen_utility_pvt.g_debug_level;
5
6 procedure INSERT_ROW (
7 X_ROWID in OUT NOCOPY VARCHAR2,
8 X_REPAIR_TYPE_ID in NUMBER,
9 X_WORKFLOW_ITEM_TYPE in VARCHAR2,
10 X_START_DATE_ACTIVE in DATE,
11 X_END_DATE_ACTIVE in DATE,
12 X_ATTRIBUTE_CATEGORY in VARCHAR2,
13 X_ATTRIBUTE1 in VARCHAR2,
14 X_ATTRIBUTE2 in VARCHAR2,
15 X_ATTRIBUTE3 in VARCHAR2,
16 X_ATTRIBUTE4 in VARCHAR2,
17 X_ATTRIBUTE5 in VARCHAR2,
18 X_ATTRIBUTE6 in VARCHAR2,
19 X_ATTRIBUTE7 in VARCHAR2,
20 X_ATTRIBUTE8 in VARCHAR2,
21 X_ATTRIBUTE9 in VARCHAR2,
22 X_ATTRIBUTE10 in VARCHAR2,
23 X_ATTRIBUTE11 in VARCHAR2,
24 X_ATTRIBUTE12 in VARCHAR2,
25 X_ATTRIBUTE13 in VARCHAR2,
26 X_ATTRIBUTE14 in VARCHAR2,
27 X_ATTRIBUTE15 in VARCHAR2,
28 X_DESCRIPTION in VARCHAR2,
29 X_NAME in VARCHAR2,
30 X_CREATION_DATE in DATE,
31 X_CREATED_BY in NUMBER,
32 X_LAST_UPDATE_DATE in DATE,
33 X_LAST_UPDATED_BY in NUMBER,
34 X_LAST_UPDATE_LOGIN in NUMBER,
35 X_REPAIR_MODE in Varchar2,
36 X_INTERFACE_TO_OM_FLAG in Varchar2,
37 X_BOOK_SALES_ORDER_FLAG in Varchar2,
38 X_RELEASE_SALES_ORDER_FLAG in Varchar2,
39 X_SHIP_SALES_ORDER_FLAG in Varchar2,
40 X_AUTO_PROCESS_RMA in Varchar2,
41 X_SEEDED_FLAG in Varchar2,
42 X_REPAIR_TYPE_REF in Varchar2,
43 X_BUSINESS_PROCESS_ID in NUMBER,
44 X_PRICE_LIST_HEADER_ID in NUMBER,
45 X_CPR_TXN_BILLING_TYPE_ID in NUMBER,
46 X_CPS_TXN_BILLING_TYPE_ID in NUMBER,
47 X_LR_TXN_BILLING_TYPE_ID in NUMBER,
48 X_LS_TXN_BILLING_TYPE_ID in NUMBER,
49 X_THIRD_SHIP_TXN_B_TYPE_ID in NUMBER := null,
50 X_THIRD_RMA_TXN_B_TYPE_ID in NUMBER := null,
51 X_MTL_TXN_BILLING_TYPE_ID in NUMBER,
52 X_LBR_TXN_BILLING_TYPE_ID in NUMBER,
53 X_EXP_TXN_BILLING_TYPE_ID in NUMBER,
54 X_INTERNAL_ORDER_FLAG in Varchar2,
55 X_THIRD_PARTY_FLAG in Varchar2 := null,
56 X_OBJECT_VERSION_NUMBER in Number,
57 X_START_FLOW_STATUS_ID in Number,
58 X_RES_DT_CALC_POINT IN VARCHAR2 := null
59 ) is
60 cursor C is select ROWID from CSD_REPAIR_TYPES_B
61 where REPAIR_TYPE_ID = X_REPAIR_TYPE_ID;
62 begin
63 insert into CSD_REPAIR_TYPES_B (
64 REPAIR_TYPE_ID,
65 WORKFLOW_ITEM_TYPE,
66 START_DATE_ACTIVE,
67 END_DATE_ACTIVE,
68 ATTRIBUTE_CATEGORY,
69 ATTRIBUTE1,
70 ATTRIBUTE2,
71 ATTRIBUTE3,
72 ATTRIBUTE4,
73 ATTRIBUTE5,
74 ATTRIBUTE6,
75 ATTRIBUTE7,
76 ATTRIBUTE8,
77 ATTRIBUTE9,
78 ATTRIBUTE10,
79 ATTRIBUTE11,
80 ATTRIBUTE12,
81 ATTRIBUTE13,
82 ATTRIBUTE14,
83 ATTRIBUTE15,
84 CREATION_DATE,
85 CREATED_BY,
86 LAST_UPDATE_DATE,
87 LAST_UPDATED_BY,
88 LAST_UPDATE_LOGIN,
89 REPAIR_MODE,
90 INTERFACE_TO_OM_FLAG,
91 BOOK_SALES_ORDER_FLAG,
92 RELEASE_SALES_ORDER_FLAG,
93 SHIP_SALES_ORDER_FLAG,
94 AUTO_PROCESS_RMA,
95 SEEDED_FLAG,
96 REPAIR_TYPE_REF,
97 BUSINESS_PROCESS_ID,
98 PRICE_LIST_HEADER_ID,
99 CPR_TXN_BILLING_TYPE_ID,
100 CPS_TXN_BILLING_TYPE_ID,
101 LR_TXN_BILLING_TYPE_ID,
102 LS_TXN_BILLING_TYPE_ID,
103 THIRD_SHIP_TXN_BILLING_TYPE_ID,
104 THIRD_RMA_TXN_BILLING_TYPE_ID,
105 MTL_TXN_BILLING_TYPE_ID,
106 LBR_TXN_BILLING_TYPE_ID,
107 EXP_TXN_BILLING_TYPE_ID,
108 INTERNAL_ORDER_FLAG,
109 THIRD_PARTY_FLAG,
110 OBJECT_VERSION_NUMBER,
111 START_FLOW_STATUS_ID,
112 RES_DT_CALC_POINT
113 ) values (
114 X_REPAIR_TYPE_ID,
115 X_WORKFLOW_ITEM_TYPE,
116 X_START_DATE_ACTIVE,
117 X_END_DATE_ACTIVE,
118 X_ATTRIBUTE_CATEGORY,
119 X_ATTRIBUTE1,
120 X_ATTRIBUTE2,
121 X_ATTRIBUTE3,
122 X_ATTRIBUTE4,
123 X_ATTRIBUTE5,
124 X_ATTRIBUTE6,
125 X_ATTRIBUTE7,
126 X_ATTRIBUTE8,
127 X_ATTRIBUTE9,
128 X_ATTRIBUTE10,
129 X_ATTRIBUTE11,
130 X_ATTRIBUTE12,
131 X_ATTRIBUTE13,
132 X_ATTRIBUTE14,
133 X_ATTRIBUTE15,
134 X_CREATION_DATE,
135 X_CREATED_BY,
136 X_LAST_UPDATE_DATE,
137 X_LAST_UPDATED_BY,
138 X_LAST_UPDATE_LOGIN,
139 X_REPAIR_MODE,
140 X_INTERFACE_TO_OM_FLAG,
141 X_BOOK_SALES_ORDER_FLAG,
142 X_RELEASE_SALES_ORDER_FLAG,
143 X_SHIP_SALES_ORDER_FLAG,
144 X_AUTO_PROCESS_RMA,
145 X_SEEDED_FLAG,
146 X_REPAIR_TYPE_REF,
147 X_BUSINESS_PROCESS_ID,
148 X_PRICE_LIST_HEADER_ID,
149 X_CPR_TXN_BILLING_TYPE_ID,
150 X_CPS_TXN_BILLING_TYPE_ID,
151 X_LR_TXN_BILLING_TYPE_ID,
152 X_LS_TXN_BILLING_TYPE_ID,
153 X_THIRD_SHIP_TXN_B_TYPE_ID,
154 X_THIRD_RMA_TXN_B_TYPE_ID,
155 X_MTL_TXN_BILLING_TYPE_ID,
156 X_LBR_TXN_BILLING_TYPE_ID,
157 X_EXP_TXN_BILLING_TYPE_ID,
158 X_INTERNAL_ORDER_FLAG,
159 X_THIRD_PARTY_FLAG,
160 X_OBJECT_VERSION_NUMBER,
161 X_START_FLOW_STATUS_ID,
162 X_RES_DT_CALC_POINT
163 );
164
165 insert into CSD_REPAIR_TYPES_TL (
166 REPAIR_TYPE_ID,
167 CREATED_BY,
168 CREATION_DATE,
169 LAST_UPDATED_BY,
170 LAST_UPDATE_DATE,
171 LAST_UPDATE_LOGIN,
172 NAME,
173 DESCRIPTION,
174 LANGUAGE,
175 SOURCE_LANG
176 ) select
177 X_REPAIR_TYPE_ID,
178 X_CREATED_BY,
179 X_CREATION_DATE,
180 X_LAST_UPDATED_BY,
181 X_LAST_UPDATE_DATE,
182 X_LAST_UPDATE_LOGIN,
183 X_NAME,
184 X_DESCRIPTION,
185 L.LANGUAGE_CODE,
186 userenv('LANG')
187 from FND_LANGUAGES L
188 where L.INSTALLED_FLAG in ('I', 'B')
189 and not exists
190 (select NULL
191 from CSD_REPAIR_TYPES_TL T
192 where T.REPAIR_TYPE_ID = X_REPAIR_TYPE_ID
193 and T.LANGUAGE = L.LANGUAGE_CODE);
194
195 open c;
196 fetch c into X_ROWID;
197 if (c%notfound) then
198 close c;
199 raise no_data_found;
200 end if;
201 close c;
202
203 end INSERT_ROW;
204
205 procedure LOCK_ROW (
206 X_REPAIR_TYPE_ID in NUMBER,
207 X_OBJECT_VERSION_NUMBER in Number
208 ) is
209
210 cursor c is
211 select object_version_number
212 from CSD_REPAIR_TYPES_B
213 where REPAIR_TYPE_ID = X_REPAIR_TYPE_ID
214 for update nowait;
215
216 recinfo c%rowtype;
217
218 begin
219 open c;
220 fetch c into recinfo;
221 if (c%notfound) then
222 close c;
223 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
224 app_exception.raise_exception;
225 end if;
226
227 IF l_debug > 0 THEN
228 csd_gen_utility_pvt.add('CSD_REPAIR_TYPES_PKG recinfo.object_version_number : '||recinfo.object_version_number);
229 csd_gen_utility_pvt.add('CSD_REPAIR_TYPES_PKG x_object_version_number : '||x_object_version_number);
230 END IF;
231
232 if(recinfo.object_version_number = x_object_version_number) then
233 null;
234 else
235 close c;
236 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
237 app_exception.raise_exception;
238 end if;
239
240 close c;
241 end LOCK_ROW;
242
243 procedure UPDATE_ROW (
244 X_REPAIR_TYPE_ID in NUMBER,
245 X_WORKFLOW_ITEM_TYPE in VARCHAR2,
246 X_START_DATE_ACTIVE in DATE,
247 X_END_DATE_ACTIVE in DATE,
248 X_ATTRIBUTE_CATEGORY in VARCHAR2,
249 X_ATTRIBUTE1 in VARCHAR2,
250 X_ATTRIBUTE2 in VARCHAR2,
251 X_ATTRIBUTE3 in VARCHAR2,
252 X_ATTRIBUTE4 in VARCHAR2,
253 X_ATTRIBUTE5 in VARCHAR2,
254 X_ATTRIBUTE6 in VARCHAR2,
255 X_ATTRIBUTE7 in VARCHAR2,
256 X_ATTRIBUTE8 in VARCHAR2,
257 X_ATTRIBUTE9 in VARCHAR2,
258 X_ATTRIBUTE10 in VARCHAR2,
259 X_ATTRIBUTE11 in VARCHAR2,
260 X_ATTRIBUTE12 in VARCHAR2,
261 X_ATTRIBUTE13 in VARCHAR2,
262 X_ATTRIBUTE14 in VARCHAR2,
263 X_ATTRIBUTE15 in VARCHAR2,
264 X_DESCRIPTION in VARCHAR2,
265 X_NAME in VARCHAR2,
266 X_LAST_UPDATE_DATE in DATE,
267 X_LAST_UPDATED_BY in NUMBER,
268 X_LAST_UPDATE_LOGIN in NUMBER,
269 X_REPAIR_MODE in Varchar2,
270 X_INTERFACE_TO_OM_FLAG in Varchar2,
271 X_BOOK_SALES_ORDER_FLAG in Varchar2,
272 X_RELEASE_SALES_ORDER_FLAG in Varchar2,
273 X_SHIP_SALES_ORDER_FLAG in Varchar2,
274 X_AUTO_PROCESS_RMA in Varchar2,
275 X_SEEDED_FLAG in Varchar2,
276 X_REPAIR_TYPE_REF in Varchar2,
277 X_BUSINESS_PROCESS_ID in Number,
278 X_PRICE_LIST_HEADER_ID in Number,
279 X_CPR_TXN_BILLING_TYPE_ID in Number,
280 X_CPS_TXN_BILLING_TYPE_ID in Number,
281 X_LR_TXN_BILLING_TYPE_ID in Number,
282 X_LS_TXN_BILLING_TYPE_ID in Number,
283 X_THIRD_SHIP_TXN_B_TYPE_ID in Number,
284 X_THIRD_RMA_TXN_B_TYPE_ID in Number,
285 X_MTL_TXN_BILLING_TYPE_ID in Number,
286 X_LBR_TXN_BILLING_TYPE_ID in Number,
287 X_EXP_TXN_BILLING_TYPE_ID in Number,
288 X_INTERNAL_ORDER_FLAG in Varchar2,
289 X_THIRD_PARTY_FLAG in Varchar2,
290 X_OBJECT_VERSION_NUMBER in Number,
291 X_START_FLOW_STATUS_ID in Number,
292 X_RES_DT_CALC_POINT IN VARCHAR2 := fnd_api.g_miss_char
293 ) is
294 begin
295 update CSD_REPAIR_TYPES_B set
296 WORKFLOW_ITEM_TYPE = X_WORKFLOW_ITEM_TYPE,
297 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
298 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
299 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
300 ATTRIBUTE1 = X_ATTRIBUTE1,
301 ATTRIBUTE2 = X_ATTRIBUTE2,
302 ATTRIBUTE3 = X_ATTRIBUTE3,
303 ATTRIBUTE4 = X_ATTRIBUTE4,
304 ATTRIBUTE5 = X_ATTRIBUTE5,
305 ATTRIBUTE6 = X_ATTRIBUTE6,
306 ATTRIBUTE7 = X_ATTRIBUTE7,
307 ATTRIBUTE8 = X_ATTRIBUTE8,
308 ATTRIBUTE9 = X_ATTRIBUTE9,
309 ATTRIBUTE10 = X_ATTRIBUTE10,
310 ATTRIBUTE11 = X_ATTRIBUTE11,
311 ATTRIBUTE12 = X_ATTRIBUTE12,
312 ATTRIBUTE13 = X_ATTRIBUTE13,
313 ATTRIBUTE14 = X_ATTRIBUTE14,
314 ATTRIBUTE15 = X_ATTRIBUTE15,
315 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
316 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
317 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
318 REPAIR_MODE = X_REPAIR_MODE,
319 INTERFACE_TO_OM_FLAG = X_INTERFACE_TO_OM_FLAG,
320 BOOK_SALES_ORDER_FLAG = X_BOOK_SALES_ORDER_FLAG,
321 RELEASE_SALES_ORDER_FLAG = X_RELEASE_SALES_ORDER_FLAG,
322 SHIP_SALES_ORDER_FLAG = X_SHIP_SALES_ORDER_FLAG,
323 AUTO_PROCESS_RMA = X_AUTO_PROCESS_RMA,
324 SEEDED_FLAG = X_SEEDED_FLAG,
325 REPAIR_TYPE_REF = X_REPAIR_TYPE_REF,
326 BUSINESS_PROCESS_ID = X_BUSINESS_PROCESS_ID,
327 PRICE_LIST_HEADER_ID = X_PRICE_LIST_HEADER_ID,
328 CPR_TXN_BILLING_TYPE_ID = X_CPR_TXN_BILLING_TYPE_ID,
329 CPS_TXN_BILLING_TYPE_ID = X_CPS_TXN_BILLING_TYPE_ID,
330 LR_TXN_BILLING_TYPE_ID = X_LR_TXN_BILLING_TYPE_ID,
331 LS_TXN_BILLING_TYPE_ID = X_LS_TXN_BILLING_TYPE_ID,
332 THIRD_SHIP_TXN_BILLING_TYPE_ID = decode( X_THIRD_SHIP_TXN_B_TYPE_ID, FND_API.G_MISS_NUM, THIRD_SHIP_TXN_BILLING_TYPE_ID, X_THIRD_SHIP_TXN_B_TYPE_ID),
333 THIRD_RMA_TXN_BILLING_TYPE_ID = decode( X_THIRD_RMA_TXN_B_TYPE_ID, FND_API.G_MISS_NUM, THIRD_RMA_TXN_BILLING_TYPE_ID, X_THIRD_RMA_TXN_B_TYPE_ID),
334 MTL_TXN_BILLING_TYPE_ID = X_MTL_TXN_BILLING_TYPE_ID,
335 LBR_TXN_BILLING_TYPE_ID = X_LBR_TXN_BILLING_TYPE_ID,
336 EXP_TXN_BILLING_TYPE_ID = X_EXP_TXN_BILLING_TYPE_ID,
337 INTERNAL_ORDER_FLAG = X_INTERNAL_ORDER_FLAG,
338 THIRD_PARTY_FLAG = decode( X_THIRD_PARTY_FLAG, FND_API.G_MISS_CHAR, THIRD_PARTY_FLAG, X_THIRD_PARTY_FLAG),
339 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
340 START_FLOW_STATUS_ID = X_START_FLOW_STATUS_ID,
341 RES_DT_CALC_POINT = decode(X_RES_DT_CALC_POINT, FND_API.G_MISS_CHAR, RES_DT_CALC_POINT, X_RES_DT_CALC_POINT)
342 where REPAIR_TYPE_ID = X_REPAIR_TYPE_ID;
343
344 if (sql%notfound) then
345 raise no_data_found;
346 end if;
347
348 update CSD_REPAIR_TYPES_TL set
349 DESCRIPTION = X_DESCRIPTION,
350 NAME = X_NAME,
351 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
352 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
353 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
354 SOURCE_LANG = userenv('LANG')
355 where REPAIR_TYPE_ID = X_REPAIR_TYPE_ID
356 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
357
358 if (sql%notfound) then
359 raise no_data_found;
360 end if;
361 end UPDATE_ROW;
362
363 procedure DELETE_ROW (
364 X_REPAIR_TYPE_ID in NUMBER
365 ) is
366 begin
367 delete from CSD_REPAIR_TYPES_TL
368 where REPAIR_TYPE_ID = X_REPAIR_TYPE_ID;
369
370 if (sql%notfound) then
371 raise no_data_found;
372 end if;
373
374 delete from CSD_REPAIR_TYPES_B
375 where REPAIR_TYPE_ID = X_REPAIR_TYPE_ID;
376
377 if (sql%notfound) then
378 raise no_data_found;
379 end if;
380 end DELETE_ROW;
381
382 procedure ADD_LANGUAGE
383 is
384 begin
385 delete from CSD_REPAIR_TYPES_TL T
386 where not exists
387 (select NULL
388 from CSD_REPAIR_TYPES_B B
389 where B.REPAIR_TYPE_ID = T.REPAIR_TYPE_ID
390 );
391
392 update CSD_REPAIR_TYPES_TL T set (
393 DESCRIPTION,
394 NAME
395 ) = (select
396 B.DESCRIPTION,
397 B.NAME
398 from CSD_REPAIR_TYPES_TL B
399 where B.REPAIR_TYPE_ID = T.REPAIR_TYPE_ID
400 and B.LANGUAGE = T.SOURCE_LANG)
401 where (
402 T.REPAIR_TYPE_ID,
403 T.LANGUAGE
404 ) in (select
405 SUBT.REPAIR_TYPE_ID,
406 SUBT.LANGUAGE
407 from CSD_REPAIR_TYPES_TL SUBB, CSD_REPAIR_TYPES_TL SUBT
408 where SUBB.REPAIR_TYPE_ID = SUBT.REPAIR_TYPE_ID
409 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
410 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
411 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
412 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
413 or SUBB.NAME <> SUBT.NAME
414 ));
415
416 insert into CSD_REPAIR_TYPES_TL (
417 REPAIR_TYPE_ID,
418 CREATED_BY,
419 CREATION_DATE,
420 LAST_UPDATED_BY,
421 LAST_UPDATE_DATE,
422 LAST_UPDATE_LOGIN,
423 NAME,
424 DESCRIPTION,
425 LANGUAGE,
426 SOURCE_LANG
427 ) select
428 B.REPAIR_TYPE_ID,
429 B.CREATED_BY,
430 B.CREATION_DATE,
431 B.LAST_UPDATED_BY,
432 B.LAST_UPDATE_DATE,
433 B.LAST_UPDATE_LOGIN,
434 B.NAME,
435 B.DESCRIPTION,
436 L.LANGUAGE_CODE,
437 B.SOURCE_LANG
438 from CSD_REPAIR_TYPES_TL B, FND_LANGUAGES L
439 where L.INSTALLED_FLAG in ('I', 'B')
440 and B.LANGUAGE = userenv('LANG')
441 and not exists
442 (select NULL
443 from CSD_REPAIR_TYPES_TL T
444 where T.REPAIR_TYPE_ID = B.REPAIR_TYPE_ID
445 and T.LANGUAGE = L.LANGUAGE_CODE);
446 end ADD_LANGUAGE;
447
448 PROCEDURE Translate_Row
449 (p_repair_type_id IN NUMBER
450 ,p_name IN VARCHAR2
451 ,p_description IN VARCHAR2
452 ,p_owner IN VARCHAR2
453 )
454 IS
455 --
456 l_user_id NUMBER := 0;
457 --
458 BEGIN
459 --
460 if p_owner = 'SEED' then
461 l_user_id := 1;
462 end if;
463 --
464 UPDATE csd_repair_types_tl
465 SET description = p_description,
466 name = p_name,
467 last_update_date = SYSDATE,
468 last_updated_by = l_user_id,
469 last_update_login = 0,
470 source_lang = userenv('LANG')
471 WHERE repair_type_id = p_repair_type_id
472 AND userenv('LANG') in (language, source_lang);
473 --
474 END Translate_Row;
475
476 PROCEDURE Load_Row
477 (p_repair_type_id IN NUMBER
478 ,p_name IN VARCHAR2
479 ,p_description IN VARCHAR2
480 ,p_workflow_item_type IN VARCHAR2
481 ,p_start_date_active IN DATE
482 ,p_end_date_active IN DATE
483 ,p_owner IN VARCHAR2
484 ,p_repair_mode IN VARCHAR2
485 ,p_interface_to_om_flag IN VARCHAR2
486 ,p_book_sales_order_flag IN VARCHAR2
487 ,p_release_sales_order_flag IN VARCHAR2
488 ,p_ship_sales_order_flag IN VARCHAR2
489 ,p_auto_process_rma IN VARCHAR2
490 ,p_seeded_flag IN VARCHAR2
491 ,p_repair_type_ref IN VARCHAR2
492 ,p_business_process_id IN NUMBER
493 ,p_price_list_header_id IN NUMBER
494 ,p_cpr_txn_billing_type_id IN NUMBER
495 ,p_cps_txn_billing_type_id IN NUMBER
496 ,p_lr_txn_billing_type_id IN NUMBER
497 ,p_ls_txn_billing_type_id IN NUMBER
498 --,p_third_ship_txn_b_type_id IN NUMBER := null
499 --,p_third_rma_txn_b_type_id IN NUMBER := null
500 ,p_mtl_txn_billing_type_id IN NUMBER
501 ,p_lbr_txn_billing_type_id IN NUMBER
502 ,p_exp_txn_billing_type_id IN NUMBER
503 ,p_object_version_number IN NUMBER
504 ,p_internal_order_flag IN VARCHAR2 := null
505 -- ,p_third_party_flag IN VARCHAR2 := null
506 )
507 IS
508 --
509 l_rowid ROWID;
510 l_user_id NUMBER := 0;
511 l_count number := 0;
512
513 CURSOR Cur_repair_type_count(p_repair_type_id IN NUMBER) IS
514 select count(*) from CSD_REPAIR_TYPES_B
515 where repair_type_id = p_repair_type_id and last_updated_by <> 1;
516
517 --
518 BEGIN
519 --
520 if p_owner = 'SEED' then
521 l_user_id := 1;
522 end if;
523 --
524
525 OPEN Cur_repair_type_count(p_repair_type_id);
526 FETCH Cur_repair_type_count INTO l_count;
527 CLOSE Cur_repair_type_count;
528
529 if (l_count >= 1) then
530 return;
531 end if;
532
533 Update_Row(
534 X_REPAIR_TYPE_ID => p_repair_type_id,
535 X_WORKFLOW_ITEM_TYPE => p_workflow_item_type,
536 X_START_DATE_ACTIVE => p_start_date_active,
537 X_END_DATE_ACTIVE => p_end_date_active,
538 X_ATTRIBUTE_CATEGORY => null,
539 X_ATTRIBUTE1 => null,
540 X_ATTRIBUTE2 => null,
541 X_ATTRIBUTE3 => null,
542 X_ATTRIBUTE4 => null,
543 X_ATTRIBUTE5 => null,
544 X_ATTRIBUTE6 => null,
545 X_ATTRIBUTE7 => null,
546 X_ATTRIBUTE8 => null,
547 X_ATTRIBUTE9 => null,
548 X_ATTRIBUTE10 => null,
549 X_ATTRIBUTE11 => null,
550 X_ATTRIBUTE12 => null,
551 X_ATTRIBUTE13 => null,
552 X_ATTRIBUTE14 => null,
553 X_ATTRIBUTE15 => null,
554 X_DESCRIPTION => p_description,
555 X_NAME => p_name,
556 X_LAST_UPDATE_DATE => sysdate,
557 X_LAST_UPDATED_BY => l_user_id,
558 X_LAST_UPDATE_LOGIN => 0,
559 X_REPAIR_MODE => p_repair_mode,
560 X_INTERFACE_TO_OM_FLAG => p_interface_to_om_flag,
561 X_BOOK_SALES_ORDER_FLAG => p_book_sales_order_flag,
562 X_RELEASE_SALES_ORDER_FLAG => p_release_sales_order_flag,
563 X_SHIP_SALES_ORDER_FLAG => p_ship_sales_order_flag,
564 X_AUTO_PROCESS_RMA => p_auto_process_rma,
565 X_SEEDED_FLAG => p_seeded_flag,
566 X_REPAIR_TYPE_REF => p_repair_type_ref,
567 X_BUSINESS_PROCESS_ID => p_business_process_id,
568 X_PRICE_LIST_HEADER_ID => p_price_list_header_id,
569 X_CPR_TXN_BILLING_TYPE_ID => p_cpr_txn_billing_type_id,
570 X_CPS_TXN_BILLING_TYPE_ID => p_cps_txn_billing_type_id,
571 X_LR_TXN_BILLING_TYPE_ID => p_lr_txn_billing_type_id,
572 X_LS_TXN_BILLING_TYPE_ID => p_ls_txn_billing_type_id,
573 -- X_THIRD_SHIP_TXN_B_TYPE_ID => p_third_ship_txn_b_type_id,
574 -- X_THIRD_RMA_TXN_B_TYPE_ID => p_third_rma_txn_b_type_id,
575 X_MTL_TXN_BILLING_TYPE_ID => p_mtl_txn_billing_type_id,
576 X_LBR_TXN_BILLING_TYPE_ID => p_lbr_txn_billing_type_id,
577 X_EXP_TXN_BILLING_TYPE_ID => p_exp_txn_billing_type_id,
578 X_INTERNAL_ORDER_FLAG => p_internal_order_flag,
579 -- X_THIRD_PARTY_FLAG => p_third_party_flag,
580 X_OBJECT_VERSION_NUMBER => p_object_version_number,
581 X_START_FLOW_STATUS_ID => null
582 );
583 EXCEPTION
584 WHEN NO_DATA_FOUND THEN
585 Insert_Row(
586 X_ROWID => l_rowid,
587 X_REPAIR_TYPE_ID => p_repair_type_id,
588 X_WORKFLOW_ITEM_TYPE => p_workflow_item_type,
589 X_START_DATE_ACTIVE => p_start_date_active,
590 X_END_DATE_ACTIVE => p_end_date_active,
591 X_ATTRIBUTE_CATEGORY => null,
592 X_ATTRIBUTE1 => null,
593 X_ATTRIBUTE2 => null,
594 X_ATTRIBUTE3 => null,
595 X_ATTRIBUTE4 => null,
596 X_ATTRIBUTE5 => null,
597 X_ATTRIBUTE6 => null,
598 X_ATTRIBUTE7 => null,
599 X_ATTRIBUTE8 => null,
600 X_ATTRIBUTE9 => null,
601 X_ATTRIBUTE10 => null,
602 X_ATTRIBUTE11 => null,
603 X_ATTRIBUTE12 => null,
604 X_ATTRIBUTE13 => null,
605 X_ATTRIBUTE14 => null,
606 X_ATTRIBUTE15 => null,
607 X_DESCRIPTION => p_description,
608 X_NAME => p_name,
609 X_CREATION_DATE => sysdate,
610 X_CREATED_BY => l_user_id,
611 X_LAST_UPDATE_DATE => sysdate,
612 X_LAST_UPDATED_BY => l_user_id,
613 X_LAST_UPDATE_LOGIN => 0,
614 X_REPAIR_MODE => p_repair_mode,
615 X_INTERFACE_TO_OM_FLAG => p_interface_to_om_flag,
616 X_BOOK_SALES_ORDER_FLAG => p_book_sales_order_flag,
617 X_RELEASE_SALES_ORDER_FLAG => p_release_sales_order_flag,
618 X_SHIP_SALES_ORDER_FLAG => p_ship_sales_order_flag,
619 X_AUTO_PROCESS_RMA => p_auto_process_rma,
620 X_SEEDED_FLAG => p_seeded_flag,
621 X_REPAIR_TYPE_REF => p_repair_type_ref,
622 X_BUSINESS_PROCESS_ID => p_business_process_id,
623 X_PRICE_LIST_HEADER_ID => p_price_list_header_id,
624 X_CPR_TXN_BILLING_TYPE_ID => p_cpr_txn_billing_type_id,
625 X_CPS_TXN_BILLING_TYPE_ID => p_cps_txn_billing_type_id,
626 X_LR_TXN_BILLING_TYPE_ID => p_lr_txn_billing_type_id,
627 X_LS_TXN_BILLING_TYPE_ID => p_ls_txn_billing_type_id,
628 -- X_THIRD_SHIP_TXN_B_TYPE_ID => p_third_ship_txn_b_type_id,
629 -- X_THIRD_RMA_TXN_B_TYPE_ID => p_third_rma_txn_b_type_id,
630 X_MTL_TXN_BILLING_TYPE_ID => p_mtl_txn_billing_type_id,
631 X_LBR_TXN_BILLING_TYPE_ID => p_lbr_txn_billing_type_id,
632 X_EXP_TXN_BILLING_TYPE_ID => p_exp_txn_billing_type_id,
633 X_INTERNAL_ORDER_FLAG => p_internal_order_flag,
634 -- X_THIRD_PARTY_FLAG => p_third_party_flag,
635 X_OBJECT_VERSION_NUMBER => p_object_version_number,
636 X_START_FLOW_STATUS_ID => null
637 );
638 --
639 END Load_Row;
640 --
641 end CSD_REPAIR_TYPES_PKG;