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