[Home] [Help]
176:
177: -- Query for Selcting Problem_Code
178: CURSOR Fnd_Lookups_Cur(p_Lookup_Meaning varchar2) IS
179: SELECT LOOKUP_CODE
180: FROM FND_LOOKUP_VALUES_VL
181: WHERE MEANING = p_Lookup_Meaning
182: AND LOOKUP_TYPE = C_REQUEST_PROBLEM_CODE
183: AND ENABLED_FLAG = 'Y'
184: AND ((START_DATE_ACTIVE IS NULL) OR (START_DATE_ACTIVE <= SYSDATE))
256: AHL_SEARCH_WORKORDERS_V A
257: --AHL_VISITS_B V,
258: --AHL_VISIT_TASKS_B VT,
259: --INV_ORGANIZATION_NAME_V ORG,
260: --FND_LOOKUP_VALUES WO_STS
261: WHERE
262: A.JOB_NUMBER = C_WORKORDER_NAME
263: AND A.JOB_STATUS_CODE NOT IN ('17', '22');
264: --AND A.MASTER_WORKORDER_FLAG = 'N'
2080: * R12 Drop 4 - SQL ID: 14399922
2081: * Bug #4918991
2082: */
2083: SELECT 1
2084: FROM FND_LOOKUP_VALUES FL
2085: WHERE
2086: FL.LOOKUP_TYPE = 'REQUEST_PROBLEM_CODE' AND
2087: FL.LOOKUP_CODE = p_problem_code AND
2088: FL.ENABLED_FLAG = 'Y' AND
2183: -- Added for FP bug# 5903318.
2184: -- validate create_wo_option.
2185: CURSOR create_wo_cur(p_lookup_code IN VARCHAR2, p_lookup_type IN VARCHAR2) IS
2186: SELECT meaning
2187: FROM FND_LOOKUP_VALUES_VL
2188: WHERE lookup_type = p_lookup_type
2189: AND lookup_code = p_lookup_code
2190: AND ENABLED_FLAG = 'Y'
2191: AND ((START_DATE_ACTIVE IS NULL) OR (START_DATE_ACTIVE <= SYSDATE))
2217: l_junk VARCHAR2(1);
2218: l_disposition_id NUMBER;
2219:
2220: -- Added for FP bug# 5903318.
2221: l_fnd_meaning fnd_lookup_values_vl.meaning%TYPE;
2222:
2223: -- Added for ER# 5854712.
2224: l_inv_locator_id NUMBER;
2225: l_project_id NUMBER;
5028: (select inv_locator_id from ahl_visits_b where visit_id = w.visit_id) inv_locator_id
5029: FROM
5030: AHL_MTL_RET_DISPOSITIONS_V D,
5031: AHL_WORKORDERS W,
5032: (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_JOB_STATUS' AND LANGUAGE= USERENV('LANG')) WO_STS,
5033: AHL_VISITS_B V,
5034: AHL_VISIT_TASKS_B VT,
5035: CS_INCIDENTS_ALL_B C,
5036: WIP_DISCRETE_JOBS WDJ,
5133: inventory_item_id CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE,
5134: item_desc MTL_SYSTEM_ITEMS_KFV.DESCRIPTION%TYPE,
5135: immediate_disposition_code AHL_PRD_DISPOSITIONS_B.IMMEDIATE_DISPOSITION_CODE%TYPE,
5136: disposition_id AHL_PRD_DISPOSITIONS_B.DISPOSITION_ID%TYPE,
5137: immediate_type FND_LOOKUP_VALUES.MEANING%TYPE,
5138: condition_code MTL_MATERIAL_STATUSES_TL.STATUS_CODE%TYPE,
5139: condition_id AHL_PRD_DISPOSITIONS_B.CONDITION_ID%TYPE,
5140: serial_number CSI_ITEM_INSTANCES.SERIAL_NUMBER%TYPE,
5141: uom AHL_PRD_DISPOSITIONS_B.UOM%TYPE,
5139: condition_id AHL_PRD_DISPOSITIONS_B.CONDITION_ID%TYPE,
5140: serial_number CSI_ITEM_INSTANCES.SERIAL_NUMBER%TYPE,
5141: uom AHL_PRD_DISPOSITIONS_B.UOM%TYPE,
5142: unit_of_measure MTL_UNITS_OF_MEASURE_VL.UNIT_OF_MEASURE%TYPE,
5143: job_status_meaning FND_LOOKUP_VALUES.MEANING%TYPE,
5144: lot_number CSI_ITEM_INSTANCES.LOT_NUMBER%TYPE,
5145: item_revision CSI_ITEM_INSTANCES.INVENTORY_REVISION%TYPE,
5146: collection_id AHL_PRD_DISPOSITIONS_B.COLLECTION_ID%TYPE,
5147: instance_id AHL_PRD_DISPOSITIONS_B.INSTANCE_ID%TYPE,
5274: AHL_MTL_RET_DISPOSITIONS_V D,
5275: AHL_VISITS_B V,
5276: AHL_WORKORDER_OPERATIONS O,
5277: MTL_UNITS_OF_MEASURE_VL UOM,
5278: (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = ''AHL_JOB_STATUS'' AND LANGUAGE= USERENV(''LANG'')) WO_STS,
5279: WIP_PARAMETERS WP,
5280: MTL_ITEM_LOCATIONS_KFV L,
5281: AHL_WORKORDERS W ';
5282:
6025: (Select csi.instance_number from csi_item_instances csi where csi.instance_id = TXNS.INSTANCE_ID) instance_number
6026: FROM
6027: AHL_WORKORDER_MTL_TXNS TXNS,
6028: AHL_WORKORDERS WO,
6029: (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_JOB_STATUS' AND LANGUAGE= USERENV('LANG')) WO_STS,
6030: AHL_VISIT_TASKS_B VST_TASK,
6031: AHL_VISITS_B VST,
6032: AHL_WORKORDER_OPERATIONS WO_OP,
6033: MTL_SYSTEM_ITEMS_KFV MTL,
6114: -- modified to retrieve segment19 and 20 from base table to fix bug# 6611033.
6115: --MTL_ITEM_LOCATIONS_KFV D,
6116: MTL_ITEM_LOCATIONS D,
6117: AHL_WORKORDERS E,
6118: (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_JOB_STATUS' AND LANGUAGE= USERENV('LANG')) WO_STS,
6119: AHL_VISITS_B V,
6120: AHL_VISIT_TASKS_B VT,
6121: AHL_WORKORDER_OPERATIONS F,
6122: WIP_PARAMETERS W
6686:
6687: TYPE l_mtl_txn_rec_type IS RECORD (
6688: workorder_id AHL_WORKORDERS.WORKORDER_ID%TYPE,
6689: job_number AHL_WORKORDERS.WORKORDER_NAME%TYPE,
6690: job_status_meaning FND_LOOKUP_VALUES_VL.MEANING%TYPE,
6691: description MTL_SYSTEM_ITEMS_KFV.DESCRIPTION%TYPE,
6692: organization_id AHL_VISITS_B.ORGANIZATION_ID%TYPE,
6693: inventory_item_id CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE,
6694: concatenated_segments MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE,
6749:
6750: V.INV_LOCATOR_ID,
6751: (select msn.current_status from mtl_serial_numbers msn where msn.inventory_item_id = C.INVENTORY_ITEM_ID and msn.serial_number = C.serial_number) serial_num_status
6752: FROM AHL_WORKORDERS W,
6753: FND_LOOKUP_VALUES_VL MLU,
6754: MTL_SYSTEM_ITEMS_KFV I,
6755: AHL_VISITS_B V,
6756: CSI_ITEM_INSTANCES C,
6757: MTL_UNITS_OF_MEASURE_VL UOM,