[Home] [Help]
179: WHERE UNIT_OF_MEASURE = p_Uom_Desc;
180: -- Cursor for Wip job id.
181: CURSOR WIP_JOB_ID_CUR(p_wo_id NUMBER) IS
182: SELECT A.WIP_ENTITY_ID, C.ORGANIZATION_ID
183: FROM AHL_WORKORDERS A, AHL_VISIT_TASKS_B B, AHL_VISITS_B C
184: WHERE A.WORKORDER_ID = p_wo_id
185: AND B.VISIT_TASK_ID = A.VISIT_TASK_ID
186: AND C.VISIT_ID = B.VISIT_ID;
187: -- Cursor for Work order operation id.
242: A.JOB_STATUS_MEANING,
243: A.WIP_ENTITY_ID
244: FROM
245: AHL_SEARCH_WORKORDERS_V A
246: --AHL_VISITS_B V,
247: --AHL_VISIT_TASKS_B VT,
248: --INV_ORGANIZATION_NAME_V ORG,
249: --FND_LOOKUP_VALUES WO_STS
250: WHERE
1729: -- Added project and locator for ER# 5854712.
1730: CURSOR AHL_WIPJOB_CUR (p_org_id NUMBER, p_wo_id NUMBER, p_wipjob IN NUMBER) IS
1731: SELECT A.STATUS_CODE, C.Visit_id, C.Inv_Locator_Id, C.project_id, B.project_task_id,
1732: LOC.subinventory_code
1733: FROM AHL_WORKORDERS A, AHL_VISIT_TASKS_B B, AHL_VISITS_B C, MTL_ITEM_LOCATIONS LOC
1734: WHERE A.WIP_ENTITY_ID = p_wipjob
1735: AND A.WORKORDER_ID = p_wo_id
1736: AND B.VISIT_TASK_ID = A.VISIT_TASK_ID
1737: AND C.VISIT_ID = B.VISIT_ID
3305:
3306: -- QWuey to select the work order dependent data to be passed to Schedule materials API.
3307: CURSOR Workop_Det_Cur(p_wo_id NUMBER) IS
3308: SELECT B.VISIT_ID,C.VISIT_TASK_ID,B.PROJECT_ID,C.PROJECT_TASK_ID
3309: FROM AHL_WORKORDERS A, AHL_VISITS_B B, AHL_VISIT_TASKS_B C
3310: WHERE A.WORKORDER_ID = p_wo_id
3311: AND A.VISIT_TASK_ID = C.VISIT_TASK_ID
3312: AND C.VISIT_ID = B.VISIT_ID;
3313: --Query to select the item desccription
4305: -- JKJAIN FP ER # 6436303 - start
4306: AHL_PRD_MTLTXN_PVT.GET_WORKORD_NET_QTY(D.WORKORDER_ID,D.INVENTORY_ITEM_ID,V.ORGANIZATION_ID) Wo_Net_Total_Qty,
4307: -- JKJAIN FP ER # 6436303 - end
4308: W.wip_entity_id,
4309: (select inv_locator_id from ahl_visits_b where visit_id = w.visit_id) inv_locator_id
4310: FROM
4311: AHL_MTL_RET_DISPOSITIONS_V D,
4312: AHL_WORKORDERS W,
4313: (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_JOB_STATUS' AND LANGUAGE= USERENV('LANG')) WO_STS,
4310: FROM
4311: AHL_MTL_RET_DISPOSITIONS_V D,
4312: AHL_WORKORDERS W,
4313: (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_JOB_STATUS' AND LANGUAGE= USERENV('LANG')) WO_STS,
4314: AHL_VISITS_B V,
4315: AHL_VISIT_TASKS_B VT,
4316: CS_INCIDENTS_ALL_B C,
4317: WIP_DISCRETE_JOBS WDJ,
4318: (SELECT ORGANIZATION_ID, NAME FROM HR_ALL_ORGANIZATION_UNITS_TL WHERE LANGUAGE = USERENV('LANG')) ORG,
4558: T.LOT_NUMBER,
4559: T.REVISION,
4560: T.INSTANCE_ID, -- added to fix FP bug# 5172147.
4561: W.WIP_ENTITY_ID, -- added to filter chk_inst_relationship_csr for wip_job_id.
4562: (select inv_locator_id from ahl_visits_b where visit_id = w.visit_id) inv_locator_id
4563: FROM
4564: AHL_WORKORDER_MTL_TXNS T,
4565: MTL_SYSTEM_ITEMS_KFV I,
4566: AHL_WORKORDERS W,
4563: FROM
4564: AHL_WORKORDER_MTL_TXNS T,
4565: MTL_SYSTEM_ITEMS_KFV I,
4566: AHL_WORKORDERS W,
4567: AHL_VISITS_B V,
4568: AHL_VISIT_TASKS_B VT,
4569: CS_INCIDENTS_ALL_B C,
4570: WIP_DISCRETE_JOBS WDJ,
4571: INV_ORGANIZATION_NAME_V ORG,
4666: inv_project.GET_LOCSEGS(WIP.DEFAULT_PULL_SUPPLY_LOCATOR_ID, WIP.organization_id) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
4667: || INV_ProjectLocator_PUB.get_project_number(MTL_LOC.segment19) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
4668: || INV_ProjectLocator_PUB.get_task_number(MTL_LOC.segment20) LOCATOR,
4669: --SYSDATE
4670: (select inv_locator_id from ahl_visits_b where visit_id = vst.visit_id) inv_locator_id
4671: FROM
4672: AHL_WORKORDER_MTL_TXNS TXNS,
4673: AHL_WORKORDERS WO,
4674: (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_JOB_STATUS' AND LANGUAGE= USERENV('LANG')) WO_STS,
4672: AHL_WORKORDER_MTL_TXNS TXNS,
4673: AHL_WORKORDERS WO,
4674: (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_JOB_STATUS' AND LANGUAGE= USERENV('LANG')) WO_STS,
4675: AHL_VISIT_TASKS_B VST_TASK,
4676: AHL_VISITS_B VST,
4677: AHL_WORKORDER_OPERATIONS WO_OP,
4678: MTL_SYSTEM_ITEMS_KFV MTL,
4679: MTL_UNITS_OF_MEASURE_VL UOM,
4680: -- modified to retrieve segment19 and 20 from base table to fix bug# 6611033.
4746: inv_project.GET_LOCSEGS(W.DEFAULT_PULL_SUPPLY_LOCATOR_ID, W.organization_id) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
4747: || INV_ProjectLocator_PUB.get_project_number(D.segment19) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
4748: || INV_ProjectLocator_PUB.get_task_number(D.segment20) LOCATOR,
4749: --SYSDATE
4750: (select inv_locator_id from ahl_visits_b where visit_id = E.visit_id) inv_locator_id
4751: FROM
4752: AHL_WORKORDER_MTL_TXNS A,
4753: MTL_SYSTEM_ITEMS_KFV B,
4754: MTL_UNITS_OF_MEASURE_VL UOM,
4756: --MTL_ITEM_LOCATIONS_KFV D,
4757: MTL_ITEM_LOCATIONS D,
4758: AHL_WORKORDERS E,
4759: (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_JOB_STATUS' AND LANGUAGE= USERENV('LANG')) WO_STS,
4760: AHL_VISITS_B V,
4761: AHL_VISIT_TASKS_B VT,
4762: AHL_WORKORDER_OPERATIONS F,
4763: WIP_PARAMETERS W
4764: WHERE
5075: ||
5076: DECODE(D.segment19,NULL,NULL,inv_project.GET_PROJECT_NUMBER(D.segment19)) || '.'
5077: ||
5078: DECODE(D.segment20,NULL,NULL,inv_project.GET_TASK_NUMBER(D.segment20)) Locator,
5079: (select inv_locator_id from ahl_visits_b where visit_id = w.visit_id) inv_locator_id
5080: FROM
5081: CSI_ITEM_INSTANCES CSI,
5082: MTL_SYSTEM_ITEMS_KFV I,
5083: AHL_SEARCH_WORKORDERS_v W,