[Home] [Help]
PACKAGE BODY: APPS.INV_CUSTOMER_ITEM_GRP
Source
1 PACKAGE BODY INV_CUSTOMER_ITEM_GRP as
2 /* $Header: INVICSDB.pls 120.6 2006/05/03 22:42:40 swshukla ship $ */
3
4 PROCEDURE CI_Attribute_Value(
5 Z_Customer_Item_Id IN Number DEFAULT NULL,
6 Z_Customer_Id IN Number DEFAULT NULL,
7 Z_Customer_Category_Code IN Varchar2 DEFAULT NULL,
8 Z_Address_Id IN Number DEFAULT NULL,
9 Z_Customer_Item_Number IN Varchar2 DEFAULT NULL,
10 Z_Inventory_Item_Id IN Number DEFAULT NULL,
11 Z_Organization_Id IN Number DEFAULT NULL,
12 Attribute_Name IN Varchar2 DEFAULT NULL,
13 Error_Code OUT NOCOPY Varchar2,
14 Error_Flag OUT NOCOPY Varchar2,
15 Error_Message OUT NOCOPY Varchar2,
16 Attribute_Value OUT NOCOPY Varchar2
17 ) IS
18 X_Customer_Item_Id Number := NULL;
19 X_Customer_Id Number := NULL;
20 X_Customer_Category_Code Varchar2(30) := NULL;
21 X_Address_Id Number := NULL;
22 X_Customer_Item_Number Varchar2(50) := NULL;
23 X_Item_Definition_Level Varchar2(1) := NULL;
24 X_Customer_Item_Desc Varchar2(240) := NULL;
25 X_Model_Customer_Item_Id Number := NULL;
26 X_Commodity_Code_Id Number := NULL;
27 X_Master_Container_Item_Id Number := NULL;
28 X_Container_Item_Org_Id Number := NULL;
29 X_Detail_Container_Item_Id Number := NULL;
30 X_Min_Fill_Percentage Number := NULL;
31 X_Dep_Plan_Required_Flag Varchar2(1) := NULL;
32 X_Dep_Plan_Prior_Bld_Flag Varchar2(1) := NULL;
33 X_Demand_Tolerance_Positive Number := NULL;
34 X_Demand_Tolerance_Negative Number := NULL;
35 X_Attribute_Category Varchar2(30) := NULL;
36 X_Attribute1 Varchar2(150) := NULL;
37 X_Attribute2 Varchar2(150) := NULL;
38 X_Attribute3 Varchar2(150) := NULL;
39 X_Attribute4 Varchar2(150) := NULL;
40 X_Attribute5 Varchar2(150) := NULL;
41 X_Attribute6 Varchar2(150) := NULL;
42 X_Attribute7 Varchar2(150) := NULL;
43 X_Attribute8 Varchar2(150) := NULL;
44 X_Attribute9 Varchar2(150) := NULL;
45 X_Attribute10 Varchar2(150) := NULL;
46 X_Attribute11 Varchar2(150) := NULL;
47 X_Attribute12 Varchar2(150) := NULL;
48 X_Attribute13 Varchar2(150) := NULL;
49 X_Attribute14 Varchar2(150) := NULL;
50 X_Attribute15 Varchar2(150) := NULL;
51 X_Inventory_Item_Id Number := NULL;
52 X_Master_Organization_Id Number := NULL;
53 X_Preference_Number Number := NULL;
54 X_Error_Code Varchar2(9) := NULL;
55 X_Error_Flag Varchar2(1) := NULL;
56 X_Error_Message Varchar2(2000) := NULL;
57 Attribute_Variable Varchar2(2000) := NULL;
58 BEGIN
59
60 IF ((Z_Customer_Id IS NOT NULL)
61 -- rdevakum, fix for bug 466738, address id may not be required from now on.
62 -- AND (Z_Address_Id IS NOT NULL)
63 AND (Z_Organization_Id IS NOT NULL) AND (Attribute_Name IS NOT NULL)
64 AND ((Z_Customer_Item_Id IS NOT NULL) OR
65 (Z_Customer_Item_Number IS NOT NULL))) THEN
66
67 IF UPPER(Attribute_Name) IN (
68 'CUSTOMER_ITEM_ID', 'CUSTOMER_ID',
69 'CUSTOMER_CATEGORY_CODE', 'ADDRESS_ID',
70 'CUSTOMER_ITEM_NUMBER', 'ITEM_DEFINITION_LEVEL',
71 'CUSTOMER_ITEM_DESC', 'MODEL_CUSTOMER_ITEM_ID',
72 'COMMODITY_CODE_ID', 'MASTER_CONTAINER_ITEM_ID',
73 'CONTAINER_ITEM_ORG_ID', 'DETAIL_CONTAINER_ITEM_ID',
74 'MIN_FILL_PERCENTAGE', 'DEP_PLAN_REQUIRED_FLAG',
75 'DEP_PLAN_PRIOR_BLD_FLAG', 'DEMAND_TOLERANCE_POSITIVE',
76 'DEMAND_TOLERANCE_NEGATIVE', 'ATTRIBUTE_CATEGORY',
77 'ATTRIBUTE1', 'ATTRIBUTE2',
78 'ATTRIBUTE3', 'ATTRIBUTE4',
79 'ATTRIBUTE5', 'ATTRIBUTE6',
80 'ATTRIBUTE7', 'ATTRIBUTE8',
81 'ATTRIBUTE9', 'ATTRIBUTE10',
82 'ATTRIBUTE11', 'ATTRIBUTE12',
83 'ATTRIBUTE13', 'ATTRIBUTE14',
84 'ATTRIBUTE15', 'INVENTORY_ITEM_ID',
85 'MASTER_ORGANIZATION_ID', 'PREFERENCE_NUMBER' ) THEN
86
87 Fetch_Attributes(
88 Z_Address_Id, Z_Customer_Category_Code,
89 Z_Customer_Id, Z_Customer_Item_Number,
90 Z_Organization_Id, Z_Customer_Item_Id,
91 Z_Inventory_Item_Id, X_Customer_Item_Id,
92 X_Customer_Id, X_Customer_Category_Code,
93 X_Address_Id, X_Customer_Item_Number,
94 X_Item_Definition_Level, X_Customer_Item_Desc,
95 X_Model_Customer_Item_Id, X_Commodity_Code_Id,
96 X_Master_Container_Item_Id, X_Container_Item_Org_Id,
97 X_Detail_Container_Item_Id, X_Min_Fill_Percentage,
98 X_Dep_Plan_Required_Flag, X_Dep_Plan_Prior_Bld_Flag,
99 X_Demand_Tolerance_Positive,
100 X_Demand_Tolerance_Negative,
101 X_Attribute_Category, X_Attribute1,
102 X_Attribute2, X_Attribute3,
103 X_Attribute4, X_Attribute5,
104 X_Attribute6, X_Attribute7,
105 X_Attribute8, X_Attribute9,
106 X_Attribute10, X_Attribute11,
107 X_Attribute12, X_Attribute13,
108 X_Attribute14, X_Attribute15,
109 X_Inventory_Item_Id, X_Master_Organization_Id,
110 X_Preference_Number, X_Error_Code,
111 X_Error_Flag, X_Error_Message);
112
113
114
115 /* Attribute_Variable := DECODE(
116 UPPER(Attribute_Name),
117 'CUSTOMER_ITEM_ID', to_char(X_Customer_Item_Id),
118 'CUSTOMER_ID', to_char(X_Customer_Id),
119 'CUSTOMER_CATEGORY_CODE' X_Customer_Category_Code,
120 'ADDRESS_ID', to_char(X_Address_Id),
121 'CUSTOMER_ITEM_NUMBER', X_Customer_Item_Number,
122 'ITEM_DEFINITION_LEVEL', X_Item_Definition_Level,
123 'CUSTOMER_ITEM_DESC', X_Customer_Item_Desc,
124 'MODEL_CUSTOMER_ITEM_ID',
125 to_char(X_Model_Customer_Item_Id),
126 'COMMODITY_CODE_ID',
127 to_char(X_Commodity_Code_Id),
128 'MASTER_CONTAINER_ITEM_ID',
129 to_char(X_Master_Container_Item_Id),
130 'CONTAINER_ITEM_ORG_ID',
131 to_char(X_Container_Item_Org_Id),
132 'DETAIL_CONTAINER_ITEM_ID',
133 to_char(X_Detail_Container_Item_Id),
134 'MIN_FILL_PERCENTAGE',
135 to_char(X_Min_Fill_Percentage),
136 'DEP_PLAN_REQUIRED_FLAG', X_Dep_Plan_Required_Flag,
137 'DEP_PLAN_PRIOR_BLD_FLAG', X_Dep_Plan_Prior_Bld_Flag,
138 'DEMAND_TOLERANCE_POSITIVE',
139 to_char(X_Demand_Tolerance_Positive),
140 'DEMAND_TOLERANCE_NEGATIVE',
141 to_char(X_Demand_Tolerance_Negative),
142 'ATTRIBUTE_CATEGORY', X_Attribute_Category,
143 'ATTRIBUTE1', X_Attribute1,
144 'ATTRIBUTE2', X_Attribute2,
145 'ATTRIBUTE3', X_Attribute3,
146 'ATTRIBUTE4', X_Attribute4,
147 'ATTRIBUTE5', X_Attribute5,
148 'ATTRIBUTE6', X_Attribute6,
149 'ATTRIBUTE7', X_Attribute7,
150 'ATTRIBUTE8', X_Attribute8,
151 'ATTRIBUTE9', X_Attribute9,
152 'ATTRIBUTE10', X_Attribute10,
153 'ATTRIBUTE11', X_Attribute11,
154 'ATTRIBUTE12', X_Attribute12,
155 'ATTRIBUTE13', X_Attribute13,
156 'ATTRIBUTE14', X_Attribute14,
157 'ATTRIBUTE15', X_Attribute15,
158 'INVENTORY_ITEM_ID',
159 to_char(X_Inventory_Item_Id),
160 'MASTER_ORGANIZATION_ID',
161 to_char(X_Master_Organization_Id),
162 'PREFERENCE_NUMBER',
163 to_char(X_Preference_Number) );
164 */
165
166 if UPPER(Attribute_Name) = 'CUSTOMER_ITEM_ID' then
167 Attribute_Variable := to_char(X_Customer_Item_Id);
168 elsif UPPER(Attribute_Name) = 'CUSTOMER_ID' then
169 Attribute_Variable := to_char(X_Customer_Id);
170 elsif UPPER(Attribute_Name) = 'CUSTOMER_CATEGORY_CODE' then
171 Attribute_Variable := X_Customer_Category_Code;
172 elsif UPPER(Attribute_Name) = 'ADDRESS_ID' then
173 Attribute_Variable := to_char(X_Address_Id);
174 elsif UPPER(Attribute_Name) = 'CUSTOMER_ITEM_NUMBER' then
175 Attribute_Variable := X_Customer_Item_Number;
176 elsif UPPER(Attribute_Name) = 'ITEM_DEFINITION_LEVEL' then
177 Attribute_Variable := X_Item_Definition_Level;
178 elsif UPPER(Attribute_Name) = 'CUSTOMER_ITEM_DESC' then
179 Attribute_Variable := X_Customer_Item_Desc;
180 elsif UPPER(Attribute_Name) = 'MODEL_CUSTOMER_ITEM_ID' then
181 Attribute_Variable := to_char(X_Model_Customer_Item_Id);
182 elsif UPPER(Attribute_Name) = 'COMMODITY_CODE_ID' then
183 Attribute_Variable := to_char(X_Commodity_Code_Id);
184 elsif UPPER(Attribute_Name) = 'MASTER_CONTAINER_ITEM_ID' then
185 Attribute_Variable := to_char(X_Master_Container_Item_Id);
186 elsif UPPER(Attribute_Name) = 'CONTAINER_ITEM_ORG_ID' then
187 Attribute_Variable := to_char(X_Container_Item_Org_Id);
188 elsif UPPER(Attribute_Name) = 'DETAIL_CONTAINER_ITEM_ID' then
189 Attribute_Variable := to_char(X_Detail_Container_Item_Id);
190 elsif UPPER(Attribute_Name) = 'MIN_FILL_PERCENTAGE' then
191 Attribute_Variable := to_char(X_Min_Fill_Percentage);
192 elsif UPPER(Attribute_Name) = 'DEP_PLAN_REQUIRED_FLAG' then
193 Attribute_Variable := X_Dep_Plan_Required_Flag;
194 elsif UPPER(Attribute_Name) = 'DEP_PLAN_PRIOR_BLD_FLAG' then
195 Attribute_Variable := X_Dep_Plan_Prior_Bld_Flag;
196 elsif UPPER(Attribute_Name) = 'DEMAND_TOLERANCE_POSITIVE' then
197 Attribute_Variable := to_char(X_Demand_Tolerance_Positive);
198 elsif UPPER(Attribute_Name) = 'DEMAND_TOLERANCE_NEGATIVE' then
199 Attribute_Variable := to_char(X_Demand_Tolerance_Negative);
200 elsif UPPER(Attribute_Name) = 'ATTRIBUTE_CATEGORY' then
201 Attribute_Variable := X_Attribute_Category;
202 elsif UPPER(Attribute_Name) = 'ATTRIBUTE1' then
203 Attribute_Variable := X_Attribute1;
204 elsif UPPER(Attribute_Name) = 'ATTRIBUTE2' then
205 Attribute_Variable := X_Attribute2;
206 elsif UPPER(Attribute_Name) = 'ATTRIBUTE3' then
207 Attribute_Variable := X_Attribute3;
208 elsif UPPER(Attribute_Name) = 'ATTRIBUTE4' then
209 Attribute_Variable := X_Attribute4;
210 elsif UPPER(Attribute_Name) = 'ATTRIBUTE5' then
211 Attribute_Variable := X_Attribute5;
212 elsif UPPER(Attribute_Name) = 'ATTRIBUTE6' then
213 Attribute_Variable := X_Attribute6;
214 elsif UPPER(Attribute_Name) = 'ATTRIBUTE7' then
215 Attribute_Variable := X_Attribute7;
216 elsif UPPER(Attribute_Name) = 'ATTRIBUTE8' then
217 Attribute_Variable := X_Attribute8;
218 elsif UPPER(Attribute_Name) = 'ATTRIBUTE9' then
219 Attribute_Variable := X_Attribute9;
220 elsif UPPER(Attribute_Name) = 'ATTRIBUTE10' then
221 Attribute_Variable := X_Attribute10;
222 elsif UPPER(Attribute_Name) = 'ATTRIBUTE11' then
223 Attribute_Variable := X_Attribute11;
224 elsif UPPER(Attribute_Name) = 'ATTRIBUTE12' then
225 Attribute_Variable := X_Attribute12;
226 elsif UPPER(Attribute_Name) = 'ATTRIBUTE13' then
227 Attribute_Variable := X_Attribute13;
228 elsif UPPER(Attribute_Name) = 'ATTRIBUTE14' then
229 Attribute_Variable := X_Attribute14;
230 elsif UPPER(Attribute_Name) = 'ATTRIBUTE15' then
231 Attribute_Variable := X_Attribute15;
232 elsif UPPER(Attribute_Name) = 'INVENTORY_ITEM_ID' then
233 Attribute_Variable := to_char(X_Inventory_Item_Id);
234 elsif UPPER(Attribute_Name) = 'MASTER_ORGANIZATION_ID' then
235 Attribute_Variable := to_char(X_Master_Organization_Id);
236 elsif UPPER(Attribute_Name) = 'PREFERENCE_NUMBER' then
237 Attribute_Variable := to_char(X_Preference_Number);
238 end if;
239
240 Error_Code := X_Error_Code;
241 Error_Flag := X_Error_Flag;
242 Error_Message := X_Error_Message;
243
244 IF ((X_Error_Code = 'APP-00000') AND
245 (Attribute_Variable IS NULL)) THEN
246
247 FND_MESSAGE.Set_Name('INV',
248 'INV_NULL_ATTRIBUTE_VALUE');
249 Error_Code := 'APP-43041';
250 Error_Flag := 'W';
251 Error_Message := FND_MESSAGE.Get;
252 RETURN;
253 ELSE
254 Attribute_Value := Attribute_Variable;
255
256 END IF;
257
258 ELSE
259 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_COLUMN_NAME');
260 Error_Code := 'APP-43043';
261 Error_Flag := 'Y';
262 Error_Message := FND_MESSAGE.Get;
263 RETURN;
264 END IF;
265
266 ELSE
267
268 FND_MESSAGE.Set_Name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
269 Error_Code := 'APP-43042';
270 Error_Flag := 'Y';
271 Error_Message := FND_MESSAGE.Get;
272 RETURN;
273 END IF;
274
275 EXCEPTION
276
277 WHEN NO_DATA_FOUND THEN
278
279 FND_MESSAGE.Set_Name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
280 Error_Code := 'APP-43042';
281 Error_Flag := 'Y';
282 Error_Message := FND_MESSAGE.Get;
283 RETURN;
284
285 WHEN OTHERS THEN
286
287 Error_Code := SQLCODE;
288 Error_Flag := 'Y';
289 Error_Message := SQLCODE || ' - ' || SUBSTR(SQLERRM, 1, 240);
290 RETURN;
291
292 END CI_Attribute_Value;
293
294
295 PROCEDURE Fetch_Attributes(
296 Y_Address_Id IN Number DEFAULT NULL,
297 Y_Customer_Category_Code IN Varchar2 DEFAULT NULL,
298 Y_Customer_Id IN Number DEFAULT NULL,
299 Y_Customer_Item_Number IN Varchar2 DEFAULT NULL,
300 Y_Organization_Id IN Number DEFAULT NULL,
301 Y_Customer_Item_Id IN Number DEFAULT NULL,
302 Y_Inventory_Item_Id IN Number DEFAULT NULL,
303 X_Customer_Item_Id OUT NOCOPY Number,
304 X_Customer_Id OUT NOCOPY Number,
305 X_Customer_Category_Code OUT NOCOPY Varchar2,
306 X_Address_Id OUT NOCOPY Number,
307 X_Customer_Item_Number OUT NOCOPY Varchar2,
308 X_Item_Definition_Level OUT NOCOPY Varchar2,
309 X_Customer_Item_Desc OUT NOCOPY Varchar2,
310 X_Model_Customer_Item_Id OUT NOCOPY Number,
311 X_Commodity_Code_Id OUT NOCOPY Number,
312 X_Master_Container_Item_Id OUT NOCOPY Number,
313 X_Container_Item_Org_Id OUT NOCOPY Number,
314 X_Detail_Container_Item_Id OUT NOCOPY Number,
315 X_Min_Fill_Percentage OUT NOCOPY Number,
316 X_Dep_Plan_Required_Flag OUT NOCOPY Varchar2,
317 X_Dep_Plan_Prior_Bld_Flag OUT NOCOPY Varchar2,
318 X_Demand_Tolerance_Positive OUT NOCOPY Number,
319 X_Demand_Tolerance_Negative OUT NOCOPY Number,
320 X_Attribute_Category OUT NOCOPY Varchar2,
321 X_Attribute1 OUT NOCOPY Varchar2,
322 X_Attribute2 OUT NOCOPY Varchar2,
323 X_Attribute3 OUT NOCOPY Varchar2,
324 X_Attribute4 OUT NOCOPY Varchar2,
325 X_Attribute5 OUT NOCOPY Varchar2,
326 X_Attribute6 OUT NOCOPY Varchar2,
327 X_Attribute7 OUT NOCOPY Varchar2,
328 X_Attribute8 OUT NOCOPY Varchar2,
329 X_Attribute9 OUT NOCOPY Varchar2,
330 X_Attribute10 OUT NOCOPY Varchar2,
331 X_Attribute11 OUT NOCOPY Varchar2,
332 X_Attribute12 OUT NOCOPY Varchar2,
333 X_Attribute13 OUT NOCOPY Varchar2,
334 X_Attribute14 OUT NOCOPY Varchar2,
335 X_Attribute15 OUT NOCOPY Varchar2,
336 X_Inventory_Item_Id OUT NOCOPY Number,
337 X_Master_Organization_Id OUT NOCOPY Number,
338 X_Preference_Number OUT NOCOPY Number,
339 X_Error_Code OUT NOCOPY Varchar2,
340 X_Error_Flag OUT NOCOPY Varchar2,
341 X_Error_Message OUT NOCOPY Varchar2
342 ) IS
343
344 Temp_Customer_Item_Id Number := NULL;
345 Temp_Master_Organization_Id Number := NULL;
346 Temp_Inventory_Item_Id Number := NULL;
347 Temp_Inactive_Flag Varchar2(1) := NULL;
348 Temp_Address_Id Number := NULL;
349 Temp_Item_Definition_Level Varchar2(1) := NULL;
350 Temp_Customer_Category_Code Varchar2(30) := NULL;
351 RA_Address_Category Varchar2(30) := NULL;
352
353 l_Customer_Id NUMBER;
354
355 --rdevakum, fix for bug 466738
356 Temp_Level_1 Number := 1;
357 Temp_Level_2 Number := 2;
358 Temp_Level_3 Number := 3;
359
360 --rdevakum, fix for bug 466738, added where clause on
361 --item_definition_level. if addr id is passed null, consider
362 --customer level only.
363 CURSOR CI_Cur (v_Customer_Id IN NUMBER) IS
364 SELECT Customer_Item_Id, Address_Id, Customer_Category_Code,
365 Inactive_Flag, Item_Definition_Level
366 FROM MTL_CUSTOMER_ITEMS
367 WHERE Customer_Id = v_Customer_Id
368 AND Item_Definition_Level in
369 (Temp_level_1, Temp_level_2, Temp_level_3)
370 AND ( (Y_Customer_Item_Id is NULL AND
371 Customer_Item_Number = Y_Customer_Item_Number)
372 OR Customer_Item_Id = Y_Customer_Item_Id)
373 ORDER BY Item_Definition_Level DESC;
374
375 CURSOR CI_XREF_Cur (TmpCustItemId NUMBER) IS
379 AND Inventory_Item_Id =
376 SELECT Master_Organization_Id, Inventory_Item_Id, Inactive_Flag
377 FROM MTL_CUSTOMER_ITEM_XREFS
378 WHERE Customer_Item_Id = TmpCustItemId
380 NVL(Y_Inventory_Item_Id, Inventory_Item_Id)
381 AND Master_Organization_Id =
382 (SELECT Master_Organization_Id
383 FROM MTL_PARAMETERS
384 WHERE Organization_Id = Y_Organization_Id)
385 ORDER BY Preference_Number ASC;
386
387
388 CURSOR CI_ATTR_VAL_Cur (
389 TmpCustItemId NUMBER,
390 TmpInvItemId NUMBER, TmpMstrOrgId NUMBER) IS
391 SELECT MCIXRF.Customer_Item_Id,
392 MCI.Customer_Item_Number,
393 MCI.Customer_Category_Code,
394 MCI.Customer_Id,
395 MCI.Address_Id,
396 MCI.Item_Definition_Level,
397 MCI.Customer_Item_Desc,
398 MCI.Model_Customer_Item_Id,
399 MCI.Commodity_Code_Id,
400 MCI.Master_Container_Item_Id,
401 MCI.Container_Item_Org_Id,
402 MCI.Detail_Container_Item_Id,
403 MCI.Min_Fill_Percentage,
404 MCI.Dep_Plan_Required_Flag,
405 MCI.Dep_Plan_Prior_Bld_Flag,
406 MCI.Demand_Tolerance_Positive,
407 MCI.Demand_Tolerance_Negative,
408 MCIXRF.Inventory_Item_Id,
409 MCIXRF.Master_Organization_Id,
410 MCIXRF.Preference_Number,
411 MCI.Attribute_Category,
412 MCI.Attribute1,
413 MCI.Attribute2,
414 MCI.Attribute3,
415 MCI.Attribute4,
416 MCI.Attribute5,
417 MCI.Attribute6,
418 MCI.Attribute7,
419 MCI.Attribute8,
420 MCI.Attribute9,
421 MCI.Attribute10,
422 MCI.Attribute11,
423 MCI.Attribute12,
424 MCI.Attribute13,
425 MCI.Attribute14,
426 MCI.Attribute15
427 FROM MTL_SYSTEM_ITEMS MSI,
428 MTL_CUSTOMER_ITEM_XREFS MCIXRF,
429 MTL_CUSTOMER_ITEMS MCI
430 WHERE MCIXRF.Customer_Item_Id = TmpCustItemId
431 AND MCIXRF.Inventory_Item_Id = TmpInvItemId
432 AND MCIXRF.Master_Organization_Id = TmpMstrOrgId
433 AND MCI.Customer_Item_Id = MCIXRF.Customer_item_Id
434 AND MSI.Inventory_Item_Id = MCIXRF.Inventory_Item_Id
435 AND MSI.Organization_id = MCIXRF.Master_Organization_Id;
436
437 Recinfo CI_ATTR_VAL_Cur%ROWTYPE;
438
439
440 CURSOR RA_Addresses_Cur (v_Address_Id IN NUMBER) IS
441
442 /* RA_ADDRESSES has been scrapped - re-writing cursor def -Anmurali
443 SELECT CUSTOMER_ID
444 FROM RA_ADDRESSES
445 WHERE ADDRESS_ID = v_Address_Id; */
446
447 /* SELECT ACCT_SITE.CUST_ACCOUNT_ID
448 FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
449 HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
450 WHERE ACCT_SITE.CUST_ACCT_SITE_ID = v_Address_Id
451 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
452 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)
453 AND NVL(ACCT_SITE.ORG_ID, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,
454 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),- 99)) =
455 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
456 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),- 99);*/
457 SELECT ACCT_SITE.CUST_ACCOUNT_ID
458 FROM HZ_CUST_ACCT_SITES_ALL ACCT_SITE
459 WHERE ACCT_SITE.CUST_ACCT_SITE_ID = v_Address_Id;
460
461 BEGIN
462
463 IF ( (Y_Customer_Id IS NULL)
464 OR (Y_Organization_Id IS NULL)
465 OR ( (Y_Customer_Item_Id IS NULL) AND
466 (Y_Customer_Item_Number IS NULL) ) ) THEN
467 FND_MESSAGE.Set_Name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
468 X_Error_Flag := 'Y';
469 X_Error_Code := 'APP-43042';
470 X_Error_Message := FND_MESSAGE.Get;
471 RETURN;
472 END IF;
473
474 --rdevakum, fix for bug 466738. variables for item definition level.
475 --setting values accordingly will control the fetch set of CI_Cur.
476 If Y_Address_Id is null then
477 Temp_Level_2 := 1;
478 Temp_Level_3 := 1;
479 End If;
480
481 l_Customer_Id := Y_Customer_Id ;
482
483 IF ( Y_Address_Id IS NOT NULL ) THEN
484 OPEN RA_Addresses_Cur (Y_Address_Id);
485 FETCH RA_Addresses_Cur
486 INTO l_Customer_Id;
487 IF ( RA_Addresses_Cur%NOTFOUND ) THEN
488 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_CUST_ADDRESS');
489 X_Error_Flag := 'Y';
490 X_Error_Code := 'APP-43099';
491 X_Error_Message := FND_MESSAGE.Get;
492 CLOSE RA_Addresses_Cur;
493 RETURN;
494 END IF;
495 CLOSE RA_Addresses_Cur;
496 END IF;
497
498 OPEN CI_Cur (l_Customer_Id);
499 LOOP
500 <<next_customer_item>>
501
502 FETCH CI_Cur INTO Temp_Customer_Item_Id, Temp_Address_Id,
503 Temp_Customer_Category_Code,
504 Temp_Inactive_Flag,
505 Temp_Item_Definition_Level;
506
507 IF ((CI_Cur%NOTFOUND) AND (CI_Cur%ROWCOUNT = 0)) THEN
508
509 FND_MESSAGE.Set_Name('INV', 'INV_NO_CUSTOMER_ITEM');
510 X_Error_Code := 'APP-43037';
511 X_Error_Flag := 'Y';
512 X_Error_Message := FND_MESSAGE.Get;
513 RETURN;
514 --Bug: 5157639 Removed validations based on Inactive flag
515 ELSIF ((Temp_Item_Definition_Level = '3') AND
519 GOTO get_cross_reference;
516 --(Temp_Inactive_Flag = 'N') AND
517 (Temp_Address_Id = Y_Address_Id)) THEN
518
520
521 ELSIF (Temp_Item_Definition_Level = '2') --AND
522 --(Temp_Inactive_Flag = 'N'))
523 THEN
524
525 /* Changing the query as RA_ADDRESSES has been scrapped -Anmurali
526
527 SELECT Customer_Category_Code
528 INTO RA_Address_Category
529 FROM RA_ADDRESSES
530 WHERE Address_Id = Y_Address_Id;
531
532 SELECT ACCT_SITE.CUSTOMER_CATEGORY_CODE
533 INTO RA_Address_Category
534 FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
535 HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
536 WHERE ACCT_SITE.CUST_ACCT_SITE_ID = Y_Address_Id
537 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
538 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)
539 AND NVL(ACCT_SITE.ORG_ID,
540 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,
541 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),- 99)) =
542 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
543 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),- 99);
544 5064537 */
545 SELECT ACCT_SITE.CUSTOMER_CATEGORY_CODE
546 INTO RA_Address_Category
547 FROM HZ_CUST_ACCT_SITES_ALL ACCT_SITE
548 WHERE ACCT_SITE.CUST_ACCT_SITE_ID = Y_Address_Id;
549
550 IF (Temp_Customer_Category_Code =
551 RA_Address_Category) THEN
552
553 GOTO get_cross_reference;
554 /* ELSE
555 GOTO next_customer_item;
556 */
557 END IF;
558
559
560 ELSIF (Temp_Item_Definition_Level = '1') THEN --AND
561 --(Temp_Inactive_Flag = 'N')) THEN
562
563 GOTO get_cross_reference;
564
565 ELSE
566 IF ((Temp_Inactive_Flag = 'Y') AND
567 (CI_Cur%NOTFOUND) AND (CI_Cur%ROWCOUNT > 0)) THEN
568
569 FND_MESSAGE.Set_Name('INV',
570 'INV_INACTIVE_CUSTOMER_ITEM');
571 X_Error_Code := 'APP-43039';
572 X_Error_Flag := 'Y';
573 X_Error_Message := FND_MESSAGE.Get;
574 Temp_Inactive_Flag := NULL;
575 RETURN;
576 ELSIF ((Temp_Item_Definition_Level = '3') AND
577 (Temp_Address_Id <> Y_Address_Id) AND
578 (CI_Cur%NOTFOUND) AND (CI_Cur%ROWCOUNT > 0)) THEN
579
580 FND_MESSAGE.Set_Name('INV',
581 'INV_INVALID_CUST_ADDRESS');
582 X_Error_Code := 'APP-43099';
583 X_Error_Flag := 'Y';
584 X_Error_Message := FND_MESSAGE.Get;
585 Temp_Inactive_Flag := NULL;
586 RETURN;
587
588 END IF;
589
590 /* GOTO next_customer_item;
591 */
592 END IF;
593
594 EXIT WHEN CI_Cur%NOTFOUND;
595
596 END LOOP;
597
598 <<get_cross_reference>>
599
600 BEGIN
601
602 OPEN CI_XREF_Cur (Temp_Customer_Item_Id);
603 FETCH CI_XREF_Cur INTO Temp_Master_Organization_Id,
604 Temp_Inventory_Item_Id,
605 Temp_Inactive_Flag;
606
607 IF (CI_XREF_Cur%NOTFOUND) THEN
608
609 FND_MESSAGE.Set_Name('INV',
610 'INV_NO_CUSTOMER_ITEM_XREF');
611 X_Error_Code := 'APP-43038';
612 X_Error_Flag := 'Y';
613 X_Error_Message := FND_MESSAGE.Get;
614 RETURN;
615 --Bug: 5157639 Commented out this code to remove validation based on Incative flag
616 /* ELSIF (Temp_Inactive_Flag = 'Y') THEN
617
618 LOOP
619
620 FETCH CI_XREF_Cur INTO
621 Temp_Master_Organization_Id,
622 Temp_Inventory_Item_Id,
623 Temp_Inactive_Flag;
624
625 IF (CI_XREF_Cur%NOTFOUND) THEN
626
627 FND_MESSAGE.Set_Name('INV',
628 'INV_INACTIVE_CI_XREF');
629 X_Error_Code := 'APP-43040';
630 X_Error_Flag := 'Y';
631 X_Error_Message :=
632 FND_MESSAGE.Get;
633 Temp_Inactive_Flag := NULL;
634 RETURN;
635
636 ELSIF (Temp_Inactive_Flag = 'N') THEN
637
638 OPEN CI_ATTR_VAL_Cur (
639 Temp_Customer_Item_Id,
640 Temp_Inventory_Item_Id,
641 Temp_Master_Organization_Id);
642
643 FETCH CI_ATTR_VAL_Cur INTO Recinfo;
644
645 END IF;
646
647 EXIT WHEN ((CI_XREF_Cur%NOTFOUND) OR
648 (Temp_Inactive_Flag = 'N'));
649
650 END LOOP; */
651
652 ELSE
653
654 OPEN CI_ATTR_VAL_Cur (
655 Temp_Customer_Item_Id,
656 Temp_Inventory_Item_Id,
657 Temp_Master_Organization_Id);
658
659 FETCH CI_ATTR_VAL_Cur INTO Recinfo;
660
661 END IF;
662
663 X_Customer_Item_Id := Recinfo.Customer_Item_Id;
664 X_Customer_Id := Recinfo.Customer_Id;
665 X_Customer_Category_Code := Recinfo.Customer_Category_Code;
666 X_Address_Id := Recinfo.Address_Id;
667 X_Customer_Item_Number := Recinfo.Customer_Item_Number;
668 X_Item_Definition_Level := Recinfo.Item_Definition_Level;
669 X_Customer_Item_Desc := Recinfo.Customer_Item_Desc;
670 X_Model_Customer_Item_Id := Recinfo.Model_Customer_Item_Id;
671 X_Commodity_Code_Id := Recinfo.Commodity_Code_Id;
675 X_Min_Fill_Percentage := Recinfo.Min_Fill_Percentage;
672 X_Master_Container_Item_Id := Recinfo.Master_Container_Item_Id;
673 X_Container_Item_Org_Id := Recinfo.Container_Item_Org_Id;
674 X_Detail_Container_Item_Id := Recinfo.Detail_Container_Item_Id;
676 X_Dep_Plan_Required_Flag := Recinfo.Dep_Plan_Required_Flag;
680 X_Demand_Tolerance_Negative :=
677 X_Dep_Plan_Prior_Bld_Flag := Recinfo.Dep_Plan_Prior_Bld_Flag;
678 X_Demand_Tolerance_Positive :=
679 Recinfo.Demand_Tolerance_Positive;
681 Recinfo.Demand_Tolerance_Negative;
682 X_Attribute_Category := Recinfo.Attribute_Category;
683 X_Attribute1 := Recinfo.Attribute1;
684 X_Attribute2 := Recinfo.Attribute2;
685 X_Attribute3 := Recinfo.Attribute3;
686 X_Attribute4 := Recinfo.Attribute4;
687 X_Attribute5 := Recinfo.Attribute5;
688 X_Attribute6 := Recinfo.Attribute6;
689 X_Attribute7 := Recinfo.Attribute7;
690 X_Attribute8 := Recinfo.Attribute8;
691 X_Attribute9 := Recinfo.Attribute9;
692 X_Attribute10 := Recinfo.Attribute10;
693 X_Attribute11 := Recinfo.Attribute11;
694 X_Attribute12 := Recinfo.Attribute12;
695 X_Attribute13 := Recinfo.Attribute13;
696 X_Attribute14 := Recinfo.Attribute14;
697 X_Attribute15 := Recinfo.Attribute15;
698 X_Inventory_Item_Id := Recinfo.Inventory_Item_Id;
699 X_Master_Organization_Id := Recinfo.Master_Organization_Id;
700 X_Preference_Number := Recinfo.Preference_Number;
701 X_Error_Code := 'APP-00000';
702 X_Error_Flag := 'N';
703 X_Error_Message := NULL;
704
705 RETURN;
706
707 END;
708
709 CLOSE CI_Cur;
710 CLOSE CI_XREF_Cur;
711 CLOSE CI_ATTR_VAL_Cur;
712
713 EXCEPTION
714
715 WHEN OTHERS THEN
716
717 X_Error_Code := SQLCODE;
718 X_Error_Flag := 'Y';
719 X_Error_Message := SQLCODE || ' - ' ||
720 SUBSTR(SQLERRM, 1, 240);
721 RETURN;
722
723 END Fetch_Attributes;
724
725
726 END INV_CUSTOMER_ITEM_GRP;