[Home] [Help]
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
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 );
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 );
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:
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,
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,
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
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:
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
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)
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
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 );
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)
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
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
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
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,
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
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
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
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,
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
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:
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');
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,
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:
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,
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 := '';
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,
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 ;
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:
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
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:
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',
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:
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',
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:
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:
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,
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: --
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
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
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
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;
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 ;
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,
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 (
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
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;
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,
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,
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:
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';