[Home] [Help]
PACKAGE BODY: APPS.INV_ITEM_STATUS_CP
Source
1 PACKAGE BODY INV_ITEM_STATUS_CP AS
2 /* $Header: INVCIPSB.pls 120.15.12020000.4 2012/08/31 02:26:04 ecchang ship $ */
3
4 G_STATUS_CODE VARCHAR2(50) := 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
5 G_STOCK_ENABLED VARCHAR2(50) := 'MTL_SYSTEM_ITEMS.STOCK_ENABLED_FLAG';
6 G_TRANSACTIONS_ENABLED VARCHAR2(50) := 'MTL_SYSTEM_ITEMS.MTL_TRANSACTIONS_ENABLED_FLAG';
7 G_PURCHASING_ENABLED VARCHAR2(50) := 'MTL_SYSTEM_ITEMS.PURCHASING_ENABLED_FLAG';
8 G_INVOICE_ENABLED VARCHAR2(50) := 'MTL_SYSTEM_ITEMS.INVOICE_ENABLED_FLAG';
9 G_BUILD_IN_WIP VARCHAR2(50) := 'MTL_SYSTEM_ITEMS.BUILD_IN_WIP_FLAG';
10 G_CUSTOMER_ENABLED VARCHAR2(50) := 'MTL_SYSTEM_ITEMS.CUSTOMER_ORDER_ENABLED_FLAG';
11 G_INTERNAL_ENABLED VARCHAR2(50) := 'MTL_SYSTEM_ITEMS.INTERNAL_ORDER_ENABLED_FLAG';
12 G_BOM_ENABLED VARCHAR2(50) := 'MTL_SYSTEM_ITEMS.BOM_ENABLED_FLAG';
13 G_PKG_NAME VARCHAR2(50) := 'INV_ITEM_STATUS_CP';
14
15 G_ITEM NUMBER := 1;
16 G_ITEM_ORG NUMBER := 2;
17 G_PWIDTH NUMBER := 132;
18 G_SO_RESERVABLE NUMBER := 1;
19 G_BOM_STANDARD NUMBER := 4;
20 G_BOM_MODEL NUMBER := 1;
21 G_UNDER_STATUS_CONTROL NUMBER := 1;
22 G_DEFAULT_CONTROL NUMBER := 2;
23 G_NO_CONTROL NUMBER := 3; -- Added for Bug-6531777
24 G_ARG_ORGANIZATION_ID NUMBER := 1;
25 G_ARG_ITEM_ID NUMBER := 2;
26
27 G_USER_ID NUMBER := -1;
28 G_LOGIN_ID NUMBER := -1;
29 G_PROG_APPID NUMBER := -1;
30 G_PROG_ID NUMBER := -1;
31 G_REQUEST_ID NUMBER := -1;
32
33 G_SUCCESS CONSTANT NUMBER := 0;
34 G_WARNING CONSTANT NUMBER := 1;
35 G_ERROR CONSTANT NUMBER := 2;
36
37 --Added for 5230429
38 G_transactable_status BOOLEAN := FALSE;
39 --commented for bug 5479302
40 -- G_BOM_status NUMBER := 0;
41
42 /* Start Bug 3713912 */
43 G_RECIPE_ENABLED VARCHAR2(50) := 'MTL_SYSTEM_ITEMS.RECIPE_ENABLED_FLAG';
44 G_PROCESS_EXECUTION_ENABLED VARCHAR2(50) := 'MTL_SYSTEM_ITEMS.PROCESS_EXECUTION_ENABLED_FLAG';
45 /* End Bug 3713912 */
46
47 -- Added for bug 5230594
48 G_TRANSACTABLE NUMBER := 0;
49 G_ORDERABLE NUMBER := 0;
50 G_PURCHASABLE NUMBER := 0;
51
52
53
54 PROCEDURE update_item_attributes(p_pending_status IN VARCHAR2
55 ,p_control_level IN NUMBER
56 ,p_Org_Id IN NUMBER
57 ,p_Item_id IN NUMBER
58 ,p_commit IN VARCHAR2:= FND_API.g_TRUE
59 ,p_return_status OUT NOCOPY BOOLEAN)
60
61 IS
62 /* Start Bug#7454766
63 Declaring Cursor c_get_values_from_msib to fetch values of INVENTORY_ITEM_STATUS_CODE, INTERNAL_ORDER_ENABLED_FLAG, PURCHASING_ENABLED_FLAG,
64 from mtl_system_items_b table.
65 */
66
67 CURSOR c_get_values_from_msib (p_item_id number,p_org_id number)
68 IS
69 SELECT INVENTORY_ITEM_STATUS_CODE, INTERNAL_ORDER_ENABLED_FLAG, PURCHASING_ENABLED_FLAG
70 FROM mtl_system_items_b msib
71 WHERE msib.inventory_item_id = p_Item_Id
72 AND msib.organization_id = p_Org_Id;
73
74 l_status_code mtl_system_items_b.INVENTORY_ITEM_STATUS_CODE%TYPE;
75 l_purch_enbl mtl_system_items_b.PURCHASING_ENABLED_FLAG%TYPE;
76 l_int_ordr_enbl mtl_system_items_b.INTERNAL_ORDER_ENABLED_FLAG%TYPE;
77
78 /* End Bug#7454766 */
79
80
81
82 CURSOR c_get_status_control (cp_attrib_name VARCHAR2)
83 IS
84 SELECT status_control_code
85 FROM mtl_item_attributes
86 WHERE attribute_name = cp_attrib_name;
87
88 l_stock_cntrl_level mtl_item_attributes.status_control_code%TYPE;
89 l_trans_cntrl_level mtl_item_attributes.status_control_code%TYPE;
90 l_purch_cntrl_level mtl_item_attributes.status_control_code%TYPE;
91 l_invoice_cntrl_level mtl_item_attributes.status_control_code%TYPE;
92 l_wip_cntrl_level mtl_item_attributes.status_control_code%TYPE;
93 l_cust_cntrl_level mtl_item_attributes.status_control_code%TYPE;
94 l_int_cntrl_level mtl_item_attributes.status_control_code%TYPE;
95 l_bom_cntrl_level mtl_item_attributes.status_control_code%TYPE;
96 l_row_temp NUMBER :=0;
97 /* Start Bug 3713912 */
98 l_recipe_cntrl_level mtl_item_attributes.status_control_code%TYPE;
99 l_process_exec_cntrl_level mtl_item_attributes.status_control_code%TYPE;
100 /* End Bug 3713912 */
101 is_transactable_upd NUMBER;
102 is_bom_enabled_upd NUMBER;
103
104 BEGIN
105
106 p_return_status := FALSE;
107
108 OPEN c_get_status_control(cp_attrib_name => G_STOCK_ENABLED);
109 FETCH c_get_status_control INTO l_stock_cntrl_level;
110 CLOSE c_get_status_control;
111
112 OPEN c_get_status_control(cp_attrib_name => G_TRANSACTIONS_ENABLED);
113 FETCH c_get_status_control INTO l_trans_cntrl_level;
114 CLOSE c_get_status_control;
115
116 OPEN c_get_status_control(cp_attrib_name => G_PURCHASING_ENABLED);
117 FETCH c_get_status_control INTO l_purch_cntrl_level;
118 CLOSE c_get_status_control;
119
120 OPEN c_get_status_control(cp_attrib_name => G_INVOICE_ENABLED);
121 FETCH c_get_status_control INTO l_invoice_cntrl_level;
122 CLOSE c_get_status_control;
123
124 OPEN c_get_status_control(cp_attrib_name => G_BUILD_IN_WIP);
125 FETCH c_get_status_control INTO l_wip_cntrl_level;
126 CLOSE c_get_status_control;
127
128 OPEN c_get_status_control(cp_attrib_name => G_CUSTOMER_ENABLED);
129 FETCH c_get_status_control INTO l_cust_cntrl_level;
130 CLOSE c_get_status_control;
131
132 OPEN c_get_status_control(cp_attrib_name => G_INTERNAL_ENABLED);
133 FETCH c_get_status_control INTO l_int_cntrl_level;
134 CLOSE c_get_status_control;
135
136 OPEN c_get_status_control(cp_attrib_name => G_BOM_ENABLED);
137 FETCH c_get_status_control INTO l_bom_cntrl_level;
138 CLOSE c_get_status_control;
139 /* Start Bug 3713912 */
140 OPEN c_get_status_control(cp_attrib_name => G_RECIPE_ENABLED);
141 FETCH c_get_status_control INTO l_recipe_cntrl_level;
142 CLOSE c_get_status_control;
143 OPEN c_get_status_control(cp_attrib_name => G_PROCESS_EXECUTION_ENABLED);
144 FETCH c_get_status_control INTO l_process_exec_cntrl_level;
145 CLOSE c_get_status_control;
146 /* End Bug 3713912 */
147
148 /* Check to see wether the row is locked */
149
150 SELECT 1 INTO l_row_temp
151 FROM mtl_system_items msi
152 WHERE inventory_item_id = p_Item_Id
153 AND ((p_control_level = G_ITEM_ORG and msi.organization_id = p_Org_Id)
154 OR
155 (p_control_level = G_ITEM
156 AND msi.organization_id IN
157 (SELECT p2.organization_id
158 FROM mtl_parameters p1,
159 mtl_parameters p2
160 WHERE p1.organization_id = p_Org_Id
161 AND p1.master_organization_id = p2.master_organization_id)))
162 AND rownum < 2
163 FOR UPDATE NOWAIT;
164
165 /* 5523531 - Condition only applies when Transactable flag of item is changed by the status */
166 SELECT count(*) INTO is_transactable_upd
167 FROM DUAL
168 WHERE EXISTS
169 (SELECT 'X' FROM mtl_system_items
170 WHERE inventory_item_id = p_item_id
171 AND organization_id = p_org_id
172 AND mtl_transactions_enabled_flag <>
173 (SELECT attribute_value FROM mtl_status_attribute_values
174 WHERE attribute_name = 'MTL_SYSTEM_ITEMS.MTL_TRANSACTIONS_ENABLED_FLAG'
175 AND inventory_item_status_code = p_pending_status));
176
177 --Added for Bug: 5230429
178 if (l_trans_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL) AND is_transactable_upd = 1) then
179 G_transactable_status := INV_ATTRIBUTE_CONTROL_PVT.transactable_uncheck(p_Org_Id,p_Item_Id);
180 end if;
181 /* 5523531 - Condition only applies when Transactable flag of item is changed by the status */
182 SELECT count(*) INTO is_bom_enabled_upd
183 FROM mtl_status_attribute_values
184 WHERE attribute_name = 'MTL_SYSTEM_ITEMS.BOM_ENABLED_FLAG'
185 AND attribute_value = 'N'
186 AND inventory_item_status_code = p_pending_status;
187
188 /*commented for bug 5479302
189 if(l_bom_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL) AND is_bom_enabled_upd = 1) then
190 select count(*)
191 into G_BOM_status
192 from dual
193 where exists
194 ( select 'x'
195 from bom_bill_of_materials bom
196 where bom.assembly_item_id = p_Item_Id
197 and bom.organization_id in
198 ( select organization_id
199 from mtl_parameters
200 where master_organization_id = p_Org_Id
201 and 1 = l_bom_cntrl_level
202 union all
203 select organization_id
204 from mtl_parameters
205 where organization_id = p_Org_Id
206 and 2 =l_bom_cntrl_level));
207 end if; */
208
209 /* Bug: 5230594
210 Following SQL checks if the pending status being applied is unchecking a status attribute which is required by an enabled
211 dependent attributes and accordingly selects a value of 1 in the corresponding global variable
212 */
213 SELECT Sum(Decode(mav.attribute_name,G_TRANSACTIONS_ENABLED,Decode(mav.attribute_value,'N',Decode(msi.check_shortages_flag,'Y',1,0),0),0)) uncheck_transactable_err,
214 Sum(Decode(mav.attribute_name,G_PURCHASING_ENABLED ,Decode(mav.attribute_value,'N',Decode(msi.default_so_source_type,'EXTERNAL',1,0),0),0)) uncheck_purchasable_err,
215 Sum(Decode(mav.attribute_name,G_CUSTOMER_ENABLED ,Decode(mav.attribute_value,'N',Decode(msi.orderable_on_web_flag,'Y',1,0),0),0)) uncheck_orderable_err
216 INTO G_TRANSACTABLE
217 ,G_PURCHASABLE
218 ,G_ORDERABLE
219 FROM mtl_system_items_b msi
220 ,mtl_status_attribute_values mav
221 WHERE msi.inventory_item_id = p_Item_Id
222 AND msi.organization_id = p_Org_Id
223 AND mav.inventory_item_status_code = p_pending_status
224 AND mav.attribute_name IN (G_TRANSACTIONS_ENABLED,G_PURCHASING_ENABLED,G_CUSTOMER_ENABLED);
225
226 IF ( (NOT G_transactable_status) --and (G_BOM_status =0)
227 -- Condition OR l_trans_cntrl_level = G_NO _CONTROL added for Bug-6531777
228 and (((G_TRANSACTABLE = 0) and l_trans_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL)) OR l_trans_cntrl_level = G_NO_CONTROL)
229 and (((G_ORDERABLE = 0) and l_cust_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL)) OR l_cust_cntrl_level = G_NO_CONTROL)
230 and (((G_PURCHASABLE = 0) and l_purch_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL)) OR l_purch_cntrl_level = G_NO_CONTROL)
231 ) THEN
232
233 /* Lock is success - Update Item status */
234
235 UPDATE mtl_system_items msi
236 SET (inventory_item_status_code,
237 last_update_date,
238 last_updated_by,
239 last_update_login) =
240 (SELECT p_pending_status,
241 sysdate,
242 G_USER_ID,
243 G_USER_ID
244 FROM mtl_status_attribute_values v,
245 mtl_item_attributes a
246 WHERE v.inventory_item_status_code = p_pending_status
247 AND a.attribute_name = G_STOCK_ENABLED
248 AND a.attribute_name = v.attribute_name)
249 WHERE msi.inventory_item_id = p_Item_Id
250 AND ((p_control_level = G_ITEM_ORG and msi.organization_id = p_Org_Id)
251 OR
252 (p_control_level = G_ITEM
253 AND msi.organization_id IN
254 (SELECT p2.organization_id
255 FROM mtl_parameters p1,
256 mtl_parameters p2
257 WHERE p1.organization_id = p_Org_Id
258 AND p1.master_organization_id = p2.master_organization_id)));
259
260 /* Update all the Eight Item Attributes */
261
262 UPDATE mtl_system_items msi
263 SET msi.stock_enabled_flag =
264 ( SELECT DECODE(mti.inventory_item_flag,'N','N',v.attribute_value)
265 FROM mtl_status_attribute_values v,
266 mtl_item_attributes a,
267 mtl_system_items mti
268 WHERE v.inventory_item_status_code = p_pending_status
269 AND a.attribute_name = G_STOCK_ENABLED
270 AND a.attribute_name = v.attribute_name
271 AND mti.inventory_item_id = p_Item_Id
272 AND mti.organization_id = p_Org_Id)
273 WHERE l_stock_cntrl_level IN ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL)
274 AND msi.inventory_item_id = p_Item_Id
275 AND msi.inventory_item_flag = 'Y'
276 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
277 OR
278 (p_control_level = G_ITEM
279 AND msi.organization_id IN
280 (SELECT p2.organization_id
281 FROM mtl_parameters p1,
282 mtl_parameters p2
283 WHERE p1.organization_id = p_Org_Id
284 AND p1.master_organization_id = p2.master_organization_id)));
285
286 UPDATE mtl_system_items msi
287 SET msi.mtl_transactions_enabled_flag =
288 ( SELECT DECODE (mti.stock_enabled_flag,'N','N', v.attribute_value)
289 FROM mtl_status_attribute_values v,
290 mtl_item_attributes a,
291 mtl_system_items mti
292 WHERE v.inventory_item_status_code = p_pending_status
293 AND a.attribute_name = G_TRANSACTIONS_ENABLED
294 AND a.attribute_name = v.attribute_name
295 AND mti.inventory_item_id = p_Item_Id
296 AND mti.organization_id = p_Org_Id)
297 WHERE l_trans_cntrl_level IN ( G_UNDER_STATUS_CONTROL, G_DEFAULT_CONTROL)
298 AND msi.inventory_item_id = p_Item_Id
299 -- AND msi.stock_enabled_flag = 'Y' /* commented for bug 3375455 */
300 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
301 OR
302 (p_control_level = G_ITEM
303 AND msi.organization_id IN
304 (SELECT p2.organization_id
305 FROM mtl_parameters p1,
306 mtl_parameters p2
307 WHERE p1.organization_id = p_Org_Id
308 AND p1.master_organization_id = p2.master_organization_id)));
309
310 /* Updating pending Item Status */
311 UPDATE mtl_system_items msi
312 SET msi.purchasing_enabled_flag =
313 (SELECT DECODE(mti.purchasing_item_flag,'N','N',v.attribute_value)
314 FROM mtl_status_attribute_values v,
315 mtl_item_attributes a,
316 mtl_system_items mti
317 WHERE v.inventory_item_status_code = p_pending_status
318 AND a.attribute_name = G_PURCHASING_ENABLED
319 AND a.attribute_name = v.attribute_name
320 AND mti.inventory_item_id = p_Item_Id
321 AND mti.organization_id = p_Org_Id)
322 WHERE l_purch_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
323 AND msi.inventory_item_id = p_Item_Id
324 AND msi.purchasing_item_flag = 'Y'
325 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
326 OR
327 (p_control_level = G_ITEM
328 AND msi.organization_id IN
329 (SELECT p2.organization_id
330 FROM mtl_parameters p1,
331 mtl_parameters p2
332 WHERE p1.organization_id = p_Org_Id
333 AND p1.master_organization_id = p2.master_organization_id)));
334
335 UPDATE mtl_system_items msi
336 SET msi.invoice_enabled_flag =
337 ( SELECT DECODE(mti.invoiceable_item_flag,'N','N',v.attribute_value)
338 FROM mtl_status_attribute_values v,
339 mtl_item_attributes a,
340 mtl_system_items mti
341 WHERE v.inventory_item_status_code = p_pending_status
342 AND a.attribute_name = G_INVOICE_ENABLED
343 AND a.attribute_name = v.attribute_name
344 AND mti.inventory_item_id = p_Item_Id
345 AND mti.organization_id = p_Org_Id)
346 WHERE l_invoice_cntrl_level in ( G_UNDER_STATUS_CONTROL, G_DEFAULT_CONTROL )
347 AND msi.inventory_item_id = p_Item_Id
348 AND msi.invoiceable_item_flag = 'Y'
349 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
350 OR
351 (p_control_level = G_ITEM
352 AND msi.organization_id IN
353 (SELECT p2.organization_id
354 FROM mtl_parameters p1,
355 mtl_parameters p2
356 WHERE p1.organization_id = p_Org_Id
357 AND p1.master_organization_id = p2.master_organization_id)));
358
359 UPDATE mtl_system_items msi
360 SET msi.build_in_wip_flag =
361 ( SELECT DECODE(mti.inventory_item_flag,'N','N',decode(mti.bom_item_type,4,v.attribute_value,'N'))
362 FROM mtl_status_attribute_values v,
363 mtl_item_attributes a,
364 mtl_system_items mti
365 WHERE v.inventory_item_status_code = p_pending_status
366 AND a.attribute_name = G_BUILD_IN_WIP
367 AND a.attribute_name = v.attribute_name
368 AND mti.inventory_item_id = p_Item_Id
369 AND mti.organization_id = p_Org_Id)
370 WHERE l_wip_cntrl_level IN ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
371 AND msi.inventory_item_id = p_Item_Id
372 AND msi.inventory_item_flag = 'Y'
373 AND msi.bom_item_type = 4
374 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
375 OR
376 (p_control_level = G_ITEM
377 AND msi.organization_id IN
378 (SELECT p2.organization_id
379 FROM mtl_parameters p1,
380 mtl_parameters p2
381 WHERE p1.organization_id = p_Org_Id
382 AND p1.master_organization_id = p2.master_organization_id)));
383
384 UPDATE mtl_system_items msi
385 SET msi.customer_order_enabled_flag =
386 ( SELECT DECODE(mti.customer_order_flag,'N','N',v.attribute_value)
387 FROM mtl_status_attribute_values v,
388 mtl_item_attributes a,
389 mtl_system_items mti
390 WHERE v.inventory_item_status_code = p_pending_status
391 AND a.attribute_name = G_CUSTOMER_ENABLED
392 AND a.attribute_name = v.attribute_name
393 AND mti.inventory_item_id = p_Item_Id
394 AND mti.organization_id = p_Org_Id)
395 WHERE l_cust_cntrl_level IN (G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL)
396 AND msi.inventory_item_id = p_Item_Id
397 AND msi.customer_order_flag = 'Y'
398 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
399 OR
400 (p_control_level = G_ITEM
401 AND msi.organization_id IN
402 (SELECT p2.organization_id
403 FROM mtl_parameters p1,
404 mtl_parameters p2
405 WHERE p1.organization_id = p_Org_Id
406 AND p1.master_organization_id = p2.master_organization_id)));
407
408
409 UPDATE mtl_system_items msi
410 SET msi.internal_order_enabled_flag =
411 ( SELECT DECODE(mti.internal_order_flag,'N','N',v.attribute_value)
412 FROM mtl_status_attribute_values v,
413 mtl_item_attributes a,
414 mtl_system_items mti
415 WHERE v.inventory_item_status_code = p_pending_status
416 AND a.attribute_name = G_INTERNAL_ENABLED
417 AND a.attribute_name = v.attribute_name
418 AND mti.inventory_item_id = p_Item_Id
419 AND mti.organization_id = p_Org_Id)
420 WHERE l_int_cntrl_level IN ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
421 AND msi.inventory_item_id = p_Item_Id
422 AND msi.internal_order_flag = 'Y'
423 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
424 OR
425 (p_control_level = G_ITEM
426 AND msi.organization_id IN
427 (SELECT p2.organization_id
428 FROM mtl_parameters p1,
429 mtl_parameters p2
430 WHERE p1.organization_id = p_Org_Id
431 AND p1.master_organization_id = p2.master_organization_id)));
432
433
434 UPDATE mtl_system_items msi
435 SET msi.bom_enabled_flag =
436 ( SELECT v.attribute_value --Bug:3546140DECODE(mti.inventory_item_flag,'N','N',v.attribute_value)
437 FROM mtl_status_attribute_values v,
438 mtl_item_attributes a
439 --Bug:3546140 ,mtl_system_items mti
440 WHERE v.inventory_item_status_code = p_pending_status
441 AND a.attribute_name = G_BOM_ENABLED
442 AND a.attribute_name = v.attribute_name
443 -- AND mti.inventory_item_id = p_Item_Id
444 -- AND mti.organization_id = p_Org_Id
445 )
446 WHERE l_bom_cntrl_level IN ( G_UNDER_STATUS_CONTROL, G_DEFAULT_CONTROL )
447 AND msi.inventory_item_id = p_Item_Id
448 --Bug:3546140 AND msi.inventory_item_flag = 'Y'
449 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
450 OR
451 (p_control_level = G_ITEM
452 AND msi.organization_id IN
453 (SELECT p2.organization_id
454 FROM mtl_parameters p1,
455 mtl_parameters p2
456 WHERE p1.organization_id = p_Org_Id
457 AND p1.master_organization_id = p2.master_organization_id)));
458
459 /* Start Bug 3713912 */
460 --update recipe enabled flag based on status
461 UPDATE mtl_system_items msi
462 SET msi.recipe_enabled_flag =
463 (SELECT v.attribute_value
464 FROM mtl_status_attribute_values v,
465 mtl_item_attributes a
466 WHERE v.inventory_item_status_code = p_pending_status
467 AND a.attribute_name = G_RECIPE_ENABLED
468 AND a.attribute_name = v.attribute_name)
469 WHERE l_recipe_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
470 AND msi.inventory_item_id = p_Item_Id
471 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
472 OR
473 (p_control_level = G_ITEM
474 AND msi.organization_id IN
475 (SELECT p2.organization_id
476 FROM mtl_parameters p1,
477 mtl_parameters p2
478 WHERE p1.organization_id = p_Org_Id
479 AND p1.master_organization_id = p2.master_organization_id)));
480
481 --update process_execution enabled flag based on status
482 --Bug 5346921 process execution enabled should not be enabled if either inventory flag is 'N' or recipe enabled is 'N'
483 UPDATE mtl_system_items msi
484 SET msi.PROCESS_EXECUTION_ENABLED_FLAG =
485 (SELECT decode(msi.inventory_item_flag,'N','N',
486 decode(msi.recipe_enabled_flag,'N','N',v.attribute_value))
487 FROM mtl_status_attribute_values v,
488 mtl_item_attributes a
489 WHERE v.inventory_item_status_code = p_pending_status
490 AND a.attribute_name = G_PROCESS_EXECUTION_ENABLED
491 AND a.attribute_name = v.attribute_name)
492 WHERE l_process_exec_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
493 AND msi.inventory_item_id = p_Item_Id
494 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
495 OR
496 (p_control_level = G_ITEM
497 AND msi.organization_id IN
498 (SELECT p2.organization_id
499 FROM mtl_parameters p1,
500 mtl_parameters p2
501 WHERE p1.organization_id = p_Org_Id
502 AND p1.master_organization_id = p2.master_organization_id)));
503
504 /* End Bug 3713912 */
505 UPDATE mtl_pending_item_status
506 SET pending_flag = 'N' ,
507 implemented_date = SYSDATE ,
508 request_id = G_REQUEST_ID,
509 program_application_id = G_PROG_APPID,
510 program_id = G_PROG_ID,
511 program_update_date = SYSDATE,
512 last_update_login = G_LOGIN_ID,
513 last_updated_by = G_USER_ID
514 WHERE status_code = p_pending_status
515 AND organization_id = p_Org_Id
516 AND inventory_item_id = p_Item_Id
517 AND effective_date <= SYSDATE
518 AND pending_flag = 'Y';
519
520 /*Start Bug#7454766
521 Calling INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs with p_dml_type => 'DELETE', when l_status_code = 'Inactive', l_purch_enbl='N' AND l_int_ordr_enbl='N'
522 */
523 OPEN c_get_values_from_msib(p_item_id, p_org_id);
524 FETCH c_get_values_from_msib
525 INTO l_status_code, l_purch_enbl, l_int_ordr_enbl;
526 CLOSE c_get_values_from_msib;
527
528
529 -- fix bug 14473049 - incips update item statuses with pending statuses item not searchable in iproc
530 if(l_status_code <> 'Inactive') THEN
531 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
532 p_entity_type => 'ITEM'
533 ,p_dml_type => 'UPDATE'
534 ,p_inventory_item_id => p_Item_Id
535 ,p_organization_id => p_org_id
536 ,p_commit => true);
537 end if;
538 -- fix bug 14473049 - incips update item statuses with pending statuses item not searchable in iproc
539 /*if(l_status_code = 'Inactive') THEN*/ /*bug 13493707, and base bug is 9705250*/
540
541 if (l_purch_enbl='N' AND l_int_ordr_enbl='N') THEN
542
543 INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
544 p_entity_type => 'ITEM'
545 ,p_dml_type => 'DELETE'
546 ,p_inventory_item_id => p_Item_Id
547 ,p_organization_id => p_org_id);
548 end if;
549 /*end if;*/
550
551 /*End Bug#7454766 */
552
553 IF FND_API.To_Boolean (p_commit) THEN
554 COMMIT WORK;
555 END IF;
556
557 p_return_status := TRUE;
558
559 -- Added for 5230429
560 else
561 p_return_status := FALSE;
562 end if;
563
564 EXCEPTION
565 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
566 p_return_status := FALSE;
567 --Write to LOG unable to lock the Item.
568 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_LOCK_ITEM_FAILED'
569 ,p_token1 => 'ORG_ID'
570 ,p_value1 => TO_CHAR(p_Org_Id)
571 ,p_token2 => 'ITEM_ID'
572 ,p_value2 => TO_CHAR(p_Item_Id)
573 ,p_token3 => 'STATUS_CODE'
574 ,p_value3 => p_pending_status);
575 WHEN OTHERS THEN
576 p_return_status := FALSE;
577 --Write to LOG regarding the Exception.
578 INV_ITEM_MSG.Add_Message(p_Msg_Name => SUBSTRB(SQLERRM, 1,240));
579
580 END update_item_attributes;
581
582 PROCEDURE Process_Pending_Status(ERRBUF OUT NOCOPY VARCHAR2
583 ,RETCODE OUT NOCOPY NUMBER
584 ,p_Org_Id IN NUMBER := NULL
585 ,p_Item_Id IN NUMBER := NULL
586 ,p_commit IN VARCHAR2 := FND_API.g_TRUE
587 ,p_prog_appid IN NUMBER := NULL
588 ,p_prog_id IN NUMBER := NULL
589 ,p_request_id IN NUMBER := NULL
590 ,p_user_id IN NUMBER := NULL
591 ,p_login_id IN NUMBER := NULL
592 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
593 ,p_msg_logname IN VARCHAR2 := 'FILE')
594 IS
595 CURSOR c_status_pending_items (cp_org_id NUMBER
596 ,cp_item_id NUMBER)
597 IS
598 SELECT pis.status_code,
599 pis.inventory_item_id,
600 pis.organization_id
601 FROM mtl_pending_item_status pis,
602 mtl_item_status pit
603 WHERE pis.effective_date <= sysdate
604 AND pis.pending_flag = 'Y'
605 AND pis.inventory_item_id = nvl(cp_item_id,pis.inventory_item_id)
606 AND pis.organization_id = nvl(cp_org_id,pis.organization_id)
607 AND pis.status_code = pit.inventory_item_status_code
608 --13962600 : AND nvl(pit.disable_date,sysdate+1) > sysdate
609 --2800987 : When called from PLM, p_msg_logname is PLM_LOG, through CP it is FILE.
610 AND ((p_msg_logname ='FILE' AND lifecycle_id IS NULL) OR (p_msg_logname ='PLM_LOG'))
611 --2772279 -last_update_date,rowid in order clause
612 ORDER BY pis.effective_date,pis.last_update_date,pis.rowid;
613
614 --13962600: LCP should not change when any expried status exists
615 CURSOR c_get_expired_status (cp_org_id NUMBER
616 ,cp_item_id NUMBER)
617 IS
618 SELECT pis.status_code,
619 pis.inventory_item_id,
620 pis.organization_id
621 FROM mtl_pending_item_status pis,
622 mtl_item_status pit
623 WHERE pis.effective_date <= sysdate
624 AND pis.pending_flag = 'Y'
625 AND pis.inventory_item_id = nvl(cp_item_id,pis.inventory_item_id)
626 AND pis.organization_id = nvl(cp_org_id,pis.organization_id)
627 AND pis.status_code = pit.inventory_item_status_code
628 AND nvl(pit.disable_date,sysdate+1) <= sysdate
629 --2800987 : When called from PLM, p_msg_logname is PLM_LOG, through CP it is FILE.
630 AND ((p_msg_logname ='FILE' AND lifecycle_id IS NULL) OR (p_msg_logname ='PLM_LOG'))
631 --2772279 -last_update_date,rowid in order clause
632 ORDER BY pis.effective_date,pis.last_update_date,pis.rowid;
633
634 CURSOR c_get_control_level
635 IS
636 SELECT control_level
637 FROM mtl_item_attributes
638 WHERE attribute_name = G_STATUS_CODE;
639
640 CURSOR c_get_status_control (cp_attrib_name VARCHAR2)
641 IS
642 SELECT status_control_code
643 FROM mtl_item_attributes
644 WHERE attribute_name = cp_attrib_name;
645
646 l_status_code mtl_pending_item_status.status_code%TYPE;
647 l_item_id mtl_pending_item_status.inventory_item_id%TYPE;
648 l_org_id mtl_pending_item_status.organization_id%TYPE;
649 l_control_level mtl_item_attributes.control_level%TYPE;
650 l_done BOOLEAN := FALSE;
651 l_counter NUMBER := 1;
652
653 l_has_expired_status BOOLEAN := FALSE;
654
655 BEGIN
656
657 G_USER_ID := NVL(p_user_id, FND_GLOBAL.user_id );
658 G_LOGIN_ID := NVL(p_login_id, FND_GLOBAL.login_id );
659 G_PROG_APPID := NVL(p_prog_appid, FND_GLOBAL.prog_appl_id );
660 G_PROG_ID := NVL(p_prog_id, FND_GLOBAL.conc_program_id );
661 G_REQUEST_ID := NVL(p_request_id, FND_GLOBAL.conc_request_id );
662
663 INV_ITEM_MSG.Initialize;
664 INV_ITEM_MSG.set_Message_Mode (p_msg_logname);
665 INV_ITEM_MSG.set_Message_Level (INV_ITEM_MSG.g_Level_Error);
666
667 IF FND_API.To_Boolean (p_init_msg_list) THEN
668 INV_ITEM_MSG.Initialize_Error_Handler;
669 END IF;
670
671 --13962600: LCP should not change when any expried status exists
672 OPEN c_get_expired_status (cp_org_id => P_Org_Id
673 ,cp_item_id => P_Item_Id);
674 LOOP
675 FETCH c_get_expired_status
676 INTO l_status_code, l_item_id, l_org_id;
677 EXIT WHEN c_get_expired_status%NOTFOUND;
678
679 -- Write to log mentioning Couldnt not update Org, Item id, Status Code
680 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_UIPS_EXPIRE_STATUS_FAIL'
681 ,p_token1 => 'ORG_ID'
682 ,p_value1 => TO_CHAR(l_org_id)
683 ,p_token2 => 'ITEM_ID'
684 ,p_value2 => TO_CHAR(l_Item_Id)
685 ,p_token3 => 'STATUS_CODE'
686 ,p_value3 => l_status_code);
687
688 l_has_expired_status := TRUE;
689 END LOOP;
690 CLOSE c_get_expired_status;
691
692 IF l_has_expired_status THEN
693 RETCODE := G_WARNING;
694 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_UIPS_WARNING');
695
696 IF p_msg_logname = 'FILE' THEN
697 INV_ITEM_MSG.Write_List (p_delete => TRUE);
698 END IF;
699 RETURN;
700 End IF;
701
702 WHILE (l_counter < 3) LOOP
703 OPEN c_status_pending_items (cp_org_id => P_Org_Id
704 ,cp_item_id => P_Item_Id);
705 LOOP
706 FETCH c_status_pending_items
707 INTO l_status_code, l_item_id, l_org_id;
708 EXIT WHEN c_status_pending_items%NOTFOUND;
709
710 OPEN c_get_control_level;
711 FETCH c_get_control_level INTO l_control_level;
712 CLOSE c_get_control_level;
713
714
715 update_item_attributes(p_pending_status => l_status_code
716 ,p_control_level => l_control_level
717 ,p_Org_Id => l_org_id
718 ,p_item_id => l_Item_Id
719 ,p_commit => p_commit
720 ,p_return_status => l_done);
721
722 IF (l_done AND p_msg_logname = 'FILE') THEN
723 --Write to the log Updated : Org, Item id, Status Code
724 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_UPDT_ITEM_STATUS'
725 ,p_token1 => 'ORG_ID'
726 ,p_value1 => TO_CHAR(l_org_id)
727 ,p_token2 => 'ITEM_ID'
728 ,p_value2 => TO_CHAR(l_Item_Id)
729 ,p_token3 => 'STATUS_CODE'
730 ,p_value3 => l_status_code);
731
732 END IF;
733
734 END LOOP;
735
736 CLOSE c_status_pending_items;
737 l_counter := l_counter + 1;
738
739 END LOOP;
740
741 RETCODE := G_SUCCESS;
742 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_UIPS_SUCCESS');
743
744 OPEN c_status_pending_items (cp_org_id => P_Org_Id
745 ,cp_item_id => P_Item_Id);
746 LOOP
747 FETCH c_status_pending_items
748 INTO l_status_code, l_item_id, l_org_id;
749 EXIT WHEN c_status_pending_items%NOTFOUND;
750
751 -- Added for Bug 5230429
752 /*commented for bug 5479302
753 If (G_BOM_status =1) then
754 -- Write to log mentioning Couldnt not update since there are items in a Bill of Material
755 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_BOM_ITEM_TYPE_UP'
756 ,p_token1 => 'ORG_ID'
757 ,p_value1 => TO_CHAR(l_Org_Id)
758 ,p_token2 => 'ITEM_ID'
759 ,p_value2 => TO_CHAR(l_Item_Id)
760 ,p_token3 => 'STATUS_CODE'
761 ,p_value3 => l_status_code);
762 G_BOM_status := 0;
763 end if; */
764
765 if (G_Transactable_status) then
766 -- Write to log mentioning Couldnt not update since there are Open Sales Order lines.
767 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_TRANSACTABLE_YES_NO'
768 ,p_token1 => 'ORG_ID'
769 ,p_value1 => TO_CHAR(l_Org_Id)
770 ,p_token2 => 'ITEM_ID'
771 ,p_value2 => TO_CHAR(l_Item_Id)
772 ,p_token3 => 'STATUS_CODE'
773 ,p_value3 => l_status_code);
774 G_Transactable_status := FALSE;
775 end if;
776
777 if (G_TRANSACTABLE = 1) then
778 -- Write to log mentioning Couldnt not update since there are Open Sales Order lines.
779 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_TRANSACTABLE_UNCHECK'
780 ,p_token1 => 'ORG_ID'
781 ,p_value1 => TO_CHAR(l_Org_Id)
782 ,p_token2 => 'ITEM_ID'
783 ,p_value2 => TO_CHAR(l_Item_Id)
784 ,p_token3 => 'STATUS_CODE'
785 ,p_value3 => l_status_code);
786 G_TRANSACTABLE := 0;
787 end if;
788
789 if (G_ORDERABLE = 1) then
790 -- Write to log mentioning Couldnt not update since there are Open Sales Order lines.
791 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_CUST_ORDERABLE_UNCHECK'
792 ,p_token1 => 'ORG_ID'
793 ,p_value1 => TO_CHAR(l_Org_Id)
794 ,p_token2 => 'ITEM_ID'
795 ,p_value2 => TO_CHAR(l_Item_Id)
796 ,p_token3 => 'STATUS_CODE'
797 ,p_value3 => l_status_code);
798 G_ORDERABLE := 0;
799 end if;
800
801 if (G_PURCHASABLE = 1) then
802 -- Write to log mentioning Couldnt not update since there are Open Sales Order lines.
803 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_PURCHASABLE_UNCHECK'
804 ,p_token1 => 'ORG_ID'
805 ,p_value1 => TO_CHAR(l_Org_Id)
806 ,p_token2 => 'ITEM_ID'
807 ,p_value2 => TO_CHAR(l_Item_Id)
808 ,p_token3 => 'STATUS_CODE'
809 ,p_value3 => l_status_code);
810 G_PURCHASABLE := 0;
811 end if;
812
813
814 -- Write to log mentioning Couldnt not update Org, Item id, Status Code
815 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_UPDT_ITEM_STATUS_FAIL'
816 ,p_token1 => 'ORG_ID'
817 ,p_value1 => TO_CHAR(l_org_id)
818 ,p_token2 => 'ITEM_ID'
819 ,p_value2 => TO_CHAR(l_Item_Id)
820 ,p_token3 => 'STATUS_CODE'
821 ,p_value3 => l_status_code);
822 RETCODE := G_WARNING;
823 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_UIPS_WARNING');
824 END LOOP;
825 CLOSE c_status_pending_items;
826
827 IF p_msg_logname = 'FILE' THEN
828 INV_ITEM_MSG.Write_List (p_delete => TRUE);
829 END IF;
830
831 EXCEPTION
832 WHEN OTHERS THEN
833 IF (c_get_expired_status%ISOPEN) THEN
834 CLOSE c_get_expired_status;
835 END IF;
836 IF (c_status_pending_items%ISOPEN) THEN
837 CLOSE c_status_pending_items;
838 END IF;
839 IF (c_get_control_level%ISOPEN) THEN
840 CLOSE c_get_control_level;
841 END IF;
842 RETCODE := G_ERROR;
843 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_UIPS_FAILURE');
844 INV_ITEM_MSG.Add_Message(p_Msg_Name => SUBSTRB(SQLERRM, 1,240));
845
846 IF p_msg_logname = 'FILE' THEN
847 INV_ITEM_MSG.Write_List (p_delete => TRUE);
848 END IF;
849
850 END Process_Pending_Status;
851
852 -- Fix for bug#9297937
853 -- ERES in Deferred during Item Creation
854 PROCEDURE Create_Item_ERES_Event ( p_commit IN VARCHAR2 := fnd_api.g_false,
855 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
856 p_event_name IN VARCHAR2,
857 p_event_key IN VARCHAR2,
858 p_caller_type IN VARCHAR2,
859 p_org_id IN NUMBER,
860 p_inventory_item_id IN NUMBER)
861 AS
862
863 l_return_status varchar2(3);
864 l_msg_count number;
865 l_msg_data varchar2(2000);
866
867 BEGIN
868
869 Create_Item_ERES_Event
870 (
871 p_commit => p_commit,
872 p_init_msg_list => p_init_msg_list,
873 p_event_name => p_event_name,
874 p_event_key => p_event_key,
875 p_caller_type => p_caller_type,
876 p_org_id => p_org_id,
877 p_inventory_item_id => p_inventory_item_id,
878 x_return_status => l_return_status,
879 x_msg_count => l_msg_count,
880 x_msg_data => l_msg_data );
881 END;
882
883 -- Fix for bug#9297937
884 -- ERES in Deferred during Item Creation
885 PROCEDURE Create_Item_ERES_Event
886 (
887 p_commit IN VARCHAR2 := fnd_api.g_false,
888 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
889 p_event_name IN VARCHAR2,
890 p_event_key IN VARCHAR2,
891 p_caller_type IN VARCHAR2,
892 p_org_id IN NUMBER,
893 p_inventory_item_id IN NUMBER,
894 x_return_status OUT NOCOPY VARCHAR2,
895 x_msg_count OUT NOCOPY NUMBER,
896 x_msg_data OUT NOCOPY VARCHAR2 )
897
898 AS
899
900 l_control_level number;
901 l_return_status boolean;
902 l_api_name CONSTANT varchar2(30) := 'Create_Item_ERES_Event';
903 l_eres_status varchar2(30);
904 l_status_code varchar2(30);
905
906 CURSOR c_get_control_level
907 IS
908 SELECT control_level
909 FROM mtl_item_attributes
910 WHERE attribute_name = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
911
912 BEGIN
913
914 -- Standard Start of API savepoint
915 SAVEPOINT create_item_eres_event;
916
917 -- Initialize message list if p_init_msg_list is set to TRUE.
918 IF FND_API.to_Boolean( p_init_msg_list ) THEN
919 FND_MSG_PUB.initialize;
920 END IF;
921
922 -- Initialize API return status to success
923 x_return_status := FND_API.g_RET_STS_SUCCESS;
924
925 -- Derive the control level
926 OPEN c_get_control_level;
927 FETCH c_get_control_level INTO l_control_level;
928 CLOSE c_get_control_level;
929
930 -- Derive the status
931 EDR_STANDARD.PSIG_STATUS
932 (
933 p_event => p_event_name,
934 p_event_key => p_event_key,
935 p_status => l_eres_status
936 );
937
938 -- Check for caller type
939 IF ( p_Caller_type = 'CREATE_ITEM_PRE_APPROVAL') THEN
940 l_status_code := 'Inactive';
941 ELSIF ( p_Caller_type = 'CREATE_ITEM_POST_APPROVAL') THEN
942 IF (l_eres_status = 'COMPLETE') THEN
943 l_status_code := NVL(fnd_profile.value('INV_STATUS_DEFER_ERES_APPROVED'),'Active');
944 ELSIF (l_eres_status = 'REJECTED') THEN
945 l_status_code := 'ERESReject';
946 END IF;
947 END IF;
948
949
950
951 IF ((p_Caller_type = 'CREATE_ITEM_PRE_APPROVAL')
952 OR (p_Caller_type = 'CREATE_ITEM_POST_APPROVAL'
953 AND l_eres_status in ('COMPLETE','REJECTED'))) THEN
954
955
956 -- Call the API to update the status
957 update_item_attributes(p_pending_status => l_status_code
958 ,p_control_level => l_control_level
959 ,p_Org_Id => p_org_id
960 ,p_Item_id => p_inventory_item_id
961 ,p_commit => FND_API.g_TRUE
962 ,p_return_status => l_return_status);
963
964 -- Check if there are any errors
965 IF ( NOT l_return_status ) THEN
966
967 IF (G_Transactable_status) THEN
968
969 FND_MESSAGE.SET_NAME('INV','INV_TRANSACTABLE_YES_NO');
970 FND_MESSAGE.SET_TOKEN('ORG_ID',TO_CHAR(p_org_Id));
971 FND_MESSAGE.SET_TOKEN('ITEM_ID',TO_CHAR(p_inventory_item_id));
972 FND_MESSAGE.SET_TOKEN('STATUS_CODE',l_status_code);
973 FND_MSG_PUB.Add;
974
975 G_Transactable_status := FALSE;
976
977 END IF;
978
979 IF (G_TRANSACTABLE = 1) THEN
980
981 FND_MESSAGE.SET_NAME('INV','INV_TRANSACTABLE_UNCHECK');
982 FND_MESSAGE.SET_TOKEN('ORG_ID',TO_CHAR(p_org_Id));
983 FND_MESSAGE.SET_TOKEN('ITEM_ID',TO_CHAR(p_inventory_item_id));
984 FND_MESSAGE.SET_TOKEN('STATUS_CODE',l_status_code);
985 FND_MSG_PUB.Add;
986
987 G_TRANSACTABLE := 0;
988
989 END IF;
990
991 IF (G_ORDERABLE = 1) THEN
992
993 FND_MESSAGE.SET_NAME('INV','INV_CUST_ORDERABLE_UNCHECK');
994 FND_MESSAGE.SET_TOKEN('ORG_ID',TO_CHAR(p_org_Id));
995 FND_MESSAGE.SET_TOKEN('ITEM_ID',TO_CHAR(p_inventory_item_id));
996 FND_MESSAGE.SET_TOKEN('STATUS_CODE',l_status_code);
997 FND_MSG_PUB.Add;
998
999 G_ORDERABLE := 0;
1000
1001 END IF;
1002
1003 IF (G_PURCHASABLE = 1) THEN
1004
1005 FND_MESSAGE.SET_NAME('INV','INV_PURCHASABLE_UNCHECK');
1006 FND_MESSAGE.SET_TOKEN('ORG_ID',TO_CHAR(p_org_Id));
1007 FND_MESSAGE.SET_TOKEN('ITEM_ID',TO_CHAR(p_inventory_item_id));
1008 FND_MESSAGE.SET_TOKEN('STATUS_CODE',l_status_code);
1009 FND_MSG_PUB.Add;
1010
1011 G_PURCHASABLE := 0;
1012
1013 END IF;
1014
1015 RAISE FND_API.G_EXC_ERROR;
1016
1017 END IF;
1018
1019 END IF;
1020
1021 -- Standard check of p_commit.
1022 IF FND_API.To_Boolean( p_commit) THEN
1023 COMMIT WORK;
1024 END IF;
1025
1026 FND_MSG_PUB.Count_And_Get
1027 ( p_count => x_msg_count
1028 , p_data => x_msg_data
1029 );
1030
1031
1032 EXCEPTION
1033 WHEN FND_API.G_EXC_ERROR THEN
1034 ROLLBACK TO create_item_eres_event;
1035 x_return_status := FND_API.G_RET_STS_ERROR ;
1036 FND_MSG_PUB.Count_And_Get
1037 (p_count => x_msg_count,
1038 p_data => x_msg_data );
1039
1040 WHEN OTHERS THEN
1041 ROLLBACK TO create_item_eres_event;
1042 x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
1043
1044 IF FND_MSG_PUB.Check_Msg_Level
1045 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1046 THEN
1047 FND_MSG_PUB.Add_Exc_Msg
1048 (G_PKG_NAME,
1049 l_api_name );
1050 END IF;
1051
1052 FND_MSG_PUB.Count_And_Get
1053 (p_count => x_msg_count,
1054 p_data => x_msg_data );
1055
1056 END Create_Item_ERES_Event;
1057
1058 end INV_ITEM_STATUS_CP;