[Home] [Help]
PACKAGE BODY: APPS.INVUPDAT
Source
1 PACKAGE BODY INVUPDAT as
2 /* $Header: INVUPDAB.pls 120.1 2005/10/18 05:26:59 swshukla noship $ */
3
4 PROCEDURE UPDATE_ATTRIBUTES(
5 current_attribute_name IN VARCHAR2,
6 current_attribute_value IN VARCHAR2 DEFAULT NULL,
7 input_status IN VARCHAR2 DEFAULT NULL
8 )
9 IS
10
11 lock_variable VARCHAR2(1);
12
13
14 CURSOR status_cursor(input_stat_value VARCHAR2) IS
15 select s.inventory_item_status_code IISC,
16 v.attribute_value AV
17 from mtl_item_status s,
18 mtl_status_attribute_values v
19 where s.inventory_item_status_code = NVL(input_stat_value , s.inventory_item_status_code)
20 and s.inventory_item_status_code = v.inventory_item_status_code
21 and v.attribute_name = current_attribute_name;
22
23
24 /*This code will lock only the records that may get updated below. This prevents
25 the system from hanging if an item record that needs to be updated is open and locked.*/
26
27 CURSOR lock_records IS
28 select 'x'
29 from mtl_system_items
30 where decode(current_attribute_name, 'MTL_SYSTEM_ITEMS.STOCK_ENABLED_FLAG', inventory_item_flag,
31 'MTL_SYSTEM_ITEMS.MTL_TRANSACTIONS_ENABLED_FLAG', stock_enabled_flag,
32 'MTL_SYSTEM_ITEMS.PURCHASING_ENABLED_FLAG', purchasing_item_flag,
33 'MTL_SYSTEM_ITEMS.INVOICE_ENABLED_FLAG', invoiceable_item_flag,
34 'MTL_SYSTEM_ITEMS.BUILD_IN_WIP_FLAG', inventory_item_flag,
35 'MTL_SYSTEM_ITEMS.CUSTOMER_ORDER_ENABLED_FLAG', customer_order_flag,
36 'MTL_SYSTEM_ITEMS.INTERNAL_ORDER_ENABLED_FLAG', internal_order_flag,
37 'MTL_SYSTEM_ITEMS.BOM_ENABLED_FLAG', bom_enabled_flag,
38 'MTL_SYSTEM_ITEMS.RECIPE_ENABLED_FLAG', recipe_enabled_flag,
39 'MTL_SYSTEM_ITEMS.PROCESS_EXECUTION_ENABLED_FLAG',process_execution_enabled_flag, 'N')
40 = 'Y'
41 and input_status = MTL_SYSTEM_ITEMS.inventory_item_status_code
42 for update of last_update_date NOWAIT;
43
44 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
45 BEGIN
46
47 OPEN lock_records;
48 FETCH lock_records into lock_variable;
49
50 /* Update the relevant attribute in the item master for all *
51 * items which match the update criteria. *
52 * while making sure that no interdependencies are violated */
53 IF l_inv_debug_level IN(101, 102) THEN
54 INVPUTLI.info('inside ');
55 END IF;
56
57 FOR status_cursor_row in status_cursor(input_status) LOOP
58 IF l_inv_debug_level IN(101, 102) THEN
59 INVPUTLI.info('inside for IISC ' || status_cursor_row.IISC);
60 INVPUTLI.info('current attribute is ' ||current_attribute_name );
61 END IF;
62 if ( current_attribute_name = 'MTL_SYSTEM_ITEMS.STOCK_ENABLED_FLAG') then
63 update mtl_system_items msi
64 set msi.stock_enabled_flag
65 = NVL(current_attribute_value, status_cursor_row.AV),
66 msi.last_updated_by = to_number(fnd_profile.value('USER_ID')),
67 msi.last_update_date = sysdate,
68 msi.last_update_login = to_number(fnd_profile.value('LOGIN_ID'))
69 where msi.inventory_item_flag = 'Y'
70 and msi.stock_enabled_flag <> NVL(current_attribute_value, status_cursor_row.AV)
71 and msi.inventory_item_status_code = status_cursor_row.IISC;
72
73 elsif ( current_attribute_name = 'MTL_SYSTEM_ITEMS.MTL_TRANSACTIONS_ENABLED_FLAG') then
74 update mtl_system_items msi
75 set msi.mtl_transactions_enabled_flag
76 = NVL(current_attribute_value, status_cursor_row.AV),
77 msi.last_updated_by = to_number(fnd_profile.value('USER_ID')),
78 msi.last_update_date = sysdate,
79 msi.last_update_login = to_number(fnd_profile.value('LOGIN_ID'))
80 where msi.stock_enabled_flag = 'Y'
81 and msi.mtl_transactions_enabled_flag <> NVL(current_attribute_value, status_cursor_row.AV)
82 and msi.inventory_item_status_code = status_cursor_row.IISC;
83
84 elsif ( current_attribute_name = 'MTL_SYSTEM_ITEMS.PURCHASING_ENABLED_FLAG') then
85 update mtl_system_items msi
86 set msi.purchasing_enabled_flag
87 = NVL(current_attribute_value, status_cursor_row.AV),
88 msi.last_updated_by = to_number(fnd_profile.value('USER_ID')),
89 msi.last_update_date = sysdate,
90 msi.last_update_login = to_number(fnd_profile.value('LOGIN_ID'))
91 where msi.purchasing_item_flag = 'Y'
92 and msi.purchasing_enabled_flag <> NVL(current_attribute_value, status_cursor_row.AV)
93 and msi.inventory_item_status_code = status_cursor_row.IISC;
94
95 elsif ( current_attribute_name = 'MTL_SYSTEM_ITEMS.INVOICE_ENABLED_FLAG') then
96 update mtl_system_items msi
97 set msi.invoice_enabled_flag
98 = NVL(current_attribute_value, status_cursor_row.AV),
99 msi.last_updated_by = to_number(fnd_profile.value('USER_ID')),
100 msi.last_update_date = sysdate,
101 msi.last_update_login = to_number(fnd_profile.value('LOGIN_ID'))
102 where msi.invoiceable_item_flag = 'Y'
103 and msi.invoice_enabled_flag <> NVL(current_attribute_value, status_cursor_row.AV)
104 and msi.inventory_item_status_code = status_cursor_row.IISC;
105
106 elsif ( current_attribute_name = 'MTL_SYSTEM_ITEMS.BUILD_IN_WIP_FLAG') then
107 update mtl_system_items msi
108 set msi.build_in_wip_flag
109 = NVL(current_attribute_value, status_cursor_row.AV),
110 msi.last_updated_by = to_number(fnd_profile.value('USER_ID')),
111 msi.last_update_date = sysdate,
112 msi.last_update_login = to_number(fnd_profile.value('LOGIN_ID'))
113 where msi.inventory_item_flag = 'Y'
114 and msi.build_in_wip_flag <> NVL(current_attribute_value, status_cursor_row.AV)
115 and msi.bom_item_type = 4
116 and msi.inventory_item_status_code = status_cursor_row.IISC;
117
118 elsif ( current_attribute_name = 'MTL_SYSTEM_ITEMS.CUSTOMER_ORDER_ENABLED_FLAG') then
119 update mtl_system_items msi
120 set msi.customer_order_enabled_flag
121 = NVL(current_attribute_value, status_cursor_row.AV),
122 msi.last_updated_by = to_number(fnd_profile.value('USER_ID')),
123 msi.last_update_date = sysdate,
124 msi.last_update_login = to_number(fnd_profile.value('LOGIN_ID'))
125 where msi.customer_order_flag = 'Y'
126 and msi.customer_order_enabled_flag <> NVL(current_attribute_value, status_cursor_row.AV)
127 and msi.inventory_item_status_code = status_cursor_row.IISC;
128
129 elsif ( current_attribute_name = 'MTL_SYSTEM_ITEMS.INTERNAL_ORDER_ENABLED_FLAG') then
130 update mtl_system_items msi
131 set msi.internal_order_enabled_flag
132 = NVL(current_attribute_value, status_cursor_row.AV),
133 msi.last_updated_by = to_number(fnd_profile.value('USER_ID')),
134 msi.last_update_date = sysdate,
135 msi.last_update_login = to_number(fnd_profile.value('LOGIN_ID'))
136 where msi.internal_order_flag = 'Y'
137 and msi.internal_order_enabled_flag <> NVL(current_attribute_value, status_cursor_row.AV)
138 and msi.inventory_item_status_code = status_cursor_row.IISC;
139
140 elsif ( current_attribute_name = 'MTL_SYSTEM_ITEMS.BOM_ENABLED_FLAG') then
141 update mtl_system_items msi
142 set msi.bom_enabled_flag
143 = NVL(current_attribute_value, status_cursor_row.AV),
144 msi.last_updated_by = to_number(fnd_profile.value('USER_ID')),
145 msi.last_update_date = sysdate,
146 msi.last_update_login = to_number(fnd_profile.value('LOGIN_ID'))
147 where --Bug: 3546140 msi.inventory_item_flag = 'Y'and
148 msi.bom_enabled_flag <> NVL(current_attribute_value, status_cursor_row.AV)
149 and msi.inventory_item_status_code = status_cursor_row.IISC;
150 /* Jalaj Srivastava Bug 4032615 */
151 elsif ( current_attribute_name = 'MTL_SYSTEM_ITEMS.RECIPE_ENABLED_FLAG') then
152 update mtl_system_items msi
153 set msi.recipe_enabled_flag
154 = NVL(current_attribute_value, status_cursor_row.AV),
155 msi.last_updated_by = to_number(fnd_profile.value('USER_ID')),
156 msi.last_update_date = sysdate,
157 msi.last_update_login = to_number(fnd_profile.value('LOGIN_ID'))
158 where msi.recipe_enabled_flag <> NVL(current_attribute_value, status_cursor_row.AV)
159 and msi.inventory_item_status_code = status_cursor_row.IISC;
160
161 elsif ( current_attribute_name = 'MTL_SYSTEM_ITEMS.PROCESS_EXECUTION_ENABLED_FLAG') then
162 update mtl_system_items msi
163 set msi.process_execution_enabled_flag
164 = NVL(current_attribute_value, status_cursor_row.AV),
165 msi.last_updated_by = to_number(fnd_profile.value('USER_ID')),
166 msi.last_update_date = sysdate,
167 msi.last_update_login = to_number(fnd_profile.value('LOGIN_ID'))
168 where msi.process_execution_enabled_flag <> NVL(current_attribute_value, status_cursor_row.AV)
169 and msi.inventory_item_status_code = status_cursor_row.IISC;
170
171 end if;
172 /* intermediate commit commented out since we want the form to do
173 all the commits. Might need to uncomment it if this leads to resource issues.
174 commit;
175 */
176
177 END LOOP;
178 /* Commented out commit
179 commit;
180 */
181 CLOSE lock_records;
182 END UPDATE_ATTRIBUTES;
183
184 END INVUPDAT;