[Home] [Help]
PACKAGE BODY: APPS.ENG_CHANGE_ORDER_TYPES_PKG
Source
1 package body ENG_CHANGE_ORDER_TYPES_PKG as
2 /* $Header: ENGCTYPEB.pls 120.3 2006/01/30 02:24:11 pdutta noship $ */
3
4 TYPE number_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5
6 procedure ENG_LC_STATUSES_INSERT (
7 X_ROWID in out nocopy VARCHAR2,
8 X_CHANGE_LIFECYCLE_STATUS_ID in NUMBER,
9 X_CHANGE_WF_ROUTE_ID in NUMBER,
10 X_AUTO_PROMOTE_STATUS in NUMBER,
11 X_AUTO_DEMOTE_STATUS in NUMBER,
12 X_WORKFLOW_STATUS in VARCHAR2,
13 X_CHANGE_EDITABLE_FLAG in VARCHAR2,
14 X_ENTITY_ID4 in NUMBER,
15 X_ENTITY_ID5 in NUMBER,
16 X_ENTITY_NAME in VARCHAR2,
17 X_ENTITY_ID1 in NUMBER,
18 X_ENTITY_ID2 in NUMBER,
19 X_ENTITY_ID3 in NUMBER,
20 X_COMPLETION_DATE in DATE,
21 X_STATUS_CODE in NUMBER,
22 X_START_DATE in DATE,
23 X_SEQUENCE_NUMBER in NUMBER,
24 X_ITERATION_NUMBER in NUMBER,
25 X_ACTIVE_FLAG in VARCHAR2,
26 X_CREATION_DATE in DATE,
27 X_CREATED_BY in NUMBER,
28 X_LAST_UPDATE_DATE in DATE,
29 X_LAST_UPDATED_BY in NUMBER,
30 X_LAST_UPDATE_LOGIN in NUMBER)
31 IS
32 cursor C is select ROWID from ENG_LIFECYCLE_STATUSES
33 where CHANGE_LIFECYCLE_STATUS_ID = X_CHANGE_LIFECYCLE_STATUS_ID;
34 BEGIN
35 insert into ENG_LIFECYCLE_STATUSES (
36 CHANGE_WF_ROUTE_ID,
37 AUTO_PROMOTE_STATUS,
38 AUTO_DEMOTE_STATUS,
39 WORKFLOW_STATUS,
40 CHANGE_EDITABLE_FLAG,
41 ENTITY_ID4,
42 ENTITY_ID5,
43 CHANGE_LIFECYCLE_STATUS_ID,
44 ENTITY_NAME,
45 ENTITY_ID1,
46 ENTITY_ID2,
47 ENTITY_ID3,
48 COMPLETION_DATE,
49 STATUS_CODE,
50 START_DATE,
51 SEQUENCE_NUMBER,
52 ITERATION_NUMBER,
53 ACTIVE_FLAG,
54 CREATION_DATE,
55 CREATED_BY,
56 LAST_UPDATE_DATE,
57 LAST_UPDATED_BY,
58 LAST_UPDATE_LOGIN
59 ) values (
60 X_CHANGE_WF_ROUTE_ID,
61 X_AUTO_PROMOTE_STATUS,
62 X_AUTO_DEMOTE_STATUS,
63 X_WORKFLOW_STATUS,
64 X_CHANGE_EDITABLE_FLAG,
65 X_ENTITY_ID4,
66 X_ENTITY_ID5,
67 X_CHANGE_LIFECYCLE_STATUS_ID,
68 X_ENTITY_NAME,
69 X_ENTITY_ID1,
70 X_ENTITY_ID2,
71 X_ENTITY_ID3,
72 X_COMPLETION_DATE,
73 X_STATUS_CODE,
74 X_START_DATE,
75 X_SEQUENCE_NUMBER,
76 X_ITERATION_NUMBER,
77 X_ACTIVE_FLAG,
78 X_CREATION_DATE,
79 X_CREATED_BY,
80 X_LAST_UPDATE_DATE,
81 X_LAST_UPDATED_BY,
82 X_LAST_UPDATE_LOGIN
83 );
84
85 OPEN c;
86 FETCH c INTO X_ROWID;
87 IF (c%notfound) then
88 close c;
89 raise no_data_found;
90 END IF;
91 CLOSE c;
92
93 END ENG_LC_STATUSES_INSERT;
94
95 procedure ENG_DUPLICATE_LC_PROP (
96 X_CHANGE_LIFECYCLE_STATUS_ID in NUMBER ,
97 X_LIFECYCLE_PARENT_ID in NUMBER ,
98 X_CREATION_DATE in DATE ,
99 X_CREATED_BY in NUMBER ,
100 X_LAST_UPDATE_LOGIN in NUMBER )
101 IS
102 CURSOR status_properties IS
103 SELECT *
104 FROM ENG_STATUS_PROPERTIES
105 WHERE CHANGE_LIFECYCLE_STATUS_ID = X_LIFECYCLE_PARENT_ID;
106 BEGIN
107 FOR sp in status_properties
108 LOOP
109 INSERT INTO ENG_STATUS_PROPERTIES (
110 CHANGE_LIFECYCLE_STATUS_ID,
111 STATUS_CODE,
112 PROMOTION_STATUS_FLAG,
113 CREATION_DATE,
114 CREATED_BY,
115 LAST_UPDATE_DATE,
116 LAST_UPDATED_BY,
117 LAST_UPDATE_LOGIN ) values (
118 X_CHANGE_LIFECYCLE_STATUS_ID,
119 sp.STATUS_CODE,
120 sp.PROMOTION_STATUS_FLAG,
121 X_CREATION_DATE,
122 X_CREATED_BY,
123 X_CREATION_DATE,
124 X_CREATED_BY,
125 X_LAST_UPDATE_LOGIN);
126 END LOOP;
127 END ENG_DUPLICATE_LC_PROP;
128
129 procedure DUPLICATE_LIFECYCLES (
130 X_CHANGE_ORDER_TYPE_ID in NUMBER,
131 X_PARENT_CHANGE_TYPE_ID in NUMBER,
132 X_CREATED_BY in NUMBER,
133 X_CREATION_DATE in DATE,
134 X_LAST_UPDATE_LOGIN in NUMBER)
135 IS
136 v_row_id VARCHAR2(100);
137 l_lifecycle_phase_id NUMBER;
138
139 CURSOR lifecycles IS
140 SELECT *
141 FROM ENG_LIFECYCLE_STATUSES
142 WHERE ENTITY_NAME = 'ENG_CHANGE_TYPE'
143 AND ENTITY_ID1 = X_PARENT_CHANGE_TYPE_ID;
144 BEGIN
145 FOR cl in lifecycles
146 LOOP
147 SELECT eng_lifecycle_statuses_s.nextval
148 INTO l_lifecycle_phase_id
149 FROM dual;
150
151 ENG_LC_STATUSES_INSERT (
152 X_ROWID =>v_row_id ,
153 X_CHANGE_LIFECYCLE_STATUS_ID =>l_lifecycle_phase_id ,
154 X_CHANGE_WF_ROUTE_ID =>cl.CHANGE_WF_ROUTE_ID ,
155 X_AUTO_PROMOTE_STATUS =>cl.AUTO_PROMOTE_STATUS ,
156 X_AUTO_DEMOTE_STATUS =>cl.AUTO_DEMOTE_STATUS ,
157 X_WORKFLOW_STATUS =>cl.WORKFLOW_STATUS ,
158 X_CHANGE_EDITABLE_FLAG =>cl.CHANGE_EDITABLE_FLAG ,
159 X_ENTITY_ID4 =>cl.ENTITY_ID4 ,
160 X_ENTITY_ID5 =>cl.ENTITY_ID5 ,
161 X_ENTITY_NAME =>cl.ENTITY_NAME ,
162 X_ENTITY_ID1 =>X_CHANGE_ORDER_TYPE_ID ,
163 X_ENTITY_ID2 =>cl.ENTITY_ID2 ,
164 X_ENTITY_ID3 =>cl.ENTITY_ID3 ,
165 X_COMPLETION_DATE =>cl.COMPLETION_DATE ,
166 X_STATUS_CODE =>cl.STATUS_CODE ,
167 X_START_DATE =>cl.START_DATE ,
168 X_SEQUENCE_NUMBER =>cl.SEQUENCE_NUMBER ,
169 X_ITERATION_NUMBER =>0 ,
170 X_ACTIVE_FLAG =>'Y' ,
171 X_CREATION_DATE =>X_CREATION_DATE ,
172 X_CREATED_BY =>X_CREATED_BY ,
173 X_LAST_UPDATE_DATE =>X_CREATION_DATE ,
174 X_LAST_UPDATED_BY =>X_CREATED_BY ,
175 X_LAST_UPDATE_LOGIN =>X_LAST_UPDATE_LOGIN);
176
177 ENG_DUPLICATE_LC_PROP (
178 X_CHANGE_LIFECYCLE_STATUS_ID =>l_lifecycle_phase_id ,
179 X_LIFECYCLE_PARENT_ID =>cl.CHANGE_LIFECYCLE_STATUS_ID ,
180 X_CREATION_DATE =>X_CREATION_DATE ,
181 X_CREATED_BY =>X_CREATED_BY ,
182 X_LAST_UPDATE_LOGIN =>X_LAST_UPDATE_LOGIN);
183 END LOOP;
184 END DUPLICATE_LIFECYCLES;
185
186 procedure DUPLICATE_PRIORITIES (
187 X_CHANGE_ORDER_TYPE_ID in NUMBER,
188 X_PARENT_CHANGE_TYPE_ID in NUMBER,
189 X_CREATED_BY in NUMBER,
190 X_CREATION_DATE in DATE,
191 X_LAST_UPDATE_LOGIN in NUMBER)
192 IS
193 v_row_id VARCHAR2(100);
194 l_priority_code VARCHAR2(10);
195
196 CURSOR priorities IS
197 SELECT *
198 FROM ENG_CHANGE_TYPE_PRIORITIES
199 WHERE CHANGE_TYPE_ID = X_PARENT_CHANGE_TYPE_ID;
200 BEGIN
201 FOR cl in priorities
202 LOOP
203 INSERT INTO ENG_CHANGE_TYPE_PRIORITIES
204 (CHANGE_TYPE_ID,
205 PRIORITY_CODE,
206 CREATION_DATE,
207 CREATED_BY,
208 LAST_UPDATE_DATE,
209 LAST_UPDATED_BY,
210 LAST_UPDATE_LOGIN) VALUES (
211 X_CHANGE_ORDER_TYPE_ID,
212 cl.PRIORITY_CODE,
213 X_CREATION_DATE,
214 X_CREATED_BY,
215 X_CREATION_DATE,
216 X_CREATED_BY,
217 X_LAST_UPDATE_LOGIN);
218 END LOOP;
219 END DUPLICATE_PRIORITIES;
220
221 procedure DUPLICATE_REASONS (
222 X_CHANGE_ORDER_TYPE_ID in NUMBER,
223 X_PARENT_CHANGE_TYPE_ID in NUMBER,
224 X_CREATED_BY in NUMBER,
225 X_CREATION_DATE in DATE,
226 X_LAST_UPDATE_LOGIN in NUMBER)
227 IS
228 v_row_id VARCHAR2(100);
229 l_reason_code VARCHAR2(10);
230
231 CURSOR REASONS IS
232 SELECT *
233 FROM ENG_CHANGE_TYPE_REASONS
234 WHERE CHANGE_TYPE_ID = X_PARENT_CHANGE_TYPE_ID;
235 BEGIN
236 FOR cl in REASONS
237 LOOP
238 INSERT INTO ENG_CHANGE_TYPE_REASONS
239 (CHANGE_TYPE_ID,
240 REASON_CODE,
241 CREATION_DATE,
242 CREATED_BY,
243 LAST_UPDATE_DATE,
244 LAST_UPDATED_BY,
245 LAST_UPDATE_LOGIN) VALUES (
246 X_CHANGE_ORDER_TYPE_ID,
247 cl.REASON_CODE,
248 X_CREATION_DATE,
249 X_CREATED_BY,
250 X_CREATION_DATE,
251 X_CREATED_BY,
252 X_LAST_UPDATE_LOGIN);
253 END LOOP;
254 END DUPLICATE_REASONS;
255
256 procedure DUPLICATE_CLASSCODES (
257 X_CHANGE_ORDER_TYPE_ID in NUMBER,
258 X_PARENT_CHANGE_TYPE_ID in NUMBER,
259 X_CREATED_BY in NUMBER,
260 X_CREATION_DATE in DATE,
261 X_LAST_UPDATE_LOGIN in NUMBER)
262 IS
263 v_row_id VARCHAR2(100);
264 l_code_id NUMBER;
265
266 CURSOR CLASSCODES IS
267 SELECT *
268 FROM ENG_CHANGE_TYPE_CLASS_CODES
269 WHERE CHANGE_TYPE_ID = X_PARENT_CHANGE_TYPE_ID;
270 BEGIN
271 FOR cl in CLASSCODES
272 LOOP
273 INSERT INTO ENG_CHANGE_TYPE_CLASS_CODES
274 (CHANGE_TYPE_ID,
275 CLASSIFICATION_ID,
276 CREATION_DATE,
277 CREATED_BY,
278 LAST_UPDATE_DATE,
279 LAST_UPDATED_BY,
280 LAST_UPDATE_LOGIN) VALUES (
281 X_CHANGE_ORDER_TYPE_ID,
282 cl.CLASSIFICATION_ID,
283 X_CREATION_DATE,
284 X_CREATED_BY,
285 X_CREATION_DATE,
286 X_CREATED_BY,
287 X_LAST_UPDATE_LOGIN);
288 END LOOP;
289 END DUPLICATE_CLASSCODES;
290
291 --Bug No: 3439555
292 --Issue: DEF-1473
293 --Procedure to insert the attribute/sections being
294 --defaulted.
295 procedure INSERT_TYPE_CONFIGURATION (
296 X_CONFIGURATION_TYPE in VARCHAR2,
297 X_CODE in VARCHAR2,
298 X_DISPLAY_SEQUENCE in NUMBER,
299 X_REGION_CODE in VARCHAR2,
300 X_CLASSIFICAITON1 in VARCHAR2,
301 X_CLASSIFICAITON2 in NUMBER,
302 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
303 X_CREATED_BY in NUMBER,
304 X_CREATION_DATE in DATE,
305 X_LAST_UPDATE_LOGIN in NUMBER)
306 IS
307 l_customization_code VARCHAR2(200);
308 l_return_status VARCHAR2(30);
309 l_error_code NUMBER;
310 BEGIN
311
312 l_customization_code := X_CLASSIFICAITON2 || X_CLASSIFICAITON1;
313 ENG_TYPE_CONFIGURATION_PKG.create_type_config
314 (
315 X_CUSTOMIZATION_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
316 X_CUSTOMIZATION_CODE => l_customization_code,
317 X_REGION_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
318 X_REGION_CODE => X_REGION_CODE,
319 X_NAME => 'CONFIG',
320 X_CREATED_BY => X_CREATED_BY,
321 X_CREATION_DATE => X_CREATION_DATE,
322 X_LAST_UPDATED_BY => X_CREATED_BY,
323 X_LAST_UPDATE_DATE => X_CREATION_DATE,
324 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
325 X_CLASSIFICATION_1 => X_CLASSIFICAITON1,
326 X_CLASSIFICATION_2 => X_CLASSIFICAITON2,
327 X_CLASSIFICATION_3 => null,
328 X_RETURN_STATUS => l_return_status,
329 X_ERRORCODE => l_error_code
330 );
331
332 IF (X_CONFIGURATION_TYPE = 'ATTRIBUTE')
333 THEN
334 ENG_TYPE_CONFIGURATION_PKG.create_Primary_Attribute
335 (
336 X_CUSTOMIZATION_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
337 X_CUSTOMIZATION_CODE => l_customization_code,
338 X_REGION_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
339 X_REGION_CODE => X_REGION_CODE,
340 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
341 X_ATTRIBUTE_CODE => X_CODE,
342 X_DISPLAY_SEQUENCE => X_DISPLAY_SEQUENCE,
343 X_ORDER_SEQUENCE => null,
344 X_ORDER_DIRECTION => null,
345 X_COLUMN_NAME => null,
346 X_SHOW_TOTAL => null,
347 X_CREATED_BY => X_CREATED_BY,
348 X_CREATION_DATE => X_CREATION_DATE,
349 X_LAST_UPDATED_BY => X_CREATED_BY,
350 X_LAST_UPDATE_DATE => X_CREATION_DATE,
351 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
352 X_RETURN_STATUS => l_return_status,
353 X_ERRORCODE => l_error_code
354 );
355 ELSE
356 ENG_TYPE_CONFIGURATION_PKG.create_config_section
357 (
358 X_CUSTOMIZATION_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
359 X_CUSTOMIZATION_CODE => l_customization_code,
360 X_REGION_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
361 X_REGION_CODE => X_REGION_CODE,
362 X_ATTRIBUTE_APPLICATION_ID => X_ATTRIBUTE_APPLICATION_ID,
363 X_ATTRIBUTE_CODE => X_CODE,
364 X_DISPLAY_SEQUENCE => X_DISPLAY_SEQUENCE,
365 X_CREATED_BY => X_CREATED_BY,
366 X_CREATION_DATE => X_CREATION_DATE,
367 X_LAST_UPDATED_BY => X_CREATED_BY,
368 X_LAST_UPDATE_DATE => X_CREATION_DATE,
369 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
370 X_RETURN_STATUS => l_return_status,
371 X_ERRORCODE => l_error_code
372 );
373 END IF;
374 END INSERT_TYPE_CONFIGURATION;
375
376 --Bug No: 3439555
377 --Issue: DEF-1473
378 --Adding the default configuration 'Change_Notice'
379 --for all header types.
380 procedure ADD_DEFAULT_CONFIGURATIONS (
381 X_CHANGE_ORDER_TYPE_ID in NUMBER,
382 X_CHANGE_MGMT_TYPE_CODE in VARCHAR2,
383 X_CREATED_BY in NUMBER,
384 X_CREATION_DATE in DATE,
385 X_LAST_UPDATE_LOGIN in NUMBER)
386 IS
387 BEGIN
388 INSERT_TYPE_CONFIGURATION (
389 X_CONFIGURATION_TYPE =>'ATTRIBUTE' ,
390 X_CODE =>'CHANGE_NOTICE' ,
391 X_DISPLAY_SEQUENCE =>0 ,
392 X_REGION_CODE =>'ENG_ADMIN_CONFIGURATIONS' ,
393 X_CLASSIFICAITON1 =>X_CHANGE_MGMT_TYPE_CODE ,
394 X_CLASSIFICAITON2 =>X_CHANGE_ORDER_TYPE_ID ,
395 X_ATTRIBUTE_APPLICATION_ID =>703 ,
396 X_CREATION_DATE =>X_CREATION_DATE ,
397 X_CREATED_BY =>X_CREATED_BY ,
398 X_LAST_UPDATE_LOGIN =>X_LAST_UPDATE_LOGIN);
399 END ADD_DEFAULT_CONFIGURATIONS;
400
401 procedure ADD_DEFAULT_LIFECYCLES (
402 X_CHANGE_ORDER_TYPE_ID in NUMBER,
403 X_BASE_CHANGE_MGMT_TYPE in VARCHAR2,
404 X_CREATED_BY in NUMBER,
405 X_CREATION_DATE in DATE,
406 X_LAST_UPDATE_LOGIN in NUMBER)
407 IS
408 phase_types number_list;
409 v_row_id VARCHAR2(100);
410 l_lifecycle_phase_id NUMBER;
411 l_seq_no NUMBER := 0;
412 l_phase_id NUMBER := null;
413 l_route_id NUMBER := null;
414 BEGIN
415 /* 30-JAN-2006: For DOM Lifecycles categories, no lifecycle is seeded
416 */
417 IF ( X_BASE_CHANGE_MGMT_TYPE = 'DOM_DOCUMENT_LIFECYCLE')
418 THEN
419 return;
420 END IF;
421
422 /* 24-FEB-2004: Changing the sequence of the phases to Open->
423 Released->Scheduled->Implemented
424 */
425 /* This procedure defaults the lifecycles for new header types.
426 If the header type is a change order type then 4 phases:
427 Open
428 Released
429 Scheduled
430 Implemented
431 are defaulted. In case it is of non change-order type then the
432 following two phases are defaulted.
433 Open
434 Implemented
435 */
436 /* Bug No: 3983759
437 11-FEB-2005: Changing the sequnece of phases for 'Document Approval
438 to Approval->Implemented.
439
440 21-FEB-2005: Changing the sequence of statuses for 'Document Review
441 to Review->Implemented.
442 */
443
444 IF ( X_BASE_CHANGE_MGMT_TYPE = 'ATTACHMENT_APPROVAL')
445 THEN
446 phase_types(1) := 8; --'Approval'
447 ELSIF ( X_BASE_CHANGE_MGMT_TYPE = 'ATTACHMENT_REVIEW')
448 THEN
449 phase_types(1) := 12; --'Review'
450 ELSE
451 phase_types(1) := 1; --'Open'
452 END IF;
453
454 IF ( X_BASE_CHANGE_MGMT_TYPE = 'CHANGE_ORDER')
455 THEN
456 --phase_types(2) := 4; --'Scheduled'
457 --phase_types(3) := 7; --'Released'
458 phase_types(2) := 7; --'Released'
459 phase_types(3) := 4; --'Scheduled'
460 phase_types(4) := 6; --'Implemented'
461 ELSE
462 phase_types(2) := 11; --'Completed'
463 END IF;
464
465 l_phase_id := phase_types.first;
466 WHILE l_phase_id is not null
467 LOOP
468 l_seq_no := l_seq_no +10;
469 l_route_id := null;
470
471 SELECT eng_lifecycle_statuses_s.nextval
472 INTO l_lifecycle_phase_id
473 FROM dual;
474
475 IF (phase_types(l_phase_id) = 8) -- approval status code
476 THEN
477 SELECT a.route_id
478 INTO l_route_id
479 FROM eng_change_routes_tl a,
480 eng_change_routes b
481 WHERE a.ROUTE_NAME = 'Standard Approval Process'
482 AND b.route_id = a.route_id
483 AND a.language = 'US'
484 AND b.TEMPLATE_FLAG = 'Y';
485 END IF;
486
487 ENG_LC_STATUSES_INSERT (
488 X_ROWID =>v_row_id,
489 X_CHANGE_LIFECYCLE_STATUS_ID =>l_lifecycle_phase_id ,
490 X_CHANGE_WF_ROUTE_ID =>l_route_id ,
491 X_AUTO_PROMOTE_STATUS =>null ,
492 X_AUTO_DEMOTE_STATUS =>null ,
493 X_WORKFLOW_STATUS =>null ,
494 X_CHANGE_EDITABLE_FLAG =>null ,
495 X_ENTITY_ID4 =>null ,
496 X_ENTITY_ID5 =>null ,
497 X_ENTITY_NAME =>'ENG_CHANGE_TYPE' ,
498 X_ENTITY_ID1 =>X_CHANGE_ORDER_TYPE_ID ,
499 X_ENTITY_ID2 =>null ,
500 X_ENTITY_ID3 =>null ,
501 X_COMPLETION_DATE =>null ,
502 X_STATUS_CODE =>phase_types(l_phase_id) ,
503 X_START_DATE =>null ,
504 X_SEQUENCE_NUMBER =>l_seq_no ,
505 X_ITERATION_NUMBER =>0 ,
506 X_ACTIVE_FLAG =>'Y' ,
507 X_CREATION_DATE =>X_CREATION_DATE ,
508 X_CREATED_BY =>X_CREATED_BY ,
509 X_LAST_UPDATE_DATE =>X_CREATION_DATE ,
510 X_LAST_UPDATED_BY =>X_CREATED_BY ,
511 X_LAST_UPDATE_LOGIN =>X_LAST_UPDATE_LOGIN);
512
513 l_phase_id := phase_types.next(l_phase_id);
514 END LOOP;
515 END ADD_DEFAULT_LIFECYCLES;
516
517 procedure DUPLICATE_ATTR_SECT (
518 X_CHANGE_ORDER_TYPE_ID in NUMBER,
519 X_CREATED_BY in NUMBER,
520 X_CREATION_DATE in DATE,
521 X_TYPE_ID in NUMBER,
522 X_LAST_UPDATE_LOGIN in NUMBER,
523 X_CHANGE_MGMT_TYPE_CODE in VARCHAR2,
524 X_BASE_CHANGE_MGMT_TYPE_CODE in VARCHAR2)
525 IS
526 l_return_status VARCHAR2(30);
527 l_error_code NUMBER;
528 l_name VARCHAR2(2000);
529 l_customization_code VARCHAR2(200);
530 l_classification2 VARCHAR2(30);
531
532 CURSOR attr_sects_c IS
533 Select
534 AK_ATTRIBUTES.ATTRIBUTE_APPLICATION_ID ATTRIBUTE_APPLICATION_ID,
535 AK_ATTRIBUTES.ATTRIBUTE_CODE ATTRIBUTE_CODE,
536 AK_ATTRIBUTES.PROPERTY_NAME PROPERTY_NAME,
537 AK_ATTRIBUTES.PROPERTY_NUMBER_VALUE PROPERTY_NUMBER_VALUE
538 FROM
539 AK_CUSTOM_REGION_ITEMS AK_ATTRIBUTES,
540 EGO_CUSTOMIZATION_EXT ATTRIBUTE_EXT
541 WHERE
542 AK_ATTRIBUTES.CUSTOMIZATION_APPLICATION_ID = ATTRIBUTE_EXT.CUSTOMIZATION_APPLICATION_ID
543 AND AK_ATTRIBUTES.CUSTOMIZATION_CODE = ATTRIBUTE_EXT.CUSTOMIZATION_CODE
544 AND AK_ATTRIBUTES.REGION_APPLICATION_ID = ATTRIBUTE_EXT.REGION_APPLICATION_ID
545 AND ATTRIBUTE_EXT.REGION_APPLICATION_ID = 703
546 AND AK_ATTRIBUTES.REGION_CODE = ATTRIBUTE_EXT.REGION_CODE
547 AND ATTRIBUTE_EXT.REGION_CODE = 'ENG_ADMIN_CONFIGURATIONS'
548 AND ( (X_TYPE_ID is NULL AND ( ATTRIBUTE_EXT.CLASSIFICATION2 is NULL
549 AND ATTRIBUTE_EXT.CLASSIFICATION1 = X_BASE_CHANGE_MGMT_TYPE_CODE))
550 OR( X_TYPE_ID is NOT NULL AND ( ATTRIBUTE_EXT.CLASSIFICATION2 = X_TYPE_ID
551 AND ATTRIBUTE_EXT.CLASSIFICATION1 = X_CHANGE_MGMT_TYPE_CODE)));
552 BEGIN
553 --l_customization_code := X_CHANGE_MGMT_TYPE_CODE||'_ENG_ADMIN_CONFIGR'; -- Commented as this gives values too large
554 IF X_TYPE_ID IS NULL
555 THEN
556 l_customization_code := X_CHANGE_MGMT_TYPE_CODE;
557 l_classification2 := null;
558 ELSE
559 l_customization_code := X_CHANGE_ORDER_TYPE_ID || X_CHANGE_MGMT_TYPE_CODE;
560 l_classification2 := X_CHANGE_ORDER_TYPE_ID;
561 END IF;
562
563 ENG_TYPE_CONFIGURATION_PKG.create_type_config
564 (
565 X_CUSTOMIZATION_APPLICATION_ID => 703,
566 X_CUSTOMIZATION_CODE => l_customization_code, --Modify if duplicating for types also
567 X_REGION_APPLICATION_ID => 703,
568 X_REGION_CODE => 'ENG_ADMIN_CONFIGURATIONS',
569 X_NAME => 'CONFIG',
570 X_CREATED_BY => X_CREATED_BY,
571 X_CREATION_DATE => X_CREATION_DATE,
572 X_LAST_UPDATED_BY => X_CREATED_BY,
573 X_LAST_UPDATE_DATE => X_CREATION_DATE,
574 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
575 X_CLASSIFICATION_1 => X_CHANGE_MGMT_TYPE_CODE,
576 --X_CLASSIFICATION_2 => null, -- Put X_TYPE_ID if duplicating for type also.
577 X_CLASSIFICATION_2 => l_classification2,
578 X_CLASSIFICATION_3 => null,
579 X_RETURN_STATUS => l_return_status,
580 X_ERRORCODE => l_error_code
581 );
582
583 FOR atsec IN attr_sects_c
584 LOOP
585 IF (atsec.PROPERTY_NAME = 'DISPLAY_SEQUENCE')
586 THEN
587 ENG_TYPE_CONFIGURATION_PKG.create_Primary_Attribute
588 (
589 X_CUSTOMIZATION_APPLICATION_ID => 703,
590 X_CUSTOMIZATION_CODE => l_customization_code,
591 X_REGION_APPLICATION_ID => 703,
592 X_REGION_CODE => 'ENG_ADMIN_CONFIGURATIONS',
593 X_ATTRIBUTE_APPLICATION_ID => atsec.ATTRIBUTE_APPLICATION_ID,
594 X_ATTRIBUTE_CODE => atsec.ATTRIBUTE_CODE,
595 X_DISPLAY_SEQUENCE => atsec.PROPERTY_NUMBER_VALUE,
596 X_ORDER_SEQUENCE => null,
597 X_ORDER_DIRECTION => null,
598 X_COLUMN_NAME => null,
599 X_SHOW_TOTAL => null,
600 X_CREATED_BY => X_CREATED_BY,
601 X_CREATION_DATE => X_CREATION_DATE,
602 X_LAST_UPDATED_BY => X_CREATED_BY,
603 X_LAST_UPDATE_DATE => X_CREATION_DATE,
604 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
605 X_RETURN_STATUS => l_return_status,
606 X_ERRORCODE => l_error_code
607 );
608 END IF;
609 IF (atsec.PROPERTY_NAME = 'SECTION_SEQUENCE')
610 THEN
611 ENG_TYPE_CONFIGURATION_PKG.create_config_section
612 (
613 X_CUSTOMIZATION_APPLICATION_ID => 703,
614 X_CUSTOMIZATION_CODE => l_customization_code,
615 X_REGION_APPLICATION_ID => 703,
616 X_REGION_CODE => 'ENG_ADMIN_CONFIGURATIONS',
617 X_ATTRIBUTE_APPLICATION_ID => atsec.ATTRIBUTE_APPLICATION_ID,
618 X_ATTRIBUTE_CODE => atsec.ATTRIBUTE_CODE,
619 X_DISPLAY_SEQUENCE => atsec.PROPERTY_NUMBER_VALUE,
620 X_CREATED_BY => X_CREATED_BY,
621 X_CREATION_DATE => X_CREATION_DATE,
622 X_LAST_UPDATED_BY => X_CREATED_BY,
623 X_LAST_UPDATE_DATE => X_CREATION_DATE,
624 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
625 X_RETURN_STATUS => l_return_status,
626 X_ERRORCODE => l_error_code
627 );
628 END IF;
629 END LOOP;
630
631 END DUPLICATE_ATTR_SECT;
632
633 procedure DUPLICATE_TYPES (
634 X_CHANGE_ORDER_TYPE_ID in NUMBER,
635 X_CREATED_BY in NUMBER,
636 X_CREATION_DATE in DATE,
637 X_LAST_UPDATE_LOGIN in NUMBER,
638 X_CHANGE_MGMT_TYPE_CODE in VARCHAR2,
639 X_SEEDED_FLAG in VARCHAR2,
640 X_BASE_CHANGE_MGMT_TYPE_CODE in VARCHAR2)
641 IS
642 v_row_id VARCHAR2(100);
643 l_change_order_type_id NUMBER;
644 l_default_assignee_id NUMBER;
645 l_subject_id NUMBER;
646 l_default_assignee_type VARCHAR2(30);
647 l_type_classification VARCHAR2(30);
648 l_class_code_derived_flag VARCHAR2(1);
649 l_assembly_type NUMBER;
650 l_disable_date DATE;
651 l_start_date DATE;
652 l_type_name VARCHAR2(80);
653 l_description VARCHAR2(240);
654 l_tab_text VARCHAR2(80);
655 l_enable_item_in_local_org VARCHAR2(1);
656 l_create_bom_in_local_org VARCHAR2(1);
657 l_subject_updatable_flag VARCHAR2(1);
658 l_base_change_mgmt_type_code VARCHAR2(30);
659 c_start_date DATE;
660
661 BEGIN
662 SELECT eng_change_order_types_s.nextval
663 INTO l_change_order_type_id
664 FROM dual;
665
666 SELECT default_assignee_id,
667 subject_id,
668 default_assignee_type,
669 type_classification,
670 class_code_derived_flag,
671 assembly_type,
672 disable_date,
673 start_date,
674 type_name,
675 description,
676 tab_text,
677 enable_item_in_local_org,
678 create_bom_in_local_org,
679 subject_updatable_flag,
680 base_change_mgmt_type_code
681 INTO l_default_assignee_id,
682 l_subject_id,
683 l_default_assignee_type,
684 l_type_classification,
685 l_class_code_derived_flag,
686 l_assembly_type,
687 l_disable_date,
688 l_start_date,
689 l_type_name,
690 l_description,
691 l_tab_text,
692 l_enable_item_in_local_org,
693 l_create_bom_in_local_org,
694 l_subject_updatable_flag,
695 l_base_change_mgmt_type_code --Added for Bug No:3497234, Issue: DEF-2071
696 FROM ENG_CHANGE_ORDER_TYPES_VL
697 WHERE
698 CHANGE_ORDER_TYPE_ID = X_CHANGE_ORDER_TYPE_ID;
699
700 SELECT START_DATE INTO c_start_date
701 FROM ENG_CHANGE_ORDER_TYPES_VL
702 WHERE CHANGE_MGMT_TYPE_CODE = X_CHANGE_MGMT_TYPE_CODE
703 AND TYPE_CLASSIFICATION = 'CATEGORY';
704
705 ENG_CHANGE_ORDER_TYPES_PKG.INSERT_ROW(
706 X_ROWID => v_row_id
707 , X_CHANGE_ORDER_TYPE_ID => l_change_order_type_id
708 , X_CHANGE_MGMT_TYPE_CODE => X_CHANGE_MGMT_TYPE_CODE
709 , X_START_DATE => c_start_date -- Start date should be same as category's start date
710 , X_DEFAULT_ASSIGNEE_ID => l_default_assignee_id
711 , X_SUBJECT_ID => l_subject_id
712 , X_AUTO_NUMBERING_METHOD => 'USR_ENT'
713 , X_DEFAULT_ASSIGNEE_TYPE => l_default_assignee_type
714 , X_TYPE_CLASSIFICATION => l_type_classification
715 , X_CLASS_CODE_DERIVED_FLAG => l_class_code_derived_flag
716 , X_SEQUENCE_NUMBER => null
717 --, X_BASE_CHANGE_MGMT_TYPE_CODE => X_BASE_CHANGE_MGMT_TYPE_CODE
718 , X_BASE_CHANGE_MGMT_TYPE_CODE => l_base_change_mgmt_type_code
719 , X_SEEDED_FLAG => X_SEEDED_FLAG
720 , X_ATTRIBUTE8 => null
721 , X_ATTRIBUTE9 => null
722 , X_ATTRIBUTE10 => null
723 , X_ATTRIBUTE11 => null
724 , X_ATTRIBUTE12 => null
725 , X_ATTRIBUTE13 => null
726 , X_ATTRIBUTE14 => null
727 , X_ATTRIBUTE15 => null
728 , X_CHANGE_ORDER_ORGANIZATION_ID => null
729 , X_ASSEMBLY_TYPE => l_assembly_type
730 , X_DISABLE_DATE => null
731 , X_ATTRIBUTE_CATEGORY => null
732 , X_ATTRIBUTE1 => null
733 , X_ATTRIBUTE2 => null
734 , X_ATTRIBUTE3 => null
735 , X_ATTRIBUTE4 => null
736 , X_ATTRIBUTE5 => null
737 , X_ATTRIBUTE6 => null
738 , X_ATTRIBUTE7 => null
739 , X_TYPE_NAME => l_type_name
740 , X_DESCRIPTION => l_description
741 , X_TAB_TEXT => l_tab_text
742 , X_CREATION_DATE => X_CREATION_DATE
743 , X_CREATED_BY => X_CREATED_BY
744 , X_LAST_UPDATE_DATE => X_CREATION_DATE
745 , X_LAST_UPDATED_BY => X_CREATED_BY
746 , X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
747 , X_ENABLE_ITEM_IN_LOCAL_ORG => l_enable_item_in_local_org
748 , X_CREATE_BOM_IN_LOCAL_ORG => l_create_bom_in_local_org
749 , X_SUBJECT_UPDATABLE_FLAG => l_subject_updatable_flag);
750 END DUPLICATE_TYPES;
751
752 procedure DUPLICATE_CATEGORY_ENTRIES (
753 X_CHANGE_ORDER_TYPE_ID in NUMBER,
754 X_CREATED_BY in NUMBER,
755 X_CREATION_DATE in DATE,
756 X_LAST_UPDATE_LOGIN in NUMBER,
757 X_CHANGE_MGMT_TYPE_CODE in VARCHAR2,
758 X_BASE_CHANGE_MGMT_TYPE_CODE in VARCHAR2)
759 IS
760
761 CURSOR get_applications IS
762 SELECT A.APPLICATION_ID
763 FROM ENG_CHANGE_TYPE_APPLICATIONS A,
764 ENG_CHANGE_ORDER_TYPES C
765 WHERE C.CHANGE_ORDER_TYPE_ID = A.CHANGE_TYPE_ID
766 AND C.TYPE_CLASSIFICATION = 'CATEGORY'
767 AND C.CHANGE_MGMT_TYPE_CODE = X_BASE_CHANGE_MGMT_TYPE_CODE;
768
769 CURSOR get_types IS
770 SELECT CHANGE_ORDER_TYPE_ID
771 FROM ENG_CHANGE_ORDER_TYPES
772 WHERE
773 --(TYPE_CLASSIFICATION = 'REVISED_LINE' OR TYPE_CLASSIFICATION = 'LINE')
774 --AND SEEDED_FLAG = 'Y'
775 TYPE_CLASSIFICATION = 'REVISED_LINE'
776 AND CHANGE_MGMT_TYPE_CODE = X_BASE_CHANGE_MGMT_TYPE_CODE;
777
778 BEGIN
779
780 -- Copying the entries for ENG_CHANGE_TYPE_APPLICATIONS
781 FOR appl IN get_applications
782 LOOP
783 INSERT INTO ENG_CHANGE_TYPE_APPLICATIONS
784 (CHANGE_TYPE_ID,
785 APPLICATION_ID,
786 CREATION_DATE,
787 CREATED_BY,
788 LAST_UPDATE_DATE,
789 LAST_UPDATED_BY,
790 LAST_UPDATE_LOGIN) VALUES
791 (X_CHANGE_ORDER_TYPE_ID,
792 appl.application_id,
793 X_CREATION_DATE,
794 X_CREATED_BY,
795 X_CREATION_DATE,
796 X_CREATED_BY,
797 X_LAST_UPDATE_LOGIN);
798 END LOOP;
799
800 -- Copying the seeded line types
801 FOR types IN get_types
802 LOOP
803 DUPLICATE_TYPES (
804 X_CHANGE_ORDER_TYPE_ID => types.CHANGE_ORDER_TYPE_ID,
805 X_CREATED_BY => X_CREATED_BY,
806 X_CREATION_DATE => X_CREATION_DATE,
807 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
808 X_CHANGE_MGMT_TYPE_CODE => X_CHANGE_MGMT_TYPE_CODE,
809 X_SEEDED_FLAG => 'N',
810 X_BASE_CHANGE_MGMT_TYPE_CODE => X_BASE_CHANGE_MGMT_TYPE_CODE);
811 END LOOP;
812
813 -- Copying the sections and attributes
814 DUPLICATE_ATTR_SECT (
815 X_CHANGE_ORDER_TYPE_ID => X_CHANGE_ORDER_TYPE_ID,
816 X_CREATED_BY => X_CREATED_BY,
817 X_CREATION_DATE => X_CREATION_DATE,
818 X_TYPE_ID => null,
819 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
820 X_CHANGE_MGMT_TYPE_CODE => X_CHANGE_MGMT_TYPE_CODE,
821 X_BASE_CHANGE_MGMT_TYPE_CODE => X_BASE_CHANGE_MGMT_TYPE_CODE);
822
823 END DUPLICATE_CATEGORY_ENTRIES;
824
825
826 procedure INSERT_ROW (
827 X_ROWID in out nocopy VARCHAR2,
828 X_CHANGE_ORDER_TYPE_ID in NUMBER,
829 X_CHANGE_MGMT_TYPE_CODE in VARCHAR2,
830 X_START_DATE in DATE,
831 X_DEFAULT_ASSIGNEE_ID in NUMBER,
832 X_SUBJECT_ID in NUMBER,
833 X_AUTO_NUMBERING_METHOD in VARCHAR2,
834 X_DEFAULT_ASSIGNEE_TYPE in VARCHAR2,
835 X_TYPE_CLASSIFICATION in VARCHAR2,
836 X_CLASS_CODE_DERIVED_FLAG in VARCHAR2,
837 X_SEQUENCE_NUMBER in NUMBER,
838 X_BASE_CHANGE_MGMT_TYPE_CODE in VARCHAR2,
839 X_SEEDED_FLAG in VARCHAR2,
840 X_ATTRIBUTE8 in VARCHAR2,
841 X_ATTRIBUTE9 in VARCHAR2,
842 X_ATTRIBUTE10 in VARCHAR2,
843 X_ATTRIBUTE11 in VARCHAR2,
844 X_ATTRIBUTE12 in VARCHAR2,
845 X_ATTRIBUTE13 in VARCHAR2,
846 X_ATTRIBUTE14 in VARCHAR2,
847 X_ATTRIBUTE15 in VARCHAR2,
848 X_CHANGE_ORDER_ORGANIZATION_ID in NUMBER,
849 X_ASSEMBLY_TYPE in NUMBER,
850 X_DISABLE_DATE in DATE,
851 X_ATTRIBUTE_CATEGORY in VARCHAR2,
852 X_ATTRIBUTE1 in VARCHAR2,
853 X_ATTRIBUTE2 in VARCHAR2,
854 X_ATTRIBUTE3 in VARCHAR2,
855 X_ATTRIBUTE4 in VARCHAR2,
856 X_ATTRIBUTE5 in VARCHAR2,
857 X_ATTRIBUTE6 in VARCHAR2,
858 X_ATTRIBUTE7 in VARCHAR2,
859 X_TYPE_NAME in VARCHAR2,
860 X_DESCRIPTION in VARCHAR2,
861 X_TAB_TEXT in VARCHAR2,
862 X_CREATION_DATE in DATE,
863 X_CREATED_BY in NUMBER,
864 X_LAST_UPDATE_DATE in DATE,
865 X_LAST_UPDATED_BY in NUMBER,
866 X_LAST_UPDATE_LOGIN in NUMBER,
867 X_ENABLE_ITEM_IN_LOCAL_ORG in VARCHAR2 DEFAULT NULL,
868 X_CREATE_BOM_IN_LOCAL_ORG in VARCHAR2 DEFAULT NULL,
869 X_SUBJECT_UPDATABLE_FLAG in VARCHAR2 DEFAULT NULL,
870 x_xml_data_source_code IN VARCHAR2 DEFAULT NULL,
871 X_TYPE_INTERNAL_NAME in VARCHAR2 DEFAULT NULL
872 ) is
873 cursor C is select ROWID from ENG_CHANGE_ORDER_TYPES
874 where CHANGE_ORDER_TYPE_ID = X_CHANGE_ORDER_TYPE_ID
875 ;
876 l_current_id NUMBER;
877 l_created_by NUMBER;
878 l_creation_date DATE;
879 l_last_update_login NUMBER;
880 l_assembly_type NUMBER;
881 l_change_mgmt_type_code VARCHAR2(30);
882 l_base_change_mgmt_type_code VARCHAR2(30);
883 l_parent_cat_id NUMBER;
884 l_subject_id NUMBER := null;
885 l_seq_id NUMBER := null;
886 l_auto_method VARCHAR2(10) := null;
887 l_class_code_derived_flag VARCHAR2(1);
888
889 begin
890 if ( X_ASSEMBLY_TYPE is null)
891 then
892 l_assembly_type := '2';
893 else
894 l_assembly_type := X_ASSEMBLY_TYPE;
895 end if;
896
897 l_subject_id := X_SUBJECT_ID;
898 l_seq_id := X_SEQUENCE_NUMBER;
899 l_auto_method := X_AUTO_NUMBERING_METHOD;
900
901 IF (l_auto_method is null)
902 THEN
903 l_auto_method := 'USR_ENT';
904 END IF;
905
906 IF (X_TYPE_CLASSIFICATION = 'CATEGORY')
907 THEN
908 l_parent_cat_id := X_SUBJECT_ID;
909 l_subject_id := null;
910 ELSE
911 l_parent_cat_id := X_SEQUENCE_NUMBER;
912 l_seq_id := null;
913 IF (l_subject_id is null)
914 THEN
915 SELECT subject_id
916 INTO l_subject_id
917 FROM eng_subject_entities
918 WHERE entity_name = 'ENG_CHANGE_MISC'
919 AND SUBJECT_LEVEL = 1;
920 END IF;
921 /* This is to default the classification code to Y for all header types created : bug 3686483 */
922 l_class_code_derived_flag := X_CLASS_CODE_DERIVED_FLAG;
923 IF (X_TYPE_CLASSIFICATION = 'HEADER' AND X_CLASS_CODE_DERIVED_FLAG is null)
924 THEN
925 l_class_code_derived_flag := 'Y';
926 END IF;
927 END IF;
928
929
930
931 insert into ENG_CHANGE_ORDER_TYPES (
932 CHANGE_MGMT_TYPE_CODE,
933 START_DATE,
934 DEFAULT_ASSIGNEE_ID,
935 SUBJECT_ID,
936 AUTO_NUMBERING_METHOD,
937 DEFAULT_ASSIGNEE_TYPE,
938 TYPE_CLASSIFICATION,
939 CLASS_CODE_DERIVED_FLAG,
940 SEQUENCE_NUMBER,
941 BASE_CHANGE_MGMT_TYPE_CODE,
942 SEEDED_FLAG,
943 ATTRIBUTE8,
944 ATTRIBUTE9,
945 ATTRIBUTE10,
946 ATTRIBUTE11,
947 ATTRIBUTE12,
948 ATTRIBUTE13,
949 ATTRIBUTE14,
950 ATTRIBUTE15,
951 CHANGE_ORDER_TYPE_ID,
952 CHANGE_ORDER_ORGANIZATION_ID,
953 ASSEMBLY_TYPE,
954 DISABLE_DATE,
955 ATTRIBUTE_CATEGORY,
956 ATTRIBUTE1,
957 ATTRIBUTE2,
958 ATTRIBUTE3,
959 ATTRIBUTE4,
960 ATTRIBUTE5,
961 ATTRIBUTE6,
962 ATTRIBUTE7,
963 CREATION_DATE,
964 CREATED_BY,
965 LAST_UPDATE_DATE,
966 LAST_UPDATED_BY,
967 LAST_UPDATE_LOGIN,
968 ENABLE_ITEM_IN_LOCAL_ORG,
969 CREATE_BOM_IN_LOCAL_ORG,
970 SUBJECT_UPDATABLE_FLAG,
971 XML_DATA_SOURCE_CODE,
972 CHANGE_ORDER_TYPE
973 ) values (
974 X_CHANGE_MGMT_TYPE_CODE,
975 X_START_DATE,
976 X_DEFAULT_ASSIGNEE_ID,
977 l_subject_id,
978 l_auto_method,
979 X_DEFAULT_ASSIGNEE_TYPE,
980 X_TYPE_CLASSIFICATION,
981 l_class_code_derived_flag,
982 l_seq_id,
983 X_BASE_CHANGE_MGMT_TYPE_CODE,
984 X_SEEDED_FLAG,
985 X_ATTRIBUTE8,
986 X_ATTRIBUTE9,
987 X_ATTRIBUTE10,
988 X_ATTRIBUTE11,
989 X_ATTRIBUTE12,
990 X_ATTRIBUTE13,
991 X_ATTRIBUTE14,
992 X_ATTRIBUTE15,
993 X_CHANGE_ORDER_TYPE_ID,
994 X_CHANGE_ORDER_ORGANIZATION_ID,
995 l_assembly_type,
996 X_DISABLE_DATE,
997 X_ATTRIBUTE_CATEGORY,
998 X_ATTRIBUTE1,
999 X_ATTRIBUTE2,
1000 X_ATTRIBUTE3,
1001 X_ATTRIBUTE4,
1002 X_ATTRIBUTE5,
1003 X_ATTRIBUTE6,
1004 X_ATTRIBUTE7,
1005 X_CREATION_DATE,
1006 X_CREATED_BY,
1007 X_LAST_UPDATE_DATE,
1008 X_LAST_UPDATED_BY,
1009 X_LAST_UPDATE_LOGIN,
1010 X_ENABLE_ITEM_IN_LOCAL_ORG ,
1011 X_CREATE_BOM_IN_LOCAL_ORG ,
1012 X_SUBJECT_UPDATABLE_FLAG,
1013 X_XML_DATA_SOURCE_CODE,
1014 X_TYPE_INTERNAL_NAME
1015 );
1016
1017 insert into ENG_CHANGE_ORDER_TYPES_TL (
1018 CHANGE_ORDER_TYPE_ID,
1019 TYPE_NAME,
1020 DESCRIPTION,
1021 TAB_TEXT,
1022 CREATION_DATE,
1023 CREATED_BY,
1024 LAST_UPDATE_DATE,
1025 LAST_UPDATED_BY,
1026 LAST_UPDATE_LOGIN,
1027 LANGUAGE,
1028 SOURCE_LANG
1029 ) select
1030 X_CHANGE_ORDER_TYPE_ID,
1031 X_TYPE_NAME,
1032 X_DESCRIPTION,
1033 X_TAB_TEXT,
1034 X_CREATION_DATE,
1035 X_CREATED_BY,
1036 X_LAST_UPDATE_DATE,
1037 X_LAST_UPDATED_BY,
1038 X_LAST_UPDATE_LOGIN,
1039 L.LANGUAGE_CODE,
1040 userenv('LANG')
1041 from FND_LANGUAGES L
1042 where L.INSTALLED_FLAG in ('I', 'B')
1043 and not exists
1044 (select NULL
1045 from ENG_CHANGE_ORDER_TYPES_TL T
1046 where T.CHANGE_ORDER_TYPE_ID = X_CHANGE_ORDER_TYPE_ID
1047 and T.LANGUAGE = L.LANGUAGE_CODE);
1048
1049 open c;
1050 fetch c into X_ROWID;
1051 if (c%notfound) then
1052 close c;
1053 raise no_data_found;
1054 end if;
1055 close c;
1056
1057 IF ((X_TYPE_CLASSIFICATION = 'CATEGORY') AND ( NVL(X_SEEDED_FLAG,'N') <> 'Y'))
1058 THEN
1059 IF (l_parent_cat_id is not null)
1060 THEN
1061
1062 SELECT CHANGE_MGMT_TYPE_CODE
1063 INTO l_base_change_mgmt_type_code
1064 FROM ENG_CHANGE_ORDER_TYPES_VL
1065 WHERE CHANGE_ORDER_TYPE_ID = l_parent_cat_id;
1066
1067 DUPLICATE_CATEGORY_ENTRIES (
1068 X_CHANGE_ORDER_TYPE_ID => X_CHANGE_ORDER_TYPE_ID,
1069 X_CREATED_BY => X_CREATED_BY,
1070 X_CREATION_DATE => X_CREATION_DATE,
1071 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
1072 X_CHANGE_MGMT_TYPE_CODE => X_CHANGE_MGMT_TYPE_CODE,
1073 X_BASE_CHANGE_MGMT_TYPE_CODE => l_base_change_mgmt_type_code);
1074 END IF;
1075 ELSIF ((X_TYPE_CLASSIFICATION = 'HEADER') AND ( NVL(X_SEEDED_FLAG,'N') <> 'Y'))
1076 THEN
1077 IF (l_parent_cat_id is not null)
1078 THEN
1079
1080 --Making calls to duplicate
1081 --1. Lifecycles
1082 --2. Priorities
1083 --3. Reasons
1084 --4. Classification Codes
1085 --5. Configurations (Sections and Attributes)
1086
1087 DUPLICATE_LIFECYCLES (
1088 X_CHANGE_ORDER_TYPE_ID => X_CHANGE_ORDER_TYPE_ID,
1089 X_PARENT_CHANGE_TYPE_ID => l_parent_cat_id,
1090 X_CREATED_BY => X_CREATED_BY,
1091 X_CREATION_DATE => X_CREATION_DATE,
1092 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN);
1093
1094 DUPLICATE_PRIORITIES(
1095 X_CHANGE_ORDER_TYPE_ID => X_CHANGE_ORDER_TYPE_ID,
1096 X_PARENT_CHANGE_TYPE_ID => l_parent_cat_id,
1097 X_CREATED_BY => X_CREATED_BY,
1098 X_CREATION_DATE => X_CREATION_DATE,
1099 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN);
1100
1101 DUPLICATE_REASONS(
1102 X_CHANGE_ORDER_TYPE_ID => X_CHANGE_ORDER_TYPE_ID,
1103 X_PARENT_CHANGE_TYPE_ID => l_parent_cat_id,
1104 X_CREATED_BY => X_CREATED_BY,
1105 X_CREATION_DATE => X_CREATION_DATE,
1106 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN);
1107
1108 DUPLICATE_CLASSCODES(
1109 X_CHANGE_ORDER_TYPE_ID => X_CHANGE_ORDER_TYPE_ID,
1110 X_PARENT_CHANGE_TYPE_ID => l_parent_cat_id,
1111 X_CREATED_BY => X_CREATED_BY,
1112 X_CREATION_DATE => X_CREATION_DATE,
1113 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN);
1114
1115 DUPLICATE_ATTR_SECT (
1116 X_CHANGE_ORDER_TYPE_ID => X_CHANGE_ORDER_TYPE_ID,
1117 X_CREATED_BY => X_CREATED_BY,
1118 X_CREATION_DATE => X_CREATION_DATE,
1119 X_TYPE_ID => l_parent_cat_id,
1120 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
1121 X_CHANGE_MGMT_TYPE_CODE => X_CHANGE_MGMT_TYPE_CODE,
1122 X_BASE_CHANGE_MGMT_TYPE_CODE => X_BASE_CHANGE_MGMT_TYPE_CODE);
1123
1124 ELSE
1125 SELECT BASE_CHANGE_MGMT_TYPE_CODE
1126 INTO l_base_change_mgmt_type_code
1127 FROM ENG_CHANGE_ORDER_TYPES_VL
1128 WHERE CHANGE_ORDER_TYPE_ID = X_CHANGE_ORDER_TYPE_ID;
1129
1130 ADD_DEFAULT_LIFECYCLES (
1131 X_CHANGE_ORDER_TYPE_ID => X_CHANGE_ORDER_TYPE_ID,
1132 X_BASE_CHANGE_MGMT_TYPE => l_base_change_mgmt_type_code,
1133 X_CREATED_BY => X_CREATED_BY,
1134 X_CREATION_DATE => X_CREATION_DATE,
1135 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN);
1136
1137 --Commenting out the lines to default the defaulting of
1138 --attributes for a type.
1139
1140 --Bug No: 3439555
1141 --Issue: DEF-1473
1142 --Defaulting the following attribute configuration for all types:
1143 --Change Number
1144 --ADD_DEFAULT_CONFIGURATIONS (
1145 -- X_CHANGE_ORDER_TYPE_ID => X_CHANGE_ORDER_TYPE_ID,
1146 -- X_CHANGE_MGMT_TYPE_CODE => X_CHANGE_MGMT_TYPE_CODE,
1147 -- X_CREATED_BY => X_CREATED_BY,
1148 -- X_CREATION_DATE => X_CREATION_DATE,
1149 -- X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN);
1150
1151 END IF;
1152 END IF;
1153 end INSERT_ROW;
1154
1155 procedure LOCK_ROW (
1156 X_CHANGE_ORDER_TYPE_ID in NUMBER,
1157 X_CHANGE_MGMT_TYPE_CODE in VARCHAR2,
1158 X_START_DATE in DATE,
1159 X_DEFAULT_ASSIGNEE_ID in NUMBER,
1160 X_SUBJECT_ID in NUMBER,
1161 X_AUTO_NUMBERING_METHOD in VARCHAR2,
1162 X_DEFAULT_ASSIGNEE_TYPE in VARCHAR2,
1163 X_TYPE_CLASSIFICATION in VARCHAR2,
1164 X_CLASS_CODE_DERIVED_FLAG in VARCHAR2,
1165 X_SEQUENCE_NUMBER in NUMBER,
1166 X_BASE_CHANGE_MGMT_TYPE_CODE in VARCHAR2,
1167 X_SEEDED_FLAG in VARCHAR2,
1168 X_ATTRIBUTE8 in VARCHAR2,
1169 X_ATTRIBUTE9 in VARCHAR2,
1170 X_ATTRIBUTE10 in VARCHAR2,
1171 X_ATTRIBUTE11 in VARCHAR2,
1172 X_ATTRIBUTE12 in VARCHAR2,
1173 X_ATTRIBUTE13 in VARCHAR2,
1174 X_ATTRIBUTE14 in VARCHAR2,
1175 X_ATTRIBUTE15 in VARCHAR2,
1176 X_CHANGE_ORDER_ORGANIZATION_ID in NUMBER,
1177 X_ASSEMBLY_TYPE in NUMBER,
1178 X_DISABLE_DATE in DATE,
1179 X_ATTRIBUTE_CATEGORY in VARCHAR2,
1180 X_ATTRIBUTE1 in VARCHAR2,
1181 X_ATTRIBUTE2 in VARCHAR2,
1182 X_ATTRIBUTE3 in VARCHAR2,
1183 X_ATTRIBUTE4 in VARCHAR2,
1184 X_ATTRIBUTE5 in VARCHAR2,
1185 X_ATTRIBUTE6 in VARCHAR2,
1186 X_ATTRIBUTE7 in VARCHAR2,
1187 X_TYPE_NAME in VARCHAR2,
1188 X_DESCRIPTION in VARCHAR2,
1189 X_TAB_TEXT in VARCHAR2,
1190 X_ENABLE_ITEM_IN_LOCAL_ORG in VARCHAR2 DEFAULT NULL,
1191 X_CREATE_BOM_IN_LOCAL_ORG in VARCHAR2 DEFAULT NULL,
1192 X_SUBJECT_UPDATABLE_FLAG in VARCHAR2 DEFAULT NULL,
1193 x_xml_data_source_code IN varchar2 default NULL,
1194 X_TYPE_INTERNAL_NAME in VARCHAR2 DEFAULT NULL
1195 ) is
1196 cursor c is select
1197 CHANGE_MGMT_TYPE_CODE,
1198 START_DATE,
1199 DEFAULT_ASSIGNEE_ID,
1200 SUBJECT_ID,
1201 AUTO_NUMBERING_METHOD,
1202 DEFAULT_ASSIGNEE_TYPE,
1203 TYPE_CLASSIFICATION,
1204 CLASS_CODE_DERIVED_FLAG,
1205 SEQUENCE_NUMBER,
1206 BASE_CHANGE_MGMT_TYPE_CODE,
1207 SEEDED_FLAG,
1208 ATTRIBUTE8,
1209 ATTRIBUTE9,
1210 ATTRIBUTE10,
1211 ATTRIBUTE11,
1212 ATTRIBUTE12,
1213 ATTRIBUTE13,
1214 ATTRIBUTE14,
1215 ATTRIBUTE15,
1216 CHANGE_ORDER_ORGANIZATION_ID,
1217 ASSEMBLY_TYPE,
1218 DISABLE_DATE,
1219 ATTRIBUTE_CATEGORY,
1220 ATTRIBUTE1,
1221 ATTRIBUTE2,
1222 ATTRIBUTE3,
1223 ATTRIBUTE4,
1224 ATTRIBUTE5,
1225 ATTRIBUTE6,
1226 ATTRIBUTE7,
1227 ENABLE_ITEM_IN_LOCAL_ORG ,
1228 CREATE_BOM_IN_LOCAL_ORG ,
1229 SUBJECT_UPDATABLE_FLAG,
1230 xml_data_source_code,
1231 X_TYPE_INTERNAL_NAME
1232 from ENG_CHANGE_ORDER_TYPES
1233 where CHANGE_ORDER_TYPE_ID = X_CHANGE_ORDER_TYPE_ID
1234 for update of CHANGE_ORDER_TYPE_ID nowait;
1235 recinfo c%rowtype;
1236
1237 cursor c1 is select
1238 TYPE_NAME,
1239 DESCRIPTION,
1240 TAB_TEXT,
1241 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
1242 from ENG_CHANGE_ORDER_TYPES_TL
1243 where CHANGE_ORDER_TYPE_ID = X_CHANGE_ORDER_TYPE_ID
1244 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
1245 for update of CHANGE_ORDER_TYPE_ID nowait;
1246 begin
1247 open c;
1248 fetch c into recinfo;
1249 if (c%notfound) then
1250 close c;
1251 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1252 app_exception.raise_exception;
1253 end if;
1254 close c;
1255
1256 if ( ((recinfo.CHANGE_MGMT_TYPE_CODE = X_CHANGE_MGMT_TYPE_CODE)
1257 OR ((recinfo.CHANGE_MGMT_TYPE_CODE is null) AND (X_CHANGE_MGMT_TYPE_CODE is null)))
1258 AND ((recinfo.START_DATE = X_START_DATE)
1259 OR ((recinfo.START_DATE is null) AND (X_START_DATE is null)))
1260 AND ((recinfo.DEFAULT_ASSIGNEE_ID = X_DEFAULT_ASSIGNEE_ID)
1261 OR ((recinfo.DEFAULT_ASSIGNEE_ID is null) AND (X_DEFAULT_ASSIGNEE_ID is null)))
1262 AND ((recinfo.SUBJECT_ID = X_SUBJECT_ID)
1263 OR ((recinfo.SUBJECT_ID is null) AND (X_SUBJECT_ID is null)))
1264 AND ((recinfo.AUTO_NUMBERING_METHOD = X_AUTO_NUMBERING_METHOD)
1265 OR ((recinfo.AUTO_NUMBERING_METHOD is null) AND (X_AUTO_NUMBERING_METHOD is null)))
1266 AND ((recinfo.DEFAULT_ASSIGNEE_TYPE = X_DEFAULT_ASSIGNEE_TYPE)
1267 OR ((recinfo.DEFAULT_ASSIGNEE_TYPE is null) AND (X_DEFAULT_ASSIGNEE_TYPE is null)))
1268 AND ((recinfo.TYPE_CLASSIFICATION = X_TYPE_CLASSIFICATION)
1269 OR ((recinfo.TYPE_CLASSIFICATION is null) AND (X_TYPE_CLASSIFICATION is null)))
1270 AND ((recinfo.CLASS_CODE_DERIVED_FLAG = X_CLASS_CODE_DERIVED_FLAG)
1271 OR ((recinfo.CLASS_CODE_DERIVED_FLAG is null) AND (X_CLASS_CODE_DERIVED_FLAG is null)))
1272 AND ((recinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
1273 OR ((recinfo.SEQUENCE_NUMBER is null) AND (X_SEQUENCE_NUMBER is null)))
1274 AND ((recinfo.BASE_CHANGE_MGMT_TYPE_CODE = X_BASE_CHANGE_MGMT_TYPE_CODE)
1275 OR ((recinfo.BASE_CHANGE_MGMT_TYPE_CODE is null) AND (X_BASE_CHANGE_MGMT_TYPE_CODE is null)))
1276 AND ((recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
1277 OR ((recinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
1278 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
1279 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
1280 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
1281 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
1282 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
1283 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
1284 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
1285 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
1286 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
1287 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
1288 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
1289 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
1290 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
1291 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
1292 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
1293 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
1294 AND ((recinfo.CHANGE_ORDER_ORGANIZATION_ID = X_CHANGE_ORDER_ORGANIZATION_ID)
1295 OR ((recinfo.CHANGE_ORDER_ORGANIZATION_ID is null) AND (X_CHANGE_ORDER_ORGANIZATION_ID is null)))
1296 AND ((recinfo.ASSEMBLY_TYPE = X_ASSEMBLY_TYPE)
1297 OR ((recinfo.ASSEMBLY_TYPE is null) AND (X_ASSEMBLY_TYPE is null)))
1298 AND ((recinfo.DISABLE_DATE = X_DISABLE_DATE)
1299 OR ((recinfo.DISABLE_DATE is null) AND (X_DISABLE_DATE is null)))
1300 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
1301 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
1302 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
1303 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
1304 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
1305 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
1306 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
1307 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
1308 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
1309 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
1310 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
1311 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
1312 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
1313 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
1314 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
1315 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
1316 AND ((recinfo.ENABLE_ITEM_IN_LOCAL_ORG = X_ENABLE_ITEM_IN_LOCAL_ORG)
1317 OR ((recinfo.ENABLE_ITEM_IN_LOCAL_ORG is null) AND (X_ENABLE_ITEM_IN_LOCAL_ORG is null)))
1318 AND ((recinfo.CREATE_BOM_IN_LOCAL_ORG = X_CREATE_BOM_IN_LOCAL_ORG)
1319 OR ((recinfo.CREATE_BOM_IN_LOCAL_ORG is null) AND (X_CREATE_BOM_IN_LOCAL_ORG is null)))
1320 AND ((recinfo.SUBJECT_UPDATABLE_FLAG = X_SUBJECT_UPDATABLE_FLAG)
1321 OR ((recinfo.SUBJECT_UPDATABLE_FLAG is null) AND (X_SUBJECT_UPDATABLE_FLAG is null)))
1322 ) then
1323 null;
1324 else
1325 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1326 app_exception.raise_exception;
1327 end if;
1328
1329 for tlinfo in c1 loop
1330 if (tlinfo.BASELANG = 'Y') then
1331 if ( (tlinfo.TYPE_NAME = X_TYPE_NAME)
1332 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
1333 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
1334 AND ((tlinfo.TAB_TEXT = X_TAB_TEXT)
1335 OR ((tlinfo.TAB_TEXT is null) AND (X_TAB_TEXT is null)))
1336 ) then
1337 null;
1338 else
1339 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1340 app_exception.raise_exception;
1341 end if;
1342 end if;
1343 end loop;
1344 return;
1345 end LOCK_ROW;
1346
1347 procedure UPDATE_ROW (
1348 X_CHANGE_ORDER_TYPE_ID in NUMBER,
1349 X_CHANGE_MGMT_TYPE_CODE in VARCHAR2,
1350 X_START_DATE in DATE,
1351 X_DEFAULT_ASSIGNEE_ID in NUMBER,
1352 X_SUBJECT_ID in NUMBER,
1353 X_AUTO_NUMBERING_METHOD in VARCHAR2,
1354 X_DEFAULT_ASSIGNEE_TYPE in VARCHAR2,
1355 X_TYPE_CLASSIFICATION in VARCHAR2,
1356 X_CLASS_CODE_DERIVED_FLAG in VARCHAR2,
1357 X_SEQUENCE_NUMBER in NUMBER,
1358 X_BASE_CHANGE_MGMT_TYPE_CODE in VARCHAR2,
1359 X_SEEDED_FLAG in VARCHAR2,
1360 X_ATTRIBUTE8 in VARCHAR2,
1361 X_ATTRIBUTE9 in VARCHAR2,
1362 X_ATTRIBUTE10 in VARCHAR2,
1363 X_ATTRIBUTE11 in VARCHAR2,
1364 X_ATTRIBUTE12 in VARCHAR2,
1365 X_ATTRIBUTE13 in VARCHAR2,
1366 X_ATTRIBUTE14 in VARCHAR2,
1367 X_ATTRIBUTE15 in VARCHAR2,
1368 X_CHANGE_ORDER_ORGANIZATION_ID in NUMBER,
1369 X_ASSEMBLY_TYPE in NUMBER,
1370 X_DISABLE_DATE in DATE,
1371 X_ATTRIBUTE_CATEGORY in VARCHAR2,
1372 X_ATTRIBUTE1 in VARCHAR2,
1373 X_ATTRIBUTE2 in VARCHAR2,
1374 X_ATTRIBUTE3 in VARCHAR2,
1375 X_ATTRIBUTE4 in VARCHAR2,
1376 X_ATTRIBUTE5 in VARCHAR2,
1377 X_ATTRIBUTE6 in VARCHAR2,
1378 X_ATTRIBUTE7 in VARCHAR2,
1379 X_TYPE_NAME in VARCHAR2,
1380 X_DESCRIPTION in VARCHAR2,
1381 X_TAB_TEXT in VARCHAR2,
1382 X_LAST_UPDATE_DATE in DATE,
1383 X_LAST_UPDATED_BY in NUMBER,
1384 X_LAST_UPDATE_LOGIN in NUMBER,
1385 X_ENABLE_ITEM_IN_LOCAL_ORG in VARCHAR2 DEFAULT NULL,
1386 X_CREATE_BOM_IN_LOCAL_ORG in VARCHAR2 DEFAULT NULL,
1387 X_SUBJECT_UPDATABLE_FLAG in VARCHAR2 DEFAULT NULL,
1388 x_xml_data_source_code IN VARCHAR2 default NULL,
1389 X_TYPE_INTERNAL_NAME in VARCHAR2 DEFAULT NULL
1390 ) is
1391
1392 CURSOR c_get_change_order_type IS
1393 SELECT change_order_type
1394 FROM ENG_CHANGE_ORDER_TYPES
1395 WHERE change_order_type_id = X_CHANGE_ORDER_TYPE_ID;
1396
1397 l_internal_type_name VARCHAR2(80);
1398 begin
1399
1400 --Bug No: 4560949
1401 --When updating a change type, the change_order_Type should get updated
1402 --only when X_TYPE_INTERNAL_NAME is populated.
1403 l_internal_type_name := null;
1404 IF X_TYPE_INTERNAL_NAME IS NULL
1405 THEN
1406 FOR l_type_info IN c_get_change_order_type
1407 LOOP
1408 l_internal_type_name := l_type_info.change_order_type;
1409 END LOOP;
1410 ELSE
1411 l_internal_type_name := X_TYPE_INTERNAL_NAME;
1412 END IF;
1413
1414 update ENG_CHANGE_ORDER_TYPES set
1415 CHANGE_MGMT_TYPE_CODE = X_CHANGE_MGMT_TYPE_CODE,
1416 START_DATE = X_START_DATE,
1417 DEFAULT_ASSIGNEE_ID = X_DEFAULT_ASSIGNEE_ID,
1418 SUBJECT_ID = X_SUBJECT_ID,
1419 AUTO_NUMBERING_METHOD = X_AUTO_NUMBERING_METHOD,
1420 DEFAULT_ASSIGNEE_TYPE = X_DEFAULT_ASSIGNEE_TYPE,
1421 TYPE_CLASSIFICATION = X_TYPE_CLASSIFICATION,
1422 CLASS_CODE_DERIVED_FLAG = X_CLASS_CODE_DERIVED_FLAG,
1423 SEQUENCE_NUMBER = X_SEQUENCE_NUMBER,
1424 BASE_CHANGE_MGMT_TYPE_CODE = X_BASE_CHANGE_MGMT_TYPE_CODE,
1425 SEEDED_FLAG = X_SEEDED_FLAG,
1426 ATTRIBUTE8 = X_ATTRIBUTE8,
1427 ATTRIBUTE9 = X_ATTRIBUTE9,
1428 ATTRIBUTE10 = X_ATTRIBUTE10,
1429 ATTRIBUTE11 = X_ATTRIBUTE11,
1430 ATTRIBUTE12 = X_ATTRIBUTE12,
1431 ATTRIBUTE13 = X_ATTRIBUTE13,
1432 ATTRIBUTE14 = X_ATTRIBUTE14,
1433 ATTRIBUTE15 = X_ATTRIBUTE15,
1434 CHANGE_ORDER_ORGANIZATION_ID = X_CHANGE_ORDER_ORGANIZATION_ID,
1435 ASSEMBLY_TYPE = X_ASSEMBLY_TYPE,
1436 DISABLE_DATE = X_DISABLE_DATE,
1437 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
1438 ATTRIBUTE1 = X_ATTRIBUTE1,
1439 ATTRIBUTE2 = X_ATTRIBUTE2,
1440 ATTRIBUTE3 = X_ATTRIBUTE3,
1441 ATTRIBUTE4 = X_ATTRIBUTE4,
1442 ATTRIBUTE5 = X_ATTRIBUTE5,
1443 ATTRIBUTE6 = X_ATTRIBUTE6,
1444 ATTRIBUTE7 = X_ATTRIBUTE7,
1445 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1446 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1447 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1448 ENABLE_ITEM_IN_LOCAL_ORG = X_ENABLE_ITEM_IN_LOCAL_ORG,
1449 CREATE_BOM_IN_LOCAL_ORG = X_CREATE_BOM_IN_LOCAL_ORG,
1450 SUBJECT_UPDATABLE_FLAG = X_SUBJECT_UPDATABLE_FLAG,
1451 xml_data_source_code = x_xml_data_source_code,
1452 CHANGE_ORDER_TYPE = l_internal_type_name
1453 where CHANGE_ORDER_TYPE_ID = X_CHANGE_ORDER_TYPE_ID;
1454
1455 if (sql%notfound) then
1456 raise no_data_found;
1457 end if;
1458
1459 update ENG_CHANGE_ORDER_TYPES_TL set
1460 TYPE_NAME = X_TYPE_NAME,
1461 DESCRIPTION = X_DESCRIPTION,
1462 TAB_TEXT = X_TAB_TEXT,
1463 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1464 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1465 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1466 SOURCE_LANG = userenv('LANG')
1467 where CHANGE_ORDER_TYPE_ID = X_CHANGE_ORDER_TYPE_ID
1468 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1469
1470 if (sql%notfound) then
1471 raise no_data_found;
1472 end if;
1473 end UPDATE_ROW;
1474
1475 procedure DELETE_ROW (
1476 X_CHANGE_ORDER_TYPE_ID in NUMBER
1477 ) is
1478 begin
1479 delete from ENG_CHANGE_ORDER_TYPES_TL
1480 where CHANGE_ORDER_TYPE_ID = X_CHANGE_ORDER_TYPE_ID;
1481
1482 if (sql%notfound) then
1483 raise no_data_found;
1484 end if;
1485
1486 delete from ENG_CHANGE_ORDER_TYPES
1487 where CHANGE_ORDER_TYPE_ID = X_CHANGE_ORDER_TYPE_ID;
1488
1489 if (sql%notfound) then
1490 raise no_data_found;
1491 end if;
1492 end DELETE_ROW;
1493
1494 procedure ADD_LANGUAGE
1495 is
1496 begin
1497 delete from ENG_CHANGE_ORDER_TYPES_TL T
1498 where not exists
1499 (select NULL
1500 from ENG_CHANGE_ORDER_TYPES B
1501 where B.CHANGE_ORDER_TYPE_ID = T.CHANGE_ORDER_TYPE_ID
1502 );
1503
1504 update ENG_CHANGE_ORDER_TYPES_TL T set (
1505 TYPE_NAME,
1506 DESCRIPTION,
1507 TAB_TEXT
1508 ) = (select
1509 B.TYPE_NAME,
1510 B.DESCRIPTION,
1511 B.TAB_TEXT
1512 from ENG_CHANGE_ORDER_TYPES_TL B
1513 where B.CHANGE_ORDER_TYPE_ID = T.CHANGE_ORDER_TYPE_ID
1514 and B.LANGUAGE = T.SOURCE_LANG)
1515 where (
1516 T.CHANGE_ORDER_TYPE_ID,
1517 T.LANGUAGE
1518 ) in (select
1519 SUBT.CHANGE_ORDER_TYPE_ID,
1520 SUBT.LANGUAGE
1521 from ENG_CHANGE_ORDER_TYPES_TL SUBB, ENG_CHANGE_ORDER_TYPES_TL SUBT
1522 where SUBB.CHANGE_ORDER_TYPE_ID = SUBT.CHANGE_ORDER_TYPE_ID
1523 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1524 and (SUBB.TYPE_NAME <> SUBT.TYPE_NAME
1525 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
1526 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
1527 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
1528 or SUBB.TAB_TEXT <> SUBT.TAB_TEXT
1529 or (SUBB.TAB_TEXT is null and SUBT.TAB_TEXT is not null)
1530 or (SUBB.TAB_TEXT is not null and SUBT.TAB_TEXT is null)
1531 ));
1532
1533 insert into ENG_CHANGE_ORDER_TYPES_TL (
1534 CHANGE_ORDER_TYPE_ID,
1535 TYPE_NAME,
1536 DESCRIPTION,
1537 TAB_TEXT,
1538 CREATION_DATE,
1539 CREATED_BY,
1540 LAST_UPDATE_DATE,
1541 LAST_UPDATED_BY,
1542 LAST_UPDATE_LOGIN,
1543 LANGUAGE,
1544 SOURCE_LANG
1545 ) select /*+ ORDERED */
1546 B.CHANGE_ORDER_TYPE_ID,
1547 B.TYPE_NAME,
1548 B.DESCRIPTION,
1549 B.TAB_TEXT,
1550 B.CREATION_DATE,
1551 B.CREATED_BY,
1552 B.LAST_UPDATE_DATE,
1553 B.LAST_UPDATED_BY,
1554 B.LAST_UPDATE_LOGIN,
1555 L.LANGUAGE_CODE,
1556 B.SOURCE_LANG
1557 from ENG_CHANGE_ORDER_TYPES_TL B, FND_LANGUAGES L
1558 where L.INSTALLED_FLAG in ('I', 'B')
1559 and B.LANGUAGE = userenv('LANG')
1560 and not exists
1561 (select NULL
1562 from ENG_CHANGE_ORDER_TYPES_TL T
1563 where T.CHANGE_ORDER_TYPE_ID = B.CHANGE_ORDER_TYPE_ID
1564 and T.LANGUAGE = L.LANGUAGE_CODE);
1565 end ADD_LANGUAGE;
1566
1567 end ENG_CHANGE_ORDER_TYPES_PKG;