[Home] [Help]
PACKAGE BODY: APPS.INV_ITEM_STATUS_CP
Source
1 PACKAGE BODY INV_ITEM_STATUS_CP AS
2 /* $Header: INVCIPSB.pls 120.10 2007/11/19 09:15:25 arattan 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
14 G_ITEM NUMBER := 1;
15 G_ITEM_ORG NUMBER := 2;
16 G_PWIDTH NUMBER := 132;
17 G_SO_RESERVABLE NUMBER := 1;
18 G_BOM_STANDARD NUMBER := 4;
19 G_BOM_MODEL NUMBER := 1;
20 G_UNDER_STATUS_CONTROL NUMBER := 1;
21 G_DEFAULT_CONTROL NUMBER := 2;
22 G_NO_CONTROL NUMBER := 3; -- Added for Bug-6531777
23 G_ARG_ORGANIZATION_ID NUMBER := 1;
24 G_ARG_ITEM_ID NUMBER := 2;
25
26 G_USER_ID NUMBER := -1;
27 G_LOGIN_ID NUMBER := -1;
28 G_PROG_APPID NUMBER := -1;
29 G_PROG_ID NUMBER := -1;
30 G_REQUEST_ID NUMBER := -1;
31
32 G_SUCCESS CONSTANT NUMBER := 0;
33 G_WARNING CONSTANT NUMBER := 1;
34 G_ERROR CONSTANT NUMBER := 2;
35
36 --Added for 5230429
37 G_transactable_status BOOLEAN := FALSE;
38 --commented for bug 5479302
39 -- G_BOM_status NUMBER := 0;
40
41 /* Start Bug 3713912 */
42 G_RECIPE_ENABLED VARCHAR2(50) := 'MTL_SYSTEM_ITEMS.RECIPE_ENABLED_FLAG';
43 G_PROCESS_EXECUTION_ENABLED VARCHAR2(50) := 'MTL_SYSTEM_ITEMS.PROCESS_EXECUTION_ENABLED_FLAG';
44 /* End Bug 3713912 */
45
46 -- Added for bug 5230594
47 G_TRANSACTABLE NUMBER := 0;
48 G_ORDERABLE NUMBER := 0;
49 G_PURCHASABLE NUMBER := 0;
50
51
52
53 PROCEDURE update_item_attributes(p_pending_status IN VARCHAR2
54 ,p_control_level IN NUMBER
55 ,p_Org_Id IN NUMBER
56 ,p_Item_id IN NUMBER
57 ,p_commit IN VARCHAR2:= FND_API.g_TRUE
58 ,p_return_status OUT NOCOPY BOOLEAN)
59
60 IS
61 CURSOR c_get_status_control (cp_attrib_name VARCHAR2)
62 IS
63 SELECT status_control_code
64 FROM mtl_item_attributes
65 WHERE attribute_name = cp_attrib_name;
66
67 l_stock_cntrl_level mtl_item_attributes.status_control_code%TYPE;
68 l_trans_cntrl_level mtl_item_attributes.status_control_code%TYPE;
69 l_purch_cntrl_level mtl_item_attributes.status_control_code%TYPE;
70 l_invoice_cntrl_level mtl_item_attributes.status_control_code%TYPE;
71 l_wip_cntrl_level mtl_item_attributes.status_control_code%TYPE;
72 l_cust_cntrl_level mtl_item_attributes.status_control_code%TYPE;
73 l_int_cntrl_level mtl_item_attributes.status_control_code%TYPE;
74 l_bom_cntrl_level mtl_item_attributes.status_control_code%TYPE;
75 l_row_temp NUMBER :=0;
76 /* Start Bug 3713912 */
77 l_recipe_cntrl_level mtl_item_attributes.status_control_code%TYPE;
78 l_process_exec_cntrl_level mtl_item_attributes.status_control_code%TYPE;
79 /* End Bug 3713912 */
80 is_transactable_upd NUMBER;
81 is_bom_enabled_upd NUMBER;
82
83 BEGIN
84
85 p_return_status := FALSE;
86
87 OPEN c_get_status_control(cp_attrib_name => G_STOCK_ENABLED);
88 FETCH c_get_status_control INTO l_stock_cntrl_level;
89 CLOSE c_get_status_control;
90
91 OPEN c_get_status_control(cp_attrib_name => G_TRANSACTIONS_ENABLED);
92 FETCH c_get_status_control INTO l_trans_cntrl_level;
93 CLOSE c_get_status_control;
94
95 OPEN c_get_status_control(cp_attrib_name => G_PURCHASING_ENABLED);
96 FETCH c_get_status_control INTO l_purch_cntrl_level;
97 CLOSE c_get_status_control;
98
99 OPEN c_get_status_control(cp_attrib_name => G_INVOICE_ENABLED);
100 FETCH c_get_status_control INTO l_invoice_cntrl_level;
101 CLOSE c_get_status_control;
102
103 OPEN c_get_status_control(cp_attrib_name => G_BUILD_IN_WIP);
104 FETCH c_get_status_control INTO l_wip_cntrl_level;
105 CLOSE c_get_status_control;
106
107 OPEN c_get_status_control(cp_attrib_name => G_CUSTOMER_ENABLED);
108 FETCH c_get_status_control INTO l_cust_cntrl_level;
109 CLOSE c_get_status_control;
110
111 OPEN c_get_status_control(cp_attrib_name => G_INTERNAL_ENABLED);
112 FETCH c_get_status_control INTO l_int_cntrl_level;
113 CLOSE c_get_status_control;
114
115 OPEN c_get_status_control(cp_attrib_name => G_BOM_ENABLED);
116 FETCH c_get_status_control INTO l_bom_cntrl_level;
117 CLOSE c_get_status_control;
118 /* Start Bug 3713912 */
119 OPEN c_get_status_control(cp_attrib_name => G_RECIPE_ENABLED);
120 FETCH c_get_status_control INTO l_recipe_cntrl_level;
121 CLOSE c_get_status_control;
122 OPEN c_get_status_control(cp_attrib_name => G_PROCESS_EXECUTION_ENABLED);
123 FETCH c_get_status_control INTO l_process_exec_cntrl_level;
124 CLOSE c_get_status_control;
125 /* End Bug 3713912 */
126
127 /* Check to see wether the row is locked */
128
129 SELECT 1 INTO l_row_temp
130 FROM mtl_system_items msi
131 WHERE inventory_item_id = p_Item_Id
132 AND ((p_control_level = G_ITEM_ORG and msi.organization_id = p_Org_Id)
133 OR
134 (p_control_level = G_ITEM
135 AND msi.organization_id IN
136 (SELECT p2.organization_id
137 FROM mtl_parameters p1,
138 mtl_parameters p2
139 WHERE p1.organization_id = p_Org_Id
140 AND p1.master_organization_id = p2.master_organization_id)))
141 AND rownum < 2
142 FOR UPDATE NOWAIT;
143
144 /* 5523531 - Condition only applies when Transactable flag of item is changed by the status */
145 SELECT count(*) INTO is_transactable_upd
146 FROM DUAL
147 WHERE EXISTS
148 (SELECT 'X' FROM mtl_system_items
149 WHERE inventory_item_id = p_item_id
150 AND organization_id = p_org_id
151 AND mtl_transactions_enabled_flag <>
152 (SELECT attribute_value FROM mtl_status_attribute_values
153 WHERE attribute_name = 'MTL_SYSTEM_ITEMS.MTL_TRANSACTIONS_ENABLED_FLAG'
154 AND inventory_item_status_code = p_pending_status));
155
156 --Added for Bug: 5230429
157 if (l_trans_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL) AND is_transactable_upd = 1) then
158 G_transactable_status := INV_ATTRIBUTE_CONTROL_PVT.transactable_uncheck(p_Org_Id,p_Item_Id);
159 end if;
160 /* 5523531 - Condition only applies when Transactable flag of item is changed by the status */
161 SELECT count(*) INTO is_bom_enabled_upd
162 FROM mtl_status_attribute_values
163 WHERE attribute_name = 'MTL_SYSTEM_ITEMS.BOM_ENABLED_FLAG'
164 AND attribute_value = 'N'
165 AND inventory_item_status_code = p_pending_status;
166
167 /*commented for bug 5479302
168 if(l_bom_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL) AND is_bom_enabled_upd = 1) then
169 select count(*)
170 into G_BOM_status
171 from dual
172 where exists
173 ( select 'x'
174 from bom_bill_of_materials bom
175 where bom.assembly_item_id = p_Item_Id
176 and bom.organization_id in
177 ( select organization_id
178 from mtl_parameters
179 where master_organization_id = p_Org_Id
180 and 1 = l_bom_cntrl_level
181 union all
182 select organization_id
183 from mtl_parameters
184 where organization_id = p_Org_Id
185 and 2 =l_bom_cntrl_level));
186 end if; */
187
188 /* Bug: 5230594
189 Following SQL checks if the pending status being applied is unchecking a status attribute which is required by an enabled
190 dependent attributes and accordingly selects a value of 1 in the corresponding global variable
191 */
192 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,
193 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,
194 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
195 INTO G_TRANSACTABLE
196 ,G_PURCHASABLE
197 ,G_ORDERABLE
198 FROM mtl_system_items_b msi
199 ,mtl_status_attribute_values mav
200 WHERE msi.inventory_item_id = p_Item_Id
201 AND msi.organization_id = p_Org_Id
202 AND mav.inventory_item_status_code = p_pending_status
203 AND mav.attribute_name IN (G_TRANSACTIONS_ENABLED,G_PURCHASING_ENABLED,G_CUSTOMER_ENABLED);
204
205 IF ( (NOT G_transactable_status) --and (G_BOM_status =0)
206 -- Condition OR l_trans_cntrl_level = G_NO _CONTROL added for Bug-6531777
207 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)
208 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)
209 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)
210 ) THEN
211
212 /* Lock is success - Update Item status */
213
214 UPDATE mtl_system_items msi
215 SET (inventory_item_status_code,
216 last_update_date,
217 last_updated_by,
218 last_update_login) =
219 (SELECT p_pending_status,
220 sysdate,
221 G_USER_ID,
222 G_USER_ID
223 FROM mtl_status_attribute_values v,
224 mtl_item_attributes a
225 WHERE v.inventory_item_status_code = p_pending_status
226 AND a.attribute_name = G_STOCK_ENABLED
227 AND a.attribute_name = v.attribute_name)
228 WHERE msi.inventory_item_id = p_Item_Id
229 AND ((p_control_level = G_ITEM_ORG and msi.organization_id = p_Org_Id)
230 OR
231 (p_control_level = G_ITEM
232 AND msi.organization_id IN
233 (SELECT p2.organization_id
234 FROM mtl_parameters p1,
235 mtl_parameters p2
236 WHERE p1.organization_id = p_Org_Id
237 AND p1.master_organization_id = p2.master_organization_id)));
238
239 /* Update all the Eight Item Attributes */
240
241 UPDATE mtl_system_items msi
242 SET msi.stock_enabled_flag =
243 ( SELECT DECODE(mti.inventory_item_flag,'N','N',v.attribute_value)
244 FROM mtl_status_attribute_values v,
245 mtl_item_attributes a,
246 mtl_system_items mti
247 WHERE v.inventory_item_status_code = p_pending_status
248 AND a.attribute_name = G_STOCK_ENABLED
249 AND a.attribute_name = v.attribute_name
250 AND mti.inventory_item_id = p_Item_Id
251 AND mti.organization_id = p_Org_Id)
252 WHERE l_stock_cntrl_level IN ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL)
253 AND msi.inventory_item_id = p_Item_Id
254 AND msi.inventory_item_flag = 'Y'
255 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
256 OR
257 (p_control_level = G_ITEM
258 AND msi.organization_id IN
259 (SELECT p2.organization_id
260 FROM mtl_parameters p1,
261 mtl_parameters p2
262 WHERE p1.organization_id = p_Org_Id
263 AND p1.master_organization_id = p2.master_organization_id)));
264
265 UPDATE mtl_system_items msi
266 SET msi.mtl_transactions_enabled_flag =
267 ( SELECT DECODE (mti.stock_enabled_flag,'N','N', v.attribute_value)
268 FROM mtl_status_attribute_values v,
269 mtl_item_attributes a,
270 mtl_system_items mti
271 WHERE v.inventory_item_status_code = p_pending_status
272 AND a.attribute_name = G_TRANSACTIONS_ENABLED
273 AND a.attribute_name = v.attribute_name
274 AND mti.inventory_item_id = p_Item_Id
275 AND mti.organization_id = p_Org_Id)
276 WHERE l_trans_cntrl_level IN ( G_UNDER_STATUS_CONTROL, G_DEFAULT_CONTROL)
277 AND msi.inventory_item_id = p_Item_Id
278 -- AND msi.stock_enabled_flag = 'Y' /* commented for bug 3375455 */
279 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
280 OR
281 (p_control_level = G_ITEM
282 AND msi.organization_id IN
283 (SELECT p2.organization_id
284 FROM mtl_parameters p1,
285 mtl_parameters p2
286 WHERE p1.organization_id = p_Org_Id
287 AND p1.master_organization_id = p2.master_organization_id)));
288
289 /* Updating pending Item Status */
290 UPDATE mtl_system_items msi
291 SET msi.purchasing_enabled_flag =
292 (SELECT DECODE(mti.purchasing_item_flag,'N','N',v.attribute_value)
293 FROM mtl_status_attribute_values v,
294 mtl_item_attributes a,
295 mtl_system_items mti
296 WHERE v.inventory_item_status_code = p_pending_status
297 AND a.attribute_name = G_PURCHASING_ENABLED
298 AND a.attribute_name = v.attribute_name
299 AND mti.inventory_item_id = p_Item_Id
300 AND mti.organization_id = p_Org_Id)
301 WHERE l_purch_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
302 AND msi.inventory_item_id = p_Item_Id
303 AND msi.purchasing_item_flag = 'Y'
304 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
305 OR
306 (p_control_level = G_ITEM
307 AND msi.organization_id IN
308 (SELECT p2.organization_id
309 FROM mtl_parameters p1,
310 mtl_parameters p2
311 WHERE p1.organization_id = p_Org_Id
312 AND p1.master_organization_id = p2.master_organization_id)));
313
314 UPDATE mtl_system_items msi
315 SET msi.invoice_enabled_flag =
316 ( SELECT DECODE(mti.invoiceable_item_flag,'N','N',v.attribute_value)
317 FROM mtl_status_attribute_values v,
318 mtl_item_attributes a,
319 mtl_system_items mti
320 WHERE v.inventory_item_status_code = p_pending_status
321 AND a.attribute_name = G_INVOICE_ENABLED
322 AND a.attribute_name = v.attribute_name
323 AND mti.inventory_item_id = p_Item_Id
324 AND mti.organization_id = p_Org_Id)
325 WHERE l_invoice_cntrl_level in ( G_UNDER_STATUS_CONTROL, G_DEFAULT_CONTROL )
326 AND msi.inventory_item_id = p_Item_Id
327 AND msi.invoiceable_item_flag = 'Y'
328 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
329 OR
330 (p_control_level = G_ITEM
331 AND msi.organization_id IN
332 (SELECT p2.organization_id
333 FROM mtl_parameters p1,
334 mtl_parameters p2
335 WHERE p1.organization_id = p_Org_Id
336 AND p1.master_organization_id = p2.master_organization_id)));
337
338 UPDATE mtl_system_items msi
339 SET msi.build_in_wip_flag =
340 ( SELECT DECODE(mti.inventory_item_flag,'N','N',decode(mti.bom_item_type,4,v.attribute_value,'N'))
341 FROM mtl_status_attribute_values v,
342 mtl_item_attributes a,
343 mtl_system_items mti
344 WHERE v.inventory_item_status_code = p_pending_status
345 AND a.attribute_name = G_BUILD_IN_WIP
346 AND a.attribute_name = v.attribute_name
347 AND mti.inventory_item_id = p_Item_Id
348 AND mti.organization_id = p_Org_Id)
349 WHERE l_wip_cntrl_level IN ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
350 AND msi.inventory_item_id = p_Item_Id
351 AND msi.inventory_item_flag = 'Y'
352 AND msi.bom_item_type = 4
353 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
354 OR
355 (p_control_level = G_ITEM
356 AND msi.organization_id IN
357 (SELECT p2.organization_id
358 FROM mtl_parameters p1,
359 mtl_parameters p2
360 WHERE p1.organization_id = p_Org_Id
361 AND p1.master_organization_id = p2.master_organization_id)));
362
363 UPDATE mtl_system_items msi
364 SET msi.customer_order_enabled_flag =
365 ( SELECT DECODE(mti.customer_order_flag,'N','N',v.attribute_value)
366 FROM mtl_status_attribute_values v,
367 mtl_item_attributes a,
368 mtl_system_items mti
369 WHERE v.inventory_item_status_code = p_pending_status
370 AND a.attribute_name = G_CUSTOMER_ENABLED
371 AND a.attribute_name = v.attribute_name
372 AND mti.inventory_item_id = p_Item_Id
373 AND mti.organization_id = p_Org_Id)
374 WHERE l_cust_cntrl_level IN (G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL)
375 AND msi.inventory_item_id = p_Item_Id
376 AND msi.customer_order_flag = 'Y'
377 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
378 OR
379 (p_control_level = G_ITEM
380 AND msi.organization_id IN
381 (SELECT p2.organization_id
382 FROM mtl_parameters p1,
383 mtl_parameters p2
384 WHERE p1.organization_id = p_Org_Id
385 AND p1.master_organization_id = p2.master_organization_id)));
386
387
388 UPDATE mtl_system_items msi
389 SET msi.internal_order_enabled_flag =
390 ( SELECT DECODE(mti.internal_order_flag,'N','N',v.attribute_value)
391 FROM mtl_status_attribute_values v,
392 mtl_item_attributes a,
393 mtl_system_items mti
394 WHERE v.inventory_item_status_code = p_pending_status
395 AND a.attribute_name = G_INTERNAL_ENABLED
396 AND a.attribute_name = v.attribute_name
397 AND mti.inventory_item_id = p_Item_Id
398 AND mti.organization_id = p_Org_Id)
399 WHERE l_int_cntrl_level IN ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
400 AND msi.inventory_item_id = p_Item_Id
401 AND msi.internal_order_flag = 'Y'
402 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
403 OR
404 (p_control_level = G_ITEM
405 AND msi.organization_id IN
406 (SELECT p2.organization_id
407 FROM mtl_parameters p1,
408 mtl_parameters p2
409 WHERE p1.organization_id = p_Org_Id
410 AND p1.master_organization_id = p2.master_organization_id)));
411
412
413 UPDATE mtl_system_items msi
414 SET msi.bom_enabled_flag =
415 ( SELECT v.attribute_value --Bug:3546140DECODE(mti.inventory_item_flag,'N','N',v.attribute_value)
416 FROM mtl_status_attribute_values v,
417 mtl_item_attributes a
418 --Bug:3546140 ,mtl_system_items mti
419 WHERE v.inventory_item_status_code = p_pending_status
420 AND a.attribute_name = G_BOM_ENABLED
421 AND a.attribute_name = v.attribute_name
422 -- AND mti.inventory_item_id = p_Item_Id
423 -- AND mti.organization_id = p_Org_Id
424 )
425 WHERE l_bom_cntrl_level IN ( G_UNDER_STATUS_CONTROL, G_DEFAULT_CONTROL )
426 AND msi.inventory_item_id = p_Item_Id
427 --Bug:3546140 AND msi.inventory_item_flag = 'Y'
428 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
429 OR
430 (p_control_level = G_ITEM
431 AND msi.organization_id IN
432 (SELECT p2.organization_id
433 FROM mtl_parameters p1,
434 mtl_parameters p2
435 WHERE p1.organization_id = p_Org_Id
436 AND p1.master_organization_id = p2.master_organization_id)));
437
438 /* Start Bug 3713912 */
439 --update recipe enabled flag based on status
440 UPDATE mtl_system_items msi
441 SET msi.recipe_enabled_flag =
442 (SELECT v.attribute_value
443 FROM mtl_status_attribute_values v,
444 mtl_item_attributes a
445 WHERE v.inventory_item_status_code = p_pending_status
446 AND a.attribute_name = G_RECIPE_ENABLED
447 AND a.attribute_name = v.attribute_name)
448 WHERE l_recipe_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
449 AND msi.inventory_item_id = p_Item_Id
450 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
451 OR
452 (p_control_level = G_ITEM
453 AND msi.organization_id IN
454 (SELECT p2.organization_id
455 FROM mtl_parameters p1,
456 mtl_parameters p2
457 WHERE p1.organization_id = p_Org_Id
458 AND p1.master_organization_id = p2.master_organization_id)));
459
460 --update process_execution enabled flag based on status
461 --Bug 5346921 process execution enabled should not be enabled if either inventory flag is 'N' or recipe enabled is 'N'
462 UPDATE mtl_system_items msi
463 SET msi.PROCESS_EXECUTION_ENABLED_FLAG =
464 (SELECT decode(msi.inventory_item_flag,'N','N',
465 decode(msi.recipe_enabled_flag,'N','N',v.attribute_value))
466 FROM mtl_status_attribute_values v,
467 mtl_item_attributes a
468 WHERE v.inventory_item_status_code = p_pending_status
469 AND a.attribute_name = G_PROCESS_EXECUTION_ENABLED
470 AND a.attribute_name = v.attribute_name)
471 WHERE l_process_exec_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
472 AND msi.inventory_item_id = p_Item_Id
473 AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
474 OR
475 (p_control_level = G_ITEM
476 AND msi.organization_id IN
477 (SELECT p2.organization_id
478 FROM mtl_parameters p1,
479 mtl_parameters p2
480 WHERE p1.organization_id = p_Org_Id
481 AND p1.master_organization_id = p2.master_organization_id)));
482
483 /* End Bug 3713912 */
484 UPDATE mtl_pending_item_status
485 SET pending_flag = 'N' ,
486 implemented_date = SYSDATE ,
487 request_id = G_REQUEST_ID,
488 program_application_id = G_PROG_APPID,
489 program_id = G_PROG_ID,
490 program_update_date = SYSDATE,
491 last_update_login = G_LOGIN_ID,
492 last_updated_by = G_USER_ID
493 WHERE status_code = p_pending_status
494 AND organization_id = p_Org_Id
495 AND inventory_item_id = p_Item_Id
496 AND effective_date <= SYSDATE
497 AND pending_flag = 'Y';
498
499 IF FND_API.To_Boolean (p_commit) THEN
500 COMMIT WORK;
501 END IF;
502
503 p_return_status := TRUE;
504
505 -- Added for 5230429
506 else
507 p_return_status := FALSE;
508 end if;
509
510 EXCEPTION
511 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
512 p_return_status := FALSE;
513 --Write to LOG unable to lock the Item.
514 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_LOCK_ITEM_FAILED'
515 ,p_token1 => 'ORG_ID'
516 ,p_value1 => TO_CHAR(p_Org_Id)
517 ,p_token2 => 'ITEM_ID'
518 ,p_value2 => TO_CHAR(p_Item_Id)
519 ,p_token3 => 'STATUS_CODE'
520 ,p_value3 => p_pending_status);
521 WHEN OTHERS THEN
522 p_return_status := FALSE;
523 --Write to LOG regarding the Exception.
524 INV_ITEM_MSG.Add_Message(p_Msg_Name => SUBSTRB(SQLERRM, 1,240));
525
526 END update_item_attributes;
527
528 PROCEDURE Process_Pending_Status(ERRBUF OUT NOCOPY VARCHAR2
529 ,RETCODE OUT NOCOPY NUMBER
530 ,p_Org_Id IN NUMBER := NULL
531 ,p_Item_Id IN NUMBER := NULL
532 ,p_commit IN VARCHAR2 := FND_API.g_TRUE
533 ,p_prog_appid IN NUMBER := NULL
534 ,p_prog_id IN NUMBER := NULL
535 ,p_request_id IN NUMBER := NULL
536 ,p_user_id IN NUMBER := NULL
537 ,p_login_id IN NUMBER := NULL
538 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
539 ,p_msg_logname IN VARCHAR2 := 'FILE')
540 IS
541 CURSOR c_status_pending_items (cp_org_id NUMBER
542 ,cp_item_id NUMBER)
543 IS
544 SELECT pis.status_code,
545 pis.inventory_item_id,
546 pis.organization_id
547 FROM mtl_pending_item_status pis,
548 mtl_item_status pit
549 WHERE pis.effective_date <= sysdate
550 AND pis.pending_flag = 'Y'
551 AND pis.inventory_item_id = nvl(cp_item_id,pis.inventory_item_id)
552 AND pis.organization_id = nvl(cp_org_id,pis.organization_id)
553 AND pis.status_code = pit.inventory_item_status_code
554 AND nvl(pit.disable_date,sysdate+1) > sysdate
555 --2800987 : When called from PLM, p_msg_logname is PLM_LOG, through CP it is FILE.
556 AND ((p_msg_logname ='FILE' AND lifecycle_id IS NULL) OR (p_msg_logname ='PLM_LOG'))
557 --2772279 -last_update_date,rowid in order clause
558 ORDER BY pis.effective_date,pis.last_update_date,pis.rowid;
559
560 CURSOR c_get_control_level
561 IS
562 SELECT control_level
563 FROM mtl_item_attributes
564 WHERE attribute_name = G_STATUS_CODE;
565
566 CURSOR c_get_status_control (cp_attrib_name VARCHAR2)
567 IS
568 SELECT status_control_code
569 FROM mtl_item_attributes
570 WHERE attribute_name = cp_attrib_name;
571
572 l_status_code mtl_pending_item_status.status_code%TYPE;
573 l_item_id mtl_pending_item_status.inventory_item_id%TYPE;
574 l_org_id mtl_pending_item_status.organization_id%TYPE;
575 l_control_level mtl_item_attributes.control_level%TYPE;
576 l_done BOOLEAN := FALSE;
577 l_counter NUMBER := 1;
578
579 BEGIN
580
581 G_USER_ID := NVL(p_user_id, FND_GLOBAL.user_id );
582 G_LOGIN_ID := NVL(p_login_id, FND_GLOBAL.login_id );
583 G_PROG_APPID := NVL(p_prog_appid, FND_GLOBAL.prog_appl_id );
584 G_PROG_ID := NVL(p_prog_id, FND_GLOBAL.conc_program_id );
585 G_REQUEST_ID := NVL(p_request_id, FND_GLOBAL.conc_request_id );
586
587 INV_ITEM_MSG.Initialize;
588 INV_ITEM_MSG.set_Message_Mode (p_msg_logname);
589 INV_ITEM_MSG.set_Message_Level (INV_ITEM_MSG.g_Level_Error);
590
591 IF FND_API.To_Boolean (p_init_msg_list) THEN
592 INV_ITEM_MSG.Initialize_Error_Handler;
593 END IF;
594
595 WHILE (l_counter < 3) LOOP
596 OPEN c_status_pending_items (cp_org_id => P_Org_Id
597 ,cp_item_id => P_Item_Id);
598 LOOP
599 FETCH c_status_pending_items
600 INTO l_status_code, l_item_id, l_org_id;
601 EXIT WHEN c_status_pending_items%NOTFOUND;
602
603 OPEN c_get_control_level;
604 FETCH c_get_control_level INTO l_control_level;
605 CLOSE c_get_control_level;
606
607
608 update_item_attributes(p_pending_status => l_status_code
609 ,p_control_level => l_control_level
610 ,p_Org_Id => l_org_id
611 ,p_item_id => l_Item_Id
612 ,p_commit => p_commit
613 ,p_return_status => l_done);
614
615 IF (l_done AND p_msg_logname = 'FILE') THEN
616 --Write to the log Updated : Org, Item id, Status Code
617 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_UPDT_ITEM_STATUS'
618 ,p_token1 => 'ORG_ID'
619 ,p_value1 => TO_CHAR(l_org_id)
620 ,p_token2 => 'ITEM_ID'
621 ,p_value2 => TO_CHAR(l_Item_Id)
622 ,p_token3 => 'STATUS_CODE'
623 ,p_value3 => l_status_code);
624
625 END IF;
626
627 END LOOP;
628
629 CLOSE c_status_pending_items;
630 l_counter := l_counter + 1;
631
632 END LOOP;
633
634 RETCODE := G_SUCCESS;
635 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_UIPS_SUCCESS');
636
637 OPEN c_status_pending_items (cp_org_id => P_Org_Id
638 ,cp_item_id => P_Item_Id);
639 LOOP
640 FETCH c_status_pending_items
641 INTO l_status_code, l_item_id, l_org_id;
642 EXIT WHEN c_status_pending_items%NOTFOUND;
643
644 -- Added for Bug 5230429
645 /*commented for bug 5479302
646 If (G_BOM_status =1) then
647 -- Write to log mentioning Couldnt not update since there are items in a Bill of Material
648 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_BOM_ITEM_TYPE_UP'
649 ,p_token1 => 'ORG_ID'
650 ,p_value1 => TO_CHAR(l_Org_Id)
651 ,p_token2 => 'ITEM_ID'
652 ,p_value2 => TO_CHAR(l_Item_Id)
653 ,p_token3 => 'STATUS_CODE'
654 ,p_value3 => l_status_code);
655 G_BOM_status := 0;
656 end if; */
657
658 if (G_Transactable_status) then
659 -- Write to log mentioning Couldnt not update since there are Open Sales Order lines.
660 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_TRANSACTABLE_YES_NO'
661 ,p_token1 => 'ORG_ID'
662 ,p_value1 => TO_CHAR(l_Org_Id)
663 ,p_token2 => 'ITEM_ID'
664 ,p_value2 => TO_CHAR(l_Item_Id)
665 ,p_token3 => 'STATUS_CODE'
666 ,p_value3 => l_status_code);
667 G_Transactable_status := FALSE;
668 end if;
669
670 if (G_TRANSACTABLE = 1) then
671 -- Write to log mentioning Couldnt not update since there are Open Sales Order lines.
672 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_TRANSACTABLE_UNCHECK'
673 ,p_token1 => 'ORG_ID'
674 ,p_value1 => TO_CHAR(l_Org_Id)
675 ,p_token2 => 'ITEM_ID'
676 ,p_value2 => TO_CHAR(l_Item_Id)
677 ,p_token3 => 'STATUS_CODE'
678 ,p_value3 => l_status_code);
679 G_TRANSACTABLE := 0;
680 end if;
681
682 if (G_ORDERABLE = 1) then
683 -- Write to log mentioning Couldnt not update since there are Open Sales Order lines.
684 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_CUST_ORDERABLE_UNCHECK'
685 ,p_token1 => 'ORG_ID'
686 ,p_value1 => TO_CHAR(l_Org_Id)
687 ,p_token2 => 'ITEM_ID'
688 ,p_value2 => TO_CHAR(l_Item_Id)
689 ,p_token3 => 'STATUS_CODE'
690 ,p_value3 => l_status_code);
691 G_ORDERABLE := 0;
692 end if;
693
694 if (G_PURCHASABLE = 1) then
695 -- Write to log mentioning Couldnt not update since there are Open Sales Order lines.
696 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_PURCHASABLE_UNCHECK'
697 ,p_token1 => 'ORG_ID'
698 ,p_value1 => TO_CHAR(l_Org_Id)
699 ,p_token2 => 'ITEM_ID'
700 ,p_value2 => TO_CHAR(l_Item_Id)
701 ,p_token3 => 'STATUS_CODE'
702 ,p_value3 => l_status_code);
703 G_PURCHASABLE := 0;
704 end if;
705
706
707 -- Write to log mentioning Couldnt not update Org, Item id, Status Code
708 INV_ITEM_MSG.Add_Message(p_Msg_Name => 'INV_UPDT_ITEM_STATUS_FAIL'
709 ,p_token1 => 'ORG_ID'
710 ,p_value1 => TO_CHAR(l_org_id)
711 ,p_token2 => 'ITEM_ID'
712 ,p_value2 => TO_CHAR(l_Item_Id)
713 ,p_token3 => 'STATUS_CODE'
714 ,p_value3 => l_status_code);
715 RETCODE := G_WARNING;
716 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_UIPS_WARNING');
717 END LOOP;
718 CLOSE c_status_pending_items;
719
720 IF p_msg_logname = 'FILE' THEN
721 INV_ITEM_MSG.Write_List (p_delete => TRUE);
722 END IF;
723
724 EXCEPTION
725 WHEN OTHERS THEN
726 IF (c_status_pending_items%ISOPEN) THEN
727 CLOSE c_status_pending_items;
728 END IF;
729 IF (c_get_control_level%ISOPEN) THEN
730 CLOSE c_get_control_level;
731 END IF;
732 RETCODE := G_ERROR;
733 ERRBUF := FND_MESSAGE.Get_String('INV', 'INV_UIPS_FAILURE');
734 INV_ITEM_MSG.Add_Message(p_Msg_Name => SUBSTRB(SQLERRM, 1,240));
735
736 IF p_msg_logname = 'FILE' THEN
737 INV_ITEM_MSG.Write_List (p_delete => TRUE);
738 END IF;
739
740 END Process_Pending_Status;
741
742 end INV_ITEM_STATUS_CP;