[Home] [Help]
PACKAGE BODY: APPS.INV_GRADE_PKG
Source
1 PACKAGE BODY INV_GRADE_PKG AS
2 /* $Header: INVUPLGB.pls 120.2 2011/12/26 04:04:33 ptian ship $ */
3
4 PROCEDURE print_debug(p_err_msg VARCHAR2,
5 p_level NUMBER default 4)
6 IS
7 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
8 BEGIN
9 IF (l_debug = 1) THEN
10 inv_mobile_helper_functions.tracelog
11 (p_err_msg => p_err_msg,
12 p_module => 'INV_GRADE_UPDATE',
13 p_level => p_level);
14 END IF;
15 END print_debug;
16
17 PROCEDURE UPDATE_GRADE
18 ( p_organization_id IN NUMBER DEFAULT NULL
19 , p_update_method IN NUMBER
20 , p_inventory_item_id IN NUMBER
21 , p_from_grade_code IN VARCHAR2
22 , p_to_grade_code IN VARCHAR2
23 , p_reason_id IN NUMBER
24 , p_lot_number IN VARCHAR2
25 , x_Status OUT NOCOPY VARCHAR2
26 , x_Message OUT NOCOPY VARCHAR2
27 , p_update_from_mobile IN VARCHAR2 DEFAULT 'N'
28 , p_primary_quantity IN NUMBER
29 , p_secondary_quantity IN NUMBER
30 ) IS
31 -- BEGIN SCHANDRU INVERES
32 l_grade_update_id NUMBER := NULL;
33 g_eres_enabled varchar2(1):= NVL(fnd_profile.VALUE('EDR_ERES_ENABLED'), 'N');
34
35 -- END SCHANDRU INVERES
36
37 --Global Lot ER Start,added varaibles below for call to quantity tree
38 l_qoh number;
39 l_rqoh number;
40 l_qr number;
41 l_qs number;
42 l_att number;
43 l_atr number;
44 l_sqoh number;
45 l_srqoh number;
46 l_sqr number;
47 l_sqs number;
48 l_satt number;
49 l_satr number;
50 l_return_status varchar2(1);
51 l_msg_count pls_integer;
52 l_msg_data varchar2(4000);
53 --Global Lot ER End
54
55 l_global_profile VARCHAR2(1) := NVL(FND_PROFILE.VALUE('INV_GLOBAL_LOT_ATTRIBUTES'),'N'); --Global Lot ER
56 l_orgid NUMBER; --Global Lot ER
57 --Global Lot ER Start
58 CURSOR get_all_orgid(p_item_id IN NUMBER, p_lot_number IN VARCHAR2) IS
59 SELECT organization_id
60 FROM mtl_lot_numbers
61 WHERE inventory_item_id = p_item_id
62 AND lot_number = p_lot_number;
63 --Global Lot ER End
64 BEGIN
65
66 /* Initialize API return status to success */
67 x_Status := FND_API.G_RET_STS_SUCCESS;
68
69
70 print_debug(' In Grade Update Package - Before Updating record ') ;
71
72 -- BEGIN SCHANDRU INVERES
73 select mtl_lot_grade_history_s.nextval
74 into l_grade_update_id
75 from dual;
76 -- END SCHANDRU INVERES
77
78 UPDATE mtl_lot_numbers
79 SET grade_code = p_to_grade_code
80 WHERE lot_number = p_lot_number
81 AND organization_id = p_organization_id
82 AND inventory_item_id = p_inventory_item_id ;
83
84 --COMMIT ;-- SCHANDRU INVERES
85
86 --Global Lot ER Start
87 IF l_global_profile = 'Y' THEN
88 UPDATE mtl_lot_numbers
89 SET
90 grade_code = p_to_grade_code
91 WHERE inventory_item_id = p_inventory_item_id
92 AND lot_number = p_lot_number;
93 END IF;
94 --Global Lot ER End
95
96 print_debug(' In Grade Update Package - Before inserting record ') ;
97
98 /* Define Savepoint */
99
100 -- BEGIN SCHANDRU INVERES
101 if ( p_update_from_mobile = 'Y') then
102 SAVEPOINT Insert_GradeUpdate_PVT;
103 end if;
104 -- END SCHANDRU INVERES
105
106 --Global Lot ER Start
107 IF l_global_profile = 'Y' THEN
108
109 OPEN get_all_orgid(p_inventory_item_id,p_lot_number);
110 LOOP
111 FETCH get_all_orgid INTO l_orgid;
112 EXIT WHEN get_all_orgid%NOTFOUND;
113
114 inv_quantity_tree_pub.query_quantities
115 (p_api_version_number => 1.0
116 ,x_return_status => l_return_status
117 ,x_msg_count => l_msg_count
118 ,x_msg_data => l_msg_data
119 ,p_organization_id => l_orgid
120 ,p_inventory_item_id => p_inventory_item_id
121 ,p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
122 ,p_is_revision_control => false
123 ,p_is_lot_control => true
124 ,p_is_serial_control => false
125 ,p_grade_code => null
126 ,p_revision => null
127 ,p_lot_number => p_lot_number
128 ,p_subinventory_code => null
129 ,p_locator_id => null
130 ,x_qoh => l_qoh
131 ,x_rqoh => l_rqoh
132 ,x_qr => l_qr
133 ,x_qs => l_qs
134 ,x_att => l_att
135 ,x_atr => l_atr
136 ,x_sqoh => l_sqoh
137 ,x_srqoh => l_srqoh
138 ,x_sqr => l_sqr
139 ,x_sqs => l_sqs
140 ,x_satt => l_satt
141 ,x_satr => l_satr
142 );
143
144 INSERT INTO MTL_LOT_GRADE_HISTORY
145 (
146 GRADE_UPDATE_ID
147 , INVENTORY_ITEM_ID
148 , ORGANIZATION_ID
149 , LOT_NUMBER
150 , UPDATE_METHOD
151 , NEW_GRADE_CODE
152 , OLD_GRADE_CODE
153 , PRIMARY_QUANTITY
154 , SECONDARY_QUANTITY
155 , UPDATE_REASON_ID
156 , INITIAL_GRADE_FLAG
157 , FROM_MOBILE_APPS_FLAG
158 , GRADE_UPDATE_DATE
159 , ATTRIBUTE1
160 , ATTRIBUTE2
161 , ATTRIBUTE3
162 , ATTRIBUTE4
163 , ATTRIBUTE5
164 , ATTRIBUTE6
165 , ATTRIBUTE7
166 , ATTRIBUTE8
167 , ATTRIBUTE9
168 , ATTRIBUTE10
169 , ATTRIBUTE11
170 , ATTRIBUTE12
171 , ATTRIBUTE13
172 , ATTRIBUTE14
173 , ATTRIBUTE15
174 , ATTRIBUTE_CATEGORY
175 , CREATION_DATE
176 , CREATED_BY
177 , LAST_UPDATED_BY
178 , LAST_UPDATE_DATE
179 , LAST_UPDATE_LOGIN
180 ) VALUES
181 (
182 -- BEGIN SCHANDRU INVERES
183 --MTL_LOT_GRADE_HISTORY_S.NEXTVAL
184 l_grade_update_id
185 -- END SCHADRU INVERES
186 , p_inventory_item_id
187 , l_orgid--p_organization_id
188 , p_lot_number
189 , p_update_method -- UPDATE_METHOD /* Jalaj Srivastava Bug 4998256 pass p_update_method instead of null */
190 , p_to_grade_code -- NEW_GRADE_CODE
191 , p_from_grade_code -- OLD_GRADE_CODE
192 , l_qoh -- PRIMARY_QUANTITY
193 , l_sqoh -- SECONDARY_QUANTITY
194 , p_reason_id -- UPDATE_REASON_ID
195 , 'N' -- INITIAL_GRADE_FLAG
196 , 'N' -- FROM_MOBILE_APPS_FLAG
197 , SYSDATE -- GRADE_UPDATE_DATE
198 , NULL -- ATTRIBUTE1
199 , NULL -- ATTRIBUTE2
200 , NULL -- ATTRIBUTE3
201 , NULL -- ATTRIBUTE4
202 , NULL -- ATTRIBUTE5
203 , NULL -- ATTRIBUTE6
204 , NULL -- ATTRIBUTE7
205 , NULL -- ATTRIBUTE8
206 , NULL -- ATTRIBUTE9
207 , NULL -- ATTRIBUTE10
208 , NULL -- ATTRIBUTE11
209 , NULL -- ATTRIBUTE12
210 , NULL -- ATTRIBUTE13
211 , NULL -- ATTRIBUTE14
212 , NULL -- ATTRIBUTE15
213 , NULL -- ATTRIBUTE_CATEGORY
214 , SYSDATE -- CREATION_DATE
215 , FND_GLOBAL.USER_ID -- CREATED_BY
216 , FND_GLOBAL.USER_ID -- LAST_UPDATED_BY
217 , SYSDATE -- LAST_UPDATE_DATE
218 , FND_GLOBAL.LOGIN_ID -- LAST_UPDATE_LOGIN
219 ) ;
220
221 END LOOP;
222 -- BEGIN SCHANDRU INVERES
223 IF g_eres_enabled <> 'N' THEN
224 Insert into MTL_GRADE_STATUS_ERES_GTMP(status_update_id, grade_update_id) values ( NULL, l_grade_update_id);
225 END IF;
226 -- END SCHANDRU INVERES
227
228 IF get_all_orgid%ISOPEN THEN
229 CLOSE get_all_orgid;
230 END IF;
231 --Global Lot ER END
232
233 ELSE --l_global_profile = 'N' Global Lot ER
234 INSERT INTO MTL_LOT_GRADE_HISTORY
235 (
236 GRADE_UPDATE_ID
237 , INVENTORY_ITEM_ID
238 , ORGANIZATION_ID
239 , LOT_NUMBER
240 , UPDATE_METHOD
241 , NEW_GRADE_CODE
242 , OLD_GRADE_CODE
243 , PRIMARY_QUANTITY
244 , SECONDARY_QUANTITY
245 , UPDATE_REASON_ID
246 , INITIAL_GRADE_FLAG
247 , FROM_MOBILE_APPS_FLAG
248 , GRADE_UPDATE_DATE
249 , ATTRIBUTE1
250 , ATTRIBUTE2
251 , ATTRIBUTE3
252 , ATTRIBUTE4
253 , ATTRIBUTE5
254 , ATTRIBUTE6
255 , ATTRIBUTE7
256 , ATTRIBUTE8
257 , ATTRIBUTE9
258 , ATTRIBUTE10
259 , ATTRIBUTE11
260 , ATTRIBUTE12
261 , ATTRIBUTE13
262 , ATTRIBUTE14
263 , ATTRIBUTE15
264 , ATTRIBUTE_CATEGORY
265 , CREATION_DATE
266 , CREATED_BY
267 , LAST_UPDATED_BY
268 , LAST_UPDATE_DATE
269 , LAST_UPDATE_LOGIN
270 ) VALUES
271 (
272 -- BEGIN SCHANDRU INVERES
273 -- MTL_LOT_GRADE_HISTORY_S.NEXTVAL
274 l_grade_update_id
275 -- END SCHADRU INVERES
276 , p_inventory_item_id
277 , p_organization_id
278 , p_lot_number
279 , p_update_method -- UPDATE_METHOD /* Jalaj Srivastava Bug 4998256 pass p_update_method instead of null */
280 , p_to_grade_code -- NEW_GRADE_CODE
281 , p_from_grade_code -- OLD_GRADE_CODE
282 , p_primary_quantity -- PRIMARY_QUANTITY
283 , p_secondary_quantity -- SECONDARY_QUANTITY
284 , p_reason_id -- UPDATE_REASON_ID
285 , 'N' -- INITIAL_GRADE_FLAG
286 , 'N' -- FROM_MOBILE_APPS_FLAG
287 , SYSDATE -- GRADE_UPDATE_DATE
288 , NULL -- ATTRIBUTE1
289 , NULL -- ATTRIBUTE2
290 , NULL -- ATTRIBUTE3
291 , NULL -- ATTRIBUTE4
292 , NULL -- ATTRIBUTE5
293 , NULL -- ATTRIBUTE6
294 , NULL -- ATTRIBUTE7
295 , NULL -- ATTRIBUTE8
296 , NULL -- ATTRIBUTE9
297 , NULL -- ATTRIBUTE10
298 , NULL -- ATTRIBUTE11
299 , NULL -- ATTRIBUTE12
300 , NULL -- ATTRIBUTE13
301 , NULL -- ATTRIBUTE14
302 , NULL -- ATTRIBUTE15
303 , NULL -- ATTRIBUTE_CATEGORY
304 , SYSDATE -- CREATION_DATE
305 , FND_GLOBAL.USER_ID -- CREATED_BY
306 , FND_GLOBAL.USER_ID -- LAST_UPDATED_BY
307 , SYSDATE -- LAST_UPDATE_DATE
308 , FND_GLOBAL.LOGIN_ID -- LAST_UPDATE_LOGIN
309 ) ;
310
311 -- BEGIN SCHANDRU INVERES
312 IF g_eres_enabled <> 'N' THEN
313
314 Insert into MTL_GRADE_STATUS_ERES_GTMP(status_update_id, grade_update_id) values ( NULL, l_grade_update_id);
315 END IF;
316 -- END SCHANDRU INVERES
317
318
319 END IF;
320
321
322
323 print_debug(' In Grade Update Package - AFtrer inserting record ') ;
324
325 --COMMIT ;-- SCHANDRU INVERES
326
327 EXCEPTION
328 WHEN FND_API.G_EXC_ERROR THEN
329 -- BEGIN SCHANDRU INVERES
330 if ( p_update_from_mobile = 'Y') then
331 ROLLBACK TO Insert_GradeUpdate_PVT;
332 end if;
333 -- END SCHANDRU INVERES
334 x_Status := FND_API.G_RET_STS_ERROR;
335 x_Message := SQLERRM ;
336 print_debug(' In Grade Update Package - Encountered exec error ') ;
337
338 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
339 -- BEGIN SCHANDRU INVERES
340 if ( p_update_from_mobile = 'Y') then
341 ROLLBACK TO Insert_GradeUpdate_PVT;
342 end if;
343 -- END SCHANDRU INVERES
344
345 x_Status := FND_API.G_RET_STS_ERROR;
346 x_Message := SQLERRM ;
347 print_debug(' In Grade Update Package - Encountered unexpected error ') ;
348 WHEN OTHERS THEN
349 -- BEGIN SCHANDRU INVERES
350 if ( p_update_from_mobile = 'Y') then
351 ROLLBACK TO Insert_GradeUpdate_PVT;
352 end if;
353 -- END SCHANDRU INVERES
354
355 x_Status := FND_API.G_RET_STS_UNEXP_ERROR;
356 X_Message := SQLERRM ;
357 print_debug(' In Grade Update Package - Encountered other error ') ;
358
359 END UPDATE_GRADE ;
360
361
362 END INV_GRADE_PKG ;