[Home] [Help]
1214: --IF( l_x_sr_rec_tbl.COUNT > 0) THEN
1215: -- l_nonrtn_wo_id := l_x_sr_rec_tbl(i).Nonroutine_wo_id;
1216: --END IF;
1217:
1218: --Insert a record into the AHL_WORKORDER_MTL_TXNS.
1219: IF(p_x_ahl_mtltxn_tbl(i).Qa_Collection_Id = FND_API.G_MISS_NUM OR
1220: p_x_ahl_mtltxn_tbl(i).disposition_id is NOT NULL) THEN
1221: p_x_ahl_mtltxn_tbl(i).Qa_Collection_Id := NULL;
1222: END IF;
1227: IF(p_x_ahl_mtltxn_tbl(i).Locator_Id = FND_API.G_MISS_NUM) THEN
1228: p_x_ahl_mtltxn_tbl(i).Locator_Id := NULL;
1229: END IF;
1230:
1231: -- In case of dynamic locator creation, retrieve locator ID to populate ahl_workorder_mtl_txns table.
1232: IF (p_x_ahl_mtltxn_tbl(i).locator_segments IS NOT NULL AND
1233: p_x_ahl_mtltxn_tbl(i).locator_id IS NULL) THEN
1234:
1235: IF G_DEBUG='Y' THEN
1400: AHL_DEBUG_PUB.debug('l_sr_mtl_id_map_tbl[' || j || ']=['|| l_sr_mtl_id_map_tbl(j) ||']');
1401: END IF;
1402:
1403: -- update non-routine workorder id.
1404: UPDATE AHL_WORKORDER_MTL_TXNS
1405: SET NON_ROUTINE_WORKORDER_ID = l_x_sr_rec_tbl(j).Nonroutine_wo_id,
1406: -- Adithya added for bug# 6995541
1407: CS_INCIDENT_ID = l_x_sr_rec_tbl(j).Incident_id
1408: WHERE WORKORDER_MTL_TXN_ID = l_sr_mtl_id_map_tbl(j);
1521: AHL_DEBUG_PUB.enable_debug;
1522: AHL_DEBUG_PUB.debug('Entered Insert_Mtl_Txn_Row, p_x_ahl_mtltxn_rec.Inventory_Item_Id='|| p_x_ahl_mtltxn_rec.Inventory_Item_Id);
1523: AHL_DEBUG_PUB.debug('Entered Insert_Mtl_Txn_Row, p_x_ahl_mtltxn_rec.Recepient_id='||p_x_ahl_mtltxn_rec.Recepient_id);
1524: END IF;
1525: AHL_WORKORDER_MTL_TXNS_PKG.INSERT_ROW(
1526: X_ROWID => l_x_row_id,
1527: X_WORKORDER_MTL_TXN_ID => x_ahl_mtl_txn_id,
1528: X_OBJECT_VERSION_NUMBER => 1,
1529: X_WORKORDER_OPERATION_ID => p_x_ahl_mtltxn_rec.workorder_Operation_Id,
1566: X_LAST_UPDATE_DATE => SYSDATE,
1567: X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1568: X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
1569:
1570: select AHL_WORKORDER_MTL_TXNS_S.currval into p_x_ahl_mtltxn_rec.Ahl_mtltxn_Id from dual;
1571: -- p_x_ahl_mtltxn_rec.Ahl_mtltxn_Id:=x_ahl_mtl_txn_id;
1572:
1573: IF G_DEBUG='Y' THEN
1574: AHL_DEBUG_PUB.debug('Entered p_x_ahl_mtltxn_rec.Ahl_mtltxn_Id='||p_x_ahl_mtltxn_rec.Ahl_mtltxn_Id);
1606: C_WO_MTLTXN_ID IN NUMBER
1607: )
1608: IS
1609: SELECT COUNT(A.workorder_mtl_txn_id)
1610: FROM AHL_WORKORDER_MTL_TXNS A,
1611: AHL_WORKORDER_OPERATIONS_V B,
1612: AHL_SCHEDULE_MATERIALS C
1613: WHERE B.WORKORDER_ID=C_WRK_ID
1614: AND B.WORKORDER_OPERATION_ID=A.WORKORDER_OPERATION_ID
3898: -- The following line will be needed in the case net quantity issued is to be displayed, instead of entire issued quantity
3899: -- SELECT sum(nvl(decode(TRANSACTION_TYPE_ID, 35, QUANTITY, 43, -QUANTITY, 0), 0))
3900: SELECT sum(nvl(QUANTITY, 0))
3901: INTO l_iss_qty
3902: FROM AHL_WORKORDER_MTL_TXNS
3903: WHERE ORGANIZATION_ID = P_ORG_ID
3904: AND INVENTORY_ITEM_ID = P_ITEM_ID
3905: AND WORKORDER_OPERATION_ID = P_WORKORDER_OP_ID
3906: -- The following line will NOT be needed in the case net quantity issued is to be displayed
3923: As
3924: issued NUMBER;
3925: CURSOR CUR_GET_WOID_LEVEL_QTY IS
3926: SELECT SUM(nvl(QUANTITY,0))
3927: FROM AHL_WORKORDER_MTL_TXNS A
3928: ,AHL_WORKORDER_OPERATIONS_V B
3929: WHERE A.ORGANIZATION_ID = p_org_id
3930: AND A.INVENTORY_ITEM_ID = p_item_id
3931: AND NVL(A.lot_number,'X')=NVL(p_lotnum,NVL(A.lot_number,'X'))
3972: -- cursor for getting total issued quantity
3973: CURSOR CUR_GET_WO_ISSUE_QTY
3974: IS
3975: SELECT SUM(nvl(QUANTITY,0))
3976: FROM AHL_WORKORDER_MTL_TXNS A ,
3977: -- AHL_WORKORDER_OPERATIONS_V B
3978: -- JKJAIN BUG # 7587902
3979: AHL_WORKORDER_OPERATIONS B
3980: WHERE A.ORGANIZATION_ID = p_org_id
3987: -- cursor for getting total returned quantity
3988: CURSOR CUR_GET_WO_RET_QTY
3989: IS
3990: SELECT SUM(nvl(QUANTITY,0))
3991: FROM AHL_WORKORDER_MTL_TXNS A ,
3992: -- AHL_WORKORDER_OPERATIONS_V B
3993: -- JKJAIN BUG # 7587902
3994: AHL_WORKORDER_OPERATIONS B
3995: WHERE A.ORGANIZATION_ID = p_org_id
4198:
4199:
4200: SELECT SUM(NVL(a.primary_uom_qty,0)) INTO l_iss_qty
4201: FROM
4202: AHL_WORKORDER_MTL_TXNS a,
4203: AHL_WORKORDER_OPERATIONS b
4204: WHERE
4205: a.workorder_operation_id=b.workorder_operation_id
4206: AND a.transaction_type_id=35
4213: AND a.workorder_operation_id=nvl(p_wo_op_id, a.workorder_operation_id);
4214:
4215: SELECT SUM(NVL(a.primary_uom_qty,0)) INTO l_rtn_qty
4216: FROM
4217: AHL_WORKORDER_MTL_TXNS a,
4218: AHL_WORKORDER_OPERATIONS b
4219: WHERE
4220: a.workorder_operation_id=b.workorder_operation_id
4221: AND a.transaction_type_id=43
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,
4567: AHL_VISITS_B V,
4568: AHL_VISIT_TASKS_B VT,
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,
4675: AHL_VISIT_TASKS_B VST_TASK,
4676: AHL_VISITS_B VST,
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,
4755: -- modified to retrieve segment19 and 20 from base table to fix bug# 6611033.
4756: --MTL_ITEM_LOCATIONS_KFV D,
5114: AND NVL(ACTIVE_END_DATE,SYSDATE))
5115: /* fix for bug# 6310766: extra row being displayed with 0 qty
5116: -- split this query into two to handle serialized and non-serialized items.
5117: AND NOT EXISTS (SELECT 'x'
5118: from ahl_workorder_mtl_txns txn, AHL_WORKORDER_OPERATIONS o
5119: where txn.workorder_operation_id = o.workorder_operation_id
5120: and o.workorder_id = w.workorder_id
5121: and txn.instance_id = csi.instance_id
5122: and txn.TRANSACTION_TYPE_ID = 35)
5122: and txn.TRANSACTION_TYPE_ID = 35)
5123: */
5124: -- for serialized items
5125: AND NOT EXISTS (SELECT 'x'
5126: from ahl_workorder_mtl_txns txn, AHL_WORKORDER_OPERATIONS o
5127: where txn.workorder_operation_id = o.workorder_operation_id
5128: and o.workorder_id = w.workorder_id
5129: and txn.instance_id = csi.instance_id
5130: and txn.TRANSACTION_TYPE_ID = 35
5134: --and txn.inventory_item_id = csi.inventory_item_id
5135: )
5136: -- for non-serialized items
5137: AND NOT EXISTS (SELECT 'x'
5138: from ahl_workorder_mtl_txns txn, AHL_WORKORDER_OPERATIONS o
5139: where txn.workorder_operation_id = o.workorder_operation_id
5140: and o.workorder_id = w.workorder_id
5141: and txn.TRANSACTION_TYPE_ID = 35
5142: and txn.serial_number is null