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