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