[Home] [Help]
192: X_ATTRIBUTE14 IN VARCHAR2,
193: X_ATTRIBUTE15 IN VARCHAR2
194: ) IS
195: BEGIN
196: INSERT INTO AHL_OPERATION_RESOURCES (
197: OPERATION_RESOURCE_ID,
198: OBJECT_VERSION_NUMBER,
199: LAST_UPDATE_DATE,
200: LAST_UPDATED_BY,
297: )
298: IS
299:
300: BEGIN
301: UPDATE AHL_OPERATION_RESOURCES SET
302: OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1,
303: RESOURCE_ID = X_RESOURCE_ID ,
304: WORKORDER_OPERATION_ID = X_WORKORDER_OPERATION_ID ,
305: RESOURCE_SEQUENCE_NUM = X_RESOURCE_SEQ_NUMBER ,
339: PROCEDURE DELETE_ROW (
340: X_OPERATION_RESOURCE_ID IN NUMBER
341: ) IS
342: BEGIN
343: DELETE FROM AHL_OPERATION_RESOURCES
344: WHERE OPERATION_RESOURCE_ID = X_OPERATION_RESOURCE_ID;
345: END DELETE_ROW;
346:
347: ---------------------------------------------------------------------
359: l_eff_end_date DATE;
360: l_sch_st_date DATE;
361: l_sch_end_date DATE;
362:
363: -- To find all information from AHL_OPERATION_RESOURCES view
364: CURSOR c_oper_req (x_id IN NUMBER) IS
365: SELECT * FROM AHL_OPERATION_RESOURCES
366: WHERE OPERATION_RESOURCE_ID = x_id;
367: c_oper_req_rec c_oper_req%ROWTYPE;
361: l_sch_end_date DATE;
362:
363: -- To find all information from AHL_OPERATION_RESOURCES view
364: CURSOR c_oper_req (x_id IN NUMBER) IS
365: SELECT * FROM AHL_OPERATION_RESOURCES
366: WHERE OPERATION_RESOURCE_ID = x_id;
367: c_oper_req_rec c_oper_req%ROWTYPE;
368:
369: -- Cursor to check
787: Ahl_Debug_Pub.debug( 'QUERY -->' || 'RESOURCE_SEQUENCE_NUM = ' || p_resrc_Require_rec.RESOURCE_SEQ_NUMBER ||
788: ' AND WORKORDER_OPERATION_ID = ' || p_resrc_Require_rec.WORKORDER_OPERATION_ID);
789:
790: l_valid_flag := Ahl_Utility_Pvt.check_uniqueness (
791: 'AHL_OPERATION_RESOURCES',
792: 'RESOURCE_SEQUENCE_NUM = ' || p_resrc_Require_rec.RESOURCE_SEQ_NUMBER ||
793: ' AND WORKORDER_OPERATION_ID = ' || p_resrc_Require_rec.WORKORDER_OPERATION_ID
794: );
795: ELSE
798: ' AND WORKORDER_OPERATION_ID = ' || p_resrc_Require_rec.WORKORDER_OPERATION_ID ||
799: ' AND OPERATION_RESOURCE_ID <> ' || p_resrc_Require_rec.OPERATION_RESOURCE_ID);
800:
801: l_valid_flag := Ahl_Utility_Pvt.check_uniqueness (
802: 'AHL_OPERATION_RESOURCES',
803: 'RESOURCE_SEQUENCE_NUM = ' || p_resrc_Require_rec.RESOURCE_SEQ_NUMBER ||
804: ' AND WORKORDER_OPERATION_ID = ' || p_resrc_Require_rec.WORKORDER_OPERATION_ID ||
805: ' AND OPERATION_RESOURCE_ID <> ' || p_resrc_Require_rec.OPERATION_RESOURCE_ID
806: );
1045: -- Check to see Operation Resource Id exists
1046: CURSOR Sch_id_exists (x_id IN NUMBER) IS
1047: SELECT 1 FROM dual
1048: WHERE EXISTS (SELECT 1
1049: FROM AHL_OPERATION_RESOURCES
1050: WHERE OPERATION_RESOURCE_ID = x_id);
1051:
1052: -- To find workorder_operation_id from ahl_workorder_operation_v view
1053: CURSOR c_wo_oper (x_id IN NUMBER, x_seq IN NUMBER) IS
1055: AHL_WORKORDER_OPERATIONS
1056: --AHL_WORKORDER_OPERATIONS_V
1057: WHERE WORKORDER_ID = x_id AND OPERATION_SEQUENCE_NUM = x_seq;
1058:
1059: -- To find the resource sequence nubmer from ahl_operation_resources
1060: CURSOR c_resrc_seq (x_id IN NUMBER, x_oper_seq IN NUMBER, x_resrc_seq IN NUMBER) IS
1061: SELECT COUNT(*) FROM
1062: AHL_WORKORDER_OPERATIONS AWOV, AHL_OPERATION_RESOURCES AOR
1063: WHERE AWOV.WORKORDER_OPERATION_ID = AOR.WORKORDER_OPERATION_ID AND
1058:
1059: -- To find the resource sequence nubmer from ahl_operation_resources
1060: CURSOR c_resrc_seq (x_id IN NUMBER, x_oper_seq IN NUMBER, x_resrc_seq IN NUMBER) IS
1061: SELECT COUNT(*) FROM
1062: AHL_WORKORDER_OPERATIONS AWOV, AHL_OPERATION_RESOURCES AOR
1063: WHERE AWOV.WORKORDER_OPERATION_ID = AOR.WORKORDER_OPERATION_ID AND
1064: AWOV.WORKORDER_ID = x_id AND AWOV.OPERATION_SEQUENCE_NUM = x_oper_seq AND
1065: AOR.RESOURCE_SEQUENCE_NUM = x_resrc_seq;
1066:
1063: WHERE AWOV.WORKORDER_OPERATION_ID = AOR.WORKORDER_OPERATION_ID AND
1064: AWOV.WORKORDER_ID = x_id AND AWOV.OPERATION_SEQUENCE_NUM = x_oper_seq AND
1065: AOR.RESOURCE_SEQUENCE_NUM = x_resrc_seq;
1066:
1067: -- To find the resource sequence nubmer from ahl_operation_resources
1068: CURSOR c_workorder (x_id IN NUMBER) IS
1069: SELECT * FROM AHL_WORKORDERS
1070: WHERE WORKORDER_ID = x_id;
1071: c_workorder_rec c_workorder%ROWTYPE;
1069: SELECT * FROM AHL_WORKORDERS
1070: WHERE WORKORDER_ID = x_id;
1071: c_workorder_rec c_workorder%ROWTYPE;
1072:
1073: -- To find the resource sequence nubmer from ahl_operation_resources
1074: CURSOR c_resources (x_id IN NUMBER) IS
1075: SELECT DEPARTMENT_ID FROM
1076: BOM_DEPARTMENT_RESOURCES
1077: WHERE RESOURCE_ID = x_id;
1075: SELECT DEPARTMENT_ID FROM
1076: BOM_DEPARTMENT_RESOURCES
1077: WHERE RESOURCE_ID = x_id;
1078:
1079: -- To find the resource sequence nubmer from ahl_operation_resources
1080: CURSOR c_wo_dept (x_id IN NUMBER) IS
1081: SELECT --V.DEPARTMENT_ID, -- department should be from wip_operations
1082: V.ORGANIZATION_ID,
1083: WORKORDER_NAME, WIP_ENTITY_ID FROM
1902: END IF;
1903:
1904: --
1905: -- Get Sequence Number for Resource Requirement ID
1906: SELECT AHL_OPERATION_RESOURCES_S.NEXTVAL
1907: INTO l_Resrc_Require_Rec.Operation_Resource_id
1908: FROM DUAL;
1909:
1910: --Check for Record Exists
2138: WHERE WORKORDER_ID = x_id
2139: AND A.VISIT_TASK_ID = B.VISIT_TASK_ID
2140: AND B.VISIT_ID = C.VISIT_ID;
2141:
2142: -- To find the RESOURCE_SEQUENCE_NUM from AHL_OPERATION_RESOURCES view
2143: CURSOR c_oper_resrc (x_id IN NUMBER) IS
2144: SELECT RESOURCE_SEQUENCE_NUM FROM
2145: AHL_OPERATION_RESOURCES
2146: WHERE WORKORDER_OPERATION_ID = x_id;
2141:
2142: -- To find the RESOURCE_SEQUENCE_NUM from AHL_OPERATION_RESOURCES view
2143: CURSOR c_oper_resrc (x_id IN NUMBER) IS
2144: SELECT RESOURCE_SEQUENCE_NUM FROM
2145: AHL_OPERATION_RESOURCES
2146: WHERE WORKORDER_OPERATION_ID = x_id;
2147:
2148: -- To find all information from AHL_OPERATION_RESOURCES view
2149: CURSOR c_oper_req (x_id IN NUMBER) IS
2144: SELECT RESOURCE_SEQUENCE_NUM FROM
2145: AHL_OPERATION_RESOURCES
2146: WHERE WORKORDER_OPERATION_ID = x_id;
2147:
2148: -- To find all information from AHL_OPERATION_RESOURCES view
2149: CURSOR c_oper_req (x_id IN NUMBER) IS
2150: SELECT * FROM AHL_OPERATION_RESOURCES
2151: WHERE OPERATION_RESOURCE_ID = x_id;
2152: c_oper_req_rec c_oper_req%ROWTYPE;
2146: WHERE WORKORDER_OPERATION_ID = x_id;
2147:
2148: -- To find all information from AHL_OPERATION_RESOURCES view
2149: CURSOR c_oper_req (x_id IN NUMBER) IS
2150: SELECT * FROM AHL_OPERATION_RESOURCES
2151: WHERE OPERATION_RESOURCE_ID = x_id;
2152: c_oper_req_rec c_oper_req%ROWTYPE;
2153:
2154: -- To find the resource sequence nubmer from ahl_operation_resources
2150: SELECT * FROM AHL_OPERATION_RESOURCES
2151: WHERE OPERATION_RESOURCE_ID = x_id;
2152: c_oper_req_rec c_oper_req%ROWTYPE;
2153:
2154: -- To find the resource sequence nubmer from ahl_operation_resources
2155: /*
2156: * R12 Perf Tuning
2157: * Balaji modified the query to use only base tables
2158: * instead of AHL_WORKORDERS_V
2171: WDJ.wip_entity_id = wo.wip_entity_id;
2172:
2173: c_workorder_rec c_workorder%ROWTYPE;
2174:
2175: -- To find the resource sequence nubmer from ahl_operation_resources
2176: CURSOR c_resources (x_id IN NUMBER) IS
2177: SELECT DEPARTMENT_ID FROM
2178: BOM_DEPARTMENT_RESOURCES
2179: WHERE RESOURCE_ID = x_id;
3024: oper_start_date DATE;
3025: oper_end_date DATE;
3026:
3027: CURSOR c_res_req (x_id IN NUMBER) IS
3028: SELECT * FROM AHL_OPERATION_RESOURCES
3029: WHERE OPERATION_RESOURCE_ID = x_id;
3030: c_resrc_req c_res_req%ROWTYPE;
3031:
3032: CURSOR c_resource (x_id IN NUMBER) IS
3030: c_resrc_req c_res_req%ROWTYPE;
3031:
3032: CURSOR c_resource (x_id IN NUMBER) IS
3033: SELECT ML.MEANING, BR.RESOURCE_TYPE, BR.RESOURCE_CODE
3034: FROM BOM_RESOURCES BR, MFG_LOOKUPS ML, AHL_OPERATION_RESOURCES AOR
3035: WHERE BR.RESOURCE_TYPE = ML.LOOKUP_CODE
3036: AND ML.LOOKUP_TYPE= 'BOM_RESOURCE_TYPE'
3037: AND AOR.RESOURCE_ID = BR.RESOURCE_ID
3038: AND AOR.OPERATION_RESOURCE_ID = x_id;
3038: AND AOR.OPERATION_RESOURCE_ID = x_id;
3039:
3040: CURSOR c_WIP_oper (x_id IN NUMBER) IS
3041: SELECT WORV.* FROM
3042: AHL_OPERATION_RESOURCES AOR,
3043: AHL_WORKORDER_OPERATIONS AWO,
3044: AHL_WORKORDERS AW,
3045: WIP_OPERATION_RESOURCES_V WORV
3046: WHERE WORV.OPERATION_SEQ_NUM = AWO.OPERATION_SEQUENCE_NUM
3072: AWO.workorder_id job_id
3073: FROM
3074: BOM_RESOURCES BOM,
3075: MTL_UNITS_OF_MEASURE MUOM,
3076: AHL_OPERATION_RESOURCES AOR,
3077: AHL_WORKORDER_OPERATIONS AWO
3078: WHERE
3079: AOR.OPERATION_RESOURCE_ID = x_id AND
3080: AOR.RESOURCE_ID = BOM.RESOURCE_ID AND
3187:
3188: l_Resrc_Require_Rec.REQ_START_DATE := c_resrc_req.SCHEDULED_START_DATE ;
3189: l_Resrc_Require_Rec.REQ_END_DATE := c_resrc_req.SCHEDULED_END_DATE ;
3190: -- change for ER 3974014
3191: -- the duration entered into AHL_OPERATION_RESOURCES is now the total_duration of all
3192: -- the resources
3193: -- so total_required := c_resrc_rec.duration
3194: -- duration := c_resrc_rec.duration/quantity
3195:
3318: IS
3319: --
3320: CURSOR Get_resource_cur (c_op_resource_id IN NUMBER)
3321: IS
3322: SELECT * FROM AHL_OPERATION_RESOURCES
3323: WHERE operation_resource_id = c_op_resource_id;
3324: --
3325: /* R12 Perf Tuning
3326: * Balaji modified the query to use only base tables
3656: Fnd_Msg_Pub.ADD;
3657: ELSE
3658: Ahl_Debug_Pub.debug ('Count in Assignments table' || l_count);
3659: Ahl_Debug_Pub.debug ('BEFORE DELETE RESOURCES' || p_x_resrc_Require_tbl(i).Operation_Resource_Id);
3660: DELETE FROM AHL_OPERATION_RESOURCES
3661: WHERE OPERATION_RESOURCE_ID = p_x_resrc_Require_tbl(i).operation_resource_id;
3662: END IF;
3663: END LOOP;
3664: END IF;--Count