[Home] [Help]
Skip to content
PACKAGE BODY: APPS.EGO_ITEM_ORG_ASSIGN_PVT
Source
1 PACKAGE BODY EGO_ITEM_ORG_ASSIGN_PVT AS
2 /* $Header: EGOVIORB.pls 120.20.12020000.2 2012/07/13 01:40:49 mshirkol ship $ */
3
4 G_TAX_CODE_CONTROL_LEVEL NUMBER;
5 G_P_TAX_CODE_CONTROL_LEVEL NUMBER;
6 --Building record from master org.
7 PROCEDURE build_item_record(p_item_rec IN OUT NOCOPY INV_ITEM_API.Item_rec_type
8 ,p_master_org_id IN NUMBER
9 ,x_master_status OUT NOCOPY MTL_SYSTEM_ITEMS_B.APPROVAL_STATUS%TYPE);
10
11 --Procedure to org specific validations.
12 --Bug: 5438006 Added parameter base_item_id
13 PROCEDURE item_org_validations (p_org_code IN VARCHAR2
14 ,p_organization_id IN NUMBER
15 ,p_tracking_quantity_ind IN VARCHAR2
16 ,p_bom_enabled_flag IN VARCHAR2
17 ,p_bom_item_type IN NUMBER
18 ,p_outsourced_assembly IN NUMBER
19 ,p_release_time_fence_code IN NUMBER
20 ,p_subcontracting_component IN NUMBER
21 ,x_return_status OUT NOCOPY VARCHAR2
22 ,x_msg_count OUT NOCOPY NUMBER
23 ,p_context IN VARCHAR2 DEFAULT NULL
24 ,x_error_code OUT NOCOPY VARCHAR2
25 ,p_secondary_uom_code IN VARCHAR2
26 ,p_secondary_default_ind IN VARCHAR2
27 ,p_ont_pricing_qty_source IN VARCHAR2
28 ,p_dual_uom_deviation_high IN NUMBER
29 ,p_dual_uom_deviation_low IN NUMBER
30 ,p_serial_number_control_code IN NUMBER
31 ,p_tax_code IN VARCHAR2
32 ,p_master_status IN VARCHAR2
33 ,p_base_item_id IN NUMBER DEFAULT NULL
34 ,p_pur_tax_code IN VARCHAR2) IS
35
36 CURSOR org_parameters(cp_organization_id IN NUMBER) IS
37 SELECT NVL(primary_cost_method,0)
38 ,NVL(process_enabled_flag,'N')
39 ,NVL(wms_enabled_flag,'N')
40 ,NVL(eam_enabled_flag,'N')
41 ,NVL(trading_partner_org_flag,'N')
42 FROM mtl_parameters
43 WHERE organization_id = cp_organization_id;
44
45
46 l_error_table INV_ITEM_GRP.Error_tbl_type;
47 l_error_index BINARY_INTEGER := 1;
48 l_ret_sts_error CONSTANT VARCHAR2(1) := FND_API.g_RET_STS_ERROR; --'E'
49 l_ret_sts_unexp_error CONSTANT VARCHAR2(1) := FND_API.g_RET_STS_UNEXP_ERROR; --'U'
50 l_eam_enabled_flag VARCHAR2(1) := 'N';
51 l_wms_enabled_flag VARCHAR2(1) := 'N';
52 l_process_enabled_flag VARCHAR2(1) := 'N';
53 l_primary_cost_method NUMBER := 0;
54 l_trading_partner_org_flag VARCHAR2(1) := 'N';
55 l_exists NUMBER;
56
57 BEGIN
58 x_return_status := FND_API.g_RET_STS_SUCCESS;
59 x_msg_count := 0;
60
61 IF p_master_status <> 'A' THEN
62 x_msg_count := x_msg_count + 1;
63 FND_MESSAGE.SET_NAME('INV','INV_IOI_UNAPPROVED_ITEM_ORG');
64 IF NVL(p_context,'N') = 'N' THEN
65 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
66 END IF;
67 Raise FND_API.g_EXC_ERROR;
68 END IF;
69
70 OPEN org_parameters(p_organization_id);
71 FETCH org_parameters
72 INTO l_primary_cost_method
73 ,l_process_enabled_flag
74 ,l_wms_enabled_flag
75 ,l_eam_enabled_flag
76 ,l_trading_partner_org_flag;
77 CLOSE org_parameters;
78
79 -- Organization specific validations for Tracking UOM Indicator
80 IF ( l_process_enabled_flag = 'N'AND
81 p_tracking_quantity_ind = 'PS' AND
82 ( p_bom_enabled_flag = 'Y' OR p_bom_item_type IN (1,2)))
83 THEN
84 x_msg_count := x_msg_count + 1;
85 FND_MESSAGE.SET_NAME('INV','INV_TRACKING_OPM_BOM_ATTR');
86 IF NVL(p_context,'N') = 'N' THEN
87 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
88 END IF;
89 Raise FND_API.g_EXC_ERROR;
90 END IF;
91
92 -- Organization specific validations for Outsourced Assembly
93 IF ( p_outsourced_assembly = 1) THEN
94 -- bug 13823050.
95 -- The restriction on EAM and WMS enabled orgs has to be relaxed
96 --
97 /*
98 IF (NOT( l_eam_enabled_flag = 'N' AND
99 l_wms_enabled_flag = 'N' AND
100 l_process_enabled_flag = 'N')) THEN
101 */
102 IF (NOT(l_process_enabled_flag = 'N')) THEN
103 x_msg_count := x_msg_count + 1;
104 FND_MESSAGE.SET_NAME('INV','INV_OS_ASMBLY_INVALID_ORG');
105 IF NVL(p_context,'N') = 'N' THEN
106 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
107 END IF;
108 Raise FND_API.g_EXC_ERROR;
109 ELSIF ( l_trading_partner_org_flag = 'Y' AND
110 nvl(p_release_time_fence_code,-999999) <> 7) THEN
111 x_msg_count := x_msg_count + 1;
112 FND_MESSAGE.SET_NAME('INV','INV_OS_ASMBLY_TP_TIME_FENSE');
113 IF NVL(p_context,'N') = 'N' THEN
114 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
115 END IF;
116 Raise FND_API.g_EXC_ERROR;
117 ELSIF ( l_primary_cost_method <> 1 ) THEN
118 x_msg_count := x_msg_count + 1;
119 FND_MESSAGE.SET_NAME('INV','INV_OS_ASMBLY_STD_COST_ORG');
120 IF NVL(p_context,'N') = 'N' THEN
121 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
122 END IF;
123 Raise FND_API.g_EXC_ERROR;
124 END IF;
125 END IF;
126
127 -- Organization specific validations for subcontracting component.
128 IF ( p_subcontracting_component IS NOT NULL ) THEN
129 -- bug 13823050.
130 -- The restriction on EAM and WMS enabled orgs has to be relaxed
131 --
132 /*
133 IF (NOT( l_eam_enabled_flag = 'N' AND
134 l_wms_enabled_flag = 'N' AND
135 l_process_enabled_flag = 'N')) THEN
136 */
137 IF (NOT(l_process_enabled_flag = 'N')) THEN
138 x_msg_count := x_msg_count + 1;
139 FND_MESSAGE.SET_NAME('INV','INV_OS_ASMBLY_INVALID_ORG');
140 IF NVL(p_context,'N') = 'N' THEN
141 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
142 END IF;
143 Raise FND_API.g_EXC_ERROR;
144 ELSIF ( l_primary_cost_method <> 1 ) THEN
145 x_msg_count := x_msg_count + 1;
146 FND_MESSAGE.SET_NAME('INV','INV_OS_ASMBLY_STD_COST_ORG');
147 IF NVL(p_context,'N') = 'N' THEN
148 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
149 END IF;
150 Raise FND_API.g_EXC_ERROR;
151 END IF;
152 END IF;
153
154 -- Bug: 5017578 Org Specific Validations
155 IF ( p_tracking_quantity_ind = 'P'
156 AND p_ont_pricing_qty_source = 'P') THEN
157
158 IF p_secondary_default_ind IS NOT NULL THEN
159 x_msg_count := x_msg_count + 1;
160 FND_MESSAGE.SET_NAME('INV','INV_SEC_DEFAULT_NOT_NULL');
161 IF NVL(p_context,'N') = 'N' THEN
162 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
163 END IF;
164 Raise FND_API.g_EXC_ERROR;
165 END IF;
166
167 IF p_secondary_uom_code IS NOT NULL THEN
168 x_msg_count := x_msg_count + 1;
169 FND_MESSAGE.SET_NAME('INV','INV_SEC_UOM_IS_NOT_NULL');
170 IF NVL(p_context,'N') = 'N' THEN
171 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
172 END IF;
173 Raise FND_API.g_EXC_ERROR;
174 END IF;
175
176 IF ( p_dual_uom_deviation_high <> 0
177 OR p_dual_uom_deviation_low <> 0 )THEN
178 x_msg_count := x_msg_count + 1;
179 FND_MESSAGE.SET_NAME('INV','INV_UOM_DEV_IS_NOT_ZERO');
180 IF NVL(p_context,'N') = 'N' THEN
181 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
182 END IF;
183 Raise FND_API.g_EXC_ERROR;
184 END IF;
185 END IF;
186
187 IF ( ( p_tracking_quantity_ind = 'PS' OR p_ont_pricing_qty_source = 'S')
188 AND ( p_secondary_default_ind IS NULL OR p_secondary_uom_code IS NULL ) )THEN
189 x_msg_count := x_msg_count + 1;
190 FND_MESSAGE.SET_NAME('INV','INV_REQUIRED_FIELDS');
191 IF NVL(p_context,'N') = 'N' THEN
192 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
193 END IF;
194 Raise FND_API.g_EXC_ERROR;
195 END IF;
196
197 IF ( p_tracking_quantity_ind = 'PS' ) THEN
198 IF (p_bom_item_type IN (1,2) OR p_bom_enabled_flag = 'Y') THEN
199 x_msg_count := x_msg_count + 1;
200 FND_MESSAGE.SET_NAME('INV','INV_TRACKING_OPM_BOM_ATTR');
201 IF NVL(p_context,'N') = 'N' THEN
202 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
203 END IF;
204 Raise FND_API.g_EXC_ERROR;
205 END IF;
206
207 IF p_serial_number_control_code > 1 THEN
208 x_msg_count := x_msg_count + 1;
209 FND_MESSAGE.SET_NAME('INV','INV_INVALID_TRACKING_QTY_IND');
210 IF NVL(p_context,'N') = 'N' THEN
211 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
212 END IF;
213 Raise FND_API.g_EXC_ERROR;
214 END IF;
215 END IF;
216
217 IF ( p_tracking_quantity_ind = 'P'
218 AND p_secondary_default_ind NOT IN ('D','N') ) THEN
219
220 x_msg_count := x_msg_count + 1;
221 FND_MESSAGE.SET_NAME('INV','INV_SEC_DEFULT_IS_FIXED');
222 IF NVL(p_context,'N') = 'N' THEN
223 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
224 END IF;
225 Raise FND_API.g_EXC_ERROR;
226 END IF;
227
228 --Bug 5330093 Begin
229 -- Confirming if "Positive Deviation" and "Negative Deviation" are 0
230 -- when "Defaulting" is neither "Default" nor "Not Default"
231 IF ( p_secondary_default_ind NOT IN ('D','N') ) THEN
232 --{
233 IF ( p_dual_uom_deviation_high <> 0 OR p_dual_uom_deviation_low <> 0 )THEN
234 --{
235 x_msg_count := x_msg_count + 1;
236 FND_MESSAGE.SET_NAME('INV','INV_UOM_DEV_IS_NOT_ZERO');
237 IF NVL(p_context,'N') = 'N' THEN
238 --{
239 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
240 --}
241 END IF;
242 Raise FND_API.g_EXC_ERROR;
243 --}
244 END IF;
245 --}
246 END IF;
247 --Bug 5330093 End
248
249 /* Bug 5207014 - Validating Output Tax Classification Code */
250 IF ( p_tax_code IS NOT NULL AND G_TAX_CODE_CONTROL_LEVEL = 1 ) THEN
251 BEGIN
252 SELECT 1 INTO l_exists
253 FROM ZX_OUTPUT_CLASSIFICATIONS_V
254 WHERE lookup_code = p_tax_code
255 AND enabled_flag = 'Y'
256 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) and NVL(end_date_active, SYSDATE)
257 AND org_id IN (p_organization_id,-99)
258 AND rownum = 1;
259 EXCEPTION
260 WHEN no_data_found THEN
261 x_msg_count := x_msg_count + 1;
262 FND_MESSAGE.SET_NAME('INV','INV_IOI_INVALID_TAX_CODE_ORG');
263 IF NVL(p_context,'N') = 'N' THEN
264 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
265 END IF;
266 Raise FND_API.g_EXC_ERROR;
267 END;
268 END IF;
269
270 --Bug 5662813
271 IF ( p_pur_tax_code IS NOT NULL AND G_P_TAX_CODE_CONTROL_LEVEL = 1 ) THEN
272 BEGIN
273 SELECT 1 INTO l_exists
274 FROM ZX_INPUT_CLASSIFICATIONS_V
275 WHERE tax_type not in ('AWT','OFFSET')
276 AND enabled_flag = 'Y'
277 AND sysdate between start_date_active and nvl(end_date_active,sysdate)
278 AND lookup_code = p_pur_tax_code
279 AND org_id IN (p_organization_id,-99)
280 AND rownum = 1;
281 EXCEPTION
282 WHEN no_data_found THEN
283 x_msg_count := x_msg_count + 1;
284 FND_MESSAGE.SET_NAME('INV','INV_IOI_PUR_TAX_CODE');
285 IF NVL(p_context,'N') = 'N' THEN
286 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
287 END IF;
288 Raise FND_API.g_EXC_ERROR;
289 END;
290 END IF;
291
292
293 --Bug: 5438006 Adding validation for base item
294 IF(p_base_item_id IS NOT NULL) THEN
295 BEGIN
296 SELECT 1 INTO l_exists
297 FROM MTL_SYSTEM_ITEMS_B
298 WHERE inventory_item_id = p_base_item_id
299 AND organization_id = p_organization_id;
300 EXCEPTION
301 WHEN NO_DATA_FOUND THEN
302 x_msg_count := x_msg_count + 1;
303 FND_MESSAGE.SET_NAME('INV','INV_IOI_BASE_ITEM_ID');
304 IF NVL(p_context,'N') = 'N' THEN
305 EGO_Item_Msg.Add_Error_Text(x_msg_count,p_org_code ||' : '||fnd_message.get);
306 END IF;
307 Raise FND_API.g_EXC_ERROR;
308 END;
309 END IF;
310
311 EXCEPTION
312 WHEN FND_API.g_EXC_ERROR THEN
313 x_return_status := l_ret_sts_error;
314 IF NVL(p_context,'N') = 'BOM' THEN
315 x_error_code := fnd_message.get;
316 END IF;
317 WHEN OTHERS THEN
318 x_return_status := l_ret_sts_unexp_error;
319 x_msg_count := x_msg_count + 1;
320 EGO_Item_Msg.Add_Error_Message ( x_msg_count, 'INV', 'EGO_ITEM_UNEXPECTED_ERROR',
321 'PACKAGE_NAME', 'EGO_ITEM_ORG_ASSIGN_PVT', FALSE,
322 'PROCEDURE_NAME', 'PROCESS_ORG_ASSIGNMENTS', FALSE,
323 'ERROR_TEXT', SQLERRM, FALSE );
324 END item_org_validations;
325
326 --API called from PLM HTML UI org assignment.
327 PROCEDURE process_org_assignments(p_item_org_assign_tab IN OUT NOCOPY SYSTEM.EGO_ITEM_ORG_ASSIGN_TABLE
328 ,p_commit IN VARCHAR2
329 ,p_context IN VARCHAR2 DEFAULT NULL
330 ,x_return_status OUT NOCOPY VARCHAR
331 ,x_msg_count OUT NOCOPY NUMBER)
332 IS
333 l_item_rec INV_ITEM_API.Item_rec_type := NULL;
334 l_return_status VARCHAR2(1) := FND_API.g_RET_STS_SUCCESS;
335 l_sysdate DATE := SYSDATE;
336 l_rowid ROWID := NULL;
337 l_inv_id NUMBER := INV_ITEM_UTIL.g_Appl_Inst.inv;
338 l_error_table INV_ITEM_GRP.Error_tbl_type;
339 l_error_index BINARY_INTEGER := 1;
340 l_ret_sts_error CONSTANT VARCHAR2(1) := FND_API.g_RET_STS_ERROR; --'E'
341 l_ret_sts_unexp_error CONSTANT VARCHAR2(1) := FND_API.g_RET_STS_UNEXP_ERROR; --'U'
342 l_item_org_valid VARCHAR2(1);
343 l_org_valid_count NUMBER;
344 l_item_id NUMBER;
345 l_master_status MTL_SYSTEM_ITEMS_B.APPROVAL_STATUS%TYPE;
346
347 /*
348 Bug 4964874
349 In Multi Create Copy flow
350 Different Inventory_ID might come if we are creating multiple items and they have organization
351 assignments. We will buuild record every time the inventory_item_id changes and for the same
352 we are ordering the rows in the sql table by inventory_item_id
353 */
354 CURSOR org_assign_recs IS
355 SELECT A.MASTER_ORGANIZATION_ID MASTER_ORGANIZATION_ID
356 ,A.ORGANIZATION_ID ORGANIZATION_ID
357 ,A.ORGANIZATION_CODE ORGANIZATION_CODE
358 ,A.PRIMARY_UOM_CODE PRIMARY_UOM_CODE
359 ,A.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
360 ,A.STATUS STATUS
361 ,A.ERROR_CODE ERROR_CODE
362 ,A.SECONDARY_UOM_CODE SECONDARY_UOM_CODE
363 ,A.TRACKING_QUANTITY_IND TRACKING_QUANTITY_IND
364 ,A.SECONDARY_DEFAULT_IND SECONDARY_DEFAULT_IND
365 ,A.ONT_PRICING_QTY_SOURCE ONT_PRICING_QTY_SOURCE
366 ,A.DUAL_UOM_DEVIATION_HIGH DUAL_UOM_DEVIATION_HIGH
367 ,A.DUAL_UOM_DEVIATION_LOW DUAL_UOM_DEVIATION_LOW
368 FROM THE (SELECT CAST(p_item_org_assign_tab AS "SYSTEM".EGO_ITEM_ORG_ASSIGN_TABLE)
369 FROM dual) A
370 ORDER BY INVENTORY_ITEM_ID;
371
372 l_error_code VARCHAR2(2000);
373 l_status VARCHAR2(1);
374
375 l_record_first NUMBER;
376 l_record_last NUMBER;
377 l_exists NUMBER;
378
379 /* Bug 8525905 comment start as raising duplicate business event
380 -- Bug 5357161
381 CURSOR rev_creates_for_org_item( c_inventory_item_id NUMBER
382 ,c_organization_id NUMBER) IS
383 SELECT inventory_item_id
384 ,organization_id
385 ,revision_id
386 FROM MTL_ITEM_REVISIONS_B
387 WHERE inventory_item_id = c_inventory_item_id
388 AND organization_id = c_organization_id;
389 Bug 8525905 comment end */
390
391 CURSOR cat_assigns_for_org_item( c_inventory_item_id NUMBER
392 ,c_organization_id NUMBER) IS
393 SELECT inventory_item_id
394 ,organization_id
395 ,category_set_id
396 ,category_id
397 FROM MTL_ITEM_CATEGORIES
398 WHERE inventory_item_id = c_inventory_item_id
399 AND organization_id = c_organization_id;
400 -- Bug 5357161
401
402 CURSOR tax_control_level IS
403 SELECT SUM(Decode(ATTRIBUTE_NAME,'MTL_SYSTEM_ITEMS.TAX_CODE',CONTROL_LEVEL,0))
404 ,SUM(Decode(ATTRIBUTE_NAME,'MTL_SYSTEM_ITEMS.PURCHASING_TAX_CODE',CONTROL_LEVEL,0))
405 FROM mtl_item_attributes
406 WHERE attribute_name IN ( 'MTL_SYSTEM_ITEMS.PURCHASING_TAX_CODE','MTL_SYSTEM_ITEMS.TAX_CODE');
407 BEGIN
408
409 OPEN tax_control_level;
410 FETCH tax_control_level INTO G_TAX_CODE_CONTROL_LEVEL,G_P_TAX_CODE_CONTROL_LEVEL;
411 CLOSE tax_control_level;
412
413 x_return_status := l_return_status;
414 x_msg_count := 0;
415
416 l_record_first := p_item_org_assign_tab.FIRST;
417 l_record_last := p_item_org_assign_tab.LAST;
418
419 FOR org_assign_rec IN org_assign_recs LOOP
420 l_item_rec.inventory_item_id := org_assign_rec.inventory_item_id;
421 --Populate the columns from master record.
422 --The "If" condition prohibits from building record multiple times.
423 --For multiple org assignemts of a single item, build record will be called only once.
424 IF NVL(l_item_id,-1) <> org_assign_rec.inventory_item_id THEN
425 l_item_id := org_assign_rec.inventory_item_id;
426 build_item_record(p_item_rec => l_item_rec
427 ,p_master_org_id => org_assign_rec.master_organization_id
428 ,x_master_status => l_master_status);
429 END IF;
430
431 --Populate org specific variable
432 l_item_rec.organization_id := org_assign_rec.organization_id;
433 l_item_rec.primary_uom_code := org_assign_rec.primary_uom_code;
434 l_item_rec.creation_date := l_sysdate;
435 l_item_rec.last_update_date := l_sysdate;
436 -- Bug: 5017578
437 l_item_rec.secondary_uom_code := org_assign_rec.secondary_uom_code;
438 l_item_rec.tracking_quantity_ind := org_assign_rec.tracking_quantity_ind;
439 l_item_rec.secondary_default_ind := org_assign_rec.secondary_default_ind;
440 l_item_rec.ont_pricing_qty_source := org_assign_rec.ont_pricing_qty_source;
441 l_item_rec.dual_uom_deviation_high := NVL(org_assign_rec.dual_uom_deviation_high,0);
442 l_item_rec.dual_uom_deviation_low := NVL(org_assign_rec.dual_uom_deviation_low,0);
443
444 /* Bug 7419728 : Added the below sql */
445 SELECT DECODE(l_item_rec.tracking_quantity_ind,'P',1,
446 DECODE(NVL(l_item_rec.secondary_default_ind,'X'),'F',2,'D',3,4))
447 INTO l_item_rec.DUAL_UOM_CONTROL
448 FROM DUAL;
449
450 l_item_rec.last_updated_by := FND_GLOBAL.User_Id;
451 l_item_rec.created_by := FND_GLOBAL.User_Id;
452
453 --Bug 5622813 NULL out tax codes if INVALID for current org and the control level is ORG
454 BEGIN
455 IF l_item_rec.tax_code IS NOT NULL THEN
456 SELECT 1 INTO l_exists
457 FROM ZX_OUTPUT_CLASSIFICATIONS_V
458 WHERE lookup_code = l_item_rec.tax_code
459 AND enabled_flag = 'Y'
460 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) and NVL(end_date_active, SYSDATE)
461 AND org_id IN (l_item_rec.organization_id,-99)
462 AND rownum = 1;
463 END IF;
464
465 EXCEPTION WHEN NO_DATA_FOUND THEN
466 IF G_TAX_CODE_CONTROL_LEVEL = 2 THEN
467 l_item_rec.tax_code := NULL;
468 END IF;
469 END;
470
471 BEGIN
472 IF l_item_rec.purchasing_tax_code IS NOT NULL THEN
473 SELECT 1 INTO l_exists
474 FROM ZX_INPUT_CLASSIFICATIONS_V
475 WHERE lookup_code = l_item_rec.purchasing_tax_code
476 AND tax_type not in ('AWT','OFFSET')
477 AND enabled_flag = 'Y'
478 AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) and NVL(end_date_active, SYSDATE)
479 AND org_id IN (l_item_rec.organization_id,-99)
480 AND rownum = 1;
481 END IF;
482
483 EXCEPTION WHEN NO_DATA_FOUND THEN
484 IF G_P_TAX_CODE_CONTROL_LEVEL = 2 THEN
485 l_item_rec.purchasing_tax_code := NULL;
486 END IF;
487 END;
488
489
490 SELECT encumbrance_account
491 ,expense_account
492 ,sales_account
493 ,cost_of_sales_account
494 INTO l_item_rec.encumbrance_account
495 ,l_item_rec.expense_account
496 ,l_item_rec.sales_account
497 ,l_item_rec.cost_of_sales_account
498 FROM mtl_parameters
499 WHERE organization_id = org_assign_rec.organization_id;
500
501 BEGIN
502 -- Calling validation API to validate
503 -- 1.Tracking UOM Indicator
504 -- 2.Outsourced Assembly
505 -- 3.Subcontracting component.
506 -- Only above fields have validations against org params.
507 l_org_valid_count := 0;
508 item_org_validations(p_org_code => org_assign_rec.organization_code
509 ,p_organization_id => org_assign_rec.organization_id
510 ,p_tracking_quantity_ind => l_item_rec.tracking_quantity_ind
511 ,p_bom_enabled_flag => l_item_rec.bom_enabled_flag
512 ,p_bom_item_type => l_item_rec.bom_item_type
513 ,p_outsourced_assembly => l_item_rec.outsourced_assembly
514 ,p_release_time_fence_code => l_item_rec.release_time_fence_code
515 ,p_subcontracting_component => l_item_rec.subcontracting_component
516 ,x_return_status => l_item_org_valid
517 ,x_msg_count => l_org_valid_count
518 ,p_context => p_context
519 ,x_error_code => l_error_code
520 ,p_secondary_uom_code => l_item_rec.secondary_uom_code
521 ,p_secondary_default_ind => l_item_rec.secondary_default_ind
522 ,p_ont_pricing_qty_source => l_item_rec.ont_pricing_qty_source
523 ,p_dual_uom_deviation_high => l_item_rec.dual_uom_deviation_high
524 ,p_dual_uom_deviation_low => l_item_rec.dual_uom_deviation_low
525 ,p_serial_number_control_code => l_item_rec.serial_number_control_code
526 ,p_tax_code => l_item_rec.tax_code
527 ,p_master_status => l_master_status
528 ,p_base_item_id => l_item_rec.base_item_id --Bug: 5438006 Added base_item_id
529 ,p_pur_tax_code => l_item_rec.purchasing_tax_code
530 );
531
532 --Bug: 4997732
533 FOR i in l_record_first .. l_record_last LOOP
534 IF (p_item_org_assign_tab(i).inventory_item_id = org_assign_rec.inventory_item_id)
535 AND (p_item_org_assign_tab(i).organization_id = org_assign_rec.organization_id) THEN
536 p_item_org_assign_tab(i).error_code := l_error_code;
537 p_item_org_assign_tab(i).status := l_item_org_valid;
538 END IF;
539 END LOOP;
540
541 IF l_item_org_valid = FND_API.g_RET_STS_SUCCESS THEN
542 INV_ITEM_PVT.CREATE_ITEM
543 (P_Item_Rec => l_Item_Rec
544 ,P_Item_Category_Struct_Id => NULL
545 ,P_Inv_Install => l_inv_id
546 ,P_Master_Org_Id => org_assign_rec.master_organization_id
547 ,P_Category_Set_Id => NULL
548 ,P_Item_Category_Id => NULL
549 ,P_Event => 'ORG_ASSIGN'
550 ,x_row_Id => l_rowid
551 ,P_Default_Move_Order_Sub_Inv => NULL
552 ,P_Default_Receiving_Sub_Inv => NULL
553 ,P_Default_Shipping_Sub_Inv => NULL);
554 /*bug 8525905 comment start as raising duplicate business event
555 -- Bug 5357161
556 -- INV_ITEM_PVT.CREATE_ITEM raises business events for Create Item.
557 -- We need to raise for Category Assignment and Revision Create.
558 -- Raising for revisions
559 FOR revs in rev_creates_for_org_item(l_item_rec.inventory_item_id, l_item_rec.organization_id) LOOP
560 INV_ITEM_EVENTS_PVT.Raise_Events(
561 p_inventory_item_id => revs.inventory_item_id
562 ,p_organization_id => revs.organization_id
563 ,p_revision_id => revs.revision_id
564 ,p_event_name => 'EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT'
565 ,p_dml_type => 'CREATE');
566 END LOOP;
567 Bug 8525905 comment end*/
568 -- Raising for category_assignments
569 FOR cats in cat_assigns_for_org_item(l_item_rec.inventory_item_id, l_item_rec.organization_id) LOOP
570 INV_ITEM_EVENTS_PVT.Raise_Events(
571 p_inventory_item_id => cats.inventory_item_id
572 ,p_organization_id => cats.organization_id
573 ,p_category_set_id => cats.category_set_id
574 ,p_category_id => cats.category_id
575 ,p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
576 ,p_dml_type => 'CREATE');
577 END LOOP;
578 -- Bug 5357161
579
580 ELSE
581 x_msg_count := x_msg_count + l_org_valid_count;
582 IF l_return_status <> l_ret_sts_unexp_error THEN
583 l_return_status := l_item_org_valid;
584 END IF;
585 END IF;
586
587 EXCEPTION
588 WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
589 x_msg_count := x_msg_count + 1;
590 EGO_Item_Msg.Add_Error_Text(x_msg_count,org_assign_rec.organization_code ||' : '||fnd_message.get);
591 IF l_return_status <> l_ret_sts_unexp_error THEN
592 l_return_status := l_ret_sts_error;
593 END IF;
594 WHEN OTHERS THEN
595 x_msg_count := x_msg_count + 1;
596 EGO_Item_Msg.Add_Error_Message ( x_msg_count, 'INV', 'INV_ITEM_UNEXPECTED_ERROR',
597 'PACKAGE_NAME', 'INV_ITEM_PVT', FALSE,
598 'PROCEDURE_NAME', 'CREATE_ITEM', FALSE,
599 'ERROR_TEXT', org_assign_rec.organization_code ||' : '||SQLERRM, FALSE );
600 x_return_status := l_ret_sts_unexp_error;
601 END;
602 END LOOP;
603
604 x_return_status := l_return_status;
605
606 EXCEPTION
607 WHEN OTHERS THEN
608 x_msg_count := x_msg_count + 1;
609 x_return_status := l_ret_sts_unexp_error;
610 EGO_Item_Msg.Add_Error_Message ( x_msg_count, 'INV', 'EGO_ITEM_UNEXPECTED_ERROR',
611 'PACKAGE_NAME', 'EGO_ITEM_ORG_ASSIGN_PVT', FALSE,
612 'PROCEDURE_NAME', 'PROCESS_ORG_ASSIGNMENTS', FALSE,
613 'ERROR_TEXT', SQLERRM, FALSE );
614 END process_org_assignments;
615
616 PROCEDURE build_item_record(p_item_rec IN OUT NOCOPY INV_ITEM_API.Item_rec_type
617 ,p_master_org_id IN NUMBER
618 ,x_master_status OUT NOCOPY MTL_SYSTEM_ITEMS_B.APPROVAL_STATUS%TYPE) IS
619 BEGIN
620 SELECT
621 b.inventory_item_id
622 ,t.description
623 ,t.long_description
624 ,b.primary_uom_code
625 ,b.allowed_units_lookup_code
626 ,b.overcompletion_tolerance_type
627 ,b.overcompletion_tolerance_value
628 ,b.effectivity_control
629 ,b.check_shortages_flag
630 ,b.full_lead_time
631 ,b.order_cost
632 ,b.mrp_safety_stock_percent
633 ,b.mrp_safety_stock_code
634 ,b.min_minmax_quantity
635 ,b.max_minmax_quantity
636 ,b.minimum_order_quantity
637 ,b.fixed_order_quantity
638 ,b.fixed_days_supply
639 ,b.maximum_order_quantity
640 ,b.atp_rule_id
641 ,b.picking_rule_id
642 ,b.reservable_type
643 ,b.positive_measurement_error
644 ,b.negative_measurement_error
645 ,b.engineering_ecn_code
646 ,b.engineering_item_id
647 ,b.engineering_date
648 ,b.service_starting_delay
649 ,b.serviceable_component_flag
650 ,b.serviceable_product_flag
651 ,b.payment_terms_id
652 ,b.preventive_maintenance_flag
653 ,b.material_billable_flag
654 ,b.prorate_service_flag
655 ,b.coverage_schedule_id
656 ,b.service_duration_period_code
657 ,b.service_duration
658 ,b.invoiceable_item_flag
659 ,b.tax_code
660 ,b.invoice_enabled_flag
661 ,b.must_use_approved_vendor_flag
662 ,b.outside_operation_flag
663 ,b.outside_operation_uom_type
664 ,b.safety_stock_bucket_days
665 ,b.auto_reduce_mps
666 ,b.costing_enabled_flag
667 ,b.auto_created_config_flag
668 ,b.cycle_count_enabled_flag
669 ,b.item_type
670 ,b.model_config_clause_name
671 ,b.ship_model_complete_flag
672 ,b.mrp_planning_code
673 ,b.return_inspection_requirement
674 ,b.ato_forecast_control
675 ,b.release_time_fence_code
676 ,b.release_time_fence_days
677 ,b.container_item_flag
678 ,b.vehicle_item_flag
679 ,b.maximum_load_weight
680 ,b.minimum_fill_percent
681 ,b.container_type_code
682 ,b.internal_volume
683 ,b.global_attribute_category
684 ,b.global_attribute1
685 ,b.global_attribute2
686 ,b.global_attribute3
687 ,b.global_attribute4
688 ,b.global_attribute5
689 ,b.global_attribute6
690 ,b.global_attribute7
691 ,b.global_attribute8
692 ,b.global_attribute9
693 ,b.global_attribute10
694 ,b.purchasing_tax_code
695 ,b.attribute6
696 ,b.attribute7
697 ,b.attribute8
698 ,b.attribute9
699 ,b.attribute10
700 ,b.attribute11
701 ,b.attribute12
702 ,b.attribute13
703 ,b.attribute14
704 ,b.attribute15
705 ,b.purchasing_item_flag
706 ,b.shippable_item_flag
707 ,b.customer_order_flag
708 ,b.internal_order_flag
709 ,b.inventory_item_flag
710 ,b.eng_item_flag
711 ,b.inventory_asset_flag
712 ,b.purchasing_enabled_flag
713 ,b.customer_order_enabled_flag
714 ,b.internal_order_enabled_flag
715 ,b.so_transactions_flag
716 ,b.mtl_transactions_enabled_flag
717 ,b.stock_enabled_flag
718 ,b.bom_enabled_flag
719 ,b.build_in_wip_flag
720 ,b.revision_qty_control_code
721 ,b.item_catalog_group_id
722 ,b.catalog_status_flag
723 ,b.returnable_flag
724 ,b.default_shipping_org
725 ,b.collateral_flag
726 ,b.taxable_flag
727 ,b.qty_rcv_exception_code
728 ,b.allow_item_desc_update_flag
729 ,b.inspection_required_flag
730 ,b.receipt_required_flag
731 ,b.market_price
732 ,b.hazard_class_id
733 ,b.rfq_required_flag
734 ,b.qty_rcv_tolerance
735 ,b.list_price_per_unit
736 ,b.un_number_id
737 ,b.price_tolerance_percent
738 ,b.asset_category_id
739 ,b.rounding_factor
740 ,b.unit_of_issue
741 ,b.enforce_ship_to_location_code
742 ,b.allow_substitute_receipts_flag
743 ,b.allow_unordered_receipts_flag
744 ,b.allow_express_delivery_flag
745 ,b.days_early_receipt_allowed
746 ,b.days_late_receipt_allowed
747 ,b.receipt_days_exception_code
748 ,b.receiving_routing_id
749 ,b.invoice_close_tolerance
750 ,b.receive_close_tolerance
751 ,b.auto_lot_alpha_prefix
752 ,b.start_auto_lot_number
753 ,b.lot_control_code
754 ,b.shelf_life_code
755 ,b.shelf_life_days
756 ,b.serial_number_control_code
757 ,b.start_auto_serial_number
758 ,b.auto_serial_alpha_prefix
759 ,b.source_type
760 ,b.source_organization_id
761 ,b.source_subinventory
762 ,b.expense_account
763 ,b.encumbrance_account
764 ,b.restrict_subinventories_code
765 ,b.unit_weight
766 ,b.weight_uom_code
767 ,b.volume_uom_code
768 ,b.unit_volume
769 ,b.restrict_locators_code
770 ,b.location_control_code
771 ,b.shrinkage_rate
772 ,b.acceptable_early_days
773 ,b.planning_time_fence_code
774 ,b.demand_time_fence_code
775 ,b.lead_time_lot_size
776 ,b.std_lot_size
777 ,b.cum_manufacturing_lead_time
778 ,b.overrun_percentage
779 ,b.mrp_calculate_atp_flag
780 ,b.acceptable_rate_increase
781 ,b.acceptable_rate_decrease
782 ,b.cumulative_total_lead_time
783 ,b.planning_time_fence_days
784 ,b.demand_time_fence_days
785 ,b.end_assembly_pegging_flag
786 ,b.repetitive_planning_flag
787 ,b.planning_exception_set
788 ,b.bom_item_type
789 ,b.pick_components_flag
790 ,b.replenish_to_order_flag
791 ,b.base_item_id
792 ,b.atp_components_flag
793 ,b.atp_flag
794 ,b.fixed_lead_time
795 ,b.variable_lead_time
796 ,b.wip_supply_locator_id
797 ,b.wip_supply_type
798 ,b.wip_supply_subinventory
799 ,b.cost_of_sales_account
800 ,b.sales_account
801 ,b.default_include_in_rollup_flag
802 ,b.inventory_item_status_code
803 ,b.inventory_planning_code
804 ,b.planner_code
805 ,b.planning_make_buy_code
806 ,b.fixed_lot_multiplier
807 ,b.rounding_control_type
808 ,b.carrying_cost
809 ,b.postprocessing_lead_time
810 ,b.preprocessing_lead_time
811 ,b.summary_flag
812 ,b.enabled_flag
813 ,b.start_date_active
814 ,b.end_date_active
815 ,b.buyer_id
816 ,b.accounting_rule_id
817 ,b.invoicing_rule_id
818 ,b.over_shipment_tolerance
819 ,b.under_shipment_tolerance
820 ,b.over_return_tolerance
821 ,b.under_return_tolerance
822 ,b.equipment_type
823 ,b.recovered_part_disp_code
824 ,b.defect_tracking_on_flag
825 ,b.event_flag
826 ,b.electronic_flag
827 ,b.downloadable_flag
828 ,b.vol_discount_exempt_flag
829 ,b.coupon_exempt_flag
830 ,b.comms_nl_trackable_flag
831 ,b.asset_creation_code
832 ,b.comms_activation_reqd_flag
833 ,b.orderable_on_web_flag
834 ,b.back_orderable_flag
835 ,b.web_status
836 ,b.indivisible_flag
837 ,b.dimension_uom_code
838 ,b.unit_length
839 ,b.unit_width
840 ,b.unit_height
841 ,b.bulk_picked_flag
842 ,b.lot_status_enabled
843 ,b.default_lot_status_id
844 ,b.serial_status_enabled
845 ,b.default_serial_status_id
846 ,b.lot_split_enabled
847 ,b.lot_merge_enabled
848 ,b.inventory_carry_penalty
849 ,b.operation_slack_penalty
850 ,b.financing_allowed_flag
851 ,b.eam_item_type
852 ,b.eam_activity_type_code
853 ,b.eam_activity_cause_code
854 ,b.eam_act_notification_flag
855 ,b.eam_act_shutdown_status
856 ,b.dual_uom_control
857 ,b.secondary_uom_code
858 ,b.dual_uom_deviation_high
859 ,b.dual_uom_deviation_low
860 ,b.contract_item_type_code
861 ,b.subscription_depend_flag
862 ,b.serv_req_enabled_code
863 ,b.serv_billing_enabled_flag
864 ,b.serv_importance_level
865 ,b.planned_inv_point_flag
866 ,b.lot_translate_enabled
867 ,b.default_so_source_type
868 ,b.create_supply_flag
869 ,b.substitution_window_code
870 ,b.substitution_window_days
871 ,b.ib_item_instance_class
872 ,b.config_model_type
873 ,b.lot_substitution_enabled
874 ,b.minimum_license_quantity
875 ,b.eam_activity_source_code
876 ,b.tracking_quantity_ind
877 ,b.ont_pricing_qty_source
878 ,b.secondary_default_ind
879 ,b.option_specific_sourced
880 ,b.config_orgs
881 ,b.config_match
882 ,b.segment1
883 ,b.segment2
884 ,b.segment3
885 ,b.segment4
886 ,b.segment5
887 ,b.segment6
888 ,b.segment7
889 ,b.segment8
890 ,b.segment9
891 ,b.segment10
892 ,b.segment11
893 ,b.segment12
894 ,b.segment13
895 ,b.segment14
896 ,b.segment15
897 ,b.segment16
898 ,b.segment17
899 ,b.segment18
900 ,b.segment19
901 ,b.segment20
902 ,b.attribute_category
903 ,b.attribute1
904 ,b.attribute2
905 ,b.attribute3
906 ,b.attribute4
907 ,b.attribute5
908 ,b.created_by
909 ,b.last_updated_by
910 ,b.last_update_login
911 ,b.lifecycle_id
912 ,b.current_phase_id
913 ,b.vmi_minimum_units
914 ,b.vmi_minimum_days
915 ,b.vmi_maximum_units
916 ,b.vmi_maximum_days
917 ,b.vmi_fixed_order_quantity
918 ,b.so_authorization_flag
919 ,b.consigned_flag
920 ,b.asn_autoexpire_flag
921 ,b.vmi_forecast_type
922 ,b.forecast_horizon
923 ,b.exclude_from_budget_flag
924 ,b.critical_component_flag
925 ,b.continous_transfer
926 ,b.convergence
927 ,b.divergence
928 ,b.drp_planned_flag
929 ,b.days_tgt_inv_supply
930 ,b.days_tgt_inv_window
931 ,b.days_max_inv_supply
932 ,b.days_max_inv_window
933 ,b.lot_divisible_flag
934 ,b.grade_control_flag
935 ,b.default_grade
936 ,b.child_lot_flag
937 ,b.parent_child_generation_flag
938 ,b.child_lot_prefix
939 ,b.child_lot_starting_number
940 ,b.child_lot_validation_flag
941 ,b.copy_lot_attribute_flag
942 ,b.process_execution_enabled_flag
943 ,b.process_costing_enabled_flag
944 ,b.retest_interval
945 ,b.expiration_action_interval
946 ,b.expiration_action_code
947 ,b.maturity_days
948 ,b.hold_days
949 ,b.process_quality_enabled_flag
950 ,b.recipe_enabled_flag
951 ,b.process_supply_subinventory
952 ,b.process_supply_locator_id
953 ,b.process_yield_subinventory
954 ,b.process_yield_locator_id
955 ,b.hazardous_material_flag
956 ,b.cas_number
957 ,b.attribute16
958 ,b.attribute17
959 ,b.attribute18
960 ,b.attribute19
961 ,b.attribute20
962 ,b.attribute21
963 ,b.attribute22
964 ,b.attribute23
965 ,b.attribute24
966 ,b.attribute25
967 ,b.attribute26
968 ,b.attribute27
969 ,b.attribute28
970 ,b.attribute29
971 ,b.attribute30
972 ,b.repair_leadtime
973 ,b.repair_yield
974 ,b.repair_program
975 ,b.preposition_point
976 ,b.charge_periodicity_code
977 ,b.outsourced_assembly
978 ,b.subcontracting_component
979 ,NVL(b.approval_status,'A')
980 INTO
981 p_item_rec.inventory_item_id
982 ,p_item_rec.description
983 ,p_item_rec.long_description
984 ,p_item_rec.primary_uom_code
985 ,p_item_rec.allowed_units_lookup_code
986 ,p_item_rec.overcompletion_tolerance_type
987 ,p_item_rec.overcompletion_tolerance_value
988 ,p_item_rec.effectivity_control
989 ,p_item_rec.check_shortages_flag
990 ,p_item_rec.full_lead_time
991 ,p_item_rec.order_cost
992 ,p_item_rec.mrp_safety_stock_percent
993 ,p_item_rec.mrp_safety_stock_code
994 ,p_item_rec.min_minmax_quantity
995 ,p_item_rec.max_minmax_quantity
996 ,p_item_rec.minimum_order_quantity
997 ,p_item_rec.fixed_order_quantity
998 ,p_item_rec.fixed_days_supply
999 ,p_item_rec.maximum_order_quantity
1000 ,p_item_rec.atp_rule_id
1001 ,p_item_rec.picking_rule_id
1002 ,p_item_rec.reservable_type
1003 ,p_item_rec.positive_measurement_error
1004 ,p_item_rec.negative_measurement_error
1005 ,p_item_rec.engineering_ecn_code
1006 ,p_item_rec.engineering_item_id
1007 ,p_item_rec.engineering_date
1008 ,p_item_rec.service_starting_delay
1009 ,p_item_rec.serviceable_component_flag
1010 ,p_item_rec.serviceable_product_flag
1011 ,p_item_rec.payment_terms_id
1012 ,p_item_rec.preventive_maintenance_flag
1013 ,p_item_rec.material_billable_flag
1014 ,p_item_rec.prorate_service_flag
1015 ,p_item_rec.coverage_schedule_id
1016 ,p_item_rec.service_duration_period_code
1017 ,p_item_rec.service_duration
1018 ,p_item_rec.invoiceable_item_flag
1019 ,p_item_rec.tax_code
1020 ,p_item_rec.invoice_enabled_flag
1021 ,p_item_rec.must_use_approved_vendor_flag
1022 ,p_item_rec.outside_operation_flag
1023 ,p_item_rec.outside_operation_uom_type
1024 ,p_item_rec.safety_stock_bucket_days
1025 ,p_item_rec.auto_reduce_mps
1026 ,p_item_rec.costing_enabled_flag
1027 ,p_item_rec.auto_created_config_flag
1028 ,p_item_rec.cycle_count_enabled_flag
1029 ,p_item_rec.item_type
1030 ,p_item_rec.model_config_clause_name
1031 ,p_item_rec.ship_model_complete_flag
1032 ,p_item_rec.mrp_planning_code
1033 ,p_item_rec.return_inspection_requirement
1034 ,p_item_rec.ato_forecast_control
1035 ,p_item_rec.release_time_fence_code
1036 ,p_item_rec.release_time_fence_days
1037 ,p_item_rec.container_item_flag
1038 ,p_item_rec.vehicle_item_flag
1039 ,p_item_rec.maximum_load_weight
1040 ,p_item_rec.minimum_fill_percent
1041 ,p_item_rec.container_type_code
1042 ,p_item_rec.internal_volume
1043 ,p_item_rec.global_attribute_category
1044 ,p_item_rec.global_attribute1
1045 ,p_item_rec.global_attribute2
1046 ,p_item_rec.global_attribute3
1047 ,p_item_rec.global_attribute4
1048 ,p_item_rec.global_attribute5
1049 ,p_item_rec.global_attribute6
1050 ,p_item_rec.global_attribute7
1051 ,p_item_rec.global_attribute8
1052 ,p_item_rec.global_attribute9
1053 ,p_item_rec.global_attribute10
1054 ,p_item_rec.purchasing_tax_code
1055 ,p_item_rec.attribute6
1056 ,p_item_rec.attribute7
1057 ,p_item_rec.attribute8
1058 ,p_item_rec.attribute9
1059 ,p_item_rec.attribute10
1060 ,p_item_rec.attribute11
1061 ,p_item_rec.attribute12
1062 ,p_item_rec.attribute13
1063 ,p_item_rec.attribute14
1064 ,p_item_rec.attribute15
1065 ,p_item_rec.purchasing_item_flag
1066 ,p_item_rec.shippable_item_flag
1067 ,p_item_rec.customer_order_flag
1068 ,p_item_rec.internal_order_flag
1069 ,p_item_rec.inventory_item_flag
1070 ,p_item_rec.eng_item_flag
1071 ,p_item_rec.inventory_asset_flag
1072 ,p_item_rec.purchasing_enabled_flag
1073 ,p_item_rec.customer_order_enabled_flag
1074 ,p_item_rec.internal_order_enabled_flag
1075 ,p_item_rec.so_transactions_flag
1076 ,p_item_rec.mtl_transactions_enabled_flag
1077 ,p_item_rec.stock_enabled_flag
1078 ,p_item_rec.bom_enabled_flag
1079 ,p_item_rec.build_in_wip_flag
1080 ,p_item_rec.revision_qty_control_code
1081 ,p_item_rec.item_catalog_group_id
1082 ,p_item_rec.catalog_status_flag
1083 ,p_item_rec.returnable_flag
1084 ,p_item_rec.default_shipping_org
1085 ,p_item_rec.collateral_flag
1086 ,p_item_rec.taxable_flag
1087 ,p_item_rec.qty_rcv_exception_code
1088 ,p_item_rec.allow_item_desc_update_flag
1089 ,p_item_rec.inspection_required_flag
1090 ,p_item_rec.receipt_required_flag
1091 ,p_item_rec.market_price
1092 ,p_item_rec.hazard_class_id
1093 ,p_item_rec.rfq_required_flag
1094 ,p_item_rec.qty_rcv_tolerance
1095 ,p_item_rec.list_price_per_unit
1096 ,p_item_rec.un_number_id
1097 ,p_item_rec.price_tolerance_percent
1098 ,p_item_rec.asset_category_id
1099 ,p_item_rec.rounding_factor
1100 ,p_item_rec.unit_of_issue
1101 ,p_item_rec.enforce_ship_to_location_code
1102 ,p_item_rec.allow_substitute_receipts_flag
1103 ,p_item_rec.allow_unordered_receipts_flag
1104 ,p_item_rec.allow_express_delivery_flag
1105 ,p_item_rec.days_early_receipt_allowed
1106 ,p_item_rec.days_late_receipt_allowed
1107 ,p_item_rec.receipt_days_exception_code
1108 ,p_item_rec.receiving_routing_id
1109 ,p_item_rec.invoice_close_tolerance
1110 ,p_item_rec.receive_close_tolerance
1111 ,p_item_rec.auto_lot_alpha_prefix
1112 ,p_item_rec.start_auto_lot_number
1113 ,p_item_rec.lot_control_code
1114 ,p_item_rec.shelf_life_code
1115 ,p_item_rec.shelf_life_days
1116 ,p_item_rec.serial_number_control_code
1117 ,p_item_rec.start_auto_serial_number
1118 ,p_item_rec.auto_serial_alpha_prefix
1119 ,p_item_rec.source_type
1120 ,p_item_rec.source_organization_id
1121 ,p_item_rec.source_subinventory
1122 ,p_item_rec.expense_account
1123 ,p_item_rec.encumbrance_account
1124 ,p_item_rec.restrict_subinventories_code
1125 ,p_item_rec.unit_weight
1126 ,p_item_rec.weight_uom_code
1127 ,p_item_rec.volume_uom_code
1128 ,p_item_rec.unit_volume
1129 ,p_item_rec.restrict_locators_code
1130 ,p_item_rec.location_control_code
1131 ,p_item_rec.shrinkage_rate
1132 ,p_item_rec.acceptable_early_days
1133 ,p_item_rec.planning_time_fence_code
1134 ,p_item_rec.demand_time_fence_code
1135 ,p_item_rec.lead_time_lot_size
1136 ,p_item_rec.std_lot_size
1137 ,p_item_rec.cum_manufacturing_lead_time
1138 ,p_item_rec.overrun_percentage
1139 ,p_item_rec.mrp_calculate_atp_flag
1140 ,p_item_rec.acceptable_rate_increase
1141 ,p_item_rec.acceptable_rate_decrease
1142 ,p_item_rec.cumulative_total_lead_time
1143 ,p_item_rec.planning_time_fence_days
1144 ,p_item_rec.demand_time_fence_days
1145 ,p_item_rec.end_assembly_pegging_flag
1146 ,p_item_rec.repetitive_planning_flag
1147 ,p_item_rec.planning_exception_set
1148 ,p_item_rec.bom_item_type
1149 ,p_item_rec.pick_components_flag
1150 ,p_item_rec.replenish_to_order_flag
1151 ,p_item_rec.base_item_id
1152 ,p_item_rec.atp_components_flag
1153 ,p_item_rec.atp_flag
1154 ,p_item_rec.fixed_lead_time
1155 ,p_item_rec.variable_lead_time
1156 ,p_item_rec.wip_supply_locator_id
1157 ,p_item_rec.wip_supply_type
1158 ,p_item_rec.wip_supply_subinventory
1159 ,p_item_rec.cost_of_sales_account
1160 ,p_item_rec.sales_account
1161 ,p_item_rec.default_include_in_rollup_flag
1162 ,p_item_rec.inventory_item_status_code
1163 ,p_item_rec.inventory_planning_code
1164 ,p_item_rec.planner_code
1165 ,p_item_rec.planning_make_buy_code
1166 ,p_item_rec.fixed_lot_multiplier
1167 ,p_item_rec.rounding_control_type
1168 ,p_item_rec.carrying_cost
1169 ,p_item_rec.postprocessing_lead_time
1170 ,p_item_rec.preprocessing_lead_time
1171 ,p_item_rec.summary_flag
1172 ,p_item_rec.enabled_flag
1173 ,p_item_rec.start_date_active
1174 ,p_item_rec.end_date_active
1175 ,p_item_rec.buyer_id
1176 ,p_item_rec.accounting_rule_id
1177 ,p_item_rec.invoicing_rule_id
1178 ,p_item_rec.over_shipment_tolerance
1179 ,p_item_rec.under_shipment_tolerance
1180 ,p_item_rec.over_return_tolerance
1181 ,p_item_rec.under_return_tolerance
1182 ,p_item_rec.equipment_type
1183 ,p_item_rec.recovered_part_disp_code
1184 ,p_item_rec.defect_tracking_on_flag
1185 ,p_item_rec.event_flag
1186 ,p_item_rec.electronic_flag
1187 ,p_item_rec.downloadable_flag
1188 ,p_item_rec.vol_discount_exempt_flag
1189 ,p_item_rec.coupon_exempt_flag
1190 ,p_item_rec.comms_nl_trackable_flag
1191 ,p_item_rec.asset_creation_code
1192 ,p_item_rec.comms_activation_reqd_flag
1193 ,p_item_rec.orderable_on_web_flag
1194 ,p_item_rec.back_orderable_flag
1195 ,p_item_rec.web_status
1196 ,p_item_rec.indivisible_flag
1197 ,p_item_rec.dimension_uom_code
1198 ,p_item_rec.unit_length
1199 ,p_item_rec.unit_width
1200 ,p_item_rec.unit_height
1201 ,p_item_rec.bulk_picked_flag
1202 ,p_item_rec.lot_status_enabled
1203 ,p_item_rec.default_lot_status_id
1204 ,p_item_rec.serial_status_enabled
1205 ,p_item_rec.default_serial_status_id
1206 ,p_item_rec.lot_split_enabled
1207 ,p_item_rec.lot_merge_enabled
1208 ,p_item_rec.inventory_carry_penalty
1209 ,p_item_rec.operation_slack_penalty
1210 ,p_item_rec.financing_allowed_flag
1211 ,p_item_rec.eam_item_type
1212 ,p_item_rec.eam_activity_type_code
1213 ,p_item_rec.eam_activity_cause_code
1214 ,p_item_rec.eam_act_notification_flag
1215 ,p_item_rec.eam_act_shutdown_status
1216 ,p_item_rec.dual_uom_control
1217 ,p_item_rec.secondary_uom_code
1218 ,p_item_rec.dual_uom_deviation_high
1219 ,p_item_rec.dual_uom_deviation_low
1220 ,p_item_rec.contract_item_type_code
1221 ,p_item_rec.subscription_depend_flag
1222 ,p_item_rec.serv_req_enabled_code
1223 ,p_item_rec.serv_billing_enabled_flag
1224 ,p_item_rec.serv_importance_level
1225 ,p_item_rec.planned_inv_point_flag
1226 ,p_item_rec.lot_translate_enabled
1227 ,p_item_rec.default_so_source_type
1228 ,p_item_rec.create_supply_flag
1229 ,p_item_rec.substitution_window_code
1230 ,p_item_rec.substitution_window_days
1231 ,p_item_rec.ib_item_instance_class
1232 ,p_item_rec.config_model_type
1233 ,p_item_rec.lot_substitution_enabled
1234 ,p_item_rec.minimum_license_quantity
1235 ,p_item_rec.eam_activity_source_code
1236 ,p_item_rec.tracking_quantity_ind
1237 ,p_item_rec.ont_pricing_qty_source
1238 ,p_item_rec.secondary_default_ind
1239 ,p_item_rec.option_specific_sourced
1240 ,p_item_rec.config_orgs
1241 ,p_item_rec.config_match
1242 ,p_item_rec.segment1
1243 ,p_item_rec.segment2
1244 ,p_item_rec.segment3
1245 ,p_item_rec.segment4
1246 ,p_item_rec.segment5
1247 ,p_item_rec.segment6
1248 ,p_item_rec.segment7
1249 ,p_item_rec.segment8
1250 ,p_item_rec.segment9
1251 ,p_item_rec.segment10
1252 ,p_item_rec.segment11
1253 ,p_item_rec.segment12
1254 ,p_item_rec.segment13
1255 ,p_item_rec.segment14
1256 ,p_item_rec.segment15
1257 ,p_item_rec.segment16
1258 ,p_item_rec.segment17
1259 ,p_item_rec.segment18
1260 ,p_item_rec.segment19
1261 ,p_item_rec.segment20
1262 ,p_item_rec.attribute_category
1263 ,p_item_rec.attribute1
1264 ,p_item_rec.attribute2
1265 ,p_item_rec.attribute3
1266 ,p_item_rec.attribute4
1267 ,p_item_rec.attribute5
1268 ,p_item_rec.created_by
1269 ,p_item_rec.last_updated_by
1270 ,p_item_rec.last_update_login
1271 ,p_item_rec.lifecycle_id
1272 ,p_item_rec.current_phase_id
1273 ,p_item_rec.vmi_minimum_units
1274 ,p_item_rec.vmi_minimum_days
1275 ,p_item_rec.vmi_maximum_units
1276 ,p_item_rec.vmi_maximum_days
1277 ,p_item_rec.vmi_fixed_order_quantity
1278 ,p_item_rec.so_authorization_flag
1279 ,p_item_rec.consigned_flag
1280 ,p_item_rec.asn_autoexpire_flag
1281 ,p_item_rec.vmi_forecast_type
1282 ,p_item_rec.forecast_horizon
1283 ,p_item_rec.exclude_from_budget_flag
1284 ,p_item_rec.critical_component_flag
1285 ,p_item_rec.continous_transfer
1286 ,p_item_rec.convergence
1287 ,p_item_rec.divergence
1288 ,p_item_rec.drp_planned_flag
1289 ,p_item_rec.days_tgt_inv_supply
1290 ,p_item_rec.days_tgt_inv_window
1291 ,p_item_rec.days_max_inv_supply
1292 ,p_item_rec.days_max_inv_window
1293 ,p_item_rec.lot_divisible_flag
1294 ,p_item_rec.grade_control_flag
1295 ,p_item_rec.default_grade
1296 ,p_item_rec.child_lot_flag
1297 ,p_item_rec.parent_child_generation_flag
1298 ,p_item_rec.child_lot_prefix
1299 ,p_item_rec.child_lot_starting_number
1300 ,p_item_rec.child_lot_validation_flag
1301 ,p_item_rec.copy_lot_attribute_flag
1302 ,p_item_rec.process_execution_enabled_flag
1303 ,p_item_rec.process_costing_enabled_flag
1304 ,p_item_rec.retest_interval
1305 ,p_item_rec.expiration_action_interval
1306 ,p_item_rec.expiration_action_code
1307 ,p_item_rec.maturity_days
1308 ,p_item_rec.hold_days
1309 ,p_item_rec.process_quality_enabled_flag
1310 ,p_item_rec.recipe_enabled_flag
1311 ,p_item_rec.process_supply_subinventory
1312 ,p_item_rec.process_supply_locator_id
1313 ,p_item_rec.process_yield_subinventory
1314 ,p_item_rec.process_yield_locator_id
1315 ,p_item_rec.hazardous_material_flag
1316 ,p_item_rec.cas_number
1317 ,p_item_rec.attribute16
1318 ,p_item_rec.attribute17
1319 ,p_item_rec.attribute18
1320 ,p_item_rec.attribute19
1321 ,p_item_rec.attribute20
1322 ,p_item_rec.attribute21
1323 ,p_item_rec.attribute22
1324 ,p_item_rec.attribute23
1325 ,p_item_rec.attribute24
1326 ,p_item_rec.attribute25
1327 ,p_item_rec.attribute26
1328 ,p_item_rec.attribute27
1329 ,p_item_rec.attribute28
1330 ,p_item_rec.attribute29
1331 ,p_item_rec.attribute30
1332 ,p_item_rec.repair_leadtime
1333 ,p_item_rec.repair_yield
1334 ,p_item_rec.repair_program
1335 ,p_item_rec.preposition_point
1336 ,p_item_rec.charge_periodicity_code
1337 ,p_item_rec.outsourced_assembly
1338 ,p_item_rec.subcontracting_component
1339 ,x_master_status
1340 FROM mtl_system_items_b b, mtl_system_items_tl t
1341 WHERE b.inventory_item_id = t.inventory_item_id
1342 AND b.organization_id = t.organization_id
1343 AND b.inventory_item_id = p_item_rec.inventory_item_id
1344 AND b.organization_id = p_master_org_id
1345 AND t.language = userenv('LANG');
1346
1347 /* Bug 7419728 : Commented the below sql */
1348 /* SELECT DECODE(p_item_rec.tracking_quantity_ind,'P',1,
1349 DECODE(NVL(p_item_rec.secondary_default_ind,'X'),'F',2,'D',3,4))
1350 INTO p_item_rec.DUAL_UOM_CONTROL
1351 FROM DUAL; */
1352
1353
1354 p_item_rec.process_supply_subinventory := NULL;
1355 p_item_rec.process_supply_locator_id := NULL;
1356 p_item_rec.process_yield_subinventory := NULL;
1357 p_item_rec.process_yield_locator_id := NULL;
1358 p_item_rec.wip_supply_locator_id := NULL;
1359 p_item_rec.wip_supply_subinventory := NULL;
1360 p_item_rec.base_warranty_service_id := NULL;
1361 p_item_rec.planner_code := NULL;
1362 p_item_rec.planning_exception_set := NULL;
1363 p_item_rec.product_family_item_id := NULL;
1364
1365 END build_item_record;
1366
1367 END EGO_ITEM_ORG_ASSIGN_PVT ;