[Home] [Help]
PACKAGE BODY: APPS.MTL_CROSS_REFERENCES_PVT
Source
1 PACKAGE BODY mtl_cross_references_pvt AS
2 /* $Header: INVVXRFB.pls 120.16.12020000.2 2012/10/03 11:44:28 ccsingh ship $ */
3
4 -------------------------------------------------------------------------
5 -- Debug Profile option used to write Error_Handler.Write_Debug --
6 -- Profile option name = INV_DEBUG_TRACE ; --
7 -- User Profile Option Name = INV: Debug Trace --
8 -- Values: 1 (True) ; 0 (False) --
9 -------------------------------------------------------------------------
10 G_DEBUG VARCHAR2(10);
11
12 -----------------------------------------------
13 -- Write Debug statements to Concurrent Log --
14 -----------------------------------------------
15 PROCEDURE Write_Debug (p_msg IN VARCHAR2) IS
16 l_err_msg VARCHAR2(240);
17 BEGIN
18 -- If Profile set to TRUE --
19 IF (G_DEBUG = 1) THEN
20 FND_FILE.put_line(FND_FILE.LOG, p_msg);
21 END IF;
22
23 EXCEPTION
24 WHEN OTHERS THEN
25 l_err_msg := SUBSTRB(SQLERRM, 1,240);
26 FND_FILE.put_line(FND_FILE.LOG, 'LOGGING SQL ERROR => '||l_err_msg);
27 END Write_Debug;
28
29 -- -----------------------------------------------------------------------------
30 -- API Name: Process_XRef
31 --
32 -- Description :
33 -- Process (CREATE/UPDATE/DELETE) a set of Cross References based on data in
34 -- the pl/sql table.
35 -- -----------------------------------------------------------------------------
36
37
38 PROCEDURE Process_XRef(
39 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
40 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
41 ,p_XRef_Tbl IN OUT NOCOPY MTL_CROSS_REFERENCES_PUB.XRef_Tbl_Type
42 ,x_return_status OUT NOCOPY VARCHAR2
43 ,x_msg_count OUT NOCOPY NUMBER
44 ,x_message_list OUT NOCOPY Error_Handler.Error_Tbl_Type) IS
45
46 -- Local variable Declarations
47 --l_api_name VARCHAR2(30) :='Process_XRef';
48 l_exists VARCHAR(1);
49 l_XRef_exists VARCHAR(1);
50
51 l_prod_exists VARCHAR(1); -- for checking security when PIM is installed
52 l_pim_exists VARCHAR(1); -- for checking security when PIM is installed
53
54
55 l_uom_code_valid VARCHAR(1); /*bug 14138918*/
56 l_rev_id_invalid VARCHAR(1); /*bug 14138918*/
57
58 l_XRef_Rec MTL_CROSS_REFERENCES_PUB.XRef_Rec_Type; -- Declaring the record type object
59 l_XRef_Tbl MTL_CROSS_REFERENCES_PUB.XRef_Tbl_Type; -- Declaring local table object
60 l_Token_Tbl Error_Handler.Token_Tbl_Type; -- For passing token in error msgs
61 l_msg_count NUMBER:=0;
62 returned_cross_ref_id NUMBER;
63 returned_object_version_number NUMBER;
64
65 l_gtin_return_status VARCHAR2(255); -- return status for gtin validations
66 l_gtin_msg_count NUMBER; -- num of msgs for gtin validations, only used for checking if errored out
67
68 -- Cursor for fetching the current values from the table.
69 CURSOR mtl_xref_cur(c_cross_reference_id NUMBER) IS
70 SELECT *
71 FROM mtl_cross_references
72 WHERE CROSS_REFERENCE_ID=c_cross_reference_id;
73
74 l_mtl_XRef_rec MTL_CROSS_REFERENCES%ROWTYPE; -- For fetching the data of above cursor
75
76 VALIDATION_ERROR EXCEPTION;
77 UNEXP_VALIDATION_ERROR EXCEPTION;
78 l_has_privilege VARCHAR2(1);
79
80 -- cursor for locking the record while updating and deleting
81 CURSOR mtl_xref_lock_b(c_cross_reference_id NUMBER) IS
82 SELECT
83 SOURCE_SYSTEM_ID
84 ,START_DATE_ACTIVE
85 ,END_DATE_ACTIVE
86 ,OBJECT_VERSION_NUMBER
87 ,UOM_CODE
88 ,REVISION_ID
89 ,EPC_GTIN_SERIAL
90 ,INVENTORY_ITEM_ID
91 ,ORGANIZATION_ID
92 ,CROSS_REFERENCE_TYPE
93 ,CROSS_REFERENCE
94 ,ORG_INDEPENDENT_FLAG
95 ,REQUEST_ID
96 ,ATTRIBUTE1
97 ,ATTRIBUTE2
98 ,ATTRIBUTE3
99 ,ATTRIBUTE4
100 ,ATTRIBUTE5
101 ,ATTRIBUTE6
102 ,ATTRIBUTE7
103 ,ATTRIBUTE8
104 ,ATTRIBUTE9
105 ,ATTRIBUTE10
106 ,ATTRIBUTE11
107 ,ATTRIBUTE12
108 ,ATTRIBUTE13
109 ,ATTRIBUTE14
110 ,ATTRIBUTE15
111 ,ATTRIBUTE_CATEGORY
112 FROM MTL_CROSS_REFERENCES_B
113 WHERE CROSS_REFERENCE_ID=c_cross_reference_id
114 FOR UPDATE OF INVENTORY_ITEM_ID NOWAIT;
115
116
117 CURSOR mtl_xref_lock_tl(c_cross_reference_id NUMBER) IS
118 SELECT
119 DESCRIPTION
120 ,DECODE(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
121 FROM MTL_CROSS_REFERENCES_TL
122 WHERE CROSS_REFERENCE_ID = c_cross_reference_id
123 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
124 FOR UPDATE OF CROSS_REFERENCE_ID NOWAIT;
125
126
127
128 l_lock_b_recinfo mtl_xref_lock_b%ROWTYPE;
129 l_lock_tl_recinfo mtl_xref_lock_tl%ROWTYPE;
130
131 resource_busy EXCEPTION;
132 PRAGMA EXCEPTION_INIT (resource_busy, -54);
133
134 BEGIN
135
136 SAVEPOINT MTL_CROSS_REFERENCES_PVT;
137 X_return_status:= FND_API.G_RET_STS_SUCCESS ;
138
139 l_gtin_msg_count := 0;
140
141 l_XRef_Tbl := p_XRef_Tbl;
142 l_prod_exists := 'y';
143 l_pim_exists := 'y';
144
145 -- Initialize message list
146 IF FND_API.To_Boolean (p_init_msg_list) THEN
147 Error_Handler.Initialize;
148 END IF;
149 G_Xref_Indx := 0; -- making sure record index is reset
150 Write_Debug('Starting to process cross reference records...');
151 -- Looping all the records
152 FOR l_Xref_Indx IN p_XRef_Tbl.FIRST..p_XRef_Tbl.Last LOOP
153 l_XRef_Rec := l_XRef_Tbl(l_Xref_Indx);
154 G_Xref_Indx := l_Xref_Indx;
155 BEGIN -- Internal Begin
156 Write_Debug('Processing cross reference record...');
157 -- common validation section for INSERT/UPDATE/DELETE
158 -- Check for valid Transaction_Type
159 -- bug 14403169
160 IF (l_XRef_Rec.Transaction_Type IS NULL ) OR
161 (l_XRef_Rec.Transaction_Type <> 'CREATE' AND l_XRef_Rec.Transaction_Type <> 'UPDATE' AND
162 l_XRef_Rec.Transaction_Type <> 'DELETE') THEN
163
164 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
165 Error_Handler.Add_Error_Message
166 (
167 p_message_name => 'INV_XREF_INVALID_TRANS_TYPE'
168 ,p_application_id => 'INV'
169 ,p_message_type => 'E'
170 ,p_entity_code => G_Entity_Code
171 ,p_entity_index => l_Xref_Indx
172 ,p_table_name => G_Table_Name
173 );
174 RAISE VALIDATION_ERROR;
175
176 END IF;
177
178
179
180
181 -- checking for privileges
182 -- Verify for NON PIM Customer
183 BEGIN
184 SELECT 'x' INTO l_prod_exists
185 FROM fnd_grants
186 WHERE object_id IN
187 (SELECT object_id FROM fnd_objects
188 WHERE obj_name = 'EGO_ITEM');
189
190 EXCEPTION
191 WHEN Too_Many_Rows THEN
192
193 l_prod_exists := 'x';
194 WHEN No_Data_Found THEN
195 NULL;
196 END;
197
198 BEGIN
199 SELECT status INTO l_pim_exists
200 FROM FND_PRODUCT_INSTALLATIONS
201 WHERE application_id = 431;
202 EXCEPTION
203 WHEN No_Data_Found THEN
204 NULL;
205 END;
206
207 IF l_prod_exists = 'x' THEN
208 IF l_pim_exists = 'I' THEN
209 IF INV_EGO_REVISION_VALIDATE.check_data_security (
210 p_function => 'EGO_EDIT_ITEM_XREFS'
211 ,p_object_name => 'EGO_ITEM'
212 ,p_instance_pk1_value => l_XRef_Rec.Inventory_item_id
213 ,p_instance_pk2_value => FND_GLOBAL.org_id
214 ,P_User_Id => FND_GLOBAL.user_id) <> 'T' THEN
215
216 Error_Handler.Add_Error_Message
217 (
218 p_message_name => 'INV_IOI_ITEM_UPDATE_PRIV'
219 ,p_application_id => 'INV'
220 ,p_message_type => 'E'
221 ,p_entity_code => G_Entity_Code
222 ,p_entity_index => l_Xref_Indx
223 ,p_table_name => 'MTL_CROSS_REFERENCES'
224 );
225 RAISE VALIDATION_ERROR;
226 END IF;
227 END IF;
228 END IF;
229 -- checking for item existance in MSIB
230 IF l_XRef_Rec.Inventory_Item_Id IS NOT NULL
231 AND l_XRef_Rec.Inventory_Item_Id<>FND_API.G_MISS_NUM then
232
233 BEGIN
234 SELECT 'x' INTO l_exists
235 FROM mtl_system_items_b
236 WHERE inventory_item_id =l_XRef_Rec.Inventory_Item_Id;
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
240 Error_Handler.Add_Error_Message
241 (
242 p_message_name => 'INV-NO ITEM RECORD'
243 ,p_application_id => 'INV'
244 ,p_message_type => 'E'
245 ,p_entity_code => G_Entity_Code
246 ,p_entity_index => l_Xref_Indx
247 ,p_table_name => 'MTL_SYSTEM_ITEMS_B'
248 );
249 RAISE VALIDATION_ERROR;
250 WHEN Too_Many_Rows THEN
251 NULL;
252 END;
253 END IF;
254
255 -- Invalid value for org_indep_flag
256
257 IF l_XRef_rec.org_independent_flag NOT IN ('Y','N',FND_API.G_MISS_CHAR) THEN
258
259 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
260 Error_Handler.Add_Error_Message
261 (
262 p_message_name => 'INV_XREF_INVALID_ORG_FLAG'
263 ,p_application_id => 'INV'
264 ,p_message_type => 'E'
265 ,p_entity_code => G_Entity_Code
266 ,p_entity_index => l_Xref_Indx
267 ,p_table_name => 'MTL_CROSS_REFERENCE'
268 );
269 RAISE VALIDATION_ERROR;
270 END IF;
271
272 -- SS_ITEM_XREF not supported.
273 IF l_XRef_rec.Cross_Reference_Type ='SS_ITEM_XREF' THEN
274
275 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
276 Error_Handler.Add_Error_Message
277 (
278 p_message_name => 'INV_XREF_INVALID_TYPES'
279 ,p_application_id => 'INV'
280 ,p_message_type => 'E'
281 ,p_entity_code => G_Entity_Code
282 ,p_entity_index => l_Xref_Indx
283 ,p_table_name => 'MTL_CROSS_REFERENCE_TYPES'
284 );
285 RAISE VALIDATION_ERROR;
286 END IF;
287
288 -- cross reference type validations
289 IF l_XRef_Rec.Cross_Reference_Type IS NOT NULL
290 AND l_XRef_Rec.Cross_Reference_Type<> FND_API.G_MISS_CHAR THEN
291
292 BEGIN -- cross reference type existance
293 SELECT 'x' INTO l_exists
294 FROM MTL_CROSS_REFERENCE_TYPES
295 WHERE cross_reference_type =l_XRef_Rec.Cross_Reference_Type
296 AND trunc(nvl(disable_date,sysdate)) >= trunc(sysdate);
297 EXCEPTION
298 WHEN NO_DATA_FOUND THEN
299 l_XRef_Rec.x_return_status :=FND_API.g_RET_STS_ERROR;
300 l_Token_Tbl(1).Token_Name := 'CROSS_REFERENCE_TYPE';
301 l_Token_Tbl(1).Token_Value := l_XRef_Rec.Cross_Reference_Type;
302 l_Token_Tbl(1).Translate := FALSE;
303 Error_Handler.Add_Error_Message
304 (
305 p_message_name => 'INV_XREF_TYPE_INACTIVE'
306 ,p_application_id => 'INV'
307 ,p_token_tbl => l_Token_Tbl
308 ,p_message_type => 'E'
309 ,p_entity_code => G_Entity_Code
310 ,p_entity_index => l_Xref_Indx
311 ,p_table_name => 'MTL_CROSS_REFERENCE_TYPES'
312 );
313 RAISE VALIDATION_ERROR;
314 END;
315 END IF ;
316
317
318
319 IF l_XRef_Rec.Transaction_Type = 'CREATE' THEN
320
321 -- Should not leave PK columns blank and cannot pass NULL to these Columns.
322 IF l_XRef_Rec.Inventory_Item_Id IS NULL OR l_XRef_Rec.Inventory_Item_Id =FND_API.G_MISS_NUM
323 OR l_XRef_Rec.Cross_Reference_Type IS NULL OR l_XRef_Rec.Cross_Reference_Type =FND_API.G_MISS_CHAR
324 OR l_XRef_Rec.Cross_Reference IS NULL OR l_XRef_Rec.Cross_Reference=FND_API.G_MISS_CHAR THEN
325
326 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
327 Error_Handler.Add_Error_Message
328 (
329 p_message_name => 'INV_XREF_NULL_COLS'
330 ,p_application_id => 'INV'
331 ,p_message_type => 'E'
332 ,p_entity_code => G_Entity_Code
333 ,p_entity_index => l_Xref_Indx
334 ,p_table_name => G_Table_Name
335 );
336 RAISE VALIDATION_ERROR;
337 END IF;
338
339 -- organization validation -- both org_indp_flag and org_id should not be NOT NULL
340 IF l_XRef_Rec.Organization_Id IS NOT NULL
341 AND l_XRef_Rec.Organization_Id <>FND_API.G_MISS_NUM THEN
342
343 IF l_XRef_Rec.Org_Independent_Flag='Y' THEN
344 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
345 Error_Handler.Add_Error_Message
346 (
347 p_message_name => 'INV_XREF_ORG_FLAG'
348 ,p_application_id => 'INV'
349 ,p_message_type => 'E'
350 ,p_entity_code => G_Entity_Code
351 ,p_entity_index => l_Xref_Indx
352 ,p_table_name => G_Table_Name
353 );
354 RAISE VALIDATION_ERROR;
355 END IF ;
356 END IF;
357
358 -- Org_Id and Org_Indp_Flag both NULL.
359 IF l_XRef_Rec.Organization_Id IS NULL
360 OR l_XRef_Rec.Organization_Id = FND_API.G_MISS_NUM THEN
361
362 IF l_XRef_Rec.Org_Independent_Flag IS NULL
363 OR l_XRef_Rec.Org_Independent_Flag=FND_API.G_MISS_CHAR
364 OR l_XRef_Rec.Org_Independent_Flag <>'Y' THEN
365
366 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
367 Error_Handler.Add_Error_Message
368 (
369 p_message_name => 'INV_XREF_NULL_ORG'
370 ,p_application_id => 'INV'
371 ,p_message_type => 'E'
372 ,p_entity_code => G_Entity_Code
373 ,p_entity_index => l_Xref_Indx
374 ,p_table_name => G_Table_Name
375 );
376 RAISE VALIDATION_ERROR;
377 END IF ;
378 END IF;
379
380 -- checking for Item existance for given org.
381 IF l_XRef_Rec.Organization_Id IS NOT NULL
382 AND l_XRef_Rec.Organization_Id <>FND_API.G_MISS_NUM THEN
383
384 BEGIN
385 SELECT 'x' INTO l_exists
386 FROM mtl_system_items_b
387 WHERE Inventory_item_id = l_XRef_Rec.Inventory_Item_Id
388 AND Organization_id = l_XRef_Rec.Organization_Id;
389 EXCEPTION
390 WHEN NO_DATA_FOUND THEN
391 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
392 Error_Handler.Add_Error_Message
393 (
394 p_message_name => 'INV_INVALID_ITEM_ORG'
395 ,p_application_id => 'INV'
396 ,p_message_type => 'E'
397 ,p_entity_code => G_Entity_Code
398 ,p_entity_index => l_Xref_Indx
399 ,p_table_name => 'MTL_SYSTEM_ITEMS_B'
400 );
401 RAISE VALIDATION_ERROR;
402 END;
403 END IF ;
404
405 -- Checking for Revision_Id and UOM_Code Values
406
407 IF (l_XRef_Rec.Uom_Code IS NOT NULL
408 AND l_XRef_Rec.Uom_Code <> FND_API.G_MISS_CHAR) THEN
409
410 IF NVL(FND_PROFILE.VALUE('INV:GTIN_CROSS_REFERENCE_TYPE'),FND_API.G_MISS_CHAR) <> l_XRef_rec.Cross_Reference_Type THEN /* bug 14138918 */
411
412 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
413 Error_Handler.Add_Error_Message
414 (
415 p_message_name => 'INV_XREF_INVALID_COLUMN_VALUE'
416 ,p_application_id => 'INV'
417 ,p_message_type => 'E'
418 ,p_entity_code => G_Entity_Code
419 ,p_entity_index => l_Xref_Indx
420 ,p_table_name => G_Table_Name
421 );
422 RAISE VALIDATION_ERROR;
423
424 ELSE /*bug 14138918 start */
425 BEGIN
426
427 SELECT 'x' INTO l_uom_code_valid
428 FROM dual
429 WHERE
430 l_Xref_Rec.Uom_Code IN (
431 select Uom_code
432 from mtl_uom_conversions_view
433 where inventory_item_id = l_XRef_Rec.Inventory_Item_Id
434 and organization_id = Decode (l_XRef_Rec.organization_id,FND_API.G_MISS_CHAR,organization_id,NULL,organization_id,organization_id)
435 )
436 AND ROWNUM=1;
437
438 EXCEPTION
439 WHEN No_Data_Found THEN
440
441 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
442 Error_Handler.Add_Error_Message
443 (
444 p_message_name => 'INV_CCEOI_INVALID_UOM'
445 ,p_application_id => 'INV'
446 ,p_message_type => 'E'
447 ,p_entity_code => G_Entity_Code
448 ,p_entity_index => l_Xref_Indx
449 ,p_table_name => G_Table_Name
450 );
451 RAISE VALIDATION_ERROR;
452 END ;
453 END IF; /* bug 14138918 end */
454 END IF; /* end if of UOM_Code */
455
456 IF (l_XRef_Rec.Revision_Id IS NOT NULL
457 AND l_XRef_Rec.Revision_Id <> FND_API.G_MISS_NUM) THEN
458
459 -- revision id can only have valus when Cross_Reference_Type is equal to profile option INV:GTIN_CROSS_REFERENCE_TYPE value
460 -- and org_independent flag should not be checked
461 -- and organization_id should be provided.
462
463 IF NVL(FND_PROFILE.VALUE('INV:GTIN_CROSS_REFERENCE_TYPE'),FND_API.G_MISS_CHAR) <> l_XRef_rec.Cross_Reference_Type
464 OR (l_XRef_Rec.Org_Independent_Flag <>'N' OR l_XRef_Rec.Org_Independent_Flag IS NULL OR l_XRef_Rec.Org_Independent_Flag=FND_API.G_MISS_CHAR)
465 OR (l_XRef_Rec.Organization_Id IS NULL OR l_XRef_Rec.Organization_Id = FND_API.G_MISS_NUM)
466 THEN /* bug 14138918 */
467
468 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
469 Error_Handler.Add_Error_Message
470 (
471 p_message_name => 'INV_XREF_INVALID_COLUMN_VALUE'
472 ,p_application_id => 'INV'
473 ,p_message_type => 'E'
474 ,p_entity_code => G_Entity_Code
475 ,p_entity_index => l_Xref_Indx
476 ,p_table_name => G_Table_Name
477 );
478 RAISE VALIDATION_ERROR;
479 ELSE /*bug 14138918 start */
480 BEGIN
481 SELECT 'x' INTO l_rev_id_invalid
482 FROM dual
483 WHERE
484 l_Xref_Rec.Revision_Id IN (
485 select item_rev.revision_id
486 from mtl_item_revisions_vl item_rev
487 where item_rev.inventory_item_id = l_XRef_Rec.Inventory_Item_Id
488 and item_rev.organization_id = l_XRef_Rec.organization_id
489 )
490 AND ROWNUM=1;
491
492 EXCEPTION
493 WHEN No_Data_Found THEN
494
495 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
496 Error_Handler.Add_Error_Message
497 (
498 p_message_name => 'INV_INT_REVCODE'
499 ,p_application_id => 'INV'
500 ,p_message_type => 'E'
501 ,p_entity_code => G_Entity_Code
502 ,p_entity_index => l_Xref_Indx
503 ,p_table_name => G_Table_Name
504 );
505 RAISE VALIDATION_ERROR;
506 END ;
507 END IF; /* bug 14138918 end */
508
509 END IF ;/* end if of Revision_Id */
510
511 -- checking for duplicate record
512 BEGIN
513
514 SELECT 'x' INTO l_XRef_exists
515 FROM mtl_cross_references
516 WHERE Cross_Reference_Type = l_XRef_Rec.Cross_Reference_Type
517 AND Inventory_Item_Id = l_XRef_Rec.Inventory_Item_Id
518 AND Cross_Reference =l_XRef_Rec.Cross_Reference
519 AND Decode(Organization_Id,NULL,Org_Independent_Flag,Organization_Id) =
520 Decode(nvl(l_XRef_Rec.Organization_Id,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,l_XRef_Rec.Org_Independent_Flag,l_XRef_Rec.Organization_Id);
521
522 --record already Exists
523 IF l_XRef_exists='x' THEN
524
525 l_Token_Tbl(1).Token_Name := 'INVENTORY_ITEM_ID';
526 l_Token_Tbl(1).Token_Value := l_XRef_Rec.Inventory_Item_Id;
527 l_Token_Tbl(1).Translate := FALSE;
528 l_Token_Tbl(2).Token_Name := 'CROSS_REFERENCE_TYPE';
529 l_Token_Tbl(2).Token_Value := l_XRef_Rec.CROSS_REFERENCE_TYPE;
530 l_Token_Tbl(2).Translate := FALSE;
531 l_Token_Tbl(3).Token_Name := 'CROSS_REFERENCE';
532 l_Token_Tbl(3).Token_Value := l_XRef_Rec.CROSS_REFERENCE;
533 l_Token_Tbl(3).Translate := FALSE;
534
535 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
536 Error_Handler.Add_Error_Message
537 (
538 p_message_name => 'INV_XREF_PRIMARY_KEY_VIOLATED'
539 ,p_application_id => 'INV'
540 ,p_token_tbl => l_Token_Tbl
541 ,p_message_type => 'E'
542 ,p_entity_code => G_Entity_Code
543 ,p_entity_index => l_Xref_Indx
544 ,p_table_name => G_Table_Name
545 );
546 RAISE VALIDATION_ERROR;
547 END IF;
548 EXCEPTION
549 WHEN NO_DATA_FOUND THEN
550 NULL;
551 END;
552 --
553 -- calling GTIN specific validations
554 --
555 IF l_XRef_Rec.Cross_Reference_Type = 'GTIN' THEN
556
557 Validate_GTIN_Rec(
558 p_init_msg_list => FND_API.G_FALSE
559 ,p_commit => FND_API.G_FALSE
560 ,p_GTIN_XRef_Rec => l_XRef_Rec
561 ,x_return_status => l_gtin_return_status
562 ,x_msg_count => l_gtin_msg_count);
563 IF l_gtin_return_status = FND_API.G_RET_STS_ERROR THEN
564 RAISE VALIDATION_ERROR;
565 END IF;
566
567 IF l_gtin_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
568 RAISE UNEXP_VALIDATION_ERROR;
569 END IF;
570
571 END IF;
572
573 --Changing Gloabal variable back to NULL before inserting.
574 SELECT Decode(l_XRef_Rec.Inventory_Item_Id,FND_API.G_MISS_NUM,NULL,l_XRef_Rec.Inventory_Item_Id),
575 Decode(l_XRef_Rec.Organization_Id,FND_API.G_MISS_NUM,NULL,l_XRef_Rec.Organization_Id),
576 Decode(l_XRef_Rec.Cross_Reference_Type,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Cross_Reference_Type),
577 Decode(l_XRef_Rec.Cross_Reference,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Cross_Reference),
578 Decode(l_XRef_Rec.Description,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Description),
579 Decode(l_XRef_Rec.Org_Independent_Flag,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Org_Independent_Flag),
580 Decode(l_XRef_Rec.Last_Update_Date,FND_API.G_MISS_DATE,NULL,l_XRef_Rec.Last_Update_Date),
581 Decode(l_XRef_Rec.Last_Updated_By,FND_API.G_MISS_NUM,NULL,l_XRef_Rec.Last_Updated_By),
582 Decode(l_XRef_Rec.Creation_Date,FND_API.G_MISS_DATE,NULL,l_XRef_Rec.Creation_Date),
583 Decode(l_XRef_Rec.Created_By,FND_API.G_MISS_NUM,NULL,l_XRef_Rec.Created_By),
584 Decode(l_XRef_Rec.Last_Update_Login,FND_API.G_MISS_NUM,NULL,l_XRef_Rec.Last_Update_Login),
585 Decode(l_XRef_Rec.Request_id,FND_API.G_MISS_NUM,NULL,l_XRef_Rec.Request_id),
586 Decode(l_XRef_Rec.Program_Application_Id,FND_API.G_MISS_NUM,NULL,l_XRef_Rec.Program_Application_Id),
587 Decode(l_XRef_Rec.Program_Id,FND_API.G_MISS_NUM,NULL,l_XRef_Rec.Program_Id),
588 Decode(l_XRef_Rec.Program_Update_Date,FND_API.G_MISS_DATE,NULL,l_XRef_Rec.Program_Update_Date),
589 Decode(l_XRef_Rec.Attribute1,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute1),
590 Decode(l_XRef_Rec.Attribute2,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute2),
591 Decode(l_XRef_Rec.Attribute3,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute3),
592 Decode(l_XRef_Rec.Attribute4,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute4),
593 Decode(l_XRef_Rec.Attribute5,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute5),
594 Decode(l_XRef_Rec.Attribute6,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute6),
595 Decode(l_XRef_Rec.Attribute7,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute7),
596 Decode(l_XRef_Rec.Attribute8,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute8),
597 Decode(l_XRef_Rec.Attribute9,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute9),
598 Decode(l_XRef_Rec.Attribute10,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute10),
599 Decode(l_XRef_Rec.Attribute11,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute11),
600 Decode(l_XRef_Rec.Attribute12,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute12),
601 Decode(l_XRef_Rec.Attribute13,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute13),
602 Decode(l_XRef_Rec.Attribute14,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute14),
603 Decode(l_XRef_Rec.Attribute15,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute15),
604 Decode(l_XRef_Rec.Attribute_category,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Attribute_category),
605 Decode(l_XRef_Rec.Uom_Code,FND_API.G_MISS_CHAR,NULL,l_XRef_Rec.Uom_Code),
606 Decode(l_XRef_Rec.Revision_Id,FND_API.G_MISS_NUM,NULL,l_XRef_Rec.Revision_Id),
607 Decode(l_XRef_Rec.Epc_Gtin_Serial,FND_API.G_MISS_NUM,NULL,l_XRef_Rec.Epc_Gtin_Serial)
608
609 INTO l_XRef_Rec.Inventory_Item_Id,l_XRef_Rec.Organization_Id, l_XRef_Rec.Cross_Reference_Type,l_XRef_Rec.Cross_Reference,
610 l_XRef_Rec.Description,l_XRef_Rec.Org_Independent_Flag,l_XRef_Rec.Last_Update_Date,l_XRef_Rec.Last_Updated_By,l_XRef_Rec.Creation_Date,
611 l_XRef_Rec.Created_By,l_XRef_Rec.Last_Update_Login,l_XRef_Rec.Request_id,l_XRef_Rec.Program_Application_Id,l_XRef_Rec.Program_Id,
612 l_XRef_Rec.Program_Update_Date,l_XRef_Rec.Attribute1,l_XRef_Rec.Attribute2,l_XRef_Rec.Attribute3,l_XRef_Rec.Attribute4,
613 l_XRef_Rec.Attribute5,l_XRef_Rec.Attribute6,l_XRef_Rec.Attribute7,l_XRef_Rec.Attribute8,l_XRef_Rec.Attribute9,l_XRef_Rec.Attribute10,
614 l_XRef_Rec.Attribute11,l_XRef_Rec.Attribute12,l_XRef_Rec.Attribute13,l_XRef_Rec.Attribute14,l_XRef_Rec.Attribute15,
615 l_XRef_Rec.Attribute_category,l_XRef_Rec.Uom_Code,l_XRef_Rec.Revision_Id, l_XRef_Rec.Epc_Gtin_Serial
616 FROM dual;
617
618 MTL_CROSS_REFERENCES_PKG.INSERT_ROW(
619 P_UOM_CODE => l_XRef_Rec.Uom_Code /*bug 14138918 */
620 ,P_REVISION_ID => l_Xref_Rec.Revision_Id /*bug 14138918 */
621 ,P_INVENTORY_ITEM_ID => l_XRef_Rec.Inventory_Item_Id
622 ,P_ORGANIZATION_ID => l_XRef_Rec.Organization_Id
623 ,P_CROSS_REFERENCE_TYPE => l_XRef_Rec.Cross_Reference_Type
624 ,P_CROSS_REFERENCE => l_XRef_Rec.Cross_Reference
625 ,P_ORG_INDEPENDENT_FLAG => Nvl(l_XRef_Rec.Org_Independent_Flag,'N')
626 ,P_REQUEST_ID => l_XRef_Rec.Request_Id
627 ,P_ATTRIBUTE1 => l_XRef_Rec.Attribute1
628 ,P_ATTRIBUTE2 => l_XRef_Rec.Attribute2
629 ,P_ATTRIBUTE3 => l_XRef_Rec.Attribute3
630 ,P_ATTRIBUTE4 => l_XRef_Rec.Attribute4
631 ,P_ATTRIBUTE5 => l_XRef_Rec.Attribute5
632 ,P_ATTRIBUTE6 => l_XRef_Rec.Attribute6
633 ,P_ATTRIBUTE7 => l_XRef_Rec.Attribute7
634 ,P_ATTRIBUTE8 => l_XRef_Rec.Attribute8
635 ,P_ATTRIBUTE9 => l_XRef_Rec.Attribute9
636 ,P_ATTRIBUTE10 => l_XRef_Rec.Attribute10
637 ,P_ATTRIBUTE11 => l_XRef_Rec.Attribute11
638 ,P_ATTRIBUTE12 => l_XRef_Rec.Attribute12
639 ,P_ATTRIBUTE13 => l_XRef_Rec.Attribute13
640 ,P_ATTRIBUTE14 => l_XRef_Rec.Attribute14
641 ,P_ATTRIBUTE15 => l_XRef_Rec.Attribute15
642 ,P_ATTRIBUTE_CATEGORY => l_XRef_Rec.Attribute_category
643 ,P_DESCRIPTION => l_XRef_Rec.Description
644 ,P_CREATION_DATE => Nvl(l_XRef_Rec.Creation_Date,SYSDATE)
645 ,P_CREATED_BY => Nvl(l_XRef_Rec.Created_By,FND_GLOBAL.USER_ID)
646 ,P_LAST_UPDATE_DATE => Nvl(l_XRef_Rec.Last_Update_Date,SYSDATE)
647 ,P_LAST_UPDATED_BY => Nvl(l_XRef_Rec.Last_Updated_By,FND_GLOBAL.USER_ID)
648 ,P_LAST_UPDATE_LOGIN => Nvl(l_XRef_Rec.Last_Update_Login,FND_GLOBAL.LOGIN_ID)
649 ,P_PROGRAM_APPLICATION_ID=> NULL
650 ,P_PROGRAM_ID => NULL
651 ,P_PROGRAM_UPDATE_DATE => NULL
652 ,P_EPC_GTIN_SERIAL =>l_XRef_Rec.Epc_Gtin_Serial
653 ,P_SOURCE_SYSTEM_ID => NULL
654 ,P_START_DATE_ACTIVE => NULL
655 ,P_END_DATE_ACTIVE => NULL
656 ,P_OBJECT_VERSION_NUMBER =>NULL
657 ,X_CROSS_REFERENCE_ID =>returned_cross_ref_id
658 );
659
660 --Bug 9749717 : Raise Business Event
661 BEGIN
662 INV_ITEM_EVENTS_PVT.Raise_Events(
663 p_event_name => 'EGO_WF_WRAPPER_PVT.G_Xref_CHANGE_EVENT'
664 ,p_dml_type => 'CREATE'
665 ,p_inventory_item_id => l_XRef_Rec.Inventory_Item_Id
666 ,p_organization_id => l_XRef_Rec.Organization_Id
667 ,p_cross_reference_type => l_XRef_Rec.Cross_Reference_Type
668 ,p_cross_reference => l_XRef_Rec.Cross_Reference
669 );
670 EXCEPTION
671 WHEN OTHERS THEN
672 NULL;
673 END;
674
675
676 ELSIF l_XRef_Rec.Transaction_Type = 'UPDATE' THEN
677
678 -- Current cols should not be NULL.
679 IF l_XRef_Rec.CROSS_REFERENCE_ID =FND_API.G_MISS_NUM THEN
680
681 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR; -- assigning the record status as error
682 Error_Handler.Add_Error_Message
683 (
684 p_message_name => 'INV_XREF_ID_NULL'
685 ,p_application_id => 'INV'
686 ,p_message_type => 'E'
687 ,p_entity_code => G_Entity_Code
688 ,p_entity_index => l_Xref_Indx
689 ,p_table_name => G_Table_Name
690 );
691 RAISE VALIDATION_ERROR;
692 END IF;
693
694 -- removing as only cross_reference_id is required for update
695 --
696 -- cannot pass NULL to these Columns
697 /*IF l_XRef_Rec.Inventory_Item_Id IS NULL
698 OR l_XRef_Rec.Cross_Reference_Type IS NULL
699 OR l_XRef_Rec.Cross_Reference IS NULL THEN
700
701 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR; -- assigning the record status as error
702 Error_Handler.Add_Error_Message
703 (
704 p_message_name => 'INV_XREF_NULL_COLS'
705 ,p_application_id => 'INV'
706 ,p_message_type => 'E'
707 ,p_entity_code => G_Entity_Code
708 ,p_entity_index => l_Xref_Indx
709 ,p_table_name => G_Table_Name
710 );
711 RAISE VALIDATION_ERROR;
712 END IF;*/
713 -- removing as only cross_reference_id is required for update
714
715 -- getting original values and checking for existance of record
716 OPEN mtl_xref_cur(l_XRef_Rec.CROSS_REFERENCE_ID);
717 FETCH mtl_xref_cur INTO l_mtl_XRef_rec;
718 IF mtl_xref_cur%NOTFOUND THEN
719 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
720 Error_Handler.Add_Error_Message
721 (
722 p_message_name => 'INV_XREF_ID_NOTEXISTS'
723 ,p_application_id => 'INV'
724 ,p_message_type => 'E'
725 ,p_entity_code => G_Entity_Code
726 ,p_entity_index => l_Xref_Indx
727 ,p_table_name => G_Table_Name
728 );
729 CLOSE mtl_xref_cur;
730 RAISE VALIDATION_ERROR;
731 END IF;
732 CLOSE mtl_xref_cur;
733
734 --
735 -- Converting global values(values left blank) back to original values.
736 -- To nullify columns during update through interface route, use -999999 for numbers, '!' for chars, '31-Dec-1999' for dates
737 --
738 SELECT Decode(Nvl(l_XRef_Rec.Inventory_Item_Id, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,l_mtl_XRef_Rec.Inventory_Item_Id,-999999,NULL,l_XRef_Rec.Inventory_Item_Id),
739 Decode(Nvl(l_XRef_Rec.Organization_Id, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,l_mtl_XRef_Rec.Organization_Id,-999999,NULL,l_XRef_Rec.Organization_Id),
740 Decode(Nvl(l_XRef_Rec.Cross_Reference_Type, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Cross_Reference_Type,'!',NULL,l_XRef_Rec.Cross_Reference_Type),
741 Decode(Nvl(l_XRef_Rec.Cross_Reference, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Cross_Reference,'!',NULL,l_XRef_Rec.Cross_Reference),
742 Decode(Nvl(l_XRef_Rec.Description, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Description,'!',NULL,l_XRef_Rec.Description),
743 Decode(Nvl(l_XRef_Rec.Org_Independent_Flag, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Org_Independent_Flag,'!',NULL,l_XRef_Rec.Org_Independent_Flag),
744 Decode(Nvl(l_XRef_Rec.Last_Update_Date, FND_API.G_MISS_DATE),FND_API.G_MISS_DATE,NULL,l_XRef_Rec.Last_Update_Date),
745 Decode(Nvl(l_XRef_Rec.Last_Updated_By, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,NULL,-999999,NULL,l_XRef_Rec.Last_Updated_By),
746 Decode(Nvl(l_XRef_Rec.Creation_Date, FND_API.G_MISS_DATE),FND_API.G_MISS_DATE,l_mtl_XRef_Rec.Creation_Date,l_XRef_Rec.Creation_Date),
747 Decode(Nvl(l_XRef_Rec.Created_By, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,l_mtl_XRef_Rec.Created_By,-999999,NULL,l_XRef_Rec.Created_By),
748 Decode(Nvl(l_XRef_Rec.Last_Update_Login, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,NULL,-999999,NULL,l_XRef_Rec.Last_Update_Login),
749 Decode(Nvl(l_XRef_Rec.Request_id, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,l_mtl_XRef_Rec.Request_id,-999999,NULL,l_XRef_Rec.Request_id),
750 Decode(Nvl(l_XRef_Rec.Program_Application_Id, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,l_mtl_XRef_Rec.Program_Application_Id,-999999,NULL,l_XRef_Rec.Program_Application_Id),
751 Decode(Nvl(l_XRef_Rec.Program_Id, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,l_XRef_Rec.Program_Id,-999999,NULL,l_mtl_XRef_Rec.Program_Id),
752 Decode(Nvl(l_XRef_Rec.Program_Update_Date, FND_API.G_MISS_DATE),FND_API.G_MISS_DATE,l_mtl_XRef_Rec.Program_Update_Date,l_XRef_Rec.Program_Update_Date),
753 Decode(Nvl(l_XRef_Rec.Attribute1, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute1,'!',NULL,l_XRef_Rec.Attribute1),
754 Decode(Nvl(l_XRef_Rec.Attribute2, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute2,'!',NULL,l_XRef_Rec.Attribute2),
755 Decode(Nvl(l_XRef_Rec.Attribute3, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute3,'!',NULL,l_XRef_Rec.Attribute3),
756 Decode(Nvl(l_XRef_Rec.Attribute4, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute4,'!',NULL,l_XRef_Rec.Attribute4),
757 Decode(Nvl(l_XRef_Rec.Attribute5, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute5,'!',NULL,l_XRef_Rec.Attribute5),
758 Decode(Nvl(l_XRef_Rec.Attribute6, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute6,'!',NULL,l_XRef_Rec.Attribute6),
759 Decode(Nvl(l_XRef_Rec.Attribute7, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute7,'!',NULL,l_XRef_Rec.Attribute7),
760 Decode(Nvl(l_XRef_Rec.Attribute8, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute8,'!',NULL,l_XRef_Rec.Attribute8),
761 Decode(Nvl(l_XRef_Rec.Attribute9, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute9,'!',NULL,l_XRef_Rec.Attribute9),
762 Decode(Nvl(l_XRef_Rec.Attribute10, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute10,'!',NULL,l_XRef_Rec.Attribute10),
763 Decode(Nvl(l_XRef_Rec.Attribute11, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute11,'!',NULL,l_XRef_Rec.Attribute11),
764 Decode(Nvl(l_XRef_Rec.Attribute12, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute12,'!',NULL,l_XRef_Rec.Attribute12),
765 Decode(Nvl(l_XRef_Rec.Attribute13, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute13,'!',NULL,l_XRef_Rec.Attribute13),
766 Decode(Nvl(l_XRef_Rec.Attribute14, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute14,'!',NULL,l_XRef_Rec.Attribute14),
767 Decode(Nvl(l_XRef_Rec.Attribute15, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute15,'!',NULL,l_XRef_Rec.Attribute15),
768 Decode(Nvl(l_XRef_Rec.Attribute_category, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Attribute_category,'!',NULL,l_XRef_Rec.Attribute_category),
769 Decode(Nvl(l_XRef_Rec.Uom_Code, FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR,l_mtl_XRef_Rec.Uom_Code,'!',NULL,l_XRef_Rec.Uom_Code),
770 Decode(Nvl(l_XRef_Rec.Revision_Id, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,l_mtl_XRef_Rec.Revision_Id,-999999,NULL,l_XRef_Rec.Revision_Id),
771 Decode(Nvl(l_XRef_Rec.Epc_Gtin_Serial, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,l_mtl_XRef_Rec.Epc_Gtin_Serial,-999999,NULL,l_XRef_Rec.Epc_Gtin_Serial)
772 INTO l_XRef_Rec.Inventory_Item_Id,l_XRef_Rec.Organization_Id, l_XRef_Rec.Cross_Reference_Type,l_XRef_Rec.Cross_Reference,
773 l_XRef_Rec.Description,l_XRef_Rec.Org_Independent_Flag,l_XRef_Rec.Last_Update_Date,l_XRef_Rec.Last_Updated_By,l_XRef_Rec.Creation_Date,
774 l_XRef_Rec.Created_By,l_XRef_Rec.Last_Update_Login,l_XRef_Rec.Request_id,l_XRef_Rec.Program_Application_Id,l_XRef_Rec.Program_Id,
775 l_XRef_Rec.Program_Update_Date,l_XRef_Rec.Attribute1,l_XRef_Rec.Attribute2,l_XRef_Rec.Attribute3,l_XRef_Rec.Attribute4,
776 l_XRef_Rec.Attribute5,l_XRef_Rec.Attribute6,l_XRef_Rec.Attribute7,l_XRef_Rec.Attribute8,l_XRef_Rec.Attribute9,l_XRef_Rec.Attribute10,
777 l_XRef_Rec.Attribute11,l_XRef_Rec.Attribute12,l_XRef_Rec.Attribute13,l_XRef_Rec.Attribute14,l_XRef_Rec.Attribute15,
778 l_XRef_Rec.Attribute_category,l_XRef_Rec.Uom_Code,l_XRef_Rec.Revision_Id,l_XRef_Rec.Epc_Gtin_Serial
779
780 FROM dual;
781
782 -- organization validation -- both org_indp_flag and org_id NOT NULL
783 IF l_XRef_Rec.Organization_Id IS NOT NULL THEN
784
785 IF l_XRef_Rec.Org_Independent_Flag='Y' THEN
786 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
787 Error_Handler.Add_Error_Message
788 (
789 p_message_name => 'INV_XREF_ORG_FLAG'
790 ,p_application_id => 'INV'
791 ,p_message_type => 'E'
792 ,p_entity_code => G_Entity_Code
793 ,p_entity_index => l_Xref_Indx
794 ,p_table_name => G_Table_Name
795 );
796 RAISE VALIDATION_ERROR;
797 END IF ;
798 END IF;
799
800 -- if org_id and org_indp_flag both NULL
801 IF l_XRef_Rec.Organization_Id IS NULL THEN
802
803 IF l_XRef_Rec.Org_Independent_Flag IS NULL
804 OR l_XRef_Rec.Org_Independent_Flag = FND_API.G_MISS_CHAR THEN
805
806 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
807 Error_Handler.Add_Error_Message
808 (
809 p_message_name => 'INV_XREF_NULL_ORG'
810 ,p_application_id => 'INV'
811 ,p_message_type => 'E'
812 ,p_entity_code => G_Entity_Code
813 ,p_entity_index => l_Xref_Indx
814 ,p_table_name => G_Table_Name
815 );
816 RAISE VALIDATION_ERROR;
817 END IF;
818 END IF ;
819
820 -- checking for Item existance for given org.
821 IF l_XRef_Rec.Organization_Id IS NOT NULL
822 AND l_XRef_Rec.Organization_Id <>FND_API.G_MISS_NUM
823 AND l_XRef_Rec.Inventory_Item_Id <>FND_API.G_MISS_NUM THEN
824
825 BEGIN
826 SELECT 'x' INTO l_exists
827 FROM mtl_system_items_b
828 WHERE Inventory_item_id = l_XRef_Rec.Inventory_Item_Id
829 AND Organization_id = l_XRef_Rec.Organization_Id;
830 EXCEPTION
831 WHEN NO_DATA_FOUND THEN
832 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
833 Error_Handler.Add_Error_Message
834 (
835 p_message_name => 'INV_INVALID_ITEM_ORG'
836 ,p_application_id => 'INV'
837 ,p_message_type => 'E'
838 ,p_entity_code => G_Entity_Code
839 ,p_entity_index => l_Xref_Indx
840 ,p_table_name => G_Table_Name
841 );
842 RAISE VALIDATION_ERROR;
843 END;
844 END IF;
845
846 -- Checking for Revision_Id and UOM_Cde Values
847
848 IF (l_XRef_Rec.Uom_Code IS NOT NULL
849 AND l_XRef_Rec.Uom_Code <> FND_API.G_MISS_CHAR) THEN
850
851 IF NVL(FND_PROFILE.VALUE('INV:GTIN_CROSS_REFERENCE_TYPE'),FND_API.G_MISS_CHAR) <> l_mtl_XRef_rec.CROSS_REFERENCE_TYPE THEN /* bug 14138918 */
852 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
853 Error_Handler.Add_Error_Message
854 (
855 p_message_name => 'INV_XREF_INVALID_COLUMN_VALUE'
856 ,p_application_id => 'INV'
857 ,p_message_type => 'E'
858 ,p_entity_code => G_Entity_Code
859 ,p_entity_index => l_Xref_Indx
860 ,p_table_name => G_Table_Name
861 );
862 RAISE VALIDATION_ERROR;
863 ELSE /*bug 14138918 start */
864 BEGIN
865 SELECT 'x' INTO l_uom_code_valid
866 FROM dual
867 WHERE
868 l_Xref_Rec.Uom_Code IN (
869 select Uom_code
870 from mtl_uom_conversions_view
871 where inventory_item_id = l_mtl_XRef_rec.INVENTORY_ITEM_ID
872 and organization_id = Decode (l_mtl_XRef_rec.Organization_Id,FND_API.G_MISS_CHAR,organization_id,NULL,organization_id,organization_id)
873 )
874 AND ROWNUM=1;
875 EXCEPTION
876 WHEN No_Data_Found THEN
877
878 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
879 Error_Handler.Add_Error_Message
880 (
881 p_message_name => 'INV_CCEOI_INVALID_UOM'
882 ,p_application_id => 'INV'
883 ,p_message_type => 'E'
884 ,p_entity_code => G_Entity_Code
885 ,p_entity_index => l_Xref_Indx
886 ,p_table_name => G_Table_Name
887 );
888 RAISE VALIDATION_ERROR;
889 END ;
890 END IF; /* bug 14138918 end */
891 END IF; /* end if of uom_code*/
892
893
894 IF (l_XRef_Rec.Revision_Id IS NOT NULL
895 AND l_XRef_Rec.Revision_Id <> FND_API.G_MISS_NUM) THEN
896
897 IF NVL(FND_PROFILE.VALUE('INV:GTIN_CROSS_REFERENCE_TYPE'),FND_API.G_MISS_CHAR) <> l_mtl_XRef_rec.CROSS_REFERENCE_TYPE
898 OR ((l_XRef_Rec.Org_Independent_Flag <>'N') AND (l_XRef_Rec.Org_Independent_Flag <>FND_API.G_MISS_CHAR))
899 OR ((l_XRef_Rec.Org_Independent_Flag IS NULL ))
900 OR ((l_XRef_Rec.Org_Independent_Flag = FND_API.G_MISS_CHAR) AND (l_mtl_XRef_rec.Org_Independent_Flag <>'N'))
901 OR ((l_XRef_Rec.Organization_Id IS NULL))
902 OR ((l_XRef_Rec.Organization_Id = FND_API.G_MISS_NUM) AND (l_mtl_XRef_rec.Organization_Id IS NULL ))
903 THEN /* bug 14138918 */
904
905 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
906 Error_Handler.Add_Error_Message
907 (
908 p_message_name => 'INV_XREF_INVALID_COLUMN_VALUE'
909 ,p_application_id => 'INV'
910 ,p_message_type => 'E'
911 ,p_entity_code => G_Entity_Code
912 ,p_entity_index => l_Xref_Indx
913 ,p_table_name => G_Table_Name
914 );
915 RAISE VALIDATION_ERROR;
916 ELSE /*bug 14138918 start */
917 BEGIN
918 SELECT 'x' INTO l_rev_id_invalid
919 FROM dual
920 WHERE
921 l_Xref_Rec.Revision_Id IN (
922 select item_rev.revision_id
923 from mtl_item_revisions_vl item_rev
924 where item_rev.inventory_item_id = Decode(l_XRef_Rec.Inventory_Item_Id,FND_API.G_MISS_NUM,l_mtl_XRef_rec.Inventory_Item_Id,l_XRef_Rec.Inventory_Item_Id)
925 and item_rev.organization_id = Decode(l_XRef_Rec.organization_id,FND_API.G_MISS_NUM, l_mtl_XRef_rec.Organization_Id,l_XRef_Rec.organization_id)
926 )
927 AND ROWNUM=1;
928
929 EXCEPTION
930 WHEN No_Data_Found THEN
931 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
932 Error_Handler.Add_Error_Message
933 (
934 p_message_name => 'INV_INT_REVCODE'
935 ,p_application_id => 'INV'
936 ,p_message_type => 'E'
937 ,p_entity_code => G_Entity_Code
938 ,p_entity_index => l_Xref_Indx
939 ,p_table_name => G_Table_Name
940 );
941 RAISE VALIDATION_ERROR ;
942 END ;
943 END IF; /* bug 14138918 end */
944 END IF ; /* end if of revision_id*/
945
946 --
947 -- calling GTIN specific validations
948 --
949 IF l_XRef_Rec.Cross_Reference_Type = 'GTIN' THEN
950
951 Validate_GTIN_Rec(
952 p_init_msg_list => FND_API.G_FALSE
953 ,p_commit => FND_API.G_FALSE
954 ,p_GTIN_XRef_Rec => l_XRef_Rec
955 ,x_return_status => l_gtin_return_status
956 ,x_msg_count => l_gtin_msg_count);
957
958 IF l_gtin_return_status = FND_API.G_RET_STS_ERROR THEN
959 RAISE VALIDATION_ERROR;
960 END IF;
961
962 IF l_gtin_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
963 RAISE UNEXP_VALIDATION_ERROR;
964 END IF;
965
966 END IF;
967
968 -- checking for duplicate record
969 IF l_mtl_XRef_Rec.Inventory_Item_Id <> l_XRef_Rec.Inventory_Item_Id
970 OR l_mtl_XRef_Rec.Cross_Reference_Type <> l_XRef_Rec.Cross_Reference_Type
971 OR l_mtl_XRef_Rec.Cross_Reference <> l_XRef_Rec.Cross_Reference
972 OR Nvl(l_mtl_XRef_Rec.Organization_Id,0) <> l_XRef_Rec.Organization_Id
973 OR l_mtl_XRef_Rec.Org_Independent_Flag <> l_XRef_Rec.Org_Independent_Flag THEN
974
975 BEGIN
976 SELECT 'x' INTO l_XRef_exists
977 FROM mtl_cross_references
978 WHERE Cross_Reference_Type = l_XRef_Rec.Cross_Reference_Type
979 AND Inventory_Item_Id = l_XRef_Rec.Inventory_Item_Id
980 AND Cross_Reference = l_XRef_Rec.Cross_Reference
981 AND Decode(Organization_Id,NULL,Org_Independent_Flag,Organization_Id) =
982 Decode(l_XRef_Rec.Organization_Id,NULL,l_XRef_Rec.Org_Independent_Flag,l_XRef_Rec.Organization_Id)
983 AND Cross_Reference_Id <> l_XRef_Rec.Cross_Reference_Id;
984
985 --record already Exists
986 IF l_XRef_exists='x' THEN
987 l_XRef_Rec.x_return_status := FND_API.g_RET_STS_ERROR;
988 l_Token_Tbl(1).Token_Name := 'INVENTORY_ITEM_ID';
989 l_Token_Tbl(1).Token_Value := l_XRef_Rec.Inventory_Item_Id;
990 l_Token_Tbl(1).Translate := FALSE;
991 l_Token_Tbl(2).Token_Name := 'CROSS_REFERENCE_TYPE';
992 l_Token_Tbl(2).Token_Value := l_XRef_Rec.Cross_Reference_Type;
993 l_Token_Tbl(2).Translate := FALSE;
994 l_Token_Tbl(3).Token_Name := 'CROSS_REFERENCE';
995 l_Token_Tbl(3).Token_Value := l_XRef_Rec.Cross_Reference;
996 l_Token_Tbl(3).Translate := FALSE;
997
998 Error_Handler.Add_Error_Message
999 (
1000 p_message_name => 'INV_XREF_PRIMARY_KEY_VIOLATED'
1001 ,p_application_id => 'INV'
1002 ,p_token_tbl => l_Token_Tbl
1003 ,p_message_type => 'E'
1004 ,p_entity_code => G_Entity_Code
1005 ,p_entity_index => l_Xref_Indx
1006 ,p_table_name => G_Table_Name
1007 );
1008 RAISE VALIDATION_ERROR;
1009 END IF;
1010 EXCEPTION
1011 WHEN NO_DATA_FOUND THEN
1012 NULL;
1013 END;
1014 END IF ;
1015
1016 -- locking the row
1017 OPEN mtl_xref_lock_b(l_XRef_Rec.CROSS_REFERENCE_ID);
1018 FETCH mtl_xref_lock_b INTO l_lock_b_recinfo ;
1019 CLOSE mtl_xref_lock_b;
1020
1021 OPEN mtl_xref_lock_tl(l_XRef_Rec.CROSS_REFERENCE_ID);
1022 FETCH mtl_xref_lock_tl INTO l_lock_tl_recinfo ;
1023 CLOSE mtl_xref_lock_tl;
1024
1025 -- calling update
1026 MTL_CROSS_REFERENCES_PKG.UPDATE_ROW(
1027 P_INVENTORY_ITEM_ID => l_XRef_Rec.Inventory_Item_Id
1028 ,P_ORGANIZATION_ID => l_XRef_Rec.Organization_Id
1029 ,P_CROSS_REFERENCE_TYPE => l_XRef_Rec.Cross_Reference_Type
1030 ,P_CROSS_REFERENCE => l_XRef_Rec.Cross_Reference
1031 ,P_CROSS_REFERENCE_ID => l_XRef_Rec.Cross_Reference_Id
1032 ,P_ORG_INDEPENDENT_FLAG => l_XRef_Rec.Org_Independent_Flag
1033 ,P_REQUEST_ID => l_XRef_Rec.Request_Id
1034 ,P_ATTRIBUTE1 => l_XRef_Rec.Attribute1
1035 ,P_ATTRIBUTE2 => l_XRef_Rec.Attribute2
1036 ,P_ATTRIBUTE3 => l_XRef_Rec.Attribute3
1037 ,P_ATTRIBUTE4 => l_XRef_Rec.Attribute4
1038 ,P_ATTRIBUTE5 => l_XRef_Rec.Attribute5
1039 ,P_ATTRIBUTE6 => l_XRef_Rec.Attribute6
1040 ,P_ATTRIBUTE7 => l_XRef_Rec.Attribute7
1041 ,P_ATTRIBUTE8 => l_XRef_Rec.Attribute8
1042 ,P_ATTRIBUTE9 => l_XRef_Rec.Attribute9
1043 ,P_ATTRIBUTE10 => l_XRef_Rec.Attribute10
1044 ,P_ATTRIBUTE11 => l_XRef_Rec.Attribute11
1045 ,P_ATTRIBUTE12 => l_XRef_Rec.Attribute12
1046 ,P_ATTRIBUTE13 => l_XRef_Rec.Attribute13
1047 ,P_ATTRIBUTE14 => l_XRef_Rec.Attribute14
1048 ,P_ATTRIBUTE15 => l_XRef_Rec.Attribute15
1049 ,P_ATTRIBUTE_CATEGORY => l_XRef_Rec.Attribute_category
1050 ,P_DESCRIPTION => l_XRef_Rec.Description
1051 ,P_LAST_UPDATE_DATE => Nvl(l_XRef_Rec.Last_Update_Date,SYSDATE)
1052 ,P_LAST_UPDATED_BY => Nvl(l_XRef_Rec.Last_Updated_By,FND_GLOBAL.USER_ID)
1053 ,P_LAST_UPDATE_LOGIN => Nvl(l_XRef_Rec.Last_Update_Login,FND_GLOBAL.LOGIN_ID)
1054 ,P_UOM_CODE => l_XRef_Rec.Uom_Code /* bug 14138918 */
1055 ,P_REVISION_ID => l_XRef_Rec.Revision_Id /* bug 14138918 */
1056 ,P_EPC_GTIN_SERIAL => l_XRef_rec.EPC_GTIN_SERIAL
1057 ,P_SOURCE_SYSTEM_ID => NULL
1058 ,P_START_DATE_ACTIVE => NULL
1059 ,P_END_DATE_ACTIVE => NULL
1060 ,X_OBJECT_VERSION_NUMBER =>returned_object_version_number
1061 );
1062 --Bug 9749717 : Raise Business Event
1063 BEGIN
1064 INV_ITEM_EVENTS_PVT.Raise_Events(
1065 p_event_name => 'EGO_WF_WRAPPER_PVT.G_Xref_CHANGE_EVENT'
1066 ,p_dml_type => 'UPDATE'
1067 ,p_inventory_item_id => l_XRef_Rec.Inventory_Item_Id
1068 ,p_organization_id => l_XRef_Rec.Organization_Id
1069 ,p_cross_reference_type => l_XRef_Rec.Cross_Reference_Type
1070 ,p_cross_reference => l_XRef_Rec.Cross_Reference
1071 );
1072 EXCEPTION
1073 WHEN OTHERS THEN
1074 NULL;
1075 END;
1076
1077
1078 ELSIF l_XRef_Rec.Transaction_Type = 'DELETE' THEN
1079 -- current cols should not be NULL.
1080 IF l_XRef_Rec.CROSS_REFERENCE_ID =FND_API.G_MISS_NUM THEN
1081
1082 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR; -- assigning the record status as error
1083 Error_Handler.Add_Error_Message
1084 (
1085 p_message_name => 'INV_XREF_ID_NULL'
1086 ,p_application_id => 'INV'
1087 ,p_message_type => 'E'
1088 ,p_entity_code => G_Entity_Code
1089 ,p_entity_index => l_Xref_Indx
1090 ,p_table_name => G_Table_Name
1091 );
1092 RAISE VALIDATION_ERROR;
1093 END IF;
1094
1095 -- checking for the record existance.
1096 OPEN mtl_xref_cur(l_XRef_Rec.CROSS_REFERENCE_ID);
1097 FETCH mtl_xref_cur INTO l_mtl_XRef_rec;
1098 IF mtl_xref_cur%NOTFOUND THEN
1099
1100 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
1101 Error_Handler.Add_Error_Message
1102 (
1103 p_message_name => 'INV_XREF_ID_NOTEXISTS'
1104 ,p_application_id => 'INV'
1105 ,p_message_type => 'E'
1106 ,p_entity_code => G_Entity_Code
1107 ,p_entity_index => l_Xref_Indx
1108 ,p_table_name => G_Table_Name
1109 );
1110 CLOSE mtl_xref_cur;
1111 RAISE VALIDATION_ERROR;
1112 END IF;
1113 CLOSE mtl_xref_cur;
1114
1115 -- locking the row
1116 OPEN mtl_xref_lock_b(l_XRef_Rec.CROSS_REFERENCE_ID);
1117 FETCH mtl_xref_lock_b INTO l_lock_b_recinfo ;
1118 CLOSE mtl_xref_lock_b;
1119
1120 OPEN mtl_xref_lock_tl(l_XRef_Rec.CROSS_REFERENCE_ID);
1121 FETCH mtl_xref_lock_tl INTO l_lock_tl_recinfo ;
1122 CLOSE mtl_xref_lock_tl;
1123
1124 Write_Debug('Deleting cross reference...');
1125 -- calling delete
1126 MTL_CROSS_REFERENCES_PKG.DELETE_ROW(
1127 P_CROSS_REFERENCE_ID => l_XRef_Rec.CROSS_REFERENCE_ID );
1128
1129 --Bug 9749717 : Raise Business Event
1130 BEGIN
1131 INV_ITEM_EVENTS_PVT.Raise_Events(
1132 p_event_name => 'EGO_WF_WRAPPER_PVT.G_Xref_CHANGE_EVENT'
1133 ,p_dml_type => 'DELETE'
1134 ,p_inventory_item_id => l_lock_b_recinfo.INVENTORY_ITEM_ID
1135 ,p_organization_id => l_lock_b_recinfo.ORGANIZATION_ID
1136 ,p_cross_reference_type => l_lock_b_recinfo.CROSS_REFERENCE_TYPE
1137 ,p_cross_reference => l_lock_b_recinfo.CROSS_REFERENCE);
1138
1139
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 NULL;
1143 END;
1144 END IF; -- Transaction type
1145
1146 EXCEPTION
1147 WHEN VALIDATION_ERROR THEN
1148 X_return_status:= FND_API.g_RET_STS_ERROR;
1149 l_msg_count := l_msg_count +1;
1150 Error_Handler.Get_Entity_Message(G_Entity_Code, x_message_list);
1151 ROLLBACK TO MTL_CROSS_REFERENCES_PVT; -- rolling back to savepoint
1152
1153 WHEN UNEXP_VALIDATION_ERROR THEN
1154 X_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1155 l_msg_count := l_msg_count +1;
1156 Error_Handler.Get_Entity_Message(G_Entity_Code, x_message_list);
1157 ROLLBACK TO MTL_CROSS_REFERENCES_PVT; -- rolling back to savepoint
1158
1159 WHEN resource_busy THEN
1160 Error_Handler.Add_Error_Message
1161 (
1162 p_message_name => 'INV_XREF_RECORD_LOCKED'
1163 ,p_application_id => 'INV'
1164 ,p_message_type => 'E'
1165 ,p_entity_code => G_Entity_Code
1166 ,p_entity_index => l_Xref_Indx
1167 ,p_table_name => G_Table_Name
1168 );
1169
1170 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
1171 X_return_status:= FND_API.g_RET_STS_ERROR;
1172 l_msg_count := l_msg_count +1;
1173
1174 WHEN others THEN
1175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1176 l_Token_Tbl(1).Token_Name := 'PACKAGE_NAME';
1177 l_Token_Tbl(1).Token_Value := G_PKG_NAME;
1178 l_Token_Tbl(1).Translate := FALSE;
1179 l_Token_Tbl(2).Token_Name := 'PROCEDURE_NAME';
1180 l_Token_Tbl(2).Token_Value := G_api_name;
1181 l_Token_Tbl(2).Translate := FALSE;
1182 l_Token_Tbl(3).Token_Name := 'ERROR_TEXT';
1183 l_Token_Tbl(3).Token_Value := SQLERRM;
1184 l_Token_Tbl(3).Translate := FALSE;
1185
1186 Error_Handler.Add_Error_Message
1187 (
1188 p_message_name => 'INV_ITEM_UNEXPECTED_ERROR'
1189 ,p_application_id => 'INV'
1190 ,p_token_tbl => l_Token_Tbl
1191 ,p_message_type => 'E'
1192 ,p_entity_code => G_Entity_Code
1193 ,p_entity_index => l_Xref_Indx
1194 ,p_table_name => G_Table_Name
1195 );
1196 l_XRef_Rec.x_return_status:=FND_API.g_RET_STS_ERROR;
1197 X_return_status:= FND_API.g_RET_STS_ERROR;
1198 l_msg_count := l_msg_count +1;
1199 Error_Handler.Get_Entity_Message(G_Entity_Code, x_message_list);
1200 ROLLBACK TO MTL_CROSS_REFERENCES_PVT; -- rolling back to savepoint
1201
1202 END; -- internal begin
1203 END LOOP; -- p_XRef_Tbl
1204
1205 p_XRef_Tbl:=l_XRef_Tbl;
1206 x_msg_count:=l_msg_count;
1207
1208 IF (p_commit = FND_API.g_TRUE) THEN
1209 Write_Debug('Commiting changes...');
1210 COMMIT;
1211 END IF;
1212
1213 END Process_XRef;
1214
1215 -- -----------------------------------------------------------------------------
1216 -- Procedure Name: Validate_GTIN_Rec
1217 --
1218 -- Description : Specific validations for Cross References that are of type GTIN
1219 -- -----------------------------------------------------------------------------
1220
1221 PROCEDURE Validate_GTIN_Rec(
1222 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1223 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1224 ,p_GTIN_XRef_Rec IN OUT NOCOPY MTL_CROSS_REFERENCES_PUB.XRef_Rec_Type
1225 ,x_return_status OUT NOCOPY VARCHAR2
1226 ,x_msg_count OUT NOCOPY NUMBER) IS
1227
1228 -- Local variable declarations
1229 l_GTIN_XRef_Rec MTL_CROSS_REFERENCES_PUB.XRef_Rec_Type; -- Declaring the record type object
1230 l_gtin_xref_exists VARCHAR(1);
1231 l_item_revision_exists VARCHAR(1);
1232 l_uom_exists VARCHAR(1);
1233 l_gtin_packitem_exists VARCHAR(1);
1234 l_gtin_num_return_status VARCHAR2(255);
1235 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1236 l_msg_count NUMBER;
1237 l_master_org NUMBER;
1238 GTIN_VALIDATION_ERROR EXCEPTION;
1239
1240 BEGIN
1241
1242 SAVEPOINT VALIDATE_GTIN_REC;
1243
1244 l_GTIN_XRef_Rec := p_GTIN_XRef_Rec;
1245 l_gtin_num_return_status := FND_API.G_RET_STS_SUCCESS;
1246 x_return_status := FND_API.G_RET_STS_SUCCESS;
1247 l_master_org := -1;
1248 -- Initialize message list
1249 IF FND_API.To_Boolean (p_init_msg_list) THEN
1250 Error_Handler.Initialize;
1251 END IF;
1252
1253 -- if msg list is to be initialized and changes to be commited, then this procedure
1254 -- is being called alone, therefore setting the the global index count to 1 for the
1255 -- one record taken in for error handling
1256
1257 IF FND_API.To_Boolean (p_init_msg_list)
1258 AND FND_API.To_Boolean (p_commit) THEN
1259 G_Xref_Indx := 1;
1260 END IF;
1261
1262
1263 Write_Debug('Validating GTIN Cross Reference Record...');
1264
1265
1266 IF (l_GTIN_XRef_Rec.UOM_CODE = FND_API.G_MISS_CHAR
1267 OR l_GTIN_XRef_Rec.UOM_CODE IS NULL) AND
1268 l_GTIN_XRef_Rec.TRANSACTION_TYPE = 'CREATE' THEN
1269
1270 Error_Handler.Add_Error_message
1271 (
1272 p_message_name => 'EGO_GTIN_UOM_INVALID'
1273 ,p_application_id => 'EGO'
1274 ,p_message_type => 'E'
1275 ,p_entity_code => G_Entity_Code
1276 ,p_entity_index => G_Xref_Indx
1277 ,p_table_name => G_Table_Name
1278 );
1279 RAISE GTIN_VALIDATION_ERROR;
1280 END IF;
1281
1282 IF l_GTIN_XRef_Rec.UOM_CODE IS NOT NULL AND
1283 l_GTIN_XRef_Rec.UOM_CODE <> FND_API.G_MISS_CHAR THEN
1284
1285 --
1286 -- UOM Code validation
1287 --
1288 BEGIN
1289 SELECT 'x'
1290 INTO l_uom_exists
1291 FROM mtl_units_of_measure_tl
1292 WHERE UOM_CODE = l_GTIN_XRef_Rec.UOM_CODE;
1293
1294 EXCEPTION
1295 WHEN No_Data_Found THEN
1296
1297 l_Token_Tbl(1).Token_Name := 'UOM_CODE';
1298 l_Token_Tbl(1).Token_Value := l_GTIN_XRef_Rec.UOM_CODE;
1299 L_TOKEN_TBL(1).TRANSLATE := FALSE;
1300
1301 Error_Handler.Add_Error_message
1302 (
1303 p_message_name => 'EGO_REL_ITEMS_UOM_NOTEXIST'
1304 ,p_application_id => 'EGO'
1305 ,p_token_tbl => l_Token_Tbl
1306 ,p_message_type => 'E'
1307 ,p_entity_code => G_Entity_Code
1308 ,p_entity_index => G_Xref_Indx
1309 ,p_table_name => G_Table_Name
1310 );
1311 RAISE GTIN_VALIDATION_ERROR;
1312 WHEN Too_Many_Rows THEN
1313 NULL;
1314 END;
1315
1316 --
1317 -- begin checking for duplicate UOM
1318 --
1319 BEGIN
1320 SELECT 'x'
1321 INTO l_gtin_xref_exists
1322 FROM mtl_cross_references
1323 WHERE CROSS_REFERENCE_TYPE = l_GTIN_XRef_Rec.CROSS_REFERENCE_TYPE
1324 AND INVENTORY_ITEM_ID = l_GTIN_XRef_Rec.INVENTORY_ITEM_ID
1325 AND UOM_CODE = l_GTIN_XRef_Rec.UOM_CODE
1326 AND NOT CROSS_REFERENCE_ID = l_GTIN_XRef_Rec.CROSS_REFERENCE_ID;
1327
1328 IF l_gtin_xref_exists='x' THEN
1329
1330 l_Token_Tbl(1).Token_Name := 'INVENTORY_ITEM_ID';
1331 l_Token_Tbl(1).Token_Value := l_GTIN_XRef_Rec.Inventory_Item_Id;
1332 l_Token_Tbl(1).Translate := FALSE;
1333 l_Token_Tbl(2).Token_Name := 'UOM_CODE';
1334 l_Token_Tbl(2).Token_Value := l_GTIN_XRef_Rec.UOM_CODE;
1335 l_Token_Tbl(2).Translate := FALSE;
1336
1337
1338 Error_Handler.Add_Error_message
1339 (
1340 p_message_name => 'EGO_GTIN_XREF_DUPLICATE_UOM'
1341 ,p_application_id => 'EGO'
1342 ,p_token_tbl => l_Token_Tbl
1343 ,p_message_type => 'E'
1344 ,p_entity_code => G_Entity_Code
1345 ,p_entity_index => G_Xref_Indx
1346 ,p_table_name => G_Table_Name
1347 );
1348 RAISE GTIN_VALIDATION_ERROR;
1349 END IF;
1350
1351 EXCEPTION
1352 WHEN NO_DATA_FOUND THEN
1353 NULL;
1354 END;
1355 -- end checking for duplicate UOM
1356 END IF;
1357 --
1358 -- call GTIN number validations
1359 --
1360 Validate_GTIN_Number(l_GTIN_XRef_Rec.CROSS_REFERENCE, l_gtin_num_return_status);
1361 IF l_gtin_num_return_status = FND_API.G_RET_STS_ERROR THEN
1362 RAISE GTIN_VALIDATION_ERROR;
1363 END IF;
1364
1365 -- GTIN can be duplicate for different items, but cannot have same UOM
1366 BEGIN
1367 SELECT 'x' INTO l_gtin_packitem_exists
1368 FROM mtl_cross_references
1369 WHERE CROSS_REFERENCE_TYPE = l_GTIN_XRef_Rec.CROSS_REFERENCE_TYPE
1370 AND NOT INVENTORY_ITEM_ID = l_GTIN_XRef_Rec.INVENTORY_ITEM_ID
1371 AND CROSS_REFERENCE = l_GTIN_XRef_Rec.CROSS_REFERENCE
1372 AND UOM_CODE = l_GTIN_XRef_Rec.UOM_CODE;
1373
1374 IF l_gtin_packitem_exists='x' THEN
1375
1376 l_Token_Tbl(1).Token_Name := 'PACK_ITEM';
1377 l_Token_Tbl(1).Token_Value := l_GTIN_XRef_Rec.Inventory_Item_Id;
1378 l_Token_Tbl(1).Translate := FALSE;
1379 l_Token_Tbl(2).Token_Name := 'GTIN';
1380 l_Token_Tbl(2).Token_Value := l_GTIN_XRef_Rec.CROSS_REFERENCE;
1381 l_Token_Tbl(2).Translate := FALSE;
1382
1383 Error_Handler.Add_Error_message
1384 (
1385 p_message_name => 'EGO_GTIN_EXISTS_WITH_PACKITEM'
1386 ,p_application_id => 'EGO'
1387 ,p_token_tbl => l_Token_Tbl
1388 ,p_message_type => 'E'
1389 ,p_entity_code => G_Entity_Code
1390 ,p_entity_index => G_Xref_Indx
1391 ,p_table_name => G_Table_Name
1392 );
1393 RAISE GTIN_VALIDATION_ERROR;
1394 END IF;
1395 EXCEPTION
1396 WHEN NO_DATA_FOUND THEN
1397 NULL;
1398 END;
1399
1400 --
1401 -- begin check for whether item revision exists for item
1402 --
1403
1404 IF l_GTIN_XRef_Rec.Revision_Id IS NOT NULL AND
1405 l_GTIN_XRef_Rec.Revision_Id <> FND_API.G_MISS_NUM THEN
1406 IF l_GTIN_XRef_Rec.ORGANIZATION_ID IS NOT NULL
1407 AND l_GTIN_XRef_Rec.ORGANIZATION_ID <> FND_API.G_MISS_NUM THEN
1408 BEGIN
1409 SELECT 'x' INTO l_item_revision_exists
1410 FROM mtl_item_revisions
1411 WHERE INVENTORY_ITEM_ID = l_GTIN_XRef_Rec.INVENTORY_ITEM_ID
1412 AND REVISION_ID = l_GTIN_XRef_Rec.REVISION_ID
1413 AND ORGANIZATION_ID = l_GTIN_XRef_Rec.ORGANIZATION_ID;
1414 EXCEPTION
1415 WHEN NO_DATA_FOUND THEN
1416 Error_Handler.Add_Error_message
1417 (
1418 p_message_name => 'EGO_GTIN_ITEM_REVISION_EXISTS'
1419 ,p_application_id => 'EGO'
1420 ,p_message_type => 'E'
1421 ,p_entity_code => G_Entity_Code
1422 ,p_entity_index => G_Xref_Indx
1423 ,p_table_name => G_Table_Name
1424 );
1425 RAISE GTIN_VALIDATION_ERROR;
1426 END;
1427 END IF;
1428
1429 --
1430 -- if all orgs, then take revisions from master org
1431 --
1432 IF l_GTIN_XRef_Rec.ORG_INDEPENDENT_FLAG = 'Y' THEN
1433 BEGIN
1434 SELECT i.organization_id INTO l_master_org
1435 FROM mtl_parameters p, mtl_system_items_b i
1436 WHERE p.organization_id = p.master_organization_id
1437 AND i.organization_id = p.organization_id
1438 AND i.inventory_item_id = l_GTIN_XRef_Rec.INVENTORY_ITEM_ID;
1439 EXCEPTION
1440 WHEN No_Data_Found THEN
1441 Error_Handler.Add_Error_message
1442 (
1443 p_message_name => 'EGO_GTIN_ITEM_REVISION_EXISTS'
1444 ,p_application_id => 'EGO'
1445 ,p_message_type => 'E'
1446 ,p_entity_code => G_Entity_Code
1447 ,p_entity_index => G_Xref_Indx
1448 ,p_table_name => G_Table_Name
1449 );
1450 RAISE GTIN_VALIDATION_ERROR;
1451 END;
1452
1453 BEGIN
1454 SELECT 'x' INTO l_item_revision_exists
1455 FROM mtl_item_revisions
1456 WHERE INVENTORY_ITEM_ID = l_GTIN_XRef_Rec.INVENTORY_ITEM_ID
1457 AND REVISION_ID = l_GTIN_XRef_Rec.REVISION_ID
1458 AND ORGANIZATION_ID = l_master_org;
1459 EXCEPTION
1460 WHEN NO_DATA_FOUND THEN
1461 Error_Handler.Add_Error_message
1462 (
1463 p_message_name => 'EGO_GTIN_ITEM_REVISION_EXISTS'
1464 ,p_application_id => 'EGO'
1465 ,p_message_type => 'E'
1466 ,p_entity_code => G_Entity_Code
1467 ,p_entity_index => G_Xref_Indx
1468 ,p_table_name => G_Table_Name
1469 );
1470 RAISE GTIN_VALIDATION_ERROR;
1471 END;
1472
1473 END IF;
1474 END IF;
1475
1476 -- end check for item revision
1477
1478
1479
1480 IF l_GTIN_XRef_Rec.EPC_GTIN_SERIAL = FND_API.G_MISS_NUM THEN
1481 l_GTIN_XRef_Rec.EPC_GTIN_SERIAL := 0;
1482 END IF;
1483
1484 p_GTIN_XRef_Rec := l_GTIN_XRef_Rec;
1485
1486 EXCEPTION
1487
1488 WHEN GTIN_VALIDATION_ERROR THEN
1489 x_msg_count := x_msg_count + 1;
1490 x_return_status := FND_API.G_RET_STS_ERROR;
1491 l_GTIN_XRef_Rec.x_return_status := FND_API.G_RET_STS_ERROR;
1492 p_GTIN_XRef_Rec := l_GTIN_XRef_Rec;
1493
1494 ROLLBACK TO VALIDATE_GTIN_REC;
1495 WHEN OTHERS THEN
1496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1497 l_Token_Tbl(1).Token_Name := 'PACKAGE_NAME';
1498 l_Token_Tbl(1).Token_Value := G_PKG_NAME;
1499 l_Token_Tbl(1).Translate := FALSE;
1500 l_Token_Tbl(2).Token_Name := 'PROCEDURE_NAME';
1501 l_Token_Tbl(2).Token_Value := G_Api_Name;
1502 l_Token_Tbl(2).Translate := FALSE;
1503 l_Token_Tbl(3).Token_Name := 'ERROR_TEXT';
1504 l_Token_Tbl(3).Token_Value := SQLERRM;
1505 l_Token_Tbl(3).Translate := FALSE;
1506
1507 Error_Handler.Add_Error_Message
1508 (
1509 p_message_name => 'INV_ITEM_UNEXPECTED_ERROR'
1510 ,p_application_id => 'INV'
1511 ,p_token_tbl => l_Token_Tbl
1512 ,p_message_type => 'E'
1513 ,p_entity_code => G_Entity_Code
1514 ,p_entity_index => G_Xref_Indx
1515 ,p_table_name => G_Table_Name
1516 );
1517
1518 l_msg_count := l_msg_count +1;
1519 ROLLBACK TO VALIDATE_GTIN_REC;
1520
1521 x_msg_count:=l_msg_count;
1522
1523 END Validate_GTIN_Rec;
1524
1525
1526 -- -----------------------------------------------------------------------------
1527 -- Procedure Name: Validate_GTIN_Number
1528 --
1529 -- Description : Takes in a GTIN as characters to validate the 14 digit GTIN
1530 -- -----------------------------------------------------------------------------
1531 PROCEDURE Validate_GTIN_Number(
1532 p_GTIN_XRef_Number IN VARCHAR2
1533 ,x_return_status OUT NOCOPY VARCHAR2) IS
1534
1535 -- Local variable declarations
1536 l_GTIN_XRef_Number VARCHAR2(255);
1537 l_gtin NUMBER;
1538 l_gtin_exists VARCHAR(1);
1539
1540 GTIN_NUM_VALIDATION_ERROR EXCEPTION;
1541
1542 BEGIN
1543
1544 SAVEPOINT VALIDATE_GTIN_NUMBER;
1545 l_GTIN_XRef_Number := p_GTIN_XRef_Number;
1546
1547
1548 Write_Debug('Validating GTIN Number...');
1549
1550 -- 1. GTIN must be a number
1551 BEGIN
1552 l_gtin := To_Number(l_GTIN_XRef_Number);
1553 EXCEPTION
1554 WHEN Value_Error THEN
1555 Error_Handler.Add_Error_message
1556 (
1557 p_message_name => 'EGO_GTIN_NOT_NUMBER'
1558 ,p_application_id => 'EGO'
1559 ,p_message_type => 'E'
1560 ,p_entity_code => G_Entity_Code
1561 ,p_entity_index => G_Xref_Indx
1562 ,p_table_name => G_Table_Name
1563 );
1564 RAISE GTIN_NUM_VALIDATION_ERROR;
1565 END;
1566
1567 -- 2. Length must be 14
1568 IF Length(p_GTIN_XRef_Number) <> 14 THEN
1569
1570 Error_Handler.Add_Error_message
1571 (
1572 p_message_name => 'EGO_GTIN_LENGTH_NOT_CORRECT'
1573 ,p_application_id => 'EGO'
1574 ,p_message_type => 'E'
1575 ,p_entity_code => G_Entity_Code
1576 ,p_entity_index => G_Xref_Indx
1577 ,p_table_name => G_Table_Name
1578 );
1579 RAISE GTIN_NUM_VALIDATION_ERROR;
1580 END IF;
1581
1582 -- 3. Check (14th) digit validity
1583 IF EGO_GTIN_ATTRS_PVT.Is_Check_Digit_Invalid(l_GTIN_XRef_Number) THEN
1584 Error_Handler.Add_Error_message
1585 (
1586 p_message_name => 'EGO_GTIN_CHECKDIGIT_INVALID'
1587 ,p_application_id => 'EGO'
1588 ,p_message_type => 'E'
1589 ,p_entity_code => G_Entity_Code
1590 ,p_entity_index => G_Xref_Indx
1591 ,p_table_name => G_Table_Name
1592 );
1593 RAISE GTIN_NUM_VALIDATION_ERROR;
1594 END IF;
1595
1596 -- 4. Cannot contain more than six leading zeros
1597 IF SubStr(l_GTIN_XRef_Number, 1, 7) = '0000000' THEN
1598
1599 Error_Handler.Add_Error_message
1600 (
1601 p_message_name => 'EGO_GTIN_LEADING_ZERO_INVALID'
1602 ,p_application_id => 'EGO'
1603 ,p_message_type => 'E'
1604 ,p_entity_code => G_Entity_Code
1605 ,p_entity_index => G_Xref_Indx
1606 ,p_table_name => G_Table_Name
1607 );
1608 RAISE GTIN_NUM_VALIDATION_ERROR;
1609 END IF;
1610
1611 -- 5. Third digit from left must be 0,1,3,6,7,8,9 when the second digit from the left is 0
1612 IF SUBSTR(l_GTIN_XRef_Number, 2, 1) = '0' AND
1613 SUBSTR(l_GTIN_XRef_Number, 3, 1) NOT IN ('0', '1', '3', '6', '7', '8', '9') THEN
1614 Error_Handler.Add_Error_message
1615 (
1616 p_message_name => 'EGO_GTIN_THIRD_DIGIT_INVALID'
1617 ,p_application_id => 'EGO'
1618 ,p_message_type => 'E'
1619 ,p_entity_code => G_Entity_Code
1620 ,p_entity_index => G_Xref_Indx
1621 ,p_table_name => G_Table_Name
1622 );
1623 RAISE GTIN_NUM_VALIDATION_ERROR;
1624 END IF;
1625
1626 -- 6. If has six leading zeros in a RCI message, digits 7-9 must be between 301-968
1627 IF SUBSTR(l_GTIN_XRef_Number, 1, 6) = '000000' AND
1628 TO_NUMBER(SUBSTR(l_GTIN_XRef_Number, 7, 3)) NOT BETWEEN 301 AND 968 THEN
1629 Error_Handler.Add_Error_message
1630 (
1631 p_message_name => 'EGO_GTIN_7TO9DIGIT_INVALID'
1632 ,p_application_id => 'EGO'
1633 ,p_message_type => 'E'
1634 ,p_entity_code => G_Entity_Code
1635 ,p_entity_index => G_Xref_Indx
1636 ,p_table_name => G_Table_Name
1637 );
1638 RAISE GTIN_NUM_VALIDATION_ERROR;
1639 END IF;
1640
1641 -- 7. If submitted in RCI messages, cannot contain values 0980-0989 or 099 in the first 4 digits
1642 IF SUBSTR(l_GTIN_XRef_Number, 1, 3) IN ('098', '099') THEN
1643 Error_Handler.Add_Error_message
1644 (
1645 p_message_name => 'EGO_GTIN_0TO4_DIGIT_INVALID'
1646 ,p_application_id => 'EGO'
1647 ,p_message_type => 'E'
1648 ,p_entity_code => G_Entity_Code
1649 ,p_entity_index => G_Xref_Indx
1650 ,p_table_name => G_Table_Name
1651 );
1652 RAISE GTIN_NUM_VALIDATION_ERROR;
1653 END IF;
1654
1655 -- 8. If submitted in RCI messages, cannot contain values 02, 04, 05, and 10-29 in the second and third digits from the left
1656 IF SUBSTR(l_GTIN_XRef_Number, 2, 2) IN ('02', '04', '05') OR
1657 TO_NUMBER(SUBSTR(l_GTIN_XRef_Number, 2, 2)) BETWEEN 10 AND 29 THEN
1658 Error_Handler.Add_Error_message
1659 (
1660 p_message_name => 'EGO_GTIN_2TO3_DIGIT_INVALID'
1661 ,p_application_id => 'EGO'
1662 ,p_message_type => 'E'
1663 ,p_entity_code => G_Entity_Code
1664 ,p_entity_index => G_Xref_Indx
1665 ,p_table_name => G_Table_Name
1666 );
1667 RAISE GTIN_NUM_VALIDATION_ERROR;
1668 END IF;
1669
1670 EXCEPTION
1671 WHEN GTIN_NUM_VALIDATION_ERROR THEN
1672 x_return_status := FND_API.G_RET_STS_ERROR;
1673 ROLLBACK TO VALIDATE_GTIN_NUMBER;
1674 END Validate_GTIN_Number;
1675
1676
1677 -- -----------------------------------------------------------------------------
1678 -- Procedure Name: Process_XRef_Intf_Rows
1679 --
1680 -- Description : Concurrent program to pull rows from
1681 -- MTL_CROSS_REFERENCES_INTERFACE for processing.
1682 -- -----------------------------------------------------------------------------
1683 PROCEDURE Process_XRef_Intf_Rows(
1684 ERRBUF OUT NOCOPY VARCHAR2,
1685 RETCODE OUT NOCOPY VARCHAR2,
1686 p_data_set_id IN NUMBER,
1687 p_del_rec_flag IN NUMBER := 1) IS
1688
1689 l_return_status VARCHAR2(1);
1690 l_msg_list Error_Handler.Error_Tbl_Type;
1691 l_msg Error_Handler.Error_Rec_Type;
1692 l_XRef_Tbl MTL_CROSS_REFERENCES_PUB.XRef_Tbl_Type;
1693 l_XRef_Rec MTL_CROSS_REFERENCES_PUB.XRef_Rec_Type;
1694 L_MSG_COUNT number;
1695 l_uom_code VARCHAR2(3);
1696 l_dummyInt INTEGER;
1697 ret_code NUMBER;
1698 err_msg VARCHAR2(300);
1699 l_err_text VARCHAR2(255);
1700 l_proc_rec_count NUMBER;
1701 l_err_count NUMBER; -- to see if error msg exists
1702
1703 p_organization_id NUMBER;
1704 p_revision_id NUMBER;
1705 p_inventory_item_id NUMBER;
1706
1707
1708 -- cursor for going through the interface table
1709 -- for records where set_process_id is equal to parameter taken in
1710 -- or all records if parameter is NULL
1711 -- and process flag is 1
1712 CURSOR C_INTF_ROWS(C_DATA_SET_ID NUMBER) IS
1713 SELECT A.ROWID, A.*
1714 FROM MTL_CROSS_REFERENCES_INTERFACE A
1715 WHERE DECODE(C_DATA_SET_ID, NULL, -1, A.SET_PROCESS_ID) = NVL(C_DATA_SET_ID, -1)
1716 AND A.PROCESS_FLAG = 1;
1717
1718 BEGIN
1719
1720 FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering process Item Cross References Import.');
1721 FND_FILE.PUT_LINE( FND_FILE.log,'Set Process Id : '||to_char(p_data_set_id));
1722 l_err_count := 0; -- start with no errors
1723 RETCODE := 0; -- assumes successful completion
1724
1725 FOR l_mtl_xref_rec IN c_intf_rows(p_data_set_id) LOOP
1726
1727 UPDATE MTL_CROSS_REFERENCES_INTERFACE
1728 SET Transaction_Id = MTL_CROSS_REF_INTERFACE_S.NEXTVAL,
1729 Request_Id = FND_GLOBAL.CONC_REQUEST_ID
1730 WHERE ROWID = l_mtl_xref_rec.ROWID;
1731 end loop;
1732
1733 L_PROC_REC_COUNT := 0;
1734
1735 FOR L_MTL_XREF_REC IN C_INTF_ROWS(P_DATA_SET_ID) LOOP
1736 Error_Handler.Initialize;
1737 l_proc_rec_count := +1;
1738 L_UOM_CODE := L_MTL_XREF_REC.UOM_CODE;
1739
1740 IF l_uom_code IS NULL
1741 AND (l_mtl_xref_rec.UNIT_OF_MEASURE_TL IS NOT NULL
1742 AND l_mtl_xref_rec.UOM_LANGUAGE IS NOT NULL) THEN
1743
1744 BEGIN
1745 SELECT A.UOM_CODE
1746 INTO L_UOM_CODE
1747 FROM MTL_UNITS_OF_MEASURE_TL A
1748 WHERE A.UNIT_OF_MEASURE_TL = L_MTL_XREF_REC.UNIT_OF_MEASURE_TL
1749 AND A.LANGUAGE = l_mtl_xref_rec.UOM_LANGUAGE;
1750 EXCEPTION
1751 WHEN Too_Many_Rows THEN
1752 NULL;
1753 WHEN No_Data_Found THEN
1754 NULL;
1755 END;
1756 END IF;
1757 -- bug 14464655
1758 l_return_status := FND_API.G_RET_STS_SUCCESS;
1759
1760 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1761 IF l_mtl_xref_rec.ORGANIZATION_CODE IS NOT NULL THEN
1762 BEGIN
1763
1764 SELECT mtp.organization_id into p_organization_id
1765 FROM mtl_parameters mtp
1766 WHERE mtp.organization_code = l_mtl_xref_rec.ORGANIZATION_CODE;
1767
1768
1769 IF l_mtl_xref_rec.ORGANIZATION_ID IS NOT NULL THEN
1770 IF (l_mtl_xref_rec.ORGANIZATION_ID <> p_organization_id) THEN
1771
1772 l_return_status := FND_API.g_RET_STS_ERROR;
1773 Error_Handler.Add_Error_message
1774 (
1775 p_message_name => 'EGO_ASSOC_INVALID_ORG'
1776 ,p_application_id => 'EGO'
1777 ,p_message_type => 'E'
1778 ,p_entity_code => G_Entity_Code
1779 ,p_entity_index => 1
1780 ,p_table_name => 'MTL_CROSS_REFERENCES_INTERFACE'
1781 );
1782
1783 l_msg_count := 1;
1784 Error_Handler.Get_Entity_Message(G_Entity_Code, l_msg_list);
1785
1786 END IF;
1787 END IF;
1788
1789
1790 l_mtl_xref_rec.ORGANIZATION_ID := p_organization_id ;
1791
1792 EXCEPTION
1793 WHEN NO_DATA_FOUND THEN
1794
1795 l_return_status := FND_API.g_RET_STS_ERROR;
1796 Error_Handler.Add_Error_message
1797 (
1798 p_message_name => 'EGO_ASSOC_INVALID_ORG'
1799 ,p_application_id => 'EGO'
1800 ,p_message_type => 'E'
1801 ,p_entity_code => G_Entity_Code
1802 ,p_entity_index => 1
1803 ,p_table_name => 'MTL_CROSS_REFERENCES_INTERFACE'
1804 );
1805
1806 l_msg_count := 1;
1807 Error_Handler.Get_Entity_Message(G_Entity_Code, l_msg_list);
1808
1809 END;
1810
1811 END IF;
1812 END IF;
1813 -- item number to id conversion
1814 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1815 IF l_mtl_xref_rec.item_number IS NOT NULL THEN
1816
1817 BEGIN
1818
1819 SELECT msk.inventory_item_id into p_inventory_item_id
1820 FROM mtl_system_items_b_kfv msk
1821 WHERE msk.concatenated_segments = l_mtl_xref_rec.ITEM_NUMBER
1822 and organization_id=l_mtl_xref_rec.ORGANIZATION_ID;
1823
1824 IF l_mtl_xref_rec.INVENTORY_ITEM_ID IS NOT NULL THEN
1825 IF (l_mtl_xref_rec.INVENTORY_ITEM_ID <> p_inventory_item_id) THEN
1826
1827
1828 l_return_status := FND_API.g_RET_STS_ERROR;
1829
1830 Error_Handler.Add_Error_message
1831 (
1832 p_message_name => 'EGO_INVALID_ITEM_SEGMENTS'
1833 ,p_application_id => 'EGO'
1834 ,p_message_type => 'E'
1835 ,p_entity_code => G_Entity_Code
1836 ,p_entity_index => 1
1837 ,p_table_name => 'MTL_CROSS_REFERENCES_INTERFACE'
1838 );
1839
1840 l_msg_count := 1;
1841 Error_Handler.Get_Entity_Message(G_Entity_Code, l_msg_list);
1842
1843 END IF;
1844 END IF;
1845
1846 l_mtl_xref_rec.INVENTORY_ITEM_ID := p_inventory_item_id ;
1847
1848 EXCEPTION
1849 WHEN NO_DATA_FOUND THEN
1850
1851 l_return_status := FND_API.g_RET_STS_ERROR;
1852 Error_Handler.Add_Error_message
1853 (
1854 p_message_name => 'EGO_INVALID_ITEM_SEGMENTS'
1855 ,p_application_id => 'EGO'
1856 ,p_message_type => 'E'
1857 ,p_entity_code => G_Entity_Code
1858 ,p_entity_index => 1
1859 ,p_table_name => 'MTL_CROSS_REFERENCES_INTERFACE'
1860 );
1861 l_msg_count := 1;
1862 Error_Handler.Get_Entity_Message(G_Entity_Code, l_msg_list);
1863
1864 END;
1865
1866 END IF;
1867 END IF;
1868
1869 -- getting revision id from revision
1870
1871 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1872 IF l_mtl_xref_rec.revision IS NOT NULL THEN
1873
1874 BEGIN
1875
1876 SELECT revision_id INTO p_revision_id
1877 FROM mtl_item_revisions_b
1878 WHERE inventory_item_id=l_mtl_xref_rec.INVENTORY_ITEM_ID
1879 AND organization_id= l_mtl_xref_rec.ORGANIZATION_ID
1880 AND revision= l_mtl_xref_rec.revision;
1881
1882
1883 IF l_mtl_xref_rec.revision_id IS NOT NULL THEN
1884 IF (l_mtl_xref_rec.revision_id <> p_revision_id) THEN
1885
1886 l_return_status := FND_API.g_RET_STS_ERROR;
1887
1888 Error_Handler.Add_Error_message
1889 (
1890 p_message_name => 'INV_ITM_INVALID_REVISION_CODE'
1891 ,p_application_id => 'INV'
1892 ,p_message_type => 'E'
1893 ,p_entity_code => G_Entity_Code
1894 ,p_entity_index => 1
1895 ,p_table_name => 'MTL_CROSS_REFERENCES_INTERFACE'
1896 );
1897
1898 l_msg_count := 1;
1899 Error_Handler.Get_Entity_Message(G_Entity_Code, l_msg_list);
1900
1901 END IF;
1902 END IF;
1903
1904 l_mtl_xref_rec.revision_id := p_revision_id ;
1905 EXCEPTION
1906 WHEN NO_DATA_FOUND THEN
1907
1908 l_return_status := FND_API.g_RET_STS_ERROR;
1909 Error_Handler.Add_Error_message
1910 (
1911 p_message_name => 'INV_ITM_INVALID_REVISION_CODE'
1912 ,p_application_id => 'INV'
1913 ,p_message_type => 'E'
1914 ,p_entity_code => G_Entity_Code
1915 ,p_entity_index => 1
1916 ,p_table_name => 'MTL_CROSS_REFERENCES_INTERFACE'
1917 );
1918 l_msg_count := 1;
1919 Error_Handler.Get_Entity_Message(G_Entity_Code, l_msg_list);
1920
1921 END;
1922
1923 END IF;
1924 END IF;
1925
1926 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1927 -- bug 14464655
1928
1929 l_XRef_Rec.TRANSACTION_TYPE := l_mtl_xref_rec.TRANSACTION_TYPE;
1930 -- bug 14403250
1931 l_XRef_Rec.CROSS_REFERENCE_ID := nvl(l_mtl_xref_rec.CROSS_REFERENCE_ID,FND_API.G_MISS_NUM);
1932 l_XRef_Rec.INVENTORY_ITEM_ID := l_mtl_xref_rec.INVENTORY_ITEM_ID;
1933 l_XRef_Rec.ORGANIZATION_ID := l_mtl_xref_rec.ORGANIZATION_ID;
1934 l_XRef_Rec.CROSS_REFERENCE_TYPE := l_mtl_xref_rec.CROSS_REFERENCE_TYPE;
1935 l_XRef_Rec.CROSS_REFERENCE := l_mtl_xref_rec.CROSS_REFERENCE;
1936 l_XRef_Rec.DESCRIPTION := l_mtl_xref_rec.DESCRIPTION;
1937 l_XRef_Rec.ORG_INDEPENDENT_FLAG := l_mtl_xref_rec.ORG_INDEPENDENT_FLAG;
1938 l_XRef_Rec.REQUEST_ID := l_mtl_xref_rec.REQUEST_ID;
1939 l_XRef_Rec.ATTRIBUTE1 := l_mtl_xref_rec.ATTRIBUTE1;
1940 l_XRef_Rec.ATTRIBUTE2 := l_mtl_xref_rec.ATTRIBUTE2;
1941 l_XRef_Rec.ATTRIBUTE3 := l_mtl_xref_rec.ATTRIBUTE3;
1942 l_XRef_Rec.ATTRIBUTE4 := l_mtl_xref_rec.ATTRIBUTE4;
1943 l_XRef_Rec.ATTRIBUTE5 := l_mtl_xref_rec.ATTRIBUTE5;
1944 l_XRef_Rec.ATTRIBUTE6 := l_mtl_xref_rec.ATTRIBUTE6;
1945 l_XRef_Rec.ATTRIBUTE7 := l_mtl_xref_rec.ATTRIBUTE7;
1946 l_XRef_Rec.ATTRIBUTE8 := l_mtl_xref_rec.ATTRIBUTE8;
1947 l_XRef_Rec.ATTRIBUTE9 := l_mtl_xref_rec.ATTRIBUTE9;
1948 l_XRef_Rec.ATTRIBUTE10 := l_mtl_xref_rec.ATTRIBUTE10;
1949 l_XRef_Rec.ATTRIBUTE11 := l_mtl_xref_rec.ATTRIBUTE11;
1950 l_XRef_Rec.ATTRIBUTE12 := l_mtl_xref_rec.ATTRIBUTE12;
1951 l_XRef_Rec.ATTRIBUTE13 := l_mtl_xref_rec.ATTRIBUTE13;
1952 l_XRef_Rec.ATTRIBUTE14 := l_mtl_xref_rec.ATTRIBUTE14;
1953 l_XRef_Rec.ATTRIBUTE15 := l_mtl_xref_rec.ATTRIBUTE15;
1954 l_XRef_Rec.ATTRIBUTE_CATEGORY := l_mtl_xref_rec.ATTRIBUTE_CATEGORY;
1955 l_XRef_Rec.UOM_CODE := l_mtl_xref_rec.UOM_CODE;
1956 l_XRef_Rec.REVISION_ID := l_mtl_xref_rec.REVISION_ID;
1957 l_XRef_Rec.EPC_GTIN_SERIAL := l_mtl_xref_rec.EPC_GTIN_SERIAL;
1958 l_XRef_Rec.LAST_UPDATE_DATE := l_mtl_xref_rec.LAST_UPDATE_DATE;
1959 l_XRef_Rec.LAST_UPDATED_BY := l_mtl_xref_rec.LAST_UPDATED_BY;
1960 l_XRef_Rec.CREATION_DATE := l_mtl_xref_rec.CREATION_DATE;
1961 l_XRef_Rec.CREATED_BY := l_mtl_xref_rec.CREATED_BY;
1962 l_XRef_Rec.LAST_UPDATE_LOGIN := l_mtl_xref_rec.LAST_UPDATE_LOGIN;
1963
1964 l_XRef_Tbl(1) := l_XRef_Rec;
1965
1966 Write_Debug('Calling MTL_CROSS_REFERENCES_PVT.Process_Xref for Transaction ' ||To_Char(l_mtl_xref_rec.Transaction_Id));
1967
1968 MTL_CROSS_REFERENCES_PVT.Process_Xref
1969 (p_init_msg_list => FND_API.G_TRUE
1970 ,p_commit => FND_API.G_TRUE
1971 ,p_XRef_Tbl => l_XRef_Tbl
1972 ,x_return_status => l_return_status
1973 ,x_msg_count => l_msg_count
1974 ,x_message_list => l_msg_list
1975 );
1976 END IF;
1977
1978 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1979 UPDATE MTL_CROSS_REFERENCES_INTERFACE
1980 SET process_flag = 3
1981 WHERE Transaction_Id = l_mtl_xref_rec.Transaction_Id;
1982
1983
1984 -- looping to take out the messages
1985 IF l_msg_list.FIRST IS NOT NULL THEN
1986 FOR msg IN l_msg_list.FIRST..l_msg_list.LAST LOOP
1987 l_msg := l_msg_list(msg);
1988 -- place into mtl_interface_error_table
1989 l_dummyInt := LOG_ERROR --bug 12533707--invpuopi.mtl_log_interface_err
1990 (
1991 org_id => l_msg.organization_id
1992 ,user_id => fnd_global.user_id
1993 ,login_id => fnd_global.login_id
1994 ,prog_appid => null
1995 ,prog_id => null
1996 ,req_id => fnd_global.conc_request_id
1997 ,trans_id => l_mtl_xref_rec.Transaction_Id
1998 ,error_text => l_msg.message_text
1999 ,p_column_name => null
2000 ,tbl_name => 'MTL_CROSS_REFERENCES_INTERFACE'
2001 ,msg_name => l_msg.message_name
2002 ,err_text => l_err_text -- error text out
2003 );
2004 END LOOP;
2005 END IF;
2006 Write_Debug('Transaction ' ||To_Char(l_mtl_xref_rec.Transaction_Id)|| ' has failed.');
2007 l_err_count := 1;
2008 ELSE
2009 Write_Debug('Transaction ' ||To_Char(l_mtl_xref_rec.Transaction_Id)|| ' has completed successfully.');
2010 UPDATE MTL_CROSS_REFERENCES_INTERFACE
2011 SET process_flag = 7
2012 WHERE Transaction_Id = l_mtl_xref_rec.Transaction_Id;
2013 END IF;
2014
2015 END LOOP;
2016
2017 -- calling method to delete processed rows:
2018 if p_del_rec_flag=1 then
2019 ret_code := Del_Processed_Recs (err_text => err_msg,
2020 com_flag => 1,
2021 p_data_set_id => p_data_set_id);
2022 end if ;
2023
2024 IF l_err_count = 1 THEN
2025 RETCODE := 1; -- if any errors, then display warning for concurrent program
2026 ELSE
2027 RETCODE := 0;
2028 END IF;
2029
2030 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processed '||To_Char(l_proc_rec_count)|| ' records.');
2031
2032 FND_FILE.PUT_LINE( FND_FILE.LOG,'Exiting Item Cross References Import.');
2033
2034 END Process_XRef_Intf_Rows;
2035
2036 -- -----------------------------------------------------------------------------
2037 -- Procedure Name: LOG_ERROR
2038 --
2039 -- Description : Function to log error message in error interface table and
2040 -- conc prog log file.-- bug 12533707 --
2041 -- -----------------------------------------------------------------------------
2042 FUNCTION LOG_ERROR (
2043 org_id number,
2044 user_id number,
2045 login_id number,
2046 prog_appid number,
2047 prog_id number,
2048 req_id number,
2049 trans_id number,
2050 error_text varchar2,
2051 p_column_name VARCHAR2 := NULL,
2052 tbl_name varchar2,
2053 msg_name varchar2,
2054 err_text OUT NOCOPY VARCHAR2
2055 )
2056 RETURN INTEGER
2057 IS
2058
2059 l_sysdate DATE := SYSDATE;
2060 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level;
2061
2062 BEGIN
2063
2064 IF l_inv_debug_level IN(101, 102) THEN
2065 INVPUTLI.info('mtl_cross_references_pvt.LOG_ERROR: msg_name = '||msg_name);
2066 END IF;
2067
2068
2069 INSERT INTO mtl_interface_errors
2070 (
2071 TRANSACTION_ID,
2072 UNIQUE_ID,
2073 ORGANIZATION_ID,
2074 LAST_UPDATE_DATE,
2075 LAST_UPDATED_BY,
2076 CREATION_DATE,
2077 CREATED_BY,
2078 LAST_UPDATE_LOGIN,
2079 COLUMN_NAME,
2080 TABLE_NAME,
2081 MESSAGE_NAME,
2082 ERROR_MESSAGE,
2083 REQUEST_ID,
2084 PROGRAM_APPLICATION_ID,
2085 PROGRAM_ID,
2086 PROGRAM_UPDATE_DATE
2087 )
2088 VALUES
2089 (
2090 trans_id,
2091 mtl_system_items_interface_s.NEXTVAL,
2092 org_id,
2093 l_sysdate,
2094 user_id,
2095 l_sysdate,
2096 user_id,
2097 login_id,
2098 p_column_name,
2099 tbl_name,
2100 msg_name,
2101 SUBSTRB(error_text, 1,2000),
2102 req_id,
2103 prog_appid,
2104 prog_id,
2105 l_sysdate
2106 );
2107
2108 -- Output error information into the log file
2109 if (to_number(nvl(fnd_profile.value('CONC_REQUEST_ID'),0)) <> 0) then
2110 FND_FILE.PUT_LINE(FND_FILE.LOG,'************************************') ;
2111 FND_FILE.PUT_LINE(FND_FILE.LOG,'TRANSACTION ID : ' || trans_id);
2112 FND_FILE.PUT_LINE(FND_FILE.LOG,'ORGANIZATION ID : ' || org_id);
2113 FND_FILE.PUT_LINE(FND_FILE.LOG,'TABLE NAME : ' || tbl_name);
2114 FND_FILE.PUT_LINE(FND_FILE.LOG,'COLUMN NAME : ' || p_column_name);
2115 FND_FILE.PUT_LINE(FND_FILE.LOG,'MESSAGE NAME : ' || msg_name);
2116 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR MESSAGE : ' || substrb(error_text,1,2000));
2117 end if ;
2118
2119 IF l_inv_debug_level IN(101, 102) THEN
2120 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: TRANSACTION ID : ' || trans_id);
2121 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: ORGANIZATION ID : ' || org_id);
2122 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: TABLE NAME : ' || tbl_name);
2123 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: COLUMN NAME : ' || p_column_name);
2124 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: MESSAGE NAME : ' || msg_name);
2125 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: ERROR MESSAGE : ' || substrb(error_text,1,2000));
2126 END IF;
2127
2128 RETURN (0);
2129
2130 EXCEPTION
2131
2132 WHEN others THEN
2133 err_text := SUBSTRB('mtl_cross_references_pvt.LOG_ERROR: ' || SQLERRM, 1,240);
2134 RETURN (SQLCODE);
2135
2136 END LOG_ERROR;
2137
2138 -- -----------------------------------------------------------------------------
2139 -- Procedure Name: Del_Processed_Recs
2140 --
2141 -- Description : Method to delete processed rows from intf table.
2142
2143 -- -----------------------------------------------------------------------------
2144
2145 FUNCTION Del_Processed_Recs
2146 (
2147 err_text OUT NOCOPY VARCHAR2,
2148 com_flag IN NUMBER DEFAULT 1,
2149 p_data_set_id IN NUMBER DEFAULT -999
2150 )
2151 RETURN INTEGER
2152 IS
2153
2154 l_process_flag_7 NUMBER := 7;
2155 l_rownum NUMBER := 100000;
2156
2157 BEGIN
2158
2159 LOOP
2160 DELETE FROM MTL_CROSS_REFERENCES_INTERFACE
2161 WHERE process_flag = l_process_flag_7
2162 AND set_process_id=p_data_set_id
2163 AND rownum < l_rownum;
2164
2165 EXIT WHEN SQL%NOTFOUND;
2166
2167 IF com_flag = 1 THEN
2168 commit;
2169 END IF;
2170 END LOOP;
2171
2172 RETURN (0);
2173
2174 EXCEPTION
2175
2176 WHEN OTHERS THEN
2177 err_text := SUBSTR('MTL_CROSS_REFERENCES_PVT.Del_Processed_Recs' || SQLERRM, 1,240);
2178 RETURN (SQLCODE);
2179
2180 END Del_Processed_Recs;
2181
2182 END MTL_CROSS_REFERENCES_PVT;