[Home] [Help]
PACKAGE BODY: APPS.CRP_FORM_PK
Source
1 PACKAGE BODY crp_form_pk AS
2 /* $Header: CRPSELEB.pls 115.2 2002/06/15 00:42:14 pkm ship $ */
3
4 /*----------crp_selection_criteria------------*/
5 PROCEDURE crp_selection_criteria(
6 arg_query_id NUMBER,
7 arg_type NUMBER,
8 arg_org_id NUMBER,
9 arg_owning_dept_id NUMBER DEFAULT NULL,
10 arg_dept_id NUMBER DEFAULT NULL,
11 arg_res_id NUMBER DEFAULT NULL,
12 arg_line_id NUMBER DEFAULT NULL,
13 arg_res_type NUMBER DEFAULT NULL,
14 arg_dept_class VARCHAR2 DEFAULT NULL,
15 arg_res_grp VARCHAR2 DEFAULT NULL) IS
16 BEGIN
17
18 IF (arg_query_id IS NULL OR
19 arg_type IS NULL OR
20 arg_org_id IS NULL) THEN
21 RAISE insufficient_args;
22 END IF;
23
24 /*==================================
25 Column mapping as follows
26 char1 Owning department
27 char2 Using department
28 char3 Resource
29 char4 Department Class
30 char5 Line
31 char6 Line description
32 char7 Org code
33 char8 Resource Type meaning
34 char9 Resource Group
35 number1 Owning department id
36 number2 Using department id
37 number3 Resource id
38 number4 Resource Type id
39 number5 Line id
40 number6 Org id
41 number7 Max Util % for
42 RESOURCE_DISCRETE canvass
43 number8 Min Util % for
44 RESOURCE_DISCRETE canvass
45 number9 Max Rate for line
46 number10 Min Rate for line
47 number11 Max Util % for
48 RESOURCE_LINE canvass
49 number12 Min Util % for
50 RESOURCE_LINE canvass
51 number13 Selection checkbox
52 1 - Checked (yes)
53 2 - unchecked (no)
54 ==================================*/
55 IF (arg_type = RATE_BASED) THEN
56 INSERT INTO crp_form_query
57 (query_id,
58 last_update_date,
59 last_updated_by,
60 last_update_login,
61 creation_date,
62 created_by,
63 char5,
64 char6,
65 char7,
66 number5,
67 number9,
68 number10,
69 number13)
70 SELECT
71 arg_query_id,
72 SYSDATE,
73 -1,
74 -1,
75 SYSDATE,
76 -1,
77 wl.line_code,
78 wl.description,
79 mtl.organization_code,
80 wl.line_id,
81 wl.maximum_rate,
82 wl.minimum_rate,
83 2
84 FROM mtl_parameters mtl,
85 wip_lines wl
86 WHERE NVL(disable_date, SYSDATE+1) > SYSDATE
87 AND mtl.organization_id = wl.organization_id
88 AND (arg_line_id is NULL OR
89 wl.line_id = arg_line_id)
90 AND wl.organization_id = arg_org_id;
91
92 ELSE
93 INSERT INTO crp_form_query
94 (query_id,
95 last_update_date,
96 last_updated_by,
97 last_update_login,
98 creation_date,
99 created_by,
100 char1,
101 char2,
102 char3,
103 char4,
104 char7,
105 char8,
106 char9,
107 number1,
108 number2,
109 number3,
110 number4,
111 number13
112 )
113 SELECT
114 arg_query_id,
115 SYSDATE,
116 -1,
117 -1,
118 SYSDATE,
119 -1,
120 depts2.department_code,
121 depts1.department_code,
122 resources.resource_code,
123 depts1.department_class_code,
124 mtl.organization_code,
125 lkps.meaning,
126 dept_res.resource_group_name,
127 depts2.department_id,
128 depts1.department_id,
129 resources.resource_id,
130 resources.resource_type,
131 2
132 FROM mtl_parameters mtl,
133 mfg_lookups lkps,
134 bom_departments depts2,
135 bom_department_resources dept_res,
136 bom_resources resources,
137 bom_departments depts1
138 WHERE resources.resource_id = dept_res.resource_id
139 AND resources.organization_id = depts1.organization_id
140 AND dept_res.department_id = depts1.department_id
141 AND depts2.department_id =
142 nvl(dept_res.share_from_dept_id, dept_res.department_id)
143 AND depts2.organization_id = depts1.organization_id
144 AND depts1.organization_id = arg_org_id
145 AND mtl.organization_id = arg_org_id
146 AND lkps.lookup_code = resources.resource_type
147 AND lkps.lookup_type = 'BOM_RESOURCE_TYPE'
148 AND (arg_dept_class is NULL OR
149 depts1.department_class_code = arg_dept_class)
150 AND (arg_owning_dept_id is NULL OR
151 depts2.department_id = arg_owning_dept_id)
152 AND (arg_dept_id is NULL OR
153 depts1.department_id = arg_dept_id)
154 AND (arg_res_id is NULL OR
155 resources.resource_id = arg_res_id)
156 AND (arg_res_grp is NULL OR
157 dept_res.resource_group_name = arg_res_grp)
158 AND (arg_res_type is NULL OR
159 resources.resource_type = arg_res_type);
160
161 END IF;
162
163 EXCEPTION
164 WHEN insufficient_args THEN
165 RAISE insufficient_args;
166
167 END crp_selection_criteria;
168
169
170 PROCEDURE crp_update_util(
171 arg_query_id1 NUMBER,
172 arg_query_id2 NUMBER,
173 arg_line_capacity NUMBER) IS
174 BEGIN
175 if arg_line_capacity = 2 then
176 update crp_form_query query
177 set (number11, number12) =
178 (select ROUND(greatest(NVL(PERIOD1,-1),
179 greatest(NVL(PERIOD2,-1),
180 greatest(NVL(PERIOD3,-1),
181 greatest(NVL(PERIOD4,-1),
182 greatest(NVL(PERIOD5,-1),
183 greatest(NVL(PERIOD6,-1),
184 greatest(NVL(PERIOD7,-1),
185 greatest(NVL(PERIOD8,-1),
186 greatest(NVL(PERIOD9,-1),
187 greatest(NVL(PERIOD10,-1),
188 greatest(NVL(PERIOD11,-1),
189 greatest(NVL(PERIOD12,-1),
190 greatest(NVL(PERIOD13,-1),
191 greatest(NVL(PERIOD14,-1),
192 greatest(NVL(PERIOD15,-1),
193 greatest(NVL(PERIOD16,-1),
194 greatest(NVL(PERIOD17,-1),
195 greatest(NVL(PERIOD18,-1))))))))))))))))))),2),
196 ROUND(least(NVL(PERIOD1,100000000),
197 least(NVL(PERIOD2, 100000000),
198 least(NVL(PERIOD3,100000000),
199 least(NVL(PERIOD4,100000000),
200 least(NVL(PERIOD5,100000000),
201 least(NVL(PERIOD6,100000000),
202 least(NVL(PERIOD7,100000000),
203 least(NVL(PERIOD8,100000000),
204 least(NVL(PERIOD9,100000000),
205 least(NVL(PERIOD10,100000000),
206 least(NVL(PERIOD11,100000000),
207 least(NVL(PERIOD12,100000000),
208 least(NVL(PERIOD13,100000000),
209 least(NVL(PERIOD14,100000000),
210 least(NVL(PERIOD15,100000000),
211 least(NVL(PERIOD16,100000000),
212 least(NVL(PERIOD17,100000000),
213 least(NVL(PERIOD18,100000000))))))))))))))))))), 2)
214 from crp_capacity_plans cap
215 where cap.type_id = 5
216 and cap.line_id = query.number5
217 and cap.query_id = arg_query_id1)
218 where query_id = arg_query_id2
219 and number5 is not null;
220 /*----------------------------------------------------------------+
221 | THis statement has been intentionally split to avoid a PL/SQL |
222 | parser stack overflow |
223 +----------------------------------------------------------------*/
224
225 update crp_form_query query
226 set (number11, number12) =
227 (select ROUND(greatest(NVL(query.number11,-1),
228 greatest(NVL(PERIOD19,-1),
229 greatest(NVL(PERIOD20,-1),
230 greatest(NVL(PERIOD21,-1),
231 greatest(NVL(PERIOD22,-1),
232 greatest(NVL(PERIOD23,-1),
233 greatest(NVL(PERIOD24,-1),
234 greatest(NVL(PERIOD25,-1),
235 greatest(NVL(PERIOD26,-1),
236 greatest(NVL(PERIOD27,-1),
237 greatest(NVL(PERIOD28,-1),
238 greatest(NVL(PERIOD29,-1),
239 greatest(NVL(PERIOD30,-1),
240 greatest(NVL(PERIOD31,-1),
241 greatest(NVL(PERIOD32,-1),
242 greatest(NVL(PERIOD33,-1),
243 greatest(NVL(PERIOD34,-1),
244 greatest(NVL(PERIOD35,-1),
245 NVL(PERIOD36,-1))))))))))))))))))), 2),
246 ROUND(least(NVL(query.number12, 100000000),
247 least(NVL(PERIOD18,100000000),
248 least(NVL(PERIOD19,100000000),
249 least(NVL(PERIOD20,100000000),
250 least(NVL(PERIOD21,100000000),
251 least(NVL(PERIOD22,100000000),
252 least(NVL(PERIOD23,100000000),
253 least(NVL(PERIOD24,100000000),
254 least(NVL(PERIOD25,100000000),
255 least(NVL(PERIOD26,100000000),
256 least(NVL(PERIOD27,100000000),
257 least(NVL(PERIOD28,100000000),
258 least(NVL(PERIOD29,100000000),
259 least(NVL(PERIOD30,100000000),
260 least(NVL(PERIOD31,100000000),
261 least(NVL(PERIOD32,100000000),
262 least(NVL(PERIOD33,100000000),
263 least(NVL(PERIOD34,100000000),
264 least(NVL(PERIOD35,100000000),
265 NVL(PERIOD36, 100000000)))))))))))))))))))), 2)
266 from crp_capacity_plans cap
267 where cap.type_id = 5
268 and cap.line_id = query.number5
269 and cap.query_id = arg_query_id1)
270 where query_id = arg_query_id2
271 and number5 is not null;
272
273 update crp_form_query
274 set number11 = NULL
275 where query_id = arg_query_id2
276 and number11 = -1
277 and number5 is not null;
278
279 update crp_form_query
280 set number12 = NULL
281 where query_id = arg_query_id2
282 and number12 = 100000000
283 and number5 is not null;
284
285 else
286 update crp_form_query query
287 set (number7, number8) =
288 (select ROUND(greatest(NVL(PERIOD1,-1),
289 greatest(NVL(PERIOD2,-1),
290 greatest(NVL(PERIOD3,-1),
291 greatest(NVL(PERIOD4,-1),
292 greatest(NVL(PERIOD5,-1),
293 greatest(NVL(PERIOD6,-1),
294 greatest(NVL(PERIOD7,-1),
295 greatest(NVL(PERIOD8,-1),
296 greatest(NVL(PERIOD9,-1),
297 greatest(NVL(PERIOD10,-1),
298 greatest(NVL(PERIOD11,-1),
299 greatest(NVL(PERIOD12,-1),
300 greatest(NVL(PERIOD13,-1),
301 greatest(NVL(PERIOD14,-1),
302 greatest(NVL(PERIOD15,-1),
303 greatest(NVL(PERIOD16,-1),
304 greatest(NVL(PERIOD17,-1),
305 greatest(NVL(PERIOD18,-1))))))))))))))))))), 2),
306 ROUND(least(NVL(PERIOD1,100000000),
307 least(NVL(PERIOD2, 100000000),
308 least(NVL(PERIOD3,100000000),
309 least(NVL(PERIOD4,100000000),
310 least(NVL(PERIOD5,100000000),
311 least(NVL(PERIOD6,100000000),
312 least(NVL(PERIOD7,100000000),
313 least(NVL(PERIOD8,100000000),
314 least(NVL(PERIOD9,100000000),
315 least(NVL(PERIOD10,100000000),
316 least(NVL(PERIOD11,100000000),
317 least(NVL(PERIOD12,100000000),
318 least(NVL(PERIOD13,100000000),
319 least(NVL(PERIOD14,100000000),
320 least(NVL(PERIOD15,100000000),
321 least(NVL(PERIOD16,100000000),
322 least(NVL(PERIOD17,100000000),
323 least(NVL(PERIOD18,100000000))))))))))))))))))), 2)
324 from crp_capacity_plans cap
325 where cap.type_id = 5
326 and cap.resource_id = query.number3
327 and cap.department_id = query.number2
328 and cap.query_id = arg_query_id1)
329 where query_id = arg_query_id2
330 and number5 is null;
331
332
333 update crp_form_query query
334 set (number7, number8) =
335 (select ROUND(greatest(NVL(query.number7, 0),
336 greatest(NVL(PERIOD19,-1),
337 greatest(NVL(PERIOD20,-1),
338 greatest(NVL(PERIOD21,-1),
339 greatest(NVL(PERIOD22,-1),
340 greatest(NVL(PERIOD23,-1),
341 greatest(NVL(PERIOD24,-1),
342 greatest(NVL(PERIOD25,-1),
343 greatest(NVL(PERIOD26,-1),
344 greatest(NVL(PERIOD27,-1),
345 greatest(NVL(PERIOD28,-1),
346 greatest(NVL(PERIOD29,-1),
347 greatest(NVL(PERIOD30,-1),
348 greatest(NVL(PERIOD31,-1),
349 greatest(NVL(PERIOD32,-1),
350 greatest(NVL(PERIOD33,-1),
351 greatest(NVL(PERIOD34,-1),
352 greatest(NVL(PERIOD35,-1),
353 NVL(PERIOD36,-1))))))))))))))))))),2),
354 ROUND(least(NVL(query.number8, 100000000),
355 least(NVL(PERIOD19,100000000),
356 least(NVL(PERIOD20,100000000),
357 least(NVL(PERIOD21,100000000),
358 least(NVL(PERIOD22,100000000),
359 least(NVL(PERIOD23,100000000),
360 least(NVL(PERIOD24,100000000),
361 least(NVL(PERIOD25,100000000),
362 least(NVL(PERIOD26,100000000),
363 least(NVL(PERIOD27,100000000),
364 least(NVL(PERIOD28,100000000),
365 least(NVL(PERIOD29,100000000),
366 least(NVL(PERIOD30,100000000),
367 least(NVL(PERIOD31,100000000),
368 least(NVL(PERIOD32,100000000),
369 least(NVL(PERIOD33,100000000),
370 least(NVL(PERIOD34,100000000),
371 least(NVL(PERIOD35,100000000),
372 NVL(PERIOD36, 100000000))))))))))))))))))), 2)
373 from crp_capacity_plans cap
374 where cap.type_id = 5
375 and cap.resource_id = query.number3
376 and cap.department_id = query.number2
377 and cap.query_id = arg_query_id1)
378 where query_id = arg_query_id2
379 and number5 is null;
380
381 update crp_form_query
382 set number7 = NULL
383 where query_id = arg_query_id2
384 and number7 = -1
385 and number5 is null;
386
387 update crp_form_query
388 set number8 = NULL
389 where query_id = arg_query_id2
390 and number8 = 100000000
391 and number5 is null;
392
393 end if;
394
395 commit;
396 END crp_update_util;
397
398
399
400 /*----------crp_resource_list------------*/
401 FUNCTION crp_resource_list(
402 arg_session_id NUMBER,
403 arg_type NUMBER,
404 arg_query_id1 NUMBER,
405 arg_query_id2 NUMBER) RETURN NUMBER IS
406
407 rows_inserted NUMBER;
408 BEGIN
409
410 IF (arg_type = ROUTING_BASED) then
411 INSERT INTO crp_form_query(
412 query_id,
413 last_update_date,
414 last_updated_by,
415 last_update_login,
416 creation_date,
417 created_by,
418 number1,
419 number2)
420 SELECT arg_query_id2,
421 SYSDATE,
422 -1,
423 -1,
424 SYSDATE,
425 -1,
426 number2,
427 number3
428 FROM crp_form_query query
429 WHERE not exists
430 (SELECT null
431 FROM crp_capacity_plans
432 WHERE query_id = arg_session_id
433 AND department_id = query.number2
434 AND resource_id = query.number3)
435 AND number13 = 1
436 AND query.query_id = arg_query_id1;
437 rows_inserted := SQL%ROWCOUNT;
438 ELSE
439 INSERT INTO crp_form_query(
440 query_id,
441 last_update_date,
442 last_updated_by,
443 last_update_login,
444 creation_date,
445 created_by,
446 number3)
447 SELECT arg_query_id2,
448 SYSDATE,
449 -1,
450 -1,
451 SYSDATE,
452 -1,
453 number5
454 FROM crp_form_query query
455 WHERE not exists
456 (SELECT null
457 FROM crp_capacity_plans
458 WHERE query_id = arg_session_id
459 AND line_id = query.number5)
460 AND number13 = 1
461 AND query.query_id = arg_query_id1;
462 rows_inserted := SQL%ROWCOUNT;
463 END IF;
464 COMMIT;
465 return(rows_inserted);
466 END crp_resource_list;
467
468 PROCEDURE crp_upd_dept_class(arg_query_id NUMBER) IS
469 BEGIN
470 update crp_capacity_plans plan
471 set department_class =
472 (select department_class_code
473 from bom_departments
474 where department_id = plan.department_id),
475 resource_type =
476 (select meaning
477 from mfg_lookups,
478 bom_resources
479 where lookup_type = 'BOM_RESOURCE_TYPE'
480 and lookup_code = resource_type
481 and resource_id = plan.resource_id),
482 resource_group_name =
483 (select resource_group_name
484 from bom_department_resources
485 where department_id = plan.department_id
486 and resource_id = plan.resource_id)
487 where query_id = arg_query_id;
488
489 COMMIT;
490 END;
491
492 END crp_form_pk;