[Home] [Help]
468: --debadey: Marshalling FDD sction 5.15 - AOG flag addition
469: X_AOG_FLAG IN VARCHAR2
470: ) IS
471: BEGIN
472: INSERT INTO AHL_SCHEDULE_MATERIALS (
473: SCHEDULED_MATERIAL_ID,
474: OBJECT_VERSION_NUMBER,
475: LAST_UPDATE_DATE,
476: LAST_UPDATED_BY,
636: --debadey: Marshalling FDD sction 5.15 - AOG flag addition
637: X_AOG_FLAG IN VARCHAR2
638: ) IS
639: BEGIN
640: UPDATE AHL_SCHEDULE_MATERIALS SET
641: OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1,
642: INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
643: SCHEDULE_DESIGNATOR = X_SCHEDULE_DESIGNATOR,
644: VISIT_ID = X_VISIT_ID,
694: PROCEDURE DELETE_ROW (
695: X_SCHEDULED_MATERIAL_ID IN NUMBER
696: ) IS
697: BEGIN
698: DELETE FROM AHL_SCHEDULE_MATERIALS
699: WHERE SCHEDULED_MATERIAL_ID = X_SCHEDULED_MATERIAL_ID;
700: END DELETE_ROW;
701: --sukhwsin: procedures added for SB Effectivity - starts
702: --Procedure to get mc_header_id from id or value
1237: c_org_id IN NUMBER,
1238: c_sequence_id IN NUMBER)
1239: IS
1240: SELECT 1
1241: FROM AHL_SCHEDULE_MATERIALS
1242: WHERE inventory_item_id = c_item_id
1243: AND workorder_operation_id = c_operation_id
1244: AND organization_id = c_org_id
1245: AND operation_sequence = c_sequence_id
1285: --sukhwsin::SB Effectivity - control position change
1286: c_mc_position_key IN NUMBER)
1287: IS
1288: SELECT *
1289: FROM ahl_schedule_materials
1290: WHERE rt_oper_material_id = c_rt_oper_mat_id
1291: AND visit_task_id = c_visit_task_id
1292: --sukhwsin::SB Effectivity - control position change
1293: AND nvl(position_key,-1) = nvl(c_mc_position_key,-1)
1340: -- sukhwsin::VCP Integration - Added cursor to get material req count that contains item group
1341: CURSOR Get_Alt_Mat_Requirment_Count(c_workorder_id IN NUMBER)
1342: IS
1343: SELECT COUNT(scheduled_material_id)
1344: FROM AHL_SCHEDULE_MATERIALS MAT,
1345: AHL_WORKORDERS WO
1346: WHERE WO.WORKORDER_ID = c_workorder_id
1347: AND WO.VISIT_TASK_ID = MAT.VISIT_TASK_ID
1348: AND NVL(MAT.STATUS,'NOTDEL') <> 'DELETED'
2392: l_Req_Material_Tbl(i).aog_flag := p_x_req_material_tbl(i).aog_flag;
2393: END IF;
2394: -- end of changes by debadey for Marshalling
2395: -- Get Sequence Number for schedule material ID
2396: SELECT ahl_schedule_materials_s.NEXTVAL
2397: INTO l_schedule_material_id FROM DUAL;
2398: --
2399: --
2400: IF G_DEBUG='Y' THEN
2431: IF ( l_material_rec.scheduled_material_id IS NOT NULL
2432: --Adithya added for FP Bug# 6366740
2433: AND p_x_req_material_tbl(i).workorder_operation_id = l_material_rec.workorder_operation_id )
2434: THEN
2435: -- UPDATE ahl schedule materials table with operation id, operation sequence
2436: UPDATE ahl_schedule_materials
2437: SET workorder_operation_id = p_x_req_material_tbl(i).workorder_operation_id,
2438: operation_code = p_x_req_material_tbl(i).operation_code,
2439: operation_sequence = p_x_req_material_tbl(i).operation_sequence,
2432: --Adithya added for FP Bug# 6366740
2433: AND p_x_req_material_tbl(i).workorder_operation_id = l_material_rec.workorder_operation_id )
2434: THEN
2435: -- UPDATE ahl schedule materials table with operation id, operation sequence
2436: UPDATE ahl_schedule_materials
2437: SET workorder_operation_id = p_x_req_material_tbl(i).workorder_operation_id,
2438: operation_code = p_x_req_material_tbl(i).operation_code,
2439: operation_sequence = p_x_req_material_tbl(i).operation_sequence,
2440: object_version_number =l_material_rec.object_version_number +1
2918: A.position_key,
2919: --sukhwsin: SB Effectivity code changes - ends
2920: --debadey; Marshalling FDD section 5.15
2921: A.aog_flag
2922: FROM AHL_SCHEDULE_MATERIALS A,
2923: AHL_JOB_OPER_MATERIALS_V B, WIP_OPERATIONS WO
2924: WHERE A.SCHEDULED_MATERIAL_ID = B.SCHEDULED_MATERIAL_ID
2925: AND B.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
2926: AND B.OPERATION_SEQUENCE = WO.OPERATION_SEQ_NUM
3611: END IF;
3612: -- end of changes by debadey for Marshalling
3613: --
3614: --Update schedule material table
3615: UPDATE AHL_SCHEDULE_MATERIALS
3616: SET inventory_item_id = l_req_material_rec.inventory_item_id,
3617: -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
3618: requested_date = trunc(l_req_material_rec.requested_date),
3619: requested_quantity = l_req_material_rec.requested_quantity,
3829: B.rt_oper_material_id,
3830: B.department_id,
3831: B.workorder_name,
3832: B.wip_entity_id
3833: FROM AHL_SCHEDULE_MATERIALS A,
3834: AHL_JOB_OPER_MATERIALS_V B
3835: WHERE A.SCHEDULED_MATERIAL_ID = B.SCHEDULED_MATERIAL_ID
3836: AND A.SCHEDULED_MATERIAL_ID = c_schedule_material_id;
3837: */
3860: --debadey: Marshalling FDD section 5.15, AOG flag updation
3861: ASML.aog_flag
3862: FROM
3863: AHL_WORKORDERS AWOS,
3864: AHL_SCHEDULE_MATERIALS ASML,
3865: wip_requirement_operations wipr,
3866: MTL_SYSTEM_ITEMS_VL MSIV,
3867: AHL_VISITS_VL AVST,
3868: AHL_WORKORDER_OPERATIONS AWOP,
4010: p_x_req_material_tbl(i).object_version_number <> FND_API.G_MISS_NUM) THEN
4011: --
4012: SELECT object_version_number,requested_quantity INTO l_object_version_number,
4013: p_x_req_material_tbl(i).requested_quantity
4014: FROM ahl_schedule_materials
4015: WHERE scheduled_material_id = p_x_req_material_tbl(i).schedule_material_id
4016: FOR UPDATE OF STATUS NOWAIT;
4017: --
4018: IF p_x_req_material_tbl(i).object_version_number <> l_object_version_number THEN
4128: LOOP
4129: -- If schedule date is not null then update to zero because collection has been done
4130: IF p_x_req_material_tbl(i).schedule_material_id IS NOT NULL THEN
4131: -- Update schedule materials table requested quantity to zero
4132: UPDATE AHL_SCHEDULE_MATERIALS
4133: SET requested_quantity = 0,
4134: status = 'DELETED',
4135: object_version_number = p_x_req_material_tbl(i).object_version_number + 1
4136: WHERE SCHEDULED_MATERIAL_ID = p_x_req_material_tbl(i).schedule_material_id;
4267: asm.requested_quantity old_requested_quantity,
4268: asm.workorder_operation_id,
4269: NVL(asm.completed_quantity, 0) completed_quantity,
4270: (SELECT NVL(SUM(mrv.primary_reservation_quantity), 0)
4271: FROM mtl_reservations mrv, ahl_schedule_materials asmt
4272: WHERE mrv.inventory_item_id = asmt.inventory_item_id
4273: AND mrv.external_source_code = 'AHL'
4274: AND mrv.requirement_date = asmt.requested_date
4275: AND mrv.organization_id = asmt.organization_id
4275: AND mrv.organization_id = asmt.organization_id
4276: AND mrv.demand_source_line_detail = c_schedule_material_id) reserved_quantity,
4277: wo.workorder_id,
4278: vst.organization_id
4279: FROM ahl_schedule_materials asm, ahl_workorders wo, ahl_visits_b vst
4280: WHERE asm.visit_task_id = wo.visit_task_id
4281: AND vst.visit_id = asm.visit_id
4282: AND scheduled_material_id = c_schedule_material_id;
4283:
4310: wip.operation_seq_num operation_sequence,
4311: wip.organization_id,
4312: wip.department_id
4313: FROM wip_requirement_operations wip,
4314: ahl_schedule_materials asm,
4315: ahl_workorders wo
4316: WHERE wip.wip_entity_id = wo.wip_entity_id
4317: AND wo.visit_task_id = asm.visit_task_id
4318: AND asm.operation_sequence = wip.operation_seq_num
4318: AND asm.operation_sequence = wip.operation_seq_num
4319: AND asm.inventory_item_id = wip.inventory_item_id
4320: AND asm.organization_id = wip.organization_id
4321: AND (asm.visit_task_id, asm.operation_sequence) IN (SELECT ASM2.visit_task_id, ASM2.operation_sequence
4322: FROM ahl_schedule_materials ASM2
4323: WHERE ASM2.scheduled_material_id = c_schedule_material_id)
4324: AND wip.inventory_item_id = c_inventory_item_id;
4325:
4326: CURSOR Get_Item_Name_Cur(c_inventory_item_id IN NUMBER,
4731: Need to logic to conditionally update ASM columns based upon the call. i.e. if p_module_type = MM then
4732: sheduling information also needs to be updated for changed item in ASM. This logic will be handled later.
4733: */
4734:
4735: UPDATE ahl_schedule_materials
4736: SET inventory_item_id = p_x_req_material_tbl(i).inventory_item_id,
4737: object_version_number = p_x_req_material_tbl(i).object_version_number + 1,
4738: last_update_date = sysdate,
4739: last_updated_by = Fnd_Global.USER_ID,
4866: l_count NUMBER;
4867:
4868: CURSOR AOG_CHECK_CUR(p_asm_id NUMBER)
4869: IS
4870: SELECT 1 FROM AHL_SCHEDULE_MATERIALS_V
4871: WHERE schedule_material_id = p_asm_id
4872: AND NVL(aog_flag, 'N') = 'N';
4873: BEGIN
4874: --------------------Initialize ----------------------------------
5178: CURSOR Check_material_cur (c_schedule_material_id IN NUMBER)
5179: IS
5180: SELECT scheduled_material_id,
5181: rt_oper_material_id
5182: FROM ahl_schedule_materials
5183: WHERE scheduled_material_id = c_schedule_material_id;
5184: --
5185: CURSOR Get_item_cur(c_segments IN VARCHAR2)
5186: IS
5223: CURSOR Check_material_cur (c_schedule_material_id IN NUMBER)
5224: IS
5225: SELECT scheduled_material_id,
5226: rt_oper_material_id,requested_quantity
5227: FROM ahl_schedule_materials
5228: WHERE scheduled_material_id = c_schedule_material_id;
5229: --
5230: CURSOR Get_item_cur(c_segments IN VARCHAR2)
5231: IS
5642: ASM.attribute12,
5643: ASM.attribute13,
5644: ASM.attribute14,
5645: ASM.attribute15
5646: FROM AHL_SCHEDULE_MATERIALS ASM,
5647: AHL_RT_OPER_MATERIALS ARM
5648: WHERE ASM.rt_oper_material_id = ARM.RT_OPER_MATERIAL_ID(+)
5649: AND ASM.visit_task_id = C_VISIT_TASK_ID
5650: AND ASM.requested_quantity > 0
5662: CURSOR Material_Detail_Cur (c_operation_id IN NUMBER,
5663: c_operation_sequence IN NUMBER)
5664: IS
5665: SELECT Scheduled_material_id
5666: FROM AHL_SCHEDULE_MATERIALS
5667: WHERE WORKORDER_OPERATION_ID = c_operation_id
5668: AND OPERATION_SEQUENCE = c_operation_sequence;
5669: l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_WO_OP_MATERIALS';
5670: l_api_version CONSTANT NUMBER := 1.0;
5800: -- fix for bug# 5549135.
5801: --l_req_material_tbl(l_idx).mrp_net_flag := 1;
5802: l_req_material_tbl(l_idx).mrp_net_flag := 2;
5803: -- Update with workorder operation details
5804: UPDATE ahl_schedule_materials
5805: SET workorder_operation_id = l_prd_wooperation_tbl(i).workorder_operation_id,
5806: operation_sequence = l_prd_wooperation_tbl(i).operation_sequence_num,
5807: -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
5808: requested_date = trunc(l_prd_wooperation_tbl(i).scheduled_start_date),
5883: l_req_material_tbl(l_idx).attribute13 := l_Sche_Mat_Rec.attribute13;
5884: l_req_material_tbl(l_idx).attribute14 := l_Sche_Mat_Rec.attribute14;
5885: l_req_material_tbl(l_idx).attribute15 := l_Sche_Mat_Rec.attribute15;
5886: --Update with operation details
5887: UPDATE ahl_schedule_materials
5888: SET workorder_operation_id = l_prd_wooperation_tbl(i).workorder_operation_id,
5889: object_version_number = object_version_number + 1,
5890: -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
5891: requested_date = trunc(l_prd_wooperation_tbl(i).scheduled_start_date),
5970: FETCH Material_Detail_Cur INTO l_scheduled_material_id;
5971: EXIT WHEN Material_Detail_Cur%NOTFOUND;
5972: IF l_scheduled_material_id IS NOT NULL THEN
5973: --
5974: UPDATE ahl_schedule_materials
5975: -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
5976: SET requested_date = trunc(l_prd_wooperation_tbl(i).actual_start_date),
5977: object_version_number = object_version_number + 1,
5978: last_update_date = sysdate,
6440: -- x_msg_count OUT NUMBER Required
6441: -- x_msg_data OUT VARCHAR2 Required
6442: --
6443: -- Process_Material Notification Parameters :
6444: -- p_asm_id IN ahl_schedule_materials.schedule_material_id
6445: -- p_alternate_item_id IN New Inventory_Item_Id which will replace old inventory item.
6446: PROCEDURE Wo_Mat_Req_Item_Change(
6447: p_api_version IN NUMBER,
6448: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
6472: ,asm.position_key
6473: ,asm.item_group_id
6474: ,wo.workorder_id
6475: ,wo.status_code
6476: FROM ahl_schedule_materials asm, ahl_workorders wo
6477: WHERE
6478: asm.scheduled_material_id = c_asm_id
6479: AND asm.status = 'ACTIVE'
6480: AND asm.visit_task_id = wo.visit_task_id;