[Home] [Help]
PACKAGE BODY: APPS.INVCIINT
Source
1 PACKAGE BODY INVCIINT as
2 /* $Header: INVICOIB.pls 120.6.12010000.2 2008/07/29 12:52:36 ptkumar ship $ */
3
4 TYPE SegOrderArray IS TABLE OF Number INDEX BY BINARY_INTEGER;
5
6
7 PROCEDURE Load_Cust_Item(ERRBUF OUT NOCOPY VARCHAR2,
8 RETCODE OUT NOCOPY VARCHAR2,
9 ARGUMENT1 IN VARCHAR2,
10 ARGUMENT2 IN VARCHAR2) IS
11
12 L_Retcode Number;
13 CONC_STATUS BOOLEAN;
14
15 --3537282 : Gather stats before running
16 l_schema VARCHAR2(30);
17 l_status VARCHAR2(1);
18 l_industry VARCHAR2(1);
19 l_records NUMBER(10);
20
21 BEGIN
22
23 --Start 3537282 : Gather stats before running
24 IF fnd_global.conc_program_id <> -1 THEN
25
26 SELECT count(*) INTO l_records
27 FROM mtl_ci_interface
28 WHERE process_flag = 1;
29 -- Bug 6983407 Collect statistics only if the no. of records is bigger than the profile
30 -- option threshold
31 IF l_records > nvl(fnd_profile.value('EGO_GATHER_STATS'),100) AND FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema) THEN
32 IF l_schema IS NOT NULL THEN
33 FND_STATS.GATHER_TABLE_STATS(l_schema, 'MTL_CI_INTERFACE');
34 END IF;
35 END IF;
36 END IF;
37 --End 3537282 : Gather stats before running
38
39
40 L_Retcode := Load_Cust_Items_Iface(argument1,
41 argument2);
42
43 if L_Retcode = 1 then
44 RETCODE := 'Success';
45 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
46 elsif L_Retcode = 3 then
47 RETCODE := 'Warning';
48 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',Current_Error_Code);
49 else
50 RETCODE := 'Error';
51 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
52 end if;
53
54 END Load_Cust_Item;
55
56
57 FUNCTION Load_Cust_Items_Iface(
58 Abort_On_Error IN Varchar2 DEFAULT 'No',
59 Delete_Record IN Varchar2 DEFAULT 'Yes'
60 ) RETURN Number IS
61
62 L_Success Number := 1;
63
64 CURSOR CI_Cur IS
65 SELECT Rowid Row_Id,
66 Process_Mode,
67 Customer_Name,
68 Customer_Number,
69 Customer_Id,
70 Customer_Category_Code,
71 Customer_Category,
72 Address1,
73 Address2,
74 Address3,
75 Address4,
76 City,
77 State,
78 County,
79 Country,
80 Postal_Code,
81 Address_Id,
82 trim(Customer_Item_Number) Customer_Item_Number, --5622573
83 Item_Definition_Level_Desc,
84 Item_Definition_Level,
85 Customer_Item_Desc,
86 Model_Customer_Item_Number,
87 Model_Customer_Item_Id,
88 Commodity_Code,
89 Commodity_Code_Id,
90 Master_Container_Segment1,
91 Master_Container_Segment2,
92 Master_Container_Segment3,
93 Master_Container_Segment4,
94 Master_Container_Segment5,
95 Master_Container_Segment6,
96 Master_Container_Segment7,
97 Master_Container_Segment8,
98 Master_Container_Segment9,
99 Master_Container_Segment10,
100 Master_Container_Segment11,
101 Master_Container_Segment12,
102 Master_Container_Segment13,
103 Master_Container_Segment14,
104 Master_Container_Segment15,
105 Master_Container_Segment16,
106 Master_Container_Segment17,
107 Master_Container_Segment18,
108 Master_Container_Segment19,
109 Master_Container_Segment20,
110 Master_Container,
111 Master_Container_Item_Id,
112 Container_Item_Org_Name,
113 Container_Item_Org_Code,
114 Container_Item_Org_Id,
115 Detail_Container_Segment1,
116 Detail_Container_Segment2,
117 Detail_Container_Segment3,
118 Detail_Container_Segment4,
119 Detail_Container_Segment5,
120 Detail_Container_Segment6,
121 Detail_Container_Segment7,
122 Detail_Container_Segment8,
123 Detail_Container_Segment9,
124 Detail_Container_Segment10,
125 Detail_Container_Segment11,
126 Detail_Container_Segment12,
127 Detail_Container_Segment13,
128 Detail_Container_Segment14,
129 Detail_Container_Segment15,
130 Detail_Container_Segment16,
131 Detail_Container_Segment17,
132 Detail_Container_Segment18,
133 Detail_Container_Segment19,
134 Detail_Container_Segment20,
135 Detail_Container,
136 Detail_Container_Item_Id,
137 Min_Fill_Percentage,
138 Dep_Plan_Required_Flag,
139 Dep_Plan_Prior_Bld_Flag,
140 Inactive_Flag,
141 Attribute_Category,
142 Attribute1,
143 Attribute2,
144 Attribute3,
145 Attribute4,
146 Attribute5,
147 Attribute6,
148 Attribute7,
149 Attribute8,
150 Attribute9,
151 Attribute10,
152 Attribute11,
153 Attribute12,
154 Attribute13,
155 Attribute14,
156 Attribute15,
157 Demand_Tolerance_Positive,
158 Demand_Tolerance_Negative,
159 Last_Update_Date,
160 Last_Updated_By,
161 Creation_Date,
162 Created_By,
163 Last_Update_Login,
164 Request_Id,
165 Program_Application_Id,
166 Program_Id,
167 Program_Update_Date
168 FROM MTL_CI_INTERFACE
169 WHERE Process_Flag = 1
170 AND Process_Mode = 1
171 OR Process_Mode = 3
172 AND UPPER(Transaction_Type) = 'CREATE'
173 ORDER BY Model_Customer_Item_Id, Model_Customer_Item_Number
174 FOR UPDATE NOWAIT;
175
176 Recinfo CI_Cur%ROWTYPE;
177
178 Error_Number Number := NULL;
179 Error_Message Varchar2(2000) := NULL;
180 Error_Counter Number := 0;
181 Curr_Error Varchar2(9) := 'APP-00000';
182
183 BEGIN
184
185 OPEN CI_Cur;
186
187 While (UPPER(Abort_On_Error) <> 'Y' or
188 Error_Counter <= 0) LOOP
189
190 FETCH CI_Cur INTO Recinfo;
191
192 EXIT WHEN CI_Cur%NOTFOUND;
193
194 BEGIN
195
196 IF (Recinfo.Process_Mode = 1) THEN
197
198 Validate_Customer_Item(
199 Recinfo.Row_Id, Recinfo.Process_Mode,
200 Recinfo.Customer_Name,
201 Recinfo.Customer_Number,
202 Recinfo.Customer_Id,
203 Recinfo.Customer_Category_Code,
204 Recinfo.Customer_Category,
205 Recinfo.Address1, Recinfo.Address2,
206 Recinfo.Address3, Recinfo.Address4,
207 Recinfo.City, Recinfo.State,
208 Recinfo.County, Recinfo.Country,
209 Recinfo.Postal_Code, Recinfo.Address_Id,
210 Recinfo.Customer_Item_Number,
211 Recinfo.Item_Definition_Level_Desc,
212 Recinfo.Item_Definition_Level,
213 Recinfo.Customer_Item_Desc,
214 Recinfo.Model_Customer_Item_Number,
215 Recinfo.Model_Customer_Item_Id,
216 Recinfo.Commodity_Code,
217 Recinfo.Commodity_Code_Id,
218 Recinfo.Master_Container_Segment1,
219 Recinfo.Master_Container_Segment2,
220 Recinfo.Master_Container_Segment3,
221 Recinfo.Master_Container_Segment4,
222 Recinfo.Master_Container_Segment5,
223 Recinfo.Master_Container_Segment6,
224 Recinfo.Master_Container_Segment7,
225 Recinfo.Master_Container_Segment8,
226 Recinfo.Master_Container_Segment9,
227 Recinfo.Master_Container_Segment10,
228 Recinfo.Master_Container_Segment11,
229 Recinfo.Master_Container_Segment12,
230 Recinfo.Master_Container_Segment13,
231 Recinfo.Master_Container_Segment14,
232 Recinfo.Master_Container_Segment15,
233 Recinfo.Master_Container_Segment16,
234 Recinfo.Master_Container_Segment17,
235 Recinfo.Master_Container_Segment18,
236 Recinfo.Master_Container_Segment19,
237 Recinfo.Master_Container_Segment20,
238 Recinfo.Master_Container,
239 Recinfo.Master_Container_Item_Id,
240 Recinfo.Container_Item_Org_Name,
241 Recinfo.Container_Item_Org_Code,
242 Recinfo.Container_Item_Org_Id,
243 Recinfo.Detail_Container_Segment1,
244 Recinfo.Detail_Container_Segment2,
245 Recinfo.Detail_Container_Segment3,
246 Recinfo.Detail_Container_Segment4,
247 Recinfo.Detail_Container_Segment5,
248 Recinfo.Detail_Container_Segment6,
249 Recinfo.Detail_Container_Segment7,
250 Recinfo.Detail_Container_Segment8,
251 Recinfo.Detail_Container_Segment9,
252 Recinfo.Detail_Container_Segment10,
253 Recinfo.Detail_Container_Segment11,
254 Recinfo.Detail_Container_Segment12,
255 Recinfo.Detail_Container_Segment13,
256 Recinfo.Detail_Container_Segment14,
257 Recinfo.Detail_Container_Segment15,
258 Recinfo.Detail_Container_Segment16,
259 Recinfo.Detail_Container_Segment17,
260 Recinfo.Detail_Container_Segment18,
261 Recinfo.Detail_Container_Segment19,
262 Recinfo.Detail_Container_Segment20,
263 Recinfo.Detail_Container,
264 Recinfo.Detail_Container_Item_Id,
265 Recinfo.Min_Fill_Percentage,
266 Recinfo.Dep_Plan_Required_Flag,
267 Recinfo.Dep_Plan_Prior_Bld_Flag,
268 Recinfo.Inactive_Flag,
269 Recinfo.Attribute_Category,
270 Recinfo.Attribute1, Recinfo.Attribute2,
271 Recinfo.Attribute3, Recinfo.Attribute4,
272 Recinfo.Attribute5, Recinfo.Attribute6,
273 Recinfo.Attribute7, Recinfo.Attribute8,
274 Recinfo.Attribute9, Recinfo.Attribute10,
275 Recinfo.Attribute11, Recinfo.Attribute12,
276 Recinfo.Attribute13, Recinfo.Attribute14,
277 Recinfo.Attribute15,
278 Recinfo.Demand_Tolerance_Positive,
279 Recinfo.Demand_Tolerance_Negative,
280 Recinfo.Last_Update_Date,
281 Recinfo.Last_Updated_By,
282 Recinfo.Creation_Date,
283 Recinfo.Created_By,
284 Recinfo.Last_Update_Login,
285 nvl(Recinfo.Request_Id, fnd_global.conc_request_id),
286 nvl(Recinfo.Program_Application_Id, fnd_global.prog_appl_id),
287 nvl(Recinfo.Program_Id, fnd_global.conc_program_id),
288 nvl(Recinfo.Program_Update_Date, sysdate),
289 Delete_Record );
290 /*
291 if L_Success = 1 then
292 COMMIT;
293 end if;
294 */
295 ELSIF (Recinfo.Process_Mode = 3) THEN
296
297 Delete_Row('I', Delete_Record,
298 Recinfo.Row_Id);
299 /*
300 if L_Success = 1 then
301 COMMIT;
302 end if;
303 */
304 ELSE
305 NULL;
306 END IF;
307
308 EXCEPTION
309 WHEN Error THEN
310
311 L_Success := 3;
312 Error_Counter := Error_Counter + 1;
313 FND_MESSAGE.Set_Token('TABLE',
314 'MTL_CI_INTERFACE', FALSE);
315 Error_Message := FND_MESSAGE.Get;
316 Manage_Error_Code('OUT', NULL, Curr_Error);
317
318 UPDATE MTL_CI_INTERFACE MCII
319 SET MCII.Error_Code = Curr_Error,
320 MCII.Error_Explanation = substrb(Error_Message,1,235),
321 MCII.Process_Mode = 2
322 WHERE MCII.Rowid = Recinfo.Row_Id;
323 /*
324 COMMIT;
325 */
326 WHEN OTHERS THEN
327
328 L_Success := 2;
329 Error_Number := SQLCODE;
330 Error_Message := SUBSTRB(SQLERRM, 1, 512);
331
332 UPDATE MTL_CI_INTERFACE MCII
333 SET MCII.Error_Code = TO_CHAR(Error_Number),
334 MCII.Error_Explanation = substrb(Error_Message,1,235),
335 MCII.Process_Mode = 2
336 WHERE MCII.Rowid = Recinfo.Row_Id;
337 /*
338 COMMIT;
339 */
340 Raise;
341 END;
342
343 END LOOP;
344
345 CLOSE CI_Cur;
346
347 IF (Error_Counter > 0) THEN
348 L_Success := 3;
349 FND_MESSAGE.Set_Name('INV', 'INV_CI_OPEN_INT_WARNING');
350 FND_MESSAGE.Set_Token('TABLE', 'MTL_CI_INTERFACE', FALSE);
351 FND_MESSAGE.Set_Token('ERROR_COUNT', Error_Counter, FALSE);
352 Error_Message := FND_MESSAGE.Get;
353 --DBMS_OUTPUT.Put_Line(Error_Message);
354 END IF;
355
356 COMMIT;
357
358 Return L_Success;
359
360 EXCEPTION
361
362 WHEN Error THEN
363
364 L_Success := 3;
365 Error_Counter := Error_Counter + 1;
366 FND_MESSAGE.Set_Token('TABLE', 'MTL_CI_INTERFACE', FALSE);
367 Error_Message := FND_MESSAGE.Get;
368 Manage_Error_Code('OUT', NULL, Curr_Error);
369
370 UPDATE MTL_CI_INTERFACE MCII
371 SET MCII.Error_Code = Curr_Error,
372 MCII.Error_Explanation = substrb(Error_Message,1,235),
373 MCII.Process_Mode = 2
374 WHERE MCII.Rowid = Recinfo.Row_Id;
375
376 COMMIT;
377
378 Return L_Success;
379
380 WHEN OTHERS THEN
381
382 L_Success := 2;
383 Error_Counter := Error_Counter + 1;
384 Error_Number := SQLCODE;
385 Error_Message := SUBSTRB(SQLERRM, 1, 512);
386
387 /* Fix for bug 5263099 - Added the below code to handle the scenario
388 where Cursor CI_Cur fails to open because the rows in
389 MTL_CI_INTERFACE are already locked by some other session.
390 It leads to "ORA-00054-resource busy and acquire with NOWAIT specified" error.
391 So we check for this error condition SQLCODE= -54.
392 Manage_Error_Code will set the Current_Error_Code to the corresponding
393 error msg which shall then be shown in the conc prog log file.
394 */
395 If SQLCODE= -54 Then
396 Manage_Error_Code('IN',substrb(Error_Message,1,235), Curr_Error);
397 End If;
398
399 UPDATE MTL_CI_INTERFACE MCII
400 SET MCII.Error_Code = TO_CHAR(Error_Number),
401 MCII.Error_Explanation = substrb(Error_Message,1,235),
402 MCII.Process_Mode = 2
403 WHERE MCII.Rowid = Recinfo.Row_Id;
404
405 COMMIT;
406
407 Return L_Success;
408
409 END Load_Cust_Items_Iface;
410
411
412 PROCEDURE Validate_Customer_Item(
413 Row_Id IN OUT NOCOPY Varchar2,
414 Process_Mode IN OUT NOCOPY Number,
415 Customer_Name IN OUT NOCOPY Varchar2,
416 Customer_Number IN OUT NOCOPY Varchar2,
417 Customer_Id IN OUT NOCOPY Number,
418 Customer_Category_Code IN OUT NOCOPY Varchar2,
419 Customer_Category IN OUT NOCOPY Varchar2,
420 Address1 IN OUT NOCOPY Varchar2,
421 Address2 IN OUT NOCOPY Varchar2,
422 Address3 IN OUT NOCOPY Varchar2,
423 Address4 IN OUT NOCOPY Varchar2,
424 City IN OUT NOCOPY Varchar2,
425 State IN OUT NOCOPY Varchar2,
426 County IN OUT NOCOPY Varchar2,
427 Country IN OUT NOCOPY Varchar2,
428 Postal_Code IN OUT NOCOPY Varchar2,
429 Address_Id IN OUT NOCOPY Number,
430 Customer_Item_Number IN OUT NOCOPY Varchar2,
431 Item_Definition_Level_Desc IN OUT NOCOPY Varchar2,
432 Item_Definition_Level IN OUT NOCOPY Number,
433 Customer_Item_Desc IN OUT NOCOPY Varchar2,
434 Model_Customer_Item_Number IN OUT NOCOPY Varchar2,
435 Model_Customer_Item_Id IN OUT NOCOPY Number,
436 Commodity_Code IN OUT NOCOPY Varchar2,
437 Commodity_Code_Id IN OUT NOCOPY Number,
438 Master_Container_Segment1 IN OUT NOCOPY Varchar2,
439 Master_Container_Segment2 IN OUT NOCOPY Varchar2,
440 Master_Container_Segment3 IN OUT NOCOPY Varchar2,
441 Master_Container_Segment4 IN OUT NOCOPY Varchar2,
442 Master_Container_Segment5 IN OUT NOCOPY Varchar2,
443 Master_Container_Segment6 IN OUT NOCOPY Varchar2,
444 Master_Container_Segment7 IN OUT NOCOPY Varchar2,
445 Master_Container_Segment8 IN OUT NOCOPY Varchar2,
446 Master_Container_Segment9 IN OUT NOCOPY Varchar2,
447 Master_Container_Segment10 IN OUT NOCOPY Varchar2,
448 Master_Container_Segment11 IN OUT NOCOPY Varchar2,
449 Master_Container_Segment12 IN OUT NOCOPY Varchar2,
450 Master_Container_Segment13 IN OUT NOCOPY Varchar2,
451 Master_Container_Segment14 IN OUT NOCOPY Varchar2,
452 Master_Container_Segment15 IN OUT NOCOPY Varchar2,
453 Master_Container_Segment16 IN OUT NOCOPY Varchar2,
454 Master_Container_Segment17 IN OUT NOCOPY Varchar2,
455 Master_Container_Segment18 IN OUT NOCOPY Varchar2,
456 Master_Container_Segment19 IN OUT NOCOPY Varchar2,
457 Master_Container_Segment20 IN OUT NOCOPY Varchar2,
458 Master_Container IN OUT NOCOPY Varchar2,
459 Master_Container_Item_Id IN OUT NOCOPY Number,
460 Container_Item_Org_Name IN OUT NOCOPY Varchar2,
461 Container_Item_Org_Code IN OUT NOCOPY Varchar2,
462 Container_Item_Org_Id IN OUT NOCOPY Number,
463 Detail_Container_Segment1 IN OUT NOCOPY Varchar2,
464 Detail_Container_Segment2 IN OUT NOCOPY Varchar2,
465 Detail_Container_Segment3 IN OUT NOCOPY Varchar2,
466 Detail_Container_Segment4 IN OUT NOCOPY Varchar2,
467 Detail_Container_Segment5 IN OUT NOCOPY Varchar2,
468 Detail_Container_Segment6 IN OUT NOCOPY Varchar2,
469 Detail_Container_Segment7 IN OUT NOCOPY Varchar2,
470 Detail_Container_Segment8 IN OUT NOCOPY Varchar2,
471 Detail_Container_Segment9 IN OUT NOCOPY Varchar2,
472 Detail_Container_Segment10 IN OUT NOCOPY Varchar2,
473 Detail_Container_Segment11 IN OUT NOCOPY Varchar2,
474 Detail_Container_Segment12 IN OUT NOCOPY Varchar2,
475 Detail_Container_Segment13 IN OUT NOCOPY Varchar2,
476 Detail_Container_Segment14 IN OUT NOCOPY Varchar2,
477 Detail_Container_Segment15 IN OUT NOCOPY Varchar2,
478 Detail_Container_Segment16 IN OUT NOCOPY Varchar2,
479 Detail_Container_Segment17 IN OUT NOCOPY Varchar2,
480 Detail_Container_Segment18 IN OUT NOCOPY Varchar2,
481 Detail_Container_Segment19 IN OUT NOCOPY Varchar2,
482 Detail_Container_Segment20 IN OUT NOCOPY Varchar2,
483 Detail_Container IN OUT NOCOPY Varchar2,
484 Detail_Container_Item_Id IN OUT NOCOPY Number,
485 Min_Fill_Percentage IN OUT NOCOPY Number,
486 Dep_Plan_Required_Flag IN OUT NOCOPY Varchar2,
487 Dep_Plan_Prior_Bld_Flag IN OUT NOCOPY Varchar2,
488 Inactive_Flag IN OUT NOCOPY Varchar2,
489 Attribute_Category IN OUT NOCOPY Varchar2,
490 Attribute1 IN OUT NOCOPY Varchar2,
491 Attribute2 IN OUT NOCOPY Varchar2,
492 Attribute3 IN OUT NOCOPY Varchar2,
493 Attribute4 IN OUT NOCOPY Varchar2,
494 Attribute5 IN OUT NOCOPY Varchar2,
495 Attribute6 IN OUT NOCOPY Varchar2,
496 Attribute7 IN OUT NOCOPY Varchar2,
497 Attribute8 IN OUT NOCOPY Varchar2,
498 Attribute9 IN OUT NOCOPY Varchar2,
499 Attribute10 IN OUT NOCOPY Varchar2,
500 Attribute11 IN OUT NOCOPY Varchar2,
501 Attribute12 IN OUT NOCOPY Varchar2,
502 Attribute13 IN OUT NOCOPY Varchar2,
503 Attribute14 IN OUT NOCOPY Varchar2,
504 Attribute15 IN OUT NOCOPY Varchar2,
505 Demand_Tolerance_Positive IN OUT NOCOPY Number,
506 Demand_Tolerance_Negative IN OUT NOCOPY Number,
507 Last_Update_Date IN OUT NOCOPY Date,
508 Last_Updated_By IN OUT NOCOPY Number,
509 Creation_Date IN OUT NOCOPY Date,
510 Created_By IN OUT NOCOPY Number,
511 Last_Update_Login IN OUT NOCOPY Number,
512 Request_Id IN Number,
513 Program_Application_Id IN Number,
514 Program_Id IN Number,
515 Program_Update_Date IN Date,
516 Delete_Record IN Varchar2 DEFAULT NULL
517 ) IS
518 BEGIN
519
520 Validate_CI_Def_Level (
521 Item_Definition_Level, Item_Definition_Level_Desc,
522 Customer_Id, Customer_Number, Customer_Name,
523 Customer_Category_Code, Customer_Category,
524 Address_Id, Address1, Address2, Address3,
525 Address4, City, State, County, Country, Postal_Code);
526
527 Validate_Commodity_Code(Commodity_Code_Id, Commodity_Code);
528
529 Validate_Inactive_Flag(Inactive_Flag);
530
531 Validate_Concurrent_Program(Request_Id, Program_Application_Id,
532 Program_Id, Program_Update_Date);
533
534 IF ((Master_Container_Item_Id IS NOT NULL)
535 OR (Master_Container IS NOT NULL)
536 OR (Master_Container_Segment1 IS NOT NULL)
537 OR (Master_Container_Segment2 IS NOT NULL)
538 OR (Master_Container_Segment3 IS NOT NULL)
539 OR (Master_Container_Segment4 IS NOT NULL)
540 OR (Master_Container_Segment5 IS NOT NULL)
541 OR (Master_Container_Segment6 IS NOT NULL)
542 OR (Master_Container_Segment7 IS NOT NULL)
543 OR (Master_Container_Segment8 IS NOT NULL)
544 OR (Master_Container_Segment9 IS NOT NULL)
545 OR (Master_Container_Segment10 IS NOT NULL)
546 OR (Master_Container_Segment11 IS NOT NULL)
547 OR (Master_Container_Segment12 IS NOT NULL)
548 OR (Master_Container_Segment13 IS NOT NULL)
549 OR (Master_Container_Segment14 IS NOT NULL)
550 OR (Master_Container_Segment15 IS NOT NULL)
551 OR (Master_Container_Segment16 IS NOT NULL)
552 OR (Master_Container_Segment17 IS NOT NULL)
553 OR (Master_Container_Segment18 IS NOT NULL)
554 OR (Master_Container_Segment19 IS NOT NULL)
555 OR (Master_Container_Segment20 IS NOT NULL)
556 OR (Detail_Container_Item_Id IS NOT NULL)
557 OR (Detail_Container IS NOT NULL)
558 OR (Detail_Container_Segment1 IS NOT NULL)
559 OR (Detail_Container_Segment2 IS NOT NULL)
560 OR (Detail_Container_Segment3 IS NOT NULL)
561 OR (Detail_Container_Segment4 IS NOT NULL)
562 OR (Detail_Container_Segment5 IS NOT NULL)
563 OR (Detail_Container_Segment6 IS NOT NULL)
564 OR (Detail_Container_Segment7 IS NOT NULL)
565 OR (Detail_Container_Segment8 IS NOT NULL)
566 OR (Detail_Container_Segment9 IS NOT NULL)
567 OR (Detail_Container_Segment10 IS NOT NULL)
568 OR (Detail_Container_Segment11 IS NOT NULL)
569 OR (Detail_Container_Segment12 IS NOT NULL)
570 OR (Detail_Container_Segment13 IS NOT NULL)
571 OR (Detail_Container_Segment14 IS NOT NULL)
572 OR (Detail_Container_Segment15 IS NOT NULL)
573 OR (Detail_Container_Segment16 IS NOT NULL)
574 OR (Detail_Container_Segment17 IS NOT NULL)
575 OR (Detail_Container_Segment18 IS NOT NULL)
576 OR (Detail_Container_Segment19 IS NOT NULL)
577 OR (Detail_Container_Segment20 IS NOT NULL)) THEN
578
579 IF ((Container_Item_Org_Id IS NOT NULL)
580 OR (Container_Item_Org_Code IS NOT NULL)
581 OR (Container_Item_Org_Name IS NOT NULL)) THEN
582
583 Validate_Master_Organization(Container_Item_Org_Id,
584 Container_Item_Org_Code,
585 Container_Item_Org_Name);
586
587 IF ((Detail_Container_Item_Id IS NOT NULL)
588 OR (Detail_Container IS NOT NULL)
589 OR (Detail_Container_Segment1 IS NOT NULL)
590 OR (Detail_Container_Segment2 IS NOT NULL)
591 OR (Detail_Container_Segment3 IS NOT NULL)
592 OR (Detail_Container_Segment4 IS NOT NULL)
593 OR (Detail_Container_Segment5 IS NOT NULL)
594 OR (Detail_Container_Segment6 IS NOT NULL)
595 OR (Detail_Container_Segment7 IS NOT NULL)
596 OR (Detail_Container_Segment8 IS NOT NULL)
597 OR (Detail_Container_Segment9 IS NOT NULL)
598 OR (Detail_Container_Segment10 IS NOT NULL)
599 OR (Detail_Container_Segment11 IS NOT NULL)
600 OR (Detail_Container_Segment12 IS NOT NULL)
601 OR (Detail_Container_Segment13 IS NOT NULL)
602 OR (Detail_Container_Segment14 IS NOT NULL)
603 OR (Detail_Container_Segment15 IS NOT NULL)
604 OR (Detail_Container_Segment16 IS NOT NULL)
605 OR (Detail_Container_Segment17 IS NOT NULL)
606 OR (Detail_Container_Segment18 IS NOT NULL)
607 OR (Detail_Container_Segment19 IS NOT NULL)
608 OR (Detail_Container_Segment20 IS NOT NULL)
609 AND (Curr_Error = 'APP-00000')) THEN
610
611 Validate_Containers(Detail_Container_Item_Id,
612 Detail_Container,
613 Detail_Container_Segment1,
614 Detail_Container_Segment2,
615 Detail_Container_Segment3,
616 Detail_Container_Segment4,
617 Detail_Container_Segment5,
618 Detail_Container_Segment6,
619 Detail_Container_Segment7,
620 Detail_Container_Segment8,
621 Detail_Container_Segment9,
622 Detail_Container_Segment10,
623 Detail_Container_Segment11,
624 Detail_Container_Segment12,
625 Detail_Container_Segment13,
626 Detail_Container_Segment14,
627 Detail_Container_Segment15,
628 Detail_Container_Segment16,
629 Detail_Container_Segment17,
630 Detail_Container_Segment18,
631 Detail_Container_Segment19,
632 Detail_Container_Segment20,
633 Container_Item_Org_Id);
634
635 IF ((Master_Container_Item_Id IS NOT NULL)
636 OR (Master_Container IS NOT NULL)
637 OR (Master_Container_Segment1 IS NOT NULL)
638 OR (Master_Container_Segment2 IS NOT NULL)
639 OR (Master_Container_Segment3 IS NOT NULL)
640 OR (Master_Container_Segment4 IS NOT NULL)
641 OR (Master_Container_Segment5 IS NOT NULL)
642 OR (Master_Container_Segment6 IS NOT NULL)
643 OR (Master_Container_Segment7 IS NOT NULL)
644 OR (Master_Container_Segment8 IS NOT NULL)
645 OR (Master_Container_Segment9 IS NOT NULL)
646 OR (Master_Container_Segment10 IS NOT NULL)
647 OR (Master_Container_Segment11 IS NOT NULL)
648 OR (Master_Container_Segment12 IS NOT NULL)
649 OR (Master_Container_Segment13 IS NOT NULL)
650 OR (Master_Container_Segment14 IS NOT NULL)
651 OR (Master_Container_Segment15 IS NOT NULL)
652 OR (Master_Container_Segment16 IS NOT NULL)
653 OR (Master_Container_Segment17 IS NOT NULL)
654 OR (Master_Container_Segment18 IS NOT NULL)
655 OR (Master_Container_Segment19 IS NOT NULL)
656 OR (Master_Container_Segment20 IS NOT NULL)
657 AND (Curr_Error = 'APP-00C03')
658 OR (Curr_Error = 'APP-00000')) THEN
659
660 Validate_Containers(
661 Master_Container_Item_Id,
662 Master_Container,
663 Master_Container_Segment1,
664 Master_Container_Segment2,
665 Master_Container_Segment3,
666 Master_Container_Segment4,
667 Master_Container_Segment5,
668 Master_Container_Segment6,
669 Master_Container_Segment7,
670 Master_Container_Segment8,
671 Master_Container_Segment9,
672 Master_Container_Segment10,
673 Master_Container_Segment11,
674 Master_Container_Segment12,
675 Master_Container_Segment13,
676 Master_Container_Segment14,
677 Master_Container_Segment15,
678 Master_Container_Segment16,
679 Master_Container_Segment17,
680 Master_Container_Segment18,
681 Master_Container_Segment19,
682 Master_Container_Segment20,
683 Container_Item_Org_Id);
684
685 ELSE
686 FND_MESSAGE.Set_Name('INV',
687 'INV_NO_MASTER_CONTAINER');
688 Manage_Error_Code('IN', 'APP-43049',
689 Curr_Error);
690 RAISE Error;
691 END IF;
692 ELSE
693 Validate_Containers(Master_Container_Item_Id,
694 Master_Container,
695 Master_Container_Segment1,
696 Master_Container_Segment2,
697 Master_Container_Segment3,
698 Master_Container_Segment4,
699 Master_Container_Segment5,
700 Master_Container_Segment6,
701 Master_Container_Segment7,
702 Master_Container_Segment8,
703 Master_Container_Segment9,
704 Master_Container_Segment10,
705 Master_Container_Segment11,
706 Master_Container_Segment12,
707 Master_Container_Segment13,
708 Master_Container_Segment14,
709 Master_Container_Segment15,
710 Master_Container_Segment16,
711 Master_Container_Segment17,
712 Master_Container_Segment18,
713 Master_Container_Segment19,
714 Master_Container_Segment20,
715 Container_Item_Org_Id);
716 END IF;
717 ELSE
718
719 FND_MESSAGE.Set_Name('INV', 'INV_NO_ORGANIZATION');
720 FND_MESSAGE.Set_Token('COLUMN1',
721 'MASTER_ORGANIZATION_ID', FALSE);
722 FND_MESSAGE.Set_Token('COLUMN2',
723 'MASTER_ORGANIZATION_CODE', FALSE);
724 FND_MESSAGE.Set_Token('COLUMN3',
725 'MASTER_ORGANIZATION_NAME', FALSE);
726 Manage_Error_Code('IN', 'APP-43045', Curr_Error);
727 RAISE Error;
728 END IF;
729 ELSE
730 NULL;
731 END IF;
732
733 Validate_Model(Model_Customer_Item_Id, Model_Customer_Item_Number,
734 Customer_Id, Address_Id, Customer_Category_Code,
735 Item_Definition_Level, Customer_Item_Number);
736
737 Validate_Demand_Tolerance(Demand_Tolerance_Positive);
738
739 Validate_Demand_Tolerance(Demand_Tolerance_Negative);
740
741 Validate_Fill_Percentage(Min_Fill_Percentage);
742
743 Validate_Departure_Plan_Flags(Dep_Plan_Required_Flag,
744 Dep_Plan_Prior_Bld_Flag);
745
746 Check_Required_Columns('I', Customer_Id, Customer_Item_Number,
747 Item_Definition_Level, Customer_Category_Code,
748 Address_Id, Inactive_Flag, Last_Updated_By,
749 Last_Update_Date, Created_By, Creation_Date,
750 NULL, NULL, NULL, NULL);
751
752 Check_Uniqueness('I', Customer_Id, Customer_Item_Number,
753 Item_Definition_Level, Customer_Category_Code,
754 Address_Id, NULL, NULL, NULL, NULL);
755
756 Insert_Row('I', Last_Update_Date, Last_Updated_By, Creation_Date,
757 Created_By, Last_Update_Login, Customer_Id,
758 Customer_Category_Code, Address_Id,
759 Customer_Item_Number, Item_Definition_Level,
760 Customer_Item_Desc, Model_Customer_Item_Id,
761 Commodity_Code_Id, Master_Container_Item_Id,
762 Container_Item_Org_Id, Detail_Container_Item_Id,
763 Min_Fill_Percentage, Dep_Plan_Required_Flag,
764 Dep_Plan_Prior_Bld_Flag, Inactive_Flag,
765 Attribute_Category, Attribute1, Attribute2,
766 Attribute3, Attribute4, Attribute5, Attribute6,
767 Attribute7, Attribute8, Attribute9, Attribute10,
768 Attribute11, Attribute12, Attribute13, Attribute14,
769 Attribute15, Demand_Tolerance_Positive,
770 Demand_Tolerance_Negative, Request_Id,
771 Program_Application_Id, Program_Id,
772 Program_Update_Date, NULL, NULL, NULL, NULL);
773
774 Delete_Row('I', Delete_Record, Row_Id);
775
776 END Validate_Customer_Item;
777
778
779 PROCEDURE Validate_CI_Def_Level ( P_Item_Definition_Level IN OUT NOCOPY Varchar2,
780 P_Item_Definition_Level_Desc IN Varchar2 DEFAULT NULL,
781 P_Customer_Id IN OUT NOCOPY Number,
782 P_Customer_Number IN OUT NOCOPY Varchar2,
783 P_Customer_Name IN OUT NOCOPY Varchar2,
784 P_Customer_Category_Code IN OUT NOCOPY Varchar2,
785 P_Customer_Category IN OUT NOCOPY Varchar2,
786 P_Address_Id IN OUT NOCOPY Number,
787 P_Address1 IN OUT NOCOPY Varchar2,
788 P_Address2 IN OUT NOCOPY Varchar2,
789 P_Address3 IN OUT NOCOPY Varchar2,
790 P_Address4 IN OUT NOCOPY Varchar2,
791 P_City IN OUT NOCOPY Varchar2,
792 P_State IN OUT NOCOPY Varchar2,
793 P_County IN OUT NOCOPY Varchar2,
794 P_Country IN OUT NOCOPY Varchar2,
795 P_Postal_Code IN OUT NOCOPY Varchar2 ) IS
796
797 Temp_Lookup_Code Number := NULL;
798 Temp_Meaning Varchar2(80) := NULL;
799
800 BEGIN
801
802 IF (P_Item_Definition_Level IS NOT NULL) THEN
803
804 IF (P_Item_Definition_Level = '1') THEN
805
806 Validate_Customer (P_Customer_Id, P_Customer_Number, P_Customer_Name);
807
808 P_Address_Id := NULL;
809
810 P_Customer_Category_Code := NULL;
811
812 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
813
814
815 ELSIF (P_Item_Definition_Level = '2') THEN
816
817 Validate_Customer (P_Customer_Id, P_Customer_Number, P_Customer_Name);
818
819 Validate_Address_Category (P_Customer_Category_Code, P_Customer_Category);
820
821 P_Address_Id := NULL;
822
823 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
824
825
826 ELSIF (P_Item_Definition_Level = '3') THEN
827
828 Validate_Customer (P_Customer_Id, P_Customer_Number, P_Customer_Name);
829
830 Validate_Address (P_Address_Id, P_Customer_Id, P_Address1, P_Address2, P_Address3, P_Address4,
831 P_City, P_State, P_County, P_Country, P_Postal_Code);
832
833 P_Customer_Category_Code := NULL;
834
835 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
836
837 ELSE
838
839 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_ITEM_DEF_LEVEL');
840 FND_MESSAGE.Set_Token('COLUMN', 'ITEM_DEFINITION_LEVEL', FALSE);
841 Manage_Error_Code('IN', 'APP-43000', Curr_Error);
842 RAISE Error;
843
844 END IF;
845
846 ELSIF ((P_Item_Definition_Level IS NULL) AND (P_Item_Definition_Level_Desc IS NOT NULL)) THEN
847
848 SELECT Lookup_Code, Meaning
849 INTO Temp_Lookup_Code, Temp_Meaning
850 FROM MFG_LOOKUPS MFGL
851 WHERE UPPER(MFGL.Meaning) = UPPER(P_Item_Definition_Level_Desc)
852 AND MFGL.Lookup_Type = 'INV_ITEM_DEFINITION_LEVEL';
853
854 IF (Temp_Lookup_Code = 1) THEN
855
856 Validate_Customer (P_Customer_Id, P_Customer_Number, P_Customer_Name);
857
858 P_Address_Id := NULL;
859
860 P_Customer_Category_Code := NULL;
861
862 P_Item_Definition_Level := '1';
863
864 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
865
866 ELSIF (Temp_Lookup_Code = 2 ) THEN
867
868 Validate_Customer (P_Customer_Id, P_Customer_Number, P_Customer_Name);
869
870 Validate_Address_Category (P_Customer_Category_Code, P_Customer_Category);
871
872 P_Address_Id := NULL;
873
874 P_Item_Definition_Level := '2';
875
876 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
877
878 ELSIF (Temp_Lookup_Code = 3) THEN
879
880 Validate_Customer (P_Customer_Id, P_Customer_Number, P_Customer_Name);
881
882 Validate_Address (P_Address_Id, P_Customer_Id, P_Address1, P_Address2, P_Address3, P_Address4,
883 P_City, P_State, P_County, P_Country, P_Postal_Code);
884
885 P_Customer_Category_Code := NULL;
886
887 P_Item_Definition_Level := '3';
888
889 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
890
891 ELSE
892
893 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_ITEM_DEF_LEVEL');
894 FND_MESSAGE.Set_Token('COLUMN', 'ITEM_DEFINITION_LEVEL_DESC', FALSE);
895 Manage_Error_Code('IN', 'APP-43000', Curr_Error);
896 RAISE Error;
897
898 END IF;
899
900 ELSE
901
902 FND_MESSAGE.Set_Name('INV', 'INV_NO_ITEM_DEF_LEVEL');
903 Manage_Error_Code('IN', 'APP-43001', Curr_Error);
904 RAISE Error;
905
906 END IF;
907
908 EXCEPTION
909
910 WHEN NO_DATA_FOUND THEN
911
912 FND_MESSAGE.Set_Name('INV', 'INV_NO_SEED_DATA_ITEM_DEF');
913 Manage_Error_Code('IN', 'APP-43047', Curr_Error);
914 RAISE Error;
915
916 WHEN TOO_MANY_ROWS THEN
917
918 FND_MESSAGE.Set_Name('INV', 'INV_MULTIPLE_ITEM_DEF');
919 Manage_Error_Code('IN', 'APP-43048', Curr_Error);
920 RAISE Error;
921
922 END Validate_CI_Def_Level;
923
924
925 PROCEDURE Validate_Customer ( P_Customer_Id IN OUT NOCOPY Number,
926 P_Customer_Number IN Varchar2 DEFAULT NULL,
927 P_Customer_Name IN Varchar2 DEFAULT NULL ) IS
928
929 Temp_Customer_Id Number := NULL;
930 Temp_Status Varchar2(1) := NULL;
931
932 BEGIN
933
934 IF (P_Customer_Id IS NOT NULL) THEN
935 /**Bug: 2786267
936 SELECT Customer_Id, Status
937 INTO Temp_Customer_Id, Temp_Status
938 FROM RA_CUSTOMERS RAC
939 WHERE RAC.Customer_Id = P_Customer_Id;
940 **/
941 SELECT CUST_ACCT.CUST_ACCOUNT_ID, CUST_ACCT.STATUS
942 INTO Temp_Customer_Id, Temp_Status
943 FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
944 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
945 AND CUST_ACCT.CUST_ACCOUNT_ID = P_Customer_Id;
946
947 IF ((SQL%FOUND) AND (Temp_Status <> 'A')) THEN
948
949 FND_MESSAGE.Set_Name('INV', 'INV_INACTIVE_CUSTOMER');
950 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_ID', FALSE);
951 Manage_Error_Code('IN', 'APP-43002', Curr_Error);
952 RAISE Error;
953
954 ELSE
955
956 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
957 P_Customer_Id := Temp_Customer_Id;
958 RETURN;
959
960 END IF;
961
962 ELSIF ((P_Customer_Id IS NULL) AND (P_Customer_Number IS NOT NULL)) THEN
963 /**Bug: 2786267
964 SELECT Customer_Id, Status
965 INTO Temp_Customer_Id, Temp_Status
966 FROM RA_CUSTOMERS RAC
967 WHERE RAC.Customer_Number = P_Customer_Number;
968 **/
969 SELECT CUST_ACCT.CUST_ACCOUNT_ID, CUST_ACCT.STATUS
970 INTO Temp_Customer_Id, Temp_Status
971 FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
972 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
973 AND CUST_ACCT.ACCOUNT_NUMBER = P_Customer_Number;
974
975 IF ((SQL%FOUND) AND (Temp_Status <> 'A')) THEN
976
977 FND_MESSAGE.Set_Name('INV', 'INV_INACTIVE_CUSTOMER');
978 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_NUMBER', FALSE);
979 Manage_Error_Code('IN', 'APP-43002', Curr_Error);
980 RAISE Error;
981
982 ELSE
983
984 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
985 P_Customer_Id := Temp_Customer_Id;
986 RETURN;
987
988 END IF;
989
990 ELSIF ((P_Customer_Id IS NULL) AND (P_Customer_Number IS NULL) AND (P_Customer_Name IS NOT NULL)) THEN
991 /**Bug: 2786267
992 SELECT Customer_Id, Status
993 INTO Temp_Customer_Id, Temp_Status
994 FROM RA_CUSTOMERS RAC
995 WHERE RAC.Customer_Name = P_Customer_Name;
996 **/
997 SELECT CUST_ACCT.CUST_ACCOUNT_ID, CUST_ACCT.STATUS
998 INTO Temp_Customer_Id, Temp_Status
999 FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT
1000 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
1001 AND PARTY.PARTY_NAME like P_Customer_Name || '%';
1002
1003 IF ((SQL%FOUND) AND (Temp_Status <> 'A')) THEN
1004
1005 FND_MESSAGE.Set_Name('INV', 'INV_INACTIVE_CUSTOMER');
1006 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_NAME', FALSE);
1007 Manage_Error_Code('IN', 'APP-43002', Curr_Error);
1008 RAISE Error;
1009
1010 ELSE
1011
1012 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
1013 P_Customer_Id := Temp_Customer_Id;
1014 RETURN;
1015
1016 END IF;
1017
1018 ELSE
1019
1020 FND_MESSAGE.Set_Name('INV', 'INV_NO_CUSTOMER');
1021 Manage_Error_Code('IN', 'APP-43003', Curr_Error);
1022 RAISE Error;
1023
1024 END IF;
1025
1026 EXCEPTION
1027
1028 WHEN NO_DATA_FOUND THEN
1029
1030 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_CUSTOMER');
1031 IF (P_Customer_Id IS NOT NULL) THEN
1032 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_ID', FALSE);
1033 ELSIF ((P_Customer_Id IS NULL) AND (P_Customer_Number IS NOT NULL)) THEN
1034 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_NUMBER', FALSE);
1035 ELSE
1036 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_NAME', FALSE);
1037 END IF;
1038 Manage_Error_Code('IN', 'APP-43004', Curr_Error);
1039 RAISE Error;
1040
1041 WHEN TOO_MANY_ROWS THEN
1042
1043 FND_MESSAGE.Set_Name('INV', 'INV_MULTIPLE_CUSTOMERS');
1044 Manage_Error_Code('IN', 'APP-43005', Curr_Error);
1045 RAISE Error;
1046
1047 END Validate_Customer;
1048
1049
1050 PROCEDURE Validate_Address ( P_Address_Id IN OUT NOCOPY Number,
1051 P_Customer_Id IN Number DEFAULT NULL,
1052 P_Address1 IN Varchar2 DEFAULT NULL,
1053 P_Address2 IN Varchar2 DEFAULT NULL,
1054 P_Address3 IN Varchar2 DEFAULT NULL,
1055 P_Address4 IN Varchar2 DEFAULT NULL,
1056 P_City IN Varchar2 DEFAULT NULL,
1057 P_State IN Varchar2 DEFAULT NULL,
1058 P_County IN Varchar2 DEFAULT NULL,
1059 P_Country IN Varchar2 DEFAULT NULL,
1060 P_Postal_Code IN Varchar2 DEFAULT NULL ) IS
1061
1062 Temp_Address_Id Number := NULL;
1063 Temp_Status Varchar2(1) := NULL;
1064
1065 BEGIN
1066
1067 IF (P_Address_Id IS NOT NULL) THEN
1068
1069 /* The RA_ADDRESSES view has been scrapped. Re-writing this query -Anmurali
1070 SELECT Address_Id, Status
1071 INTO Temp_Address_Id, Temp_Status
1072 FROM RA_ADDRESSES RAA
1073 WHERE RAA.Address_Id = P_Address_Id
1074 AND RAA.Customer_Id = P_Customer_Id; */
1075
1076 SELECT ACCT_SITE.CUST_ACCT_SITE_ID , ACCT_SITE.STATUS
1077 INTO Temp_Address_Id, Temp_Status
1078 FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1079 HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1080 WHERE ACCT_SITE.CUST_ACCT_SITE_ID = P_Address_Id
1081 AND ACCT_SITE.CUST_ACCOUNT_ID = P_Customer_Id
1082 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1083 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1084 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1085 AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)
1086 AND NVL(ACCT_SITE.ORG_ID,
1087 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,
1088 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),- 99)) =
1089 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
1090 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),- 99);
1091
1092 IF ((SQL%FOUND) AND (Temp_Status <> 'A')) THEN
1093
1094 FND_MESSAGE.Set_Name('INV', 'INV_INACTIVE_ADDRESS');
1095 FND_MESSAGE.Set_Token('COLUMN', 'ADDRESS_ID', FALSE);
1096 Manage_Error_Code('IN', 'APP-43006', Curr_Error);
1097 RAISE Error;
1098
1099 ELSE
1100
1101 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
1102 P_Address_Id := Temp_Address_Id;
1103 RETURN;
1104
1105 END IF;
1106
1107 ELSIF ((P_Address_Id IS NULL) AND (P_Customer_Id IS NOT NULL) AND
1108 ((P_Address1 IS NOT NULL) OR (P_Address2 IS NOT NULL) OR
1109 (P_Address3 IS NOT NULL) OR (P_Address4 IS NOT NULL) OR
1110 (P_City IS NOT NULL) OR (P_State IS NOT NULL) OR (P_County IS NOT NULL) OR
1111 (P_Country IS NOT NULL) OR (P_Postal_Code IS NOT NULL))) THEN
1112
1113 /* The view RA_ADDRESSES has been scrapped, re-writing this query -Anmurali
1114
1115 SELECT Address_Id, Status
1116 INTO Temp_Address_Id, Temp_Status
1117 FROM RA_ADDRESSES RAA
1118 WHERE NVL(RAA.Address1, ' ') = NVL(P_Address1, ' ')
1119 AND NVL(RAA.Address2, ' ') = NVL(P_Address2, ' ')
1120 AND NVL(RAA.Address3, ' ') = NVL(P_Address3, ' ')
1121 AND NVL(RAA.Address4, ' ') = NVL(P_Address4, ' ')
1122 AND NVL(RAA.City, ' ') = NVL(P_City, ' ')
1123 AND NVL(RAA.State, ' ') = NVL(P_State, ' ')
1124 AND NVL(RAA.County, ' ') = NVL(P_County, ' ')
1125 AND NVL(RAA.Country, ' ') = NVL(P_Country, ' ')
1126 AND NVL(RAA.Postal_Code, ' ') = NVL(P_Postal_Code, ' ')
1127 AND RAA.Customer_Id = P_Customer_Id; */
1128
1129 SELECT ACCT_SITE.CUST_ACCT_SITE_ID , ACCT_SITE.STATUS
1130 INTO Temp_Address_Id, Temp_Status
1131 FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
1132 HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1133 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1134 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1135 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1136 AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)
1137 AND NVL(ACCT_SITE.ORG_ID,
1138 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,
1139 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),- 99)) =
1140 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
1141 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),- 99)
1142 AND NVL(LOC.Address1, ' ') = NVL(P_Address1, ' ')
1143 AND NVL(LOC.Address2, ' ') = NVL(P_Address2, ' ')
1144 AND NVL(LOC.Address3, ' ') = NVL(P_Address3, ' ')
1145 AND NVL(LOC.Address4, ' ') = NVL(P_Address4, ' ')
1146 AND NVL(LOC.City, ' ') = NVL(P_City, ' ')
1147 AND NVL(LOC.State, ' ') = NVL(P_State, ' ')
1148 AND NVL(LOC.County, ' ') = NVL(P_County, ' ')
1149 AND NVL(LOC.Country, ' ') = NVL(P_Country, ' ')
1150 AND NVL(LOC.Postal_Code, ' ') = NVL(P_Postal_Code, ' ')
1151 AND ACCT_SITE.CUST_ACCOUNT_ID = P_Customer_Id;
1152
1153
1154
1155 IF ((SQL%FOUND) AND (Temp_Status <> 'A')) THEN
1156
1157 FND_MESSAGE.Set_Name('INV', 'INV_INACTIVE_ADDRESS');
1158 FND_MESSAGE.Set_Token('COLUMN', 'ADDRESS_ID', FALSE);
1159 Manage_Error_Code('IN', 'APP-43006', Curr_Error);
1160 RAISE Error;
1161
1162 ELSE
1163
1164 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
1165 P_Address_Id := Temp_Address_Id;
1166 RETURN;
1167
1168 END IF;
1169
1170 ELSE
1171
1172 FND_MESSAGE.Set_Name('INV', 'INV_NO_ADDRESS');
1173 Manage_Error_Code('IN', 'APP-43007', Curr_Error);
1174 RAISE Error;
1175
1176 END IF;
1177
1178 EXCEPTION
1179
1180 WHEN NO_DATA_FOUND THEN
1181
1182 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_ADDRESS');
1183 IF (P_Address_Id IS NOT NULL) THEN
1184 FND_MESSAGE.Set_Token('COLUMN', 'ADDRESS_ID', FALSE);
1185 ELSE
1186 FND_MESSAGE.Set_Token('COLUMN', 'ADDRESS1-4, CITY, STATE, ETC.', FALSE);
1187 END IF;
1188 Manage_Error_Code('IN', 'APP-43008', Curr_Error);
1189 RAISE Error;
1190
1191 WHEN TOO_MANY_ROWS THEN
1192
1193 FND_MESSAGE.Set_Name('INV', 'INV_MULTIPLE_ADDRESSES');
1194 Manage_Error_Code('IN', 'APP-43009', Curr_Error);
1195 RAISE Error;
1196
1197 END Validate_Address;
1198
1199
1200 PROCEDURE Validate_Address_Category ( P_Customer_Category_Code IN OUT NOCOPY Varchar2,
1201 P_Customer_Category IN Varchar2 DEFAULT NULL ) IS
1202
1203 Temp_Lookup_Code Varchar2(80) := NULL;
1204 Temp_Enabled_Flag Varchar2(1) := NULL;
1205 Temp_Start_Date_Active Date := NULL;
1206 Temp_End_Date_Active Date := NULL;
1207
1208 BEGIN
1209
1210 IF (P_Customer_Category_Code IS NOT NULL) THEN
1211
1212 SELECT ARL.Lookup_Code, ARL.Enabled_Flag, ARL.Start_Date_Active, ARL.End_Date_Active
1213 INTO Temp_Lookup_Code, Temp_Enabled_Flag, Temp_Start_Date_Active, Temp_End_Date_Active
1214 FROM AR_LOOKUPS ARL
1215 WHERE ARL.Lookup_Code = P_Customer_Category_Code
1216 AND ARL.Lookup_Type = 'ADDRESS_CATEGORY'
1217 AND rownum = 1;
1218
1219 IF ((SQL%FOUND) AND (Temp_Enabled_Flag = 'N')) THEN
1220
1221 FND_MESSAGE.Set_Name('INV', 'INV_DISABLED_ADDR_CAT');
1222 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_CATEGORY_CODE', FALSE);
1223 Manage_Error_Code('IN', 'APP-43010', Curr_Error);
1224 RAISE Error;
1225
1226 ELSIF ((SQL%FOUND) AND NOT (TRUNC(SYSDATE) BETWEEN NVL(TRUNC((Temp_Start_Date_Active)),SYSDATE)AND NVL(TRUNC((Temp_End_Date_Active)), SYSDATE))) THEN
1227
1228 FND_MESSAGE.Set_Name('INV', 'INV_INACTIVE_ADDR_CAT');
1229 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_CATEGORY_CODE', FALSE);
1230 Manage_Error_Code('IN', 'APP-43011', Curr_Error);
1231 RAISE Error;
1232
1233 ELSE
1234
1235 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
1236 P_Customer_Category_Code := Temp_Lookup_Code;
1237 RETURN;
1238
1239 END IF;
1240
1241 ELSIF ((P_Customer_Category_Code IS NULL) AND (P_Customer_Category IS NOT NULL)) THEN
1242
1243 SELECT ARL.Lookup_Code, ARL.Enabled_Flag, ARL.Start_Date_Active, ARL.End_Date_Active
1244 INTO Temp_Lookup_Code, Temp_Enabled_Flag, Temp_Start_Date_Active, Temp_End_Date_Active
1245 FROM AR_LOOKUPS ARL
1246 WHERE UPPER(ARL.Meaning) = UPPER(P_Customer_Category)
1247 AND ARL.Lookup_Type = 'ADDRESS_CATEGORY'
1248 AND rownum = 1;
1249
1250 IF ((SQL%FOUND) AND (Temp_Enabled_Flag = 'N')) THEN
1251
1252 FND_MESSAGE.Set_Name('INV', 'INV_DISABLED_ADDR_CAT');
1253 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_CATEGORY', FALSE);
1254 Manage_Error_Code('IN', 'APP-43010', Curr_Error);
1255 RAISE Error;
1256
1257 ELSIF ((SQL%FOUND) AND NOT (TRUNC(SYSDATE) BETWEEN NVL(TRUNC((Temp_Start_Date_Active)),SYSDATE)AND NVL(TRUNC((Temp_End_Date_Active)), SYSDATE))) THEN
1258
1259 FND_MESSAGE.Set_Name('INV', 'INV_INACTIVE_ADDR_CAT');
1260 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_CATEGORY', FALSE);
1261 Manage_Error_Code('IN', 'APP-43011', Curr_Error);
1262 RAISE Error;
1263
1264 ELSE
1265
1266 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
1267 P_Customer_Category_Code := Temp_Lookup_Code;
1268 RETURN;
1269
1270 END IF;
1271
1272 ELSE
1273
1274 FND_MESSAGE.Set_Name('INV', 'INV_NO_ADDR_CAT');
1275 Manage_Error_Code('IN', 'APP-43012', Curr_Error);
1276 RAISE Error;
1277
1278 END IF;
1279
1280 EXCEPTION
1281
1282 WHEN NO_DATA_FOUND THEN
1283
1284 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_ADDR_CAT');
1285 IF (P_Customer_Category_Code IS NOT NULL) THEN
1286 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_CATEGORY_CODE', FALSE);
1287 ELSE
1288 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_CATEGORY', FALSE);
1289 END IF;
1290 Manage_Error_Code('IN', 'APP-43013', Curr_Error);
1291 RAISE Error;
1292
1293 END Validate_Address_Category;
1294
1295
1296 PROCEDURE Validate_Containers(
1297 P_Container_Item_Id IN OUT NOCOPY Number,
1298 P_Container_Item IN Varchar2 DEFAULT NULL,
1299 P_Container_Item_Segment1 IN Varchar2 DEFAULT NULL,
1300 P_Container_Item_Segment2 IN Varchar2 DEFAULT NULL,
1301 P_Container_Item_Segment3 IN Varchar2 DEFAULT NULL,
1302 P_Container_Item_Segment4 IN Varchar2 DEFAULT NULL,
1303 P_Container_Item_Segment5 IN Varchar2 DEFAULT NULL,
1304 P_Container_Item_Segment6 IN Varchar2 DEFAULT NULL,
1305 P_Container_Item_Segment7 IN Varchar2 DEFAULT NULL,
1306 P_Container_Item_Segment8 IN Varchar2 DEFAULT NULL,
1307 P_Container_Item_Segment9 IN Varchar2 DEFAULT NULL,
1308 P_Container_Item_Segment10 IN Varchar2 DEFAULT NULL,
1309 P_Container_Item_Segment11 IN Varchar2 DEFAULT NULL,
1310 P_Container_Item_Segment12 IN Varchar2 DEFAULT NULL,
1311 P_Container_Item_Segment13 IN Varchar2 DEFAULT NULL,
1312 P_Container_Item_Segment14 IN Varchar2 DEFAULT NULL,
1313 P_Container_Item_Segment15 IN Varchar2 DEFAULT NULL,
1314 P_Container_Item_Segment16 IN Varchar2 DEFAULT NULL,
1315 P_Container_Item_Segment17 IN Varchar2 DEFAULT NULL,
1316 P_Container_Item_Segment18 IN Varchar2 DEFAULT NULL,
1317 P_Container_Item_Segment19 IN Varchar2 DEFAULT NULL,
1318 P_Container_Item_Segment20 IN Varchar2 DEFAULT NULL,
1319 P_Container_Organization_Id IN Number DEFAULT NULL
1320 ) IS
1321
1322 Temp_Container_Item_Id Number := NULL;
1323 Now Varchar2(20) := TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS');
1324 Temp_Container_Item_Flag Varchar2(1) := NULL;
1325 L_Segment_Array fnd_flex_ext.SegmentArray;
1326 L_SegOrder_Array SegOrderArray;
1327 L_TempSeg_Counter Number := 0;
1328 L_Success Boolean;
1329 L_Temp_Concat_Container Varchar2(2000);
1330 L_FlexSeg_Counter Number := 0;
1331
1332 Cursor L_FlexSegOrder_Curr is
1333 SELECT segment_num from
1334 fnd_id_flex_segments
1335 where application_id = 401
1336 and id_flex_code = 'MSTK'
1337 and enabled_flag = 'Y'
1338 order by segment_num;
1339
1340 L_SegNumDummy Number;
1341 L_SegNumIndex Number := 0;
1342 L_Delimiter Varchar2(1) := NULL;
1343 L_ConcatSegs Varchar2(2000) := NULL;
1344 L_StructNum Number := NULL;
1345
1346 BEGIN
1347
1348 IF (P_Container_Item_Id IS NOT NULL) THEN
1349
1350 SELECT Inventory_Item_Id
1351 INTO Temp_Container_Item_Id
1352 FROM MTL_SYSTEM_ITEMS MSI
1353 WHERE MSI.Inventory_Item_Id = P_Container_Item_Id
1354 AND MSI.Container_Item_Flag = 'Y'
1355 AND MSI.Organization_Id = P_Container_Organization_Id;
1356
1357 IF (SQL%FOUND) THEN
1358
1359 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
1360 P_Container_Item_Id := Temp_Container_Item_Id;
1361 RETURN;
1362
1363 END IF;
1364
1365 ELSIF ((P_Container_Item_Id IS NULL) AND
1366 (P_Container_Item IS NOT NULL)) THEN
1367
1368
1369 BEGIN
1370 SELECT ID_Flex_Num
1371 INTO L_StructNum
1372 FROM fnd_id_flex_structures
1373 WHERE application_id = 401
1374 AND id_flex_code = 'MSTK'
1375 AND upper(enabled_flag) = 'Y';
1376 EXCEPTION
1377 WHEN NO_DATA_FOUND THEN
1378 FND_MESSAGE.Set_Name('INV', 'INV_NO_ITEM_FLEX_STRUCT');
1379 Manage_Error_Code('IN', 'APP-43014',
1380 Curr_Error);
1381 RAISE Error;
1382
1383 WHEN TOO_MANY_ROWS THEN
1384 FND_MESSAGE.Set_Name('INV',
1385 'INV_MULT_ITEM_FLEX_STRUCT');
1386 Manage_Error_Code('IN', 'APP-43014',
1387 Curr_Error);
1388 RAISE Error;
1389 END;
1390
1391 L_Success := FND_FLEX_KEYVAL.Validate_Segs(
1392 OPERATION => 'FIND_COMBINATION',
1393 APPL_SHORT_NAME => 'INV',
1394 KEY_FLEX_CODE => 'MSTK',
1395 STRUCTURE_NUMBER=> L_StructNum,
1396 CONCAT_SEGMENTS => P_Container_Item,
1397 DATA_SET => P_Container_Organization_Id,
1398 WHERE_CLAUSE =>
1399 'UPPER(MTL_SYSTEM_ITEMS.Container_Item_Flag) = ''Y'''
1400 );
1401
1402 if L_Success then
1403 P_Container_Item_Id :=
1404 FND_FLEX_KEYVAL.Combination_Id;
1405
1406 else
1407 P_Container_Item_Id := NULL;
1408 end if;
1409
1410 If P_Container_Item_Id is NULL then
1411
1412 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_CONTAINER');
1413 FND_MESSAGE.Set_Token('COLUMN',
1414 'CONTAINER_ITEM', FALSE);
1415 Manage_Error_Code('IN', 'APP-43014',
1416 Curr_Error);
1417 RAISE Error;
1418 END IF;
1419
1420 ELSIF ((P_Container_Item_Id IS NULL) AND
1421 (P_Container_Item IS NULL) ) THEN
1422
1423 BEGIN
1424 SELECT ID_Flex_Num
1425 INTO L_StructNum
1426 FROM fnd_id_flex_structures
1427 WHERE application_id = 401
1428 AND id_flex_code = 'MSTK'
1429 AND upper(enabled_flag) = 'Y';
1430 EXCEPTION
1431 WHEN NO_DATA_FOUND THEN
1432 FND_MESSAGE.Set_Name('INV', 'INV_NO_ITEM_FLEX_STRUCT');
1433 Manage_Error_Code('IN', 'APP-43014',
1434 Curr_Error);
1435 RAISE Error;
1436
1437 WHEN TOO_MANY_ROWS THEN
1438 FND_MESSAGE.Set_Name('INV',
1439 'INV_MULT_ITEM_FLEX_STRUCT');
1440 Manage_Error_Code('IN', 'APP-43014',
1441 Curr_Error);
1442 RAISE Error;
1443 END;
1444
1445 OPEN L_FlexSegOrder_Curr;
1446
1447 LOOP
1448 FETCH L_FlexSegOrder_Curr into L_SegNumDummy;
1449 EXIT WHEN L_FlexSegOrder_Curr%NOTFOUND;
1450
1451 L_FlexSeg_Counter := L_FlexSeg_Counter+1;
1452 L_Segment_Array(L_FlexSeg_Counter) := NULL;
1453 L_SegOrder_Array(L_FlexSeg_Counter) := L_SegNumDummy;
1454 END LOOP;
1455
1456 CLOSE L_FlexSegOrder_Curr;
1457
1458 if P_Container_Item_Segment1 IS NOT NULL THEN
1459 L_TempSeg_Counter := L_TempSeg_Counter + 1;
1460
1461 BEGIN
1462 select segment_num
1463 into L_SegNumDummy
1464 from fnd_id_flex_segments
1465 where application_id = 401
1466 and id_flex_code = 'MSTK'
1467 and enabled_flag = 'Y'
1468 and application_column_name = 'SEGMENT1';
1469
1470 EXCEPTION
1471 WHEN NO_DATA_FOUND THEN
1472 FND_MESSAGE.Set_Name('INV',
1473 'INV_INVALID_CONTAINER_SEG');
1474 FND_MESSAGE.Set_Token('SEGMENT',
1475 'CONTAINER_SEGMENT1',
1476 FALSE);
1477 Manage_Error_Code('IN', 'APP-43014',
1478 Curr_Error);
1479 RAISE Error;
1480
1481 WHEN TOO_MANY_ROWS THEN
1482 FND_MESSAGE.Set_Name('INV',
1483 'INV_MULTIPLE_FLEX_SEG');
1484 Manage_Error_Code('IN', 'APP-43014',
1485 Curr_Error);
1486 RAISE Error;
1487 END;
1488
1489 L_SegNumIndex := 0;
1490
1491 for i in 1..L_FlexSeg_Counter loop
1492
1493 L_SegNumIndex := i;
1494
1495 EXIT WHEN L_SegOrder_Array(i) =
1496 L_SegNumDummy;
1497 end loop;
1498
1499
1500 if L_SegNumIndex <= L_FlexSeg_Counter then
1501 L_Segment_Array(L_SegNumIndex) :=
1502 P_Container_Item_Segment1;
1503 else
1504 FND_MESSAGE.Set_Name('INV',
1505 'INV_FLEX_SEG_OUT_OF_RANGE');
1506 Manage_Error_Code('IN', 'APP-43014',
1507 Curr_Error);
1508 RAISE Error;
1509
1510 end if;
1511
1512 end if;
1513
1514 if P_Container_Item_Segment2 IS NOT NULL THEN
1515 L_TempSeg_Counter := L_TempSeg_Counter + 1;
1516
1517 BEGIN
1518 select segment_num
1519 into L_SegNumDummy
1520 from fnd_id_flex_segments
1521 where application_id = 401
1522 and id_flex_code = 'MSTK'
1523 and enabled_flag = 'Y'
1524 and application_column_name = 'SEGMENT2';
1525
1526 EXCEPTION
1527 WHEN NO_DATA_FOUND THEN
1528 FND_MESSAGE.Set_Name('INV',
1529 'INV_INVALID_CONTAINER_SEG');
1530 FND_MESSAGE.Set_Token('SEGMENT',
1531 'CONTAINER_SEGMENT2',
1532 FALSE);
1533 Manage_Error_Code('IN', 'APP-43014',
1534 Curr_Error);
1535 RAISE Error;
1536
1537 WHEN TOO_MANY_ROWS THEN
1538 FND_MESSAGE.Set_Name('INV',
1539 'INV_MULTIPLE_FLEX_SEG');
1540 Manage_Error_Code('IN', 'APP-43014',
1541 Curr_Error);
1542 RAISE Error;
1543 END;
1544
1545 L_SegNumIndex := 0;
1546
1547 for i in 1..L_FlexSeg_Counter loop
1548
1549 L_SegNumIndex := i;
1550
1551 EXIT WHEN L_SegOrder_Array(i) =
1552 L_SegNumDummy;
1553 end loop;
1554
1555
1556 if L_SegNumIndex <= L_FlexSeg_Counter then
1557 L_Segment_Array(L_SegNumIndex) :=
1558 P_Container_Item_Segment2;
1559 else
1560 FND_MESSAGE.Set_Name('INV',
1561 'INV_FLEX_SEG_OUT_OF_RANGE');
1562 Manage_Error_Code('IN', 'APP-43014',
1563 Curr_Error);
1564 RAISE Error;
1565
1566 end if;
1567 end if;
1568
1569 if P_Container_Item_Segment3 IS NOT NULL THEN
1570 L_TempSeg_Counter := L_TempSeg_Counter + 1;
1571
1572 BEGIN
1573 select segment_num
1574 into L_SegNumDummy
1575 from fnd_id_flex_segments
1576 where application_id = 401
1577 and id_flex_code = 'MSTK'
1578 and enabled_flag = 'Y'
1579 and application_column_name = 'SEGMENT3';
1580
1581 EXCEPTION
1582 WHEN NO_DATA_FOUND THEN
1583 FND_MESSAGE.Set_Name('INV',
1584 'INV_INVALID_CONTAINER_SEG');
1585 FND_MESSAGE.Set_Token('SEGMENT',
1586 'CONTAINER_SEGMENT3',
1587 FALSE);
1588 Manage_Error_Code('IN', 'APP-43014',
1589 Curr_Error);
1590 RAISE Error;
1591
1592 WHEN TOO_MANY_ROWS THEN
1593 FND_MESSAGE.Set_Name('INV',
1594 'INV_MULTIPLE_FLEX_SEG');
1595 Manage_Error_Code('IN', 'APP-43014',
1596 Curr_Error);
1597 RAISE Error;
1598 END;
1599
1600 L_SegNumIndex := 0;
1601
1602 for i in 1..L_FlexSeg_Counter loop
1603
1604 L_SegNumIndex := i;
1605
1606 EXIT WHEN L_SegOrder_Array(i) =
1607 L_SegNumDummy;
1608 end loop;
1609
1610
1611 if L_SegNumIndex <= L_FlexSeg_Counter then
1612 L_Segment_Array(L_SegNumIndex) :=
1613 P_Container_Item_Segment3;
1614 else
1615 FND_MESSAGE.Set_Name('INV',
1616 'INV_FLEX_SEG_OUT_OF_RANGE');
1617 Manage_Error_Code('IN', 'APP-43014',
1618 Curr_Error);
1619 RAISE Error;
1620
1621 end if;
1622 end if;
1623
1624 if P_Container_Item_Segment4 IS NOT NULL THEN
1625 L_TempSeg_Counter := L_TempSeg_Counter + 1;
1626
1627 BEGIN
1628 select segment_num
1629 into L_SegNumDummy
1630 from fnd_id_flex_segments
1631 where application_id = 401
1632 and id_flex_code = 'MSTK'
1633 and enabled_flag = 'Y'
1634 and application_column_name = 'SEGMENT4';
1635
1636 EXCEPTION
1637 WHEN NO_DATA_FOUND THEN
1638 FND_MESSAGE.Set_Name('INV',
1639 'INV_INVALID_CONTAINER_SEG');
1640 FND_MESSAGE.Set_Token('SEGMENT',
1641 'CONTAINER_SEGMENT4',
1642 FALSE);
1643 Manage_Error_Code('IN', 'APP-43014',
1644 Curr_Error);
1645 RAISE Error;
1646
1647 WHEN TOO_MANY_ROWS THEN
1648 FND_MESSAGE.Set_Name('INV',
1649 'INV_MULTIPLE_FLEX_SEG');
1650 Manage_Error_Code('IN', 'APP-43014',
1651 Curr_Error);
1652 RAISE Error;
1653 END;
1654
1655 L_SegNumIndex := 0;
1656
1657 for i in 1..L_FlexSeg_Counter loop
1658
1659 L_SegNumIndex := i;
1660
1661 EXIT WHEN L_SegOrder_Array(i) =
1662 L_SegNumDummy;
1663 end loop;
1664
1665
1666 if L_SegNumIndex <= L_FlexSeg_Counter then
1667 L_Segment_Array(L_SegNumIndex) :=
1668 P_Container_Item_Segment4;
1669 else
1670 FND_MESSAGE.Set_Name('INV',
1671 'INV_FLEX_SEG_OUT_OF_RANGE');
1672 Manage_Error_Code('IN', 'APP-43014',
1673 Curr_Error);
1674 RAISE Error;
1675
1676 end if;
1677 end if;
1678
1679 if P_Container_Item_Segment5 IS NOT NULL THEN
1680 L_TempSeg_Counter := L_TempSeg_Counter + 1;
1681
1682 BEGIN
1683 select segment_num
1684 into L_SegNumDummy
1685 from fnd_id_flex_segments
1686 where application_id = 401
1687 and id_flex_code = 'MSTK'
1688 and enabled_flag = 'Y'
1689 and application_column_name = 'SEGMENT5';
1690
1691 EXCEPTION
1692 WHEN NO_DATA_FOUND THEN
1693 FND_MESSAGE.Set_Name('INV',
1694 'INV_INVALID_CONTAINER_SEG');
1695 FND_MESSAGE.Set_Token('SEGMENT',
1696 'CONTAINER_SEGMENT5',
1697 FALSE);
1698 Manage_Error_Code('IN', 'APP-43014',
1699 Curr_Error);
1700 RAISE Error;
1701
1702 WHEN TOO_MANY_ROWS THEN
1703 FND_MESSAGE.Set_Name('INV',
1704 'INV_MULTIPLE_FLEX_SEG');
1705 Manage_Error_Code('IN', 'APP-43014',
1706 Curr_Error);
1707 RAISE Error;
1708 END;
1709
1710 L_SegNumIndex := 0;
1711
1712 for i in 1..L_FlexSeg_Counter loop
1713
1714 L_SegNumIndex := i;
1715
1716 EXIT WHEN L_SegOrder_Array(i) =
1717 L_SegNumDummy;
1718 end loop;
1719
1720
1721 if L_SegNumIndex <= L_FlexSeg_Counter then
1722 L_Segment_Array(L_SegNumIndex) :=
1723 P_Container_Item_Segment5;
1724 else
1725 FND_MESSAGE.Set_Name('INV',
1726 'INV_FLEX_SEG_OUT_OF_RANGE');
1727 Manage_Error_Code('IN', 'APP-43014',
1728 Curr_Error);
1729 RAISE Error;
1730
1731 end if;
1732 end if;
1733
1734 if P_Container_Item_Segment6 IS NOT NULL THEN
1735 L_TempSeg_Counter := L_TempSeg_Counter + 1;
1736
1737 BEGIN
1738 select segment_num
1739 into L_SegNumDummy
1740 from fnd_id_flex_segments
1741 where application_id = 401
1742 and id_flex_code = 'MSTK'
1743 and enabled_flag = 'Y'
1744 and application_column_name = 'SEGMENT6';
1745
1746 EXCEPTION
1747 WHEN NO_DATA_FOUND THEN
1748 FND_MESSAGE.Set_Name('INV',
1749 'INV_INVALID_CONTAINER_SEG');
1750 FND_MESSAGE.Set_Token('SEGMENT',
1751 'CONTAINER_SEGMENT6',
1752 FALSE);
1753 Manage_Error_Code('IN', 'APP-43014',
1754 Curr_Error);
1755 RAISE Error;
1756
1757 WHEN TOO_MANY_ROWS THEN
1758 FND_MESSAGE.Set_Name('INV',
1759 'INV_MULTIPLE_FLEX_SEG');
1760 Manage_Error_Code('IN', 'APP-43014',
1761 Curr_Error);
1762 RAISE Error;
1763 END;
1764
1765 L_SegNumIndex := 0;
1766
1767 for i in 1..L_FlexSeg_Counter loop
1768
1769 L_SegNumIndex := i;
1770
1771 EXIT WHEN L_SegOrder_Array(i) =
1772 L_SegNumDummy;
1773 end loop;
1774
1775
1776 if L_SegNumIndex <= L_FlexSeg_Counter then
1777 L_Segment_Array(L_SegNumIndex) :=
1778 P_Container_Item_Segment6;
1779 else
1780 FND_MESSAGE.Set_Name('INV',
1781 'INV_FLEX_SEG_OUT_OF_RANGE');
1782 Manage_Error_Code('IN', 'APP-43014',
1783 Curr_Error);
1784 RAISE Error;
1785
1786 end if;
1787 end if;
1788
1789 if P_Container_Item_Segment7 IS NOT NULL THEN
1790 L_TempSeg_Counter := L_TempSeg_Counter + 1;
1791
1792 BEGIN
1793 select segment_num
1794 into L_SegNumDummy
1795 from fnd_id_flex_segments
1796 where application_id = 401
1797 and id_flex_code = 'MSTK'
1798 and enabled_flag = 'Y'
1799 and application_column_name = 'SEGMENT7';
1800
1801 EXCEPTION
1802 WHEN NO_DATA_FOUND THEN
1803 FND_MESSAGE.Set_Name('INV',
1804 'INV_INVALID_CONTAINER_SEG');
1805 FND_MESSAGE.Set_Token('SEGMENT',
1806 'CONTAINER_SEGMENT7',
1807 FALSE);
1808 Manage_Error_Code('IN', 'APP-43014',
1809 Curr_Error);
1810 RAISE Error;
1811
1812 WHEN TOO_MANY_ROWS THEN
1813 FND_MESSAGE.Set_Name('INV',
1814 'INV_MULTIPLE_FLEX_SEG');
1815 Manage_Error_Code('IN', 'APP-43014',
1816 Curr_Error);
1817 RAISE Error;
1818 END;
1819
1820 L_SegNumIndex := 0;
1821
1822 for i in 1..L_FlexSeg_Counter loop
1823
1824 L_SegNumIndex := i;
1825
1826 EXIT WHEN L_SegOrder_Array(i) =
1827 L_SegNumDummy;
1828 end loop;
1829
1830
1831 if L_SegNumIndex <= L_FlexSeg_Counter then
1832 L_Segment_Array(L_SegNumIndex) :=
1833 P_Container_Item_Segment7;
1834 else
1835 FND_MESSAGE.Set_Name('INV',
1836 'INV_FLEX_SEG_OUT_OF_RANGE');
1837 Manage_Error_Code('IN', 'APP-43014',
1838 Curr_Error);
1839 RAISE Error;
1840
1841 end if;
1842 end if;
1843
1844 if P_Container_Item_Segment8 IS NOT NULL THEN
1845 L_TempSeg_Counter := L_TempSeg_Counter + 1;
1846
1847 BEGIN
1848 select segment_num
1849 into L_SegNumDummy
1850 from fnd_id_flex_segments
1851 where application_id = 401
1852 and id_flex_code = 'MSTK'
1853 and enabled_flag = 'Y'
1854 and application_column_name = 'SEGMENT8';
1855
1856 EXCEPTION
1857 WHEN NO_DATA_FOUND THEN
1858 FND_MESSAGE.Set_Name('INV',
1859 'INV_INVALID_CONTAINER_SEG');
1860 FND_MESSAGE.Set_Token('SEGMENT',
1861 'CONTAINER_SEGMENT8',
1862 FALSE);
1863 Manage_Error_Code('IN', 'APP-43014',
1864 Curr_Error);
1865 RAISE Error;
1866
1867 WHEN TOO_MANY_ROWS THEN
1868 FND_MESSAGE.Set_Name('INV',
1869 'INV_MULTIPLE_FLEX_SEG');
1870 Manage_Error_Code('IN', 'APP-43014',
1871 Curr_Error);
1872 RAISE Error;
1873 END;
1874
1875 L_SegNumIndex := 0;
1876
1877 for i in 1..L_FlexSeg_Counter loop
1878
1879 L_SegNumIndex := i;
1880
1881 EXIT WHEN L_SegOrder_Array(i) =
1882 L_SegNumDummy;
1883 end loop;
1884
1885
1886 if L_SegNumIndex <= L_FlexSeg_Counter then
1887 L_Segment_Array(L_SegNumIndex) :=
1888 P_Container_Item_Segment8;
1889 else
1890 FND_MESSAGE.Set_Name('INV',
1891 'INV_FLEX_SEG_OUT_OF_RANGE');
1892 Manage_Error_Code('IN', 'APP-43014',
1893 Curr_Error);
1894 RAISE Error;
1895
1896 end if;
1897 end if;
1898
1899 if P_Container_Item_Segment9 IS NOT NULL THEN
1900 L_TempSeg_Counter := L_TempSeg_Counter + 1;
1901
1902 BEGIN
1903 select segment_num
1904 into L_SegNumDummy
1905 from fnd_id_flex_segments
1906 where application_id = 401
1907 and id_flex_code = 'MSTK'
1908 and enabled_flag = 'Y'
1909 and application_column_name = 'SEGMENT9';
1910
1911 EXCEPTION
1912 WHEN NO_DATA_FOUND THEN
1913 FND_MESSAGE.Set_Name('INV',
1914 'INV_INVALID_CONTAINER_SEG');
1915 FND_MESSAGE.Set_Token('SEGMENT',
1916 'CONTAINER_SEGMENT9',
1917 FALSE);
1918 Manage_Error_Code('IN', 'APP-43014',
1919 Curr_Error);
1920 RAISE Error;
1921
1922 WHEN TOO_MANY_ROWS THEN
1923 FND_MESSAGE.Set_Name('INV',
1924 'INV_MULTIPLE_FLEX_SEG');
1925 Manage_Error_Code('IN', 'APP-43014',
1926 Curr_Error);
1927 RAISE Error;
1928 END;
1929
1930 L_SegNumIndex := 0;
1931
1932 for i in 1..L_FlexSeg_Counter loop
1933
1934 L_SegNumIndex := i;
1935
1936 EXIT WHEN L_SegOrder_Array(i) =
1937 L_SegNumDummy;
1938 end loop;
1939
1940
1941 if L_SegNumIndex <= L_FlexSeg_Counter then
1942 L_Segment_Array(L_SegNumIndex) :=
1943 P_Container_Item_Segment9;
1944 else
1945 FND_MESSAGE.Set_Name('INV',
1946 'INV_FLEX_SEG_OUT_OF_RANGE');
1947 Manage_Error_Code('IN', 'APP-43014',
1948 Curr_Error);
1949 RAISE Error;
1950
1951 end if;
1952 end if;
1953
1954 if P_Container_Item_Segment10 IS NOT NULL THEN
1955 L_TempSeg_Counter := L_TempSeg_Counter + 1;
1956
1957 BEGIN
1958 select segment_num
1959 into L_SegNumDummy
1960 from fnd_id_flex_segments
1961 where application_id = 401
1962 and id_flex_code = 'MSTK'
1963 and enabled_flag = 'Y'
1964 and application_column_name = 'SEGMENT10';
1965
1966 EXCEPTION
1967 WHEN NO_DATA_FOUND THEN
1968 FND_MESSAGE.Set_Name('INV',
1969 'INV_INVALID_CONTAINER_SEG');
1970 FND_MESSAGE.Set_Token('SEGMENT',
1971 'CONTAINER_SEGMENT10',
1972 FALSE);
1973 Manage_Error_Code('IN', 'APP-43014',
1974 Curr_Error);
1975 RAISE Error;
1976
1977 WHEN TOO_MANY_ROWS THEN
1978 FND_MESSAGE.Set_Name('INV',
1979 'INV_MULTIPLE_FLEX_SEG');
1980 Manage_Error_Code('IN', 'APP-43014',
1981 Curr_Error);
1982 RAISE Error;
1983 END;
1984
1985 L_SegNumIndex := 0;
1986
1987 for i in 1..L_FlexSeg_Counter loop
1988
1989 L_SegNumIndex := i;
1990
1991 EXIT WHEN L_SegOrder_Array(i) =
1992 L_SegNumDummy;
1993 end loop;
1994
1995
1996 if L_SegNumIndex <= L_FlexSeg_Counter then
1997 L_Segment_Array(L_SegNumIndex) :=
1998 P_Container_Item_Segment10;
1999 else
2000 FND_MESSAGE.Set_Name('INV',
2001 'INV_FLEX_SEG_OUT_OF_RANGE');
2002 Manage_Error_Code('IN', 'APP-43014',
2003 Curr_Error);
2004 RAISE Error;
2005
2006 end if;
2007 end if;
2008
2009 if P_Container_Item_Segment11 IS NOT NULL THEN
2010 L_TempSeg_Counter := L_TempSeg_Counter + 1;
2011
2012 BEGIN
2013 select segment_num
2014 into L_SegNumDummy
2015 from fnd_id_flex_segments
2016 where application_id = 401
2017 and id_flex_code = 'MSTK'
2018 and enabled_flag = 'Y'
2019 and application_column_name = 'SEGMENT11';
2020
2021 EXCEPTION
2022 WHEN NO_DATA_FOUND THEN
2023 FND_MESSAGE.Set_Name('INV',
2024 'INV_INVALID_CONTAINER_SEG');
2025 FND_MESSAGE.Set_Token('SEGMENT',
2026 'CONTAINER_SEGMENT11',
2027 FALSE);
2028 Manage_Error_Code('IN', 'APP-43014',
2029 Curr_Error);
2030 RAISE Error;
2031
2032 WHEN TOO_MANY_ROWS THEN
2033 FND_MESSAGE.Set_Name('INV',
2034 'INV_MULTIPLE_FLEX_SEG');
2035 Manage_Error_Code('IN', 'APP-43014',
2036 Curr_Error);
2037 RAISE Error;
2038 END;
2039
2040 L_SegNumIndex := 0;
2041
2042 for i in 1..L_FlexSeg_Counter loop
2043
2044 L_SegNumIndex := i;
2045
2046 EXIT WHEN L_SegOrder_Array(i) =
2047 L_SegNumDummy;
2048 end loop;
2049
2050
2051 if L_SegNumIndex <= L_FlexSeg_Counter then
2052 L_Segment_Array(L_SegNumIndex) :=
2053 P_Container_Item_Segment11;
2054 else
2055 FND_MESSAGE.Set_Name('INV',
2056 'INV_FLEX_SEG_OUT_OF_RANGE');
2057 Manage_Error_Code('IN', 'APP-43014',
2058 Curr_Error);
2059 RAISE Error;
2060
2061 end if;
2062 end if;
2063
2064 if P_Container_Item_Segment12 IS NOT NULL THEN
2065 L_TempSeg_Counter := L_TempSeg_Counter + 1;
2066
2067 BEGIN
2068 select segment_num
2069 into L_SegNumDummy
2070 from fnd_id_flex_segments
2071 where application_id = 401
2072 and id_flex_code = 'MSTK'
2073 and enabled_flag = 'Y'
2074 and application_column_name = 'SEGMENT12';
2075
2076 EXCEPTION
2077 WHEN NO_DATA_FOUND THEN
2078 FND_MESSAGE.Set_Name('INV',
2079 'INV_INVALID_CONTAINER_SEG');
2080 FND_MESSAGE.Set_Token('SEGMENT',
2081 'CONTAINER_SEGMENT12',
2082 FALSE);
2083 Manage_Error_Code('IN', 'APP-43014',
2084 Curr_Error);
2085 RAISE Error;
2086
2087 WHEN TOO_MANY_ROWS THEN
2088 FND_MESSAGE.Set_Name('INV',
2089 'INV_MULTIPLE_FLEX_SEG');
2090 Manage_Error_Code('IN', 'APP-43014',
2091 Curr_Error);
2092 RAISE Error;
2093 END;
2094
2095 L_SegNumIndex := 0;
2096
2097 for i in 1..L_FlexSeg_Counter loop
2098
2099 L_SegNumIndex := i;
2100
2101 EXIT WHEN L_SegOrder_Array(i) =
2102 L_SegNumDummy;
2103 end loop;
2104
2105
2106 if L_SegNumIndex <= L_FlexSeg_Counter then
2107 L_Segment_Array(L_SegNumIndex) :=
2108 P_Container_Item_Segment12;
2109 else
2110 FND_MESSAGE.Set_Name('INV',
2111 'INV_FLEX_SEG_OUT_OF_RANGE');
2112 Manage_Error_Code('IN', 'APP-43014',
2113 Curr_Error);
2114 RAISE Error;
2115
2116 end if;
2117 end if;
2118
2119 if P_Container_Item_Segment13 IS NOT NULL THEN
2120 L_TempSeg_Counter := L_TempSeg_Counter + 1;
2121
2122 BEGIN
2123 select segment_num
2124 into L_SegNumDummy
2125 from fnd_id_flex_segments
2126 where application_id = 401
2127 and id_flex_code = 'MSTK'
2128 and enabled_flag = 'Y'
2129 and application_column_name = 'SEGMENT13';
2130
2131 EXCEPTION
2132 WHEN NO_DATA_FOUND THEN
2133 FND_MESSAGE.Set_Name('INV',
2134 'INV_INVALID_CONTAINER_SEG');
2135 FND_MESSAGE.Set_Token('SEGMENT',
2136 'CONTAINER_SEGMENT13',
2137 FALSE);
2138 Manage_Error_Code('IN', 'APP-43014',
2139 Curr_Error);
2140 RAISE Error;
2141
2142 WHEN TOO_MANY_ROWS THEN
2143 FND_MESSAGE.Set_Name('INV',
2144 'INV_MULTIPLE_FLEX_SEG');
2145 Manage_Error_Code('IN', 'APP-43014',
2146 Curr_Error);
2147 RAISE Error;
2148 END;
2149
2150 L_SegNumIndex := 0;
2151
2152 for i in 1..L_FlexSeg_Counter loop
2153
2154 L_SegNumIndex := i;
2155
2156 EXIT WHEN L_SegOrder_Array(i) =
2157 L_SegNumDummy;
2158 end loop;
2159
2160
2161 if L_SegNumIndex <= L_FlexSeg_Counter then
2162 L_Segment_Array(L_SegNumIndex) :=
2163 P_Container_Item_Segment13;
2164 else
2165 FND_MESSAGE.Set_Name('INV',
2166 'INV_FLEX_SEG_OUT_OF_RANGE');
2167 Manage_Error_Code('IN', 'APP-43014',
2168 Curr_Error);
2169 RAISE Error;
2170
2171 end if;
2172 end if;
2173
2174 if P_Container_Item_Segment14 IS NOT NULL THEN
2175 L_TempSeg_Counter := L_TempSeg_Counter + 1;
2176
2177 BEGIN
2178 select segment_num
2179 into L_SegNumDummy
2180 from fnd_id_flex_segments
2181 where application_id = 401
2182 and id_flex_code = 'MSTK'
2183 and enabled_flag = 'Y'
2184 and application_column_name = 'SEGMENT14';
2185
2186 EXCEPTION
2187 WHEN NO_DATA_FOUND THEN
2188 FND_MESSAGE.Set_Name('INV',
2189 'INV_INVALID_CONTAINER_SEG');
2190 FND_MESSAGE.Set_Token('SEGMENT',
2191 'CONTAINER_SEGMENT14',
2192 FALSE);
2193 Manage_Error_Code('IN', 'APP-43014',
2194 Curr_Error);
2195 RAISE Error;
2196
2197 WHEN TOO_MANY_ROWS THEN
2198 FND_MESSAGE.Set_Name('INV',
2199 'INV_MULTIPLE_FLEX_SEG');
2200 Manage_Error_Code('IN', 'APP-43014',
2201 Curr_Error);
2202 RAISE Error;
2203 END;
2204
2205 L_SegNumIndex := 0;
2206
2207 for i in 1..L_FlexSeg_Counter loop
2208
2209 L_SegNumIndex := i;
2210
2211 EXIT WHEN L_SegOrder_Array(i) =
2212 L_SegNumDummy;
2213 end loop;
2214
2215
2216 if L_SegNumIndex <= L_FlexSeg_Counter then
2217 L_Segment_Array(L_SegNumIndex) :=
2218 P_Container_Item_Segment14;
2219 else
2220 FND_MESSAGE.Set_Name('INV',
2221 'INV_FLEX_SEG_OUT_OF_RANGE');
2222 Manage_Error_Code('IN', 'APP-43014',
2223 Curr_Error);
2224 RAISE Error;
2225
2226 end if;
2227 end if;
2228
2229 if P_Container_Item_Segment15 IS NOT NULL THEN
2230 L_TempSeg_Counter := L_TempSeg_Counter + 1;
2231
2232 BEGIN
2233 select segment_num
2234 into L_SegNumDummy
2235 from fnd_id_flex_segments
2236 where application_id = 401
2237 and id_flex_code = 'MSTK'
2238 and enabled_flag = 'Y'
2239 and application_column_name = 'SEGMENT15';
2240
2241 EXCEPTION
2242 WHEN NO_DATA_FOUND THEN
2243 FND_MESSAGE.Set_Name('INV',
2244 'INV_INVALID_CONTAINER_SEG');
2245 FND_MESSAGE.Set_Token('SEGMENT',
2246 'CONTAINER_SEGMENT15',
2247 FALSE);
2248 Manage_Error_Code('IN', 'APP-43014',
2249 Curr_Error);
2250 RAISE Error;
2251
2252 WHEN TOO_MANY_ROWS THEN
2253 FND_MESSAGE.Set_Name('INV',
2254 'INV_MULTIPLE_FLEX_SEG');
2255 Manage_Error_Code('IN', 'APP-43014',
2256 Curr_Error);
2257 RAISE Error;
2258 END;
2259
2260 L_SegNumIndex := 0;
2261
2262 for i in 1..L_FlexSeg_Counter loop
2263
2264 L_SegNumIndex := i;
2265
2266 EXIT WHEN L_SegOrder_Array(i) =
2267 L_SegNumDummy;
2268 end loop;
2269
2270
2271 if L_SegNumIndex <= L_FlexSeg_Counter then
2272 L_Segment_Array(L_SegNumIndex) :=
2273 P_Container_Item_Segment15;
2274 else
2275 FND_MESSAGE.Set_Name('INV',
2276 'INV_FLEX_SEG_OUT_OF_RANGE');
2277 Manage_Error_Code('IN', 'APP-43014',
2278 Curr_Error);
2279 RAISE Error;
2280
2281 end if;
2282 end if;
2283
2284 if P_Container_Item_Segment16 IS NOT NULL THEN
2285 L_TempSeg_Counter := L_TempSeg_Counter + 1;
2286
2287 BEGIN
2288 select segment_num
2289 into L_SegNumDummy
2290 from fnd_id_flex_segments
2291 where application_id = 401
2292 and id_flex_code = 'MSTK'
2293 and enabled_flag = 'Y'
2294 and application_column_name = 'SEGMENT16';
2295
2296 EXCEPTION
2297 WHEN NO_DATA_FOUND THEN
2298 FND_MESSAGE.Set_Name('INV',
2299 'INV_INVALID_CONTAINER_SEG');
2300 FND_MESSAGE.Set_Token('SEGMENT',
2301 'CONTAINER_SEGMENT16',
2302 FALSE);
2303 Manage_Error_Code('IN', 'APP-43014',
2304 Curr_Error);
2305 RAISE Error;
2306
2307 WHEN TOO_MANY_ROWS THEN
2308 FND_MESSAGE.Set_Name('INV',
2309 'INV_MULTIPLE_FLEX_SEG');
2310 Manage_Error_Code('IN', 'APP-43014',
2311 Curr_Error);
2312 RAISE Error;
2313 END;
2314
2315 L_SegNumIndex := 0;
2316
2317 for i in 1..L_FlexSeg_Counter loop
2318
2319 L_SegNumIndex := i;
2320
2321 EXIT WHEN L_SegOrder_Array(i) =
2322 L_SegNumDummy;
2323 end loop;
2324
2325
2326 if L_SegNumIndex <= L_FlexSeg_Counter then
2327 L_Segment_Array(L_SegNumIndex) :=
2328 P_Container_Item_Segment16;
2329 else
2330 FND_MESSAGE.Set_Name('INV',
2331 'INV_FLEX_SEG_OUT_OF_RANGE');
2332 Manage_Error_Code('IN', 'APP-43014',
2333 Curr_Error);
2334 RAISE Error;
2335
2336 end if;
2337 end if;
2338
2339 if P_Container_Item_Segment17 IS NOT NULL THEN
2340 L_TempSeg_Counter := L_TempSeg_Counter + 1;
2341
2342 BEGIN
2343 select segment_num
2344 into L_SegNumDummy
2345 from fnd_id_flex_segments
2346 where application_id = 401
2347 and id_flex_code = 'MSTK'
2348 and enabled_flag = 'Y'
2349 and application_column_name = 'SEGMENT17';
2350
2351 EXCEPTION
2352 WHEN NO_DATA_FOUND THEN
2353 FND_MESSAGE.Set_Name('INV',
2354 'INV_INVALID_CONTAINER_SEG');
2355 FND_MESSAGE.Set_Token('SEGMENT',
2356 'CONTAINER_SEGMENT17',
2357 FALSE);
2358 Manage_Error_Code('IN', 'APP-43014',
2359 Curr_Error);
2360 RAISE Error;
2361
2362 WHEN TOO_MANY_ROWS THEN
2363 FND_MESSAGE.Set_Name('INV',
2364 'INV_MULTIPLE_FLEX_SEG');
2365 Manage_Error_Code('IN', 'APP-43014',
2366 Curr_Error);
2367 RAISE Error;
2368 END;
2369
2370 L_SegNumIndex := 0;
2371
2372 for i in 1..L_FlexSeg_Counter loop
2373
2374 L_SegNumIndex := i;
2375
2376 EXIT WHEN L_SegOrder_Array(i) =
2377 L_SegNumDummy;
2378 end loop;
2379
2380
2381 if L_SegNumIndex <= L_FlexSeg_Counter then
2382 L_Segment_Array(L_SegNumIndex) :=
2383 P_Container_Item_Segment17;
2384 else
2385 FND_MESSAGE.Set_Name('INV',
2386 'INV_FLEX_SEG_OUT_OF_RANGE');
2387 Manage_Error_Code('IN', 'APP-43014',
2388 Curr_Error);
2389 RAISE Error;
2390
2391 end if;
2392 end if;
2393
2394 if P_Container_Item_Segment18 IS NOT NULL THEN
2395 L_TempSeg_Counter := L_TempSeg_Counter + 1;
2396
2397 BEGIN
2398 select segment_num
2399 into L_SegNumDummy
2400 from fnd_id_flex_segments
2401 where application_id = 401
2402 and id_flex_code = 'MSTK'
2403 and enabled_flag = 'Y'
2404 and application_column_name = 'SEGMENT18';
2405
2406 EXCEPTION
2407 WHEN NO_DATA_FOUND THEN
2408 FND_MESSAGE.Set_Name('INV',
2409 'INV_INVALID_CONTAINER_SEG');
2410 FND_MESSAGE.Set_Token('SEGMENT',
2411 'CONTAINER_SEGMENT18',
2412 FALSE);
2413 Manage_Error_Code('IN', 'APP-43014',
2414 Curr_Error);
2415 RAISE Error;
2416
2417 WHEN TOO_MANY_ROWS THEN
2418 FND_MESSAGE.Set_Name('INV',
2419 'INV_MULTIPLE_FLEX_SEG');
2420 Manage_Error_Code('IN', 'APP-43014',
2421 Curr_Error);
2422 RAISE Error;
2423 END;
2424
2425 L_SegNumIndex := 0;
2426
2427 for i in 1..L_FlexSeg_Counter loop
2428
2429 L_SegNumIndex := i;
2430
2431 EXIT WHEN L_SegOrder_Array(i) =
2432 L_SegNumDummy;
2433 end loop;
2434
2435
2436 if L_SegNumIndex <= L_FlexSeg_Counter then
2437 L_Segment_Array(L_SegNumIndex) :=
2438 P_Container_Item_Segment18;
2439 else
2440 FND_MESSAGE.Set_Name('INV',
2441 'INV_FLEX_SEG_OUT_OF_RANGE');
2442 Manage_Error_Code('IN', 'APP-43014',
2443 Curr_Error);
2444 RAISE Error;
2445
2446 end if;
2447 end if;
2448
2449 if P_Container_Item_Segment19 IS NOT NULL THEN
2450 L_TempSeg_Counter := L_TempSeg_Counter + 1;
2451
2452 BEGIN
2453 select segment_num
2454 into L_SegNumDummy
2455 from fnd_id_flex_segments
2456 where application_id = 401
2457 and id_flex_code = 'MSTK'
2458 and enabled_flag = 'Y'
2459 and application_column_name = 'SEGMENT19';
2460
2461 EXCEPTION
2462 WHEN NO_DATA_FOUND THEN
2463 FND_MESSAGE.Set_Name('INV',
2464 'INV_INVALID_CONTAINER_SEG');
2465 FND_MESSAGE.Set_Token('SEGMENT',
2466 'CONTAINER_SEGMENT19',
2467 FALSE);
2468 Manage_Error_Code('IN', 'APP-43014',
2469 Curr_Error);
2470 RAISE Error;
2471
2472 WHEN TOO_MANY_ROWS THEN
2473 FND_MESSAGE.Set_Name('INV',
2474 'INV_MULTIPLE_FLEX_SEG');
2475 Manage_Error_Code('IN', 'APP-43014',
2476 Curr_Error);
2477 RAISE Error;
2478 END;
2479
2480 L_SegNumIndex := 0;
2481
2482 for i in 1..L_FlexSeg_Counter loop
2483
2484 L_SegNumIndex := i;
2485
2486 EXIT WHEN L_SegOrder_Array(i) =
2487 L_SegNumDummy;
2488 end loop;
2489
2490
2491 if L_SegNumIndex <= L_FlexSeg_Counter then
2492 L_Segment_Array(L_SegNumIndex) :=
2493 P_Container_Item_Segment19;
2494 else
2495 FND_MESSAGE.Set_Name('INV',
2496 'INV_FLEX_SEG_OUT_OF_RANGE');
2497 Manage_Error_Code('IN', 'APP-43014',
2498 Curr_Error);
2499 RAISE Error;
2500
2501 end if;
2502 end if;
2503
2504 if P_Container_Item_Segment20 IS NOT NULL THEN
2505 L_TempSeg_Counter := L_TempSeg_Counter + 1;
2506
2507 BEGIN
2508 select segment_num
2509 into L_SegNumDummy
2510 from fnd_id_flex_segments
2511 where application_id = 401
2512 and id_flex_code = 'MSTK'
2513 and enabled_flag = 'Y'
2514 and application_column_name = 'SEGMENT20';
2515
2516 EXCEPTION
2517 WHEN NO_DATA_FOUND THEN
2518 FND_MESSAGE.Set_Name('INV',
2519 'INV_INVALID_CONTAINER_SEG');
2520 FND_MESSAGE.Set_Token('SEGMENT',
2521 'CONTAINER_SEGMENT20',
2522 FALSE);
2523 Manage_Error_Code('IN', 'APP-43014',
2524 Curr_Error);
2525 RAISE Error;
2526
2527 WHEN TOO_MANY_ROWS THEN
2528 FND_MESSAGE.Set_Name('INV',
2529 'INV_MULTIPLE_FLEX_SEG');
2530 Manage_Error_Code('IN', 'APP-43014',
2531 Curr_Error);
2532 RAISE Error;
2533 END;
2534
2535 L_SegNumIndex := 0;
2536
2537 for i in 1..L_FlexSeg_Counter loop
2538
2539 L_SegNumIndex := i;
2540
2541 EXIT WHEN L_SegOrder_Array(i) =
2542 L_SegNumDummy;
2543 end loop;
2544
2545
2546 if L_SegNumIndex <= L_FlexSeg_Counter then
2547 L_Segment_Array(L_SegNumIndex) :=
2548 P_Container_Item_Segment20;
2549 else
2550 FND_MESSAGE.Set_Name('INV',
2551 'INV_FLEX_SEG_OUT_OF_RANGE');
2552 Manage_Error_Code('IN', 'APP-43014',
2553 Curr_Error);
2554 RAISE Error;
2555
2556 end if;
2557 end if;
2558
2559 L_Delimiter := fnd_flex_ext.get_delimiter('INV',
2560 'MSTK',
2561 L_StructNum);
2562
2563 if (L_TempSeg_Counter > 0) then
2564
2565 L_ConcatSegs := fnd_flex_ext.concatenate_segments(
2566 L_FlexSeg_Counter,
2567 L_Segment_Array,
2568 L_Delimiter);
2569 else
2570 FND_MESSAGE.Set_Name('INV',
2571 'INV_FLEX_SEG_MISMATCH');
2572 Manage_Error_Code('IN', 'APP-43014',
2573 Curr_Error);
2574 RAISE Error;
2575 end if;
2576
2577 if L_ConcatSegs is not null then
2578
2579 L_Success := FND_FLEX_KEYVAL.Validate_Segs(
2580 OPERATION => 'FIND_COMBINATION',
2581 APPL_SHORT_NAME => 'INV',
2582 KEY_FLEX_CODE => 'MSTK',
2583 STRUCTURE_NUMBER=> L_StructNum,
2584 CONCAT_SEGMENTS => L_ConcatSegs,
2585 DATA_SET => P_Container_Organization_Id,
2586 WHERE_CLAUSE =>
2587 'UPPER(MTL_SYSTEM_ITEMS.Container_Item_Flag) = ''Y'''
2588 );
2589
2590 if L_Success then
2591 P_Container_Item_Id :=
2592 FND_FLEX_KEYVAL.Combination_Id;
2593
2594 else
2595 P_Container_Item_Id := NULL;
2596 end if;
2597
2598 If P_Container_Item_Id is NULL then
2599
2600 FND_MESSAGE.Set_Name('INV',
2601 'INV_INVALID_CONTAINER');
2602 FND_MESSAGE.Set_Token('COLUMN',
2603 'CONTAINER_ITEM', FALSE);
2604 Manage_Error_Code('IN', 'APP-43014',
2605 Curr_Error);
2606 RAISE Error;
2607 END IF;
2608
2609 else
2610 FND_MESSAGE.Set_Name('INV',
2611 'INV_CONCAT_SEG_ERROR');
2612 Manage_Error_Code('IN', 'APP-43014',
2613 Curr_Error);
2614 RAISE Error;
2615 end if;
2616
2617 ELSE
2618 Manage_Error_Code('IN', 'APP-00C03', Curr_Error);
2619 RAISE Error;
2620
2621 END IF;
2622
2623 EXCEPTION
2624
2625 WHEN NO_DATA_FOUND THEN
2626
2627 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_CONTAINER');
2628 IF (P_Container_Item_Id IS NOT NULL) THEN
2629 FND_MESSAGE.Set_Token('COLUMN',
2630 'CONTAINER_ITEM_ID', FALSE);
2631 ELSIF ((P_Container_Item_Id IS NULL) AND
2632 (P_Container_Item IS NOT NULL)) THEN
2633 FND_MESSAGE.Set_Token('COLUMN',
2634 'CONTAINER_ITEM', FALSE);
2635 ELSE
2636 FND_MESSAGE.Set_Token('COLUMN',
2637 'CONTAINER_ITEM_SEGMENT1 - 20', FALSE);
2638 END IF;
2639 Manage_Error_Code('IN', 'APP-43014', Curr_Error);
2640 RAISE Error;
2641
2642 WHEN TOO_MANY_ROWS THEN
2643
2644 FND_MESSAGE.Set_Name('INV', 'INV_MULTIPLE_CONTAINERS');
2645 Manage_Error_Code('IN', 'APP-43055', Curr_Error);
2646 RAISE Error;
2647
2648 END Validate_Containers;
2649
2650
2651 PROCEDURE Validate_Commodity_Code(
2652 P_Commodity_Code_Id IN OUT NOCOPY Number,
2653 P_Commodity_Code IN Varchar2 DEFAULT NULL
2654 ) IS
2655
2656 Temp_Commodity_Code_Id Number := NULL;
2657 Temp_Inactive_Date Date := NULL;
2658
2659 BEGIN
2660
2661 IF (P_Commodity_Code_Id IS NOT NULL) THEN
2662
2663 SELECT Commodity_Code_Id, Inactive_Date
2664 INTO Temp_Commodity_Code_Id, Temp_Inactive_Date
2665 FROM MTL_COMMODITY_CODES MCC
2666 WHERE MCC.Commodity_Code_Id = P_Commodity_Code_Id;
2667
2668 IF ((SQL%FOUND) AND ((Temp_Inactive_Date IS NOT NULL)
2669 AND (Temp_Inactive_Date <= SYSDATE))) THEN
2670
2671 FND_MESSAGE.Set_Name('INV',
2672 'INV_INACTIVE_COMMODITY_CODE');
2673 FND_MESSAGE.Set_Token('COLUMN',
2674 'COMMODITY_CODE_ID', FALSE);
2675 Manage_Error_Code('IN', 'APP-43015', Curr_Error);
2676 RAISE Error;
2677
2678 ELSE
2679
2680 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
2681 P_Commodity_Code_Id := Temp_Commodity_Code_Id;
2682 RETURN;
2683
2684 END IF;
2685
2686 ELSIF ((P_Commodity_Code_Id IS NULL) AND
2687 (P_Commodity_Code IS NOT NULL)) THEN
2688
2689 SELECT Commodity_Code_Id, Inactive_Date
2690 INTO Temp_Commodity_Code_Id, Temp_Inactive_Date
2691 FROM MTL_COMMODITY_CODES MCC
2692 WHERE MCC.Commodity_Code = P_Commodity_Code;
2693
2694 IF ((SQL%FOUND) AND
2695 ((Temp_Inactive_Date IS NOT NULL) AND
2696 (Temp_Inactive_Date <= SYSDATE))) THEN
2697
2698 FND_MESSAGE.Set_Name('INV',
2699 'INV_INACTIVE_COMMODITY_CODE');
2700 FND_MESSAGE.Set_Token('COLUMN',
2701 'COMMODITY_CODE', FALSE);
2702 Manage_Error_Code('IN', 'APP-43015', Curr_Error);
2703 RAISE Error;
2704
2705 ELSE
2706 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
2707 P_Commodity_Code_Id := Temp_Commodity_Code_Id;
2708 RETURN;
2709
2710 END IF;
2711
2712 ELSE
2713 FND_MESSAGE.Set_Name('INV', 'INV_NO_COMMODITY_CODE');
2714 Manage_Error_Code('IN', 'APP-43016', Curr_Error);
2715 RAISE Error;
2716 END IF;
2717
2718 EXCEPTION
2719
2720 WHEN NO_DATA_FOUND THEN
2721
2722 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_COMMODITY_CODE');
2723 IF (P_Commodity_Code_Id IS NOT NULL) THEN
2724 FND_MESSAGE.Set_Token('COLUMN',
2725 'COMMODITY_CODE_ID', FALSE);
2726 ELSE
2727 FND_MESSAGE.Set_Token('COLUMN',
2728 'COMMODITY_CODE', FALSE);
2729 END IF;
2730 Manage_Error_Code('IN', 'APP-43017', Curr_Error);
2731 RAISE Error;
2732
2733 WHEN TOO_MANY_ROWS THEN
2734
2735 FND_MESSAGE.Set_Name('INV', 'INV_MULTIPLE_COMMODITY_CODES');
2736 Manage_Error_Code('IN', 'APP-43018', Curr_Error);
2737 RAISE Error;
2738
2739 END Validate_Commodity_Code;
2740
2741
2742 PROCEDURE Validate_Model(
2743 P_Model_Customer_Item_Id IN OUT NOCOPY Number,
2744 P_Model_Customer_Item IN Varchar2 DEFAULT NULL,
2745 P_Customer_Id IN Number DEFAULT NULL,
2746 P_Address_Id IN Number DEFAULT NULL,
2747 P_Customer_Category_Code IN Varchar2 DEFAULT NULL,
2748 P_Item_Definition_Level IN Varchar2 DEFAULT NULL,
2749 P_Customer_Item_Number IN Varchar2 DEFAULT NULL
2750 ) IS
2751
2752 Temp_Model_Customer_Item_Id Number := NULL;
2753 Temp_Item_Definition_Level Varchar(1) := NULL;
2754 Temp_Inactive_Flag Varchar(1) := NULL;
2755
2756 BEGIN
2757
2758 IF ((P_Customer_Id IS NULL) AND
2759 ((P_Address_Id IS NULL) OR
2760 (P_Customer_Category_Code IS NULL)) OR
2761 (P_Item_Definition_Level IS NULL) OR
2762 (P_Customer_Item_Number IS NULL)) THEN
2763
2764 FND_MESSAGE.Set_Name('INV', 'INV_NO_MODEL_CI_INFORMATION');
2765 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
2766 RETURN;
2767 ELSE
2768
2769 IF (P_Model_Customer_Item_Id IS NOT NULL) THEN
2770 /* Bug 3849821 Select Customer_Item_Id of Model Ct Item entered in the Interface table */
2771
2772 SELECT Customer_Item_Id,
2773 Item_Definition_Level, Inactive_Flag
2774 INTO Temp_Model_Customer_Item_Id,
2775 Temp_Item_Definition_Level, Temp_Inactive_Flag
2776 FROM MTL_CUSTOMER_ITEMS MCI
2777 WHERE MCI.Customer_Item_Id =
2778 P_Model_Customer_Item_Id
2779 AND MCI.Customer_Id =
2780 P_Customer_Id;
2781
2782 IF ((SQL%FOUND) AND (Temp_Inactive_Flag = 'Y')) THEN
2783
2784 FND_MESSAGE.Set_Name('INV',
2785 'INV_INACTIVE_MODEL_CI');
2786 FND_MESSAGE.Set_Token('COLUMN',
2787 'MODEL_CUSTOMER_ITEM_ID',
2788 FALSE);
2789 Manage_Error_Code('IN', 'APP-43019',
2790 Curr_Error);
2791 RAISE Error;
2792
2793 ELSIF ((SQL%FOUND) AND (Temp_Inactive_Flag = 'N') AND
2794 (Temp_Item_Definition_Level <=
2795 P_Item_Definition_Level)) THEN
2796
2797 Manage_Error_Code('IN', 'APP-00000',
2798 Curr_Error);
2799 P_Model_Customer_Item_Id :=
2800 Temp_Model_Customer_Item_Id;
2801 RETURN;
2802
2803 ELSE
2804 FND_MESSAGE.Set_Name('INV',
2805 'INV_INV_MODEL_DEF_LVL');
2806 FND_MESSAGE.Set_Token('COLUMN',
2807 'MODEL_CUSTOMER_ITEM_ID',
2808 FALSE);
2809 Manage_Error_Code('IN', 'APP-43050',
2810 Curr_Error);
2811 RAISE Error;
2812 END IF;
2813
2814 ELSIF ((P_Model_Customer_Item_Id IS NULL) AND
2815 (P_Model_Customer_Item IS NOT NULL)) THEN
2816 --Bug:3849821
2817 SELECT Customer_Item_Id,
2818 Item_Definition_Level, Inactive_Flag
2819 INTO Temp_Model_Customer_Item_Id,
2820 Temp_Item_Definition_Level, Temp_Inactive_Flag
2821 FROM MTL_CUSTOMER_ITEMS MCI
2822 WHERE MCI.Customer_Item_Number =
2823 P_Model_Customer_Item
2824 AND MCI.Customer_Id = P_Customer_Id
2825 AND MCI.Customer_Category_Code =
2826 P_Customer_Category_Code
2827 OR MCI.Address_Id = P_Address_Id;
2828
2829 IF ((SQL%FOUND) AND (Temp_Inactive_Flag = 'Y')) THEN
2830
2831 FND_MESSAGE.Set_Name('INV',
2832 'INV_INACTIVE_MODEL_CI');
2833 FND_MESSAGE.Set_Token('COLUMN',
2834 'MODEL_CUSTOMER_ITEM', FALSE);
2835 Manage_Error_Code('IN', 'APP-43019',
2836 Curr_Error);
2837 RAISE Error;
2838
2839 ELSIF ((SQL%FOUND) AND (Temp_Inactive_Flag = 'N') AND
2840 (Temp_Item_Definition_Level <=
2841 P_Item_Definition_Level)) THEN
2842
2843 Manage_Error_Code('IN', 'APP-00000',
2844 Curr_Error);
2845 P_Model_Customer_Item_Id :=
2846 Temp_Model_Customer_Item_Id;
2847 RETURN;
2848 ELSE
2849
2850 FND_MESSAGE.Set_Name('INV',
2851 'INV_INV_MODEL_DEF_LVL');
2852 FND_MESSAGE.Set_Token('COLUMN',
2853 'MODEL_CUSTOMER_ITEM', FALSE);
2854 Manage_Error_Code('IN', 'APP-43050',
2855 Curr_Error);
2856 RAISE Error;
2857
2858 END IF;
2859
2860 ELSE
2861
2862 FND_MESSAGE.Set_Name('INV',
2863 'INV_NO_MODEL_CI_INFORMATION');
2864 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
2865 RETURN;
2866
2867 END IF;
2868
2869 END IF;
2870
2871 EXCEPTION
2872
2873 WHEN NO_DATA_FOUND THEN
2874
2875 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_MODEL_CI');
2876 IF (P_Model_Customer_Item_Id IS NULL) THEN
2877 FND_MESSAGE.Set_Token('COLUMN',
2878 'MODEL_CUSTOMER_ITEM_ID', FALSE);
2879 ELSE
2880 FND_MESSAGE.Set_Token('COLUMN',
2881 'MODEL_CUSTOMER_ITEM', FALSE);
2882 END IF;
2883 Manage_Error_Code('IN', 'APP-43020', Curr_Error);
2884 RAISE Error;
2885
2886 WHEN TOO_MANY_ROWS THEN
2887
2888 FND_MESSAGE.Set_Name('INV', 'INV_MULTIPLE_MODELS');
2889 Manage_Error_Code('IN', 'APP-43021', Curr_Error);
2890 RAISE Error;
2891
2892 END Validate_Model;
2893
2894
2895 PROCEDURE Validate_Demand_Tolerance ( P_Demand_Tolerance IN Number DEFAULT NULL ) IS
2896
2897 BEGIN
2898
2899 IF ((P_Demand_Tolerance >= 999.99) OR (P_Demand_Tolerance <= 0)) THEN
2900
2901 FND_MESSAGE.Set_Name('INV', 'INV_TOLERANCE_OUT_RANGE');
2902 Manage_Error_Code('IN', 'APP-43022', Curr_Error);
2903 RAISE Error;
2904
2905 ELSE
2906
2907 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
2908 RETURN;
2909
2910 END IF;
2911
2912 END Validate_Demand_Tolerance;
2913
2914
2915 PROCEDURE Validate_Fill_Percentage ( P_Min_Fill_Percentage IN Number DEFAULT NULL ) IS
2916
2917 BEGIN
2918
2919 IF ((P_Min_Fill_Percentage > 100) OR (P_Min_Fill_Percentage < 0)) THEN
2920
2921 FND_MESSAGE.Set_Name('INV', 'INV_FILL_PERCENT_OUT_RANGE');
2922 Manage_Error_Code('IN', 'APP-43023', Curr_Error);
2923 RAISE Error;
2924
2925 ELSE
2926
2927 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
2928 RETURN;
2929
2930 END IF;
2931
2932 END Validate_Fill_Percentage;
2933
2934
2935 PROCEDURE Validate_Departure_Plan_Flags (
2936 P_Dep_Plan_Required_Flag IN OUT NOCOPY Varchar2,
2937 P_Dep_Plan_Prior_Bld_Flag IN OUT NOCOPY Varchar2
2938 ) IS
2939
2940 BEGIN
2941
2942 IF (P_Dep_Plan_Required_Flag IS NULL) THEN
2943 P_Dep_Plan_Required_Flag:= '2';
2944 END IF;
2945
2946 IF (P_Dep_Plan_Prior_Bld_Flag IS NULL) THEN
2947 P_Dep_Plan_Prior_Bld_Flag:= '2';
2948 END IF;
2949
2950 IF NOT (((P_Dep_Plan_Required_Flag = '1') OR
2951 (P_Dep_Plan_Required_Flag = '2') OR
2952 (P_Dep_Plan_Required_Flag IS NULL)) AND
2953 ((P_Dep_Plan_Prior_Bld_Flag = '1') OR
2954 (P_Dep_Plan_Prior_Bld_Flag = '2') OR
2955 (P_Dep_Plan_Prior_Bld_Flag IS NULL))) THEN
2956
2957 FND_MESSAGE.Set_Name('INV', 'INV_INV_DEP_PLAN_FLAG');
2958 FND_MESSAGE.Set_Token('RULE',
2959 'Departure Planning Flags <> 1, 2, or NULL', FALSE);
2960 Manage_Error_Code('IN', 'APP-43024', Curr_Error);
2961 RAISE Error;
2962
2963 ELSIF ((P_Dep_Plan_Required_Flag = '2') AND
2964 (P_Dep_Plan_Prior_Bld_Flag = '1')) THEN
2965
2966 FND_MESSAGE.Set_Name('INV', 'INV_INV_DEP_PLAN_FLAG');
2967 FND_MESSAGE.Set_Token('RULE',
2968 'Departure Planning Prior Build = 1, Departure Planning Required = 2', FALSE);
2969 Manage_Error_Code('IN', 'APP-43024', Curr_Error);
2970 RAISE Error;
2971
2972 ELSIF ((P_Dep_Plan_Required_Flag IS NULL) AND
2973 (P_Dep_Plan_Prior_Bld_Flag = '1')) THEN
2974
2975 FND_MESSAGE.Set_Name('INV', 'INV_INV_DEP_PLAN_FLAG');
2976 FND_MESSAGE.Set_Token('RULE',
2977 'Departure Planning Prior Build = 1, Departure Planning Required = NULL', FALSE);
2978 Manage_Error_Code('IN', 'APP-43024', Curr_Error);
2979 RAISE Error;
2980
2981 ELSIF ((P_Dep_Plan_Required_Flag = '1') AND
2982 (P_Dep_Plan_Prior_Bld_Flag IS NULL)) THEN
2983
2984 FND_MESSAGE.Set_Name('INV', 'INV_INV_DEP_PLAN_FLAG');
2985 FND_MESSAGE.Set_Token('RULE',
2986 'Departure Planning Prior Build = NULL, Departure Planning Required = 1', FALSE);
2987 Manage_Error_Code('IN', 'APP-43024', Curr_Error);
2988 RAISE Error;
2989
2990 ELSIF ((P_Dep_Plan_Required_Flag = '2') AND
2991 (P_Dep_Plan_Prior_Bld_Flag IS NULL)) THEN
2992
2993 FND_MESSAGE.Set_Name('INV', 'INV_INV_DEP_PLAN_FLAG');
2994 FND_MESSAGE.Set_Token('RULE',
2995 'Departure Planning Prior Build = NULL, Departure Planning Required = 2', FALSE);
2996 Manage_Error_Code('IN', 'APP-43024', Curr_Error);
2997 RAISE Error;
2998
2999 ELSIF ((P_Dep_Plan_Required_Flag IS NULL) AND
3000 (P_Dep_Plan_Prior_Bld_Flag = '2')) THEN
3001
3002 FND_MESSAGE.Set_Name('INV', 'INV_INV_DEP_PLAN_FLAG');
3003 FND_MESSAGE.Set_Token('RULE',
3004 'Departure Planning Prior Build = 2, Departure Planning Required = NULL', FALSE);
3005 Manage_Error_Code('IN', 'APP-43024', Curr_Error);
3006 RAISE Error;
3007
3008 ELSIF ((P_Dep_Plan_Required_Flag IS NULL) AND
3009 (P_Dep_Plan_Prior_Bld_Flag IS NULL)) THEN
3010
3011 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
3012 RETURN;
3013
3014 ELSE
3015
3016 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
3017
3018 IF (P_Dep_Plan_Prior_Bld_Flag = '1') THEN
3019
3020 P_Dep_Plan_Prior_Bld_Flag := 'Y';
3021
3022 ELSE
3023
3024 P_Dep_Plan_Prior_Bld_Flag := 'N';
3025
3026 END IF;
3027
3028 IF (P_Dep_Plan_Required_Flag = '1') THEN
3029
3030 P_Dep_Plan_Required_Flag := 'Y';
3031
3032 ELSE
3033
3034 P_Dep_Plan_Required_Flag := 'N';
3035 END IF;
3036
3037 END IF;
3038
3039 END Validate_Departure_Plan_Flags;
3040
3041
3042 /*===========================================================================+
3043 +===========================================================================*/
3044 /* These procedures are specific to the Customer Item XRefs Open Interface. */
3045 /*===========================================================================+
3046 +===========================================================================*/
3047
3048 PROCEDURE Load_Cust_Item_Xrefs(ERRBUF OUT NOCOPY VARCHAR2,
3049 RETCODE OUT NOCOPY VARCHAR2,
3050 ARGUMENT1 IN VARCHAR2,
3051 ARGUMENT2 IN VARCHAR2) IS
3052
3053 L_Retcode Number;
3054 CONC_STATUS BOOLEAN;
3055
3056 --3537282 : Gather stats before running
3057 l_schema VARCHAR2(30);
3058 l_status VARCHAR2(1);
3059 l_industry VARCHAR2(1);
3060 l_records NUMBER(10);
3061
3062 BEGIN
3063
3064 --Start 3537282 : Gather stats before running
3065 IF fnd_global.conc_program_id <> -1 THEN
3066 SELECT count(*) INTO l_records
3067 FROM mtl_ci_xrefs_interface
3068 WHERE process_flag = 1;
3069
3070 -- Bug 6983407 Collect statistics only if the no. of records is bigger than the profile
3071 -- option threshold
3072 IF l_records > nvl(fnd_profile.value('EGO_GATHER_STATS'),100) AND FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema) THEN
3073 IF l_schema IS NOT NULL THEN
3074 FND_STATS.GATHER_TABLE_STATS(l_schema, 'MTL_CI_XREFS_INTERFACE');
3075 END IF;
3076 END IF;
3077 END IF;
3078 --End 3537282 : Gather stats before running
3079
3080
3081 L_Retcode := Load_Cust_Item_Xrefs_Iface(argument1,
3082 argument2);
3083
3084 if L_Retcode = 1 then
3085 RETCODE := 'Success';
3086 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
3087
3088
3089 elsif L_Retcode = 3 then
3090 RETCODE := 'Warning';
3091 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',Current_Error_Code);
3092
3093 else
3094 RETCODE := 'Error';
3095 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
3096
3097 end if;
3098
3099 END Load_Cust_Item_Xrefs;
3100
3101
3102 FUNCTION Load_Cust_Item_XRefs_Iface(
3103 Abort_On_Error IN Varchar2 DEFAULT 'No',
3104 Delete_Record IN Varchar2 DEFAULT 'Yes'
3105 ) RETURN Number IS
3106
3107 L_Success Number := 1;
3108
3109 CURSOR CI_XRefs_Cur IS
3110 SELECT Rowid Row_Id,
3111 Process_Mode,
3112 Customer_Name,
3113 Customer_Number,
3114 Customer_Id,
3115 Customer_Category_Code,
3116 Customer_Category,
3117 Address1,
3118 Address2,
3119 Address3,
3120 Address4,
3121 City,
3122 State,
3123 County,
3124 Country,
3125 Postal_Code,
3126 Address_Id,
3127 Customer_Item_Number,
3128 Item_Definition_Level_Desc,
3129 Item_Definition_Level,
3130 Customer_Item_Id,
3131 Master_Organization_Name,
3132 Master_Organization_Code,
3133 Master_Organization_Id,
3134 Inventory_Item_Segment1,
3135 Inventory_Item_Segment2,
3136 Inventory_Item_Segment3,
3137 Inventory_Item_Segment4,
3138 Inventory_Item_Segment5,
3139 Inventory_Item_Segment6,
3140 Inventory_Item_Segment7,
3141 Inventory_Item_Segment8,
3142 Inventory_Item_Segment9,
3143 Inventory_Item_Segment10,
3144 Inventory_Item_Segment11,
3145 Inventory_Item_Segment12,
3146 Inventory_Item_Segment13,
3147 Inventory_Item_Segment14,
3148 Inventory_Item_Segment15,
3149 Inventory_Item_Segment16,
3150 Inventory_Item_Segment17,
3151 Inventory_Item_Segment18,
3152 Inventory_Item_Segment19,
3153 Inventory_Item_Segment20,
3154 Inventory_Item,
3155 Inventory_Item_Id,
3156 Preference_Number,
3157 Inactive_Flag,
3158 Attribute_Category,
3159 Attribute1,
3160 Attribute2,
3161 Attribute3,
3162 Attribute4,
3163 Attribute5,
3164 Attribute6,
3165 Attribute7,
3166 Attribute8,
3167 Attribute9,
3168 Attribute10,
3169 Attribute11,
3170 Attribute12,
3171 Attribute13,
3172 Attribute14,
3173 Attribute15,
3174 Last_Update_Date,
3175 Last_Updated_By,
3176 Creation_Date,
3177 Created_By,
3178 Last_Update_Login,
3179 Request_Id,
3180 Program_Application_Id,
3181 Program_Id,
3182 Program_Update_Date
3183 FROM MTL_CI_XREFS_INTERFACE
3184 WHERE Process_Flag = 1
3185 AND Process_Mode = 1
3186 OR Process_Mode = 3
3187 AND UPPER(Transaction_Type) = 'CREATE'
3188 FOR UPDATE NOWAIT;
3189
3190 Recinfo2 CI_XRefs_Cur%ROWTYPE;
3191
3192 Error_Number Number := NULL;
3193 Error_Message Varchar2(2000) := NULL;
3194 Error_Counter Number := 0;
3195 Curr_Error Varchar2(9) := 'APP-00000';
3196
3197 BEGIN
3198
3199 OPEN CI_XRefs_Cur;
3200
3201 While (UPPER(Abort_On_Error) <> 'Y' or
3202 Error_Counter <= 0) LOOP
3203
3204 FETCH CI_XRefs_Cur INTO Recinfo2;
3205
3206 EXIT WHEN CI_XRefs_Cur%NOTFOUND;
3207
3208 BEGIN
3209
3210 IF (Recinfo2.Process_Mode = 1) THEN
3211
3212 Validate_CI_XRefs(Recinfo2.Row_Id,
3213 Recinfo2.Process_Mode,
3214 Recinfo2.Customer_Name,
3215 Recinfo2.Customer_Number,
3216 Recinfo2.Customer_Id,
3217 Recinfo2.Customer_Category_Code,
3218 Recinfo2.Customer_Category,
3219 Recinfo2.Address1, Recinfo2.Address2,
3220 Recinfo2.Address3, Recinfo2.Address4,
3221 Recinfo2.City, Recinfo2.State,
3222 Recinfo2.County, Recinfo2.Country,
3223 Recinfo2.Postal_Code, Recinfo2.Address_Id,
3224 Recinfo2.Customer_Item_Number,
3225 Recinfo2.Item_Definition_Level_Desc,
3226 Recinfo2.Item_Definition_Level,
3227 Recinfo2.Customer_Item_Id,
3228 Recinfo2.Master_Organization_Name,
3229 Recinfo2.Master_Organization_Code,
3230 Recinfo2.Master_Organization_Id,
3231 Recinfo2.Inventory_Item_Segment1,
3232 Recinfo2.Inventory_Item_Segment2,
3233 Recinfo2.Inventory_Item_Segment3,
3234 Recinfo2.Inventory_Item_Segment4,
3235 Recinfo2.Inventory_Item_Segment5,
3236 Recinfo2.Inventory_Item_Segment6,
3237 Recinfo2.Inventory_Item_Segment7,
3238 Recinfo2.Inventory_Item_Segment8,
3239 Recinfo2.Inventory_Item_Segment9,
3240 Recinfo2.Inventory_Item_Segment10,
3241 Recinfo2.Inventory_Item_Segment11,
3242 Recinfo2.Inventory_Item_Segment12,
3243 Recinfo2.Inventory_Item_Segment13,
3244 Recinfo2.Inventory_Item_Segment14,
3245 Recinfo2.Inventory_Item_Segment15,
3246 Recinfo2.Inventory_Item_Segment16,
3247 Recinfo2.Inventory_Item_Segment17,
3248 Recinfo2.Inventory_Item_Segment18,
3249 Recinfo2.Inventory_Item_Segment19,
3250 Recinfo2.Inventory_Item_Segment20,
3251 Recinfo2.Inventory_Item,
3252 Recinfo2.Inventory_Item_Id,
3253 Recinfo2.Preference_Number,
3254 Recinfo2.Inactive_Flag,
3255 Recinfo2.Attribute_Category,
3256 Recinfo2.Attribute1, Recinfo2.Attribute2,
3257 Recinfo2.Attribute3, Recinfo2.Attribute4,
3258 Recinfo2.Attribute5, Recinfo2.Attribute6,
3259 Recinfo2.Attribute7, Recinfo2.Attribute8,
3260 Recinfo2.Attribute9, Recinfo2.Attribute10,
3261 Recinfo2.Attribute11, Recinfo2.Attribute12,
3262 Recinfo2.Attribute13, Recinfo2.Attribute14,
3263 Recinfo2.Attribute15,
3264 Recinfo2.Last_Update_Date,
3265 Recinfo2.Last_Updated_By,
3266 Recinfo2.Creation_Date,
3267 Recinfo2.Created_By,
3268 Recinfo2.Last_Update_Login,
3269 nvl(Recinfo2.Request_Id, fnd_global.conc_request_id),
3270 nvl(Recinfo2.Program_Application_Id, fnd_global.prog_appl_id),
3271 nvl(Recinfo2.Program_Id, fnd_global.conc_program_id),
3272 nvl(Recinfo2.Program_Update_Date, sysdate),
3273 Delete_Record);
3274 /*
3275 if L_Success = 1 then
3276 COMMIT;
3277 end if;
3278 */
3279
3280 ELSIF (Recinfo2.Process_Mode = 3) THEN
3281
3282 Delete_Row('X', Delete_Record, Recinfo2.Row_Id);
3283
3284 /*
3285 if L_Success = 1 then
3286 COMMIT;
3287 end if;
3288 */
3289
3290 ELSE
3291 NULL;
3292 END IF;
3293
3294 EXCEPTION
3295
3296 WHEN Error THEN
3297
3298 L_Success := 3;
3299
3300 Error_Counter := Error_Counter + 1;
3301 FND_MESSAGE.Set_Token('TABLE',
3302 'MTL_CI_XREFS_INTERFACE', FALSE);
3303 Error_Message := FND_MESSAGE.Get;
3304 Manage_Error_Code('OUT', NULL, Curr_Error);
3305
3306 UPDATE MTL_CI_XREFS_INTERFACE MCIXI
3307 SET MCIXI.Error_Code = Curr_Error,
3308 MCIXI.Error_Explanation = substrb(Error_Message,1,235),
3309 MCIXI.Process_Mode = 2
3310 WHERE MCIXI.Rowid = Recinfo2.Row_Id;
3311 /*
3312 commit;
3313 */
3314 WHEN OTHERS THEN
3315
3316 L_Success := 2;
3317
3318 Error_Counter := Error_Counter + 1;
3319 Error_Number := SQLCODE;
3320 Error_Message := SUBSTRB(SQLERRM, 1, 512);
3321
3322 UPDATE MTL_CI_XREFS_INTERFACE MCIXI
3323 SET MCIXI.Error_Code = TO_CHAR(Error_Number),
3324 MCIXI.Error_Explanation = substrb(Error_Message,1,235),
3325 MCIXI.Process_Mode = 2
3326 WHERE MCIXI.Rowid = Recinfo2.Row_Id;
3327 /*
3328 commit;
3329 */
3330 END;
3331
3332 END LOOP;
3333
3334 CLOSE CI_XRefs_Cur;
3335
3336 IF (Error_Counter > 0) THEN
3337
3338 L_Success := 3;
3339
3340 FND_MESSAGE.Set_Name('INV', 'INV_CI_OPEN_INT_WARNING');
3341 FND_MESSAGE.Set_Token('TABLE',
3342 'MTL_CI_XREFS_INTERFACE', FALSE);
3343 FND_MESSAGE.Set_Token('ERROR_COUNT', Error_Counter, FALSE);
3344 Error_Message := FND_MESSAGE.Get;
3345 --DBMS_OUTPUT.Put_Line(Error_Message);
3346 END IF;
3347
3348 COMMIT;
3349
3350 RETURN L_Success;
3351
3352 EXCEPTION
3353
3354 WHEN Error THEN
3355
3356 L_Success := 3;
3357
3358 Error_Counter := Error_Counter + 1;
3359 FND_MESSAGE.Set_Token('TABLE',
3360 'MTL_CI_XREFS_INTERFACE', FALSE);
3361 Error_Message := FND_MESSAGE.Get;
3362 Manage_Error_Code('OUT', NULL, Curr_Error);
3363
3364 UPDATE MTL_CI_XREFS_INTERFACE MCIXI
3365 SET MCIXI.Error_Code = Curr_Error,
3366 MCIXI.Error_Explanation = substrb(Error_Message,1,235),
3367 MCIXI.Process_Mode = 2
3368 WHERE MCIXI.Rowid = Recinfo2.Row_Id;
3369
3370 commit;
3371
3372 Return L_Success;
3373
3374 WHEN OTHERS THEN
3375
3376 L_Success := 2;
3377
3378 Error_Counter := Error_Counter + 1;
3379 Error_Number := SQLCODE;
3380 Error_Message := SUBSTRB(SQLERRM, 1, 512);
3381
3382
3383 /* Fix for bug 5263099 - Added the below code to handle the scenario
3384 where Cursor CI_XRefs_Cur fails to open because the rows in
3385 MTL_CI_XREFS_INTERFACE are already locked by some other session.
3386 It leads to "ORA-00054-resource busy and acquire with NOWAIT specified" error.
3387 So we check for this error condition SQLCODE= -54.
3388 Manage_Error_Code will set the Current_Error_Code to the corresponding
3389 error msg which shall then be shown in the conc prog log file.
3390 */
3391 If SQLCODE= -54 Then
3392 Manage_Error_Code('IN',substrb(Error_Message,1,235), Curr_Error);
3393 End If;
3394
3395 UPDATE MTL_CI_XREFS_INTERFACE MCIXI
3396 SET MCIXI.Error_Code = TO_CHAR(Error_Number),
3397 MCIXI.Error_Explanation = substrb(Error_Message,1,235),
3398 MCIXI.Process_Mode = 2
3399 WHERE MCIXI.Rowid = Recinfo2.Row_Id;
3400
3401 commit;
3402
3403 Return L_Success;
3404
3405 END Load_Cust_Item_XRefs_Iface;
3406
3407
3408 PROCEDURE Validate_CI_XRefs(
3409 Row_Id IN OUT NOCOPY Varchar2,
3410 Process_Mode IN OUT NOCOPY Varchar2,
3411 Customer_Name IN OUT NOCOPY Varchar2,
3412 Customer_Number IN OUT NOCOPY Varchar2,
3413 Customer_Id IN OUT NOCOPY Number,
3414 Customer_Category_Code IN OUT NOCOPY Varchar2,
3415 Customer_Category IN OUT NOCOPY Varchar2,
3416 Address1 IN OUT NOCOPY Varchar2,
3417 Address2 IN OUT NOCOPY Varchar2,
3418 Address3 IN OUT NOCOPY Varchar2,
3419 Address4 IN OUT NOCOPY Varchar2,
3420 City IN OUT NOCOPY Varchar2,
3421 State IN OUT NOCOPY Varchar2,
3422 County IN OUT NOCOPY Varchar2,
3423 Country IN OUT NOCOPY Varchar2,
3424 Postal_Code IN OUT NOCOPY Varchar2,
3425 Address_Id IN OUT NOCOPY Number,
3426 Customer_Item_Number IN OUT NOCOPY Varchar2,
3427 Item_Definition_Level_Desc IN OUT NOCOPY Varchar2,
3428 Item_Definition_Level IN OUT NOCOPY Varchar2,
3429 Customer_Item_Id IN OUT NOCOPY Number,
3430 Master_Organization_Name IN OUT NOCOPY Varchar2,
3431 Master_Organization_Code IN OUT NOCOPY Varchar2,
3432 Master_Organization_Id IN OUT NOCOPY Number,
3433 Inventory_Item_Segment1 IN OUT NOCOPY Varchar2,
3434 Inventory_Item_Segment2 IN OUT NOCOPY Varchar2,
3435 Inventory_Item_Segment3 IN OUT NOCOPY Varchar2,
3436 Inventory_Item_Segment4 IN OUT NOCOPY Varchar2,
3437 Inventory_Item_Segment5 IN OUT NOCOPY Varchar2,
3438 Inventory_Item_Segment6 IN OUT NOCOPY Varchar2,
3439 Inventory_Item_Segment7 IN OUT NOCOPY Varchar2,
3440 Inventory_Item_Segment8 IN OUT NOCOPY Varchar2,
3441 Inventory_Item_Segment9 IN OUT NOCOPY Varchar2,
3442 Inventory_Item_Segment10 IN OUT NOCOPY Varchar2,
3443 Inventory_Item_Segment11 IN OUT NOCOPY Varchar2,
3444 Inventory_Item_Segment12 IN OUT NOCOPY Varchar2,
3445 Inventory_Item_Segment13 IN OUT NOCOPY Varchar2,
3446 Inventory_Item_Segment14 IN OUT NOCOPY Varchar2,
3447 Inventory_Item_Segment15 IN OUT NOCOPY Varchar2,
3448 Inventory_Item_Segment16 IN OUT NOCOPY Varchar2,
3449 Inventory_Item_Segment17 IN OUT NOCOPY Varchar2,
3450 Inventory_Item_Segment18 IN OUT NOCOPY Varchar2,
3451 Inventory_Item_Segment19 IN OUT NOCOPY Varchar2,
3452 Inventory_Item_Segment20 IN OUT NOCOPY Varchar2,
3453 Inventory_Item IN OUT NOCOPY Varchar2,
3454 Inventory_Item_Id IN OUT NOCOPY Number,
3455 Preference_Number IN OUT NOCOPY Number,
3456 Inactive_Flag IN OUT NOCOPY Varchar2,
3457 Attribute_Category IN OUT NOCOPY Varchar2,
3458 Attribute1 IN OUT NOCOPY Varchar2,
3459 Attribute2 IN OUT NOCOPY Varchar2,
3460 Attribute3 IN OUT NOCOPY Varchar2,
3461 Attribute4 IN OUT NOCOPY Varchar2,
3462 Attribute5 IN OUT NOCOPY Varchar2,
3463 Attribute6 IN OUT NOCOPY Varchar2,
3464 Attribute7 IN OUT NOCOPY Varchar2,
3465 Attribute8 IN OUT NOCOPY Varchar2,
3466 Attribute9 IN OUT NOCOPY Varchar2,
3467 Attribute10 IN OUT NOCOPY Varchar2,
3468 Attribute11 IN OUT NOCOPY Varchar2,
3469 Attribute12 IN OUT NOCOPY Varchar2,
3470 Attribute13 IN OUT NOCOPY Varchar2,
3471 Attribute14 IN OUT NOCOPY Varchar2,
3472 Attribute15 IN OUT NOCOPY Varchar2,
3473 Last_Update_Date IN OUT NOCOPY Date,
3474 Last_Updated_By IN OUT NOCOPY Number,
3475 Creation_Date IN OUT NOCOPY Date,
3476 Created_By IN OUT NOCOPY Number,
3477 Last_Update_Login IN OUT NOCOPY Number,
3478 Request_Id IN Number,
3479 Program_Application_Id IN Number,
3480 Program_Id IN Number,
3481 Program_Update_Date IN Date,
3482 Delete_Record IN Varchar2 DEFAULT NULL ) IS
3483
3484 BEGIN
3485
3486 Validate_Cust_Item(Customer_Item_Id, Customer_Item_Number, Item_Definition_Level, Item_Definition_Level_Desc, Customer_Id,
3487 Customer_Number, Customer_Name, Customer_Category_Code, Customer_Category, Address_Id, Address1, Address2, Address3,
3488 Address4, City, State, County, Country, Postal_Code);
3489
3490 Validate_Master_Organization(Master_Organization_Id, Master_Organization_Code, Master_Organization_Name);
3491
3492 Validate_Inventory_Item(
3493 Inventory_Item_Id,
3494 Inventory_Item,
3495 Inventory_Item_Segment1,
3496 Inventory_Item_Segment2,
3497 Inventory_Item_Segment3,
3498 Inventory_Item_Segment4,
3499 Inventory_Item_Segment5,
3500 Inventory_Item_Segment6,
3501 Inventory_Item_Segment7,
3502 Inventory_Item_Segment8,
3503 Inventory_Item_Segment9,
3504 Inventory_Item_Segment10,
3505 Inventory_Item_Segment11,
3506 Inventory_Item_Segment12,
3507 Inventory_Item_Segment13,
3508 Inventory_Item_Segment14,
3509 Inventory_Item_Segment15,
3510 Inventory_Item_Segment16,
3511 Inventory_Item_Segment17,
3512 Inventory_Item_Segment18,
3513 Inventory_Item_Segment19,
3514 Inventory_Item_Segment20,
3515 Master_Organization_Id);
3516
3517 Validate_Inactive_Flag(Inactive_Flag);
3518
3519 Check_Required_Columns(NULL, NULL, NULL, NULL, NULL, NULL, Inactive_Flag, Last_Updated_By, Last_Update_Date, Created_By,
3520 Creation_Date, Customer_Item_Id, Inventory_Item_Id, Master_Organization_Id, Preference_Number);
3521
3522 Check_Uniqueness(NULL, NULL, NULL, NULL, NULL, NULL, Customer_Item_Id, Inventory_Item_Id, Master_Organization_Id, Preference_Number);
3523
3524 Validate_Concurrent_Program(Request_Id, Program_Application_Id, Program_Id, Program_Update_Date);
3525
3526 Insert_Row(
3527 NULL,
3528 Last_Update_Date,
3529 Last_Updated_By,
3530 Creation_Date,
3531 Created_By,
3532 Last_Update_Login,
3533 NULL,
3534 NULL,
3535 NULL,
3536 NULL,
3537 NULL,
3538 NULL,
3539 NULL,
3540 NULL,
3541 NULL,
3542 NULL,
3543 NULL,
3544 NULL,
3545 NULL,
3546 NULL,
3547 Inactive_Flag,
3548 Attribute_Category,
3549 Attribute1,
3550 Attribute2,
3551 Attribute3,
3552 Attribute4,
3553 Attribute5,
3554 Attribute6,
3555 Attribute7,
3556 Attribute8,
3557 Attribute9,
3558 Attribute10,
3559 Attribute11,
3560 Attribute12,
3561 Attribute13,
3562 Attribute14,
3563 Attribute15,
3564 NULL,
3565 NULL,
3566 Request_Id,
3567 Program_Application_Id,
3568 Program_Id,
3569 Program_Update_Date,
3570 Customer_Item_Id,
3571 Inventory_Item_Id,
3572 Master_Organization_Id,
3573 Preference_Number);
3574
3575 Delete_Row('X', Delete_Record, Row_Id);
3576
3577 END Validate_CI_XRefs;
3578
3579
3580 PROCEDURE Validate_Cust_Item ( P_Customer_Item_Id IN OUT NOCOPY Number,
3581 P_Customer_Item_Number IN Varchar2 DEFAULT NULL,
3582 P_Item_Definition_Level IN Varchar2 DEFAULT NULL,
3583 P_Item_Definition_Level_Desc IN Varchar2 DEFAULT NULL,
3584 P_Customer_Id IN Number DEFAULT NULL,
3585 P_Customer_Number IN Varchar2 DEFAULT NULL,
3586 P_Customer_Name IN Varchar2 DEFAULT NULL,
3587 P_Customer_Category_Code IN Varchar2 DEFAULT NULL,
3588 P_Customer_Category IN Varchar2 DEFAULT NULL,
3589 P_Address_Id IN Number DEFAULT NULL,
3590 P_Address1 IN Varchar2 DEFAULT NULL,
3591 P_Address2 IN Varchar2 DEFAULT NULL,
3592 P_Address3 IN Varchar2 DEFAULT NULL,
3593 P_Address4 IN Varchar2 DEFAULT NULL,
3594 P_City IN Varchar2 DEFAULT NULL,
3595 P_State IN Varchar2 DEFAULT NULL,
3596 P_County IN Varchar2 DEFAULT NULL,
3597 P_Country IN Varchar2 DEFAULT NULL,
3598 P_Postal_Code IN Varchar2 DEFAULT NULL ) IS
3599
3600 Temp_Customer_Item_Id Number := NULL;
3601 Temp_Inactive_Flag Varchar2(1) := NULL;
3602 V_Item_Definition_Level Varchar2(1) := P_Item_Definition_Level;
3603 V_Item_Definition_Level_Desc Varchar2(30) := P_Item_Definition_Level_Desc;
3604 V_Customer_Id Number := P_Customer_Id;
3605 V_Customer_Number Varchar2(50) := P_Customer_Number;
3606 V_Customer_Name Varchar2(50) := P_Customer_Name;
3607 V_Customer_Category_Code Varchar2(30) := P_Customer_Category_Code;
3608 V_Customer_Category Varchar2(80) := P_Customer_Category;
3609 V_Address_Id Number := P_Address_Id;
3610 V_Address1 Varchar2(240) := P_Address1;
3611 V_Address2 Varchar2(240) := P_Address2;
3612 V_Address3 Varchar2(240) := P_Address3;
3613 V_Address4 Varchar2(240) := P_Address4;
3614 V_City Varchar2(50) := P_City;
3615 V_State Varchar2(50) := P_State;
3616 V_County Varchar2(50) := P_County;
3617 V_Country Varchar2(50) := P_Country;
3618 V_Postal_Code Varchar2(30) := P_Postal_Code;
3619
3620
3621 BEGIN
3622
3623 IF (P_Customer_Item_Id IS NOT NULL) THEN
3624
3625 SELECT Customer_Item_Id, Inactive_Flag
3626 INTO Temp_Customer_Item_Id, Temp_Inactive_Flag
3627 FROM MTL_CUSTOMER_ITEMS MCI
3628 WHERE MCI.Customer_Item_Id = P_Customer_Item_Id;
3629
3630
3631 IF (SQL%FOUND) THEN
3632
3633 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
3634 P_Customer_Item_Id := Temp_Customer_Item_Id;
3635 RETURN;
3636
3637 END IF;
3638
3639 ELSIF ((P_Customer_Item_Id IS NULL) AND (P_Customer_Item_Number IS NOT NULL)) THEN
3640
3641 Validate_CI_Def_Level (V_Item_Definition_Level, V_Item_Definition_Level_Desc, V_Customer_Id,
3642 V_Customer_Number, V_Customer_Name, V_Customer_Category_Code, V_Customer_Category,
3643 V_Address_Id, V_Address1, V_Address2, V_Address3, V_Address4, V_City, V_State, V_County, V_Country, V_Postal_Code);
3644
3645 SELECT Customer_Item_Id, Inactive_Flag
3646 INTO Temp_Customer_Item_Id, Temp_Inactive_Flag
3647 FROM MTL_CUSTOMER_ITEMS MCI
3648 WHERE MCI.Item_Definition_Level = NVL(P_Item_Definition_Level, V_Item_Definition_Level)
3649 AND MCI.Customer_Id = NVL(P_Customer_Id, V_Customer_Id)
3650 AND NVL(MCI.Customer_Category_Code, ' ') = NVL(NVL(P_Customer_Category_Code, V_Customer_Category_Code), ' ')
3651 AND NVL(MCI.Address_Id, -99) = NVL(NVL(P_Address_Id, V_Address_Id), -99)
3652 AND MCI.Customer_Item_Number = P_Customer_Item_Number;
3653
3654 IF (SQL%FOUND) THEN
3655
3656 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
3657 P_Customer_Item_Id := Temp_Customer_Item_Id;
3658 RETURN;
3659
3660 END IF;
3661
3662 ELSE
3663
3664 FND_MESSAGE.Set_Name('INV', 'INV_MISSING_CI_INFO');
3665 Manage_Error_Code('IN', 'APP-43051', Curr_Error);
3666 RAISE Error;
3667
3668 END IF;
3669
3670
3671 EXCEPTION
3672
3673 WHEN NO_DATA_FOUND THEN
3674
3675 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_CI');
3676 IF (P_Customer_Item_Id IS NULL) THEN
3677 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_ITEM_ID', FALSE);
3678 ELSE
3679 FND_MESSAGE.Set_Token('COLUMN', 'CUSTOMER_ITEM_NUMBER', FALSE);
3680 END IF;
3681 Manage_Error_Code('IN', 'APP-43052', Curr_Error);
3682 RAISE Error;
3683
3684 WHEN TOO_MANY_ROWS THEN
3685
3686 FND_MESSAGE.Set_Name('INV', 'INV_MULTIPLE_CI');
3687 Manage_Error_Code('IN', 'APP-43053', Curr_Error);
3688 RAISE Error;
3689
3690 END Validate_Cust_Item;
3691
3692
3693 PROCEDURE Validate_Master_Organization(
3694 P_Master_Organization_Id IN OUT NOCOPY Number,
3695 P_Master_Organization_Code IN Varchar2 DEFAULT NULL,
3696 P_Master_Organization_Name IN Varchar2 DEFAULT NULL
3697 ) IS
3698
3699 Temp_Master_Organization_Id Number := NULL;
3700 Temp_Date_From Date := NULL;
3701 Temp_Date_To Date := NULL;
3702
3703 BEGIN
3704
3705 IF (P_Master_Organization_Id IS NOT NULL) THEN
3706
3707 SELECT MP.Organization_Id,
3708 HROU.Date_From, HROU.Date_To
3709 INTO Temp_Master_Organization_Id,
3710 Temp_Date_From, Temp_Date_To
3711 FROM HR_ORGANIZATION_UNITS HROU,
3712 MTL_PARAMETERS MP
3713 WHERE MP.Organization_Id =
3714 P_Master_Organization_Id
3715 AND HROU.Organization_Id =
3716 MP.Organization_Id;
3717
3718 IF ((SQL%FOUND) AND
3719 (NOT (TRUNC(SYSDATE) BETWEEN
3720 NVL(TRUNC((Temp_Date_From)),SYSDATE)
3721 AND NVL(TRUNC((Temp_Date_To)), SYSDATE)))) THEN
3722
3723 FND_MESSAGE.Set_Name('INV',
3724 'INV_INACTIVE_ORGANIZATION');
3725 FND_MESSAGE.Set_Token('COLUMN',
3726 'MASTER_ORGANIZATION_ID', FALSE);
3727 Manage_Error_Code('IN', 'APP-43027', Curr_Error);
3728 RAISE Error;
3729 ELSE
3730 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
3731 P_Master_Organization_Id :=
3732 Temp_Master_Organization_Id;
3733 RETURN;
3734 END IF;
3735
3736 ELSIF ((P_Master_Organization_Id IS NULL)
3737 AND (P_Master_Organization_Code IS NOT NULL)) THEN
3738
3739 SELECT MP.Organization_Id, HROU.Date_From,
3740 HROU.Date_To
3741 INTO Temp_Master_Organization_Id, Temp_Date_From,
3742 Temp_Date_To
3743 FROM MTL_PARAMETERS MP,
3744 HR_ORGANIZATION_UNITS HROU
3745 WHERE MP.Organization_Code = P_Master_Organization_Code
3746 AND HROU.Organization_Id = MP.Organization_id;
3747
3748 IF ((SQL%FOUND) AND
3749 (NOT (TRUNC(SYSDATE) BETWEEN
3750 NVL(TRUNC((Temp_Date_From)),SYSDATE)
3751 AND NVL(TRUNC((Temp_Date_To)), SYSDATE)))) THEN
3752
3753 FND_MESSAGE.Set_Name('INV',
3754 'INV_INACTIVE_ORGANIZATION');
3755 FND_MESSAGE.Set_Token('COLUMN',
3756 'MASTER_ORGANIZATION_CODE', FALSE);
3757 Manage_Error_Code('IN', 'APP-43027', Curr_Error);
3758 RAISE Error;
3759 ELSE
3760
3761 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
3762 P_Master_Organization_Id :=
3763 Temp_Master_Organization_Id;
3764 RETURN;
3765 END IF;
3766 ELSIF ((P_Master_Organization_Id IS NULL)
3767 AND (P_Master_Organization_Code IS NULL)
3768 AND (P_Master_Organization_Name IS NOT NULL)) THEN
3769
3770 SELECT MP.Organization_Id, HROU.Date_From,
3771 HROU.Date_To
3772 INTO Temp_Master_Organization_Id, Temp_Date_From,
3773 Temp_Date_To
3774 FROM MTL_PARAMETERS MP,
3775 HR_ORGANIZATION_INFORMATION HROI,
3776 HR_ORGANIZATION_UNITS HROU
3777 WHERE HROU.Name = P_Master_Organization_Name
3778 AND MP.Organization_Id = HROU.Organization_Id
3779 AND HROI.Organization_Id = MP.Organization_Id
3780 AND HROI.Org_Information_Context = 'CLASS'
3781 AND HROI.Org_Information1 = 'INV'
3782 AND HROI.Org_Information2 = 'Y';
3783
3784 IF ((SQL%FOUND) AND
3785 (NOT (TRUNC(SYSDATE) BETWEEN
3786 NVL(TRUNC((Temp_Date_From)),SYSDATE)
3787 AND NVL(TRUNC((Temp_Date_To)), SYSDATE)))) THEN
3788
3789 FND_MESSAGE.Set_Name('INV',
3790 'INV_INACTIVE_ORGANIZATION');
3791 FND_MESSAGE.Set_Token('COLUMN',
3792 'MASTER_ORGANIZATION_NAME', FALSE);
3793 Manage_Error_Code('IN', 'APP-43027', Curr_Error);
3794 RAISE Error;
3795 ELSE
3796 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
3797 P_Master_Organization_Id :=
3798 Temp_Master_Organization_Id;
3799 RETURN;
3800 END IF;
3801 ELSE
3802 FND_MESSAGE.Set_Name('INV', 'INV_NO_ORGANIZATION');
3803 FND_MESSAGE.Set_Token('COLUMN1',
3804 'MASTER_ORGANIZATION_ID', FALSE);
3805 FND_MESSAGE.Set_Token('COLUMN2',
3806 'MASTER_ORGANIZATION_CODE', FALSE);
3807 FND_MESSAGE.Set_Token('COLUMN3',
3808 'MASTER_ORGANIZATION_NAME', FALSE);
3809 Manage_Error_Code('IN', 'APP-43045', Curr_Error);
3810 RAISE Error;
3811 END IF;
3812
3813 EXCEPTION
3814 WHEN NO_DATA_FOUND THEN
3815
3816 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_ORGANIZATION');
3817 IF (P_Master_Organization_Id IS NOT NULL) THEN
3818 FND_MESSAGE.Set_Token('COLUMN',
3819 'MASTER_ORGANIZATION_ID', FALSE);
3820 ELSIF ((P_Master_Organization_Id IS NULL)
3821 AND (P_Master_Organization_Code IS NOT NULL)) THEN
3822 FND_MESSAGE.Set_Token('COLUMN',
3823 'MASTER_ORGANIZATION_CODE', FALSE);
3824 ELSE
3825 FND_MESSAGE.Set_Token('COLUMN',
3826 'MASTER_ORGANIZATION_NAME', FALSE);
3827 END IF;
3828 Manage_Error_Code('IN', 'APP-43028', Curr_Error);
3829 RAISE Error;
3830
3831 WHEN TOO_MANY_ROWS THEN
3832
3833 FND_MESSAGE.Set_Name('INV', 'INV_MULTIPLE_ORGANIZATIONS');
3834 Manage_Error_Code('IN', 'APP-43029', Curr_Error);
3835 RAISE Error;
3836
3837 END Validate_Master_Organization;
3838
3839
3840 PROCEDURE Validate_Inventory_Item(
3841 P_Inventory_Item_Id IN OUT NOCOPY Number,
3842 P_Inventory_Item IN Varchar2 DEFAULT NULL,
3843 P_Inventory_Item_Segment1 IN Varchar2 DEFAULT NULL,
3844 P_Inventory_Item_Segment2 IN Varchar2 DEFAULT NULL,
3845 P_Inventory_Item_Segment3 IN Varchar2 DEFAULT NULL,
3846 P_Inventory_Item_Segment4 IN Varchar2 DEFAULT NULL,
3847 P_Inventory_Item_Segment5 IN Varchar2 DEFAULT NULL,
3848 P_Inventory_Item_Segment6 IN Varchar2 DEFAULT NULL,
3849 P_Inventory_Item_Segment7 IN Varchar2 DEFAULT NULL,
3850 P_Inventory_Item_Segment8 IN Varchar2 DEFAULT NULL,
3851 P_Inventory_Item_Segment9 IN Varchar2 DEFAULT NULL,
3852 P_Inventory_Item_Segment10 IN Varchar2 DEFAULT NULL,
3853 P_Inventory_Item_Segment11 IN Varchar2 DEFAULT NULL,
3854 P_Inventory_Item_Segment12 IN Varchar2 DEFAULT NULL,
3855 P_Inventory_Item_Segment13 IN Varchar2 DEFAULT NULL,
3856 P_Inventory_Item_Segment14 IN Varchar2 DEFAULT NULL,
3857 P_Inventory_Item_Segment15 IN Varchar2 DEFAULT NULL,
3858 P_Inventory_Item_Segment16 IN Varchar2 DEFAULT NULL,
3859 P_Inventory_Item_Segment17 IN Varchar2 DEFAULT NULL,
3860 P_Inventory_Item_Segment18 IN Varchar2 DEFAULT NULL,
3861 P_Inventory_Item_Segment19 IN Varchar2 DEFAULT NULL,
3862 P_Inventory_Item_Segment20 IN Varchar2 DEFAULT NULL,
3863 P_Master_Organization_Id IN Number DEFAULT NULL
3864 ) IS
3865
3866 Now Varchar2(20) := TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS');
3867 Temp_Inventory_Item_Id Number := NULL;
3868 Dummy_Inventory_Item_Id Number := NULL;
3869
3870 L_Segment_Array fnd_flex_ext.SegmentArray;
3871 L_SegOrder_Array SegOrderArray;
3872 L_TempSeg_Counter Number := 0;
3873 L_Success Boolean;
3874 L_Temp_Concat_Container Varchar2(2000);
3875 L_FlexSeg_Counter Number := 0;
3876
3877 Cursor L_FlexSegOrder_Curr is
3878 SELECT segment_num from
3879 fnd_id_flex_segments
3880 where application_id = 401
3881 and id_flex_code = 'MSTK'
3882 and enabled_flag = 'Y'
3883 order by segment_num;
3884
3885 L_SegNumDummy Number;
3886 L_SegNumIndex Number := 0;
3887 L_Delimiter Varchar2(1) := NULL;
3888 L_ConcatSegs Varchar2(2000) := NULL;
3889 L_StructNum Number := NULL;
3890
3891 BEGIN
3892
3893 IF (P_Inventory_Item_Id IS NOT NULL) THEN
3894
3895 SELECT Inventory_Item_Id
3896 INTO Temp_Inventory_Item_Id
3897 FROM MTL_SYSTEM_ITEMS MSI
3898 WHERE MSI.Inventory_Item_Id = P_Inventory_Item_Id
3899 AND MSI.Organization_Id = P_Master_Organization_Id
3900 AND NVL(MSI.Approval_Status,'A') = 'A';--Added for 11.5.10 PLM
3901
3902 IF (SQL%FOUND) THEN
3903
3904 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
3905 P_Inventory_Item_Id := Temp_Inventory_Item_Id;
3906 RETURN;
3907
3908 END IF;
3909
3910 ELSIF ((P_Inventory_Item_Id IS NULL) AND
3911 (P_Inventory_Item IS NOT NULL)) THEN
3912
3913 BEGIN
3914 SELECT ID_Flex_Num
3915 INTO L_StructNum
3916 FROM fnd_id_flex_structures
3917 WHERE application_id = 401
3918 AND id_flex_code = 'MSTK'
3919 AND upper(enabled_flag) = 'Y';
3920 EXCEPTION
3921 WHEN NO_DATA_FOUND THEN
3922 FND_MESSAGE.Set_Name('INV', 'INV_NO_ITEM_FLEX_STRUCT');
3923 Manage_Error_Code('IN', 'APP-43014',
3924 Curr_Error);
3925 RAISE Error;
3926
3927 WHEN TOO_MANY_ROWS THEN
3928 FND_MESSAGE.Set_Name('INV',
3929 'INV_MULT_ITEM_FLEX_STRUCT');
3930 Manage_Error_Code('IN', 'APP-43014',
3931 Curr_Error);
3932 RAISE Error;
3933 END;
3934
3935 L_Success := FND_FLEX_KEYVAL.Validate_Segs(
3936 OPERATION => 'FIND_COMBINATION',
3937 APPL_SHORT_NAME => 'INV',
3938 KEY_FLEX_CODE => 'MSTK',
3939 STRUCTURE_NUMBER=> L_StructNum,
3940 CONCAT_SEGMENTS => P_Inventory_Item,
3941 DATA_SET => P_Master_Organization_Id
3942 );
3943
3944 if L_Success then
3945 P_Inventory_Item_Id :=
3946 FND_FLEX_KEYVAL.Combination_Id;
3947
3948 else
3949 P_Inventory_Item_Id := NULL;
3950 end if;
3951
3952 If P_Inventory_Item_Id is NULL then
3953
3954 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_INV_ITEM');
3955 FND_MESSAGE.Set_Token('COLUMN',
3956 'INVENTORY_ITEM', FALSE);
3957 Manage_Error_Code('IN', 'APP-43014',
3958 Curr_Error);
3959 RAISE Error;
3960 END IF;
3961
3962 ELSIF ((P_Inventory_Item_Id IS NULL) AND
3963 (P_Inventory_Item IS NULL)) THEN
3964
3965 BEGIN
3966 SELECT ID_Flex_Num
3967 INTO L_StructNum
3968 FROM fnd_id_flex_structures
3969 WHERE application_id = 401
3970 AND id_flex_code = 'MSTK'
3971 AND upper(enabled_flag) = 'Y';
3972 EXCEPTION
3973 WHEN NO_DATA_FOUND THEN
3974 FND_MESSAGE.Set_Name('INV', 'INV_NO_ITEM_FLEX_STRUCT');
3975 Manage_Error_Code('IN', 'APP-43014',
3976 Curr_Error);
3977 RAISE Error;
3978
3979 WHEN TOO_MANY_ROWS THEN
3980 FND_MESSAGE.Set_Name('INV',
3981 'INV_MULT_ITEM_FLEX_STRUCT');
3982 Manage_Error_Code('IN', 'APP-43014',
3983 Curr_Error);
3984 RAISE Error;
3985 END;
3986
3987 OPEN L_FlexSegOrder_Curr;
3988
3989 LOOP
3990 FETCH L_FlexSegOrder_Curr into L_SegNumDummy;
3991 EXIT WHEN L_FlexSegOrder_Curr%NOTFOUND;
3992
3993 L_FlexSeg_Counter := L_FlexSeg_Counter+1;
3994 L_Segment_Array(L_FlexSeg_Counter) := NULL;
3995 L_SegOrder_Array(L_FlexSeg_Counter) := L_SegNumDummy;
3996 END LOOP;
3997
3998 CLOSE L_FlexSegOrder_Curr;
3999
4000 if P_Inventory_Item_Segment1 IS NOT NULL THEN
4001 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4002
4003 BEGIN
4004 select segment_num
4005 into L_SegNumDummy
4006 from fnd_id_flex_segments
4007 where application_id = 401
4008 and id_flex_code = 'MSTK'
4009 and enabled_flag = 'Y'
4010 and application_column_name = 'SEGMENT1';
4011
4012 EXCEPTION
4013 WHEN NO_DATA_FOUND THEN
4014 FND_MESSAGE.Set_Name('INV',
4015 'INV_INVALID_ITEM_SEG');
4016 FND_MESSAGE.Set_Token('SEGMENT',
4017 'SEGMENT1',
4018 FALSE);
4019 Manage_Error_Code('IN', 'APP-43014',
4020 Curr_Error);
4021 RAISE Error;
4022
4023 WHEN TOO_MANY_ROWS THEN
4024 FND_MESSAGE.Set_Name('INV',
4025 'INV_MULTIPLE_FLEX_SEG');
4026 Manage_Error_Code('IN', 'APP-43014',
4027 Curr_Error);
4028 RAISE Error;
4029 END;
4030
4031 L_SegNumIndex := 0;
4032
4033 for i in 1..L_FlexSeg_Counter loop
4034
4035 L_SegNumIndex := i;
4036
4037 EXIT WHEN L_SegOrder_Array(i) =
4038 L_SegNumDummy;
4039 end loop;
4040
4041
4042 if L_SegNumIndex <= L_FlexSeg_Counter then
4043 L_Segment_Array(L_SegNumIndex) :=
4044 P_Inventory_Item_Segment1;
4045 else
4046 FND_MESSAGE.Set_Name('INV',
4047 'INV_FLEX_SEG_OUT_OF_RANGE');
4048 Manage_Error_Code('IN', 'APP-43014',
4049 Curr_Error);
4050 RAISE Error;
4051
4052 end if;
4053 end if;
4054
4055 if P_Inventory_Item_Segment2 IS NOT NULL THEN
4056 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4057
4058 BEGIN
4059 select segment_num
4060 into L_SegNumDummy
4061 from fnd_id_flex_segments
4062 where application_id = 401
4063 and id_flex_code = 'MSTK'
4064 and enabled_flag = 'Y'
4065 and application_column_name = 'SEGMENT2';
4066
4067 EXCEPTION
4068 WHEN NO_DATA_FOUND THEN
4069 FND_MESSAGE.Set_Name('INV',
4070 'INV_INVALID_ITEM_SEG');
4071 FND_MESSAGE.Set_Token('SEGMENT',
4072 'SEGMENT2',
4073 FALSE);
4074 Manage_Error_Code('IN', 'APP-43014',
4075 Curr_Error);
4076 RAISE Error;
4077
4078 WHEN TOO_MANY_ROWS THEN
4079 FND_MESSAGE.Set_Name('INV',
4080 'INV_MULTIPLE_FLEX_SEG');
4081 Manage_Error_Code('IN', 'APP-43014',
4082 Curr_Error);
4083 RAISE Error;
4084 END;
4085
4086 L_SegNumIndex := 0;
4087
4088 for i in 1..L_FlexSeg_Counter loop
4089
4090 L_SegNumIndex := i;
4091
4092 EXIT WHEN L_SegOrder_Array(i) =
4093 L_SegNumDummy;
4094 end loop;
4095
4096
4097 if L_SegNumIndex <= L_FlexSeg_Counter then
4098 L_Segment_Array(L_SegNumIndex) :=
4099 P_Inventory_Item_Segment2;
4100 else
4101 FND_MESSAGE.Set_Name('INV',
4102 'INV_FLEX_SEG_OUT_OF_RANGE');
4103 Manage_Error_Code('IN', 'APP-43014',
4104 Curr_Error);
4105 RAISE Error;
4106
4107 end if;
4108 end if;
4109
4110 if P_Inventory_Item_Segment3 IS NOT NULL THEN
4111 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4112
4113 BEGIN
4114 select segment_num
4115 into L_SegNumDummy
4116 from fnd_id_flex_segments
4117 where application_id = 401
4118 and id_flex_code = 'MSTK'
4119 and enabled_flag = 'Y'
4120 and application_column_name = 'SEGMENT3';
4121
4122 EXCEPTION
4123 WHEN NO_DATA_FOUND THEN
4124 FND_MESSAGE.Set_Name('INV',
4125 'INV_INVALID_ITEM_SEG');
4126 FND_MESSAGE.Set_Token('SEGMENT',
4127 'SEGMENT3',
4128 FALSE);
4129 Manage_Error_Code('IN', 'APP-43014',
4130 Curr_Error);
4131 RAISE Error;
4132
4133 WHEN TOO_MANY_ROWS THEN
4134 FND_MESSAGE.Set_Name('INV',
4135 'INV_MULTIPLE_FLEX_SEG');
4136 Manage_Error_Code('IN', 'APP-43014',
4137 Curr_Error);
4138 RAISE Error;
4139 END;
4140
4141 L_SegNumIndex := 0;
4142
4143 for i in 1..L_FlexSeg_Counter loop
4144
4145 L_SegNumIndex := i;
4146
4147 EXIT WHEN L_SegOrder_Array(i) =
4148 L_SegNumDummy;
4149 end loop;
4150
4151
4152 if L_SegNumIndex <= L_FlexSeg_Counter then
4153 L_Segment_Array(L_SegNumIndex) :=
4154 P_Inventory_Item_Segment3;
4155 else
4156 FND_MESSAGE.Set_Name('INV',
4157 'INV_FLEX_SEG_OUT_OF_RANGE');
4158 Manage_Error_Code('IN', 'APP-43014',
4159 Curr_Error);
4160 RAISE Error;
4161
4162 end if;
4163 end if;
4164
4165 if P_Inventory_Item_Segment4 IS NOT NULL THEN
4166 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4167
4168 BEGIN
4169 select segment_num
4170 into L_SegNumDummy
4171 from fnd_id_flex_segments
4172 where application_id = 401
4173 and id_flex_code = 'MSTK'
4174 and enabled_flag = 'Y'
4175 and application_column_name = 'SEGMENT4';
4176
4177 EXCEPTION
4178 WHEN NO_DATA_FOUND THEN
4179 FND_MESSAGE.Set_Name('INV',
4180 'INV_INVALID_ITEM_SEG');
4181 FND_MESSAGE.Set_Token('SEGMENT',
4182 'SEGMENT4',
4183 FALSE);
4184 Manage_Error_Code('IN', 'APP-43014',
4185 Curr_Error);
4186 RAISE Error;
4187
4188 WHEN TOO_MANY_ROWS THEN
4189 FND_MESSAGE.Set_Name('INV',
4190 'INV_MULTIPLE_FLEX_SEG');
4191 Manage_Error_Code('IN', 'APP-43014',
4192 Curr_Error);
4193 RAISE Error;
4194 END;
4195
4196 L_SegNumIndex := 0;
4197
4198 for i in 1..L_FlexSeg_Counter loop
4199
4200 L_SegNumIndex := i;
4201
4202 EXIT WHEN L_SegOrder_Array(i) =
4203 L_SegNumDummy;
4204 end loop;
4205
4206
4207 if L_SegNumIndex <= L_FlexSeg_Counter then
4208 L_Segment_Array(L_SegNumIndex) :=
4209 P_Inventory_Item_Segment4;
4210 else
4211 FND_MESSAGE.Set_Name('INV',
4212 'INV_FLEX_SEG_OUT_OF_RANGE');
4213 Manage_Error_Code('IN', 'APP-43014',
4214 Curr_Error);
4215 RAISE Error;
4216
4217 end if;
4218 end if;
4219
4220 if P_Inventory_Item_Segment5 IS NOT NULL THEN
4221 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4222
4223 BEGIN
4224 select segment_num
4225 into L_SegNumDummy
4226 from fnd_id_flex_segments
4227 where application_id = 401
4228 and id_flex_code = 'MSTK'
4229 and enabled_flag = 'Y'
4230 and application_column_name = 'SEGMENT5';
4231
4232 EXCEPTION
4233 WHEN NO_DATA_FOUND THEN
4234 FND_MESSAGE.Set_Name('INV',
4235 'INV_INVALID_ITEM_SEG');
4236 FND_MESSAGE.Set_Token('SEGMENT',
4237 'SEGMENT5',
4238 FALSE);
4239 Manage_Error_Code('IN', 'APP-43014',
4240 Curr_Error);
4241 RAISE Error;
4242
4243 WHEN TOO_MANY_ROWS THEN
4244 FND_MESSAGE.Set_Name('INV',
4245 'INV_MULTIPLE_FLEX_SEG');
4246 Manage_Error_Code('IN', 'APP-43014',
4247 Curr_Error);
4248 RAISE Error;
4249 END;
4250
4251 L_SegNumIndex := 0;
4252
4253 for i in 1..L_FlexSeg_Counter loop
4254
4255 L_SegNumIndex := i;
4256
4257 EXIT WHEN L_SegOrder_Array(i) =
4258 L_SegNumDummy;
4259 end loop;
4260
4261
4262 if L_SegNumIndex <= L_FlexSeg_Counter then
4263 L_Segment_Array(L_SegNumIndex) :=
4264 P_Inventory_Item_Segment5;
4265 else
4266 FND_MESSAGE.Set_Name('INV',
4267 'INV_FLEX_SEG_OUT_OF_RANGE');
4268 Manage_Error_Code('IN', 'APP-43014',
4269 Curr_Error);
4270 RAISE Error;
4271
4272 end if;
4273 end if;
4274
4275 if P_Inventory_Item_Segment6 IS NOT NULL THEN
4276 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4277
4278 BEGIN
4279 select segment_num
4280 into L_SegNumDummy
4281 from fnd_id_flex_segments
4282 where application_id = 401
4283 and id_flex_code = 'MSTK'
4284 and enabled_flag = 'Y'
4285 and application_column_name = 'SEGMENT6';
4286
4287 EXCEPTION
4288 WHEN NO_DATA_FOUND THEN
4289 FND_MESSAGE.Set_Name('INV',
4290 'INV_INVALID_ITEM_SEG');
4291 FND_MESSAGE.Set_Token('SEGMENT',
4292 'SEGMENT6',
4293 FALSE);
4294 Manage_Error_Code('IN', 'APP-43014',
4295 Curr_Error);
4296 RAISE Error;
4297
4298 WHEN TOO_MANY_ROWS THEN
4299 FND_MESSAGE.Set_Name('INV',
4300 'INV_MULTIPLE_FLEX_SEG');
4301 Manage_Error_Code('IN', 'APP-43014',
4302 Curr_Error);
4303 RAISE Error;
4304 END;
4305
4306 L_SegNumIndex := 0;
4307
4308 for i in 1..L_FlexSeg_Counter loop
4309
4310 L_SegNumIndex := i;
4311
4312 EXIT WHEN L_SegOrder_Array(i) =
4313 L_SegNumDummy;
4314 end loop;
4315
4316
4317 if L_SegNumIndex <= L_FlexSeg_Counter then
4318 L_Segment_Array(L_SegNumIndex) :=
4319 P_Inventory_Item_Segment6;
4320 else
4321 FND_MESSAGE.Set_Name('INV',
4322 'INV_FLEX_SEG_OUT_OF_RANGE');
4323 Manage_Error_Code('IN', 'APP-43014',
4324 Curr_Error);
4325 RAISE Error;
4326
4327 end if;
4328 end if;
4329
4330 if P_Inventory_Item_Segment7 IS NOT NULL THEN
4331 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4332
4333 BEGIN
4334 select segment_num
4335 into L_SegNumDummy
4336 from fnd_id_flex_segments
4337 where application_id = 401
4338 and id_flex_code = 'MSTK'
4339 and enabled_flag = 'Y'
4340 and application_column_name = 'SEGMENT7';
4341
4342 EXCEPTION
4343 WHEN NO_DATA_FOUND THEN
4344 FND_MESSAGE.Set_Name('INV',
4345 'INV_INVALID_ITEM_SEG');
4346 FND_MESSAGE.Set_Token('SEGMENT',
4347 'SEGMENT7',
4348 FALSE);
4349 Manage_Error_Code('IN', 'APP-43014',
4350 Curr_Error);
4351 RAISE Error;
4352
4353 WHEN TOO_MANY_ROWS THEN
4354 FND_MESSAGE.Set_Name('INV',
4355 'INV_MULTIPLE_FLEX_SEG');
4356 Manage_Error_Code('IN', 'APP-43014',
4357 Curr_Error);
4358 RAISE Error;
4359 END;
4360
4361 L_SegNumIndex := 0;
4362
4363 for i in 1..L_FlexSeg_Counter loop
4364
4365 L_SegNumIndex := i;
4366
4367 EXIT WHEN L_SegOrder_Array(i) =
4368 L_SegNumDummy;
4369 end loop;
4370
4371
4372 if L_SegNumIndex <= L_FlexSeg_Counter then
4373 L_Segment_Array(L_SegNumIndex) :=
4374 P_Inventory_Item_Segment7;
4375 else
4376 FND_MESSAGE.Set_Name('INV',
4377 'INV_FLEX_SEG_OUT_OF_RANGE');
4378 Manage_Error_Code('IN', 'APP-43014',
4379 Curr_Error);
4380 RAISE Error;
4381
4382 end if;
4383 end if;
4384
4385 if P_Inventory_Item_Segment8 IS NOT NULL THEN
4386 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4387
4388 BEGIN
4389 select segment_num
4390 into L_SegNumDummy
4391 from fnd_id_flex_segments
4392 where application_id = 401
4393 and id_flex_code = 'MSTK'
4394 and enabled_flag = 'Y'
4395 and application_column_name = 'SEGMENT8';
4396
4397 EXCEPTION
4398 WHEN NO_DATA_FOUND THEN
4399 FND_MESSAGE.Set_Name('INV',
4400 'INV_INVALID_ITEM_SEG');
4401 FND_MESSAGE.Set_Token('SEGMENT',
4402 'SEGMENT8',
4403 FALSE);
4404 Manage_Error_Code('IN', 'APP-43014',
4405 Curr_Error);
4406 RAISE Error;
4407
4408 WHEN TOO_MANY_ROWS THEN
4409 FND_MESSAGE.Set_Name('INV',
4410 'INV_MULTIPLE_FLEX_SEG');
4411 Manage_Error_Code('IN', 'APP-43014',
4412 Curr_Error);
4413 RAISE Error;
4414 END;
4415
4416 L_SegNumIndex := 0;
4417
4418 for i in 1..L_FlexSeg_Counter loop
4419
4420 L_SegNumIndex := i;
4421
4422 EXIT WHEN L_SegOrder_Array(i) =
4423 L_SegNumDummy;
4424 end loop;
4425
4426
4427 if L_SegNumIndex <= L_FlexSeg_Counter then
4428 L_Segment_Array(L_SegNumIndex) :=
4429 P_Inventory_Item_Segment8;
4430 else
4431 FND_MESSAGE.Set_Name('INV',
4432 'INV_FLEX_SEG_OUT_OF_RANGE');
4433 Manage_Error_Code('IN', 'APP-43014',
4434 Curr_Error);
4435 RAISE Error;
4436
4437 end if;
4438 end if;
4439
4440 if P_Inventory_Item_Segment9 IS NOT NULL THEN
4441 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4442
4443 BEGIN
4444 select segment_num
4445 into L_SegNumDummy
4446 from fnd_id_flex_segments
4447 where application_id = 401
4448 and id_flex_code = 'MSTK'
4449 and enabled_flag = 'Y'
4450 and application_column_name = 'SEGMENT9';
4451
4452 EXCEPTION
4453 WHEN NO_DATA_FOUND THEN
4454 FND_MESSAGE.Set_Name('INV',
4455 'INV_INVALID_ITEM_SEG');
4456 FND_MESSAGE.Set_Token('SEGMENT',
4457 'SEGMENT9',
4458 FALSE);
4459 Manage_Error_Code('IN', 'APP-43014',
4460 Curr_Error);
4461 RAISE Error;
4462
4463 WHEN TOO_MANY_ROWS THEN
4464 FND_MESSAGE.Set_Name('INV',
4465 'INV_MULTIPLE_FLEX_SEG');
4466 Manage_Error_Code('IN', 'APP-43014',
4467 Curr_Error);
4468 RAISE Error;
4469 END;
4470
4471 L_SegNumIndex := 0;
4472
4473 for i in 1..L_FlexSeg_Counter loop
4474
4475 L_SegNumIndex := i;
4476
4477 EXIT WHEN L_SegOrder_Array(i) =
4478 L_SegNumDummy;
4479 end loop;
4480
4481
4482 if L_SegNumIndex <= L_FlexSeg_Counter then
4483 L_Segment_Array(L_SegNumIndex) :=
4484 P_Inventory_Item_Segment9;
4485 else
4486 FND_MESSAGE.Set_Name('INV',
4487 'INV_FLEX_SEG_OUT_OF_RANGE');
4488 Manage_Error_Code('IN', 'APP-43014',
4489 Curr_Error);
4490 RAISE Error;
4491
4492 end if;
4493 end if;
4494
4495 if P_Inventory_Item_Segment10 IS NOT NULL THEN
4496 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4497
4498 BEGIN
4499 select segment_num
4500 into L_SegNumDummy
4501 from fnd_id_flex_segments
4502 where application_id = 401
4503 and id_flex_code = 'MSTK'
4504 and enabled_flag = 'Y'
4505 and application_column_name = 'SEGMENT10';
4506
4507 EXCEPTION
4508 WHEN NO_DATA_FOUND THEN
4509 FND_MESSAGE.Set_Name('INV',
4510 'INV_INVALID_ITEM_SEG');
4511 FND_MESSAGE.Set_Token('SEGMENT',
4512 'SEGMENT10',
4513 FALSE);
4514 Manage_Error_Code('IN', 'APP-43014',
4515 Curr_Error);
4516 RAISE Error;
4517
4518 WHEN TOO_MANY_ROWS THEN
4519 FND_MESSAGE.Set_Name('INV',
4520 'INV_MULTIPLE_FLEX_SEG');
4521 Manage_Error_Code('IN', 'APP-43014',
4522 Curr_Error);
4523 RAISE Error;
4524 END;
4525
4526 L_SegNumIndex := 0;
4527
4528 for i in 1..L_FlexSeg_Counter loop
4529
4530 L_SegNumIndex := i;
4531
4532 EXIT WHEN L_SegOrder_Array(i) =
4533 L_SegNumDummy;
4534 end loop;
4535
4536
4537 if L_SegNumIndex <= L_FlexSeg_Counter then
4538 L_Segment_Array(L_SegNumIndex) :=
4539 P_Inventory_Item_Segment10;
4540 else
4541 FND_MESSAGE.Set_Name('INV',
4542 'INV_FLEX_SEG_OUT_OF_RANGE');
4543 Manage_Error_Code('IN', 'APP-43014',
4544 Curr_Error);
4545 RAISE Error;
4546
4547 end if;
4548 end if;
4549
4550 if P_Inventory_Item_Segment11 IS NOT NULL THEN
4551 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4552
4553 BEGIN
4554 select segment_num
4555 into L_SegNumDummy
4556 from fnd_id_flex_segments
4557 where application_id = 401
4558 and id_flex_code = 'MSTK'
4559 and enabled_flag = 'Y'
4560 and application_column_name = 'SEGMENT11';
4561
4562 EXCEPTION
4563 WHEN NO_DATA_FOUND THEN
4564 FND_MESSAGE.Set_Name('INV',
4565 'INV_INVALID_ITEM_SEG');
4566 FND_MESSAGE.Set_Token('SEGMENT',
4567 'SEGMENT11',
4568 FALSE);
4569 Manage_Error_Code('IN', 'APP-43014',
4570 Curr_Error);
4571 RAISE Error;
4572
4573 WHEN TOO_MANY_ROWS THEN
4574 FND_MESSAGE.Set_Name('INV',
4575 'INV_MULTIPLE_FLEX_SEG');
4576 Manage_Error_Code('IN', 'APP-43014',
4577 Curr_Error);
4578 RAISE Error;
4579 END;
4580
4581 L_SegNumIndex := 0;
4582
4583 for i in 1..L_FlexSeg_Counter loop
4584
4585 L_SegNumIndex := i;
4586
4587 EXIT WHEN L_SegOrder_Array(i) =
4588 L_SegNumDummy;
4589 end loop;
4590
4591
4592 if L_SegNumIndex <= L_FlexSeg_Counter then
4593 L_Segment_Array(L_SegNumIndex) :=
4594 P_Inventory_Item_Segment11;
4595 else
4596 FND_MESSAGE.Set_Name('INV',
4597 'INV_FLEX_SEG_OUT_OF_RANGE');
4598 Manage_Error_Code('IN', 'APP-43014',
4599 Curr_Error);
4600 RAISE Error;
4601
4602 end if;
4603 end if;
4604
4605 if P_Inventory_Item_Segment12 IS NOT NULL THEN
4606 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4607
4608 BEGIN
4609 select segment_num
4610 into L_SegNumDummy
4611 from fnd_id_flex_segments
4612 where application_id = 401
4613 and id_flex_code = 'MSTK'
4614 and enabled_flag = 'Y'
4615 and application_column_name = 'SEGMENT12';
4616
4617 EXCEPTION
4618 WHEN NO_DATA_FOUND THEN
4619 FND_MESSAGE.Set_Name('INV',
4620 'INV_INVALID_ITEM_SEG');
4621 FND_MESSAGE.Set_Token('SEGMENT',
4622 'SEGMENT12',
4623 FALSE);
4624 Manage_Error_Code('IN', 'APP-43014',
4625 Curr_Error);
4626 RAISE Error;
4627
4628 WHEN TOO_MANY_ROWS THEN
4629 FND_MESSAGE.Set_Name('INV',
4630 'INV_MULTIPLE_FLEX_SEG');
4631 Manage_Error_Code('IN', 'APP-43014',
4632 Curr_Error);
4633 RAISE Error;
4634 END;
4635
4636 L_SegNumIndex := 0;
4637
4638 for i in 1..L_FlexSeg_Counter loop
4639
4640 L_SegNumIndex := i;
4641
4642 EXIT WHEN L_SegOrder_Array(i) =
4643 L_SegNumDummy;
4644 end loop;
4645
4646
4647 if L_SegNumIndex <= L_FlexSeg_Counter then
4648 L_Segment_Array(L_SegNumIndex) :=
4649 P_Inventory_Item_Segment12;
4650 else
4651 FND_MESSAGE.Set_Name('INV',
4652 'INV_FLEX_SEG_OUT_OF_RANGE');
4653 Manage_Error_Code('IN', 'APP-43014',
4654 Curr_Error);
4655 RAISE Error;
4656
4657 end if;
4658 end if;
4659
4660 if P_Inventory_Item_Segment13 IS NOT NULL THEN
4661 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4662
4663 BEGIN
4664 select segment_num
4665 into L_SegNumDummy
4666 from fnd_id_flex_segments
4667 where application_id = 401
4668 and id_flex_code = 'MSTK'
4669 and enabled_flag = 'Y'
4670 and application_column_name = 'SEGMENT13';
4671
4672 EXCEPTION
4673 WHEN NO_DATA_FOUND THEN
4674 FND_MESSAGE.Set_Name('INV',
4675 'INV_INVALID_ITEM_SEG');
4676 FND_MESSAGE.Set_Token('SEGMENT',
4677 'SEGMENT13',
4678 FALSE);
4679 Manage_Error_Code('IN', 'APP-43014',
4680 Curr_Error);
4681 RAISE Error;
4682
4683 WHEN TOO_MANY_ROWS THEN
4684 FND_MESSAGE.Set_Name('INV',
4685 'INV_MULTIPLE_FLEX_SEG');
4686 Manage_Error_Code('IN', 'APP-43014',
4687 Curr_Error);
4688 RAISE Error;
4689 END;
4690
4691 L_SegNumIndex := 0;
4692
4693 for i in 1..L_FlexSeg_Counter loop
4694
4695 L_SegNumIndex := i;
4696
4697 EXIT WHEN L_SegOrder_Array(i) =
4698 L_SegNumDummy;
4699 end loop;
4700
4701
4702 if L_SegNumIndex <= L_FlexSeg_Counter then
4703 L_Segment_Array(L_SegNumIndex) :=
4704 P_Inventory_Item_Segment13;
4705 else
4706 FND_MESSAGE.Set_Name('INV',
4707 'INV_FLEX_SEG_OUT_OF_RANGE');
4708 Manage_Error_Code('IN', 'APP-43014',
4709 Curr_Error);
4710 RAISE Error;
4711
4712 end if;
4713 end if;
4714
4715 if P_Inventory_Item_Segment14 IS NOT NULL THEN
4716 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4717
4718 BEGIN
4719 select segment_num
4720 into L_SegNumDummy
4721 from fnd_id_flex_segments
4722 where application_id = 401
4723 and id_flex_code = 'MSTK'
4724 and enabled_flag = 'Y'
4725 and application_column_name = 'SEGMENT14';
4726
4727 EXCEPTION
4728 WHEN NO_DATA_FOUND THEN
4729 FND_MESSAGE.Set_Name('INV',
4730 'INV_INVALID_ITEM_SEG');
4731 FND_MESSAGE.Set_Token('SEGMENT',
4732 'SEGMENT14',
4733 FALSE);
4734 Manage_Error_Code('IN', 'APP-43014',
4735 Curr_Error);
4736 RAISE Error;
4737
4738 WHEN TOO_MANY_ROWS THEN
4739 FND_MESSAGE.Set_Name('INV',
4740 'INV_MULTIPLE_FLEX_SEG');
4741 Manage_Error_Code('IN', 'APP-43014',
4742 Curr_Error);
4743 RAISE Error;
4744 END;
4745
4746 L_SegNumIndex := 0;
4747
4748 for i in 1..L_FlexSeg_Counter loop
4749
4750 L_SegNumIndex := i;
4751
4752 EXIT WHEN L_SegOrder_Array(i) =
4753 L_SegNumDummy;
4754 end loop;
4755
4756
4757 if L_SegNumIndex <= L_FlexSeg_Counter then
4758 L_Segment_Array(L_SegNumIndex) :=
4759 P_Inventory_Item_Segment14;
4760 else
4761 FND_MESSAGE.Set_Name('INV',
4762 'INV_FLEX_SEG_OUT_OF_RANGE');
4763 Manage_Error_Code('IN', 'APP-43014',
4764 Curr_Error);
4765 RAISE Error;
4766
4767 end if;
4768 end if;
4769
4770 if P_Inventory_Item_Segment15 IS NOT NULL THEN
4771 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4772
4773 BEGIN
4774 select segment_num
4775 into L_SegNumDummy
4776 from fnd_id_flex_segments
4777 where application_id = 401
4778 and id_flex_code = 'MSTK'
4779 and enabled_flag = 'Y'
4780 and application_column_name = 'SEGMENT15';
4781
4782 EXCEPTION
4783 WHEN NO_DATA_FOUND THEN
4784 FND_MESSAGE.Set_Name('INV',
4785 'INV_INVALID_ITEM_SEG');
4786 FND_MESSAGE.Set_Token('SEGMENT',
4787 'SEGMENT15',
4788 FALSE);
4789 Manage_Error_Code('IN', 'APP-43014',
4790 Curr_Error);
4791 RAISE Error;
4792
4793 WHEN TOO_MANY_ROWS THEN
4794 FND_MESSAGE.Set_Name('INV',
4795 'INV_MULTIPLE_FLEX_SEG');
4796 Manage_Error_Code('IN', 'APP-43014',
4797 Curr_Error);
4798 RAISE Error;
4799 END;
4800
4801 L_SegNumIndex := 0;
4802
4803 for i in 1..L_FlexSeg_Counter loop
4804
4805 L_SegNumIndex := i;
4806
4807 EXIT WHEN L_SegOrder_Array(i) =
4808 L_SegNumDummy;
4809 end loop;
4810
4811
4812 if L_SegNumIndex <= L_FlexSeg_Counter then
4813 L_Segment_Array(L_SegNumIndex) :=
4814 P_Inventory_Item_Segment15;
4815 else
4816 FND_MESSAGE.Set_Name('INV',
4817 'INV_FLEX_SEG_OUT_OF_RANGE');
4818 Manage_Error_Code('IN', 'APP-43014',
4819 Curr_Error);
4820 RAISE Error;
4821
4822 end if;
4823 end if;
4824
4825 if P_Inventory_Item_Segment16 IS NOT NULL THEN
4826 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4827
4828 BEGIN
4829 select segment_num
4830 into L_SegNumDummy
4831 from fnd_id_flex_segments
4832 where application_id = 401
4833 and id_flex_code = 'MSTK'
4834 and enabled_flag = 'Y'
4835 and application_column_name = 'SEGMENT16';
4836
4837 EXCEPTION
4838 WHEN NO_DATA_FOUND THEN
4839 FND_MESSAGE.Set_Name('INV',
4840 'INV_INVALID_ITEM_SEG');
4841 FND_MESSAGE.Set_Token('SEGMENT',
4842 'SEGMENT16',
4843 FALSE);
4844 Manage_Error_Code('IN', 'APP-43014',
4845 Curr_Error);
4846 RAISE Error;
4847
4848 WHEN TOO_MANY_ROWS THEN
4849 FND_MESSAGE.Set_Name('INV',
4850 'INV_MULTIPLE_FLEX_SEG');
4851 Manage_Error_Code('IN', 'APP-43014',
4852 Curr_Error);
4853 RAISE Error;
4854 END;
4855
4856 L_SegNumIndex := 0;
4857
4858 for i in 1..L_FlexSeg_Counter loop
4859
4860 L_SegNumIndex := i;
4861
4862 EXIT WHEN L_SegOrder_Array(i) =
4863 L_SegNumDummy;
4864 end loop;
4865
4866
4867 if L_SegNumIndex <= L_FlexSeg_Counter then
4868 L_Segment_Array(L_SegNumIndex) :=
4869 P_Inventory_Item_Segment16;
4870 else
4871 FND_MESSAGE.Set_Name('INV',
4872 'INV_FLEX_SEG_OUT_OF_RANGE');
4873 Manage_Error_Code('IN', 'APP-43014',
4874 Curr_Error);
4875 RAISE Error;
4876
4877 end if;
4878 end if;
4879
4880 if P_Inventory_Item_Segment17 IS NOT NULL THEN
4881 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4882
4883 BEGIN
4884 select segment_num
4885 into L_SegNumDummy
4886 from fnd_id_flex_segments
4887 where application_id = 401
4888 and id_flex_code = 'MSTK'
4889 and enabled_flag = 'Y'
4890 and application_column_name = 'SEGMENT17';
4891
4892 EXCEPTION
4893 WHEN NO_DATA_FOUND THEN
4894 FND_MESSAGE.Set_Name('INV',
4895 'INV_INVALID_ITEM_SEG');
4896 FND_MESSAGE.Set_Token('SEGMENT',
4897 'SEGMENT17',
4898 FALSE);
4899 Manage_Error_Code('IN', 'APP-43014',
4900 Curr_Error);
4901 RAISE Error;
4902
4903 WHEN TOO_MANY_ROWS THEN
4904 FND_MESSAGE.Set_Name('INV',
4905 'INV_MULTIPLE_FLEX_SEG');
4906 Manage_Error_Code('IN', 'APP-43014',
4907 Curr_Error);
4908 RAISE Error;
4909 END;
4910
4911 L_SegNumIndex := 0;
4912
4913 for i in 1..L_FlexSeg_Counter loop
4914
4915 L_SegNumIndex := i;
4916
4917 EXIT WHEN L_SegOrder_Array(i) =
4918 L_SegNumDummy;
4919 end loop;
4920
4921
4922 if L_SegNumIndex <= L_FlexSeg_Counter then
4923 L_Segment_Array(L_SegNumIndex) :=
4924 P_Inventory_Item_Segment17;
4925 else
4926 FND_MESSAGE.Set_Name('INV',
4927 'INV_FLEX_SEG_OUT_OF_RANGE');
4928 Manage_Error_Code('IN', 'APP-43014',
4929 Curr_Error);
4930 RAISE Error;
4931
4932 end if;
4933 end if;
4934
4935 if P_Inventory_Item_Segment18 IS NOT NULL THEN
4936 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4937
4938 BEGIN
4939 select segment_num
4940 into L_SegNumDummy
4941 from fnd_id_flex_segments
4942 where application_id = 401
4943 and id_flex_code = 'MSTK'
4944 and enabled_flag = 'Y'
4945 and application_column_name = 'SEGMENT18';
4946
4947 EXCEPTION
4948 WHEN NO_DATA_FOUND THEN
4949 FND_MESSAGE.Set_Name('INV',
4950 'INV_INVALID_ITEM_SEG');
4951 FND_MESSAGE.Set_Token('SEGMENT',
4952 'SEGMENT18',
4953 FALSE);
4954 Manage_Error_Code('IN', 'APP-43014',
4955 Curr_Error);
4956 RAISE Error;
4957
4958 WHEN TOO_MANY_ROWS THEN
4959 FND_MESSAGE.Set_Name('INV',
4960 'INV_MULTIPLE_FLEX_SEG');
4961 Manage_Error_Code('IN', 'APP-43014',
4962 Curr_Error);
4963 RAISE Error;
4964 END;
4965
4966 L_SegNumIndex := 0;
4967
4968 for i in 1..L_FlexSeg_Counter loop
4969
4970 L_SegNumIndex := i;
4971
4972 EXIT WHEN L_SegOrder_Array(i) =
4973 L_SegNumDummy;
4974 end loop;
4975
4976
4977 if L_SegNumIndex <= L_FlexSeg_Counter then
4978 L_Segment_Array(L_SegNumIndex) :=
4979 P_Inventory_Item_Segment18;
4980 else
4981 FND_MESSAGE.Set_Name('INV',
4982 'INV_FLEX_SEG_OUT_OF_RANGE');
4983 Manage_Error_Code('IN', 'APP-43014',
4984 Curr_Error);
4985 RAISE Error;
4986
4987 end if;
4988 end if;
4989
4990 if P_Inventory_Item_Segment19 IS NOT NULL THEN
4991 L_TempSeg_Counter := L_TempSeg_Counter + 1;
4992
4993 BEGIN
4994 select segment_num
4995 into L_SegNumDummy
4996 from fnd_id_flex_segments
4997 where application_id = 401
4998 and id_flex_code = 'MSTK'
4999 and enabled_flag = 'Y'
5000 and application_column_name = 'SEGMENT19';
5001
5002 EXCEPTION
5003 WHEN NO_DATA_FOUND THEN
5004 FND_MESSAGE.Set_Name('INV',
5005 'INV_INVALID_ITEM_SEG');
5006 FND_MESSAGE.Set_Token('SEGMENT',
5007 'SEGMENT19',
5008 FALSE);
5009 Manage_Error_Code('IN', 'APP-43014',
5010 Curr_Error);
5011 RAISE Error;
5012
5013 WHEN TOO_MANY_ROWS THEN
5014 FND_MESSAGE.Set_Name('INV',
5015 'INV_MULTIPLE_FLEX_SEG');
5016 Manage_Error_Code('IN', 'APP-43014',
5017 Curr_Error);
5018 RAISE Error;
5019 END;
5020
5021 L_SegNumIndex := 0;
5022
5023 for i in 1..L_FlexSeg_Counter loop
5024
5025 L_SegNumIndex := i;
5026
5027 EXIT WHEN L_SegOrder_Array(i) =
5028 L_SegNumDummy;
5029 end loop;
5030
5031
5032 if L_SegNumIndex <= L_FlexSeg_Counter then
5033 L_Segment_Array(L_SegNumIndex) :=
5034 P_Inventory_Item_Segment19;
5035 else
5036 FND_MESSAGE.Set_Name('INV',
5037 'INV_FLEX_SEG_OUT_OF_RANGE');
5038 Manage_Error_Code('IN', 'APP-43014',
5039 Curr_Error);
5040 RAISE Error;
5041
5042 end if;
5043 end if;
5044
5045 if P_Inventory_Item_Segment20 IS NOT NULL THEN
5046 L_TempSeg_Counter := L_TempSeg_Counter + 1;
5047
5048 BEGIN
5049 select segment_num
5050 into L_SegNumDummy
5051 from fnd_id_flex_segments
5052 where application_id = 401
5053 and id_flex_code = 'MSTK'
5054 and enabled_flag = 'Y'
5055 and application_column_name = 'SEGMENT20';
5056
5057 EXCEPTION
5058 WHEN NO_DATA_FOUND THEN
5059 FND_MESSAGE.Set_Name('INV',
5060 'INV_INVALID_ITEM_SEG');
5061 FND_MESSAGE.Set_Token('SEGMENT',
5062 'SEGMENT20',
5063 FALSE);
5064 Manage_Error_Code('IN', 'APP-43014',
5065 Curr_Error);
5066 RAISE Error;
5067
5068 WHEN TOO_MANY_ROWS THEN
5069 FND_MESSAGE.Set_Name('INV',
5070 'INV_MULTIPLE_FLEX_SEG');
5071 Manage_Error_Code('IN', 'APP-43014',
5072 Curr_Error);
5073 RAISE Error;
5074 END;
5075
5076 L_SegNumIndex := 0;
5077
5078 for i in 1..L_FlexSeg_Counter loop
5079
5080 L_SegNumIndex := i;
5081
5082 EXIT WHEN L_SegOrder_Array(i) =
5083 L_SegNumDummy;
5084 end loop;
5085
5086
5087 if L_SegNumIndex <= L_FlexSeg_Counter then
5088 L_Segment_Array(L_SegNumIndex) :=
5089 P_Inventory_Item_Segment20;
5090 else
5091 FND_MESSAGE.Set_Name('INV',
5092 'INV_FLEX_SEG_OUT_OF_RANGE');
5093 Manage_Error_Code('IN', 'APP-43014',
5094 Curr_Error);
5095 RAISE Error;
5096
5097 end if;
5098 end if;
5099
5100 L_Delimiter := fnd_flex_ext.get_delimiter('INV',
5101 'MSTK',
5102 L_StructNum);
5103
5104 if (L_TempSeg_Counter > 0) then
5105
5106 L_ConcatSegs := fnd_flex_ext.concatenate_segments(
5107 L_FlexSeg_Counter,
5108 L_Segment_Array,
5109 L_Delimiter);
5110 else
5111 FND_MESSAGE.Set_Name('INV',
5112 'INV_FLEX_SEG_MISMATCH');
5113 Manage_Error_Code('IN', 'APP-43014',
5114 Curr_Error);
5115 RAISE Error;
5116 end if;
5117
5118 if L_ConcatSegs is not null then
5119
5120 L_Success := FND_FLEX_KEYVAL.Validate_Segs(
5121 OPERATION => 'FIND_COMBINATION',
5122 APPL_SHORT_NAME => 'INV',
5123 KEY_FLEX_CODE => 'MSTK',
5124 STRUCTURE_NUMBER=> L_StructNum,
5125 CONCAT_SEGMENTS => L_ConcatSegs,
5126 DATA_SET => P_Master_Organization_Id
5127 );
5128
5129 if L_Success then
5130 P_Inventory_Item_Id :=
5131 FND_FLEX_KEYVAL.Combination_Id;
5132
5133 else
5134 P_Inventory_Item_Id := NULL;
5135 end if;
5136
5137 If P_Inventory_Item_Id is NULL then
5138
5139 FND_MESSAGE.Set_Name('INV',
5140 'INV_INVALID_CONTAINER');
5141 FND_MESSAGE.Set_Token('COLUMN',
5142 'INVENTORY_ITEM', FALSE);
5143 Manage_Error_Code('IN', 'APP-43014',
5144 Curr_Error);
5145 RAISE Error;
5146 END IF;
5147
5148 else
5149 FND_MESSAGE.Set_Name('INV',
5150 'INV_CONCAT_SEG_ERROR');
5151 Manage_Error_Code('IN', 'APP-43014',
5152 Curr_Error);
5153 RAISE Error;
5154 end if;
5155
5156 ELSE
5157 FND_MESSAGE.Set_Name('INV', 'INV_NO_ITEM');
5158 Manage_Error_Code('IN', 'APP-43030', Curr_Error);
5159 RAISE Error;
5160 END IF;
5161 --PLM 11.5.10 validation. XRef can be done only for Approved Items
5162 IF (P_Inventory_Item_Id IS NOT NULL) THEN
5163 SELECT Inventory_Item_Id
5164 INTO Temp_Inventory_Item_Id
5165 FROM MTL_SYSTEM_ITEMS MSI
5166 WHERE MSI.Inventory_Item_Id = P_Inventory_Item_Id
5167 AND MSI.Organization_Id = P_Master_Organization_Id
5168 AND NVL(MSI.Approval_Status,'A') = 'A';
5169
5170 END IF;
5171
5172 EXCEPTION
5173
5174 WHEN NO_DATA_FOUND THEN
5175 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_INV_ITEM');
5176 IF (P_Inventory_Item_Id IS NOT NULL) THEN
5177 FND_MESSAGE.Set_Token('COLUMN',
5178 'INVENTORY_ITEM_ID', FALSE);
5179 ELSIF ((P_Inventory_Item_Id IS NULL) AND
5180 (P_Inventory_Item IS NOT NULL)) THEN
5181 FND_MESSAGE.Set_Token('COLUMN',
5182 'INVENTORY_ITEM', FALSE);
5183 ELSE
5184 FND_MESSAGE.Set_Token('COLUMN',
5185 'INVENTORY_ITEM_SEGMENT1 - 20',
5186 FALSE);
5187 END IF;
5188 Manage_Error_Code('IN', 'APP-43031', Curr_Error);
5189 RAISE Error;
5190
5191 WHEN TOO_MANY_ROWS THEN
5192
5193 FND_MESSAGE.Set_Name('INV', 'INV_MULTIPLE_ITEMS');
5194 Manage_Error_Code('IN', 'APP-43056', Curr_Error);
5195 RAISE Error;
5196
5197 END Validate_Inventory_Item;
5198
5199
5200 /*===========================================================================+
5201 +===========================================================================*/
5202 /* These procedures will be shared by both Customer Item Open Interfaces. */
5203 /*===========================================================================+
5204 +===========================================================================*/
5205
5206 PROCEDURE Validate_Inactive_Flag(
5207 P_Inactive_Flag IN OUT NOCOPY Varchar2
5208 ) IS
5209
5210 BEGIN
5211
5212 IF ((P_Inactive_Flag = '1') OR (P_Inactive_Flag = '2')) THEN
5213
5214 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
5215
5216 IF (P_Inactive_Flag = '1') THEN
5217 P_Inactive_Flag := 'Y';
5218 RETURN;
5219 ELSE
5220 P_Inactive_Flag := 'N';
5221 RETURN;
5222 END IF;
5223 ELSE
5224 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_INACTIVE_FLAG');
5225 Manage_Error_Code('IN', 'APP-43032', Curr_Error);
5226 RAISE Error;
5227 END IF;
5228
5229 END Validate_Inactive_Flag;
5230
5231
5232 PROCEDURE Validate_Concurrent_Program ( P_Request_Id IN Number DEFAULT NULL,
5233 P_Program_Application_Id IN Number DEFAULT NULL,
5234 P_Program_Id IN Number DEFAULT NULL,
5235 P_Program_Update_Date IN Date DEFAULT NULL ) IS
5236
5237 BEGIN
5238
5239 IF ((P_Request_Id IS NOT NULL) AND (P_Program_Application_Id IS NOT NULL) AND (P_Program_Id IS NOT NULL)
5240 AND (P_Program_Update_Date IS NOT NULL) AND (P_Program_Update_Date <= SYSDATE)) THEN
5241
5242 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
5243 RETURN;
5244
5245 ELSE
5246
5247 FND_MESSAGE.Set_Name('INV', 'INV_NO_CONCURRENT_PROG_INFO');
5248 Manage_Error_Code('IN', 'APP-43033', Curr_Error);
5249 RAISE Error;
5250
5251 END IF;
5252
5253 END Validate_Concurrent_Program;
5254
5255
5256 PROCEDURE Check_Uniqueness ( P_Origin IN Varchar2 DEFAULT NULL,
5257 P_Customer_Id IN Number DEFAULT NULL,
5258 P_Customer_Item_Number IN Varchar2 DEFAULT NULL,
5259 P_Item_Definition_Level IN Varchar2 DEFAULT NULL,
5260 P_Customer_Category_Code IN Varchar2 DEFAULT NULL,
5261 P_Address_Id IN Number DEFAULT NULL,
5262 P_Customer_Item_Id IN Number DEFAULT NULL,
5263 P_Inventory_Item_Id IN Number DEFAULT NULL,
5264 P_Master_Organization_Id IN Number DEFAULT NULL,
5265 P_Preference_Number IN Number DEFAULT NULL ) IS
5266
5267
5268 Temp_Customer_Id Number := NULL;
5269 Temp_Customer_Item_Number Varchar2(50) := NULL;
5270 Temp_Item_Definition_Level Varchar2(1) := NULL;
5271 Temp_Customer_Category_Code Varchar2(30) := NULL;
5272 Temp_Address_Id Number := NULL;
5273 Temp_Customer_Item_Id Number := NULL;
5274 Temp_Inventory_Item_Id Number := NULL;
5275 Temp_Master_Organization_Id Number := NULL;
5276 Temp_Preference_Number Number := NULL;
5277
5278
5279 BEGIN
5280
5281
5282 IF (P_Origin = 'I') THEN
5283
5284 SELECT Customer_Id, Customer_Item_Number, Item_Definition_Level, Customer_Category_Code, Address_Id
5285 INTO Temp_Customer_Id, Temp_Customer_Item_Number, Temp_Item_Definition_Level, Temp_Customer_Category_Code, Temp_Address_Id
5286 FROM MTL_CUSTOMER_ITEMS MCI
5287 WHERE MCI.Customer_Id = P_Customer_Id
5288 AND MCI.Customer_Item_Number = P_Customer_Item_Number
5289 AND MCI.Item_Definition_Level = P_Item_Definition_Level
5290 AND NVL(MCI.Customer_Category_Code, ' ') = NVL(P_Customer_Category_Code, ' ')
5291 AND NVL(MCI.Address_Id, -1) = NVL(P_Address_Id, -1);
5292 IF (SQL%FOUND) THEN
5293 FND_MESSAGE.Set_Name('INV', 'INV_NON_UNIQUE_CI_RECORD');
5294 Manage_Error_Code('IN', 'APP-43034', Curr_Error);
5295 RAISE Error;
5296 END IF;
5297
5298 ELSE
5299
5300
5301 SELECT Customer_Item_Id, Inventory_Item_Id, Master_Organization_Id
5302 INTO Temp_Customer_Item_Id, Temp_Inventory_Item_Id, Temp_Master_Organization_Id
5303 FROM MTL_CUSTOMER_ITEM_XREFS MCIXRF
5304 WHERE MCIXRF.Customer_Item_Id = P_Customer_Item_Id
5305 AND MCIXRF.Inventory_Item_Id = P_Inventory_Item_Id
5306 AND MCIXRF.Master_Organization_Id = P_Master_Organization_Id
5307 AND Rownum = 1;
5308
5309 IF (SQL%FOUND) THEN
5310
5311 FND_MESSAGE.Set_Name('INV', 'INV_NON_UNIQUE_CI_XREF_RECORD');
5312 FND_MESSAGE.Set_Token('COLUMN1', 'CUSTOMER_ITEM_ID', FALSE);
5313 FND_MESSAGE.Set_Token('COLUMN2', 'MASTER_ORGANIZATION_ID', FALSE);
5314 FND_MESSAGE.Set_Token('COLUMN3', 'INVENTORY_ITEM_ID', FALSE);
5315 Manage_Error_Code('IN', 'APP-43035', Curr_Error);
5316 RAISE Error;
5317
5318 ELSE
5319
5320 SELECT Customer_Item_Id, Master_Organization_Id, Preference_Number
5321 INTO Temp_Customer_Item_Id, Temp_Master_Organization_Id, Temp_Preference_Number
5322 FROM MTL_CUSTOMER_ITEM_XREFS MCIXRF
5323 WHERE MCIXRF.Customer_Item_Id = P_Customer_Item_Id
5324 AND MCIXRF.Master_Organization_Id = P_Master_Organization_Id
5325 AND MCIXRF.Preference_Number = P_Preference_Number
5326 AND Rownum = 1;
5327
5328 IF (SQL%FOUND) THEN
5329
5330 FND_MESSAGE.Set_Name('INV', 'INV_NON_UNIQUE_CI_XREF_RECORD');
5331 FND_MESSAGE.Set_Token('COLUMN1', 'CUSTOMER_ITEM_ID', FALSE);
5332 FND_MESSAGE.Set_Token('COLUMN2', 'MASTER_ORGANIZATION_ID', FALSE);
5333 FND_MESSAGE.Set_Token('COLUMN3', 'PREFERENCE_NUMBER', FALSE);
5334 Manage_Error_Code('IN', 'APP-43035', Curr_Error);
5335 RAISE Error;
5336
5337 END IF;
5338
5339 END IF;
5340
5341 END IF;
5342
5343 EXCEPTION
5344
5345 WHEN NO_DATA_FOUND THEN
5346
5347 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
5348 RETURN;
5349
5350 END Check_Uniqueness;
5351
5352 PROCEDURE Check_Required_Columns ( P_Origin IN Varchar2 DEFAULT NULL,
5353 P_Customer_Id IN Number DEFAULT NULL,
5354 P_Customer_Item_Number IN Varchar2 DEFAULT NULL,
5355 P_Item_Definition_Level IN Varchar2 DEFAULT NULL,
5356 P_Customer_Category_Code IN Varchar2 DEFAULT NULL,
5357 P_Address_Id IN Number DEFAULT NULL,
5358 P_Inactive_Flag IN Varchar2 DEFAULT NULL,
5359 P_Last_Updated_By IN Number DEFAULT NULL,
5360 P_Last_Update_Date IN Date DEFAULT NULL,
5361 P_Created_By IN Number DEFAULT NULL,
5362 P_Creation_Date IN Date DEFAULT NULL,
5363 P_Customer_Item_Id IN Number DEFAULT NULL,
5364 P_Inventory_Item_Id IN Number DEFAULT NULL,
5365 P_Master_Organization_Id IN Number DEFAULT NULL,
5366 P_Preference_Number IN Number DEFAULT NULL ) IS
5367
5368 BEGIN
5369
5370 IF (P_Origin = 'I') THEN
5371
5372 IF ((P_Customer_Id IS NULL) OR
5373 (P_Customer_Item_Number IS NULL) OR
5374 (P_Item_Definition_Level IS NULL) OR
5375 ((P_Item_Definition_Level = '2') AND (P_Customer_Category_Code IS NULL)) OR
5376 ((P_Item_Definition_Level = '3') AND (P_Address_Id IS NULL)) OR
5377 (P_Inactive_Flag IS NULL) OR
5378 (P_Last_Updated_By IS NULL) OR
5379 (P_Last_Update_Date IS NULL) OR
5380 (P_Created_By IS NULL) OR
5381 (P_Creation_Date IS NULL)) THEN
5382
5383 FND_MESSAGE.Set_Name('INV', 'INV_REQUIRED_COLUMNS_MISSING');
5384 Manage_Error_Code('IN', 'APP-43036', Curr_Error);
5385 RAISE Error;
5386
5387 ELSE
5388
5389 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
5390 RETURN;
5391
5392 END IF;
5393
5394 ELSE
5395
5396 IF ((P_Customer_Item_Id IS NULL) OR
5397 (P_Inventory_Item_Id IS NULL) OR
5398 (P_Master_Organization_Id IS NULL) OR
5399 (P_Preference_Number IS NULL) OR
5400 (P_Inactive_Flag IS NULL) OR
5401 (P_Last_Updated_By IS NULL) OR
5402 (P_Last_Update_Date IS NULL) OR
5403 (P_Created_By IS NULL) OR
5404 (P_Creation_Date IS NULL)) THEN
5405
5406 FND_MESSAGE.Set_Name('INV', 'INV_REQUIRED_COLUMNS_MISSING');
5407 Manage_Error_Code('IN', 'APP-43036', Curr_Error);
5408 RAISE Error;
5409
5410 ELSE
5411
5412 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
5413 RETURN;
5414
5415 END IF;
5416
5417 END IF;
5418
5419 END Check_Required_Columns;
5420
5421
5422 PROCEDURE Insert_Row(
5423 P_Origin IN Varchar2 DEFAULT NULL,
5424 P_Last_Update_Date IN Date DEFAULT NULL,
5425 P_Last_Updated_By IN Number DEFAULT NULL,
5426 P_Creation_Date IN Date DEFAULT NULL,
5427 P_Created_By IN Number DEFAULT NULL,
5428 P_Last_Update_Login IN Number DEFAULT NULL,
5429 P_Customer_Id IN Number DEFAULT NULL,
5430 P_Customer_Category_Code IN Varchar2 DEFAULT NULL,
5431 P_Address_Id IN Number DEFAULT NULL,
5432 P_Customer_Item_Number IN Varchar2 DEFAULT NULL,
5433 P_Item_Definition_Level IN Varchar2 DEFAULT NULL,
5434 P_Customer_Item_Desc IN Varchar2 DEFAULT NULL,
5435 P_Model_Customer_Item_Id IN Number DEFAULT NULL,
5436 P_Commodity_Code_Id IN Number DEFAULT NULL,
5437 P_Master_Container_Item_Id IN Number DEFAULT NULL,
5438 P_Container_Item_Org_Id IN Number DEFAULT NULL,
5439 P_Detail_Container_Item_Id IN Number DEFAULT NULL,
5440 P_Min_Fill_Percentage IN Number DEFAULT NULL,
5441 P_Dep_Plan_Required_Flag IN Varchar2 DEFAULT NULL,
5442 P_Dep_Plan_Prior_Bld_Flag IN Varchar2 DEFAULT NULL,
5443 P_Inactive_Flag IN Varchar2 DEFAULT NULL,
5444 P_Attribute_Category IN Varchar2 DEFAULT NULL,
5445 P_Attribute1 IN Varchar2 DEFAULT NULL,
5446 P_Attribute2 IN Varchar2 DEFAULT NULL,
5447 P_Attribute3 IN Varchar2 DEFAULT NULL,
5448 P_Attribute4 IN Varchar2 DEFAULT NULL,
5449 P_Attribute5 IN Varchar2 DEFAULT NULL,
5450 P_Attribute6 IN Varchar2 DEFAULT NULL,
5451 P_Attribute7 IN Varchar2 DEFAULT NULL,
5452 P_Attribute8 IN Varchar2 DEFAULT NULL,
5453 P_Attribute9 IN Varchar2 DEFAULT NULL,
5454 P_Attribute10 IN Varchar2 DEFAULT NULL,
5455 P_Attribute11 IN Varchar2 DEFAULT NULL,
5456 P_Attribute12 IN Varchar2 DEFAULT NULL,
5457 P_Attribute13 IN Varchar2 DEFAULT NULL,
5458 P_Attribute14 IN Varchar2 DEFAULT NULL,
5459 P_Attribute15 IN Varchar2 DEFAULT NULL,
5460 P_Demand_Tolerance_Positive IN Number DEFAULT NULL,
5461 P_Demand_Tolerance_Negative IN Number DEFAULT NULL,
5462 P_Request_Id IN Number DEFAULT NULL,
5463 P_Program_Application_Id IN Number DEFAULT NULL,
5464 P_Program_Id IN Number DEFAULT NULL,
5465 P_Program_Update_Date IN Date DEFAULT NULL,
5466 P_Customer_Item_Id IN Number DEFAULT NULL,
5467 P_Inventory_Item_Id IN Number DEFAULT NULL,
5468 P_Master_Organization_Id IN Number DEFAULT NULL,
5469 P_Preference_Number IN Number DEFAULT NULL
5470 ) IS
5471
5472 BEGIN
5473
5474 IF (P_Origin = 'I') THEN
5475
5476 INSERT
5477 INTO MTL_CUSTOMER_ITEMS(
5478 Customer_Item_Id,
5479 Last_Update_Date,
5480 Last_Updated_By,
5481 Creation_Date,
5482 Created_By,
5483 Last_Update_Login,
5484 Customer_Id,
5485 Customer_Category_Code,
5486 Address_Id,
5487 Customer_Item_Number,
5488 Item_Definition_Level,
5489 Customer_Item_Desc,
5490 Model_Customer_Item_Id,
5491 Commodity_Code_Id,
5492 Master_Container_Item_Id,
5493 Container_Item_Org_Id,
5494 Detail_Container_Item_Id,
5495 Min_Fill_Percentage,
5496 Dep_Plan_Required_Flag,
5497 Dep_Plan_Prior_Bld_Flag,
5498 Inactive_Flag,
5499 Attribute_Category,
5500 Attribute1,
5501 Attribute2,
5502 Attribute3,
5503 Attribute4,
5504 Attribute5,
5505 Attribute6,
5506 Attribute7,
5507 Attribute8,
5508 Attribute9,
5509 Attribute10,
5510 Attribute11,
5511 Attribute12,
5512 Attribute13,
5513 Attribute14,
5514 Attribute15,
5515 Demand_Tolerance_Positive,
5516 Demand_Tolerance_Negative,
5517 Request_Id,
5518 Program_Application_Id,
5519 Program_Id,
5520 Program_Update_Date
5521 )
5522 VALUES
5523 (
5524 MTL_CUSTOMER_ITEMS_S.Nextval,
5525 SYSDATE,
5526 NVL(P_Last_Updated_By, -1),
5527 SYSDATE,
5528 NVL(P_Created_By, -1),
5529 NVL(P_Last_Update_Login,-1),
5530 P_Customer_Id,
5531 P_Customer_Category_Code,
5532 P_Address_Id,
5533 P_Customer_Item_Number,
5534 P_Item_Definition_Level,
5535 trim(P_Customer_Item_Desc),
5536 P_Model_Customer_Item_Id,
5537 P_Commodity_Code_Id,
5538 P_Master_Container_Item_Id,
5539 P_Container_Item_Org_Id,
5540 P_Detail_Container_Item_Id,
5541 P_Min_Fill_Percentage,
5542 P_Dep_Plan_Required_Flag,
5543 P_Dep_Plan_Prior_Bld_Flag,
5544 P_Inactive_Flag,
5545 P_Attribute_Category,
5546 P_Attribute1,
5547 P_Attribute2,
5548 P_Attribute3,
5549 P_Attribute4,
5550 P_Attribute5,
5551 P_Attribute6,
5552 P_Attribute7,
5553 P_Attribute8,
5554 P_Attribute9,
5555 P_Attribute10,
5556 P_Attribute11,
5557 P_Attribute12,
5558 P_Attribute13,
5559 P_Attribute14,
5560 P_Attribute15,
5561 P_Demand_Tolerance_Positive,
5562 P_Demand_Tolerance_Negative,
5563 P_Request_Id,
5564 P_Program_Application_Id,
5565 P_Program_Id,
5566 P_Program_Update_Date
5567 );
5568
5569 ELSE
5570
5571 INSERT
5572 INTO MTL_CUSTOMER_ITEM_XREFS
5573 (
5574 Customer_Item_Id,
5575 Inventory_Item_Id,
5576 Master_Organization_Id,
5577 Preference_Number,
5578 Inactive_Flag,
5579 Last_Update_Date,
5580 Last_Updated_By,
5581 Creation_Date,
5582 Created_By,
5583 Last_Update_Login,
5584 Attribute_Category,
5585 Attribute1,
5586 Attribute2,
5587 Attribute3,
5588 Attribute4,
5589 Attribute5,
5590 Attribute6,
5591 Attribute7,
5592 Attribute8,
5593 Attribute9,
5594 Attribute10,
5595 Attribute11,
5596 Attribute12,
5597 Attribute13,
5598 Attribute14,
5599 Attribute15,
5600 Request_Id,
5601 Program_Application_Id,
5602 Program_Id,
5603 Program_Update_Date
5604 )
5605 VALUES
5606 (
5607 P_Customer_Item_Id,
5608 P_Inventory_Item_Id,
5609 P_Master_Organization_Id,
5610 P_Preference_Number,
5611 P_Inactive_Flag,
5612 SYSDATE,
5613 NVL(P_Last_Updated_By, -1),
5614 SYSDATE,
5615 NVL(P_Created_By, -1),
5616 NVL(P_Last_Update_Login, -1),
5617 P_Attribute_Category,
5618 P_Attribute1,
5619 P_Attribute2,
5620 P_Attribute3,
5621 P_Attribute4,
5622 P_Attribute5,
5623 P_Attribute6,
5624 P_Attribute7,
5625 P_Attribute8,
5626 P_Attribute9,
5627 P_Attribute10,
5628 P_Attribute11,
5629 P_Attribute12,
5630 P_Attribute13,
5631 P_Attribute14,
5632 P_Attribute15,
5633 P_Request_Id,
5634 P_Program_Application_Id,
5635 P_Program_Id,
5636 P_Program_Update_Date
5637 );
5638
5639 END IF;
5640
5641 IF ((SQL%FOUND) AND (SQL%ROWCOUNT = 1)) THEN
5642
5643 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
5644 RETURN;
5645
5646 END IF;
5647
5648 EXCEPTION
5649
5650 WHEN NO_DATA_FOUND THEN
5651
5652 FND_MESSAGE.Set_Name('INV', 'INV_NO_ROW_INSERTED');
5653 Manage_Error_Code('IN', 'APP-43025', Curr_Error);
5654 RAISE Error;
5655
5656 END Insert_Row;
5657
5658
5659 PROCEDURE Delete_Row(
5660 P_Origin IN Varchar2 DEFAULT NULL,
5661 P_Delete_Record IN Varchar2 DEFAULT NULL,
5662 P_Temp_RowId IN Varchar2 DEFAULT NULL
5663 ) IS
5664
5665 BEGIN
5666
5667 IF ((P_Origin = 'I') AND (UPPER(P_Delete_Record) = 'Y')) THEN
5668
5669 DELETE
5670 FROM MTL_CI_INTERFACE
5671 WHERE Rowid = P_Temp_RowId;
5672
5673 ELSIF ((P_Origin = 'X') AND (UPPER(P_Delete_Record) = 'Y')) THEN
5674
5675 DELETE
5676 FROM MTL_CI_XREFS_INTERFACE
5677 WHERE Rowid = P_Temp_RowId;
5678
5679 ELSIF ((P_Origin = 'I') AND (UPPER(P_Delete_Record) = 'N')) THEN
5680
5681 UPDATE MTL_CI_INTERFACE
5682 SET Process_Mode = 3
5683 WHERE Rowid = P_Temp_RowId;
5684
5685 ELSIF ((P_Origin = 'X') AND (UPPER(P_Delete_Record) = 'N')) THEN
5686
5687 UPDATE MTL_CI_XREFS_INTERFACE
5688 SET Process_Mode = 3
5689 WHERE Rowid = P_Temp_RowId;
5690
5691 ELSE
5692 NULL;
5693 END IF;
5694
5695 IF ((SQL%FOUND) AND (SQL%ROWCOUNT = 1)) THEN
5696
5697 Manage_Error_Code('IN', 'APP-00000', Curr_Error);
5698 RETURN;
5699 END IF;
5700
5701 EXCEPTION
5702
5703 WHEN NO_DATA_FOUND THEN
5704
5705 FND_MESSAGE.Set_Name('INV', 'INV_NO_ROW_DELETED');
5706 Manage_Error_Code('IN', 'APP-43026', Curr_Error);
5707 RAISE Error;
5708
5709 END Delete_Row;
5710
5711 PROCEDURE Manage_Error_Code(
5712 P_Action IN Varchar2 DEFAULT 'IN',
5713 Error_Code IN Varchar2 DEFAULT NULL,
5714 Curr_Error OUT NOCOPY Varchar2
5715 ) IS
5716
5717 BEGIN
5718 IF (P_Action = 'IN') THEN
5719 Current_Error_Code := Error_Code ;
5720 ELSIF (P_Action = 'OUT') THEN
5721 Curr_Error := Current_Error_Code;
5722 END IF;
5723 END Manage_Error_Code;
5724
5725
5726 END INVCIINT;