[Home] [Help]
576: and end_date is null
577: and resource_id in (select resource_id from bom_resources where resource_type = 2)
578: and (exclude_scheduled_flag is null or
579: exclude_scheduled_flag <>
580: nvl((select scheduled_flag from wip_operation_resources wor
581: where wor.wip_entity_id = t.wip_entity_id
582: and wor.operation_seq_num = t.operation_seq_num
583: and wor.resource_seq_num = t.resource_seq_num), 2) /* ad-hoc has no scheduled flag*/
584: );
593: decode(
594: ( select count(*)
595: from wip_resource_actual_times wrat,
596: bom_resources br,
597: wip_operation_resources wor
598: where wrat.wip_entity_id = p_wip_entity_id
599: and wrat.operation_seq_num = p_operation_seq_num
600: and wrat.resource_id = br.resource_id
601: and wrat.wip_entity_id = wor.wip_entity_id (+) /* ad hoc resource not in wor */
614: cursor max_labor_end_date(p_scheduled_flag number) is
615: select max(wrat.end_date)
616: from wip_resource_actual_times wrat,
617: bom_resources br,
618: wip_operation_resources wor
619: where wrat.wip_entity_id = p_wip_entity_id
620: and wrat.operation_seq_num = p_operation_seq_num
621: and wrat.resource_id = br.resource_id
622: and wrat.wip_entity_id = wor.wip_entity_id (+) /* ad hoc resource not in wor */
665: and start_date is not null
666: and end_date is null
667: and resource_id in (select resource_id from bom_resources where resource_type = 1)
668: and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
669: from wip_operation_resources wor
670: where wor.wip_entity_id = t.wip_entity_id
671: and wor.operation_seq_num = t.operation_seq_num
672: and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;
673: end if;
702: and start_date is not null
703: and end_date is null
704: and resource_id in (select resource_id from bom_resources where resource_type = 1)
705: and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
706: from wip_operation_resources wor
707: where wor.wip_entity_id = t.wip_entity_id
708: and wor.operation_seq_num = t.operation_seq_num
709: and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;
710: end if;
738: and start_date is not null
739: and end_date is null
740: and resource_id in (select resource_id from bom_resources where resource_type = 1)
741: and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
742: from wip_operation_resources wor
743: where wor.wip_entity_id = t.wip_entity_id
744: and wor.operation_seq_num = t.operation_seq_num
745: and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;
746: end if;
774: if( resource_seq_in_wrat%NOTFOUND ) then
775:
776: select max(wor.resource_seq_num) seq
777: into l_wor_max
778: from wip_operation_resources wor
779: where wor.wip_entity_id = p_wip_entity_id
780: and wor.operation_seq_num = p_operation_seq_num;
781:
782: select max(wrat.resource_seq_num) seq
802: IS
803: Begin
804:
805: /* update wor's actual completion date */
806: update wip_operation_resources wor
807: set
808: wor.actual_completion_date =
809: nvl( ( select max(wrat.end_date)
810: from wip_resource_actual_times wrat
827: update wip_operations wo
828: set
829: wo.actual_completion_date =
830: nvl(greatest( ( select max(wor.actual_completion_date)
831: from wip_operation_resources wor
832: where wor.wip_entity_id = wo.wip_entity_id
833: and wor.operation_seq_num = wo.operation_seq_num
834: and wor.actual_completion_date is not null)
835: , ( select max(wrat.end_date)
1003: wrat.end_date,
1004: wrat.time_entry_mode
1005: from wip_resource_actual_times wrat,
1006: bom_resources br,
1007: wip_operation_resources wor
1008: where wrat.wip_entity_id = p_wip_entity_id
1009: and wrat.operation_seq_num = p_completed_op
1010: and wrat.resource_id = br.resource_id
1011: and wrat.wip_entity_id = wor.wip_entity_id (+)
1024: --find out if there are any active next resources
1025: cursor active_clock_cursor is
1026: select count(*)
1027: from wip_resource_actual_times wrat,
1028: wip_operation_resources wor
1029: where wrat.wip_entity_id = p_wip_entity_id
1030: and wrat.operation_seq_num = p_completed_op
1031: and wrat.status_type = 1
1032: and wrat.process_status <> 4
1339:
1340: l_manual_exist number;
1341: cursor has_autocharge_manual_cursor is
1342: select 1
1343: from wip_operation_resources wor
1344: where wor.wip_entity_id = p_wip_entity_id
1345: and wor.operation_seq_num = p_operation_seq_num
1346: and wor.autocharge_type = G_BOM_AUTOCHARGE_TYPE_MANUAL
1347: and rownum = 1;
1346: and wor.autocharge_type = G_BOM_AUTOCHARGE_TYPE_MANUAL
1347: and rownum = 1;
1348:
1349: cursor autocharge_type_res_cursor is select wor.autocharge_type
1350: from wip_operation_resources wor
1351: where wor.wip_entity_id = p_wip_entity_id
1352: and wor.operation_seq_num = p_operation_seq_num
1353: and wor.resource_seq_num = p_resource_seq_num;
1354: BEGIN
1471: and wrat.start_date is not null
1472: and wrat.process_status <> 4;
1473:
1474: cursor min_start_date_wor_cursor is select min(wor.actual_start_date)
1475: from wip_operation_resources wor
1476: where wor.wip_entity_id = p_wip_entity_id
1477: and wor.operation_seq_num = p_operation_seq_num
1478: and wor.actual_start_date is not null;
1479:
1487: open min_start_date_wrat_cursor;
1488: fetch min_start_date_wrat_cursor into l_min_start_date;
1489: if min_start_date_wrat_cursor%FOUND then
1490: if l_min_start_date is not null then
1491: update wip_operation_resources set
1492: actual_start_date = l_min_start_date
1493: where wip_entity_id = p_wip_entity_id
1494: and operation_seq_num = p_operation_seq_num
1495: and resource_seq_num = p_resource_seq_num;
1554:
1555: --find out if there are any active next resources
1556: cursor active_next_resource_cursor is select 'Y'
1557: from wip_resource_actual_times wrat,
1558: wip_operation_resources wor
1559: where wrat.wip_entity_id = p_wip_entity_id
1560: and wrat.operation_seq_num = p_operation_seq_num
1561: and wrat.status_type = 1
1562: and wrat.process_status <> 4
1566: and wrat.operation_seq_num = wor.operation_seq_num
1567: and wrat.resource_id = wor.resource_id;
1568:
1569: cursor max_end_date_wor_cursor is select max(wor.actual_completion_date)
1570: from wip_operation_resources wor
1571: where wor.wip_entity_id = p_wip_entity_id
1572: and wor.operation_seq_num = p_operation_seq_num
1573: and not exists (select wor.actual_completion_date
1574: from wip_operation_resources wor
1570: from wip_operation_resources wor
1571: where wor.wip_entity_id = p_wip_entity_id
1572: and wor.operation_seq_num = p_operation_seq_num
1573: and not exists (select wor.actual_completion_date
1574: from wip_operation_resources wor
1575: where wor.wip_entity_id = p_wip_entity_id
1576: and wor.operation_seq_num = p_operation_seq_num
1577: and wor.actual_completion_date is null);
1578: BEGIN
1579: open max_end_date_wrat_cursor;
1580: fetch max_end_date_wrat_cursor into l_max_end_date;
1581: if max_end_date_wrat_cursor%FOUND then
1582: if l_max_end_date is not null then
1583: update wip_operation_resources set
1584: actual_completion_date = l_max_end_date
1585: where wip_entity_id = p_wip_entity_id
1586: and operation_seq_num = p_operation_seq_num
1587: and resource_seq_num = p_resource_seq_num;
1623: p_resource_seq_num => p_resource_seq_num,
1624: p_resource_id => p_resource_id,
1625: p_instance_id => null,
1626: p_start_date => p_start_date);
1627: update wip_operation_resources set
1628: projected_completion_date = l_projected_completion_date
1629: where wip_entity_id = p_wip_entity_id
1630: and operation_seq_num = p_operation_seq_num
1631: and resource_seq_num = p_resource_seq_num;
2186: where br.resource_id = p_resource_id;
2187:
2188: cursor scheduled_flag_cursor is
2189: select scheduled_flag
2190: from wip_operation_resources wor
2191: where wor.wip_entity_id = p_wip_entity_id
2192: and wor.operation_seq_num = p_operation_seq_num
2193: and wor.resource_seq_num = p_resource_seq_num;
2194:
2194:
2195: /* check if there is no clock-in in the scheduled flag group */
2196: cursor num_job_op_cursor(p_scheduled_flag varchar2) is
2197: select count(*)
2198: from wip_resource_actual_times wrat, wip_operation_resources wor
2199: where wrat.wip_entity_id = p_wip_entity_id
2200: and wrat.operation_seq_num = p_operation_seq_num
2201: and wrat.wip_entity_id = wor.wip_entity_id (+)
2202: and wrat.operation_seq_num = wor.operation_seq_num (+)
2210: cursor time_records_machine_cursor(c_scheduled_flag number) is
2211: select wor.resource_id,
2212: wor.resource_seq_num,
2213: wor.uom_code
2214: from wip_operation_resources wor,
2215: bom_resources br
2216: where wor.wip_entity_id = p_wip_entity_id
2217: and wor.operation_seq_num = p_operation_seq_num
2218: and decode(wor.scheduled_flag, 2, 1, wor.scheduled_flag) =