[Home] [Help]
PACKAGE BODY: APPS.GMO_VBATCH_TASK_PVT
Source
1 package body GMO_VBATCH_TASK_PVT AS
2 /* $Header: GMOVVTKB.pls 120.2.12020000.7 2013/04/25 13:54:00 rborpatl ship $ */
3
4 function is_wms_installed return varchar2 is
5 begin
6 if (inv_install.adv_inv_installed(NULL)) then
7 return 'TRUE';
8 else
9 return 'FALSE';
10 end if;
11 end is_wms_installed;
12
13 procedure get_resource_txn_end_date(
14 p_start_date IN DATE
15 ,p_usage IN NUMBER
16 ,p_trans_um IN VARCHAR2
17 ,x_end_date OUT NOCOPY DATE
18 ,x_return_status OUT NOCOPY VARCHAR2
19 ,x_message_count OUT NOCOPY NUMBER
20 ,x_message_data OUT NOCOPY VARCHAR2
21 ) IS
22
23 l_usage_time number;
24 l_txn_usage number;
25 l_hour_um varchar2(100);
26 missing_profile_option exception;
27 uom_conversion_err exception;
28 BEGIN
29 l_hour_um := fnd_profile.value_specific(name => 'BOM:HOUR_UOM_CODE',user_id => FND_GLOBAL.USER_ID);
30 IF (l_hour_um IS NULL) THEN
31 RAISE missing_profile_option;
32 END IF;
33 IF l_hour_um <> p_trans_um THEN
34 l_txn_usage := inv_convert.inv_um_convert
35 (
36 item_id => 0
37 ,PRECISION => 5
38 ,from_quantity => p_usage
39 ,from_unit => p_trans_um
40 ,to_unit => l_hour_um
41 ,from_name => NULL
42 ,to_name => NULL
43 );
44 IF (l_txn_usage = -99999) THEN
45 RAISE uom_conversion_err;
46 END IF;
47 ELSE
48 l_txn_usage := p_usage;
49 END IF;
50 x_end_date := p_start_date + (l_txn_usage/24);
51 x_return_status := fnd_api.g_ret_sts_success;
52 EXCEPTION
53 WHEN missing_profile_option THEN
54 x_return_status := fnd_api.g_ret_sts_error;
55 FND_MESSAGE.SET_NAME('GME','GME_API_UNABLE_TO_GET_CONSTANT');
56 FND_MESSAGE.SET_TOKEN('CONSTANT_NAME','BOM:HOUR_UOM_CODE');
57 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
58 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_task_pvt.get_resource_txn_end_date', FALSE);
59 end if;
60 FND_MSG_PUB.ADD;
61 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
62
63 WHEN uom_conversion_err THEN
64 x_return_status:= fnd_api.g_ret_sts_error;
65 FND_MESSAGE.SET_NAME('GME','GME_RSRC_USG_NT_CNV_SYUOM');
66 FND_MESSAGE.SET_TOKEN('SY_UOM',l_hour_um);
67 FND_MESSAGE.SET_TOKEN('RSRC_USG_UOM',p_trans_um);
68 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
69 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_task_pvt.get_resource_txn_end_date', FALSE);
70 end if;
71 FND_MSG_PUB.ADD;
72 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
73 WHEN OTHERS THEN
74 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
75 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
76 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
77 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
78 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
79 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.get_resource_txn_end_date', FALSE);
80 end if;
81 FND_MSG_PUB.ADD;
82 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
83 END get_resource_txn_end_date;
84
85 procedure get_resource_txn_usage(
86 p_start_date IN DATE
87 ,p_end_date IN DATE
88 ,p_trans_um IN VARCHAR2
89 ,x_usage OUT NOCOPY NUMBER
90 ,x_return_status OUT NOCOPY VARCHAR2
91 ,x_message_count OUT NOCOPY NUMBER
92 ,x_message_data OUT NOCOPY VARCHAR2
93 ) IS
94
95 l_usage_time number;
96 l_txn_usage number;
97 l_hour_um varchar2(100);
98 missing_profile_option exception;
99 uom_conversion_err exception;
100 BEGIN
101 l_usage_time := (p_end_date - p_start_date) * 24;
102 l_hour_um := fnd_profile.value_specific(name => 'BOM:HOUR_UOM_CODE',user_id => FND_GLOBAL.USER_ID);
103 IF (l_hour_um IS NULL) THEN
104 RAISE missing_profile_option;
105 END IF;
106 IF l_hour_um <> p_trans_um THEN
107 l_txn_usage := inv_convert.inv_um_convert
108 (
109 item_id => 0
110 ,PRECISION => 5
111 ,from_quantity => l_usage_time
112 ,from_unit => l_hour_um
113 ,to_unit => p_trans_um
114 ,from_name => NULL
115 ,to_name => NULL
116 );
117 IF (l_txn_usage = -99999) THEN
118 RAISE uom_conversion_err;
119 END IF;
120 ELSE
121 l_txn_usage := l_usage_time;
122 END IF;
123 x_return_status := fnd_api.g_ret_sts_success;
124 x_usage := l_txn_usage;
125 EXCEPTION
126 WHEN missing_profile_option THEN
127 x_return_status := fnd_api.g_ret_sts_error;
128 FND_MESSAGE.SET_NAME('GME','GME_API_UNABLE_TO_GET_CONSTANT');
129 FND_MESSAGE.SET_TOKEN('CONSTANT_NAME','BOM:HOUR_UOM_CODE');
130 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
131 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_task_pvt.get_resource_txn_usage', FALSE);
132 end if;
133 FND_MSG_PUB.ADD;
134 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
135
136 WHEN uom_conversion_err THEN
137 x_return_status:= fnd_api.g_ret_sts_error;
138 FND_MESSAGE.SET_NAME('GME','GME_RSRC_USG_NT_CNV_SYUOM');
139 FND_MESSAGE.SET_TOKEN('SY_UOM',l_hour_um);
140 FND_MESSAGE.SET_TOKEN('RSRC_USG_UOM',p_trans_um);
141 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
142 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_task_pvt.get_resource_txn_usage', FALSE);
143 end if;
144 FND_MSG_PUB.ADD;
145 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
146 WHEN OTHERS THEN
147 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
148 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
149 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
150 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
151 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
152 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.get_resource_txn_usage', FALSE);
153 end if;
154 FND_MSG_PUB.ADD;
155 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
156 END get_resource_txn_usage;
157
158 procedure get_resource_transaction_arr
159 (
160 p_resource_transaction_rec in GME_RESOURCE_TXNS_GTMP%ROWTYPE
161 ,x_resource_transaction_rec OUT NOCOPY fnd_table_of_varchar2_255
162 )
163 AS
164 BEGIN
165
166 x_resource_transaction_rec := new fnd_table_of_varchar2_255();
167
168 x_resource_transaction_rec.extend;
169 x_resource_transaction_rec(1) := p_resource_transaction_rec.POC_TRANS_ID;
170 x_resource_transaction_rec.extend;
171 x_resource_transaction_rec(2) := p_resource_transaction_rec.ORGN_CODE;
172 x_resource_transaction_rec.extend;
173 x_resource_transaction_rec(3) := p_resource_transaction_rec.DOC_TYPE;
174 x_resource_transaction_rec.extend;
175 x_resource_transaction_rec(4) := p_resource_transaction_rec.DOC_ID;
176 x_resource_transaction_rec.extend;
177 x_resource_transaction_rec(5) := p_resource_transaction_rec.LINE_ID;
178 x_resource_transaction_rec.extend;
179 x_resource_transaction_rec(6) := p_resource_transaction_rec.LINE_TYPE;
180 x_resource_transaction_rec.extend;
181 x_resource_transaction_rec(7) := p_resource_transaction_rec.RESOURCES;
182 x_resource_transaction_rec.extend;
183 x_resource_transaction_rec(8) := p_resource_transaction_rec.RESOURCE_USAGE;
184 x_resource_transaction_rec.extend;
185 x_resource_transaction_rec(9) := p_resource_transaction_rec.TRANS_UM;
186 x_resource_transaction_rec.extend;
187 x_resource_transaction_rec(10) := p_resource_transaction_rec.TRANS_DATE;
188 x_resource_transaction_rec.extend;
189 x_resource_transaction_rec(11) := p_resource_transaction_rec.COMPLETED_IND;
190 x_resource_transaction_rec.extend;
191 x_resource_transaction_rec(12) := p_resource_transaction_rec.POSTED_IND;
192 x_resource_transaction_rec.extend;
193 x_resource_transaction_rec(13) := p_resource_transaction_rec.REASON_CODE;
194 x_resource_transaction_rec.extend;
195 x_resource_transaction_rec(14) := p_resource_transaction_rec.EVENT_ID;
196 x_resource_transaction_rec.extend;
197 x_resource_transaction_rec(15) := p_resource_transaction_rec.INSTANCE_ID;
198 x_resource_transaction_rec.extend;
199 x_resource_transaction_rec(16) := p_resource_transaction_rec.SEQUENCE_DEPENDENT_IND;
200 x_resource_transaction_rec.extend;
201 x_resource_transaction_rec(17) := p_resource_transaction_rec.START_DATE;
202 x_resource_transaction_rec.extend;
203 x_resource_transaction_rec(18) := p_resource_transaction_rec.END_DATE;
204 x_resource_transaction_rec.extend;
205 x_resource_transaction_rec(19) := p_resource_transaction_rec.TEXT_CODE;
206 x_resource_transaction_rec.extend;
207 x_resource_transaction_rec(20) := p_resource_transaction_rec.OVERRIDED_PROTECTED_IND;
208 x_resource_transaction_rec.extend;
209 x_resource_transaction_rec(21) := p_resource_transaction_rec.ACTION_CODE;
210 x_resource_transaction_rec.extend;
211 x_resource_transaction_rec(22) := p_resource_transaction_rec.TRANSACTION_NO;
212 x_resource_transaction_rec.extend;
213 x_resource_transaction_rec(23) := p_resource_transaction_rec.DELETE_MARK;
214 x_resource_transaction_rec.extend;
215 x_resource_transaction_rec(24) := p_resource_transaction_rec.ORGANIZATION_ID;
216 x_resource_transaction_rec.extend;
217 x_resource_transaction_rec(25) := p_resource_transaction_rec.ATTRIBUTE_CATEGORY;
218 x_resource_transaction_rec.extend;
219 x_resource_transaction_rec(26) := p_resource_transaction_rec.ATTRIBUTE1;
220 x_resource_transaction_rec.extend;
221 x_resource_transaction_rec(27) := p_resource_transaction_rec.ATTRIBUTE2;
222 x_resource_transaction_rec.extend;
223 x_resource_transaction_rec(28) := p_resource_transaction_rec.ATTRIBUTE3;
224 x_resource_transaction_rec.extend;
225 x_resource_transaction_rec(29) := p_resource_transaction_rec.ATTRIBUTE4;
226 x_resource_transaction_rec.extend;
227 x_resource_transaction_rec(30) := p_resource_transaction_rec.ATTRIBUTE5;
228 x_resource_transaction_rec.extend;
229 x_resource_transaction_rec(31) := p_resource_transaction_rec.ATTRIBUTE6;
230 x_resource_transaction_rec.extend;
231 x_resource_transaction_rec(32) := p_resource_transaction_rec.ATTRIBUTE7;
232 x_resource_transaction_rec.extend;
233 x_resource_transaction_rec(33) := p_resource_transaction_rec.ATTRIBUTE8;
234 x_resource_transaction_rec.extend;
235 x_resource_transaction_rec(34) := p_resource_transaction_rec.ATTRIBUTE9;
236 x_resource_transaction_rec.extend;
237 x_resource_transaction_rec(35) := p_resource_transaction_rec.ATTRIBUTE10;
238 x_resource_transaction_rec.extend;
239 x_resource_transaction_rec(36) := p_resource_transaction_rec.ATTRIBUTE11;
240 x_resource_transaction_rec.extend;
241 x_resource_transaction_rec(37) := p_resource_transaction_rec.ATTRIBUTE12;
242 x_resource_transaction_rec.extend;
243 x_resource_transaction_rec(38) := p_resource_transaction_rec.ATTRIBUTE13;
244 x_resource_transaction_rec.extend;
245 x_resource_transaction_rec(39) := p_resource_transaction_rec.ATTRIBUTE14;
246 x_resource_transaction_rec.extend;
247 x_resource_transaction_rec(40) := p_resource_transaction_rec.ATTRIBUTE15;
248 x_resource_transaction_rec.extend;
249 x_resource_transaction_rec(41) := p_resource_transaction_rec.ATTRIBUTE16;
250 x_resource_transaction_rec.extend;
251 x_resource_transaction_rec(42) := p_resource_transaction_rec.ATTRIBUTE17;
252 x_resource_transaction_rec.extend;
253 x_resource_transaction_rec(43) := p_resource_transaction_rec.ATTRIBUTE18;
254 x_resource_transaction_rec.extend;
255 x_resource_transaction_rec(44) := p_resource_transaction_rec.ATTRIBUTE19;
256 x_resource_transaction_rec.extend;
257 x_resource_transaction_rec(45) := p_resource_transaction_rec.ATTRIBUTE20;
258 x_resource_transaction_rec.extend;
259 x_resource_transaction_rec(46) := p_resource_transaction_rec.ATTRIBUTE21;
260 x_resource_transaction_rec.extend;
261 x_resource_transaction_rec(47) := p_resource_transaction_rec.ATTRIBUTE22;
262 x_resource_transaction_rec.extend;
263 x_resource_transaction_rec(48) := p_resource_transaction_rec.ATTRIBUTE23;
264 x_resource_transaction_rec.extend;
265 x_resource_transaction_rec(49) := p_resource_transaction_rec.ATTRIBUTE24;
266 x_resource_transaction_rec.extend;
267 x_resource_transaction_rec(50) := p_resource_transaction_rec.ATTRIBUTE25;
268 x_resource_transaction_rec.extend;
269 x_resource_transaction_rec(51) := p_resource_transaction_rec.ATTRIBUTE26;
270 x_resource_transaction_rec.extend;
271 x_resource_transaction_rec(52) := p_resource_transaction_rec.ATTRIBUTE27;
272 x_resource_transaction_rec.extend;
273 x_resource_transaction_rec(53) := p_resource_transaction_rec.ATTRIBUTE28;
274 x_resource_transaction_rec.extend;
275 x_resource_transaction_rec(54) := p_resource_transaction_rec.ATTRIBUTE29;
276 x_resource_transaction_rec.extend;
277 x_resource_transaction_rec(55) := p_resource_transaction_rec.ATTRIBUTE30;
278 x_resource_transaction_rec.extend;
279 x_resource_transaction_rec(56) := p_resource_transaction_rec.REASON_ID;
280
281 END get_resource_transaction_arr;
282
283 procedure get_resource_transaction_rec
284 (
285 p_resource_transaction_rec IN fnd_table_of_varchar2_255
286 ,x_resource_transaction_rec OUT NOCOPY GME_RESOURCE_TXNS_GTMP%ROWTYPE
287 )
288 AS
289 BEGIN
290 x_resource_transaction_rec.POC_TRANS_ID := p_resource_transaction_rec(1);
291 x_resource_transaction_rec.ORGN_CODE := p_resource_transaction_rec(2);
292 x_resource_transaction_rec.DOC_TYPE := p_resource_transaction_rec(3);
293 x_resource_transaction_rec.DOC_ID := p_resource_transaction_rec(4);
294 x_resource_transaction_rec.LINE_ID := p_resource_transaction_rec(5);
295 x_resource_transaction_rec.LINE_TYPE := p_resource_transaction_rec(6);
296 x_resource_transaction_rec.RESOURCES := p_resource_transaction_rec(7);
297 x_resource_transaction_rec.RESOURCE_USAGE := p_resource_transaction_rec(8);
298 x_resource_transaction_rec.TRANS_UM := p_resource_transaction_rec(9);
299 x_resource_transaction_rec.TRANS_DATE := fnd_date.displaydt_to_date(p_resource_transaction_rec(10), FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE);
300 x_resource_transaction_rec.COMPLETED_IND := p_resource_transaction_rec(11);
301 x_resource_transaction_rec.POSTED_IND := p_resource_transaction_rec(12);
302 x_resource_transaction_rec.REASON_CODE := p_resource_transaction_rec(13);
303 x_resource_transaction_rec.EVENT_ID := p_resource_transaction_rec(14);
304 x_resource_transaction_rec.INSTANCE_ID := p_resource_transaction_rec(15);
305 x_resource_transaction_rec.SEQUENCE_DEPENDENT_IND := p_resource_transaction_rec(16);
306 x_resource_transaction_rec.START_DATE := fnd_date.displaydt_to_date(p_resource_transaction_rec(17), FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE);
307 x_resource_transaction_rec.END_DATE := fnd_date.displaydt_to_date(p_resource_transaction_rec(18), FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE);
308 x_resource_transaction_rec.TEXT_CODE := p_resource_transaction_rec(19);
309 x_resource_transaction_rec.OVERRIDED_PROTECTED_IND := p_resource_transaction_rec(20);
310 x_resource_transaction_rec.ACTION_CODE := p_resource_transaction_rec(21);
311 x_resource_transaction_rec.TRANSACTION_NO := p_resource_transaction_rec(22);
312 x_resource_transaction_rec.DELETE_MARK := p_resource_transaction_rec(23);
313 x_resource_transaction_rec.ORGANIZATION_ID := p_resource_transaction_rec(24);
314 x_resource_transaction_rec.ATTRIBUTE_CATEGORY := p_resource_transaction_rec(25);
315 x_resource_transaction_rec.ATTRIBUTE1 := p_resource_transaction_rec(26);
316 x_resource_transaction_rec.ATTRIBUTE2 := p_resource_transaction_rec(27);
317 x_resource_transaction_rec.ATTRIBUTE3 := p_resource_transaction_rec(28);
318 x_resource_transaction_rec.ATTRIBUTE4 := p_resource_transaction_rec(29);
319 x_resource_transaction_rec.ATTRIBUTE5 := p_resource_transaction_rec(30);
320 x_resource_transaction_rec.ATTRIBUTE6 := p_resource_transaction_rec(31);
321 x_resource_transaction_rec.ATTRIBUTE7 := p_resource_transaction_rec(32);
322 x_resource_transaction_rec.ATTRIBUTE8 := p_resource_transaction_rec(33);
323 x_resource_transaction_rec.ATTRIBUTE9 := p_resource_transaction_rec(34);
324 x_resource_transaction_rec.ATTRIBUTE10 := p_resource_transaction_rec(35);
325 x_resource_transaction_rec.ATTRIBUTE11 := p_resource_transaction_rec(36);
326 x_resource_transaction_rec.ATTRIBUTE12 := p_resource_transaction_rec(37);
327 x_resource_transaction_rec.ATTRIBUTE13 := p_resource_transaction_rec(38);
328 x_resource_transaction_rec.ATTRIBUTE14 := p_resource_transaction_rec(39);
329 x_resource_transaction_rec.ATTRIBUTE15 := p_resource_transaction_rec(40);
330 x_resource_transaction_rec.ATTRIBUTE16 := p_resource_transaction_rec(41);
331 x_resource_transaction_rec.ATTRIBUTE17 := p_resource_transaction_rec(42);
332 x_resource_transaction_rec.ATTRIBUTE18 := p_resource_transaction_rec(43);
333 x_resource_transaction_rec.ATTRIBUTE19 := p_resource_transaction_rec(44);
334 x_resource_transaction_rec.ATTRIBUTE20 := p_resource_transaction_rec(45);
335 x_resource_transaction_rec.ATTRIBUTE21 := p_resource_transaction_rec(46);
336 x_resource_transaction_rec.ATTRIBUTE22 := p_resource_transaction_rec(47);
337 x_resource_transaction_rec.ATTRIBUTE23 := p_resource_transaction_rec(48);
338 x_resource_transaction_rec.ATTRIBUTE24 := p_resource_transaction_rec(49);
339 x_resource_transaction_rec.ATTRIBUTE25 := p_resource_transaction_rec(50);
340 x_resource_transaction_rec.ATTRIBUTE26 := p_resource_transaction_rec(51);
341 x_resource_transaction_rec.ATTRIBUTE27 := p_resource_transaction_rec(52);
342 x_resource_transaction_rec.ATTRIBUTE28 := p_resource_transaction_rec(53);
343 x_resource_transaction_rec.ATTRIBUTE29 := p_resource_transaction_rec(54);
344 x_resource_transaction_rec.ATTRIBUTE30 := p_resource_transaction_rec(55);
345 x_resource_transaction_rec.REASON_ID := p_resource_transaction_rec(56);
346
347 END get_resource_transaction_rec;
348
349 procedure create_resource_transaction (
350 p_resource_transaction_rec IN fnd_table_of_varchar2_255
351 , p_actual_resource_count IN NUMBER
352 ,x_resource_transaction_rec OUT NOCOPY fnd_table_of_varchar2_255
353 ,x_return_status OUT NOCOPY VARCHAR2
354 ,x_message_count OUT NOCOPY NUMBER
355 ,x_message_data OUT NOCOPY VARCHAR2
356 )
357 AS
358 l_resource_transaction_rec_in gme_resource_txns_gtmp%rowtype;
359 l_resource_transaction_rec_out gme_resource_txns_gtmp%rowtype;
360 BEGIN
361 get_resource_transaction_rec(p_resource_transaction_rec, l_resource_transaction_rec_in);
362 /*
363 gme_resource_engine_pvt.create_resource_trans (
364 p_tran_rec => l_resource_transaction_rec_in,
365 x_tran_rec => l_resource_transaction_rec_out,
366 x_return_status => x_return_status);
367 */
368 gme_api_grp.create_resource_txn (
369 p_rsrc_txn_gtmp_rec => l_resource_transaction_rec_in,
370 x_rsrc_txn_gtmp_rec => l_resource_transaction_rec_out,
371 x_return_status => x_return_status);
372
373 IF (x_return_status = fnd_api.g_ret_sts_success) then
374 UPDATE GME_BATCH_STEP_RESOURCES
375 SET actual_rsrc_count = p_actual_resource_count
376 WHERE BATCHSTEP_RESOURCE_ID=l_resource_transaction_rec_in.LINE_ID;
377 get_resource_transaction_arr(l_resource_transaction_rec_out, x_resource_transaction_rec);
378 end if;
379 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
380 END create_resource_transaction;
381
382 procedure update_resource_transaction (
383 p_resource_transaction_rec IN fnd_table_of_varchar2_255
384 , p_actual_resource_count IN NUMBER
385 ,x_return_status OUT NOCOPY VARCHAR2
386 ,x_message_count OUT NOCOPY NUMBER
387 ,x_message_data OUT NOCOPY VARCHAR2
388 )
389 AS
390 l_resource_transaction_rec_in gme_resource_txns_gtmp%rowtype;
391 BEGIN
392 get_resource_transaction_rec(p_resource_transaction_rec, l_resource_transaction_rec_in);
393 /*
394 gme_resource_engine_pvt.update_resource_trans (
395 p_tran_rec => l_resource_transaction_rec_in,
396 x_return_status => x_return_status);
397 */
398 gme_api_grp.update_resource_txn (
399 p_rsrc_txn_gtmp_rec => l_resource_transaction_rec_in,
400 x_return_status => x_return_status);
401 UPDATE GME_BATCH_STEP_RESOURCES
402 SET actual_rsrc_count = p_actual_resource_count
403 WHERE BATCHSTEP_RESOURCE_ID=l_resource_transaction_rec_in.LINE_ID;
404 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
405 END update_resource_transaction;
406
407 procedure delete_resource_transaction (
408 p_resource_transaction_rec IN fnd_table_of_varchar2_255
409 ,x_return_status OUT NOCOPY VARCHAR2
410 ,x_message_count OUT NOCOPY NUMBER
411 ,x_message_data OUT NOCOPY VARCHAR2
412 )
413 AS
414 l_resource_transaction_rec_in gme_resource_txns_gtmp%rowtype;
415 BEGIN
416 get_resource_transaction_rec(p_resource_transaction_rec, l_resource_transaction_rec_in);
417 /*
418 gme_resource_engine_pvt.delete_resource_trans (
419 p_tran_rec => l_resource_transaction_rec_in,
420 x_return_status => x_return_status);
421 */
422 gme_api_grp.delete_resource_txn (
423 p_rsrc_txn_gtmp_rec => l_resource_transaction_rec_in,
424 x_return_status => x_return_status);
425
426 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
427 END delete_resource_transaction;
428
429 procedure setup_resource_transaction(
430 p_org_id NUMBER,
431 p_org_code VARCHAR2,
432 p_batch_id NUMBER,
433 x_return_status OUT NOCOPY VARCHAR2,
434 x_message_count OUT NOCOPY NUMBER,
435 x_message_data OUT NOCOPY VARCHAR2
436 ) AS
437 l_batch_record gme_batch_header%rowtype;
438 l_rsrc_row_count number;
439 BEGIN
440
441 if (gme_common_pvt.setup(p_org_id => p_org_id, p_org_code => p_org_code)) then
442 x_return_status := 'S';
443 else
444 x_return_status := 'E';
445 end if;
446 select * into l_batch_record from gme_batch_header where batch_id = p_batch_id;
447 gme_trans_engine_util.load_rsrc_trans (p_batch_row =>l_batch_record, x_rsc_row_count => l_rsrc_row_count, x_return_status => x_return_status);
448 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
449 END setup_resource_transaction;
450
451 procedure update_process_parameter
452 (
453 p_batch_no IN VARCHAR2
454 ,p_org_code IN VARCHAR2
455 ,p_validate_flexfields IN VARCHAR2
456 ,p_batchstep_no IN NUMBER
457 ,p_activity IN VARCHAR2
458 ,p_parameter IN VARCHAR2
459 ,p_process_param_rec IN fnd_table_of_varchar2_255
460 ,x_process_param_rec OUT NOCOPY fnd_table_of_varchar2_255
461 ,x_return_status OUT NOCOPY VARCHAR2
462 ,x_message_count OUT NOCOPY NUMBER
463 ,x_message_data OUT NOCOPY VARCHAR2
464 ) AS
465
466 l_process_param_rec_in gme_process_parameters%rowtype;
467 l_process_param_rec_out gme_process_parameters%rowtype;
468
469 BEGIN
470
471
472 l_process_param_rec_in.PROCESS_PARAM_ID := p_process_param_rec(1);
473 l_process_param_rec_in.BATCH_ID := p_process_param_rec(2);
474 l_process_param_rec_in.BATCHSTEP_ID := p_process_param_rec(3);
475 l_process_param_rec_in.BATCHSTEP_ACTIVITY_ID := p_process_param_rec(4);
476 l_process_param_rec_in.BATCHSTEP_RESOURCE_ID := p_process_param_rec(5);
477 l_process_param_rec_in.RESOURCES := p_process_param_rec(6);
478 l_process_param_rec_in.PARAMETER_ID := p_process_param_rec(7);
479 l_process_param_rec_in.TARGET_VALUE := p_process_param_rec(8);
480 l_process_param_rec_in.MINIMUM_VALUE := p_process_param_rec(9);
481 l_process_param_rec_in.MAXIMUM_VALUE := p_process_param_rec(10);
482 l_process_param_rec_in.PARAMETER_UOM := p_process_param_rec(11);
483 l_process_param_rec_in.ATTRIBUTE_CATEGORY := p_process_param_rec(12);
484 l_process_param_rec_in.ATTRIBUTE1 := p_process_param_rec(13);
485 l_process_param_rec_in.ATTRIBUTE2 := p_process_param_rec(14);
486 l_process_param_rec_in.ATTRIBUTE3 := p_process_param_rec(15);
487 l_process_param_rec_in.ATTRIBUTE4 := p_process_param_rec(16);
488 l_process_param_rec_in.ATTRIBUTE5 := p_process_param_rec(17);
489 l_process_param_rec_in.ATTRIBUTE6 := p_process_param_rec(18);
490 l_process_param_rec_in.ATTRIBUTE7 := p_process_param_rec(19);
491 l_process_param_rec_in.ATTRIBUTE8 := p_process_param_rec(20);
492 l_process_param_rec_in.ATTRIBUTE9 := p_process_param_rec(21);
493 l_process_param_rec_in.ATTRIBUTE10 := p_process_param_rec(22);
494 l_process_param_rec_in.ATTRIBUTE11 := p_process_param_rec(23);
495 l_process_param_rec_in.ATTRIBUTE12 := p_process_param_rec(24);
496 l_process_param_rec_in.ATTRIBUTE13 := p_process_param_rec(25);
497 l_process_param_rec_in.ATTRIBUTE14 := p_process_param_rec(26);
498 l_process_param_rec_in.ATTRIBUTE15 := p_process_param_rec(27);
499 l_process_param_rec_in.ATTRIBUTE16 := p_process_param_rec(28);
500 l_process_param_rec_in.ATTRIBUTE17 := p_process_param_rec(29);
501 l_process_param_rec_in.ATTRIBUTE18 := p_process_param_rec(30);
502 l_process_param_rec_in.ATTRIBUTE19 := p_process_param_rec(31);
503 l_process_param_rec_in.ATTRIBUTE20 := p_process_param_rec(32);
504 l_process_param_rec_in.ATTRIBUTE21 := p_process_param_rec(33);
505 l_process_param_rec_in.ATTRIBUTE22 := p_process_param_rec(34);
506 l_process_param_rec_in.ATTRIBUTE23 := p_process_param_rec(35);
507 l_process_param_rec_in.ATTRIBUTE24 := p_process_param_rec(36);
508 l_process_param_rec_in.ATTRIBUTE25 := p_process_param_rec(37);
509 l_process_param_rec_in.ATTRIBUTE26 := p_process_param_rec(38);
510 l_process_param_rec_in.ATTRIBUTE27 := p_process_param_rec(39);
511 l_process_param_rec_in.ATTRIBUTE28 := p_process_param_rec(40);
512 l_process_param_rec_in.ATTRIBUTE29 := p_process_param_rec(41);
513 l_process_param_rec_in.ATTRIBUTE30 := p_process_param_rec(42);
514 l_process_param_rec_in.CREATED_BY := p_process_param_rec(43);
515 l_process_param_rec_in.CREATION_DATE := fnd_date.displaydt_to_date(p_process_param_rec(44), FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE);
516 l_process_param_rec_in.LAST_UPDATED_BY := p_process_param_rec(45);
517 l_process_param_rec_in.LAST_UPDATE_LOGIN := p_process_param_rec(46);
518 l_process_param_rec_in.LAST_UPDATE_DATE := fnd_date.displaydt_to_date(p_process_param_rec(47), FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE);
519 l_process_param_rec_in.ACTUAL_VALUE := p_process_param_rec(48);
520 l_process_param_rec_in.DEVICE_ID := p_process_param_rec(49);
521
522 gme_api_pub.update_process_parameter
523 (
524 x_message_count => x_message_count
525 ,x_message_list => x_message_data
526 ,x_return_status => x_return_status
527 ,p_batch_no => p_batch_no
528 ,p_org_code => p_org_code
529 ,p_validate_flexfields => p_validate_flexfields
530 ,p_batchstep_no => p_batchstep_no
531 ,p_activity => p_activity
532 ,p_parameter => p_parameter
533 ,p_process_param_rec => l_process_param_rec_in
534 ,x_process_param_rec => l_process_param_rec_out
535 );
536
537 x_process_param_rec := new fnd_table_of_varchar2_255();
538
539 x_process_param_rec.extend;
540 x_process_param_rec(1) := l_process_param_rec_out.PROCESS_PARAM_ID ;
541 x_process_param_rec.extend;
542 x_process_param_rec(2) := l_process_param_rec_out.BATCH_ID ;
543 x_process_param_rec.extend;
544 x_process_param_rec(3) := l_process_param_rec_out.BATCHSTEP_ID ;
545 x_process_param_rec.extend;
546 x_process_param_rec(4) := l_process_param_rec_out.BATCHSTEP_ACTIVITY_ID ;
547 x_process_param_rec.extend;
548 x_process_param_rec(5) := l_process_param_rec_out.BATCHSTEP_RESOURCE_ID ;
549 x_process_param_rec.extend;
550 x_process_param_rec(6) := l_process_param_rec_out.RESOURCES ;
551 x_process_param_rec.extend;
552 x_process_param_rec(7) := l_process_param_rec_out.PARAMETER_ID ;
553 x_process_param_rec.extend;
554 x_process_param_rec(8) := l_process_param_rec_out.TARGET_VALUE ;
555 x_process_param_rec.extend;
556 x_process_param_rec(9) := l_process_param_rec_out.MINIMUM_VALUE ;
557 x_process_param_rec.extend;
558 x_process_param_rec(10) := l_process_param_rec_out.MAXIMUM_VALUE ;
559 x_process_param_rec.extend;
560 x_process_param_rec(11) := l_process_param_rec_out.PARAMETER_UOM ;
561 x_process_param_rec.extend;
562 x_process_param_rec(12) := l_process_param_rec_out.ATTRIBUTE_CATEGORY ;
563 x_process_param_rec.extend;
564 x_process_param_rec(13) := l_process_param_rec_out.ATTRIBUTE1 ;
565 x_process_param_rec.extend;
566 x_process_param_rec(14) := l_process_param_rec_out.ATTRIBUTE2 ;
567 x_process_param_rec.extend;
568 x_process_param_rec(15) := l_process_param_rec_out.ATTRIBUTE3 ;
569 x_process_param_rec.extend;
570 x_process_param_rec(16) := l_process_param_rec_out.ATTRIBUTE4 ;
571 x_process_param_rec.extend;
572 x_process_param_rec(17) := l_process_param_rec_out.ATTRIBUTE5 ;
573 x_process_param_rec.extend;
574 x_process_param_rec(18) := l_process_param_rec_out.ATTRIBUTE6 ;
575 x_process_param_rec.extend;
576 x_process_param_rec(19) := l_process_param_rec_out.ATTRIBUTE7 ;
577 x_process_param_rec.extend;
578 x_process_param_rec(20) := l_process_param_rec_out.ATTRIBUTE8 ;
579 x_process_param_rec.extend;
580 x_process_param_rec(21) := l_process_param_rec_out.ATTRIBUTE9 ;
581 x_process_param_rec.extend;
582 x_process_param_rec(22) := l_process_param_rec_out.ATTRIBUTE10 ;
583 x_process_param_rec.extend;
584 x_process_param_rec(23) := l_process_param_rec_out.ATTRIBUTE11 ;
585 x_process_param_rec.extend;
586 x_process_param_rec(24) := l_process_param_rec_out.ATTRIBUTE12 ;
587 x_process_param_rec.extend;
588 x_process_param_rec(25) := l_process_param_rec_out.ATTRIBUTE13 ;
589 x_process_param_rec.extend;
590 x_process_param_rec(26) := l_process_param_rec_out.ATTRIBUTE14 ;
591 x_process_param_rec.extend;
592 x_process_param_rec(27) := l_process_param_rec_out.ATTRIBUTE15 ;
593 x_process_param_rec.extend;
594 x_process_param_rec(28) := l_process_param_rec_out.ATTRIBUTE16 ;
595 x_process_param_rec.extend;
596 x_process_param_rec(29) := l_process_param_rec_out.ATTRIBUTE17 ;
597 x_process_param_rec.extend;
598 x_process_param_rec(30) := l_process_param_rec_out.ATTRIBUTE18 ;
599 x_process_param_rec.extend;
600 x_process_param_rec(31) := l_process_param_rec_out.ATTRIBUTE19 ;
601 x_process_param_rec.extend;
602 x_process_param_rec(32) := l_process_param_rec_out.ATTRIBUTE20 ;
603 x_process_param_rec.extend;
604 x_process_param_rec(33) := l_process_param_rec_out.ATTRIBUTE21 ;
605 x_process_param_rec.extend;
606 x_process_param_rec(34) := l_process_param_rec_out.ATTRIBUTE22 ;
607 x_process_param_rec.extend;
608 x_process_param_rec(35) := l_process_param_rec_out.ATTRIBUTE23 ;
609 x_process_param_rec.extend;
610 x_process_param_rec(36) := l_process_param_rec_out.ATTRIBUTE24 ;
611 x_process_param_rec.extend;
612 x_process_param_rec(37) := l_process_param_rec_out.ATTRIBUTE25 ;
613 x_process_param_rec.extend;
614 x_process_param_rec(38) := l_process_param_rec_out.ATTRIBUTE26 ;
615 x_process_param_rec.extend;
616 x_process_param_rec(39) := l_process_param_rec_out.ATTRIBUTE27 ;
617 x_process_param_rec.extend;
618 x_process_param_rec(40) := l_process_param_rec_out.ATTRIBUTE28 ;
619 x_process_param_rec.extend;
620 x_process_param_rec(41) := l_process_param_rec_out.ATTRIBUTE29 ;
621 x_process_param_rec.extend;
622 x_process_param_rec(42) := l_process_param_rec_out.ATTRIBUTE30 ;
623 x_process_param_rec.extend;
624 x_process_param_rec(43) := l_process_param_rec_out.CREATED_BY ;
625 x_process_param_rec.extend;
626 x_process_param_rec(44) := l_process_param_rec_out.CREATION_DATE ;
627 x_process_param_rec.extend;
628 x_process_param_rec(45) := l_process_param_rec_out.LAST_UPDATED_BY ;
629 x_process_param_rec.extend;
630 x_process_param_rec(46) := l_process_param_rec_out.LAST_UPDATE_LOGIN ;
631 x_process_param_rec.extend;
632 x_process_param_rec(47) := l_process_param_rec_out.LAST_UPDATE_DATE ;
633 x_process_param_rec.extend;
634 x_process_param_rec(48) := l_process_param_rec_out.ACTUAL_VALUE ;
635 x_process_param_rec.extend;
636 x_process_param_rec(49) := l_process_param_rec_out.DEVICE_ID ;
637
638
639 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
640
641 END update_process_parameter;
642
643 procedure save_batch (
644 p_table in number
645 ,x_return_status OUT NOCOPY VARCHAR2
646 ,x_message_count OUT NOCOPY NUMBER
647 ,x_message_data OUT NOCOPY VARCHAR2
648 ) is
649 begin
650 gme_api_pub.save_batch (
651 p_header_id => null,
652 p_table => p_table,
653 p_commit => fnd_api.g_false,
654 x_return_status => x_return_status,
655 p_clear_qty_cache =>FND_API.g_true
656 );
657 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
658 end save_batch;
659
660
661 procedure get_material_transactions(
662 p_organization_id IN NUMBER
663 ,p_batch_id IN NUMBER
664 ,p_material_detail_id IN NUMBER
665 ,x_mmt_cur OUT NOCOPY gme_api_grp.g_gmo_txns
666 ,x_return_status OUT NOCOPY VARCHAR2
667 ,x_message_count OUT NOCOPY NUMBER
668 ,x_message_data OUT NOCOPY VARCHAR2
669 )
670 is
671
672 begin
673 /*
674 open x_mmt_cur for
675 SELECT
676 mmt.transaction_id, mmt.subinventory_code, mmt.transaction_quantity, mmt.transaction_uom,
677 mmt.secondary_transaction_quantity, mmt.secondary_uom_code, mtlt.lot_number, mmt.revision,
678 mmt.locator_id, (select concatenated_segments
679 from mtl_item_locations_kfv
680 where organization_id = mmt.organization_id
681 and subinventory_code = mmt.subinventory_code
682 and inventory_location_id=mmt.locator_id) locator_code,
683 mmt.TRANSACTION_TYPE_ID
684 FROM mtl_material_transactions mmt, mtl_transaction_lot_numbers mtlt
685 WHERE mmt.transaction_source_id = p_batch_id
686 AND mmt.trx_source_line_id = p_material_detail_id
687 AND mmt.transaction_source_type_id = 5
688 AND NOT EXISTS (SELECT transaction_id1
689 FROM gme_transaction_pairs
690 WHERE transaction_id1 = mmt.transaction_id
691 AND pair_type = 1)
692 and mtlt.transaction_id (+) = mmt.transaction_id
693 and mmt.organization_id = p_organization_id;
694 x_return_status := 'S';
695
696 */
697 gme_api_grp.get_mat_trans
698 (
699 p_organization_id => p_organization_id
700 ,p_mat_det_id => p_material_detail_id
701 ,p_batch_id => p_batch_id
702 ,x_txns_cur => x_mmt_cur
703 ,x_return_status => x_return_status
704 );
705 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
706
707 end get_material_transactions;
708
709 procedure get_lot_transactions(
710 p_transaction_id IN NUMBER
711 ,x_lt_cur OUT NOCOPY gme_api_grp.g_gmo_lot_txns
712 ,x_return_status OUT NOCOPY VARCHAR2
713 ,x_message_count OUT NOCOPY NUMBER
714 ,x_message_data OUT NOCOPY VARCHAR2
715 )
716 IS
717 begin
718 gme_api_grp.get_lot_trans
719 (
720 p_transaction_id => p_transaction_id
721 ,x_lot_txns_cur => x_lt_cur
722 ,x_return_status => x_return_status
723 );
724 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
725 end get_lot_transactions;
726
727 procedure get_material_reservations(
728 p_organization_id IN NUMBER
729 ,p_batch_id IN NUMBER
730 ,p_material_detail_id IN NUMBER
731 ,x_res_cur OUT NOCOPY gme_api_grp.g_gmo_resvns
732 ,x_return_status OUT NOCOPY VARCHAR2
733 ,x_message_count OUT NOCOPY NUMBER
734 ,x_message_data OUT NOCOPY VARCHAR2
735 )
736 IS
737 begin
738 /*
739 open x_res_cur for
740 SELECT
741 reservation_id, subinventory_code, primary_reservation_quantity, reservation_uom_code,
742 secondary_reservation_quantity, secondary_uom_code, lot_number, revision,
743 locator_id, (select concatenated_segments
744 from mtl_item_locations_kfv
745 where organization_id = mr.organization_id
746 and subinventory_code = mr.subinventory_code
747 and inventory_location_id=mr.locator_id)
748 FROM mtl_reservations mr
749 WHERE organization_id = p_organization_id
750 AND demand_source_type_id = 5
751 AND demand_source_header_id = p_batch_id
752 AND demand_source_line_id = p_material_detail_id
753 AND NOT EXISTS (SELECT 1
754 FROM mtl_material_transactions_temp
755 WHERE reservation_id = mr.reservation_id);
756 x_return_status := 'S';
757 */
758 gme_api_grp.get_mat_resvns
759 (
760 p_organization_id => p_organization_id
761 ,p_mat_det_id => p_material_detail_id
762 ,p_batch_id => p_batch_id
763 ,x_resvns_cur => x_res_cur
764 ,x_return_status => x_return_status
765 );
766
767 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
768
769 end get_material_reservations;
770
771 procedure get_material_pplots(
772 p_organization_id IN NUMBER
773 ,p_batch_id IN NUMBER
774 ,p_material_detail_id IN NUMBER
775 ,x_pplot_cur OUT NOCOPY gme_api_grp.g_gmo_pplots
776 ,x_return_status OUT NOCOPY VARCHAR2
777 ,x_message_count OUT NOCOPY NUMBER
778 ,x_message_data OUT NOCOPY VARCHAR2
779 )
780 IS
781 begin
782 gme_api_grp.get_mat_pplots
783 (
784 p_mat_det_id => p_material_detail_id
785 ,x_pplot_cur => x_pplot_cur
786 ,x_return_status => x_return_status
787 );
788
789 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
790 end get_material_pplots;
791
792
793 procedure convert_um (
794 p_organization_id IN NUMBER
795 ,p_inventory_item_id IN NUMBER
796 ,p_lot_number IN VARCHAR2
797 ,p_from_qty IN NUMBER
798 ,p_from_um IN VARCHAR2
799 ,p_to_um IN VARCHAR2
800 ,x_to_qty OUT NOCOPY NUMBER
801 ,x_return_status OUT NOCOPY VARCHAR2
802 ,x_message_count OUT NOCOPY NUMBER
803 ,x_message_data OUT NOCOPY VARCHAR2
804 )
805 is
806 uom_conversion_err exception;
807 begin
808 x_to_qty := inv_convert.inv_um_convert(
809 p_inventory_item_id,
810 p_lot_number,
811 p_organization_id,
812 5,
813 p_from_qty,
814 p_from_um,
815 p_to_um,
816 null,
817 null
818 );
819 if (x_to_qty = -99999) THEN
820 RAISE uom_conversion_err;
821 END IF;
822
823 x_return_status := fnd_api.g_ret_sts_success;
824 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
825 exception
826 WHEN uom_conversion_err THEN
827 x_return_status:= fnd_api.g_ret_sts_error;
828 FND_MESSAGE.SET_NAME('GMO','GMO_UM_CONVERT_ERR');
829 FND_MESSAGE.SET_TOKEN('FROM_UOM',p_from_um);
830 FND_MESSAGE.SET_TOKEN('TO_UOM',p_to_um);
831 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
832 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_task_pvt.convert_um', FALSE);
833 end if;
834 FND_MSG_PUB.ADD;
835 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
836 WHEN OTHERS THEN
837 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
838 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
839 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
840 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
841 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
842 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.convert_um', FALSE);
843 end if;
844 FND_MSG_PUB.ADD;
845 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
846
847 end convert_um;
848
849 procedure qty_within_deviation (
850 p_organization_id IN NUMBER
851 ,p_inventory_item_id IN NUMBER
852 ,p_lot_number IN NUMBER
853 ,p_qty IN NUMBER
854 ,p_um IN VARCHAR2
855 ,p_sec_qty IN NUMBER
856 ,p_sec_um IN VARCHAR2
857 ,x_return_status OUT NOCOPY VARCHAR2
858 ,x_message_count OUT NOCOPY NUMBER
859 ,x_message_data OUT NOCOPY VARCHAR2
860 )
861 IS
862
863 DEV_LOW_ERROR EXCEPTION;
864 DEV_HIGH_ERROR EXCEPTION;
865 INVALID_ITEM EXCEPTION;
866 INCORRECT_FIXED_VALUE EXCEPTION;
867 INVALID_UOM_CONV EXCEPTION;
868
869 l_is_valid NUMBER(1);
870 l_msg_index_out NUMBER;
871
872 BEGIN
873
874 x_return_status := FND_API.G_RET_STS_SUCCESS;
875
876 -- Validate the quantities within deviation
877 l_is_valid := INV_CONVERT.within_deviation(
878 p_organization_id => p_organization_id
879 , p_inventory_item_id => p_inventory_item_id
880 , p_lot_number => p_lot_number
881 , p_precision => 5
882 , p_quantity => ABS(p_qty)
883 , p_uom_code1 => p_um
884 , p_quantity2 => ABS(p_sec_qty)
885 , p_uom_code2 => p_sec_um);
886 IF (l_is_valid = 0)
887 THEN
888
889 x_return_status := FND_API.G_RET_STS_ERROR;
890
891 FND_MSG_PUB.Get(
892 p_msg_index => 1,
893 p_data => x_message_data,
894 p_encoded => FND_API.G_FALSE,
895 p_msg_index_out => l_msg_index_out);
896 END IF;
897
898 EXCEPTION
899 WHEN INVALID_ITEM THEN
900 x_return_status:= fnd_api.g_ret_sts_error;
901 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ITEM');
902 FND_MSG_PUB.ADD;
903 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
904 WHEN INCORRECT_FIXED_VALUE THEN
905 x_return_status:= fnd_api.g_ret_sts_error;
906 FND_MESSAGE.SET_NAME('INV','INV_INCORRECT_FIXED_VALUE');
907 FND_MSG_PUB.ADD;
908 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
909 WHEN INVALID_UOM_CONV THEN
910 x_return_status:= fnd_api.g_ret_sts_error;
911 FND_MESSAGE.SET_NAME('INV','INV_INVALID_UOM_CONV');
912 FND_MESSAGE.SET_TOKEN ('VALUE1',p_um);
913 FND_MESSAGE.SET_TOKEN ('VALUE2',p_sec_um);
914 FND_MSG_PUB.ADD;
915 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
916 WHEN DEV_LOW_ERROR THEN
917 x_return_status:= fnd_api.g_ret_sts_error;
918 FND_MESSAGE.SET_NAME('INV','INV_DEVIATION_LO_ERR');
919 FND_MSG_PUB.ADD;
920 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
921 WHEN DEV_HIGH_ERROR THEN
922 x_return_status:= fnd_api.g_ret_sts_error;
923 FND_MESSAGE.SET_NAME('INV','INV_DEVIATION_HI_ERR');
924 FND_MSG_PUB.ADD;
925 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
926 WHEN OTHERS THEN
927 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
928 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
929 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
930 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
931 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
932 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.qty_within_deviation', FALSE);
933 end if;
934 FND_MSG_PUB.ADD;
935 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
936 END qty_within_deviation;
937
938 procedure get_dispense_um(
939 p_material_detail_id IN NUMBER
940 ,x_dispense_um OUT NOCOPY VARCHAR2
941 ,x_return_status OUT NOCOPY VARCHAR2
942 ,x_message_count OUT NOCOPY NUMBER
943 ,x_message_data OUT NOCOPY VARCHAR2
944 )
945
946 is
947 l_instr_def_key VARCHAR2(40);
948 l_dispense_config_row GMO_DISPENSE_CONFIG%ROWTYPE;
949 DISP_NOT_REQ_EXCEPTION exception;
950 begin
951 GMO_DISPENSE_SETUP_PVT.GET_DISPENSE_CONFIG_INST(P_ENTITY_NAME=> GMO_DISPENSE_GRP.G_MATERIAL_LINE_ENTITY,
952 P_ENTITY_KEY=> p_material_detail_id ,
953 X_DISPENSE_CONFIG => l_dispense_config_row,
954 X_INSTRUCTION_DEFINITION_KEY => l_instr_def_key);
955 if(l_dispense_config_row.config_id is null) then
956 RAISE DISP_NOT_REQ_EXCEPTION;
957 end if;
958 x_dispense_um := l_dispense_config_row.dispense_uom;
959 x_return_status := fnd_api.g_ret_sts_success;
960 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
961 exception
962 when DISP_NOT_REQ_EXCEPTION then
963 x_return_status:= fnd_api.g_ret_sts_error;
964 FND_MESSAGE.SET_NAME('GMO','GMO_DISP_DISPENSE_NOT_REQ_ERR');
965 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
966 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_task_pvt.get_dispense_um', FALSE);
967 end if;
968 FND_MSG_PUB.ADD;
969 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
970 WHEN OTHERS THEN
971 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
972 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
973 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
974 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
975 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
976 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.get_dispense_um', FALSE);
977 end if;
978 FND_MSG_PUB.ADD;
979 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
980
981 end get_dispense_um;
982
983 procedure relieve_reservation(
984 p_reservation_id IN NUMBER
985 ,p_prim_relieve_quantity IN NUMBER
986 ,x_return_status OUT NOCOPY VARCHAR2
987 ,x_message_count OUT NOCOPY NUMBER
988 ,x_message_data OUT NOCOPY VARCHAR2
989 )
990 IS
991
992 BEGIN
993
994 gme_reservations_pvt.relieve_reservation (
995 p_reservation_id => p_reservation_id
996 ,p_prim_relieve_qty => p_prim_relieve_quantity
997 ,x_return_status => x_return_status
998 );
999
1000 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1001
1002 EXCEPTION
1003 WHEN OTHERS THEN
1004 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1005 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
1006 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1007 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1008 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1009 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.get_resource_txn_end_date', FALSE);
1010 end if;
1011 FND_MSG_PUB.ADD;
1012 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1013 END relieve_reservation;
1014
1015 procedure relieve_pending_lot(
1016 p_pending_lot_id IN NUMBER
1017 ,p_quantity IN NUMBER
1018 ,p_secondary_quantity IN NUMBER
1019 ,x_return_status OUT NOCOPY VARCHAR2
1020 ,x_message_count OUT NOCOPY NUMBER
1021 ,x_message_data OUT NOCOPY VARCHAR2
1022 )
1023 IS
1024 BEGIN
1025
1026 gme_pending_product_lots_pvt.relieve_pending_lot (
1027 p_pending_lot_id => p_pending_lot_id
1028 ,p_quantity => p_quantity
1029 ,p_secondary_quantity => p_secondary_quantity
1030 ,x_return_status => x_return_status
1031 );
1032
1033 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1034
1035 EXCEPTION
1036 WHEN OTHERS THEN
1037 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1038 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
1039 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1040 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1041 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1042 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.relieve_pending_lot', FALSE);
1043 end if;
1044 FND_MSG_PUB.ADD;
1045 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1046 END relieve_pending_lot;
1047
1048
1049 procedure create_material_transaction(
1050 p_mtl_txn_rec IN fnd_table_of_varchar2_255
1051 ,p_mtl_lot_rec IN fnd_table_of_varchar2_255
1052 ,x_return_status OUT NOCOPY VARCHAR2
1053 ,x_message_count OUT NOCOPY NUMBER
1054 ,x_message_data OUT NOCOPY VARCHAR2
1055 )
1056 IS
1057
1058 l_mtl_txn_rec mtl_transactions_interface%ROWTYPE;
1059 l_mtl_lot_rec mtl_transaction_lots_interface%ROWTYPE;
1060 l_mtl_lot_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1061 l_mtl_lot_num_rec mtl_lot_numbers%ROWTYPE;
1062 l_lot_expiration_date DATE;
1063 l_orig_date DATE;
1064 CURSOR c_fetch_lot_attributes(cp_lot_number mtl_lot_numbers.lot_number%TYPE,
1065 cp_organization_id mtl_lot_numbers.organization_id%TYPE,
1066 cp_inventory_item_id mtl_lot_numbers.inventory_item_id%TYPE) IS
1067 SELECT * FROM mtl_lot_numbers
1068 WHERE lot_number=cp_lot_number
1069 AND organization_id= cp_organization_id
1070 AND inventory_item_id = cp_inventory_item_id ;
1071
1072 BEGIN
1073 l_mtl_txn_rec.TRANSACTION_INTERFACE_ID := p_mtl_txn_rec(1);
1074 l_mtl_txn_rec.TRANSACTION_TYPE_ID := p_mtl_txn_rec(2);
1075 l_mtl_txn_rec.REVISION := p_mtl_txn_rec(3);
1076 l_mtl_txn_rec.TRANSACTION_UOM := p_mtl_txn_rec(4);
1077 l_mtl_txn_rec.TRANSACTION_DATE := fnd_date.displaydt_to_date(p_mtl_txn_rec(5), FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE);
1078 l_mtl_txn_rec.SUBINVENTORY_CODE := p_mtl_txn_rec(6);
1079 l_mtl_txn_rec.SECONDARY_UOM_CODE := p_mtl_txn_rec(7);
1080 l_mtl_txn_rec.SECONDARY_TRANSACTION_QUANTITY := p_mtl_txn_rec(8);
1081 l_mtl_txn_rec.PRIMARY_QUANTITY := p_mtl_txn_rec(9);
1082 l_mtl_txn_rec.TRANSACTION_QUANTITY := p_mtl_txn_rec(10);
1083 l_mtl_txn_rec.ORGANIZATION_ID := p_mtl_txn_rec(11);
1084 l_mtl_txn_rec.REASON_ID := p_mtl_txn_rec(12);
1085 l_mtl_txn_rec.TRANSACTION_ACTION_ID := p_mtl_txn_rec(13);
1086 l_mtl_txn_rec.TRANSACTION_SOURCE_NAME := p_mtl_txn_rec(14);
1087 l_mtl_txn_rec.TRX_SOURCE_LINE_ID := p_mtl_txn_rec(15);
1088 l_mtl_txn_rec.LOCATOR_ID := p_mtl_txn_rec(16);
1089 l_mtl_txn_rec.INVENTORY_ITEM_ID := p_mtl_txn_rec(17);
1090 l_mtl_txn_rec.TRANSACTION_REFERENCE := p_mtl_txn_rec(18);
1091 l_mtl_txn_rec.TRANSACTION_SOURCE_ID := p_mtl_txn_rec(19);
1092
1093 if(p_mtl_lot_rec IS NOT NULL and p_mtl_lot_rec.count > 0) THEN
1094 l_mtl_lot_rec.TRANSACTION_QUANTITY := p_mtl_lot_rec(1);
1095 l_mtl_lot_rec.TRANSACTION_INTERFACE_ID := p_mtl_lot_rec(2);
1096 l_mtl_lot_rec.SECONDARY_TRANSACTION_QUANTITY := p_mtl_lot_rec(3);
1097 l_mtl_lot_rec.PRIMARY_QUANTITY := p_mtl_lot_rec(4);
1098 l_mtl_lot_rec.LOT_NUMBER := p_mtl_lot_rec(5);
1099 l_mtl_lot_tbl(1) := l_mtl_lot_rec;
1100 end if;
1101
1102 SAVEPOINT create_transaction;
1103 inv_quantity_tree_pub.clear_quantity_cache;
1104
1105 gme_api_grp.create_material_txn(
1106 p_mmti_rec => l_mtl_txn_rec,
1107 p_mmli_tbl => l_mtl_lot_tbl,
1108 x_return_status => x_return_status
1109 );
1110
1111 IF(x_return_status = 'S') THEN
1112 inv_calculate_exp_date.get_lot_expiration_date(
1113 p_mtli_lot_rec => l_mtl_lot_rec
1114 ,p_mti_trx_rec => l_mtl_txn_rec
1115 ,p_mtlt_lot_rec => null
1116 ,p_mmtt_trx_rec => null
1117 ,p_table => 1
1118 ,x_lot_expiration_date => l_lot_expiration_date
1119 ,x_return_status => x_return_status);
1120 IF(x_return_status <> 'S') THEN
1121 ROLLBACK TO create_transaction;
1122 END IF;
1123 get_origination_date (
1124 p_inventory_item_id => l_mtl_txn_rec.INVENTORY_ITEM_ID
1125 ,p_organization_id => l_mtl_txn_rec.ORGANIZATION_ID
1126 ,p_lot_number => l_mtl_lot_rec.LOT_NUMBER
1127 ,p_transaction_date => l_mtl_txn_rec.TRANSACTION_DATE
1128 ,x_orig_date => l_orig_date
1129 ,x_return_status => x_return_status);
1130 IF(x_return_status <> 'S') THEN
1131 ROLLBACK TO create_transaction;
1132 END IF;
1133 OPEN c_fetch_lot_attributes(l_mtl_lot_rec.LOT_NUMBER,l_mtl_txn_rec.ORGANIZATION_ID,l_mtl_txn_rec.INVENTORY_ITEM_ID);
1134 LOOP
1135 FETCH c_fetch_lot_attributes INTO l_mtl_lot_num_rec;
1136 EXIT WHEN c_fetch_lot_attributes%NOTFOUND;
1137 if (l_mtl_lot_num_rec.EXPIRATION_DATE is null and
1138 l_mtl_txn_rec.transaction_type_id in (44,1002)) then
1139 inv_calculate_exp_date.update_inv_lot_attr(
1140 x_return_status => x_return_status
1141 , x_msg_count => x_message_count
1142 , x_msg_data => x_message_data
1143 , p_inventory_item_id => l_mtl_txn_rec.INVENTORY_ITEM_ID
1144 , p_organization_id => l_mtl_txn_rec.ORGANIZATION_ID
1145 , p_lot_number => l_mtl_lot_rec.LOT_NUMBER
1146 , p_source => 1
1147 , p_expiration_date => l_lot_expiration_date
1148 , p_grade_code => l_mtl_lot_num_rec.grade_code
1149 , p_origination_date => l_orig_date
1150 , p_origination_type => l_mtl_lot_num_rec.origination_type
1151 , p_status_id => l_mtl_lot_num_rec.status_id
1152 , p_retest_date => l_mtl_lot_num_rec.retest_date
1153 , p_maturity_date => l_mtl_lot_num_rec.maturity_date
1154 , p_supplier_lot_number => l_mtl_lot_num_rec.supplier_lot_number
1155 , p_expiration_action_code => l_mtl_lot_num_rec.expiration_action_code
1156 , p_expiration_action_date => l_mtl_lot_num_rec.expiration_action_date
1157 , p_hold_date => l_mtl_lot_num_rec.hold_date
1158 , p_c_attribute1 => l_mtl_lot_num_rec.c_attribute1
1159 , p_c_attribute2 => l_mtl_lot_num_rec.c_attribute2
1160 , p_c_attribute3 => l_mtl_lot_num_rec.c_attribute3
1161 , p_c_attribute4 => l_mtl_lot_num_rec.c_attribute4
1162 , p_c_attribute5 => l_mtl_lot_num_rec.c_attribute5
1163 , p_c_attribute6 => l_mtl_lot_num_rec.c_attribute6
1164 , p_c_attribute7 => l_mtl_lot_num_rec.c_attribute7
1165 , p_c_attribute8 => l_mtl_lot_num_rec.c_attribute8
1166 , p_c_attribute9 => l_mtl_lot_num_rec.c_attribute9
1167 , p_c_attribute10 => l_mtl_lot_num_rec.c_attribute10
1168 , p_c_attribute11 => l_mtl_lot_num_rec.c_attribute11
1169 , p_c_attribute12 => l_mtl_lot_num_rec.c_attribute12
1170 , p_c_attribute13 => l_mtl_lot_num_rec.c_attribute13
1171 , p_c_attribute14 => l_mtl_lot_num_rec.c_attribute14
1172 , p_c_attribute15 => l_mtl_lot_num_rec.c_attribute15
1173 , p_c_attribute16 => l_mtl_lot_num_rec.c_attribute16
1174 , p_c_attribute17 => l_mtl_lot_num_rec.c_attribute17
1175 , p_c_attribute18 => l_mtl_lot_num_rec.c_attribute18
1176 , p_c_attribute19 => l_mtl_lot_num_rec.c_attribute19
1177 , p_c_attribute20 => l_mtl_lot_num_rec.c_attribute20
1178 , p_d_attribute1 => l_mtl_lot_num_rec.d_attribute1
1179 , p_d_attribute2 => l_mtl_lot_num_rec.d_attribute2
1180 , p_d_attribute3 => l_mtl_lot_num_rec.d_attribute3
1181 , p_d_attribute4 => l_mtl_lot_num_rec.d_attribute4
1182 , p_d_attribute5 => l_mtl_lot_num_rec.d_attribute5
1183 , p_d_attribute6 => l_mtl_lot_num_rec.d_attribute6
1184 , p_d_attribute7 => l_mtl_lot_num_rec.d_attribute7
1185 , p_d_attribute8 => l_mtl_lot_num_rec.d_attribute8
1186 , p_d_attribute9 => l_mtl_lot_num_rec.d_attribute9
1187 , p_d_attribute10 => l_mtl_lot_num_rec.d_attribute10
1188 , p_n_attribute1 => l_mtl_lot_num_rec.n_attribute1
1189 , p_n_attribute2 => l_mtl_lot_num_rec.n_attribute2
1190 , p_n_attribute3 => l_mtl_lot_num_rec.n_attribute3
1191 , p_n_attribute4 => l_mtl_lot_num_rec.n_attribute4
1192 , p_n_attribute5 => l_mtl_lot_num_rec.n_attribute5
1193 , p_n_attribute6 => l_mtl_lot_num_rec.n_attribute6
1194 , p_n_attribute7 => l_mtl_lot_num_rec.n_attribute7
1195 , p_n_attribute8 => l_mtl_lot_num_rec.n_attribute8
1196 , p_n_attribute9 => l_mtl_lot_num_rec.n_attribute9
1197 , p_n_attribute10 => l_mtl_lot_num_rec.n_attribute10
1198 , p_description => l_mtl_lot_num_rec.description
1199 , p_vendor_name => l_mtl_lot_num_rec.vendor_name
1200 , p_date_code => l_mtl_lot_num_rec.date_code
1201 , p_change_date => l_mtl_lot_num_rec.change_date
1202 , p_age => l_mtl_lot_num_rec.age
1203 , p_item_size => l_mtl_lot_num_rec.item_size
1204 , p_color => l_mtl_lot_num_rec.color
1205 , p_volume => l_mtl_lot_num_rec.volume
1206 , p_volume_uom => l_mtl_lot_num_rec.volume_uom
1207 , p_place_of_origin => l_mtl_lot_num_rec.place_of_origin
1208 , p_best_by_date => l_mtl_lot_num_rec.best_by_date
1209 , p_length => l_mtl_lot_num_rec.length
1210 , p_length_uom => l_mtl_lot_num_rec.length_uom
1211 , p_recycled_content => l_mtl_lot_num_rec.recycled_content
1212 , p_thickness => l_mtl_lot_num_rec.thickness
1213 , p_thickness_uom => l_mtl_lot_num_rec.thickness_uom
1214 , p_width => l_mtl_lot_num_rec.width
1215 , p_width_uom => l_mtl_lot_num_rec.width_uom
1216 , p_curl_wrinkle_fold => l_mtl_lot_num_rec.curl_wrinkle_fold
1217 , p_lot_attribute_category => l_mtl_lot_num_rec.lot_attribute_category
1218 , p_territory_code => l_mtl_lot_num_rec.territory_code
1219 , p_vendor_id => l_mtl_lot_num_rec.vendor_id
1220 , p_parent_lot_number => l_mtl_lot_num_rec.parent_lot_number
1221 );
1222 end if;
1223 END LOOP;
1224 CLOSE c_fetch_lot_attributes;
1225 IF(x_return_status <> 'S') THEN
1226 ROLLBACK TO create_transaction;
1227 END IF;
1228 ELSE
1229 ROLLBACK TO create_transaction;
1230 END IF;
1231
1232 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1233
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1237 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
1238 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1239 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1240 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1241 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.create_material_transaction', FALSE);
1242 end if;
1243 FND_MSG_PUB.ADD;
1244 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1245 END create_material_transaction;
1246
1247
1248
1249 procedure update_material_transaction(
1250 p_mtl_txn_rec IN fnd_table_of_varchar2_255
1251 ,p_mtl_lot_rec IN fnd_table_of_varchar2_255
1252 ,x_return_status OUT NOCOPY VARCHAR2
1253 ,x_message_count OUT NOCOPY NUMBER
1254 ,x_message_data OUT NOCOPY VARCHAR2
1255 )
1256 IS
1257
1258 l_mtl_txn_rec mtl_transactions_interface%ROWTYPE;
1259 l_mtl_lot_rec mtl_transaction_lots_interface%ROWTYPE;
1260 l_mtl_lot_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1261
1262 BEGIN
1263
1264 l_mtl_txn_rec.TRANSACTION_INTERFACE_ID := p_mtl_txn_rec(1);
1265 l_mtl_txn_rec.TRANSACTION_TYPE_ID := p_mtl_txn_rec(2);
1266 l_mtl_txn_rec.REVISION := p_mtl_txn_rec(3);
1267 l_mtl_txn_rec.TRANSACTION_UOM := p_mtl_txn_rec(4);
1268 l_mtl_txn_rec.TRANSACTION_DATE := fnd_date.displaydt_to_date(p_mtl_txn_rec(5), FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE);
1269 l_mtl_txn_rec.SUBINVENTORY_CODE := p_mtl_txn_rec(6);
1270 l_mtl_txn_rec.SECONDARY_UOM_CODE := p_mtl_txn_rec(7);
1271 l_mtl_txn_rec.SECONDARY_TRANSACTION_QUANTITY := p_mtl_txn_rec(8);
1272 l_mtl_txn_rec.PRIMARY_QUANTITY := p_mtl_txn_rec(9);
1273 l_mtl_txn_rec.TRANSACTION_QUANTITY := p_mtl_txn_rec(10);
1274 l_mtl_txn_rec.ORGANIZATION_ID := p_mtl_txn_rec(11);
1275 l_mtl_txn_rec.REASON_ID := p_mtl_txn_rec(12);
1276 l_mtl_txn_rec.TRANSACTION_ACTION_ID := p_mtl_txn_rec(13);
1277 l_mtl_txn_rec.TRANSACTION_SOURCE_NAME := p_mtl_txn_rec(14);
1278 l_mtl_txn_rec.TRX_SOURCE_LINE_ID := p_mtl_txn_rec(15);
1279 l_mtl_txn_rec.LOCATOR_ID := p_mtl_txn_rec(16);
1280 l_mtl_txn_rec.INVENTORY_ITEM_ID := p_mtl_txn_rec(17);
1281 l_mtl_txn_rec.TRANSACTION_REFERENCE := p_mtl_txn_rec(18);
1282 l_mtl_txn_rec.TRANSACTION_SOURCE_ID := p_mtl_txn_rec(19);
1283
1284 if(p_mtl_lot_rec IS NOT NULL and p_mtl_lot_rec.count > 0) THEN
1285 l_mtl_lot_rec.TRANSACTION_QUANTITY := p_mtl_lot_rec(1);
1286 l_mtl_lot_rec.TRANSACTION_INTERFACE_ID := p_mtl_lot_rec(2);
1287 l_mtl_lot_rec.SECONDARY_TRANSACTION_QUANTITY := p_mtl_lot_rec(3);
1288 l_mtl_lot_rec.PRIMARY_QUANTITY := p_mtl_lot_rec(4);
1289 l_mtl_lot_rec.LOT_NUMBER := p_mtl_lot_rec(5);
1290 l_mtl_lot_tbl(1) := l_mtl_lot_rec;
1291 end if;
1292
1293 SAVEPOINT update_transaction;
1294
1295 inv_quantity_tree_pub.clear_quantity_cache;
1296 gme_api_grp.update_material_txn(
1297 p_transaction_id => l_mtl_txn_rec.TRANSACTION_INTERFACE_ID,
1298 p_mmti_rec => l_mtl_txn_rec,
1299 p_mmli_tbl => l_mtl_lot_tbl,
1300 x_return_status => x_return_status
1301 );
1302
1303 IF(x_return_status <> 'S') THEN
1304 ROLLBACK TO update_transaction;
1305 END IF;
1306
1307 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1308
1309
1310 EXCEPTION
1311 WHEN OTHERS THEN
1312 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1313 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
1314 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1315 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1316 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1317 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.update_material_transaction', FALSE);
1318 end if;
1319 FND_MSG_PUB.ADD;
1320 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1321 END update_material_transaction;
1322
1323
1324 procedure delete_material_transaction(
1325 p_mtl_txn_rec IN fnd_table_of_varchar2_255
1326 ,p_mtl_lot_rec IN fnd_table_of_varchar2_255
1327 ,x_return_status OUT NOCOPY VARCHAR2
1328 ,x_message_count OUT NOCOPY NUMBER
1329 ,x_message_data OUT NOCOPY VARCHAR2
1330 )
1331 IS
1332
1333 l_mtl_txn_rec mtl_transactions_interface%ROWTYPE;
1334 l_mtl_lot_rec mtl_transaction_lots_interface%ROWTYPE;
1335 l_mtl_lot_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1336
1337 BEGIN
1338
1339 l_mtl_txn_rec.TRANSACTION_INTERFACE_ID := p_mtl_txn_rec(1);
1340 l_mtl_txn_rec.TRANSACTION_TYPE_ID := p_mtl_txn_rec(2);
1341 l_mtl_txn_rec.REVISION := p_mtl_txn_rec(3);
1342 l_mtl_txn_rec.TRANSACTION_UOM := p_mtl_txn_rec(4);
1343 l_mtl_txn_rec.TRANSACTION_DATE := fnd_date.displaydt_to_date(p_mtl_txn_rec(5), FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE);
1344 l_mtl_txn_rec.SUBINVENTORY_CODE := p_mtl_txn_rec(6);
1345 l_mtl_txn_rec.SECONDARY_UOM_CODE := p_mtl_txn_rec(7);
1346 l_mtl_txn_rec.SECONDARY_TRANSACTION_QUANTITY := p_mtl_txn_rec(8);
1347 l_mtl_txn_rec.PRIMARY_QUANTITY := p_mtl_txn_rec(9);
1348 l_mtl_txn_rec.TRANSACTION_QUANTITY := p_mtl_txn_rec(10);
1349 l_mtl_txn_rec.ORGANIZATION_ID := p_mtl_txn_rec(11);
1350 l_mtl_txn_rec.REASON_ID := p_mtl_txn_rec(12);
1351 l_mtl_txn_rec.TRANSACTION_ACTION_ID := p_mtl_txn_rec(13);
1352 l_mtl_txn_rec.TRANSACTION_SOURCE_NAME := p_mtl_txn_rec(14);
1353 l_mtl_txn_rec.TRX_SOURCE_LINE_ID := p_mtl_txn_rec(15);
1354 l_mtl_txn_rec.LOCATOR_ID := p_mtl_txn_rec(16);
1355 l_mtl_txn_rec.INVENTORY_ITEM_ID := p_mtl_txn_rec(17);
1356 l_mtl_txn_rec.TRANSACTION_REFERENCE := p_mtl_txn_rec(18);
1357 l_mtl_txn_rec.TRANSACTION_SOURCE_ID := p_mtl_txn_rec(19);
1358 if(p_mtl_lot_rec IS NOT NULL and p_mtl_lot_rec.count > 0) THEN
1359 l_mtl_lot_rec.TRANSACTION_QUANTITY := p_mtl_lot_rec(1);
1360 l_mtl_lot_rec.TRANSACTION_INTERFACE_ID := p_mtl_lot_rec(2);
1361 l_mtl_lot_rec.SECONDARY_TRANSACTION_QUANTITY := p_mtl_lot_rec(3);
1362 l_mtl_lot_rec.PRIMARY_QUANTITY := p_mtl_lot_rec(4);
1363 l_mtl_lot_rec.LOT_NUMBER := p_mtl_lot_rec(5);
1364
1365 l_mtl_lot_tbl(1) := l_mtl_lot_rec;
1366 end if;
1367 gme_api_grp.delete_material_txn(
1368 p_organization_id => l_mtl_txn_rec.ORGANIZATION_ID,
1369 p_transaction_id => l_mtl_txn_rec.TRANSACTION_INTERFACE_ID,
1370 x_return_status => x_return_status
1371 );
1372
1373 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1374
1375 EXCEPTION
1376 WHEN OTHERS THEN
1377 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1378 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
1379 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1380 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1381 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1382 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.delete_material_transaction', FALSE);
1383 end if;
1384 FND_MSG_PUB.ADD;
1385 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1386 END delete_material_transaction;
1387
1388 procedure create_lot(
1389 p_lot_rec IN fnd_table_of_varchar2_255
1390 ,x_return_status OUT NOCOPY VARCHAR2
1391 ,x_message_count OUT NOCOPY NUMBER
1392 ,x_message_data OUT NOCOPY VARCHAR2
1393 )
1394 IS
1395 l_lot_rec mtl_lot_numbers%ROWTYPE;
1396 l_char_tbl inv_lot_api_pub.char_tbl;
1397 l_number_tbl inv_lot_api_pub.number_tbl;
1398 l_date_tbl inv_lot_api_pub.date_tbl;
1399 BEGIN
1400 l_lot_rec.INVENTORY_ITEM_ID := p_lot_rec(1);
1401 l_lot_rec.ORGANIZATION_ID := p_lot_rec(2);
1402 l_lot_rec.LOT_NUMBER := p_lot_rec(3);
1403 if (p_lot_rec(4) is not null) then
1404 l_lot_rec.EXPIRATION_DATE := fnd_date.displaydt_to_date(p_lot_rec(4), FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE);
1405 end if;
1406 l_lot_rec.LAST_UPDATE_DATE := sysdate;
1407 l_lot_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1408 l_lot_rec.CREATION_DATE := sysdate;
1409 l_lot_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1410 l_lot_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1411 /*
1412 inv_lot_api_pub.create_inv_lot
1413 (
1414 p_lot_rec => l_lot_rec
1415 ,p_source => NULL
1416 ,p_api_version => 1.0
1417 ,p_init_msg_list => fnd_api.g_true
1418 ,p_commit => fnd_api.g_false
1419 ,p_validation_level => fnd_api.g_valid_level_full
1420 ,p_origin_txn_id => 1
1421 );
1422 */
1423 inv_lot_api_pub.create_inv_lot(
1424 x_return_status => x_return_status
1425 ,x_msg_count => x_message_count
1426 ,x_msg_data => x_message_data
1427 , p_inventory_item_id => l_lot_rec.INVENTORY_ITEM_ID
1428 , p_organization_id => l_lot_rec.ORGANIZATION_ID
1429 , p_lot_number => l_lot_rec.LOT_NUMBER
1430 , p_expiration_date => l_lot_rec.EXPIRATION_DATE
1431 , p_disable_flag => NULL
1432 , p_attribute_category => NULL
1433 , p_lot_attribute_category => NULL
1434 , p_attributes_tbl => l_char_tbl
1435 , p_c_attributes_tbl => l_char_tbl
1436 , p_n_attributes_tbl => l_number_tbl
1437 , p_d_attributes_tbl => l_date_tbl
1438 , p_grade_code => NULL
1439 , p_origination_date => NULL
1440 , p_date_code => NULL
1441 , p_status_id => NULL
1442 , p_change_date => NULL
1443 , p_age => NULL
1444 , p_retest_date => NULL
1445 , p_maturity_date => NULL
1446 , p_item_size => NULL
1447 , p_color => NULL
1448 , p_volume => NULL
1449 , p_volume_uom => NULL
1450 , p_place_of_origin => NULL
1451 , p_best_by_date => NULL
1452 , p_length => NULL
1453 , p_length_uom => NULL
1454 , p_recycled_content => NULL
1455 , p_thickness => NULL
1456 , p_thickness_uom => NULL
1457 , p_width => NULL
1458 , p_width_uom => NULL
1459 , p_territory_code => NULL
1460 , p_supplier_lot_number => NULL
1461 , p_vendor_name => NULL
1462 , p_source => NULL
1463 ) ;
1464 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1465 EXCEPTION
1466 WHEN OTHERS THEN
1467 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1468 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
1469 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1470 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1471 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1472 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.create_lot', FALSE);
1473 end if;
1474 FND_MSG_PUB.ADD;
1475 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1476 END create_lot;
1477
1478 procedure generate_lot(
1479 p_organization_id IN NUMBER
1480 ,p_inventory_item_id IN NUMBER
1481 ,x_lot_number OUT NOCOPY VARCHAR2
1482 ,x_return_status OUT NOCOPY VARCHAR2
1483 ,x_message_count OUT NOCOPY NUMBER
1484 ,x_message_data OUT NOCOPY VARCHAR2
1485 )
1486 IS
1487 BEGIN
1488 x_lot_number := INV_LOT_API_PUB.auto_gen_lot
1489 (
1490 p_org_id => p_organization_id,
1491 p_inventory_item_id => p_inventory_item_id,
1492 p_lot_generation => NULL,
1493 p_lot_uniqueness => NULL,
1494 p_lot_prefix => NULL,
1495 p_zero_pad => NULL,
1496 p_lot_length => NULL,
1497 p_transaction_date => NULL,
1498 p_revision => NULL,
1499 p_subinventory_code => NULL,
1500 p_locator_id => NULL,
1501 p_transaction_type_id => NULL,
1502 p_transaction_action_id => NULL,
1503 p_transaction_source_type_id => NULL,
1504 p_lot_number => NULL,
1505 p_api_version => 1.0,
1506 p_init_msg_list => FND_API.G_FALSE,
1507 p_commit => FND_API.G_FALSE,
1508 p_validation_level => NULL,
1509 p_parent_lot_number => null,
1510 x_return_status => x_return_status,
1511 x_msg_count => x_message_count,
1512 x_msg_data => x_message_data
1513 );
1514
1515 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1516 EXCEPTION
1517 WHEN OTHERS THEN
1518 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1519 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
1520 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1521 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1522 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1523 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.generate_lot', FALSE);
1524 end if;
1525 FND_MSG_PUB.ADD;
1526 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1527 END generate_lot;
1528
1529 procedure get_lot_event_key (
1530 p_organization_id IN NUMBER
1531 ,p_inventory_item_id IN NUMBER
1532 ,p_lot_number IN VARCHAR2
1533 ,x_lot_event_key OUT NOCOPY VARCHAR2
1534 )
1535 IS
1536 BEGIN
1537
1538 select gen_object_id into x_lot_event_key from mtl_lot_numbers
1539 where organization_id = p_organization_id
1540 and inventory_item_id = p_inventory_item_id
1541 and lot_number = p_lot_number;
1542
1543 END get_lot_event_key;
1544
1545 PROCEDURE release_or_complete_step
1546 (
1547 p_batch_id IN NUMBER,
1548 p_batchstep_id IN NUMBER,
1549 p_event IN VARCHAR2,
1550 x_return_status OUT NOCOPY VARCHAR2,
1551 x_message OUT NOCOPY VARCHAR2,
1552 x_message_count OUT NOCOPY NUMBER
1553
1554 ) IS
1555
1556 l_batch_step_details gme_batch_steps%ROWTYPE;
1557 l_batch_header gme_batch_header%ROWTYPE;
1558 vo_batch_step_details gme_batch_steps%ROWTYPE;
1559 vo_exception_material_tbl gme_common_pvt.exceptions_tab;
1560 x_batch_step_rec gme_batch_steps%ROWTYPE;
1561 v_batch_id NUMBER(15);
1562
1563 x_validate_status VARCHAR2(1);
1564 l_batch_no VARCHAR2(32);
1565 l_org_code VARCHAR2(4);
1566
1567 RELEASE_EXCEPTION EXCEPTION;
1568 COMPLETE_EXCEPTION EXCEPTION;
1569
1570 BEGIN
1571 v_batch_id := p_batch_id;
1572
1573 IF(v_batch_id=0) THEN
1574
1575 SELECT batch_id
1576 INTO v_batch_id
1577 FROM gme_batch_steps
1578 WHERE batchstep_id=p_batchstep_id;
1579 END IF;
1580
1581 /*Fetching the batch step details based on the batch_id and batchstep_id*/
1582
1583 SELECT *
1584 INTO l_batch_step_details
1585 FROM gme_batch_steps
1586 WHERE batchstep_id=p_batchstep_id
1587 AND batch_id=v_batch_id;
1588
1589 /*Fetching the batch details based on the batch_id */
1590
1591 SELECT *
1592 INTO l_batch_header
1593 FROM gme_batch_header
1594 WHERE batch_id=v_batch_id;
1595 --checks for step dependencies
1596 gme_release_batch_step_pvt.validate_step_for_release
1597 ( p_batch_header_rec => l_batch_header
1598 ,p_batch_step_rec => l_batch_step_details
1599 ,x_batch_step_rec => x_batch_step_rec
1600 ,x_return_status => x_validate_status);
1601
1602 IF( x_validate_status <> 'S') THEN
1603 ROLLBACK;
1604 x_return_status := 'E';
1605 x_message_count := 1;
1606 x_message := 'GME_RESOURCE_TXNS Step Dependencies';
1607 RAISE RELEASE_EXCEPTION;
1608 END IF;
1609
1610 IF(Lower(p_event)='release') THEN
1611
1612 /*calling release step api */
1613
1614 gme_api_pub.release_step(
1615 p_api_version => 2.0
1616 ,p_validation_level => gme_common_pvt.g_max_errors
1617 ,p_init_msg_list => fnd_api.g_false
1618 ,p_commit => fnd_api.g_false
1619 ,p_save_batch => fnd_api.g_false
1620 ,x_message_count => x_message_count
1621 ,x_message_list => x_message
1622 ,x_return_status => x_return_status
1623 ,p_batch_step_rec => l_batch_step_details
1624 ,p_batch_no => l_batch_header.batch_no
1625 ,p_org_code => l_batch_header.plant_code
1626 ,p_ignore_exception => fnd_api.g_false
1627 ,p_validate_flexfields => fnd_api.g_false
1628 ,x_batch_step_rec => x_batch_step_rec
1629 ,x_exception_material_tbl => vo_exception_material_tbl
1630 );
1631
1632 IF( x_return_status <> 'S' ) THEN
1633 ROLLBACK;
1634 RAISE RELEASE_EXCEPTION;
1635 END IF;
1636
1637 ELSIF (Lower(p_event)='complete') THEN
1638
1639 /*calling complete step api */
1640
1641 gme_api_pub.complete_step(
1642 p_api_version => 2.0
1643 ,p_validation_level => gme_common_pvt.g_max_errors
1644 ,p_init_msg_list => fnd_api.g_false
1645 ,p_commit => fnd_api.g_false
1646 ,p_save_batch => fnd_api.g_false
1647 ,x_message_count => x_message_count
1648 ,x_message_list => x_message
1649 ,x_return_status => x_return_status
1650 ,p_batch_step_rec => l_batch_step_details
1651 ,p_batch_no => l_batch_header.batch_no
1652 ,p_org_code => l_batch_header.plant_code
1653 ,p_ignore_exception => fnd_api.g_false
1654 ,p_validate_flexfields => fnd_api.g_false
1655 ,x_batch_step_rec => x_batch_step_rec
1656 ,x_exception_material_tbl => vo_exception_material_tbl
1657
1658 );
1659
1660 IF( x_return_status <> 'S' ) THEN
1661 ROLLBACK;
1662 RAISE COMPLETE_EXCEPTION;
1663 END IF;
1664
1665 END IF;
1666
1667 EXCEPTION
1668 WHEN RELEASE_EXCEPTION THEN
1669 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1670 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1671 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1672 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'release raised exception', FALSE);
1673 end if;
1674 FND_MSG_PUB.ADD;
1675 --FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message);
1676
1677 WHEN COMPLETE_EXCEPTION THEN
1678 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1679 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1680 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1681 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'complete raised exception', FALSE);
1682 end if;
1683 FND_MSG_PUB.ADD;
1684 --FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message);
1685
1686 WHEN OTHERS THEN
1687 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1688 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1689 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1690 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'Other exception', FALSE);
1691 end if;
1692 FND_MSG_PUB.ADD;
1693 --FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message);
1694
1695
1696 END release_or_complete_step;
1697 PROCEDURE get_origination_date
1698 ( p_inventory_item_id IN NUMBER
1699 ,p_organization_id IN NUMBER
1700 ,p_lot_number IN VARCHAR2
1701 ,p_transaction_date IN DATE
1702 ,x_orig_date OUT NOCOPY DATE
1703 ,x_return_status OUT NOCOPY VARCHAR2
1704 ) IS
1705 orig_date DATE;
1706 BEGIN
1707 x_return_status := FND_API.G_RET_STS_SUCCESS;
1708 BEGIN /*begin segment 2 */
1709 SELECT Nvl(origination_date,p_transaction_date)
1710 INTO x_orig_date
1711 FROM mtl_lot_numbers
1712 WHERE inventory_item_id = p_inventory_item_id
1713 AND lot_number = p_lot_number
1714 AND organization_id = p_organization_id;
1715 EXCEPTION
1716 WHEN no_data_found THEN
1717 x_orig_date := p_transaction_date;
1718 WHEN OTHERS THEN
1719 x_orig_date := NULL;
1720 END;
1721 EXCEPTION
1722 WHEN OTHERS THEN
1723 x_orig_date := NULL;
1724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1725 END get_origination_date;
1726 end GMO_VBATCH_TASK_PVT;