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