[Home] [Help]
Skip to content
PACKAGE BODY: APPS.EGO_IMPORT_BATCHES_PKG
Source
1 PACKAGE BODY EGO_IMPORT_BATCHES_PKG AS
2 /* $Header: EGOVBATB.pls 120.0.12010000.2 2009/04/02 01:58:16 geguo ship $ */
3
4 PROCEDURE INSERT_ROW (
5 X_ROWID IN OUT NOCOPY VARCHAR2,
6 X_BATCH_ID IN NUMBER,
7 X_ORGANIZATION_ID IN NUMBER,
8 X_SOURCE_SYSTEM_ID IN NUMBER,
9 X_BATCH_TYPE IN VARCHAR2,
10 X_ASSIGNEE IN NUMBER,
11 X_BATCH_STATUS IN VARCHAR2,
12 X_OBJECT_VERSION_NUMBER IN NUMBER,
13 X_NAME IN VARCHAR2,
14 X_DESCRIPTION IN VARCHAR2,
15 X_CREATION_DATE IN DATE,
16 X_CREATED_BY IN NUMBER,
17 X_LAST_UPDATE_DATE IN DATE,
18 X_LAST_UPDATED_BY IN NUMBER,
19 X_LAST_UPDATE_LOGIN IN NUMBER)
20 IS
21 CURSOR C IS
22 SELECT ROWID FROM EGO_IMPORT_BATCHES_B
23 WHERE BATCH_ID = X_BATCH_ID;
24 BEGIN
25 INSERT into EGO_IMPORT_BATCHES_B (
26 ORGANIZATION_ID,
27 BATCH_ID,
28 SOURCE_SYSTEM_ID,
29 BATCH_TYPE,
30 ASSIGNEE,
31 BATCH_STATUS,
32 OBJECT_VERSION_NUMBER,
33 CREATION_DATE,
34 CREATED_BY,
35 LAST_UPDATE_DATE,
36 LAST_UPDATED_BY,
37 LAST_UPDATE_LOGIN
38 ) values (
39 X_ORGANIZATION_ID,
40 X_BATCH_ID,
41 X_SOURCE_SYSTEM_ID,
42 X_BATCH_TYPE,
43 X_ASSIGNEE,
44 X_BATCH_STATUS,
45 X_OBJECT_VERSION_NUMBER,
46 X_CREATION_DATE,
47 X_CREATED_BY,
48 X_LAST_UPDATE_DATE,
49 X_LAST_UPDATED_BY,
50 X_LAST_UPDATE_LOGIN
51 );
52
53 INSERT into EGO_IMPORT_BATCHES_TL (
54 BATCH_ID,
55 NAME,
56 DESCRIPTION,
57 OBJECT_VERSION_NUMBER,
58 CREATED_BY,
59 CREATION_DATE,
60 LAST_UPDATED_BY,
61 LAST_UPDATE_DATE,
62 LAST_UPDATE_LOGIN,
63 LANGUAGE,
64 SOURCE_LANG
65 ) SELECT
66 X_BATCH_ID,
67 X_NAME,
68 X_DESCRIPTION,
69 X_OBJECT_VERSION_NUMBER,
70 X_CREATED_BY,
71 X_CREATION_DATE,
72 X_LAST_UPDATED_BY,
73 X_LAST_UPDATE_DATE,
74 X_LAST_UPDATE_LOGIN,
75 L.LANGUAGE_CODE,
76 USERENV('LANG')
77 FROM FND_LANGUAGES L
78 WHERE L.INSTALLED_FLAG IN ('I', 'B')
79 AND NOT EXISTS
80 (SELECT NULL
81 FROM EGO_IMPORT_BATCHES_TL T
82 WHERE T.BATCH_ID = X_BATCH_ID
83 AND T.LANGUAGE = L.LANGUAGE_CODE);
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 END INSERT_ROW;
93
94 PROCEDURE LOCK_ROW (
95 X_BATCH_ID IN NUMBER,
96 X_ORGANIZATION_ID IN NUMBER,
97 X_SOURCE_SYSTEM_ID IN NUMBER,
98 X_BATCH_TYPE IN VARCHAR2,
99 X_ASSIGNEE IN NUMBER,
100 X_BATCH_STATUS IN VARCHAR2,
101 X_OBJECT_VERSION_NUMBER IN NUMBER,
102 X_NAME IN VARCHAR2,
103 X_DESCRIPTION IN VARCHAR2
104 ) IS
105 CURSOR C IS
106 SELECT
107 ORGANIZATION_ID,
108 SOURCE_SYSTEM_ID,
109 BATCH_TYPE,
110 ASSIGNEE,
111 BATCH_STATUS,
112 OBJECT_VERSION_NUMBER
113 FROM EGO_IMPORT_BATCHES_B
114 WHERE BATCH_ID = X_BATCH_ID
115 FOR UPDATE OF BATCH_ID NOWAIT;
116
117 RECINFO C%ROWTYPE;
118
119 CURSOR C1 IS
120 SELECT
121 NAME,
122 DESCRIPTION,
123 DECODE(LANGUAGE, USERENV('LANG'), 'Y', 'N') BASELANG
124 FROM EGO_IMPORT_BATCHES_TL
125 WHERE BATCH_ID = X_BATCH_ID
126 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
127 FOR UPDATE OF BATCH_ID NOWAIT;
128 BEGIN
129 OPEN C;
130 FETCH C INTO recinfo;
131 IF (C%NOTFOUND) THEN
132 CLOSE C;
133 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
134 APP_EXCEPTION.RAISE_EXCEPTION;
135 END IF;
136 CLOSE C;
137 IF ( (recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
138 AND (recinfo.SOURCE_SYSTEM_ID = X_SOURCE_SYSTEM_ID)
139 AND (recinfo.BATCH_TYPE = X_BATCH_TYPE)
140 AND ((recinfo.ASSIGNEE = X_ASSIGNEE)
141 OR ((recinfo.ASSIGNEE IS NULL) AND (X_ASSIGNEE IS NULL)))
142 AND (recinfo.BATCH_STATUS = X_BATCH_STATUS)
143 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
144 ) THEN
145 NULL;
146 ELSE
147 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
148 APP_EXCEPTION.RAISE_EXCEPTION;
149 END IF;
150
151 FOR tlinfo IN c1 LOOP
152 IF (tlinfo.BASELANG = 'Y') THEN
153 IF ( (tlinfo.NAME = X_NAME)
154 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
155 OR ((tlinfo.DESCRIPTION IS NULL) AND (X_DESCRIPTION IS NULL)))
156 ) THEN
157 NULL;
158 ELSE
159 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
160 APP_EXCEPTION.RAISE_EXCEPTION;
161 END IF;
162 END IF;
163 END LOOP;
164 RETURN;
165 END LOCK_ROW;
166
167 PROCEDURE UPDATE_ROW (
168 X_BATCH_ID IN NUMBER,
169 X_ORGANIZATION_ID IN NUMBER,
170 X_SOURCE_SYSTEM_ID IN NUMBER,
171 X_BATCH_TYPE IN VARCHAR2,
172 X_ASSIGNEE IN NUMBER,
173 X_BATCH_STATUS IN VARCHAR2,
174 X_OBJECT_VERSION_NUMBER IN NUMBER,
175 X_NAME IN VARCHAR2,
176 X_DESCRIPTION IN VARCHAR2,
177 X_LAST_UPDATE_DATE IN DATE,
178 X_LAST_UPDATED_BY IN NUMBER,
179 X_LAST_UPDATE_LOGIN IN NUMBER
180 ) IS
181 BEGIN
182 UPDATE EGO_IMPORT_BATCHES_B SET
183 ORGANIZATION_ID = X_ORGANIZATION_ID,
184 SOURCE_SYSTEM_ID = X_SOURCE_SYSTEM_ID,
185 BATCH_TYPE = X_BATCH_TYPE,
186 ASSIGNEE = X_ASSIGNEE,
187 BATCH_STATUS = X_BATCH_STATUS,
188 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
189 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
190 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
191 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
192 WHERE BATCH_ID = X_BATCH_ID;
193
194 IF (SQL%NOTFOUND) THEN
195 RAISE NO_DATA_FOUND;
196 END IF;
197
198 UPDATE EGO_IMPORT_BATCHES_TL SET
199 NAME = X_NAME,
200 DESCRIPTION = X_DESCRIPTION,
201 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
202 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
203 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
204 SOURCE_LANG = USERENV('LANG')
205 WHERE BATCH_ID = X_BATCH_ID
206 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
207
208 IF (SQL%NOTFOUND) THEN
209 RAISE NO_DATA_FOUND;
210 END IF;
211 END UPDATE_ROW;
212
213 PROCEDURE DELETE_ROW (
214 X_BATCH_ID IN NUMBER
215 ) IS
216 BEGIN
217 DELETE FROM EGO_IMPORT_BATCHES_TL
218 WHERE BATCH_ID = X_BATCH_ID;
219
220 IF (SQL%NOTFOUND) THEN
221 RAISE NO_DATA_FOUND;
222 END IF;
223
224 DELETE FROM EGO_IMPORT_BATCHES_B
225 WHERE BATCH_ID = X_BATCH_ID;
226
227 IF (SQL%NOTFOUND) THEN
228 RAISE NO_DATA_FOUND;
229 END IF;
230 END DELETE_ROW;
231
232
233 PROCEDURE ADD_LANGUAGE
234 IS
235 BEGIN
236 DELETE FROM EGO_IMPORT_BATCHES_TL T
237 WHERE NOT EXISTS
238 (SELECT NULL
239 FROM EGO_IMPORT_BATCHES_B B
240 WHERE B.BATCH_ID = T.BATCH_ID
241 );
242
243 UPDATE EGO_IMPORT_BATCHES_TL T SET (
244 NAME,
245 DESCRIPTION
246 ) = (SELECT
247 B.NAME,
248 B.DESCRIPTION
249 FROM EGO_IMPORT_BATCHES_TL B
250 WHERE B.BATCH_ID = T.BATCH_ID
251 AND B.LANGUAGE = T.SOURCE_LANG)
252 WHERE (
253 T.BATCH_ID,
254 T.LANGUAGE
255 ) IN (SELECT
256 SUBT.BATCH_ID,
257 SUBT.LANGUAGE
258 FROM EGO_IMPORT_BATCHES_TL SUBB, EGO_IMPORT_BATCHES_TL SUBT
259 WHERE SUBB.BATCH_ID = SUBT.BATCH_ID
260 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
261 AND (SUBB.NAME <> SUBT.NAME
262 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
263 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
264 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
265 ));
266
267 INSERT INTO EGO_IMPORT_BATCHES_TL (
268 BATCH_ID,
269 NAME,
270 DESCRIPTION,
271 OBJECT_VERSION_NUMBER,
272 CREATED_BY,
273 CREATION_DATE,
274 LAST_UPDATED_BY,
275 LAST_UPDATE_DATE,
276 LAST_UPDATE_LOGIN,
277 LANGUAGE,
278 SOURCE_LANG
279 ) SELECT /*+ ORDERED */
280 B.BATCH_ID,
281 B.NAME,
282 B.DESCRIPTION,
283 B.OBJECT_VERSION_NUMBER,
284 B.CREATED_BY,
285 B.CREATION_DATE,
286 B.LAST_UPDATED_BY,
287 B.LAST_UPDATE_DATE,
288 B.LAST_UPDATE_LOGIN,
289 L.LANGUAGE_CODE,
290 B.SOURCE_LANG
291 FROM EGO_IMPORT_BATCHES_TL B, FND_LANGUAGES L
292 WHERE L.INSTALLED_FLAG IN ('I', 'B')
293 AND B.LANGUAGE = USERENV('LANG')
294 AND NOT EXISTS
295 (SELECT NULL
296 FROM EGO_IMPORT_BATCHES_TL T
297 WHERE T.BATCH_ID = B.BATCH_ID
298 AND T.LANGUAGE = L.LANGUAGE_CODE);
299 END ADD_LANGUAGE;
300
301 /*Use this pulbic API to create item/structure import batch.
302 * This API can create both PIMDH and none-PIMDH type import batches.
303 * It will take 'PIMDH' as default if user not pass-in argument source system code.
304 * If hasn't input any particular parameters, this pl/sql API will load
305 * source system default setting.
306 * Current version of API does not support creation of a new change order.
307 *
308 * @param p_source_system_code A code indicating the Source System.
309 * @param p_organization_id
310 * @param p_batch_type_display_name A code indicating the type for this import batch.
311 * Canditate values: 'Item','Structure', not case sensitive.
312 * @param p_assignee_name Indicate the user to whom this batch will been assigned. Default value: fnd_global.user_id.
313 * @param p_batch_name
314 * @param p_apply_def_match_rule_all A flag indicating if apply the default match rule to all the items/structures within this batch.
315 * @param p_match_on_data_load A flag indicating if automatically match once data loaded.
316 * @param p_confirm_single_match A flag indicating if automatically confirm single matches.
317 * @param p_import_on_data_load A flag indicating if automatically import on data load.
318 * @param p_import_xref_only A flag indicating if import all data or only cross reference.
319 * Only N or null make sense the CO and NIR input.
320 * @param p_revision_import_policy A flag indicating the revision import policy.
321 * Candidate values: 'L' for update latest, or 'N' for create new.
322 * @param p_structure_name A code indicating the internal structure type name.
323 * @param p_structure_effectivity_type A Number indicating the effectivity control type of the structure
324 * candidate values: 1 indicate using serial number or unit effectivity
325 * 2 indicate using effectivity date.
326 * @param p_effectivity_date A Code indicating the structure effectivity date.
327 * @param p_from_end_item_unit_number A Code indicating info for serial number or unit effectivity.
328 * @param p_structure_content A Code indicating if the structure contains changed components only
329 *
330 * @param p_change_order_creation A Code indicating if create new change order or add to existing change order.
331 * candidate values: 'O' for None, 'N' for New and 'E' for adding to existing one.
332 * Current version API ONLY support O & E
333 * @param p_add_all_to_change_flag A flag indicating if add all imported items to change order.
334 * @param p_change_mgmt_type_code A Code indicating the Change Management Type Code. eg. 'CHANGE_ORDER'.
335 * @param p_change_type_id A Number indicating the change order type id.
336 * @param p_change_notice A Code indicating the change order notice.
337 * @param p_change_name Default to null in this version Since new ECO not supported.
338 * @param p_change_description Default to null in this version Since new ECO not supported.
339 * @param p_def_match_rule_cust_app_id A Number indicating the application id for default match rule applied.
340 * @param p_def_match_rule_cust_code A Number indicating the custom id for default match rule applied.
341 * @param p_nir_option A Code indicating the NIR creation opting
342 * candidate values: 'N' for none, 'C' for one per ICC, 'I' for each item
343 * @param p_enabled_for_data_pool A Code indicating whether batch/source system is enabled for Data Pool or not
344 * If a batch is enabled for data pool, then only it can be used for inbound messages
345 *
346 * API USAGE: CREATE_IMPORT_BATCH(
347 * p_source_system_code => 'PIMDH',
348 * p_organization_id => 204,
349 * p_batch_type_display_name => 'item',
350 * p_assignee_name => 'PLMMGR',
351 * p_batch_name => 'Test Import Batch',
352 * p_match_on_data_load => 'Y',
353 * p_apply_def_match_rule_all => 'Y',
354 * p_confirm_single_match => 'Y',
355 * p_import_on_data_load => 'Y',
356 * p_import_xref_only => 'N',
357 * p_revision_import_policy => 'N',
358 * p_structure_name => 'MBOM',
359 * p_structure_effectivity_type => 2,
360 * p_effectivity_date => TO_DATE('2009-03-04','yyyy-mm-dd'),
361 * p_from_end_item_unit_number => '',
362 * p_structure_content => '',
363 * p_change_order_creation => 'E',
364 * p_add_all_to_change_flag => 'y',
365 * p_change_mgmt_type_code => 'CHANGE_ORDER',
366 * p_change_type_id => 938 --'EBS CO',
367 * p_change_notice => 'ECO111',
368 * p_def_match_rule_cust_app_id => 431,
369 * p_def_match_rule_cust_code => '1156416628834EGO_ITEM_MATCH_RU',
370 * p_nir_option => 'N',
371 * p_enabled_for_data_pool => 'N',
372 * x_batch_id => l_batch_id,
373 * x_return_status => l_return_status,
374 * x_error_msg => l_error_msg);
375 */
376 Procedure CREATE_IMPORT_BATCH(p_source_system_code IN VARCHAR2 DEFAULT 'PIMDH',
377 p_organization_id IN NUMBER,
378 p_batch_type_display_name IN VARCHAR2 DEFAULT NULL,
379 p_assignee_name IN VARCHAR2 DEFAULT NULL,
380 p_batch_name IN VARCHAR2,
381 --Options
382 p_match_on_data_load IN VARCHAR2 DEFAULT NULL,
383 p_apply_def_match_rule_all IN VARCHAR2 DEFAULT NULL,
384 p_confirm_single_match IN VARCHAR2 DEFAULT NULL,
385 p_import_on_data_load IN VARCHAR2 DEFAULT NULL,
386 p_import_xref_only IN VARCHAR2 DEFAULT NULL,
387 p_revision_import_policy IN VARCHAR2 DEFAULT NULL,
388 --p_structure_type_id
389 p_structure_name IN VARCHAR2 DEFAULT NULL,
390 p_structure_effectivity_type IN NUMBER DEFAULT NULL,
391 p_effectivity_date IN DATE DEFAULT SYSDATE,
392 p_from_end_item_unit_number IN VARCHAR2 DEFAULT NULL,
393 p_structure_content IN VARCHAR2 DEFAULT NULL,
394 --Change Order Options
395 p_change_order_creation IN VARCHAR2 DEFAULT NULL,
396 p_add_all_to_change_flag IN VARCHAR2 DEFAULT NULL,
397 p_change_mgmt_type_code IN VARCHAR2 DEFAULT NULL,
398 p_change_type_id IN NUMBER DEFAULT NULL,
399 p_change_notice IN VARCHAR2 DEFAULT NULL,
400 p_change_name IN VARCHAR2 DEFAULT NULL,
401 p_change_description IN VARCHAR2 DEFAULT NULL,
402 --p_object_version_number IN NUMBER,
403 p_def_match_rule_cust_app_id IN NUMBER DEFAULT 431,
404 p_def_match_rule_cust_code IN VARCHAR2 DEFAULT NULL,
405 --p_def_match_rule_rn_app_id IN NUMBER,
406 --p_def_match_rule_rn_code IN VARCHAR2,
407 p_nir_option IN VARCHAR2 DEFAULT NULL,
408 p_enabled_for_data_pool IN VARCHAR2 DEFAULT NULL,
409 x_batch_id OUT NOCOPY NUMBER,
410 x_return_status OUT NOCOPY VARCHAR2,
411 x_error_msg OUT NOCOPY VARCHAR2) IS
412
413 Cursor Ego_Master_Org_Csr(v_organization_id IN NUMBER) IS
414 SELECT MP.ORGANIZATION_ID, MP.MASTER_ORGANIZATION_ID
415 FROM MTL_PARAMETERS MP
416 WHERE MP.ORGANIZATION_ID = v_organization_id;
417
418 Cursor Ego_Source_System_Csr(v_source_system_code IN VARCHAR2) IS
419 SELECT SSM.ORIG_SYSTEM_ID
420 FROM HZ_ORIG_SYSTEMS_VL SSM, EGO_SOURCE_SYSTEM_EXT SSM_EXT
421 WHERE SSM_EXT.SOURCE_SYSTEM_ID = SSM.ORIG_SYSTEM_ID
422 AND SSM.ORIG_SYSTEM = v_source_system_code;
423
424 Cursor Ego_Ssm_Options_Csr(v_source_system_id IN NUMBER) IS
425 select *
426 from EGO_IMPORT_OPTION_SETS
427 WHERE SOURCE_SYSTEM_ID = v_source_system_id
428 AND BATCH_ID IS NULL;
429
430 Cursor Ego_Ssm_Assignee_Csr(v_user_name in VARCHAR2) IS
431 SELECT USER_ID FROM ego_user_v where UPPER(user_NAME) = v_user_name;
432
433 Cursor Ego_Structure_Id_Name_Csr(v_structure_type_name in VARCHAR2) IS
434 SELECT STRUCTURE_TYPE_ID
435 FROM bom_structure_types_b
436 WHERE STRUCTURE_TYPE_NAME = v_structure_type_name;
437
438 Cursor Ego_Structure_Type_Csr(v_structure_type_id IN NUMBER, v_master_org_id IN NUMBER) IS
439 SELECT DISPLAY_NAME
440 FROM (SELECT DISTINCT bad.display_name DISPLAY_NAME,
441 bad.alternate_designator_code ALTERNATE_DESIGNATOR_CODE,
442 bad.structure_type_id STRUCTURE_TYPE_ID
443 FROM BOM_ALTERNATE_DESIGNATORS_VL bad,
444 BOM_STRUCTURE_TYPES_VL bsvl
445 WHERE bad.alternate_designator_code IS NOT NULL
446 AND bad.structure_type_id IN
447 (SELECT bst.structure_type_id
448 FROM BOM_STRUCTURE_TYPES_B bst
449 START WITH bst.structure_type_id = v_structure_type_id
450 CONNECT BY PRIOR bst.parent_structure_type_id =
451 bst.structure_type_id)
452 UNION
453 SELECT bom_globals.retrieve_message('BOM', 'BOM_PRIMARY') DISPLAY_NAME,
454 BOM_Globals.GET_PRIMARY_UI() ALTERNATE_DESIGNATOR_CODE,
455 structure_type_iD
456 FROM BOM_STRUCTURE_TYPES_B b
457 WHERE structure_type_name = 'All-Structure Types') QRSLT
458 WHERE ((ALTERNATE_DESIGNATOR_CODE = BOM_Globals.GET_PRIMARY_UI() OR
459 ALTERNATE_DESIGNATOR_CODE IN
460 (SELECT DISTINCT ALTERNATE_DESIGNATOR_CODE
461 FROM BOM_ALTERNATE_DESIGNATORS_VL b, mtl_parameters p
462 WHERE p.master_organization_id = v_master_org_id
463 AND b.ORGANIZATION_ID = p.organization_id)) AND
464 ((v_structure_type_id =
465 (Select Structure_Type_id
466 FROM bom_structure_types_b
467 where structure_type_name = 'Packaging Hierarchy') AND
468 ALTERNATE_DESIGNATOR_CODE IN
469 (SELECT ALTERNATE_DESIGNATOR_CODE
470 FROM BOM_ALTERNATE_DESIGNATORS b1
471 WHERE b1.organization_id = v_master_org_id
472 and is_preferred = 'Y')) OR
473 (v_structure_type_id IS NULL OR
474 v_structure_type_id <>
475 (Select Structure_Type_id
476 FROM bom_structure_types_b
477 where structure_type_name = 'Packaging Hierarchy'))));
478
479 Cursor Change_Order_Category_Csr(v_change_mgmt_type_code IN VARCHAR2) IS
480 select type_name,
481 change_mgmt_type_code,
482 change_order_type_id,
483 decode(sign(nvl(disable_date, sysdate + 1) - sysdate),
484 1,
485 'N',
486 'Y') DISABLE_FLAG
487 from eng_change_order_types_vl
488 where base_change_mgmt_type_code = v_change_mgmt_type_code
489 and type_classification = 'CATEGORY';
490
491 Cursor Change_Order_Type_Csr(v_change_mgmt_type_code IN VARCHAR2, v_change_order_type_id IN NUMBER) IS
492 SELECT change_order_type_id
493 FROM (select description,
494 type_name change_order_type,
495 change_order_type_id,
496 change_mgmt_type_code,
497 assembly_type,
498 subject_id
499 from eng_change_order_types_v
500 where (disable_date is null or disable_date >= sysdate)
501 and nvl(START_DATE, sysdate) <= sysdate
502 and type_classification = 'HEADER'
503 and NOT (nvl(FND_PROFILE.value('ENG:ENG_ITEM_ECN_ACCESS'), 1) = 2 and
504 assembly_type = 2 and base_change_mgmt_type_code =
505 v_change_mgmt_type_code)
506 order by change_order_type) QRSLT
507 WHERE CHANGE_MGMT_TYPE_CODE = v_change_mgmt_type_code
508 AND CHANGE_ORDER_TYPE_ID = v_change_order_type_id;
509
510 Cursor Change_Order_Notice_Csr(v_change_mgmt_type_code IN VARCHAR2, v_change_notice IN VARCHAR2, v_master_org_id IN NUMBER) IS
511 SELECT change_notice
512 FROM (SELECT DISTINCT eec.change_notice, eec.CHANGE_MGMT_TYPE_CODE
513 FROM eng_engineering_changes eec,
514 eng_lifecycle_statuses els,
515 eng_change_statuses_vl ecs
516 WHERE els.entity_name(+) = 'ENG_CHANGE'
517 AND els.entity_id1(+) = eec.change_id
518 AND els.status_code(+) = eec.status_code
519 AND els.active_flag(+) = 'Y'
520 AND ecs.status_code = eec.status_code
521 AND (eec.status_type in (0, 1) OR
522 (eec.status_type = 10 AND ecs.status_type = 1))
523 AND (eec.status_code = 0 OR
524 (els.workflow_status is null OR
525 (els.workflow_status is not null AND
526 els.workflow_status <> 'IN_PROGRESS') OR
527 (els.workflow_status = 'IN_PROGRESS' AND
528 els.change_editable_flag = 'Y')))
529 AND (eec.status_code = 0 OR
530 (eec.approval_status_type is null OR
531 (eec.approval_status_type is not null AND
532 eec.approval_status_type <> 3) OR
533 (eec.approval_status_type = 3 AND
534 els.change_editable_flag = 'Y')))) QRSLT
535 WHERE (CHANGE_NOTICE IN
536 (SELECT CHANGE_NOTICE
537 FROM ENG_ENGINEERING_CHANGES EEC1, MTL_PARAMETERS mtp
538 WHERE EEC1.ORGANIZATION_ID = mtp.ORGANIZATION_ID
539 and mtp.MASTER_ORGANIZATION_ID = v_master_org_id) AND
540 (UPPER(CHANGE_MGMT_TYPE_CODE) =
541 UPPER(v_change_mgmt_type_code)))
542 AND change_notice = v_change_notice;
543
544 Cursor Ego_Def_Match_Rule_Csr(v_def_match_rule_cust_app_id IN NUMBER, v_def_match_rule_cust_code IN VARCHAR2) IS
545 select CUSTOMIZATION_CODE, REGION_CODE
546 from EGO_CRITERIA_TEMPLATES_V
547 WHERE REGION_CODE LIKE 'EGO_ITEM_MATCH_RULE_REGION'
548 AND CLASSIFICATION1 = -1
549 AND CUSTOMIZATION_APPLICATION_ID = v_def_match_rule_cust_app_id
550 AND CUSTOMIZATION_CODE = v_def_match_rule_cust_code;
551
552 l_master_org_rec Ego_Master_Org_Csr%ROWTYPE;
553
554 l_batch_id NUMBER := NULL; --p_batch_id;
555 l_source_system_id NUMBER := NULL;
556 l_source_system_code HZ_ORIG_SYSTEMS_VL.ORIG_SYSTEM%TYPE := p_source_system_code;
557 l_organization_id NUMBER := p_organization_id;
558 l_master_org_id NUMBER := NULL;
559
560 l_batch_type_display_name VARCHAR2(30) := p_batch_type_display_name;
561 l_assignee_name VARCHAR2(100) := p_assignee_name;
562 l_assignee_id NUMBER;
563
564 l_batch_name VARCHAR2(80) := p_batch_name;
565 l_batch_status VARCHAR2(1) := 'A';
566
567 l_option_set_id NUMBER := NULL; --p_option_set_id;
568 l_apply_def_match_rule_all VARCHAR2(1) := p_apply_def_match_rule_all;
569
570 l_match_on_data_load VARCHAR2(1) := p_match_on_data_load;
571 l_confirm_single_match VARCHAR2(1) := p_confirm_single_match;
572 l_import_on_data_load VARCHAR2(1) := p_import_on_data_load;
573 l_revision_import_policy VARCHAR2(30) := p_revision_import_policy;
574
575 l_import_xref_only VARCHAR2(1) := p_import_xref_only;
576
577 l_structure_type_id NUMBER := null; --p_structure_type_id;
578 l_structure_name VARCHAR2(10) := p_structure_name;
579 l_structure_effectivity_type NUMBER := p_structure_effectivity_type;
580 l_effectivity_date DATE := p_effectivity_date;
581 l_from_end_item_unit_number VARCHAR2(30) := p_from_end_item_unit_number;
582 l_structure_content VARCHAR2(30) := p_structure_content;
583
584 l_change_order_creation VARCHAR2(30) := p_change_order_creation;
585 l_add_all_to_change_flag VARCHAR2(1) := p_add_all_to_change_flag;
586 l_change_mgmt_type_code VARCHAR2(30) := p_change_mgmt_type_code;
587 l_change_type_id NUMBER := p_change_type_id;
588 l_change_notice VARCHAR2(10) := p_change_notice;
589 l_change_name VARCHAR2(240) := p_change_name;
590 l_change_description VARCHAR2(2000) := p_change_description;
591
592 l_object_version_number NUMBER := 1; --p_object_version_number;
593
594 l_def_match_rule_cust_app_id NUMBER := p_def_match_rule_cust_app_id;
595 l_def_match_rule_cust_code VARCHAR2(30) := p_def_match_rule_cust_code;
596 l_def_match_rule_rn_app_id NUMBER;
597 l_def_match_rule_rn_code VARCHAR2(30);
598
599 l_nir_option VARCHAR2(1) := p_nir_option;
600 l_enabled_for_data_pool VARCHAR2(1) := p_enabled_for_data_pool;
601
602 --l_tmp_import_batch_rec EGO_IMPORT_BATCHES_B%ROWTYPE;
603 l_tmp_batch_type_name VARCHAR2(30);
604 l_tmp_ssm_options_rec EGO_IMPORT_OPTION_SETS%ROWTYPE;
605 l_def_match_rule_rec Ego_Def_Match_Rule_Csr%ROWTYPE;
606
607 l_tmp_change_order_type_id NUMBER;
608 l_tmp_change_notice VARCHAR2(10);
609
610 l_api_name VARCHAR2(50) := 'CREATE_IMPORT_BATCH';
611 l_err_text VARCHAR2(200) := '';
612 l_import_batches_rowid VARCHAR2(50) := null;
613
614 INVALID_ORGANIZATION_ID EXCEPTION;
615 INVALID_SSM_ID EXCEPTION;
616 INVALID_STRUCTURE_TYPE_ID EXCEPTION;
617 INVALID_STRUCTURE_EFF_TYPE EXCEPTION;
618 INVALID_DEF_MAT_RULE_CODE EXCEPTION;
619 INVALID_CHG_ORDER_TYPE EXCEPTION;
620 INVALID_CHG_ORDER_NOTICE EXCEPTION;
621 INVALID_BATCH_TYPE_ID EXCEPTION;
622 UNSUPPORTED_ECO_CREATION_TYPE EXCEPTION;
623
624 BEGIN
625 x_return_status := FND_API.g_ret_sts_success;
626 --l_user_id := FND_GLOBAL.User_Id;
627
628 -------------------------------------------------------
629 --- Validate Batch Header ---
630 -------------------------------------------------------
631 --Debug_Msg('--Starting to validate Import Batch Header--');
632 -- 1. Validate Source System info
633
634 IF l_source_system_code IS NOT NULL THEN
635 OPEN Ego_Source_System_Csr(v_source_system_code => l_source_system_code);
636 FETCH Ego_Source_System_Csr
637 INTO l_source_system_id;
638 IF Ego_Source_System_Csr%NOTFOUND THEN
639 RAISE INVALID_SSM_ID;
640 END IF;
641 CLOSE Ego_Source_System_Csr;
642
643 OPEN Ego_Ssm_Options_Csr(v_source_system_id => l_source_system_id);
644 FETCH Ego_Ssm_Options_Csr
645 INTO l_tmp_ssm_options_rec;
646
647 IF Ego_Ssm_Options_Csr%NOTFOUND THEN
648 RAISE INVALID_SSM_ID;
649 END IF;
650
651 ELSE
652 RAISE INVALID_SSM_ID;
653 END IF;
654
655 --Validate organization info
656 IF NVL(l_organization_id, -1) = -1 THEN
657 RAISE INVALID_ORGANIZATION_ID;
658 END IF;
659 OPEN Ego_Master_Org_Csr(v_organization_id => l_organization_id);
660 FETCH Ego_Master_Org_Csr
661 INTO l_master_org_rec;
662 IF Ego_Master_Org_Csr%NOTFOUND THEN
663 RAISE INVALID_ORGANIZATION_ID;
664 ELSE
665 l_master_org_id := l_master_org_rec.MASTER_ORGANIZATION_ID;
666 END IF;
667 CLOSE Ego_Master_Org_Csr;
668
669 --Validate assignee user info
670 IF l_assignee_name IS NOT NULL THEN
671 OPEN Ego_Ssm_Assignee_Csr(v_user_name => l_assignee_name);
672 FETCH Ego_Ssm_Assignee_Csr
673 INTO l_assignee_id;
674 IF Ego_Ssm_Assignee_Csr%NOTFOUND THEN
675 l_assignee_name := 'PLMMGR';
676 OPEN Ego_Ssm_Assignee_Csr(v_user_name => l_assignee_name);
677 FETCH Ego_Ssm_Assignee_Csr
678 INTO l_assignee_id;
679 END IF;
680 CLOSE Ego_Ssm_Assignee_Csr;
681 ELSE
682 l_assignee_id := fnd_global.user_id;
683 END IF;
684
685 -- 2. Generate Batch Option Set ID & Batch Id.
686 SELECT MTL_SYSTEM_ITEMS_INTF_SETS_S.nextval INTO l_batch_id FROM dual;
687 select EGO_IMPORT_OPTION_SETS_S.nextval INTO l_option_set_id FROM dual;
688
689 -------------------------------------------------------
690 --- Initializing Input Params ---
691 -------------------------------------------------------
692 select nvl(l_apply_def_match_rule_all,
693 l_tmp_ssm_options_rec.APPLY_DEF_MATCH_RULE_ALL),
694 nvl(l_match_on_data_load,
695 l_tmp_ssm_options_rec.MATCH_ON_DATA_LOAD),
696 nvl(l_confirm_single_match,
697 l_tmp_ssm_options_rec.CONFIRM_SINGLE_MATCH),
698 nvl(l_import_on_data_load,
699 l_tmp_ssm_options_rec.IMPORT_ON_DATA_LOAD),
700 nvl(l_revision_import_policy,
701 l_tmp_ssm_options_rec.REVISION_IMPORT_POLICY),
702 nvl(l_import_xref_only, l_tmp_ssm_options_rec.IMPORT_XREF_ONLY),
703 nvl(l_structure_type_id, l_tmp_ssm_options_rec.STRUCTURE_TYPE_ID),
704 nvl(l_structure_name, l_tmp_ssm_options_rec.STRUCTURE_NAME),
705 nvl(l_structure_effectivity_type,
706 l_tmp_ssm_options_rec.STRUCTURE_EFFECTIVITY_TYPE),
707 nvl(l_effectivity_date, l_tmp_ssm_options_rec.EFFECTIVITY_DATE),
708 nvl(l_from_end_item_unit_number,
709 l_tmp_ssm_options_rec.FROM_END_ITEM_UNIT_NUMBER),
710 nvl(l_structure_content, l_tmp_ssm_options_rec.STRUCTURE_CONTENT),
711 nvl(l_change_order_creation,
712 l_tmp_ssm_options_rec.CHANGE_ORDER_CREATION),
713 nvl(l_add_all_to_change_flag,
714 l_tmp_ssm_options_rec.ADD_ALL_TO_CHANGE_FLAG),
715 nvl(l_change_mgmt_type_code,
716 l_tmp_ssm_options_rec.CHANGE_MGMT_TYPE_CODE),
717 nvl(l_change_type_id, l_tmp_ssm_options_rec.CHANGE_TYPE_ID),
718 nvl(l_change_notice, l_tmp_ssm_options_rec.CHANGE_NOTICE),
719 nvl(l_change_name, l_tmp_ssm_options_rec.CHANGE_NAME),
720 nvl(l_change_description,
721 l_tmp_ssm_options_rec.CHANGE_DESCRIPTION),
722 nvl(l_object_version_number,
723 l_tmp_ssm_options_rec.OBJECT_VERSION_NUMBER),
724 nvl(l_def_match_rule_cust_app_id,
725 l_tmp_ssm_options_rec.DEF_MATCH_RULE_CUST_APP_ID),
726 nvl(l_def_match_rule_cust_code,
727 l_tmp_ssm_options_rec.DEF_MATCH_RULE_CUST_CODE),
728 nvl(l_def_match_rule_rn_app_id,
729 l_tmp_ssm_options_rec.DEF_MATCH_RULE_RN_APP_ID),
730 nvl(l_def_match_rule_rn_code,
731 l_tmp_ssm_options_rec.DEF_MATCH_RULE_RN_CODE),
732 nvl(l_nir_option, l_tmp_ssm_options_rec.NIR_OPTION),
733 nvl(l_enabled_for_data_pool,
734 l_tmp_ssm_options_rec.ENABLED_FOR_DATA_POOL)
735 INTO l_apply_def_match_rule_all,
736 l_match_on_data_load,
737 l_confirm_single_match,
738 l_import_on_data_load,
739 l_revision_import_policy,
740 l_import_xref_only,
741 l_structure_type_id,
742 l_structure_name,
743 l_structure_effectivity_type,
744 l_effectivity_date,
745 l_from_end_item_unit_number,
746 l_structure_content,
747 l_change_order_creation,
748 l_add_all_to_change_flag,
749 l_change_mgmt_type_code,
750 l_change_type_id,
751 l_change_notice,
752 l_change_name,
753 l_change_description,
754 l_object_version_number,
755 l_def_match_rule_cust_app_id,
756 l_def_match_rule_cust_code,
757 l_def_match_rule_rn_app_id,
758 l_def_match_rule_rn_code,
759 l_nir_option,
760 l_enabled_for_data_pool
761 FROM DUAL;
762
763 IF l_source_system_id = 7 THEN
764 --should ignore l_apply_def_match_rule_all, l_match_on_data_load, l_confirm_single_match, set them to null.
765 l_match_on_data_load := l_tmp_ssm_options_rec.MATCH_ON_DATA_LOAD;
766 l_confirm_single_match := l_tmp_ssm_options_rec.CONFIRM_SINGLE_MATCH;
767 l_def_match_rule_cust_app_id := l_tmp_ssm_options_rec.def_match_rule_cust_app_id;
768 l_def_match_rule_cust_code := l_tmp_ssm_options_rec.def_match_rule_cust_code;
769 l_def_match_rule_rn_app_id := l_tmp_ssm_options_rec.def_match_rule_rn_app_id;
770 l_def_match_rule_rn_code := l_tmp_ssm_options_rec.def_match_rule_rn_code;
771
772 l_apply_def_match_rule_all := l_tmp_ssm_options_rec.APPLY_DEF_MATCH_RULE_ALL;
773 l_import_xref_only := l_tmp_ssm_options_rec.IMPORT_XREF_ONLY;
774 l_revision_import_policy := l_tmp_ssm_options_rec.revision_import_policy;
775 End IF;
776 CLOSE Ego_Ssm_Options_Csr;
777
778 -- 3. Validate Batch Type
779 IF NVL(l_batch_type_display_name, '') = '' THEN
780 RAISE INVALID_BATCH_TYPE_ID;
781 ELSE
782 select obj_name
783 into l_tmp_batch_type_name
784 from fnd_objects_vl
785 where obj_name in ('EGO_ITEM', 'BOM_STRUCTURE')
786 AND upper(display_name) = upper(l_batch_type_display_name);
787
788 IF l_tmp_batch_type_name <> 'BOM_STRUCTURE' THEN
789 --Set all Structure related setting to nulls.
790 l_structure_type_id := null;
791 l_structure_name := null;
792 l_structure_effectivity_type := null;
793 l_effectivity_date := null;
794 l_from_end_item_unit_number := null;
795 l_structure_content := null;
796 ELSIF l_tmp_batch_type_name = 'BOM_STRUCTURE' THEN
797 --Check the structure type id, validate the structure name
798 --if not valid, set to default Root type.
799 IF NVL(l_structure_name, '') = '' THEN
800 RAISE INVALID_STRUCTURE_TYPE_ID;
801 END IF;
802
803 OPEN Ego_Structure_Id_Name_Csr(v_structure_type_name => l_structure_name);
804 FETCH Ego_Structure_Id_Name_Csr
805 INTO l_structure_type_id;
806 IF Ego_Structure_Id_Name_Csr%NOTFOUND THEN
807 RAISE INVALID_STRUCTURE_TYPE_ID;
808 END IF;
809 CLOSE Ego_Structure_Id_Name_Csr;
810
811 OPEN Ego_Structure_Type_Csr(v_structure_type_id => l_structure_type_id,
812 v_master_org_id => l_master_org_id);
813 FETCH Ego_Structure_Type_Csr
814 INTO l_structure_name;
815 IF Ego_Structure_Type_Csr%NOTFOUND THEN
816 RAISE INVALID_STRUCTURE_TYPE_ID;
817 ELSE
818 IF NVL(l_structure_effectivity_type, 1) = 1 THEN
819 --Date
820 l_from_end_item_unit_number := NULL;
821 ELSIF l_structure_effectivity_type = 2 THEN
822 l_effectivity_date := NULL;
823 ELSE
824 RAISE INVALID_STRUCTURE_EFF_TYPE;
825 END IF;
826 CLOSE Ego_Structure_Type_Csr;
827 END IF;
828
829 END IF;
830
831 END IF;
832
833 -------------------------------------------------------
834 -- Validate Data Load Options
835 -------------------------------------------------------
836 -- 1. Validate Match Rule
837 IF l_source_system_id <> 7 THEN
838 -- Even Match On Data Load is NO, you should still be able to save changes to the default match rule.
839 IF l_match_on_data_load <> 'Y' THEN
840 l_apply_def_match_rule_all := NULL;
841 l_match_on_data_load := NULL;
842 l_confirm_single_match := NULL;
843 l_def_match_rule_cust_app_id := NULL;
844 l_def_match_rule_cust_code := NULL;
845 l_def_match_rule_rn_app_id := NULL;
846 l_def_match_rule_rn_code := NULL;
847
848 ELSIF l_match_on_data_load = 'Y' THEN
849 IF l_def_match_rule_cust_code IS NULL OR
850 l_def_match_rule_cust_code IS NULL THEN
851 RAISE INVALID_DEF_MAT_RULE_CODE;
852 END IF;
853 -- validate the input match rule
854 OPEN Ego_Def_Match_Rule_Csr(v_def_match_rule_cust_app_id => l_def_match_rule_cust_app_id,
855 v_def_match_rule_cust_code => l_def_match_rule_cust_code);
856 FETCH Ego_Def_Match_Rule_Csr
857 INTO l_def_match_rule_rec;
858
859 IF Ego_Def_Match_Rule_Csr%NOTFOUND THEN
860 RAISE INVALID_DEF_MAT_RULE_CODE;
861 END IF;
862 l_def_match_rule_rn_code := l_def_match_rule_rec.REGION_CODE;
863 CLOSE Ego_Def_Match_Rule_Csr;
864
865 END IF;
866 END IF;
867
868 -- 2. Data Load Import
869 IF l_source_system_id = 7 THEN
870 l_revision_import_policy := l_tmp_ssm_options_rec.REVISION_IMPORT_POLICY;
871 END IF;
872
873 -------------------------------------------------------
874 -- Validate Import Options
875 -------------------------------------------------------
876 -- 1. if 'Data Import' is 'Create Cross References Only' then no CO and NIR required.
877 IF l_import_xref_only = 'Y' THEN
878 --NONE
879 l_change_order_creation := NULL;
880 l_add_all_to_change_flag := NULL;
881 l_change_mgmt_type_code := NULL;
882 l_change_type_id := NULL;
883 l_change_notice := NULL;
884 l_change_name := NULL;
885 l_change_description := NULL;
886
887 ELSE
888
889 IF l_change_order_creation = 'O' THEN
890 --NONE
891 NULL;
892 ELSE
893 -- Validate Change Order Category & Change Order Type.
894 OPEN Change_Order_Type_Csr(v_change_mgmt_type_code => l_change_mgmt_type_code,
895 v_change_order_type_id => l_change_type_id);
896 FETCH Change_Order_Type_Csr
897 INTO l_tmp_change_order_type_id;
898
899 IF Change_Order_Type_Csr%NOTFOUND THEN
900 -- invalid input change order category and change order type.
901 RAISE INVALID_CHG_ORDER_TYPE;
902 ELSE
903 IF l_change_order_creation = 'N' THEN
904 --NEW
905 NULL; --Generate Change Order Number
906 RAISE UNSUPPORTED_ECO_CREATION_TYPE;
907 --NOT SUPPORTTED IN THIS VERSION
908 ELSIF l_change_order_creation = 'E' THEN
909 --EXISTING
910
911 -- Validate Change Notice
912 OPEN Change_Order_Notice_Csr(v_change_mgmt_type_code => l_change_mgmt_type_code,
913 v_change_notice => l_change_notice,
914 v_master_org_id => l_master_org_id);
915 FETCH Change_Order_Notice_Csr
916 into l_tmp_change_notice;
917
918 IF Change_Order_Notice_Csr%NOTFOUND THEN
919 RAISE INVALID_CHG_ORDER_NOTICE;
920 END IF;
921 CLOSE Change_Order_Notice_Csr;
922
923 ELSE
924 l_change_order_creation := 'O';
925 END IF;
926 END IF;
927 CLOSE Change_Order_Type_Csr;
928 END IF;
929
930 END IF;
931
932 INSERT_ROW(X_ROWID => l_import_batches_rowid,
933 X_BATCH_ID => l_batch_id,
934 X_ORGANIZATION_ID => l_master_org_id,
935 X_SOURCE_SYSTEM_ID => l_source_system_id,
936 X_BATCH_TYPE => l_tmp_batch_type_name,
937 X_ASSIGNEE => l_assignee_id,
938 X_BATCH_STATUS => l_batch_status,
939 X_OBJECT_VERSION_NUMBER => l_object_version_number,
940 X_NAME => l_batch_name,
941 X_DESCRIPTION => l_batch_name,
942 X_CREATION_DATE => sysdate,
943 X_CREATED_BY => fnd_global.user_id,
944 X_LAST_UPDATE_DATE => sysdate,
945 X_LAST_UPDATED_BY => fnd_global.user_id,
946 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
947
948 INSERT INTO EGO_IMPORT_OPTION_SETS
949 (OPTION_SET_ID,
950 SOURCE_SYSTEM_ID,
951 BATCH_ID,
952 MATCH_ON_DATA_LOAD,
953 DEF_MATCH_RULE_CUST_APP_ID,
954 DEF_MATCH_RULE_CUST_CODE,
955 DEF_MATCH_RULE_RN_APP_ID,
956 DEF_MATCH_RULE_RN_CODE,
957 APPLY_DEF_MATCH_RULE_ALL,
958 CONFIRM_SINGLE_MATCH,
959 ENABLED_FOR_DATA_POOL,
960 IMPORT_ON_DATA_LOAD,
961 REVISION_IMPORT_POLICY,
962 IMPORT_XREF_ONLY,
963 STRUCTURE_TYPE_ID,
964 STRUCTURE_NAME,
965 STRUCTURE_EFFECTIVITY_TYPE,
966 EFFECTIVITY_DATE,
967 FROM_END_ITEM_UNIT_NUMBER,
968 STRUCTURE_CONTENT,
969 CHANGE_ORDER_CREATION,
970 ADD_ALL_TO_CHANGE_FLAG,
971 CHANGE_MGMT_TYPE_CODE,
972 CHANGE_TYPE_ID,
973 CHANGE_NOTICE,
974 CHANGE_NAME,
975 CHANGE_DESCRIPTION,
976 NIR_OPTION,
977 OBJECT_VERSION_NUMBER,
978 CREATED_BY,
979 CREATION_DATE,
980 LAST_UPDATED_BY,
981 LAST_UPDATE_DATE,
982 LAST_UPDATE_LOGIN)
983 VALUES
984 (l_option_set_id,
985 l_source_system_id,
986 l_batch_id,
987 l_match_on_data_load,
988 l_def_match_rule_cust_app_id,
989 l_def_match_rule_cust_code,
990 l_def_match_rule_rn_app_id,
991 l_def_match_rule_rn_code,
992 l_apply_def_match_rule_all,
993 l_confirm_single_match,
994 l_enabled_for_data_pool,
995 l_import_on_data_load,
996 l_revision_import_policy,
997 l_import_xref_only,
998 l_structure_type_id,
999 l_structure_name,
1000 l_structure_effectivity_type,
1001 l_effectivity_date,
1002 l_from_end_item_unit_number,
1003 l_structure_content,
1004 l_change_order_creation,
1005 l_add_all_to_change_flag,
1006 l_change_mgmt_type_code,
1007 l_change_type_id,
1008 l_change_notice,
1009 l_change_name,
1010 l_change_description,
1011 l_nir_option,
1012 l_object_version_number,
1013 fnd_global.user_id,
1014 sysdate,
1015 fnd_global.user_id,
1016 sysdate,
1017 fnd_global.login_id);
1018
1019 -------------------------------------------------------
1020 -- Set OUT Argument values
1021 -------------------------------------------------------
1022 x_batch_id := l_batch_id;
1023
1024 EXCEPTION
1025 WHEN INVALID_ORGANIZATION_ID THEN
1026 x_error_msg := 'Invalid Organization ID';
1027 x_return_status := FND_API.g_ret_sts_error;
1028
1029 WHEN INVALID_SSM_ID THEN
1030 x_error_msg := 'Invalid Source System Code or ID';
1031 x_return_status := FND_API.g_ret_sts_error;
1032
1033 WHEN INVALID_STRUCTURE_TYPE_ID THEN
1034 x_error_msg := 'Invalid Structure Type Name or ID';
1035 IF Ego_Structure_Type_Csr%ISOPEN THEN
1036 CLOSE Ego_Structure_Type_Csr;
1037 END IF;
1038 x_return_status := FND_API.g_ret_sts_error;
1039
1040 WHEN INVALID_STRUCTURE_EFF_TYPE THEN
1041 x_error_msg := 'Invalid Structure Effectivity Type';
1042 x_return_status := FND_API.g_ret_sts_error;
1043
1044 WHEN INVALID_DEF_MAT_RULE_CODE THEN
1045 x_error_msg := 'Invalid Default Match Rule Code';
1046 IF Ego_Def_Match_Rule_Csr%ISOPEN THEN
1047 CLOSE Ego_Def_Match_Rule_Csr;
1048 END IF;
1049 x_return_status := FND_API.g_ret_sts_error;
1050
1051 WHEN INVALID_CHG_ORDER_TYPE THEN
1052 x_error_msg := 'Invalid Change Order Type';
1053 IF Change_Order_Type_Csr%ISOPEN THEN
1054 CLOSE Change_Order_Type_Csr;
1055 END IF;
1056 x_return_status := FND_API.g_ret_sts_error;
1057
1058 WHEN INVALID_CHG_ORDER_NOTICE THEN
1059 x_error_msg := 'Invalid Change Order Notice';
1060 IF Change_Order_Notice_Csr%ISOPEN THEN
1061 CLOSE Change_Order_Notice_Csr;
1062 END IF;
1063 x_return_status := FND_API.g_ret_sts_error;
1064
1065 WHEN INVALID_BATCH_TYPE_ID THEN
1066 x_error_msg := 'Invalid Import Batch Type';
1067 x_return_status := FND_API.g_ret_sts_error;
1068
1069 WHEN UNSUPPORTED_ECO_CREATION_TYPE THEN
1070 x_error_msg := 'Not support create new ECO';
1071 x_return_status := FND_API.g_ret_sts_error;
1072
1073 WHEN OTHERS THEN
1074 x_return_status := FND_API.g_ret_sts_error;
1075 l_err_text := SUBSTRB(SQLERRM, 1, 240);
1076 x_error_msg := l_err_text;
1077
1078 END Create_Import_Batch;
1079
1080
1081 /* Use this API to change status of import batch.
1082 * @param p_batch_id
1083 * @param p_batch_name
1084 * @param p_batch_status A Code indicating the import batch status.
1085 * 'A' -- Active status
1086 * 'C' -- Completed status
1087 * 'R' -- Rejected status
1088 * 'P' -- Pending status
1089 */
1090 PROCEDURE UPDATE_BATCH_STATUS(p_batch_id NUMBER,
1091 p_batch_name VARCHAR2,
1092 p_batch_status VARCHAR2,
1093 x_return_status OUT NOCOPY VARCHAR2,
1094 x_error_msg OUT NOCOPY VARCHAR2) IS
1095 Cursor Ego_Import_Batch_Csr(v_batch_id NUMBER) IS
1096 SELECT B.BATCH_ID, TL.NAME
1097 FROM EGO_IMPORT_BATCHES_B B, EGO_IMPORT_BATCHES_TL TL
1098 WHERE B.BATCH_ID = TL.BATCH_ID
1099 AND B.BATCH_ID = v_batch_id
1100 AND TL.LANGUAGE = USERENV('LANG');
1101
1102 Cursor Ego_Batch_Status_Csr(v_status_code VARCHAR2) IS
1103 SELECT LOOKUP_CODE, MEANING
1104 FROM FND_LOOKUPS
1105 WHERE LOOKUP_TYPE = 'EGO_IMPORT_BATCH_STATUS'
1106 AND LOOKUP_CODE = v_status_code;
1107
1108 l_import_batch_rec Ego_Import_Batch_Csr%ROWTYPE;
1109 l_batch_status_rec Ego_Batch_Status_Csr%ROWTYPE;
1110 l_err_text VARCHAR2(200);
1111
1112 INVALID_BATCH_ID EXCEPTION;
1113 INVALID_BATCH_NAME EXCEPTION;
1114 INVALID_BATCH_STATUS EXCEPTION;
1115
1116 BEGIN
1117 x_return_status := FND_API.g_ret_sts_success;
1118
1119 --Validate import batch id and name
1120 OPEN Ego_Import_Batch_Csr(v_batch_id => NVL(p_batch_id, 0));
1121 FETCH Ego_Import_Batch_Csr
1122 INTO l_import_batch_rec;
1123 IF Ego_Import_Batch_Csr%NOTFOUND THEN
1124 RAISE INVALID_BATCH_ID;
1125 ELSIF p_batch_name <> l_import_batch_rec.NAME THEN
1126 RAISE INVALID_BATCH_NAME;
1127 END IF;
1128 CLOSE Ego_Import_Batch_Csr;
1129
1130 --Validate batch status
1131 IF p_batch_status IS NULL OR p_batch_status = '' THEN
1132 RAISE INVALID_BATCH_STATUS;
1133 END IF;
1134 OPEN Ego_Batch_Status_Csr(v_status_code => p_batch_status);
1135 FETCH Ego_Batch_Status_Csr
1136 INTO l_batch_status_rec;
1137 IF Ego_Batch_Status_Csr%NOTFOUND THEN
1138 RAISE INVALID_BATCH_STATUS;
1139 END IF;
1140 CLOSE Ego_Batch_Status_Csr;
1141
1142 --Update table EGO_IMPORT_BATCHES_B
1143 UPDATE EGO_IMPORT_BATCHES_B
1144 SET BATCH_STATUS = p_batch_status
1145 WHERE BATCH_ID = p_batch_id;
1146
1147 EXCEPTION
1148 WHEN INVALID_BATCH_ID THEN
1149 x_return_status := FND_API.g_ret_sts_error;
1150 x_error_msg := 'Invalid Import Batch ID';
1151 IF Ego_Import_Batch_Csr%ISOPEN THEN
1152 CLOSE Ego_Import_Batch_Csr;
1153 END IF;
1154
1155 WHEN INVALID_BATCH_NAME THEN
1156 x_return_status := FND_API.g_ret_sts_error;
1157 x_error_msg := 'Invalid Import Batch Name';
1158 IF Ego_Batch_Status_Csr%ISOPEN THEN
1159 CLOSE Ego_Batch_Status_Csr;
1160 END IF;
1161
1162 WHEN OTHERS THEN
1163 x_return_status := FND_API.g_ret_sts_error;
1164 l_err_text := SUBSTRB(SQLERRM, 1, 240);
1165 x_error_msg := l_err_text;
1166
1167 END UPDATE_BATCH_STATUS;
1168
1169 END EGO_IMPORT_BATCHES_PKG;