[Home] [Help]
PACKAGE BODY: APPS.GMD_FETCH_OPRN
Source
1 PACKAGE BODY GMD_FETCH_OPRN AS
2 /* $Header: GMDPOPNB.pls 120.1 2006/02/01 10:10:54 txdaniel noship $ */
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 ,
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,
15 X_oprn_resc_rec OUT NOCOPY gmd_recipe_fetch_pub.oprn_resc_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
130 ORDER BY a.oprn_line_id;
131
132 begin
133
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;
137 END IF;
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 ;
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 ;
186 x_oprn_act_out(i).attribute19 := get_rec.attribute19 ;
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,
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 ORDER BY res.oprn_line_id ;
281
282
283 BEGIN
284
285 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
286 l_api_name, G_PKG_NAME) THEN
287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
288 END IF;
289 IF FND_API.to_Boolean(p_init_msg_list) THEN
290 FND_MSG_PUB.initialize;
291 END IF;
292
293 x_return_status := FND_API.G_RET_STS_SUCCESS;
294
295 /* BUG#2621411 RajaSekhar Added capacity_tolerance field */
296
297 For get_rec IN get_oprn_resc LOOP
298 i := i + 1;
299
300 x_oprn_resc_rec(i).oprn_id := get_rec.oprn_id ;
301 x_oprn_resc_rec(i).oprn_no := get_rec.oprn_no ;
302 x_oprn_resc_rec(i).oprn_vers := get_rec.oprn_vers ;
303 x_oprn_resc_rec(i).oprn_desc := get_rec.oprn_desc ;
304 x_oprn_resc_rec(i).activity := get_rec.activity ;
305 x_oprn_resc_rec(i).oprn_line_id := get_rec.oprn_line_id ;
306 x_oprn_resc_rec(i).resources := get_rec.resources ;
307 x_oprn_resc_rec(i).resource_usage := get_rec.resource_usage ;
308 x_oprn_resc_rec(i).resource_count := get_rec.resource_count ;
309 x_oprn_resc_rec(i).process_qty := get_rec.process_qty ;
310 x_oprn_resc_rec(i).prim_rsrc_ind := get_rec.prim_rsrc_ind ;
311 x_oprn_resc_rec(i).scale_type := get_rec.scale_type ;
312 x_oprn_resc_rec(i).cost_analysis_code := get_rec.cost_analysis_code ;
313 x_oprn_resc_rec(i).cost_cmpntcls_id := get_rec.cost_cmpntcls_id ;
314 x_oprn_resc_rec(i).usage_um := get_rec.resource_usage_uom ;
315 x_oprn_resc_rec(i).offset_interval := get_rec.offset_interval ;
316 x_oprn_resc_rec(i).min_capacity := get_rec.min_capacity ;
317 x_oprn_resc_rec(i).max_capacity := get_rec.max_capacity ;
318 x_oprn_resc_rec(i).capacity_uom := get_rec.capacity_uom ;
319 x_oprn_resc_rec(i).capacity_constraint := get_rec.capacity_constraint ;
320 x_oprn_resc_rec(i).capacity_tolerance := get_rec.capacity_tolerance ;
321 x_oprn_resc_rec(i).process_uom := get_rec.resource_process_uom ;
322 x_oprn_resc_rec(i).offset_interval := get_rec.offset_interval ;
323 x_oprn_resc_rec(i).process_parameter_1 := get_rec.process_parameter_1 ;
324 x_oprn_resc_rec(i).process_parameter_2 := get_rec.process_parameter_2 ;
325 x_oprn_resc_rec(i).process_parameter_3 := get_rec.process_parameter_3 ;
326 x_oprn_resc_rec(i).process_parameter_4 := get_rec.process_parameter_4 ;
327 x_oprn_resc_rec(i).process_parameter_5 := get_rec.process_parameter_5 ;
328 x_oprn_resc_rec(i).text_code := get_rec.text_code ;
329 x_oprn_resc_rec(i).last_updated_by := get_rec.last_updated_by ;
330 x_oprn_resc_rec(i).created_by := get_rec.created_by ;
331 x_oprn_resc_rec(i).last_update_date := get_rec.last_update_date ;
332 x_oprn_resc_rec(i).creation_date := get_rec.creation_date ;
333 x_oprn_resc_rec(i).last_update_login := get_rec.last_update_login ;
334 x_oprn_resc_rec(i).attribute_category := get_rec.attribute_category ;
335 x_oprn_resc_rec(i).attribute1 := get_rec.attribute1 ;
336 x_oprn_resc_rec(i).attribute2 := get_rec.attribute2 ;
337 x_oprn_resc_rec(i).attribute3 := get_rec.attribute3 ;
338 x_oprn_resc_rec(i).attribute4 := get_rec.attribute4 ;
339 x_oprn_resc_rec(i).attribute5 := get_rec.attribute5 ;
340 x_oprn_resc_rec(i).attribute6 := get_rec.attribute6 ;
341 x_oprn_resc_rec(i).attribute7 := get_rec.attribute7 ;
342 x_oprn_resc_rec(i).attribute8 := get_rec.attribute8 ;
343 x_oprn_resc_rec(i).attribute9 := get_rec.attribute9 ;
344 x_oprn_resc_rec(i).attribute10 := get_rec.attribute10 ;
345 x_oprn_resc_rec(i).attribute11 := get_rec.attribute11 ;
346 x_oprn_resc_rec(i).attribute12 := get_rec.attribute12 ;
347 x_oprn_resc_rec(i).attribute13 := get_rec.attribute13 ;
348 x_oprn_resc_rec(i).attribute14 := get_rec.attribute14 ;
349 x_oprn_resc_rec(i).attribute15 := get_rec.attribute15 ;
350 x_oprn_resc_rec(i).attribute16 := get_rec.attribute16 ;
351 x_oprn_resc_rec(i).attribute17 := get_rec.attribute17 ;
352 x_oprn_resc_rec(i).attribute18 := get_rec.attribute18 ;
353 x_oprn_resc_rec(i).attribute19 := get_rec.attribute19 ;
354 x_oprn_resc_rec(i).attribute20 := get_rec.attribute20 ;
355 x_oprn_resc_rec(i).attribute21 := get_rec.attribute21 ;
356 x_oprn_resc_rec(i).attribute22 := get_rec.attribute22 ;
357 x_oprn_resc_rec(i).attribute23 := get_rec.attribute23 ;
358 x_oprn_resc_rec(i).attribute24 := get_rec.attribute24 ;
359 x_oprn_resc_rec(i).attribute25 := get_rec.attribute25 ;
360 x_oprn_resc_rec(i).attribute26 := get_rec.attribute26 ;
361 x_oprn_resc_rec(i).attribute27 := get_rec.attribute27 ;
362 x_oprn_resc_rec(i).attribute28 := get_rec.attribute28 ;
363 x_oprn_resc_rec(i).attribute29 := get_rec.attribute29 ;
364 x_oprn_resc_rec(i).attribute30 := get_rec.attribute30 ;
365
366 gmd_fetch_oprn.get_oprn_process_param_detl
367 (p_api_version => p_api_version
368 ,p_init_msg_list => p_init_msg_list
369 ,p_oprn_line_id => get_rec.oprn_line_id
370 ,p_resources => get_rec.resources
371 ,x_return_status => x_return_status
372 ,x_msg_count => x_msg_count
373 ,x_msg_data => x_msg_data
374 ,X_oprn_resc_proc_param_tbl => l_resc_param_tbl);
375
376 X_row := X_oprn_resc_proc_param_tbl.count;
377
378 FOR K in 1.. l_resc_param_tbl.count loop
379 X_row := X_row + 1;
380 X_oprn_resc_proc_param_tbl(X_row) := l_resc_param_tbl(K);
381 END LOOP;
382 END LOOP;
383
384 IF i = 0 THEN
385 RAISE fnd_api.g_exc_error;
386 END IF;
387
388 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
389 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
390
391 EXCEPTION
392 WHEN FND_API.G_EXC_ERROR THEN
393 X_return_code := SQLCODE;
394 x_return_status := FND_API.G_RET_STS_ERROR;
395 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
396
397 WHEN OTHERS THEN
398 X_return_code := SQLCODE;
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
400 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
401 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
402 END get_oprn_resc;
403
404 PROCEDURE get_oprn_process_param_detl
405 ( p_api_version IN NUMBER ,
406 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
407 p_oprn_line_id IN NUMBER ,
408 p_resources IN VARCHAR2 ,
409 x_return_status OUT NOCOPY VARCHAR2 ,
410 x_msg_count OUT NOCOPY NUMBER ,
411 x_msg_data OUT NOCOPY VARCHAR2 ,
412 X_oprn_resc_proc_param_tbl OUT NOCOPY gmd_recipe_fetch_pub.recp_resc_proc_param_tbl
413 ) IS
414
415 /* Parameters at the oprn resource level */
416 CURSOR Cur_get_oprn_rsrc IS
417 SELECT p.*,g.parameter_name,g.parameter_description,g.units,g.parameter_type
418 FROM gmd_oprn_process_parameters_v1 p, gmp_process_parameters g
419 WHERE p.oprn_line_id = P_oprn_line_id
420 AND p.resources = P_resources
421 AND p.parameter_id = g.parameter_id
422 ORDER BY sequence_no;
423
424 l_oprn_rec Cur_get_oprn_rsrc%ROWTYPE;
425 l_api_name VARCHAR2(40) := 'get_oprn_process_param_detl';
426 X_row NUMBER DEFAULT 0;
427 X_override NUMBER(5) DEFAULT 0;
428 BEGIN
429 x_return_status := FND_API.G_RET_STS_SUCCESS;
430
431 FOR l_oprn_rec IN Cur_get_oprn_rsrc LOOP
432 X_row := X_row + 1;
433 X_oprn_resc_proc_param_tbl(X_row).recipe_id := NULL;
434 X_oprn_resc_proc_param_tbl(X_row).routingstep_id := NULL;
435 X_oprn_resc_proc_param_tbl(X_row).routingstep_no := NULL;
436 X_oprn_resc_proc_param_tbl(X_row).oprn_line_id := P_oprn_line_id;
437 X_oprn_resc_proc_param_tbl(X_row).resources := P_resources;
438 X_oprn_resc_proc_param_tbl(X_row).parameter_id := l_oprn_rec.parameter_id;
439 X_oprn_resc_proc_param_tbl(X_row).parameter_name := l_oprn_rec.parameter_name;
440 X_oprn_resc_proc_param_tbl(X_row).parameter_description := l_oprn_rec.parameter_description;
441 X_oprn_resc_proc_param_tbl(X_row).units := l_oprn_rec.units;
442 X_oprn_resc_proc_param_tbl(X_row).target_value := l_oprn_rec.target_value;
443 X_oprn_resc_proc_param_tbl(X_row).minimum_value := l_oprn_rec.minimum_value;
444 X_oprn_resc_proc_param_tbl(X_row).maximum_value := l_oprn_rec.maximum_value;
445 X_oprn_resc_proc_param_tbl(X_row).parameter_type := l_oprn_rec.parameter_type;
446 X_oprn_resc_proc_param_tbl(X_row).sequence_no := l_oprn_rec.sequence_no;
447 X_oprn_resc_proc_param_tbl(X_row).created_by := l_oprn_rec.created_by;
448 X_oprn_resc_proc_param_tbl(X_row).creation_date := l_oprn_rec.creation_date;
449 X_oprn_resc_proc_param_tbl(X_row).last_updated_by := l_oprn_rec.last_updated_by;
450 X_oprn_resc_proc_param_tbl(X_row).last_update_date := l_oprn_rec.last_update_date;
451 X_oprn_resc_proc_param_tbl(X_row).last_update_login := l_oprn_rec.last_update_login;
452 X_oprn_resc_proc_param_tbl(X_row).recipe_override := X_override;
453 END LOOP; /* FOR l_oprn_rec IN Cur_get_oprn_rsrc */
454 EXCEPTION
455 WHEN OTHERS THEN
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
457 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
458 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
459 END get_oprn_process_param_detl;
460
461 END GMD_FETCH_OPRN ;