[Home] [Help]
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;
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.
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,
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,
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,
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'));
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,
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;
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 ;
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);
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,
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;
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.
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: --
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,
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';
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
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
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
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
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:
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;
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;
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:
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,
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,
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,
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
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
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
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
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,
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,
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
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
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
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
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
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,
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
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 ;
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:
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
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(
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)
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.');
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:
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:
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;
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,
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,
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';
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
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:
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),
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,
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
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
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 ;
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,
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),
4902: end if ;
4903: */
4904:
4905:
4906: insert into wip_indicators_temp(
4907: group_id,
4908: organization_id,
4909: department_id,
4910: department_code,
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
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:
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,
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
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;
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
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,
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