[Home] [Help]
785: and end_date is null
786: and resource_id in (select resource_id from bom_resources where resource_type = 2)
787: and (exclude_scheduled_flag is null or
788: exclude_scheduled_flag <>
789: nvl((select scheduled_flag from wip_operation_resources wor
790: where wor.wip_entity_id = t.wip_entity_id
791: and wor.operation_seq_num = t.operation_seq_num
792: and wor.resource_seq_num = t.resource_seq_num), 2) /* ad-hoc has no scheduled flag*/
793: );
810: decode(
811: ( select count(*)
812: from wip_resource_actual_times wrat,
813: bom_resources br,
814: wip_operation_resources wor
815: where wrat.wip_entity_id = p_wip_entity_id
816: and wrat.operation_seq_num = p_operation_seq_num
817: and wrat.resource_id = br.resource_id
818: and wrat.wip_entity_id = wor.wip_entity_id (+) /* ad hoc resource not in wor */
831: cursor max_labor_end_date(p_scheduled_flag number) is
832: select max(wrat.end_date)
833: from wip_resource_actual_times wrat,
834: bom_resources br,
835: wip_operation_resources wor
836: where wrat.wip_entity_id = p_wip_entity_id
837: and wrat.operation_seq_num = p_operation_seq_num
838: and wrat.resource_id = br.resource_id
839: and wrat.wip_entity_id = wor.wip_entity_id (+) /* ad hoc resource not in wor */
896: and start_date is not null
897: and end_date is null
898: and resource_id in (select resource_id from bom_resources where resource_type = 1)
899: and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
900: from wip_operation_resources wor
901: where wor.wip_entity_id = t.wip_entity_id
902: and wor.operation_seq_num = t.operation_seq_num
903: and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;
904: end if;
933: and start_date is not null
934: and end_date is null
935: and resource_id in (select resource_id from bom_resources where resource_type = 1)
936: and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
937: from wip_operation_resources wor
938: where wor.wip_entity_id = t.wip_entity_id
939: and wor.operation_seq_num = t.operation_seq_num
940: and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;
941: end if;
969: and start_date is not null
970: and end_date is null
971: and resource_id in (select resource_id from bom_resources where resource_type = 1)
972: and ( select decode(scheduled_flag, 2, 1, scheduled_flag)
973: from wip_operation_resources wor
974: where wor.wip_entity_id = t.wip_entity_id
975: and wor.operation_seq_num = t.operation_seq_num
976: and wor.resource_seq_num = t.resource_seq_num) = l_scheduled_flag;
977: end if;
1011: if( resource_seq_in_wrat%NOTFOUND ) then
1012:
1013: select max(wor.resource_seq_num) seq
1014: into l_wor_max
1015: from wip_operation_resources wor
1016: where wor.wip_entity_id = p_wip_entity_id
1017: and wor.operation_seq_num = p_operation_seq_num;
1018:
1019: select max(wrat.resource_seq_num) seq
1039: IS
1040: Begin
1041:
1042: /* update wor's actual completion date */
1043: update wip_operation_resources wor
1044: set
1045: wor.actual_completion_date =
1046: nvl( ( select max(wrat.end_date)
1047: from wip_resource_actual_times wrat
1064: update wip_operations wo
1065: set
1066: wo.actual_completion_date =
1067: nvl(greatest( ( select max(wor.actual_completion_date)
1068: from wip_operation_resources wor
1069: where wor.wip_entity_id = wo.wip_entity_id
1070: and wor.operation_seq_num = wo.operation_seq_num
1071: and wor.actual_completion_date is not null)
1072: , ( select max(wrat.end_date)
1280: wrat.end_date,
1281: wrat.time_entry_mode
1282: from wip_resource_actual_times wrat,
1283: bom_resources br,
1284: wip_operation_resources wor
1285: where wrat.wip_entity_id = p_wip_entity_id
1286: and wrat.operation_seq_num = p_completed_op
1287: and wrat.resource_id = br.resource_id
1288: and wrat.wip_entity_id = wor.wip_entity_id (+)
1304: --Joining WOR can cause clocking out automatcially when doing clocking in
1305: cursor active_clock_cursor is
1306: select count(*)
1307: from wip_resource_actual_times wrat
1308: --wip_operation_resources wor
1309: where wrat.wip_entity_id = p_wip_entity_id
1310: and wrat.operation_seq_num = p_completed_op
1311: and wrat.status_type = 1
1312: and wrat.process_status <> 4
1636: l_params wip_logger.param_tbl_t;
1637: l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1638: cursor has_autocharge_manual_cursor is
1639: select 1
1640: from wip_operation_resources wor
1641: where wor.wip_entity_id = p_wip_entity_id
1642: and wor.operation_seq_num = p_operation_seq_num
1643: and wor.autocharge_type = G_BOM_AUTOCHARGE_TYPE_MANUAL
1644: and rownum = 1;
1643: and wor.autocharge_type = G_BOM_AUTOCHARGE_TYPE_MANUAL
1644: and rownum = 1;
1645:
1646: cursor autocharge_type_res_cursor is select wor.autocharge_type
1647: from wip_operation_resources wor
1648: where wor.wip_entity_id = p_wip_entity_id
1649: and wor.operation_seq_num = p_operation_seq_num
1650: and wor.resource_seq_num = p_resource_seq_num;
1651: BEGIN
1794: and wrat.start_date is not null
1795: and wrat.process_status <> 4;
1796:
1797: cursor min_start_date_wor_cursor is select min(wor.actual_start_date)
1798: from wip_operation_resources wor
1799: where wor.wip_entity_id = p_wip_entity_id
1800: and wor.operation_seq_num = p_operation_seq_num
1801: and wor.actual_start_date is not null;
1802:
1825: open min_start_date_wrat_cursor;
1826: fetch min_start_date_wrat_cursor into l_min_start_date;
1827: if min_start_date_wrat_cursor%FOUND then
1828: if l_min_start_date is not null then
1829: update wip_operation_resources set
1830: actual_start_date = l_min_start_date
1831: where wip_entity_id = p_wip_entity_id
1832: and operation_seq_num = p_operation_seq_num
1833: and resource_seq_num = p_resource_seq_num;
1901:
1902: --find out if there are any active next resources
1903: cursor active_next_resource_cursor is select 'Y'
1904: from wip_resource_actual_times wrat,
1905: wip_operation_resources wor
1906: where wrat.wip_entity_id = p_wip_entity_id
1907: and wrat.operation_seq_num = p_operation_seq_num
1908: and wrat.status_type = 1
1909: and wrat.process_status <> 4
1913: and wrat.operation_seq_num = wor.operation_seq_num
1914: and wrat.resource_id = wor.resource_id;
1915:
1916: cursor max_end_date_wor_cursor is select max(wor.actual_completion_date)
1917: from wip_operation_resources wor
1918: where wor.wip_entity_id = p_wip_entity_id
1919: and wor.operation_seq_num = p_operation_seq_num
1920: and not exists (select wor.actual_completion_date
1921: from wip_operation_resources wor
1917: from wip_operation_resources wor
1918: where wor.wip_entity_id = p_wip_entity_id
1919: and wor.operation_seq_num = p_operation_seq_num
1920: and not exists (select wor.actual_completion_date
1921: from wip_operation_resources wor
1922: where wor.wip_entity_id = p_wip_entity_id
1923: and wor.operation_seq_num = p_operation_seq_num
1924: and wor.actual_completion_date is null);
1925: BEGIN
1941: open max_end_date_wrat_cursor;
1942: fetch max_end_date_wrat_cursor into l_max_end_date;
1943: if max_end_date_wrat_cursor%FOUND then
1944: if l_max_end_date is not null then
1945: update wip_operation_resources set
1946: actual_completion_date = l_max_end_date
1947: where wip_entity_id = p_wip_entity_id
1948: and operation_seq_num = p_operation_seq_num
1949: and resource_seq_num = p_resource_seq_num;
2015: p_resource_seq_num => p_resource_seq_num,
2016: p_resource_id => p_resource_id,
2017: p_instance_id => null,
2018: p_start_date => p_start_date);
2019: update wip_operation_resources set
2020: projected_completion_date = l_projected_completion_date
2021: where wip_entity_id = p_wip_entity_id
2022: and operation_seq_num = p_operation_seq_num
2023: and resource_seq_num = p_resource_seq_num;
2789: where br.resource_id = p_resource_id;
2790:
2791: cursor scheduled_flag_cursor is
2792: select scheduled_flag
2793: from wip_operation_resources wor
2794: where wor.wip_entity_id = p_wip_entity_id
2795: and wor.operation_seq_num = p_operation_seq_num
2796: and wor.resource_seq_num = p_resource_seq_num;
2797:
2797:
2798: /* check if there is no clock-in in the scheduled flag group */
2799: cursor num_job_op_cursor(p_scheduled_flag varchar2) is
2800: select count(*)
2801: from wip_resource_actual_times wrat, wip_operation_resources wor
2802: where wrat.wip_entity_id = p_wip_entity_id
2803: and wrat.operation_seq_num = p_operation_seq_num
2804: and wrat.wip_entity_id = wor.wip_entity_id (+)
2805: and wrat.operation_seq_num = wor.operation_seq_num (+)
2813: cursor time_records_machine_cursor(c_scheduled_flag number) is
2814: select wor.resource_id,
2815: wor.resource_seq_num,
2816: wor.uom_code
2817: from wip_operation_resources wor,
2818: bom_resources br
2819: where wor.wip_entity_id = p_wip_entity_id
2820: and wor.operation_seq_num = p_operation_seq_num
2821: and decode(wor.scheduled_flag, 2, 1, wor.scheduled_flag) =
3611: -- Modified for Bug 16528843.
3612: begin
3613: select wor.basis_type, wor.standard_rate_flag, wor.uom_code,ca.activity_id, ca.activity
3614: into l_basis_type, l_standard_rate_flag, l_primary_uom, l_activity_id, l_activity_name
3615: from wip_operation_resources wor,cst_activities ca
3616: where wor.wip_entity_id = p_wip_entity_id
3617: and wor.operation_seq_num = p_operation_seq_num
3618: and wor.resource_seq_num = l_resource_seq_num -- p_resource_seq_num Bug 13481688
3619: and wor.resource_id = p_resource_id
3811: l_activity_name); --Bug 10097774
3812: -- Bug 13481688 @start
3813: if (l_resource_seq_num is not null ) then
3814:
3815: insert into wip_operation_resources
3816: (wip_entity_id,
3817: operation_seq_num,
3818: resource_seq_num,
3819: organization_id,
3868: and wti.process_status = 1
3869: and wti.transaction_type = 1
3870: and not exists
3871: (select 'op res already exists'
3872: from wip_operation_resources wor
3873: where wor.wip_entity_id = wti.wip_entity_id
3874: and wor.operation_seq_num = wti.operation_seq_num
3875: and wor.resource_seq_num = wti.resource_seq_num
3876: and wor.organization_id = wti.organization_id