[Home] [Help]
PACKAGE BODY: APPS.OUTSIDE_PROC_SV
Source
1 PACKAGE BODY outside_proc_sv AS
2 /* $Header: POXOPROB.pls 115.6 2003/12/18 18:18:44 jskim ship $*/
3 /*===========================================================================
4
5 PROCEDURE NAME: get_entity_defaults()
6
7 ===========================================================================*/
8
9 PROCEDURE get_entity_defaults (x_entity_id IN NUMBER,
10 x_dest_org_id IN NUMBER,
11 x_entity_name IN OUT NOCOPY VARCHAR2,
12 x_entity_type IN OUT NOCOPY VARCHAR2) IS
13
14
15
16 x_progress VARCHAR2(3) := NULL;
17
18
19 BEGIN
20
21 x_progress := 10;
22
23 SELECT wip_entity_name,
24 entity_type
25 INTO x_entity_name,
26 x_entity_type
27 FROM wip_entities
28 WHERE wip_entity_id = x_entity_id
29 AND organization_id = x_dest_org_id;
30
31 RETURN;
32
33 EXCEPTION
34
35 WHEN NO_DATA_FOUND THEN
36 RETURN;
37
38 WHEN OTHERS THEN
39 po_message_s.sql_error('get_entity_defaults', x_progress, sqlcode);
40 RAISE;
41
42 END get_entity_defaults;
43
44 /*===========================================================================
45
46 PROCEDURE NAME: test_get_entity_defaults()
47
48 ===========================================================================*/
49
50 PROCEDURE test_get_entity_defaults (x_entity_id IN NUMBER,
51 x_dest_org_id IN NUMBER) IS
52
53
54
55 x_progress VARCHAR2(3) := NULL;
56 x_entity_name VARCHAR2(15);
57 x_entity_type VARCHAR2(15);
58
59 BEGIN
60
61
62 --DBMS_OUTPUT.PUT_LINE('x_entity_id = ' || x_entity_id );
63 --DBMS_OUTPUT.PUT_LINE('x_dest_org_id = ' || x_dest_org_id );
64
65
66 outside_proc_sv.get_entity_defaults(x_entity_id,
67 x_dest_org_id, x_entity_name, x_entity_type );
68
69
70
71 --DBMS_OUTPUT.PUT_LINE('x_entity_name = ' || x_entity_name);
72 --DBMS_OUTPUT.PUT_LINE('x_entity_type = ' || x_entity_type);
73
74
75 RETURN;
76
77 EXCEPTION
78 WHEN OTHERS THEN
79 po_message_s.sql_error('test_get_entity_defaults', x_progress, sqlcode);
80 RAISE;
81
82
83 end test_get_entity_defaults;
84
85 /*===========================================================================
86
87 PROCEDURE NAME: get_wip_line_defaults()
88
89 ===========================================================================*/
90
91 PROCEDURE get_wip_line_defaults (x_line_id IN NUMBER,
92 x_dest_org_id IN NUMBER,
93 x_wip_line_code IN OUT NOCOPY VARCHAR2) IS
94
95
96
97
98 x_progress VARCHAR2(3) := NULL;
99
100
101 BEGIN
102
103 x_progress := 10;
104
105 SELECT line_code
106 INTO x_wip_line_code
107 FROM wip_lines
108 WHERE line_id = x_line_id
109 AND organization_id = x_dest_org_id;
110
111
112 RETURN;
113
114 EXCEPTION
115
116 WHEN NO_DATA_FOUND THEN
117 RETURN;
118
119 WHEN OTHERS THEN
120 po_message_s.sql_error('get_wip_line_defaults', x_progress, sqlcode);
121 RAISE;
122
123 END get_wip_line_defaults;
124
125
126 /*===========================================================================
127
128 PROCEDURE NAME: test_get_wip_line_defaults()
129
130 ===========================================================================*/
131
132 PROCEDURE test_get_wip_line_defaults (x_line_id IN NUMBER,
133 x_dest_org_id IN NUMBER) IS
134
135
136
137 x_progress VARCHAR2(3) := NULL;
138 x_wip_line_code VARCHAR2(15);
139
140
141 BEGIN
142
143
144 --DBMS_OUTPUT.PUT_LINE('x_line_id = ' || x_line_id );
145 --DBMS_OUTPUT.PUT_LINE('x_dest_org_id = ' || x_dest_org_id );
146
147
148 outside_proc_sv.get_wip_line_defaults(x_line_id,
149 x_dest_org_id, x_wip_line_code );
150
151
152
153 --DBMS_OUTPUT.PUT_LINE('x_wip_line_code = ' || x_wip_line_code);
154
155
156 RETURN;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 po_message_s.sql_error('test_get_wip_line_defaults', x_progress, sqlcode);
161 RAISE;
162
163
164 end test_get_wip_line_defaults;
165
166
167 /*===========================================================================
168
169 PROCEDURE NAME: get_operation_defaults()
170
171 ===========================================================================*/
172
173 PROCEDURE get_operation_defaults(x_wip_repetitive_schedule_id IN NUMBER,
174 x_wip_operation_seq_num IN NUMBER,
175 x_entity_id IN NUMBER,
176 x_dest_org_id IN NUMBER,
177 x_bom_department_code IN OUT NOCOPY VARCHAR2,
178 x_wip_operation_code IN OUT NOCOPY VARCHAR2) IS
179
180
181
182 x_progress VARCHAR2(3) := NULL;
183
184
185 BEGIN
186
187 x_progress := 10;
188
189
190 /* Bug 655523
191 Modified the following SQL for performance improvements
192 */
193
194 IF x_wip_repetitive_schedule_id is NULL THEN
195
196 SELECT department_code, operation_code
197 INTO x_bom_department_code, x_wip_operation_code
198 FROM wip_osp_operations_val_v
199 WHERE organization_id = x_dest_org_id
200 AND operation_seq_num = x_wip_operation_seq_num
201 AND wip_entity_id = x_entity_id
202 AND x_wip_repetitive_schedule_id is null;
203
204
205 ELSE
206
207 SELECT department_code, operation_code
208 INTO x_bom_department_code, x_wip_operation_code
209 FROM wip_osp_operations_val_v
210 WHERE organization_id = x_dest_org_id
211 AND operation_seq_num = x_wip_operation_seq_num
212 AND wip_entity_id = x_entity_id
213 AND x_wip_repetitive_schedule_id is not null
214 AND repetitive_schedule_id = x_wip_repetitive_schedule_id;
215
216
217 END IF;
218
219
220 RETURN;
221
222 EXCEPTION
223
224 WHEN NO_DATA_FOUND THEN
225 RETURN;
226
227 WHEN OTHERS THEN
228 po_message_s.sql_error('get_operation_defaults', x_progress, sqlcode);
229 RAISE;
230
231 END get_operation_defaults;
232
233
234 /*===========================================================================
235
236 PROCEDURE NAME: get_resource_defaults()
237
238 ===========================================================================*/
239
240 PROCEDURE get_resource_defaults(x_bom_resource_id IN NUMBER,
241 x_dest_org_id IN NUMBER,
242 x_bom_resource_code IN OUT NOCOPY VARCHAR2,
243 x_bom_resource_unit IN OUT NOCOPY VARCHAR2,
244 x_bom_cost_element_id IN OUT NOCOPY NUMBER) IS
245
246
247 x_progress VARCHAR2(3) := NULL;
248
249
250 BEGIN
251
252 x_progress := 10;
253
254 SELECT resource_code,
255 unit_of_measure,
256 cost_element_id
257 INTO x_bom_resource_code,
258 x_bom_resource_unit,
259 x_bom_cost_element_id
260 FROM bom_resources
261 WHERE resource_id = x_bom_resource_id
262 AND organization_id = x_dest_org_id;
263
264
265 RETURN;
266
267 EXCEPTION
268
269 WHEN NO_DATA_FOUND THEN
270 RETURN;
271
272 WHEN OTHERS THEN
273 po_message_s.sql_error('get_resource_defaults', x_progress, sqlcode);
274 RAISE;
275
276 END get_resource_defaults;
277
278
279
280 /*===========================================================================
281
282 PROCEDURE NAME: calc_assy_res_qty()
283
284 ===========================================================================*/
285
286 PROCEDURE calc_assy_res_qty(x_outside_op_uom_type IN VARCHAR2,
287 x_quantity_ordered IN NUMBER,
288 x_wip_repetitive_schedule_id IN NUMBER,
289 x_wip_operation_seq_num IN NUMBER,
290 x_wip_resource_seq_num IN NUMBER,
291 x_entity_id IN NUMBER,
292 x_dest_org_id IN NUMBER,
293 x_usage_rate_or_amount IN OUT NOCOPY NUMBER,
294 x_assembly_quantity IN OUT NOCOPY NUMBER,
295 x_resource_quantity IN OUT NOCOPY NUMBER) IS
296
297
298 x_progress VARCHAR2(3) := NULL;
299
300
301 BEGIN
302
303 x_progress := 10;
304
305 SELECT usage_rate_or_amount,
306 decode(x_outside_op_uom_type,
307 'ASSEMBLY',x_quantity_ordered,
308 'RESOURCE',x_quantity_ordered /
309 decode(wor.usage_rate_or_amount,
310 0,x_quantity_ordered,
311 wor.usage_rate_or_amount)
312 ),
313 decode(x_outside_op_uom_type,
314 'ASSEMBLY', x_quantity_ordered
315 * wor.usage_rate_or_amount,
316 'RESOURCE', x_quantity_ordered)
317 INTO x_usage_rate_or_amount,
318 x_assembly_quantity,
319 x_resource_quantity
320 FROM wip_operation_resources wor
321 WHERE wor.wip_entity_id = x_entity_id
322 AND nvl(wor.repetitive_schedule_id,-1) =
323 nvl(x_wip_repetitive_schedule_id,-1)
324 AND wor.operation_seq_num = x_wip_operation_seq_num
325 AND wor.resource_seq_num = x_wip_resource_seq_num
326 AND wor.organization_id = x_dest_org_id;
327
328
329 RETURN;
330
331 EXCEPTION
332
333 WHEN NO_DATA_FOUND THEN
334 RETURN;
335
336 WHEN OTHERS THEN
337 po_message_s.sql_error('calc_assy_res_qty', x_progress, sqlcode);
338 RAISE;
339
340 END calc_assy_res_qty;
341
342 /*===========================================================================
343
344 PROCEDURE NAME: get_unit_type()
345
346 ===========================================================================*/
347
348 PROCEDURE get_unit_type (x_op_uom_type_dsp IN VARCHAR2,
349 x_op_uom_type IN OUT NOCOPY VARCHAR2) IS
350
351
352
353 x_progress VARCHAR2(3) := NULL;
354
355
356 BEGIN
357
358 x_progress := 10;
359
360 SELECT displayed_field
361 INTO x_op_uom_type
362 FROM po_lookup_codes
363 WHERE lookup_type = 'OUTSIDE OPERATION UOM TYPE'
364 AND lookup_code = x_op_uom_type_dsp;
365
366 RETURN;
367
368 EXCEPTION
369
370 WHEN NO_DATA_FOUND THEN
371 RETURN;
372
373 WHEN OTHERS THEN
374 po_message_s.sql_error('get_unit_type', x_progress, sqlcode);
375 RAISE;
376
377 END get_unit_type;
378
379
380 /*===========================================================================
381
382 PROCEDURE NAME: test_get_unit_type()
383
384 ===========================================================================*/
385
386 PROCEDURE test_get_unit_type (x_op_uom_type_dsp IN VARCHAR2) IS
387
388
389
390
391 x_progress VARCHAR2(3) := NULL;
392 x_op_uom_type VARCHAR2(15);
393
394
395 BEGIN
396
397
398 --DBMS_OUTPUT.PUT_LINE('x_op_uom_type_dsp = ' || x_op_uom_type_dsp );
399
400
401 outside_proc_sv.get_unit_type(x_op_uom_type_dsp, x_op_uom_type );
402
403
404
405 --DBMS_OUTPUT.PUT_LINE('x_op_uom_type = ' || x_op_uom_type);
406
407
408 RETURN;
409
410 EXCEPTION
411 WHEN OTHERS THEN
412 po_message_s.sql_error('test_get_unit_type', x_progress, sqlcode);
413 RAISE;
414
415
416 end test_get_unit_type;
417
418
419 procedure get_project_task_num(x_project_id IN NUMBER,
420 x_task_id IN NUMBER,
421 x_project IN OUT NOCOPY VARCHAR2,
422 x_task IN OUT NOCOPY VARCHAR2) is
423 x_progress varchar2(3);
424 BEGIN
425
426 x_progress := '020';
427 if x_project_id is not null then
428 select project_number
429 into x_project
430 from pjm_projects_all_ou_v --< Bug 3265539 >
431 where project_id = x_project_id;
432 end if;
433
434 x_progress := '030';
435 if x_task_id is not null then
436 select task_number
437 into x_task
438 from pa_tasks --< Bug 3265539 >
439 where task_id = x_task_id and
440 project_id = x_project_id;
441 end if;
442
443 x_progress := '040';
444
445 EXCEPTION
446 WHEN OTHERS THEN
447 po_message_s.sql_error('get_project_task_num', x_progress, sqlcode);
448 END;
449
450
451 /*===========================================================================
452
453 FUNCTION NAME: prj_id_to_num()
454
455 ===========================================================================*/
456
457 FUNCTION prj_id_to_num (X_project_id IN NUMBER) RETURN VARCHAR2 IS
458
459
460 L_project_num varchar2(30);
461
462 cursor C1 is
463
464 select segment1
465 from pa_projects_all
466 where project_id = X_project_id
467 union
468 select project_number
469 from pjm_seiban_numbers
470 where project_id = X_project_id;
471
472 BEGIN
473
474 if X_project_id is null then
475 return null;
476 end if;
477
478 open C1;
479 fetch C1 into L_project_num;
480 close C1;
481
482 return L_project_num;
483
484 END prj_id_to_num;
485
486
487 END outside_proc_sv;
488