[Home] [Help]
75: l_change_order_type_id number;
76: stat_name varchar2(80);
77:
78: l_type_id NUMBER;
79: l_auto_number_method eng_change_order_types.AUTO_NUMBERING_METHOD%TYPE;
80: l_change_number eng_engineering_changes.CHANGE_NOTICE%TYPE := NULL;
81:
82: l_change_type_name eng_change_order_types_tl.type_name%TYPE := NULL;
83: l_hist_rec_row_id VARCHAR2(100);
78: l_type_id NUMBER;
79: l_auto_number_method eng_change_order_types.AUTO_NUMBERING_METHOD%TYPE;
80: l_change_number eng_engineering_changes.CHANGE_NOTICE%TYPE := NULL;
81:
82: l_change_type_name eng_change_order_types_tl.type_name%TYPE := NULL;
83: l_hist_rec_row_id VARCHAR2(100);
84: l_change_mgmt_type_id eng_change_order_types_tl.change_order_type_id%TYPE;
85:
86: CURSOR c_change_type_detail(l_change_order_type_id IN NUMBER)
80: l_change_number eng_engineering_changes.CHANGE_NOTICE%TYPE := NULL;
81:
82: l_change_type_name eng_change_order_types_tl.type_name%TYPE := NULL;
83: l_hist_rec_row_id VARCHAR2(100);
84: l_change_mgmt_type_id eng_change_order_types_tl.change_order_type_id%TYPE;
85:
86: CURSOR c_change_type_detail(l_change_order_type_id IN NUMBER)
87: IS
88: SELECT ecotv1.change_order_type_id change_mgmt_type_id,
88: SELECT ecotv1.change_order_type_id change_mgmt_type_id,
89: ecotv2.change_order_type_id,
90: ecotv2.auto_numbering_method,
91: ecotv1.auto_numbering_method change_mgmt_method
92: FROM eng_change_order_types_vl ecotv1, eng_change_order_types_vl ecotv2
93: WHERE ecotv1.change_order_type_id = l_change_order_type_id
94: AND ecotv1.TYPE_CLASSIFICATION= 'CATEGORY'
95: AND ecotv1.change_mgmt_type_code = ecotv2.change_mgmt_type_code
96: AND ecotv2.type_name = change_type_code
106: WHERE eng_change.change_notice = p_change_notice
107: AND eng_change.organization_id = org_param.organization_id
108: AND org_param.organization_code = p_org_code;
109:
110: CURSOR c_first_status (cp_change_mgmt_type_id IN eng_change_order_types_tl.change_order_type_id%TYPE)
111: IS
112: SELECT a.status_name INTO stat_name
113: FROM eng_change_statuses_vl a, eng_lifecycle_statuses b
114: WHERE a.status_code = b.status_code
129: -- Bug : 5140579 Removed the hardcoding and getting following values from database. Priority need not be hardcoded.
130: -- select status_name into stat_name FROM ENG_CHANGE_STATUSES_VL where status_code=1; -- 1 = Open
131:
132: -- Bug : 5282713 We need to get the first status of this change type.
133: --select change_order_type_id into l_change_mgmt_type_id from eng_change_order_types_tl where type_name = change_type_code and language = userenv('LANG');
134:
135:
136: --Bug 8242706(base bug7587290): If NIR type and Change Order Type has got same name, Creation of NIR is failing.
137: -- validaton on NIR type included in the select query.
137: -- validaton on NIR type included in the select query.
138:
139: SELECT ECOT.change_order_type_id
140: INTO l_change_mgmt_type_id
141: FROM eng_change_order_types_tl ECOT,
142: ENG_CHANGE_ORDER_TYPES ECO
143: WHERE ECOT.CHANGE_ORDER_TYPE_ID=ECO.CHANGE_ORDER_TYPE_ID
144: AND ECO.CHANGE_MGMT_TYPE_CODE= 'NEW_ITEM_REQUEST'
145: AND ECOT.type_name = change_type_code
138:
139: SELECT ECOT.change_order_type_id
140: INTO l_change_mgmt_type_id
141: FROM eng_change_order_types_tl ECOT,
142: ENG_CHANGE_ORDER_TYPES ECO
143: WHERE ECOT.CHANGE_ORDER_TYPE_ID=ECO.CHANGE_ORDER_TYPE_ID
144: AND ECO.CHANGE_MGMT_TYPE_CODE= 'NEW_ITEM_REQUEST'
145: AND ECOT.type_name = change_type_code
146: AND ECOT.language = userenv('LANG');
163:
164:
165: select change_order_type, change_order_type_id
166: into change_mgmt_type_name, l_change_order_type_id
167: FROM ENG_CHANGE_ORDER_TYPES_VL
168: WHERE change_mgmt_type_code='NEW_ITEM_REQUEST'
169: AND type_classification='CATEGORY';
170:
171: l_eco_rec.change_management_type := change_mgmt_type_name;
337: sysdate creation_date,
338: FND_GLOBAL.USER_ID last_updated_by,
339: sysdate last_update_date,
340: FND_GLOBAL.USER_ID last_update_login,
341: (select change_order_type_id from eng_change_order_types where TYPE_CLASSIFICATION='LINE'
342: AND CHANGE_MGMT_TYPE_CODE = 'NEW_ITEM_REQUEST' AND ROWNUM=1) change_type_id,
343: 1 status_code,
344: 1 APPROVAL_STATUS_TYPE
345: from mtl_system_items_interface msii
360: l_change_type_array,
361: l_org_array
362: FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
363: MTL_ITEM_CATALOG_GROUPS_VL micg,
364: ENG_CHANGE_ORDER_TYPES_VL eng_types,
365: MTL_PARAMETERS mp
366: WHERE msii.set_process_id=P_BATCH_ID
367: AND PROCESS_FLAG = l_process_flag
368: AND msii.ITEM_CATALOG_GROUP_ID = micg.ITEM_CATALOG_GROUP_ID
397: BULK COLLECT INTO l_icc_array ,
398: l_change_type_array,
399: l_org_array
400: FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
401: ENG_CHANGE_ORDER_TYPES_VL eng_types,
402: MTL_PARAMETERS mp,
403: ego_item_cat_denorm_hier heir,
404: mtl_item_catalog_groups_b micgb
405: WHERE msii.set_process_id = P_BATCH_ID
428: bulk collect INTO l_icc_array ,
429: l_change_type_array,
430: l_org_array
431: FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
432: -- ENG_CHANGE_ORDER_TYPES eng_types,
433: MTL_PARAMETERS mp
434: WHERE msii.set_process_id=P_BATCH_ID
435: AND PROCESS_FLAG = l_process_flag
436: AND mp.organization_id = msii.organization_id
458: l_prev_org_id := l_org_id;
459: end if;
460:
461: l_change_type_code_num := TO_NUMBER(SUBSTR(l_change_type_array(i), INSTR(l_change_type_array(i), '$$', 2)+2));
462: SELECT eng_types.type_name into l_change_type_code FROM ENG_CHANGE_ORDER_TYPES_VL eng_types WHERE eng_types.change_order_type_id = l_change_type_code_num;
463:
464: Create_New_Item_Request( x_return_status => l_return_status,
465: x_change_id => l_change_id,
466: change_number => l_icc_array(i),
547: AND PROCESS_FLAG = l_process_flag
548: -- AND TRANSACTION_TYPE =L_TRANSACTION_TYPE
549: ;
550: l_change_type_code_num := TO_NUMBER(SUBSTR(l_change_type_array(i), INSTR(l_change_type_array(i), '$$', 2)+2));
551: SELECT eng_types.type_name into l_change_type_code FROM ENG_CHANGE_ORDER_TYPES_VL eng_types WHERE eng_types.change_order_type_id = l_change_type_code_num;
552: for x in l_item_ids_array.FIRST .. l_item_ids_array.LAST
553: loop
554:
555:
632: FND_GLOBAL.USER_ID,
633: sysdate,
634: FND_GLOBAL.USER_ID,
635: 10,
636: (select change_order_type_id from eng_change_order_types where TYPE_CLASSIFICATION='LINE'
637: AND CHANGE_MGMT_TYPE_CODE = 'NEW_ITEM_REQUEST' AND ROWNUM=1) TYPE_ID,
638: 1 ,
639: 1
640: from mtl_system_items_interface msii