[Home] [Help]
PACKAGE BODY: APPS.WIP_PROD_INDICATORS
Source
1 PACKAGE BODY WIP_PROD_INDICATORS AS
2 /* $Header: wippindb.pls 120.1 2006/06/09 08:38:34 srayadur noship $ */
3
4
5 g_userid NUMBER;
6 g_applicationid NUMBER;
7 g_debug NUMBER; -- turning on debug mode
8 g_date_from DATE ;
9 g_date_to DATE;
10 g_uom_code mtl_units_of_measure.uom_code%type;
11 g_uom_class mtl_units_of_measure.uom_class%type;
12
13 /* Constants for session - including schema name for truncating and
14 collecting stats */
15 g_wip_schema VARCHAR2(30);
16 g_status VARCHAR2(30);
17 g_industry VARCHAR2(30);
18 WIP_CALL_LOG INTEGER; -- Bug 3624837
19 /* Wip Contants for identifying the type */
20 WIP_EFFICIENCY CONSTANT INTEGER := 1 ;
21 WIP_UTILIZATION CONSTANT INTEGER := 2 ;
22 WIP_YIELD CONSTANT INTEGER := 3 ;
23 WIP_PRODUCTIVITY CONSTANT INTEGER := 4 ;
24 WIP_RESOURCE_LOAD CONSTANT INTEGER := 5 ;
25
26
27 /*Wip Process Phase Constants */
28 WIP_DEPT_YIELD CONSTANT INTEGER := 2 ;
29 WIP_RES_YIELD CONSTANT INTEGER := 3 ;
30 WIP_UTZ_PHASE_ONE CONSTANT INTEGER := 1 ;
31 WIP_UTZ_PHASE_TWO CONSTANT INTEGER := 2 ;
32 WIP_EFF_PHASE_ONE CONSTANT INTEGER := 1 ;
33 WIP_EFF_PHASE_TWO CONSTANT INTEGER := 2 ;
34 WIP_EFF_PHASE_THREE CONSTANT INTEGER := 3 ;
35 WIP_EFF_PHASE_FOUR CONSTANT INTEGER := 4 ; -- bug 3280647
36 WIP_PROD_PHASE_ONE CONSTANT INTEGER := 1 ;
37 WIP_PROD_PHASE_TWO CONSTANT INTEGER := 2 ;
38 WIP_PROD_PHASE_THREE CONSTANT INTEGER := 3 ;
39 WIP_PROD_PHASE_FOUR CONSTANT INTEGER := 4 ;
40 WIP_RL_PHASE_ONE CONSTANT INTEGER := 1 ;
41 WIP_RL_PHASE_TWO CONSTANT INTEGER := 2 ;
42 WIP_RL_PHASE_THREE CONSTANT INTEGER := 3 ;
43
44
45
46 /* Private Procedures */
47 PROCEDURE Misc_Applied_Units(
48 p_group_id IN NUMBER,
49 p_organization_id IN NUMBER,
50 p_date_from IN DATE,
51 p_date_to IN DATE,
52 p_department_id IN NUMBER,
53 p_resource_id IN NUMBER,
54 p_errnum OUT NOCOPY NUMBER,
55 p_errmesg OUT NOCOPY VARCHAR2);
56
57
58 PROCEDURE Resource_Yield(
59 p_group_id IN NUMBER,
60 p_errnum OUT NOCOPY NUMBER,
61 p_errmesg OUT NOCOPY VARCHAR2);
62
63 PROCEDURE Move_Info_Into_Summary(
64 p_group_id IN NUMBER,
65 p_errnum OUT NOCOPY NUMBER,
66 p_errmesg OUT NOCOPY VARCHAR2);
67
68 PROCEDURE Move_Yield_Info(
69 p_group_id IN NUMBER,
70 p_errnum OUT NOCOPY NUMBER,
71 p_errmesg OUT NOCOPY VARCHAR2);
72
73 PROCEDURE Move_Utz_Info(
74 p_group_id IN NUMBER,
75 p_errnum OUT NOCOPY NUMBER,
76 p_errmesg OUT NOCOPY VARCHAR2);
77
78 PROCEDURE Post_Move_CleanUp(
79 p_group_id IN NUMBER,
80 p_errnum OUT NOCOPY NUMBER,
81 p_errmesg OUT NOCOPY VARCHAR2);
82
83 PROCEDURE Pre_Program_CleanUp(
84 p_errnum OUT NOCOPY NUMBER,
85 p_errmesg OUT NOCOPY VARCHAR2);
86
87 PROCEDURE Move_SFCB_Utz_Info(
88 p_group_id IN NUMBER,
89 p_organization_id IN NUMBER,
90 p_date_from IN DATE,
91 p_date_to IN DATE,
92 p_department_id IN NUMBER,
93 p_resource_id IN NUMBER,
94 p_userid IN NUMBER,
95 p_applicationid IN NUMBER,
96 p_errnum OUT NOCOPY NUMBER,
97 p_errmesg OUT NOCOPY VARCHAR2);
98
99 FUNCTION check_backup_needed
100 RETURN BOOLEAN;
101
102 PROCEDURE backup_summary_tables (
103 p_max_backup_date IN DATE,
104 p_errnum OUT NOCOPY NUMBER,
105 p_errmesg OUT NOCOPY VARCHAR2);
106
107
108 PROCEDURE update_existing_flag (
109 p_errnum OUT NOCOPY NUMBER,
110 p_errmesg OUT NOCOPY VARCHAR2);
111
112 PROCEDURE merge_previous_run_data (
113 p_errnum OUT NOCOPY NUMBER,
114 p_errmesg OUT NOCOPY VARCHAR2);
115
116
117 PROCEDURE clear_temp_summary_tables (
118 p_errnum OUT NOCOPY NUMBER,
119 p_errmesg OUT NOCOPY VARCHAR2);
120
121
122 PROCEDURE populate_temp_table (
123 p_table_name IN VARCHAR2,
124 p_indicator IN NUMBER,
125 p_group_id IN NUMBER);
126 --Added for Bug 3280647
127 PROCEDURE populate_eff_temp_table (
128 p_table_name IN VARCHAR2,
129 p_indicator IN NUMBER,
130 p_group_id IN NUMBER);
131
132 /* Exceptions to be used in this file
133 */
134 -- for some stage failure
135 collection_stage_failed EXCEPTION;
136 PRAGMA EXCEPTION_INIT (collection_stage_failed, -20000);
137
138
139 /* Load_Summary_Info
140 This is the encapsulation that gets called from
141 the concurrent program. Main wrapper for capture production indicators
142 program.
143 */
144
145 PROCEDURE Load_Summary_Info(
146 errbuf OUT NOCOPY VARCHAR2,
147 retcode OUT NOCOPY VARCHAR2,
148 p_date_from IN VARCHAR2,
149 p_date_to IN VARCHAR2) IS
150
151 x_errnum NUMBER;
152 x_errmesg VARCHAR2(240);
153 p_from_date DATE;
154 p_to_date DATE;
155 BEGIN
156 g_debug := 1;
157 WIP_CALL_LOG:=1; -- To differentiate b/w call of Capture production indicator and discrete workstation
158 p_from_date := FND_DATE.canonical_to_date(p_date_from);
159 p_to_date := FND_DATE.canonical_to_date(p_date_to);
160
161 Populate_Summary_Table(
162 p_group_id => null,
163 p_organization_id => null,
164 p_date_from => p_from_date,
165 p_date_to => p_to_date,
166 p_department_id => null,
167 p_resource_id => null,
168 p_userid => null,
169 p_applicationid => null,
170 p_errnum => x_errnum,
171 p_errmesg => x_errmesg ) ;
172
173 errbuf := x_errmesg ;
174 retcode := to_char(x_errnum);
175
176 EXCEPTION
177 WHEN OTHERS THEN
178 FND_FILE.PUT_LINE (fnd_file.log, 'Capture Production Indicators has terminated with an exception');
179 FND_FILE.PUT_LINE (fnd_file.log, SQLERRM);
180 rollback;
181 errbuf := SQLERRM;
182 retcode := to_char (-1);
183
184 END Load_Summary_Info ;
185
186
187 /*
188 Main wrapper for all the routines and logic in this file.
189 */
190 PROCEDURE Populate_Summary_Table (
191 p_group_id IN NUMBER,
192 p_organization_id IN NUMBER,
193 p_date_from IN DATE,
194 p_date_to IN DATE,
195 p_department_id IN NUMBER,
196 p_resource_id IN NUMBER,
197 p_userid IN NUMBER,
198 p_applicationid IN NUMBER,
199 p_errnum OUT NOCOPY NUMBER,
200 p_errmesg OUT NOCOPY VARCHAR2)
201
202 IS
203 x_group_id NUMBER;
204 x_userid NUMBER;
205 x_appl_id NUMBER;
206 x_date_from DATE;
207 x_date_to DATE;
208 x_mrp_debug VARCHAR2(2);
209 x_mrp_trace VARCHAR2(2);
210
211 -- Boolean flag to determine if old data needs to be backed up
212 l_backup_old_data BOOLEAN ;
213
214 -- exception in case some stage fails. This is now important because
215 -- of the backing up and merging at the end. If some stage after
216 -- the back up fails, empty out the summary tables, and restore
217 -- backup.
218
219 BEGIN
220 l_backup_old_data := TRUE;
221 /* get global session parameters */
222 IF NOT (fnd_installation.get_app_info(
223 'WIP', g_status, g_industry, g_wip_schema)) THEN
224
225 RAISE_APPLICATION_ERROR (-20000,
226 'Unable to get session information.');
227
228 END IF;
229
230
231 if p_userid is null then
232 -- This is an Error Condition
233 x_userid := fnd_global.user_id ;
234 else
235 x_userid := p_userid ;
236 end if;
237
238 IF p_group_id IS NULL THEN
239 select wip_indicators_temp_s.nextval into x_group_id
240 from sys.dual ;
241 ELSE
242 x_group_id := p_group_id ;
243 END IF;
244
245
246 if p_applicationid is null then
247 x_appl_id := fnd_global.prog_appl_id ;
248 else
249 x_appl_id := p_applicationid ;
250 end if;
251
252
253 g_userid := x_userid ;
254 g_applicationid := x_appl_id ;
255 p_errnum := 0;
256 p_errmesg := '';
257
258
259 IF p_date_from IS NULL THEN
260 begin
261
262 select trunc(min(calendar_date))
263 into g_date_from
264 from bom_calendar_dates ;
265
266 exception
267 when no_data_found then
268 g_date_from := sysdate ;
269 end ;
270
271 ELSE
272 g_date_from := p_date_from;
273
274 END IF;
275
276 IF p_date_to IS NULL THEN
277 begin
278
279 select trunc(max(calendar_date))
280 into g_date_to
281 from bom_calendar_dates ;
282
283 exception
284 when no_data_found then
285 g_date_to := sysdate ;
286 end ;
287
288 ELSE
289 g_date_to := p_date_to;
290 END IF;
291
292 x_date_from := g_date_from ;
293 x_date_to := g_date_to ;
294
295 -- always print debug messages to log.
296 g_debug := 1;
297
298 fnd_file.put_line (fnd_file.log,
299 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
300 fnd_file.put_line(fnd_file.log,'Commencing WIP Capture Production Indicators.');
301
302 -- Step 0: Determine if old data needs to be backed up
303 l_backup_old_data := check_backup_needed ();
304
305 -- Step 5: Backup of old data
306 if g_debug = 1 then
307 fnd_file.put_line (fnd_file.log,
308 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
309 fnd_file.put_line(fnd_file.log,'Previous Data Backup - Stage 5');
310 end if ;
311
312 -- Previously, the this program used to throw away all the existing
313 -- data in the tables:
314 -- WIP_BIS_PROD_INDICATORS
315 -- WIP_BIS_PROD_DEPT_YIELD
316 -- WIP_BIS_PROD_ASSY_YIELD
317 -- This has to be avoided now. So we are doing the following:
318 -- Each of these tables will be backed up into a temp table
319 -- The original tables will be truncated as before.
320 -- When the program is done, then the data in the temp tables
321 -- prior to the start date of this run will be merged with the
322 -- data in this run. Data in the backed up temp tables beyond the
323 -- start date of this run will be thrown away.
324 -- This data has already been denormalized.
325 IF (l_backup_old_data) THEN
326 backup_summary_tables (x_date_from, p_errnum, p_errmesg);
327 END IF;
328
329 -- RS: If backup fails then , don't raise collection_stage_failed as this
330 -- would truncate the summary tables completely. There might be a reason
331 -- for this to fail due to unforeseen database issues like tablespace
332 -- insufficiency, etc at the customer site. These issues are external and
333 -- can be handled separately.Once, they are corrected the collection we
334 -- should be able to re-run the request for the last request range alone
335 -- and no data should be lost in such cases. So, this wouldn't be handled
336 -- here but in the exception block of this procedure. Commented below.
337
338 --if(p_errnum <0 )then
339 -- raise collection_stage_failed;
340 --end if ;
341 commit;
342
343 ----dbms_output.put_line('Remove the Inconsitent Data - Preface');
344
345
346 -- Step 1: Clean up of work tables
347 if g_debug = 1 then
348 fnd_file.put_line (fnd_file.log,
349 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
350 fnd_file.put_line(fnd_file.log,'Initial Clean up - Stage 1');
351 end if ;
352 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
353 --dbms_output.put_line('Initial Clean Up - Stage 1');
354
355 -- Clear the main temp table, wip_indicators_temp
356 Delete_Temp_Info(p_group_id => x_group_id);
357 commit ;
358
359 -- Clean up all temp/working/base tables.
360 Pre_Program_CleanUp(p_errnum => p_errnum,
361 p_errmesg => p_errmesg);
362
363 commit ;
364
365 if(p_errnum <0 )then
366 raise collection_stage_failed;
367 end if ;
368
369 -- Step 2: Collection of efficiency data into temp table
370 if g_debug = 1 then
371 fnd_file.put_line (fnd_file.log,
372 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
373 fnd_file.put_line(fnd_file.log, 'Before Stage 2');
374 end if ;
375 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
376 --dbms_output.put_line('Before Stage 2');
377
378 -- Populate Efficiency data into wip_indicators_temp
379 Populate_Efficiency(
380 p_group_id => x_group_id,
381 p_organization_id => p_organization_id,
382 p_date_from => x_date_from,
383 p_date_to => x_date_to,
384 p_department_id => p_department_id,
385 p_resource_id => p_resource_id,
386 p_userid => x_userid,
387 p_applicationid => x_appl_id,
388 p_errnum => p_errnum,
389 p_errmesg => p_errmesg );
390 commit;
391
392 if(p_errnum <0 )then
393 raise collection_stage_failed;
394 end if ;
395
396 -- Step 3: collection of utilization data into temp table
397 if g_debug = 1 then
398 fnd_file.put_line (fnd_file.log,
399 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
400 fnd_file.put_line(fnd_file.log, 'Before Stage 3');
401 end if ;
402 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
403 --dbms_output.put_line('Before Stage 3');
404
405 -- Populate Utilization data into wip_indicators_temp
406 Populate_Utilization(
407 p_group_id => x_group_id,
408 p_organization_id => p_organization_id,
409 p_date_from => x_date_from,
410 p_date_to => x_date_to,
411 p_department_id => p_department_id,
412 p_resource_id => p_resource_id,
413 p_userid => x_userid,
414 p_applicationid => x_appl_id,
415 p_errnum => p_errnum,
416 p_errmesg => p_errmesg );
417 commit;
418
419 if(p_errnum <0 )then
420 raise collection_stage_failed;
421 end if ;
422
423
424 -- Step 4: Collection of Yield data into temp table
425 if g_debug = 1 then
426 fnd_file.put_line (fnd_file.log,
427 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
428 fnd_file.put_line(fnd_file.log, 'Before Stage 4');
429 end if ;
430 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
431 --dbms_output.put_line('Before Stage 4');
432
433 -- Populate Yield data into wip_indicators_temp
434 Populate_Yield(
435 p_group_id => x_group_id,
436 p_organization_id => p_organization_id,
437 p_date_from => x_date_from,
438 p_date_to => x_date_to,
439 p_department_id => p_department_id,
440 p_resource_id => p_resource_id,
441 p_userid => x_userid,
442 p_applicationid => x_appl_id,
443 p_errnum => p_errnum,
444 p_errmesg => p_errmesg );
445 commit;
446
447 if(p_errnum <0 )then
448 raise collection_stage_failed;
449 end if ;
450
451 -- Step 5: Populate efficiency, resource, utilization data into
452 -- summary table
453 if g_debug = 1 then
454 fnd_file.put_line (fnd_file.log,
455 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
456 fnd_file.put_line(fnd_file.log, 'Before Stage 5');
457 end if ;
458 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
459 --dbms_output.put_line('Before Stage 5');
460
461 -- Move the collected efficiency, utilization and yield data into
462 -- the summary table wip_bis_prod_indicators
463 Move_Info_Into_Summary(
464 p_group_id => x_group_id,
465 p_errnum => p_errnum,
466 p_errmesg => p_errmesg );
467 commit;
468
469 if(p_errnum <0 )then
470 raise collection_stage_failed;
471 end if ;
472
473 -- Step 6: Move the resource yield information into its
474 -- summary table.
475 if g_debug = 1 then
476 fnd_file.put_line (fnd_file.log,
477 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
478 fnd_file.put_line(fnd_file.log, 'Before Stage 6');
479 end if ;
480 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
481 --dbms_output.put_line('Before Stage 6');
482
483 -- Move the yield information for each department into the
484 -- the summary table wip_bis_prod_dept_yield
485 Move_Yield_Info(
486 p_group_id => x_group_id,
487 p_errnum => p_errnum,
488 p_errmesg => p_errmesg );
489 commit;
490
491 if(p_errnum <0 )then
492 raise collection_stage_failed;
493 end if ;
494
495 -- Stage 7: MIA
496
497 -- Stage 8: Collect and populate the assembly yield data into
498 -- summary table
499 if g_debug = 1 then
500 fnd_file.put_line (fnd_file.log,
501 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
502 fnd_file.put_line(fnd_file.log, 'Before Stage 8');
503 end if ;
504 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
505 --dbms_output.put_line('Before Stage 8');
506
507 -- Populate Assembly yield data directly into WIP_BIS_PROD_ASSY_YIELD.
508 -- No staging table required in this step.
509 Populate_Assy_Yield(
510 p_organization_id => p_organization_id,
511 p_date_from => x_date_from,
512 p_date_to => x_date_to,
513 p_userid => x_userid,
514 p_applicationid => x_appl_id,
515 p_errnum => p_errnum,
516 p_errmesg => p_errmesg );
517 commit;
518
519 if(p_errnum <0 )then
520 raise collection_stage_failed;
521 end if ;
522
523 -- Stage 9: Clean up all temp tables since all data has been
524 -- transferred to summary tables.
525 if g_debug = 1 then
526 fnd_file.put_line (fnd_file.log,
527 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
528 fnd_file.put_line(fnd_file.log, 'Before Stage 9');
529 end if ;
530 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
531 --dbms_output.put_line('Before Stage 9');
532
533 -- Clean up all the work tables. Simply truncate them all.
534 Post_Move_CleanUp(p_group_id => x_group_id,
535 p_errnum => p_errnum,
536 p_errmesg => p_errmesg );
537 commit;
538
539 if(p_errnum <0 )then
540 raise collection_stage_failed;
541 end if ;
542
543
544 -- Stage 10: Denormalize the organization, item, time and
545 -- geographical location info in the summary tables.
546 if g_debug = 1 then
547 fnd_file.put_line (fnd_file.log,
548 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
549 fnd_file.put_line(fnd_file.log, 'Before Stage 10');
550 end if ;
551 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
552 --dbms_output.put_line('Before Stage 10');
553
554 -- Populate the denormalized information into the summary tables:
555 -- 1. WIP_BIS_PROD_INDICATORS
556 -- 2. WIP_BIS_PROD_ASSY_YIELD
557 -- 3. WIP_BIS_PROD_DEPT_YIELD
558 Populate_Denormalize_Data(p_errnum => p_errnum,
559 p_errmesg => p_errmesg );
560 commit;
561
562 if(p_errnum <0 )then
563 raise collection_stage_failed;
564 end if ;
565
566 -- Stage: 10.5
567 -- Update existing flag to 1 for all new rows because
568 -- that is the filtering criterion for wip_bis_prod_assy_yield_v
569 -- and wip_bis_prod_dept_yield_v.
570 -- Bugfix 3387800.
571
572 if g_debug = 1 then
573 fnd_file.put_line (fnd_file.log,
574 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
575 fnd_file.put_line(fnd_file.log, 'Before Stage 10.5');
576 end if ;
577
578 update_existing_flag (p_errnum => p_errnum,
579 p_errmesg => p_errmesg);
580
581 commit;
582
583 if(p_errnum <0 )then
584 raise collection_stage_failed;
585 end if ;
586
587 -- Stage 11: Merge back data from previous runs that was not
588 -- recollected. This was not part of the original functionality
589 -- of the program, but was added later.
590 if g_debug = 1 then
591 fnd_file.put_line (fnd_file.log,
592 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
593 fnd_file.put_line(fnd_file.log, 'Before Stage 11');
594 end if ;
595 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
596 --dbms_output.put_line('Before Stage 11');
597
598 -- Merge already collected data that was backed up in temp tables,
599 -- for dates that did not lie in the specified date range for
600 -- this run. Only merge if old data has been backed up
601 IF (l_backup_old_data) THEN
602 merge_previous_run_data (p_errnum, p_errmesg);
603 END IF;
604
605 -- RS: If merge fails then , we would truncate the summary tables which
606 -- would have data for the present collection range and terminate the request.
607 -- The user may run the collection again after fixing the issue.
608 -- if(p_errnum <0 )then
609 -- raise collection_stage_failed;
610 -- end if ;
611 commit;
612
613 -- Stage 12: Delete the temp staging table, wip_indicators_temp.
614 if g_debug = 1 then
615 fnd_file.put_line(fnd_file.log, 'Before Stage 12');
616 fnd_file.put_line (fnd_file.log,
617 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
618 end if ;
619 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
620 --dbms_output.put_line('Before Stage 12');
621
622 -- Delete any existing data with this group ID
623 Delete_Temp_Info(p_group_id => x_group_id);
624 commit ;
625
626 fnd_file.put_line (fnd_file.log,
627 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
628 fnd_file.put_line (fnd_file.log,
629 'Capture Production indicators has terminated successfully.');
630 fnd_file.put_line (fnd_file.log,
631 'Dates collected: ' ||
632 to_char (x_date_from, 'DD-MON-YYYY') || ' to ' ||
633 to_char (x_date_to, 'DD-MON-YYYY'));
634 p_errnum := 0;
635 p_errmesg := '';
636
637 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
638 --dbms_output.put_line ('Done');
639 return ;
640
641 Exception
642
643 when collection_stage_failed then
644
645 if g_debug = 1 then
646 fnd_file.put_line(fnd_file.log, SQLCODE);
647 fnd_file.put_line(fnd_file.log,SQLERRM);
648 fnd_file.put_line (fnd_file.log,
649 'A collection stage has failed. ' ||
650 'All data starting from ' || x_date_from ||
651 ' is being purged and must be recollected.'
652 );
653 end if ;
654
655 -- truncate the 3 summary tables
656 execute immediate 'truncate table ' || g_wip_schema ||
657 '.WIP_BIS_PROD_INDICATORS';
658
659 execute immediate 'truncate table ' || g_wip_schema ||
660 '.WIP_BIS_PROD_DEPT_YIELD';
661
662 execute immediate 'truncate table ' || g_wip_schema ||
663 '.WIP_BIS_PROD_ASSY_YIELD';
664 -- keep data that is not part if this collection's date range
665 merge_previous_run_data (p_errnum, p_errmesg);
666 commit ;
667
668 -- clean up all the collected/staged data
669 Delete_Temp_Info(p_group_id =>x_group_id);
670 Post_Move_CleanUp(p_group_id => x_group_id,
671 p_errnum => p_errnum,
672 p_errmesg => p_errmesg );
673 commit ;
674
675
676 p_errnum := -1 ;
677 p_errmesg := substr(SQLERRM,1,150);
678
679 return ;
680
681 when others then
682
683 if g_debug = 1 then
684 fnd_file.put_line(fnd_file.log, SQLCODE);
685 fnd_file.put_line(fnd_file.log, SQLERRM);
686 fnd_file.put_line(fnd_file.log,'Clean up of collected/staged data');
687 end if ;
688 --dbms_output.put_line(SQLCODE);
689 --dbms_output.put_line(SQLERRM);
690
691
692 -- clean up all the collected/staged data
693 Delete_Temp_Info(p_group_id =>x_group_id);
694 Post_Move_CleanUp(p_group_id => x_group_id,
695 p_errnum => p_errnum,
696 p_errmesg => p_errmesg );
697 commit;
698
699 p_errnum := -1;
700 p_errmesg := substr(SQLERRM,1,150);
701
702 return ;
703
704 End Populate_Summary_Table ;
705
706
707
708 /* Populate_Efficiency
709 Procedure that populates the efficiency information into
710 the temp table wip_indicators_temp
711 */
712
713 PROCEDURE Populate_Efficiency(
714 p_group_id IN NUMBER,
715 p_organization_id IN NUMBER,
716 p_date_from IN DATE,
717 p_date_to IN DATE,
718 p_department_id IN NUMBER,
719 p_resource_id IN NUMBER,
720 p_userid IN NUMBER,
721 p_applicationid IN NUMBER,
722 p_errnum OUT NOCOPY NUMBER,
723 p_errmesg OUT NOCOPY VARCHAR2)
724
725 IS
726 x_group_id NUMBER;
727 x_phase VARCHAR2(10) ;
728 x_date_from DATE;
729 x_date_to DATE;
730 x_userid NUMBER;
731 x_appl_id NUMBER;
732 BEGIN
733
734 /* As the entry point for this more than a single
735 point we have to do the validation in here as
736 well. Ex :
737 Concurrent Program
738 SFCB
739 */
740 IF NOT (fnd_installation.get_app_info(
741 'WIP', g_status, g_industry, g_wip_schema)) THEN
742
743 RAISE_APPLICATION_ERROR (-20000,
744 'Unable to get session information.');
745
746 END IF;
747 if p_userid is null then
748 -- This is an Error Condition
749 x_userid := fnd_global.user_id ;
750 else
751 x_userid := p_userid ;
752 end if;
753
754
755 IF p_group_id IS NULL THEN
756 select wip_indicators_temp_s.nextval into x_group_id
757 from sys.dual ;
758 ELSE
759 x_group_id := p_group_id ;
760 END IF;
761
762 if p_applicationid is null then
763 -- This is an Error Condition
764 x_appl_id := fnd_global.prog_appl_id ;
765 else
766 x_appl_id := p_applicationid ;
767 end if;
768
769
770 g_userid := x_userid ;
771 g_applicationid := x_appl_id ;
772
773
774 IF p_date_from IS NULL THEN
775 begin
776
777 select trunc(min(calendar_date))
778 into g_date_from
779 from bom_calendar_dates ;
780
781 exception
782 when no_data_found then
783 g_date_from := sysdate ;
784 end ;
785
786 ELSE
787 g_date_from := p_date_from;
788
789 END IF;
790
791 IF p_date_to IS NULL THEN
792 begin
793
794 select trunc(max(calendar_date))
795 into g_date_to
796 from bom_calendar_dates ;
797
798 exception
799 when no_data_found then
800 g_date_to := sysdate ;
801 end ;
802
803 ELSE
804 g_date_to := p_date_to;
805 END IF;
806
807 x_date_from := g_date_from ;
808 x_date_to := g_date_to ;
809
810 -- Phase I: Calculate Standard Quantities
811 x_phase := 'I';
812 if g_debug = 1 then
813 fnd_file.put_line(fnd_file.log, 'Before Stage 2 Phase I');
814 end if ;
815 ----dbms_output.put_line('Before Stage 2 Phase I');
816
817 -- Calculate the Standard Quantities for the various departments
818 Calculate_Std_Quantity(
819 p_group_id => x_group_id,
820 p_organization_id => p_organization_id ,
821 p_date_from => x_date_from,
822 p_date_to => x_date_to ,
823 p_department_id => p_department_id,
824 p_indicator => WIP_EFFICIENCY);
825 commit ;
826
827 -- Phase II: Calculate Std Units
828 x_phase := 'II' ;
829 if g_debug = 1 then
830 fnd_file.put_line(fnd_file.log, 'Before Stage 2 Phase II');
831 end if ;
832 ----dbms_output.put_line('Before Stage 2 Phase II');
833
834 -- Calculate the standard units for the various resources in the
835 -- departments
836 Calculate_Std_Units(
837 p_group_id => x_group_id,
838 p_resource_id => p_resource_id,
839 p_errnum => p_errnum,
840 p_errmesg => p_errmesg,
841 p_indicator => WIP_EFFICIENCY);
842 commit;
843
844 -- Phase III: Calculate efficiency applied units
845 x_phase := 'III';
846 if g_debug = 1 then
847 fnd_file.put_line(fnd_file.log, 'Before Stage 2 Phase III');
848 end if ;
849
850 -- Calculate the Efficiency applied units.
851 -- Technically this function should not being doing anything
852 -- right now, because there are no rows in WIT with
853 -- WIP_EFF_PHASE_THREE (see function for details).
854 calc_Eff_Applied_Units(
855 p_group_id => x_group_id,
856 p_errnum => p_errnum,
857 p_errmesg => p_errmesg);
858 commit ;
859
860 -- Phase IV: Calculate miscellaneous applied units.
861 x_phase := 'IV';
862 if g_debug = 1 then
863 fnd_file.put_line(fnd_file.log, 'Before Stage 2 Phase IV');
864 end if ;
865 ----dbms_output.put_line('Before Stage 2 Phase IV');
866
867 /*
868 Richard's insight - the bug with efficiency
869 calculation */
870
871 -- Take into account the miscellaneous organizations
872 Misc_Applied_Units(
873 p_group_id => x_group_id,
874 p_organization_id => p_organization_id,
875 p_date_from => x_date_from,
876 p_date_to => x_date_to,
877 p_department_id => p_department_id,
878 p_resource_id => p_resource_id,
879 p_errnum => p_errnum,
880 p_errmesg => p_errmesg );
881 commit ;
882
883 -- gather stats on table to allow index access
884 If nvl(WIP_CALL_LOG,-1) =1 then
885 fnd_stats.gather_table_stats (g_wip_schema, 'WIP_INDICATORS_TEMP',
886 cascade => true);
887 End If;
888 -- all successful
889 p_errnum := 0 ;
890 p_errmesg := '';
891 return ;
892
893 Exception
894
895 WHEN OTHERS THEN
896
897 if g_debug = 1 then
898 fnd_file.put_line(fnd_file.log,'Failed in Stage 2 phase : '||x_phase);
899 fnd_file.put_line(fnd_file.log, to_char(SQLCODE));
900 fnd_file.put_line(fnd_file.log,SQLERRM);
901 end if ;
902
903 -- to make sure there is no garbage returned to SFCB,
904 -- truncate wip_indicators_temp
905 Delete_Temp_Info (p_group_id => x_group_id);
906
907 ----dbms_output.put_line('Failed in Stage 2 phase : '||x_phase);
908 ----dbms_output.put_line(SQLCODE);
909 ----dbms_output.put_line(SQLERRM);
910 p_errnum := -1 ;
911 p_errmesg := 'Failed in Stage 2 Phase : '||x_phase||substr(SQLERRM,1,125);
912
913 -- returns to populate_summary_table, so don't raise exception.
914
915 commit ;
916 return ;
917
918 END Populate_Efficiency;
919
920
921 /* Populate_Utilization
922 Procedure to populate utilization information into the
923 temp table, wip_indicators_temp
924 */
925 PROCEDURE Populate_Utilization (
926 p_group_id IN NUMBER,
927 p_organization_id IN NUMBER,
928 p_date_from IN DATE,
929 p_date_to IN DATE,
930 p_department_id IN NUMBER,
931 p_resource_id IN NUMBER,
932 p_userid IN NUMBER,
933 p_applicationid IN NUMBER,
934 p_errnum OUT NOCOPY NUMBER,
935 p_errmesg OUT NOCOPY VARCHAR2,
936 p_sfcb IN NUMBER DEFAULT NULL )
937 IS
938
939 -- Cursor to get all
940 CURSOR All_Orgs IS
941 SELECT DISTINCT organization_id
942 FROM mtl_parameters
943 WHERE process_enabled_flag <> 'Y'; -- Added to exclude process orgs after R12 uptake
944
945
946 x_date_from DATE;
947 x_date_to DATE;
948 x_group_id NUMBER;
949 x_phase VARCHAR2(10);
950 x_userid NUMBER;
951 x_appl_id NUMBER;
952 x_org_id NUMBER ;
953
954 BEGIN
955 x_org_id := 0 ;
956 /* As the entry point for this more than a single
957 point we have to do the validation in here as
958 well. Ex :
959 Concurrent Program
960 SFCB
961 */
962 IF NOT (fnd_installation.get_app_info(
963 'WIP', g_status, g_industry, g_wip_schema)) THEN
964
965 RAISE_APPLICATION_ERROR (-20000,
966 'Unable to get session information.');
967
968 END IF;
969 if p_userid is null then
970 -- This is an Error Condition
971 x_userid := fnd_global.user_id ;
972 else
973 x_userid := p_userid ;
974 end if;
975
976 IF p_group_id IS NULL THEN
977 select wip_indicators_temp_s.nextval into x_group_id
978 from sys.dual ;
979 ELSE
980 x_group_id := p_group_id ;
981 END IF;
982
983 if p_applicationid is null then
984 -- This is an Error Condition
985 x_appl_id := fnd_global.prog_appl_id ;
986 else
987 x_appl_id := p_applicationid ;
988 end if;
989
990
991 g_userid := x_userid ;
992 g_applicationid := x_appl_id ;
993
994 -- Get the UOM code from the profile
995
996 g_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
997 select uom_class
998 into g_uom_class
999 from mtl_units_of_measure
1000 where uom_code = g_uom_code;
1001
1002 --dbms_output.put_line(g_uom_code);
1003
1004 -- Set up the date ranges if needed
1005 /* For performance reasons should be just use the
1006 minimum and maximum date from efficiency */
1007
1008 IF p_date_from IS NULL THEN
1009 begin
1010
1011 select trunc(min(calendar_date))
1012 into g_date_from
1013 from bom_calendar_dates ;
1014
1015 exception
1016 when no_data_found then
1017 g_date_from := sysdate ;
1018 end ;
1019
1020 ELSE
1021 g_date_from := p_date_from;
1022 END IF;
1023
1024 IF p_date_to IS NULL THEN
1025 begin
1026
1027 select trunc(max(calendar_date))
1028 into g_date_to
1029 from bom_calendar_dates ;
1030
1031 exception
1032 when no_data_found then
1033 g_date_to := sysdate ;
1034 end ;
1035
1036 ELSE
1037 g_date_to := p_date_to;
1038 END IF;
1039
1040 x_date_from := g_date_from ;
1041 x_date_to := g_date_to ;
1042
1043 --dbms_output.put_line(x_date_from);
1044 --dbms_output.put_line(x_date_to);
1045
1046 -- For each one of the organizations
1047
1048 -- Phase I: calculate the resource availability for each org
1049 x_phase := 'I';
1050 if g_debug = 1 then
1051 fnd_file.put_line(fnd_file.log, 'Before Stage 3 Phase I');
1052 end if ;
1053 --dbms_output.put_line('Before Stage 3 Phase I');
1054
1055 -- Populate the fresh resource availability data into the
1056 -- mrp_net_resource_avail table.
1057 -- Calculate_Resource_Avail will call an MRP API to do this job
1058 -- per org.
1059 -- Note that in calc_resource_avail, we pass in NULL as the
1060 -- argument to the arg_simulation_set paramter to the MRP API.
1061 -- This is important because in the various joins to MNRA in this
1062 -- program, it seems the simulation_set = NULL filter is placed in
1063 -- some and not in others. However, the simulation set will be the
1064 -- same as the value of the arg_simulation_set passed into the MRP
1065 -- API (which is NULL). Therefore, the simulation_set = NULL filter
1066 -- is relevant across the board in all queries of this program.
1067 --
1068 -- This is not required for all the organizations,
1069 -- if just one organization is passed.
1070
1071 IF (p_organization_id is null ) THEN
1072
1073 FOR Org_Rec IN All_Orgs LOOP
1074
1075 x_org_id := Org_Rec.organization_id ;
1076
1077 Calculate_Resource_Avail(
1078 p_organization_id => x_org_id,
1079 p_date_from => trunc (x_date_from),
1080 p_date_to => trunc (x_date_to),
1081 p_department_id => p_department_id,
1082 p_resource_id => p_resource_id,
1083 p_errnum => p_errnum,
1084 p_errmesg => p_errmesg);
1085
1086 END LOOP ;
1087
1088 ELSE
1089 Calculate_Resource_Avail(
1090 p_organization_id => p_organization_id,
1091 p_date_from => trunc (x_date_from),
1092 p_date_to => trunc (x_date_to),
1093 p_department_id => p_department_id,
1094 p_resource_id => p_resource_id,
1095 p_errnum => p_errnum,
1096 p_errmesg => p_errmesg);
1097 END IF ;
1098
1099 -- Phase II: Pre-aggregate resource availability info
1100 -- for quick access in later SQLs since MNRA is a big table.
1101 x_phase := 'II';
1102 if g_debug = 1 then
1103 fnd_file.put_line(fnd_file.log, 'Before Stage 3 Phase II');
1104 end if ;
1105 --dbms_output.put_line('Before Stage 3 Phase II');
1106
1107 -- Pre-aggregate all the information needed from
1108 -- mrp_net_resource_avail and group the data by the attributes
1109 -- needed by the rest of the program. MNRA is a large table, so
1110 -- do this once, and then use this temp table to get values from.
1111 -- We want to pick only the rows that are referenced by the
1112 -- simulation_set = NULL condition, because this is what we specified
1113 -- the simulation_set to be when calling the MRP API.
1114 -- What this also means is that (shift_date, resource_id,
1115 -- department_id, organization_id, simulation_set) form a primary
1116 -- key for wip_bis_mnra_temp.
1117 if nvl(WIP_CALL_LOG,-1) =1 then -- Bug 3624837 If clause is added as Else code alone can hit performace for Capture Request
1118 INSERT INTO wip_bis_mnra_temp (
1119 shift_date,
1120 resource_id,
1121 department_id,
1122 organization_id,
1123 --simulation_set,
1124 available_hours
1125 )
1126 SELECT
1127 trunc (shift_date),
1128 resource_id,
1129 department_id,
1130 organization_id,
1131 --simulation_set, -- Not used after that --3779182
1132 --sum(((to_time-from_time)/3600)*capacity_units) --BUG - 3581581
1133 -- sum(((decode(sign(to_time - from_time),
1134 -- -1, ( 86400 - from_time ) + to_time,
1135 -- 1, ( to_time - from_time ) ,
1136 -- 0 ))/3600)*capacity_units)
1137 decode(sum(shift_num),
1138 0, nvl(sum(capacity_units)*24,0),
1139 nvl(sum(((decode(sign(to_time - from_time),
1140 -1, ( 86400 - from_time ) + to_time,
1141 1, ( to_time - from_time ) ,
1142 0 ))/3600)*capacity_units),0))
1143 FROM mrp_net_resource_avail
1144 WHERE simulation_set IS NULL
1145 and shift_date BETWEEN x_date_from AND (x_date_to + 0.99999)
1146 GROUP BY
1147 trunc (shift_date),
1148 resource_id,
1149 department_id,
1150 organization_id;--,
1151 --simulation_set
1152
1153 commit;
1154 Else -- This code will work only for discrete Workstation
1155 INSERT INTO wip_bis_mnra_temp (
1156 shift_date,
1157 resource_id,
1158 department_id,
1159 organization_id,
1160 -- simulation_set,
1161 available_hours
1162 )
1163 SELECT
1164 trunc (shift_date),
1165 resource_id,
1166 department_id,
1167 organization_id,
1168 -- simulation_set,
1169 --sum(((to_time-from_time)/3600)*capacity_units) --BUG - 3581581
1170 -- sum(((decode(sign(to_time - from_time),
1171 -- -1, ( 86400 - from_time ) + to_time,
1172 -- 1, ( to_time - from_time ) ,
1173 -- 0 ))/3600)*capacity_units)
1174 decode(sum(shift_num),
1175 0, nvl(sum(capacity_units)*24,0),
1176 nvl(sum(((decode(sign(to_time - from_time),
1177 -1, ( 86400 - from_time ) + to_time,
1178 1, ( to_time - from_time ) ,
1179 0 ))/3600)*capacity_units),0))
1180 FROM mrp_net_resource_avail mrp_outer
1181 WHERE simulation_set IS NULL
1182 and mrp_outer.shift_date BETWEEN x_date_from AND (x_date_to + 0.99999) --3779182
1183 AND mrp_outer.resource_id = nvl(p_resource_id, mrp_outer.resource_id)
1184 AND mrp_outer.department_id = nvl(p_department_id, mrp_outer.department_id)
1185 AND mrp_outer.organization_id = nvl(p_organization_id, mrp_outer.organization_id)
1186 and not exists
1187 ( select null
1188 from wip_bis_mnra_temp mrp_inner
1189 where mrp_outer.shift_date between trunc (mrp_inner.shift_date) and( trunc (mrp_inner.shift_date)+ 0.99999)
1190 and mrp_outer.resource_id= mrp_inner.resource_id
1191 and mrp_outer.department_id= mrp_inner.department_id
1192 and mrp_outer.organization_id = mrp_inner.organization_id
1193 )
1194 GROUP BY
1195 trunc (shift_date),
1196 resource_id,
1197 department_id,
1198 organization_id; --,
1199 -- simulation_set
1200
1201 commit;
1202 end if;
1203 -- Phase III: Insert all the utilization data into the temp table.
1204 x_phase := 'III';
1205 if g_debug = 1 then
1206 fnd_file.put_line(fnd_file.log, 'Before Stage 3 Phase III');
1207 end if ;
1208 --dbms_output.put_line('Before Stage 3 Phase III');
1209
1210 -- Insert all the data into the wip_indicators_temp table.
1211 -- This requires finding all the relevant records in
1212 -- in the OLTP tables and the available_units from the
1213 -- wip_bis_mnra_temp table. Null available_units are set to 0 in
1214 -- tune with the previous logic of the program.
1215 --
1216 -- This SQL now merges two old SQLs. The first used to insert to
1217 -- wip_indicators_temp and the second used to update EVERY row
1218 -- to have a process_phase of WIP_UTZ_PHASE_TWO and available_units
1219 -- from MNRA. Because of the merging, the reference to
1220 -- WIP_UTZ_PHASE_ONE in the insert step is now gone.
1221 --
1222 -- Note the that the inner group by makes org, dept, resource and
1223 -- and transaction_date a primary key for the UTILIZATION data.
1224 -- This is impt. when considering the logic in Move_Utz_info.
1225 INSERT INTO wip_indicators_temp(
1226 group_id,
1227 organization_id,
1228 department_id,
1229 department_code,
1230 resource_id,
1231 resource_code,
1232 wip_entity_id,-- added for bug 3604065
1233 operation_seq_num, -- bug 3662056
1234 applied_units_utz,
1235 available_units,
1236 transaction_date,
1237 indicator_type,
1238 process_phase,
1239 last_update_date,
1240 last_updated_by,
1241 creation_date,
1242 created_by,
1243 program_application_id)
1244 SELECT
1245 x_group_id group_id,
1246 utz_rows.organization_id,
1247 utz_rows.department_id,
1248 utz_rows.department_code,
1249 utz_rows.resource_id,
1250 utz_rows.resource_code,
1251 utz_rows.wip_entity_id, -- Bug 3604065
1252 utz_rows.operation_seq_num, --bug 3662056
1253 utz_rows.applied_units_utz,
1254 nvl (mnra_temp.available_hours, 0) available_units,
1255 utz_rows.transaction_date,
1256 WIP_UTILIZATION indicator_type,
1257 WIP_UTZ_PHASE_TWO process_phase,
1258 sysdate last_update_date,
1259 g_userid last_updated_by,
1260 SYSDATE creation_date,
1261 g_userid created_by,
1262 g_applicationid program_application_id
1263 FROM wip_bis_mnra_temp mnra_temp,
1264 (SELECT
1265 wt.organization_id organization_id,
1266 bd.department_id department_id,
1267 bd.department_code department_code,
1268 wt.resource_id resource_id,
1269 br.resource_code resource_code,
1270 wt.wip_entity_id wip_entity_id, -- Bug 3604065
1271 wt.operation_seq_num operation_seq_num, --bug 3662056
1272 trunc(wt.transaction_date) transaction_date,
1273 sum(inv_convert.inv_um_convert(0,NULL,wt.primary_quantity,
1274 wt.primary_uom,g_uom_code,
1275 NULL,NULL)) applied_units_utz
1276 FROM
1277 bom_resources br,
1278 bom_departments bd,
1279 bom_department_resources bdr,
1280 wip_transactions wt,
1281 mtl_units_of_measure muom
1282 WHERE
1283 wt.transaction_date BETWEEN x_date_from AND
1284 (x_date_to + 0.99999)
1285 AND wt.resource_id = nvl(p_resource_id, wt.resource_id)
1286 AND wt.department_id = nvl(p_department_id, wt.department_id)
1287 AND wt.organization_id = nvl(p_organization_id,
1288 wt.organization_id)
1289 AND wt.transaction_type in (1, 3)
1290 AND bdr.resource_id = wt.resource_id
1291 AND bdr.department_id = wt.department_id
1292 AND bd.department_id = nvl(bdr.share_from_dept_id,
1293 bdr.department_id)
1294 AND bd.organization_id = wt.organization_id
1295 AND br.resource_id = wt.resource_id
1296 AND br.unit_of_measure = muom.uom_code
1297 AND muom.uom_class = g_uom_class
1298 AND br.organization_id = wt.organization_id
1299 GROUP BY
1300 wt.organization_id,
1301 bd.department_id,
1302 bd.department_code,
1303 wt.resource_id,
1304 br.resource_code,
1305 wt.wip_entity_id,-- Bug 3604065
1306 wt.operation_seq_num, --bug 3662056
1307 trunc(wt.transaction_date)) utz_rows
1308 WHERE mnra_temp.organization_id (+) = utz_rows.organization_id
1309 AND mnra_temp.department_id (+) = utz_rows.department_id
1310 AND mnra_temp.resource_id (+) = utz_rows.resource_id
1311 AND mnra_temp.shift_date (+) = utz_rows.transaction_date;
1312
1313 COMMIT ;
1314
1315 -- gather stats on table to allow index access
1316 If nvl(WIP_CALL_LOG,-1) =1 then
1317 fnd_stats.gather_table_stats (g_wip_schema, 'WIP_INDICATORS_TEMP',
1318 cascade => true);
1319 End If;
1320 -- Phase IV: SFCB
1321 x_phase := 'IV';
1322 if g_debug = 1 then
1323 fnd_file.put_line(fnd_file.log, 'Before Stage 3 Phase IV');
1324 end if ;
1325 --dbms_output.put_line('Before Stage 3 Phase IV');
1326 IF (p_sfcb IS NOT NULL) THEN
1327
1328 Move_SFCB_Utz_Info(
1329 p_group_id => x_group_id,
1330 p_organization_id => p_organization_id,
1331 p_date_from =>x_date_from,
1332 p_date_to => x_date_to,
1333 p_department_id => p_department_id,
1334 p_resource_id => p_resource_id,
1335 p_userid => x_userid, -- this parameter is not really needed
1336 p_applicationid => x_appl_id, -- this parameter is not really needed
1337 p_errnum => p_errnum,
1338 p_errmesg => p_errmesg ) ;
1339
1340 IF (p_errnum <0) THEN
1341 raise collection_stage_failed;
1342 END IF ;
1343
1344 END IF ;
1345
1346 If nvl(WIP_CALL_LOG,-1) <> 1 then
1347 -- Due to performance reason . this code will work for only discrete workstation .
1348 --For Capture Production Indicator Request we already have these fix.
1349 -- Addtion to remove available hours from other than min(wip_entity_id) on same day bug -3624837
1350 update wip_indicators_temp wbpi
1351 set wbpi.available_units = 0
1352 where wbpi.available_units is not null
1353 and wbpi.indicator_type=WIP_UTILIZATION
1354 and wbpi.process_phase= WIP_UTZ_PHASE_TWO
1355 and wbpi.group_id=x_group_id
1356 and wbpi.wip_entity_id <>
1357 (select min(wit.wip_entity_id)
1358 from wip_indicators_temp wit
1359 where trunc(wit.transaction_date) =trunc(wbpi.transaction_date)
1360 and wbpi.resource_id = wit.resource_id
1361 and wbpi.department_id = wit.department_id
1362 and wbpi.organization_id = wit.organization_id
1363 and wbpi.group_id=wit.group_id
1364 and wit.indicator_type=WIP_UTILIZATION
1365 and wit.process_phase= WIP_UTZ_PHASE_TWO);
1366
1367 --- Fix when same resource is used for more than one step bug -3624837
1368 update wip_indicators_temp wbpi
1369 set wbpi.available_units = 0
1370 where wbpi.available_units is not null
1371 and wbpi.indicator_type=WIP_UTILIZATION
1372 and wbpi.process_phase= WIP_UTZ_PHASE_TWO
1373 and wbpi.group_id=x_group_id
1374 and wbpi.operation_seq_num <>
1375 (select min(wit.operation_seq_num)
1376 from wip_indicators_temp wit
1377 where trunc(wit.transaction_date) =trunc(wbpi.transaction_date)
1378 and wbpi.resource_id = wit.resource_id
1379 and wbpi.department_id = wit.department_id
1380 and wbpi.organization_id = wit.organization_id
1381 and wbpi.wip_entity_id = wit.wip_entity_id
1382 and wbpi.group_id=wit.group_id
1383 and wit.indicator_type=WIP_UTILIZATION
1384 and wit.process_phase= WIP_UTZ_PHASE_TWO);
1385
1386 Commit;
1387 end if;
1388 p_errnum := 0;
1389 p_errmesg := '';
1390 RETURN ;
1391
1392
1393 EXCEPTION
1394 WHEN OTHERS THEN
1395
1396 if g_debug = 1 then
1397 if x_org_id <> 0 then
1398 fnd_file.put_line(fnd_file.log,
1399 'Failed in Stage 3 phase : '||x_phase ||
1400 ' for Organization Id : ' ||
1401 to_char(x_org_id) );
1402
1403 --dbms_output.put_line('Failed in Stage 3 phase : '||
1404 -- x_phase ||
1405 -- ' for Organization Id : ' ||
1406 -- to_char(x_org_id) );
1407 else
1408 fnd_file.put_line(fnd_file.log,
1409 'Failed in Stage 3 phase : '||x_phase);
1410 end if;
1411 fnd_file.put_line(fnd_file.log, SQLCODE);
1412 fnd_file.put_line(fnd_file.log,SQLERRM);
1413 end if ;
1414
1415 --dbms_output.put_line('Failed in Stage 3 phase : '||x_phase);
1416 --dbms_output.put_line(SQLCODE);
1417 --dbms_output.put_line(SQLERRM);
1418
1419 p_errnum := -1 ;
1420
1421 if x_org_id <> 0 then
1422 p_errmesg := 'Failed in Stage 3 Phase : '||x_phase||
1423 ' for Organization : '
1424 || to_char(x_org_id) || substr(SQLERRM,1,105);
1425 else
1426 p_errmesg := 'Failed in Stage 3 Phase : '||x_phase||
1427 substr(SQLERRM,1,125);
1428 end if ;
1429
1430 -- to make sure there is no garbage returned to SFCB,
1431 -- truncate wip_indicators_temp
1432 Delete_Temp_Info (p_group_id => x_group_id);
1433
1434 -- returns to populate_summary_table, so don't raise exception.
1435
1436 commit ;
1437 return ;
1438
1439 END Populate_Utilization;
1440
1441
1442 /*Populate_Yield
1443
1444 Calculates the Yield for the report.
1445 We have included the resource_id as a new paramater - just for future
1446 use, if we decided to get the yield for the resource as well
1447 */
1448 PROCEDURE Populate_Yield(
1449 p_group_id IN NUMBER,
1450 p_organization_id IN NUMBER,
1451 p_date_from IN DATE,
1452 p_date_to IN DATE,
1453 p_department_id IN NUMBER,
1454 p_resource_id IN NUMBER,
1455 p_userid IN NUMBER,
1456 p_applicationid IN NUMBER,
1457 p_errnum OUT NOCOPY NUMBER,
1458 p_errmesg OUT NOCOPY VARCHAR2)
1459
1460 IS
1461 x_group_id NUMBER;
1462 x_phase VARCHAR2(10);
1463 x_date_from DATE;
1464 x_date_to DATE;
1465 x_userid NUMBER;
1466 x_appl_id NUMBER;
1467 BEGIN
1468
1469 /* As the entry point for this more than a single
1470 point we have to do the validation in here as
1471 well. Ex :
1472 Concurrent Program
1473 SFCB
1474 */
1475 IF NOT (fnd_installation.get_app_info(
1476 'WIP', g_status, g_industry, g_wip_schema)) THEN
1477
1478 RAISE_APPLICATION_ERROR (-20000,
1479 'Unable to get session information.');
1480
1481 END IF;
1482 if p_userid is null then
1483 -- This is an Error Condition
1484 x_userid := fnd_global.user_id ;
1485 else
1486 x_userid := p_userid ;
1487 end if;
1488
1489
1490 IF p_group_id IS NULL THEN
1491 select wip_indicators_temp_s.nextval into x_group_id
1492 from sys.dual ;
1493 ELSE
1494 x_group_id := p_group_id ;
1495 END IF;
1496
1497 if p_applicationid is null then
1498 -- This is an Error Condition
1499 x_appl_id := fnd_global.prog_appl_id ;
1500 else
1501 x_appl_id := p_applicationid ;
1502 end if;
1503
1504 g_userid := x_userid ;
1505 g_applicationid := x_appl_id ;
1506
1507 IF p_date_from IS NULL THEN
1508 begin
1509
1510 select trunc(min(calendar_date))
1511 into g_date_from
1512 from bom_calendar_dates ;
1513
1514 exception
1515 when no_data_found then
1516 g_date_from := sysdate ;
1517 end ;
1518
1519 ELSE
1520 g_date_from := p_date_from;
1521
1522 END IF;
1523
1524 IF p_date_to IS NULL THEN
1525 begin
1526
1527 select trunc(max(calendar_date))
1528 into g_date_to
1529 from bom_calendar_dates ;
1530
1531 exception
1532 when no_data_found then
1533 g_date_to := sysdate ;
1534 end ;
1535
1536 ELSE
1537 g_date_to := p_date_to;
1538 END IF;
1539
1540 x_date_from := g_date_from ;
1541 x_date_to := g_date_to ;
1542
1543
1544 -- Phase I: Calculate the Total Quantities Produced
1545 -- by the various departments
1546 x_phase := 'I';
1547 if g_debug = 1 then
1548 fnd_file.put_line(fnd_file.log, 'Before Stage 4 Phase I');
1549 end if ;
1550 ----dbms_output.put_line('Before Stage 4 Phase I');
1551
1552 Calculate_Total_Quantity(
1553 p_group_id => x_group_id,
1554 p_organization_id => p_organization_id ,
1555 p_date_from => trunc (x_date_from),
1556 p_date_to => trunc (x_date_to) ,
1557 p_department_id => p_department_id);
1558
1559 commit ;
1560
1561 -- Phase II: Calculate the Scrap Quantity by the various departments
1562 x_phase := 'II';
1563 if g_debug = 1 then
1564 fnd_file.put_line(fnd_file.log, 'Before Stage 4 Phase II');
1565 end if ;
1566 ----dbms_output.put_line('Before Stage 4 Phase II');
1567
1568 Calculate_Scrap_Quantity(
1569 p_group_id => x_group_id,
1570 p_organization_id => p_organization_id,
1571 p_date_from => trunc (x_date_from),
1572 p_date_to => trunc (x_date_to),
1573 p_errnum => p_errnum,
1574 p_errmesg => p_errmesg);
1575 commit ;
1576
1577 -- Error in the called program
1578 if(p_errnum <0 )then
1579 return ;
1580 end if ;
1581
1582 -- Phase III: Incorporate the resource information for the yield
1583 x_phase := 'III';
1584 if g_debug = 1 then
1585 fnd_file.put_line(fnd_file.log, 'Before Stage 4 Phase III');
1586 end if ;
1587 ----dbms_output.put_line('Before Stage 4 Phase III');
1588
1589 Resource_Yield( p_group_id => x_group_id,
1590 p_errnum => p_errnum,
1591 p_errmesg => p_errmesg );
1592 commit ;
1593
1594 -- gather stats on table to allow index access
1595 If nvl(WIP_CALL_LOG,-1) =1 then
1596 fnd_stats.gather_table_stats (g_wip_schema, 'WIP_INDICATORS_TEMP',
1597 cascade => true);
1598 End If;
1599 p_errnum := 0;
1600 p_errmesg := 0;
1601 return ;
1602
1603 Exception
1604
1605 when others then
1606
1607 if g_debug = 1 then
1608 fnd_file.put_line(fnd_file.log,'Failed in Stage 4 phase : '||x_phase);
1609 fnd_file.put_line(fnd_file.log, SQLCODE);
1610 fnd_file.put_line(fnd_file.log,SQLERRM);
1611 end if ;
1612 ----dbms_output.put_line('Failed in Stage 4 phase : '||x_phase);
1613 ----dbms_output.put_line(SQLCODE);
1614 ----dbms_output.put_line(SQLERRM);
1615 p_errnum := -1 ;
1616 p_errmesg := 'Failed in Stage 4 Phase : '||x_phase||substr(SQLERRM,1,125);
1617
1618 -- to make sure there is no garbage returned to SFCB,
1619 -- truncate wip_indicators_temp
1620 Delete_Temp_Info (p_group_id => x_group_id);
1621
1622 -- returns to populate_summary_table, so don't raise exception.
1623
1624 commit ;
1625 return ;
1626
1627
1628 END Populate_Yield;
1629
1630 /* Calculate_Std_Quantity
1631
1632 Calculate the Standard Quantities used by the various departments
1633 in the organization within the date range that is specified.
1634 However if the date range is not specified then we consider the
1635 whole time horizon. If no department is specified then we get all
1636 the departments in the organization. However if the department is
1637 specified then we get only corresponding department information
1638 */
1639
1640 PROCEDURE Calculate_Std_Quantity(
1641 p_group_id IN NUMBER,
1642 p_organization_id IN NUMBER,
1643 p_date_from IN DATE,
1644 p_date_to IN DATE,
1645 p_department_id IN NUMBER,
1646 p_indicator IN NUMBER )
1647 IS
1648
1649 proc_name VARCHAR2 (40);
1650
1651 BEGIN
1652 proc_name := 'Calculate_Std_Quantity';
1653 insert into wip_indicators_temp(
1654 group_id,
1655 organization_id,
1656 department_id,
1657 department_code,
1658 wip_entity_id,
1659 operation_seq_num,
1660 indicator_type,
1661 process_phase,
1662 transaction_date,
1663 applied_units_prd,
1664 standard_units,
1665 standard_quantity,
1666 last_update_date,
1667 last_updated_by,
1668 creation_date,
1669 created_by,
1670 program_application_id)
1671 select
1672 p_group_id,
1673 wmt.organization_id,
1674 wo.department_id,
1675 bd.department_code,
1676 wmt.wip_entity_id,
1677 wo.operation_seq_num,
1678 p_indicator,
1679 WIP_EFF_PHASE_ONE, /* First Process Phase */
1680 trunc(wmt.transaction_date),
1681 null,
1682 null,
1683 sum( decode ( sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
1684 0, -- Within the same operation
1685 decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
1686 1, -- From Queue
1687 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1688 2 , 0,
1689 1, 0, -- this is not possible but still
1690 (wmt.primary_quantity)
1691 ),
1692 2, -- From Run
1693 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1694 1, 0,
1695 2, 0, -- this is not possible but still
1696 (wmt.primary_quantity)
1697 ),
1698 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1699 3, 0,
1700 4, 0,
1701 5, 0,
1702 (-1*wmt.primary_quantity)
1703 )
1704 ),
1705 -1, -- Move in the positive direction
1706 decode( wo.operation_seq_num,
1707 wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
1708 decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
1709 3, 0,
1710 4, 0,
1711 5, 0,
1712 (wmt.primary_quantity)
1713 ),
1714 wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
1715 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1716 1, 0,
1717 2, 0,
1718 decode( wo.count_point_type,
1719 3, 0,
1720 wmt.primary_quantity)
1721 ),
1722 decode( wo.count_point_type,
1723 3, 0,
1724 (wmt.primary_quantity)
1725 )
1726 ),
1727 1, -- Move in the negative direction
1728 decode( wo.operation_seq_num,
1729 wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
1730 decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
1731 1, 0,
1732 2, 0,
1733 3, 0,
1734 (-1*wmt.primary_quantity)
1735 ),
1736 wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
1737 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1738 3, 0,
1739 4, 0,
1740 5, 0,
1741 decode( wo.count_point_type,
1742 3, 0,
1743 -1*wmt.primary_quantity)
1744 ),
1745 decode( wo.count_point_type,
1746 3, 0,
1747 (-1*wmt.primary_quantity)
1748 )
1749 )
1750 ) ) "Quantity",
1751 sysdate,
1752 g_userid,
1753 SYSDATE,
1754 g_userid,
1755 g_applicationid
1756 from
1757 wip_move_transactions wmt,
1758 wip_operations wo,
1759 bom_departments bd
1760 where
1761 trunc(wmt.transaction_date) between trunc(nvl(p_date_from,wmt.transaction_date))
1762 and trunc(nvl(p_date_to,wmt.transaction_date))
1763 and wo.operation_seq_num <= decode(sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
1764 -1,wmt.TO_OPERATION_SEQ_NUM, 1, wmt.FM_OPERATION_SEQ_NUM,
1765 wmt.FM_OPERATION_SEQ_NUM)
1766 and wo.operation_seq_num >= decode(sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
1767 -1,wmt.FM_OPERATION_SEQ_NUM, 1, wmt.TO_OPERATION_SEQ_NUM,
1768 wmt.FM_OPERATION_SEQ_NUM)
1769 and wmt.organization_id = wo.organization_id
1770 and wo.department_id = bd.department_id
1771 and wo.wip_entity_id = wmt.wip_entity_id
1772 and wo.organization_id = bd.organization_id
1773 and wo.department_id = nvl(p_department_id, wo.department_id)
1774 and bd.organization_id = nvl(p_organization_id, bd.organization_id)
1775 group by
1776 wmt.organization_id,
1777 wo.department_id,
1778 bd.department_code,
1779 wmt.wip_entity_id,
1780 wo.operation_seq_num,
1781 p_indicator,
1782 1,
1783 trunc(wmt.transaction_date),
1784 null,
1785 null,
1786 sysdate,
1787 g_userid,
1788 SYSDATE,
1789 g_userid,
1790 g_applicationid
1791 having sum( decode ( sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
1792 0, -- Within the same operation
1793 decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
1794 1, -- From Queue
1795 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1796 2 , 0,
1797 1, 0, -- this is not possible but still
1798 (wmt.primary_quantity)
1799 ),
1800 2, -- From Run
1801 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1802 1, 0,
1803 2, 0, -- this is not possible but still
1804 (wmt.primary_quantity)
1805 ),
1806 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1807 3, 0,
1808 4, 0,
1809 5, 0,
1810 (-1*wmt.primary_quantity)
1811 )
1812 ),
1813 -1, -- Move in the positive direction
1814 decode( wo.operation_seq_num,
1815 wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
1816 decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
1817 3, 0,
1818 4, 0,
1819 5, 0,
1820 (wmt.primary_quantity)
1821 ),
1822 wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
1823 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1824 1, 0,
1825 2, 0,
1826 decode( wo.count_point_type,
1827 3, 0,
1828 wmt.primary_quantity)
1829 ),
1830 decode( wo.count_point_type,
1831 3, 0,
1832 (wmt.primary_quantity)
1833 )
1834 ),
1835 1, -- Move in the negative direction
1836 decode( wo.operation_seq_num,
1837 wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
1838 decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
1839 1, 0,
1840 2, 0,
1841 3, 0,
1842 (-1*wmt.primary_quantity)
1843 ),
1844 wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
1845 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
1846 3, 0,
1847 4, 0,
1848 5, 0,
1849 decode( wo.count_point_type,
1850 3, 0,
1851 -1*wmt.primary_quantity)
1852 ),
1853 decode( wo.count_point_type,
1854 3, 0,
1855 (-1*wmt.primary_quantity)
1856 )
1857 )
1858 ) ) <> 0 ;
1859
1860 commit;
1861
1862 EXCEPTION
1863
1864 WHEN OTHERS
1865 THEN
1866 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
1867 RAISE; -- propagate to calling function
1868
1869 END Calculate_Std_Quantity;
1870
1871
1872
1873 /* This gets the information regarding the resource in a
1874 a particular department - however if a particular resource
1875 is specified then we get the information regarding only that
1876 resource.
1877
1878 - This has been modified to take into consideration for both
1879 lot/Item based resources - fixed for Oabis 11.1 - required
1880 introducing a new column basis_type. The sequence of steps
1881 are :
1882
1883 1. Calculate the Standard_Quantities for each
1884 wip_entity_id, operation_seq_num, department,
1885 resource, transaction_date.
1886
1887 2. Delete the original rows + lot based resource
1888 transactions except for the first transaction
1889 information.
1890
1891 The summarization and steps across the various combinations
1892 have been commented out long back and are being removed from the
1893 file. (digupta 10/02/03).
1894
1895 */
1896
1897 PROCEDURE Calculate_Std_Units(
1898 p_group_id IN NUMBER,
1899 p_resource_id IN NUMBER,
1900 p_errnum OUT NOCOPY NUMBER,
1901 p_errmesg OUT NOCOPY VARCHAR2,
1902 p_indicator IN NUMBER )
1903 IS
1904 x_step NUMBER ;
1905 proc_name VARCHAR2 (40) ;
1906
1907 BEGIN
1908 x_step := 0 ;
1909 proc_name := 'Calculate_Std_Units';
1910 -- Get the default UOM Class
1911 g_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
1912 select uom_class
1913 into g_uom_class
1914 from mtl_units_of_measure
1915 where uom_code = g_uom_code;
1916
1917 -- Step 1 : Calculate the Std. Qty for each
1918 -- wip_entity_id, OP Seq, Dept, Res, Txn Date
1919 -- and insert into WIT
1920 x_step := 1;
1921 if g_debug = 1 then
1922 fnd_file.put_line(fnd_file.log,
1923 'Before Stage 2 Phase II Step : ' || x_step);
1924 end if ;
1925 ----dbms_output.put_line('Before Stage 2 Phase II Step : ' || x_step);
1926
1927 /* Modify this to include the changes required for
1928 the calculation for the owning department
1929 */
1930 /*???? has this already been done? digupta - 10/14/03*/
1931 insert into wip_indicators_temp(
1932 group_id,
1933 organization_id,
1934 department_id,
1935 department_code,
1936 standard_quantity,
1937 resource_id,
1938 resource_code,
1939 wip_entity_id,
1940 operation_seq_num,
1941 resource_basis,
1942 indicator_type,
1943 process_phase,
1944 transaction_date,
1945 standard_units,
1946 applied_units_prd,
1947 last_update_date,
1948 last_updated_by,
1949 creation_date,
1950 created_by,
1951 program_application_id )
1952 select
1953 wit.group_id,
1954 wit.organization_id,
1955 decode(wit.indicator_type,
1956 WIP_EFFICIENCY, wit.department_id,
1957 WIP_PRODUCTIVITY,
1958 nvl(bdr.share_from_dept_id,wit.department_id)
1959 ),
1960 decode(wit.indicator_type,
1961 WIP_EFFICIENCY, wit.department_code,
1962 WIP_PRODUCTIVITY,
1963 nvl(bd.department_code, wit.department_code)),
1964 wit.standard_quantity,
1965 wor.resource_id,
1966 br.resource_code,
1967 wit.wip_entity_id,
1968 wit.operation_seq_num,
1969 wor.basis_type,
1970 wit.indicator_type,
1971 WIP_EFF_PHASE_TWO, /* This is second stage */
1972 transaction_date, -- already trunc'ed
1973 inv_convert.inv_um_convert(0,
1974 NULL,
1975 decode(wor.basis_type,
1976 1, (wit.standard_quantity*
1977 wor.usage_rate_or_amount),
1978 2, (wit.standard_quantity)),
1979 wor.uom_code,
1980 g_uom_code,
1981 NULL,
1982 NULL),
1983 null,
1984 wit.last_update_date,
1985 wit.last_updated_by,
1986 wit.creation_date,
1987 wit.created_by,
1988 wit.program_application_id
1989 from wip_indicators_temp wit,
1990 bom_resources br,
1991 bom_departments bd,
1992 bom_department_resources bdr,
1993 wip_operation_resources wor,
1994 mtl_units_of_measure muom
1995 where
1996 wor.wip_entity_id = wit.wip_entity_id
1997 and wor.operation_seq_num = wit.operation_seq_num
1998 and wit.indicator_type = p_indicator
1999 and wor.resource_id = nvl(p_resource_id, wor.resource_id)
2000 and br.organization_id = wor.organization_id
2001 and br.resource_id = wor.resource_id
2002 and bdr.resource_id = br.resource_id
2003 and bdr.department_id = wit.department_id
2004 and bd.department_id (+) = bdr.share_from_dept_id
2005 and wor.uom_code = muom.uom_code
2006 and muom.uom_class = g_uom_class;
2007
2008 commit ;
2009
2010 -- gather stats on table to allow index access
2011 If nvl(WIP_CALL_LOG,-1) =1 then
2012 fnd_stats.gather_table_stats (g_wip_schema, 'WIP_INDICATORS_TEMP',
2013 cascade => true);
2014 End If;
2015 -- Step2 : Delete all the original rows (i.e. the ones
2016 -- without the resource information that were
2017 -- generated in the WIP_EFF_PHASE_ONE)
2018 -- and the lot-based information for a job except
2019 -- for the first transaction across this particular
2020 -- resource.
2021 x_step := 2;
2022 if g_debug = 1 then
2023 fnd_file.put_line(fnd_file.log, 'Before Stage 2 Phase II Step : ' || x_step);
2024 end if ;
2025 ----dbms_output.put_line('Before Stage 2 Phase II Step : ' || x_step);
2026
2027 delete from wip_indicators_temp wit
2028 where wit.indicator_type = p_indicator
2029 and ( ( wit.process_phase = WIP_EFF_PHASE_ONE )
2030 or ( wit.process_phase = WIP_EFF_PHASE_TWO
2031 and wit.resource_basis = 2
2032 and wit.transaction_date >
2033 (
2034 select min(transaction_date)
2035 from wip_indicators_temp wit2
2036 where wit2.wip_entity_id = wit.wip_entity_id
2037 and wit2.indicator_type = wit.indicator_type
2038 and wit2.operation_seq_num = wit.operation_seq_num
2039 and wit2.resource_id = wit.resource_id
2040 and wit2.resource_basis = 2)
2041 )
2042 );
2043 commit ;
2044
2045 p_errnum := 0;
2046 p_errmesg := '';
2047
2048 return ;
2049
2050 EXCEPTION
2051
2052 WHEN OTHERS
2053 THEN
2054 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
2055 p_errnum := -1;
2056 p_errmesg := (proc_name || ':' || sqlerrm);
2057 RAISE; -- propagate to calling function
2058
2059
2060 End Calculate_Std_Units;
2061
2062
2063 /* Calculate the efficiency applied units.
2064 As far as we can tell, this function now does nothing because
2065 when it is called, there is no row in WIT with
2066 WIP_EFF_PHASE_THREE
2067 */
2068
2069 PROCEDURE Calc_Eff_Applied_Units (
2070 p_errmesg OUT NOCOPY VARCHAR2,
2071 p_errnum OUT NOCOPY NUMBER,
2072 p_group_id IN NUMBER)
2073 IS
2074 proc_name VARCHAR2 (40);
2075
2076 BEGIN
2077 proc_name := 'Calc_Eff_Applied_Units';
2078 g_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
2079 select uom_class
2080 into g_uom_class
2081 from mtl_units_of_measure
2082 where uom_code = g_uom_code;
2083
2084 --- ??? Can this ever happen? The SQL that set the process phase
2085 --- to WIP_EFF_PHASE_THREE in calculate_std_units
2086 --- was stubbed out back in version 115.32. Please check. ???
2087
2088 -- Go to wip_transactions to get the actual units applied
2089 -- consider only resource and oustide processing charges
2090 /* update wip_indicators_temp wit
2091 set APPLIED_UNITS_PRD = (
2092 select nvl(wt.primary_quantity,0)
2093 from wip_transactions wt
2094 where wt.organization_id = wit.organization_id
2095 and wt.transaction_date BETWEEN trunc(wit.transaction_date)
2096 and trunc (wit.transaction_date) + 0.999999
2097 and wt.transaction_type in (1, 3)
2098 and wt.operation_seq_num = wit.operation_seq_num
2099 AND wt.wip_entity_id = wit.wip_entity_id
2100 and wt.department_id = wit.department_id
2101 and wt.resource_id = wit.resource_id
2102 )
2103 where wit.indicator_type = WIP_EFFICIENCY
2104 AND wit.process_phase = WIP_EFF_PHASE_THREE ;
2105 */
2106 p_errnum := 0;
2107 p_errmesg := '';
2108 return;
2109
2110 EXCEPTION
2111
2112 WHEN OTHERS
2113 THEN
2114 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
2115 p_errnum := -1;
2116 p_errmesg := (proc_name || ':' || sqlerrm);
2117 RAISE; -- propagate to calling function
2118
2119 END Calc_Eff_Applied_Units;
2120
2121
2122 /* Misc_Applied_Units
2123
2124 */
2125 PROCEDURE Misc_Applied_Units(
2126 p_group_id IN NUMBER,
2127 p_organization_id IN NUMBER,
2128 p_date_from IN DATE,
2129 p_date_to IN DATE,
2130 p_department_id IN NUMBER,
2131 p_resource_id IN NUMBER,
2132 p_errnum OUT NOCOPY NUMBER,
2133 p_errmesg OUT NOCOPY VARCHAR2)
2134 IS
2135 proc_name VARCHAR2 (40) ;
2136
2137 BEGIN
2138 proc_name := 'Misc_Applied_Units';
2139 g_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
2140 select uom_class
2141 into g_uom_class
2142 from mtl_units_of_measure
2143 where uom_code = g_uom_code;
2144
2145 -- ??? What is this SQL doing?
2146 insert into wip_indicators_temp(
2147 group_id,
2148 organization_id,
2149 wip_entity_id,
2150 operation_seq_num,
2151 department_id,
2152 department_code,
2153 resource_id,
2154 resource_code,
2155 standard_quantity,
2156 standard_units,
2157 applied_units_prd,
2158 transaction_date,
2159 indicator_type,
2160 process_phase,
2161 last_update_date,
2162 last_updated_by,
2163 creation_date,
2164 created_by,
2165 program_application_id)
2166 select
2167 p_group_id,
2168 wt.organization_id,
2169 wt.wip_entity_id,
2170 wt.operation_seq_num,
2171 bd.department_id,
2172 bd.department_code,
2173 wt.resource_id,
2174 br.resource_code,
2175 0,
2176 0,
2177 sum(inv_convert.inv_um_convert(0,NULL,wt.primary_quantity,
2178 wt.primary_uom,g_uom_code,NULL,NULL)),
2179 trunc(wt.transaction_date),
2180 WIP_EFFICIENCY,
2181 WIP_EFF_PHASE_THREE, -- this is the third and final phase
2182 sysdate,
2183 g_userid,
2184 SYSDATE,
2185 g_userid,
2186 g_applicationid
2187 from
2188 bom_resources br,
2189 bom_departments bd,
2190 bom_department_resources bdr,
2191 wip_transactions wt,
2192 mtl_units_of_measure muom
2193 where
2194 wt.transaction_date between trunc(p_date_from)
2195 and trunc(p_date_to) + 0.999999
2196 and wt.resource_id = nvl(p_resource_id, wt.resource_id)
2197 and wt.department_id = nvl(p_department_id, wt.department_id)
2198 and wt.organization_id = nvl(p_organization_id, wt.organization_id)
2199 and wt.transaction_type in (1, 3)
2200 and bdr.resource_id = wt.resource_id
2201 and bdr.department_id = wt.department_id
2202 and bd.department_id = nvl(bdr.share_from_dept_id, bdr.department_id)
2203 and bd.organization_id = wt.organization_id
2204 and br.resource_id = wt.resource_id
2205 and br.unit_of_measure = muom.uom_code
2206 and muom.uom_class = g_uom_class
2207 and br.organization_id = wt.organization_id
2208 group by
2209 wt.organization_id,
2210 wt.wip_entity_id,
2211 wt.operation_seq_num,
2212 bd.department_id,
2213 bd.department_code,
2214 wt.resource_id,
2215 br.resource_code,
2216 trunc(wt.transaction_date);
2217
2218 commit ;
2219
2220 p_errnum := 0;
2221 p_errmesg := '';
2222
2223 return ;
2224
2225 EXCEPTION
2226
2227 WHEN OTHERS
2228 THEN
2229 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
2230 p_errnum := -1;
2231 p_errmesg := (proc_name || ':' || sqlerrm);
2232 RAISE; -- propagate to calling function
2233
2234 END Misc_Applied_Units ;
2235
2236 /* Calculate_Total_Quantity
2237
2238 Calculate the Total Quantities produced by the various departments
2239 in the organization within the date range that is specified.
2240 However if the date range is not specified then we consider the
2241 whole time horizon. If no department is specified then we get all
2242 the departments in the organization. However if the department is
2243 specified then we get only corresponding department information
2244 */
2245
2246 PROCEDURE Calculate_Total_Quantity(
2247 p_group_id IN NUMBER,
2248 p_organization_id IN NUMBER,
2249 p_date_from IN DATE,
2250 p_date_to IN DATE,
2251 p_department_id IN NUMBER)
2252 IS
2253
2254 proc_name VARCHAR2 (40);
2255
2256 -- ??? The truncs in this SQL on the dates can be better written because
2257 -- the arguments are already trunc'ed and WHERE transaction_date BETWEEN
2258 -- transaction_date and transaction_date always returns true.
2259
2260 BEGIN
2261 proc_name := 'Calculate_Total_Quantity';
2262 insert into wip_indicators_temp(
2263 group_id,
2264 organization_id,
2265 department_id,
2266 department_code,
2267 wip_entity_id,
2268 operation_seq_num,
2269 indicator_type,
2270 process_phase,
2271 transaction_date,
2272 total_quantity,
2273 last_update_date,
2274 last_updated_by,
2275 creation_date,
2276 created_by,
2277 program_application_id)
2278 select
2279 p_group_id,
2280 wmt.organization_id,
2281 wo.department_id,
2282 bd.department_code,
2283 wmt.wip_entity_id,
2284 wo.operation_seq_num,
2285 WIP_YIELD,
2286 1, /* this is the first step */
2287 trunc(wmt.transaction_date),
2288 sum( decode ( sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
2289 0, -- Within the same operation
2290 decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
2291 1, -- From Queue
2292 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
2293 2 , 0,
2294 1, 0, -- this is not possible but still
2295 (wmt.primary_quantity)
2296 ),
2297 2, -- From Run
2298 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
2299 1, 0,
2300 2, 0, -- this is not possible but still
2301 (wmt.primary_quantity)
2302 ),
2303 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
2304 3, 0,
2305 4, 0,
2306 5, 0,
2307 (-1*wmt.primary_quantity)
2308 )
2309 ),
2310 -1, -- Move in the positive direction
2311 decode( wo.operation_seq_num,
2312 wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
2313 decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
2314 3, 0,
2315 4, 0,
2316 5, 0,
2317 (wmt.primary_quantity)
2318 ),
2319 wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
2320 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
2321 1, 0,
2322 2, 0,
2323 (wmt.primary_quantity)
2324 ),
2325 (wmt.primary_quantity)
2326 ),
2327 1, -- Move in the negative direction
2328 decode( wo.operation_seq_num,
2329 wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
2330 decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
2331 1, 0,
2332 2, 0,
2333 (-1*wmt.primary_quantity)
2334 ),
2335 wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
2336 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
2337 3, 0,
2338 4, 0,
2339 5, 0,
2340 (-1*wmt.primary_quantity)
2341 ),
2342 (-1*wmt.primary_quantity)
2343 )
2344 ) ) "Quantity",
2345 sysdate,
2346 g_userid,
2347 SYSDATE,
2348 g_userid,
2349 g_applicationid
2350 from
2351 wip_move_transactions wmt,
2352 wip_operations wo,
2353 bom_departments bd
2354 where trunc(wmt.transaction_date) between trunc(nvl(p_date_from,wmt.transaction_date))
2355 and trunc(nvl(p_date_to,wmt.transaction_date))
2356 -- below statement is equavivalent to between only. Dont know why such a complex condition.
2357 -- and wo.operation_seq_num between wmt.FM_OPERATION_SEQ_NUM and wmt.TO_OPERATION_SEQ_NUM
2358 and wo.operation_seq_num <= decode(sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
2359 -1,wmt.TO_OPERATION_SEQ_NUM, 1, wmt.FM_OPERATION_SEQ_NUM,
2360 wmt.FM_OPERATION_SEQ_NUM)
2361 and wo.operation_seq_num >= decode(sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
2362 -1,wmt.FM_OPERATION_SEQ_NUM, 1, wmt.TO_OPERATION_SEQ_NUM,
2363 wmt.FM_OPERATION_SEQ_NUM)
2364 and wmt.organization_id = wo.organization_id
2365 and wo.wip_entity_id = wmt.wip_entity_id
2366 and wo.organization_id = bd.organization_id
2367 and wo.department_id = bd.department_id
2368 and wo.department_id = nvl(p_department_id, wo.department_id)
2369 and bd.organization_id = nvl(p_organization_id,bd.organization_id)
2370 group by
2371 wmt.organization_id,
2372 wo.department_id,
2373 bd.department_code,
2374 wmt.wip_entity_id,
2375 wo.operation_seq_num,
2376 trunc(wmt.transaction_date),
2377 WIP_YIELD,
2378 sysdate,
2379 g_userid,
2380 SYSDATE,
2381 g_userid,
2382 g_applicationid
2383 having sum( decode ( sign(wmt.FM_OPERATION_SEQ_NUM-wmt.TO_OPERATION_SEQ_NUM),
2384 0, -- Within the same operation
2385 decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
2386 1, -- From Queue
2387 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
2388 2 , 0,
2389 1, 0, -- this is not possible but still
2390 (wmt.primary_quantity)
2391 ),
2392 2, -- From Run
2393 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
2394 1, 0,
2395 2, 0, -- this is not possible but still
2396 (wmt.primary_quantity)
2397 ),
2398 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
2399 3, 0,
2400 4, 0,
2401 5, .99,--instead of 0 it is made .99 for bug 3280671
2402 (-1*wmt.primary_quantity)
2403 )
2404 ),
2405 -1, -- Move in the positive direction
2406 decode( wo.operation_seq_num,
2407 wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
2408 decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
2409 3, 0,
2410 4, 0,
2411 5, 0,
2412 (wmt.primary_quantity)
2413 ),
2414 wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
2415 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
2416 1, 0,
2417 2, 0,
2418 (wmt.primary_quantity)
2419 ),
2420 (wmt.primary_quantity)
2421 ),
2422 1, -- Move in the negative direction
2423 decode( wo.operation_seq_num,
2424 wmt.FM_OPERATION_SEQ_NUM, -- Starting Operation
2425 decode( wmt.FM_INTRAOPERATION_STEP_TYPE,
2426 1, 0,
2427 2, 0,
2428 (-1*wmt.primary_quantity)
2429 ),
2430 wmt.TO_OPERATION_SEQ_NUM, -- Final Operation
2431 decode( wmt.TO_INTRAOPERATION_STEP_TYPE,
2432 3, 0,
2433 4, 0,
2434 5, 0,
2435 (-1*wmt.primary_quantity)
2436 ),
2437 (-1*wmt.primary_quantity)
2438 )
2439 ) ) <> 0 ;
2440 -------------------------------------BUG 3280671-----------------------------------------------------
2441 /*This Having Clause is stopping row with total_quantity 0. This need when we have scrap transaction
2442 on a different date then to_move transcations for that step 5 ,0->.99*/
2443 -----------------------------------------------------------------------------------------------------
2444 commit;
2445
2446 -- gather stats on table to allow index access
2447 If nvl(WIP_CALL_LOG,-1) =1 then
2448 fnd_stats.gather_table_stats (g_wip_schema, 'WIP_INDICATORS_TEMP',
2449 cascade => true);
2450 End If;
2451
2452 EXCEPTION
2453
2454 WHEN OTHERS
2455 THEN
2456 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
2457 RAISE; -- propagate to calling function
2458
2459 END Calculate_Total_Quantity;
2460
2461
2462
2463
2464 /* Calculate_Scrap_Quantity
2465 This gets the Quantity scrapped over every department
2466 however if the scrapped quantity was moved from one department
2467 to the other, this takes that into account
2468 */
2469
2470 PROCEDURE Calculate_Scrap_Quantity(
2471 p_group_id IN NUMBER,
2472 p_organization_id IN NUMBER,
2473 p_date_from IN DATE,
2474 p_date_to IN DATE,
2475 p_errnum OUT NOCOPY NUMBER,
2476 p_errmesg OUT NOCOPY VARCHAR2 )
2477 IS
2478
2479 proc_name VARCHAR2 (40);
2480
2481 -- ??? The truncs in this SQL on the dates can be better written because
2482 -- the arguments are already trunc'ed and WHERE transaction_date BETWEEN
2483 -- transaction_date and transaction_date always returns true.
2484 -- What the hell is this join condition on date anyway? It excludes
2485 -- all dates that lie between from and to, when the from is greater
2486 -- than the to. That should not be the intent, should it?
2487
2488
2489 -- Cursor to get all departments scrap to scrap transaction
2490 CURSOR Scrap_Adjustment (
2491 p_group_id number,
2492 p_org_id number,
2493 p_date_from date,
2494 p_date_to date ) IS
2495 SELECT /* WIP_MOVE_TRANSACTIONS_N2 */
2496 organization_id,
2497 wip_entity_id,
2498 fm_operation_seq_num,
2499 to_operation_seq_num,
2500 fm_intraoperation_step_type,
2501 to_intraoperation_step_type,
2502 primary_quantity,
2503 trunc(transaction_date) transaction_date
2504 FROM wip_move_transactions
2505 WHERE ( trunc(transaction_date) >= trunc(nvl(p_date_from,transaction_date))
2506 AND trunc(transaction_date) <= trunc(nvl(p_date_to,transaction_date)) )-- Or is replace AND Bug 3280671
2507 --( trunc(transaction_date) >= trunc(nvl(p_date_from,transaction_date))
2508 --OR trunc(transaction_date) <= trunc(nvl(p_date_to,transaction_date)) )
2509 AND organization_id = nvl(p_org_id,organization_id)
2510 AND fm_intraoperation_step_type = 5 ;
2511
2512 x_step NUMBER ;
2513 BEGIN
2514 x_step := 0;
2515 proc_name := 'Calculate_Scrap_Quantity';
2516 -- Step 1: <see comment below>
2517 x_step := 1;
2518 if g_debug = 1 then
2519 fnd_file.put_line(fnd_file.log, 'Before Stage 4 Phase II Step : ' || x_step);
2520 end if ;
2521 ----dbms_output.put_line('Before Stage 4 Phase II Step : ' || x_step);
2522
2523 -- Update the Scrap Quantitied for the various departments.
2524 -- This does an update only for that particular txn_date
2525 -- was already existing.
2526 -- Note: we don't have to worry about making an insert for
2527 -- those transactions on a day which has got nothing
2528 -- but scrap transactions for that day (because
2529 -- it is not possible in this logic not to have a record
2530 -- for that day when all the transactions were scrap transactions).
2531
2532 UPDATE wip_indicators_temp wit
2533 SET wit.scrap_quantity = (
2534 SELECT nvl(sum(wmt.primary_quantity),0)
2535 FROM wip_move_transactions wmt
2536 WHERE wmt.wip_entity_id = wit.wip_entity_id
2537 AND wmt.to_operation_seq_num = wit.operation_seq_num
2538 AND wmt.organization_id = wit.organization_id
2539 AND wmt.to_intraoperation_step_type = 5
2540 AND wmt.fm_intraoperation_step_type <> 5
2541 AND wmt.transaction_date BETWEEN
2542 nvl(p_date_from, wmt.transaction_date)
2543 AND nvl(p_date_to + 0.99999,
2544 wmt.transaction_date)
2545 AND wmt.transaction_date BETWEEN wit.transaction_date
2546 AND wit.transaction_date + 0.99999
2547 ),
2548 wit.process_phase = WIP_DEPT_YIELD /* process phase 2 */
2549 WHERE wit.indicator_type = WIP_YIELD;
2550
2551 COMMIT;
2552
2553
2554 -- Step 2:
2555 -- Take into account the scrap quantities that are moved from one
2556 -- department to the other
2557 x_step := 2;
2558 if g_debug = 1 then
2559 fnd_file.put_line(fnd_file.log, 'Before Stage 4 Phase II Step : '
2560 || x_step);
2561 end if ;
2562 ----dbms_output.put_line('Before Stage 4 Phase II Step : ' || x_step);
2563 FOR Adj_Rec IN Scrap_Adjustment(
2564 p_group_id,
2565 p_organization_id,
2566 p_date_from,
2567 p_date_to) LOOP
2568
2569 -- ??? what does this next comment mean
2570 -- This is to let it compile without a problem
2571 IF (Adj_Rec.to_intraoperation_step_type = 5) then
2572
2573 update wip_indicators_temp
2574 set scrap_quantity = (scrap_quantity -
2575 Adj_Rec.Primary_Quantity)
2576 where indicator_type = WIP_YIELD
2577 and process_phase = WIP_DEPT_YIELD
2578 and organization_id = Adj_Rec.organization_id
2579 and wip_entity_id = Adj_Rec.Wip_Entity_id
2580 and operation_seq_num = Adj_Rec.fm_operation_seq_num
2581 and transaction_date = Adj_Rec.transaction_date ;
2582
2583
2584 update wip_indicators_temp
2585 set scrap_quantity = (scrap_quantity +
2586 Adj_Rec.Primary_Quantity)
2587 where indicator_type = WIP_YIELD
2588 and process_phase = WIP_DEPT_YIELD
2589 and organization_id = Adj_Rec.organization_id
2590 and wip_entity_id = Adj_Rec.Wip_Entity_id
2591 and operation_seq_num = Adj_Rec.to_operation_seq_num
2592 and transaction_date = Adj_Rec.transaction_date ;
2593
2594 /* I had not considered this initially - I have handled
2595 this movement from the scrap intraoperation step
2596 as a negative scrap transaction */
2597
2598 ELSIF (Adj_Rec.to_intraoperation_step_type <>5 ) then
2599
2600 update wip_indicators_temp
2601 set scrap_quantity = (scrap_quantity -
2602 Adj_Rec.Primary_Quantity)
2603 where indicator_type = WIP_YIELD
2604 and process_phase = WIP_DEPT_YIELD
2605 and organization_id = Adj_Rec.organization_id
2606 and wip_entity_id = Adj_Rec.Wip_Entity_id
2607 and operation_seq_num = Adj_Rec.fm_operation_seq_num
2608 and transaction_date = Adj_Rec.transaction_date ;
2609
2610 END IF ;
2611
2612 END LOOP ;
2613
2614 COMMIT ;
2615
2616 p_errnum := 0;
2617 p_errmesg := '';
2618
2619 RETURN ;
2620
2621 EXCEPTION
2622
2623 WHEN OTHERS
2624 THEN
2625 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
2626 p_errnum := -1;
2627 p_errmesg := (proc_name || ':' || sqlerrm);
2628 RAISE; -- propagate to calling function
2629
2630 -- The exceptions in this will be handled by the calling function
2631
2632 END Calculate_Scrap_Quantity;
2633
2634
2635
2636 /* Resource_Yield
2637 This incorporates the yield for the resource
2638 associated with the departments
2639 */
2640
2641 PROCEDURE Resource_Yield(
2642 p_group_id IN NUMBER,
2643 p_errnum OUT NOCOPY NUMBER,
2644 p_errmesg OUT NOCOPY VARCHAR2 )
2645 IS
2646
2647 proc_name VARCHAR2 (40);
2648
2649 BEGIN
2650 proc_name := 'Resource_Yield';
2651 -- Incorporate the resource information
2652 -- Note : We should probably summarize across the
2653 -- Operation Sequences for a Department
2654 ----dbms_output.put_line('Inside the Resource Yield');
2655
2656 insert into wip_indicators_temp(
2657 group_id,
2658 organization_id,
2659 wip_entity_id,
2660 operation_seq_num,
2661 department_id,
2662 department_code,
2663 resource_id,
2664 resource_code,
2665 total_quantity,
2666 scrap_quantity,
2667 transaction_date,
2668 indicator_type,
2669 process_phase,
2670 last_update_date,
2671 last_updated_by,
2672 creation_date,
2673 created_by,
2674 program_application_id )
2675 select
2676 wit.group_id,
2677 wit.organization_id,
2678 wit.wip_entity_id,
2679 wit.operation_seq_num,
2680 wit.department_id,
2681 wit.department_code,
2682 wor.resource_id,
2683 br.resource_code,
2684 sum(wit.total_quantity),
2685 sum(wit.scrap_quantity),
2686 wit.transaction_date, -- already trunc'ed
2687 wit.indicator_type,
2688 WIP_RES_YIELD, /* This is the resource phase */
2689 wit.last_update_date,
2690 wit.last_updated_by,
2691 wit.creation_date,
2692 wit.created_by,
2693 wit.program_application_id
2694 from wip_indicators_temp wit,
2695 bom_resources br,
2696 wip_operation_resources wor
2697 where wor.wip_entity_id = wit.wip_entity_id
2698 and wor.operation_seq_num = wit.operation_seq_num
2699 and wit.indicator_type = WIP_YIELD
2700 and wit.process_phase = WIP_DEPT_YIELD
2701 and br.organization_id = wor.organization_id
2702 and br.resource_id = wor.resource_id
2703 group by
2704 wit.group_id,
2705 wit.organization_id,
2706 wit.wip_entity_id,
2707 wit.operation_seq_num,
2708 wit.department_id,
2709 wit.department_code,
2710 wor.resource_id,
2711 br.resource_code,
2712 wit.transaction_date,
2713 wit.indicator_type,
2714 WIP_RES_YIELD,
2715 wit.last_update_date,
2716 wit.last_updated_by,
2717 wit.creation_date,
2718 wit.created_by,
2719 wit.program_application_id ;
2720
2721 commit ;
2722
2723 -- clean up the tables
2724 delete from wip_indicators_temp
2725 where indicator_type = WIP_YIELD
2726 and process_phase = 1 ;
2727
2728 commit ;
2729
2730 p_errnum := 0;
2731 p_errmesg := '';
2732
2733 RETURN ;
2734
2735 EXCEPTION
2736
2737 WHEN OTHERS
2738 THEN
2739 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
2740 p_errnum := -1;
2741 p_errmesg := (proc_name || ':' || sqlerrm);
2742 RAISE; -- propagate to calling function
2743
2744 END Resource_Yield;
2745
2746 /* Move_info_into_summary
2747 Move the utilization, efficiency, yield data into the
2748 summary table, wip_bis_prod_indicators.
2749 */
2750 PROCEDURE Move_Info_Into_Summary (
2751 p_group_id IN NUMBER,
2752 p_errnum OUT NOCOPY NUMBER,
2753 p_errmesg OUT NOCOPY VARCHAR2
2754 )
2755 is
2756 x_phase VARCHAR2(10);
2757 begin
2758
2759 -- Split wip_indicators_temp into three temp tables with
2760 -- efficiency, yield and utilization info for faster data
2761 -- manipulation. Now we don't need to join to the
2762 -- entire WIT, but only the specialized temp tables.
2763
2764 -- efficiency table -- indicator = WIP_EFFICIENCY = 1
2765 populate_eff_temp_table ('WIP_BIS_EFF_TEMP', 1, p_group_id);-- for bug 3280647
2766 --populate_temp_table ('WIP_BIS_EFF_TEMP', 1, p_group_id);
2767 Commit;
2768 -- utilization tabe - indicator = WIP_UTILIZATION = 2
2769 populate_temp_table ('WIP_BIS_UTZ_TEMP', 2, p_group_id);
2770 commit;
2771 -- yield table - indicator = WIP_YIELD = 3
2772 populate_temp_table ('WIP_BIS_YLD_TEMP', 3, p_group_id);
2773
2774 commit;
2775
2776 -- Phase I: Move records that have utilization, yield and
2777 -- efficiency info into summary table.
2778 x_phase := 'I';
2779 if g_debug = 1 then
2780 fnd_file.put_line (fnd_file.log,
2781 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2782 fnd_file.put_line(fnd_file.log, 'Before Stage 5 Phase I');
2783 end if ;
2784 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2785 --dbms_output.put_line('Before Stage 5 Phase I');
2786
2787 -- The new version of this query.
2788 -- A simple decomposition done in the simple_decomp function
2789 simple_decomp (p_group_id);
2790 commit;
2791
2792 -- Phase II:
2793 -- Insert Efficiency information into the summary table
2794 -- for all the org_id, wip_id, op_seq, dept_id, res_id,
2795 -- txn_date that did not get moved in Phase I
2796 x_phase := 'II';
2797 if g_debug = 1 then
2798 fnd_file.put_line (fnd_file.log,
2799 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2800 fnd_file.put_line(fnd_file.log, 'Before Stage 5 Phase II');
2801 end if ;
2802 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2803 --dbms_output.put_line('Before Stage 5 Phase II');
2804
2805 insert into wip_bis_prod_indicators (
2806 ORGANIZATION_ID,
2807 WIP_ENTITY_ID,
2808 INVENTORY_ITEM_ID,
2809 TRANSACTION_DATE,
2810 OPERATION_SEQ_NUM,
2811 DEPARTMENT_ID,
2812 DEPARTMENT_CODE,
2813 RESOURCE_ID,
2814 RESOURCE_CODE,
2815 STANDARD_HOURS,
2816 APPLIED_HOURS_PRD,
2817 LAST_UPDATE_DATE,
2818 LAST_UPDATED_BY,
2819 CREATION_DATE,
2820 CREATED_BY,
2821 LAST_UPDATE_LOGIN,
2822 REQUEST_ID,
2823 PROGRAM_APPLICATION_ID,
2824 PROGRAM_UPDATE_DATE)
2825 select wit.organization_id,
2826 wit.wip_entity_id,
2827 we.primary_item_id,
2828 trunc(wit.transaction_date),
2829 wit.operation_seq_num,
2830 wit.department_id,
2831 wit.department_code,
2832 wit.resource_id,
2833 wit.resource_code,
2834 wit.standard_units,
2835 wit.applied_units_prd,
2836 wit.last_update_date,
2837 wit.last_updated_by,
2838 wit.creation_date,
2839 wit.created_by,
2840 wit.last_update_login,
2841 wit.request_id,
2842 wit.program_application_id,
2843 sysdate
2844 from wip_entities we,
2845 wip_bis_eff_temp wit
2846 where we.wip_entity_id = wit.wip_entity_id
2847 and we.organization_id = wit.organization_id
2848 and wit.indicator_type = WIP_EFFICIENCY
2849 and not exists (
2850 select null
2851 from wip_bis_prod_indicators wbpi
2852 where wit.organization_id = wbpi.organization_id
2853 and wit.wip_entity_id = wbpi.wip_entity_id
2854 and wit.operation_seq_num = wbpi.operation_seq_num
2855 and wit.department_id = wbpi.department_id
2856 and wit.resource_id = wbpi.resource_id
2857 and wbpi.transaction_date between
2858 trunc(wit.transaction_date)
2859 and trunc(wit.transaction_date) + 0.99999
2860 ) ;
2861
2862 commit ;
2863
2864 -- gather stats on table to allow index access
2865 If nvl(WIP_CALL_LOG,-1) =1 then
2866 fnd_stats.gather_table_stats (g_wip_schema, 'WIP_BIS_PROD_INDICATORS',
2867 cascade => true);
2868 End If;
2869
2870 -- Phase III:
2871 -- Update Utilization information into
2872 -- the summary table for the new efficiency records added.
2873
2874 x_phase := 'III';
2875 if g_debug = 1 then
2876 fnd_file.put_line (fnd_file.log,
2877 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2878 fnd_file.put_line(fnd_file.log, 'Before Stage 5 Phase III');
2879 end if ;
2880 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2881 --dbms_output.put_line('Before Stage 5 Phase III');
2882
2883 update/*+ PARALLEL*/ wip_bis_prod_indicators wbpi
2884 set (wbpi.APPLIED_HOURS_UTZ, wbpi.AVAILABLE_HOURS) =
2885 ( select wit.applied_units_utz, wit.available_units
2886 from wip_bis_utz_temp wit
2887 where wit.organization_id = wbpi.organization_id
2888 and wit.wip_entity_id = wbpi.wip_entity_id
2889 and wit.operation_seq_num = wbpi.operation_seq_num
2890 and wit.department_id = wbpi.department_id
2891 and wit.resource_id = wbpi.resource_id
2892 and wit.transaction_date BETWEEN
2893 trunc(wbpi.transaction_date)
2894 AND trunc (wbpi.transaction_date) + 0.99999
2895 and wit.indicator_type = WIP_UTILIZATION
2896 )
2897 where wbpi.APPLIED_HOURS_UTZ is null
2898 and wbpi.AVAILABLE_HOURS is null ;
2899 commit;
2900 -- Addtion to remove available hours from other than min(wip_entity_id) on same day bug -3662056
2901 update /*+ INDEX(wbpi WIP_BIS_PROD_INDICATORS_N8) */ wip_bis_prod_indicators wbpi
2902 set wbpi.AVAILABLE_HOURS = 0
2903 where wbpi.AVAILABLE_HOURS is not null
2904 and wbpi.wip_entity_id <>
2905 (select /*+ INDEX(wit WIP_BIS_PROD_INDICATORS_N8) INDEX_FFS(wit WIP_BIS_PROD_INDICATORS_N8)*/ min(wit.wip_entity_id)
2906 from wip_bis_prod_indicators wit
2907 where trunc(wit.transaction_date) =trunc(wbpi.transaction_date)
2908 and wbpi.resource_id = wit.resource_id
2909 and wbpi.department_id = wit.department_id
2910 and wbpi.organization_id = wit.organization_id );
2911
2912 --- Fix when same resource is used for more than one step bug -3662056
2913 update /*+ INDEX(wbpi WIP_BIS_PROD_INDICATORS_N8) */ wip_bis_prod_indicators wbpi
2914 set wbpi.AVAILABLE_HOURS = 0
2915 where wbpi.AVAILABLE_HOURS is not null
2916 and wbpi.operation_seq_num <>
2917 (select /*+ INDEX(wit WIP_BIS_PROD_INDICATORS_N8) INDEX_FFS(wit WIP_BIS_PROD_INDICATORS_N8)*/ min(wit.operation_seq_num)
2918 from wip_bis_prod_indicators wit
2919 where trunc(wit.transaction_date) =trunc(wbpi.transaction_date)
2920 and wbpi.resource_id = wit.resource_id
2921 and wbpi.department_id = wit.department_id
2922 and wbpi.organization_id = wit.organization_id
2923 and wbpi.wip_entity_id = wip_entity_id);
2924
2925 commit;
2926 -- Phase IV:
2927 -- Update Yield/Scrap information into
2928 -- the summary table for the new efficiency records added
2929 x_phase := 'IV';
2930 if g_debug = 1 then
2931 fnd_file.put_line (fnd_file.log,
2932 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2933 fnd_file.put_line(fnd_file.log, 'Before Stage 5 Phase IV');
2934 end if ;
2935 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2936 --dbms_output.put_line('Before Stage 5 Phase IV');
2937
2938 update wip_bis_prod_indicators wbpi
2939 set (wbpi.TOTAL_QUANTITY, wbpi.SCRAp_QUANTITY) =
2940 ( select wit.total_quantity, wit.scrap_quantity
2941 from wip_bis_yld_temp wit
2942 where wit.organization_id = wbpi.organization_id
2943 and wit.wip_entity_id = wbpi.wip_entity_id
2944 and wit.operation_seq_num = wbpi.operation_seq_num
2945 and wit.department_id = wbpi.department_id
2946 and wit.resource_id = wbpi.resource_id
2947 and wit.transaction_date BETWEEN trunc(wbpi.transaction_date)
2948 and trunc (wbpi.transaction_date) + 0.99999
2949 and wit.indicator_type = WIP_YIELD
2950 and wit.process_phase = WIP_RES_YIELD
2951 )
2952 where wbpi.TOTAL_QUANTITY is null
2953 and wbpi.SCRAP_QUANTITY is null ;
2954
2955 commit;
2956
2957 -- Phase V:
2958 -- Insert all utilization records that have not been inserted yet.
2959 x_phase := 'V';
2960 if g_debug = 1 then
2961 fnd_file.put_line (fnd_file.log,
2962 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2963 fnd_file.put_line(fnd_file.log, 'Before Stage 5 Phase V');
2964 end if ;
2965 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
2966 --dbms_output.put_line('Before Stage 5 Phase V');
2967
2968 insert into wip_bis_prod_indicators (
2969 ORGANIZATION_ID,
2970 WIP_ENTITY_ID,
2971 INVENTORY_ITEM_ID,
2972 TRANSACTION_DATE,
2973 OPERATION_SEQ_NUM,
2974 DEPARTMENT_ID,
2975 DEPARTMENT_CODE,
2976 RESOURCE_ID,
2977 RESOURCE_CODE,
2978 APPLIED_HOURS_UTZ,
2979 AVAILABLE_HOURS,
2980 LAST_UPDATE_DATE,
2981 LAST_UPDATED_BY,
2982 CREATION_DATE,
2983 CREATED_BY,
2984 LAST_UPDATE_LOGIN,
2985 REQUEST_ID,
2986 PROGRAM_APPLICATION_ID,
2987 PROGRAM_UPDATE_DATE)
2988 select wit.organization_id,
2989 wit.wip_entity_id,
2990 we.primary_item_id,
2991 trunc(wit.transaction_date),
2992 wit.operation_seq_num,
2993 wit.department_id,
2994 wit.department_code,
2995 wit.resource_id,
2996 wit.resource_code,
2997 wit.applied_units_utz,
2998 wit.available_units,
2999 wit.last_update_date,
3000 wit.last_updated_by,
3001 wit.creation_date,
3002 wit.created_by,
3003 wit.last_update_login,
3004 wit.request_id,
3005 wit.program_application_id,
3006 sysdate
3007 from wip_entities we,
3008 wip_bis_utz_temp wit
3009 where we.wip_entity_id = wit.wip_entity_id
3010 and we.organization_id = wit.organization_id
3011 and wit.indicator_type = WIP_UTILIZATION
3012 and not exists (
3013 select null
3014 from wip_bis_prod_indicators wbpi
3015 where wit.organization_id = wbpi.organization_id
3016 and wit.wip_entity_id = wbpi.wip_entity_id
3017 and wit.operation_seq_num = wbpi.operation_seq_num
3018 and wit.department_id = wbpi.department_id
3019 and wit.resource_id = wbpi.resource_id
3020 and wbpi.transaction_date between
3021 trunc(wit.transaction_date)
3022 and trunc(wit.transaction_date) + 0.99999);
3023
3024 commit ;
3025
3026 -- gather stats on table to allow index access
3027 If nvl(WIP_CALL_LOG,-1) =1 then
3028 fnd_stats.gather_table_stats (g_wip_schema, 'WIP_BIS_PROD_INDICATORS',
3029 cascade => true);
3030 End If;
3031 -- Phase VI:
3032 -- Update Yield information into
3033 -- the summary table for the new utilization records added
3034
3035 x_phase := 'VI';
3036 if g_debug = 1 then
3037 fnd_file.put_line (fnd_file.log,
3038 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3039 fnd_file.put_line(fnd_file.log, 'Before Stage 5 Phase VI');
3040 end if ;
3041 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3042 --dbms_output.put_line('Before Stage 5 Phase VI');
3043
3044 update wip_bis_prod_indicators wbpi
3045 set (wbpi.total_quantity, wbpi.scrap_quantity) =
3046 ( select wit.total_quantity, wit.scrap_quantity
3047 from wip_bis_yld_temp wit
3048 where wit.organization_id = wbpi.organization_id
3049 and wit.wip_entity_id = wbpi.wip_entity_id
3050 and wit.operation_seq_num = wbpi.operation_seq_num
3051 and wit.department_id = wbpi.department_id
3052 and wit.resource_id = wbpi.resource_id
3053 and wit.transaction_date BETWEEN trunc(wbpi.transaction_date)
3054 and trunc (wbpi.transaction_date) + 0.99999
3055 and wit.indicator_type = WIP_YIELD
3056 and wit.process_phase = WIP_RES_YIELD
3057 )
3058 where wbpi.total_quantity is null
3059 and wbpi.scrap_quantity is null ;
3060
3061 commit;
3062
3063 -- Phase VII:
3064 -- Insert all the yield info not already inserted/updated
3065 -- into the summary table.
3066
3067 x_phase := 'VII';
3068 if g_debug = 1 then
3069 fnd_file.put_line(fnd_file.log, 'Before Stage 5 Phase VII');
3070 fnd_file.put_line (fnd_file.log,
3071 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3072 end if ;
3073 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3074 --dbms_output.put_line('Before Stage 5 Phase VII');
3075
3076 insert into wip_bis_prod_indicators (
3077 ORGANIZATION_ID,
3078 WIP_ENTITY_ID,
3079 INVENTORY_ITEM_ID,
3080 TRANSACTION_DATE,
3081 OPERATION_SEQ_NUM,
3082 DEPARTMENT_ID,
3083 DEPARTMENT_CODE,
3084 RESOURCE_ID,
3085 RESOURCE_CODE,
3086 TOTAL_QUANTITY,
3087 SCRAP_QUANTITY,
3088 LAST_UPDATE_DATE,
3089 LAST_UPDATED_BY,
3090 CREATION_DATE,
3091 CREATED_BY,
3092 LAST_UPDATE_LOGIN,
3093 REQUEST_ID,
3094 PROGRAM_APPLICATION_ID,
3095 PROGRAM_UPDATE_DATE)
3096 select wit.organization_id,
3097 wit.wip_entity_id,
3098 we.primary_item_id,
3099 trunc(wit.transaction_date),
3100 wit.operation_seq_num,
3101 wit.department_id,
3102 wit.department_code,
3103 wit.resource_id,
3104 wit.resource_code,
3105 wit.total_quantity,
3106 wit.scrap_quantity,
3107 wit.last_update_date,
3108 wit.last_updated_by,
3109 wit.creation_date,
3110 wit.created_by,
3111 wit.last_update_login,
3112 wit.request_id,
3113 wit.program_application_id,
3114 sysdate
3115 from wip_entities we,
3116 wip_bis_yld_temp wit
3117 where we.wip_entity_id = wit.wip_entity_id
3118 and we.organization_id = wit.organization_id
3119 and wit.indicator_type = WIP_YIELD
3120 and wit.process_phase = WIP_RES_YIELD
3121 and not exists
3122 (select null
3123 from wip_bis_prod_indicators wbpi
3124 where wit.organization_id = wbpi.organization_id
3125 and wit.wip_entity_id = wbpi.wip_entity_id
3126 and wit.operation_seq_num = wbpi.operation_seq_num
3127 and wit.department_id = wbpi.department_id
3128 and wit.resource_id = wbpi.resource_id
3129 and wbpi.transaction_date between trunc(wit.transaction_date)
3130 and trunc(wit.transaction_date) + 0.99999);
3131
3132 commit ;
3133
3134 -- gather stats on table to allow index access
3135 If nvl(WIP_CALL_LOG,-1) =1 then
3136 fnd_stats.gather_table_stats (g_wip_schema, 'WIP_BIS_PROD_INDICATORS',
3137 cascade => true);
3138 End if;
3139
3140 -- Phase VIII:
3141 -- Move the Utilization Information for the
3142 -- resources with zero utilization into the
3143 -- the Summary table wip_bis_prod_indicators
3144
3145 x_phase := 'VIII';
3146 if g_debug = 1 then
3147 fnd_file.put_line(fnd_file.log, 'Before Stage 5 Phase VIII');
3148 fnd_file.put_line (fnd_file.log,
3149 to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3150 end if ;
3151 --dbms_output.put_line (to_char (sysdate, 'DD-MON-YYYY HH24:MI:SS'));
3152 --dbms_output.put_line('Before Stage 5 Phase VIII');
3153
3154 Move_Utz_Info(
3155 p_group_id => p_group_id,
3156 p_errnum => p_errnum,
3157 p_errmesg => p_errmesg );
3158
3159
3160 p_errnum := 0;
3161 p_errmesg := '';
3162 return ;
3163
3164
3165 -- The exceptions in this will be handled by the calling function
3166 exception
3167
3168 when others then
3169
3170 if g_debug = 1 then
3171 fnd_file.put_line(fnd_file.log,
3172 'Failed in Move_Info_Into_Summary in Stage 5 phase : '
3173 ||x_phase);
3174 fnd_file.put_line(fnd_file.log, SQLCODE);
3175 fnd_file.put_line(fnd_file.log,SQLERRM);
3176 end if ;
3177
3178 -- dbms_output.put_line('Failed in Stage 5 phase : '||x_phase);
3179 -- dbms_output.put_line(SQLCODE);
3180 -- dbms_output.put_line(SQLERRM);
3181
3182 p_errnum := -1 ;
3183 p_errmesg := 'Failed in Stage 5 Phase : '|| x_phase||
3184 substr(SQLERRM,1,125);
3185
3186 -- returns to populate_summary_table, so don't raise exception.
3187
3188 commit ;
3189 return ;
3190
3191 End Move_Info_Into_Summary;
3192
3193
3194 /* Move_Yield_Info
3195 Move the yield information for every department into the
3196 summary table wip_bis_prod_dept_yield.
3197 */
3198 PROCEDURE Move_Yield_Info (
3199 p_group_id IN NUMBER,
3200 p_errnum OUT NOCOPY NUMBER,
3201 p_errmesg OUT NOCOPY VARCHAR2) IS
3202
3203 x_phase VARCHAR2(10);
3204
3205 proc_name VARCHAR2 (40);
3206
3207 BEGIN
3208 proc_name := 'Move_Yield_Info';
3209
3210 x_phase := 'I';
3211 IF g_debug = 1 THEN
3212 fnd_file.put_line(fnd_file.log, 'Before Stage 6 Phase I');
3213 END IF ;
3214 ----dbms_output.put_line('Before Stage 6 Phase I');
3215
3216 -- insert efficiency into the summary table
3217 INSERT INTO wip_bis_prod_dept_yield (
3218 ORGANIZATION_ID,
3219 WIP_ENTITY_ID,
3220 INVENTORY_ITEM_ID,
3221 TRANSACTION_DATE,
3222 OPERATION_SEQ_NUM,
3223 DEPARTMENT_ID,
3224 DEPARTMENT_CODE,
3225 TOTAL_QUANTITY,
3226 SCRAP_QUANTITY,
3227 LAST_UPDATE_DATE,
3228 LAST_UPDATED_BY,
3229 CREATION_DATE,
3230 CREATED_BY,
3231 LAST_UPDATE_LOGIN,
3232 REQUEST_ID,
3233 PROGRAM_APPLICATION_ID,
3234 PROGRAM_UPDATE_DATE)
3235 SELECT wit.organization_id,
3236 wit.wip_entity_id,
3237 we.primary_item_id,
3238 wit.transaction_date,
3239 wit.operation_seq_num,
3240 wit.department_id,
3241 wit.department_code,
3242 wit.total_quantity,
3243 wit.scrap_quantity,
3244 wit.last_update_date,
3245 wit.last_updated_by,
3246 wit.creation_date,
3247 wit.created_by,
3248 wit.last_update_login,
3249 wit.request_id,
3250 wit.program_application_id,
3251 sysdate
3252 FROM wip_entities we,
3253 wip_bis_yld_temp wit
3254 WHERE we.wip_entity_id = wit.wip_entity_id
3255 AND we.organization_id = wit.organization_id
3256 AND wit.indicator_type = WIP_YIELD
3257 AND wit.process_phase = WIP_DEPT_YIELD;
3258
3259 COMMIT ;
3260
3261 p_errnum := 0;
3262 p_errmesg := '';
3263
3264 RETURN ;
3265
3266
3267 EXCEPTION
3268
3269 WHEN OTHERS THEN
3270
3271 IF g_debug = 1 THEN
3272 fnd_file.put_line(fnd_file.log,
3273 'Failed in Move_Yield_Info in Stage 6 phase : '||
3274 x_phase);
3275 fnd_file.put_line(fnd_file.log, SQLCODE);
3276 fnd_file.put_line(fnd_file.log,SQLERRM);
3277 END IF ;
3278 ----dbms_output.put_line('Failed in Stage 6 phase : '||x_phase);
3279 ----dbms_output.put_line(SQLCODE);
3280 ----dbms_output.put_line(SQLERRM);
3281 p_errnum := -1 ;
3282 p_errmesg := 'Failed in Stage 6 Phase : '|| x_phase ||
3283 substr(SQLERRM,1,125);
3284
3285 -- returns to populate_summary_table, so don't raise exception.
3286
3287 COMMIT ;
3288 RETURN ;
3289
3290 END Move_Yield_Info;
3291
3292
3293
3294 /* Move_Utz_Info
3295 Move the utilization information that doesnot have
3296 job and op seq reference from mrp_net_resource_avail
3297 into wip_bis_prod_indicators
3298 */
3299 /* PROCEDURE Move_Utz_Info( --comment for bug 3662056
3300 p_group_id IN NUMBER,
3301 p_errnum OUT NOCOPY NUMBER,
3302 p_errmesg OUT NOCOPY VARCHAR2) IS
3303
3304 x_phase VARCHAR2(10);
3305 x_org_id NUMBER;
3306
3307 l_all_available_hours NUMBER;
3308 l_wit_utz_size NUMBER ;
3309
3310 proc_name VARCHAR2 (40) ;
3311
3312 BEGIN
3313 l_all_available_hours := 0;
3314 l_wit_utz_size := 0;
3315 proc_name := 'Move_Utz_Info';
3316
3317 -- The original insert into wip_bis_prod_indicators was using
3318 -- wip_indicators_temp's UTILIZATION rows and computing the
3319 -- measure available_hours as:
3320 -- sum (((mnra.to_time-mnra.from_time)/3600)*mnra.capacity_units)
3321 -- for 4 attributes:
3322 -- 1. organization_id
3323 -- 2. department_id
3324 -- 3. resource_id
3325 -- 4. transaction_date
3326 -- Where any one of these 4 fields in WIT did not
3327 -- match those in MNRA. In other words, we had a
3328 -- nested loop sum on MNRA that was performing badly. Instead,
3329 -- we have now summed up the above measure by these 4 attributes
3330 -- (actually, there is also a simulation_set field which is always
3331 -- NULL for us and can be ignored) in a temp table called
3332 -- wip_bis_mnra_temp. Therefore, the same sum as before
3333 -- can be computed by summing across the table, and subtracting
3334 -- each record's value from the full sum over the table.
3335 -- WIP_BIS_MNRA_TEMP stores exactly one row per distinct set of
3336 -- the 4 attributes.
3337 SELECT sum (available_hours)
3338 INTO l_all_available_hours
3339 FROM wip_bis_mnra_temp;
3340
3341
3342 -- Based on the old join conditions, every combination of
3343 -- org, dept, res, date in MNRA was added up for every row
3344 -- in WIT that it did not match.
3345 SELECT count (*)
3346 INTO l_wit_utz_size
3347 FROM (SELECT distinct organization_id,
3348 resource_id,
3349 department_id,
3350 transaction_date
3351 FROM wip_bis_utz_temp
3352 WHERE process_phase = WIP_UTZ_PHASE_TWO
3353 AND indicator_type = WIP_UTILIZATION) wit_distinct;
3354
3355 -- Note that from Populate_Utilization, we know that
3356 -- org, dept, resource and transaction_date make a primary
3357 -- key for the UTILIZATION data, and the same is true of
3358 -- wip_bis_mnra_temp. Hence the join here need not do
3359 -- any group by etc.
3360 insert into wip_bis_prod_indicators(
3361 organization_id,
3362 wip_entity_id,
3363 operation_seq_num,
3364 department_id,
3365 department_code,
3366 resource_id,
3367 resource_code,
3368 applied_hours_utz,
3369 AVAILABLE_HOURS,
3370 transaction_date,
3371 last_update_date,
3372 last_updated_by,
3373 creation_date,
3374 created_by,
3375 program_application_id)
3376 select
3377 mnra.organization_id,
3378 null,
3379 null,
3380 mnra.department_id,
3381 bd.department_code,
3382 mnra.resource_id,
3383 br.resource_code,
3384 null,
3385 decode (wit.net_occurances,
3386 NULL, l_wit_utz_size * mnra.available_hours,
3387 (l_wit_utz_size - net_occurances) * mnra.available_hours),
3388 mnra.shift_date, -- already trunc'ed
3389 sysdate,
3390 g_userid,
3391 SYSDATE,
3392 g_userid,
3393 g_applicationid
3394 FROM
3395 (SELECT organization_id,
3396 department_id,
3397 resource_id,
3398 transaction_date,
3399 count (*) net_occurances
3400 FROM wip_bis_utz_temp
3401 WHERE indicator_type = WIP_UTILIZATION
3402 AND process_phase = WIP_UTZ_PHASE_TWO
3403 GROUP BY organization_id,
3404 department_id,
3405 resource_id,
3406 transaction_date) wit,
3407 bom_resources br,
3408 bom_departments bd,
3409 wip_bis_mnra_temp mnra,
3410 mtl_units_of_measure muom
3411 where mnra.shift_date BETWEEN trunc(g_date_from)
3412 AND trunc (g_date_to) + 0.99999
3413 and br.resource_id = mnra.resource_id
3414 and br.unit_of_measure = muom.uom_code
3415 and muom.uom_class = g_uom_class
3416 and br.organization_id = mnra.organization_id
3417 and bd.department_id = mnra.department_id
3418 and bd.organization_id = mnra.organization_id
3419 and mnra.shift_date = wit.transaction_date(+) -- both are trunc'ed
3420 and mnra.resource_id = wit.resource_id(+)
3421 and mnra.department_id = wit.department_id(+)
3422 and mnra.organization_id = wit.organization_id(+);
3423
3424 commit ;
3425
3426
3427 -- gather stats on table to allow index access
3428 fnd_stats.gather_table_stats (g_wip_schema, 'WIP_BIS_PROD_INDICATORS',
3429 cascade => true);
3430
3431 p_errnum := 0;
3432 p_errmesg := '';
3433
3434 RETURN;
3435
3436 EXCEPTION
3437
3438 WHEN OTHERS
3439 THEN
3440 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
3441 p_errnum := -1;
3442 p_errmesg := (proc_name || ':' || sqlerrm);
3443 RAISE; -- propagate to calling function
3444
3445 End Move_Utz_Info ;*/
3446
3447 /*
3448 Clean up all the temp tables other than WIP_INDICATORS_TEMP
3449 that have been used to stage data for better SQL performance.
3450 */
3451 -- Modified Move_Utz_Info bug -3662056
3452 Procedure Move_Utz_Info(
3453 p_group_id in number,
3454 p_errnum OUT NOCOPY NUMBER,
3455 p_errmesg OUT NOCOPY VARCHAR2
3456 ) is
3457 /* ************************************************************
3458 Cursor to get all the inventory organizations
3459 ******************************************************** */
3460 CURSOR All_Orgs is
3461 SELECT distinct
3462 organization_id
3463 FROM mtl_parameters
3464 WHERE process_enabled_flag <> 'Y'; -- Added to exclude process orgs after R12 uptake
3465
3466 x_phase VARCHAR2(10);
3467 x_org_id NUMBER;
3468
3469 begin
3470
3471
3472 /*
3473 We do a commit per organization to avoid
3474 rollback segment problems. Else it is not
3475 required.
3476 */
3477
3478 x_phase := 'I';
3479 if g_debug = 1 then
3480 fnd_file.put_line(fnd_file.log, 'Before Stage 7 Phase I');
3481 end if ;
3482
3483
3484 FOR Org_Rec IN All_Orgs LOOP
3485
3486 x_org_id := Org_Rec.organization_id ;
3487
3488 /* Bug 3589936 - Below insert does not take care of shift times when to_time is less
3489 than from_time for available_units. If the shift starts late night today and ends
3490 tomorrow morning, then to_time will be less than the from_time. Now added decode
3491 and sign to take care of the same */
3492
3493 insert into wip_bis_prod_indicators(
3494 organization_id,
3495 wip_entity_id,
3496 operation_seq_num,
3497 department_id,
3498 department_code,
3499 resource_id,
3500 resource_code,
3501 applied_hours_utz,
3502 available_hours,
3503 transaction_date,
3504 last_update_date,
3505 last_updated_by,
3506 creation_date,
3507 created_by,
3508 program_application_id)
3509 select
3510 mnra1.organization_id,
3511 null,
3512 null,
3513 mnra1.department_id,
3514 mnra1.department_code,
3515 mnra1.resource_id,
3516 mnra1.resource_code,
3517 null,
3518 mnra1.available_hours,
3519 mnra1.shift_date,
3520 sysdate,
3521 g_userid,
3522 SYSDATE,
3523 g_userid,
3524 g_applicationid
3525 from
3526 (select
3527 mnra.organization_id organization_id,
3528 mnra.department_id department_id,
3529 bd.department_code department_code,
3530 mnra.resource_id resource_id,
3531 br.resource_code resource_code,
3532 decode(sum(mnra.shift_num),
3533 0, sum(capacity_units)*24,
3534 sum(((decode(sign(mnra.to_time - mnra.from_time),
3535 -1, ( 86400 - mnra.from_time ) + mnra.to_time,
3536 1, ( mnra.to_time - mnra.from_time ) , 0 ))/3600)*mnra.capacity_units)) available_hours,
3537 trunc(mnra.shift_date) shift_date
3538 FROM
3539 bom_resources br,
3540 bom_departments bd,
3541 mrp_net_resource_avail mnra,
3542 mtl_units_of_measure muom
3543 where
3544 trunc(mnra.shift_date) between trunc(g_date_from) and trunc(g_date_to)
3545 and trunc(mnra.shift_date) >= trunc(br.creation_date)
3546 and br.resource_id = mnra.resource_id
3547 and br.unit_of_measure = muom.uom_code
3548 and muom.uom_class = g_uom_class
3549 and br.organization_id = mnra.organization_id
3550 and bd.department_id = mnra.department_id
3551 and bd.organization_id = mnra.organization_id
3552 and mnra.organization_id = x_org_id
3553 group by mnra.organization_id,
3554 mnra.department_id,
3555 mnra.resource_id,
3556 mnra.shift_date,
3557 bd.department_code,
3558 br.resource_code ) mnra1
3559 where not exists
3560 (select null
3561 from wip_indicators_temp wit
3562 where wit.group_id = p_group_id
3563 and wit.indicator_type = WIP_UTILIZATION
3564 and wit.process_phase = WIP_UTZ_PHASE_TWO
3565 and mnra1.shift_date = trunc(wit.transaction_date)
3566 and mnra1.resource_id = wit.resource_id
3567 and mnra1.department_id = wit.department_id
3568 and mnra1.organization_id = wit.organization_id );
3569
3570 -- to avoid large rollback segments
3571 commit ;
3572
3573 END LOOP ;
3574
3575
3576 exception
3577
3578 when others then
3579 if g_debug = 1 then
3580 fnd_file.put_line(fnd_file.log,'Failed in Stage 7 phase : '||x_phase ||
3581 ' for Organization_id : '|| to_char(x_org_id) );
3582 fnd_file.put_line(fnd_file.log, SQLCODE);
3583 fnd_file.put_line(fnd_file.log,SQLERRM);
3584 end if ;
3585 ----dbms_output.put_line('Failed in Stage 7 phase : '||x_phase ||
3586 -- ' for Organization_id : ' || to_char(x_org_id));
3587 ----dbms_output.put_line(SQLCODE);
3588 ----dbms_output.put_line(SQLERRM);
3589 p_errnum := -1 ;
3590 p_errmesg := 'Failed in Stage 5 Phase : '||x_phase|| ' for Organization_id : ' ||
3591 to_char(x_org_id) || ' ' || substr(SQLERRM,1,105);
3592 Delete_Temp_Info(p_group_id=>p_group_Id);
3593 delete from wip_bis_prod_indicators
3594 where existing_flag is null ;
3595 delete from wip_bis_prod_dept_yield
3596 where existing_flag is null ;
3597 commit ;
3598 return ;
3599
3600
3601 End Move_Utz_Info ;
3602
3603
3604 PROCEDURE Post_Move_CleanUp(
3605 p_group_id IN NUMBER,
3606 p_errnum OUT NOCOPY NUMBER,
3607 p_errmesg OUT NOCOPY VARCHAR2 )
3608 IS
3609
3610 x_phase VARCHAR2(10);
3611
3612 BEGIN
3613
3614
3615 x_phase := 'I';
3616 IF g_debug = 1 THEN
3617 fnd_file.put_line(fnd_file.log, 'Before Stage 9 Phase I');
3618 END IF ;
3619
3620 -- clean out all the temp tables.
3621 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_wip_schema ||
3622 '.WIP_BIS_MNRA_TEMP';
3623
3624 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_wip_schema ||
3625 '.WIP_BIS_EFF_TEMP';
3626
3627 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_wip_schema ||
3628 '.WIP_BIS_UTZ_TEMP';
3629
3630 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_wip_schema ||
3631 '.WIP_BIS_YLD_TEMP';
3632
3633 IF g_debug = 1 THEN
3634 fnd_file.put_line(fnd_file.log, 'After Stage 9 Phase I');
3635 END IF ;
3636
3637 p_errnum := 0;
3638 p_errmesg := '';
3639
3640 RETURN ;
3641
3642 EXCEPTION
3643
3644 WHEN OTHERS THEN
3645
3646 IF g_debug = 1 THEN
3647 fnd_file.put_line(fnd_file.log,
3648 'Failed in Post_Move_Cleanup in Stage 9 phase : '||x_phase);
3649 fnd_file.put_line(fnd_file.log, SQLCODE);
3650 fnd_file.put_line(fnd_file.log,SQLERRM);
3651 END IF ;
3652 ----dbms_output.put_line('Failed in Stage 9 phase : '||x_phase);
3653 ----dbms_output.put_line(SQLCODE);
3654 ----dbms_output.put_line(SQLERRM);
3655
3656 p_errnum := -1 ;
3657 p_errmesg := 'Failed in Stage 9 Phase : '||x_phase||substr(SQLERRM,1,125);
3658
3659 -- returns to populate_summary_table, so don't raise exception.
3660
3661 END Post_Move_CleanUp ;
3662
3663
3664 /*
3665 Truncate all the indicator data collected in this run of the
3666 program.
3667 */
3668 PROCEDURE Delete_Temp_Info (p_group_id in number)
3669 IS
3670
3671 BEGIN
3672 IF NOT (fnd_installation.get_app_info(
3673 'WIP', g_status, g_industry, g_wip_schema)) THEN
3674
3675 RAISE_APPLICATION_ERROR (-20000,
3676 'Unable to get session information.');
3677
3678 END IF;
3679
3680 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_wip_schema ||
3681 '.WIP_INDICATORS_TEMP';
3682
3683 EXCEPTION
3684 WHEN OTHERS THEN
3685 fnd_file.put_line(fnd_file.log,'Failed in Delete_Temp_Info.');
3686 fnd_file.put_line(fnd_file.log, SQLCODE);
3687 fnd_file.put_line(fnd_file.log,SQLERRM);
3688 ----dbms_output.put_line('Failed in Delete_Temp_Info. ');
3689 ----dbms_output.put_line(SQLCODE);
3690 ----dbms_output.put_line(SQLERRM);
3691
3692 RAISE; -- send to wrapper
3693
3694 END Delete_Temp_Info;
3695
3696 /*
3697 Clean up all staging tables and base tables. Assuming that any backing
3698 up of base tables has already been performed.
3699 */
3700 PROCEDURE Pre_Program_CleanUp(
3701 p_errnum OUT NOCOPY NUMBER,
3702 p_errmesg OUT NOCOPY VARCHAR2 )
3703 IS
3704
3705 BEGIN
3706
3707 -- clean out the fact tables after they have been backed up
3708
3709 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_wip_schema ||
3710 '.WIP_BIS_PROD_INDICATORS';
3711
3712 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_wip_schema ||
3713 '.WIP_BIS_PROD_DEPT_YIELD';
3714
3715 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_wip_schema ||
3716 '.WIP_BIS_PROD_ASSY_YIELD';
3717
3718
3719 -- clean up the temp tables used for staging etc.
3720 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_wip_schema ||
3721 '.WIP_INDICATORS_TEMP';
3722
3723 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_wip_schema ||
3724 '.WIP_BIS_MNRA_TEMP';
3725
3726 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_wip_schema ||
3727 '.WIP_BIS_EFF_TEMP';
3728
3729 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_wip_schema ||
3730 '.WIP_BIS_UTZ_TEMP';
3731
3732 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_wip_schema ||
3733 '.WIP_BIS_YLD_TEMP';
3734
3735
3736 p_errnum := 0;
3737 p_errmesg := '';
3738
3739 RETURN ;
3740
3741 EXCEPTION
3742
3743 WHEN OTHERS THEN
3744
3745 IF g_debug = 1 THEN
3746 fnd_file.put_line(fnd_file.log,'Failed in pre-program Clean Up');
3747 fnd_file.put_line(fnd_file.log, SQLCODE);
3748 fnd_file.put_line(fnd_file.log,SQLERRM);
3749 END IF;
3750
3751 ----dbms_output.put_line('Failed in pre-program Clean Up');
3752 ----dbms_output.put_line(SQLCODE);
3753 ----dbms_output.put_line(SQLERRM);
3754 p_errnum := -1 ;
3755 p_errmesg := 'Failed in pre-program clean up '||substr(SQLERRM,1,125);
3756 -- returns to populate_summary_table, so don't raise exception.
3757
3758
3759 END Pre_Program_CleanUp ;
3760
3761
3762
3763 /* Populate_Assy_Yield
3764 Calculates the Assembly Yield for the
3765 organization - It does not make sense
3766 to calculate the assembly yield from
3767 a department or resource dimension
3768 hence no resource or department
3769 parameters.
3770 */
3771 PROCEDURE Populate_Assy_Yield(
3772 p_organization_id IN NUMBER,
3773 p_date_from IN DATE,
3774 p_date_to IN DATE,
3775 p_userid IN NUMBER,
3776 p_applicationid IN NUMBER,
3777 p_errnum OUT NOCOPY NUMBER,
3778 p_errmesg OUT NOCOPY VARCHAR2)
3779
3780 IS
3781 x_group_id NUMBER;
3782 x_phase VARCHAR2(10);
3783 x_userid NUMBER;
3784 x_appl_id NUMBER;
3785
3786 BEGIN
3787
3788
3789 /* As the entry point for this more than a single
3790 point we have to do the validation in here as
3791 well. Ex :
3792 Concurrent Program
3793 SFCB
3794 */
3795
3796 if p_userid is null then
3797 -- This is an Error Condition
3798 x_userid := fnd_global.user_id ;
3799 else
3800 x_userid := p_userid ;
3801 end if;
3802
3803
3804 if p_applicationid is null then
3805 -- This is an Error Condition
3806 x_appl_id := fnd_global.prog_appl_id ;
3807 else
3808 x_appl_id := p_applicationid ;
3809 end if;
3810
3811 g_userid := x_userid ;
3812 g_applicationid := x_appl_id ;
3813
3814 x_phase := 'I';
3815 if g_debug = 1 then
3816 fnd_file.put_line(fnd_file.log, 'Before Stage 8 Phase I');
3817 end if ;
3818 ----dbms_output.put_line('Before Stage 8 Phase I');
3819
3820
3821 INSERT INTO wip_bis_prod_assy_yield (
3822 organization_id,
3823 wip_entity_id,
3824 inventory_item_id,
3825 transaction_date,
3826 completed_quantity,
3827 last_update_date,
3828 last_updated_by,
3829 creation_date,
3830 created_by,
3831 program_application_id)
3832 SELECT
3833 organization_id,
3834 transaction_source_id,
3835 inventory_item_id,
3836 trunc (transaction_date),
3837 sum (primary_quantity),
3838 sysdate,
3839 g_userid,
3840 sysdate,
3841 g_userid,
3842 g_applicationid
3843 FROM
3844 mtl_material_transactions
3845 WHERE transaction_source_type_id = 5
3846 AND transaction_action_id IN (31,32)
3847 AND organization_id = nvl(p_organization_id, organization_id)
3848 AND transaction_date between
3849 trunc(nvl(p_date_from,transaction_date))
3850 and trunc(nvl(p_date_to,transaction_date)) + 0.99999
3851 GROUP BY
3852 organization_id,
3853 transaction_source_id,
3854 inventory_item_id,
3855 trunc(transaction_date),
3856 sysdate,
3857 g_userid,
3858 sysdate,
3859 g_userid,
3860 g_applicationid ;
3861
3862
3863 x_phase := 'II';
3864 IF g_debug = 1 THEN
3865 fnd_file.put_line(fnd_file.log, 'Before Stage 8 Phase II');
3866 END IF ;
3867 ----dbms_output.put_line('Before Stage 8 Phase II');
3868
3869 /* We will not be interested in a movement transaction between
3870 two scrap transactions for a Job as it evauluates to the
3871 same amount of assemblies being scrapped for that particular
3872 job - dsoosai */
3873 UPDATE wip_bis_prod_assy_yield wbpay
3874 SET wbpay.scrap_quantity = (
3875 SELECT Nvl(sum(decode(wmt.fm_intraoperation_step_type,
3876 5, -1*(primary_quantity),
3877 decode(wmt.to_intraoperation_step_type,
3878 5, primary_quantity,
3879 0 ))),0)
3880 FROM wip_move_transactions wmt
3881 WHERE wmt.wip_entity_id = wbpay.wip_entity_id
3882 AND wmt.organization_id = wbpay.organization_id
3883 AND trunc(wmt.transaction_date)= trunc(wbpay.transaction_date)
3884 AND (wmt.fm_intraoperation_step_type = 5
3885 OR wmt.to_intraoperation_step_type = 5
3886 AND (wmt.fm_intraoperation_step_type <> wmt.to_intraoperation_step_type))); --3280671
3887 -- AND (wmt.fm_intraoperation_step_type <> 5
3888 -- AND wmt.to_intraoperation_step_type <> 5 )));
3889
3890 x_phase := 'III';
3891 IF g_debug = 1 THEN
3892 fnd_file.put_line(fnd_file.log, 'Before Stage 8 Phase III');
3893 END IF ;
3894 ----dbms_output.put_line('Before Stage 8 Phase III');
3895
3896 /* This SQL has a full table scan on wip_bis_prod_assy_yield
3897 because of the trunc operator on the join - if we know a
3898 way out then we should use it as that will reduce the
3899 full table scan to be a range scan
3900 */
3901 INSERT INTO wip_bis_prod_assy_yield(
3902 organization_id,
3903 wip_entity_id,
3904 inventory_item_id,
3905 transaction_date,
3906 completed_quantity,
3907 scrap_quantity,
3908 last_update_date,
3909 last_updated_by,
3910 creation_date,
3911 created_by,
3912 program_application_id )
3913 SELECT
3914 wmt.organization_id,
3915 wmt.wip_entity_id,
3916 we.primary_item_id,
3917 trunc(wmt.transaction_date),
3918 0,
3919 sum(decode(wmt.fm_intraoperation_step_type,
3920 5, -1*(primary_quantity),
3921 decode(wmt.to_intraoperation_step_type,
3922 5, primary_quantity,
3923 0 ))),
3924 sysdate,
3925 g_userid,
3926 sysdate,
3927 g_userid,
3928 g_applicationid
3929 FROM
3930 wip_entities we,
3931 wip_move_transactions wmt
3932 WHERE we.wip_entity_id = wmt.wip_entity_id
3933 AND we.organization_id = wmt.organization_id
3934 AND wmt.organization_id = nvl(p_organization_id,
3935 wmt.organization_id)
3936 AND wmt.transaction_date BETWEEN
3937 trunc(nvl(p_date_from,wmt.transaction_date))
3938 AND trunc(nvl(p_date_to,wmt.transaction_date)) + 0.99999
3939 AND (wmt.fm_intraoperation_step_type = 5
3940 OR wmt.to_intraoperation_step_type = 5
3941 AND (wmt.fm_intraoperation_step_type <> wmt.to_intraoperation_step_type)) --3280671
3942 -- AND (wmt.fm_intraoperation_step_type <> 5
3943 --AND wmt.to_intraoperation_step_type <> 5))
3944 AND NOT exists (
3945 SELECT 'X'
3946 FROM wip_bis_prod_assy_yield wbpay1
3947 WHERE wbpay1.wip_entity_id = wmt.wip_entity_id
3948 AND wbpay1.organization_id = wmt.organization_id
3949 AND wbpay1.transaction_date BETWEEN
3950 trunc(wmt.transaction_date) AND
3951 trunc (wmt.transaction_date) + 0.99999)
3952 GROUP BY
3953 wmt.organization_id,
3954 wmt.wip_entity_id,
3955 we.primary_item_id,
3956 trunc(wmt.transaction_date),
3957 0,
3958 sysdate,
3959 g_userid,
3960 sysdate,
3961 g_userid,
3962 g_applicationid ;
3963
3964 COMMIT ;
3965
3966 x_phase := 'IV';
3967 IF g_debug = 1 THEN
3968 fnd_file.put_line(fnd_file.log, 'Before Stage 8 Phase IV');
3969 END IF ;
3970 ----dbms_output.put_line('Before Stage 8 Phase IV');
3971
3972 COMMIT ;
3973
3974 p_errnum := 0;
3975 p_errmesg := '';
3976
3977 RETURN;
3978
3979 EXCEPTION
3980
3981 WHEN OTHERS THEN
3982
3983 IF g_debug = 1 THEN
3984 fnd_file.put_line(fnd_file.log,
3985 'Failed in Populate_Assy_Yield in Stage 8 phase : '||x_phase);
3986 fnd_file.put_line(fnd_file.log, SQLCODE);
3987 fnd_file.put_line(fnd_file.log,SQLERRM);
3988 END IF ;
3989 ----dbms_output.put_line('Failed in Stage 8 phase : '||x_phase);
3990 ----dbms_output.put_line(SQLCODE);
3991 ----dbms_output.put_line(SQLERRM);
3992 p_errnum := -1 ;
3993 p_errmesg := 'Failed in Stage 8 Phase : '||x_phase||
3994 substr(SQLERRM,1,125);
3995
3996 -- to make sure there is no garbage returned to SFCB,
3997 -- truncate wip_indicators_temp
3998 Delete_Temp_Info (p_group_id => x_group_id);
3999
4000 -- returns to populate_summary_table, so don't raise exception.
4001
4002 END Populate_Assy_Yield;
4003
4004
4005 /* Calculate_Resource_Avail
4006
4007 */
4008
4009 PROCEDURE Calculate_Resource_Avail (
4010 p_organization_id IN NUMBER,
4011 p_date_from IN DATE,
4012 p_date_to IN DATE,
4013 p_department_id IN NUMBER,
4014 p_resource_id IN NUMBER,
4015 p_errnum OUT NOCOPY NUMBER,
4016 p_errmesg OUT NOCOPY VARCHAR2)
4017 IS
4018
4019 x_from_count NUMBER;
4020 x_to_count NUMBER;
4021
4022 BEGIN
4023
4024 BEGIN
4025
4026 SELECT count(*)
4027 INTO x_from_count
4028 FROM mrp_net_resource_avail
4029 WHERE organization_id = p_organization_id
4030 AND shift_date BETWEEN p_date_from AND p_date_from + 0.99999
4031 AND simulation_set is null ;
4032
4033 EXCEPTION
4034
4035 WHEN NO_DATA_FOUND THEN
4036 x_from_count := 0 ;
4037 END;
4038
4039 BEGIN
4040 SELECT count(*)
4041 INTO x_to_count
4042 FROM mrp_net_resource_avail
4043 WHERE organization_id = p_organization_id
4044 AND shift_date BETWEEN p_date_to AND p_date_to +0.99999
4045 AND simulation_set is null ;
4046
4047 EXCEPTION
4048
4049 WHEN NO_DATA_FOUND THEN
4050 x_to_count := 0 ;
4051 END;
4052
4053
4054 IF (x_to_count = 0 ) OR (x_from_count = 0) THEN
4055 IF g_debug = 1 THEN
4056 fnd_file.put_line(fnd_file.log,
4057 'Before the MRP calling Phase for Org Id : '
4058 || p_organization_id );
4059 END IF ;
4060
4061 /* Because of the MRP limitation we have to get the
4062 information for the whole Org as such -
4063 I will ask nsriniva to provide a wrapper function or
4064 we have to call calc_res_avail ourself to solve this
4065 issue */
4066
4067 MRP_RHX_RESOURCE_AVAILABILITY.populate_avail_resources(
4068 arg_simulation_set => null,
4069 arg_organization_id =>p_organization_id,
4070 arg_start_date => p_date_from,
4071 arg_cutoff_date => p_date_to );
4072
4073 END IF ;
4074
4075 COMMIT ;
4076
4077 p_errnum := 0;
4078 p_errmesg := '';
4079
4080 RETURN ;
4081
4082 END Calculate_Resource_Avail;
4083
4084
4085
4086 /*
4087 This is primarily called from the SFCB - hence we summarize it to
4088 the resource level as the lowest granularity that you can get for
4089 availability is the resource level.
4090 Further this will always entered from an organization hence we would
4091 never use the All_orgs cursor, but if this is going to be called
4092 from a concurrent program for OABIS then we might have to open the
4093 cursor.
4094
4095 This is currently built so that we can get the resource productivity
4096 as well as department productivity - but it doesnot have the organization
4097 productivity - to insert that we need to insert a simple cursor to go
4098 through all the departments in an organization in bd
4099 */
4100
4101
4102 Procedure Populate_Productivity(
4103 p_group_id IN NUMBER,
4104 p_organization_id IN NUMBER,
4105 p_date_from IN DATE,
4106 p_date_to IN DATE,
4107 p_department_id IN NUMBER,
4108 p_resource_id IN NUMBER,
4109 p_userid IN NUMBER,
4110 p_applicationid IN NUMBER,
4111 p_errnum OUT NOCOPY NUMBER,
4112 p_errmesg OUT NOCOPY VARCHAR2)
4113 IS
4114 /**************************************************************
4115 Cursor to get all valid inventory organizations
4116 **************************************************************/
4117 CURSOR All_Orgs is
4118 SELECT distinct organization_id
4119 FROM mtl_parameters
4120 WHERE organization_id = nvl(p_organization_id, organization_id)
4121 AND process_enabled_flag <> 'Y'; -- Added to exclude process orgs after R12 uptake
4122
4123 x_date_from DATE;
4124 x_date_to DATE;
4125 x_group_id NUMBER;
4126 x_phase VARCHAR2(10);
4127 x_userid NUMBER;
4128 x_appl_id NUMBER;
4129
4130 BEGIN
4131
4132
4133 /* As the entry point for this more than a single
4134 point we have to do the validation in here as
4135 well. Ex :
4136 Concurrent Program
4137 SFCB
4138 */
4139
4140 IF NOT (fnd_installation.get_app_info(
4141 'WIP', g_status, g_industry, g_wip_schema)) THEN
4142
4143 RAISE_APPLICATION_ERROR (-20000,
4144 'Unable to get session information.');
4145
4146 END IF;
4147 if p_userid is null then
4148 -- This is an Error Condition
4149 x_userid := fnd_global.user_id ;
4150 else
4151 x_userid := p_userid ;
4152 end if;
4153
4154
4155 IF p_group_id IS NULL THEN
4156 select wip_indicators_temp_s.nextval into x_group_id
4157 from sys.dual ;
4158 ELSE
4159 x_group_id := p_group_id ;
4160 END IF;
4161
4162
4163 if p_applicationid is null then
4164 -- This is an Error Condition
4165 x_appl_id := fnd_global.prog_appl_id ;
4166 else
4167 x_appl_id := p_applicationid ;
4168 end if;
4169
4170 g_userid := x_userid ;
4171 g_applicationid := x_appl_id ;
4172
4173 -- Get the UOM code from the profile
4174 g_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
4175 select uom_class
4176 into g_uom_class
4177 from mtl_units_of_measure
4178 where uom_code = g_uom_code;
4179
4180 -- Set up the date ranges if needed
4181 /* For performance reasons should be just use the
4182 minimum and maximum date from efficiency */
4183
4184 IF p_date_from IS NULL THEN
4185 begin
4186
4187 select trunc(sysdate)
4188 into g_date_from
4189 from dual ;
4190 end ;
4191
4192 ELSE
4193 g_date_from := p_date_from;
4194
4195 END IF;
4196
4197 IF p_date_to IS NULL THEN
4198 begin
4199
4200 select trunc(max(calendar_date))
4201 into g_date_to
4202 from bom_calendar_dates ;
4203
4204 exception
4205 when no_data_found then
4206 g_date_to := sysdate ;
4207 end ;
4208
4209 ELSE
4210 g_date_to := p_date_to;
4211 END IF;
4212
4213 x_date_from := g_date_from ;
4214 x_date_to := g_date_to ;
4215
4216
4217 x_phase := 'I';
4218 /* if g_debug = 1 then
4219 fnd_file.put_line(fnd_file.log, 'Before Stage PROD Phase I');
4220 end if ;
4221 */
4222 --dbms_output.put_line('Before Stage PROD Phase I');
4223
4224
4225 Calculate_Std_Quantity(
4226 p_group_id => x_group_id,
4227 p_organization_id => p_organization_id,
4228 p_date_from => x_date_from,
4229 p_date_to => x_date_to,
4230 p_department_id => p_department_id,
4231 p_indicator => WIP_PRODUCTIVITY) ;
4232
4233
4234
4235 x_phase := 'II';
4236 /* if g_debug = 1 then
4237 fnd_file.put_line(fnd_file.log, 'Before Stage PROD Phase II');
4238 end if ;
4239 */
4240 --dbms_output.put_line('Before Stage PROD Phase II');
4241
4242 Calculate_Std_Units(
4243 p_group_id=> x_group_id,
4244 p_resource_id => p_resource_id,
4245 p_errnum=> p_errnum,
4246 p_errmesg => p_errmesg,
4247 p_indicator => WIP_PRODUCTIVITY) ;
4248
4249
4250 x_phase := 'III';
4251 /* if g_debug = 1 then
4252 fnd_file.put_line(fnd_file.log, 'Before Stage PROD Phase III');
4253 end if ;
4254 */
4255 --dbms_output.put_line('Before Stage PROD Phase III');
4256
4257 -- This is the stage where we summarize the information at
4258 -- the resource level and at the next stage we delete the
4259 -- the unwanted information.
4260 -- Is this stage required? This is a question that is to
4261 -- be answered by Serena, as soon as she replies, I will
4262 -- proceed with checking in this file.
4263
4264
4265 -- ????????? Is this stage required ?????????????
4266 /* Yes, this stage is required */
4267
4268 insert into wip_indicators_temp(
4269 group_id,
4270 organization_id,
4271 department_id,
4272 department_code,
4273 standard_quantity,
4274 resource_id,
4275 resource_code,
4276 transaction_date,
4277 standard_units,
4278 indicator_type,
4279 process_phase,
4280 last_update_date,
4281 last_updated_by,
4282 creation_date,
4283 created_by,
4284 program_application_id )
4285 select
4286 group_id,
4287 organization_id,
4288 department_id,
4289 department_code,
4290 sum(standard_quantity),
4291 resource_id,
4292 resource_code,
4293 transaction_date, -- already trunc'ed
4294 sum(standard_units),
4295 WIP_PRODUCTIVITY,
4296 WIP_PROD_PHASE_FOUR,
4297 last_update_date,
4298 last_updated_by,
4299 creation_date,
4300 created_by,
4301 program_application_id
4302 from wip_indicators_temp
4303 where indicator_type = WIP_PRODUCTIVITY
4304 and process_phase = WIP_PROD_PHASE_THREE
4305 group by
4306 group_id,
4307 organization_id,
4308 department_id,
4309 department_code,
4310 resource_id,
4311 resource_code,
4312 transaction_date,
4313 WIP_PRODUCTIVITY,
4314 WIP_PROD_PHASE_THREE, -- This is the third Phase
4315 last_update_date,
4316 last_updated_by,
4317 creation_date,
4318 created_by,
4319 program_application_id ;
4320
4321
4322 -- gather stats on table to allow index access
4323 If nvl(WIP_CALL_LOG,-1) =1 then
4324 fnd_stats.gather_table_stats (g_wip_schema, 'WIP_INDICATORS_TEMP',
4325 cascade => true);
4326 End If;
4327
4328 x_phase := 'IV';
4329 /* if g_debug = 1 then
4330 fnd_file.put_line(fnd_file.log, 'Before Stage PROD Phase IV');
4331 end if ;
4332 */
4333 --dbms_output.put_line('Before Stage PROD Phase IV');
4334
4335 /* Get rid of unsummarized info */
4336 delete from wip_indicators_temp
4337 where indicator_type = WIP_PRODUCTIVITY
4338 and process_phase = WIP_PROD_PHASE_THREE ;
4339 -- gather stats on table to allow index access
4340 If nvl(WIP_CALL_LOG,-1) =1 then
4341 fnd_stats.gather_table_stats (g_wip_schema, 'WIP_INDICATORS_TEMP',
4342 cascade => true);
4343 End If;
4344
4345
4346
4347
4348 -- If we had to call this function from the Concurrent
4349 -- program and we didn't have a specific organization
4350 -- then we had to open the All_Org Cursor out here
4351 -- and have all the logic in this for loop, for
4352 -- now we don't worry about, as we will be using it
4353 -- from SFCB dsoosai 11/10/98
4354
4355
4356 x_phase := 'V';
4357 /* if g_debug = 1 then
4358 fnd_file.put_line(fnd_file.log, 'Before Stage PROD Phase V');
4359 end if ;
4360 */
4361 --dbms_output.put_line('Before Stage PROD Phase V');
4362
4363
4364 Calculate_Resource_Avail(
4365 p_organization_id => p_organization_id,
4366 p_date_from => x_date_from,
4367 p_date_to => x_date_to,
4368 p_department_id => p_department_id,
4369 p_resource_id => p_resource_id,
4370 p_errnum => p_errnum,
4371 p_errmesg => p_errmesg
4372 ) ;
4373
4374
4375
4376 x_phase := 'VI';
4377 /* if g_debug = 1 then
4378 fnd_file.put_line(fnd_file.log, 'Before Stage PROD Phase VI');
4379 end if ;
4380 */
4381 --dbms_output.put_line('Before Stage PROD Phase VI');
4382
4383
4384 UPDATE wip_indicators_temp wit
4385 SET wit.available_units = (
4386 select
4387 --nvl(sum(((to_time-from_time)/3600)*capacity_units),0)
4388 -- nvl(sum(((decode(sign(to_time - from_time),
4389 -- -1, ( 86400 - from_time ) + to_time,
4390 -- 1, ( to_time - from_time ) ,
4391 -- 0 ))/3600)*capacity_units),0)
4392 decode(sum(shift_num),
4393 0, nvl(sum(capacity_units)*24,0),
4394 nvl(sum(((decode(sign(to_time - from_time),
4395 -1, ( 86400 - from_time ) + to_time,
4396 1, ( to_time - from_time ) ,
4397 0 ))/3600)*capacity_units),0))
4398 from
4399 mrp_net_resource_avail mnra
4400 where
4401 mnra.organization_id = wit.organization_id
4402 and mnra.department_id = wit.department_id
4403 and mnra.resource_id = wit.resource_id
4404 and wit.transaction_date between trunc(mnra.shift_date)
4405 and trunc (mnra.shift_date) + 0.99999
4406 and simulation_set is null
4407 )
4408 where wit.indicator_type = WIP_PRODUCTIVITY
4409 and process_phase = WIP_PROD_PHASE_FOUR ;
4410
4411
4412 x_phase := 'VII';
4413 /* if g_debug = 1 then
4414 fnd_file.put_line(fnd_file.log, 'Before Stage PROD Phase VII');
4415 end if ;
4416 */
4417 --dbms_output.put_line('Before Stage PROD Phase VII');
4418
4419 insert into wip_indicators_temp(
4420 group_id,
4421 organization_id,
4422 department_id,
4423 department_code,
4424 resource_id,
4425 resource_code,
4426 standard_units,
4427 available_units,
4428 transaction_date,
4429 indicator_type,
4430 process_phase,
4431 last_update_date,
4432 last_updated_by,
4433 creation_date,
4434 created_by,
4435 program_application_id)
4436 select
4437 x_group_id,
4438 mnra.organization_id,
4439 mnra.department_id,
4440 bd.department_code,
4441 mnra.resource_id,
4442 br.resource_code,
4443 null,
4444 --sum(((mnra.to_time-mnra.from_time)/3600)*mnra.capacity_units),
4445 --sum(((decode(sign(mnra.to_time - mnra.from_time),
4446 -- -1, ( 86400 - mnra.from_time ) + mnra.to_time,
4447 -- 1, ( mnra.to_time - mnra.from_time ) , 0 ))/3600)*mnra.capacity_units),
4448 decode(sum(shift_num),
4449 0, sum(capacity_units)*24,
4450 sum(((decode(sign(mnra.to_time - mnra.from_time),
4451 -1, ( 86400 - mnra.from_time ) + mnra.to_time,
4452 1, ( mnra.to_time - mnra.from_time ) , 0 ))/3600)*mnra.capacity_units)),
4453 trunc(mnra.shift_date),
4454 WIP_PRODUCTIVITY,
4455 WIP_PROD_PHASE_FOUR,
4456 sysdate,
4457 g_userid,
4458 SYSDATE,
4459 g_userid,
4460 g_applicationid
4461 from
4462 bom_resources br,
4463 bom_departments bd,
4464 mrp_net_resource_avail mnra,
4465 mtl_units_of_measure muom
4466 where
4467 mnra.shift_date between trunc(x_date_from) and
4468 trunc(x_date_to) + 0.99999
4469 and br.resource_id = mnra.resource_id
4470 and br.unit_of_measure = muom.uom_code
4471 and muom.uom_class = g_uom_class
4472 and br.organization_id = mnra.organization_id
4473 and bd.department_id = mnra.department_id
4474 and bd.organization_id = mnra.organization_id
4475 and mnra.organization_id = p_organization_id
4476 and mnra.department_id = p_department_id
4477 and mnra.resource_id = nvl(p_resource_id, mnra.resource_id)
4478 and mnra.shift_date not in (
4479 select distinct transaction_date
4480 from wip_indicators_temp wit
4481 where wit.resource_id = nvl(p_resource_id, wit.resource_id)
4482 and wit.department_id = p_department_id
4483 and wit.organization_id = p_organization_id
4484 and wit.indicator_type = WIP_PRODUCTIVITY
4485 and wit.process_phase = WIP_PROD_PHASE_FOUR
4486 and wit.transaction_date between
4487 trunc(x_date_from) and trunc(x_date_to) + 0.99999
4488 )
4489 group by
4490 x_group_id,
4491 mnra.organization_id,
4492 mnra.department_id,
4493 bd.department_code,
4494 mnra.resource_id,
4495 br.resource_code,
4496 null,
4497 trunc(mnra.shift_date),
4498 WIP_PRODUCTIVITY,
4499 WIP_PROD_PHASE_FOUR,
4500 sysdate,
4501 g_userid,
4502 SYSDATE,
4503 g_userid,
4504 g_applicationid ;
4505
4506 -- gather stats on table to allow index access
4507 If nvl(WIP_CALL_LOG,-1) =1 then
4508 fnd_stats.gather_table_stats (g_wip_schema, 'WIP_INDICATORS_TEMP',
4509 cascade => true);
4510 End IF;
4511
4512 EXCEPTION
4513 WHEN OTHERS THEN
4514 /*
4515 if g_debug = 1 then
4516 fnd_file.put_line(fnd_file.log,'Failed in Productivity phase : '||x_phase);
4517 fnd_file.put_line(fnd_file.log, SQLCODE);
4518 fnd_file.put_line(fnd_file.log,SQLERRM);
4519 end if ;
4520 */
4521 --dbms_output.put_line('Failed in Productivity phase : '||x_phase);
4522 --dbms_output.put_line(SQLCODE);
4523 --dbms_output.put_line(SQLERRM);
4524 p_errnum := -1 ;
4525 p_errmesg := 'Failed in Productivity Phase : '||x_phase||substr(SQLERRM,1,125);
4526 Delete_Temp_Info(p_group_id=>x_group_Id);
4527 commit ;
4528 return ;
4529
4530 End Populate_Productivity ;
4531
4532
4533
4534
4535
4536 /*
4537 This is primarily called from the SFCB - hence we summarize it to
4538 the resource level as the lowest granularity that you can get for
4539 availability is the resource level.
4540 Further this will always entered from an organization hence we would
4541 never use the All_orgs cursor, but if this is going to be called
4542 from a concurrent program for OABIS then we might have to open the
4543 cursor.
4544 */
4545
4546 PROCEDURE Populate_Resource_Load (
4547 p_group_id IN NUMBER,
4548 p_organization_id IN NUMBER,
4549 p_date_from IN DATE,
4550 p_date_to IN DATE,
4551 p_department_id IN NUMBER,
4552 p_resource_id IN NUMBER,
4553 p_userid IN NUMBER,
4554 p_applicationid IN NUMBER,
4555 p_errnum OUT NOCOPY NUMBER,
4556 p_errmesg OUT NOCOPY VARCHAR2)
4557 IS
4558 /* ***********************************************************************
4559 Cursor to get all the department, resources within an Organization
4560 ***********************************************************************/
4561 CURSOR All_Dept_Resources(
4562 p_organization_id IN NUMBER,
4563 p_department_id IN NUMBER,
4564 p_resource_id IN NUMBER,
4565 p_uom_code IN VARCHAR2
4566 ) IS
4567 select distinct organization_id, department_id, resource_id
4568 from bom_department_resources_v bdrv,
4569 mtl_units_of_measure muom
4570 where bdrv.organization_id = nvl(p_organization_id, organization_id)
4571 and bdrv.department_id = nvl(p_department_id, department_id)
4572 and bdrv.resource_id = nvl(p_resource_id, resource_id)
4573 AND bdrv.unit_of_measure = muom.uom_code
4574 and muom.uom_class = g_uom_class
4575 AND bdrv.share_from_dept_id IS null ;
4576
4577
4578
4579
4580 /**************************************************************
4581 Cursor to get all valid inventory organizations
4582 **************************************************************/
4583 CURSOR All_Orgs is
4584 SELECT distinct organization_id
4585 FROM mtl_parameters
4586 WHERE organization_id = nvl(p_organization_id, organization_id)
4587 AND process_enabled_flag <> 'Y'; -- Added to exclude process orgs after R12 uptake
4588
4589 x_date_from DATE;
4590 x_date_to DATE;
4591 x_sim_date_from DATE;
4592 x_sim_date_to DATE;
4593 x_group_id NUMBER;
4594 x_phase VARCHAR2(10);
4595 x_userid NUMBER;
4596 x_appl_id NUMBER;
4597
4598
4599 BEGIN
4600
4601 /* As the entry point for this more than a single
4602 point we have to do the validation in here as
4603 well. Ex :
4604 Concurrent Program
4605 SFCB
4606 */
4607
4608 IF NOT (fnd_installation.get_app_info(
4609 'WIP', g_status, g_industry, g_wip_schema)) THEN
4610
4611 RAISE_APPLICATION_ERROR (-20000,
4612 'Unable to get session information.');
4613
4614 END IF;
4615 if p_userid is null then
4616 -- This is an Error Condition
4617 x_userid := fnd_global.user_id ;
4618 else
4619 x_userid := p_userid ;
4620 end if;
4621
4622
4623 IF p_group_id IS NULL THEN
4624 select wip_indicators_temp_s.nextval into x_group_id
4625 from sys.dual ;
4626 ELSE
4627
4628 x_group_id := p_group_id ;
4629 END IF;
4630
4631
4632 if p_applicationid is null then
4633 -- This is an Error Condition
4634 x_appl_id := fnd_global.prog_appl_id ;
4635 else
4636 x_appl_id := p_applicationid ;
4637 end if;
4638
4639 g_userid := x_userid ;
4640 g_applicationid := x_appl_id ;
4641
4642 -- Get the UOM code from the profile
4643 g_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
4644 select uom_class
4645 into g_uom_class
4646 from mtl_units_of_measure
4647 where uom_code = g_uom_code;
4648
4649 -- Set up the date ranges if needed
4650 /* For performance reasons should be just use the
4651 minimum and maximum date from efficiency */
4652
4653 IF p_date_from IS NULL THEN
4654 begin
4655
4656 select trunc(sysdate)
4657 into g_date_from
4658 from dual ;
4659 end ;
4660
4661 ELSE
4662 g_date_from := p_date_from;
4663
4664 END IF;
4665
4666 IF p_date_to IS NULL THEN
4667 begin
4668
4669 select trunc(max(calendar_date))
4670 into g_date_to
4671 from bom_calendar_dates ;
4672
4673 exception
4674 when no_data_found then
4675 g_date_to := sysdate ;
4676 end ;
4677
4678 ELSE
4679 g_date_to := p_date_to;
4680 END IF;
4681
4682 x_date_from := g_date_from ;
4683 x_date_to := g_date_to ;
4684
4685 begin
4686
4687 select trunc(min(start_date)), trunc(max(completion_date))
4688 into x_sim_date_from, x_sim_date_to
4689 from wip_operation_resources
4690 where trunc(start_date) between trunc(x_date_from)
4691 and trunc(x_date_to)
4692 or trunc(completion_date) between trunc(x_date_from)
4693 and trunc(x_date_to) ;
4694
4695 exception
4696 when others then
4697 x_sim_date_from := x_date_from ;
4698 x_sim_date_to := x_date_to ;
4699
4700 end ;
4701
4702 -- If we had to call this function from the Concurrent
4703 -- program and we didn't have a specific organization
4704 -- then we had to open the All_Org Cursor out here
4705 -- and have all the logic in this for loop, for
4706 -- now we don't worry about, as we will be using it
4707 -- from SFCB dsoosai 11/10/98
4708
4709 x_phase := 'I';
4710 /* if g_debug = 1 then
4711 fnd_file.put_line(fnd_file.log, 'Before Stage RL Phase I');
4712 end if ;
4713 */
4714 --dbms_output.put_line('Before Stage RL Phase I');
4715
4716 Calculate_Resource_Avail(
4717 p_organization_id => p_organization_id,
4718 p_date_from => x_sim_date_from,
4719 p_date_to => x_sim_date_to,
4720 p_department_id => p_department_id,
4721 p_resource_id => p_resource_id,
4722 p_errnum => p_errnum,
4723 p_errmesg => p_errmesg
4724 ) ;
4725
4726
4727 FOR Dept_Res_Rec IN All_Dept_Resources(
4728 p_organization_id => p_organization_id,
4729 p_department_id => p_department_id,
4730 p_resource_id => p_resource_id,
4731 p_uom_code => g_uom_code
4732 ) LOOP
4733
4734
4735 x_phase := 'II';
4736 /*
4737 if g_debug = 1 then
4738 fnd_file.put_line(fnd_file.log, 'Before Stage RL Phase II');
4739 end if ;
4740 */
4741
4742 insert into wip_indicators_temp (
4743 group_id,
4744 organization_id,
4745 resource_id,
4746 resource_code,
4747 department_id,
4748 department_code,
4749 transaction_date,
4750 available_units,
4751 required_hours,
4752 indicator_type,
4753 process_phase,
4754 last_update_date,
4755 last_updated_by,
4756 creation_date,
4757 created_by,
4758 program_application_id )
4759 select
4760 x_group_id,
4761 wor.organization_id,
4762 wor.resource_id,
4763 wor.resource_code,
4764 bdr.department_id,
4765 bd.department_code,
4766 mnra.shift_date,
4767 null,
4768 decode(sign(sum(inv_convert.inv_um_convert(0,NULL,decode(wor.basis_type,
4769 1,
4770 NVL((wor.usage_rate_or_amount*wo.scheduled_quantity
4771 - nvl(wor.applied_resource_units,0)),0)*
4772 get_Workday_Ratio(wor.resource_id, wor.organization_id,trunc(wor.start_date), trunc(wor.completion_date), trunc(mnra.shift_date)),
4773 DECODE(nvl(wor.applied_resource_units,0),
4774 0,
4775 decode(trunc(wor.start_date),
4776 trunc(mnra.shift_date),
4777 NVL(wor.usage_rate_or_amount,0),
4778 0),
4779 0)
4780 ),wor.uom_code,g_uom_code,NULL,NULL
4781 ))),1,sum(inv_convert.inv_um_convert(0,NULL,decode(wor.basis_type,
4782 1,
4783 NVL((wor.usage_rate_or_amount*wo.scheduled_quantity
4784 - nvl(wor.applied_resource_units,0)),0)*
4785 get_Workday_Ratio(wor.resource_id, wor.organization_id,trunc(wor.start_date), trunc(wor.completion_date), trunc(mnra.shift_date)),
4786 DECODE(nvl(wor.applied_resource_units,0),
4787 0,
4788 decode(trunc(wor.start_date),
4789 trunc(mnra.shift_date),
4790 NVL(wor.usage_rate_or_amount,0),
4791 0),
4792 0)
4793 ),wor.uom_code,g_uom_code,NULL,NULL
4794 )),0) "Required",
4795 WIP_RESOURCE_LOAD, -- Indicator Type
4796 WIP_RL_PHASE_ONE, -- process phase
4797 sysdate,
4798 g_userid,
4799 sysdate,
4800 g_userid,
4801 g_applicationid
4802 from
4803 mrp_net_resource_avail mnra,
4804 bom_departments bd,
4805 bom_department_resources bdr,
4806 wip_operations_v wo,
4807 wip_operation_resources_v wor,
4808 wip_discrete_jobs wdj
4809 where
4810 wdj.wip_entity_id = wor.wip_entity_id
4811 and wdj.organization_id = wor.organization_id
4812 and wdj.status_type in (1, 3, 6 ) -- unreleased, released and hold
4813 and mnra.simulation_set is null
4814 and mnra.resource_id = wor.resource_id
4815 and mnra.organization_id = wor.organization_id
4816 and wor.organization_id = nvl(Dept_Res_Rec.Organization_id, wor.organization_id)
4817 and wor.resource_id = nvl(Dept_Res_Rec.resource_id, wor.resource_id)
4818 and mnra.shift_date between trunc(wor.start_date) and trunc(wor.completion_date) + 0.99999
4819 and ( ( wor.start_date between trunc(x_date_from)
4820 and trunc(x_date_to) + 0.99999
4821 )
4822 or ( wor.completion_date between trunc(x_date_from)
4823 and trunc(x_date_to) + 0.99999
4824 )
4825 or ( wor.start_date < trunc(x_date_from) + 0.99999 and
4826 wor.completion_date > trunc(x_date_to) + 0.99999
4827 )
4828 )
4829 and mnra.shift_date between trunc(x_date_from)
4830 and trunc(x_date_to) + 0.99999
4831 and wo.wip_entity_id = wor.wip_entity_id
4832 and wo.organization_id = wor.organization_id
4833 and wo.operation_seq_num = wor.operation_seq_num
4834 and nvl(wo.repetitive_schedule_id,-999) = nvl(wor.repetitive_schedule_id, -999)
4835 and bdr.resource_id = wor.resource_id
4836 and bdr.share_from_dept_id is null
4837 and bdr.department_id = nvl(Dept_Res_rec.department_id, bdr.department_id)
4838 and bd.organization_id = wor.organization_id
4839 and bd.department_id = bdr.department_id
4840 group by
4841 x_group_id,
4842 wor.organization_id,
4843 wor.resource_id,
4844 wor.resource_code,
4845 bdr.department_id,
4846 bd.department_code,
4847 mnra.shift_date,
4848 null,
4849 WIP_RESOURCE_LOAD,
4850 WIP_RL_PHASE_ONE,
4851 sysdate,
4852 g_userid,
4853 sysdate,
4854 g_userid,
4855 g_applicationid ;
4856
4857
4858 /*
4859 x_phase := 'III';
4860 if g_debug = 1 then
4861 fnd_file.put_line(fnd_file.log, 'Before Stage RL Phase III');
4862 end if ;
4863 */
4864
4865
4866
4867 UPDATE wip_indicators_temp wit
4868 SET wit.available_units = (
4869 select
4870 -- nvl(sum(((to_time-from_time)/3600)*capacity_units),0) --BUG - 3565583
4871 -- nvl(sum(((decode(sign(to_time - from_time),
4872 -- -1, ( 86400 - from_time ) + to_time,
4873 -- 1, ( to_time - from_time ) ,
4874 -- 0 ))/3600)*capacity_units),0)
4875 decode(sum(shift_num),
4876 0, nvl(sum(capacity_units)*24,0),
4877 nvl(sum(((decode(sign(to_time - from_time),
4878 -1, ( 86400 - from_time ) + to_time,
4879 1, ( to_time - from_time ) ,
4880 0 ))/3600)*capacity_units),0))
4881 from
4882 mrp_net_resource_avail mnra
4883 where
4884 mnra.organization_id = wit.organization_id
4885 and mnra.department_id = wit.department_id
4886 and mnra.resource_id = wit.resource_id
4887 and wit.transaction_date between trunc(mnra.shift_date)
4888 and trunc (mnra.shift_date) + 0.99999
4889 and simulation_set is null
4890 )
4891 where wit.organization_id = Dept_Res_Rec.organization_id
4892 AND wit.department_id = Dept_Res_Rec.department_id
4893 AND wit.resource_id = Dept_Res_Rec.resource_id
4894 and wit.indicator_type = WIP_RESOURCE_LOAD
4895 and process_phase = WIP_RL_PHASE_ONE ;
4896
4897
4898 /*
4899 x_phase := 'IV';
4900 if g_debug = 1 then
4901 fnd_file.put_line(fnd_file.log, 'Before Stage RL Phase IV');
4902 end if ;
4903 */
4904
4905
4906 insert into wip_indicators_temp(
4907 group_id,
4908 organization_id,
4909 department_id,
4910 department_code,
4911 resource_id,
4912 resource_code,
4913 required_hours,
4914 available_units,
4915 transaction_date,
4916 last_update_date,
4917 last_updated_by,
4918 creation_date,
4919 created_by,
4920 program_application_id)
4921 select
4922 x_group_id,
4923 mnra.organization_id,
4924 mnra.department_id,
4925 bd.department_code,
4926 mnra.resource_id,
4927 br.resource_code,
4928 null,
4929 -- sum(((mnra.to_time-mnra.from_time)/3600)*mnra.capacity_units), --BUG - 3565583
4930 -- sum(((decode(sign(mnra.to_time - mnra.from_time),
4931 -- -1, ( 86400 - mnra.from_time ) + mnra.to_time,
4932 -- 1, ( mnra.to_time - mnra.from_time ) , 0 ))/3600)*mnra.capacity_units),
4933 decode(sum(mnra.shift_num),
4934 0, sum(capacity_units)*24,
4935 sum(((decode(sign(mnra.to_time - mnra.from_time),
4936 -1, ( 86400 - mnra.from_time ) + mnra.to_time,
4937 1, ( mnra.to_time - mnra.from_time ) , 0 ))/3600)*mnra.capacity_units)),
4938 trunc(mnra.shift_date),
4939 sysdate,
4940 g_userid,
4941 SYSDATE,
4942 g_userid,
4943 g_applicationid
4944 from
4945 bom_resources br,
4946 bom_departments bd,
4947 mrp_net_resource_avail mnra,
4948 mtl_units_of_measure muom
4949 where
4950 mnra.shift_date between trunc(x_date_from) and
4951 trunc(x_date_to) + 0.99999
4952 and br.resource_id = mnra.resource_id
4953 and br.unit_of_measure = muom.uom_code
4954 and muom.uom_class = g_uom_class
4955 and br.organization_id = mnra.organization_id
4956 and bd.department_id = mnra.department_id
4957 and bd.organization_id = mnra.organization_id
4958 and mnra.organization_id = Dept_Res_Rec.organization_id
4959 and mnra.department_id = Dept_Res_Rec.department_id
4960 and mnra.resource_id = Dept_Res_Rec.resource_id
4961 and mnra.shift_date not in (
4962 select distinct transaction_date
4963 from wip_indicators_temp wit
4964 where wit.resource_id = Dept_Res_Rec.resource_id
4965 and wit.department_id = Dept_Res_Rec.department_id
4966 and wit.organization_id = Dept_Res_Rec.organization_id
4967 and wit.indicator_type = WIP_RESOURCE_LOAD
4968 and wit.process_phase = WIP_RL_PHASE_ONE
4969 and wit.transaction_date between
4970 trunc(x_date_from) and trunc(x_date_to) + 0.99999
4971 )
4972 group by
4973 x_group_id,
4974 mnra.organization_id,
4975 mnra.department_id,
4976 bd.department_code,
4977 mnra.resource_id,
4978 br.resource_code,
4979 trunc(mnra.shift_date),
4980 sysdate,
4981 g_userid,
4982 SYSDATE,
4983 g_userid,
4984 g_applicationid ;
4985
4986
4987 END LOOP ;
4988
4989 -- gather stats on table to allow index access
4990 If nvl(WIP_CALL_LOG,-1) =1 then
4991 fnd_stats.gather_table_stats (g_wip_schema, 'WIP_INDICATORS_TEMP',
4992 cascade => true);
4993 End If;
4994
4995
4996 EXCEPTION
4997 WHEN OTHERS THEN
4998 /*
4999 if g_debug = 1 then
5000 fnd_file.put_line(fnd_file.log,'Failed in Resource Load phase : '||x_phase);
5001 fnd_file.put_line(fnd_file.log, SQLCODE);
5002 fnd_file.put_line(fnd_file.log,SQLERRM);
5003 end if ;
5004 */
5005 --dbms_output.put_line('Failed in Resource Load phase : '||x_phase);
5006 --dbms_output.put_line(SQLCODE);
5007 --dbms_output.put_line(SQLERRM);
5008 p_errnum := -1 ;
5009 p_errmesg := 'Failed in Resource Load Phase : '||x_phase||substr(SQLERRM,1,125);
5010 Delete_Temp_Info(p_group_id=>x_group_Id);
5011 commit ;
5012 return ;
5013
5014 End Populate_Resource_Load ;
5015
5016
5017
5018
5019
5020 Procedure Move_SFCB_Utz_Info(
5021 p_group_id IN NUMBER,
5022 p_organization_id IN NUMBER,
5023 p_date_from IN DATE,
5024 p_date_to IN DATE,
5025 p_department_id IN NUMBER,
5026 p_resource_id IN NUMBER,
5027 p_userid IN NUMBER, -- this parameter is not really needed
5028 p_applicationid IN NUMBER, -- this parameter is not really needed
5029 p_errnum OUT NOCOPY NUMBER,
5030 p_errmesg OUT NOCOPY VARCHAR2)
5031 IS
5032 /* ***********************************************************************
5033 Cursor to get all the department, resources within an Organization
5034 ***********************************************************************/
5035 CURSOR All_Dept_Resources(
5036 p_organization_id IN NUMBER,
5037 p_department_id IN NUMBER,
5038 p_resource_id IN NUMBER,
5039 p_date_from IN DATE,
5040 p_date_to IN DATE
5041 ) IS
5042 select distinct organization_id, department_id, resource_id
5043 from mrp_net_resource_avail
5044 where organization_id = nvl(p_organization_id, organization_id)
5045 and department_id = nvl(p_department_id, department_id)
5046 and resource_id = nvl(p_resource_id, resource_id)
5047 and trunc(shift_date) between trunc(p_date_from)
5048 and trunc(p_date_to)
5049 and simulation_set is null ;
5050
5051 x_phase VARCHAR2(10);
5052 BEGIN
5053
5054
5055 -- If we had to call this function from the Concurrent
5056 -- program and we didn't have a specific organization
5057 -- then we had to open the All_Org Cursor out here
5058 -- and have all the logic in this for loop, for
5059 -- now we don't worry about, as we will be using it
5060 -- from SFCB dsoosai 11/10/98
5061
5062 FOR Dept_Res_Rec IN All_Dept_Resources(
5063 p_organization_id => p_organization_id,
5064 p_department_id => p_department_id,
5065 p_resource_id => p_resource_id,
5066 p_date_from => p_date_from,
5067 p_date_to => p_date_to
5068 ) LOOP
5069
5070
5071 x_phase := 'I';
5072 if g_debug = 1 then
5073 fnd_file.put_line(fnd_file.log, 'Before Stage MSUI Phase IN I');
5074 end if ;
5075
5076
5077 --dbms_output.put_line('Before Stage MSUI Phase I');
5078
5079 insert into wip_indicators_temp(
5080 group_id,
5081 organization_id,
5082 department_id,
5083 department_code,
5084 resource_id,
5085 resource_code,
5086 applied_units_utz,
5087 available_units,
5088 transaction_date,
5089 indicator_type,
5090 process_phase,
5091 last_update_date,
5092 last_updated_by,
5093 creation_date,
5094 created_by,
5095 program_application_id)
5096 select
5097 p_group_id,
5098 mnra.organization_id,
5099 mnra.department_id,
5100 bd.department_code,
5101 mnra.resource_id,
5102 br.resource_code,
5103 null,
5104 --sum(((mnra.to_time-mnra.from_time)/3600)*mnra.capacity_units), --BUG - 3581581
5105 -- sum(((decode(sign(mnra.to_time - mnra.from_time),
5106 -- -1, ( 86400 - mnra.from_time ) + mnra.to_time,
5107 -- 1, ( mnra.to_time - mnra.from_time ) , 0 ))/3600)*mnra.capacity_units),
5108 decode(sum(mnra.shift_num),
5109 0, sum(capacity_units)*24,
5110 sum(((decode(sign(mnra.to_time - mnra.from_time),
5111 -1, ( 86400 - mnra.from_time ) + mnra.to_time,
5112 1, ( mnra.to_time - mnra.from_time ) , 0 ))/3600)*mnra.capacity_units)),
5113 trunc(mnra.shift_date),
5114 WIP_UTILIZATION,
5115 WIP_UTZ_PHASE_TWO,
5116 sysdate,
5117 g_userid,
5118 SYSDATE,
5119 g_userid,
5120 g_applicationid
5121 from
5122 bom_resources br,
5123 bom_departments bd,
5124 mrp_net_resource_avail mnra,
5125 mtl_units_of_measure muom
5126 where
5127 mnra.shift_date between trunc(p_date_from) and
5128 trunc(p_date_to) + 0.99999
5129 and br.resource_id = mnra.resource_id
5130 and br.unit_of_measure = muom.uom_code
5131 and muom.uom_class = g_uom_class
5132 and br.organization_id = mnra.organization_id
5133 and bd.department_id = mnra.department_id
5134 and bd.organization_id = mnra.organization_id
5135 and mnra.organization_id = Dept_Res_Rec.organization_id
5136 and mnra.department_id = Dept_Res_Rec.department_id
5137 and mnra.resource_id = Dept_Res_Rec.resource_id
5138 and mnra.shift_date not in (
5139 select distinct transaction_date
5140 from wip_indicators_temp wit
5141 where wit.resource_id = Dept_Res_Rec.resource_id
5142 and wit.department_id = Dept_Res_Rec.department_id
5143 and wit.organization_id = Dept_Res_Rec.organization_id
5144 and wit.indicator_type = WIP_UTILIZATION
5145 and wit.process_phase = WIP_UTZ_PHASE_TWO
5146 and wit.transaction_date between
5147 trunc(p_date_from) and trunc(p_date_to) + 0.99999
5148 )
5149 group by
5150 p_group_id,
5151 mnra.organization_id,
5152 mnra.department_id,
5153 bd.department_code,
5154 mnra.resource_id,
5155 br.resource_code,
5156 trunc(mnra.shift_date),
5157 sysdate,
5158 g_userid,
5159 SYSDATE,
5160 g_userid,
5161 g_applicationid ;
5162
5163 END LOOP ;
5164
5165 commit ;
5166
5167 -- gather stats on table to allow index access
5168 If nvl(WIP_CALL_LOG,-1) =1 then
5169 fnd_stats.gather_table_stats (g_wip_schema, 'WIP_INDICATORS_TEMP',
5170 cascade => true);
5171 End If;
5172
5173 p_errnum := 0;
5174 p_errmesg := '';
5175
5176 return ;
5177
5178 EXCEPTION
5179
5180 WHEN OTHERS THEN
5181 if g_debug = 1 then
5182 fnd_file.put_line(fnd_file.log,'Failed in MSUI phase : '||x_phase);
5183 fnd_file.put_line(fnd_file.log, SQLCODE);
5184 fnd_file.put_line(fnd_file.log,SQLERRM);
5185 end if ;
5186
5187 --dbms_output.put_line('Failed in MSUI phase : '||x_phase);
5188 --dbms_output.put_line(SQLCODE);
5189 --dbms_output.put_line(SQLERRM);
5190 p_errnum := -1 ;
5191 p_errmesg := 'Failed in MSUI Phase : '||x_phase||substr(SQLERRM,1,125);
5192 Delete_Temp_Info(p_group_id=>p_group_Id);
5193 commit ;
5194 return ;
5195
5196 End Move_SFCB_Utz_Info ;
5197
5198
5199
5200 function get_Workday_Ratio
5201 (p_resource_id IN NUMBER,
5202 p_organization_id IN NUMBER,
5203 p_start_date IN DATE,
5204 p_completion_date IN DATE,
5205 p_transaction_date IN DATE )
5206 return NUMBER IS
5207 x_no_of_day_shifts NUMBER;
5208 x_total_days NUMBER ;
5209 x_workday_ratio NUMBER ;
5210 BEGIN
5211
5212
5213
5214 begin
5215
5216 select
5217 nvl(count(*),0)
5218 into
5219 x_no_of_day_shifts
5220 from
5221 mrp_net_resource_avail
5222 where resource_id = p_resource_id
5223 and organization_id = p_organization_id
5224 and simulation_set is null
5225 and shift_date = p_transaction_date ;
5226
5227 exception
5228 when others then
5229 x_no_of_day_shifts := 1 ;
5230
5231 end ;
5232
5233 begin
5234
5235 select
5236 nvl(count(distinct shift_date),0)
5237 into
5238 x_total_days
5239 from
5240 mrp_net_resource_avail
5241 where resource_id = p_resource_id
5242 and organization_id = p_organization_id
5243 and simulation_set is null
5244 and shift_date between p_start_date and p_completion_date ;
5245
5246 exception
5247 when others then
5248 x_total_days := 0 ;
5249
5250 end ;
5251
5252
5253 /********************************************************
5254 * We have to use the number of Day shifts as we have the
5255 * same day information will be called in multiple times
5256 *********************************************************/
5257
5258 x_workday_ratio := ((1/x_total_days)/x_no_of_day_shifts);
5259
5260 return x_workday_ratio ;
5261
5262 End get_Workday_Ratio ;
5263
5264
5265
5266 /* Populate_Denormalize_Data
5267
5268 This populates the denormalized data into the the following
5269 tables of the following types:
5270
5271 1. Wip_bis_prod_indicators
5272 -- organization
5273 -- item
5274 -- time
5275 -- geographical location
5276 2. Wip_bis_prod_dept_yield
5277 -- organization
5278 -- time
5279 -- geographical location
5280 (Note item is not denormalized here)
5281 3. Wip_bis_prod_assy_yield
5282 -- organization
5283 -- item
5284 -- time
5285 -- geographical location
5286
5287 */
5288
5289 PROCEDURE Populate_Denormalize_Data(
5290 p_errnum IN OUT NOCOPY NUMBER,
5291 p_errmesg IN OUT NOCOPY VARCHAR2)
5292 IS
5293 x_phase VARCHAR2(10);
5294 BEGIN
5295
5296
5297 /**********************
5298 WIP_BIS_PROD_INDICATORS
5299 **********************/
5300 x_phase := 'I';
5301
5302 if g_debug = 1 then
5303 fnd_file.put_line(fnd_file.log, 'Before Stage 10 Phase IN I');
5304 end if ;
5305
5306 denormalize_item_dimension(
5307 p_table_name => 'wip_bis_prod_indicators',
5308 p_errnum => p_errnum,
5309 p_errmesg => p_errmesg );
5310 commit;
5311
5312 x_phase := 'II';
5313
5314 if g_debug = 1 then
5315 fnd_file.put_line(fnd_file.log, 'Before Stage 10 Phase IN II');
5316 end if ;
5317
5318 denormalize_org_dimension(
5319 p_table_name => 'wip_bis_prod_indicators',
5320 p_errnum => p_errnum,
5321 p_errmesg => p_errmesg );
5322 /* denormalize_time_dimension(
5323 p_table_name => 'wip_bis_prod_indicators',
5324 p_errnum => p_errnum,
5325 p_errmesg => p_errmesg );*/
5326 commit;
5327
5328 x_phase := 'III';
5329
5330 if g_debug = 1 then
5331 fnd_file.put_line(fnd_file.log, 'Before Stage 10 Phase IN III');
5332 end if ;
5333 denormalize_time_dimension(
5334 p_table_name => 'wip_bis_prod_indicators',
5335 p_errnum => p_errnum,
5336 p_errmesg => p_errmesg );
5337 /* denormalize_org_dimension(
5338 p_table_name => 'wip_bis_prod_indicators',
5339 p_errnum => p_errnum,
5340 p_errmesg => p_errmesg );*/
5341 commit;
5342
5343 x_phase := 'IV';
5344
5345 if g_debug = 1 then
5346 fnd_file.put_line(fnd_file.log, 'Before Stage 10 Phase IN IV');
5347 end if ;
5348
5349 denormalize_geo_dimension(
5350 p_table_name => 'wip_bis_prod_indicators',
5351 p_errnum => p_errnum,
5352 p_errmesg => p_errmesg );
5353 commit;
5354
5355 /**********************
5356 WIP_BIS_PROD_DEPT_YIELD
5357 **********************/
5358 -- do not denormalize the item information for departmentyYield
5359
5360 x_phase := 'V';
5361
5362 if g_debug = 1 then
5363 fnd_file.put_line(fnd_file.log, 'Before Stage 10 Phase IN V');
5364 end if ;
5365 denormalize_org_dimension(
5366 p_table_name => 'wip_bis_prod_dept_yield',
5367 p_errnum => p_errnum,
5368 p_errmesg => p_errmesg );
5369 /* denormalize_time_dimension(
5370 p_table_name => 'wip_bis_prod_dept_yield',
5371 p_errnum => p_errnum,
5372 p_errmesg => p_errmesg );*/
5373 commit;
5374
5375 x_phase := 'VI';
5376
5377 if g_debug = 1 then
5378 fnd_file.put_line(fnd_file.log, 'Before Stage 10 Phase IN VI');
5379 end if ;
5380 denormalize_time_dimension(
5381 p_table_name => 'wip_bis_prod_dept_yield',
5382 p_errnum => p_errnum,
5383 p_errmesg => p_errmesg );
5384 /* denormalize_org_dimension(
5385 p_table_name => 'wip_bis_prod_dept_yield',
5386 p_errnum => p_errnum,
5387 p_errmesg => p_errmesg );*/
5388 commit;
5389
5390 x_phase := 'VII';
5391
5392 if g_debug = 1 then
5393 fnd_file.put_line(fnd_file.log, 'Before Stage 10 Phase IN VII');
5394 end if ;
5395
5396 denormalize_geo_dimension(
5397 p_table_name => 'wip_bis_prod_dept_yield',
5398 p_errnum => p_errnum,
5399 p_errmesg => p_errmesg );
5400 commit;
5401
5402
5403 /**********************
5404 WIP_BIS_PROD_ASSY_YIELD
5405 **********************/
5406
5407 x_phase := 'VIII';
5408
5409 if g_debug = 1 then
5410 fnd_file.put_line(fnd_file.log, 'Before Stage 10 Phase IN VIII');
5411 end if ;
5412
5413 denormalize_item_dimension(
5414 p_table_name => 'wip_bis_prod_assy_yield',
5415 p_errnum => p_errnum,
5416 p_errmesg => p_errmesg );
5417 commit;
5418
5419 x_phase := 'IX';
5420
5421 if g_debug = 1 then
5422 fnd_file.put_line(fnd_file.log, 'Before Stage 10 Phase IN IX');
5423 end if ;
5424 denormalize_org_dimension(
5425 p_table_name => 'wip_bis_prod_assy_yield',
5426 p_errnum => p_errnum,
5427 p_errmesg => p_errmesg );
5428 /* denormalize_time_dimension(
5429 p_table_name => 'wip_bis_prod_assy_yield',
5430 p_errnum => p_errnum,
5431 p_errmesg => p_errmesg );*/
5432 commit;
5433
5434 x_phase := 'X';
5435
5436 if g_debug = 1 then
5437 fnd_file.put_line(fnd_file.log, 'Before Stage 10 Phase IN X');
5438 end if ;
5439 denormalize_time_dimension(
5440 p_table_name => 'wip_bis_prod_assy_yield',
5441 p_errnum => p_errnum,
5442 p_errmesg => p_errmesg );
5443 /*denormalize_org_dimension(
5444 p_table_name => 'wip_bis_prod_assy_yield',
5445 p_errnum => p_errnum,
5446 p_errmesg => p_errmesg );*/
5447 commit;
5448
5449 x_phase := 'XI';
5450
5451 if g_debug = 1 then
5452 fnd_file.put_line(fnd_file.log, 'Before Stage 10 Phase IN XI');
5453 end if ;
5454
5455 denormalize_geo_dimension(
5456 p_table_name => 'wip_bis_prod_assy_yield',
5457 p_errnum => p_errnum,
5458 p_errmesg => p_errmesg );
5459 commit;
5460
5461 p_errnum := 0;
5462 p_errmesg := '';
5463
5464 return;
5465
5466 EXCEPTION
5467
5468 WHEN OTHERS THEN
5469 if g_debug = 1 then
5470 fnd_file.put_line(fnd_file.log,'Failed in PDD phase : '||
5471 x_phase);
5472 fnd_file.put_line(fnd_file.log, SQLCODE);
5473 fnd_file.put_line(fnd_file.log,SQLERRM);
5474 end if ;
5475 p_errnum := -1 ;
5476 p_errmesg := 'Failed in PDD Phase : '||x_phase||substr(SQLERRM,1,125);
5477
5478 -- returns to populate_summary_table, so don't raise exception.
5479
5480 END populate_denormalize_data ;
5481
5482 /**************************************************************
5483 * This procedure will denormalize the item dimension for a given
5484 * table - the table name is given as an input parameter. It
5485 * makes the following assumptions about the columns that needs
5486 * to be updated :
5487 * 1. Inventory_Item_Id -- The Id of the inventory item.
5488 * 2. Inventory_Item_Name -- The name of the inventory item.
5489 * 3. Category_Id -- The Id of the category to which the item
5490 * belongs
5491 * 4. Category_Name -- The name of the category
5492 *************************************************************/
5493
5494 PROCEDURE denormalize_item_dimension(
5495 p_table_name IN VARCHAR2,
5496 p_errnum IN OUT NOCOPY NUMBER,
5497 p_errmesg IN OUT NOCOPY VARCHAR2)
5498 AS
5499 x_cursor_id INTEGER ;
5500 x_sql_statement VARCHAR2(32767);
5501 x_ignore INTEGER ;
5502
5503 proc_name VARCHAR2 (40) ;
5504
5505 BEGIN
5506 proc_name := 'denormalize_item_dimension';
5507
5508 x_cursor_id := DBMS_SQL.OPEN_CURSOR;
5509
5510 x_sql_statement :=
5511 'UPDATE ' || p_table_name || ' xtable ' ||
5512 ' SET ( ' ||
5513 'inventory_item_name, ' ||
5514 'category_id, ' ||
5515 'category_name ' || ') = ' ||
5516 '( SELECT ' ||
5517 ' mif.item_number, ' ||
5518 ' mic.category_id, ' ||
5519 ' mckfv.concatenated_segments ' ||
5520 ' FROM ' ||
5521 ' mtl_item_flexfields mif, ' ||
5522 ' mtl_categories_kfv mckfv, ' ||
5523 ' mtl_item_categories mic, ' ||
5524 ' mtl_default_category_sets mdcs ' ||
5525 ' WHERE mif.organization_id = xtable.organization_id ' ||
5526 ' AND mif.inventory_item_id = xtable.inventory_item_id ' ||
5527 ' AND mic.inventory_item_id (+) = xtable.inventory_item_id ' ||
5528 ' AND mic.organization_id (+) = xtable.organization_id ' ||
5529 ' AND mdcs.category_set_id (+) = mic.category_set_id ' ||
5530 ' AND mdcs.functional_area_id = 7 ' ||
5531 ' AND mckfv.category_id = mic.category_id ' ||
5532 ' ) ' ;
5533
5534
5535 DBMS_SQL.PARSE( x_cursor_id, x_sql_statement, DBMS_SQL.V7 );
5536
5537 x_ignore := DBMS_SQL.EXECUTE( x_cursor_id );
5538 DBMS_SQL.CLOSE_CURSOR (x_cursor_id);
5539
5540 p_errnum := 0;
5541 p_errmesg := '';
5542
5543 EXCEPTION
5544
5545 WHEN OTHERS
5546 THEN
5547 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
5548 p_errnum := -1;
5549 p_errmesg := (proc_name || ':' || sqlerrm);
5550 RAISE;
5551
5552 END denormalize_item_dimension ;
5553
5554
5555 /**************************************************************
5556 * This procedure will denormalize the time dimension for a given
5557 * table - the table name is given as an input parameter. It
5558 * makes the following assumptions about the columns that needs
5559 * to be updated :
5560 * 1. Transaction_Date -- The date of the transaction
5561 * 2. Period_Set_Name -- The GL Periods, period_set_name
5562 * 3. Year -- The Year in the GL Periods
5563 * 4. Quarter -- The Quarter in the GL Periods
5564 * 5. Month -- The Month in the GL periods
5565 *************************************************************/
5566
5567 PROCEDURE denormalize_time_dimension(
5568 p_table_name IN VARCHAR2,
5569 p_errnum IN OUT NOCOPY NUMBER,
5570 p_errmesg IN OUT NOCOPY VARCHAR2)
5571 AS
5572 x_cursor_id INTEGER ;
5573 x_sql_statement VARCHAR2(32767);
5574 x_ignore INTEGER ;
5575 proc_name VARCHAR2 (40);
5576 BEGIN
5577 proc_name := 'denormalize_time_dimension';
5578 x_cursor_id := DBMS_SQL.OPEN_CURSOR;
5579
5580 x_sql_statement :=
5581 'UPDATE ' || p_table_name || ' xtable ' ||
5582 ' SET ( ' ||
5583 'period_set_name ' || ',' ||
5584 'year ' || ',' ||
5585 'quarter ' || ',' ||
5586 'month ' || ') = ' ||
5587 '( SELECT /*+ ORDERED */ ' ||
5588 ' yr.period_set_name, '||
5589 ' yr.period_name, ' ||
5590 ' qt.period_name, ' ||
5591 ' mo.period_name ' ||
5592 ' FROM ' ||
5593 -- ' org_organization_definitions ood , ' ||
5594 ' gl_sets_of_books gsob, ' ||
5595 ' gl_periods mo, ' ||
5596 ' gl_periods qt, ' ||
5597 ' gl_periods yr ' ||
5598 -- ' WHERE ood.organization_id = xtable.organization_id ' ||
5599 -- ' AND gsob.set_of_books_id = ood.set_of_books_id ' ||
5600 ' WHERE gsob.set_of_books_id = xtable.set_of_books_id ' ||
5601 ' AND yr.period_set_name = gsob.period_set_name ' ||
5602 ' AND yr.period_type = ''Year'' ' ||
5603 ' AND xtable.transaction_date between yr.start_date and yr.end_date ' ||
5604 ' AND yr.adjustment_period_flag = ''N'' ' ||
5605 ' AND qt.period_set_name = gsob.period_set_name ' ||
5606 ' AND qt.period_type = ''Quarter'' ' ||
5607 ' AND xtable.transaction_date between qt.start_date and qt.end_date ' ||
5608 ' AND qt.adjustment_period_flag = ''N'' ' ||
5609 ' AND mo.period_set_name = gsob.period_set_name ' ||
5610 ' AND mo.period_type = gsob.ACCOUNTED_PERIOD_TYPE ' ||
5611 ' AND xtable.transaction_date between mo.start_date and mo.end_date ' ||
5612 ' AND mo.adjustment_period_flag = ''N'' ' ||
5613 ' ) ' ;
5614
5615
5616 DBMS_SQL.PARSE( x_cursor_id, x_sql_statement, DBMS_SQL.V7 );
5617
5618 x_ignore := DBMS_SQL.EXECUTE( x_cursor_id );
5619 DBMS_SQL.CLOSE_CURSOR (x_cursor_id);
5620
5621 p_errnum := 0;
5622 p_errmesg := '';
5623
5624 EXCEPTION
5625
5626 WHEN OTHERS
5627 THEN
5628 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
5629 p_errnum := -1;
5630 p_errmesg := (proc_name || ':' || sqlerrm);
5631 RAISE;
5632
5633 END denormalize_time_dimension ;
5634
5635
5636 /**************************************************************
5637 * This procedure will denormalize the org dimension for a given
5638 * table - the table name is given as an input parameter. It
5639 * makes the following assumptions about the columns that needs
5640 * to be updated :
5641 * 1. Organization_ID -- The Organization Id
5642 * 2. Organization_Name -- The Organization Name
5643 * 3. Legal_Entity_ID -- The Legal Entity Id
5644 * 4. Legal_Entity_Name -- The Legal Entity Name
5645 * 5. Operating_Unit_ID -- The operating unit ID
5646 * 6. Operating_Unit_Name -- The operating unit name
5647 * 7. set_of_books_id -- The set of books id
5648 * 8. set_of_books_name -- The set of books name
5649 *************************************************************/
5650
5651 PROCEDURE denormalize_org_dimension(
5652 p_table_name IN VARCHAR2,
5653 p_errnum IN OUT NOCOPY NUMBER,
5654 p_errmesg IN OUT NOCOPY VARCHAR2)
5655 AS
5656
5657 x_cursor_id INTEGER ;
5658 x_sql_statement VARCHAR2(32767);
5659 x_ignore INTEGER ;
5660 x_mapping VARCHAR2(240);
5661
5662 proc_name VARCHAR2 (40);
5663
5664 BEGIN
5665 proc_name := 'denormalize_org_dimension';
5666 x_cursor_id := DBMS_SQL.OPEN_CURSOR;
5667
5668
5669
5670 x_sql_statement :=
5671 'UPDATE ' || p_table_name || ' xtable ' ||
5672 ' SET ( ' ||
5673 'organization_name ' || ',' ||
5674 'legal_entity_id ' || ',' ||
5675 'legal_entity_name ' || ',' ||
5676 'operating_unit_id ' || ',' ||
5677 'operating_unit_name ' || ',' ||
5678 'set_of_books_id ' || ',' ||
5679 'set_of_books_name ' || ' ) = ' ||
5680 '( SELECT /*+ ORDERED USE_HASH (ood) USE_HASH (hle) USE_HASH (gsob) USE_HASH (hou) PARALLEL*/ ' ||
5681 ' ood.organization_name, ' ||
5682 ' hle.organization_id , ' ||
5683 ' hle.name, '||
5684 ' hou.organization_id, ' ||
5685 ' hou.name, ' ||
5686 ' ood.set_of_books_id, ' ||
5687 ' gsob.name ' ||
5688 ' FROM ' ||
5689 ' org_organization_definitions ood, ' ||
5690 ' hr_legal_entities hle, ' ||
5691 ' gl_sets_of_books gsob ,' ||
5692 ' hr_operating_units hou ' ||
5693 ' WHERE ood.organization_id = xtable.organization_id ' ||
5694 ' AND hle.organization_id = ood.legal_entity ' ||
5695 -- ' AND hle.set_of_books_id = ood.set_of_books_id ' ||
5696 ' AND gsob.set_of_books_id = ood.set_of_books_id ' ||
5697 ' AND hou.organization_id = ood.operating_unit ' ||
5698 ' AND hou.default_legal_context_id = to_char(ood.legal_entity) ' ||
5699 ' ) ' ;
5700 -- hou.legal_entity_id changed to hou.default_legal_context_id as part of R12 uptake
5701
5702 DBMS_SQL.PARSE( x_cursor_id, x_sql_statement, DBMS_SQL.V7 );
5703
5704 x_ignore := DBMS_SQL.EXECUTE( x_cursor_id );
5705 DBMS_SQL.CLOSE_CURSOR (x_cursor_id);
5706
5707 p_errnum := 0;
5708 p_errmesg := '';
5709
5710 EXCEPTION
5711
5712 WHEN OTHERS
5713 THEN
5714 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
5715 p_errnum := -1;
5716 p_errmesg := (proc_name || ':' || sqlerrm);
5717 RAISE;
5718
5719 END denormalize_org_dimension ;
5720
5721
5722
5723 /**************************************************************
5724 * This procedure will denormalize the geo dimension for a given
5725 * table - the table name is given as an input parameter. It
5726 * makes the following assumptions about the columns that needs
5727 * to be updated :
5728 * 1. Organization_ID -- The Organization Id
5729 * 2. location_id -- The Location ID
5730 * 3. country_code -- The Country Code
5731 * 4. country_Name -- The Country Name
5732 * 5. Area_Code -- The Area Code
5733 * 6. Area_Name -- The Area Name
5734 * 7. region_code -- The region Code
5735 * 8. region_name -- The Region Name
5736 *************************************************************/
5737
5738 PROCEDURE denormalize_geo_dimension(
5739 p_table_name IN VARCHAR2,
5740 p_errnum IN OUT NOCOPY NUMBER,
5741 p_errmesg IN OUT NOCOPY VARCHAR2)
5742 AS
5743 x_cursor_id INTEGER ;
5744 x_sql_statement VARCHAR2(32767);
5745 x_sql_statement1 VARCHAR2(32767);
5746 x_ignore INTEGER ;
5747 x_mapping VARCHAR2(240);
5748
5749 proc_name VARCHAR2 (40);
5750
5751 BEGIN
5752 proc_name := 'denormalize_geo_dimension';
5753
5754 -- The region mapping from the bis_flex_mappings column
5755
5756 begin
5757
5758 select application_column_name
5759 into x_mapping
5760 from bis_flex_mappings_v
5761 where id_flex_code = 'HR_LOCATIONS'
5762 and flex_field_type = 'D'
5763 and level_short_name = 'REGION' ;
5764
5765 exception
5766
5767 when others then
5768 x_mapping := null ;
5769 end ;
5770
5771
5772 x_cursor_id := DBMS_SQL.OPEN_CURSOR;
5773
5774 x_sql_statement :=
5775 'UPDATE ' || p_table_name || ' xtable ' ||
5776 ' SET ( ' ||
5777 'location_id ' || ',' ||
5778 'country_code ' || ',' ||
5779 'country_name ' || ',' ||
5780 'area_code ' || ',' ||
5781 'area_name ' || ',' ||
5782 'region_code ' || ') = ' ||
5783 '( SELECT/*+ ORDERED PARALLEL */ ' ||
5784 ' horgu.location_id, ' ||
5785 ' hl.country, ' ||
5786 ' bthv.child_territory_name, ' ||
5787 ' bthv.parent_territory_code, ' ||
5788 ' bthv.parent_territory_name, ' ;
5789
5790 if (x_mapping is not null ) then
5791 x_sql_statement := x_sql_statement ||
5792 ' hl.' || x_mapping || ' ' ;
5793 elsif (x_mapping is null ) then
5794 x_sql_statement := x_sql_statement ||
5795 ' null ' ;
5796 end if ;
5797
5798 x_sql_statement := x_sql_statement ||
5799 ' FROM ' ||
5800 ' org_organization_definitions ood, ' ||
5801 ' hr_organization_units horgu, ' ||
5802 ' hr_locations hl, ' ||
5803 ' bis_territory_hierarchies_v bthv ' ||
5804 ' WHERE ood.organization_id = xtable.organization_id ' ||
5805 ' AND horgu.organization_id = ood.organization_id ' ||
5806 ' AND horgu.business_group_id = ood.business_group_id ' ||
5807 ' AND hl.location_id = horgu.location_id ' ||
5808 ' AND bthv.child_territory_code = hl.country ' ||
5809 ' AND bthv.child_territory_type = ''COUNTRY'' ' ||
5810 ' AND bthv.parent_territory_type = ''AREA'' ' ||
5811 ' ) ' ;
5812
5813 DBMS_SQL.PARSE( x_cursor_id, x_sql_statement, DBMS_SQL.V7 );
5814
5815 x_ignore := DBMS_SQL.EXECUTE( x_cursor_id );
5816
5817
5818 x_sql_statement1 :=
5819 'UPDATE ' || p_table_name || ' xtable ' ||
5820 ' SET ( ' ||
5821 'region_name ' || ') = ' ||
5822 '( SELECT ' ||
5823 ' bthv.child_territory_name ' ||
5824 ' FROM ' ||
5825 ' bis_territory_hierarchies_v bthv ' ||
5826 ' WHERE bthv.child_territory_code = xtable.region_code ' ||
5827 ' AND bthv.child_territory_type = ''REGION'' ' ||
5828 ' AND bthv.parent_territory_type = ''COUNTRY'' ' ||
5829 ' AND bthv.parent_territory_code = xtable.country_code ' ||
5830 ' ) ' ;
5831
5832
5833 DBMS_SQL.PARSE( x_cursor_id, x_sql_statement1, DBMS_SQL.V7 );
5834
5835 x_ignore := DBMS_SQL.EXECUTE( x_cursor_id );
5836 DBMS_SQL.CLOSE_CURSOR (x_cursor_id);
5837
5838 p_errnum := 0;
5839 p_errmesg := '';
5840
5841 EXCEPTION
5842
5843 WHEN OTHERS
5844 THEN
5845 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
5846 p_errnum := -1;
5847 p_errmesg := (proc_name || ':' || sqlerrm);
5848 RAISE;
5849
5850 END denormalize_geo_dimension ;
5851
5852
5853 /* Populate_temp_table
5854 */
5855 -- Since the three temporary tables only differ in which
5856 -- indicator type they are selected on, we will make the
5857 -- table name and indicator type parameters to the
5858 -- populating function.
5859 --
5860 -- Since the tables might not exist in the first run,
5861 -- we must pass the table name as an argument, and the
5862 -- the function has to be written as an execute immediate.
5863 PROCEDURE populate_temp_table (
5864 p_table_name IN VARCHAR2,
5865 p_indicator IN NUMBER,
5866 p_group_id IN NUMBER)
5867 IS
5868 -- procedure name
5869 proc_name VARCHAR2(20) ;
5870
5871 BEGIN
5872 proc_name := 'populate_temp_table';
5873 EXECUTE IMMEDIATE ('
5874 INSERT INTO ' ||p_table_name || ' (' ||
5875 ' group_id,
5876 organization_id,
5877 wip_entity_id,
5878 operation_seq_num,
5879 department_id,
5880 department_code,
5881 resource_id,
5882 resource_code,
5883 transaction_date,
5884 shift_num,
5885 standard_quantity,
5886 total_quantity,
5887 scrap_quantity,
5888 standard_units,
5889 applied_units_prd,
5890 applied_units_utz,
5891 available_units,
5892 resource_cost,
5893 resource_basis,
5894 indicator_type,
5895 process_phase,
5896 creation_date,
5897 created_by,
5898 last_updated_by,
5899 last_update_date,
5900 last_update_login,
5901 request_id,
5902 program_application_id,
5903 program_id,
5904 program_update_date,
5905 line_id,
5906 available_quantity,
5907 required_quantity,
5908 required_hours,
5909 share_from_dept_id' || ' ) ' ||
5910 'SELECT group_id,
5911 organization_id,
5912 wip_entity_id,
5913 operation_seq_num,
5914 department_id,
5915 department_code,
5916 resource_id,
5917 resource_code,
5918 transaction_date,
5919 shift_num,
5920 standard_quantity,
5921 total_quantity,
5922 scrap_quantity,
5923 standard_units,
5924 applied_units_prd,
5925 applied_units_utz,
5926 available_units,
5927 resource_cost,
5928 resource_basis,
5929 indicator_type,
5930 process_phase,
5931 creation_date,
5932 created_by,
5933 last_updated_by,
5934 last_update_date,
5935 last_update_login,
5936 request_id,
5937 program_application_id,
5938 program_id,
5939 program_update_date,
5940 line_id,
5941 available_quantity,
5942 required_quantity,
5943 required_hours,
5944 share_from_dept_id
5945 FROM wip_indicators_temp wit
5946 WHERE wit.indicator_type = ' || p_indicator);
5947 EXCEPTION
5948 WHEN OTHERS
5949 THEN
5950 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
5951 RAISE;
5952
5953 END populate_temp_table;
5954 ---------------------------------BUG 3280647 -----------------------------------------
5955 -- PROCEDURE populate_eff_temp_table is added to group data of WIP_EFFICIENCY, Which
5956 -- is necessary to avoid more than one rows of the data created due to deletion of insert
5957 -- statement Calculate_Std_Units(115.19). This Insert satement insert data for WIP_EFF_PHASE_THREE
5958 -- and later on updated with APPLIED_UNITS_PRD in PROCEDURE Calc_Eff_Applied_Units.
5959 -- Now statement in PROCEDURE Calc_Eff_Applied_Units is not working. And Procedure
5960 -- Misc_Applied_Units inserts extra rows for APPLIED_UNITS_PRD.
5961 -- Grouping is done here to avoid duplication
5962 --------------------------------------------------------------------------------------
5963 PROCEDURE populate_eff_temp_table (
5964 p_table_name IN VARCHAR2,
5965 p_indicator IN NUMBER,
5966 p_group_id IN NUMBER)
5967 IS
5968 -- procedure name
5969 proc_name VARCHAR2(20) ;
5970
5971 BEGIN
5972 proc_name := 'populate_temp_table';
5973 insert into WIP_BIS_EFF_TEMP(
5974 group_id,
5975 organization_id,
5976 wip_entity_id,
5977 operation_seq_num,
5978 department_id,
5979 department_code,
5980 resource_id,
5981 resource_code,
5982 transaction_date,
5983 shift_num,
5984 standard_quantity,
5985 total_quantity,
5986 scrap_quantity,
5987 standard_units,
5988 applied_units_prd,
5989 applied_units_utz,
5990 available_units,
5991 resource_cost,
5992 resource_basis,
5993 indicator_type,
5994 process_phase,
5995 creation_date,
5996 created_by,
5997 last_updated_by,
5998 last_update_date,
5999 last_update_login,
6000 request_id,
6001 program_application_id,
6002 program_id,
6003 program_update_date,
6004 line_id,
6005 available_quantity,
6006 required_quantity,
6007 required_hours,
6008 share_from_dept_id)
6009 select
6010 p_group_id,
6011 organization_id,
6012 wip_entity_id,
6013 operation_seq_num,
6014 department_id,
6015 department_code,
6016 resource_id,
6017 resource_code,
6018 trunc(transaction_date),
6019 NULL,
6020 SUM(standard_quantity),
6021 sum(total_quantity),
6022 sum(scrap_quantity),
6023 SUM(standard_units),
6024 sum(applied_units_prd),
6025 sum(applied_units_utz),
6026 sum(available_units),
6027 sum(resource_cost),
6028 NULL,
6029 WIP_EFFICIENCY,
6030 WIP_EFF_PHASE_FOUR, -- this is the fourth and final phase
6031 sysdate,
6032 g_userid,
6033 g_userid,
6034 sysdate,
6035 NULL,
6036 NULL,
6037 g_applicationid ,
6038 NULL,
6039 sysdate,
6040 NULL,
6041 sum(available_quantity),
6042 sum(required_quantity),
6043 sum(required_hours),
6044 NULL
6045 from
6046 wip_indicators_temp
6047 where indicator_type =WIP_EFFICIENCY
6048 and process_phase in(WIP_EFF_PHASE_ONE, WIP_EFF_PHASE_TWO,WIP_EFF_PHASE_THREE)
6049 group by
6050 organization_id,
6051 wip_entity_id,
6052 operation_seq_num,
6053 department_id,
6054 department_code,
6055 resource_id,
6056 resource_code,
6057 trunc(transaction_date);
6058
6059
6060
6061 EXCEPTION
6062 WHEN OTHERS
6063 THEN
6064 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
6065 RAISE;
6066
6067 END populate_eff_temp_table;
6068
6069
6070
6071 /* Simple_decomp
6072 */
6073 -- First query rewrite.
6074 -- All we do here is decompose the wip_indicators_temp table
6075 -- into 3 separate tables based on whether the records have
6076 -- indicator_type = WIP_EFFICIENCY, WIP_UTILIZATION, WIP_YIELD.
6077 -- Then we do a cartesian join on the three tables to pick the
6078 -- records we need. Since the tables have already been filtered
6079 -- using the group_id and the indicator types, the cartesian
6080 -- join will be on smaller tables and should be faster.
6081 PROCEDURE simple_decomp (p_group_id IN NUMBER)
6082 IS
6083 -- procedure name
6084 proc_name VARCHAR2(20);
6085
6086 BEGIN
6087 proc_name := 'simple_decomp';
6088
6089 INSERT INTO /*+ NOAPPEND */ wip_bis_prod_indicators (
6090 organization_id,
6091 wip_entity_id,
6092 inventory_item_id,
6093 transaction_date,
6094 operation_seq_num,
6095 department_id,
6096 department_code,
6097 resource_id,
6098 resource_code,
6099 standard_hours,
6100 applied_hours_prd,
6101 available_hours,
6102 applied_hours_utz,
6103 total_quantity,
6104 scrap_quantity,
6105 last_update_date,
6106 last_updated_by,
6107 creation_date,
6108 created_by,
6109 last_update_login,
6110 request_id,
6111 program_application_id,
6112 program_update_date)
6113 SELECT /*+ leading(wit2)*/ wit.organization_id,
6114 wit.wip_entity_id,
6115 we.primary_item_id,
6116 wit.transaction_date, -- already trunc'ed
6117 wit.operation_seq_num,
6118 wit.department_id,
6119 wit.department_code,
6120 wit.resource_id,
6121 wit.resource_code,
6122 wit.standard_units,
6123 wit.applied_units_prd,
6124 wit2.available_units,
6125 wit2.applied_units_utz,
6126 wit3.total_quantity,
6127 wit3.scrap_quantity,
6128 wit.last_update_date,
6129 wit.last_updated_by,
6130 wit.creation_date,
6131 wit.created_by,
6132 wit.last_update_login,
6133 wit.request_id,
6134 wit.program_application_id,
6135 sysdate
6136 FROM wip_entities we,
6137 wip_bis_yld_temp wit3,
6138 wip_bis_utz_temp wit2,
6139 wip_bis_eff_temp wit
6140 WHERE
6141 wit2.organization_id = wit.organization_id
6142 AND wit2.department_id = wit.department_id
6143 AND wit2.resource_id = wit.resource_id
6144 AND wit2.wip_entity_id = wit.wip_entity_id --Bug 3604065
6145 AND wit2.operation_seq_num = wit.operation_seq_num --Bug 3604065
6146 AND wit2.transaction_date = wit.transaction_date -- trunc'ed
6147 AND wit3.organization_id = wit.organization_id
6148 AND wit3.wip_entity_id = wit.wip_entity_id
6149 AND wit3.operation_seq_num = wit.operation_seq_num
6150 AND wit3.department_id = wit.department_id
6151 AND wit3.resource_id = wit.resource_id
6152 AND wit3.transaction_date = wit.transaction_date -- trunc'ed
6153 AND wit3.process_phase = 3
6154 AND we.wip_entity_id = wit.wip_entity_id
6155 AND we.organization_id = wit.organization_id;
6156
6157 commit;
6158
6159 return;
6160
6161 EXCEPTION
6162 WHEN OTHERS
6163 THEN
6164 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
6165 RAISE; -- exceptions handled in calling routine.
6166
6167 END simple_decomp;
6168
6169 -- Following bug 3387800, check for the existing flag not being
6170 -- equal to 1 for any row in the wip_bis_prod_indicators,
6171 -- wip_bis_prod_assy_yield or wip_bis_prod_dept_yield tables.
6172 -- If it is not equal to 1, all existing data data should not
6173 -- be backed up.
6174 --
6175 -- Note that since the old program used to set the existing_flag to
6176 -- 0 for old rows, but not delete them, this condition will take care
6177 -- of removing all old data.
6178 FUNCTION check_backup_needed
6179 RETURN BOOLEAN
6180 IS
6181
6182 l_csr_rec NUMBER;
6183
6184 -- by default, believe that backup is needed
6185 l_backup_needed BOOLEAN ;
6186
6187 CURSOR bad_existing_flag_wbpi_csr IS
6188 SELECT 1
6189 FROM wip_bis_prod_indicators
6190 WHERE nvl (existing_flag, -1) <> 1
6191 AND rownum < 2;
6192
6193 CURSOR bad_existing_flag_wbpay_csr IS
6194 SELECT 1
6195 FROM wip_bis_prod_assy_yield
6196 WHERE nvl (existing_flag, -1) <> 1
6197 AND rownum < 2;
6198
6199 CURSOR bad_existing_flag_wbpdy_csr IS
6200 SELECT 1
6201 FROM wip_bis_prod_dept_yield
6202 WHERE nvl (existing_flag, -1) <> 1
6203 AND rownum < 2;
6204
6205
6206 BEGIN
6207 l_backup_needed := TRUE;
6208 OPEN bad_existing_flag_wbpdy_csr;
6209 OPEN bad_existing_flag_wbpay_csr;
6210 OPEN bad_existing_flag_wbpi_csr;
6211
6212 FETCH bad_existing_flag_wbpdy_csr INTO l_csr_rec;
6213 FETCH bad_existing_flag_wbpay_csr INTO l_csr_rec;
6214 FETCH bad_existing_flag_wbpi_csr INTO l_csr_rec;
6215
6216 -- RS: These would return NOTFOUND when there is no bad data
6217 -- and also when there are no rows at all in the
6218 -- summary tables. In a case, when data is present in the
6219 -- temp tables but not in the summary, it would be better
6220 -- to return backup_needed = TRUE as we would have to merge
6221 -- data on completion of the run.
6222
6223 IF (bad_existing_flag_wbpdy_csr%NOTFOUND AND
6224 bad_existing_flag_wbpay_csr%NOTFOUND AND
6225 bad_existing_flag_wbpi_csr%NOTFOUND) THEN
6226
6227 -- The new program has run at least once
6228 -- and there is no need to truncate existing data. ? -> RS: can't be confirmed that this is the second run
6229 l_backup_needed := true;
6230
6231 ELSE
6232 -- Bad data present in summary tables. Collect entire data again.
6233 l_backup_needed := false;
6234 END IF;
6235
6236 CLOSE bad_existing_flag_wbpi_csr;
6237 CLOSE bad_existing_flag_wbpay_csr;
6238 CLOSE bad_existing_flag_wbpdy_csr;
6239
6240 return l_backup_needed;
6241
6242 END check_backup_needed;
6243
6244
6245
6246 -- Create the 3 backup tables
6247 -- WIP_BIS_PROD_INDICATORS
6248 -- WIP_BIS_PROD_ASSY_YIELD
6249 -- WIP_BIS_PROD_DEPT_YIELD
6250 -- into three temp tables
6251 -- The three tables are backed up with data less than the specified
6252 -- date. This will take care of the fact that the present collection
6253 -- might overlap with the data already collected. In that case, we
6254 -- want to keep the data collected this time, and throw away
6255 -- whatever was collected previously.
6256
6257 PROCEDURE backup_summary_tables (
6258 p_max_backup_date IN DATE,
6259 p_errnum OUT NOCOPY NUMBER,
6260 p_errmesg OUT NOCOPY VARCHAR2)
6261 IS
6262 -- procedure name
6263 proc_name VARCHAR2(20);
6264
6265 l_wip_bis_prod_indicators NUMBER := 0;
6266 l_wip_bis_prod_assy_yield NUMBER := 0;
6267 l_wip_bis_prod_dept_yield NUMBER := 0;
6268
6269 BEGIN
6270 proc_name := 'backup_summary_table';
6271 -- clear garbage out of the tables.
6272 -- RS: BUG 5132779: Check whether the summary tables are empty or have data in them.
6273 -- There would be a case when the previous collection would have errored
6274 -- out abnormally due to database issues. In such cases, the exception handling
6275 -- purges the data collected in the present run in the summary tables and tries
6276 -- to merge data from temp tables. But, this may also fail resulting in a state where
6277 -- the temp summary tables have data and actual summary tables are empty. In such
6278 -- cases, the clean up of temp tables in the following run should be avoided as
6279 -- the entire data would be lost if the temp summary tables are truncated.
6280
6281 SELECT count(1) INTO l_wip_bis_prod_indicators FROM wip_bis_prod_indicators;
6282
6283 SELECT count(1) INTO l_wip_bis_prod_assy_yield FROM wip_bis_prod_assy_yield;
6284
6285 SELECT count(1) INTO l_wip_bis_prod_dept_yield FROM wip_bis_prod_dept_yield;
6286
6287 -- RS: If summary tables don't have any data, it is assumed that the previous run failed at merge
6288 -- and data is present in temp tables. So, don't truncate or back up them in this run.
6289 -- Though this is an unusual case, its a difficult situation to get out as these temp tables
6290 -- would be truncated in the next run, and customer will have to collect entire data again.
6291
6292 IF (l_wip_bis_prod_indicators <> 0 AND l_wip_bis_prod_assy_yield <> 0 AND l_wip_bis_prod_dept_yield <> 0) THEN
6293
6294 IF g_debug = 1 THEN
6295 fnd_file.put_line(fnd_file.log,'Backing up old data (if any)...');
6296 END IF ;
6297 clear_temp_summary_tables (p_errnum, p_errmesg);
6298 IF (p_errnum < 0) THEN
6299 return;
6300 END IF;
6301
6302 -- simply back up the entire summary tables
6303
6304 INSERT INTO wip_bis_prod_indicators_temp (
6305 organization_id,
6306 wip_entity_id,
6307 inventory_item_id,
6308 operation_seq_num,
6309 department_id,
6310 department_code,
6311 resource_id,
6312 resource_code,
6313 transaction_date,
6314 total_quantity,
6315 scrap_quantity,
6316 standard_hours,
6317 applied_hours_prd,
6318 applied_hours_utz,
6319 available_hours,
6320 existing_flag,
6321 last_update_date,
6322 last_updated_by,
6323 creation_date,
6324 created_by,
6325 last_update_login,
6326 request_id,
6327 program_application_id,
6328 program_id,
6329 program_update_date,
6330 set_of_books_id,
6331 set_of_books_name,
6332 legal_entity_id,
6333 legal_entity_name,
6334 operating_unit_id,
6335 operating_unit_name,
6336 organization_name,
6337 location_id,
6338 area_code,
6339 area_name,
6340 country_code,
6341 country_name,
6342 region_code,
6343 region_name,
6344 category_id,
6345 category_name,
6346 inventory_item_name,
6347 period_set_name,
6348 year,
6349 quarter,
6350 month,
6351 indicator_type,
6352 share_from_dept_id)
6353 SELECT
6354 organization_id,
6355 wip_entity_id,
6356 inventory_item_id,
6357 operation_seq_num,
6358 department_id,
6359 department_code,
6360 resource_id,
6361 resource_code,
6362 transaction_date,
6363 total_quantity,
6364 scrap_quantity,
6365 standard_hours,
6366 applied_hours_prd,
6367 applied_hours_utz,
6368 available_hours,
6369 existing_flag,
6370 last_update_date,
6371 last_updated_by,
6372 creation_date,
6373 created_by,
6374 last_update_login,
6375 request_id,
6376 program_application_id,
6377 program_id,
6378 program_update_date,
6379 set_of_books_id,
6380 set_of_books_name,
6381 legal_entity_id,
6382 legal_entity_name,
6383 operating_unit_id,
6384 operating_unit_name,
6385 organization_name,
6386 location_id,
6387 area_code,
6388 area_name,
6389 country_code,
6390 country_name,
6391 region_code,
6392 region_name,
6393 category_id,
6394 category_name,
6395 inventory_item_name,
6396 period_set_name,
6397 year,
6398 quarter,
6399 month,
6400 indicator_type,
6401 share_from_dept_id
6402 FROM wip_bis_prod_indicators
6403 WHERE transaction_date < trunc (p_max_backup_date);
6404
6405
6406 INSERT INTO wip_bis_prod_assy_yield_temp (
6407 organization_id,
6408 wip_entity_id,
6409 inventory_item_id,
6410 transaction_date,
6411 completed_quantity,
6412 scrap_quantity,
6413 existing_flag,
6414 last_update_date,
6415 last_updated_by,
6416 creation_date,
6417 created_by,
6418 last_update_login,
6419 request_id,
6420 program_application_id,
6421 program_id,
6422 program_update_date,
6423 set_of_books_id,
6424 set_of_books_name,
6425 legal_entity_id,
6426 legal_entity_name,
6427 operating_unit_id,
6428 operating_unit_name,
6429 organization_name,
6430 category_id,
6431 category_name,
6432 inventory_item_name,
6433 location_id,
6434 area_code,
6435 area_name,
6436 country_code,
6437 country_name,
6438 region_code,
6439 region_name,
6440 period_set_name,
6441 year,
6442 quarter,
6443 month
6444 )
6445 SELECT
6446 organization_id,
6447 wip_entity_id,
6448 inventory_item_id,
6449 transaction_date,
6450 completed_quantity,
6451 scrap_quantity,
6452 existing_flag,
6453 last_update_date,
6454 last_updated_by,
6455 creation_date,
6456 created_by,
6457 last_update_login,
6458 request_id,
6459 program_application_id,
6460 program_id,
6461 program_update_date,
6462 set_of_books_id,
6463 set_of_books_name,
6464 legal_entity_id,
6465 legal_entity_name,
6466 operating_unit_id,
6467 operating_unit_name,
6468 organization_name,
6469 category_id,
6470 category_name,
6471 inventory_item_name,
6472 location_id,
6473 area_code,
6474 area_name,
6475 country_code,
6476 country_name,
6477 region_code,
6478 region_name,
6479 period_set_name,
6480 year,
6481 quarter,
6482 month
6483 FROM wip_bis_prod_assy_yield
6484 WHERE transaction_date < trunc (p_max_backup_date);
6485
6486
6487 INSERT INTO wip_bis_prod_dept_yield_temp (
6488 organization_id,
6489 wip_entity_id,
6490 inventory_item_id,
6491 operation_seq_num,
6492 department_id,
6493 department_code,
6494 transaction_date,
6495 total_quantity,
6496 scrap_quantity,
6497 existing_flag,
6498 last_update_date,
6499 last_updated_by,
6500 creation_date,
6501 created_by,
6502 last_update_login,
6503 request_id,
6504 program_application_id,
6505 program_id,
6506 program_update_date,
6507 set_of_books_id,
6508 set_of_books_name,
6509 legal_entity_id,
6510 legal_entity_name,
6511 operating_unit_id,
6512 operating_unit_name,
6513 organization_name,
6514 location_id,
6515 area_code,
6516 area_name,
6517 country_code,
6518 country_name,
6519 region_code,
6520 region_name,
6521 period_set_name,
6522 year,
6523 quarter,
6524 month
6525 )
6526 SELECT
6527 organization_id,
6528 wip_entity_id,
6529 inventory_item_id,
6530 operation_seq_num,
6531 department_id,
6532 department_code,
6533 transaction_date,
6534 total_quantity,
6535 scrap_quantity,
6536 existing_flag,
6537 last_update_date,
6538 last_updated_by,
6539 creation_date,
6540 created_by,
6541 last_update_login,
6542 request_id,
6543 program_application_id,
6544 program_id,
6545 program_update_date,
6546 set_of_books_id,
6547 set_of_books_name,
6548 legal_entity_id,
6549 legal_entity_name,
6550 operating_unit_id,
6551 operating_unit_name,
6552 organization_name,
6553 location_id,
6554 area_code,
6555 area_name,
6556 country_code,
6557 country_name,
6558 region_code,
6559 region_name,
6560 period_set_name,
6561 year,
6562 quarter,
6563 month
6564 FROM wip_bis_prod_dept_yield
6565 WHERE transaction_date < trunc (p_max_backup_date);
6566
6567 END IF;
6568
6569 p_errnum := 0;
6570 p_errmesg := '';
6571
6572 return;
6573
6574 EXCEPTION
6575
6576 WHEN OTHERS
6577 THEN
6578 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
6579 p_errnum := -1;
6580 p_errmesg := (proc_name || ':' || sqlerrm);
6581 if g_debug = 1 then
6582 fnd_file.put_line(fnd_file.log,'Failed in Backup of old data. Please run the collection after fixing the issue.');
6583 end if ;
6584 raise ; -- Added by Suhasini for Bug 5132779
6585
6586 -- RS: This used to return to populate_summary_table and not raise
6587 -- prior to bug fix 5132779, Now don't return to populate_summary_table
6588 -- as this would raise collection stage failed and truncate summary tables
6589 -- that are not yet backed up. This would result in loss of complete data
6590 -- in summary tables. Instead raise, so that it is handled in exception
6591 -- of populate_summary_table and collection is terminated normally.
6592
6593 END backup_summary_tables;
6594
6595
6596 /* Update the existing flag off all rows in
6597 in:
6598 wip_bis_prod_indicators
6599 wip_bis_prod_assy_yield
6600 wip_bis_prod_dept_yield
6601
6602 This is for fixing bug 3387800 which causes various views
6603 on these tables to turn up empty.
6604
6605 Do not commit here.
6606
6607 */
6608 PROCEDURE update_existing_flag (
6609 p_errnum OUT NOCOPY NUMBER,
6610 p_errmesg OUT NOCOPY VARCHAR2)
6611 IS
6612 proc_name VARCHAR2 (40);
6613 BEGIN
6614 proc_name := 'update_existing_flag';
6615 UPDATE wip_bis_prod_indicators
6616 SET existing_flag = 1;
6617
6618 UPDATE wip_bis_prod_assy_yield
6619 SET existing_flag = 1;
6620
6621 UPDATE wip_bis_prod_dept_yield
6622 SET existing_flag = 1;
6623
6624 p_errnum := 0;
6625 p_errmesg := '';
6626
6627 return;
6628
6629 EXCEPTION
6630
6631 WHEN OTHERS
6632 THEN
6633 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
6634 p_errnum := -1;
6635 p_errmesg := (proc_name || ':' || sqlerrm);
6636
6637 -- returns to populate_summary_table, so don't raise exception.
6638
6639
6640 END update_existing_flag;
6641
6642
6643 /* Merge the old data in the temp tables into the actual tables.
6644 The temp data is denormalized by item, org, time, and geo, and
6645 it is assumed that the data collected in this run has been
6646 denormalized by these dimensions before the merge.
6647 The merge can be done blindly, since we only backed up data
6648 prior to the start date of this run (i.e. data that is not
6649 overlapping with that collected this time).
6650 */
6651 PROCEDURE merge_previous_run_data (
6652 p_errnum OUT NOCOPY NUMBER,
6653 p_errmesg OUT NOCOPY VARCHAR2)
6654 IS
6655 proc_name VARCHAR2 (40) ;
6656 BEGIN
6657 proc_name := 'merge_previous_run_data';
6658 -- simply back up the entire temp counterparts of summary tables
6659
6660 INSERT INTO wip_bis_prod_indicators (
6661 organization_id,
6662 wip_entity_id,
6663 inventory_item_id,
6664 operation_seq_num,
6665 department_id,
6666 department_code,
6667 resource_id,
6668 resource_code,
6669 transaction_date,
6670 total_quantity,
6671 scrap_quantity,
6672 standard_hours,
6673 applied_hours_prd,
6674 applied_hours_utz,
6675 available_hours,
6676 existing_flag,
6677 last_update_date,
6678 last_updated_by,
6679 creation_date,
6680 created_by,
6681 last_update_login,
6682 request_id,
6683 program_application_id,
6684 program_id,
6685 program_update_date,
6686 set_of_books_id,
6687 set_of_books_name,
6688 legal_entity_id,
6689 legal_entity_name,
6690 operating_unit_id,
6691 operating_unit_name,
6692 organization_name,
6693 location_id,
6694 area_code,
6695 area_name,
6696 country_code,
6697 country_name,
6698 region_code,
6699 region_name,
6700 category_id,
6701 category_name,
6702 inventory_item_name,
6703 period_set_name,
6704 year,
6705 quarter,
6706 month,
6707 indicator_type,
6708 share_from_dept_id)
6709 SELECT
6710 organization_id,
6711 wip_entity_id,
6712 inventory_item_id,
6713 operation_seq_num,
6714 department_id,
6715 department_code,
6716 resource_id,
6717 resource_code,
6718 transaction_date,
6719 total_quantity,
6720 scrap_quantity,
6721 standard_hours,
6722 applied_hours_prd,
6723 applied_hours_utz,
6724 available_hours,
6725 existing_flag,
6726 last_update_date,
6727 last_updated_by,
6728 creation_date,
6729 created_by,
6730 last_update_login,
6731 request_id,
6732 program_application_id,
6733 program_id,
6734 program_update_date,
6735 set_of_books_id,
6736 set_of_books_name,
6737 legal_entity_id,
6738 legal_entity_name,
6739 operating_unit_id,
6740 operating_unit_name,
6741 organization_name,
6742 location_id,
6743 area_code,
6744 area_name,
6745 country_code,
6746 country_name,
6747 region_code,
6748 region_name,
6749 category_id,
6750 category_name,
6751 inventory_item_name,
6752 period_set_name,
6753 year,
6754 quarter,
6755 month,
6756 indicator_type,
6757 share_from_dept_id
6758 FROM wip_bis_prod_indicators_temp;
6759
6760 INSERT INTO wip_bis_prod_assy_yield (
6761 organization_id,
6762 wip_entity_id,
6763 inventory_item_id,
6764 transaction_date,
6765 completed_quantity,
6766 scrap_quantity,
6767 existing_flag,
6768 last_update_date,
6769 last_updated_by,
6770 creation_date,
6771 created_by,
6772 last_update_login,
6773 request_id,
6774 program_application_id,
6775 program_id,
6776 program_update_date,
6777 set_of_books_id,
6778 set_of_books_name,
6779 legal_entity_id,
6780 legal_entity_name,
6781 operating_unit_id,
6782 operating_unit_name,
6783 organization_name,
6784 category_id,
6785 category_name,
6786 inventory_item_name,
6787 location_id,
6788 area_code,
6789 area_name,
6790 country_code,
6791 country_name,
6792 region_code,
6793 region_name,
6794 period_set_name,
6795 year,
6796 quarter,
6797 month
6798 )
6799 SELECT
6800 organization_id,
6801 wip_entity_id,
6802 inventory_item_id,
6803 transaction_date,
6804 completed_quantity,
6805 scrap_quantity,
6806 existing_flag,
6807 last_update_date,
6808 last_updated_by,
6809 creation_date,
6810 created_by,
6811 last_update_login,
6812 request_id,
6813 program_application_id,
6814 program_id,
6815 program_update_date,
6816 set_of_books_id,
6817 set_of_books_name,
6818 legal_entity_id,
6819 legal_entity_name,
6820 operating_unit_id,
6821 operating_unit_name,
6822 organization_name,
6823 category_id,
6824 category_name,
6825 inventory_item_name,
6826 location_id,
6827 area_code,
6828 area_name,
6829 country_code,
6830 country_name,
6831 region_code,
6832 region_name,
6833 period_set_name,
6834 year,
6835 quarter,
6836 month
6837 FROM wip_bis_prod_assy_yield_temp;
6838
6839 INSERT INTO wip_bis_prod_dept_yield (
6840 organization_id,
6841 wip_entity_id,
6842 inventory_item_id,
6843 operation_seq_num,
6844 department_id,
6845 department_code,
6846 transaction_date,
6847 total_quantity,
6848 scrap_quantity,
6849 existing_flag,
6850 last_update_date,
6851 last_updated_by,
6852 creation_date,
6853 created_by,
6854 last_update_login,
6855 request_id,
6856 program_application_id,
6857 program_id,
6858 program_update_date,
6859 set_of_books_id,
6860 set_of_books_name,
6861 legal_entity_id,
6862 legal_entity_name,
6863 operating_unit_id,
6864 operating_unit_name,
6865 organization_name,
6866 location_id,
6867 area_code,
6868 area_name,
6869 country_code,
6870 country_name,
6871 region_code,
6872 region_name,
6873 period_set_name,
6874 year,
6875 quarter,
6876 month
6877 )
6878 SELECT
6879 organization_id,
6880 wip_entity_id,
6881 inventory_item_id,
6882 operation_seq_num,
6883 department_id,
6884 department_code,
6885 transaction_date,
6886 total_quantity,
6887 scrap_quantity,
6888 existing_flag,
6889 last_update_date,
6890 last_updated_by,
6891 creation_date,
6892 created_by,
6893 last_update_login,
6894 request_id,
6895 program_application_id,
6896 program_id,
6897 program_update_date,
6898 set_of_books_id,
6899 set_of_books_name,
6900 legal_entity_id,
6901 legal_entity_name,
6902 operating_unit_id,
6903 operating_unit_name,
6904 organization_name,
6905 location_id,
6906 area_code,
6907 area_name,
6908 country_code,
6909 country_name,
6910 region_code,
6911 region_name,
6912 period_set_name,
6913 year,
6914 quarter,
6915 month
6916 FROM wip_bis_prod_dept_yield_temp;
6917
6918 p_errnum := 0;
6919 p_errmesg := '';
6920
6921 RETURN;
6922
6923 EXCEPTION
6924
6925 WHEN OTHERS
6926 THEN
6927 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
6928 p_errnum := -1;
6929 p_errmesg := (proc_name || ':' || sqlerrm);
6930 if g_debug = 1 then
6931 fnd_file.put_line(fnd_file.log,'Failed during merging old data. Data for the present collection range is being truncated.');
6932 fnd_file.put_line(fnd_file.log,'Please run the collection after fixing the issue.');
6933 end if ;
6934
6935 -- truncate the 3 summary tables
6936 execute immediate 'truncate table ' || g_wip_schema ||
6937 '.WIP_BIS_PROD_INDICATORS';
6938
6939 execute immediate 'truncate table ' || g_wip_schema ||
6940 '.WIP_BIS_PROD_DEPT_YIELD';
6941
6942 execute immediate 'truncate table ' || g_wip_schema ||
6943 '.WIP_BIS_PROD_ASSY_YIELD';
6944 raise; -- Added by Suhasini for bug 5132779
6945 -- RS: Do not return to populate_summary as it would raise collection_stage_failed exception
6946 -- and try to merge data again. This may result in an inconsistent state of the three
6947 -- summary tables as data is committed after that.
6948
6949 END merge_previous_run_data;
6950
6951 /* clean out the temp summary tables that were used to
6952 preserve data from previous runs
6953 */
6954 PROCEDURE clear_temp_summary_tables (
6955 p_errnum OUT NOCOPY NUMBER,
6956 p_errmesg OUT NOCOPY VARCHAR2)
6957 IS
6958 proc_name VARCHAR2 (40);
6959
6960 BEGIN
6961 proc_name := 'clear_temp_summary_tables';
6962 execute immediate 'truncate table ' || g_wip_schema ||
6963 '.WIP_BIS_PROD_INDICATORS_TEMP';
6964 execute immediate 'truncate table ' || g_wip_schema ||
6965 '.WIP_BIS_PROD_DEPT_YIELD_TEMP';
6966 execute immediate 'truncate table ' || g_wip_schema ||
6967 '.WIP_BIS_PROD_ASSY_YIELD_TEMP';
6968
6969 p_errnum := 0;
6970 p_errmesg := '';
6971
6972 return;
6973
6974 EXCEPTION
6975
6976 WHEN OTHERS
6977 THEN
6978 FND_FILE.PUT_LINE (fnd_file.log, proc_name || ':' || sqlerrm);
6979 p_errnum := -1;
6980 p_errmesg := (proc_name || ':' || sqlerrm);
6981 RAISE;
6982
6983 END clear_temp_summary_tables;
6984
6985
6986 END WIP_PROD_INDICATORS;