DBA Data[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;