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