[Home] [Help]
PACKAGE BODY: APPS.MTL_RELATED_ITEMS_PVT
Source
1 PACKAGE BODY MTL_RELATED_ITEMS_PVT AS
2 /* $Header: INVVRITB.pls 120.16.12020000.3 2013/04/10 09:31:06 ccsingh ship $ */
3
4 -----------------------------------------------
5 -- Write Debug statements to Concurrent Log --
6 -----------------------------------------------
7 PROCEDURE Write_Debug (p_msg IN VARCHAR2) IS
8 l_err_msg VARCHAR2(240);
9 BEGIN
10 -- If Profile set to TRUE --
11 IF (G_DEBUG = 1) THEN
12 FND_FILE.put_line(FND_FILE.LOG, p_msg);
13 END IF;
14
15 EXCEPTION
16 WHEN OTHERS THEN
17 l_err_msg := SUBSTRB(SQLERRM, 1,240);
18 FND_FILE.put_line(FND_FILE.LOG, 'LOGGING SQL ERROR => '||l_err_msg);
19 END Write_Debug;
20
21 -- -----------------------------------------------------------------------------
22 -- Procedure Name: Process_Rel_Item
23 --
24 -- Description : Main API to process (CREATE/UPDATE/DELETE) a single
25 -- Related Item record and its corresponding Planning Details
26 -- (Substitution Sets and Customer References).
27 -- -----------------------------------------------------------------------------
28 PROCEDURE Process_Rel_Item(
29 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
30 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
31 ,p_Rel_Item_Rec IN OUT NOCOPY MTL_RELATED_ITEMS_PUB.Rel_Item_Rec_Type
32 ,p_Pln_Info_Tbl IN OUT NOCOPY MTL_RELATED_ITEMS_PUB.Pln_Info_Tbl_Type
33 ,p_Cust_Ref_Tbl IN OUT NOCOPY MTL_RELATED_ITEMS_PUB.Cust_Ref_Tbl_Type
34 ,x_return_status OUT NOCOPY VARCHAR2
35 ,x_msg_count OUT NOCOPY NUMBER
36 ,x_msg_list OUT NOCOPY Error_Handler.Error_Tbl_Type) IS
37
38 -- Local variable declariations
39 l_api_name VARCHAR2(30) := 'Process_Rel_Item';
40 l_prod_exists VARCHAR(1); -- for checking security when PIM is installed
41 l_pim_exists VARCHAR(1); -- for checking security when PIM is installed
42 l_inv_item_exists VARCHAR(1);
43 l_rel_item_exists VARCHAR(1);
44 l_rel_exists VARCHAR(1);
45 l_rel_type_exists VARCHAR(1);
46 l_master_org_exists VARCHAR(1);
47 l_cust_ref_exists VARCHAR(1);
48 l_cust_ref_rel_exists VARCHAR(1);
49
50 l_Rel_Item_Rec MTL_RELATED_ITEMS_PUB.Rel_Item_Rec_Type; -- declaring related items record type
51 l_Pln_Info_Rec MTL_RELATED_ITEMS_PUB.Pln_Info_Rec_Type; -- declating planning information record type
52 l_Pln_Info_Tbl MTL_RELATED_ITEMS_PUB.Pln_Info_Tbl_Type; -- declaring planning information table type
53 l_Cust_Ref_Rec MTL_RELATED_ITEMS_PUB.Cust_Ref_Rec_Type; -- declating customer reference record type
54 l_Cust_Ref_Tbl MTL_RELATED_ITEMS_PUB.Cust_Ref_Tbl_Type; -- declaring customer reference table type
55
56 Returned_Row_Id VARCHAR2(30);
57
58
59 l_Token_Tbl Error_Handler.Token_Tbl_Type; -- For passing token in error msgs
60 l_msg_count NUMBER;
61 RETURNED_PLN_INFO_ID NUMBER;
62 VALIDATION_ERROR EXCEPTION;
63 resource_busy EXCEPTION;
64
65
66 -- Cursor for fetching the current values from the related items table
67 CURSOR mtl_rel_item_cur
68 (c_inventory_item_id NUMBER
69 ,c_related_item_id NUMBER
70 ,c_relationship_type_id NUMBER
71 ,c_organization_id NUMBER) IS
72 SELECT *
73 FROM mtl_related_items
74 WHERE
75 INVENTORY_ITEM_ID = c_inventory_item_id
76 AND RELATED_ITEM_ID = c_related_item_id
77 AND RELATIONSHIP_TYPE_ID = c_relationship_type_id
78 AND ORGANIZATION_ID = c_organization_id;
79
80 l_mtl_Rel_Item_rec MTL_RELATED_ITEMS%ROWTYPE; -- For fetching the data of above cursor
81
82
83 -- cursor for locking the record while updating and deleting
84 CURSOR mtl_rel_item_lock_b
85 (c_inventory_item_id NUMBER
86 ,c_related_item_id NUMBER
87 ,c_relationship_type_id NUMBER
88 ,c_organization_id NUMBER) IS
89 SELECT
90 INVENTORY_ITEM_ID
91 ,ORGANIZATION_ID
92 ,RELATED_ITEM_ID
93 ,RELATIONSHIP_TYPE_ID
94 ,RECIPROCAL_FLAG
95 ,START_DATE
96 ,END_DATE
97 ,ATTR_CONTEXT
98 ,ATTR_CHAR1
99 ,ATTR_CHAR2
100 ,ATTR_CHAR3
101 ,ATTR_CHAR4
102 ,ATTR_CHAR5
103 ,ATTR_CHAR6
104 ,ATTR_CHAR7
105 ,ATTR_CHAR8
106 ,ATTR_CHAR9
107 ,ATTR_CHAR10
108 ,ATTR_NUM1
109 ,ATTR_NUM2
110 ,ATTR_NUM3
111 ,ATTR_NUM4
112 ,ATTR_NUM5
113 ,ATTR_NUM6
114 ,ATTR_NUM7
115 ,ATTR_NUM8
116 ,ATTR_NUM9
117 ,ATTR_NUM10
118 ,ATTR_DATE1
119 ,ATTR_DATE2
120 ,ATTR_DATE3
121 ,ATTR_DATE4
122 ,ATTR_DATE5
123 ,ATTR_DATE6
124 ,ATTR_DATE7
125 ,ATTR_DATE8
126 ,ATTR_DATE9
127 ,ATTR_DATE10
128 ,PLANNING_ENABLED_FLAG
129 FROM MTL_RELATED_ITEMS
130 WHERE INVENTORY_ITEM_ID = c_inventory_item_id
131 AND RELATED_ITEM_ID = c_related_item_id
132 AND RELATIONSHIP_TYPE_ID = c_relationship_type_id
133 AND ORGANIZATION_ID = c_organization_id
134 FOR UPDATE OF INVENTORY_ITEM_ID NOWAIT; --??
135
136 l_lock_b_recinfo mtl_rel_item_lock_b%ROWTYPE;
137 PRAGMA EXCEPTION_INIT (resource_busy, -54); -- ??
138
139
140 -- Cursor for fetching the current values from the planning information table
141 -- for delete when deleting related item record
142 CURSOR mtl_rel_item_pln_info_cur
143 (c_inventory_item_id NUMBER
144 ,c_related_item_id NUMBER
145 ,c_relationship_type_id NUMBER
146 ,c_organization_id NUMBER) IS
147 SELECT *
148 FROM mtl_related_items_pln_info
149 WHERE
150 INVENTORY_ITEM_ID = c_inventory_item_id
151 AND RELATED_ITEM_ID = c_related_item_id
152 AND RELATIONSHIP_TYPE_ID = c_relationship_type_id
153 AND ORGANIZATION_ID = c_organization_id;
154
155 -- Cursor for fetching the current values from the planning information table
156 CURSOR mtl_pln_info_cur
157 (c_inventory_item_id NUMBER
158 ,c_related_item_id NUMBER
159 ,c_relationship_type_id NUMBER
160 ,c_organization_id NUMBER
161 ,c_pln_info_id NUMBER) IS
162 SELECT *
163 FROM mtl_related_items_pln_info
164 WHERE
165 INVENTORY_ITEM_ID = c_inventory_item_id
166 AND RELATED_ITEM_ID = c_related_item_id
167 AND RELATIONSHIP_TYPE_ID = c_relationship_type_id
168 AND ORGANIZATION_ID = c_organization_id
169 AND PLN_INFO_ID = c_pln_info_id;
170
171 l_mtl_Rel_Item_Pln_Info_rec MTL_RELATED_ITEMS_PLN_INFO%ROWTYPE; -- For fetching the data of above cursor
172
173
174 -- cursor for locking the record while updating and deleting
175 CURSOR mtl_rel_item_pln_info_lock_b
176 (c_inventory_item_id NUMBER
177 ,c_related_item_id NUMBER
178 ,c_relationship_type_id NUMBER
179 ,C_Organization_Id Number
180 ,c_pln_info_id NUMBER) IS
181 SELECT
182 INVENTORY_ITEM_ID
183 ,ORGANIZATION_ID
184 ,RELATED_ITEM_ID
185 ,RELATIONSHIP_TYPE_ID
186 ,PLN_INFO_ID
187 ,START_DATE
188 ,END_DATE
189 ,SUBSTITUTION_SET
190 ,PARTIAL_FULFILLMENT_FLAG
191 ,ALL_CUSTOMERS_FLAG
192 FROM MTL_RELATED_ITEMS_PLN_INFO
193 WHERE
194 INVENTORY_ITEM_ID = c_inventory_item_id
195 AND RELATED_ITEM_ID = c_related_item_id
196 AND RELATIONSHIP_TYPE_ID = c_relationship_type_id
197 AND ORGANIZATION_ID = c_organization_id
198 AND PLN_INFO_ID = c_pln_info_id
199 FOR UPDATE OF INVENTORY_ITEM_ID NOWAIT; --??
200
201 l_lock_b_pln_info_recinfo mtl_rel_item_pln_info_lock_b%ROWTYPE;
202 PRAGMA EXCEPTION_INIT (resource_busy, -54); -- ??
203
204 -- Cursor for fetching the current values from the planning information table
205 -- for delete when deleting related item record
206 CURSOR mtl_rel_item_cust_ref_cur
207 (c_inventory_item_id NUMBER
208 ,c_related_item_id NUMBER
209 ,c_relationship_type_id NUMBER
210 ,c_organization_id NUMBER) IS
211 SELECT *
212 FROM mtl_related_items_cust_ref
213 WHERE
214 INVENTORY_ITEM_ID = c_inventory_item_id
215 AND RELATED_ITEM_ID = c_related_item_id
216 AND RELATIONSHIP_TYPE_ID = C_RELATIONSHIP_TYPE_ID
217 AND ORGANIZATION_ID = c_organization_id;
218
219 -- Cursor for fetching the current values from the customer set table
220 CURSOR mtl_cust_ref_cur
221 (c_inventory_item_id NUMBER
222 ,c_related_item_id NUMBER
223 ,c_relationship_type_id NUMBER
224 ,c_organization_id NUMBER
225 ,c_pln_info_id NUMBER
226 ,c_customer_id NUMBER
227 ,c_site_use_id NUMBER) IS
228 SELECT *
229 FROM mtl_related_items_cust_ref
230 WHERE
231 INVENTORY_ITEM_ID = c_inventory_item_id
232 AND RELATED_ITEM_ID = c_related_item_id
233 AND RELATIONSHIP_TYPE_ID = c_relationship_type_id
234 AND ORGANIZATION_ID = c_organization_id
235 AND PLN_INFO_ID = c_pln_info_id
236 AND CUSTOMER_ID = c_customer_id
237 AND SITE_USE_ID = c_site_use_id;
238
239 l_mtl_Rel_Item_Cust_Ref_rec MTL_RELATED_ITEMS_CUST_REF%ROWTYPE; -- For fetching the data of above cursor
240
241 -- cursor for locking the record while updating and deleting
242 CURSOR mtl_rel_item_cust_ref_lock_b
243 (c_inventory_item_id NUMBER
244 ,c_related_item_id NUMBER
245 ,c_relationship_type_id NUMBER
246 ,c_organization_id NUMBER
247 ,c_pln_info_id NUMBER
248 ,c_customer_id NUMBER
249 ,c_site_use_id NUMBER) IS
250 SELECT
251 INVENTORY_ITEM_ID
252 ,ORGANIZATION_ID
253 ,RELATED_ITEM_ID
254 ,RELATIONSHIP_TYPE_ID
255 ,PLN_INFO_ID
256 ,CUSTOMER_ID
257 ,SITE_USE_ID
258 ,START_DATE
259 ,END_DATE
260 FROM MTL_RELATED_ITEMS_CUST_REF
261 WHERE
262 INVENTORY_ITEM_ID = c_inventory_item_id
263 AND RELATED_ITEM_ID = c_related_item_id
264 AND RELATIONSHIP_TYPE_ID = c_relationship_type_id
265 AND ORGANIZATION_ID = c_organization_id
266 AND PLN_INFO_ID = c_pln_info_id
267 AND CUSTOMER_ID = c_customer_id
268 AND SITE_USE_ID = c_site_use_id
269 FOR UPDATE OF INVENTORY_ITEM_ID NOWAIT; --??
270
271 l_lock_b_cust_ref_recinfo mtl_rel_item_cust_ref_lock_b%ROWTYPE;
272 PRAGMA EXCEPTION_INIT (resource_busy, -54); -- ??
273
274
275 BEGIN
276
277 SAVEPOINT MTL_RELATED_ITEMS_PVT;
278 x_return_status := FND_API.G_RET_STS_SUCCESS;
279 l_Rel_Item_Rec := p_Rel_Item_Rec;
280 l_Pln_Info_Tbl := p_Pln_Info_Tbl;
281 l_Cust_Ref_Tbl := p_Cust_Ref_Tbl;
282 l_prod_exists := 'y';
283 l_pim_exists := 'y';
284
285
286 -- Initialize message list
287 IF FND_API.To_Boolean (p_init_msg_list) THEN
288 Error_Handler.Initialize;
289 END IF;
290
291 Write_Debug('Starting to process Related Item record...');
292
293 -- Primary key columns cannot be left blank
294 IF l_Rel_Item_Rec.INVENTORY_ITEM_ID IS NULL OR
295 l_Rel_Item_Rec.INVENTORY_ITEM_ID = FND_API.G_MISS_NUM OR
296 l_Rel_Item_Rec.RELATED_ITEM_ID IS NULL OR
297 l_Rel_Item_Rec.RELATED_ITEM_ID = FND_API.G_MISS_NUM OR
298 l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID IS NULL OR
299 l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID = FND_API.G_MISS_NUM OR
300 l_Rel_Item_Rec.ORGANIZATION_ID IS NULL OR
301 l_Rel_Item_Rec.ORGANIZATION_ID = FND_API.G_MISS_NUM THEN
302
303 x_return_status := FND_API.G_RET_STS_ERROR;
304 Error_Handler.Add_Error_message
305 (
306 p_message_name => 'EGO_REL_ITEM_NULL_COLS'
307 ,p_application_id => 'EGO'
308 ,p_message_type => 'E'
309 ,p_entity_code => G_Entity_Code
310 ,p_entity_index => 1
311 ,p_table_name => G_Table_Name
312 );
313 RAISE VALIDATION_ERROR;
314 END IF;
315
316
317 -- check for valid transaction_type
318
319 IF (l_Rel_Item_Rec.Transaction_Type IS NULL ) OR ( --bug 12407548
320 l_Rel_Item_Rec.Transaction_Type <> 'CREATE' AND
321 l_Rel_Item_Rec.Transaction_Type <> 'UPDATE' AND
322 l_Rel_Item_Rec.Transaction_Type <> 'DELETE') THEN
323
324 x_return_status := FND_API.G_RET_STS_ERROR;
325 Error_Handler.Add_Error_message
326 (
327 p_message_name => 'EGO_REL_ITEM_INVALID_TRANS_TYP'
328 ,p_application_id => 'EGO'
329 ,p_message_type => 'E'
330 ,p_entity_code => G_Entity_Code
331 ,p_entity_index => 1
332 ,p_table_name => G_Table_Name
333 );
334 RAISE VALIDATION_ERROR;
335 END IF;
336
337 -- checking for privileges for inventory and related item
338 -- Verify for NON PIM Customer
339 BEGIN
340 SELECT 'x' INTO l_prod_exists
341 FROM fnd_grants
342 WHERE object_id IN
343 (SELECT object_id FROM fnd_objects
344 WHERE obj_name = 'EGO_ITEM');
345 EXCEPTION
346 WHEN Too_Many_Rows THEN
347 l_prod_exists := 'x';
348 WHEN No_Data_Found THEN
349 NULL;
350 END;
351
352 BEGIN
353 SELECT status INTO l_pim_exists
354 FROM FND_PRODUCT_INSTALLATIONS
355 WHERE application_id = 431;
356 EXCEPTION
357 WHEN No_Data_Found THEN
358 NULL;
359 END;
360
361 IF l_prod_exists = 'x' THEN
362 IF l_pim_exists = 'I' THEN
363 IF INV_EGO_REVISION_VALIDATE.check_data_security (
364 p_function => 'EGO_EDIT_RELATED_ITEMS'
365 ,p_object_name => 'EGO_ITEM'
366 ,p_instance_pk1_value => l_Rel_Item_Rec.INVENTORY_ITEM_ID
367 ,p_instance_pk2_value => FND_GLOBAL.org_id
368 ,P_User_Id => FND_GLOBAL.user_id) <> 'T'
369 OR
370 INV_EGO_REVISION_VALIDATE.check_data_security (
371 p_function => 'EGO_EDIT_RELATED_ITEMS'
372 ,p_object_name => 'EGO_ITEM'
373 ,p_instance_pk1_value => l_Rel_Item_Rec.RELATED_ITEM_ID
374 ,p_instance_pk2_value => FND_GLOBAL.org_id
375 ,P_User_Id => FND_GLOBAL.user_id) <> 'T'
376 THEN
377 Error_Handler.Add_Error_message
378 (
379 p_message_name => 'INV_IOI_ITEM_UPDATE_PRIV'
380 ,p_application_id => 'INV'
381 ,p_message_type => 'E'
382 ,p_entity_code => G_Entity_Code
383 ,p_entity_index => 1
384 ,p_table_name => 'MTL_RELATED_ITEMS'
385 );
386 RAISE VALIDATION_ERROR;
387 END IF;
388 END IF;
389 END IF;
390
391
392
393 -- can only edit if in master org
394 IF l_Rel_Item_Rec.ORGANIZATION_ID IS NOT NULL
395 AND l_Rel_Item_Rec.ORGANIZATION_ID <> FND_API.G_MISS_NUM THEN
396
397 BEGIN
398 SELECT 'x' INTO l_master_org_exists
399 FROM mtl_parameters
400 WHERE ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID
401 AND MASTER_ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID;
402 EXCEPTION
403 WHEN NO_DATA_FOUND THEN
404 l_Token_Tbl(1).Token_Name := 'ORG_CODE';
405 l_Token_Tbl(1).Token_Value := l_Rel_Item_Rec.ORGANIZATION_ID;
406 l_Token_Tbl(1).Translate := FALSE;
407
408 x_return_status := FND_API.g_RET_STS_ERROR;
409 Error_Handler.Add_Error_message
410 (
411 p_message_name => 'EGO_MASTERORG_NOT_VALID'
412 ,p_application_id => 'EGO'
413 ,p_token_tbl => l_Token_Tbl
414 ,p_message_type => 'E'
415 ,p_entity_code => G_Entity_Code
416 ,p_entity_index => 1
417 ,p_table_name => 'MTL_PARAMETERS'
418 );
419 RAISE VALIDATION_ERROR;
420 WHEN Too_Many_Rows THEN
421 NULL;
422 END;
423 END IF;
424
425 -- checking for item existance in MSIB
426 IF l_Rel_Item_Rec.INVENTORY_ITEM_ID IS NOT NULL
427 AND l_Rel_Item_Rec.INVENTORY_ITEM_ID<>FND_API.G_MISS_NUM THEN
428
429 BEGIN
430 SELECT 'x' INTO l_inv_item_exists
431 FROM mtl_system_items_b
432 WHERE INVENTORY_ITEM_ID = l_Rel_Item_Rec.INVENTORY_ITEM_ID
433 AND ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID;
434 EXCEPTION
435 WHEN NO_DATA_FOUND THEN
436
437 l_Token_Tbl(1).Token_Name := 'ITEM_NUMBER';
438 l_Token_Tbl(1).Token_Value := l_Rel_Item_Rec.INVENTORY_ITEM_ID;
439 l_Token_Tbl(1).Translate := FALSE;
440
441 x_return_status := FND_API.g_RET_STS_ERROR;
442 Error_Handler.Add_Error_message
443 (
444 p_message_name => 'EGO_INV_ITEM_NOT_VALID'
445 ,p_application_id => 'EGO'
446 ,p_token_tbl => l_Token_Tbl
447 ,p_message_type => 'E'
448 ,p_entity_code => G_Entity_Code
449 ,p_entity_index => 1
450 ,p_table_name => 'MTL_SYSTEM_ITEMS_B'
451 );
452 RAISE VALIDATION_ERROR;
453 WHEN Too_Many_Rows THEN
454 NULL;
455 END;
456 End If;
457
458 -- checking for related item existance in MSIB
459 IF l_Rel_Item_Rec.RELATED_ITEM_ID IS NOT NULL
460 AND l_Rel_Item_Rec.RELATED_ITEM_ID<>FND_API.G_MISS_NUM then
461
462 BEGIN
463 SELECT 'x' INTO l_rel_item_exists
464 FROM mtl_system_items_b
465 WHERE INVENTORY_ITEM_ID = l_Rel_Item_Rec.RELATED_ITEM_ID
466 AND ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID;
467 EXCEPTION
468 WHEN NO_DATA_FOUND THEN
469
470 l_Token_Tbl(1).Token_Name := 'ITEM_NUMBER';
471 l_Token_Tbl(1).Token_Value := l_Rel_Item_Rec.RELATED_ITEM_ID;
472 l_Token_Tbl(1).Translate := FALSE;
473
474 x_return_status := FND_API.g_RET_STS_ERROR;
475 Error_Handler.Add_Error_message
476 (
477 p_message_name => 'EGO_REL_ITEM_NOT_VALID'
478 ,p_application_id => 'EGO'
479 ,p_token_tbl => l_Token_Tbl
480 ,p_message_type => 'E'
481 ,p_entity_code => G_Entity_Code
482 ,p_entity_index => 1
483 ,p_table_name => 'MTL_SYSTEM_ITEMS_B'
484 );
485 RAISE VALIDATION_ERROR;
486 END;
487 END IF;
488
489 -- checking for related item existance in MSIB - update case
490 IF l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL IS NOT NULL
491 AND l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL <>FND_API.G_MISS_NUM then
492
493 BEGIN
494 SELECT 'x' INTO l_rel_item_exists
495 FROM mtl_system_items_b
496 WHERE INVENTORY_ITEM_ID = l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL
497 AND ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID;
498 EXCEPTION
499 WHEN NO_DATA_FOUND THEN
500
501 l_Token_Tbl(1).Token_Name := 'ITEM_NUMBER';
502 l_Token_Tbl(1).Token_Value := l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL;
503 l_Token_Tbl(1).Translate := FALSE;
504
505 x_return_status := FND_API.g_RET_STS_ERROR;
506 Error_Handler.Add_Error_message
507 (
508 p_message_name => 'EGO_REL_ITEM_NOT_VALID'
509 ,p_application_id => 'EGO'
510 ,p_token_tbl => l_Token_Tbl
511 ,p_message_type => 'E'
512 ,p_entity_code => G_Entity_Code
513 ,p_entity_index => 1
514 ,p_table_name => 'MTL_SYSTEM_ITEMS_B'
515 );
516 RAISE VALIDATION_ERROR;
517 END;
518 END IF;
519
520
521
522 -- checking for existance of relationship type
523 IF l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID IS NOT NULL
524 AND l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID <> FND_API.G_MISS_NUM THEN
525
526 BEGIN
527 SELECT 'x' INTO l_rel_type_exists
528 FROM mfg_lookups
529 WHERE lookup_type = 'MTL_RELATIONSHIP_TYPES'
530 AND enabled_flag = 'Y'
531 AND lookup_code >= 1
532 AND lookup_code = l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID;
533 EXCEPTION
534 WHEN No_Data_Found THEN
535
536 l_Token_Tbl(1).Token_Name := 'REL_TYPE';
537 l_Token_Tbl(1).Token_Value := l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID;
538 L_TOKEN_TBL(1).TRANSLATE := FALSE;
539
540 x_return_status := FND_API.G_RET_STS_ERROR;
541 Error_Handler.Add_Error_message
542 (
543 p_message_name => 'EGO_REL_TYPE_NOT_VALID'
544 ,P_APPLICATION_ID => 'EGO'
545 ,p_token_tbl => l_Token_Tbl
546 ,p_message_type => 'E'
547 ,p_entity_code => G_Entity_Code
548 ,p_entity_index => 1
549 ,p_table_name => 'MFG_LOOKUPS'
550 );
551 RAISE VALIDATION_ERROR;
552 END;
553 END IF;
554
555 -- checking for existance of relationship type -update case
556 IF l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL IS NOT NULL
557 AND l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL <> FND_API.G_MISS_NUM THEN
558
559 BEGIN
560 SELECT 'x' INTO l_rel_type_exists
561 FROM mfg_lookups
562 WHERE lookup_type = 'MTL_RELATIONSHIP_TYPES'
563 AND enabled_flag = 'Y'
564 AND lookup_code > =1
565 AND lookup_code = l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL;
566 EXCEPTION
567 WHEN No_Data_Found THEN
568
569 l_Token_Tbl(1).Token_Name := 'REL_TYPE';
570 l_Token_Tbl(1).Token_Value := l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL;
571 L_TOKEN_TBL(1).TRANSLATE := FALSE;
572
573 x_return_status := FND_API.G_RET_STS_ERROR;
574 Error_Handler.Add_Error_message
575 (
576 p_message_name => 'EGO_REL_TYPE_NOT_VALID'
577 ,P_APPLICATION_ID => 'EGO'
578 ,p_token_tbl => l_Token_Tbl
579 ,p_message_type => 'E'
580 ,p_entity_code => G_Entity_Code
581 ,p_entity_index => 1
582 ,p_table_name => 'MFG_LOOKUPS'
583 );
584 RAISE VALIDATION_ERROR;
585 END;
586 END IF;
587
588
589 -- checking for reciprocal flag, must Y/N. If NULL then set to N
590 IF l_Rel_Item_Rec.RECIPROCAL_FLAG IS NULL
591 OR l_Rel_Item_Rec.RECIPROCAL_FLAG = FND_API.G_MISS_CHAR THEN
592 l_Rel_Item_Rec.RECIPROCAL_FLAG := 'N';
593 END IF;
594
595 IF l_Rel_Item_Rec.RECIPROCAL_FLAG NOT IN ('Y','N') THEN
596 x_return_status := FND_API.G_RET_STS_ERROR;
597 Error_Handler.Add_Error_message
598 (
599 p_message_name => 'EGO_RECIPROCAL_FLAG_INVALID'
600 ,p_application_id => 'EGO'
601 ,p_message_type => 'E'
602 ,p_entity_code => G_Entity_Code
603 ,p_entity_index => 1
604 ,p_table_name => 'MTL_RELATED_ITEMS'
605 );
606 RAISE VALIDATION_ERROR;
607 END IF;
608
609 -- checking for planning enabled flag
610 IF l_Rel_Item_Rec.PLANNING_ENABLED_FLAG IS NULL
611 OR l_Rel_Item_Rec.PLANNING_ENABLED_FLAG = FND_API.G_MISS_CHAR THEN
612 l_Rel_Item_Rec.Planning_Enabled_Flag := 'N';
613 END IF;
614
615 IF l_Rel_Item_Rec.PLANNING_ENABLED_FLAG NOT IN ('Y','N') THEN
616 x_return_status := FND_API.G_RET_STS_ERROR;
617 Error_Handler.Add_Error_message
618 (
619 p_message_name => 'EGO_PLN_ENABLED_FLAG_INVALID'
620 ,p_application_id => 'EGO'
621 ,p_message_type => 'E'
622 ,p_entity_code => G_Entity_Code
623 ,p_entity_index => 1
624 ,p_table_name => 'MTL_RELATED_ITEMS'
625 );
626 RAISE VALIDATION_ERROR;
627 END IF;
628
629
630 -- create/update validations
631
632 IF l_Rel_Item_Rec.TRANSACTION_TYPE = 'CREATE' OR l_Rel_Item_Rec.TRANSACTION_TYPE = 'UPDATE' THEN
633
634 -- item cannot be related to self
635 -- bug 14403205
636 IF (l_Rel_Item_Rec.INVENTORY_ITEM_ID = l_Rel_Item_Rec.RELATED_ITEM_ID) OR (l_Rel_Item_Rec.INVENTORY_ITEM_ID =
637 l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL) THEN
638 x_return_status := FND_API.G_RET_STS_ERROR;
639 Error_Handler.Add_Error_message
640 (
641 p_message_name => 'EGO_RELATE_TO_SELF'
642 ,p_application_id => 'EGO'
643 ,p_message_type => 'E'
644 ,p_entity_code => G_Entity_Code
645 ,p_entity_index => 1
646 ,p_table_name => G_Table_Name
647 );
648 RAISE VALIDATION_ERROR;
649 END IF;
650
651 -- no effective dates if not of the four rel types
652 IF NOT (l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID = 8 OR l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL = 8
653 OR l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID = 15 OR l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL =15
654 OR l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID = 16 OR l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL = 16
655 OR l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID = 17 OR l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL = 17) AND
656 (L_Rel_Item_Rec.Start_Date Is Not Null Or
657 l_Rel_Item_Rec.END_DATE IS NOT NULL) AND
658 (l_Rel_Item_Rec.START_DATE <> FND_API.G_MISS_DATE OR
659 l_Rel_Item_Rec.END_DATE <> FND_API.G_MISS_DATE)THEN
660
661 x_return_status := FND_API.G_RET_STS_ERROR;
662 Error_Handler.Add_Error_message
663 (
664 p_message_name => 'EGO_REL_ITEM_DATE_INACTIVE'
665 ,p_application_id => 'EGO'
666 ,p_message_type => 'E'
667 ,p_entity_code => G_Entity_Code
668 ,p_entity_index => 1
669 ,p_table_name => G_Table_Name
670 );
671 Raise Validation_Error;
672 END IF;
673
674 -- if relationship type is 'split' 'merge' 'migration' or 'superseded' the effective dates are allowed
675 IF l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID = 8 OR l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL =8
676 OR l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID = 15 OR l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL =15
677 OR l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID = 16 OR l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL = 16
678 OR l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID = 17 OR l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL =17 THEN
679
680 IF l_Rel_Item_Rec.START_DATE IS NOT NULL AND -- both dates are given
681 l_Rel_Item_Rec.END_DATE IS NOT NULL AND
682 l_Rel_Item_Rec.START_DATE <> FND_API.G_MISS_DATE AND
683 l_Rel_Item_Rec.END_DATE <> FND_API.G_MISS_DATE THEN
684
685 IF l_Rel_Item_Rec.END_DATE < l_Rel_Item_Rec.START_DATE THEN
686 x_return_status := FND_API.G_RET_STS_ERROR;
687 Error_Handler.Add_Error_message
688 (
689 p_message_name => 'EGO_STARTDATE_PRECEDES_ENDDATE'
690 ,p_application_id => 'EGO'
691 ,p_message_type => 'E'
692 ,p_entity_code => G_Entity_Code
693 ,p_entity_index => 1
694 ,p_table_name => G_Table_Name
695 );
696 RAISE VALIDATION_ERROR;
697 END IF;
698 END IF;
699
700 IF l_Rel_Item_Rec.START_DATE IS NOT NULL
701 AND l_Rel_Item_Rec.START_DATE <> FND_API.G_MISS_DATE THEN
702 IF l_Rel_Item_Rec.START_DATE < SYSDATE THEN
703 x_return_status := FND_API.G_RET_STS_ERROR;
704 Error_Handler.Add_Error_message
705 (
706 p_message_name => 'EGO_SELECTED_DATE_INVALID'
707 ,p_application_id => 'EGO'
708 ,p_message_type => 'E'
709 ,p_entity_code => G_Entity_Code
710 ,p_entity_index => 1
711 ,p_table_name => G_Table_Name
712 );
713 RAISE VALIDATION_ERROR;
714 END IF;
715 END IF;
716
717 IF l_Rel_Item_Rec.END_DATE IS NOT NULL
718 AND l_Rel_Item_Rec.END_DATE <> FND_API.G_MISS_DATE THEN
719 IF l_Rel_Item_Rec.END_DATE < SYSDATE THEN
720 x_return_status := FND_API.G_RET_STS_ERROR;
721 Error_Handler.Add_Error_message
722 (
723 p_message_name => 'EGO_SELECTED_DATE_INVALID'
724 ,p_application_id => 'EGO'
725 ,p_message_type => 'E'
726 ,p_entity_code => G_Entity_Code
727 ,p_entity_index => 1
728 ,p_table_name => G_Table_Name
729 );
730 RAISE VALIDATION_ERROR;
731 END IF;
732 END IF;
733
734 END IF; -- if type split, merge, migration
735
736 -- bug 12668692
737 -- bug 14403276
738 IF l_Rel_Item_Rec.PLANNING_ENABLED_FLAG ='Y' AND
739 (l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID<>2 OR (l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL<>2 AND l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL<>FND_API.G_MISS_NUM))THEN
740
741 x_return_status := FND_API.G_RET_STS_ERROR;
742 Error_Handler.Add_Error_message
743 (
744 p_message_name => 'EGO_PLN_FLG_SEL_CONS'
745 ,p_application_id => 'EGO'
746 ,p_message_type => 'E'
747 ,p_entity_code => G_Entity_Code
748 ,p_entity_index => 1
749 ,p_table_name => 'MTL_RELATED_ITEMS'
750 );
751 RAISE VALIDATION_ERROR;
752 END IF;
753
754 END IF; -- if create/update
755
756
757 BEGIN
758 IF l_Rel_Item_Rec.TRANSACTION_TYPE = 'CREATE' THEN
759
760 -- checking that item and relationship type combination is unique
761
762 BEGIN
763 SELECT 'x' INTO l_rel_exists
764 FROM mtl_related_items
765 WHERE (INVENTORY_ITEM_ID = l_Rel_Item_Rec.INVENTORY_ITEM_ID
766 AND RELATIONSHIP_TYPE_ID= l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID
767 AND RELATED_ITEM_ID = l_Rel_Item_Rec.RELATED_ITEM_ID
768 AND ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID)
769 OR (INVENTORY_ITEM_ID = l_Rel_Item_Rec.RELATED_ITEM_ID -- checking for derived related items
770 AND RELATED_ITEM_ID = l_Rel_Item_Rec.INVENTORY_ITEM_ID
771 AND RELATIONSHIP_TYPE_ID= l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID
772 AND ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID
773 AND RECIPROCAL_FLAG = 'Y');
774
775 IF l_rel_exists='x' THEN
776 l_Token_Tbl(1).Token_Name := 'INV_ITEM';
777 l_Token_Tbl(1).Token_Value := l_Rel_Item_Rec.INVENTORY_ITEM_ID;
778 l_Token_Tbl(1).Translate := FALSE;
779 l_Token_Tbl(2).Token_Name := 'REL_TYPE';
780 l_Token_Tbl(2).Token_Value := l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID;
781 l_Token_Tbl(2).Translate := FALSE;
782 l_Token_Tbl(3).Token_Name := 'REL_ITEM';
783 l_Token_Tbl(3).Token_Value := l_Rel_Item_Rec.RELATED_ITEM_ID;
784 l_Token_Tbl(3).Translate := FALSE;
785
786
787 x_return_status:=FND_API.g_RET_STS_ERROR;
788 Error_Handler.Add_Error_message
789 (
790 p_message_name => 'EGO_RELATIONSHIP_EXISTS'
791 ,p_application_id => 'EGO'
792 ,p_token_tbl => l_Token_Tbl
793 ,p_message_type => 'E'
794 ,p_entity_code => G_Entity_Code
795 ,p_entity_index => 1
796 ,p_table_name => G_Table_Name
797 );
798 RAISE VALIDATION_ERROR;
799 END IF;
800 EXCEPTION
801 WHEN NO_DATA_FOUND THEN
802 NULL;
803 END;
804
805
806 -- Changing Global variable back to NULL before inserting.
807 SELECT Decode(l_Rel_Item_Rec.Inventory_Item_Id,FND_API.G_MISS_NUM, NULL, l_Rel_Item_Rec.Inventory_Item_Id),
808 Decode(l_Rel_Item_Rec.Organization_Id,FND_API.G_MISS_NUM, NULL, l_Rel_Item_Rec.Organization_Id),
809 Decode(l_Rel_Item_Rec.Related_Item_Id,FND_API.G_MISS_NUM, NULL, l_Rel_Item_Rec.Related_Item_Id),
810 Decode(l_Rel_Item_Rec.Relationship_Type_Id,FND_API.G_MISS_NUM, NULL, l_Rel_Item_Rec.Relationship_Type_Id),
811 Decode(l_Rel_Item_Rec.Reciprocal_Flag,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Reciprocal_Flag),
812 Decode(l_Rel_Item_Rec.Start_Date,FND_API.G_MISS_DATE, NULL, l_Rel_Item_Rec.Start_Date),
813 Decode(l_Rel_Item_Rec.End_Date,FND_API.G_MISS_DATE, NULL, l_Rel_Item_Rec.End_Date),
814 Decode(l_Rel_Item_Rec.Attr_Context,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Context),
815 Decode(l_Rel_Item_Rec.Attr_Char1,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char1),
816 Decode(l_Rel_Item_Rec.Attr_Char2,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char2),
817 Decode(l_Rel_Item_Rec.Attr_Char3,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char3),
818 Decode(l_Rel_Item_Rec.Attr_Char4,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char4),
819 Decode(l_Rel_Item_Rec.Attr_Char5,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char5),
820 Decode(l_Rel_Item_Rec.Attr_Char6,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char6),
821 Decode(l_Rel_Item_Rec.Attr_Char7,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char7),
822 Decode(l_Rel_Item_Rec.Attr_Char8,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char8),
823 Decode(l_Rel_Item_Rec.Attr_Char9,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char9),
824 Decode(l_Rel_Item_Rec.Attr_Char10,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Char10),
825 Decode(l_Rel_Item_Rec.Attr_Num1,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num1),
826 Decode(l_Rel_Item_Rec.Attr_Num2,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num2),
827 Decode(l_Rel_Item_Rec.Attr_Num3,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num3),
828 Decode(l_Rel_Item_Rec.Attr_Num4,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num4),
829 Decode(l_Rel_Item_Rec.Attr_Num5,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num5),
830 Decode(l_Rel_Item_Rec.Attr_Num6,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num6),
831 Decode(l_Rel_Item_Rec.Attr_Num7,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num7),
832 Decode(l_Rel_Item_Rec.Attr_Num8,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num8),
833 Decode(l_Rel_Item_Rec.Attr_Num9,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num9),
834 Decode(l_Rel_Item_Rec.Attr_Num10,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Num10),
835 Decode(l_Rel_Item_Rec.Attr_Date1,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date1),
836 Decode(l_Rel_Item_Rec.Attr_Date2,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date2),
837 Decode(l_Rel_Item_Rec.Attr_Date3,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date3),
838 Decode(l_Rel_Item_Rec.Attr_Date4,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date4),
839 Decode(l_Rel_Item_Rec.Attr_Date5,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date5),
840 Decode(l_Rel_Item_Rec.Attr_Date6,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date6),
841 Decode(l_Rel_Item_Rec.Attr_Date7,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date7),
842 Decode(l_Rel_Item_Rec.Attr_Date8,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date8),
843 Decode(l_Rel_Item_Rec.Attr_Date9,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date9),
844 Decode(l_Rel_Item_Rec.Attr_Date10,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Attr_Date10),
845 Decode(l_Rel_Item_Rec.Planning_Enabled_Flag,FND_API.G_MISS_CHAR, NULL, l_Rel_Item_Rec.Planning_Enabled_Flag)--,
846 --Decode(l_Rel_Item_Rec.Last_Update_Date,FND_API.G_MISS_DATE,NULL,l_Rel_Item_Rec.Last_Update_Date),
847 --Decode(l_Rel_Item_Rec.Last_Updated_By,FND_API.G_MISS_NUM,NULL,l_Rel_Item_Rec.Last_Updated_By),
848 --Decode(l_Rel_Item_Rec.Creation_Date,FND_API.G_MISS_DATE,NULL,l_Rel_Item_Rec.Creation_Date),
849 --Decode(l_Rel_Item_Rec.Created_By,FND_API.G_MISS_NUM,NULL,l_Rel_Item_Rec.Created_By),
850 --Decode(l_Rel_Item_Rec.Last_Update_Login,FND_API.G_MISS_NUM,NULL,l_Rel_Item_Rec.Last_Update_Login)
851
852 INTO l_Rel_Item_Rec.Inventory_Item_Id, l_Rel_Item_Rec.Organization_Id, l_Rel_Item_Rec.Related_Item_Id,
853 l_Rel_Item_Rec.Relationship_Type_Id, l_Rel_Item_Rec.Reciprocal_Flag, l_Rel_Item_Rec.Start_Date,
854 l_Rel_Item_Rec.End_Date, l_Rel_Item_Rec.Attr_Context, l_Rel_Item_Rec.Attr_Char1, l_Rel_Item_Rec.Attr_Char2,
855 l_Rel_Item_Rec.Attr_Char3, l_Rel_Item_Rec.Attr_Char4, l_Rel_Item_Rec.Attr_Char5, l_Rel_Item_Rec.Attr_Char6,
856 l_Rel_Item_Rec.Attr_Char7, l_Rel_Item_Rec.Attr_Char8, l_Rel_Item_Rec.Attr_Char9, l_Rel_Item_Rec.Attr_Char10,
857 l_Rel_Item_Rec.Attr_Num1, l_Rel_Item_Rec.Attr_Num2, l_Rel_Item_Rec.Attr_Num3, l_Rel_Item_Rec.Attr_Num4,
858 l_Rel_Item_Rec.Attr_Num5, l_Rel_Item_Rec.Attr_Num6, l_Rel_Item_Rec.Attr_Num7, l_Rel_Item_Rec.Attr_Num8,
859 l_Rel_Item_Rec.Attr_Num9, l_Rel_Item_Rec.Attr_Num10, l_Rel_Item_Rec.Attr_Date1, l_Rel_Item_Rec.Attr_Date2,
860 l_Rel_Item_Rec.Attr_Date3, l_Rel_Item_Rec.Attr_Date4, l_Rel_Item_Rec.Attr_Date5, l_Rel_Item_Rec.Attr_Date6,
861 l_Rel_Item_Rec.Attr_Date7, l_Rel_Item_Rec.Attr_Date8, l_Rel_Item_Rec.Attr_Date9, l_Rel_Item_Rec.Attr_Date10,
862 l_Rel_Item_Rec.Planning_Enabled_Flag--, l_Rel_Item_Rec.Last_Update_Date, l_Rel_Item_Rec.Last_Updated_By,
863 --l_Rel_Item_Rec.Creation_Date,l_Rel_Item_Rec.Created_By, l_Rel_Item_Rec.Last_Update_Login
864
865 FROM dual;
866
867 Write_Debug('Creating Related Item record...');
868
869 MTL_RELATED_ITEMS_PKG.INSERT_ROW(
870 X_ROWID => RETURNED_ROW_ID
871 ,X_INVENTORY_ITEM_ID => L_REL_ITEM_REC.INVENTORY_ITEM_ID
872 ,X_ORGANIZATION_ID => L_REL_ITEM_REC.ORGANIZATION_ID
873 ,X_RELATED_ITEM_ID => L_REL_ITEM_REC.RELATED_ITEM_ID
874 ,X_RELATIONSHIP_TYPE_ID => L_REL_ITEM_REC.RELATIONSHIP_TYPE_ID
875 ,X_RECIPROCAL_FLAG => L_REL_ITEM_REC.RECIPROCAL_FLAG
876 ,X_START_DATE => L_REL_ITEM_REC.START_DATE
877 ,X_END_DATE => L_REL_ITEM_REC.END_DATE
878 ,X_ATTR_CONTEXT => L_REL_ITEM_REC.ATTR_CONTEXT
879 ,X_ATTR_CHAR1 => L_REL_ITEM_REC.ATTR_CHAR1
880 ,X_ATTR_CHAR2 => L_REL_ITEM_REC.ATTR_CHAR2
881 ,X_ATTR_CHAR3 => L_REL_ITEM_REC.ATTR_CHAR3
882 ,X_ATTR_CHAR4 => L_REL_ITEM_REC.ATTR_CHAR4
883 ,X_ATTR_CHAR5 => L_REL_ITEM_REC.ATTR_CHAR5
884 ,X_ATTR_CHAR6 => L_REL_ITEM_REC.ATTR_CHAR6
885 ,X_ATTR_CHAR7 => L_REL_ITEM_REC.ATTR_CHAR7
886 ,X_ATTR_CHAR8 => L_REL_ITEM_REC.ATTR_CHAR8
887 ,X_ATTR_CHAR9 => L_REL_ITEM_REC.ATTR_CHAR9
888 ,X_ATTR_CHAR10 => L_REL_ITEM_REC.ATTR_CHAR10
889 ,X_ATTR_NUM1 => L_REL_ITEM_REC.ATTR_NUM1
890 ,X_ATTR_NUM2 => L_REL_ITEM_REC.ATTR_NUM2
891 ,X_ATTR_NUM3 => L_REL_ITEM_REC.ATTR_NUM3
892 ,X_ATTR_NUM4 => L_REL_ITEM_REC.ATTR_NUM4
893 ,X_ATTR_NUM5 => L_REL_ITEM_REC.ATTR_NUM5
894 ,X_ATTR_NUM6 => L_REL_ITEM_REC.ATTR_NUM6
895 ,X_ATTR_NUM7 => L_REL_ITEM_REC.ATTR_NUM7
896 ,X_ATTR_NUM8 => L_REL_ITEM_REC.ATTR_NUM8
897 ,X_ATTR_NUM9 => L_REL_ITEM_REC.ATTR_NUM9
898 ,X_ATTR_NUM10 => L_REL_ITEM_REC.ATTR_NUM10
899 ,X_ATTR_DATE1 => L_REL_ITEM_REC.ATTR_DATE1
900 ,X_ATTR_DATE2 => L_REL_ITEM_REC.ATTR_DATE2
901 ,X_ATTR_DATE3 => L_REL_ITEM_REC.ATTR_DATE3
902 ,X_ATTR_DATE4 => L_REL_ITEM_REC.ATTR_DATE4
903 ,X_ATTR_DATE5 => l_Rel_Item_Rec.Attr_Date5
904 ,X_ATTR_DATE6 => L_REL_ITEM_REC.ATTR_DATE6
905 ,X_ATTR_DATE7 => L_REL_ITEM_REC.ATTR_DATE7
906 ,X_ATTR_DATE8 => L_REL_ITEM_REC.ATTR_DATE8
907 ,X_ATTR_DATE9 => L_REL_ITEM_REC.ATTR_DATE9
908 ,X_ATTR_DATE10 => L_REL_ITEM_REC.ATTR_DATE10
909 ,X_PLANNING_ENABLED_FLAG => L_REL_ITEM_REC.PLANNING_ENABLED_FLAG
910 --,X_CREATION_DATE => Nvl(l_Rel_Item_Rec.Creation_Date, SYSDATE)
911 --,X_CREATED_BY => NVL(L_REL_ITEM_REC.CREATED_BY, FND_GLOBAL.USER_ID)
912 --,X_LAST_UPDATE_DATE => NVL(L_REL_ITEM_REC.LAST_UPDATE_DATE, sysdate)
913 --,X_LAST_UPDATED_BY => NVL(L_REL_ITEM_REC.LAST_UPDATED_BY, FND_GLOBAL.USER_ID)
914 --,X_LAST_UPDATE_LOGIN => NVL(L_REL_ITEM_REC.LAST_UPDATE_LOGIN, FND_GLOBAL.LOGIN_ID)
915 ,X_CREATION_DATE => SYSDATE
916 ,X_CREATED_BY =>FND_GLOBAL.USER_ID
917 ,X_LAST_UPDATE_DATE => sysdate
918 ,X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
919 ,X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
920 ,X_OBJECT_VERSION_NUMBER => NULL
921 );
922 END IF;
923
924 -- raising post business event
925 BEGIN
926 INV_ITEM_EVENTS_PVT.Raise_Events(
927 p_event_name => 'EGO_WF_WRAPPER_PVT.G_REL_ITEM_CHANGE_EVENT'
928 ,p_dml_type => 'CREATE'
929 ,p_inventory_item_id => L_REL_ITEM_REC.Inventory_Item_Id
930 ,p_organization_id => L_REL_ITEM_REC.Organization_Id
931 ,p_related_item_id => L_REL_ITEM_REC.Related_Item_Id
932 ,p_relationship_type_id => L_REL_ITEM_REC.Relationship_Type_Id
933 );
934 EXCEPTION
935 WHEN OTHERS THEN
936 NULL;
937 END;
938
939
940 IF l_Rel_Item_Rec.Transaction_Type = 'UPDATE' THEN
941
942 -- null columns have already been checked earlier
943
944 -- getting original values and checking for existance of record
945 OPEN mtl_rel_item_cur(l_Rel_Item_Rec.INVENTORY_ITEM_ID
946 ,l_Rel_Item_Rec.RELATED_ITEM_ID
947 ,l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID
948 ,l_Rel_Item_Rec.ORGANIZATION_ID);
949 FETCH mtl_rel_item_cur INTO l_mtl_Rel_Item_rec;
950 IF mtl_rel_item_cur%NOTFOUND THEN
951 x_return_status:=FND_API.G_RET_STS_ERROR;
952 Error_Handler.Add_Error_message
953 (
954 p_message_name => 'EGO_REL_ITEM_NOTEXISTS'
955 ,p_application_id => 'EGO'
956 ,p_message_type => 'E'
957 ,p_entity_code => G_Entity_Code
958 ,p_entity_index => 1
959 ,p_table_name => G_Table_Name
960 );
961 CLOSE mtl_rel_item_cur;
962 RAISE VALIDATION_ERROR;
963 END IF;
964 CLOSE mtl_rel_item_cur;
965
966 --
967 -- To nullify columns during update through interface route, use -999999 for numbers, '!' for chars, '31-Dec-1999' for dates
968 --
969
970 SELECT Decode(Nvl(l_Rel_Item_Rec.Inventory_Item_Id,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM, l_mtl_Rel_Item_rec.Inventory_Item_Id, -999999,NULL, l_Rel_Item_Rec.Inventory_Item_Id),
971 Decode(Nvl(l_Rel_Item_Rec.Organization_Id,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM, l_mtl_Rel_Item_rec.Organization_Id, -999999,NULL, l_Rel_Item_Rec.Organization_Id),
972 Decode(Nvl(l_Rel_Item_Rec.Related_Item_Id,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM, l_mtl_Rel_Item_rec.Related_Item_Id, -999999,NULL, l_Rel_Item_Rec.Related_Item_Id),
973 Decode(Nvl(l_Rel_Item_Rec.Relationship_Type_Id,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM, l_mtl_Rel_Item_rec.Relationship_Type_Id, -999999,NULL, l_Rel_Item_Rec.Relationship_Type_Id),
974 -- using to replace new values.
975 Decode(Nvl(l_Rel_Item_Rec.Related_Item_Id_upd_val,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM, l_mtl_Rel_Item_rec.Related_Item_Id, -999999,NULL, l_Rel_Item_Rec.Related_Item_Id_upd_val),
976 Decode(Nvl(l_Rel_Item_Rec.Relationship_Type_Id_upd_val,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM, l_mtl_Rel_Item_rec.Relationship_Type_Id, -999999,NULL, l_Rel_Item_Rec.Relationship_Type_Id_upd_val),
977
978 Decode(Nvl(l_Rel_Item_Rec.Reciprocal_Flag,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Reciprocal_Flag, '!',NULL, l_Rel_Item_Rec.Reciprocal_Flag),
979 Decode(Nvl(l_Rel_Item_Rec.Start_Date,FND_API.G_MISS_DATE),FND_API.G_MISS_DATE, l_mtl_Rel_Item_rec.Start_Date, to_date('31-12-9999','DD-MM-YYYY'),NULL, l_Rel_Item_Rec.Start_Date),
980 Decode(Nvl(l_Rel_Item_Rec.End_Date,FND_API.G_MISS_DATE),FND_API.G_MISS_DATE, l_mtl_Rel_Item_rec.End_Date, to_date('31-12-9999','DD-MM-YYYY'),NULL, l_Rel_Item_Rec.End_Date),
981 Decode(Nvl(l_Rel_Item_Rec.Attr_Context,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Context, '!',NULL, l_Rel_Item_Rec.Attr_Context),
982 Decode(Nvl(l_Rel_Item_Rec.Attr_Char1,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char1, '!',NULL, l_Rel_Item_Rec.Attr_Char1),
983 Decode(Nvl(l_Rel_Item_Rec.Attr_Char2,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char2, '!',NULL, l_Rel_Item_Rec.Attr_Char2),
984 Decode(Nvl(l_Rel_Item_Rec.Attr_Char3,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char3, '!',NULL, l_Rel_Item_Rec.Attr_Char3),
985 Decode(Nvl(l_Rel_Item_Rec.Attr_Char4,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char4, '!',NULL, l_Rel_Item_Rec.Attr_Char4),
986 Decode(Nvl(l_Rel_Item_Rec.Attr_Char5,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char5, '!',NULL, l_Rel_Item_Rec.Attr_Char5),
987 Decode(Nvl(l_Rel_Item_Rec.Attr_Char6,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char6, '!',NULL, l_Rel_Item_Rec.Attr_Char6),
988 Decode(Nvl(l_Rel_Item_Rec.Attr_Char7,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char7, '!',NULL, l_Rel_Item_Rec.Attr_Char7),
989 Decode(Nvl(l_Rel_Item_Rec.Attr_Char8,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char8, '!',NULL, l_Rel_Item_Rec.Attr_Char8),
990 Decode(Nvl(l_Rel_Item_Rec.Attr_Char9,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char9, '!',NULL, l_Rel_Item_Rec.Attr_Char9),
991 Decode(Nvl(l_Rel_Item_Rec.Attr_Char10,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Char10, '!',NULL, l_Rel_Item_Rec.Attr_Char10),
992 Decode(Nvl(l_Rel_Item_Rec.Attr_Num1,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num1, '!',NULL, l_Rel_Item_Rec.Attr_Num1),
993 Decode(Nvl(l_Rel_Item_Rec.Attr_Num2,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num2, '!',NULL, l_Rel_Item_Rec.Attr_Num2),
994 Decode(Nvl(l_Rel_Item_Rec.Attr_Num3,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num3, '!',NULL, l_Rel_Item_Rec.Attr_Num3),
995 Decode(Nvl(l_Rel_Item_Rec.Attr_Num4,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num4, '!',NULL, l_Rel_Item_Rec.Attr_Num4),
996 Decode(Nvl(l_Rel_Item_Rec.Attr_Num5,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num5, '!',NULL, l_Rel_Item_Rec.Attr_Num5),
997 Decode(Nvl(l_Rel_Item_Rec.Attr_Num6,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num6, '!',NULL, l_Rel_Item_Rec.Attr_Num6),
998 Decode(Nvl(l_Rel_Item_Rec.Attr_Num7,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num7, '!',NULL, l_Rel_Item_Rec.Attr_Num7),
999 Decode(Nvl(l_Rel_Item_Rec.Attr_Num8,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num8, '!',NULL, l_Rel_Item_Rec.Attr_Num8),
1000 Decode(Nvl(l_Rel_Item_Rec.Attr_Num9,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num9, '!',NULL, l_Rel_Item_Rec.Attr_Num9),
1001 Decode(Nvl(l_Rel_Item_Rec.Attr_Num10,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Num10, '!',NULL, l_Rel_Item_Rec.Attr_Num10),
1002 Decode(Nvl(l_Rel_Item_Rec.Attr_Date1,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date1, '!',NULL, l_Rel_Item_Rec.Attr_Date1),
1003 Decode(Nvl(l_Rel_Item_Rec.Attr_Date2,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date2, '!',NULL, l_Rel_Item_Rec.Attr_Date2),
1004 Decode(Nvl(l_Rel_Item_Rec.Attr_Date3,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date3, '!',NULL, l_Rel_Item_Rec.Attr_Date3),
1005 Decode(Nvl(l_Rel_Item_Rec.Attr_Date4,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date4, '!',NULL, l_Rel_Item_Rec.Attr_Date4),
1006 Decode(Nvl(l_Rel_Item_Rec.Attr_Date5,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date5, '!',NULL, l_Rel_Item_Rec.Attr_Date5),
1007 Decode(Nvl(l_Rel_Item_Rec.Attr_Date6,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date6, '!',NULL, l_Rel_Item_Rec.Attr_Date6),
1008 Decode(Nvl(l_Rel_Item_Rec.Attr_Date7,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date7, '!',NULL, l_Rel_Item_Rec.Attr_Date7),
1009 Decode(Nvl(l_Rel_Item_Rec.Attr_Date8,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date8, '!',NULL, l_Rel_Item_Rec.Attr_Date8),
1010 Decode(Nvl(l_Rel_Item_Rec.Attr_Date9,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Attr_Date9, '!',NULL, l_Rel_Item_Rec.Attr_Date9),
1011 DECODE(Nvl(L_REL_ITEM_REC.ATTR_DATE10,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, L_MTL_REL_ITEM_REC.ATTR_DATE10, '!',NULL, L_REL_ITEM_REC.ATTR_DATE10),
1012 Decode(Nvl(l_Rel_Item_Rec.Planning_Enabled_Flag,FND_API.G_MISS_CHAR),FND_API.G_MISS_CHAR, l_mtl_Rel_Item_rec.Planning_Enabled_Flag, '!',NULL, l_Rel_Item_Rec.Planning_Enabled_Flag)--,
1013 -- Decode(Nvl(l_Rel_Item_Rec.Last_Update_Date,FND_API.G_MISS_DATE),FND_API.G_MISS_DATE,NULL,l_Rel_Item_Rec.Last_Update_Date),
1014 -- Decode(Nvl(l_Rel_Item_Rec.Last_Updated_By,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,NULL,-999999,NULL,l_Rel_Item_Rec.Last_Updated_By),
1015 -- DECODE(Nvl(L_REL_ITEM_REC.CREATION_DATE,FND_API.G_MISS_DATE),FND_API.G_MISS_DATE,L_MTL_REL_ITEM_REC.CREATION_DATE,l_Rel_Item_Rec.CREATION_DATE),
1016 -- DECODE(Nvl(l_Rel_Item_Rec.Created_By,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,l_mtl_Rel_Item_rec.Created_By,-999999,NULL,l_Rel_Item_Rec.Created_By),
1017 -- Decode(Nvl(l_Rel_Item_Rec.Last_Update_Login,FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,NULL,-999999,NULL,l_Rel_Item_Rec.Last_Update_Login)
1018
1019 INTO l_Rel_Item_Rec.Inventory_Item_Id, l_Rel_Item_Rec.Organization_Id, l_Rel_Item_Rec.Related_Item_Id,
1020 l_Rel_Item_Rec.Relationship_Type_Id,l_Rel_Item_Rec.Related_Item_Id_upd_val,l_Rel_Item_Rec.Relationship_Type_Id_upd_val,
1021 l_Rel_Item_Rec.Reciprocal_Flag, l_Rel_Item_Rec.Start_Date,
1022 l_Rel_Item_Rec.End_Date, l_Rel_Item_Rec.Attr_Context, l_Rel_Item_Rec.Attr_Char1, l_Rel_Item_Rec.Attr_Char2,
1023 l_Rel_Item_Rec.Attr_Char3, l_Rel_Item_Rec.Attr_Char4, l_Rel_Item_Rec.Attr_Char5, l_Rel_Item_Rec.Attr_Char6,
1024 l_Rel_Item_Rec.Attr_Char7, l_Rel_Item_Rec.Attr_Char8, l_Rel_Item_Rec.Attr_Char9, l_Rel_Item_Rec.Attr_Char10,
1025 l_Rel_Item_Rec.Attr_Num1, l_Rel_Item_Rec.Attr_Num2, l_Rel_Item_Rec.Attr_Num3, l_Rel_Item_Rec.Attr_Num4,
1026 l_Rel_Item_Rec.Attr_Num5, l_Rel_Item_Rec.Attr_Num6, l_Rel_Item_Rec.Attr_Num7, l_Rel_Item_Rec.Attr_Num8,
1027 l_Rel_Item_Rec.Attr_Num9, l_Rel_Item_Rec.Attr_Num10, l_Rel_Item_Rec.Attr_Date1, l_Rel_Item_Rec.Attr_Date2,
1028 l_Rel_Item_Rec.Attr_Date3, l_Rel_Item_Rec.Attr_Date4, l_Rel_Item_Rec.Attr_Date5, l_Rel_Item_Rec.Attr_Date6,
1029 L_REL_ITEM_REC.ATTR_DATE7, L_REL_ITEM_REC.ATTR_DATE8, L_REL_ITEM_REC.ATTR_DATE9, L_REL_ITEM_REC.ATTR_DATE10,
1030 l_Rel_Item_Rec.Planning_Enabled_Flag--, l_Rel_Item_Rec.Last_Update_Date, l_Rel_Item_Rec.Last_Updated_By,
1031 -- l_Rel_Item_Rec.Creation_Date,l_Rel_Item_Rec.Created_By, l_Rel_Item_Rec.Last_Update_Login
1032
1033 FROM dual;
1034
1035 --*** to check for duplicate record?
1036
1037 IF
1038 --l_mtl_Rel_Item_rec.INVENTORY_ITEM_ID <> l_Rel_Item_Rec.INVENTORY_ITEM_ID OR
1039 l_mtl_Rel_Item_rec.RELATIONSHIP_TYPE_ID <> l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL
1040 OR l_mtl_Rel_Item_rec.RELATED_ITEM_ID <> l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL
1041 -- OR l_mtl_Rel_Item_rec.ORGANIZATION_ID <> l_Rel_Item_Rec.ORGANIZATION_ID
1042 THEN
1043
1044 BEGIN
1045 SELECT 'x' INTO l_rel_exists
1046 FROM mtl_related_items
1047 WHERE (INVENTORY_ITEM_ID = l_Rel_Item_Rec.INVENTORY_ITEM_ID
1048 AND RELATIONSHIP_TYPE_ID= l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL
1049 AND RELATED_ITEM_ID = l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL
1050 AND ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID)
1051 OR (INVENTORY_ITEM_ID = l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL -- checking for derived related items
1052 AND RELATED_ITEM_ID = l_Rel_Item_Rec.INVENTORY_ITEM_ID
1053 AND RELATIONSHIP_TYPE_ID= l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL
1054 AND ORGANIZATION_ID = l_Rel_Item_Rec.ORGANIZATION_ID
1055 AND RECIPROCAL_FLAG = 'Y');
1056
1057 IF l_rel_exists='x' THEN
1058 l_Token_Tbl(1).Token_Name := 'INVENTORY_ITEM_ID';
1059 l_Token_Tbl(1).Token_Value := l_Rel_Item_Rec.INVENTORY_ITEM_ID;
1060 l_Token_Tbl(1).Translate := FALSE;
1061 L_Token_Tbl(2).Token_Name := 'RELATIONSHIP_TYPE';
1062 l_Token_Tbl(2).Token_Value := l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL;
1063 l_Token_Tbl(2).Translate := FALSE;
1064 l_Token_Tbl(3).Token_Name := 'RELATED_ITEM_ID';
1065 l_Token_Tbl(3).Token_Value := l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL;
1066 l_Token_Tbl(3).Translate := FALSE;
1067 l_Token_Tbl(4).Token_Name := 'ORGANIZATION_ID';
1068 l_Token_Tbl(4).Token_Value := l_Rel_Item_Rec.ORGANIZATION_ID;
1069 l_Token_Tbl(4).Translate := FALSE;
1070
1071
1072 x_return_status:=FND_API.g_RET_STS_ERROR;
1073 Error_Handler.Add_Error_message
1074 (
1075 p_message_name => 'EGO_RELATIONSHIP_EXISTS'
1076 ,p_application_id => 'EGO'
1077 ,p_token_tbl => l_Token_Tbl
1078 ,p_message_type => 'E'
1079 ,p_entity_code => G_Entity_Code
1080 ,p_entity_index => 1
1081 ,p_table_name => G_Table_Name
1082 );
1083 RAISE VALIDATION_ERROR;
1084 END IF;
1085 EXCEPTION
1086 WHEN NO_DATA_FOUND THEN
1087 NULL;
1088 END;
1089 END IF;
1090
1091 BEGIN SELECT ROWID INTO Returned_Row_Id
1092 FROM MTL_RELATED_ITEMS
1093 WHERE INVENTORY_ITEM_ID = L_REL_ITEM_REC.INVENTORY_ITEM_ID
1094 AND ORGANIZATION_ID = L_REL_ITEM_REC.ORGANIZATION_ID
1095 AND RELATED_ITEM_ID = L_REL_ITEM_REC.RELATED_ITEM_ID
1096 AND RELATIONSHIP_TYPE_ID = L_REL_ITEM_REC.RELATIONSHIP_TYPE_ID;
1097 END;
1098
1099
1100 -- locking the row
1101 OPEN mtl_rel_item_lock_b(l_Rel_Item_Rec.INVENTORY_ITEM_ID
1102 ,l_Rel_Item_Rec.RELATED_ITEM_ID
1103 ,l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID
1104 ,l_Rel_Item_Rec.ORGANIZATION_ID);
1105 FETCH mtl_rel_item_lock_b INTO l_lock_b_recinfo;
1106 CLOSE mtl_rel_item_lock_b;
1107
1108 Write_Debug('Updating Related Item record...');
1109
1110 -- all validation passed, so assigning new values.
1111
1112 if l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID <> l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL
1113 OR l_Rel_Item_Rec.RELATED_ITEM_ID <> l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL then
1114
1115 l_Rel_Item_Rec.RELATED_ITEM_ID:=l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL;
1116 l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID:=l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL;
1117
1118 end if ;
1119
1120 -- calling update
1121 MTL_RELATED_ITEMS_PKG.UPDATE_ROW(
1122 X_ROWID => Returned_Row_Id
1123 ,X_INVENTORY_ITEM_ID => l_Rel_Item_Rec.Inventory_Item_Id
1124 ,X_ORGANIZATION_ID => l_Rel_Item_Rec.Organization_Id
1125 ,X_RELATED_ITEM_ID => l_Rel_Item_Rec.Related_Item_Id
1126 ,X_RELATIONSHIP_TYPE_ID => l_Rel_Item_Rec.Relationship_Type_Id
1127 ,X_RECIPROCAL_FLAG => l_Rel_Item_Rec.Reciprocal_Flag
1128 ,X_START_DATE => l_Rel_Item_Rec.Start_Date
1129 ,X_END_DATE => l_Rel_Item_Rec.End_Date
1130 ,X_ATTR_CONTEXT => l_Rel_Item_Rec.Attr_Context
1131 ,X_ATTR_CHAR1 => l_Rel_Item_Rec.Attr_Char1
1132 ,X_ATTR_CHAR2 => l_Rel_Item_Rec.Attr_Char2
1133 ,X_ATTR_CHAR3 => l_Rel_Item_Rec.Attr_Char3
1134 ,X_ATTR_CHAR4 => l_Rel_Item_Rec.Attr_Char4
1135 ,X_Attr_Char5 => L_Rel_Item_Rec.Attr_Char5
1136 ,X_ATTR_CHAR6 => l_Rel_Item_Rec.Attr_Char6
1137 ,X_ATTR_CHAR7 => l_Rel_Item_Rec.Attr_Char7
1138 ,X_ATTR_CHAR8 => l_Rel_Item_Rec.Attr_Char8
1139 ,X_ATTR_CHAR9 => l_Rel_Item_Rec.Attr_Char9
1140 ,X_ATTR_CHAR10 => l_Rel_Item_Rec.Attr_Char10
1141 ,X_ATTR_NUM1 => l_Rel_Item_Rec.Attr_Num1
1142 ,X_ATTR_NUM2 => l_Rel_Item_Rec.Attr_Num2
1143 ,X_ATTR_NUM3 => l_Rel_Item_Rec.Attr_Num3
1144 ,X_ATTR_NUM4 => l_Rel_Item_Rec.Attr_Num4
1145 ,X_ATTR_NUM5 => l_Rel_Item_Rec.Attr_Num5
1146 ,X_Attr_Num6 => l_Rel_Item_Rec.Attr_Num6
1147 ,X_ATTR_NUM7 => l_Rel_Item_Rec.Attr_Num7
1148 ,X_ATTR_NUM8 => l_Rel_Item_Rec.Attr_Num8
1149 ,X_ATTR_NUM9 => l_Rel_Item_Rec.Attr_Num9
1150 ,X_ATTR_NUM10 => l_Rel_Item_Rec.Attr_Num10
1151 ,X_ATTR_DATE1 => l_Rel_Item_Rec.Attr_Date1
1152 ,X_ATTR_DATE2 => l_Rel_Item_Rec.Attr_Date2
1153 ,X_ATTR_DATE3 => l_Rel_Item_Rec.Attr_Date3
1154 ,X_ATTR_DATE4 => l_Rel_Item_Rec.Attr_Date4
1155 ,X_ATTR_DATE5 => l_Rel_Item_Rec.Attr_Date5
1156 ,X_ATTR_DATE6 => l_Rel_Item_Rec.Attr_Date6
1157 ,X_ATTR_DATE7 => l_Rel_Item_Rec.Attr_Date7
1158 ,X_ATTR_DATE8 => l_Rel_Item_Rec.Attr_Date8
1159 ,X_ATTR_DATE9 => l_Rel_Item_Rec.Attr_Date9
1160 ,X_ATTR_DATE10 => l_Rel_Item_Rec.Attr_Date10
1161 ,X_PLANNING_ENABLED_FLAG => l_Rel_Item_Rec.Planning_Enabled_Flag
1162 --,X_LAST_UPDATE_DATE => Nvl(l_Rel_Item_Rec.Last_Update_Date, SYSDATE)
1163 --,X_LAST_UPDATED_BY => Nvl(l_Rel_Item_Rec.Last_Updated_By, FND_GLOBAL.USER_ID)
1164 --,X_LAST_UPDATE_LOGIN => Nvl(l_Rel_Item_Rec.Last_Update_Login, FND_GLOBAL.LOGIN_ID)
1165 ,X_LAST_UPDATE_DATE => SYSDATE
1166 ,X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
1167 ,X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
1168 );
1169 END IF;
1170
1171 -- raising post business event
1172 BEGIN
1173 INV_ITEM_EVENTS_PVT.Raise_Events(
1174 p_event_name => 'EGO_WF_WRAPPER_PVT.G_REL_ITEM_CHANGE_EVENT'
1175 ,p_dml_type => 'UPDATE'
1176 ,p_inventory_item_id => L_REL_ITEM_REC.Inventory_Item_Id
1177 ,p_organization_id => L_REL_ITEM_REC.Organization_Id
1178 ,p_related_item_id => L_REL_ITEM_REC.Related_Item_Id
1179 ,p_relationship_type_id => L_REL_ITEM_REC.Relationship_Type_Id
1180 );
1181 EXCEPTION
1182 WHEN OTHERS THEN
1183 NULL;
1184 END;
1185
1186 IF l_Rel_Item_Rec.Transaction_Type = 'DELETE' THEN
1187
1188 BEGIN
1189 -- check for record existance --
1190 OPEN mtl_rel_item_cur(l_Rel_Item_Rec.INVENTORY_ITEM_ID
1191 ,l_Rel_Item_Rec.RELATED_ITEM_ID
1192 ,l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID
1193 ,l_Rel_Item_Rec.ORGANIZATION_ID);
1194 FETCH mtl_rel_item_cur INTO l_mtl_Rel_Item_rec;
1195 IF mtl_rel_item_cur%NOTFOUND THEN
1196 x_return_status:=FND_API.G_RET_STS_ERROR;
1197 Error_Handler.Add_Error_message
1198 (
1199 p_message_name => 'EGO_REL_ITEM_NOTEXISTS'
1200 ,p_application_id => 'EGO'
1201 ,p_token_tbl => l_Token_Tbl
1202 ,p_message_type => 'E'
1203 ,p_entity_code => G_Entity_Code
1204 ,p_entity_index => 1
1205 ,p_table_name => G_Table_Name
1206 );
1207
1208 CLOSE mtl_rel_item_cur;
1209 RAISE VALIDATION_ERROR;
1210 END IF;
1211 CLOSE mtl_rel_item_cur;
1212
1213 -- locking row
1214 OPEN mtl_rel_item_lock_b(l_Rel_Item_Rec.INVENTORY_ITEM_ID
1215 ,l_Rel_Item_Rec.RELATED_ITEM_ID
1216 ,l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID
1217 ,l_Rel_Item_Rec.ORGANIZATION_ID);
1218 FETCH mtl_rel_item_lock_b INTO l_lock_b_recinfo;
1219 CLOSE mtl_rel_item_lock_b;
1220
1221 BEGIN SELECT ROWID INTO Returned_Row_Id
1222 FROM MTL_RELATED_ITEMS
1223 WHERE INVENTORY_ITEM_ID = L_REL_ITEM_REC.INVENTORY_ITEM_ID
1224 AND ORGANIZATION_ID = L_REL_ITEM_REC.ORGANIZATION_ID
1225 AND RELATED_ITEM_ID = L_REL_ITEM_REC.RELATED_ITEM_ID
1226 AND RELATIONSHIP_TYPE_ID = L_REL_ITEM_REC.RELATIONSHIP_TYPE_ID;
1227 END;
1228
1229 Write_Debug('Deleting Related Item record...');
1230 -- calling delete
1231 Mtl_Related_Items_Pkg.Delete_Row(
1232 X_ROWID => RETURNED_ROW_ID);
1233
1234 -- raising post business event
1235 BEGIN
1236 INV_ITEM_EVENTS_PVT.Raise_Events(
1237 p_event_name => 'EGO_WF_WRAPPER_PVT.G_REL_ITEM_CHANGE_EVENT'
1238 ,p_dml_type => 'DELETE'
1239 ,p_inventory_item_id => L_REL_ITEM_REC.Inventory_Item_Id
1240 ,p_organization_id => L_REL_ITEM_REC.Organization_Id
1241 ,p_related_item_id => L_REL_ITEM_REC.Related_Item_Id
1242 ,p_relationship_type_id => L_REL_ITEM_REC.Relationship_Type_Id
1243 );
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 NULL;
1247 END;
1248
1249 END;
1250 BEGIN
1251 Write_Debug('Checking for associated substitution sets...');
1252 -- deleting planning information associated with item relationship
1253 FOR l_mtl_Rel_Item_Pln_Info_rec IN mtl_rel_item_pln_info_cur(l_Rel_Item_Rec.INVENTORY_ITEM_ID
1254 ,l_Rel_Item_Rec.RELATED_ITEM_ID
1255 ,l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID
1256 ,l_Rel_Item_Rec.ORGANIZATION_ID) LOOP
1257
1258 -- locking the row
1259 MTL_RELATED_ITEMS_PLN_INFO_PKG.LOCK_ROW(
1260 P_PLN_INFO_ID => l_mtl_Rel_Item_Pln_Info_rec.Pln_Info_Id
1261 ,P_INVENTORY_ITEM_ID => l_mtl_Rel_Item_Pln_Info_rec.Inventory_Item_Id
1262 ,P_ORGANIZATION_ID => l_mtl_Rel_Item_Pln_Info_rec.Organization_Id
1263 ,P_Related_Item_Id => l_mtl_Rel_Item_Pln_Info_rec.Related_Item_Id
1264 ,P_RELATIONSHIP_TYPE_ID => l_mtl_Rel_Item_Pln_Info_rec.Relationship_Type_Id
1265 ,P_SUBSTITUTION_SET => l_mtl_Rel_Item_Pln_Info_rec.Substitution_Set
1266 ,P_PARTIAL_FULFILLMENT_FLAG => l_mtl_Rel_Item_Pln_Info_rec.Partial_Fulfillment_Flag
1267 ,P_START_DATE => l_mtl_Rel_Item_Pln_Info_rec.Start_Date
1268 ,P_END_DATE => l_mtl_Rel_Item_Pln_Info_rec.End_Date
1269 ,P_ALL_CUSTOMERS_FLAG => l_mtl_Rel_Item_Pln_Info_rec.All_Customers_Flag);
1270
1271 Write_Debug('Deleting associated substitution set...');
1272
1273 MTL_RELATED_ITEMS_PLN_INFO_PKG.DELETE_ROW(
1274 P_PLN_INFO_ID => l_mtl_Rel_Item_Pln_Info_rec.Pln_Info_Id
1275 ,P_INVENTORY_ITEM_ID => l_mtl_Rel_Item_Pln_Info_rec.Inventory_Item_Id
1276 ,P_ORGANIZATION_ID => l_mtl_Rel_Item_Pln_Info_rec.Organization_Id
1277 ,P_RELATED_ITEM_ID => l_mtl_Rel_Item_Pln_Info_rec.Related_Item_Id
1278 ,P_RELATIONSHIP_TYPE_ID => l_mtl_Rel_Item_Pln_Info_rec.Relationship_Type_Id);
1279 End Loop;
1280 END;
1281
1282 -- deleting customer references associated with item relationship
1283 BEGIN
1284 Write_Debug('Checking for associated customer references...');
1285
1286 FOR l_mtl_Rel_Item_Cust_Ref_rec IN mtl_rel_item_cust_ref_cur(l_Cust_Ref_Rec.INVENTORY_ITEM_ID
1287 ,l_Cust_Ref_Rec.RELATED_ITEM_ID
1288 ,l_Cust_Ref_Rec.RELATIONSHIP_TYPE_ID
1289 ,l_Cust_Ref_Rec.ORGANIZATION_ID) LOOP
1290
1291 -- locking the row
1292 MTL_RELATED_ITEMS_CUST_REF_PKG.LOCK_ROW(
1293 P_PLN_INFO_ID => l_mtl_Rel_Item_Cust_Ref_rec.Pln_Info_Id
1294 ,P_INVENTORY_ITEM_ID => l_mtl_Rel_Item_Cust_Ref_rec.Inventory_Item_Id
1295 ,P_ORGANIZATION_ID => l_mtl_Rel_Item_Cust_Ref_rec.Organization_Id
1296 ,P_RELATED_ITEM_ID => l_mtl_Rel_Item_Cust_Ref_rec.Related_Item_Id
1297 ,P_RELATIONSHIP_TYPE_ID => l_mtl_Rel_Item_Cust_Ref_rec.Relationship_Type_Id
1298 ,P_CUSTOMER_ID => l_mtl_Rel_Item_Cust_Ref_rec.Customer_Id
1299 ,P_SITE_USE_ID => l_mtl_Rel_Item_Cust_Ref_rec.Site_Use_Id
1300 ,P_START_DATE => l_mtl_Rel_Item_Cust_Ref_rec.Start_Date
1301 ,P_END_DATE => l_mtl_Rel_Item_Cust_Ref_rec.End_Date);
1302
1303
1304 Write_Debug('Deleting associated customer references...');
1305
1306 MTL_RELATED_ITEMS_CUST_REF_PKG.DELETE_ROW(
1307 P_PLN_INFO_ID => l_mtl_Rel_Item_Cust_Ref_rec.Pln_Info_Id
1308 ,P_INVENTORY_ITEM_ID => l_mtl_Rel_Item_Cust_Ref_rec.Inventory_Item_Id
1309 ,P_ORGANIZATION_ID => l_mtl_Rel_Item_Cust_Ref_rec.Organization_Id
1310 ,P_RELATED_ITEM_ID => l_mtl_Rel_Item_Cust_Ref_rec.Related_Item_Id
1311 ,P_RELATIONSHIP_TYPE_ID => l_mtl_Rel_Item_Cust_Ref_rec.Relationship_Type_Id
1312 ,P_CUSTOMER_ID => l_mtl_Rel_Item_Cust_Ref_rec.Customer_Id
1313 ,P_SITE_USE_ID => l_mtl_Rel_Item_Cust_Ref_rec.Site_Use_Id);
1314
1315 END LOOP;
1316
1317 END;
1318
1319 END IF; -- all transaction types
1320
1321 Write_Debug('End related item record processing.');
1322 -----
1323 --Planning Information
1324 -----
1325
1326 If l_Rel_Item_Rec.Transaction_Type = 'CREATE' OR
1327 l_Rel_Item_Rec.Transaction_Type = 'UPDATE' THEN
1328
1329 -- Planning info and customer reference create/update/delete
1330 IF l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID = 2 THEN
1331
1332 IF p_Pln_Info_Tbl.FIRST IS NOT NULL THEN
1333
1334 Write_Debug('Checking for associated substitution set...');
1335
1336 FOR l_Pln_Info_Indx IN p_Pln_Info_Tbl.FIRST..p_Pln_Info_Tbl.Last LOOP
1337
1338 l_Pln_Info_Rec := l_Pln_Info_Tbl(l_Pln_Info_Indx);
1339
1340 -- 4/5 Primary Key columns required to match planning info with
1341 -- corresponding related item
1342
1343 IF l_Pln_Info_Rec.INVENTORY_ITEM_ID IS NOT NULL AND
1344 l_Pln_Info_Rec.INVENTORY_ITEM_ID <> FND_API.G_MISS_NUM
1345 AND l_Pln_Info_Rec.RELATED_ITEM_ID IS NOT NULL AND
1346 l_Pln_Info_Rec.RELATED_ITEM_ID <> FND_API.G_MISS_NUM
1347 AND l_Pln_Info_Rec.RELATIONSHIP_TYPE_ID IS NOT NULL AND
1348 l_Pln_Info_Rec.Relationship_Type_Id <> FND_API.G_MISS_NUM
1349 AND l_Pln_Info_Rec.ORGANIZATION_ID IS NOT NULL AND
1350 l_Pln_Info_Rec.ORGANIZATION_ID <> FND_API.G_MISS_NUM THEN
1351
1352 IF l_Rel_Item_Rec.INVENTORY_ITEM_ID = l_Pln_Info_Rec.INVENTORY_ITEM_ID
1353 AND l_Rel_Item_Rec.RELATED_ITEM_ID = l_Pln_Info_Rec.RELATED_ITEM_ID
1354 AND l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID = l_Pln_Info_Rec.RELATIONSHIP_TYPE_ID
1355 AND l_Rel_Item_Rec.ORGANIZATION_ID = l_Pln_Info_Rec.ORGANIZATION_ID THEN
1356
1357
1358 Write_Debug('Processing associated substitution set...');
1359
1360 IF l_Pln_Info_Rec.Transaction_Type <> 'CREATE' AND
1361 l_Pln_Info_Rec.Transaction_Type <> 'UPDATE' AND
1362 l_Pln_Info_Rec.Transaction_Type <> 'DELETE' THEN
1363
1364 x_return_status := FND_API.G_RET_STS_ERROR;
1365 Error_Handler.Add_Error_message
1366 (
1367 p_message_name => 'EGO_PLN_INFO_INVALID_TRANS_TYP'
1368 ,p_application_id => 'EGO'
1369 ,p_message_type => 'E'
1370 ,p_entity_code => G_Entity_Code
1371 ,p_entity_index => 1
1372 ,P_Table_Name => 'MTL_RELATED_ITEMS_PLN_INFO'
1373 );
1374 RAISE VALIDATION_ERROR;
1375 END IF;
1376
1377 IF l_Pln_Info_Rec.TRANSACTION_TYPE = 'CREATE' OR
1378 l_Pln_Info_Rec.TRANSACTION_TYPE = 'UPDATE' THEN
1379
1380 IF l_Pln_Info_Rec.TRANSACTION_TYPE = 'CREATE' THEN
1381
1382 BEGIN
1383 SELECT Decode(l_Pln_Info_Rec.Inventory_Item_Id,FND_API.G_MISS_NUM,NULL,l_Pln_Info_Rec.Inventory_Item_Id),
1384 Decode(l_Pln_Info_Rec.Organization_Id,FND_API.G_MISS_NUM, NULL, l_Pln_Info_Rec.Organization_Id),
1385 Decode(l_Pln_Info_Rec.Related_Item_Id,FND_API.G_MISS_NUM, NULL, l_Pln_Info_Rec.Related_Item_Id),
1386 Decode(l_Pln_Info_Rec.Relationship_Type_Id,FND_API.G_MISS_NUM, NULL, l_Pln_Info_Rec.Relationship_Type_Id),
1387 Decode(l_Pln_Info_Rec.Substitution_Set,FND_API.G_MISS_CHAR, NULL, l_Pln_Info_Rec.Substitution_Set),
1388 Decode(l_Pln_Info_Rec.Partial_Fulfillment_Flag,FND_API.G_MISS_CHAR, NULL, l_Pln_Info_Rec.Partial_Fulfillment_Flag),
1389 Decode(L_Pln_Info_Rec.Start_Date,Fnd_Api.G_Miss_Date, Null, L_Pln_Info_Rec.Start_Date),
1390 Decode(l_Pln_Info_Rec.End_Date,FND_API.G_MISS_DATE, NULL, l_Pln_Info_Rec.End_Date),
1391 Decode(l_Pln_Info_Rec.All_Customers_Flag,FND_API.G_MISS_CHAR, NULL, l_Pln_Info_Rec.All_Customers_Flag)
1392
1393 INTO l_Pln_Info_Rec.Inventory_Item_Id,l_Pln_Info_Rec.Organization_Id,l_Pln_Info_Rec.Related_Item_Id,
1394 l_Pln_Info_Rec.Relationship_Type_Id,l_Pln_Info_Rec.Substitution_Set,l_Pln_Info_Rec.Partial_Fulfillment_Flag,
1395 l_Pln_Info_Rec.Start_Date,l_Pln_Info_Rec.End_Date,l_Pln_Info_Rec.All_Customers_Flag
1396
1397 FROM dual;
1398 End;
1399 END IF;
1400
1401 IF l_Pln_Info_Rec.TRANSACTION_TYPE = 'UPDATE' THEN
1402
1403 -- must have id if updating
1404 IF l_Pln_Info_Rec.PLN_INFO_ID = FND_API.G_MISS_NUM THEN
1405
1406 x_return_status:=FND_API.g_RET_STS_ERROR;
1407 Error_Handler.Add_Error_message
1408 (
1409 p_message_name => 'EGO_PLN_INFO_ID_NULL'
1410 ,p_application_id => 'EGO'
1411 ,p_message_type => 'E'
1412 ,p_entity_code => G_Entity_Code
1413 ,p_entity_index => l_Pln_Info_Indx
1414 ,p_table_name => 'MTL_RELATED_ITEMS_PLN_INFO'
1415 );
1416 RAISE VALIDATION_ERROR;
1417 END IF;
1418
1419 -- check if info exists
1420 OPEN mtl_pln_info_cur(l_Pln_Info_Rec.INVENTORY_ITEM_ID
1421 ,l_Pln_Info_Rec.RELATED_ITEM_ID
1422 ,l_Pln_Info_Rec.RELATIONSHIP_TYPE_ID
1423 ,l_Pln_Info_Rec.ORGANIZATION_ID
1424 ,l_Pln_Info_Rec.PLN_INFO_ID);
1425 FETCH mtl_pln_info_cur INTO l_mtl_Rel_Item_Pln_Info_rec;
1426 IF mtl_pln_info_cur%NOTFOUND THEN
1427 x_return_status:=FND_API.G_RET_STS_ERROR;
1428 Error_Handler.Add_Error_message
1429 (
1430 p_message_name => 'EGO_PLN_INFO_NOTEXISTS'
1431 ,p_application_id => 'EGO'
1432 ,p_message_type => 'E'
1433 ,p_entity_code => G_Entity_Code
1434 ,p_entity_index => l_Pln_Info_Indx
1435 ,p_table_name => 'MTL_RELATED_ITEMS_PLN_INFO'
1436 );
1437 CLOSE mtl_pln_info_cur;
1438 RAISE VALIDATION_ERROR;
1439 END IF;
1440 CLOSE mtl_pln_info_cur;
1441
1442 BEGIN
1443 SELECT Decode(l_Pln_Info_Rec.Inventory_Item_Id,FND_API.G_MISS_NUM,l_mtl_Rel_Item_Pln_Info_rec.Inventory_Item_Id,l_Pln_Info_Rec.Inventory_Item_Id),
1444 Decode(l_Pln_Info_Rec.Organization_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Pln_Info_rec.Organization_Id, l_Pln_Info_Rec.Organization_Id),
1445 Decode(l_Pln_Info_Rec.Related_Item_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Pln_Info_rec.Related_Item_Id, l_Pln_Info_Rec.Related_Item_Id),
1446 Decode(l_Pln_Info_Rec.Relationship_Type_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Pln_Info_rec.Relationship_Type_Id, l_Pln_Info_Rec.Relationship_Type_Id),
1447 Decode(L_Pln_Info_Rec.Substitution_Set,Fnd_Api.G_Miss_Char, L_Mtl_Rel_Item_Pln_Info_Rec.Substitution_Set, L_Pln_Info_Rec.Substitution_Set),
1448 Decode(l_Pln_Info_Rec.Partial_Fulfillment_Flag,FND_API.G_MISS_CHAR, l_mtl_Rel_Item_Pln_Info_rec.Partial_Fulfillment_Flag, l_Pln_Info_Rec.Partial_Fulfillment_Flag),
1449 Decode(l_Pln_Info_Rec.Start_Date,FND_API.G_MISS_DATE, l_mtl_Rel_Item_Pln_Info_rec.Start_Date, l_Pln_Info_Rec.Start_Date),
1450 Decode(l_Pln_Info_Rec.End_Date,FND_API.G_MISS_DATE, l_mtl_Rel_Item_Pln_Info_rec.End_Date, l_Pln_Info_Rec.End_Date),
1451 Decode(l_Pln_Info_Rec.All_Customers_Flag,FND_API.G_MISS_CHAR, l_mtl_Rel_Item_Pln_Info_rec.All_Customers_Flag, l_Pln_Info_Rec.All_Customers_Flag)
1452 INTO l_Pln_Info_Rec.Inventory_Item_Id,l_Pln_Info_Rec.Organization_Id,l_Pln_Info_Rec.Related_Item_Id,l_Pln_Info_Rec.Relationship_Type_Id,
1453 l_Pln_Info_Rec.Substitution_Set,l_Pln_Info_Rec.Partial_Fulfillment_Flag,l_Pln_Info_Rec.Start_Date,l_Pln_Info_Rec.End_Date,
1454 l_Pln_Info_Rec.All_Customers_Flag
1455 FROM dual;
1456 END;
1457 END IF;
1458
1459 -- planning info/ substitution set date validation --
1460
1461
1462
1463 IF l_Pln_Info_Rec.Start_Date IS NOT NULL AND
1464 l_Pln_Info_Rec.END_DATE IS NOT NULL AND
1465 l_Pln_Info_Rec.START_DATE <> FND_API.G_MISS_DATE AND
1466 l_Pln_Info_Rec.END_DATE <> FND_API.G_MISS_DATE THEN
1467 IF l_Pln_Info_Rec.END_DATE < l_Pln_Info_Rec.START_DATE THEN
1468 x_return_status := FND_API.G_RET_STS_ERROR;
1469 Error_Handler.Add_Error_message
1470 (
1471 p_message_name => 'EGO_STARTDATE_PRECEDES_ENDDATE'
1472 ,p_application_id => 'EGO'
1473 ,p_message_type => 'E'
1474 ,p_entity_code => G_Entity_Code
1475 ,p_entity_index => l_Pln_Info_Indx
1476 ,p_table_name => 'MTL_RELATED_ITEMS_PLN_INFO'
1477 );
1478 RAISE VALIDATION_ERROR;
1479 END IF;
1480 END IF;
1481
1482 IF l_Pln_Info_Rec.START_DATE IS NOT NULL
1483 AND l_Pln_Info_Rec.START_DATE <> FND_API.G_MISS_DATE THEN
1484 IF l_Pln_Info_Rec.START_DATE < SYSDATE THEN
1485 x_return_status := FND_API.G_RET_STS_ERROR;
1486 Error_Handler.Add_Error_message
1487 (
1488 p_message_name => 'EGO_SELECTED_DATE_INVALID'
1489 ,p_application_id => 'EGO'
1490 ,p_message_type => 'E'
1491 ,p_entity_code => G_Entity_Code
1492 ,p_entity_index => l_Pln_Info_Indx
1493 ,p_table_name => 'MTL_RELATED_ITEMS_PLN_INFO'
1494 );
1495 RAISE VALIDATION_ERROR;
1496 END IF;
1497 END IF;
1498
1499 IF l_Pln_Info_Rec.END_DATE IS NOT NULL
1500 AND l_Pln_Info_Rec.END_DATE <> FND_API.G_MISS_DATE THEN
1501
1502 IF l_Pln_Info_Rec.END_DATE < SYSDATE THEN
1503 x_return_status := FND_API.G_RET_STS_ERROR;
1504 Error_Handler.Add_Error_message
1505 (
1506 p_message_name => 'EGO_SELECTED_DATE_INVALID'
1507 ,p_application_id => 'EGO'
1508 ,p_message_type => 'E'
1509 ,p_entity_code => G_Entity_Code
1510 ,p_entity_index => l_Pln_Info_Indx
1511 ,p_table_name => 'MTL_RELATED_ITEMS_PLN_INFO'
1512 );
1513 RAISE VALIDATION_ERROR;
1514 END IF;
1515 END IF;
1516 -- end planning info/substitution date validations --
1517
1518 -- default Partial_Fulfillment_Flag to 'N'
1519 IF l_Pln_Info_Rec.Partial_Fulfillment_Flag IS NULL OR
1520 l_Pln_Info_Rec.Partial_Fulfillment_Flag = FND_API.G_MISS_CHAR THEN
1521 l_Pln_Info_Rec.Partial_Fulfillment_Flag := 'N';
1522 END IF;
1523
1524
1525 -- Partial fulfillment flag cannot be NULL
1526 IF l_Pln_Info_Rec.Partial_Fulfillment_Flag NOT IN ('Y','N') THEN
1527 x_return_status := FND_API.G_RET_STS_ERROR;
1528 Error_Handler.Add_Error_message
1529 (
1530 p_message_name => 'EGO_PART_FUL_FLAG_INVALID'
1531 ,p_application_id => 'EGO'
1532 ,p_message_type => 'E'
1533 ,p_entity_code => G_Entity_Code
1534 ,p_entity_index => l_Pln_Info_Indx
1535 ,p_table_name => 'MTL_RELATED_ITEMS_PLN_INFO'
1536 );
1537 RAISE VALIDATION_ERROR;
1538 END IF;
1539
1540 -- default All_Customers_Flag to 'Y'
1541 IF l_Pln_Info_Rec.All_Customers_Flag IS NULL OR
1542 l_Pln_Info_Rec.All_Customers_Flag = FND_API.G_MISS_CHAR THEN
1543 l_Pln_Info_Rec.All_Customers_Flag := 'Y';
1544 END IF;
1545
1546 --??? All customers flag cannot be NULL '
1547 IF l_Pln_Info_Rec.All_Customers_Flag NOT IN ('Y','N',FND_API.G_MISS_CHAR) THEN
1548 x_return_status := FND_API.G_RET_STS_ERROR;
1549 Error_Handler.Add_Error_message
1550 (
1551 p_message_name => 'EGO_ALL_CUSTOMERS_FLAG_INVALID'
1552 ,p_application_id => 'EGO'
1553 ,p_message_type => 'E'
1554 ,p_entity_code => G_Entity_Code
1555 ,p_entity_index => l_Pln_Info_Indx
1556 ,p_table_name => 'MTL_RELATED_ITEMS_PLN_INFO'
1557 );
1558 RAISE VALIDATION_ERROR;
1559 END IF;
1560
1561 IF l_Pln_Info_Rec.TRANSACTION_TYPE = 'CREATE' THEN
1562
1563 Write_Debug('Creating associated substitution set...');
1564
1565 MTL_RELATED_ITEMS_PLN_INFO_PKG.INSERT_ROW(
1566 P_INVENTORY_ITEM_ID => l_Pln_Info_Rec.Inventory_Item_Id
1567 ,P_ORGANIZATION_ID => l_Pln_Info_Rec.Organization_Id
1568 ,P_RELATED_ITEM_ID => l_Pln_Info_Rec.Related_Item_Id
1569 ,P_RELATIONSHIP_TYPE_ID => l_Pln_Info_Rec.Relationship_Type_Id
1570 ,P_SUBSTITUTION_SET => l_Pln_Info_Rec.Substitution_Set
1571 ,P_PARTIAL_FULFILLMENT_FLAG => l_Pln_Info_Rec.Partial_Fulfillment_Flag
1572 ,P_START_DATE => l_Pln_Info_Rec.Start_Date
1573 ,P_END_DATE => l_Pln_Info_Rec.End_Date
1574 ,P_ALL_CUSTOMERS_FLAG => l_Pln_Info_Rec.All_Customers_Flag
1575 --,P_CREATION_DATE => Nvl(l_Rel_Item_Rec.Creation_Date, SYSDATE)
1576 --,P_CREATED_BY => Nvl(l_Rel_Item_Rec.Created_By, FND_GLOBAL.USER_ID)
1577 --,P_LAST_UPDATE_DATE => Nvl(l_Rel_Item_Rec.Last_Update_Date, SYSDATE)
1578 --,P_LAST_UPDATED_BY => Nvl(l_Rel_Item_Rec.Last_Updated_By, FND_GLOBAL.USER_ID)
1579 --,P_LAST_UPDATE_LOGIN => Nvl(l_Rel_Item_Rec.Last_Update_Login, FND_GLOBAL.LOGIN_ID)
1580 ,P_CREATION_DATE => SYSDATE
1581 ,P_CREATED_BY => FND_GLOBAL.USER_ID
1582 ,P_LAST_UPDATE_DATE => SYSDATE
1583 ,P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
1584 ,P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
1585 ,X_PLN_INFO_ID => returned_pln_info_id
1586 );
1587 END IF;
1588
1589
1590 IF l_Pln_Info_Rec.TRANSACTION_TYPE = 'UPDATE' THEN
1591
1592 -- Note: pln info does not need to check for duplicates since
1593 -- all columns except for pln_info_id can be the same as other rows
1594
1595 BEGIN
1596 -- locking the row
1597 OPEN mtl_rel_item_pln_info_lock_b(l_Pln_Info_Rec.INVENTORY_ITEM_ID
1598 ,l_Pln_Info_Rec.RELATED_ITEM_ID
1599 ,l_Pln_Info_Rec.RELATIONSHIP_TYPE_ID
1600 ,l_Pln_Info_Rec.ORGANIZATION_ID
1601 ,l_Pln_Info_Rec.PLN_INFO_ID);
1602 FETCH mtl_rel_item_pln_info_lock_b INTO l_lock_b_pln_info_recinfo;
1603 CLOSE mtl_rel_item_pln_info_lock_b;
1604
1605 Write_Debug('Updating associated substitution set...');
1606
1607 MTL_RELATED_ITEMS_PLN_INFO_PKG.UPDATE_ROW(
1608 P_PLN_INFO_ID => l_Pln_Info_Rec.Pln_Info_Id
1609 ,P_INVENTORY_ITEM_ID => l_Pln_Info_Rec.Inventory_Item_Id
1610 ,P_ORGANIZATION_ID => l_Pln_Info_Rec.Organization_Id
1611 ,P_RELATED_ITEM_ID => l_Pln_Info_Rec.Related_Item_Id
1612 ,P_RELATIONSHIP_TYPE_ID => l_Pln_Info_Rec.Relationship_Type_Id
1613 ,P_SUBSTITUTION_SET => l_Pln_Info_Rec.Substitution_Set
1614 ,P_PARTIAL_FULFILLMENT_FLAG => l_Pln_Info_Rec.Partial_Fulfillment_Flag
1615 ,P_START_DATE => l_Pln_Info_Rec.Start_Date
1616 ,P_END_DATE => l_Pln_Info_Rec.End_Date
1617 ,P_ALL_CUSTOMERS_FLAG => l_Pln_Info_Rec.All_Customers_Flag
1618 -- ,P_LAST_UPDATE_DATE => Nvl(l_Rel_Item_Rec.Last_Update_Date, SYSDATE)
1619 -- ,P_LAST_UPDATED_BY => Nvl(l_Rel_Item_Rec.Last_Updated_By, FND_GLOBAL.USER_ID)
1620 -- ,P_LAST_UPDATE_LOGIN => Nvl(l_Rel_Item_Rec.Last_Update_Login, FND_GLOBAL.LOGIN_ID)
1621 ,P_LAST_UPDATE_DATE =>SYSDATE
1622 ,P_LAST_UPDATED_BY =>FND_GLOBAL.USER_ID
1623 ,P_LAST_UPDATE_LOGIN =>FND_GLOBAL.LOGIN_ID
1624 );
1625 END;
1626 END IF;
1627
1628
1629
1630 -- checking for customer reference records
1631 IF p_Cust_Ref_Tbl.FIRST IS NOT NULL THEN
1632
1633 Write_Debug('Checking for associated customer references...');
1634
1635 FOR l_Cust_Ref_Indx IN p_Cust_Ref_Tbl.FIRST..p_Cust_Ref_Tbl.Last LOOP
1636 l_Cust_Ref_Rec := l_Cust_Ref_Tbl(l_Cust_Ref_Indx);
1637
1638 -- if either update or create of pln info, then cust ref can be created
1639
1640 IF l_Cust_Ref_Rec.INVENTORY_ITEM_ID IS NOT NULL AND -- checking for primary key columns
1641 l_Cust_Ref_Rec.INVENTORY_ITEM_ID <> FND_API.G_MISS_NUM
1642 AND l_Cust_Ref_Rec.RELATED_ITEM_ID IS NOT NULL AND
1643 l_Cust_Ref_Rec.RELATED_ITEM_ID <> FND_API.G_MISS_NUM
1644 AND l_Cust_Ref_Rec.RELATIONSHIP_TYPE_ID IS NOT NULL AND
1645 l_Cust_Ref_Rec.RELATIONSHIP_TYPE_ID <> FND_API.G_MISS_NUM
1646 AND l_Cust_Ref_Rec.ORGANIZATION_ID IS NOT NULL AND
1647 l_Cust_Ref_Rec.ORGANIZATION_ID <> FND_API.G_MISS_NUM
1648 AND l_Cust_Ref_Rec.CUSTOMER_ID IS NOT NULL AND
1649 l_Cust_Ref_Rec.CUSTOMER_ID <> FND_API.G_MISS_NUM
1650 AND l_Cust_Ref_Rec.SITE_USE_ID IS NOT NULL AND
1651 l_Cust_Ref_Rec.SITE_USE_ID <> FND_API.G_MISS_NUM THEN
1652
1653 -- cust ref for plan info rec
1654 IF l_Cust_Ref_Rec.INVENTORY_ITEM_ID = l_Pln_Info_Rec.INVENTORY_ITEM_ID
1655 AND l_Cust_Ref_Rec.RELATED_ITEM_ID = l_Pln_Info_Rec.RELATED_ITEM_ID
1656 AND l_Cust_Ref_Rec.RELATIONSHIP_TYPE_ID = l_Pln_Info_Rec.RELATIONSHIP_TYPE_ID
1657 /* Commented as part of bug#12668577
1658 -- AND l_Cust_Ref_Rec.PLN_INFO_ID = l_Pln_Info_Rec.PLN_INFO_ID
1659 */
1660 AND l_Cust_Ref_Rec.ORGANIZATION_ID = l_Pln_Info_Rec.ORGANIZATION_ID THEN
1661
1662
1663 Write_Debug('Processing associated customer reference...');
1664
1665 IF l_Cust_Ref_Rec.Transaction_Type <> 'CREATE' AND
1666 l_Cust_Ref_Rec.Transaction_Type <> 'UPDATE' AND
1667 l_Cust_Ref_Rec.Transaction_Type <> 'DELETE' THEN
1668
1669 x_return_status := FND_API.G_RET_STS_ERROR;
1670 Error_Handler.Add_Error_message
1671 (
1672 p_message_name => 'EGO_CUST_REF_INVALID_TRANS_TYP'
1673 ,p_application_id => 'EGO'
1674 ,p_message_type => 'E'
1675 ,p_entity_code => G_Entity_Code
1676 ,p_entity_index => 1
1677 ,P_Table_Name => 'MTL_RELATED_ITEMS_PLN_INFO'
1678 );
1679 RAISE VALIDATION_ERROR;
1680 END IF;
1681
1682
1683 IF l_Cust_Ref_Rec.TRANSACTION_TYPE = 'CREATE' OR
1684 l_Cust_Ref_Rec.TRANSACTION_TYPE = 'UPDATE' THEN
1685
1686 IF l_Cust_Ref_Rec.TRANSACTION_TYPE = 'CREATE' THEN
1687 BEGIN
1688 /* Addition as part of bug#12668577 */
1689 l_Cust_Ref_Rec.Pln_Info_Id:=returned_pln_info_id;
1690
1691 SELECT
1692 Decode(l_Cust_Ref_Rec.Pln_Info_Id,FND_API.G_MISS_NUM,NULL,l_Cust_Ref_Rec.Pln_Info_Id),
1693 Decode(l_Cust_Ref_Rec.Inventory_Item_Id,FND_API.G_MISS_NUM,NULL,l_Cust_Ref_Rec.Inventory_Item_Id),
1694 Decode(l_Cust_Ref_Rec.Organization_Id,FND_API.G_MISS_NUM, NULL, l_Cust_Ref_Rec.Organization_Id),
1695 Decode(l_Cust_Ref_Rec.Related_Item_Id,FND_API.G_MISS_NUM, NULL, l_Cust_Ref_Rec.Related_Item_Id),
1696 Decode(l_Cust_Ref_Rec.Relationship_Type_Id,FND_API.G_MISS_NUM, NULL, l_Cust_Ref_Rec.Relationship_Type_Id),
1697 Decode(l_Cust_Ref_Rec.Customer_Id,FND_API.G_MISS_NUM, NULL, l_Cust_Ref_Rec.Customer_Id),
1698 Decode(l_Cust_Ref_Rec.Site_Use_Id,FND_API.G_MISS_NUM, NULL, l_Cust_Ref_Rec.Site_Use_Id),
1699 Decode(l_Cust_Ref_Rec.Start_Date,FND_API.G_MISS_DATE, NULL, l_Cust_Ref_Rec.Start_Date),
1700 Decode(l_Cust_Ref_Rec.End_Date,FND_API.G_MISS_DATE, NULL, l_Cust_Ref_Rec.End_Date)
1701
1702 INTO l_Cust_Ref_Rec.Pln_Info_Id,l_Cust_Ref_Rec.Inventory_Item_Id,l_Cust_Ref_Rec.Organization_Id,l_Cust_Ref_Rec.Related_Item_Id,
1703 l_Cust_Ref_Rec.Relationship_Type_Id,l_Cust_Ref_Rec.Customer_Id,l_Cust_Ref_Rec.Site_Use_Id,
1704 l_Cust_Ref_Rec.Start_Date,l_Cust_Ref_Rec.End_Date
1705
1706 FROM dual;
1707
1708 -- checking if customer and site combinations already exists
1709 SELECT 'x' INTO l_cust_ref_exists
1710 FROM mtl_related_items_cust_ref
1711 WHERE Inventory_Item_Id = l_Cust_Ref_Rec.INVENTORY_ITEM_ID
1712 AND Related_Item_Id = l_Cust_Ref_Rec.RELATED_ITEM_ID
1713 AND Relationship_Type_Id = l_Cust_Ref_Rec.RELATIONSHIP_TYPE_ID
1714 AND Pln_Info_Id = l_Cust_Ref_Rec.PLN_INFO_ID
1715 AND Organization_Id = l_Cust_Ref_Rec.ORGANIZATION_ID
1716 AND Customer_Id = l_Cust_Ref_Rec.CUSTOMER_ID
1717 AND Site_Use_Id = l_Cust_Ref_Rec.SITE_USE_ID;
1718 IF l_cust_ref_exists='x' THEN
1719 l_Token_Tbl(1).Token_Name := 'INVENTORY_ITEM_ID';
1720 l_Token_Tbl(1).Token_Value := l_Cust_Ref_Rec.INVENTORY_ITEM_ID;
1721 l_Token_Tbl(1).Translate := FALSE;
1722 l_Token_Tbl(2).Token_Name := 'RELATIONSHIP_TYPE_ID';
1723 l_Token_Tbl(2).Token_Value := l_Cust_Ref_Rec.RELATIONSHIP_TYPE_ID;
1724 l_Token_Tbl(2).Translate := FALSE;
1725 l_Token_Tbl(3).Token_Name := 'RELATED_ITEM_ID';
1726 l_Token_Tbl(3).Token_Value := l_Cust_Ref_Rec.RELATED_ITEM_ID;
1727 l_Token_Tbl(3).Translate := FALSE;
1728 l_Token_Tbl(4).Token_Name := 'PLN_INFO_ID';
1729 l_Token_Tbl(4).Token_Value := l_Cust_Ref_Rec.PLN_INFO_ID;
1730 l_Token_Tbl(4).Translate := FALSE;
1731 l_Token_Tbl(5).Token_Name := 'ORGANIZATION_ID';
1732 l_Token_Tbl(5).Token_Value := l_Cust_Ref_Rec.ORGANIZATION_ID;
1733 l_Token_Tbl(5).Translate := FALSE;
1734 l_Token_Tbl(6).Token_Name := 'CUSTOMER_ID';
1735 l_Token_Tbl(6).Token_Value := l_Cust_Ref_Rec.CUSTOMER_ID;
1736 l_Token_Tbl(6).Translate := FALSE;
1737 l_Token_Tbl(7).Token_Name := 'SITE_USE_ID';
1738 l_Token_Tbl(7).Token_Value := l_Cust_Ref_Rec.SITE_USE_ID;
1739 l_Token_Tbl(7).Translate := FALSE;
1740
1741
1742 x_return_status:=FND_API.g_RET_STS_ERROR;
1743 Error_Handler.Add_Error_message
1744 (
1745 p_message_name => 'EGO_RELATIONSHIP_EXISTS'
1746 ,p_application_id => 'EGO'
1747 ,p_token_tbl => l_Token_Tbl
1748 ,p_message_type => 'E'
1749 ,p_entity_code => G_Entity_Code
1750 ,p_entity_index => l_Cust_Ref_Indx
1751 ,p_table_name => 'MTL_RELATED_ITEMS_CUST_REF'
1752 );
1753 RAISE VALIDATION_ERROR;
1754 END IF;
1755 EXCEPTION
1756 WHEN No_Data_Found THEN
1757 NULL;
1758 END;
1759 END IF;
1760
1761 IF l_Cust_Ref_Rec.TRANSACTION_TYPE = 'UPDATE' THEN
1762 BEGIN
1763 OPEN mtl_cust_ref_cur(l_Cust_Ref_Rec.INVENTORY_ITEM_ID
1764 ,l_Cust_Ref_Rec.RELATED_ITEM_ID
1765 ,l_Cust_Ref_Rec.RELATIONSHIP_TYPE_ID
1766 ,l_Cust_Ref_Rec.ORGANIZATION_ID
1767 ,l_Cust_Ref_Rec.PLN_INFO_ID
1768 ,l_Cust_Ref_Rec.CUSTOMER_ID
1769 ,l_Cust_Ref_Rec.SITE_USE_ID);
1770 FETCH mtl_cust_ref_cur INTO l_mtl_Rel_Item_Cust_Ref_rec;
1771 IF mtl_cust_ref_cur%NOTFOUND THEN
1772 x_return_status:=FND_API.G_RET_STS_ERROR;
1773 Error_Handler.Add_Error_message
1774 (
1775 p_message_name => 'EGO_CUST_REF_NOTEXISTS'
1776 ,p_application_id => 'EGO'
1777 ,p_message_type => 'E'
1778 ,p_entity_code => G_Entity_Code
1779 ,p_entity_index => l_Cust_Ref_Indx
1780 ,p_table_name => 'MTL_RELATED_ITEMS_CUST_REF'
1781 );
1782 CLOSE mtl_cust_ref_cur;
1783 RAISE VALIDATION_ERROR;
1784 END IF;
1785 CLOSE mtl_cust_ref_cur;
1786
1787 SELECT Decode(l_Cust_Ref_Rec.Inventory_Item_Id,FND_API.G_MISS_NUM,l_mtl_Rel_Item_Cust_Ref_rec.Inventory_Item_Id,l_Cust_Ref_Rec.Inventory_Item_Id),
1788 Decode(l_Cust_Ref_Rec.Organization_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Cust_Ref_rec.Organization_Id, l_Cust_Ref_Rec.Organization_Id),
1789 Decode(l_Cust_Ref_Rec.Related_Item_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Cust_Ref_rec.Related_Item_Id, l_Cust_Ref_Rec.Related_Item_Id),
1790 Decode(l_Cust_Ref_Rec.Relationship_Type_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Cust_Ref_rec.Relationship_Type_Id, l_Cust_Ref_Rec.Relationship_Type_Id),
1791 Decode(l_Cust_Ref_Rec.Pln_Info_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Cust_Ref_rec.Pln_Info_Id, l_Cust_Ref_Rec.Pln_Info_Id),
1792 Decode(l_Cust_Ref_Rec.Customer_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Cust_Ref_rec.Customer_Id, l_Cust_Ref_Rec.Customer_Id),
1793 Decode(l_Cust_Ref_Rec.Site_Use_Id,FND_API.G_MISS_NUM, l_mtl_Rel_Item_Cust_Ref_rec.Site_Use_Id, l_Cust_Ref_Rec.Site_Use_Id),
1794 Decode(l_Cust_Ref_Rec.Start_Date,FND_API.G_MISS_DATE, l_mtl_Rel_Item_Cust_Ref_rec.Start_Date, l_Cust_Ref_Rec.Start_Date),
1795 Decode(l_Cust_Ref_Rec.End_Date,FND_API.G_MISS_DATE, l_mtl_Rel_Item_Cust_Ref_rec.End_Date, l_Cust_Ref_Rec.End_Date)
1796 Into l_Cust_Ref_Rec.Inventory_Item_Id,l_Cust_Ref_Rec.Organization_Id,l_Cust_Ref_Rec.Related_Item_Id,l_Cust_Ref_Rec.Relationship_Type_Id,
1797 l_Cust_Ref_Rec.Pln_Info_Id,l_Cust_Ref_Rec.Customer_Id,l_Cust_Ref_Rec.Site_Use_Id,l_Cust_Ref_Rec.Start_Date,l_Cust_Ref_Rec.End_Date
1798 FROM dual;
1799 END;
1800
1801 END IF;
1802
1803 -- common update and create validations
1804 BEGIN
1805 SELECT 'x' INTO l_cust_ref_rel_exists
1806 FROM
1807 (Select PARTY_NAME customer_name, cust_Account_id customer_id
1808 from hz_parties p , hz_cust_Accounts a
1809 where a.party_id = p.party_id),
1810 hz_cust_site_uses_all su,
1811 hz_party_sites party_site,
1812 hz_loc_assignments loc_assign,
1813 hz_locations loc,
1814 hz_cust_acct_sites_all acct_site
1815 WHERE customer_id = l_Cust_Ref_Rec.CUSTOMER_ID AND
1816 site_use_id = l_Cust_Ref_Rec.SITE_USE_ID AND
1817 acct_site.cust_account_id = customer_id AND
1818 acct_site.party_site_id = party_site.party_site_id AND
1819 loc.location_id = party_site.location_id AND
1820 loc.location_id = loc_assign.location_id AND
1821 NVL ( acct_site.org_id, -99 ) = NVL (loc_assign.org_id, -99) AND
1822 acct_site.cust_acct_site_id = su.cust_acct_site_id AND
1823 su.site_use_code = 'SHIP_TO';
1824
1825 IF l_cust_ref_rel_exists = 'x' THEN
1826 NULL;
1827 END IF;
1828 EXCEPTION
1829 WHEN No_Data_Found THEN
1830 x_return_status:=FND_API.G_RET_STS_ERROR;
1831 Error_Handler.Add_Error_message
1832 (
1833 p_message_name => 'EGO_CUST_SITE_COMB_NOTEXISTS'
1834 ,p_application_id => 'EGO'
1835 ,p_message_type => 'E'
1836 ,p_entity_code => G_Entity_Code
1837 ,p_entity_index => l_Cust_Ref_Indx
1838 ,p_table_name => 'MTL_RELATED_ITEMS_CUST_REF'
1839 );
1840 RAISE VALIDATION_ERROR;
1841 END;
1842
1843
1844
1845 -- customer ref date validation --
1846 IF (l_Cust_Ref_Rec.START_DATE IS NOT NULL OR l_Cust_Ref_Rec.START_DATE <> FND_API.G_MISS_DATE)
1847 AND (l_Cust_Ref_Rec.END_DATE IS NOT NULL OR l_Cust_Ref_Rec.END_DATE <> FND_API.G_MISS_DATE)
1848 THEN
1849
1850 IF l_Cust_Ref_Rec.END_DATE < l_Cust_Ref_Rec.START_DATE THEN
1851 x_return_status := FND_API.G_RET_STS_ERROR;
1852 Error_Handler.Add_Error_message
1853 (
1854 p_message_name => 'EGO_STARTDATE_PRECEDES_ENDDATE'
1855 ,P_Application_Id => 'EGO'
1856 ,p_token_tbl => l_Token_Tbl
1857 ,p_message_type => 'E'
1858 ,p_entity_code => G_Entity_Code
1859 ,p_entity_index => l_Cust_Ref_Indx
1860 ,p_table_name => 'MTL_RELATED_ITEMS_CUST_REF'
1861 );
1862 RAISE VALIDATION_ERROR;
1863 END IF;
1864 END IF;
1865
1866 IF l_Cust_Ref_Rec.START_DATE IS NOT NULL
1867 AND l_Cust_Ref_Rec.START_DATE <> FND_API.G_MISS_DATE THEN
1868 IF l_Cust_Ref_Rec.START_DATE < SYSDATE THEN
1869 x_return_status := FND_API.G_RET_STS_ERROR;
1870 Error_Handler.Add_Error_message
1871 (
1872 p_message_name => 'EGO_SELECTED_DATE_INVALID'
1873 ,P_Application_Id => 'EGO'
1874 ,p_token_tbl => l_Token_Tbl
1875 ,p_message_type => 'E'
1876 ,p_entity_code => G_Entity_Code
1877 ,p_entity_index => l_Cust_Ref_Indx
1878 ,p_table_name => 'MTL_RELATED_ITEMS_CUST_REF'
1879 );
1880 RAISE VALIDATION_ERROR;
1881 END IF;
1882 END IF;
1883
1884 IF l_Cust_Ref_Rec.END_DATE IS NOT NULL
1885 AND l_Cust_Ref_Rec.END_DATE <> FND_API.G_MISS_DATE THEN
1886 IF l_Cust_Ref_Rec.END_DATE < SYSDATE THEN
1887 x_return_status := FND_API.G_RET_STS_ERROR;
1888 Error_Handler.Add_Error_message
1889 (
1890 p_message_name => 'EGO_SELECTED_DATE_INVALID'
1891 ,p_application_id => 'EGO'
1892 ,p_message_type => 'E'
1893 ,p_entity_code => G_Entity_Code
1894 ,p_entity_index => l_Cust_Ref_Indx
1895 ,p_table_name => 'MTL_RELATED_ITEMS_CUST_REF'
1896 );
1897 RAISE VALIDATION_ERROR;
1898 END IF;
1899 END IF;
1900
1901 -- end date validations --
1902
1903 IF l_Cust_Ref_Rec.TRANSACTION_TYPE = 'CREATE' THEN
1904
1905 Write_Debug('Creating associated customer reference...');
1906
1907 BEGIN
1908 MTL_RELATED_ITEMS_CUST_REF_PKG.INSERT_ROW(
1909 P_PLN_INFO_ID => l_Cust_Ref_Rec.Pln_Info_Id
1910 ,P_INVENTORY_ITEM_ID => l_Cust_Ref_Rec.Inventory_Item_Id
1911 ,P_ORGANIZATION_ID => l_Cust_Ref_Rec.Organization_Id
1912 ,P_RELATED_ITEM_ID => l_Cust_Ref_Rec.Related_Item_Id
1913 ,P_RELATIONSHIP_TYPE_ID => l_Cust_Ref_Rec.Relationship_Type_Id
1914 ,P_CUSTOMER_ID => l_Cust_Ref_Rec.Customer_Id
1915 ,P_SITE_USE_ID => l_Cust_Ref_Rec.Site_Use_Id
1916 ,P_START_DATE => l_Cust_Ref_Rec.Start_Date
1917 ,P_END_DATE => l_Cust_Ref_Rec.End_Date
1918 --,P_CREATION_DATE => Nvl(l_Rel_Item_Rec.Creation_Date, SYSDATE)
1919 --,P_CREATED_BY => Nvl(l_Rel_Item_Rec.Created_By, FND_GLOBAL.USER_ID)
1920 --,P_Last_Update_Date => Nvl(L_Rel_Item_Rec.Last_Update_Date, Sysdate)
1921 --,P_LAST_UPDATED_BY => Nvl(l_Rel_Item_Rec.Last_Updated_By, FND_GLOBAL.USER_ID)
1922 --,P_LAST_UPDATE_LOGIN => Nvl(l_Rel_Item_Rec.Last_Update_Login, FND_GLOBAL.LOGIN_ID)
1923 ,P_CREATION_DATE => SYSDATE
1924 ,P_CREATED_BY => FND_GLOBAL.USER_ID
1925 ,P_Last_Update_Date => Sysdate
1926 ,P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
1927 ,P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
1928 );
1929 END;
1930
1931 End If;
1932
1933 IF l_Cust_Ref_Rec.TRANSACTION_TYPE = 'UPDATE' THEN
1934 BEGIN
1935 -- locking the cust ref row
1936 OPEN mtl_rel_item_cust_ref_lock_b(l_Cust_Ref_Rec.INVENTORY_ITEM_ID
1937 ,l_Cust_Ref_Rec.RELATED_ITEM_ID
1938 ,l_Cust_Ref_Rec.RELATIONSHIP_TYPE_ID
1939 ,l_Cust_Ref_Rec.ORGANIZATION_ID
1940 ,l_Cust_Ref_Rec.PLN_INFO_ID
1941 ,l_Cust_Ref_Rec.CUSTOMER_ID
1942 ,l_Cust_Ref_Rec.SITE_USE_ID);
1943 FETCH mtl_rel_item_cust_ref_lock_b INTO l_lock_b_cust_ref_recinfo;
1944 CLOSE mtl_rel_item_cust_ref_lock_b;
1945
1946 Write_Debug('Updating associated customer reference...');
1947
1948 MTL_RELATED_ITEMS_CUST_REF_PKG.UPDATE_ROW(
1949 P_PLN_INFO_ID => l_Cust_Ref_Rec.Pln_Info_Id
1950 ,P_INVENTORY_ITEM_ID => l_Cust_Ref_Rec.Inventory_Item_Id
1951 ,P_ORGANIZATION_ID => l_Cust_Ref_Rec.Organization_Id
1952 ,P_RELATED_ITEM_ID => l_Cust_Ref_Rec.Related_Item_Id
1953 ,P_RELATIONSHIP_TYPE_ID => l_Cust_Ref_Rec.Relationship_Type_Id
1954 ,P_CUSTOMER_ID => l_Cust_Ref_Rec.Customer_Id
1955 ,P_SITE_USE_ID => l_Cust_Ref_Rec.Site_Use_Id
1956 ,P_START_DATE => l_Cust_Ref_Rec.Start_Date
1957 ,P_END_DATE => l_Cust_Ref_Rec.End_Date
1958 --,P_LAST_UPDATE_DATE => Nvl(l_Rel_Item_Rec.Last_Update_Date, SYSDATE)
1959 --,P_LAST_UPDATED_BY => Nvl(l_Rel_Item_Rec.Last_Updated_By, FND_GLOBAL.USER_ID)
1960 --,P_LAST_UPDATE_LOGIN => Nvl(l_Rel_Item_Rec.Last_Update_Login, FND_GLOBAL.LOGIN_ID)
1961 ,P_LAST_UPDATE_DATE => SYSDATE
1962 ,P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
1963 ,P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
1964 );
1965 END;
1966 END IF;
1967 END IF;
1968
1969 IF l_Cust_Ref_Rec.TRANSACTION_TYPE = 'DELETE' THEN
1970 BEGIN
1971 OPEN mtl_cust_ref_cur(l_Cust_Ref_Rec.INVENTORY_ITEM_ID
1972 ,l_Cust_Ref_Rec.RELATED_ITEM_ID
1973 ,l_Cust_Ref_Rec.RELATIONSHIP_TYPE_ID
1974 ,l_Cust_Ref_Rec.ORGANIZATION_ID
1975 ,l_Cust_Ref_Rec.PLN_INFO_ID
1976 ,l_Cust_Ref_Rec.CUSTOMER_ID
1977 ,l_Cust_Ref_Rec.SITE_USE_ID);
1978 FETCH mtl_cust_ref_cur INTO l_mtl_Rel_Item_Cust_Ref_rec;
1979 IF mtl_cust_ref_cur%NOTFOUND THEN
1980 x_return_status:=FND_API.G_RET_STS_ERROR;
1981 Error_Handler.Add_Error_message
1982 (
1983 p_message_name => 'EGO_CUST_REF_NOTEXISTS'
1984 ,p_application_id => 'EGO'
1985 ,p_message_type => 'E'
1986 ,p_entity_code => G_Entity_Code
1987 ,p_entity_index => l_Cust_Ref_Indx
1988 ,p_table_name => 'MTL_RELATED_ITEMS_CUST_REF'
1989 );
1990 CLOSE mtl_cust_ref_cur;
1991 RAISE VALIDATION_ERROR;
1992 END IF;
1993 CLOSE mtl_cust_ref_cur;
1994
1995 -- locking the cust ref row
1996 OPEN mtl_rel_item_cust_ref_lock_b(l_Cust_Ref_Rec.INVENTORY_ITEM_ID
1997 ,l_Cust_Ref_Rec.RELATED_ITEM_ID
1998 ,l_Cust_Ref_Rec.RELATIONSHIP_TYPE_ID
1999 ,l_Cust_Ref_Rec.ORGANIZATION_ID
2000 ,l_Cust_Ref_Rec.PLN_INFO_ID
2001 ,l_Cust_Ref_Rec.CUSTOMER_ID
2002 ,l_Cust_Ref_Rec.SITE_USE_ID);
2003 FETCH mtl_rel_item_cust_ref_lock_b INTO l_lock_b_cust_ref_recinfo;
2004 CLOSE mtl_rel_item_cust_ref_lock_b;
2005
2006 Write_Debug('Deleting associated customer reference...');
2007 -- calling delete
2008 MTL_RELATED_ITEMS_CUST_REF_PKG.DELETE_ROW(
2009 P_PLN_INFO_ID => l_Cust_Ref_Rec.Pln_Info_Id
2010 ,P_INVENTORY_ITEM_ID => l_Cust_Ref_Rec.Inventory_Item_Id
2011 ,P_ORGANIZATION_ID => l_Cust_Ref_Rec.Organization_Id
2012 ,P_RELATED_ITEM_ID => l_Cust_Ref_Rec.Related_Item_Id
2013 ,P_RELATIONSHIP_TYPE_ID => l_Cust_Ref_Rec.Relationship_Type_Id
2014 ,P_CUSTOMER_ID => l_Cust_Ref_Rec.Customer_Id
2015 ,P_SITE_USE_ID => l_Cust_Ref_Rec.Site_Use_Id);
2016 END;
2017 END IF; -- cust ref delete transaction type
2018 END IF; -- cust ref for plan info rec
2019 ELSE -- if primary keys of cust ref are not provided
2020 x_return_status:=FND_API.g_RET_STS_ERROR;
2021 Error_Handler.Add_Error_message
2022 (
2023 p_message_name => 'EGO_CUST_REF_NULL_COLS'
2024 ,p_application_id => 'EGO'
2025 ,p_message_type => 'E'
2026 ,p_entity_code => G_Entity_Code
2027 ,p_entity_index => l_Cust_Ref_Indx
2028 ,p_table_name => 'MTL_RELATED_ITEMS_CUST_REF'
2029 );
2030 RAISE VALIDATION_ERROR;
2031 End If; -- primary keys of cust ref are not provided
2032 END LOOP; -- cust ref loop
2033 END IF; -- cust ref is not null
2034 END IF; -- end if update/create planning info rec
2035
2036 IF l_Pln_Info_Rec.TRANSACTION_TYPE = 'DELETE' THEN
2037 -- must check whether cust ref exists for this substitution set
2038 -- if so then, cust ref must be delete first prior to deletion of pln info rec
2039
2040 -- must have id if deleting
2041 IF l_Pln_Info_Rec.PLN_INFO_ID = FND_API.G_MISS_NUM THEN
2042
2043 x_return_status:=FND_API.g_RET_STS_ERROR;
2044 Error_Handler.Add_Error_message
2045 (
2046 p_message_name => 'EGO_PLN_INFO_ID_NULL'
2047 ,p_application_id => 'EGO'
2048 ,p_message_type => 'E'
2049 ,p_entity_code => G_Entity_Code
2050 ,p_entity_index => l_Pln_Info_Indx
2051 ,p_table_name => 'MTL_RELATED_ITEMS_PLN_INFO'
2052 );
2053 RAISE VALIDATION_ERROR;
2054 END IF;
2055
2056 BEGIN
2057 SELECT 'x' INTO l_cust_ref_exists
2058 FROM mtl_related_items_cust_ref
2059 WHERE (INVENTORY_ITEM_ID = l_Pln_Info_Rec.INVENTORY_ITEM_ID
2060 AND RELATIONSHIP_TYPE_ID= l_Pln_Info_Rec.RELATIONSHIP_TYPE_ID
2061 AND RELATED_ITEM_ID = l_Pln_Info_Rec.RELATED_ITEM_ID
2062 AND ORGANIZATION_ID = l_Pln_Info_Rec.ORGANIZATION_ID
2063 AND PLN_INFO_ID = l_Pln_Info_Rec.PLN_INFO_ID
2064 );
2065
2066 IF l_cust_ref_exists='x' THEN
2067 l_Token_Tbl(1).Token_Name := 'INVENTORY_ITEM_ID';
2068 l_Token_Tbl(1).Token_Value := l_Pln_Info_Rec.INVENTORY_ITEM_ID;
2069 l_Token_Tbl(1).Translate := FALSE;
2070 l_Token_Tbl(2).Token_Name := 'RELATIONSHIP_TYPE_ID';
2071 l_Token_Tbl(2).Token_Value := l_Pln_Info_Rec.RELATIONSHIP_TYPE_ID;
2072 l_Token_Tbl(2).Translate := FALSE;
2073 l_Token_Tbl(3).Token_Name := 'RELATED_ITEM_ID';
2074 l_Token_Tbl(3).Token_Value := l_Pln_Info_Rec.RELATED_ITEM_ID;
2075 l_Token_Tbl(3).Translate := FALSE;
2076 l_Token_Tbl(4).Token_Name := 'ORGANIZATION_ID';
2077 l_Token_Tbl(4).Token_Value := l_Pln_Info_Rec.ORGANIZATION_ID;
2078 l_Token_Tbl(4).Translate := FALSE;
2079 l_Token_Tbl(5).Token_Name := 'PLN_INFO_ID';
2080 l_Token_Tbl(5).Token_Value := l_Pln_Info_Rec.PLN_INFO_ID;
2081 l_Token_Tbl(5).Translate := FALSE;
2082
2083
2084 x_return_status:=FND_API.g_RET_STS_ERROR;
2085 Error_Handler.Add_Error_message
2086 (
2087 p_message_name => 'EGO_PLN_INFO_CUST_REF_EXISTS'
2088 ,p_application_id => 'EGO'
2089 ,p_token_tbl => l_Token_Tbl
2090 ,p_message_type => 'E'
2091 ,p_entity_code => G_Entity_Code
2092 ,p_entity_index => l_Pln_Info_Indx
2093 ,p_table_name => 'MTL_RELATED_ITEMS_PLN_INFO'
2094 );
2095 RAISE VALIDATION_ERROR;
2096 END IF;
2097 EXCEPTION
2098 WHEN NO_DATA_FOUND THEN
2099 -- calling delete
2100
2101 Write_Debug('Deleting associated substitution set...');
2102
2103 MTL_RELATED_ITEMS_PLN_INFO_PKG.DELETE_ROW(
2104 P_PLN_INFO_ID => l_Pln_Info_Rec.Pln_Info_Id
2105 ,P_INVENTORY_ITEM_ID => l_Pln_Info_Rec.Inventory_Item_Id
2106 ,P_ORGANIZATION_ID => l_Pln_Info_Rec.Organization_Id
2107 ,P_RELATED_ITEM_ID => l_Pln_Info_Rec.Related_Item_Id
2108 ,P_RELATIONSHIP_TYPE_ID => l_Pln_Info_Rec.Relationship_Type_Id);
2109
2110 END;
2111
2112 END IF; -- planning info transaction type
2113 END IF; -- planning info for specific rel item
2114
2115 ELSE -- if missing primary key columns
2116 x_return_status := FND_API.G_RET_STS_ERROR;
2117 Error_Handler.Add_Error_message
2118 (
2119 p_message_name => 'EGO_PLN_INFO_NULL_COLS'
2120 ,p_application_id => 'EGO'
2121 ,p_message_type => 'E'
2122 ,p_entity_code => G_Entity_Code
2123 ,P_ENTITY_INDEX => 1
2124 ,P_TABLE_NAME => 'MTL_RELATED_ITEMS_PLN_INFO'
2125 );
2126 END IF; -- passing in null primary key columns
2127 END LOOP; -- looping planning info
2128 END IF; -- if planning info table is not null
2129 Write_Debug('End checking for associated substitution set...');
2130 END IF; -- if substitute related item type
2131 End If; -- create or update of related item
2132 END;
2133 -- bug 12659679
2134 IF (p_commit = FND_API.G_TRUE) THEN
2135 Write_Debug('Commiting changes...');
2136
2137 COMMIT;
2138 END IF;
2139
2140 EXCEPTION
2141 WHEN VALIDATION_ERROR THEN
2142 x_return_status:= FND_API.g_RET_STS_ERROR;
2143 l_msg_count := l_msg_count +1;
2144 Error_Handler.Get_Entity_Message(G_Entity_Code, x_msg_list);
2145
2146 ROLLBACK TO MTL_RELATED_ITEMS_PVT; -- rollback to savepoint
2147 WHEN resource_busy THEN
2148
2149 Error_Handler.Add_Error_message
2150 (
2151 p_message_name => 'EGO_REL_ITEM_RECORD_LOCKED'
2152 ,p_application_id => 'EGO'
2153 ,p_token_tbl => l_Token_Tbl
2154 ,p_message_type => 'E'
2155 ,p_entity_code => G_Entity_Code
2156 ,p_entity_index => 1
2157 ,p_table_name => G_Table_Name
2158 );
2159
2160 ROLLBACK TO MTL_RELATED_ITEMS_PVT; -- rollback to savepoint
2161 x_return_status:= FND_API.g_RET_STS_ERROR;
2162 l_msg_count := l_msg_count +1;
2163 Error_Handler.Get_Entity_Message(G_Entity_Code, x_msg_list);
2164
2165 WHEN OTHERS THEN
2166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2167 l_Token_Tbl(1).Token_Name := 'PACKAGE_NAME';
2168 l_Token_Tbl(1).Token_Value := G_PKG_NAME;
2169 l_Token_Tbl(1).Translate := FALSE;
2170 l_Token_Tbl(2).Token_Name := 'PROCEDURE_NAME';
2171 l_Token_Tbl(2).Token_Value := l_api_name;
2172 l_Token_Tbl(2).Translate := FALSE;
2173 l_Token_Tbl(3).Token_Name := 'ERROR_TEXT';
2174 l_Token_Tbl(3).Token_Value := SQLERRM;
2175 l_Token_Tbl(3).Translate := FALSE;
2176
2177 Error_Handler.Add_Error_message
2178 (
2179 p_message_name => 'INV_ITEM_UNEXPECTED_ERROR'
2180 ,p_application_id => 'INV'
2181 ,p_token_tbl => l_Token_Tbl
2182 ,p_message_type => 'E'
2183 ,p_entity_code => G_Entity_Code
2184 ,p_entity_index => 1
2185 ,p_table_name => G_Table_Name
2186 );
2187
2188 x_return_status:= FND_API.g_RET_STS_ERROR;
2189 l_msg_count := l_msg_count +1;
2190 Error_Handler.Get_Entity_Message(G_Entity_Code, x_msg_list);
2191
2192 ROLLBACK TO MTL_RELATED_ITEMS_PVT; -- rollback to savepoint
2193
2194 -- //END; -- internal begin for transactions
2195
2196 p_Rel_Item_Rec := l_Rel_Item_Rec;
2197 p_Pln_Info_Tbl := l_Pln_Info_Tbl;
2198 p_Cust_Ref_Tbl := l_Cust_Ref_Tbl;
2199
2200 /* -- bug 12659679
2201 IF (p_commit = FND_API.G_TRUE) THEN
2202 Write_Debug('Commiting changes...');
2203
2204 COMMIT;
2205 END IF;
2206 */
2207 Write_Debug('End related items import API');
2208
2209
2210 END; -- main procedure
2211
2212
2213 -- -----------------------------------------------------------------------------
2214 -- Procedure Name: Process_Rel_Items_Rows
2215 --
2216 -- Description : Concurrent program to pull rows from
2217 -- MTL_RELATED_ITEMS_INTERFACE for processing.
2218 -- Does not support planning details.
2219 -- -----------------------------------------------------------------------------
2220 PROCEDURE Process_Rel_Items_Rows(
2221 ERRBUF OUT NOCOPY VARCHAR2,
2222 RETCODE OUT NOCOPY VARCHAR2,
2223 p_data_set_id IN NUMBER,
2224 p_del_rec_flag IN NUMBER := 1) IS
2225
2226 l_return_status VARCHAR2(1);
2227 l_msg_list Error_Handler.Error_Tbl_Type;
2228 l_msg Error_Handler.Error_Rec_Type;
2229 l_msg_count NUMBER;
2230 l_err_text VARCHAR2(255);
2231 l_dummyInt INTEGER;
2232 ret_code NUMBER;
2233 err_msg VARCHAR2(300);
2234 l_Rel_Item_Rec MTL_RELATED_ITEMS_PUB.Rel_Item_Rec_Type;
2235 l_Pln_Info_Tbl MTL_RELATED_ITEMS_PUB.Pln_Info_Tbl_Type;
2236 l_Cust_Ref_Tbl MTL_RELATED_ITEMS_PUB.Cust_Ref_Tbl_Type;
2237 l_proc_rec_count NUMBER;
2238 l_err_count NUMBER; -- to see if error msg exists
2239
2240 -- Adding to derive these based on the organization_code , related_item_name and item_number columns
2241
2242 p_organization_id NUMBER;
2243 p_related_item_id NUMBER;
2244 p_inventory_item_id NUMBER;
2245 l_Token_Tbl Error_Handler.Token_Tbl_Type; -- For passing token in error msgs
2246
2247 -- cursor for going through the interface table
2248 -- for records where set_process_id is equal to parameter taken in
2249 -- or all records if parameter is NULL
2250 -- and process flag is 1
2251 CURSOR C_INTF_ROWS(C_DATA_SET_ID NUMBER) IS
2252 SELECT A.ROWID,A.*
2253 FROM MTL_RELATED_ITEMS_INTERFACE A
2254 WHERE DECODE(C_DATA_SET_ID, NULL, -1, A.SET_PROCESS_ID) = NVL(C_DATA_SET_ID, -1)
2255 AND a.PROCESS_FLAG = 1;
2256
2257 BEGIN
2258
2259 FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering process Related Items Import.');
2260 FND_FILE.PUT_LINE( FND_FILE.LOG,'Set Process Id : '||To_Char(p_data_set_id));
2261
2262 l_err_count := 0; -- start with no errors
2263 RETCODE := 0; -- assumes successful completion
2264
2265 L_PROC_REC_COUNT := 0;
2266
2267 FOR L_MTL_REL_ITEM_REC IN C_INTF_ROWS(P_DATA_SET_ID) LOOP
2268 UPDATE MTL_RELATED_ITEMS_INTERFACE
2269 SET TRANSACTION_ID = MTL_RELATED_ITEMS_INTERFACE_S.NEXTVAL,
2270 REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
2271 WHERE ROWID = l_mtl_Rel_Item_rec.ROWID;
2272 END LOOP;
2273
2274 FOR l_mtl_Rel_Item_rec IN c_intf_rows(p_data_set_id) LOOP
2275
2276 Error_Handler.Initialize; -- Adding this as we now have validations and error handling in the Process_Rel_Items_Rows itself
2277
2278 l_proc_rec_count := +1;
2279
2280 l_return_status := FND_API.G_RET_STS_SUCCESS;
2281
2282 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2283 IF l_mtl_Rel_Item_rec.ORGANIZATION_CODE IS NOT NULL THEN
2284
2285 BEGIN
2286
2287 SELECT mtp.organization_id into p_organization_id
2288 FROM mtl_parameters mtp
2289 WHERE mtp.organization_code = l_mtl_Rel_Item_rec.ORGANIZATION_CODE;
2290
2291
2292 IF l_mtl_Rel_Item_rec.ORGANIZATION_ID IS NOT NULL THEN
2293 IF (l_mtl_Rel_Item_rec.ORGANIZATION_ID <> p_organization_id) THEN
2294
2295 l_return_status := FND_API.g_RET_STS_ERROR;
2296 Error_Handler.Add_Error_message
2297 (
2298 p_message_name => 'EGO_ASSOC_INVALID_ORG'
2299 ,p_application_id => 'EGO'
2300 ,p_message_type => 'E'
2301 ,p_entity_code => G_Entity_Code
2302 ,p_entity_index => 1
2303 ,p_table_name => 'MTL_RELATED_ITEMS_INTERFACE'
2304 );
2305
2306 l_msg_count := 1;
2307 Error_Handler.Get_Entity_Message(G_Entity_Code, l_msg_list);
2308
2309 END IF;
2310 END IF;
2311
2312
2313 l_mtl_Rel_Item_rec.ORGANIZATION_ID := p_organization_id ;
2314
2315 EXCEPTION
2316 WHEN NO_DATA_FOUND THEN
2317
2318 l_return_status := FND_API.g_RET_STS_ERROR;
2319 Error_Handler.Add_Error_message
2320 (
2321 p_message_name => 'EGO_ASSOC_INVALID_ORG'
2322 ,p_application_id => 'EGO'
2323 ,p_message_type => 'E'
2324 ,p_entity_code => G_Entity_Code
2325 ,p_entity_index => 1
2326 ,p_table_name => 'MTL_RELATED_ITEMS_INTERFACE'
2327 );
2328
2329 l_msg_count := 1;
2330 Error_Handler.Get_Entity_Message(G_Entity_Code, l_msg_list);
2331
2332 END;
2333
2334 END IF;
2335 END IF;
2336
2337 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2338 IF l_mtl_Rel_Item_rec.related_item_number IS NOT NULL THEN
2339
2340 BEGIN
2341
2342 SELECT msk.inventory_item_id into p_related_item_id
2343 FROM mtl_system_items_b_kfv msk
2344 WHERE msk.concatenated_segments = l_mtl_Rel_Item_rec.RELATED_ITEM_NUMBER
2345 and rownum=1;
2346
2347 IF l_mtl_Rel_Item_rec.RELATED_ITEM_ID IS NOT NULL THEN
2348 IF (l_mtl_Rel_Item_rec.RELATED_ITEM_ID <> p_related_item_id) THEN
2349
2350 l_return_status := FND_API.g_RET_STS_ERROR;
2351 Error_Handler.Add_Error_message
2352 (
2353 p_message_name => 'EGO_INVALID_ITEM_SEGMENTS'
2354 ,p_application_id => 'EGO'
2355 ,p_message_type => 'E'
2356 ,p_entity_code => G_Entity_Code
2357 ,p_entity_index => 1
2358 ,p_table_name => 'MTL_RELATED_ITEMS_INTERFACE'
2359 );
2360
2361 l_msg_count := 1;
2362 Error_Handler.Get_Entity_Message(G_Entity_Code, l_msg_list);
2363
2364 END IF;
2365 END IF;
2366
2367 l_mtl_Rel_Item_rec.RELATED_ITEM_ID := p_related_item_id ;
2368
2369 EXCEPTION
2370 WHEN NO_DATA_FOUND THEN
2371
2372
2373 l_return_status := FND_API.g_RET_STS_ERROR;
2374 Error_Handler.Add_Error_message
2375 (
2376 p_message_name => 'EGO_INVALID_ITEM_SEGMENTS'
2377 ,p_application_id => 'EGO'
2378 ,p_message_type => 'E'
2379 ,p_entity_code => G_Entity_Code
2380 ,p_entity_index => 1
2381 ,p_table_name => 'MTL_RELATED_ITEMS_INTERFACE'
2382 );
2383 l_msg_count := 1;
2384 Error_Handler.Get_Entity_Message(G_Entity_Code, l_msg_list);
2385
2386 END;
2387 END IF;
2388 END IF;
2389
2390 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2391 IF l_mtl_Rel_Item_rec.item_number IS NOT NULL THEN
2392
2393 BEGIN
2394
2395 SELECT msk.inventory_item_id into p_inventory_item_id
2396 FROM mtl_system_items_b_kfv msk
2397 WHERE msk.concatenated_segments = l_mtl_Rel_Item_rec.ITEM_NUMBER
2398 and organization_id=l_mtl_Rel_Item_rec.ORGANIZATION_ID;
2399
2400 IF l_mtl_Rel_Item_rec.INVENTORY_ITEM_ID IS NOT NULL THEN
2401 IF (l_mtl_Rel_Item_rec.INVENTORY_ITEM_ID <> p_inventory_item_id) THEN
2402
2403 l_return_status := FND_API.g_RET_STS_ERROR;
2404
2405 Error_Handler.Add_Error_message
2406 (
2407 p_message_name => 'EGO_INVALID_ITEM_SEGMENTS'
2408 ,p_application_id => 'EGO'
2409 ,p_message_type => 'E'
2410 ,p_entity_code => G_Entity_Code
2411 ,p_entity_index => 1
2412 ,p_table_name => 'MTL_RELATED_ITEMS_INTERFACE'
2413 );
2414
2415 l_msg_count := 1;
2416 Error_Handler.Get_Entity_Message(G_Entity_Code, l_msg_list);
2417
2418 END IF;
2419 END IF;
2420
2421 l_mtl_Rel_Item_rec.INVENTORY_ITEM_ID := p_inventory_item_id ;
2422 EXCEPTION
2423 WHEN NO_DATA_FOUND THEN
2424
2425 l_return_status := FND_API.g_RET_STS_ERROR;
2426 Error_Handler.Add_Error_message
2427 (
2428 p_message_name => 'EGO_INVALID_ITEM_SEGMENTS'
2429 ,p_application_id => 'EGO'
2430 ,p_message_type => 'E'
2431 ,p_entity_code => G_Entity_Code
2432 ,p_entity_index => 1
2433 ,p_table_name => 'MTL_RELATED_ITEMS_INTERFACE'
2434 );
2435 l_msg_count := 1;
2436 Error_Handler.Get_Entity_Message(G_Entity_Code, l_msg_list);
2437
2438 END;
2439
2440 END IF;
2441 END IF;
2442
2443
2444
2445 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2446
2447 l_Rel_Item_Rec.TRANSACTION_TYPE := l_mtl_Rel_Item_rec.TRANSACTION_TYPE;
2448 l_Rel_Item_Rec.INVENTORY_ITEM_ID := l_mtl_Rel_Item_rec.INVENTORY_ITEM_ID;
2449 l_Rel_Item_Rec.ORGANIZATION_ID := l_mtl_Rel_Item_rec.ORGANIZATION_ID;
2450 l_Rel_Item_Rec.RELATED_ITEM_ID := l_mtl_Rel_Item_rec.RELATED_ITEM_ID;
2451 l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID := l_mtl_Rel_Item_rec.RELATIONSHIP_TYPE_ID;
2452 -- bug 14403169
2453 l_Rel_Item_Rec.RELATED_ITEM_ID_UPD_VAL := nvl(l_mtl_Rel_Item_rec.RELATED_ITEM_ID_UPD_VAL,FND_API.G_MISS_NUM);
2454 l_Rel_Item_Rec.RELATIONSHIP_TYPE_ID_UPD_VAL := nvl(l_mtl_Rel_Item_rec.RELATIONSHIP_TYPE_ID_UPD_VAL,FND_API.G_MISS_NUM);
2455
2456 l_Rel_Item_Rec.RECIPROCAL_FLAG := l_mtl_Rel_Item_rec.RECIPROCAL_FLAG;
2457 l_Rel_Item_Rec.START_DATE := l_mtl_Rel_Item_rec.START_DATE;
2458 l_Rel_Item_Rec.END_DATE := l_mtl_Rel_Item_rec.END_DATE;
2459 l_Rel_Item_Rec.ATTR_CONTEXT := l_mtl_Rel_Item_rec.ATTR_CONTEXT;
2460 l_Rel_Item_Rec.ATTR_CHAR1 := l_mtl_Rel_Item_rec.ATTR_CHAR1;
2461 l_Rel_Item_Rec.ATTR_CHAR2 := l_mtl_Rel_Item_rec.ATTR_CHAR2;
2462 l_Rel_Item_Rec.ATTR_CHAR3 := l_mtl_Rel_Item_rec.ATTR_CHAR3;
2463 l_Rel_Item_Rec.ATTR_CHAR4 := l_mtl_Rel_Item_rec.ATTR_CHAR4;
2464 l_Rel_Item_Rec.ATTR_CHAR5 := l_mtl_Rel_Item_rec.ATTR_CHAR5;
2465 l_Rel_Item_Rec.ATTR_CHAR6 := l_mtl_Rel_Item_rec.ATTR_CHAR6;
2466 l_Rel_Item_Rec.ATTR_CHAR7 := l_mtl_Rel_Item_rec.ATTR_CHAR7;
2467 l_Rel_Item_Rec.ATTR_CHAR8 := l_mtl_Rel_Item_rec.ATTR_CHAR8;
2468 l_Rel_Item_Rec.ATTR_CHAR9 := l_mtl_Rel_Item_rec.ATTR_CHAR9;
2469 l_Rel_Item_Rec.ATTR_CHAR10 := l_mtl_Rel_Item_rec.ATTR_CHAR10;
2470 l_Rel_Item_Rec.ATTR_NUM1 := l_mtl_Rel_Item_rec.ATTR_NUM1;
2471 l_Rel_Item_Rec.ATTR_NUM2 := l_mtl_Rel_Item_rec.ATTR_NUM2;
2472 l_Rel_Item_Rec.ATTR_NUM3 := l_mtl_Rel_Item_rec.ATTR_NUM3;
2473 l_Rel_Item_Rec.ATTR_NUM4 := l_mtl_Rel_Item_rec.ATTR_NUM4;
2474 l_Rel_Item_Rec.ATTR_NUM5 := l_mtl_Rel_Item_rec.ATTR_NUM5;
2475 l_Rel_Item_Rec.ATTR_NUM6 := l_mtl_Rel_Item_rec.ATTR_NUM6;
2476 l_Rel_Item_Rec.ATTR_NUM7 := l_mtl_Rel_Item_rec.ATTR_NUM7;
2477 l_Rel_Item_Rec.ATTR_NUM8 := l_mtl_Rel_Item_rec.ATTR_NUM8;
2478 l_Rel_Item_Rec.ATTR_NUM9 := l_mtl_Rel_Item_rec.ATTR_NUM9;
2479 l_Rel_Item_Rec.ATTR_NUM10 := l_mtl_Rel_Item_rec.ATTR_NUM10;
2480 l_Rel_Item_Rec.ATTR_DATE1 := l_mtl_Rel_Item_rec.ATTR_DATE1;
2481 l_Rel_Item_Rec.ATTR_DATE2 := l_mtl_Rel_Item_rec.ATTR_DATE2;
2482 l_Rel_Item_Rec.ATTR_DATE3 := l_mtl_Rel_Item_rec.ATTR_DATE3;
2483 l_Rel_Item_Rec.ATTR_DATE4 := l_mtl_Rel_Item_rec.ATTR_DATE4;
2484 l_Rel_Item_Rec.ATTR_DATE5 := l_mtl_Rel_Item_rec.ATTR_DATE5;
2485 l_Rel_Item_Rec.ATTR_DATE6 := l_mtl_Rel_Item_rec.ATTR_DATE6;
2486 l_Rel_Item_Rec.ATTR_DATE7 := l_mtl_Rel_Item_rec.ATTR_DATE7;
2487 l_Rel_Item_Rec.ATTR_DATE8 := l_mtl_Rel_Item_rec.ATTR_DATE8;
2488 l_Rel_Item_Rec.ATTR_DATE9 := l_mtl_Rel_Item_rec.ATTR_DATE9;
2489 l_Rel_Item_Rec.ATTR_DATE10 := l_mtl_Rel_Item_rec.ATTR_DATE10;
2490 l_Rel_Item_Rec.PLANNING_ENABLED_FLAG := l_mtl_Rel_Item_rec.PLANNING_ENABLED_FLAG;
2491 --l_Rel_Item_Rec.LAST_UPDATE_DATE := l_mtl_Rel_Item_rec.LAST_UPDATE_DATE;
2492 --l_Rel_Item_Rec.LAST_UPDATED_BY := l_mtl_Rel_Item_rec.LAST_UPDATED_BY;
2493 --l_Rel_Item_Rec.CREATION_DATE := l_mtl_Rel_Item_rec.CREATION_DATE;
2494 --l_Rel_Item_Rec.CREATED_BY := l_mtl_Rel_Item_rec.CREATED_BY;
2495 --l_Rel_Item_Rec.LAST_UPDATE_LOGIN := l_mtl_Rel_Item_rec.LAST_UPDATE_LOGIN;
2496
2497 Write_Debug('Calling MTL_RELATED_ITEMS_PVT.Process_Rel_Item for Transaction ' ||To_Char(l_mtl_Rel_Item_rec.Transaction_Id));
2498 MTL_RELATED_ITEMS_PVT.Process_Rel_Item
2499 (
2500 p_commit => FND_API.G_TRUE
2501 ,p_init_msg_list => FND_API.G_TRUE
2502 ,p_Rel_Item_Rec => l_Rel_Item_Rec
2503 ,p_Pln_Info_Tbl => l_Pln_Info_Tbl
2504 ,p_Cust_Ref_Tbl => l_Cust_Ref_Tbl
2505 ,x_return_status => l_return_status
2506 ,x_msg_count => l_msg_count
2507 ,x_msg_list => l_msg_list
2508 );
2509 END IF;
2510
2511 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2512 UPDATE MTL_RELATED_ITEMS_INTERFACE
2513 SET process_flag = 3
2514 WHERE Transaction_Id = l_mtl_Rel_Item_rec.Transaction_Id;
2515
2516 -- looping to take out the messages
2517 IF l_msg_list.FIRST IS NOT NULL THEN
2518 FOR msg IN l_msg_list.FIRST..l_msg_list.LAST LOOP
2519 l_msg := l_msg_list(msg);
2520 FND_FILE.PUT_LINE( FND_FILE.LOG,'Error Message for Transaction '||To_Char(l_mtl_Rel_Item_rec.Transaction_Id)|| ' : ' ||l_msg.message_text);
2521
2522 -- place into mtl_interface_error_table
2523 l_dummyInt := mtl_cross_references_pvt.LOG_ERROR --bug 12533707 -- invpuopi.mtl_log_interface_err
2524 (
2525 org_id => l_msg.organization_id
2526 ,user_id => fnd_global.user_id
2527 ,login_id => fnd_global.login_id
2528 ,prog_appid => null
2529 ,prog_id => null
2530 ,req_id => fnd_global.conc_request_id
2531 ,trans_id => l_mtl_Rel_Item_rec.Transaction_Id
2532 ,error_text => l_msg.message_text
2533 ,p_column_name => null
2534 ,tbl_name => 'MTL_RELATED_ITEMS_INTERFACE'
2535 ,msg_name => l_msg.message_name
2536 ,err_text => l_err_text -- error text out
2537 );
2538 END LOOP;
2539 END IF;
2540
2541 Write_Debug('Transaction ' ||To_Char(l_mtl_Rel_Item_rec.Transaction_Id)|| ' has failed.');
2542 l_err_count := 1;
2543
2544 ELSE
2545 Write_Debug('Transaction ' ||To_Char(l_mtl_Rel_Item_rec.Transaction_Id)|| ' has completed successfully.');
2546 UPDATE MTL_RELATED_ITEMS_INTERFACE
2547 SET process_flag = 7
2548 WHERE Transaction_Id = l_mtl_Rel_Item_rec.Transaction_Id;
2549 END IF;
2550 END LOOP;
2551
2552 -- calling method to delete processed rows:
2553 if p_del_rec_flag=1 then
2554 ret_code := Del_Processed_Recs (err_text => err_msg,
2555 com_flag => 1,
2556 p_data_set_id => p_data_set_id);
2557 end if ;
2558
2559 IF l_err_count = 1 THEN
2560 RETCODE := 1; -- if any errors, then display warning for concurrent program
2561 ELSE
2562 RETCODE := 0;
2563 END IF;
2564
2565
2566 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processed '||To_Char(l_proc_rec_count)|| ' records.');
2567 FND_FILE.PUT_LINE( FND_FILE.LOG,'Exiting Related Items Import.');
2568
2569
2570 END Process_Rel_Items_Rows;
2571
2572 -- -----------------------------------------------------------------------------
2573 -- Procedure Name: Del_Processed_Recs
2574 --
2575 -- Description : Method to delete processed rows from intf table.
2576
2577 -- -----------------------------------------------------------------------------
2578 FUNCTION Del_Processed_Recs
2579 (
2580 err_text OUT NOCOPY VARCHAR2,
2581 com_flag IN NUMBER DEFAULT 1,
2582 p_data_set_id IN NUMBER DEFAULT -999
2583 )
2584 RETURN INTEGER
2585 IS
2586
2587 l_process_flag_7 NUMBER := 7;
2588 l_rownum NUMBER := 100000;
2589
2590 BEGIN
2591
2592 LOOP
2593 DELETE FROM MTL_RELATED_ITEMS_INTERFACE
2594 WHERE process_flag = l_process_flag_7
2595 AND set_process_id=p_data_set_id
2596 AND rownum < l_rownum;
2597
2598 EXIT WHEN SQL%NOTFOUND;
2599
2600 IF com_flag = 1 THEN
2601 commit;
2602 END IF;
2603 END LOOP;
2604
2605 RETURN (0);
2606
2607 EXCEPTION
2608
2609 WHEN OTHERS THEN
2610 err_text := SUBSTR('MTL_RELATED_ITEMS_PVT.Del_Processed_Recs' || SQLERRM, 1,240);
2611 RETURN (SQLCODE);
2612
2613 END Del_Processed_Recs;
2614
2615 END MTL_RELATED_ITEMS_PVT;
2616