[Home] [Help]
6: G_BOM_AUTOCHARGE_TYPE_MANUAL CONSTANT NUMBER := 2;
7:
8: G_RES_CHG_FAILED Number :=wip_constants.no;
9:
10: -- Insert a record into the wip_resource_actual_times table.
11: PROCEDURE record_insert(
12: p_time_entry_id in number,
13: p_organization_id in number,
14: p_wip_entity_id in number,
61: l_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
62: l_return_status := 'U';
63:
64: if p_time_entry_id is null then
65: select WIP_RESOURCE_ACTUAL_TIMES_S.nextval into l_time_entry_id from dual;
66: else
67: l_time_entry_id := p_time_entry_id;
68: end if;
69:
108: else
109: l_projected_completion_date := p_projected_completion_date;
110: end if;
111:
112: insert into wip_resource_actual_times
113: (time_entry_id,
114: organization_id,
115: wip_entity_id,
116: operation_seq_num,
264: x_time_entry_id := l_time_entry_id;
265: x_return_status := l_return_status;
266: END record_insert;
267:
268: -- Update a record in the wip_resource_actual_times table.
269: PROCEDURE record_update(
270: p_time_entry_id in number,
271: p_organization_id in number,
272: p_wip_entity_id in number,
327: to_name => null);
328: end if;
329:
330: select object_version_number, start_date into l_object_version_num, l_start_date
331: from wip_resource_actual_times where time_entry_id = p_time_entry_id;
332:
333: if l_start_date <> p_start_date then
334: l_projected_completion_date := wip_ws_util.get_projected_completion_date(p_organization_id => p_organization_id,
335: p_wip_entity_id => p_wip_entity_id,
342: l_projected_completion_date := p_projected_completion_date;
343: end if;
344:
345: if l_object_version_num = p_object_version_num then
346: update wip_resource_actual_times set
347: organization_id = p_organization_id,
348: wip_entity_id = p_wip_entity_id,
349: operation_seq_num = p_operation_seq_num,
350: resource_id = p_resource_id,
393:
394: x_return_status := l_return_status;
395: END record_update;
396:
397: -- Delete a record from the wip_resource_actual_times table.
398: PROCEDURE record_delete(
399: p_time_entry_id in number,
400: p_object_version_num in number,
401: x_return_status out NOCOPY varchar2)
416: operation_seq_num,
417: resource_id,
418: resource_seq_num,
419: start_date
420: from wip_resource_actual_times
421: where time_entry_id = p_time_entry_id;
422: BEGIN
423: l_process_status := 4; --deleted
424: l_return_status := 'U';
434: if delete_cursor%NOTFOUND then
435: l_return_status := 'U';
436: else
437: if l_object_version_num = p_object_version_num then
438: update wip_resource_actual_times set
439: process_status = l_process_status,
440: object_version_number = p_object_version_num + 1,
441: last_update_date = sysdate,
442: last_updated_by = fnd_global.user_id,
463: close delete_cursor;
464: x_return_status := l_return_status;
465: END record_delete;
466:
467: -- Delete a record from the wip_resource_actual_times table.
468: PROCEDURE record_delete(
469: p_wip_entity_id in number,
470: p_operation_seq_num in number,
471: p_employee_id in number,
483: organization_id,
484: resource_id,
485: resource_seq_num,
486: start_date
487: from wip_resource_actual_times
488: where wip_entity_id = p_wip_entity_id
489: and operation_seq_num = p_operation_seq_num
490: and employee_id = p_employee_id
491: and process_status <> 4;
501: l_start_date;
502: if delete_cursor%NOTFOUND then
503: l_return_status := 'U';
504: else
505: update wip_resource_actual_times set
506: process_status = l_process_status,
507: object_version_number = l_object_version_num + 1,
508: last_update_date = sysdate,
509: last_updated_by = fnd_global.user_id,
557: Begin
558: l_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
559: l_date := sysdate;
560:
561: update wip_resource_actual_times t
562: set
563: end_date = l_date,
564: duration = (l_date - start_date)*24,
565: uom_code = l_uom_code,
591: cursor all_labor_clocked_out(p_scheduled_flag number) is
592: select
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
612:
613: /* max end date for yes/no labor resources */
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
648: close max_labor_end_date;
649:
650: if( l_date is null ) then l_date := sysdate; end if;
651:
652: update wip_resource_actual_times t
653: set
654: end_date = l_date,
655: duration = (l_date - start_date)*24,
656: uom_code = l_uom_code,
686: close max_labor_end_date;
687:
688: if( l_date is null ) then l_date := sysdate; end if;
689:
690: update wip_resource_actual_times t
691: set
692: end_date = l_date,
693: duration = (l_date - start_date)*24,
694: process_status = '3',
722: close max_labor_end_date;
723:
724: if( l_date is null ) then l_date := sysdate; end if;
725:
726: update wip_resource_actual_times t
727: set
728: end_date = l_date,
729: duration = (l_date - start_date)*24,
730: process_status = '3',
752: Is
753: -- check if the resuorce is aleady used in wrat
754: cursor resource_seq_in_wrat is
755: select resource_seq_num
756: from wip_resource_actual_times
757: where wip_entity_id = p_wip_entity_id
758: and operation_seq_num = p_operation_seq_num
759: and resource_id = p_resource_id
760: and resource_seq_num is not null
780: and wor.operation_seq_num = p_operation_seq_num;
781:
782: select max(wrat.resource_seq_num) seq
783: into l_wrat_max
784: from wip_resource_actual_times wrat
785: where wrat.wip_entity_id = p_wip_entity_id
786: and wrat.operation_seq_num = p_operation_seq_num
787: and wrat.resource_seq_num is not null;
788:
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
811: where wrat.wip_entity_id = wor.wip_entity_id
812: and wrat.operation_seq_num = wor.operation_seq_num
813: and wrat.resource_seq_num = wor.resource_seq_num
814: and wrat.process_status <> 4
815: and wrat.end_date is not null), sysdate)
816: where wip_entity_id = p_wip_entity_id
817: and operation_seq_num = p_operation_seq_num
818: and not exists (select wrat.end_date
819: from wip_resource_actual_times wrat
820: where wrat.wip_entity_id = wor.wip_entity_id
821: and wrat.operation_seq_num = wor.operation_seq_num
822: and wrat.resource_seq_num = wor.resource_seq_num
823: and wrat.start_date is not null
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)
836: from wip_resource_actual_times wrat
837: where wrat.wip_entity_id = wo.wip_entity_id
838: and wrat.operation_seq_num = wo.operation_seq_num
839: and wrat.end_date is not null)
840: ), sysdate)
840: ), sysdate)
841: where wip_entity_id = p_wip_entity_id
842: and operation_seq_num = p_operation_seq_num
843: and not exists (select 1
844: from wip_resource_actual_times wrat
845: where wrat.wip_entity_id = wo.wip_entity_id
846: and wrat.operation_seq_num = wo.operation_seq_num
847: and wrat.start_date is not null
848: and wrat.end_date is null
858:
859: cursor time_records_all_cursor is
860: select distinct wrat.wip_entity_id,
861: wrat.operation_seq_num
862: from wip_resource_actual_times wrat
863: where wrat.organization_id = p_organization_id
864: and wrat.status_type = 1
865: and wrat.process_status = 2 /* only new records */
866: and wrat.duration is not null;
886:
887: cursor time_records_mytime_cursor is
888: select distinct wrat.wip_entity_id,
889: wrat.operation_seq_num
890: from wip_resource_actual_times wrat
891: where wrat.organization_id = p_organization_id
892: and wrat.instance_id = p_instance_id
893: and wrat.status_type = 1
894: and wrat.process_status in (2, 3, 4)
1001: wrat.duration,
1002: wrat.uom_code,
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
1023:
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
1121: l_change_flag := 'Y';
1122: end if;
1123:
1124: if l_change_flag = 'Y' then
1125: update wip_resource_actual_times
1126: set
1127: cost_flag = l_cost_flag,
1128: add_to_rtg = l_add_to_rtg,
1129: resource_seq_num = l_resource_seq_num,
1163: But resource charging will not be allowed.*/
1164: begin
1165: select wrat.employee_id, mec.employee_num
1166: into l_employee_id, l_employee_num
1167: from wip_resource_actual_times wrat, mtl_employees_current_view mec
1168: where wrat.time_entry_id = l_entry_id
1169: and wrat.employee_id = mec.employee_id
1170: and wrat.organization_id = mec.organization_id;
1171: exception
1235: end if;
1236: end if;
1237:
1238: --mark status type complete for wrat records
1239: update wip_resource_actual_times
1240: set
1241: status_type = l_completed_status_type,
1242: process_status = l_completed_process_status,
1243: object_version_number = l_object_version_num + 1,
1258: update_actual_comp_dates(p_wip_entity_id, p_completed_op);
1259: end if;
1260:
1261: /* for wrat records with process status deleted - delete records */
1262: delete from wip_resource_actual_times
1263: where wip_entity_id = p_wip_entity_id
1264: and operation_seq_num = p_completed_op
1265: and status_type = 1
1266: and process_status = 4;
1265: and status_type = 1
1266: and process_status = 4;
1267:
1268: /* reset process_status of records of inserted/updated - mark as completed */
1269: update wip_resource_actual_times set
1270: process_status = l_completed_process_status,
1271: last_update_date = sysdate,
1272: last_updated_by = fnd_global.user_id,
1273: last_update_login = fnd_global.login_id
1463:
1464: l_min_start_date_wrat date;
1465:
1466: cursor min_start_date_wrat_cursor is select min(wrat.start_date)
1467: from wip_resource_actual_times wrat
1468: where wrat.wip_entity_id = p_wip_entity_id
1469: and wrat.operation_seq_num = p_operation_seq_num
1470: and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
1471: and wrat.start_date is not null
1538: l_max_end_date date;
1539: l_active_next_resource_flag varchar2(1);
1540:
1541: cursor max_end_date_wrat_cursor is select max(wrat.end_date)
1542: from wip_resource_actual_times wrat
1543: where wrat.wip_entity_id = p_wip_entity_id
1544: and wrat.operation_seq_num = p_operation_seq_num
1545: and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
1546: and not exists (select wrat.end_date
1543: where wrat.wip_entity_id = p_wip_entity_id
1544: and wrat.operation_seq_num = p_operation_seq_num
1545: and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
1546: and not exists (select wrat.end_date
1547: from wip_resource_actual_times wrat
1548: where wrat.wip_entity_id = p_wip_entity_id
1549: and wrat.operation_seq_num = p_operation_seq_num
1550: and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
1551: and wrat.end_date is null
1553: and wrat.process_status <> 4;
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
1804: l_ret_status varchar2(200);
1805:
1806: CURSOR shift_in_cursor IS
1807: SELECT 1
1808: FROM wip_resource_actual_times wrat
1809: WHERE wrat.wip_entity_id IS NULL
1810: AND wrat.end_date IS NULL
1811: and wrat.employee_id = l_person_id
1812: and wrat.time_entry_mode = 8
1836: l_shift_status := 'C';
1837: ELSE
1838: --shift in employee for which badge number was entered
1839: -- removed hardcoding of resource_id for bug 6969269.
1840: insert into wip_resource_actual_times
1841: (TIME_ENTRY_ID,ORGANIZATION_ID,WIP_ENTITY_ID,
1842: OPERATION_SEQ_NUM,RESOURCE_ID,RESOURCE_SEQ_NUM,
1843: INSTANCE_ID,SERIAL_NUMBER,TIME_ENTRY_MODE,
1844: COST_FLAG,ADD_TO_RTG,STATUS_TYPE,START_DATE,
1848: LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER,
1849: ACTION_FLAG,REQUEST_ID,PROGRAM_ID,
1850: PROGRAM_APPLICATION_ID,PROGRAM_UPDATE_DATE)
1851: values
1852: (WIP_RESOURCE_ACTUAL_TIMES_S.nextval,p_org_id,
1853: null,null,null,null,null,null,8,'N','N',null,
1854: sysdate,null,null,null,
1855: fnd_profile.value('BOM:HOUR_UOM_CODE'),
1856: l_person_id,1,fnd_global.user_id,
1880: l_person_id number;
1881:
1882: cursor shift_out_test_cursor is
1883: select start_date
1884: from wip_resource_actual_times wrat
1885: where wrat.wip_entity_id is null
1886: and wrat.EMPLOYEE_ID=l_person_id
1887: and wrat.ORGANIZATION_ID=p_org_id
1888: and wrat.end_date is null
1909: else
1910: l_date := sysdate;
1911: l_duration := (l_date - l_start_date)*24;
1912:
1913: update wip_resource_actual_times set
1914: end_date = l_date,
1915: duration = l_duration
1916: where ORGANIZATION_ID=p_org_id
1917: and employee_id =l_person_id
1938: l_person_id number;
1939:
1940: cursor undo_shift_in_test_cursor is
1941: select 1
1942: from wip_resource_actual_times wrat
1943: where wrat.wip_entity_id is null
1944: and wrat.EMPLOYEE_ID=l_person_id
1945: and wrat.ORGANIZATION_ID=p_org_id
1946: and wrat.end_date is null;
1961: l_shift_status :='N';
1962: elsif undo_shift_in_test_cursor%NOTFOUND then
1963: l_shift_status := 'O';
1964: else
1965: delete from wip_resource_actual_times wrat
1966: where
1967: wrat.wip_entity_id is null
1968: and wrat.EMPLOYEE_ID=l_person_id
1969: and wrat.ORGANIZATION_ID=p_org_id
1986: l_ret_status varchar2(200);
1987:
1988: CURSOR shift_in_cursor IS
1989: SELECT 1
1990: FROM wip_resource_actual_times wrat
1991: WHERE wrat.wip_entity_id IS NULL
1992: AND wrat.end_date IS NULL
1993: and wrat.employee_id = p_wip_employee_id
1994: and wrat.time_entry_mode = 8;
2011: l_shift_status := 'C';
2012: ELSE
2013: --shift in employee for which badge number was entered
2014: -- removed hardcoding of resource_id for bug 6969269.
2015: insert into wip_resource_actual_times
2016: (TIME_ENTRY_ID,ORGANIZATION_ID,WIP_ENTITY_ID,
2017: OPERATION_SEQ_NUM,RESOURCE_ID,RESOURCE_SEQ_NUM,
2018: INSTANCE_ID,SERIAL_NUMBER,TIME_ENTRY_MODE,
2019: COST_FLAG,ADD_TO_RTG,STATUS_TYPE,START_DATE,
2023: LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER,
2024: ACTION_FLAG,REQUEST_ID,PROGRAM_ID,
2025: PROGRAM_APPLICATION_ID,PROGRAM_UPDATE_DATE)
2026: values
2027: (WIP_RESOURCE_ACTUAL_TIMES_S.nextval,p_org_id,
2028: null,null,null,null,null,null,8,'N','N',null,
2029: sysdate,null,null,null,
2030: fnd_profile.value('BOM:HOUR_UOM_CODE'),
2031: p_wip_employee_id,1,fnd_global.user_id,
2055: l_person_id number;
2056:
2057: cursor shift_out_test_cursor is
2058: select start_date
2059: from wip_resource_actual_times wrat
2060: where wrat.wip_entity_id is null
2061: and wrat.EMPLOYEE_ID=p_wip_employee_id
2062: and wrat.ORGANIZATION_ID=p_org_id
2063: and wrat.end_date is null;
2079: else
2080: l_date := sysdate;
2081: l_duration := (l_date - l_start_date)*24;
2082:
2083: update wip_resource_actual_times set
2084: end_date = l_date,
2085: duration = l_duration
2086: where ORGANIZATION_ID=p_org_id
2087: and employee_id =p_wip_employee_id
2108: l_person_id number;
2109:
2110: cursor undo_shift_in_test_cursor is
2111: select 1
2112: from wip_resource_actual_times wrat
2113: where wrat.wip_entity_id is null
2114: and wrat.EMPLOYEE_ID=p_wip_employee_id
2115: and wrat.ORGANIZATION_ID=p_org_id
2116: and wrat.end_date is null;
2127: l_shift_status :='N';
2128: elsif undo_shift_in_test_cursor%NOTFOUND then
2129: l_shift_status := 'O';
2130: else
2131: delete from wip_resource_actual_times wrat
2132: where
2133: wrat.wip_entity_id is null
2134: and wrat.EMPLOYEE_ID=p_wip_employee_id
2135: and wrat.ORGANIZATION_ID=p_org_id
2170: l_num_job_op number;
2171: l_skill_check number;
2172:
2173: cursor clock_in_cursor is select 1
2174: from wip_resource_actual_times wrat
2175: where wrat.wip_entity_id = p_wip_entity_id
2176: and wrat.operation_seq_num = p_operation_seq_num
2177: and wrat.resource_id = p_resource_id
2178: and nvl(wrat.resource_seq_num, -1) = nvl(p_resource_seq_num, -1)
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 (+)
2219: decode(c_scheduled_flag, 2, 1, c_scheduled_flag)
2220: and br.resource_type = 1 --machine resource
2221: and wor.resource_id = br.resource_id
2222: and not exists (select 1
2223: from wip_resource_actual_times wrat
2224: where wrat.wip_entity_id = p_wip_entity_id
2225: and wrat.operation_seq_num = p_operation_seq_num
2226: and wrat.resource_id = wor.resource_id
2227: and wrat.resource_seq_num = wor.resource_seq_num
2393:
2394: cursor clock_out_test_cursor is
2395: select object_version_number,
2396: start_date
2397: from wip_resource_actual_times
2398: where wip_entity_id = p_wip_entity_id
2399: and operation_seq_num = p_operation_seq_num
2400: and resource_id = p_resource_id
2401: /* and nvl(resource_seq_num, -1) = nvl(p_resource_seq_num, -1) BUG 7322174*/
2415: l_clock_status := 'O';
2416: else
2417: l_date := sysdate;
2418: l_duration := (l_date - l_start_date)*24;
2419: update wip_resource_actual_times set
2420: end_date = l_date,
2421: duration = l_duration,
2422: uom_code = l_uom_code,
2423: process_status = l_process_status,
2546: l_dummy_var varchar2(1);
2547:
2548: cursor pending_clockout_cursor is
2549: select count(*)
2550: from wip_resource_actual_times
2551: where wip_entity_id = p_wip_entity_id
2552: and operation_seq_num = nvl(p_operation_seq_num, operation_seq_num)
2553: and status_type = 1
2554: and start_date is not null
2598: select count(1)
2599: into l_emp_clock_ins
2600: from dual
2601: where exists( select wip_entity_id
2602: from wip_resource_actual_times
2603: where organization_id = p_organization_id
2604: and employee_id = l_person_id
2605: and end_date is null
2606: and wip_entity_id is not null );