1 PACKAGE BODY WIP_PERF_TO_PLAN AS
2 /* $Header: wipbptpb.pls 115.9 2002/11/28 19:22:55 rmahidha ship $ */
3
4 PROCEDURE Load_Performance_Info(
5 errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY VARCHAR2,
7 p_date_from IN VARCHAR2,
8 p_date_to IN VARCHAR2) IS
9 x_errnum NUMBER;
10 x_errmesg VARCHAR2(240);
11 BEGIN
12 --dbms_output.put_line('Inside the Load_Performance_Info procedure.');
13 Populate_Performance(
17 p_applicationid => null,
14 p_date_from => p_date_from,
15 p_date_to => p_date_to,
16 p_userid => null,
18 p_errnum => x_errnum,
19 p_errmesg => x_errmesg);
20
21 --dbms_output.put_line('After the Populate_Performance Procedure');
22
23 errbuf := x_errmesg;
24 retcode := to_char(x_errnum);
25 EXCEPTION
26 WHEN OTHERS THEN
27 --dbms_output.put_line('Error in the Populate_Performance.');
28 retcode := 1;
29 null;
30 END Load_Performance_Info;
31
32 PROCEDURE Populate_Performance(
33 p_date_from IN VARCHAR2,
34 p_date_to IN VARCHAR2,
35 p_userid IN NUMBER,
36 p_applicationid IN NUMBER,
37 p_errmesg OUT NOCOPY VARCHAR2,
38 p_errnum OUT NOCOPY NUMBER)
39 IS
40 x_userid NUMBER;
41 x_applicationid NUMBER;
42
43 p_from_date DATE;
44 p_to_date DATE;
45
46 BEGIN
47 --dbms_output.put_line('Entered the Main_Populate_Performance procedure');
48
49 p_from_date := FND_DATE.canonical_to_date(p_date_from);
50 p_to_date := FND_DATE.canonical_to_date(p_date_to);
51
52 if p_userid is null then
53 x_userid := fnd_global.user_id;
54 else
55 x_userid := p_userid;
56 end if;
57
58 if p_applicationid is null then
59 x_applicationid := fnd_global.prog_appl_id;
60 else
61 x_applicationid := p_applicationid;
62 end if;
63
64 p_errnum := 0;
65 p_errmesg :='';
66
67 -- To Clean up the temporary data which can be caused by exception (6/14/98)
68 Clean_Up_Exception;
69
70 --dbms_output.put_line('Before Stage 1');
71 -- Populate Plan data and Who columns for the performance table
72 Populate_Who(
73 p_date_from => p_from_date,
74 p_date_to => p_to_date,
75 p_userid => x_userid,
76 p_applicationid => x_applicationid,
77 p_errnum => p_errnum,
78 p_errmesg => p_errmesg);
79
80 --Error in the called program
81 if (p_errnum <> 0) then
82 return;
83 end if;
84
85 --dbms_output.put_line('Before Stage 2');
86 -- Now get the actual performance data and the item cost
87 -- from discrete jobs, flow schedules and repetitive schedules
88 -- and update the performance table
89 Update_Actual_Quantity(
90 p_errnum => p_errnum,
91 p_errmesg => p_errmesg);
92
93 --Error in the called program
94 if (p_errnum <> 0) then
95 return;
96 end if;
97
98 --dbms_output.put_line('Initial Clean Up - Stage3');
99 --Delete old data in the performance table, and commit new populated data
100
101 Post_Populate_Perf_Info(
102 p_errnum => p_errnum,
103 p_errmesg => p_errmesg);
104
105 --Error in the called program
106 if (p_errnum <> 0) then
107 return;
108 end if;
109
110 return;
111
112 EXCEPTION
113 WHEN OTHERS THEN
114 --dbms_output.put_line(SQLCODE);
115 --dbms_output.put_line(SQLERRM);
116 p_errmesg := substr(SQLERRM, 1, 150);
117 p_errnum := SQLCODE;
118 Clean_Up_Exception;
119 return;
120 END Populate_Performance;
121
122 /*------------------------------------------------------------------------------
123 Populate Planning data and Who columns for the performance table
124 default actual_quantity = 0 and item_cost = 1
125 -----------------------------------------------------------------------------*/
126
127 PROCEDURE Populate_Who(
128 p_date_from IN DATE,
129 p_date_to IN DATE,
130 p_userid IN NUMBER,
131 p_applicationid IN NUMBER,
132 p_errmesg OUT NOCOPY VARCHAR2,
133 p_errnum OUT NOCOPY NUMBER)
134
135 IS
136 BEGIN
137 --dbms_output.put_line('Populate Plan data and Who columns');
138
139 p_errnum := 0;
140 p_errmesg :='';
141
142 -- LOCK TABLE wip_bis_perf_to_plan IN EXCLUSIVE MODE NOWAIT;
143
144 insert into wip_bis_perf_to_plan(
145 ORGANIZATION_ID,
146 INVENTORY_ITEM_ID,
147 SCHEDULE_DATE,
148 SCHEDULE_QUANTITY,
149 ACTUAL_QUANTITY,
150 ITEM_COST,
151 EXISTING_FLAG,
152 LAST_UPDATE_DATE,
153 LAST_UPDATED_BY,
154 CREATION_DATE,
155 CREATED_BY,
156 PROGRAM_APPLICATION_ID
157 )
158 (select mbppv.organization_id,
159 mbppv.inventory_item_id,
160 mbppv.schedule_date,
161 nvl(mbppv.schedule_quantity,0),
162 0,
163 0,
164 0,
165 sysdate,
166 p_userid,
167 sysdate,
168 p_userid,
169 p_applicationid
170 from mrp_bis_plan_prod_v mbppv
171 where trunc(mbppv.schedule_date) between trunc(nvl(p_date_from,mbppv.schedule_date))
172 and trunc(nvl(p_date_to,mbppv.schedule_date))
173 );
174
175 --dbms_output.put_line('Populate plan data and Who columns successfully.');
176 commit;
177 return;
178 EXCEPTION
179 WHEN OTHERS THEN
180 --dbms_output.put_line('Failed in Populate plan data and Who columns.');
181 --dbms_output.put_line(SQLCODE);
182 --dbms_output.put_line(SQLERRM);
183 p_errmesg := 'Failed in Populate plan data and Who columns: ' ||substr(SQLERRM, 1, 150);
184 p_errnum := SQLCODE;
188 END Populate_Who;
185 Clean_Up_Exception;
186 return;
187
189 /*------------------------------------------------------------------------------------
190 to update the actual_quantity,item_cost for performance table
191 ------------------------------------------------------------------------------------*/
192
193 PROCEDURE Update_Actual_Quantity(
194 p_errmesg OUT NOCOPY VARCHAR2,
195 p_errnum OUT NOCOPY NUMBER)
196 IS
197 sum_quantity NUMBER;
198 complete_quantity NUMBER;
199 p_item_cost NUMBER;
200
201 CURSOR perf_cur IS
202 SELECT organization_id,
203 inventory_item_id,
204 schedule_date,
205 actual_quantity,
206 item_cost
207 FROM wip_bis_perf_to_plan WHERE existing_flag = 0 FOR UPDATE;
208 perf_rec perf_cur%ROWTYPE;
209
210 BEGIN
211 p_errnum := 0;
212 p_errmesg :='';
213
214 --dbms_output.put_line('Start Update actual_quantity.');
215
216 OPEN perf_cur;
217 LOOP
218 FETCH perf_cur INTO perf_rec;
219 EXIT WHEN perf_cur%NOTFOUND or perf_cur%NOTFOUND IS NULL;
220
221 -- Update the item_cost based on table cst_item_costs, mtl_parameters
222 BEGIN
223 select distinct cic.item_cost into p_item_cost
224 from
225 cst_item_costs cic,
226 mtl_parameters mp
227 where mp.organization_id = perf_rec.organization_id
228 and cic.organization_id = mp.organization_id
229 and cic.inventory_item_id = perf_rec.inventory_item_id
230 and cic.cost_type_id = mp.primary_cost_method;
231
232
233 EXCEPTION
234 WHEN NO_DATA_FOUND THEN p_item_cost := 0;
235 END;
236
237
238 sum_quantity := 0;
239
240 -- For all the discrete jobs complete on schedule
241
242 BEGIN
243 select nvl(sum(mmt.primary_quantity),0) into complete_quantity
244 from
245 mtl_material_transactions mmt,
246 wip_entities we,
247 wip_discrete_jobs wdj
248
249 where mmt.transaction_source_type_id = 5
250 and mmt.transaction_action_id in (31,32)
251 and wdj.organization_id = perf_rec.organization_id
252 and wdj.primary_item_id = perf_rec.inventory_item_id
253 and mmt.organization_id = wdj.organization_id
254 and mmt.inventory_item_id = wdj.primary_item_id
255 and mmt.transaction_source_id = wdj.wip_entity_id
256 and we.wip_entity_id = wdj.wip_entity_id
257 and we.entity_type in (1,3)
258 and trunc(mmt.transaction_date) <= trunc(wdj.scheduled_completion_date)
259 and trunc(wdj.scheduled_completion_date) = trunc(perf_rec.schedule_date)
260
261 group by
262 mmt.inventory_item_id,
263 trunc(wdj.scheduled_completion_date);
264 EXCEPTION
265 WHEN NO_DATA_FOUND THEN complete_quantity := 0;
266 END;
267
268 sum_quantity := sum_quantity + complete_quantity;
269
270 -- for all the discrete jobs completed after the completion date
271 BEGIN
272 select nvl(sum(mmt.primary_quantity),0) into complete_quantity
273 from
274 mtl_material_transactions mmt,
275 wip_entities we,
276 wip_discrete_jobs wdj
277
278 where mmt.transaction_source_type_id = 5
279 and mmt.transaction_action_id in (31,32)
280 and wdj.organization_id = perf_rec.organization_id
281 and wdj.primary_item_id = perf_rec.inventory_item_id
282 and mmt.organization_id = wdj.organization_id
283 and mmt.inventory_item_id = wdj.primary_item_id
284 and mmt.transaction_source_id = wdj.wip_entity_id
285 and we.wip_entity_id = wdj.wip_entity_id
286 and we.entity_type in (1,3)
287 and trunc(mmt.transaction_date) > trunc(wdj.scheduled_completion_date)
288 and trunc(mmt.transaction_date) = trunc(perf_rec.schedule_date)
289
290 group by
291 mmt.inventory_item_id,
292 trunc(mmt.transaction_date);
293 EXCEPTION
294 WHEN NO_DATA_FOUND THEN complete_quantity := 0;
295 END;
296
297 sum_quantity := sum_quantity + complete_quantity;
298
299
300 -- For all the flow schedules complete on schedule
301 BEGIN
302 select nvl(sum(mmt.primary_quantity),0) into complete_quantity
303 from
304 mtl_material_transactions mmt,
305 wip_entities we,
306 wip_flow_schedules wfs
307 where mmt.transaction_source_type_id = 5
308 and mmt.transaction_action_id in (31,32)
309 and wfs.organization_id = perf_rec.organization_id
310 and wfs.primary_item_id = perf_rec.inventory_item_id
311 and mmt.organization_id = wfs.organization_id
312 and mmt.inventory_item_id = wfs.primary_item_id
313 and mmt.transaction_source_id = wfs.wip_entity_id
314 and we.wip_entity_id = wfs.wip_entity_id
315 and we.entity_type = 4
316 and trunc(mmt.transaction_date) <= trunc(wfs.scheduled_completion_date)
317 and trunc(wfs.scheduled_completion_date) = trunc(perf_rec.schedule_date)
318 group by
319 mmt.inventory_item_id,
320 trunc(wfs.scheduled_completion_date);
321 EXCEPTION
322 WHEN NO_DATA_FOUND THEN complete_quantity := 0;
323 END;
324
325 sum_quantity := sum_quantity + complete_quantity;
326
327 -- for all the flow schedules completed after the completion date
328 BEGIN
329 select nvl(sum(mmt.primary_quantity),0) into complete_quantity
330 from
331 mtl_material_transactions mmt,
332 wip_entities we,
333 wip_flow_schedules wfs
334
335 where mmt.transaction_source_type_id = 5
336 and mmt.transaction_action_id in (31,32)
337 and wfs.organization_id = perf_rec.organization_id
341 and mmt.transaction_source_id = wfs.wip_entity_id
338 and wfs.primary_item_id = perf_rec.inventory_item_id
339 and mmt.organization_id = wfs.organization_id
340 and mmt.inventory_item_id = wfs.primary_item_id
342 and we.wip_entity_id = wfs.wip_entity_id
343 and we.entity_type = 4
344 and trunc(mmt.transaction_date) > trunc(wfs.scheduled_completion_date)
345 and trunc(mmt.transaction_date) = trunc(perf_rec.schedule_date)
346
347 group by
348 mmt.inventory_item_id,
349 trunc(mmt.transaction_date);
350 EXCEPTION
351 WHEN NO_DATA_FOUND THEN complete_quantity := 0;
352 END;
353
354 sum_quantity := sum_quantity + complete_quantity;
355
356 -- For all the repetitive schedules completed on schedule
357 BEGIN
358 select nvl(sum(mmta.primary_quantity),0) into complete_quantity
359 from
360 wip_repetitive_schedules wrs,
361 wip_entities we,
362 mtl_material_transactions mmt,
363 mtl_material_txn_allocations mmta
364 where mmt.transaction_source_type_id = 5
365 and mmt.transaction_action_id in (31,32)
366 and wrs.organization_id = perf_rec.organization_id
367 and we.primary_item_id = perf_rec.inventory_item_id
368 and we.entity_type = 2
369 and we.wip_entity_id = wrs.wip_entity_id
370 and we.organization_id = wrs.organization_id
371 and mmta.organization_id = wrs.organization_id
372 and mmta.repetitive_schedule_id = wrs.repetitive_schedule_id
373 and mmt.organization_id = wrs.organization_id
374 and mmt.inventory_item_id = we.primary_item_id
375 and mmt.transaction_source_id = we.wip_entity_id
376 and mmt.transaction_id = mmta.transaction_id
377 and trunc(mmta.transaction_date) <= trunc(wrs.last_unit_completion_date)
378 and trunc(wrs.last_unit_completion_date) = trunc(perf_rec.schedule_date)
379 group by
380 mmt.inventory_item_id,
381 trunc(wrs.last_unit_completion_date);
382 EXCEPTION
383 WHEN NO_DATA_FOUND THEN complete_quantity := 0;
384 END;
385
386 sum_quantity := sum_quantity + complete_quantity;
387
388 -- for all the repetitive schedules completed after the completion date
389 BEGIN
390 select nvl(sum(mmta.primary_quantity),0) into complete_quantity
391 from
392 wip_repetitive_schedules wrs,
393 wip_entities we,
394 mtl_material_transactions mmt,
395 mtl_material_txn_allocations mmta
396 where mmt.transaction_source_type_id = 5
397 and mmt.transaction_action_id in (31,32)
398 and wrs.organization_id = perf_rec.organization_id
399 and we.primary_item_id = perf_rec.inventory_item_id
400 and we.entity_type = 2
401 and we.wip_entity_id = wrs.wip_entity_id
402 and we.organization_id = wrs.organization_id
403 and mmta.organization_id = wrs.organization_id
404 and mmta.repetitive_schedule_id = wrs.repetitive_schedule_id
405 and mmt.organization_id = wrs.organization_id
406 and mmt.inventory_item_id = we.primary_item_id
407 and mmt.transaction_source_id = we.wip_entity_id
408 and mmt.transaction_id = mmta.transaction_id
409 and trunc(mmta.transaction_date) > trunc(wrs.last_unit_completion_date)
410 and trunc(mmta.transaction_date) = trunc(perf_rec.schedule_date)
411 group by
412 mmt.inventory_item_id,
413 trunc(mmta.transaction_date);
414 EXCEPTION
415 WHEN NO_DATA_FOUND THEN complete_quantity := 0;
416 END;
417
418 sum_quantity := sum_quantity + complete_quantity;
419
420 UPDATE wip_bis_perf_to_plan
421 SET
422 actual_quantity = actual_quantity + sum_quantity,
423 item_cost = p_item_cost
424 WHERE CURRENT OF perf_cur;
425
426 END LOOP;
427
428 CLOSE perf_cur;
429
430 commit;
431 --dbms_output.put_line('Update actual_quantity successfully.');
432 return;
433 EXCEPTION
434 WHEN OTHERS THEN
435 --dbms_output.put_line('Failed in Update actual_quantity.');
436 --dbms_output.put_line(SQLCODE);
437 --dbms_output.put_line(SQLERRM);
438 p_errmesg := 'Failed in Update actual_quantity: ' ||substr(SQLERRM, 1, 150);
439 p_errnum := SQLCODE;
440 if perf_cur%ISOPEN then
441 --dbms_output.put_line('Close cursor when exception.');
442 CLOSE perf_cur;
443 end if;
444 Clean_Up_Exception;
445 return;
446 END Update_Actual_Quantity;
447
448
449 PROCEDURE Post_Populate_Perf_Info(
450 p_errnum OUT NOCOPY NUMBER,
451 p_errmesg OUT NOCOPY VARCHAR2)
452 IS
453
454 BEGIN
455
456 p_errnum := 0;
457 p_errmesg :='';
458
459 -- LOCK TABLE wip_bis_perf_to_plan IN EXCLUSIVE MODE ; -- NOWAIT;
460 --dbms_output.put_line('Delete all the old information from performance table.');
461 delete from wip_bis_perf_to_plan where existing_flag <> 0;
462
463 --dbms_output.put_line('Commit all the populated data');
464 update wip_bis_perf_to_plan set existing_flag = 1;
465
466 --dbms_output.put_line('all the populated data commited successfully.');
467
468 commit;
469 return;
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 --dbms_output.put_line('Failed in post populating performance table.');
474 --dbms_output.put_line(SQLCODE);
475 --dbms_output.put_line(SQLERRM);
476 p_errmesg := 'Failed in post updating performance table: ' ||substr(SQLERRM, 1, 150);
477 p_errnum := SQLCODE;
478 delete from wip_bis_perf_to_plan;
479 --dbms_output.put_line('All data deleted from wip_bis_perf_to_plan');
480 commit;
481 return;
482
483 --null;
484 End Post_Populate_Perf_Info;
485
486 PROCEDURE Clean_Up_Exception
487
488 IS
489 BEGIN
490
491
492 -- LOCK TABLE wip_bis_perf_to_plan IN EXCLUSIVE MODE NOWAIT;
493
494 -- Delete all the performance data not correctly populated
495 delete from wip_bis_perf_to_plan
496 where existing_flag = 0;
497
498 commit;
499 return;
500 EXCEPTION
501
502 WHEN OTHERS THEN
503 --dbms_output.put_line('Failed in cleaning up exception.');
504 --dbms_output.put_line(SQLCODE);
505 --dbms_output.put_line(SQLERRM);
506
507 delete from wip_bis_perf_to_plan;
508 --dbms_output.put_line('All data deleted from wip_bis_perf_to_plan');
509 commit;
510 return;
511
512 END Clean_Up_Exception;
513
514 END WIP_PERF_TO_PLAN;
[Home] [Help]