[Home] [Help]
190: WHERE UNIT_OF_MEASURE = p_Uom_Desc;
191: -- Cursor for Wip job id.
192: CURSOR WIP_JOB_ID_CUR(p_wo_id NUMBER) IS
193: SELECT A.WIP_ENTITY_ID, C.ORGANIZATION_ID
194: FROM AHL_WORKORDERS A, AHL_VISIT_TASKS_B B, AHL_VISITS_B C
195: WHERE A.WORKORDER_ID = p_wo_id
196: AND B.VISIT_TASK_ID = A.VISIT_TASK_ID
197: AND C.VISIT_ID = B.VISIT_ID;
198: -- Cursor for Work order operation id.
253: A.JOB_STATUS_MEANING,
254: A.WIP_ENTITY_ID
255: FROM
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
2018: -- Added project and locator for ER# 5854712.
2019: CURSOR AHL_WIPJOB_CUR (p_org_id NUMBER, p_wo_id NUMBER, p_wipjob IN NUMBER) IS
2020: SELECT A.STATUS_CODE, C.Visit_id, C.Inv_Locator_Id, C.project_id, B.project_task_id,
2021: LOC.subinventory_code
2022: FROM AHL_WORKORDERS A, AHL_VISIT_TASKS_B B, AHL_VISITS_B C, MTL_ITEM_LOCATIONS LOC
2023: WHERE A.WIP_ENTITY_ID = p_wipjob
2024: AND A.WORKORDER_ID = p_wo_id
2025: AND B.VISIT_TASK_ID = A.VISIT_TASK_ID
2026: AND C.VISIT_ID = B.VISIT_ID
3872:
3873: -- QWuey to select the work order dependent data to be passed to Schedule materials API.
3874: CURSOR Workop_Det_Cur(p_wo_id NUMBER) IS
3875: SELECT B.VISIT_ID,C.VISIT_TASK_ID,B.PROJECT_ID,C.PROJECT_TASK_ID
3876: FROM AHL_WORKORDERS A, AHL_VISITS_B B, AHL_VISIT_TASKS_B C
3877: WHERE A.WORKORDER_ID = p_wo_id
3878: AND A.VISIT_TASK_ID = C.VISIT_TASK_ID
3879: AND C.VISIT_ID = B.VISIT_ID;
3880: -- Bug # 9373684 (FP for Bug # 9248655) -- start
5024: -- JKJAIN FP ER # 6436303 - start
5025: AHL_PRD_MTLTXN_PVT.GET_WORKORD_NET_QTY(D.WORKORDER_ID,D.INVENTORY_ITEM_ID,V.ORGANIZATION_ID) Wo_Net_Total_Qty,
5026: -- JKJAIN FP ER # 6436303 - end
5027: W.wip_entity_id,
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,
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,
5037: (SELECT ORGANIZATION_ID, NAME FROM HR_ALL_ORGANIZATION_UNITS_TL WHERE LANGUAGE = USERENV('LANG')) ORG,
5125:
5126: TYPE l_disp_rec_type IS RECORD (
5127: workorder_id AHL_PRD_DISPOSITIONS_B.WORKORDER_ID%TYPE,
5128: workorder_name AHL_WORKORDERS.WORKORDER_NAME%TYPE,
5129: organization_id AHL_VISITS_B.ORGANIZATION_ID%TYPE,
5130: workorder_operation_id AHL_PRD_DISPOSITIONS_B.WO_OPERATION_ID%TYPE,
5131: operation_sequence_num AHL_WORKORDER_OPERATIONS.OPERATION_SEQUENCE_NUM%TYPE,
5132: item_number MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE,
5133: inventory_item_id CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE,
5152: quantity AHL_PRD_DISPOSITIONS_B.QUANTITY%TYPE,
5153: net_quantity CSI_ITEM_INSTANCES.QUANTITY%TYPE,
5154: Wo_Net_Total_Qty NUMBER,
5155: wip_entity_id AHL_WORKORDERS.WIP_ENTITY_ID%TYPE,
5156: inv_locator_id AHL_VISITS_B.INV_LOCATOR_ID%TYPE,
5157: --JKJain, Bug 9268076
5158: path_position_id AHL_PRD_DISPOSITIONS_B.PATH_POSITION_ID%TYPE
5159: );
5160:
5271: V.INV_LOCATOR_ID,
5272: D.PATH_POSITION_ID
5273: FROM
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,
5911: T.LOT_NUMBER,
5912: T.REVISION,
5913: T.INSTANCE_ID, -- added to fix FP bug# 5172147.
5914: W.WIP_ENTITY_ID, -- added to filter chk_inst_relationship_csr for wip_job_id.
5915: (select inv_locator_id from ahl_visits_b where visit_id = w.visit_id) inv_locator_id
5916: FROM
5917: AHL_WORKORDER_MTL_TXNS T,
5918: MTL_SYSTEM_ITEMS_KFV I,
5919: AHL_WORKORDERS W,
5916: FROM
5917: AHL_WORKORDER_MTL_TXNS T,
5918: MTL_SYSTEM_ITEMS_KFV I,
5919: AHL_WORKORDERS W,
5920: AHL_VISITS_B V,
5921: AHL_VISIT_TASKS_B VT,
5922: CS_INCIDENTS_ALL_B C,
5923: WIP_DISCRETE_JOBS WDJ,
5924: INV_ORGANIZATION_NAME_V ORG,
6020: inv_project.GET_LOCSEGS(WP.DEFAULT_PULL_SUPPLY_LOCATOR_ID, WP.organization_id) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
6021: || INV_ProjectLocator_PUB.get_project_number(MTL_LOC.segment19) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
6022: || INV_ProjectLocator_PUB.get_task_number(MTL_LOC.segment20) LOCATOR,
6023: --SYSDATE
6024: (select inv_locator_id from ahl_visits_b where visit_id = vst.visit_id) inv_locator_id,
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,
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,
6034: MTL_UNITS_OF_MEASURE_VL UOM,
6035: -- modified to retrieve segment19 and 20 from base table to fix bug# 6611033.
6102: inv_project.GET_LOCSEGS(W.DEFAULT_PULL_SUPPLY_LOCATOR_ID, W.organization_id) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
6103: || INV_ProjectLocator_PUB.get_project_number(D.segment19) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
6104: || INV_ProjectLocator_PUB.get_task_number(D.segment20) LOCATOR,
6105: --SYSDATE
6106: (select inv_locator_id from ahl_visits_b where visit_id = E.visit_id) inv_locator_id,
6107: (Select csi.instance_number from csi_item_instances csi where csi.instance_id = A.INSTANCE_ID) instance_number
6108:
6109:
6110: FROM
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
6123: WHERE
6187: lot_number AHL_WORKORDER_MTL_TXNS.LOT_NUMBER%TYPE,
6188: revision AHL_WORKORDER_MTL_TXNS.REVISION%TYPE,
6189: instance_id AHL_WORKORDER_MTL_TXNS.INSTANCE_ID%TYPE,
6190: wip_entity_id AHL_WORKORDERS.WIP_ENTITY_ID%TYPE,
6191: inv_locator_id AHL_VISITS_B.INV_LOCATOR_ID%TYPE
6192: );
6193: l_mtlTxn_rec l_mtlTxn_rec_type;
6194:
6195: BEGIN
6217: FROM
6218: AHL_WORKORDER_MTL_TXNS T,
6219: AHL_WORKORDERS W,
6220: AHL_WORKORDER_OPERATIONS O,
6221: AHL_VISITS_B V ' ;
6222:
6223: l_mtl_txn_dtls_where := '
6224: WHERE T.TRANSACTION_TYPE_ID = 35
6225: AND W.STATUS_CODE IN (''3'', ''4'', ''20'')
6290: AND C.INCIDENT_NUMBER LIKE :'||l_bind_index;
6291: l_bind_value_tbl(l_bind_index) := P_prd_Mtltxn_criteria_rec.INCIDENT_NUMBER;
6292: l_bind_index := l_bind_index + 1;
6293: ELSE
6294: l_mtl_txn_dtls := l_mtl_txn_dtls || ', AHL_VISITS_B V,AHL_VISIT_TASKS_B VT,CS_INCIDENTS_ALL_B C';
6295: l_mtl_txn_dtls_where := l_mtl_txn_dtls_where || ' AND W.VISIT_TASK_ID = VT.VISIT_TASK_ID
6296: AND VT.VISIT_ID = V.VISIT_ID
6297: AND VT.SERVICE_REQUEST_ID = C.INCIDENT_ID(+)
6298: AND C.INCIDENT_NUMBER LIKE :'||l_bind_index;
6586: ||
6587: DECODE(D.segment19,NULL,NULL,inv_project.GET_PROJECT_NUMBER(D.segment19)) || '.'
6588: ||
6589: DECODE(D.segment20,NULL,NULL,inv_project.GET_TASK_NUMBER(D.segment20)) Locator,
6590: (select inv_locator_id from ahl_visits_b where visit_id = w.visit_id) inv_locator_id
6591: FROM
6592: CSI_ITEM_INSTANCES CSI,
6593: MTL_SYSTEM_ITEMS_KFV I,
6594: AHL_SEARCH_WORKORDERS_v W,
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,
6695: serial_number CSI_ITEM_INSTANCES.SERIAL_NUMBER%TYPE,
6696: instance_number CSI_ITEM_INSTANCES.INSTANCE_NUMBER%TYPE,
6704: default_pull_supply_subinv WIP_PARAMETERS.DEFAULT_PULL_SUPPLY_SUBINV%TYPE,
6705: default_pull_supply_locator_id WIP_PARAMETERS.DEFAULT_PULL_SUPPLY_LOCATOR_ID%TYPE,
6706: locator VARCHAR2(500),
6707:
6708: inv_locator_id AHL_VISITS_B.INV_LOCATOR_ID%TYPE,
6709: SERIAL_NUM_STATUS mtl_serial_numbers.current_status%TYPE );
6710:
6711: l_mtl_txn_rec l_mtl_txn_rec_type;
6712:
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,
6758: WIP_PARAMETERS P,
6759: MTL_ITEM_LOCATIONS D ';