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