[Home] [Help]
PACKAGE BODY: APPS.INV_ITEM_REVISION_PUB
Source
1 PACKAGE BODY INV_ITEM_REVISION_PUB AS
2 /* $Header: INVPREVB.pls 120.7.12010000.2 2008/09/09 11:42:11 appldev ship $ */
3
4 -- ============================================================================
5 -- Package global variables and cursors
6 -- ============================================================================
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_ITEM_REVISION_PUB';
9 G_FILE_NAME CONSTANT VARCHAR2(12) := 'INVPREVB.pls';
10
11 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
12 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
13
14 G_Miss_Char VARCHAR2(1) := fnd_api.G_MISS_CHAR;
15 G_Miss_Num NUMBER := fnd_api.G_MISS_NUM;
16 G_Miss_Date DATE := fnd_api.G_MISS_DATE;
17
18 G_Language_Code VARCHAR2(4);
19 G_Revision_Id NUMBER;
20 G_Object_Version_Number NUMBER;
21
22 G_Message_API VARCHAR2(3) := 'FND';
23
24 --
25 -- Capture the sysdate at once for the whole process. During the process we use
26 -- sysdate in many places for compare, insert and update. It is essential that
27 -- we deal with the same sysdate value since the revisions are time sensitive
28 -- upto seconds. Date will be assigned in the entry procedure.
29 --
30 G_Sysdate DATE;
31
32 CURSOR org_item_exists_cur
33 ( p_inventory_item_id NUMBER
34 , p_organization_id NUMBER
35 ) IS
36 SELECT 'x'
37 FROM mtl_system_items_b
38 WHERE
39 inventory_item_id = p_inventory_item_id
40 AND organization_id = p_organization_id;
41
42 CURSOR Item_Revision_Exists_cur
43 ( p_inventory_item_id NUMBER
44 , p_organization_id NUMBER
45 , p_revision VARCHAR2
46 ) IS
47 SELECT object_version_number
48 FROM mtl_item_revisions_b
49 WHERE
50 inventory_item_id = p_inventory_item_id
51 AND organization_id = p_organization_id
52 AND revision = p_revision;
53
54 --3655522 begin
55 CURSOR Upd_Item_Rev_Exists_cur
56 ( p_inventory_item_id NUMBER
57 , p_organization_id NUMBER
58 , p_revision VARCHAR2
59 , p_revision_id NUMBER
60 ) IS
61 SELECT object_version_number
62 FROM mtl_item_revisions_b
63 WHERE
64 inventory_item_id = p_inventory_item_id
65 AND organization_id = p_organization_id
66 AND revision = p_revision
67 AND revision_id <> p_revision_id;
68 --3655522 end
69
70 CURSOR Item_Revision_Id_Exists_cur
71 ( p_inventory_item_id NUMBER
72 , p_organization_id NUMBER
73 , p_revision_id NUMBER
74 ) IS
75 SELECT object_version_number
76 FROM mtl_item_revisions_b
77 WHERE
78 inventory_item_id = p_inventory_item_id
79 AND organization_id = p_organization_id
80 AND revision_id = p_revision_id;
81
82 /* Current phase id will be obtained from
83 INV_EGO_REVISION_VALIDATE.Get_Initial_Lifecycle_Phase API */
84
85 /*
86 CURSOR ItemRev_CurrentPhase_cur
87 ( p_lifecycle_id NUMBER
88 ) IS
89 SELECT pe.proj_element_id lifecycle_phase_id
90 FROM pa_proj_elements pe,
91 pa_proj_element_versions pevl,
92 pa_proj_element_versions pevlp
93 WHERE pevl.object_type = 'PA_STRUCTURES' AND
94 pevl.project_id = 0 AND
95 pevl.proj_element_id = p_lifecycle_id AND
96 pevlp.object_type = 'PA_TASKS' AND
97 pevlp.project_id = 0 AND
98 pevlp.parent_structure_version_id = pevl.element_version_id AND
99 pevlp.proj_element_id = pe.proj_element_id AND
100 pevlp.project_id = pe.project_id
101 ORDER BY pevlp.display_sequence;
102 */
103
104 -- ============================================================================
105 -- API Name: Add_Message
106 -- ============================================================================
107
108 PROCEDURE Add_Message
109 (
110 p_application_short_name IN VARCHAR2 := NULL
111 , p_message_name IN VARCHAR2 := NULL
112 , p_message_text IN VARCHAR2 := NULL
113 , p_api_name IN VARCHAR2 := NULL
114 )
115 IS
116 BEGIN
117
118 IF G_Message_API = 'BOM' THEN
119 IF p_message_text IS NULL THEN
120 Error_Handler.Add_Error_Message
121 ( p_message_name => p_message_name
122 , p_application_id => p_application_short_name
123 , p_token_tbl => Error_Handler.G_MISS_TOKEN_TBL
124 , p_message_type => 'E'
125 , p_row_identifier => NULL
126 , p_entity_id => NULL
127 , p_entity_index => NULL
128 , p_table_name => NULL
129 , p_entity_code => 'INV_ITEM_REVISION'
130 );
131 ELSE
132 Error_Handler.Add_Error_Message
133 ( p_message_text => p_message_text
134 , p_message_type => 'E'
135 , p_row_identifier => NULL
136 , p_entity_id => NULL
137 , p_entity_index => NULL
138 , p_table_name => NULL
139 , p_entity_code => 'INV_ITEM_REVISION'
140 );
141 END IF;
142
143 ELSE
144
145 /* If messaging API is FND */
146
147 IF p_message_text IS NULL THEN
148 FND_MESSAGE.Set_Name (p_application_short_name, p_message_name);
149 FND_MSG_PUB.Add;
150 ELSE
151 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
152 THEN
153 FND_MSG_PUB.Add_Exc_Msg
154 ( p_pkg_name => G_PKG_NAME
155 , p_procedure_name => p_api_name
156 , p_error_text => p_message_text
157 );
158 END IF;
159 END IF;
160
161 END IF;
162
163 END;
164
165
166
167 -- ============================================================================
168 -- API Name: Write_Debug_Message
169 -- ============================================================================
170
171 PROCEDURE Write_Debug_Message
172 (
173 p_debug_message IN VARCHAR2
174 )
175 IS
176
177 BEGIN
178
179 IF Error_Handler.Get_Debug = 'Y' THEN
180 Error_Handler.Write_Debug (p_debug_message);
181 END IF;
182
183 END;
184
185 -- ============================================================================
186 -- API Name: Validate_Effectivity_Date
187 -- ============================================================================
188
189 PROCEDURE Validate_Effectivity_Date
190 (
191 p_Item_Revision_rec IN Item_Revision_rec_type
192 , x_return_status OUT NOCOPY VARCHAR2
193 )
194 IS
195 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Effectivity_Date';
196
197 CURSOR Item_Revs_cur
198 IS
199 SELECT revision, effectivity_date
200 FROM mtl_item_revisions_b
201 WHERE
202 inventory_item_id = p_Item_Revision_rec.inventory_item_id
203 AND organization_id = p_Item_Revision_rec.organization_id
204 AND revision_id <> NVL(p_Item_Revision_rec.revision_id,-999999) --3655522 & 7248982:API taking same effectivity date
205 ORDER BY
206 revision, effectivity_date;
207
208 v_revision mtl_item_revisions_b.revision%TYPE;
209 v_effectivity_date mtl_item_revisions_b.effectivity_date%TYPE;
210
211 v_count NUMBER;
212 rev_place_found BOOLEAN;
213
214 BEGIN
215
216 -- Initialize API return status to success
217 x_return_status := FND_API.g_RET_STS_SUCCESS;
218
219 IF ( p_Item_Revision_rec.effectivity_date = FND_API.g_MISS_DATE ) THEN
220 RETURN;
221 END IF;
222
223 v_count := 0;
224 rev_place_found := FALSE;
225
226 -- ----------------------------------------------------------------------------------
227 -- Loop through existing revisions to locate a place of the revision being validated
228 -- ----------------------------------------------------------------------------------
229
230 FOR item_rev_rec IN Item_Revs_cur LOOP
231
232 -- Skip the revision being validated, so that validation algorithm
233 -- remains the same in case of create or update.
234
235 IF ( item_rev_rec.revision <> p_Item_Revision_rec.revision ) THEN
236
237 v_count := v_count + 1;
238
239 -- See if the revision's place within the existing revisions order has been found
240 --2880802: Use lpad space while comparing revision code.
241 --To avoid cases like '9'>'10' which returns true
242
243 /* Reverting the fix done for bug 2880802 for bug3430431
244 This is casuing problems while entering revisions like 'M' after
245 starting revision '00'
246 */
247 rev_place_found := item_rev_rec.revision > p_Item_Revision_rec.revision;
248
249 IF ( rev_place_found ) THEN
250
251 --IF ( item_rev_rec%ROWCOUNT > 1 ) THEN
252 IF ( v_count > 1 ) THEN
253
254 -- -----------------------------------------------------------------------------------------
255 -- Effectivity Date must be between effectivity dates of the previous and the next revision
256 -- -----------------------------------------------------------------------------------------
257
258 IF NOT ( ( p_Item_Revision_rec.effectivity_date > v_effectivity_date )
259 AND ( p_Item_Revision_rec.effectivity_date < item_rev_rec.effectivity_date )
260 )
261 THEN
262 -- inv_UTILITY_PVT.debug_message(' BAD DATE...... ');
263
264 x_return_status := FND_API.g_RET_STS_ERROR;
265 Add_Message ('INV', 'INV_ITM_REV_OUT_EFF_DATE');
266
267 END IF;
268
269 ELSE -- v_count = 1
270
271 -- -----------------------------------------------------
272 -- Effectivity Date must be less than the next revision
273 -- -----------------------------------------------------
274
275 IF ( p_Item_Revision_rec.effectivity_date > item_rev_rec.effectivity_date )
276 THEN
277 x_return_status := FND_API.g_RET_STS_ERROR;
278 Add_Message ('INV', 'INV_ITM_REV_OUT_EFF_DATE');
279 END IF;
280
281 END IF; -- v_count > 1
282
283 -- Exit the Item_Revs_cur loop because revision place has been found
284
285 EXIT;
286
287 END IF; -- rev_place_found
288
289 -- Save record data for the next cycle
290
291 v_revision := item_rev_rec.revision;
292 v_effectivity_date := item_rev_rec.effectivity_date;
293
294 END IF; -- skip the revision being validated
295
296 END LOOP; -- Item_Revs_cur
297
298 -- If the revision place has not been found, and there are other revisions,
299 -- validate against the greatest revision.
300
301 IF ( ( NOT rev_place_found ) AND ( v_count > 0 ) ) THEN
302
303 --2880802: Use lpad space while comparing revision code.
304 --To avoid cases like '9'>'10' which returns true
305 /* Reverting the fix done for bug 2880802 for bug3430431
306 This is casuing problems while entering revisions like 'M' after
307 starting revision '00'
308 */
309
310 IF p_Item_Revision_rec.revision > v_revision THEN
311
312 -- Effectivity Date must be past the date of the greatest revision
313
314 IF ( p_Item_Revision_rec.effectivity_date <= v_effectivity_date ) THEN
315 x_return_status := FND_API.g_RET_STS_ERROR;
316 Add_Message ('INV', 'INV_ITM_REV_OUT_EFF_DATE');
317 END IF;
318
319 ELSE
320 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
321 Add_Message ('INV', 'INV_ITM_INVALID_REVISION_CODE');
322 END IF;
323
324 END IF; -- NOT rev_place_found AND v_count > 0
325
326 EXCEPTION
327
328 WHEN others THEN
329
330 IF ( Item_Revs_cur%ISOPEN ) THEN
331 CLOSE Item_Revs_cur;
332 END IF;
333
334 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
335
336 Add_Message
337 ( p_api_name => l_api_name
338 , p_message_text => 'UNEXP_ERROR : ' || SQLERRM
339 );
340
341
342 END Validate_Effectivity_Date;
343
344 --Added for 5208102
345 PROCEDURE Insert_Revision_UserAttr(p_organization_id IN NUMBER
346 ,p_inventory_item_id IN NUMBER
347 ,p_revision_id IN NUMBER
348 ,p_transaction_type IN VARCHAR2
349 ,p_template_id IN NUMBER) IS
350
351 CURSOR c_get_item_catalog(cp_inventory_item_id NUMBER
352 ,cp_organization_id NUMBER) IS
353 SELECT item_catalog_group_id
354 FROM mtl_system_items_b
355 WHERE organization_id = cp_organization_id
356 AND inventory_item_id = cp_inventory_item_id;
357
358 CURSOR c_parent_catalogs(cp_catalog_group_id NUMBER) IS
359 SELECT item_catalog_group_id
360 ,parent_catalog_group_id
361 FROM mtl_item_catalog_groups_b
362 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
363 START WITH item_catalog_group_id = cp_catalog_group_id;
364
365 l_catalog_group_id mtl_system_items_b.item_catalog_group_id%TYPE;
366 l_parent_catalog VARCHAR2(150):= NULL;
367 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
368 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
369 l_data_level_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
370 l_msg_count NUMBER;
371 l_error_code NUMBER;
372 l_msg_data VARCHAR2(2000);
373 l_return_status VARCHAR2(1);
374 BEGIN
375 OPEN c_get_item_catalog(cp_inventory_item_id => p_inventory_item_id
376 ,cp_organization_id => p_organization_id);
377 FETCH c_get_item_catalog INTO l_catalog_group_id;
378 CLOSE c_get_item_catalog;
379
380 IF l_catalog_group_id IS NOT NULL THEN
381 l_parent_catalog := NULL;
382 BEGIN
383 FOR parent_cur IN c_parent_catalogs(l_catalog_group_id) LOOP
384 IF parent_cur.parent_catalog_group_id IS NOT NULL THEN
385 IF l_parent_catalog IS NULL THEN
386 l_parent_catalog := parent_cur.parent_catalog_group_id;
387 ELSE
388 l_parent_catalog := l_parent_catalog||','||parent_cur.parent_catalog_group_id;
389 END IF;
390 END IF;
391 END LOOP;
392 EXCEPTION
393 WHEN OTHERS THEN
394 NULL;
395 END;
396
397 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
398 EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID',p_inventory_item_id)
399 ,EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID' ,p_organization_id));
400 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
401 EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_CATALOG_GROUP_ID' ,l_catalog_group_id)
402 ,EGO_COL_NAME_VALUE_PAIR_OBJ('RELATED_CLASS_CODE_LIST_1',l_parent_catalog));
403 l_data_level_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
404 EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION_ID', p_revision_id));
405 EGO_ITEM_PUB.Apply_Templ_User_Attrs_To_Item
406 (p_api_version => 1.0
407 ,p_mode => p_transaction_type
408 ,p_item_id => p_inventory_item_id
409 ,p_organization_id => p_organization_id
410 ,p_template_id => p_template_id
411 ,p_object_name => 'EGO_ITEM'
412 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
413 ,p_data_level_name_value_pairs => l_data_level_name_value_pairs
414 ,x_return_status => l_return_status
415 ,x_errorcode => l_error_code
416 ,x_msg_count => l_msg_count
417 ,x_msg_data => l_msg_data);
418 END IF;
419 EXCEPTION
420 WHEN OTHERS THEN
421 NULL;
422 END Insert_Revision_UserAttr;
423
424 --Added for bug 5435229
425 Procedure copy_rev_UDA(p_organization_id IN NUMBER
426 ,p_inventory_item_id IN NUMBER
427 ,p_revision_id IN NUMBER
428 ,p_revision IN VARCHAR2
429 ,p_source_revision_id IN NUMBER DEFAULT NULL) IS
430
431 CURSOR c_get_effective_revision(cp_inventory_item_id NUMBER
432 ,cp_organization_id NUMBER
433 ,cp_revision VARCHAR2) IS
434 SELECT revision_id
435 FROM mtl_item_revisions_b
436 WHERE inventory_item_id = cp_inventory_item_id
437 AND organization_id = cp_organization_id
438 AND revision < cp_revision
439 AND implementation_date IS NOT NULL
440 AND effectivity_date <= sysdate
441 ORDER BY effectivity_date desc;
442
443 CURSOR c_is_source_revision_valid(cp_inventory_item_id NUMBER
444 ,cp_organization_id NUMBER
445 ,cp_source_revision_id NUMBER) IS
446 SELECT revision_id
447 FROM mtl_item_revisions_b
448 WHERE inventory_item_id = cp_inventory_item_id
449 AND organization_id = cp_organization_id
450 AND revision_id = cp_source_revision_id;
451
452 l_source_revision_id mtl_item_revisions_b.revision_id%TYPE;
453 l_return_status VARCHAR2(100);
454 l_error_code NUMBER;
455 l_msg_count NUMBER ;
456 l_msg_data VARCHAR2(100);
457 l_pk_item_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
458 l_pk_item_rev_pairs_src EGO_COL_NAME_VALUE_PAIR_ARRAY;
459 l_pk_item_rev_pairs_dst EGO_COL_NAME_VALUE_PAIR_ARRAY;
460
461 BEGIN
462 IF p_source_revision_id IS NULL THEN
463 -- API User has not passed in any Source Revision Id.
464 -- So get the current effective revision.
465
466 OPEN c_get_effective_revision(cp_inventory_item_id => p_inventory_item_id
467 ,cp_organization_id => p_organization_id
468 ,cp_revision => p_revision);
469 FETCH c_get_effective_revision INTO l_source_revision_id;
470 CLOSE c_get_effective_revision;
471
472 ELSE --p_source_revision_id IS NULL
473 -- API User has passed in a Source Revision Id.
474 -- Check if it is valid for the current item.
475
476 OPEN c_is_source_revision_valid(cp_inventory_item_id => p_inventory_item_id
477 ,cp_organization_id => p_organization_id
478 ,cp_source_revision_id => p_source_revision_id);
479 FETCH c_is_source_revision_valid INTO l_source_revision_id;
480
481 IF ( c_is_source_revision_valid%NOTFOUND ) THEN
482 -- Source REvision Id passed by API user is invalid.
483 -- So get the current effective revision. (or throw error?)
484
485 OPEN c_get_effective_revision(cp_inventory_item_id => p_inventory_item_id
486 ,cp_organization_id => p_organization_id
487 ,cp_revision => p_revision);
488 FETCH c_get_effective_revision INTO l_source_revision_id;
489 CLOSE c_get_effective_revision;
490
491 END IF; --c_is_source_revision_valid%NOTFOUND
492
493 CLOSE c_is_source_revision_valid;
494
495 END IF; --p_source_revision_id IS NULL
496
497 IF l_source_revision_id IS NOT NULL THEN
498 l_pk_item_pairs :=EGO_COL_NAME_VALUE_PAIR_ARRAY(
499 EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', p_inventory_item_id)
500 ,EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', p_organization_id));
501
502 l_pk_item_rev_pairs_src := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'REVISION_ID' , l_source_revision_id));
503 l_pk_item_rev_pairs_dst := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'REVISION_ID' , p_revision_id));
504 EGO_USER_ATTRS_DATA_PVT.Copy_User_Attrs_Data(
505 p_api_version => 1.0
506 ,p_application_id => 431
507 ,p_object_name => 'EGO_ITEM'
508 ,p_old_pk_col_value_pairs => l_pk_item_pairs
509 ,p_old_dtlevel_col_value_pairs => l_pk_item_rev_pairs_src
510 ,p_new_pk_col_value_pairs => l_pk_item_pairs
511 ,p_new_dtlevel_col_value_pairs => l_pk_item_rev_pairs_dst
512 ,x_return_status => l_return_status
513 ,x_errorcode => l_error_code
514 ,x_msg_count => l_msg_count
515 ,x_msg_data => l_msg_data);
516 END IF; --l_source_revision_id IS NOT NULL
517
518 EXCEPTION
519 WHEN OTHERS THEN
520 BEGIN
521 IF (c_get_effective_revision%ISOPEN) THEN
522 CLOSE c_get_effective_revision;
523 END IF; --c_get_effective_revision%ISOPEN
524 IF (c_is_source_revision_valid%ISOPEN) THEN
525 CLOSE c_is_source_revision_valid;
526 END IF; --c_is_source_revision_valid%ISOPEN
527 END;
528
529 END copy_rev_UDA;
530 --5435229 : Defaulting UDA's during revision creation.
531
532 -- ============================================================================
533 -- API Name: Create_Item_Revision
534 --
535 -- Note: Primary key is passed in with the revision record.
536 -- ============================================================================
537
538 PROCEDURE Create_Item_Revision
539 (
540 p_api_version IN NUMBER
541 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
542 , p_commit IN VARCHAR2 := FND_API.G_FALSE
543 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
544 , p_process_control IN VARCHAR2 := NULL
545 , x_return_status OUT NOCOPY VARCHAR2
546 , x_msg_count OUT NOCOPY NUMBER
547 , x_msg_data OUT NOCOPY VARCHAR2
548 , p_Item_Revision_rec IN OUT NOCOPY Item_Revision_rec_type
549 )
550 IS
551
552 CURSOR check_template_name (cp_template_name VARCHAR2) IS
553 SELECT template_id
554 FROM mtl_item_templates
555 WHERE template_name = cp_template_name;
556
557 CURSOR check_template_id (cp_template_id NUMBER) IS
558 SELECT template_id
559 FROM mtl_item_templates
560 WHERE template_id = cp_template_id;
561
562 l_api_name CONSTANT VARCHAR2(30) := 'Create_Item_Revision';
563 l_api_version CONSTANT NUMBER := 1.0;
564 l_return_status VARCHAR2(1);
565 l_exists VARCHAR2(1);
566 l_object_version_number NUMBER;
567 c_object_version_number CONSTANT NUMBER := 1;
568 l_sysdate DATE;
569 l_revision_id NUMBER;
570 l_apply_template BOOLEAN := FALSE;
571 l_template_id mtl_item_templates.template_id%TYPE := NULL;
572 l_message_name VARCHAR2(200);
573
574 BEGIN
575
576 -- Standard start of API savepoint
577 SAVEPOINT Create_Item_Revision_PUB;
578
579 --
580 -- Capture the current date. The Global has value when it is called from
581 -- Procees_Item_Revision
582 --
583 IF G_Sysdate IS NOT NULL THEN
584 l_sysdate := G_Sysdate;
585 ELSE
586 l_sysdate := SYSDATE;
587 END IF;
588
589 -- Check for call compatibility
590 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
591 l_api_name, G_PKG_NAME)
592 THEN
593 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
594 END IF;
595
596 -- Initialize message list
597 IF G_Message_API = 'FND' THEN
598 IF FND_API.To_Boolean (p_init_msg_list) THEN
599 FND_MSG_PUB.Initialize;
600 END IF;
601 END IF;
602
603 -- Define message context
604 -- Mctx.Package_Name := G_PKG_NAME;
605 -- Mctx.Procedure_Name := l_api_name;
606
607 -- Initialize API return status to success
608 x_return_status := FND_API.g_RET_STS_SUCCESS;
609
610 -- Debug Message
611 -- AMS_UTILITY_PVT.debug_message('API: ' || l_api_name || ': start');
612
613 -- code for req, unique and fk checks
614
615 -- ------------------------------------
616 -- Check for missing or NULL PK values
617 -- ------------------------------------
618
619 IF ( p_Item_Revision_rec.inventory_item_id = fnd_api.G_MISS_NUM )
620 OR ( p_Item_Revision_rec.inventory_item_id IS NULL )
621 THEN
622 Add_Message ('INV', 'INV_ITM_MISS_ITEM_ID');
623 END IF;
624
625 IF ( p_Item_Revision_rec.organization_id = fnd_api.G_MISS_NUM )
626 OR ( p_Item_Revision_rec.organization_id IS NULL )
627 THEN
628 Add_Message ('INV', 'INV_ITM_MISS_ORG_ID');
629 END IF;
630
631 IF ( p_Item_Revision_rec.revision = fnd_api.G_MISS_CHAR )
632 OR ( p_Item_Revision_rec.revision IS NULL )
633 THEN
634 Add_Message ('INV', 'INV_ITM_MISS_REVISION_CODE');
635 END IF;
636
637 IF ( x_return_status <> fnd_api.G_RET_STS_SUCCESS ) THEN
638 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
639 END IF;
640
641 -- ------------------------------------------------
642 -- Validate a part of Revision PK - foreign key to
643 -- the composite primary key of the System Item.
644 -- ------------------------------------------------
645
646 --dbms_output.put_line('OPEN org_item_exists_cur ; x_return_status = ' || x_return_status);
647
648 --INV_ITEM_MSG.Debug(Mctx, 'Check if OrgItem Id exists');
649
650 OPEN org_item_exists_cur ( p_Item_Revision_rec.inventory_item_id
651 , p_Item_Revision_rec.organization_id );
652
653 FETCH org_item_exists_cur INTO l_exists;
654
655 IF ( org_item_exists_cur%NOTFOUND ) THEN
656 CLOSE org_item_exists_cur;
657 Add_Message ('INV', 'INV_ITM_INVALID_ORGITEM_ID');
658 RAISE FND_API.g_EXC_ERROR;
659 END IF;
660
661 CLOSE org_item_exists_cur;
662
663 -- ----------------------------------
664 -- Check for duplicate item revision
665 -- ----------------------------------
666
667 --INV_ITEM_MSG.Debug(Mctx, 'Check for duplicate item revision');
668
669 OPEN Item_Revision_Exists_cur ( p_Item_Revision_rec.inventory_item_id
670 , p_Item_Revision_rec.organization_id
671 , p_Item_Revision_rec.revision );
672
673 FETCH Item_Revision_Exists_cur INTO l_object_version_number;
674
675 IF ( Item_Revision_Exists_cur%FOUND ) THEN
676 CLOSE Item_Revision_Exists_cur;
677 Add_Message ('INV', 'INV_ITM_DUPLICATE_REVISION');
678 RAISE FND_API.g_EXC_ERROR;
679 END IF;
680
681 CLOSE Item_Revision_Exists_cur;
682
683 -- --------------------------------------------------------
684 -- Description is a mandatory attribute for a new revision
685 -- Bug: 3055810 Description is Optional comparing with forms ui.
686 -- --------------------------------------------------------
687
688 IF ( p_Item_Revision_rec.description = fnd_api.G_MISS_CHAR ) THEN
689
690 p_Item_Revision_rec.description := NULL;
691 -- x_return_status := FND_API.g_RET_STS_ERROR;
692 -- Add_Message ('INV', 'INV_ITM_REV_MISS_DESCRIPTION');
693 END IF;
694 /* Bug:3055810
695 IF ( p_Item_Revision_rec.description IS NULL ) THEN
696 x_return_status := FND_API.g_RET_STS_ERROR;
697 Add_Message ('INV', 'INV_ITM_REV_NULL_DESCRIPTION');
698 END IF;
699 */
700 -- -------------------------------------------------------------
701 -- Effectivity Date is a mandatory attribute for a new revision
702 -- -------------------------------------------------------------
703
704 IF ( p_Item_Revision_rec.effectivity_date = FND_API.g_MISS_DATE ) THEN
705 x_return_status := FND_API.g_RET_STS_ERROR;
706 Add_Message ('INV', 'INV_ITM_REV_MISS_EFF_DATE');
707 END IF;
708
709 -- New revision Effectivity Date value cannot be NULL
710
711 IF ( p_Item_Revision_rec.effectivity_date IS NULL ) THEN
712 x_return_status := FND_API.g_RET_STS_ERROR;
713 Add_Message ('INV', 'INV_ITM_REV_NULL_EFF_DATE');
714 END IF;
715
716 -- If the effectivity date is current date, then it is
717 -- current date + current time
718 -- This Validation will be skipped if the Change Notice is present
719 -- Check added for bug 3817613 by absinha
720
721 IF(p_Item_Revision_rec.change_notice IS NULL) THEN
722 IF ( trunc(p_Item_Revision_rec.effectivity_date) = trunc(l_sysdate) ) THEN
723 IF(p_Item_Revision_rec.effectivity_date < l_sysdate) THEN
724 p_Item_Revision_rec.effectivity_date := l_sysdate;
725 END IF;
726 END IF;
727
728 -- New revision Effectivity Date must be past the current date
729
730 IF ( p_Item_Revision_rec.effectivity_date < l_sysdate ) THEN
731 x_return_status := FND_API.g_RET_STS_ERROR;
732 Add_Message ('INV', 'INV_ITM_REV_OLD_EFF_DATE');
733 END IF;
734 END IF;
735
736 IF ( x_return_status <> FND_API.g_RET_STS_SUCCESS ) THEN
737 RAISE fnd_api.G_EXC_ERROR;
738 END IF;
739
740 -- -----------------------------------------------------
741 -- Validate all the other Effectivity Date dependencies
742 -- -----------------------------------------------------
743
744 --INV_ITEM_MSG.Debug(Mctx, 'Validate Effectivity Date');
745
746 Validate_Effectivity_Date
747 (
748 p_Item_Revision_rec => p_Item_Revision_rec
749 , x_return_status => l_return_status
750 );
751
752 IF ( l_return_status = FND_API.g_RET_STS_ERROR ) THEN
753 RAISE fnd_api.G_EXC_ERROR;
754 ELSIF ( l_return_status = FND_API.g_RET_STS_UNEXP_ERROR ) THEN
755 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
756 END IF;
757
758 -- ---------------------------
759 -- Default missing attributes
760 -- ---------------------------
761
762 IF ( p_Item_Revision_rec.change_notice = FND_API.g_MISS_CHAR ) THEN
763 p_Item_Revision_rec.change_notice := NULL;
764 END IF;
765
766 IF ( p_Item_Revision_rec.ecn_initiation_date = FND_API.g_MISS_DATE ) THEN
767 p_Item_Revision_rec.ecn_initiation_date := NULL;
768 END IF;
769
770 /*
771 IF ( p_Item_Revision_rec.implementation_date = FND_API.g_MISS_DATE ) OR
772 p_Item_Revision_rec.implementation_date IS NULL THEN
773 p_Item_Revision_rec.implementation_date := p_Item_Revision_rec.effectivity_date;
774 END IF;
775 */
776
777 -- Implementation date is always the effectivity date
778 IF p_Item_Revision_rec.change_notice IS NOT NULL THEN
779 p_Item_Revision_rec.implementation_date := NULL;
780 ELSE
781 p_Item_Revision_rec.implementation_date := p_Item_Revision_rec.effectivity_date;
782 END IF;
783
784
785 IF ( p_Item_Revision_rec.revised_item_sequence_id = FND_API.g_MISS_NUM ) THEN
786 p_Item_Revision_rec.revised_item_sequence_id := NULL;
787 END IF;
788
789 --
790 -- Revision label cannot be null. If the user did not pass any value or the
791 -- value is missing, then revision_label will be same as revision
792 --
793 IF ( p_Item_Revision_rec.revision_label = FND_API.g_MISS_CHAR OR
794 p_Item_Revision_rec.revision_label IS NULL ) THEN
795 p_Item_Revision_rec.revision_label := p_Item_Revision_rec.revision;
796 END IF;
797
798 IF ( p_Item_Revision_rec.revision_reason = FND_API.g_MISS_CHAR ) THEN
799 p_Item_Revision_rec.revision_reason := NULL;
800 END IF;
801
802 IF ( p_Item_Revision_rec.lifecycle_id = FND_API.g_MISS_NUM ) THEN
803 p_Item_Revision_rec.lifecycle_id := NULL;
804 p_Item_Revision_rec.current_phase_id := NULL;
805 END IF;
806
807 IF ( p_Item_Revision_rec.current_phase_id = FND_API.g_MISS_NUM ) THEN
808 p_Item_Revision_rec.current_phase_id := NULL;
809 END IF;
810
811 --
812 -- Derive the Current Phase Id when it is not passed by the user
813 --
814 IF p_Item_Revision_rec.lifecycle_id IS NOT NULL AND
815 p_Item_Revision_rec.current_phase_id IS NULL THEN
816
817 p_Item_Revision_rec.current_phase_id :=
818 INV_EGO_REVISION_VALIDATE.Get_Initial_Lifecycle_Phase (p_Item_Revision_rec.lifecycle_id);
819
820 IF ( p_Item_Revision_rec.current_phase_id = 0 ) THEN
821 Add_Message ('INV', 'INV_REV_LIFECYCLE_INVALID');
822 RAISE FND_API.g_EXC_ERROR;
823 END IF;
824
825 END IF;
826
827 -- Start :5208102: Supporting template for UDA's at revisions
828 IF p_Item_Revision_rec.template_id = FND_API.g_MISS_NUM THEN
829 p_Item_Revision_rec.template_id := NULL;
830 END IF;
831 IF p_Item_Revision_rec.template_name = FND_API.g_MISS_CHAR THEN
832 p_Item_Revision_rec.template_name := NULL;
833 END IF;
834
835 IF (p_Item_Revision_rec.template_id IS NOT NULL)
836 OR (p_Item_Revision_rec.template_name IS NOT NULL)
837 THEN
838 l_message_name := NULL;
839 --Validate template name
840 IF p_Item_Revision_rec.template_id IS NULL AND p_Item_Revision_rec.template_name IS NOT NULL THEN
841 OPEN check_template_name(p_Item_Revision_rec.template_name);
842 FETCH check_template_name INTO l_template_id;
843 CLOSE check_template_name;
844
845 IF l_template_id IS NULL THEN
846 l_message_name := 'INV_TEMPLATE_ERROR';
847 END IF;
848
849 --Validate template id
850 ELSIF p_Item_Revision_rec.template_id IS NOT NULL THEN
851 OPEN check_template_id(p_Item_Revision_rec.template_id);
852 FETCH check_template_id INTO l_template_id;
853 CLOSE check_template_id;
854
855 IF l_template_id IS NULL THEN
856 l_message_name := 'INV_TEMPLATE_ERROR';
857 END IF;
858 END IF;
859
860 IF l_message_name IS NOT NULL THEN
861 Add_Message ('INV', l_message_name);
862 RAISE FND_API.g_EXC_ERROR;
863 ELSE
864 l_apply_template := TRUE;
865 END IF;
866 END IF;
867 -- End :5208102: Supporting template for UDA's at revisions
868
869 --Supporting revision id during revision create.
870 IF ( p_Item_Revision_rec.revision_id = FND_API.g_MISS_NUM ) THEN
871 p_Item_Revision_rec.revision_id := NULL;
872 END IF;
873
874 IF p_Item_Revision_rec.revision_id IS NOT NULL THEN
875 BEGIN
876 SELECT mtl_item_revisions_b_s.CURRVAL
877 INTO l_revision_id FROM DUAL;
878 IF p_Item_Revision_rec.revision_id > l_revision_id THEN
879 Add_Message ('INV', 'INV_INVALID_REVISION_ID');
880 RAISE FND_API.g_EXC_ERROR;
881 END IF;
882 EXCEPTION
883 WHEN OTHERS THEN
884 Add_Message ('INV', 'INV_INVALID_REVISION_ID');
885 RAISE FND_API.g_EXC_ERROR;
886 END;
887 END IF;
888
889 IF p_Item_Revision_rec.revision_id IS NULL THEN
890
891 SELECT mtl_item_revisions_b_s.NEXTVAL
892 INTO p_Item_Revision_rec.revision_id
893 FROM DUAL;
894
895 END IF;
896
897 --INV_ITEM_MSG.Debug(Mctx, 'INSERT INTO mtl_item_revisions table');
898
899 INSERT INTO mtl_item_revisions_b
900 (
901 inventory_item_id
902 , organization_id
903 , revision_id
904 , revision
905 , change_notice
906 , ecn_initiation_date
907 , implementation_date
908 , effectivity_date
909 , revised_item_sequence_id
910 , attribute_category
911 , attribute1
912 , attribute2
913 , attribute3
914 , attribute4
915 , attribute5
916 , attribute6
917 , attribute7
918 , attribute8
919 , attribute9
920 , attribute10
921 , attribute11
922 , attribute12
923 , attribute13
924 , attribute14
925 , attribute15
926 , creation_date
927 , created_by
928 , last_update_date
929 , last_updated_by
930 , last_update_login
931 , request_id
932 , program_application_id
933 , program_id
934 , program_update_date
935 , object_version_number
936 , revision_label
937 , revision_reason
938 , lifecycle_id
939 , current_phase_id
940 )
941 VALUES
942 (
943 p_Item_Revision_rec.inventory_item_id
944 , p_Item_Revision_rec.organization_id
945 , p_Item_Revision_rec.revision_id
946 , p_Item_Revision_rec.revision
947 , p_Item_Revision_rec.change_notice
948 , p_Item_Revision_rec.ecn_initiation_date
949 , p_Item_Revision_rec.implementation_date
950 , p_Item_Revision_rec.effectivity_date
951 , p_Item_Revision_rec.revised_item_sequence_id
952 , DECODE(p_Item_Revision_rec.attribute_category, G_Miss_Char, NULL, p_Item_Revision_rec.attribute_category )
953 , DECODE(p_Item_Revision_rec.attribute1, G_Miss_Char, NULL, p_Item_Revision_rec.attribute1 )
954 , DECODE(p_Item_Revision_rec.attribute2, G_Miss_Char, NULL, p_Item_Revision_rec.attribute2 )
955 , DECODE(p_Item_Revision_rec.attribute3, G_Miss_Char, NULL, p_Item_Revision_rec.attribute3 )
956 , DECODE(p_Item_Revision_rec.attribute4, G_Miss_Char, NULL, p_Item_Revision_rec.attribute4 )
957 , DECODE(p_Item_Revision_rec.attribute5, G_Miss_Char, NULL, p_Item_Revision_rec.attribute5 )
958 , DECODE(p_Item_Revision_rec.attribute6, G_Miss_Char, NULL, p_Item_Revision_rec.attribute6 )
959 , DECODE(p_Item_Revision_rec.attribute7, G_Miss_Char, NULL, p_Item_Revision_rec.attribute7 )
960 , DECODE(p_Item_Revision_rec.attribute8, G_Miss_Char, NULL, p_Item_Revision_rec.attribute8 )
961 , DECODE(p_Item_Revision_rec.attribute9, G_Miss_Char, NULL, p_Item_Revision_rec.attribute9 )
962 , DECODE(p_Item_Revision_rec.attribute10, G_Miss_Char, NULL, p_Item_Revision_rec.attribute10 )
963 , DECODE(p_Item_Revision_rec.attribute11, G_Miss_Char, NULL, p_Item_Revision_rec.attribute11 )
964 , DECODE(p_Item_Revision_rec.attribute12, G_Miss_Char, NULL, p_Item_Revision_rec.attribute12 )
965 , DECODE(p_Item_Revision_rec.attribute13, G_Miss_Char, NULL, p_Item_Revision_rec.attribute13 )
966 , DECODE(p_Item_Revision_rec.attribute14, G_Miss_Char, NULL, p_Item_Revision_rec.attribute14 )
967 , DECODE(p_Item_Revision_rec.attribute15, G_Miss_Char, NULL, p_Item_Revision_rec.attribute15 )
968 , l_sysdate
969 , FND_GLOBAL.user_id
970 , l_sysdate
971 , FND_GLOBAL.user_id
972 , FND_GLOBAL.conc_login_id
973 , DECODE(p_Item_Revision_rec.request_id, G_Miss_Num, NULL, p_Item_Revision_rec.request_id )
974 , DECODE(p_Item_Revision_rec.program_application_id, G_Miss_Num, NULL, p_Item_Revision_rec.program_application_id )
975 , DECODE(p_Item_Revision_rec.program_id, G_Miss_Num, NULL, p_Item_Revision_rec.program_id )
976 , DECODE(p_Item_Revision_rec.program_update_date, G_Miss_Date, NULL, p_Item_Revision_rec.program_update_date )
977 , c_object_version_number
978 , p_Item_Revision_rec.revision_label
979 , p_Item_Revision_rec.revision_reason
980 , p_Item_Revision_rec.lifecycle_id
981 , p_Item_Revision_rec.current_phase_id
982 ) RETURNING revision_id, object_version_number INTO G_revision_id, G_object_version_number;
983
984 --dbms_output.put_line('done INSERTing INTO mtl_item_revisions table; x_return_status = ' || x_return_status);
985
986 --
987 -- IF Create revision API is called directly (not through Process_Item_Revision),
988 -- then get the language code
989 --
990
991 IF G_language_code IS NULL THEN
992 SELECT userenv('LANG') INTO G_language_code FROM dual;
993 END IF;
994
995 -- Insert into TL table
996
997 INSERT INTO mtl_item_revisions_TL
998 ( Inventory_Item_Id
999 , Organization_Id
1000 , Revision_id
1001 , Language
1002 , Source_Lang
1003 , Created_By
1004 , Creation_Date
1005 , Last_Updated_By
1006 , Last_Update_Date
1007 , Last_Update_Login
1008 , Description
1009 )
1010 SELECT p_Item_Revision_rec.inventory_item_id
1011 , p_Item_Revision_rec.organization_id
1012 , G_revision_id
1013 , lang.language_code
1014 , G_language_code
1015 , G_User_Id
1016 , l_sysdate
1017 , G_User_Id
1018 , l_sysdate
1019 , G_Login_Id
1020 , p_Item_Revision_rec.description
1021 FROM FND_LANGUAGES lang
1022 WHERE lang.installed_flag in ('I', 'B');
1023
1024 --
1025 -- Initiate the revision entry in pending item status table which will maintain
1026 -- the period for each lifecycle phase
1027 --
1028
1029 IF p_Item_Revision_rec.lifecycle_id IS NOT NULL THEN
1030
1031 INSERT INTO mtl_pending_item_status
1032 ( Inventory_Item_Id
1033 , Organization_Id
1034 , Status_code
1035 , Effective_date
1036 , Implemented_date
1037 , Pending_flag
1038 , Revision_Id
1039 , lifecycle_id
1040 , phase_id
1041 , Created_By
1042 , Creation_Date
1043 , Last_Updated_By
1044 , Last_Update_Date
1045 , Last_Update_Login
1046 )
1047 VALUES
1048 ( p_Item_Revision_rec.Inventory_Item_Id
1049 , p_Item_Revision_rec.Organization_Id
1050 , NULL
1051 , l_sysdate
1052 , l_sysdate
1053 , 'N'
1054 , G_revision_id
1055 , p_Item_Revision_rec.lifecycle_id
1056 , p_Item_Revision_rec.current_phase_id
1057 , G_User_Id
1058 , l_sysdate
1059 , G_User_Id
1060 , l_sysdate
1061 , G_Login_Id
1062 );
1063 END IF;
1064
1065 IF (INSTR(NVL(p_process_control,'PLM_UI:N'),'PLM_UI:Y') = 0 ) THEN
1066 -- Start 5208102: Supporting template for UDA's at revisions
1067
1068
1069 IF l_apply_template THEN
1070 Insert_Revision_UserAttr(p_organization_id => p_Item_Revision_rec.Organization_Id
1071 ,p_inventory_item_id => p_Item_Revision_rec.inventory_item_id
1072 ,p_revision_id => G_revision_id
1073 ,p_transaction_type => 'CREATE'
1074 ,p_template_id => l_template_id);
1075 END IF;
1076 -- End 5208102: Supporting template for UDA's at revisions
1077
1078 -- Bug 5435229
1079 -- Copy revision UDA
1080 copy_rev_UDA(p_organization_id => p_Item_Revision_rec.organization_id
1081 ,p_inventory_item_id => p_Item_Revision_rec.inventory_item_id
1082 ,p_revision_id => p_Item_Revision_rec.revision_id
1083 ,p_revision => p_Item_Revision_rec.revision);
1084
1085 -- Bug 5525054
1086 BEGIN
1087 INV_ITEM_EVENTS_PVT.Raise_Events(
1088 p_event_name => 'EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT'
1089 ,p_dml_type => 'CREATE'
1090 ,p_inventory_item_id => p_Item_Revision_rec.Inventory_Item_Id
1091 ,p_organization_id => p_Item_Revision_rec.Organization_Id
1092 ,p_revision_id => p_Item_Revision_rec.revision_id);
1093
1094 EXCEPTION
1095 WHEN OTHERS THEN
1096 NULL;
1097 END;
1098
1099 END IF;
1100
1101 --Commented out for bug 5525054
1102 /* R12: Business Event Enhancement:
1103 Raise Event if Revision got Created successfully *//*
1104 BEGIN
1105 INV_ITEM_EVENTS_PVT.Raise_Events(
1106 p_event_name => 'EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT'
1107 ,p_dml_type => 'CREATE'
1108 ,p_inventory_item_id => p_Item_Revision_rec.Inventory_Item_Id
1109 ,p_organization_id => p_Item_Revision_rec.Organization_Id
1110 ,p_revision_id => p_Item_Revision_rec.revision_id);
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 NULL;
1114 END;
1115 */ /* R12: Business Event Enhancement:
1116 Raise Event if Revision got Created successfully */
1117
1118 -- Standard check of p_commit
1119 IF FND_API.To_Boolean (p_commit) THEN
1120 --INV_ITEM_MSG.Debug(Mctx, 'before COMMIT WORK');
1121 COMMIT WORK;
1122 END IF;
1123
1124 -- Standard call to get message count and if count is 1, get message info.
1125
1126 IF G_Message_API = 'BOM' THEN
1127 x_msg_count := Error_Handler.Get_Message_Count;
1128 ELSE
1129 FND_MSG_PUB.Count_And_Get
1130 ( p_count => x_msg_count
1131 , p_data => x_msg_data
1132 );
1133
1134 /*Bug 6853558 Added to get the message if count is > 1 */
1135 IF( x_msg_count > 1 ) THEN
1136 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE );
1137 END IF;
1138 END IF;
1139
1140 EXCEPTION
1141
1142 WHEN FND_API.g_EXC_ERROR THEN
1143
1144 ROLLBACK TO Create_Item_Revision_PUB;
1145 x_return_status := FND_API.g_RET_STS_ERROR;
1146
1147 IF G_Message_API = 'BOM' THEN
1148 x_msg_count := Error_Handler.Get_Message_Count;
1149 ELSE
1150 FND_MSG_PUB.Count_And_Get
1151 ( p_count => x_msg_count
1152 , p_data => x_msg_data
1153 );
1154 /*Bug 6853558 Added to get the message if count is > 1 */
1155 IF( x_msg_count > 1 ) THEN
1156 x_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE );
1157 END IF;
1158 END IF;
1159
1160 WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
1161
1162 ROLLBACK TO Create_Item_Revision_PUB;
1163 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
1164
1165 IF G_Message_API = 'BOM' THEN
1166 x_msg_count := Error_Handler.Get_Message_Count;
1167 ELSE
1168 FND_MSG_PUB.Count_And_Get
1169 ( p_count => x_msg_count
1170 , p_data => x_msg_data
1171 );
1172 /*Bug 6853558 Added to get the message if count is > 1 */
1173 IF( x_msg_count > 1 ) THEN
1174 x_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
1175 END IF;
1176 END IF;
1177
1178 WHEN others THEN
1179
1180 ROLLBACK TO Create_Item_Revision_PUB;
1181 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
1182
1183 Add_Message
1184 ( p_api_name => l_api_name
1185 , p_message_text => 'UNEXP_ERROR : ' || SQLERRM
1186 );
1187
1188 IF G_Message_API = 'BOM' THEN
1189 x_msg_count := Error_Handler.Get_Message_Count;
1190 ELSE
1191 FND_MSG_PUB.Count_And_Get
1192 ( p_count => x_msg_count
1193 , p_data => x_msg_data
1194 );
1195 /*Bug 6853558 Added to get the message if count is > 1 */
1196 IF( x_msg_count > 1 ) THEN
1197 x_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
1198 END IF;
1199 END IF;
1200
1201 END Create_Item_Revision;
1202
1203
1204 -- ============================================================================
1205 -- API Name: Update_Item_Revision
1206 -- ============================================================================
1207
1208 PROCEDURE Update_Item_Revision
1209 (
1210 p_api_version IN NUMBER
1211 , p_init_msg_list IN VARCHAR2 := FND_API.g_FALSE
1212 , p_commit IN VARCHAR2 := FND_API.g_FALSE
1213 , p_validation_level IN NUMBER := FND_API.g_VALID_LEVEL_FULL
1214 , p_process_control IN VARCHAR2 := NULL
1215 , x_return_status OUT NOCOPY VARCHAR2
1216 , x_msg_count OUT NOCOPY NUMBER
1217 , x_msg_data OUT NOCOPY VARCHAR2
1218 , p_Item_Revision_rec IN OUT NOCOPY Item_Revision_rec_type
1219 )
1220 IS
1221 CURSOR check_template_name (cp_template_name VARCHAR2) IS
1222 SELECT template_id
1223 FROM mtl_item_templates
1224 WHERE template_name = cp_template_name;
1225
1226 CURSOR check_template_id (cp_template_id NUMBER) IS
1227 SELECT template_id
1228 FROM mtl_item_templates
1229 WHERE template_id = cp_template_id;
1230
1231 CURSOR ItemRev_oldvalues_cur(p_inventory_item_id NUMBER
1232 ,p_organization_id NUMBER
1233 ,p_revision VARCHAR2) IS
1234 SELECT effectivity_date
1235 ,implementation_date
1236 ,lifecycle_id
1237 ,current_phase_id
1238 FROM mtl_item_revisions_b
1239 WHERE inventory_item_id = p_inventory_item_id
1240 AND organization_id = p_organization_id
1241 AND revision = p_revision;
1242
1243 l_api_name CONSTANT VARCHAR2(30) := 'Update_Item_Revision';
1244 l_api_version CONSTANT NUMBER := 1.0;
1245 l_return_status VARCHAR2(1) := NULL;
1246 l_exists VARCHAR2(1);
1247 l_object_version_number NUMBER;
1248 l_orig_effectivity_date DATE;
1249 l_orig_implementation_date DATE;
1250 l_orig_lifecycle_id NUMBER;
1251 l_orig_current_phase_id NUMBER;
1252 l_lifecycle_id NUMBER;
1253 l_current_phase_id NUMBER;
1254 l_sysdate DATE;
1255 l_msg_count NUMBER;
1256 l_msg_text VARCHAR2(4000);
1257 l_apply_template BOOLEAN := FALSE;
1258 l_template_id mtl_item_templates.template_id%TYPE := NULL;
1259 l_message_name VARCHAR2(200);
1260
1261 BEGIN
1262
1263
1264 -- Standard start of API savepoint
1265 SAVEPOINT Update_Item_Revision_PUB;
1266
1267 --
1268 -- Capture the current date. The Global has value when it is called from
1269 -- Procees_Item_Revision
1270 --
1271 IF G_Sysdate IS NOT NULL THEN
1272 l_sysdate := G_Sysdate;
1273 ELSE
1274 l_sysdate := SYSDATE;
1275 END IF;
1276
1277 -- Check for call compatibility
1278 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1279 l_api_name, G_PKG_NAME)
1280 THEN
1281 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
1282 END IF;
1283
1284 -- Initialize message list
1285 IF G_Message_API = 'FND' THEN
1286 IF FND_API.To_Boolean (p_init_msg_list) THEN
1287 FND_MSG_PUB.Initialize;
1288 END IF;
1289 END IF;
1290
1291 -- Define message context
1292 -- Mctx.Package_Name := G_PKG_NAME;
1293 -- Mctx.Procedure_Name := l_api_name;
1294
1295 -- Initialize API return status to success
1296 x_return_status := FND_API.g_RET_STS_SUCCESS;
1297
1298 -- code for req, unique and fk checks
1299
1300 -- ------------------------------------
1301 -- Check for missing or NULL PK values
1302 -- ------------------------------------
1303
1304 IF ( p_Item_Revision_rec.inventory_item_id = FND_API.g_MISS_NUM )
1305 OR ( p_Item_Revision_rec.inventory_item_id IS NULL )
1306 THEN
1307 Add_Message ('INV', 'INV_ITM_MISS_ITEM_ID');
1308 END IF;
1309
1310 IF ( p_Item_Revision_rec.organization_id = FND_API.g_MISS_NUM )
1311 OR ( p_Item_Revision_rec.organization_id IS NULL )
1312 THEN
1313 Add_Message ('INV', 'INV_ITM_MISS_ORG_ID');
1314 END IF;
1315
1316 IF ( p_Item_Revision_rec.revision = FND_API.g_MISS_CHAR )
1317 OR ( p_Item_Revision_rec.revision IS NULL )
1318 THEN
1319 Add_Message ('INV', 'INV_ITM_MISS_REVISION_CODE');
1320 END IF;
1321
1322 -- Return with errors accumulated so far
1323 IF ( x_return_status <> FND_API.g_RET_STS_SUCCESS ) THEN
1324 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
1325 END IF;
1326
1327 -- ------------------------------------------------
1328 -- Validate a part of Revision PK - foreign key to
1329 -- the composite primary key of the System Item.
1330 -- ------------------------------------------------
1331
1332 --dbms_output.put_line('OPEN org_item_exists_cur ; x_return_status = ' || x_return_status);
1333
1334 --INV_ITEM_MSG.Debug(Mctx, 'Check if OrgItem Id exists');
1335
1336 OPEN org_item_exists_cur ( p_Item_Revision_rec.inventory_item_id
1337 , p_Item_Revision_rec.organization_id );
1338
1339 FETCH org_item_exists_cur INTO l_exists;
1340
1341 IF ( org_item_exists_cur%NOTFOUND ) THEN
1342 CLOSE org_item_exists_cur;
1343 Add_Message ('INV', 'INV_ITM_INVALID_ORGITEM_ID');
1344 RAISE FND_API.g_EXC_ERROR;
1345 END IF;
1346
1347 CLOSE org_item_exists_cur;
1348
1349 -- ------------------------------
1350 -- Check if item revision exists
1351 -- ------------------------------
1352
1353 --INV_ITEM_MSG.Debug(Mctx, 'Check if item revision exists');
1354 IF p_Item_Revision_rec.revision_id IS NOT NULL THEN
1355 OPEN Item_Revision_Id_Exists_cur ( p_Item_Revision_rec.inventory_item_id
1356 , p_Item_Revision_rec.organization_id
1357 , p_Item_Revision_rec.revision_id );
1358 FETCH Item_Revision_Id_Exists_cur INTO l_object_version_number;
1359 IF ( Item_Revision_Id_Exists_cur%NOTFOUND ) THEN
1360 CLOSE Item_Revision_Id_Exists_cur;
1361 Add_Message ('INV', 'INV_ITM_INVALID_REVISION_CODE');
1362 RAISE FND_API.g_EXC_ERROR;
1363 END IF;
1364 CLOSE Item_Revision_Id_Exists_cur;
1365
1366 --3655522 begin
1367 OPEN Upd_Item_Rev_Exists_cur (p_Item_Revision_rec.inventory_item_id
1368 , p_Item_Revision_rec.organization_id
1369 , p_Item_Revision_rec.revision
1370 , p_Item_Revision_rec.revision_id);
1371 FETCH Upd_Item_Rev_Exists_cur INTO l_object_version_number;
1372 IF ( Upd_Item_Rev_Exists_cur%FOUND ) THEN
1373 CLOSE Upd_Item_Rev_Exists_cur;
1374 Add_Message ('INV', 'INV_ITM_DUPLICATE_REVISION');
1375 RAISE FND_API.g_EXC_ERROR;
1376 END IF;
1377 CLOSE Upd_Item_Rev_Exists_cur;
1378 --3655522 end
1379
1380 ELSE
1381 OPEN Item_Revision_Exists_cur ( p_Item_Revision_rec.inventory_item_id
1382 , p_Item_Revision_rec.organization_id
1383 , p_Item_Revision_rec.revision );
1384
1385 FETCH Item_Revision_Exists_cur INTO l_object_version_number;
1386 IF ( Item_Revision_Exists_cur%NOTFOUND ) THEN
1387 CLOSE Item_Revision_Exists_cur;
1388 Add_Message ('INV', 'INV_ITM_INVALID_REVISION_CODE');
1389 RAISE FND_API.g_EXC_ERROR;
1390 END IF;
1391 CLOSE Item_Revision_Exists_cur;
1392 END IF;
1393
1394 -- --------------------------------------
1395 -- Description cannot be updated to NULL
1396 -- Bug: 3055810 Description is optional.
1397 -- --------------------------------------
1398 /*
1399 IF ( p_Item_Revision_rec.description = FND_API.g_MISS_CHAR ) THEN
1400 x_return_status := FND_API.g_RET_STS_ERROR;
1401 Add_Message ('INV', 'INV_ITM_REV_NULL_DESCRIPTION');
1402 END IF;
1403 */
1404 -- -------------------------------------------
1405 -- Effectivity Date cannot be updated to NULL
1406 -- -------------------------------------------
1407
1408 --Dbms_output.put_line('UPDATE: Checking for missing effectivity date');
1409
1410 IF ( p_Item_Revision_rec.effectivity_date = FND_API.g_MISS_DATE ) THEN
1411 x_return_status := FND_API.g_RET_STS_ERROR;
1412 Add_Message ('INV', 'INV_ITM_REV_NULL_EFF_DATE');
1413 END IF;
1414
1415 IF x_return_status <> FND_API.g_RET_STS_SUCCESS THEN
1416 RAISE FND_API.g_EXC_ERROR;
1417 END IF;
1418
1419 -- ------------------------------------------------------------------------------
1420 -- Revision is not updateable when Effectivity Date is prior to the current date
1421 -- ------------------------------------------------------------------------------
1422
1423 --Dbms_output.put_line('UPDATE: Checking for null effectivity date');
1424
1425 -- Get the original effectivity date and compare with the user input
1426
1427 OPEN ItemRev_Oldvalues_cur ( p_Item_Revision_rec.inventory_item_id
1428 , p_Item_Revision_rec.organization_id
1429 , p_Item_Revision_rec.revision );
1430 FETCH ItemRev_Oldvalues_cur INTO l_orig_effectivity_date, l_orig_implementation_date, l_orig_lifecycle_id,
1431 l_orig_current_phase_id;
1432 CLOSE ItemRev_Oldvalues_cur;
1433
1434 -- If the user has passed in the effectivity date
1435
1436 IF ( p_Item_Revision_rec.effectivity_date IS NOT NULL ) THEN
1437
1438 -- And it is different from the revision's old effectivity date
1439
1440 IF (p_Item_Revision_rec.effectivity_date <> l_orig_effectivity_date) THEN
1441
1442 -- Assign current date + time if the user is passing the current date
1443 -- (may be he is moving the effectivity from future to current)
1444 -- Adding the nested IF loop for Bug 4162240 - Anmurali
1445
1446 IF trunc(p_Item_Revision_rec.effectivity_date) = trunc(l_sysdate) THEN
1447 IF(p_Item_Revision_rec.effectivity_date < l_sysdate) THEN
1448 p_Item_Revision_rec.effectivity_date := l_sysdate;
1449 END IF;
1450 END IF;
1451
1452 -- Effectivity cannnot be changed if the revision is current/past revision
1453 -- and also the effectivity cannot be moved to past
1454 /*Bug: 5037166 Modified the clause below to prevent Revision being updated
1455 with Effectivity date prior to SYSDATE*/
1456 IF (( l_orig_effectivity_date < l_sysdate AND
1457 l_orig_implementation_date IS NOT NULL )
1458 OR p_Item_Revision_rec.effectivity_date < l_sysdate ) THEN
1459 --3655522 if rev is not implemented, then we allow changing effectivity date
1460 x_return_status := FND_API.g_RET_STS_ERROR;
1461 Add_Message ('INV','INV_ITM_REV_EFF_DATE_NON_UPD');
1462 RAISE FND_API.g_EXC_ERROR;
1463 END IF;
1464
1465 END IF;
1466
1467 END IF;
1468
1469 -- -----------------------------------------------------
1470 -- Validate all the other Effectivity Date dependencies
1471 -- -----------------------------------------------------
1472
1473 --INV_ITEM_MSG.Debug(Mctx, 'Validate Effectivity Date');
1474
1475 IF ( p_Item_Revision_rec.effectivity_date IS NOT NULL ) --AND
1476 --( p_Item_Revision_rec.effectivity_date <> l_orig_effectivity_date )
1477 --3655522 we support update of rev code through ECO. So we need to
1478 --validate if rev code conforms to effectivity date rules
1479 THEN
1480
1481 Validate_Effectivity_Date
1482 (
1483 p_Item_Revision_rec => p_Item_Revision_rec
1484 , x_return_status => l_return_status
1485 );
1486
1487 IF ( l_return_status = FND_API.g_RET_STS_ERROR ) THEN
1488 RAISE FND_API.g_EXC_ERROR;
1489 ELSIF ( l_return_status = FND_API.g_RET_STS_UNEXP_ERROR ) THEN
1490 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
1491 END IF;
1492
1493 END IF;
1494
1495 --dbms_output.put_line('UPDATE mtl_item_revisions table; x_return_status = ' || x_return_status);
1496 --
1497 -- Cannnot NULL OUT or CHANGE an existing lifecycle for a revision
1498 --
1499 -- 3557001
1500 -- user can null out lifecycle.
1501 -- IF l_orig_lifecycle_id IS NOT NULL AND
1502 -- nvl(p_Item_Revision_rec.lifecycle_id,l_orig_lifecycle_id) <> l_orig_lifecycle_id THEN
1503 --
1504 -- Add_Message ('INV', 'INV_CANNOT_CHANGE_LIFECYCLE');
1505 -- RAISE FND_API.g_EXC_ERROR;
1506 -- END IF;
1507 --
1508 --
1509 -- Now either the user tries to update an existing life cycle (can update only the current phase)
1510 -- or assign a new one to the revision or leave those as it is
1511 --
1512 l_lifecycle_id := p_Item_Revision_rec.lifecycle_id;
1513 l_current_phase_id := p_Item_Revision_rec.current_phase_id;
1514 --
1515 -- When the lifecycle id is a MISSING value, then assign NULL to both lifecycle and
1516 -- current phase
1517 --
1518 IF l_lifecycle_id = FND_API.g_MISS_NUM THEN
1519
1520 l_lifecycle_id := NULL;
1521 l_current_phase_id := NULL;
1522
1523 --
1524 -- If the lifecycle is NULL, then default it from database
1525 --
1526 ELSIF l_lifecycle_id IS NULL THEN
1527
1528 l_lifecycle_id := l_orig_lifecycle_id;
1529
1530 -- The lifecycle id could be null in the database (i.e not yet assigned to this revision)
1531 -- When a lifecycle is null, then current phase cannnot have value
1532 --
1533 IF l_lifecycle_id IS NULL THEN
1534 l_current_phase_id := NULL;
1535
1536 -- When there is a lifecycle and the current phase id is null or missing, then
1537 -- default it from the database.
1538 -- If the user has passed a valid current phase id, then use that
1539
1540 ELSIF l_current_phase_id IS NULL OR l_current_phase_id = FND_API.g_MISS_NUM THEN
1541 l_current_phase_id := l_orig_current_phase_id;
1542 END IF;
1543
1544 ELSIF l_lifecycle_id IS NOT NULL THEN
1545 --
1546 -- If the life cycle already exists for the revision, and the user has passed null
1547 -- or missing for the current phase, then default the old from the database
1548 --
1549 IF l_orig_lifecycle_id IS NOT NULL THEN
1550
1551 IF l_current_phase_id IS NULL OR l_current_phase_id = FND_API.g_MISS_NUM THEN
1552 l_current_phase_id := l_orig_current_phase_id;
1553 END IF;
1554
1555 ELSE
1556 --
1557 -- If the life cycle does not exist, and the user has passed null or missing for
1558 -- the current phase, then derive the current phase id
1559 --
1560 IF l_current_phase_id IS NULL OR l_current_phase_id = FND_API.g_MISS_NUM THEN
1561
1562 l_current_phase_id :=
1563 INV_EGO_REVISION_VALIDATE.Get_Initial_Lifecycle_Phase (l_lifecycle_id);
1564 IF l_current_phase_id = 0 THEN
1565 Add_Message ('INV', 'INV_REV_LIFECYCLE_INVALID');
1566 RAISE FND_API.g_EXC_ERROR;
1567 END IF;
1568
1569 END IF;
1570
1571 END IF;
1572 END IF;
1573 --Bug: 3802017 Validate if there is any pending CO for revision
1574 EXECUTE IMMEDIATE
1575 ' BEGIN '
1576 ||' EGO_INV_ITEM_CATALOG_PVT.VALIDATE_AND_CHANGE_ITEM_LC( '
1577 ||' P_API_VERSION => 1.0 '
1578 ||' ,P_COMMIT => FND_API.G_FALSE '
1579 ||' ,P_INVENTORY_ITEM_ID => :p_Item_Revision_rec.inventory_item_id'
1580 ||' ,P_ORGANIZATION_ID => :p_Item_Revision_rec.organization_id '
1581 ||' ,P_ITEM_REVISION_ID => :p_Item_Revision_rec.revision_id '
1582 ||' ,P_FETCH_CURR_VALUES => FND_API.G_TRUE '
1583 ||' ,P_CURR_CC_ID => NULL '
1584 ||' ,P_NEW_CC_ID => NULL '
1585 ||' ,P_CURR_LC_ID => NULL '
1586 ||' ,P_NEW_LC_ID => :l_lifecycle_id '
1587 ||' ,P_NEW_LCP_ID => :l_current_phase_id '
1588 ||' ,P_CURR_LCP_ID => NULL '
1589 ||' ,P_IS_NEW_CC_IN_HIER => FND_API.G_TRUE '
1590 ||' ,P_CHANGE_ID => NULL '
1591 ||' ,P_CHANGE_LINE_ID => NULL '
1592 ||' ,X_RETURN_STATUS => :l_return_status '
1593 ||' ,X_MSG_COUNT => :l_msg_count '
1594 ||' ,X_MSG_DATA => :l_msg_text); '
1595 ||' EXCEPTION '
1596 ||' WHEN OTHERS THEN '
1597 ||' NULL; '
1598 ||' END; '
1599 USING IN p_Item_Revision_rec.inventory_item_id,
1600 IN p_Item_Revision_rec.organization_id,
1601 IN p_Item_Revision_rec.revision_id,
1602 IN l_lifecycle_id,
1603 IN l_current_phase_id,
1604 OUT l_return_status,
1605 OUT l_msg_count,
1606 OUT l_msg_text;
1607
1608 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1609 Add_Message (p_message_text => l_msg_text);
1610 RAISE FND_API.g_EXC_ERROR;
1611 END IF;
1612
1613 --Bug: 3802017 ends
1614
1615 -- Start :5208102: Supporting template for UDA's at revisions
1616 IF p_Item_Revision_rec.template_id = FND_API.g_MISS_NUM THEN
1617 p_Item_Revision_rec.template_id := NULL;
1618 END IF;
1619 IF p_Item_Revision_rec.template_name = FND_API.g_MISS_CHAR THEN
1620 p_Item_Revision_rec.template_name := NULL;
1621 END IF;
1622
1623 IF (p_Item_Revision_rec.template_id IS NOT NULL)
1624 OR (p_Item_Revision_rec.template_name IS NOT NULL)
1625 THEN
1626 l_message_name := NULL;
1627 --Validate template name
1628 IF p_Item_Revision_rec.template_id IS NULL AND p_Item_Revision_rec.template_name IS NOT NULL THEN
1629 OPEN check_template_name(p_Item_Revision_rec.template_name);
1630 FETCH check_template_name INTO l_template_id;
1631 CLOSE check_template_name;
1632
1633 IF l_template_id IS NULL THEN
1634 l_message_name := 'INV_TEMPLATE_ERROR';
1635 END IF;
1636
1637 --Validate template id
1638 ELSIF p_Item_Revision_rec.template_id IS NOT NULL THEN
1639 OPEN check_template_id(p_Item_Revision_rec.template_id);
1640 FETCH check_template_id INTO l_template_id;
1641 CLOSE check_template_id;
1642
1643 IF l_template_id IS NULL THEN
1644 l_message_name := 'INV_TEMPLATE_ERROR';
1645 END IF;
1646 END IF;
1647
1648 IF l_message_name IS NOT NULL THEN
1649 Add_Message ('INV', l_message_name);
1650 RAISE FND_API.g_EXC_ERROR;
1651 ELSE
1652 l_apply_template := TRUE;
1653 END IF;
1654 END IF;
1655 -- End :5208102: Supporting template for UDA's at revisions
1656
1657
1658 --INV_ITEM_MSG.Debug(Mctx, 'UPDATE mtl_item_revisions table');
1659
1660 UPDATE mtl_item_revisions_b
1661 SET
1662 revision = DECODE(p_Item_Revision_rec.revision, G_Miss_Char, revision, null, revision, p_Item_Revision_rec.revision )
1663 , change_notice = DECODE(p_Item_Revision_rec.change_notice, G_Miss_Char, null, null, change_notice, p_Item_Revision_rec.change_notice )
1664 , ecn_initiation_date = DECODE(p_Item_Revision_rec.ecn_initiation_date, G_Miss_Date, null, null, ecn_initiation_date, p_Item_Revision_rec.ecn_initiation_date )
1665 , effectivity_date = DECODE(p_Item_Revision_rec.effectivity_date, null, effectivity_date, p_Item_Revision_rec.effectivity_date )
1666 , implementation_date = DECODE(change_notice,null,DECODE(p_Item_Revision_rec.effectivity_date,null,effectivity_date,p_Item_Revision_rec.effectivity_date),implementation_date) --3607562
1667 , revised_item_sequence_id = DECODE(p_Item_Revision_rec.revised_item_sequence_id, G_Miss_Num, null, null, revised_item_sequence_id, p_Item_Revision_rec.revised_item_sequence_id )
1668 , attribute_category = DECODE(p_Item_Revision_rec.attribute_category, G_Miss_Char, null, null, attribute_category, p_Item_Revision_rec.attribute_category )
1669 , attribute1 = DECODE(p_Item_Revision_rec.attribute1, G_Miss_Char, null, null, attribute1, p_Item_Revision_rec.attribute1 )
1670 , attribute2 = DECODE(p_Item_Revision_rec.attribute2, G_Miss_Char, null, null, attribute2, p_Item_Revision_rec.attribute2 )
1671 , attribute3 = DECODE(p_Item_Revision_rec.attribute3, G_Miss_Char, null, null, attribute3, p_Item_Revision_rec.attribute3 )
1672 , attribute4 = DECODE(p_Item_Revision_rec.attribute4, G_Miss_Char, null, null, attribute4, p_Item_Revision_rec.attribute4 )
1673 , attribute5 = DECODE(p_Item_Revision_rec.attribute5, G_Miss_Char, null, null, attribute5, p_Item_Revision_rec.attribute5 )
1674 , attribute6 = DECODE(p_Item_Revision_rec.attribute6, G_Miss_Char, null, null, attribute6, p_Item_Revision_rec.attribute6 )
1675 , attribute7 = DECODE(p_Item_Revision_rec.attribute7, G_Miss_Char, null, null, attribute7, p_Item_Revision_rec.attribute7 )
1676 , attribute8 = DECODE(p_Item_Revision_rec.attribute8, G_Miss_Char, null, null, attribute8, p_Item_Revision_rec.attribute8 )
1677 , attribute9 = DECODE(p_Item_Revision_rec.attribute9, G_Miss_Char, null, null, attribute9, p_Item_Revision_rec.attribute9 )
1678 , attribute10 = DECODE(p_Item_Revision_rec.attribute10, G_Miss_Char, null, null, attribute10, p_Item_Revision_rec.attribute10 )
1679 , attribute11 = DECODE(p_Item_Revision_rec.attribute11, G_Miss_Char, null, null, attribute11, p_Item_Revision_rec.attribute11 )
1680 , attribute12 = DECODE(p_Item_Revision_rec.attribute12, G_Miss_Char, null, null, attribute12, p_Item_Revision_rec.attribute12 )
1681 , attribute13 = DECODE(p_Item_Revision_rec.attribute13, G_Miss_Char, null, null, attribute13, p_Item_Revision_rec.attribute13 )
1682 , attribute14 = DECODE(p_Item_Revision_rec.attribute14, G_Miss_Char, null, null, attribute14, p_Item_Revision_rec.attribute14 )
1683 , attribute15 = DECODE(p_Item_Revision_rec.attribute15, G_Miss_Char, null, null, attribute15, p_Item_Revision_rec.attribute15 )
1684 , last_update_date = l_sysdate
1685 , last_updated_by = FND_GLOBAL.user_id
1686 , last_update_login = FND_GLOBAL.conc_login_id
1687 , request_id = DECODE(p_Item_Revision_rec.request_id, G_Miss_Num, null, null, request_id, p_Item_Revision_rec.request_id )
1688 , program_application_id = DECODE(p_Item_Revision_rec.program_application_id, G_Miss_Num, null, null, program_application_id, p_Item_Revision_rec.program_application_id )
1689 , program_id = DECODE(p_Item_Revision_rec.program_id, G_Miss_Num, null, null, program_id, p_Item_Revision_rec.program_id )
1690 , program_update_date = DECODE(p_Item_Revision_rec.program_update_date, G_Miss_Date, null, null, program_update_date, p_Item_Revision_rec.program_update_date )
1691 , object_version_number = nvl(object_version_number,0) + 1
1692 , revision_label = DECODE(p_Item_Revision_rec.revision_label, G_Miss_Char, revision_label, null, revision_label, p_Item_Revision_rec.revision_label)
1693 , revision_reason = DECODE(p_Item_Revision_rec.revision_reason, G_Miss_Char, null, null, revision_reason, p_Item_Revision_rec.revision_reason)
1694 , lifecycle_id = l_lifecycle_id
1695 , current_phase_id = l_current_phase_id
1696 WHERE
1697 inventory_item_id = p_Item_Revision_rec.inventory_item_id
1698 AND organization_id = p_Item_Revision_rec.organization_id
1699 AND (revision_id = p_Item_Revision_rec.revision_id or revision = p_Item_Revision_rec.revision)
1700 AND nvl(object_version_number,0) = nvl(p_Item_Revision_rec.object_version_number,nvl(object_version_number,0))
1701 RETURNING revision_id, object_version_number INTO G_revision_id, G_object_version_number;
1702
1703 IF ( SQL%NOTFOUND ) THEN
1704 Add_Message ('INV', 'INV_ITM_REVISION_REC_CHANGED');
1705 RAISE FND_API.g_EXC_ERROR;
1706 END IF;
1707
1708
1709 --
1710 -- IF Update revision API is called directly (not through Process_Item_Revision),
1711 -- then get the language code
1712 --
1713
1714 IF G_language_code IS NULL THEN
1715 SELECT userenv('LANG') INTO G_language_code FROM dual;
1716 END IF;
1717
1718 -- Update the description in the TL table
1719 --
1720 UPDATE mtl_item_revisions_TL
1721 SET description = DECODE( p_Item_Revision_rec.description, G_Miss_Char, description, --Bug: 3055810 replaced NULL with G_Miss_Char
1722 p_Item_Revision_rec.description)
1723 , last_updated_by = G_User_Id
1724 , last_update_date = l_sysdate
1725 WHERE revision_id = G_revision_id
1726 AND LANGUAGE = G_language_code;
1727
1728 --
1729 -- Initiate the revision entry in pending item status table which will maintain
1730 -- the period for each lifecycle phase
1731 --
1732 IF l_lifecycle_id IS NOT NULL AND
1733 l_orig_lifecycle_id IS NULL THEN
1734
1735 INSERT INTO mtl_pending_item_status
1736 ( Inventory_Item_Id
1737 , Organization_Id
1738 , Status_code
1739 , Effective_date
1740 , Implemented_date
1741 , Pending_flag
1742 , Revision_Id
1743 , lifecycle_id
1744 , phase_id
1745 , Created_By
1746 , Creation_Date
1747 , Last_Updated_By
1748 , Last_Update_Date
1749 , Last_Update_Login
1750 )
1751 VALUES
1752 ( p_Item_Revision_rec.Inventory_Item_Id
1753 , p_Item_Revision_rec.Organization_Id
1754 , NULL
1755 , l_sysdate
1756 , l_sysdate
1757 , 'N'
1758 , G_revision_id
1759 , l_lifecycle_id
1760 , l_current_phase_id
1761 , G_User_Id
1762 , l_sysdate
1763 , G_User_Id
1764 , l_sysdate
1765 , G_Login_Id
1766 );
1767 END IF;
1768
1769
1770
1771 /* R12: Business Event Enhancement :
1772 Raise Event if Revision got Updated successfully */
1773
1774 IF (INSTR(NVL(p_process_control,'PLM_UI:N'),'PLM_UI:Y') = 0 ) THEN
1775
1776 -- Start 5208102: Supporting template for UDA's at revisions
1777 IF l_apply_template THEN
1778 Insert_Revision_UserAttr(p_organization_id => p_Item_Revision_rec.Organization_Id
1779 ,p_inventory_item_id => p_Item_Revision_rec.inventory_item_id
1780 ,p_revision_id => G_revision_id
1781 ,p_transaction_type => 'UPDATE'
1782 ,p_template_id => l_template_id);
1783 END IF;
1784 -- End 5208102: Supporting template for UDA's at revisions
1785
1786 -- Bug 5525054
1787 BEGIN
1788 INV_ITEM_EVENTS_PVT.Raise_Events(
1789 p_event_name => 'EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT'
1790 ,p_dml_type => 'UPDATE'
1791 ,p_inventory_item_id => p_Item_Revision_rec.Inventory_Item_Id
1792 ,p_organization_id => p_Item_Revision_rec.Organization_Id
1793 ,p_revision_id => p_Item_Revision_rec.revision_id);
1794
1795 EXCEPTION
1796 WHEN OTHERS THEN
1797 NULL;
1798 END;
1799 END IF;
1800 /* R12: Business Event Enhancement :
1801 Raise Event if Revision got Updated successfully */
1802
1803
1804 -- Standard check of p_commit
1805 IF FND_API.To_Boolean (p_commit) THEN
1806 --INV_ITEM_MSG.Debug(Mctx, 'before COMMIT WORK');
1807 COMMIT WORK;
1808 END IF;
1809
1810 -- Standard call to get message count and if count is 1, get message info.
1811
1812 IF G_Message_API = 'BOM' THEN
1813 x_msg_count := Error_Handler.Get_Message_Count;
1814 ELSE
1815 FND_MSG_PUB.Count_And_Get
1816 ( p_count => x_msg_count
1817 , p_data => x_msg_data
1818 );
1819 /*Bug 6853558 Added to get the message if count is > 1 */
1820 IF( x_msg_count > 1 ) THEN
1821 x_msg_data := fnd_msg_pub.get( x_msg_count,FND_API.G_FALSE);
1822 END IF;
1823 END IF;
1824
1825 EXCEPTION
1826
1827 WHEN FND_API.g_EXC_ERROR THEN
1828
1829 ROLLBACK TO Update_Item_Revision_PUB;
1830 x_return_status := FND_API.g_RET_STS_ERROR;
1831
1832 IF G_Message_API = 'BOM' THEN
1833 x_msg_count := Error_Handler.Get_Message_Count;
1834 ELSE
1835 FND_MSG_PUB.Count_And_Get
1836 ( p_count => x_msg_count
1837 , p_data => x_msg_data
1838 );
1839 /*Bug 6853558 Added to get the message if count is > 1 */
1840 IF( x_msg_count > 1 ) THEN
1841 x_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
1842 END IF;
1843 END IF;
1844
1845 WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
1846
1847 ROLLBACK TO Update_Item_Revision_PUB;
1848 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
1849
1850 IF G_Message_API = 'BOM' THEN
1851 x_msg_count := Error_Handler.Get_Message_Count;
1852 ELSE
1853 FND_MSG_PUB.Count_And_Get
1854 ( p_count => x_msg_count
1855 , p_data => x_msg_data
1856 );
1857 /*Bug 6853558 Added to get the message if count is > 1 */
1858 IF( x_msg_count > 1 ) THEN
1859 x_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
1860 END IF;
1861 END IF;
1862
1863 WHEN others THEN
1864
1865 ROLLBACK TO Update_Item_Revision_PUB;
1866 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
1867
1868 Add_Message
1869 ( p_api_name => l_api_name
1870 , p_message_text => 'UNEXP_ERROR : ' || SQLERRM
1871 );
1872
1873 IF G_Message_API = 'BOM' THEN
1874 x_msg_count := Error_Handler.Get_Message_Count;
1875 ELSE
1876 FND_MSG_PUB.Count_And_Get
1877 ( p_count => x_msg_count
1878 , p_data => x_msg_data
1879 );
1880 /**Bug 6853558 Added to get the message if count is > 1 */
1881 IF( x_msg_count > 1 ) THEN
1882 x_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
1883 END IF;
1884 END IF;
1885
1886 END Update_Item_Revision;
1887
1888
1889 -- ============================================================================
1890 -- API Name: Lock_Item_Revision
1891 -- ============================================================================
1892
1893 PROCEDURE Lock_Item_Revision
1894 (
1895 p_api_version IN NUMBER
1896 , p_init_msg_list IN VARCHAR2 := FND_API.g_FALSE
1897 , x_return_status OUT NOCOPY VARCHAR2
1898 , x_msg_count OUT NOCOPY NUMBER
1899 , x_msg_data OUT NOCOPY VARCHAR2
1900 , p_inventory_item_id IN NUMBER
1901 , p_organization_id IN NUMBER
1902 , p_revision IN VARCHAR2
1903 , p_object_version_number IN NUMBER
1904 )
1905 IS
1906 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Item_Revision';
1907 l_api_version CONSTANT NUMBER := 1.0;
1908 -- Mctx INV_ITEM_MSG.Msg_Ctx_type;
1909
1910 l_return_status VARCHAR2(1);
1911 l_object_version_number NUMBER;
1912
1913 CURSOR Item_Revision_Lock_cur
1914 ( p_inventory_item_id NUMBER
1915 , p_organization_id NUMBER
1916 , p_revision VARCHAR2
1917 ) IS
1918 SELECT object_version_number
1919 FROM mtl_item_revisions_b
1920 WHERE
1921 inventory_item_id = p_inventory_item_id
1922 AND organization_id = p_organization_id
1923 AND revision = p_revision
1924 FOR UPDATE NOWAIT;
1925
1926 BEGIN
1927
1928 -- Standard Start of API savepoint
1929 SAVEPOINT Lock_Item_Revision_PUB;
1930
1931 -- Check for call compatibility
1932 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1933 l_api_name, G_PKG_NAME)
1934 THEN
1935 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
1936 END IF;
1937
1938 -- Initialize message list
1939 IF G_Message_API = 'FND' THEN
1940 IF FND_API.To_Boolean (p_init_msg_list) THEN
1941 FND_MSG_PUB.Initialize;
1942 END IF;
1943 END IF;
1944
1945 -- Define message context
1946 -- Mctx.Package_Name := G_PKG_NAME;
1947 -- Mctx.Procedure_Name := l_api_name;
1948
1949 -- Initialize API return status to success
1950 x_return_status := FND_API.g_RET_STS_SUCCESS;
1951
1952 -- ----------------------------------------------
1953 -- Check if revision exists, and lock the record
1954 -- ----------------------------------------------
1955
1956 OPEN Item_Revision_Lock_cur ( p_inventory_item_id
1957 , p_organization_id
1958 , p_revision );
1959
1960 FETCH Item_Revision_Lock_cur INTO l_object_version_number;
1961
1962 IF ( Item_Revision_Lock_cur%NOTFOUND ) THEN
1963 CLOSE Item_Revision_Lock_cur;
1964 Add_Message ('INV', 'INV_ITM_REVISION_REC_DELETED');
1965 RAISE FND_API.g_EXC_ERROR;
1966 END IF;
1967
1968 CLOSE Item_Revision_Lock_cur;
1969
1970 -- -------------------------------------
1971 -- Check if revision record has changed
1972 -- -------------------------------------
1973
1974 IF ( nvl(l_object_version_number,0) <> nvl(p_object_version_number,0) ) THEN
1975 Add_Message ('INV', 'INV_ITM_REVISION_REC_CHANGED');
1976 RAISE FND_API.g_EXC_ERROR;
1977 END IF;
1978
1979 -- Standard call to get message count and if count is 1, get message info.
1980
1981 IF G_Message_API = 'BOM' THEN
1982 x_msg_count := Error_Handler.Get_Message_Count;
1983 ELSE
1984 FND_MSG_PUB.Count_And_Get
1985 ( p_count => x_msg_count
1986 , p_data => x_msg_data
1987 );
1988 /*Bug 6853558 Added to get the message if count is > 1 */
1989 IF( x_msg_count > 1 ) THEN
1990 x_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
1991 END IF;
1992 END IF;
1993
1994 EXCEPTION
1995
1996 WHEN FND_API.g_EXC_ERROR THEN
1997
1998 ROLLBACK TO Lock_Item_Revision_PUB;
1999 x_return_status := FND_API.g_RET_STS_ERROR;
2000
2001 IF G_Message_API = 'BOM' THEN
2002 x_msg_count := Error_Handler.Get_Message_Count;
2003 ELSE
2004 FND_MSG_PUB.Count_And_Get
2005 ( p_count => x_msg_count
2006 , p_data => x_msg_data
2007 );
2008 /*Bug 6853558 Added to get the message if count is > 1 */
2009 IF( x_msg_count > 1 ) THEN
2010 x_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
2011 END IF;
2012 END IF;
2013
2014 WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
2015
2016 ROLLBACK TO Lock_Item_Revision_PUB;
2017 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
2018
2019 IF G_Message_API = 'BOM' THEN
2020 x_msg_count := Error_Handler.Get_Message_Count;
2021 ELSE
2022 FND_MSG_PUB.Count_And_Get
2023 ( p_count => x_msg_count
2024 , p_data => x_msg_data
2025 );
2026 /*Bug 6853558 Added to get the message if count is > 1 */
2027 IF( x_msg_count > 1 ) THEN
2028 x_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
2029 END IF;
2030 END IF;
2031
2032 WHEN others THEN
2033
2034 ROLLBACK TO Lock_Item_Revision_PUB;
2035 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
2036
2037 Add_Message
2038 ( p_api_name => l_api_name
2039 , p_message_text => 'UNEXP_ERROR : ' || SQLERRM
2040 );
2041
2042 IF G_Message_API = 'BOM' THEN
2043 x_msg_count := Error_Handler.Get_Message_Count;
2044 ELSE
2045 FND_MSG_PUB.Count_And_Get
2046 ( p_count => x_msg_count
2047 , p_data => x_msg_data
2048 );
2049 /*Bug 6853558 Added to get the message if count is > 1 */
2050 IF( x_msg_count > 1 ) THEN
2051 x_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
2052 END IF;
2053 END IF;
2054
2055 END Lock_Item_Revision;
2056
2057
2058 -- ============================================================================
2059 -- API Name: Delete_Item_Revision
2060 -- ============================================================================
2061
2062 PROCEDURE Delete_Item_Revision
2063 (
2064 p_api_version IN NUMBER
2065 , p_init_msg_list IN VARCHAR2 := FND_API.g_FALSE
2066 , p_commit IN VARCHAR2 := FND_API.g_FALSE
2067 , p_validation_level IN NUMBER := FND_API.g_VALID_LEVEL_FULL
2068 , x_return_status OUT NOCOPY VARCHAR2
2069 , x_msg_count OUT NOCOPY NUMBER
2070 , x_msg_data OUT NOCOPY VARCHAR2
2071 , p_inventory_item_id IN NUMBER
2072 , p_organization_id IN NUMBER
2073 , p_revision IN VARCHAR2
2074 , p_object_version_number IN NUMBER
2075 )
2076 IS
2077 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Item_Revision';
2078 l_api_version CONSTANT NUMBER := 1.0;
2079 -- Mctx INV_ITEM_MSG.Msg_Ctx_type;
2080
2081 l_return_status VARCHAR2(1);
2082 l_object_version_number NUMBER;
2083
2084 BEGIN
2085
2086 -- Standard Start of API savepoint
2087 SAVEPOINT Delete_Item_Revision_PUB;
2088
2089 -- Check for call compatibility
2090 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
2091 l_api_name, G_PKG_NAME)
2092 THEN
2093 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
2094 END IF;
2095
2096 -- Initialize message list
2097 IF G_Message_API = 'FND' THEN
2098 IF FND_API.To_Boolean (p_init_msg_list) THEN
2099 FND_MSG_PUB.Initialize;
2100 END IF;
2101 END IF;
2102
2103 -- Define message context
2104 -- Mctx.Package_Name := G_PKG_NAME;
2105 -- Mctx.Procedure_Name := l_api_name;
2106
2107 -- Initialize API return status to success
2108 x_return_status := FND_API.g_RET_STS_SUCCESS;
2109
2110 -- -------------------------
2111 -- Check if revision exists
2112 -- -------------------------
2113
2114 OPEN Item_Revision_Exists_cur ( p_inventory_item_id
2115 , p_organization_id
2116 , p_revision );
2117
2118 FETCH Item_Revision_Exists_cur INTO l_object_version_number;
2119
2120 IF ( Item_Revision_Exists_cur%NOTFOUND ) THEN
2121 CLOSE Item_Revision_Exists_cur;
2122 Add_Message ('INV', 'INV_ITM_REVISION_REC_DELETED');
2123 RAISE FND_API.g_EXC_ERROR;
2124 END IF;
2125
2126 CLOSE Item_Revision_Exists_cur;
2127
2128 -- -------------------------------------
2129 -- Check if revision record has changed
2130 -- -------------------------------------
2131
2132 IF ( l_object_version_number <> p_object_version_number ) THEN
2133 Add_Message ('INV', 'INV_ITM_REVISION_REC_CHANGED');
2134 RAISE FND_API.g_EXC_ERROR;
2135 END IF;
2136
2137 --dbms_output.put_line('DELETE FROM mtl_item_revisions; x_return_status = ' || x_return_status);
2138
2139 DELETE FROM mtl_item_revisions_b
2140 WHERE
2141 inventory_item_id = p_inventory_item_id
2142 AND organization_id = p_organization_id
2143 AND revision = p_revision
2144 AND nvl(object_version_number,0) = nvl(p_object_version_number,0)
2145 RETURNING revision_id, object_version_number INTO G_revision_id, G_object_version_number;
2146
2147 IF ( SQL%NOTFOUND ) THEN
2148 Add_Message ('INV', 'INV_ITM_REVISION_REC_CHANGED');
2149 RAISE FND_API.g_EXC_ERROR;
2150 END IF;
2151
2152 --
2153 -- Remove the corresponding TL entries for this revision record
2154 -- from the TL table
2155 --
2156 DELETE FROM mtl_item_revisions_TL
2157 WHERE revision_id = G_revision_id;
2158
2159 --
2160 -- Remove the corresponding entries from pending item status table
2161 --
2162 DELETE FROM mtl_pending_item_status
2163 WHERE inventory_item_id = p_inventory_item_id
2164 AND organization_id = p_organization_id
2165 AND revision_id = G_revision_id;
2166
2167 --dbms_output.put_line('done DELETEing FROM mtl_item_revisions; x_return_status = ' || x_return_status);
2168
2169 -- Standard check of p_commit
2170 IF FND_API.To_Boolean (p_commit) THEN
2171 --INV_ITEM_MSG.Debug(Mctx, 'before COMMIT WORK');
2172 COMMIT WORK;
2173 END IF;
2174
2175 -- Standard call to get message count and if count is 1, get message info.
2176
2177 IF G_Message_API = 'BOM' THEN
2178 x_msg_count := Error_Handler.Get_Message_Count;
2179 ELSE
2180 FND_MSG_PUB.Count_And_Get
2181 ( p_count => x_msg_count
2182 , p_data => x_msg_data
2183 );
2184 /*Bug 6853558 Added to get the message if count is > 1 */
2185 IF( x_msg_count > 1 ) THEN
2186 x_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
2187 END IF;
2188 END IF;
2189
2190 EXCEPTION
2191
2192 WHEN FND_API.g_EXC_ERROR THEN
2193
2194 ROLLBACK TO Delete_Item_Revision_PUB;
2195 x_return_status := FND_API.g_RET_STS_ERROR;
2196
2197 IF G_Message_API = 'BOM' THEN
2198 x_msg_count := Error_Handler.Get_Message_Count;
2199 ELSE
2200 FND_MSG_PUB.Count_And_Get
2201 ( p_count => x_msg_count
2202 , p_data => x_msg_data
2203 );
2204 /*Bug 6853558 Added to get the message if count is > 1 */
2205 IF( x_msg_count > 1 ) THEN
2206 x_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
2207 END IF;
2208 END IF;
2209
2210 WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
2211
2212 ROLLBACK TO Delete_Item_Revision_PUB;
2213 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
2214
2215 IF G_Message_API = 'BOM' THEN
2216 x_msg_count := Error_Handler.Get_Message_Count;
2217 ELSE
2218 FND_MSG_PUB.Count_And_Get
2219 ( p_count => x_msg_count
2220 , p_data => x_msg_data
2221 );
2222 /*Bug 6853558 Added to get the message if count is > 1 */
2223 IF( x_msg_count > 1 ) THEN
2224 x_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
2225 END IF;
2226 END IF;
2227
2228 WHEN others THEN
2229
2230 ROLLBACK TO Delete_Item_Revision_PUB;
2231 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
2232
2233 Add_Message
2234 ( p_api_name => l_api_name
2235 , p_message_text => 'UNEXP_ERROR : ' || SQLERRM
2236 );
2237
2238 IF G_Message_API = 'BOM' THEN
2239 x_msg_count := Error_Handler.Get_Message_Count;
2240 ELSE
2241 FND_MSG_PUB.Count_And_Get
2242 ( p_count => x_msg_count
2243 , p_data => x_msg_data
2244 );
2245 /*Bug 6853558 Added to get the message if count is > 1 */
2246 IF( x_msg_count > 1 ) THEN
2247 x_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
2248 END IF;
2249 END IF;
2250
2251 END Delete_Item_Revision;
2252
2253 PROCEDURE Process_Item_Revision
2254 (
2255 p_inventory_item_id IN NUMBER
2256 , p_organization_id IN NUMBER
2257 , p_revision IN VARCHAR2
2258 , p_description IN VARCHAR2 := NULL
2259 , p_change_notice IN VARCHAR2 := NULL
2260 , p_ecn_initiation_date IN DATE := NULL
2261 , p_implementation_date IN DATE := NULL
2262 , p_effectivity_date IN DATE := NULL
2263 , p_revised_item_sequence_id IN NUMBER := NULL
2264 , p_attribute_category IN VARCHAR2 := NULL
2265 , p_attribute1 IN VARCHAR2 := NULL
2266 , p_attribute2 IN VARCHAR2 := NULL
2267 , p_attribute3 IN VARCHAR2 := NULL
2268 , p_attribute4 IN VARCHAR2 := NULL
2269 , p_attribute5 IN VARCHAR2 := NULL
2270 , p_attribute6 IN VARCHAR2 := NULL
2271 , p_attribute7 IN VARCHAR2 := NULL
2272 , p_attribute8 IN VARCHAR2 := NULL
2273 , p_attribute9 IN VARCHAR2 := NULL
2274 , p_attribute10 IN VARCHAR2 := NULL
2275 , p_attribute11 IN VARCHAR2 := NULL
2276 , p_attribute12 IN VARCHAR2 := NULL
2277 , p_attribute13 IN VARCHAR2 := NULL
2278 , p_attribute14 IN VARCHAR2 := NULL
2279 , p_attribute15 IN VARCHAR2 := NULL
2280 , p_object_version_number IN NUMBER
2281 , p_revision_label IN VARCHAR2 := NULL
2282 , p_revision_reason IN VARCHAR2 := NULL
2283 , p_lifecycle_id IN NUMBER := NULL
2284 , p_current_phase_id IN NUMBER := NULL
2285 , p_template_id IN NUMBER := NULL --5208102
2286 , p_template_name IN VARCHAR2 := NULL --5208102
2287 , p_language_code IN VARCHAR2 := 'US'
2288 , p_transaction_type IN VARCHAR2
2289 , p_message_api IN VARCHAR2 := 'FND'
2290 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2291 , x_Return_Status OUT NOCOPY VARCHAR2
2292 , x_msg_count OUT NOCOPY NUMBER
2293 , x_revision_id IN OUT NOCOPY NUMBER
2294 , x_object_version_number IN OUT NOCOPY NUMBER
2295 , p_debug IN VARCHAR2 := 'N'
2296 , p_output_dir IN VARCHAR2 := NULL
2297 , p_debug_filename IN VARCHAR2 := 'Ego_Item_Revision.log'
2298 , p_revision_id IN NUMBER := NULL
2299 , p_process_control IN VARCHAR2 := NULL
2300 ) IS
2301
2302 l_item_revision_rec Item_Revision_rec_type;
2303 l_object_version_number NUMBER;
2304 l_msg_data VARCHAR2(2000);
2305 l_api_name CONSTANT VARCHAR2(30) := 'Process_Item_Revision';
2306
2307 l_debug_return_status VARCHAR2(1);
2308 l_debug_error_message VARCHAR2(2000);
2309 BEGIN
2310 -- Initialize the global variables
2311
2312 G_revision_id := NULL;
2313 G_object_version_number := NULL;
2314 G_language_code := p_language_code;
2315 G_Message_API := p_message_API;
2316 G_Sysdate := SYSDATE;
2317
2318 -- Initialize message list
2319 IF G_Message_API = 'BOM' THEN
2320 IF FND_API.To_Boolean (p_init_msg_list) THEN
2321 Error_Handler.Initialize;
2322 Error_Handler.Set_BO_Identifier ('INV_ITEM_REVISION');
2323 END IF;
2324 ELSE
2325 /* G_Message_API = 'FND' THEN */
2326 IF FND_API.To_Boolean (p_init_msg_list) THEN
2327 FND_MSG_PUB.Initialize;
2328 END IF;
2329 END IF;
2330
2331 -- Open the debug session
2332
2333 IF p_debug = 'Y' THEN
2334
2335 Error_Handler.Set_Debug (p_debug_flag => 'Y');
2336
2337 Error_Handler.Open_Debug_Session
2338 ( p_debug_filename => p_debug_filename
2339 , p_output_dir => p_output_dir
2340 , x_return_status => l_debug_return_status
2341 , x_error_mesg => l_debug_error_message
2342 );
2343
2344 IF l_debug_return_status <> FND_API.g_RET_STS_SUCCESS THEN
2345 -- Debug fail information can be inserted into the error table
2346 Null;
2347 END IF;
2348
2349 END IF;
2350
2351 Error_Handler.Write_Debug('Debug file mode is '||Error_Handler.Get_Debug);
2352
2353 Error_Handler.Write_Debug('Sysdate is '||to_char(G_sysdate,'DD-MON-YYYY HH24:MI:SS')||' Effectivity date is '||to_char(p_effectivity_date,'DD-MON-YYYY HH24:MI:SS'));
2354
2355 -- Convert the transaction type if it is SYNC
2356
2357 IF p_transaction_type = 'SYNC' THEN
2358
2359 OPEN Item_Revision_Exists_cur ( p_inventory_item_id
2360 , p_organization_id
2361 , p_revision );
2362
2363 FETCH Item_Revision_Exists_cur INTO l_object_version_number;
2364
2365 IF ( Item_Revision_Exists_cur%FOUND ) THEN
2366 l_item_revision_rec.transaction_type := Bom_Globals.G_OPR_UPDATE;
2367 ELSE
2368 l_item_revision_rec.transaction_type := Bom_Globals.G_OPR_CREATE;
2369 END IF;
2370
2371 CLOSE Item_Revision_Exists_cur;
2372
2373 ELSE
2374
2375 l_item_revision_rec.transaction_type := p_transaction_type;
2376
2377 END IF;
2378
2379 -- Validate the transaction type
2380
2381 IF l_item_revision_rec.transaction_type NOT IN (Bom_Globals.G_OPR_CREATE,
2382 Bom_Globals.G_OPR_UPDATE,
2383 Bom_Globals.G_OPR_DELETE) THEN
2384
2385 Add_Message ('INV', 'INV_INVALID_TRANS_TYPE');
2386 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
2387
2388 IF G_Message_API = 'BOM' THEN
2389 x_msg_count := Error_Handler.Get_Message_Count;
2390 ELSE
2391 FND_MSG_PUB.Count_And_Get
2392 ( p_count => x_msg_count
2393 , p_data => l_msg_data
2394 );
2395 /*Bug 6853558 Added to get the message if count is > 1 */
2396 IF( x_msg_count > 1 ) THEN
2397 l_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
2398 END IF;
2399 END IF;
2400
2401 IF Error_Handler.Get_Debug = 'Y' THEN
2402 Error_Handler.Close_Debug_Session;
2403 END IF;
2404
2405 Return;
2406
2407 END IF;
2408
2409 IF l_item_revision_rec.transaction_type = Bom_Globals.G_OPR_CREATE
2410 AND p_revision_id IS NOT NULL THEN
2411 l_item_revision_rec.revision_id := p_revision_id;
2412 END IF;
2413
2414 -- Create the record structure from the individual parameters for CREATE and UPDATE
2415 IF l_item_revision_rec.transaction_type IN (Bom_Globals.G_OPR_CREATE,
2416 Bom_Globals.G_OPR_UPDATE)
2417 THEN
2418 l_item_revision_rec.inventory_item_id := p_inventory_item_id;
2419 l_item_revision_rec.organization_id := p_organization_id;
2420 l_item_revision_rec.revision := p_revision;
2421 l_item_revision_rec.description := p_description;
2422 l_item_revision_rec.change_notice := p_change_notice;
2423 l_item_revision_rec.ecn_initiation_date := p_ecn_initiation_date;
2424 l_item_revision_rec.implementation_date := p_implementation_date;
2425 l_item_revision_rec.effectivity_date := p_effectivity_date;
2426 l_item_revision_rec.revised_item_sequence_id := p_revised_item_sequence_id;
2427 l_item_revision_rec.attribute_category := p_attribute_category;
2428 l_item_revision_rec.attribute1 := p_attribute1;
2429 l_item_revision_rec.attribute2 := p_attribute2;
2430 l_item_revision_rec.attribute3 := p_attribute3;
2431 l_item_revision_rec.attribute4 := p_attribute4;
2432 l_item_revision_rec.attribute5 := p_attribute5;
2433 l_item_revision_rec.attribute6 := p_attribute6;
2434 l_item_revision_rec.attribute7 := p_attribute7;
2435 l_item_revision_rec.attribute8 := p_attribute8;
2436 l_item_revision_rec.attribute9 := p_attribute9;
2437 l_item_revision_rec.attribute10 := p_attribute10;
2438 l_item_revision_rec.attribute11 := p_attribute11;
2439 l_item_revision_rec.attribute12 := p_attribute12;
2440 l_item_revision_rec.attribute13 := p_attribute13;
2441 l_item_revision_rec.attribute14 := p_attribute14;
2442 l_item_revision_rec.attribute15 := p_attribute15;
2443 l_item_revision_rec.object_version_number := p_object_version_number;
2444 l_item_revision_rec.revision_label := p_revision_label;
2445 l_item_revision_rec.revision_reason := p_revision_reason;
2446 --35557001
2447 -- lifecycle can be null, for API compatability, change it to MISS_NUM
2448 l_item_revision_rec.lifecycle_id := NVL(p_lifecycle_id,FND_API.G_MISS_NUM);
2449 l_item_revision_rec.current_phase_id := NVL(p_current_phase_id,FND_API.G_MISS_NUM);
2450
2451 -- 5208102: Supporting template for UDA's at revisions
2452 l_item_revision_rec.template_id := p_template_id;
2453 l_item_revision_rec.template_name := p_template_name;
2454
2455 END IF;
2456
2457 -- Call the appropriate procedure to carry out the transaction
2458
2459 IF l_item_revision_rec.transaction_type = Bom_Globals.G_OPR_CREATE
2460 THEN
2461
2462 --dbms_output.put_line('Calling Create ...');
2463
2464 Create_Item_Revision
2465 (
2466 p_api_version => 1.0
2467 , p_init_msg_list => FND_API.G_TRUE
2468 , x_return_status => x_return_status
2469 , x_msg_count => x_msg_count
2470 , x_msg_data => l_msg_data
2471 , p_Item_Revision_rec => l_item_revision_rec
2472 , p_process_control => p_process_control
2473 );
2474
2475 ELSIF l_item_revision_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
2476 THEN
2477
2478 --dbms_output.put_line('Calling Update ...');
2479 l_item_revision_rec.revision_id := p_revision_id;
2480
2481 Update_Item_Revision
2482 (
2483 p_api_version => 1.0
2484 , p_init_msg_list => FND_API.G_TRUE
2485 , x_return_status => x_return_status
2486 , x_msg_count => x_msg_count
2487 , x_msg_data => l_msg_data
2488 , p_Item_Revision_rec => l_item_revision_rec
2489 , p_process_control => p_process_control
2490 );
2491
2492 ELSE
2493
2494 --dbms_output.put_line('Calling Delete ...');
2495
2496 Delete_Item_Revision
2497 (
2498 p_api_version => 1.0
2499 , p_init_msg_list => FND_API.G_TRUE
2500 , x_return_status => x_return_status
2501 , x_msg_count => x_msg_count
2502 , x_msg_data => l_msg_data
2503 , p_inventory_item_id => p_inventory_item_id
2504 , p_organization_id => p_organization_id
2505 , p_revision => p_revision
2506 , p_object_version_number => p_object_version_number
2507 );
2508 END IF;
2509
2510 -- Assign the values for remaining OUT variables
2511
2512 x_revision_id := G_revision_id;
2513 x_object_version_number := G_object_version_number;
2514
2515 IF Error_Handler.Get_Debug = 'Y' THEN
2516 Error_Handler.Close_Debug_Session;
2517 END IF;
2518
2519 EXCEPTION WHEN OTHERS THEN
2520
2521 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
2522
2523 Add_Message
2524 ( p_api_name => l_api_name
2525 , p_message_text => 'UNEXP_ERROR : ' || SQLERRM
2526 );
2527
2528 IF G_Message_API = 'BOM' THEN
2529 x_msg_count := Error_Handler.Get_Message_Count;
2530 ELSE
2531 FND_MSG_PUB.Count_And_Get
2532 ( p_count => x_msg_count
2533 , p_data => l_msg_data
2534 );
2535 /*Bug 6853558 Added to get the message if count is > 1 */
2536 IF( x_msg_count > 1 ) THEN
2537 l_msg_data := fnd_msg_pub.get(x_msg_count,FND_API.G_FALSE);
2538 END IF;
2539 END IF;
2540
2541 IF Error_Handler.Get_Debug = 'Y' THEN
2542 Error_Handler.Close_Debug_Session;
2543 END IF;
2544
2545 END Process_Item_Revision;
2546
2547
2548
2549 END INV_ITEM_REVISION_PUB;