[Home] [Help]
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
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,
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;
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
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:
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 ||
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,
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,
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: */
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,
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
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
5400: commit;
5401:
5402:
5403: /**********************
5404: WIP_BIS_PROD_ASSY_YIELD
5405: **********************/
5406:
5407: x_phase := 'VIII';
5408:
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:
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',
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:
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',
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:
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:
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
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
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
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';
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 ;
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,
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 (
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.
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;
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,
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,
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.
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: