[Home] [Help]
PACKAGE BODY: APPS.GMO_VBATCH_TASK_PVT
Source
1 package body GMO_VBATCH_TASK_PVT AS
2 /* $Header: GMOVVTKB.pls 120.1 2007/06/21 06:16:59 rvsingh noship $ */
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 ,x_resource_transaction_rec OUT NOCOPY fnd_table_of_varchar2_255
352 ,x_return_status OUT NOCOPY VARCHAR2
353 ,x_message_count OUT NOCOPY NUMBER
354 ,x_message_data OUT NOCOPY VARCHAR2
355 )
356 AS
357 l_resource_transaction_rec_in gme_resource_txns_gtmp%rowtype;
358 l_resource_transaction_rec_out gme_resource_txns_gtmp%rowtype;
359 BEGIN
360 get_resource_transaction_rec(p_resource_transaction_rec, l_resource_transaction_rec_in);
361 /*
362 gme_resource_engine_pvt.create_resource_trans (
363 p_tran_rec => l_resource_transaction_rec_in,
364 x_tran_rec => l_resource_transaction_rec_out,
365 x_return_status => x_return_status);
366 */
367 gme_api_grp.create_resource_txn (
368 p_rsrc_txn_gtmp_rec => l_resource_transaction_rec_in,
369 x_rsrc_txn_gtmp_rec => l_resource_transaction_rec_out,
370 x_return_status => x_return_status);
371
372 IF (x_return_status = fnd_api.g_ret_sts_success) then
373 get_resource_transaction_arr(l_resource_transaction_rec_out, x_resource_transaction_rec);
374 end if;
375 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
376 END create_resource_transaction;
377
378 procedure update_resource_transaction (
379 p_resource_transaction_rec IN fnd_table_of_varchar2_255
380 ,x_return_status OUT NOCOPY VARCHAR2
381 ,x_message_count OUT NOCOPY NUMBER
382 ,x_message_data OUT NOCOPY VARCHAR2
383 )
384 AS
385 l_resource_transaction_rec_in gme_resource_txns_gtmp%rowtype;
386 BEGIN
387 get_resource_transaction_rec(p_resource_transaction_rec, l_resource_transaction_rec_in);
388 /*
389 gme_resource_engine_pvt.update_resource_trans (
390 p_tran_rec => l_resource_transaction_rec_in,
391 x_return_status => x_return_status);
392 */
393 gme_api_grp.update_resource_txn (
394 p_rsrc_txn_gtmp_rec => l_resource_transaction_rec_in,
395 x_return_status => x_return_status);
396 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
397 END update_resource_transaction;
398
399 procedure delete_resource_transaction (
400 p_resource_transaction_rec IN fnd_table_of_varchar2_255
401 ,x_return_status OUT NOCOPY VARCHAR2
402 ,x_message_count OUT NOCOPY NUMBER
403 ,x_message_data OUT NOCOPY VARCHAR2
404 )
405 AS
406 l_resource_transaction_rec_in gme_resource_txns_gtmp%rowtype;
407 BEGIN
408 get_resource_transaction_rec(p_resource_transaction_rec, l_resource_transaction_rec_in);
409 /*
410 gme_resource_engine_pvt.delete_resource_trans (
411 p_tran_rec => l_resource_transaction_rec_in,
412 x_return_status => x_return_status);
413 */
414 gme_api_grp.delete_resource_txn (
415 p_rsrc_txn_gtmp_rec => l_resource_transaction_rec_in,
416 x_return_status => x_return_status);
417
418 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
419 END delete_resource_transaction;
420
421 procedure setup_resource_transaction(
422 p_org_id NUMBER,
423 p_org_code VARCHAR2,
424 p_batch_id NUMBER,
425 x_return_status OUT NOCOPY VARCHAR2,
426 x_message_count OUT NOCOPY NUMBER,
427 x_message_data OUT NOCOPY VARCHAR2
428 ) AS
429 l_batch_record gme_batch_header%rowtype;
430 l_rsrc_row_count number;
431 BEGIN
432
433 if (gme_common_pvt.setup(p_org_id => p_org_id, p_org_code => p_org_code)) then
434 x_return_status := 'S';
435 else
436 x_return_status := 'E';
437 end if;
438 select * into l_batch_record from gme_batch_header where batch_id = p_batch_id;
439 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);
440 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
441 END setup_resource_transaction;
442
443 procedure update_process_parameter
444 (
445 p_batch_no IN VARCHAR2
446 ,p_org_code IN VARCHAR2
447 ,p_validate_flexfields IN VARCHAR2
448 ,p_batchstep_no IN NUMBER
449 ,p_activity IN VARCHAR2
450 ,p_parameter IN VARCHAR2
451 ,p_process_param_rec IN fnd_table_of_varchar2_255
452 ,x_process_param_rec OUT NOCOPY fnd_table_of_varchar2_255
453 ,x_return_status OUT NOCOPY VARCHAR2
454 ,x_message_count OUT NOCOPY NUMBER
455 ,x_message_data OUT NOCOPY VARCHAR2
456 ) AS
457
458 l_process_param_rec_in gme_process_parameters%rowtype;
459 l_process_param_rec_out gme_process_parameters%rowtype;
460
461 BEGIN
462
463
464 l_process_param_rec_in.PROCESS_PARAM_ID := p_process_param_rec(1);
465 l_process_param_rec_in.BATCH_ID := p_process_param_rec(2);
466 l_process_param_rec_in.BATCHSTEP_ID := p_process_param_rec(3);
467 l_process_param_rec_in.BATCHSTEP_ACTIVITY_ID := p_process_param_rec(4);
468 l_process_param_rec_in.BATCHSTEP_RESOURCE_ID := p_process_param_rec(5);
469 l_process_param_rec_in.RESOURCES := p_process_param_rec(6);
470 l_process_param_rec_in.PARAMETER_ID := p_process_param_rec(7);
471 l_process_param_rec_in.TARGET_VALUE := p_process_param_rec(8);
472 l_process_param_rec_in.MINIMUM_VALUE := p_process_param_rec(9);
473 l_process_param_rec_in.MAXIMUM_VALUE := p_process_param_rec(10);
474 l_process_param_rec_in.PARAMETER_UOM := p_process_param_rec(11);
475 l_process_param_rec_in.ATTRIBUTE_CATEGORY := p_process_param_rec(12);
476 l_process_param_rec_in.ATTRIBUTE1 := p_process_param_rec(13);
477 l_process_param_rec_in.ATTRIBUTE2 := p_process_param_rec(14);
478 l_process_param_rec_in.ATTRIBUTE3 := p_process_param_rec(15);
479 l_process_param_rec_in.ATTRIBUTE4 := p_process_param_rec(16);
480 l_process_param_rec_in.ATTRIBUTE5 := p_process_param_rec(17);
481 l_process_param_rec_in.ATTRIBUTE6 := p_process_param_rec(18);
482 l_process_param_rec_in.ATTRIBUTE7 := p_process_param_rec(19);
483 l_process_param_rec_in.ATTRIBUTE8 := p_process_param_rec(20);
484 l_process_param_rec_in.ATTRIBUTE9 := p_process_param_rec(21);
485 l_process_param_rec_in.ATTRIBUTE10 := p_process_param_rec(22);
486 l_process_param_rec_in.ATTRIBUTE11 := p_process_param_rec(23);
487 l_process_param_rec_in.ATTRIBUTE12 := p_process_param_rec(24);
488 l_process_param_rec_in.ATTRIBUTE13 := p_process_param_rec(25);
489 l_process_param_rec_in.ATTRIBUTE14 := p_process_param_rec(26);
490 l_process_param_rec_in.ATTRIBUTE15 := p_process_param_rec(27);
491 l_process_param_rec_in.ATTRIBUTE16 := p_process_param_rec(28);
492 l_process_param_rec_in.ATTRIBUTE17 := p_process_param_rec(29);
493 l_process_param_rec_in.ATTRIBUTE18 := p_process_param_rec(30);
494 l_process_param_rec_in.ATTRIBUTE19 := p_process_param_rec(31);
495 l_process_param_rec_in.ATTRIBUTE20 := p_process_param_rec(32);
496 l_process_param_rec_in.ATTRIBUTE21 := p_process_param_rec(33);
497 l_process_param_rec_in.ATTRIBUTE22 := p_process_param_rec(34);
498 l_process_param_rec_in.ATTRIBUTE23 := p_process_param_rec(35);
499 l_process_param_rec_in.ATTRIBUTE24 := p_process_param_rec(36);
500 l_process_param_rec_in.ATTRIBUTE25 := p_process_param_rec(37);
501 l_process_param_rec_in.ATTRIBUTE26 := p_process_param_rec(38);
502 l_process_param_rec_in.ATTRIBUTE27 := p_process_param_rec(39);
503 l_process_param_rec_in.ATTRIBUTE28 := p_process_param_rec(40);
504 l_process_param_rec_in.ATTRIBUTE29 := p_process_param_rec(41);
505 l_process_param_rec_in.ATTRIBUTE30 := p_process_param_rec(42);
506 l_process_param_rec_in.CREATED_BY := p_process_param_rec(43);
507 l_process_param_rec_in.CREATION_DATE := fnd_date.displaydt_to_date(p_process_param_rec(44), FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE);
508 l_process_param_rec_in.LAST_UPDATED_BY := p_process_param_rec(45);
509 l_process_param_rec_in.LAST_UPDATE_LOGIN := p_process_param_rec(46);
510 l_process_param_rec_in.LAST_UPDATE_DATE := fnd_date.displaydt_to_date(p_process_param_rec(47), FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE);
511 l_process_param_rec_in.ACTUAL_VALUE := p_process_param_rec(48);
512 l_process_param_rec_in.DEVICE_ID := p_process_param_rec(49);
513
514 gme_api_pub.update_process_parameter
515 (
516 x_message_count => x_message_count
517 ,x_message_list => x_message_data
518 ,x_return_status => x_return_status
519 ,p_batch_no => p_batch_no
520 ,p_org_code => p_org_code
521 ,p_validate_flexfields => p_validate_flexfields
522 ,p_batchstep_no => p_batchstep_no
523 ,p_activity => p_activity
524 ,p_parameter => p_parameter
525 ,p_process_param_rec => l_process_param_rec_in
526 ,x_process_param_rec => l_process_param_rec_out
527 );
528
529 x_process_param_rec := new fnd_table_of_varchar2_255();
530
531 x_process_param_rec.extend;
532 x_process_param_rec(1) := l_process_param_rec_out.PROCESS_PARAM_ID ;
533 x_process_param_rec.extend;
534 x_process_param_rec(2) := l_process_param_rec_out.BATCH_ID ;
535 x_process_param_rec.extend;
536 x_process_param_rec(3) := l_process_param_rec_out.BATCHSTEP_ID ;
537 x_process_param_rec.extend;
538 x_process_param_rec(4) := l_process_param_rec_out.BATCHSTEP_ACTIVITY_ID ;
539 x_process_param_rec.extend;
540 x_process_param_rec(5) := l_process_param_rec_out.BATCHSTEP_RESOURCE_ID ;
541 x_process_param_rec.extend;
542 x_process_param_rec(6) := l_process_param_rec_out.RESOURCES ;
543 x_process_param_rec.extend;
544 x_process_param_rec(7) := l_process_param_rec_out.PARAMETER_ID ;
545 x_process_param_rec.extend;
546 x_process_param_rec(8) := l_process_param_rec_out.TARGET_VALUE ;
547 x_process_param_rec.extend;
548 x_process_param_rec(9) := l_process_param_rec_out.MINIMUM_VALUE ;
549 x_process_param_rec.extend;
550 x_process_param_rec(10) := l_process_param_rec_out.MAXIMUM_VALUE ;
551 x_process_param_rec.extend;
552 x_process_param_rec(11) := l_process_param_rec_out.PARAMETER_UOM ;
553 x_process_param_rec.extend;
554 x_process_param_rec(12) := l_process_param_rec_out.ATTRIBUTE_CATEGORY ;
555 x_process_param_rec.extend;
556 x_process_param_rec(13) := l_process_param_rec_out.ATTRIBUTE1 ;
557 x_process_param_rec.extend;
558 x_process_param_rec(14) := l_process_param_rec_out.ATTRIBUTE2 ;
559 x_process_param_rec.extend;
560 x_process_param_rec(15) := l_process_param_rec_out.ATTRIBUTE3 ;
561 x_process_param_rec.extend;
562 x_process_param_rec(16) := l_process_param_rec_out.ATTRIBUTE4 ;
563 x_process_param_rec.extend;
564 x_process_param_rec(17) := l_process_param_rec_out.ATTRIBUTE5 ;
565 x_process_param_rec.extend;
566 x_process_param_rec(18) := l_process_param_rec_out.ATTRIBUTE6 ;
567 x_process_param_rec.extend;
568 x_process_param_rec(19) := l_process_param_rec_out.ATTRIBUTE7 ;
569 x_process_param_rec.extend;
570 x_process_param_rec(20) := l_process_param_rec_out.ATTRIBUTE8 ;
571 x_process_param_rec.extend;
572 x_process_param_rec(21) := l_process_param_rec_out.ATTRIBUTE9 ;
573 x_process_param_rec.extend;
574 x_process_param_rec(22) := l_process_param_rec_out.ATTRIBUTE10 ;
575 x_process_param_rec.extend;
576 x_process_param_rec(23) := l_process_param_rec_out.ATTRIBUTE11 ;
577 x_process_param_rec.extend;
578 x_process_param_rec(24) := l_process_param_rec_out.ATTRIBUTE12 ;
579 x_process_param_rec.extend;
580 x_process_param_rec(25) := l_process_param_rec_out.ATTRIBUTE13 ;
581 x_process_param_rec.extend;
582 x_process_param_rec(26) := l_process_param_rec_out.ATTRIBUTE14 ;
583 x_process_param_rec.extend;
584 x_process_param_rec(27) := l_process_param_rec_out.ATTRIBUTE15 ;
585 x_process_param_rec.extend;
586 x_process_param_rec(28) := l_process_param_rec_out.ATTRIBUTE16 ;
587 x_process_param_rec.extend;
588 x_process_param_rec(29) := l_process_param_rec_out.ATTRIBUTE17 ;
589 x_process_param_rec.extend;
590 x_process_param_rec(30) := l_process_param_rec_out.ATTRIBUTE18 ;
591 x_process_param_rec.extend;
592 x_process_param_rec(31) := l_process_param_rec_out.ATTRIBUTE19 ;
593 x_process_param_rec.extend;
594 x_process_param_rec(32) := l_process_param_rec_out.ATTRIBUTE20 ;
595 x_process_param_rec.extend;
596 x_process_param_rec(33) := l_process_param_rec_out.ATTRIBUTE21 ;
597 x_process_param_rec.extend;
598 x_process_param_rec(34) := l_process_param_rec_out.ATTRIBUTE22 ;
599 x_process_param_rec.extend;
600 x_process_param_rec(35) := l_process_param_rec_out.ATTRIBUTE23 ;
601 x_process_param_rec.extend;
602 x_process_param_rec(36) := l_process_param_rec_out.ATTRIBUTE24 ;
603 x_process_param_rec.extend;
604 x_process_param_rec(37) := l_process_param_rec_out.ATTRIBUTE25 ;
605 x_process_param_rec.extend;
606 x_process_param_rec(38) := l_process_param_rec_out.ATTRIBUTE26 ;
607 x_process_param_rec.extend;
608 x_process_param_rec(39) := l_process_param_rec_out.ATTRIBUTE27 ;
609 x_process_param_rec.extend;
610 x_process_param_rec(40) := l_process_param_rec_out.ATTRIBUTE28 ;
611 x_process_param_rec.extend;
612 x_process_param_rec(41) := l_process_param_rec_out.ATTRIBUTE29 ;
613 x_process_param_rec.extend;
614 x_process_param_rec(42) := l_process_param_rec_out.ATTRIBUTE30 ;
615 x_process_param_rec.extend;
616 x_process_param_rec(43) := l_process_param_rec_out.CREATED_BY ;
617 x_process_param_rec.extend;
618 x_process_param_rec(44) := l_process_param_rec_out.CREATION_DATE ;
619 x_process_param_rec.extend;
620 x_process_param_rec(45) := l_process_param_rec_out.LAST_UPDATED_BY ;
621 x_process_param_rec.extend;
622 x_process_param_rec(46) := l_process_param_rec_out.LAST_UPDATE_LOGIN ;
623 x_process_param_rec.extend;
624 x_process_param_rec(47) := l_process_param_rec_out.LAST_UPDATE_DATE ;
625 x_process_param_rec.extend;
626 x_process_param_rec(48) := l_process_param_rec_out.ACTUAL_VALUE ;
627 x_process_param_rec.extend;
628 x_process_param_rec(49) := l_process_param_rec_out.DEVICE_ID ;
629
630
631 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
632
633 END update_process_parameter;
634
635 procedure save_batch (
636 p_table in number
637 ,x_return_status OUT NOCOPY VARCHAR2
638 ,x_message_count OUT NOCOPY NUMBER
639 ,x_message_data OUT NOCOPY VARCHAR2
640 ) is
641 begin
642 gme_api_pub.save_batch (
643 p_header_id => null,
644 p_table => p_table,
645 p_commit => fnd_api.g_false,
646 x_return_status => x_return_status,
647 p_clear_qty_cache =>FND_API.g_true
648 );
649 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
650 end save_batch;
651
652
653 procedure get_material_transactions(
654 p_organization_id IN NUMBER
655 ,p_batch_id IN NUMBER
656 ,p_material_detail_id IN NUMBER
657 ,x_mmt_cur OUT NOCOPY gme_api_grp.g_gmo_txns
658 ,x_return_status OUT NOCOPY VARCHAR2
659 ,x_message_count OUT NOCOPY NUMBER
660 ,x_message_data OUT NOCOPY VARCHAR2
661 )
662 is
663
664 begin
665 /*
666 open x_mmt_cur for
667 SELECT
668 mmt.transaction_id, mmt.subinventory_code, mmt.transaction_quantity, mmt.transaction_uom,
669 mmt.secondary_transaction_quantity, mmt.secondary_uom_code, mtlt.lot_number, mmt.revision,
670 mmt.locator_id, (select concatenated_segments
671 from mtl_item_locations_kfv
672 where organization_id = mmt.organization_id
673 and subinventory_code = mmt.subinventory_code
674 and inventory_location_id=mmt.locator_id) locator_code,
675 mmt.TRANSACTION_TYPE_ID
676 FROM mtl_material_transactions mmt, mtl_transaction_lot_numbers mtlt
677 WHERE mmt.transaction_source_id = p_batch_id
678 AND mmt.trx_source_line_id = p_material_detail_id
679 AND mmt.transaction_source_type_id = 5
680 AND NOT EXISTS (SELECT transaction_id1
681 FROM gme_transaction_pairs
682 WHERE transaction_id1 = mmt.transaction_id
683 AND pair_type = 1)
684 and mtlt.transaction_id (+) = mmt.transaction_id
685 and mmt.organization_id = p_organization_id;
686 x_return_status := 'S';
687
688 */
689 gme_api_grp.get_mat_trans
690 (
691 p_organization_id => p_organization_id
692 ,p_mat_det_id => p_material_detail_id
693 ,p_batch_id => p_batch_id
694 ,x_txns_cur => x_mmt_cur
695 ,x_return_status => x_return_status
696 );
697 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
698
699 end get_material_transactions;
700
701 procedure get_lot_transactions(
702 p_transaction_id IN NUMBER
703 ,x_lt_cur OUT NOCOPY gme_api_grp.g_gmo_lot_txns
704 ,x_return_status OUT NOCOPY VARCHAR2
705 ,x_message_count OUT NOCOPY NUMBER
706 ,x_message_data OUT NOCOPY VARCHAR2
707 )
708 IS
709 begin
710 gme_api_grp.get_lot_trans
711 (
712 p_transaction_id => p_transaction_id
713 ,x_lot_txns_cur => x_lt_cur
714 ,x_return_status => x_return_status
715 );
716 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
717 end get_lot_transactions;
718
719 procedure get_material_reservations(
720 p_organization_id IN NUMBER
721 ,p_batch_id IN NUMBER
722 ,p_material_detail_id IN NUMBER
723 ,x_res_cur OUT NOCOPY gme_api_grp.g_gmo_resvns
724 ,x_return_status OUT NOCOPY VARCHAR2
725 ,x_message_count OUT NOCOPY NUMBER
726 ,x_message_data OUT NOCOPY VARCHAR2
727 )
728 IS
729 begin
730 /*
731 open x_res_cur for
732 SELECT
733 reservation_id, subinventory_code, primary_reservation_quantity, reservation_uom_code,
734 secondary_reservation_quantity, secondary_uom_code, lot_number, revision,
735 locator_id, (select concatenated_segments
736 from mtl_item_locations_kfv
737 where organization_id = mr.organization_id
738 and subinventory_code = mr.subinventory_code
739 and inventory_location_id=mr.locator_id)
740 FROM mtl_reservations mr
741 WHERE organization_id = p_organization_id
742 AND demand_source_type_id = 5
743 AND demand_source_header_id = p_batch_id
744 AND demand_source_line_id = p_material_detail_id
745 AND NOT EXISTS (SELECT 1
746 FROM mtl_material_transactions_temp
747 WHERE reservation_id = mr.reservation_id);
748 x_return_status := 'S';
749 */
750 gme_api_grp.get_mat_resvns
751 (
752 p_organization_id => p_organization_id
753 ,p_mat_det_id => p_material_detail_id
754 ,p_batch_id => p_batch_id
755 ,x_resvns_cur => x_res_cur
756 ,x_return_status => x_return_status
757 );
758
759 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
760
761 end get_material_reservations;
762
763 procedure get_material_pplots(
764 p_organization_id IN NUMBER
765 ,p_batch_id IN NUMBER
766 ,p_material_detail_id IN NUMBER
767 ,x_pplot_cur OUT NOCOPY gme_api_grp.g_gmo_pplots
768 ,x_return_status OUT NOCOPY VARCHAR2
769 ,x_message_count OUT NOCOPY NUMBER
770 ,x_message_data OUT NOCOPY VARCHAR2
771 )
772 IS
773 begin
774 gme_api_grp.get_mat_pplots
775 (
776 p_mat_det_id => p_material_detail_id
777 ,x_pplot_cur => x_pplot_cur
778 ,x_return_status => x_return_status
779 );
780
781 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
782 end get_material_pplots;
783
784
785 procedure convert_um (
786 p_organization_id IN NUMBER
787 ,p_inventory_item_id IN NUMBER
788 ,p_lot_number IN VARCHAR2
789 ,p_from_qty IN NUMBER
790 ,p_from_um IN VARCHAR2
791 ,p_to_um IN VARCHAR2
792 ,x_to_qty OUT NOCOPY NUMBER
793 ,x_return_status OUT NOCOPY VARCHAR2
794 ,x_message_count OUT NOCOPY NUMBER
795 ,x_message_data OUT NOCOPY VARCHAR2
796 )
797 is
798 uom_conversion_err exception;
799 begin
800 x_to_qty := inv_convert.inv_um_convert(
801 p_inventory_item_id,
802 p_lot_number,
803 p_organization_id,
804 5,
805 p_from_qty,
806 p_from_um,
807 p_to_um,
808 null,
809 null
810 );
811 if (x_to_qty = -99999) THEN
812 RAISE uom_conversion_err;
813 END IF;
814
815 x_return_status := fnd_api.g_ret_sts_success;
816 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
817 exception
818 WHEN uom_conversion_err THEN
819 x_return_status:= fnd_api.g_ret_sts_error;
820 FND_MESSAGE.SET_NAME('GMO','GMO_UM_CONVERT_ERR');
821 FND_MESSAGE.SET_TOKEN('FROM_UOM',p_from_um);
822 FND_MESSAGE.SET_TOKEN('TO_UOM',p_to_um);
823 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
824 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_task_pvt.convert_um', FALSE);
825 end if;
826 FND_MSG_PUB.ADD;
827 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
828 WHEN OTHERS THEN
829 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
830 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
831 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
832 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
833 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
834 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.convert_um', FALSE);
835 end if;
836 FND_MSG_PUB.ADD;
837 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
838
839 end convert_um;
840
841 procedure qty_within_deviation (
842 p_organization_id IN NUMBER
843 ,p_inventory_item_id IN NUMBER
844 ,p_lot_number IN NUMBER
845 ,p_qty IN NUMBER
846 ,p_um IN VARCHAR2
847 ,p_sec_qty IN NUMBER
848 ,p_sec_um IN VARCHAR2
849 ,x_return_status OUT NOCOPY VARCHAR2
850 ,x_message_count OUT NOCOPY NUMBER
851 ,x_message_data OUT NOCOPY VARCHAR2
852 )
853 IS
854
855 DEV_LOW_ERROR EXCEPTION;
856 DEV_HIGH_ERROR EXCEPTION;
857 INVALID_ITEM EXCEPTION;
858 INCORRECT_FIXED_VALUE EXCEPTION;
859 INVALID_UOM_CONV EXCEPTION;
860
861 l_is_valid NUMBER(1);
862 l_msg_index_out NUMBER;
863
864 BEGIN
865
866 x_return_status := FND_API.G_RET_STS_SUCCESS;
867
868 -- Validate the quantities within deviation
869 l_is_valid := INV_CONVERT.within_deviation(
870 p_organization_id => p_organization_id
871 , p_inventory_item_id => p_inventory_item_id
872 , p_lot_number => p_lot_number
873 , p_precision => 5
874 , p_quantity => ABS(p_qty)
875 , p_uom_code1 => p_um
876 , p_quantity2 => ABS(p_sec_qty)
877 , p_uom_code2 => p_sec_um);
878 IF (l_is_valid = 0)
879 THEN
880
881 x_return_status := FND_API.G_RET_STS_ERROR;
882
883 FND_MSG_PUB.Get(
884 p_msg_index => 1,
885 p_data => x_message_data,
886 p_encoded => FND_API.G_FALSE,
887 p_msg_index_out => l_msg_index_out);
888 END IF;
889
890 EXCEPTION
891 WHEN INVALID_ITEM THEN
892 x_return_status:= fnd_api.g_ret_sts_error;
893 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ITEM');
894 FND_MSG_PUB.ADD;
895 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
896 WHEN INCORRECT_FIXED_VALUE THEN
897 x_return_status:= fnd_api.g_ret_sts_error;
898 FND_MESSAGE.SET_NAME('INV','INV_INCORRECT_FIXED_VALUE');
899 FND_MSG_PUB.ADD;
900 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
901 WHEN INVALID_UOM_CONV THEN
902 x_return_status:= fnd_api.g_ret_sts_error;
903 FND_MESSAGE.SET_NAME('INV','INV_INVALID_UOM_CONV');
904 FND_MESSAGE.SET_TOKEN ('VALUE1',p_um);
905 FND_MESSAGE.SET_TOKEN ('VALUE2',p_sec_um);
906 FND_MSG_PUB.ADD;
907 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
908 WHEN DEV_LOW_ERROR THEN
909 x_return_status:= fnd_api.g_ret_sts_error;
910 FND_MESSAGE.SET_NAME('INV','INV_DEVIATION_LO_ERR');
911 FND_MSG_PUB.ADD;
912 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
913 WHEN DEV_HIGH_ERROR THEN
914 x_return_status:= fnd_api.g_ret_sts_error;
915 FND_MESSAGE.SET_NAME('INV','INV_DEVIATION_HI_ERR');
916 FND_MSG_PUB.ADD;
917 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
918 WHEN OTHERS THEN
919 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
920 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
921 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
922 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
923 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
924 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.qty_within_deviation', FALSE);
925 end if;
926 FND_MSG_PUB.ADD;
927 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
928 END qty_within_deviation;
929
930 procedure get_dispense_um(
931 p_material_detail_id IN NUMBER
932 ,x_dispense_um OUT NOCOPY VARCHAR2
933 ,x_return_status OUT NOCOPY VARCHAR2
934 ,x_message_count OUT NOCOPY NUMBER
935 ,x_message_data OUT NOCOPY VARCHAR2
936 )
937
938 is
939 l_instr_def_key VARCHAR2(40);
940 l_dispense_config_row GMO_DISPENSE_CONFIG%ROWTYPE;
941 DISP_NOT_REQ_EXCEPTION exception;
942 begin
943 GMO_DISPENSE_SETUP_PVT.GET_DISPENSE_CONFIG_INST(P_ENTITY_NAME=> GMO_DISPENSE_GRP.G_MATERIAL_LINE_ENTITY,
944 P_ENTITY_KEY=> p_material_detail_id ,
945 X_DISPENSE_CONFIG => l_dispense_config_row,
946 X_INSTRUCTION_DEFINITION_KEY => l_instr_def_key);
947 if(l_dispense_config_row.config_id is null) then
948 RAISE DISP_NOT_REQ_EXCEPTION;
949 end if;
950 x_dispense_um := l_dispense_config_row.dispense_uom;
951 x_return_status := fnd_api.g_ret_sts_success;
952 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
953 exception
954 when DISP_NOT_REQ_EXCEPTION then
955 x_return_status:= fnd_api.g_ret_sts_error;
956 FND_MESSAGE.SET_NAME('GMO','GMO_DISP_DISPENSE_NOT_REQ_ERR');
957 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
958 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_task_pvt.get_dispense_um', FALSE);
959 end if;
960 FND_MSG_PUB.ADD;
961 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
962 WHEN OTHERS THEN
963 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
964 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
965 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
966 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
967 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
968 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.get_dispense_um', FALSE);
969 end if;
970 FND_MSG_PUB.ADD;
971 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
972
973 end get_dispense_um;
974
975 procedure relieve_reservation(
976 p_reservation_id IN NUMBER
977 ,p_prim_relieve_quantity IN NUMBER
978 ,x_return_status OUT NOCOPY VARCHAR2
979 ,x_message_count OUT NOCOPY NUMBER
980 ,x_message_data OUT NOCOPY VARCHAR2
981 )
982 IS
983
984 BEGIN
985
986 gme_reservations_pvt.relieve_reservation (
987 p_reservation_id => p_reservation_id
988 ,p_prim_relieve_qty => p_prim_relieve_quantity
989 ,x_return_status => x_return_status
990 );
991
992 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
993
994 EXCEPTION
995 WHEN OTHERS THEN
996 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
997 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
998 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
999 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1000 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1001 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.get_resource_txn_end_date', FALSE);
1002 end if;
1003 FND_MSG_PUB.ADD;
1004 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1005 END relieve_reservation;
1006
1007 procedure relieve_pending_lot(
1008 p_pending_lot_id IN NUMBER
1009 ,p_quantity IN NUMBER
1010 ,p_secondary_quantity IN NUMBER
1011 ,x_return_status OUT NOCOPY VARCHAR2
1012 ,x_message_count OUT NOCOPY NUMBER
1013 ,x_message_data OUT NOCOPY VARCHAR2
1014 )
1015 IS
1016 BEGIN
1017
1018 gme_pending_product_lots_pvt.relieve_pending_lot (
1019 p_pending_lot_id => p_pending_lot_id
1020 ,p_quantity => p_quantity
1021 ,p_secondary_quantity => p_secondary_quantity
1022 ,x_return_status => x_return_status
1023 );
1024
1025 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1026
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1030 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
1031 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1032 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1033 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1034 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.relieve_pending_lot', FALSE);
1035 end if;
1036 FND_MSG_PUB.ADD;
1037 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1038 END relieve_pending_lot;
1039
1040
1041 procedure create_material_transaction(
1042 p_mtl_txn_rec IN fnd_table_of_varchar2_255
1043 ,p_mtl_lot_rec IN fnd_table_of_varchar2_255
1044 ,x_return_status OUT NOCOPY VARCHAR2
1045 ,x_message_count OUT NOCOPY NUMBER
1046 ,x_message_data OUT NOCOPY VARCHAR2
1047 )
1048 IS
1049
1050 l_mtl_txn_rec mtl_transactions_interface%ROWTYPE;
1051 l_mtl_lot_rec mtl_transaction_lots_interface%ROWTYPE;
1052 l_mtl_lot_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1053
1054 BEGIN
1055 l_mtl_txn_rec.TRANSACTION_INTERFACE_ID := p_mtl_txn_rec(1);
1056 l_mtl_txn_rec.TRANSACTION_TYPE_ID := p_mtl_txn_rec(2);
1057 l_mtl_txn_rec.REVISION := p_mtl_txn_rec(3);
1058 l_mtl_txn_rec.TRANSACTION_UOM := p_mtl_txn_rec(4);
1059 l_mtl_txn_rec.TRANSACTION_DATE := fnd_date.displaydt_to_date(p_mtl_txn_rec(5), FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE);
1060 l_mtl_txn_rec.SUBINVENTORY_CODE := p_mtl_txn_rec(6);
1061 l_mtl_txn_rec.SECONDARY_UOM_CODE := p_mtl_txn_rec(7);
1062 l_mtl_txn_rec.SECONDARY_TRANSACTION_QUANTITY := p_mtl_txn_rec(8);
1063 l_mtl_txn_rec.PRIMARY_QUANTITY := p_mtl_txn_rec(9);
1064 l_mtl_txn_rec.TRANSACTION_QUANTITY := p_mtl_txn_rec(10);
1065 l_mtl_txn_rec.ORGANIZATION_ID := p_mtl_txn_rec(11);
1066 l_mtl_txn_rec.REASON_ID := p_mtl_txn_rec(12);
1067 l_mtl_txn_rec.TRANSACTION_ACTION_ID := p_mtl_txn_rec(13);
1068 l_mtl_txn_rec.TRANSACTION_SOURCE_NAME := p_mtl_txn_rec(14);
1069 l_mtl_txn_rec.TRX_SOURCE_LINE_ID := p_mtl_txn_rec(15);
1070 l_mtl_txn_rec.LOCATOR_ID := p_mtl_txn_rec(16);
1071 l_mtl_txn_rec.INVENTORY_ITEM_ID := p_mtl_txn_rec(17);
1072 l_mtl_txn_rec.TRANSACTION_REFERENCE := p_mtl_txn_rec(18);
1073 l_mtl_txn_rec.TRANSACTION_SOURCE_ID := p_mtl_txn_rec(19);
1074
1075 if(p_mtl_lot_rec IS NOT NULL and p_mtl_lot_rec.count > 0) THEN
1076 l_mtl_lot_rec.TRANSACTION_QUANTITY := p_mtl_lot_rec(1);
1077 l_mtl_lot_rec.TRANSACTION_INTERFACE_ID := p_mtl_lot_rec(2);
1078 l_mtl_lot_rec.SECONDARY_TRANSACTION_QUANTITY := p_mtl_lot_rec(3);
1079 l_mtl_lot_rec.PRIMARY_QUANTITY := p_mtl_lot_rec(4);
1080 l_mtl_lot_rec.LOT_NUMBER := p_mtl_lot_rec(5);
1081 l_mtl_lot_tbl(1) := l_mtl_lot_rec;
1082 end if;
1083
1084 gme_api_grp.create_material_txn(
1085 p_mmti_rec => l_mtl_txn_rec,
1086 p_mmli_tbl => l_mtl_lot_tbl,
1087 x_return_status => x_return_status
1088 );
1089
1090 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1091
1092 EXCEPTION
1093 WHEN OTHERS THEN
1094 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1095 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
1096 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1097 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1098 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1099 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.create_material_transaction', FALSE);
1100 end if;
1101 FND_MSG_PUB.ADD;
1102 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1103 END create_material_transaction;
1104
1105
1106
1107 procedure update_material_transaction(
1108 p_mtl_txn_rec IN fnd_table_of_varchar2_255
1109 ,p_mtl_lot_rec IN fnd_table_of_varchar2_255
1110 ,x_return_status OUT NOCOPY VARCHAR2
1111 ,x_message_count OUT NOCOPY NUMBER
1112 ,x_message_data OUT NOCOPY VARCHAR2
1113 )
1114 IS
1115
1116 l_mtl_txn_rec mtl_transactions_interface%ROWTYPE;
1117 l_mtl_lot_rec mtl_transaction_lots_interface%ROWTYPE;
1118 l_mtl_lot_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1119
1120 BEGIN
1121
1122 l_mtl_txn_rec.TRANSACTION_INTERFACE_ID := p_mtl_txn_rec(1);
1123 l_mtl_txn_rec.TRANSACTION_TYPE_ID := p_mtl_txn_rec(2);
1124 l_mtl_txn_rec.REVISION := p_mtl_txn_rec(3);
1125 l_mtl_txn_rec.TRANSACTION_UOM := p_mtl_txn_rec(4);
1126 l_mtl_txn_rec.TRANSACTION_DATE := fnd_date.displaydt_to_date(p_mtl_txn_rec(5), FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE);
1127 l_mtl_txn_rec.SUBINVENTORY_CODE := p_mtl_txn_rec(6);
1128 l_mtl_txn_rec.SECONDARY_UOM_CODE := p_mtl_txn_rec(7);
1129 l_mtl_txn_rec.SECONDARY_TRANSACTION_QUANTITY := p_mtl_txn_rec(8);
1130 l_mtl_txn_rec.PRIMARY_QUANTITY := p_mtl_txn_rec(9);
1131 l_mtl_txn_rec.TRANSACTION_QUANTITY := p_mtl_txn_rec(10);
1132 l_mtl_txn_rec.ORGANIZATION_ID := p_mtl_txn_rec(11);
1133 l_mtl_txn_rec.REASON_ID := p_mtl_txn_rec(12);
1134 l_mtl_txn_rec.TRANSACTION_ACTION_ID := p_mtl_txn_rec(13);
1135 l_mtl_txn_rec.TRANSACTION_SOURCE_NAME := p_mtl_txn_rec(14);
1136 l_mtl_txn_rec.TRX_SOURCE_LINE_ID := p_mtl_txn_rec(15);
1137 l_mtl_txn_rec.LOCATOR_ID := p_mtl_txn_rec(16);
1138 l_mtl_txn_rec.INVENTORY_ITEM_ID := p_mtl_txn_rec(17);
1139 l_mtl_txn_rec.TRANSACTION_REFERENCE := p_mtl_txn_rec(18);
1140 l_mtl_txn_rec.TRANSACTION_SOURCE_ID := p_mtl_txn_rec(19);
1141
1142 if(p_mtl_lot_rec IS NOT NULL and p_mtl_lot_rec.count > 0) THEN
1143 l_mtl_lot_rec.TRANSACTION_QUANTITY := p_mtl_lot_rec(1);
1144 l_mtl_lot_rec.TRANSACTION_INTERFACE_ID := p_mtl_lot_rec(2);
1145 l_mtl_lot_rec.SECONDARY_TRANSACTION_QUANTITY := p_mtl_lot_rec(3);
1146 l_mtl_lot_rec.PRIMARY_QUANTITY := p_mtl_lot_rec(4);
1147 l_mtl_lot_rec.LOT_NUMBER := p_mtl_lot_rec(5);
1148 l_mtl_lot_tbl(1) := l_mtl_lot_rec;
1149 end if;
1150
1151 gme_api_grp.update_material_txn(
1152 p_transaction_id => l_mtl_txn_rec.TRANSACTION_INTERFACE_ID,
1153 p_mmti_rec => l_mtl_txn_rec,
1154 p_mmli_tbl => l_mtl_lot_tbl,
1155 x_return_status => x_return_status
1156 );
1157
1158 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1159
1160
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1164 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
1165 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1166 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1167 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1168 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.update_material_transaction', FALSE);
1169 end if;
1170 FND_MSG_PUB.ADD;
1171 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1172 END update_material_transaction;
1173
1174
1175 procedure delete_material_transaction(
1176 p_mtl_txn_rec IN fnd_table_of_varchar2_255
1177 ,p_mtl_lot_rec IN fnd_table_of_varchar2_255
1178 ,x_return_status OUT NOCOPY VARCHAR2
1179 ,x_message_count OUT NOCOPY NUMBER
1180 ,x_message_data OUT NOCOPY VARCHAR2
1181 )
1182 IS
1183
1184 l_mtl_txn_rec mtl_transactions_interface%ROWTYPE;
1185 l_mtl_lot_rec mtl_transaction_lots_interface%ROWTYPE;
1186 l_mtl_lot_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1187
1188 BEGIN
1189
1190 l_mtl_txn_rec.TRANSACTION_INTERFACE_ID := p_mtl_txn_rec(1);
1191 l_mtl_txn_rec.TRANSACTION_TYPE_ID := p_mtl_txn_rec(2);
1192 l_mtl_txn_rec.REVISION := p_mtl_txn_rec(3);
1193 l_mtl_txn_rec.TRANSACTION_UOM := p_mtl_txn_rec(4);
1194 l_mtl_txn_rec.TRANSACTION_DATE := fnd_date.displaydt_to_date(p_mtl_txn_rec(5), FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE);
1195 l_mtl_txn_rec.SUBINVENTORY_CODE := p_mtl_txn_rec(6);
1196 l_mtl_txn_rec.SECONDARY_UOM_CODE := p_mtl_txn_rec(7);
1197 l_mtl_txn_rec.SECONDARY_TRANSACTION_QUANTITY := p_mtl_txn_rec(8);
1198 l_mtl_txn_rec.PRIMARY_QUANTITY := p_mtl_txn_rec(9);
1199 l_mtl_txn_rec.TRANSACTION_QUANTITY := p_mtl_txn_rec(10);
1200 l_mtl_txn_rec.ORGANIZATION_ID := p_mtl_txn_rec(11);
1201 l_mtl_txn_rec.REASON_ID := p_mtl_txn_rec(12);
1202 l_mtl_txn_rec.TRANSACTION_ACTION_ID := p_mtl_txn_rec(13);
1203 l_mtl_txn_rec.TRANSACTION_SOURCE_NAME := p_mtl_txn_rec(14);
1204 l_mtl_txn_rec.TRX_SOURCE_LINE_ID := p_mtl_txn_rec(15);
1205 l_mtl_txn_rec.LOCATOR_ID := p_mtl_txn_rec(16);
1206 l_mtl_txn_rec.INVENTORY_ITEM_ID := p_mtl_txn_rec(17);
1207 l_mtl_txn_rec.TRANSACTION_REFERENCE := p_mtl_txn_rec(18);
1208 l_mtl_txn_rec.TRANSACTION_SOURCE_ID := p_mtl_txn_rec(19);
1209 if(p_mtl_lot_rec IS NOT NULL and p_mtl_lot_rec.count > 0) THEN
1210 l_mtl_lot_rec.TRANSACTION_QUANTITY := p_mtl_lot_rec(1);
1211 l_mtl_lot_rec.TRANSACTION_INTERFACE_ID := p_mtl_lot_rec(2);
1212 l_mtl_lot_rec.SECONDARY_TRANSACTION_QUANTITY := p_mtl_lot_rec(3);
1213 l_mtl_lot_rec.PRIMARY_QUANTITY := p_mtl_lot_rec(4);
1214 l_mtl_lot_rec.LOT_NUMBER := p_mtl_lot_rec(5);
1215
1216 l_mtl_lot_tbl(1) := l_mtl_lot_rec;
1217 end if;
1218 gme_api_grp.delete_material_txn(
1219 p_organization_id => l_mtl_txn_rec.ORGANIZATION_ID,
1220 p_transaction_id => l_mtl_txn_rec.TRANSACTION_INTERFACE_ID,
1221 x_return_status => x_return_status
1222 );
1223
1224 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1225
1226 EXCEPTION
1227 WHEN OTHERS THEN
1228 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1229 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
1230 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1231 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1232 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1233 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.delete_material_transaction', FALSE);
1234 end if;
1235 FND_MSG_PUB.ADD;
1236 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1237 END delete_material_transaction;
1238
1239 procedure create_lot(
1240 p_lot_rec IN fnd_table_of_varchar2_255
1241 ,x_return_status OUT NOCOPY VARCHAR2
1242 ,x_message_count OUT NOCOPY NUMBER
1243 ,x_message_data OUT NOCOPY VARCHAR2
1244 )
1245 IS
1246 l_lot_rec mtl_lot_numbers%ROWTYPE;
1247 l_char_tbl inv_lot_api_pub.char_tbl;
1248 l_number_tbl inv_lot_api_pub.number_tbl;
1249 l_date_tbl inv_lot_api_pub.date_tbl;
1250 BEGIN
1251 l_lot_rec.INVENTORY_ITEM_ID := p_lot_rec(1);
1252 l_lot_rec.ORGANIZATION_ID := p_lot_rec(2);
1253 l_lot_rec.LOT_NUMBER := p_lot_rec(3);
1254 if (p_lot_rec(4) is not null) then
1255 l_lot_rec.EXPIRATION_DATE := fnd_date.displaydt_to_date(p_lot_rec(4), FND_TIMEZONES.GET_CLIENT_TIMEZONE_CODE);
1256 end if;
1257 l_lot_rec.LAST_UPDATE_DATE := sysdate;
1258 l_lot_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1259 l_lot_rec.CREATION_DATE := sysdate;
1260 l_lot_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1261 l_lot_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1262 /*
1263 inv_lot_api_pub.create_inv_lot
1264 (
1265 p_lot_rec => l_lot_rec
1266 ,p_source => NULL
1267 ,p_api_version => 1.0
1268 ,p_init_msg_list => fnd_api.g_true
1269 ,p_commit => fnd_api.g_false
1270 ,p_validation_level => fnd_api.g_valid_level_full
1271 ,p_origin_txn_id => 1
1272 );
1273 */
1274 inv_lot_api_pub.create_inv_lot(
1275 x_return_status => x_return_status
1276 ,x_msg_count => x_message_count
1277 ,x_msg_data => x_message_data
1278 , p_inventory_item_id => l_lot_rec.INVENTORY_ITEM_ID
1279 , p_organization_id => l_lot_rec.ORGANIZATION_ID
1280 , p_lot_number => l_lot_rec.LOT_NUMBER
1281 , p_expiration_date => l_lot_rec.EXPIRATION_DATE
1282 , p_disable_flag => NULL
1283 , p_attribute_category => NULL
1284 , p_lot_attribute_category => NULL
1285 , p_attributes_tbl => l_char_tbl
1286 , p_c_attributes_tbl => l_char_tbl
1287 , p_n_attributes_tbl => l_number_tbl
1288 , p_d_attributes_tbl => l_date_tbl
1289 , p_grade_code => NULL
1290 , p_origination_date => NULL
1291 , p_date_code => NULL
1292 , p_status_id => NULL
1293 , p_change_date => NULL
1294 , p_age => NULL
1295 , p_retest_date => NULL
1296 , p_maturity_date => NULL
1297 , p_item_size => NULL
1298 , p_color => NULL
1299 , p_volume => NULL
1300 , p_volume_uom => NULL
1301 , p_place_of_origin => NULL
1302 , p_best_by_date => NULL
1303 , p_length => NULL
1304 , p_length_uom => NULL
1305 , p_recycled_content => NULL
1306 , p_thickness => NULL
1307 , p_thickness_uom => NULL
1308 , p_width => NULL
1309 , p_width_uom => NULL
1310 , p_territory_code => NULL
1311 , p_supplier_lot_number => NULL
1312 , p_vendor_name => NULL
1313 , p_source => NULL
1314 ) ;
1315 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1316 EXCEPTION
1317 WHEN OTHERS THEN
1318 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1319 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
1320 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1321 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1322 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1323 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.create_lot', FALSE);
1324 end if;
1325 FND_MSG_PUB.ADD;
1326 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1327 END create_lot;
1328
1329 procedure generate_lot(
1330 p_organization_id IN NUMBER
1331 ,p_inventory_item_id IN NUMBER
1332 ,x_lot_number OUT NOCOPY VARCHAR2
1333 ,x_return_status OUT NOCOPY VARCHAR2
1334 ,x_message_count OUT NOCOPY NUMBER
1335 ,x_message_data OUT NOCOPY VARCHAR2
1336 )
1337 IS
1338 BEGIN
1339 x_lot_number := INV_LOT_API_PUB.auto_gen_lot
1340 (
1341 p_org_id => p_organization_id,
1342 p_inventory_item_id => p_inventory_item_id,
1343 p_lot_generation => NULL,
1344 p_lot_uniqueness => NULL,
1345 p_lot_prefix => NULL,
1346 p_zero_pad => NULL,
1347 p_lot_length => NULL,
1348 p_transaction_date => NULL,
1349 p_revision => NULL,
1350 p_subinventory_code => NULL,
1351 p_locator_id => NULL,
1352 p_transaction_type_id => NULL,
1353 p_transaction_action_id => NULL,
1354 p_transaction_source_type_id => NULL,
1355 p_lot_number => NULL,
1356 p_api_version => 1.0,
1357 p_init_msg_list => FND_API.G_FALSE,
1358 p_commit => FND_API.G_FALSE,
1359 p_validation_level => NULL,
1360 p_parent_lot_number => null,
1361 x_return_status => x_return_status,
1362 x_msg_count => x_message_count,
1363 x_msg_data => x_message_data
1364 );
1365
1366 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1370 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
1371 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1372 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
1373 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1374 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_task_pvt.generate_lot', FALSE);
1375 end if;
1376 FND_MSG_PUB.ADD;
1377 FND_MSG_PUB.Count_And_Get (p_count => x_message_count, p_data => x_message_data);
1378 END generate_lot;
1379
1380 procedure get_lot_event_key (
1381 p_organization_id IN NUMBER
1382 ,p_inventory_item_id IN NUMBER
1383 ,p_lot_number IN VARCHAR2
1384 ,x_lot_event_key OUT NOCOPY VARCHAR2
1385 )
1386 IS
1387 BEGIN
1388
1389 select gen_object_id into x_lot_event_key from mtl_lot_numbers
1390 where organization_id = p_organization_id
1391 and inventory_item_id = p_inventory_item_id
1392 and lot_number = p_lot_number;
1393
1394 END get_lot_event_key;
1395
1396 end GMO_VBATCH_TASK_PVT;