[Home] [Help]
1182: SELECT ASM.SCHEDULED_MATERIAL_ID,
1183: ASM.ATP_FLAG,
1184: WO.WORKORDER_NAME
1185: FROM AHL_SCHEDULE_MATERIALS ASM,
1186: AHL_WORKORDERS WO,
1187: AHL_VISIT_TASKS_B TSK
1188: WHERE (ASM.REQUESTED_QUANTITY - NVL(ASM.COMPLETED_QUANTITY,0) ) > 0
1189: AND ASM.STATUS = 'ACTIVE'
1190: AND ASM.VISIT_TASK_ID = WO.VISIT_TASK_ID
1472: l_process_name VARCHAR2(30);
1473: l_item_type VARCHAR2(8);
1474: l_subject FND_NEW_MESSAGES.message_text%TYPE;
1475: l_item_key VARCHAR2(200);
1476: TYPE workorder_dtl_record IS RECORD(workorder_name AHL_WORKORDERS.WORKORDER_NAME%TYPE, asm_ids VARCHAR2(2000));
1477: TYPE workorder_dtl_table IS TABLE OF workorder_dtl_record INDEX BY BINARY_INTEGER;
1478: l_wo_dtl_tab workorder_dtl_table;
1479: l_wo_tab_cnt NUMBER :=0;
1480: TYPE cur_type IS REF CURSOR;
1480: TYPE cur_type IS REF CURSOR;
1481: c_wo_dtl cur_type;
1482: l_sql_string VARCHAR2(2000);
1483: l_asm_id NUMBER;
1484: l_workorder_name AHL_WORKORDERS.WORKORDER_NAME%TYPE;
1485:
1486: BEGIN
1487: --------------------Initialize ----------------------------------
1488: -- Standard Start of API savepoint
1556: --End of Get Workflow attributes.
1557:
1558: --Build asm id string from table of records recieved as parameter.
1559: l_sql_string := 'SELECT wo.workorder_name, asm.scheduled_material_id ' ||
1560: 'FROM ahl_schedule_materials asm, ahl_workorders wo ' ||
1561: 'WHERE ' ||
1562: 'asm.visit_task_id = wo.visit_task_id ' ||
1563: 'AND asm.scheduled_material_id IN ( 0';
1564: FOR i IN p_asm_id_tab.first..p_asm_id_tab.last
3744: )
3745: IS
3746:
3747: L_CUR AHL_OSP_UTIL_PKG.AHL_SEARCH_CSR;
3748: L_SQL_STRING VARCHAR2(30000) := 'SELECT distinct AWO.WORKORDER_ID,AWO.OBJECT_VERSION_NUMBER FROM AHL_WORKORDERS AWO,
3749: AHL_SCHEDULE_MATERIALS ASM WHERE AWO.VISIT_TASK_ID = ASM.VISIT_TASK_ID
3750: AND AWO.VISIT_TASK_ID = ASM.VISIT_TASK_ID AND ASM.SCHEDULED_MATERIAL_ID in(';
3751: L_BINDVAR_TBL AHL_OSP_UTIL_PKG.AHL_CONDITIONS_TBL;
3752: P_WORK_ORDER_ID_TBL AHL_PRD_WORKORDER_PVT.WORKORDER_ID_TBL_TYPE;
5917: || ' FROM MTL_RESERVATIONS MR,'
5918: || ' MTL_SERIAL_NUMBERS MSN,'
5919: || ' (SELECT CSIII.serial_number,'
5920: || ' WO.WIP_ENTITY_ID'
5921: || ' FROM AHL_WORKORDERS WO,'
5922: || ' AHL_VISIT_TASKS_B ATSK,'
5923: || ' AHL_VISITS_B AVST,'
5924: || ' CSI_ITEM_INSTANCES CSIII'
5925: || ' WHERE WO.visit_task_id = ATSK.visit_task_id'
5980: END IF;
5981:
5982: --Base criteria for Work Order Supply
5983: l_base_query := l_base_query || ' AND EXISTS (SELECT 1'
5984: || ' FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST'
5985: || ' WHERE WO.visit_task_id = TSK.visit_task_id'
5986: || ' AND WO.STATUS_CODE NOT IN (7, 22, 17, 12, 18, 21, 4, 5)'
5987: || ' AND TSK.return_to_supply_flag = ''Y'' '
5988: || ' AND TSK.visit_id = VST.visit_id'