[Home] [Help]
PACKAGE BODY: APPS.GMD_FETCH_OPRN
Source
1 PACKAGE BODY GMD_FETCH_OPRN AS
2 /* $Header: GMDPOPNB.pls 120.2 2011/06/23 14:38:49 rnalla ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'gmd_fetch_oprn';
5 PROCEDURE fetch_oprn
6 ( p_api_version IN NUMBER ,
7 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
8 p_oprn_id IN NUMBER ,
9 p_orgn_code IN VARCHAR2 ,
10 x_return_status OUT NOCOPY VARCHAR2 ,
11 x_msg_count OUT NOCOPY NUMBER ,
15 X_oprn_resc_rec OUT NOCOPY gmd_recipe_fetch_pub.oprn_resc_tbl,
12 x_msg_data OUT NOCOPY VARCHAR2 ,
13 x_return_code OUT NOCOPY NUMBER ,
14 X_oprn_act_out OUT NOCOPY gmd_recipe_fetch_pub.oprn_act_tbl,
16 X_oprn_resc_proc_param_tbl OUT NOCOPY gmd_recipe_fetch_pub.recp_resc_proc_param_tbl
17 ) IS
18
19 CURSOR Cur_validate_record IS
20 SELECT 1
21 FROM gmd_operations_vl
22 WHERE oprn_id = p_oprn_id;
23 X_count NUMBER;
24 INVALID_OPERATION EXCEPTION;
25 -- Bug #2415756 (JKB) Added cursor above.
26
27 /* local Variables */
28 l_api_name VARCHAR2(30) := 'fetch_oprn';
29 l_api_version NUMBER := 1.0;
30 i NUMBER := 0;
31 BEGIN
32 x_return_status := FND_API.G_RET_STS_SUCCESS;
33
34 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
35 l_api_name, G_PKG_NAME) THEN
36 RAISE FND_API.G_EXC_ERROR;
37 END IF;
38
39 IF FND_API.to_Boolean(p_init_msg_list) THEN
40 FND_MSG_PUB.initialize;
41 END IF;
42
43 OPEN Cur_validate_record;
44 FETCH Cur_validate_record INTO X_count;
45 IF (Cur_validate_record%NOTFOUND) THEN
46 CLOSE Cur_validate_record;
47 RAISE INVALID_OPERATION;
48 END IF;
49 CLOSE Cur_validate_record;
50 -- Bug #2415756 (JKB) Added validation above.
51
52
53 gmd_fetch_oprn.get_oprn_act(p_api_version => p_api_version,
54 p_init_msg_list => FND_API.G_FALSE,
55 p_oprn_id => p_oprn_id,
56 x_return_status => x_return_status,
57 x_msg_count => x_msg_count,
58 x_msg_data => x_msg_data,
59 x_return_code => x_return_code,
60 x_oprn_act_out => x_oprn_act_out);
61
62 IF X_return_status = FND_API.g_ret_sts_success THEN
63 gmd_fetch_oprn.get_oprn_resc(p_api_version => p_api_version,
64 p_init_msg_list => FND_API.G_FALSE,
65 p_oprn_id => p_oprn_id,
66 p_orgn_code => p_orgn_code,
67 x_return_status => x_return_status,
68 x_msg_count => x_msg_count,
69 x_msg_data => x_msg_data,
70 x_return_code => x_return_code,
71 x_oprn_resc_rec => x_oprn_resc_rec,
72 X_oprn_resc_proc_param_tbl => X_oprn_resc_proc_param_tbl);
73 END IF;
74 /*standard call to get msge cnt, and if cnt is 1, get mesg info*/
75 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
76
77 EXCEPTION
78 WHEN FND_API.G_EXC_ERROR THEN
79 X_return_code := SQLCODE;
80 x_return_status := FND_API.G_RET_STS_ERROR;
81 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
82
83 WHEN INVALID_OPERATION THEN
84 x_return_status := FND_API.G_RET_STS_ERROR;
85 FND_MESSAGE.SET_NAME('GMD', 'FM_INVOPRN');
86 FND_MSG_PUB.ADD;
87 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
88 WHEN OTHERS THEN
89 X_return_code := SQLCODE;
90 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
91 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
92 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
93 END fetch_oprn;
94
95
96 PROCEDURE get_oprn_act
97 ( p_api_version IN NUMBER ,
98 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
99 p_oprn_id IN NUMBER ,
100 x_return_status OUT NOCOPY VARCHAR2 ,
101 x_msg_count OUT NOCOPY NUMBER ,
102 x_msg_data OUT NOCOPY VARCHAR2 ,
103 x_return_code OUT NOCOPY NUMBER ,
104 x_oprn_act_out OUT NOCOPY gmd_recipe_fetch_pub.oprn_act_tbl
105 ) IS
106 /* local Variables */
107 l_api_name VARCHAR2(30) := 'get_oprn_act';
108 l_api_version NUMBER := 1.0;
109 i NUMBER := 0;
110
111 CURSOR get_oprn_act1 IS
112 select o.oprn_no, o.oprn_desc, o.oprn_vers,o.oprn_id,
113 a.activity,fm.activity_desc, a.oprn_line_id, a.activity_factor,a.offset_interval,
114 a.sequence_dependent_ind, a.break_ind, a.max_break, a.text_code, a.creation_date,
115 o.minimum_transfer_qty, a.material_ind, a.created_by,a.last_updated_by,
116 a.last_update_date, a.last_update_login, a.attribute_category,
117 a.attribute1, a.attribute2, a.attribute3, a.attribute4,
118 a.attribute5, a.attribute6, a.attribute7, a.attribute8,
119 a.attribute9, a.attribute10, a.attribute11, a.attribute12,
120 a.attribute13, a.attribute14, a.attribute15, a.attribute16,
121 a.attribute17, a.attribute18, a.attribute19, a.attribute20,
122 a.attribute21, a.attribute22, a.attribute23, a.attribute24,
123 a.attribute25, a.attribute26, a.attribute27, a.attribute28,
124 a.attribute29, a.attribute30
125 from gmd_operations_vl o, gmd_operation_activities a, fm_actv_mst fm
126 where a.oprn_id = p_oprn_id
127 and a.oprn_id = o.oprn_id
128 and a.activity = fm.activity
129 --Added the order by for the bug 12613037.
130 ORDER BY a.sequence_dependent_ind, a.oprn_line_id;
131
132 begin
133
137 END IF;
134 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
135 l_api_name, G_PKG_NAME) THEN
136 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
138 IF FND_API.to_Boolean(p_init_msg_list) THEN
139 FND_MSG_PUB.initialize;
140 END IF;
141
142 For get_rec IN get_oprn_act1 LOOP
143 x_return_status := FND_API.G_RET_STS_SUCCESS;
144
145 i := i + 1;
146
147 x_oprn_act_out(i).oprn_no := get_rec.oprn_no ;
148 x_oprn_act_out(i).oprn_desc := get_rec.oprn_desc ;
149 x_oprn_act_out(i).oprn_vers := get_rec.oprn_vers ;
150 x_oprn_act_out(i).oprn_id := get_rec.oprn_id ;
151 x_oprn_act_out(i).activity := get_rec.activity ;
152 x_oprn_act_out(i).activity_desc := get_rec.activity_desc ;
153 x_oprn_act_out(i).oprn_line_id := get_rec.oprn_line_id ;
154 x_oprn_act_out(i).activity_factor := get_rec.activity_factor ;
155 x_oprn_act_out(i).sequence_dependent_ind := get_rec.sequence_dependent_ind ;
156 x_oprn_act_out(i).offset_interval := get_rec.offset_interval ;
157 x_oprn_act_out(i).break_ind := get_rec.break_ind ;
158 x_oprn_act_out(i).max_break := get_rec.max_break ;
159 x_oprn_act_out(i).minimum_transfer_qty := get_rec.minimum_transfer_qty ;
160 x_oprn_act_out(i).material_ind := get_rec.material_ind ;
161 x_oprn_act_out(i).text_code := get_rec.text_code ;
162 x_oprn_act_out(i).creation_date := get_rec.creation_date ;
163 x_oprn_act_out(i).created_by := get_rec.created_by ;
164 x_oprn_act_out(i).last_updated_by := get_rec.last_updated_by ;
165 x_oprn_act_out(i).last_update_date := get_rec.last_update_date ;
166 x_oprn_act_out(i).last_update_login := get_rec.last_update_login ;
167 x_oprn_act_out(i).attribute_category := get_rec.attribute_category ;
168 x_oprn_act_out(i).attribute1 := get_rec.attribute1 ;
169 x_oprn_act_out(i).attribute2 := get_rec.attribute2 ;
170 x_oprn_act_out(i).attribute3 := get_rec.attribute3 ;
171 x_oprn_act_out(i).attribute4 := get_rec.attribute4 ;
172 x_oprn_act_out(i).attribute5 := get_rec.attribute5 ;
173 x_oprn_act_out(i).attribute6 := get_rec.attribute6 ;
174 x_oprn_act_out(i).attribute7 := get_rec.attribute7 ;
175 x_oprn_act_out(i).attribute8 := get_rec.attribute8 ;
176 x_oprn_act_out(i).attribute9 := get_rec.attribute9 ;
177 x_oprn_act_out(i).attribute10 := get_rec.attribute10 ;
178 x_oprn_act_out(i).attribute11 := get_rec.attribute11 ;
179 x_oprn_act_out(i).attribute12 := get_rec.attribute12 ;
180 x_oprn_act_out(i).attribute13 := get_rec.attribute13 ;
181 x_oprn_act_out(i).attribute14 := get_rec.attribute14 ;
182 x_oprn_act_out(i).attribute15 := get_rec.attribute15 ;
186 x_oprn_act_out(i).attribute19 := get_rec.attribute19 ;
183 x_oprn_act_out(i).attribute16 := get_rec.attribute16 ;
184 x_oprn_act_out(i).attribute17 := get_rec.attribute17 ;
185 x_oprn_act_out(i).attribute18 := get_rec.attribute18 ;
187 x_oprn_act_out(i).attribute20 := get_rec.attribute20 ;
188 x_oprn_act_out(i).attribute21 := get_rec.attribute21 ;
189 x_oprn_act_out(i).attribute22 := get_rec.attribute22 ;
190 x_oprn_act_out(i).attribute23 := get_rec.attribute23 ;
191 x_oprn_act_out(i).attribute24 := get_rec.attribute24 ;
192 x_oprn_act_out(i).attribute25 := get_rec.attribute25 ;
193 x_oprn_act_out(i).attribute26 := get_rec.attribute26 ;
194 x_oprn_act_out(i).attribute27 := get_rec.attribute27 ;
195 x_oprn_act_out(i).attribute28 := get_rec.attribute28 ;
196 x_oprn_act_out(i).attribute29 := get_rec.attribute29 ;
197 x_oprn_act_out(i).attribute30 := get_rec.attribute30 ;
198
199 END LOOP;
200
201 IF i = 0 THEN
202 RAISE fnd_api.g_exc_error;
203 END IF;
204
205
206
207 /*standard call to get msge cnt, and if cnt is 1, get mesg info*/
208 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
209
210 EXCEPTION
211 WHEN FND_API.G_EXC_ERROR THEN
212 X_return_code := SQLCODE;
213 x_return_status := FND_API.G_RET_STS_ERROR;
214 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
215
216 WHEN OTHERS THEN
217 X_return_code := SQLCODE;
218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
219 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
220 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
221
222 END get_oprn_act;
223
224
225
226
227 PROCEDURE get_oprn_resc
228 ( p_api_version IN NUMBER ,
229 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
230 p_oprn_id IN NUMBER ,
231 p_orgn_code IN VARCHAR2 ,
232 x_return_status OUT NOCOPY VARCHAR2 ,
233 x_msg_count OUT NOCOPY NUMBER ,
234 x_msg_data OUT NOCOPY VARCHAR2 ,
235 x_return_code OUT NOCOPY NUMBER ,
236 X_oprn_resc_rec OUT NOCOPY gmd_recipe_fetch_pub.oprn_resc_tbl,
237 X_oprn_resc_proc_param_tbl OUT NOCOPY gmd_recipe_fetch_pub.recp_resc_proc_param_tbl
238 ) IS
239
240 /* local Variables */
241 l_api_name CONSTANT VARCHAR2(30) := 'get_oprn_resc';
242 l_api_version CONSTANT NUMBER := 1.0;
243 l_resc_param_tbl gmd_recipe_fetch_pub.recp_resc_proc_param_tbl;
244 X_row NUMBER DEFAULT 0;
245 i NUMBER := 0;
246
247 /* BUG#2621411 RajaSekhar Added capacity_tolerance field */
248
249 CURSOR get_oprn_resc IS
250 select
251 o.oprn_id,o.oprn_no,o.oprn_vers, o.oprn_desc,
252 a.activity,
253 res.oprn_line_id,res.resources, res.resource_usage, res.resource_count,
254 res.process_qty, prim_rsrc_ind, scale_type, cost_analysis_code, res.cost_cmpntcls_id,
255 res.resource_usage_uom, res.offset_interval, nvl(l.max_capacity,m.max_capacity) max_capacity,
256 nvl(l.min_capacity,m.min_capacity) min_capacity,
257 nvl(l.capacity_um,m.capacity_um) capacity_uom, a.sequence_dependent_ind,
258 nvl(l.capacity_constraint,m.capacity_constraint) capacity_constraint,
259 nvl(l.capacity_tolerance, m.capacity_tolerance) capacity_tolerance,
260 res.resource_process_uom, PROCESS_PARAMETER_1, PROCESS_PARAMETER_2,PROCESS_PARAMETER_3,
261 PROCESS_PARAMETER_4, PROCESS_PARAMETER_5, res.text_code, res.created_by,
262 res.last_updated_by, res.last_update_date, res.creation_date, res.last_update_login,
263 res.attribute_category,
264 res.attribute1, res.attribute2, res.attribute3, res.attribute4,
265 res.attribute5, res.attribute6, res.attribute7, res.attribute8,
266 res.attribute9, res.attribute10, res.attribute11, res.attribute12,
267 res.attribute13, res.attribute14, res.attribute15, res.attribute16,
268 res.attribute17, res.attribute18, res.attribute19, res.attribute20,
269 res.attribute21, res.attribute22,res.attribute23, res.attribute24,
270 res.attribute25, res.attribute26, res.attribute27, res.attribute28,
271 res.attribute29, res.attribute30
272 FROM gmd_operations_vl o,gmd_operation_activities a, gmd_operation_resources res,
273 cr_rsrc_mst m, cr_rsrc_dtl l
274 where a.oprn_id = p_oprn_id
275 and o.oprn_id = a.oprn_id
276 and a.oprn_line_id = res.oprn_line_id
277 and m.resources = res.resources
278 AND m.resources = l.resources (+)
279 AND l.orgn_code (+) = p_orgn_code
280 --Added the order by for the bug 12613037.
281 ORDER BY a.sequence_dependent_ind, res.offset_interval, res.resources, res.oprn_line_id;
282 --ORDER BY res.oprn_line_id ;
283
284
285 BEGIN
286
287 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
288 l_api_name, G_PKG_NAME) THEN
289 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
290 END IF;
291 IF FND_API.to_Boolean(p_init_msg_list) THEN
292 FND_MSG_PUB.initialize;
293 END IF;
294
295 x_return_status := FND_API.G_RET_STS_SUCCESS;
296
297 /* BUG#2621411 RajaSekhar Added capacity_tolerance field */
298
299 For get_rec IN get_oprn_resc LOOP
300 i := i + 1;
301
302 x_oprn_resc_rec(i).oprn_id := get_rec.oprn_id ;
303 x_oprn_resc_rec(i).oprn_no := get_rec.oprn_no ;
307 x_oprn_resc_rec(i).oprn_line_id := get_rec.oprn_line_id ;
304 x_oprn_resc_rec(i).oprn_vers := get_rec.oprn_vers ;
305 x_oprn_resc_rec(i).oprn_desc := get_rec.oprn_desc ;
306 x_oprn_resc_rec(i).activity := get_rec.activity ;
308 x_oprn_resc_rec(i).resources := get_rec.resources ;
309 x_oprn_resc_rec(i).resource_usage := get_rec.resource_usage ;
310 x_oprn_resc_rec(i).resource_count := get_rec.resource_count ;
311 x_oprn_resc_rec(i).process_qty := get_rec.process_qty ;
312 x_oprn_resc_rec(i).prim_rsrc_ind := get_rec.prim_rsrc_ind ;
313 x_oprn_resc_rec(i).scale_type := get_rec.scale_type ;
314 x_oprn_resc_rec(i).cost_analysis_code := get_rec.cost_analysis_code ;
315 x_oprn_resc_rec(i).cost_cmpntcls_id := get_rec.cost_cmpntcls_id ;
316 x_oprn_resc_rec(i).usage_um := get_rec.resource_usage_uom ;
317 x_oprn_resc_rec(i).offset_interval := get_rec.offset_interval ;
318 x_oprn_resc_rec(i).min_capacity := get_rec.min_capacity ;
319 x_oprn_resc_rec(i).max_capacity := get_rec.max_capacity ;
320 x_oprn_resc_rec(i).capacity_uom := get_rec.capacity_uom ;
321 x_oprn_resc_rec(i).capacity_constraint := get_rec.capacity_constraint ;
322 x_oprn_resc_rec(i).capacity_tolerance := get_rec.capacity_tolerance ;
323 x_oprn_resc_rec(i).process_uom := get_rec.resource_process_uom ;
324 x_oprn_resc_rec(i).offset_interval := get_rec.offset_interval ;
325 x_oprn_resc_rec(i).process_parameter_1 := get_rec.process_parameter_1 ;
326 x_oprn_resc_rec(i).process_parameter_2 := get_rec.process_parameter_2 ;
327 x_oprn_resc_rec(i).process_parameter_3 := get_rec.process_parameter_3 ;
328 x_oprn_resc_rec(i).process_parameter_4 := get_rec.process_parameter_4 ;
329 x_oprn_resc_rec(i).process_parameter_5 := get_rec.process_parameter_5 ;
330 x_oprn_resc_rec(i).text_code := get_rec.text_code ;
331 x_oprn_resc_rec(i).last_updated_by := get_rec.last_updated_by ;
332 x_oprn_resc_rec(i).created_by := get_rec.created_by ;
333 x_oprn_resc_rec(i).last_update_date := get_rec.last_update_date ;
334 x_oprn_resc_rec(i).creation_date := get_rec.creation_date ;
335 x_oprn_resc_rec(i).last_update_login := get_rec.last_update_login ;
336 x_oprn_resc_rec(i).attribute_category := get_rec.attribute_category ;
337 x_oprn_resc_rec(i).attribute1 := get_rec.attribute1 ;
338 x_oprn_resc_rec(i).attribute2 := get_rec.attribute2 ;
339 x_oprn_resc_rec(i).attribute3 := get_rec.attribute3 ;
340 x_oprn_resc_rec(i).attribute4 := get_rec.attribute4 ;
341 x_oprn_resc_rec(i).attribute5 := get_rec.attribute5 ;
342 x_oprn_resc_rec(i).attribute6 := get_rec.attribute6 ;
343 x_oprn_resc_rec(i).attribute7 := get_rec.attribute7 ;
344 x_oprn_resc_rec(i).attribute8 := get_rec.attribute8 ;
345 x_oprn_resc_rec(i).attribute9 := get_rec.attribute9 ;
346 x_oprn_resc_rec(i).attribute10 := get_rec.attribute10 ;
347 x_oprn_resc_rec(i).attribute11 := get_rec.attribute11 ;
348 x_oprn_resc_rec(i).attribute12 := get_rec.attribute12 ;
349 x_oprn_resc_rec(i).attribute13 := get_rec.attribute13 ;
350 x_oprn_resc_rec(i).attribute14 := get_rec.attribute14 ;
351 x_oprn_resc_rec(i).attribute15 := get_rec.attribute15 ;
352 x_oprn_resc_rec(i).attribute16 := get_rec.attribute16 ;
353 x_oprn_resc_rec(i).attribute17 := get_rec.attribute17 ;
354 x_oprn_resc_rec(i).attribute18 := get_rec.attribute18 ;
355 x_oprn_resc_rec(i).attribute19 := get_rec.attribute19 ;
356 x_oprn_resc_rec(i).attribute20 := get_rec.attribute20 ;
357 x_oprn_resc_rec(i).attribute21 := get_rec.attribute21 ;
358 x_oprn_resc_rec(i).attribute22 := get_rec.attribute22 ;
359 x_oprn_resc_rec(i).attribute23 := get_rec.attribute23 ;
360 x_oprn_resc_rec(i).attribute24 := get_rec.attribute24 ;
361 x_oprn_resc_rec(i).attribute25 := get_rec.attribute25 ;
362 x_oprn_resc_rec(i).attribute26 := get_rec.attribute26 ;
363 x_oprn_resc_rec(i).attribute27 := get_rec.attribute27 ;
364 x_oprn_resc_rec(i).attribute28 := get_rec.attribute28 ;
365 x_oprn_resc_rec(i).attribute29 := get_rec.attribute29 ;
366 x_oprn_resc_rec(i).attribute30 := get_rec.attribute30 ;
367
368 gmd_fetch_oprn.get_oprn_process_param_detl
369 (p_api_version => p_api_version
370 ,p_init_msg_list => p_init_msg_list
371 ,p_oprn_line_id => get_rec.oprn_line_id
372 ,p_resources => get_rec.resources
373 ,x_return_status => x_return_status
374 ,x_msg_count => x_msg_count
375 ,x_msg_data => x_msg_data
376 ,X_oprn_resc_proc_param_tbl => l_resc_param_tbl);
377
378 X_row := X_oprn_resc_proc_param_tbl.count;
379
380 FOR K in 1.. l_resc_param_tbl.count loop
381 X_row := X_row + 1;
382 X_oprn_resc_proc_param_tbl(X_row) := l_resc_param_tbl(K);
383 END LOOP;
384 END LOOP;
385
386 IF i = 0 THEN
387 RAISE fnd_api.g_exc_error;
388 END IF;
389
390 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
391 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
392
393 EXCEPTION
394 WHEN FND_API.G_EXC_ERROR THEN
395 X_return_code := SQLCODE;
396 x_return_status := FND_API.G_RET_STS_ERROR;
397 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
398
399 WHEN OTHERS THEN
400 X_return_code := SQLCODE;
401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
402 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
403 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
404 END get_oprn_resc;
405
406 PROCEDURE get_oprn_process_param_detl
407 ( p_api_version IN NUMBER ,
408 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
412 x_msg_count OUT NOCOPY NUMBER ,
409 p_oprn_line_id IN NUMBER ,
410 p_resources IN VARCHAR2 ,
411 x_return_status OUT NOCOPY VARCHAR2 ,
413 x_msg_data OUT NOCOPY VARCHAR2 ,
414 X_oprn_resc_proc_param_tbl OUT NOCOPY gmd_recipe_fetch_pub.recp_resc_proc_param_tbl
415 ) IS
416
417 /* Parameters at the oprn resource level */
418 CURSOR Cur_get_oprn_rsrc IS
419 SELECT p.*,g.parameter_name,g.parameter_description,g.units,g.parameter_type
420 FROM gmd_oprn_process_parameters_v1 p, gmp_process_parameters g
421 WHERE p.oprn_line_id = P_oprn_line_id
422 AND p.resources = P_resources
423 AND p.parameter_id = g.parameter_id
424 ORDER BY sequence_no;
425
426 l_oprn_rec Cur_get_oprn_rsrc%ROWTYPE;
427 l_api_name VARCHAR2(40) := 'get_oprn_process_param_detl';
428 X_row NUMBER DEFAULT 0;
429 X_override NUMBER(5) DEFAULT 0;
430 BEGIN
431 x_return_status := FND_API.G_RET_STS_SUCCESS;
432
433 FOR l_oprn_rec IN Cur_get_oprn_rsrc LOOP
434 X_row := X_row + 1;
435 X_oprn_resc_proc_param_tbl(X_row).recipe_id := NULL;
436 X_oprn_resc_proc_param_tbl(X_row).routingstep_id := NULL;
437 X_oprn_resc_proc_param_tbl(X_row).routingstep_no := NULL;
438 X_oprn_resc_proc_param_tbl(X_row).oprn_line_id := P_oprn_line_id;
439 X_oprn_resc_proc_param_tbl(X_row).resources := P_resources;
440 X_oprn_resc_proc_param_tbl(X_row).parameter_id := l_oprn_rec.parameter_id;
441 X_oprn_resc_proc_param_tbl(X_row).parameter_name := l_oprn_rec.parameter_name;
442 X_oprn_resc_proc_param_tbl(X_row).parameter_description := l_oprn_rec.parameter_description;
443 X_oprn_resc_proc_param_tbl(X_row).units := l_oprn_rec.units;
444 X_oprn_resc_proc_param_tbl(X_row).target_value := l_oprn_rec.target_value;
445 X_oprn_resc_proc_param_tbl(X_row).minimum_value := l_oprn_rec.minimum_value;
446 X_oprn_resc_proc_param_tbl(X_row).maximum_value := l_oprn_rec.maximum_value;
447 X_oprn_resc_proc_param_tbl(X_row).parameter_type := l_oprn_rec.parameter_type;
448 X_oprn_resc_proc_param_tbl(X_row).sequence_no := l_oprn_rec.sequence_no;
449 X_oprn_resc_proc_param_tbl(X_row).created_by := l_oprn_rec.created_by;
450 X_oprn_resc_proc_param_tbl(X_row).creation_date := l_oprn_rec.creation_date;
451 X_oprn_resc_proc_param_tbl(X_row).last_updated_by := l_oprn_rec.last_updated_by;
452 X_oprn_resc_proc_param_tbl(X_row).last_update_date := l_oprn_rec.last_update_date;
453 X_oprn_resc_proc_param_tbl(X_row).last_update_login := l_oprn_rec.last_update_login;
454 X_oprn_resc_proc_param_tbl(X_row).recipe_override := X_override;
455 END LOOP; /* FOR l_oprn_rec IN Cur_get_oprn_rsrc */
456 EXCEPTION
457 WHEN OTHERS THEN
458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
459 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
460 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
461 END get_oprn_process_param_detl;
462
463 END GMD_FETCH_OPRN ;