[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.
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
262: A.JOB_NUMBER = C_WORKORDER_NAME
890: WDJ.REBUILD_ITEM_ID,
891: wdj.MAINTENANCE_OBJECT_ID
892: FROM AHL_WORKORDERS WO,
893: WIP_DISCRETE_JOBS WDJ,
894: AHL_VISIT_TASKS_B VTS,
895: AHL_WORKORDERS WO1
896: WHERE WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
897: AND WO.STATUS_CODE NOT IN ('7','12','17','22' ) -- Cancelled,Closed,Draft and Deleted
898: AND WO.VISIT_ID = WO1.VISIT_ID
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
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,
5038: BOM_DEPARTMENTS B,
5345: l_bind_index := l_bind_index + 1;
5346: END IF;
5347:
5348: IF (P_prd_Mtltxn_criteria_rec.INCIDENT_NUMBER IS NOT NULL) THEN
5349: l_mtl_txn_dtls := l_mtl_txn_dtls || ', AHL_VISIT_TASKS_B VT,CS_INCIDENTS_ALL_B C';
5350: l_mtl_txn_dtls_where := l_mtl_txn_dtls_where || ' AND W.VISIT_TASK_ID = VT.VISIT_TASK_ID
5351: AND V.VISIT_ID = VT.VISIT_ID
5352: AND VT.SERVICE_REQUEST_ID = C.INCIDENT_ID(+)
5353: AND C.INCIDENT_NUMBER LIKE :' || l_bind_index;
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,
5925: BOM_DEPARTMENTS B,
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,
6034: MTL_UNITS_OF_MEASURE_VL UOM,
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
6124: A.INVENTORY_ITEM_ID=B.INVENTORY_ITEM_ID
6284: END IF;
6285:
6286: IF (P_prd_Mtltxn_criteria_rec.INCIDENT_NUMBER IS NOT NULL) THEN
6287: IF (P_prd_Mtltxn_criteria_rec.VISIT_NUMBER IS NOT NULL) THEN
6288: l_mtl_txn_dtls := l_mtl_txn_dtls || ',AHL_VISIT_TASKS_B VT,CS_INCIDENTS_ALL_B C';
6289: l_mtl_txn_dtls_where := l_mtl_txn_dtls_where || ' AND W.VISIT_TASK_ID = VT.VISIT_TASK_ID AND VT.SERVICE_REQUEST_ID = C.INCIDENT_ID(+)
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;
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;
6843: l_bind_index := l_bind_index + 1;
6844: END IF;
6845:
6846: IF (P_PRD_MTLTXN_CRITERIA_REC.INCIDENT_NUMBER IS NOT NULL) THEN
6847: L_MTL_TXN_DTLS := L_MTL_TXN_DTLS || ', AHL_VISIT_TASKS_B VTS,CS_INCIDENTS_ALL_B CSIN';
6848: L_MTL_TXN_DTLS_WHERE := L_MTL_TXN_DTLS_WHERE || ' AND W.VISIT_TASK_ID = VTS.VISIT_TASK_ID
6849: AND VTS.VISIT_ID = V.VISIT_ID
6850: AND VTS.SERVICE_REQUEST_ID = CSIN.INCIDENT_ID(+)
6851: AND CSIN.INCIDENT_NUMBER LIKE :' || l_bind_index;
7669: CURSOR Chk_Rts_Workorder(C_WORKORDER_ID NUMBER, c_ins_id NUMBER)
7670: IS
7671: SELECT 'x'
7672: FROM AHL_WORKORDERS WO,
7673: AHL_VISIT_TASKS_B VTS,
7674: AHL_WORKORDERS WO1
7675: WHERE WO.STATUS_CODE NOT IN ('7','12','17','22' ) -- Cancelled,Closed,Draft and Deleted
7676: AND WO.VISIT_ID = WO1.VISIT_ID
7677: AND wo1.workorder_id = C_WORKORDER_ID