[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Mtl_Txn_Intf
(
p_x_ahl_mtlxfr_rec IN OUT NOCOPY Ahl_Mtlxfr_Rec_Type,
p_x_txn_hdr_id IN OUT NOCOPY NUMBER,
p_x_txn_intf_id IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
);
PROCEDURE Update_EAM_Workorders
(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_wip_entity_id IN NUMBER,
p_instance_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
SELECT intf.ERROR_EXPLANATION ,intf.ERROR_CODE, kfv.concatenated_segments
FROM MTL_TRANSACTIONS_INTERFACE INTF, mtl_system_items_kfv kfv
WHERE TRANSACTION_INTERFACE_ID = p_txn_Id
AND intf.inventory_item_id = kfv.inventory_item_id
AND intf.organization_id = kfv.organization_id ;
AHL_DEBUG_PUB.debug('Calling Insert_Txn_Intf...');
INSERT_MTL_TXN_INTF
(
p_x_ahl_mtlxfr_rec => p_x_ahl_mtlxfr_tbl(i) ,
p_x_txn_hdr_id => l_txn_Header_Id,
p_x_txn_intf_id => l_txn_tmp_Id,
x_return_status => x_return_status
);
AHL_DEBUG_PUB.debug('After Calling Insert_Txn_Intf...ret_status['||x_return_status||']');
END LOOP; -- End of loop for Interface table inserts
SELECT SERIAL_NUMBER_CONTROL_CODE, LOT_CONTROL_CODE, REVISION_QTY_CONTROL_CODE,
LOCATION_CONTROL_CODE,primary_uom_code, concatenated_segments
FROM MTL_SYSTEM_ITEMS_KFV
WHERE ORGANIZATION_ID = p_org_id
AND INVENTORY_ITEM_ID = p_item
AND ENABLED_FLAG = 'Y'
AND ((START_DATE_ACTIVE IS NULL) OR (START_DATE_ACTIVE <= SYSDATE))
AND ((END_DATE_ACTIVE IS NULL) OR (END_DATE_ACTIVE >= SYSDATE));
SELECT 1
FROM MTL_SECONDARY_INVENTORIES
WHERE ORGANIZATION_ID = p_org_id
AND SECONDARY_INVENTORY_NAME = p_subinv;
SELECT 1
FROM MTL_ITEM_LOCATIONS
WHERE ORGANIZATION_ID = p_org_id
AND INVENTORY_LOCATION_ID = p_locator_id;
SELECT 1
FROM MTL_ITEM_REVISIONS
WHERE ORGANIZATION_ID = p_org_id
AND INVENTORY_ITEM_ID = p_item
AND REVISION = p_revision;
SELECT current_subinventory_code, current_locator_id
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND current_organization_id = p_org_id
AND inventory_item_id = p_inv_id
AND current_status = 3;
SELECT 'x'
FROM mtl_lot_numbers
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number
AND nvl(disable_flag,2) = 2;
SELECT TRANSACTION_TYPE_NAME
from MTL_TRANSACTION_TYPES
where TRANSACTION_TYPE_ID = p_type_Id;
PROCEDURE INSERT_MTL_TXN_INTF
(
p_x_ahl_mtlxfr_rec IN OUT NOCOPY Ahl_Mtlxfr_Rec_Type,
p_x_txn_hdr_id IN OUT NOCOPY NUMBER,
p_x_txn_intf_id IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_Process_Flag VARCHAR2(1);
SELECT TRANSACTION_ACTION_ID,TRANSACTION_SOURCE_TYPE_ID
from MTL_TRANSACTION_TYPES
where TRANSACTION_TYPE_ID = p_type_Id;
SELECT ATTRIBUTE_CATEGORY,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
C_ATTRIBUTE1, C_ATTRIBUTE2, C_ATTRIBUTE3, C_ATTRIBUTE4, C_ATTRIBUTE5,
C_ATTRIBUTE6, C_ATTRIBUTE7, C_ATTRIBUTE8, C_ATTRIBUTE9, C_ATTRIBUTE10,
C_ATTRIBUTE11, C_ATTRIBUTE12, C_ATTRIBUTE13, C_ATTRIBUTE14, C_ATTRIBUTE15,
C_ATTRIBUTE16, C_ATTRIBUTE17, C_ATTRIBUTE18, C_ATTRIBUTE19, C_ATTRIBUTE20,
D_ATTRIBUTE1, D_ATTRIBUTE2, D_ATTRIBUTE3, D_ATTRIBUTE4, D_ATTRIBUTE5,
D_ATTRIBUTE6, D_ATTRIBUTE7, D_ATTRIBUTE8, D_ATTRIBUTE9, D_ATTRIBUTE10,
N_ATTRIBUTE1, N_ATTRIBUTE2, N_ATTRIBUTE3, N_ATTRIBUTE4, N_ATTRIBUTE5,
N_ATTRIBUTE6, N_ATTRIBUTE7, N_ATTRIBUTE8, N_ATTRIBUTE9, N_ATTRIBUTE10
FROM mtl_lot_numbers
where lot_number = p_lot_number;
SELECT ATTRIBUTE_CATEGORY,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
C_ATTRIBUTE1, C_ATTRIBUTE2, C_ATTRIBUTE3, C_ATTRIBUTE4, C_ATTRIBUTE5,
C_ATTRIBUTE6, C_ATTRIBUTE7, C_ATTRIBUTE8, C_ATTRIBUTE9, C_ATTRIBUTE10,
C_ATTRIBUTE11, C_ATTRIBUTE12, C_ATTRIBUTE13, C_ATTRIBUTE14, C_ATTRIBUTE15,
C_ATTRIBUTE16, C_ATTRIBUTE17, C_ATTRIBUTE18, C_ATTRIBUTE19, C_ATTRIBUTE20,
D_ATTRIBUTE1, D_ATTRIBUTE2, D_ATTRIBUTE3, D_ATTRIBUTE4, D_ATTRIBUTE5,
D_ATTRIBUTE6, D_ATTRIBUTE7, D_ATTRIBUTE8, D_ATTRIBUTE9, D_ATTRIBUTE10,
N_ATTRIBUTE1, N_ATTRIBUTE2, N_ATTRIBUTE3, N_ATTRIBUTE4, N_ATTRIBUTE5,
N_ATTRIBUTE6, N_ATTRIBUTE7, N_ATTRIBUTE8, N_ATTRIBUTE9, N_ATTRIBUTE10
FROM mtl_serial_numbers
where inventory_item_id = p_inv_item_id
and serial_number = p_serial_number;
SELECT Mtl_Material_Transactions_S.nextval
INTO p_x_txn_hdr_id
FROM DUAL;
SELECT Mtl_Material_Transactions_S.nextval
INTO p_x_txn_intf_id
FROM DUAL;
SELECT Mtl_Material_Transactions_S.nextval
INTO l_txn_tmp_id
FROM DUAL;
AHL_DEBUG_PUB.debug('insertng the lot record,interface id,tempid['
||to_char(p_x_txn_intf_id)||','
||to_char(l_txn_tmp_id)||']');
INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
( TRANSACTION_INTERFACE_ID ,
SOURCE_CODE ,
SOURCE_LINE_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
LOT_NUMBER ,
LOT_EXPIRATION_DATE ,
TRANSACTION_QUANTITY ,
PRIMARY_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, C_ATTRIBUTE1,
C_ATTRIBUTE2, C_ATTRIBUTE3, C_ATTRIBUTE4, C_ATTRIBUTE5,
C_ATTRIBUTE6, C_ATTRIBUTE7, C_ATTRIBUTE8, C_ATTRIBUTE9,
C_ATTRIBUTE10, C_ATTRIBUTE11, C_ATTRIBUTE12, C_ATTRIBUTE13,
C_ATTRIBUTE14, C_ATTRIBUTE15, C_ATTRIBUTE16, C_ATTRIBUTE17,
C_ATTRIBUTE18, C_ATTRIBUTE19, C_ATTRIBUTE20, D_ATTRIBUTE1,
D_ATTRIBUTE2, D_ATTRIBUTE3, D_ATTRIBUTE4, D_ATTRIBUTE5,
D_ATTRIBUTE6, D_ATTRIBUTE7, D_ATTRIBUTE8, D_ATTRIBUTE9,
D_ATTRIBUTE10, N_ATTRIBUTE1, N_ATTRIBUTE2, N_ATTRIBUTE3,
N_ATTRIBUTE4, N_ATTRIBUTE5, N_ATTRIBUTE6, N_ATTRIBUTE7,
N_ATTRIBUTE8, N_ATTRIBUTE9, N_ATTRIBUTE10
)
VALUES(p_x_txn_intf_id,
l_Source_Code,
l_Source_Line_Id,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
p_x_ahl_mtlxfr_rec.Lot_Number,
l_lot_expiration_Date,
p_x_ahl_mtlxfr_rec.Quantity,
p_x_ahl_mtlxfr_rec.Quantity,
l_txn_tmp_id,
l_lot_dff_rec.ATTRIBUTE_CATEGORY,
l_lot_dff_rec.ATTRIBUTE1, l_lot_dff_rec.ATTRIBUTE2, l_lot_dff_rec.ATTRIBUTE3, l_lot_dff_rec.ATTRIBUTE4,
l_lot_dff_rec.ATTRIBUTE5, l_lot_dff_rec.ATTRIBUTE6, l_lot_dff_rec.ATTRIBUTE7, l_lot_dff_rec.ATTRIBUTE8,
l_lot_dff_rec.ATTRIBUTE9, l_lot_dff_rec.ATTRIBUTE10, l_lot_dff_rec.ATTRIBUTE11, l_lot_dff_rec.ATTRIBUTE12,
l_lot_dff_rec.ATTRIBUTE13, l_lot_dff_rec.ATTRIBUTE14, l_lot_dff_rec.ATTRIBUTE15,
l_lot_dff_rec.C_ATTRIBUTE1, l_lot_dff_rec.C_ATTRIBUTE2, l_lot_dff_rec.C_ATTRIBUTE3, l_lot_dff_rec.C_ATTRIBUTE4,
l_lot_dff_rec.C_ATTRIBUTE5, l_lot_dff_rec.C_ATTRIBUTE6, l_lot_dff_rec.C_ATTRIBUTE7, l_lot_dff_rec.C_ATTRIBUTE8,
l_lot_dff_rec.C_ATTRIBUTE9, l_lot_dff_rec.C_ATTRIBUTE10, l_lot_dff_rec.C_ATTRIBUTE11, l_lot_dff_rec.C_ATTRIBUTE12,
l_lot_dff_rec.C_ATTRIBUTE13, l_lot_dff_rec.C_ATTRIBUTE14, l_lot_dff_rec.C_ATTRIBUTE15, l_lot_dff_rec.C_ATTRIBUTE16,
l_lot_dff_rec.C_ATTRIBUTE17, l_lot_dff_rec.C_ATTRIBUTE18, l_lot_dff_rec.C_ATTRIBUTE19, l_lot_dff_rec.C_ATTRIBUTE20,
l_lot_dff_rec.D_ATTRIBUTE1, l_lot_dff_rec.D_ATTRIBUTE2, l_lot_dff_rec.D_ATTRIBUTE3, l_lot_dff_rec.D_ATTRIBUTE4,
l_lot_dff_rec.D_ATTRIBUTE5, l_lot_dff_rec.D_ATTRIBUTE6, l_lot_dff_rec.D_ATTRIBUTE7, l_lot_dff_rec.D_ATTRIBUTE8,
l_lot_dff_rec.D_ATTRIBUTE9, l_lot_dff_rec.D_ATTRIBUTE10,
l_lot_dff_rec.N_ATTRIBUTE1, l_lot_dff_rec.N_ATTRIBUTE2, l_lot_dff_rec.N_ATTRIBUTE3, l_lot_dff_rec.N_ATTRIBUTE4,
l_lot_dff_rec.N_ATTRIBUTE5, l_lot_dff_rec.N_ATTRIBUTE6, l_lot_dff_rec.N_ATTRIBUTE7, l_lot_dff_rec.N_ATTRIBUTE8,
l_lot_dff_rec.N_ATTRIBUTE9, l_lot_dff_rec.N_ATTRIBUTE10
);
AHL_DEBUG_PUB.debug('insertng the serial record,interface id['
||to_char(l_txn_tmp_id)||']');
INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
TRANSACTION_INTERFACE_ID, SOURCE_CODE, SOURCE_LINE_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN, FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER, PROCESS_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, C_ATTRIBUTE1,
C_ATTRIBUTE2, C_ATTRIBUTE3, C_ATTRIBUTE4, C_ATTRIBUTE5,
C_ATTRIBUTE6, C_ATTRIBUTE7, C_ATTRIBUTE8, C_ATTRIBUTE9,
C_ATTRIBUTE10, C_ATTRIBUTE11, C_ATTRIBUTE12, C_ATTRIBUTE13,
C_ATTRIBUTE14, C_ATTRIBUTE15, C_ATTRIBUTE16, C_ATTRIBUTE17,
C_ATTRIBUTE18, C_ATTRIBUTE19, C_ATTRIBUTE20, D_ATTRIBUTE1,
D_ATTRIBUTE2, D_ATTRIBUTE3, D_ATTRIBUTE4, D_ATTRIBUTE5,
D_ATTRIBUTE6, D_ATTRIBUTE7, D_ATTRIBUTE8, D_ATTRIBUTE9,
D_ATTRIBUTE10, N_ATTRIBUTE1, N_ATTRIBUTE2, N_ATTRIBUTE3,
N_ATTRIBUTE4, N_ATTRIBUTE5, N_ATTRIBUTE6, N_ATTRIBUTE7,
N_ATTRIBUTE8, N_ATTRIBUTE9, N_ATTRIBUTE10
)
VALUES ( l_txn_tmp_id,
l_source_code,
l_source_line_id,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
p_x_ahl_mtlxfr_rec.Serial_Number,
p_x_ahl_mtlxfr_rec.Serial_Number,
l_Process_Flag,
l_serial_dff_rec.ATTRIBUTE_CATEGORY,
l_serial_dff_rec.ATTRIBUTE1, l_serial_dff_rec.ATTRIBUTE2, l_serial_dff_rec.ATTRIBUTE3, l_serial_dff_rec.ATTRIBUTE4,
l_serial_dff_rec.ATTRIBUTE5, l_serial_dff_rec.ATTRIBUTE6, l_serial_dff_rec.ATTRIBUTE7, l_serial_dff_rec.ATTRIBUTE8,
l_serial_dff_rec.ATTRIBUTE9, l_serial_dff_rec.ATTRIBUTE10, l_serial_dff_rec.ATTRIBUTE11, l_serial_dff_rec.ATTRIBUTE12,
l_serial_dff_rec.ATTRIBUTE13, l_serial_dff_rec.ATTRIBUTE14, l_serial_dff_rec.ATTRIBUTE15,
l_serial_dff_rec.C_ATTRIBUTE1, l_serial_dff_rec.C_ATTRIBUTE2, l_serial_dff_rec.C_ATTRIBUTE3,
l_serial_dff_rec.C_ATTRIBUTE4, l_serial_dff_rec.C_ATTRIBUTE5,
l_serial_dff_rec.C_ATTRIBUTE6, l_serial_dff_rec.C_ATTRIBUTE7,
l_serial_dff_rec.C_ATTRIBUTE8, l_serial_dff_rec.C_ATTRIBUTE9,
l_serial_dff_rec.C_ATTRIBUTE10, l_serial_dff_rec.C_ATTRIBUTE11,
l_serial_dff_rec.C_ATTRIBUTE12, l_serial_dff_rec.C_ATTRIBUTE13,
l_serial_dff_rec.C_ATTRIBUTE14, l_serial_dff_rec.C_ATTRIBUTE15,
l_serial_dff_rec.C_ATTRIBUTE16, l_serial_dff_rec.C_ATTRIBUTE17,
l_serial_dff_rec.C_ATTRIBUTE18, l_serial_dff_rec.C_ATTRIBUTE19,
l_serial_dff_rec.C_ATTRIBUTE20, l_serial_dff_rec.D_ATTRIBUTE1,
l_serial_dff_rec.D_ATTRIBUTE2, l_serial_dff_rec.D_ATTRIBUTE3,
l_serial_dff_rec.D_ATTRIBUTE4, l_serial_dff_rec.D_ATTRIBUTE5,
l_serial_dff_rec.D_ATTRIBUTE6, l_serial_dff_rec.D_ATTRIBUTE7,
l_serial_dff_rec.D_ATTRIBUTE8, l_serial_dff_rec.D_ATTRIBUTE9,
l_serial_dff_rec.D_ATTRIBUTE10, l_serial_dff_rec.N_ATTRIBUTE1,
l_serial_dff_rec.N_ATTRIBUTE2, l_serial_dff_rec.N_ATTRIBUTE3,
l_serial_dff_rec.N_ATTRIBUTE4, l_serial_dff_rec.N_ATTRIBUTE5,
l_serial_dff_rec.N_ATTRIBUTE6, l_serial_dff_rec.N_ATTRIBUTE7,
l_serial_dff_rec.N_ATTRIBUTE8, l_serial_dff_rec.N_ATTRIBUTE9,
l_serial_dff_rec.N_ATTRIBUTE10
);
AHL_DEBUG_PUB.debug('insertng the txn record,header id,interface id['
||to_char(p_x_txn_hdr_id)||','
||to_char(p_x_txn_intf_id)||']');
INSERT INTO MTL_TRANSACTIONS_INTERFACE
( TRANSACTION_INTERFACE_ID , TRANSACTION_HEADER_ID ,
SOURCE_CODE , SOURCE_LINE_ID ,
SOURCE_HEADER_ID, PROCESS_FLAG ,
VALIDATION_REQUIRED , TRANSACTION_MODE ,
LAST_UPDATE_DATE , LAST_UPDATED_BY ,
CREATION_DATE , CREATED_BY ,
LAST_UPDATE_LOGIN , INVENTORY_ITEM_ID ,
ORGANIZATION_ID , TRANSACTION_QUANTITY ,
PRIMARY_QUANTITY , TRANSACTION_UOM ,
TRANSACTION_DATE , SUBINVENTORY_CODE ,
LOCATOR_ID , TRANSACTION_TYPE_ID ,
REVISION , TRANSACTION_REFERENCE ,
TRANSFER_SUBINVENTORY, TRANSFER_LOCATOR,
XFER_LOC_SEGMENT1, XFER_LOC_SEGMENT2,
XFER_LOC_SEGMENT3, XFER_LOC_SEGMENT4,
XFER_LOC_SEGMENT5, XFER_LOC_SEGMENT6,
XFER_LOC_SEGMENT7, XFER_LOC_SEGMENT8,
XFER_LOC_SEGMENT9, XFER_LOC_SEGMENT10,
XFER_LOC_SEGMENT11, XFER_LOC_SEGMENT12,
XFER_LOC_SEGMENT13, XFER_LOC_SEGMENT14,
XFER_LOC_SEGMENT15, XFER_LOC_SEGMENT16,
XFER_LOC_SEGMENT17, XFER_LOC_SEGMENT18,
XFER_LOC_SEGMENT19, XFER_LOC_SEGMENT20 )
values (p_x_txn_intf_id, p_x_txn_hdr_id,
l_Source_Code, l_Source_Line_Id,
l_Source_Header_Id, l_Process_Flag,
l_Validation_required , l_transaction_Mode,
sysdate, FND_GLOBAL.USER_ID,
sysdate, FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID, p_x_ahl_mtlxfr_rec.Inventory_Item_Id,
p_x_ahl_mtlxfr_rec.Organization_Id, p_x_ahl_mtlxfr_rec.Quantity,
p_x_ahl_mtlxfr_rec.Quantity, p_x_ahl_mtlxfr_rec.UOM,
sysdate , p_x_ahl_mtlxfr_rec.Src_Subinv_Name,
p_x_ahl_mtlxfr_rec.Src_Locator_Id, p_x_ahl_mtlxfr_rec.Transaction_Type_Id,
p_x_ahl_mtlxfr_rec.Revision, p_x_ahl_mtlxfr_rec.Transaction_Reference,
p_x_ahl_mtlxfr_rec.Dst_SubInv_Name, p_x_ahl_mtlxfr_rec.Dst_Locator_Id,
l_mti_seglist(1), l_mti_seglist(2),
l_mti_seglist(3), l_mti_seglist(4),
l_mti_seglist(5), l_mti_seglist(6),
l_mti_seglist(7), l_mti_seglist(8),
l_mti_seglist(9), l_mti_seglist(10),
l_mti_seglist(11), l_mti_seglist(12),
l_mti_seglist(13), l_mti_seglist(14),
l_mti_seglist(15), l_mti_seglist(16),
l_mti_seglist(17), l_mti_seglist(18),
l_mti_seglist(19), l_mti_seglist(20)
) ;
AHL_DEBUG_PUB.debug('Exception inserting into mtl_txn interface' || SQLCODE);
END INSERT_MTL_TXN_INTF;
SELECT CSI.serial_number,
CSI.lot_number,
CSI.quantity,
CSI.unit_of_measure,
MTL.concatenated_segments
FROM CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL
WHERE CSI.inv_master_organization_id = c_item_mstr_org_id
AND CSI.inv_organization_id = c_item_inv_org_id
AND CSI.inventory_item_id = c_inv_item_id
AND CSI.instance_id = c_instance_id
AND MTL.inventory_item_id = CSI.inventory_item_id
AND MTL.organization_id = CSI.inv_master_organization_id
AND NOT EXISTS (
SELECT 'X'
FROM CSI_II_RELATIONSHIPS
WHERE relationship_type_code = 'COMPONENT-OF'
AND (
object_id = CSI.instance_id
OR
subject_id = CSI.instance_id
)
AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
)
AND TRUNC(NVL(CSI.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(CSI.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT 'X'
FROM MTL_ITEM_LOCATIONS
WHERE organization_id = c_org_id
AND inventory_location_id = c_locator_id
AND subinventory_code = c_subinv_name;
SELECT 'X'
FROM AHL_VISIT_TASKS_B TSK, CSI_ITEM_INSTANCES CSI
WHERE TSK.repair_batch_name = c_rpr_batch_name
AND TSK.instance_id = CSI.instance_id
AND CSI.inventory_item_id = c_inv_item_id
AND TSK.status_code NOT IN ('CANCELLED', 'CLOSED', 'DELETED');
SELECT name
FROM HR_ALL_ORGANIZATION_UNITS
WHERE organization_id = c_org_id;
SELECT VST.visit_number,
TSK.visit_task_number,
VST.project_id,
PRJ.name project_number,
PRT.task_id project_task_id,
PRT.task_number project_task_number,
VST.comp_planning_loc_id,
VST.comp_inrepair_loc_id
FROM AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
PA_PROJECTS_ALL PRJ, PA_TASKS PRT
WHERE TSK.visit_task_id = c_visit_task_id
AND VST.visit_id = TSK.visit_id
AND PRJ.project_id = VST.project_id
AND PRT.project_id = PRJ.project_id
AND PRT.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id);
SELECT MTK.concatenated_segments,
MTI.subinventory_code
FROM MTL_ITEM_LOCATIONS MTI, MTL_ITEM_LOCATIONS_KFV MTK
WHERE MTK.inventory_location_id = MTI.inventory_location_id
AND MTI.inventory_location_id = c_inv_locator_id;
SELECT MTLP.inventory_location_id planning_loc_id,
MTLP.subinventory_code plannning_subinv,
(SELECT inventory_location_id
FROM MTL_ITEM_LOCATIONS
WHERE physical_location_id = VST.comp_inrepair_loc_id
AND project_id = VST.project_id
AND task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id)) inrepair_loc_id,
(SELECT subinventory_code
FROM MTL_ITEM_LOCATIONS
WHERE physical_location_id = VST.comp_inrepair_loc_id
AND project_id = VST.project_id
AND task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id)) inrepair_subinv
FROM AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
MTL_ITEM_LOCATIONS MTLP
WHERE TSK.repair_batch_name = c_rpr_batch_name
AND VST.visit_id = TSK.visit_id
AND MTLP.physical_location_id = VST.comp_planning_loc_id
AND MTLP.project_id = VST.project_id
AND MTLP.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id);
SELECT instance_id
FROM CSI_ITEM_INSTANCES
WHERE inv_locator_id = c_inv_locator_id
AND inventory_item_id = c_inv_item_id
AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT quantity
FROM CSI_ITEM_INSTANCES
WHERE inv_locator_id = c_inv_locator_id
AND inventory_item_id = c_inv_item_id
AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT visit_task_id
FROM AHL_VISIT_TASKS_B
WHERE repair_batch_name = c_rpr_batch_name
UNION ALL
SELECT vts.visit_task_id
FROM AHL_VISIT_TASKS_B vts,
AHL_WORKORDERS wo
WHERE vts.status_code NOT IN ('CANCELLED', 'CLOSED', 'DELETED')
AND wo.visit_task_id = vts.visit_task_id
AND wo.status_code NOT IN ('7','12','22' ) -- Cancelled,Closed,Deleted
START WITH vts.cost_parent_id = ( SELECT visit_task_id
FROM AHL_VISIT_TASKS_B
WHERE repair_batch_name = c_rpr_batch_name)
CONNECT BY vts.cost_parent_id = PRIOR vts.visit_task_id;
SELECT 'x'
FROM AHL_VISIT_TASKS_B tsk,
AHL_VISITS_B vst
WHERE vst.visit_id = tsk.visit_id
AND tsk.repair_batch_name = c_rpr_batch_name
AND NVL(vst.close_date_time, SYSDATE+1) < SYSDATE;
IF (NVL(p_opr_flag, '-') <> G_OP_CREATE AND NVL(p_opr_flag, '-') <> G_OP_UPDATE) THEN
-- operation flag is invalid
IF (l_log_statement >= l_log_current_level) THEN
FND_LOG.string(l_log_statement, l_full_name, 'operation flag is invalid');
IF (p_opr_flag = G_OP_UPDATE) THEN
OPEN chk_rpr_batch_csr (p_x_dst_rpr_batch_name, p_inv_item_id);
FND_LOG.string(l_log_statement, l_full_name, 'repair batch update not allowed for lot controlled items');
FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_CMP_LOT_UPDATE_INVLD');
UPDATE AHL_VISIT_TASKS_B SET
target_qty = p_repair_qty,
quantity = p_repair_qty,
repair_batch_name = p_x_dst_rpr_batch_name,
ret_serviceable_qty = NULL,
ret_unserviceable_qty = NULL
WHERE visit_task_id = l_task_rec.visit_task_id;
UPDATE AHL_VISIT_TASKS_TL SET
visit_task_name = p_x_dst_rpr_batch_name
WHERE visit_task_id = l_task_rec.visit_task_id;
UPDATE AHL_VISIT_TASKS_B SET
instance_id = l_locator_inst_id
WHERE repair_batch_name = p_x_dst_rpr_batch_name;
UPDATE AHL_VISIT_TASKS_B SET
quantity = l_locator_inst_qty
WHERE visit_task_id = l_task_id_tbl(i);
UPDATE AHL_VISIT_TASKS_B SET
quantity = l_locator_inst_qty
WHERE visit_task_id = l_task_id_tbl(i);
SELECT wo.visit_task_id,wo.workorder_id,wo.workorder_name,wo.status_code
FROM ahl_workorders wo
WHERE wo.visit_task_id in
( SELECT vt.visit_task_id
FROM AHL_VISIT_TASKS_B vt
START WITH vt.visit_task_id = c_repair_batch_id
CONNECT BY PRIOR VT.VISIT_TASK_ID = VT.COST_PARENT_ID );
SELECT wo.visit_task_id,wo.workorder_id,wo.wip_entity_id
FROM ahl_workorders wo
WHERE wo.STATUS_CODE <> '7'
AND wo.visit_task_id IN
( SELECT vt.visit_task_id
FROM AHL_VISIT_TASKS_B vt
START WITH vt.visit_task_id = c_repair_batch_id
CONNECT BY PRIOR VT.VISIT_TASK_ID = VT.COST_PARENT_ID );
SELECT *
FROM AHL_VISIT_TASKS_B
WHERE visit_task_id = c_repair_batch_id;
IF l_batch_dtl_rec.status_code IS NOT NULL AND l_batch_dtl_rec.status_code IN ('CLOSED','CANCELLED','DELETED') THEN
FND_MESSAGE.SET_NAME('AHL','AHL_CMP_CANNOT_CLOSE');
UPDATE ahl_visit_tasks_b
SET actual_cost = l_actual_cost
WHERE visit_task_id = l_wip_entity_rec.visit_task_id;
/* UPDATE ahl_visit_tasks_b
SET status_code = 'CLOSED'
WHERE visit_task_id IN ( SELECT vt.visit_task_id
FROM AHL_VISIT_TASKS_B vt
START WITH vt.visit_task_id = p_repair_batch_id
CONNECT BY PRIOR VT.VISIT_TASK_ID = VT.COST_PARENT_ID ); */
UPDATE ahl_visit_tasks_b
SET status_code = 'CLOSED'
WHERE visit_task_id = p_repair_batch_id ;
SELECT CSI.serial_number,
CSI.lot_number,
CSI.quantity,
CSI.unit_of_measure,
MTL.concatenated_segments,
CSI.inv_master_organization_id,
CSI.inv_organization_id,
CSI.inventory_item_id
FROM CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL
WHERE CSI.instance_id = p_instance_id_csr
AND MTL.inventory_item_id = CSI.inventory_item_id
AND MTL.organization_id = CSI.inv_master_organization_id
AND TRUNC(NVL(CSI.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(CSI.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT VST.visit_number,
TSK.visit_task_number,
VST.project_id,
PRJ.name project_number,
PRT.task_id project_task_id,
PRT.task_number project_task_number,
VST.comp_inrepair_loc_id,
VST.comp_planning_loc_id
FROM AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
PA_PROJECTS_ALL PRJ, PA_TASKS PRT
WHERE TSK.repair_batch_name = p_rpr_batch_name_csr
AND VST.visit_id = TSK.visit_id
AND PRJ.project_id = VST.project_id
AND PRT.project_id = PRJ.project_id
AND PRT.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id);
SELECT MTL.inventory_location_id,
MTL.subinventory_code
FROM AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
MTL_ITEM_LOCATIONS MTL
WHERE TSK.repair_batch_name = p_rpr_batch_name_csr
AND VST.visit_id = TSK.visit_id
AND MTL.physical_location_id = VST.comp_planning_loc_id
AND MTL.project_id = VST.project_id
AND MTL.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id);
SELECT MTL.inventory_location_id,
MTL.subinventory_code
FROM AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
MTL_ITEM_LOCATIONS MTL
WHERE TSK.repair_batch_name = p_rpr_batch_name_csr
AND VST.visit_id = TSK.visit_id
AND MTL.physical_location_id = VST.comp_inrepair_loc_id
AND MTL.project_id = VST.project_id
AND MTL.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id);
SELECT instance_id FROM csi_item_instances WHERE
inv_locator_id = p_inrepair_locator_id;
SELECT MTK.concatenated_segments,
MTI.subinventory_code
FROM MTL_ITEM_LOCATIONS MTI, MTL_ITEM_LOCATIONS_KFV MTK
WHERE MTK.inventory_location_id = MTI.inventory_location_id
AND MTI.inventory_location_id = p_inv_locator_id_csr;
SELECT object_version_number
FROM csi_item_instances
WHERE instance_id = c_instance_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT wip_entity_id
FROM ahl_workorders
WHERE status_code IN ('1', '3', '6', '19', '20', '17')
--unreleased, released, on-hold, parts hold and pending QA approval(open workorders)
AND visit_task_id IN
(SELECT vt.visit_task_id
FROM AHL_VISIT_TASKS_B vt
WHERE instance_id = p_instance_id_csr
START WITH vt.visit_task_id =
(SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE repair_batch_name = p_repair_batch_csr
)
CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id
);
SELECT 'Y'
FROM (SELECT VST.comp_inrepair_loc_id, VST.project_id,
VST.visit_id,
TSK.project_task_id,
TSK.inventory_item_id
FROM AHL_VISIT_TASKS_VL TSK,
AHL_VISITS_B VST
WHERE TSK.repair_batch_name = p_rprbatch_name_csr
AND VST.visit_id = TSK.visit_id) VISIT_TASK,
MTL_ITEM_LOCATIONS_KFV INRPR_LOC, CSI_ITEM_INSTANCES csi
WHERE csi.inv_locator_id = INRPR_LOC.inventory_location_id
AND INRPR_LOC.physical_location_id = VISIT_TASK.comp_inrepair_loc_id
AND INRPR_LOC.project_id = VISIT_TASK.project_id
AND INRPR_LOC.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VISIT_TASK.project_id,VISIT_TASK.project_task_id,VISIT_TASK.visit_id)
AND csi.inventory_item_id = VISIT_TASK.inventory_item_id
AND csi.instance_id = p_instance_id_csr
AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT COUNT(wo.visit_task_id) INTO l_open_wo_count FROM ahl_workorders wo, ahl_visit_tasks_b task
WHERE
task.visit_task_id = wo.visit_task_id
and task.instance_id = p_instance_id;
UPDATE ahl_visit_tasks_b SET instance_id = l_new_instance_id WHERE
visit_id = (SELECT visit_id FROM ahl_visit_tasks_b
WHERE repair_batch_name = p_repair_batch_name)
AND instance_id = p_instance_id;
SELECT COUNT(wo.visit_task_id) INTO l_open_wo_count FROM ahl_workorders wo, ahl_visit_tasks_b task
WHERE
task.visit_task_id = wo.visit_task_id
and task.instance_id = p_instance_id;
SELECT COUNT(wip_entity_id) INTO l_wipEntityIds FROM wip_discrete_jobs
where maintenance_object_id = p_instance_id;
FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Calling AHL_CMP_PVT.Update_EAM_Workorders');
AHL_CMP_PVT.Update_EAM_Workorders(
p_api_version => l_api_version,
p_init_msg_list => Fnd_Api.g_false,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_wip_entity_id => l_wip_entity_tbl(i),
p_instance_id => l_new_instance_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
/********************End of Update of EAM Workorders**************************************/
IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
IF (l_log_statement >= l_log_current_level) THEN
FND_LOG.string(l_log_statement, L_DEBUG_KEY, 'call to Update_EAM_Workorders errored out');
SELECT COUNT(wip_entity_id) INTO l_wipEntityIds FROM wip_discrete_jobs
where maintenance_object_id = p_instance_id;
FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Update tasks with the instance');
UPDATE ahl_visit_tasks_b SET instance_id = l_locator_inst_id WHERE
visit_id = (SELECT visit_id FROM ahl_visit_tasks_b
WHERE repair_batch_name = p_repair_batch_name)
AND instance_id = l_new_instance_id;
FND_LOG.string(l_log_statement, L_DEBUG_KEY,'Calling AHL_CMP_PVT.Update_EAM_Workorders');
AHL_CMP_PVT.Update_EAM_Workorders(
p_api_version => l_api_version,
p_init_msg_list => Fnd_Api.g_false,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_wip_entity_id => l_wip_entity_tbl(i),
p_instance_id => l_locator_inst_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
/********************End of Update of EAM Workorders**************************************/
IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
IF (l_log_statement >= l_log_current_level) THEN
FND_LOG.string(l_log_statement, L_DEBUG_KEY, 'call to Update_EAM_Workorders errored out');
FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_DELETED');
SELECT c.location_id,
c.inventory_item_id,
c.inv_master_organization_id,
c.inv_organization_id,
c.quantity,
c.unit_of_measure,
c.lot_number,
c.install_date,
c.inventory_revision,
p.party_id,
p.party_source_table,
p.instance_party_id
FROM csi_item_instances c, csi_i_parties p
WHERE c.instance_id = c_csi_instance_id
AND p.instance_id = c.instance_id
AND p.relationship_type_code = 'OWNER'
AND trunc(nvl(c.active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(c.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT party_account_id
FROM csi_ip_accounts
WHERE relationship_type_code = 'OWNER'
AND instance_party_id = c_instance_party_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
PROCEDURE Update_EAM_Workorders
(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_wip_entity_id IN NUMBER,
p_instance_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Cursor to get the workorder details for a given WIP entity ID
CURSOR get_workorder_dtls_csr(p_wip_entity_id_csr NUMBER) IS
SELECT wo.workorder_id,
task.inventory_item_id, visit.organization_id,
wo.status_code
FROM
ahl_workorders wo, ahl_visit_tasks_b task,
ahl_visits_b visit
WHERE
wo.wip_entity_id = p_wip_entity_id_csr
AND task.visit_task_id = wo.visit_task_id
AND visit.visit_id = task.visit_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_EAM_Workorders';
AHL_DEBUG_PUB.debug('Entering Update_EAM_Workorders API with WIP Entity ID: '|| p_wip_entity_id ||
' Instance ID: ' || p_instance_id);
l_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
p_procedure_name => 'Update_EAM_Workorders',
p_error_text => SQLERRM);
END Update_EAM_Workorders;
SELECT serial_number_control_code
FROM MTL_SYSTEM_ITEMS_KFV
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id ;
SELECT workorder_name, visit_task_id
FROM AHL_WORKORDERS
WHERE status_code IN ('1', '3', '6', '17', '19', '20' ) -- unreleased, released, on-hold, draft, parts hold, pending QA (Open WO Statuses)
AND visit_task_id IN
(SELECT visit_task_id
FROM AHL_VISIT_TASKS_B vt
WHERE vt.cost_parent_id IS NOT NULL
AND vt.instance_id = c_ins_id
START WITH vt.visit_task_id = c_repair_batch_id
CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id );
SELECT wo.workorder_id,wo.workorder_name,wo.wip_entity_id,wdj.REBUILD_ITEM_ID,wdj.MAINTENANCE_OBJECT_ID
FROM AHL_WORKORDERS WO,
WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
and wo.status_code NOT IN ('7','12','17','22' ) -- Cancelled,Closed,Draft and Deleted
AND VISIT_TASK_ID IN
( SELECT vt.visit_task_id
FROM AHL_VISIT_TASKS_B vt
WHERE vt.cost_parent_id IS NOT NULL
AND nvl(vt.return_to_supply_flag,'N') = 'Y'
AND vt.instance_id = nvl(c_ins_id,vt.instance_id)
START WITH vt.visit_task_id = (SELECT visit_task_id
FROM AHL_VISIT_TASKS_B
WHERE REPAIR_BATCH_NAME = C_REPAIR_BATCH)
CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id );
SELECT RESERVATION_ID,
DEMAND_SOURCE_LINE_DETAIL,
DEMAND_SOURCE_HEADER_ID,
SUPPLY_SOURCE_HEADER_ID,
PRIMARY_RESERVATION_QUANTITY
FROM MTL_RESERVATIONS
WHERE SUPPLY_SOURCE_TYPE_ID = 5
AND DEMAND_SOURCE_TYPE_ID = 5
AND SUPPLY_SOURCE_HEADER_ID = c_wip_entity_id ;
SELECT vt.visit_task_id
FROM AHL_VISIT_TASKS_B vt
WHERE vt.cost_parent_id IS NOT NULL
AND vt.originating_task_id IS NULL
AND vt.task_type_code = 'SUMMARY'
AND vt.status_code = 'PLANNING'
AND vt.instance_id = c_ins_id
START WITH vt.visit_task_id = ( SELECT visit_task_id
FROM AHL_VISIT_TASKS_B
WHERE repair_batch_name = c_repair_batch )
CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id;
UPDATE AHL_VISIT_TASKS_B
SET quantity = l_remaining_qty
WHERE visit_task_id = l_visit_task_id ;
FND_LOG.string(l_log_statement, l_debug, 'Workorder - ' || l_workorder_name || ' is updated with Quantity ::' || l_remaining_qty );
UPDATE AHL_VISIT_TASKS_B
SET RET_UNSERVICEABLE_QTY = NVL(RET_UNSERVICEABLE_QTY,0) + P_X_AHL_MTLRTN_TBL(I).RETURN_QTY
WHERE visit_task_id = p_x_ahl_mtlrtn_tbl(i).Repair_Batch_Id;
UPDATE AHL_VISIT_TASKS_B
SET RET_SERVICEABLE_QTY = NVL(RET_SERVICEABLE_QTY,0) + P_X_AHL_MTLRTN_TBL(I).RETURN_QTY
WHERE visit_task_id = p_x_ahl_mtlrtn_tbl(i).Repair_Batch_Id;
AHL_PRD_PARTS_CHANGE_PVT.Update_Part_Condition
(
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_instance_id => p_x_ahl_mtlrtn_tbl(i).Instance_Id,
p_instance_condition_id => p_x_ahl_mtlrtn_tbl(i).Return_Condition,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
FND_LOG.string(l_log_statement, l_debug, 'after PartsChange Part-Condition-Update api:ret status=['||x_return_status||']');
SELECT RESERVABLE_TYPE
INTO l_reservable
FROM MTL_SECONDARY_INVENTORIES
WHERE ORGANIZATION_ID = p_x_ahl_mtlrtn_tbl(i).Organization_Id
AND SECONDARY_INVENTORY_NAME = p_x_ahl_mtlrtn_tbl(i).Dst_SubInv_Name ;
fnd_log.string(l_log_statement, l_debug,'Calling Delete_Reservation API...');
AHL_MM_RESERVATIONS_PVT.DELETE_RESERVATION
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_module_type => null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_reservation_id => l_resv_rec.reservation_id
);
FND_LOG.string(l_log_statement, l_debug, 'After Delete_Reservation API...ret_status['||x_return_status||']');
FND_LOG.string(l_log_statement, l_debug, 'Calling AHL_VWP_TASKS_PVT.Delete_Task - ' || 'Summary Task ID: ' || l_visit_task_id );
AHL_VWP_TASKS_PVT.Delete_Task
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_module_type => NULL,
p_Visit_Task_Id => l_visit_task_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FND_LOG.string(l_log_statement, l_debug, 'After Delete_Task API...ret_status['||x_return_status||']');