[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Mtl_Txn_Row
(
p_x_ahl_mtltxn_rec IN OUT NOCOPY Ahl_Mtltxn_Rec_Type,
p_material_Transaction_Id IN NUMBER,
p_nonroutine_workorder_Id IN NUMBER,
p_prim_uom_qty IN NUMBER:=0,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_ahl_mtl_txn_id OUT NOCOPY NUMBER
);
PROCEDURE Insert_Sch_Mtl_Row
(
p_mtl_txn_Rec IN Ahl_Mtltxn_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_ahl_sch_mtl_id OUT NOCOPY NUMBER
);
PROCEDURE INSERT_MTL_TXN_INTF
(
p_x_ahl_mtl_txn_rec IN OUT NOCOPY AHL_MTLTXN_REC_TYPE,
p_eam_item_type_id IN NUMBER,
p_x_txn_hdr_id IN OUT NOCOPY NUMBER,
p_x_txn_intf_id IN OUT NOCOPY NUMBER,
p_reservation_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
);
SELECT Inventory_Item_Id
FROM MTL_SYSTEM_ITEMS_KFV
WHERE Concatenated_Segments = p_item_name
AND Organization_Id = p_org_id
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 INVENTORY_LOCATION_ID
FROM MTL_ITEM_LOCATIONS_KFV
WHERE ORGANIZATION_ID = p_org_Id
AND CONCATENATED_SEGMENTS = p_location_name
AND ((START_DATE_ACTIVE IS NULL) OR (START_DATE_ACTIVE <= SYSDATE))
AND ((END_DATE_ACTIVE IS NULL) OR (END_DATE_ACTIVE >= SYSDATE));*/
SELECT MIL.INVENTORY_LOCATION_ID, MIL_kfv.CONCATENATED_SEGMENTS
FROM MTL_ITEM_LOCATIONS_KFV MIL_kfv, MTL_ITEM_LOCATIONS MIL
WHERE MIL_kfv.INVENTORY_LOCATION_ID = MIL.INVENTORY_LOCATION_ID
AND MIL.ORGANIZATION_ID = p_org_Id
AND decode(MIL.segment19, null, MIL_kfv.concatenated_segments,
INV_PROJECT.GET_LOCSEGS(MIL_kfv.concatenated_segments) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
|| INV_ProjectLocator_PUB.get_project_number(MIL.segment19) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
|| INV_ProjectLocator_PUB.get_task_number(MIL.segment20)) = p_location_name
AND ((MIL.START_DATE_ACTIVE IS NULL) OR (MIL.START_DATE_ACTIVE <= SYSDATE))
AND ((MIL.END_DATE_ACTIVE IS NULL) OR (MIL.END_DATE_ACTIVE >= SYSDATE));
SELECT REASON_ID
FROM MTL_TRANSACTION_REASONS
WHERE REASON_NAME = p_reason_Name
AND (DISABLE_DATE IS NULL OR DISABLE_DATE > SYSDATE);
SELECT Transaction_Type_Id
FROM MTL_TRANSACTION_TYPES
WHERE TRANSACTION_TYPE_NAME = p_Transaction_Type_Name
AND (DISABLE_DATE IS NULL OR DISABLE_DATE > SYSDATE);
SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES_VL
WHERE MEANING = p_Lookup_Meaning
AND LOOKUP_TYPE = C_REQUEST_PROBLEM_CODE
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 UOM_CODE
FROM MTL_UNITS_OF_MEASURE
WHERE UNIT_OF_MEASURE = p_Uom_Desc;
SELECT A.WIP_ENTITY_ID, C.ORGANIZATION_ID
FROM AHL_WORKORDERS A, AHL_VISIT_TASKS_B B, AHL_VISITS_B C
WHERE A.WORKORDER_ID = p_wo_id
AND B.VISIT_TASK_ID = A.VISIT_TASK_ID
AND C.VISIT_ID = B.VISIT_ID;
SELECT WORKORDER_OPERATION_ID
FROM AHL_WORKORDER_OPERATIONS
WHERE WORKORDER_ID = p_wo_id
AND OPERATION_SEQUENCE_NUM = p_oper_seq;
SELECT STATUS_ID
FROM MTL_MATERIAL_STATUSES
WHERE STATUS_CODE= RTRIM(LTRIM(p_condition_desc))
AND ENABLED_FLAG = 1;
SELECT PERSON_ID
FROM PER_PEOPLE_F
WHERE FULL_NAME = p_recepient_name
AND SYSDATE BETWEEN NVL(EFFECTIVE_START_DATE,SYSDATE) AND
NVL(EFFECTIVE_END_DATE,SYSDATE);
SELECT A.employee_id
FROM FND_USER A
WHERE USER_ID=FND_GLOBAL.USER_ID;
SELECT DISPOSITION_ID
FROM AHL_PRD_DISPOSITIONS_B A
WHERE
A.WORKORDER_ID = C_WORKORDER_ID AND
A.DISPOSITION_ID = C_DISP_ID;
SELECT
A.JOB_NUMBER,
A.WORKORDER_ID,
A.ORGANIZATION_ID,
A.ORGANIZATION_NAME,
A.JOB_STATUS_CODE,
A.JOB_STATUS_MEANING,
A.WIP_ENTITY_ID
FROM
AHL_SEARCH_WORKORDERS_V A
--AHL_VISITS_B V,
--AHL_VISIT_TASKS_B VT,
--INV_ORGANIZATION_NAME_V ORG,
--FND_LOOKUP_VALUES WO_STS
WHERE
A.JOB_NUMBER = C_WORKORDER_NAME
AND A.JOB_STATUS_CODE NOT IN ('17', '22');
SELECT A.job_number,
A.workorder_id,
A.organization_id,
A.Organization_name,
A.JOB_STATUS_CODE,
A.job_status_meaning,
A.wip_entity_id
FROM AHL_SEARCH_WORKORDERS_V A
WHERE A.workorder_id=C_WORKORDER_ID;
AHL_DEBUG_PUB.debug('after the Wip entity select['||p_x_ahl_mtltxn_rec.Wip_Entity_Id||','||p_x_ahl_mtltxn_rec.Organization_Id||']');
AHL_DEBUG_PUB.debug('selecting woopid for['||p_x_ahl_mtltxn_rec.Workorder_Id||','||p_x_ahl_mtltxn_rec.Operation_Seq_num||']');
AHL_DEBUG_PUB.debug('after the woop id select');
AHL_DEBUG_PUB.debug('after the txn type select');
AHL_DEBUG_PUB.debug('after the item id select:'||to_char(p_x_ahl_mtltxn_rec.Inventory_Item_Id)||'.');
AHL_DEBUG_PUB.debug('after the locator select');
AHL_DEBUG_PUB.debug('after the reason name select');
AHL_DEBUG_PUB.debug('after problem code select');
AHL_DEBUG_PUB.debug('after the CONDITION select['||p_x_ahl_mtltxn_rec.CONDITION||']');
AHL_DEBUG_PUB.debug('after UOM select');
SELECT intf.ERROR_EXPLANATION ,intf.ERROR_CODE, kfv.concatenated_segments,
WO.workorder_name
--FROM MTL_MATERIAL_TRANSACTIONS_TEMP
FROM MTL_TRANSACTIONS_INTERFACE INTF, mtl_system_items_kfv kfv,
ahl_workorders WO
--WHERE TRANSACTION_TEMP_ID = p_txn_Id;
SELECT intf.ERROR_EXPLANATION, intf.ERROR_CODE, kfv.concatenated_segments,
WO.workorder_name
FROM MTL_TRANSACTIONS_INTERFACE INTF, mtl_system_items_kfv kfv,
ahl_workorders WO
WHERE TRANSACTION_HEADER_ID = p_hdr_txn_Id
AND intf.inventory_item_id = p_inventory_item_id
AND wo.workorder_id = p_workorder_id
AND intf.inventory_item_id = kfv.inventory_item_id
AND intf.organization_id = kfv.organization_id
AND WO.wip_entity_id = intf.transaction_source_id
AND intf.ERROR_CODE IS NOT NULL
AND ROWNUM < 2;
SELECT COMPLETED_QUANTITY, UOM, object_version_number
FROM AHL_SCHEDULE_MATERIALS A
WHERE ORGANIZATION_ID = p_org_id
AND A.WORKORDER_OPERATION_ID =p_workorder_op_id
AND A.INVENTORY_ITEM_ID = p_item_Id
AND A.MATERIAL_REQUEST_TYPE <> 'FORECAST'
--AND A.status='ACTIVE'
--Added for FP ER# 6310725.
AND A.status IN ('ACTIVE','HISTORY', 'IN-SERVICE')
FOR UPDATE OF COMPLETED_QUANTITY NOWAIT;
SELECT 'x'
FROM AHL_SCHEDULE_MATERIALS A
WHERE ORGANIZATION_ID = p_org_id
AND A.WORKORDER_OPERATION_ID =p_workorder_op_id
AND A.INVENTORY_ITEM_ID = p_item_Id
AND A.MATERIAL_REQUEST_TYPE <> 'FORECAST'
--AND A.status='ACTIVE';
SELECT 'x'
FROM AHL_PRD_DISPOSITIONS_B DISP, AHL_WORKORDERS WO, CSI_ITEM_INSTANCES CSI
WHERE
DISP.INSTANCE_ID = CSI.INSTANCE_ID AND
DISP.WORKORDER_ID = WO.WORKORDER_ID AND
WO.WIP_ENTITY_ID = CSI.WIP_JOB_ID AND
CSI.LOCATION_TYPE_CODE NOT IN ('PO', 'IN-TRANSIT', 'PROJECT', 'INVENTORY') AND
TRUNC(SYSDATE) BETWEEN TRUNC(NVL(CSI.ACTIVE_START_DATE, SYSDATE)) AND TRUNC(NVL(CSI.ACTIVE_END_DATE, SYSDATE)) AND
DISP.INSTANCE_ID = p_item_instance_id AND
DISP.WORKORDER_ID = p_workorder_id AND
CSI.QUANTITY > 0;
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,
AHL_VISIT_TASKS_B VTS,
AHL_WORKORDERS WO1
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 WO.VISIT_ID = WO1.VISIT_ID
AND wo1.workorder_id = C_WORKORDER_ID
AND VTS.VISIT_TASK_ID = WO.VISIT_TASK_ID
AND NVL(VTS.RETURN_TO_SUPPLY_FLAG,'N') = 'Y'
AND VTS.INSTANCE_ID = C_INS_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 ;
G_nonserial_ii_tbl.delete;
Insert_Sch_Mtl_Row( p_mtl_txn_rec => p_x_ahl_mtltxn_tbl(i),
x_return_status => x_Return_Status,
x_msg_count => x_Msg_Count,
x_msg_data => x_Msg_Data,
x_ahl_sch_mtl_id => l_sch_Mtl_Id);
AHL_DEBUG_PUB.debug('after Sch_Mtl insert api');
AHL_DEBUG_PUB.debug('Calling Insert_Txn_Intf...');
Insert_Mtl_Txn_Intf(p_x_ahl_mtl_txn_rec => p_x_ahl_mtltxn_tbl(i),
p_eam_item_type_id => l_eam_item_type_id_tbl(i),
p_x_txn_Hdr_Id => l_txn_Header_Id,
p_x_txn_intf_Id => l_txn_tmp_Id,
p_reservation_flag => l_reservation_flag, -- added for R12.
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 PRIMARY_UOM_CODE INTO l_uom_code
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = p_x_ahl_mtltxn_tbl(i).Inventory_Item_Id
AND ORGANIZATION_ID = p_x_ahl_mtltxn_tbl(i).Organization_Id;
UPDATE AHL_SCHEDULE_MATERIALS
SET completed_quantity = nvl(completed_quantity,0) + l_quantity,
object_version_number = l_object_version_number + 1
WHERE CURRENT OF Sch_Mtl_Cur;
UPDATE AHL_SCHEDULE_MATERIALS
SET completed_quantity = nvl(completed_quantity,0) - l_quantity,
object_version_number = l_object_version_number + 1
WHERE CURRENT OF Sch_Mtl_Cur;
Insert_Mtl_Txn_Row(p_x_ahl_mtltxn_rec => p_x_ahl_mtltxn_tbl(i),
p_material_Transaction_Id => NULL,
p_nonroutine_workorder_Id => l_nonrtn_wo_id,
p_prim_uom_qty =>L_QUANTITY,
x_return_status => x_Return_Status,
x_msg_count => x_Msg_Count,
x_msg_data => x_Msg_Data,
x_ahl_mtl_txn_id => l_x_Mtl_Txn_Id);
AHL_DEBUG_PUB.debug('after mtl_Txn insert api');
AHL_DEBUG_PUB.debug('after mtl_Txn insert api call ret status=['||x_return_status||']');
AHL_DEBUG_PUB.debug('after mtl_Txn insert api call msg=['||x_msg_data||']');
AHL_PRD_PARTS_CHANGE_PVT.Update_Material_Return
(
p_return_mtl_txn_id => l_x_Mtl_Txn_Id,
p_workorder_id => p_x_ahl_mtltxn_tbl(i).workorder_id,
p_Item_Instance_Id => l_instance_id_tbl(i),
x_return_status => x_return_status
);
AHL_DEBUG_PUB.debug('after PartsChange Update Material Return api:ret status=['||x_return_status||']');
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 => l_instance_id_tbl(i),
p_instance_condition_id => p_x_ahl_mtltxn_tbl(i).Condition,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
AHL_DEBUG_PUB.debug('after PartsChange Update_Part_Condition api:ret status=['||x_return_status||']');
SELECT RESERVABLE_TYPE
INTO l_reservable
FROM MTL_SECONDARY_INVENTORIES
WHERE ORGANIZATION_ID = p_x_ahl_mtltxn_tbl(i).Organization_Id
AND SECONDARY_INVENTORY_NAME = p_x_ahl_mtltxn_tbl(i).Subinventory_Name;
fnd_log.string(G_LEVEL_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(G_LEVEL_STATEMENT, l_debug, 'After Delete_Reservation API...ret_status['||x_return_status||']');
UPDATE AHL_WORKORDER_MTL_TXNS
SET NON_ROUTINE_WORKORDER_ID = l_x_sr_rec_tbl(j).Nonroutine_wo_id,
-- Adithya added for bug# 6995541
CS_INCIDENT_ID = l_x_sr_rec_tbl(j).Incident_id
WHERE WORKORDER_MTL_TXN_ID = l_sr_mtl_id_map_tbl(j);
This procedure will insert a record in the AHL_WO_MTL_TXNS table.
**********************************************************/
PROCEDURE Insert_Mtl_Txn_Row(
p_x_ahl_mtltxn_rec IN OUT NOCOPY Ahl_Mtltxn_Rec_Type,
p_material_Transaction_Id IN NUMBER,
p_nonroutine_workorder_Id IN NUMBER,
p_prim_uom_qty IN NUMBER:=0,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_ahl_mtl_txn_id OUT NOCOPY NUMBER)
IS
l_x_row_id VARCHAR2(240);
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);
AHL_DEBUG_PUB.debug('Entered Insert_Mtl_Txn_Row, p_x_ahl_mtltxn_rec.Recepient_id='||p_x_ahl_mtltxn_rec.Recepient_id);
AHL_WORKORDER_MTL_TXNS_PKG.INSERT_ROW(
X_ROWID => l_x_row_id,
X_WORKORDER_MTL_TXN_ID => x_ahl_mtl_txn_id,
X_OBJECT_VERSION_NUMBER => 1,
X_WORKORDER_OPERATION_ID => p_x_ahl_mtltxn_rec.workorder_Operation_Id,
X_MATERIAL_TRANSACTION_ID => p_material_Transaction_Id,
X_COLLECTION_ID => p_x_ahl_mtltxn_rec.Qa_Collection_Id,
X_STATUS_ID => p_x_ahl_mtltxn_rec.Condition,
X_NON_ROUTINE_WORKORDER_ID => p_nonroutine_workorder_Id,
X_ORGANIZATION_ID => p_x_ahl_mtltxn_rec.Organization_Id,
X_INVENTORY_ITEM_ID => p_x_ahl_mtltxn_rec.Inventory_Item_Id,
X_REVISION => p_x_ahl_mtltxn_rec.Revision,
X_LOT_NUMBER => p_x_ahl_mtltxn_rec.Lot_Number,
X_SERIAL_NUMBER => p_x_ahl_mtltxn_rec.Serial_Number,
X_LOCATOR_ID => p_x_ahl_mtltxn_rec.Locator_Id,
X_SUBINVENTORY_CODE => p_x_ahl_mtltxn_rec.Subinventory_Name,
X_QUANTITY => p_x_ahl_mtltxn_rec.Quantity,
X_TRANSACTION_TYPE_ID => p_x_ahl_mtltxn_rec.Transaction_Type_Id,
X_UOM => p_x_ahl_mtltxn_rec.Uom,
X_RECEPIENT_ID => p_x_ahl_mtltxn_rec.Recepient_id,
X_PRIMARY_UOM_QUANTITY => P_PRIM_UOM_QTY,
X_INSTANCE_ID => p_x_ahl_mtltxn_rec.Item_Instance_ID,
X_TRANSACTION_DATE => p_x_ahl_mtltxn_rec.transaction_date,
X_ATTRIBUTE_CATEGORY => NULL ,
X_ATTRIBUTE1 => NULL ,
X_ATTRIBUTE2 => NULL ,
X_ATTRIBUTE3 => NULL ,
X_ATTRIBUTE4 => NULL ,
X_ATTRIBUTE5 => NULL ,
X_ATTRIBUTE6 => NULL ,
X_ATTRIBUTE7 => NULL ,
X_ATTRIBUTE8 => NULL ,
X_ATTRIBUTE9 => NULL ,
X_ATTRIBUTE10 => NULL ,
X_ATTRIBUTE11 => NULL ,
X_ATTRIBUTE12 => NULL ,
X_ATTRIBUTE13 => NULL ,
X_ATTRIBUTE14 => NULL ,
X_ATTRIBUTE15 => NULL ,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
select AHL_WORKORDER_MTL_TXNS_S.currval into p_x_ahl_mtltxn_rec.Ahl_mtltxn_Id from dual;
AHL_DEBUG_PUB.debug('Exception inserting into mtl_txn' || SQLCODE);
END Insert_Mtl_Txn_Row;
SELECT COUNT(A.workorder_mtl_txn_id)
FROM AHL_WORKORDER_MTL_TXNS A,
AHL_WORKORDER_OPERATIONS_V B,
AHL_SCHEDULE_MATERIALS C
WHERE B.WORKORDER_ID=C_WRK_ID
AND B.WORKORDER_OPERATION_ID=A.WORKORDER_OPERATION_ID
AND B.WORKORDER_OPERATION_ID=C.WORKORDER_OPERATION_ID
AND A.INVENTORY_ITEM_ID=C.INVENTORY_ITEM_ID
AND C.STATUS='ACTIVE'
AND A.INVENTORY_ITEM_ID=C_INV_ITEM_ID
AND A.SERIAL_NUMBER=C_SERIAL_NO
AND A.REVISION=C_REVISION
AND A.workorder_mtl_txn_id<>C_WO_MTLTXN_ID;
SELECT location_id
FROM hr_all_organization_units
WHERE organization_id = p_org_id;
SELECT INSTANCE_ID
FROM CSI_ITEM_INSTANCES CII
WHERE INVENTORY_ITEM_ID = p_item_id
AND WIP_JOB_ID = p_job_id
AND SERIAL_NUMBER = p_serial_num
AND ACTIVE_START_DATE <= SYSDATE
AND ((ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE > SYSDATE))
--AND LOCATION_TYPE_CODE = 'WIP'
--AND LOCATION_ID = p_wip_location
AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
--AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE,SYSDATE) AND NVL(ACTIVE_END_DATE,SYSDATE));
SELECT INSTANCE_ID
FROM CSI_ITEM_INSTANCES CII
WHERE INVENTORY_ITEM_ID = p_item_id
AND SERIAL_NUMBER = p_serial_num
AND ACTIVE_START_DATE <= SYSDATE
AND ((ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE > SYSDATE))
AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE,SYSDATE) AND NVL(ACTIVE_END_DATE,SYSDATE));
SELECT 1
FROM AHL_WORKORDER_OPERATIONS
WHERE WORKORDER_OPERATION_ID =p_woop_id
AND WORKORDER_ID =p_wo_id
AND OPERATION_SEQUENCE_NUM = p_op_seq;
SELECT A.STATUS_CODE, C.Visit_id, C.Inv_Locator_Id, C.project_id, B.project_task_id,
LOC.subinventory_code
FROM AHL_WORKORDERS A, AHL_VISIT_TASKS_B B, AHL_VISITS_B C, MTL_ITEM_LOCATIONS LOC
WHERE A.WIP_ENTITY_ID = p_wipjob
AND A.WORKORDER_ID = p_wo_id
AND B.VISIT_TASK_ID = A.VISIT_TASK_ID
AND C.VISIT_ID = B.VISIT_ID
AND C.ORGANIZATION_ID = p_org_id
AND C.ORGANIZATION_ID = LOC.ORGANIZATION_ID(+)
AND C.INV_LOCATOR_ID = LOC.INVENTORY_LOCATION_ID(+);
SELECT SERIAL_NUMBER_CONTROL_CODE, LOT_CONTROL_CODE, REVISION_QTY_CONTROL_CODE,
LOCATION_CONTROL_CODE,EAM_ITEM_TYPE, 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
;--AND SUBINVENTORY_CODE = p_subinv;
SELECT 1
FROM MTL_ITEM_REVISIONS
WHERE ORGANIZATION_ID = p_org_id
AND INVENTORY_ITEM_ID = p_item
AND REVISION = p_revision;
SELECT 1
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_reason;
SELECT STATUS_ID
FROM MTL_MATERIAL_STATUSES
WHERE STATUS_ID = p_Condition;
SELECT 1
FROM FND_LOOKUP_VALUES FL
WHERE
FL.LOOKUP_TYPE = 'REQUEST_PROBLEM_CODE' AND
FL.LOOKUP_CODE = p_problem_code AND
FL.ENABLED_FLAG = 'Y' AND
FL.LANGUAGE = USERENV('LANG') AND
TRUNC(SYSDATE) BETWEEN TRUNC(NVL(FL.START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(FL.END_DATE_ACTIVE,SYSDATE)) AND
(
(
NOT EXISTS
(
SELECT 1
FROM CS_SR_PROB_CODE_MAPPING_DETAIL
WHERE
INCIDENT_TYPE_ID = FND_PROFILE.VALUE('AHL_PRD_SR_TYPE') AND
TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))
)
)
OR
(
EXISTS
(
SELECT 1
FROM CS_SR_PROB_CODE_MAPPING_DETAIL
WHERE
INCIDENT_TYPE_ID = FND_PROFILE.VALUE('AHL_PRD_SR_TYPE') AND
PROBLEM_CODE = p_problem_code AND
TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))
)
)
);
SELECT A.scheduled_start_date
FROM AHL_WORKORDER_OPERATIONS_V A ,AHL_SCHEDULE_MATERIALS B
WHERE A.WORKORDER_OPERATION_ID=C_WORKORDER_OPERATION_ID
AND A.WORKORDER_ID=C_WORKORDER_ID
AND A.WORKORDER_OPERATION_ID=B.WORKORDER_OPERATION_ID
AND B.INVENTORY_ITEM_ID=C_INV_ITEM_ID
AND A.OPERATION_SEQUENCE_NUM=B.OPERATION_SEQUENCE;
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 default_pull_supply_subinv, default_pull_supply_locator_id
FROM wip_parameters
WHERE organization_id = p_org_id;
SELECT DATE_RELEASED FROM WIP_DISCRETE_JOBS
WHERE WIP_ENTITY_ID = p_wip_entity_id;
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 disposition_id, collection_id, condition_code, condition_id
FROM AHL_MTL_RET_DISPOSITIONS_V a
WHERE WORKORDER_ID = p_workorder_id
AND a.disposition_id = p_disposition_id
AND INVENTORY_ITEM_ID = p_inventory_item_id
--AND ORGANIZATION_ID = p_org_id
AND nvl(SERIAL_NUMBER,'x')=NVL(p_serial_num,nvl(SERIAL_NUMBER,'x'))
AND nvl(LOT_NUMBER,'x')=NVL(p_lotNumber,nvl(lot_number,'x'))
AND nvl(ITEM_REVISION,'x')=NVL(p_revision,nvl(ITEM_REVISION,'x'));
SELECT meaning
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
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 lookup_code
FROM pa_lookups
WHERE MEANING = c_service_type
AND LOOKUP_TYPE = 'SERVICE TYPE'
AND ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE - 1))
AND TRUNC(NVL(END_DATE_ACTIVE, SYSDATE));
SELECT sum(abs(mmt.primary_quantity))
FROM mtl_material_transactions mmt, MTL_TRANSACTION_LOT_NUMBERS mtln
WHERE mmt.TRANSACTION_ID = mtln.TRANSACTION_ID
AND mmt.TRANSACTION_SOURCE_ID = p_wip_entity_id
AND mmt.INVENTORY_ITEM_ID = p_inventory_item_id
AND mmt.Transaction_Type_Id = p_type_id
AND nvl(mmt.revision, '1') = nvl(p_item_revision, '1')
AND mtln.lot_number = p_lot_number
AND NOT EXISTS (select 'x' from csi_transactions where INV_MATERIAL_TRANSACTION_ID = mmt.TRANSACTION_ID);
SELECT sum(abs(mmt.primary_quantity))
FROM mtl_material_transactions mmt
WHERE mmt.TRANSACTION_SOURCE_ID = p_wip_entity_id
AND mmt.INVENTORY_ITEM_ID = p_inventory_item_id
AND mmt.Transaction_Type_Id = p_type_id
AND nvl(mmt.revision, '1') = nvl(p_item_revision, '1')
AND NOT EXISTS (select 'x' from csi_transactions where INV_MATERIAL_TRANSACTION_ID = mmt.TRANSACTION_ID);
SELECT 'x'
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inv_item_id
AND serial_number = p_serial_num
AND current_status IN ('1','4'); -- defined, not used and 'out of stores'
select 'x' from qa_plan_transactions_v
where mandatory_collection_flag = 1 and enabled_flag = 1
and plan_id = p_plan_id;
AHL_DEBUG_PUB.debug('selecting wip location id..');
l_sql := 'SELECT INSTANCE_ID, QUANTITY FROM CSI_ITEM_INSTANCES CII ';
l_sql := l_sql || ' AND NOT EXISTS (SELECT null FROM CSI_II_RELATIONSHIPS CIR ';
AHL_DEBUG_PUB.debug('Updated pending qty: nonserial and lot controlled case:k:' || l_pend_qty || ':' || k);
AHL_DEBUG_PUB.debug('Updated pending qty for instance:qty:k:' || G_nonserial_ii_tbl(k).instance_id || ':' || l_pend_qty || ':' || k);
AHL_DEBUG_PUB.debug('Updated pending qty: nonserial and non lot controlled case:k:' || l_pend_qty || ':' || k);
'Updated Pending txns quantity nonserial and not lot case:' || l_pend_qty
);
AHL_DEBUG_PUB.debug('Updated pending qty for instance:qty:k:' || G_nonserial_ii_tbl(k).instance_id || ':' || l_pend_qty || ':' || k);
inserted into interface tables and the API is called to process the transaction.
********************************************************************************/
/* Tamal [R12 APPSPERF fixes]
* R12 Drop 4 - SQL ID: 14400039
* Bug #4918991
* Since the following procedure has no functional footprint at the moment, commenting out the procedure
* Additionally marking the SQL ID as Obsolete in sql_repos...
*/
/*
PROCEDURE INSERT_MTL_TXN_TEMP
(
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_default IN VARCHAR2 := FND_API.G_FALSE,
p_module_type IN VARCHAR2 := NULL,
p_x_ahl_mtltxn_rec IN OUT NOCOPY Ahl_Mtltxn_Rec_Type,
x_txn_Hdr_Id OUT NOCOPY NUMBER,
x_txn_Tmp_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_Process_Flag VARCHAR2(1);
SELECT ACCT_PERIOD_ID from org_acct_periods
where organization_id = p_org_id and open_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(PERIOD_START_DATE) AND TRUNC(SCHEDULE_CLOSE_DATE);
SELECT TRANSACTION_ACTION_ID,TRANSACTION_SOURCE_TYPE_ID
from MTL_TRANSACTION_TYPES
where TRANSACTION_TYPE_ID = p_type_Id;
l_mmtt_rec.LAST_UPDATE_DATE := SYSDATE;
l_mmtt_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID; --p_x_ahl_mtltxn_rec.Last_Updated_By;
l_mmtt_rec.CREATED_BY := FND_GLOBAL.USER_ID; --p_x_ahl_mtltxn_rec.Last_Updated_By;
l_mmtt_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID; --p_x_ahl_mtltxn_rec.Last_Update_Login;
AHL_DEBUG_PUB.debug('inserting the mmtt..');
inv_util.insert_mmtt(p_api_version => 1,
p_mmtt_rec =>l_mmtt_rec,
x_trx_header_id => x_Txn_hdr_id,
x_trx_temp_id => x_Txn_Tmp_Id,
x_return_status => x_return_status,
x_msg_count =>x_msg_count,
x_msg_data => x_msg_data);
AHL_DEBUG_PUB.debug('inserted in mmtt..ret_status['||x_return_status||']');
SELECT Mtl_Material_Transactions_S.nextval
INTO l_Srl_Txn_Tmp_Id
FROM DUAL;
l_mtlt_rec.LAST_UPDATE_DATE := SYSDATE;
l_mtlt_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID; --p_x_ahl_mtltxn_rec.Last_Updated_By;
l_mtlt_rec.CREATED_BY := FND_GLOBAL.USER_ID; --p_x_ahl_mtltxn_rec.Last_Updated_By;
l_mtlt_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID; --p_x_ahl_mtltxn_rec.Last_Update_Login;
l_msnt_rec.LAST_UPDATE_DATE := SYSDATE;
l_msnt_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID; --p_x_ahl_mtltxn_rec.Last_Updated_By;
l_msnt_rec.CREATED_BY := FND_GLOBAL.USER_ID; --p_x_ahl_mtltxn_rec.Last_Updated_By;
l_msnt_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID; --p_x_ahl_mtltxn_rec.Last_Update_Login;
AHL_DEBUG_PUB.debug('inserting in mtlt..');
inv_util.insert_mtlt(p_api_version => 1,
p_mtlt_rec =>l_mtlt_rec,
x_return_status => x_return_status,
x_msg_count =>x_msg_count,
x_msg_data => x_msg_data);
AHL_DEBUG_PUB.debug('inserted in mtlt..ret_status['||x_return_status||']');
AHL_DEBUG_PUB.debug('inserting in msnt..');
inv_util.insert_msnt(p_api_version => 1,
p_msnt_rec =>l_msnt_rec,
x_return_status => x_return_status,
x_msg_count =>x_msg_count,
x_msg_data => x_msg_data);
AHL_DEBUG_PUB.debug('inserted in msnt..ret_status['||x_return_status||']');
l_mtlt_rec.LAST_UPDATE_DATE := SYSDATE;
l_mtlt_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID; --p_x_ahl_mtltxn_rec.Last_Updated_By;
l_mtlt_rec.CREATED_BY := FND_GLOBAL.USER_ID; --p_x_ahl_mtltxn_rec.Last_Updated_By;
l_mtlt_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID; --p_x_ahl_mtltxn_rec.Last_Update_Login;
AHL_DEBUG_PUB.debug('inserting in mtlt..');
inv_util.insert_mtlt(p_api_version => 1,
p_mtlt_rec =>l_mtlt_rec,
x_return_status => x_return_status,
x_msg_count =>x_msg_count,
x_msg_data => x_msg_data);
AHL_DEBUG_PUB.debug('inserted in mtlt..ret_status['||x_return_status||']');
l_msnt_rec.LAST_UPDATE_DATE := SYSDATE;
l_msnt_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID; --p_x_ahl_mtltxn_rec.Last_Updated_By;
l_msnt_rec.CREATED_BY := FND_GLOBAL.USER_ID; --p_x_ahl_mtltxn_rec.Last_Updated_By;
l_msnt_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID; --p_x_ahl_mtltxn_rec.Last_Update_Login;
AHL_DEBUG_PUB.debug('inserting in msnt..');
inv_util.insert_msnt(p_api_version => 1,
p_msnt_rec =>l_msnt_rec,
x_return_status => x_return_status,
x_msg_count =>x_msg_count,
x_msg_data => x_msg_data);
AHL_DEBUG_PUB.debug('inserted in msnt..ret_status['||x_return_status||']');
END INSERT_MTL_TXN_TEMP;
SELECT 1
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = p_Item_Id
AND ORGANIZATION_ID = p_Org_Id
AND COMMS_NL_TRACKABLE_FLAG = 'Y'
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));
This procedure inserts record in the AHL_SCHEDULE_MATERIALS table
TBD to be corrected.
************************************************/
PROCEDURE Insert_Sch_Mtl_Row(
p_mtl_txn_Rec IN Ahl_Mtltxn_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_ahl_sch_mtl_id OUT NOCOPY NUMBER)
IS
Material_Tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type ;
SELECT B.VISIT_ID,C.VISIT_TASK_ID,B.PROJECT_ID,C.PROJECT_TASK_ID
FROM AHL_WORKORDERS A, AHL_VISITS_B B, AHL_VISIT_TASKS_B C
WHERE A.WORKORDER_ID = p_wo_id
AND A.VISIT_TASK_ID = C.VISIT_TASK_ID
AND C.VISIT_ID = B.VISIT_ID;
SELECT DESCRIPTION
FROM MTL_SYSTEM_ITEMS_KFV
WHERE ORGANIZATION_ID = p_org_id
AND INVENTORY_ITEM_ID = p_item_id;
AHL_DEBUG_PUB.debug('Entered Insert_Sch_Mtl_Row');
AHL_DEBUG_PUB.debug('Selected the work order paramters');
AHL_DEBUG_PUB.debug('Selected the Item description');
/* will be updated after wip api is successful - modified for bug fix 5499575.
-- Update Completed quantity.
Update ahl_schedule_materials
set completed_quantity = l_quantity
where scheduled_material_id = Material_Tbl(0).schedule_material_id;
END Insert_Sch_Mtl_Row;
This procedure will insert the interface records.
*********************************************************************/
PROCEDURE INSERT_MTL_TXN_INTF
(
p_x_ahl_mtl_txn_rec IN OUT NOCOPY AHL_MTLTXN_REC_TYPE,
p_eam_item_type_id IN NUMBER,
p_x_txn_hdr_id IN OUT NOCOPY NUMBER,
p_x_txn_intf_id IN OUT NOCOPY NUMBER,
p_reservation_flag IN VARCHAR2,
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_mtl_txn_rec.Lot_Number,
l_lot_expiration_Date,
l_qty,
l_qty,
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_mtl_txn_rec.Serial_Number,
p_x_ahl_mtl_txn_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 ,
WIP_ENTITY_TYPE , OPERATION_SEQ_NUM,
TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_ID,
TRX_SOURCE_LINE_ID,
ATTRIBUTE_CATEGORY, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9,
ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15,
RELIEVE_RESERVATIONS_FLAG,
REASON_ID,
LOC_SEGMENT1, LOC_SEGMENT2,
LOC_SEGMENT3, LOC_SEGMENT4,
LOC_SEGMENT5, LOC_SEGMENT6,
LOC_SEGMENT7, LOC_SEGMENT8,
LOC_SEGMENT9, LOC_SEGMENT10,
LOC_SEGMENT11, LOC_SEGMENT12,
LOC_SEGMENT13, LOC_SEGMENT14,
LOC_SEGMENT15, LOC_SEGMENT16,
LOC_SEGMENT17, LOC_SEGMENT18,
LOC_SEGMENT19, 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_mtl_txn_rec.Inventory_Item_Id,
p_x_ahl_mtl_txn_rec.Organization_Id, l_qty,
l_qty, p_x_ahl_mtl_txn_rec.Uom,
p_x_ahl_mtl_txn_rec.Transaction_Date, p_x_ahl_mtl_txn_rec.Subinventory_Name,
p_x_ahl_mtl_txn_rec.Locator_Id, p_x_ahl_mtl_txn_rec.Transaction_Type_Id,
p_x_ahl_mtl_txn_rec.Revision, p_x_ahl_mtl_txn_rec.Transaction_Reference,
WIP_CONSTANTS.DISCRETE, p_x_ahl_mtl_txn_rec.Operation_Seq_Num,
l_txn_source_type, p_x_ahl_mtl_txn_rec.Wip_Entity_id,
p_x_ahl_mtl_txn_rec.Operation_Seq_Num, -- TRX_SOURCE_LINE_ID (needed for relieving reservations)
p_x_ahl_mtl_txn_rec.Attribute_Category, p_x_ahl_mtl_txn_rec.Attribute1,
p_x_ahl_mtl_txn_rec.Attribute2, p_x_ahl_mtl_txn_rec.Attribute3,
p_x_ahl_mtl_txn_rec.Attribute4, p_x_ahl_mtl_txn_rec.Attribute5,
p_x_ahl_mtl_txn_rec.Attribute6, p_x_ahl_mtl_txn_rec.Attribute7,
p_x_ahl_mtl_txn_rec.Attribute8, p_x_ahl_mtl_txn_rec.Attribute9,
p_x_ahl_mtl_txn_rec.Attribute10, p_x_ahl_mtl_txn_rec.Attribute11,
p_x_ahl_mtl_txn_rec.Attribute12, p_x_ahl_mtl_txn_rec.Attribute13,
p_x_ahl_mtl_txn_rec.Attribute14, p_x_ahl_mtl_txn_rec.Attribute15,
p_reservation_flag, -- relieve reservations flag.
p_x_ahl_mtl_txn_rec.reason_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 sum(nvl(QUANTITY, 0))
INTO l_iss_qty
FROM AHL_WORKORDER_MTL_TXNS
WHERE ORGANIZATION_ID = P_ORG_ID
AND INVENTORY_ITEM_ID = P_ITEM_ID
AND WORKORDER_OPERATION_ID = P_WORKORDER_OP_ID
-- The following line will NOT be needed in the case net quantity issued is to be displayed
AND TRANSACTION_TYPE_ID = 35;
SELECT SUM(nvl(QUANTITY,0))
FROM AHL_WORKORDER_MTL_TXNS A
,AHL_WORKORDER_OPERATIONS_V B
WHERE A.ORGANIZATION_ID = p_org_id
AND A.INVENTORY_ITEM_ID = p_item_id
AND NVL(A.lot_number,'X')=NVL(p_lotnum,NVL(A.lot_number,'X'))
AND NVL(A.revision,'X')=NVL(p_rev,NVL(A.REVISION,'X'))
AND NVL(A.serial_number,'X')=NVL(p_serial_number,NVL(A.SERIAL_NUMBER,'X'))
AND A.TRANSACTION_TYPE_ID=35
AND A.ORGANIZATION_ID = B.organization_id
AND A.workorder_operation_id =B.workorder_operation_id
AND B.workorder_id = p_wid;
SELECT SUM(nvl(QUANTITY,0))
FROM AHL_WORKORDER_MTL_TXNS A ,
-- AHL_WORKORDER_OPERATIONS_V B
-- JKJAIN BUG # 7587902
AHL_WORKORDER_OPERATIONS B
WHERE A.ORGANIZATION_ID = p_org_id
AND A.INVENTORY_ITEM_ID = p_item_id
AND A.TRANSACTION_TYPE_ID =35
-- AND A.ORGANIZATION_ID = B.organization_id
AND A.workorder_operation_id =B.workorder_operation_id
AND B.workorder_id = p_wid;
SELECT SUM(nvl(QUANTITY,0))
FROM AHL_WORKORDER_MTL_TXNS A ,
-- AHL_WORKORDER_OPERATIONS_V B
-- JKJAIN BUG # 7587902
AHL_WORKORDER_OPERATIONS B
WHERE A.ORGANIZATION_ID = p_org_id
AND A.INVENTORY_ITEM_ID = p_item_id
AND A.TRANSACTION_TYPE_ID =43
-- AND A.ORGANIZATION_ID = B.organization_id
AND A.workorder_operation_id =B.workorder_operation_id
AND B.workorder_id = p_wid;
SELECT SUM(TRANSACTION_QUANTITY)
FROM MTL_ONHAND_QUANTITIES
WHERE ORGANIZATION_ID = p_org_id
AND INVENTORY_ITEM_ID = p_item_id;
SELECT
SERIAL_NUMBER_CONTROL_CODE,
LOT_CONTROL_CODE,
REVISION_QTY_CONTROL_CODE
FROM
MTL_SYSTEM_ITEMS_B
WHERE
inventory_item_id=c_inv_item_id
AND ORGANIZATION_ID=c_org_id;
SELECT SUM(NVL(a.primary_uom_qty,0)) INTO l_iss_qty
FROM
AHL_WORKORDER_MTL_TXNS a,
AHL_WORKORDER_OPERATIONS b
WHERE
a.workorder_operation_id=b.workorder_operation_id
AND a.transaction_type_id=35
AND b.workorder_id=p_wo_id
AND a.inventory_item_id=p_item_id
AND a.organization_id=p_org_id
AND nvl(a.serial_number,'X') = nvl(decode(l_serial_flag, 'Y', p_serial_num, a.serial_number),'X')
AND nvl(a.lot_number,'X') = nvl(decode(l_lot_flag, 'Y', p_lot_num, a.lot_number),'X')
AND nvl(a.revision,'X') = nvl(decode(l_rev_flag, 'Y', p_rev_num, a.revision),'X')
AND a.workorder_operation_id=nvl(p_wo_op_id, a.workorder_operation_id);
SELECT SUM(NVL(a.primary_uom_qty,0)) INTO l_rtn_qty
FROM
AHL_WORKORDER_MTL_TXNS a,
AHL_WORKORDER_OPERATIONS b
WHERE
a.workorder_operation_id=b.workorder_operation_id
AND a.transaction_type_id=43
AND b.workorder_id=p_wo_id
AND a.inventory_item_id=p_item_id
AND a.organization_id=p_org_id
AND nvl(a.serial_number,'X') = nvl(decode(l_serial_flag, 'Y', p_serial_num, a.serial_number),'X')
AND nvl(a.lot_number,'X') = nvl(decode(l_lot_flag, 'Y', p_lot_num, a.lot_number),'X')
AND nvl(a.revision,'X') = nvl(decode(l_rev_flag, 'Y', p_rev_num, a.revision),'X')
AND a.workorder_operation_id=nvl(p_wo_op_id, a.workorder_operation_id);
SELECT SUM(NVL(a.net_quantity,0)) INTO l_disp_qty
FROM
AHL_MTL_RET_DISPOSITIONS_V a,
AHL_WORKORDERS b
WHERE
a.workorder_id=b.workorder_id
AND b.master_workorder_flag = 'N'
--AND b.status_code NOT IN ('17' , '22')
AND b.status_code IN ('3','4','20')
AND b.workorder_id=p_wo_id
AND a.inventory_item_id=p_item_id
--AND a.organization_id=p_org_id
AND nvl(a.serial_number,'X') = nvl(decode(l_serial_flag, 'Y', p_serial_num, a.serial_number),'X')
AND nvl(a.lot_number,'X') = nvl(decode(l_lot_flag, 'Y', p_lot_num, a.lot_number),'X')
AND nvl(a.item_revision,'X') = nvl(decode(l_rev_flag, 'Y', p_rev_num, a.item_revision),'X')
AND a.workorder_operation_id=nvl(p_wo_op_id, a.workorder_operation_id);
/*SELECT
D.WORKORDER_ID,
D.WORKORDER_NAME,
V.ORGANIZATION_ID,
D.WORKORDER_OPERATION_ID,
O.OPERATION_SEQUENCE_NUM,
D.ITEM_NUMBER,
D.INVENTORY_ITEM_ID,
D.ITEM_DESC,
D.IMMEDIATE_DISPOSITION_CODE,
D.DISPOSITION_ID,
D.IMMEDIATE_TYPE,
D.CONDITION_CODE,
D.CONDITION_ID,
D.SERIAL_NUMBER,
D.UOM,
UOM.UNIT_OF_MEASURE,
WO_STS.MEANING JOB_STATUS_MEANING,
D.LOT_NUMBER,
D.ITEM_REVISION,
D.COLLECTION_ID,
D.INSTANCE_ID,
WIP.DEFAULT_PULL_SUPPLY_SUBINV,
WIP.DEFAULT_PULL_SUPPLY_LOCATOR_ID,
L.CONCATENATED_SEGMENTS,
--SYSDATE,
D.QUANTITY, --GOES TO THE ISSUE QTY UI
D.NET_QUANTITY, --GOES TO THE RETURN QTY
-- JKJAIN FP ER # 6436303 - start
AHL_PRD_MTLTXN_PVT.GET_WORKORD_NET_QTY(D.WORKORDER_ID,D.INVENTORY_ITEM_ID,V.ORGANIZATION_ID) Wo_Net_Total_Qty,
-- JKJAIN FP ER # 6436303 - end
W.wip_entity_id,
(select inv_locator_id from ahl_visits_b where visit_id = w.visit_id) inv_locator_id
FROM
AHL_MTL_RET_DISPOSITIONS_V D,
AHL_WORKORDERS W,
(SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_JOB_STATUS' AND LANGUAGE= USERENV('LANG')) WO_STS,
AHL_VISITS_B V,
AHL_VISIT_TASKS_B VT,
CS_INCIDENTS_ALL_B C,
WIP_DISCRETE_JOBS WDJ,
(SELECT ORGANIZATION_ID, NAME FROM HR_ALL_ORGANIZATION_UNITS_TL WHERE LANGUAGE = USERENV('LANG')) ORG,
BOM_DEPARTMENTS B,
AHL_WORKORDER_OPERATIONS O,
MTL_UNITS_OF_MEASURE_VL UOM,
WIP_PARAMETERS WIP,
MTL_ITEM_LOCATIONS_KFV L
WHERE
D.WORKORDER_ID = W.WORKORDER_ID AND
W.MASTER_WORKORDER_FLAG = 'N' AND
--W.STATUS_CODE NOT IN ('17', '22', '5','7','12') AND
W.STATUS_CODE IN ('3', '4', '20') AND
W.STATUS_CODE = WO_STS.LOOKUP_CODE AND
O.WORKORDER_OPERATION_ID (+) = D.WORKORDER_OPERATION_ID AND
O.WORKORDER_ID (+) = D.WORKORDER_ID AND
D.UOM = UOM.UOM_CODE AND
WIP.ORGANIZATION_ID = L.ORGANIZATION_ID(+) AND
WIP.DEFAULT_PULL_SUPPLY_LOCATOR_ID = L.INVENTORY_LOCATION_ID(+) AND
WIP.ORGANIZATION_ID = V.ORGANIZATION_ID AND
W.VISIT_TASK_ID = VT.VISIT_TASK_ID AND
V.VISIT_ID = VT.VISIT_ID AND
V.SERVICE_REQUEST_ID = C.INCIDENT_ID(+) AND
WDJ.WIP_ENTITY_ID = W.WIP_ENTITY_ID AND
WDJ.OWNING_DEPARTMENT = B.DEPARTMENT_ID(+) AND
V.ORGANIZATION_ID = ORG.ORGANIZATION_ID AND
D.IMMEDIATE_TYPE LIKE NVL(p_disposition_name, D.IMMEDIATE_TYPE) AND
D.ITEM_NUMBER LIKE NVL(p_item, D.ITEM_NUMBER) AND
NVL(C.INCIDENT_NUMBER,'X') LIKE NVL(p_incident_number, NVL(C.INCIDENT_NUMBER,'X')) AND
W.WORKORDER_NAME LIKE NVL(p_job_number, W.WORKORDER_NAME) AND
UPPER(ORG.NAME) LIKE UPPER(NVL(p_org_name, ORG.NAME)) AND
NVL(WDJ.PRIORITY,0) = NVL(p_priority, NVL(WDJ.PRIORITY,0)) AND
V.VISIT_NUMBER = NVL(p_visit_number, V.VISIT_NUMBER) AND
UPPER(B.DESCRIPTION) LIKE UPPER(NVL(p_dept_name, B.DESCRIPTION)); */
SELECT 'x'
FROM CSI_ITEM_INSTANCES CII
WHERE CII.inventory_item_id = p_INVENTORY_ITEM_ID
AND nvl(cii.inventory_revision,'1') = nvl(p_ITEM_Revision, '1')
AND nvl(cii.lot_number, '1') = nvl(p_lot_number, '1')
AND nvl(cii.serial_number,'1') = nvl(p_serial_number, '1')
AND CII.ACTIVE_START_DATE <= SYSDATE
AND ((CII.ACTIVE_END_DATE IS NULL) OR (CII.ACTIVE_END_DATE > SYSDATE))
AND CII.QUANTITY > 0
AND CII.LOCATION_TYPE_CODE = 'WIP'
AND CII.WIP_JOB_ID = p_wip_entity_id
AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND NVL(CIR.ACTIVE_START_DATE,SYSDATE) <= SYSDATE AND
(CIR.ACTIVE_END_DATE IS NULL OR CIR.ACTIVE_END_DATE > SYSDATE));
SELECT cii.quantity,
(SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND NVL(CIR.ACTIVE_START_DATE,SYSDATE) <= SYSDATE
AND (CIR.ACTIVE_END_DATE IS NULL OR CIR.ACTIVE_END_DATE > SYSDATE)) reln_exists
FROM CSI_ITEM_INSTANCES CII
WHERE CII.inventory_item_id = p_INVENTORY_ITEM_ID
AND nvl(cii.inventory_revision,'1') = nvl(p_ITEM_Revision, '1')
AND nvl(cii.lot_number, '1') = nvl(p_lot_number, '1')
AND nvl(cii.serial_number,'1') = nvl(p_serial_number, '1')
AND CII.ACTIVE_START_DATE <= SYSDATE
AND ((CII.ACTIVE_END_DATE IS NULL) OR (CII.ACTIVE_END_DATE > SYSDATE))
AND CII.QUANTITY > 0
AND CII.LOCATION_TYPE_CODE = 'WIP'
AND CII.WIP_JOB_ID = p_wip_entity_id;
SELECT 'x'
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inv_item_id
AND serial_number = p_serial_num
AND current_status IN ('1','4');
SELECT sum(abs(mmt.primary_quantity))
FROM mtl_material_transactions mmt, MTL_TRANSACTION_LOT_NUMBERS mtln
WHERE mmt.TRANSACTION_ID = mtln.TRANSACTION_ID
AND mmt.TRANSACTION_SOURCE_ID = p_wip_entity_id
AND mmt.INVENTORY_ITEM_ID = p_inventory_item_id
AND mmt.Transaction_Type_Id = p_type_id
AND nvl(mmt.revision, '1') = nvl(p_item_revision, '1')
AND mtln.lot_number = p_lot_number
AND NOT EXISTS (select 'x' from csi_transactions where INV_MATERIAL_TRANSACTION_ID = mmt.TRANSACTION_ID);
SELECT sum(abs(mmt.primary_quantity))
FROM mtl_material_transactions mmt
WHERE mmt.TRANSACTION_SOURCE_ID = p_wip_entity_id
AND mmt.INVENTORY_ITEM_ID = p_inventory_item_id
AND mmt.Transaction_Type_Id = p_type_id
AND nvl(mmt.revision, '1') = nvl(p_item_revision, '1')
AND NOT EXISTS (select 'x' from csi_transactions where INV_MATERIAL_TRANSACTION_ID = mmt.TRANSACTION_ID);
SELECT
D.WORKORDER_ID,
D.WORKORDER_NAME,
V.ORGANIZATION_ID,
D.WORKORDER_OPERATION_ID,
O.OPERATION_SEQUENCE_NUM,
D.ITEM_NUMBER,
D.INVENTORY_ITEM_ID,
D.ITEM_DESC,
D.IMMEDIATE_DISPOSITION_CODE,
D.DISPOSITION_ID,
D.IMMEDIATE_TYPE,
D.CONDITION_CODE,
D.CONDITION_ID,
D.SERIAL_NUMBER,
D.UOM,
UOM.UNIT_OF_MEASURE,
WO_STS.MEANING JOB_STATUS_MEANING,
D.LOT_NUMBER,
D.ITEM_REVISION,
D.COLLECTION_ID,
D.INSTANCE_ID,
(Select csi.instance_number from csi_item_instances csi where csi.instance_id = D.INSTANCE_ID) instance_number,
WP.DEFAULT_PULL_SUPPLY_SUBINV,
WP.DEFAULT_PULL_SUPPLY_LOCATOR_ID,
L.CONCATENATED_SEGMENTS,
--SYSDATE,
D.QUANTITY, --GOES TO THE ISSUE QTY UI
D.NET_QUANTITY, --GOES TO THE RETURN QTY
AHL_PRD_MTLTXN_PVT.GET_WORKORD_NET_QTY(D.WORKORDER_ID,D.INVENTORY_ITEM_ID,V.ORGANIZATION_ID) Wo_Net_Total_Qty,
W.WIP_ENTITY_ID,
V.INV_LOCATOR_ID,
D.PATH_POSITION_ID
FROM
AHL_MTL_RET_DISPOSITIONS_V D,
AHL_VISITS_B V,
AHL_WORKORDER_OPERATIONS O,
MTL_UNITS_OF_MEASURE_VL UOM,
(SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = ''AHL_JOB_STATUS'' AND LANGUAGE= USERENV(''LANG'')) WO_STS,
WIP_PARAMETERS WP,
MTL_ITEM_LOCATIONS_KFV L,
AHL_WORKORDERS W ';
l_mtl_txn_dtls := l_mtl_txn_dtls || ', (SELECT ORGANIZATION_ID, NAME FROM HR_ALL_ORGANIZATION_UNITS_TL WHERE LANGUAGE = USERENV(''LANG'')) ORG';
SELECT instance_number
INTO l_instance_number
FROM CSI_ITEM_INSTANCES
WHERE instance_id = l_disp_rec.instance_id ;
SELECT sum(D.Net_quantity)
INTO l_cnt_disposition
FROM AHL_MTL_RET_DISPOSITIONS_V D
WHERE D.WORKORDER_ID = l_wo_id
AND D.INVENTORY_ITEM_ID = l_inv_item_id
AND nvl(D.Serial_Number, '1') = nvl(l_inv_serial, '1')
AND nvl(D.lot_number, '1') = nvl(l_inv_lot_no, '1')
AND nvl(D.ITEM_Revision, '1') = nvl(l_inv_rev, '1');
/*SELECT DISTINCT
W.WORKORDER_ID,
T.ORGANIZATION_ID,
T.INVENTORY_ITEM_ID,
T.SERIAL_NUMBER,
T.LOT_NUMBER,
T.REVISION,
T.INSTANCE_ID, -- added to fix FP bug# 5172147.
W.WIP_ENTITY_ID, -- added to filter chk_inst_relationship_csr for wip_job_id.
(select inv_locator_id from ahl_visits_b where visit_id = w.visit_id) inv_locator_id
FROM
AHL_WORKORDER_MTL_TXNS T,
MTL_SYSTEM_ITEMS_KFV I,
AHL_WORKORDERS W,
AHL_VISITS_B V,
AHL_VISIT_TASKS_B VT,
CS_INCIDENTS_ALL_B C,
WIP_DISCRETE_JOBS WDJ,
INV_ORGANIZATION_NAME_V ORG,
BOM_DEPARTMENTS B,
AHL_WORKORDER_OPERATIONS O
WHERE
T.ORGANIZATION_ID = V.ORGANIZATION_ID
AND T.WORKORDER_OPERATION_ID = O.WORKORDER_OPERATION_ID
AND W.WORKORDER_ID = O.WORKORDER_ID
AND T.TRANSACTION_TYPE_ID = 35
AND T.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND T.ORGANIZATION_ID = I.ORGANIZATION_ID
AND W.VISIT_TASK_ID = VT.VISIT_TASK_ID
AND VT.VISIT_ID = V.VISIT_ID
AND V.SERVICE_REQUEST_ID = C.INCIDENT_ID(+)
AND WDJ.WIP_ENTITY_ID = W.WIP_ENTITY_ID
AND WDJ.OWNING_DEPARTMENT = B.DEPARTMENT_ID (+)
AND V.ORGANIZATION_ID = ORG.ORGANIZATION_ID
--AND W.STATUS_CODE NOT IN ('5','7','12')
AND W.STATUS_CODE IN ('3', '4', '20')
AND I.ENABLED_FLAG = 'Y'
AND ((I.START_DATE_ACTIVE IS NULL) OR (I.START_DATE_ACTIVE <= SYSDATE))
AND ((I.END_DATE_ACTIVE IS NULL) OR (I.END_DATE_ACTIVE >= SYSDATE))
AND I.CONCATENATED_SEGMENTS LIKE NVL(p_item,I.CONCATENATED_SEGMENTS)
AND UPPER(ORG.ORGANIZATION_NAME) LIKE UPPER(NVL(p_org_name,ORG.ORGANIZATION_NAME))
AND UPPER(B.DESCRIPTION) LIKE UPPER(NVL(p_dept_name,B.DESCRIPTION))
AND UPPER(NVL(C.INCIDENT_NUMBER,'X')) LIKE UPPER(NVL(p_incident_number,NVL(C.INCIDENT_NUMBER,'X')))
AND NVL(WDJ.PRIORITY,0) = NVL(p_priority,NVL(WDJ.PRIORITY,0))
AND V.VISIT_NUMBER = NVL(p_visit_number,V.VISIT_NUMBER)
AND UPPER(W.WORKORDER_NAME) LIKE UPPER(NVL(p_job_number,W.WORKORDER_NAME)); */
SELECT 'T'
FROM AHL_MTL_RET_DISPOSITIONS_V A
WHERE
WORKORDER_ID = c_wid AND
INVENTORY_ITEM_ID = c_itemId AND
--ORGANIZATION_ID = c_org_id AND
NVL(SERIAL_NUMBER, 'X') = NVL(c_sno, NVL(SERIAL_NUMBER, 'X')) AND
NVL(LOT_NUMBER, 'X') = NVL(c_lotNumber, NVL(LOT_NUMBER, 'X')) AND
NVL(ITEM_REVISION, 'X') = NVL(c_rev, NVL(ITEM_REVISION, 'X')) AND
WORKORDER_OPERATION_ID IS NOT NULL;
SELECT DISTINCT
WO.WORKORDER_ID,
WO.WORKORDER_NAME JOB_NUMBER ,
VST.ORGANIZATION_ID,
TXNS.WORKORDER_OPERATION_ID,
WO_OP.OPERATION_SEQUENCE_NUM,
MTL.CONCATENATED_SEGMENTS,
TXNS.INVENTORY_ITEM_ID,
MTL.DESCRIPTION,
TXNS.SERIAL_NUMBER ,
AHL_PRD_MTLTXN_PVT.GET_ISSUED_QTY(TXNS.ORGANIZATION_ID, TXNS.INVENTORY_ITEM_ID,TXNS.WORKORDER_OPERATION_ID) ISSUEQTY,
-- JKJAIN FP ER # 6436303 - start
AHL_PP_MATERIALS_PVT.GET_NET_QTY(TXNS.ORGANIZATION_ID, TXNS.INVENTORY_ITEM_ID,TXNS.WORKORDER_OPERATION_ID) Net_Total_Qty,
-- JKJAIN FP ER # 6436303 - end
TXNS.UOM,
UOM.UNIT_OF_MEASURE,
TXNS.RECEPIENT_ID,
PER.FULL_NAME,
WO_STS.MEANING JOB_STATUS_MEANING,
TXNS.LOT_NUMBER,
TXNS.REVISION,
WP.DEFAULT_PULL_SUPPLY_SUBINV,
WP.DEFAULT_PULL_SUPPLY_LOCATOR_ID,
--MTL_LOC.CONCATENATED_SEGMENTS LOCATOR
--Fix for bug number 5903275
inv_project.GET_LOCSEGS(WP.DEFAULT_PULL_SUPPLY_LOCATOR_ID, WP.organization_id) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
|| INV_ProjectLocator_PUB.get_project_number(MTL_LOC.segment19) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
|| INV_ProjectLocator_PUB.get_task_number(MTL_LOC.segment20) LOCATOR,
--SYSDATE
(select inv_locator_id from ahl_visits_b where visit_id = vst.visit_id) inv_locator_id,
(Select csi.instance_number from csi_item_instances csi where csi.instance_id = TXNS.INSTANCE_ID) instance_number
FROM
AHL_WORKORDER_MTL_TXNS TXNS,
AHL_WORKORDERS WO,
(SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_JOB_STATUS' AND LANGUAGE= USERENV('LANG')) WO_STS,
AHL_VISIT_TASKS_B VST_TASK,
AHL_VISITS_B VST,
AHL_WORKORDER_OPERATIONS WO_OP,
MTL_SYSTEM_ITEMS_KFV MTL,
MTL_UNITS_OF_MEASURE_VL UOM,
-- modified to retrieve segment19 and 20 from base table to fix bug# 6611033.
--MTL_ITEM_LOCATIONS_KFV MTL_LOC,
MTL_ITEM_LOCATIONS MTL_LOC,
WIP_PARAMETERS WP,
PER_ALL_PEOPLE_F PER
WHERE
TXNS.TRANSACTION_TYPE_ID = 35 AND
TXNS.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID AND
TXNS.ORGANIZATION_ID = MTL.ORGANIZATION_ID AND
TXNS.WORKORDER_OPERATION_ID = WO_OP.WORKORDER_OPERATION_ID AND
WO_OP.WORKORDER_ID = WO.WORKORDER_ID AND
TXNS.ORGANIZATION_ID = VST.ORGANIZATION_ID AND
TXNS.UOM = UOM.UOM_CODE AND
--MTL_LOC setup is optional(bug# 6761128).
--MTL_LOC.ORGANIZATION_ID = VST.ORGANIZATION_ID AND
VST.ORGANIZATION_ID = WP.ORGANIZATION_ID AND
WO.STATUS_CODE = WO_STS.LOOKUP_CODE AND
WO.VISIT_TASK_ID = VST_TASK.VISIT_TASK_ID AND
VST.VISIT_ID = VST_TASK.VISIT_ID AND
WO.MASTER_WORKORDER_FLAG = 'N' AND
--WO.STATUS_CODE NOT IN ('17', '22') AND
WO.STATUS_CODE IN ('3', '4', '20') AND
WP.ORGANIZATION_ID = MTL_LOC.ORGANIZATION_ID (+) AND
WP.DEFAULT_PULL_SUPPLY_LOCATOR_ID = MTL_LOC.INVENTORY_LOCATION_ID (+) AND
TXNS.RECEPIENT_ID = PER.PERSON_ID (+) AND
WO.WORKORDER_ID = c_wid AND
MTL.INVENTORY_ITEM_ID =c_itemid AND
NVL(TXNS.SERIAL_NUMBER,'X') = NVL(c_SNO, NVL(TXNS.SERIAL_NUMBER,'X')) AND
NVL(TXNS.LOT_NUMBER,'X') = NVL(c_lotNumber, NVL(TXNS.LOT_NUMBER,'X')) AND
NVL(TXNS.REVISION,'X') = NVL(c_rev, NVL(TXNS.REVISION,'X')) AND
TXNS.ORGANIZATION_ID = c_ORG_ID;
SELECT DISTINCT
E.WORKORDER_ID,
E.WORKORDER_NAME JOB_NUMBER,
V.ORGANIZATION_ID,
B.CONCATENATED_SEGMENTS ,
A.INVENTORY_ITEM_ID,
B.DESCRIPTION,
A.SERIAL_NUMBER ,
AHL_PRD_MTLTXN_PVT.GET_WORKORD_LEVEL_QTY(c_wid, c_itemid, c_ORG_ID, c_lotNumber, c_rev, c_SNO) issWoQty,
-- JKJAIN FP ER # 6436303 - start
AHL_PRD_MTLTXN_PVT.GET_WORKORD_NET_QTY(c_wid,c_itemid,c_ORG_ID) Wo_Net_Total_Qty,
-- JKJAIN FP ER # 6436303 - end
A.UOM ,
UOM.unit_of_measure,
WO_STS.MEANING JOB_STATUS_MEANING,
A.LOT_NUMBER,
A.REVISION,
W.DEFAULT_PULL_SUPPLY_SUBINV,
W.DEFAULT_PULL_SUPPLY_LOCATOR_ID,
--D.CONCATENATED_SEGMENTS Locator
-- Fix for bug number 5903275
inv_project.GET_LOCSEGS(W.DEFAULT_PULL_SUPPLY_LOCATOR_ID, W.organization_id) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
|| INV_ProjectLocator_PUB.get_project_number(D.segment19) || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
|| INV_ProjectLocator_PUB.get_task_number(D.segment20) LOCATOR,
--SYSDATE
(select inv_locator_id from ahl_visits_b where visit_id = E.visit_id) inv_locator_id,
(Select csi.instance_number from csi_item_instances csi where csi.instance_id = A.INSTANCE_ID) instance_number
FROM
AHL_WORKORDER_MTL_TXNS A,
MTL_SYSTEM_ITEMS_KFV B,
MTL_UNITS_OF_MEASURE_VL UOM,
-- modified to retrieve segment19 and 20 from base table to fix bug# 6611033.
--MTL_ITEM_LOCATIONS_KFV D,
MTL_ITEM_LOCATIONS D,
AHL_WORKORDERS E,
(SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_JOB_STATUS' AND LANGUAGE= USERENV('LANG')) WO_STS,
AHL_VISITS_B V,
AHL_VISIT_TASKS_B VT,
AHL_WORKORDER_OPERATIONS F,
WIP_PARAMETERS W
WHERE
A.INVENTORY_ITEM_ID=B.INVENTORY_ITEM_ID
AND A.WORKORDER_OPERATION_ID=F.WORKORDER_OPERATION_ID
AND A.ORGANIZATION_ID=B.ORGANIZATION_ID
AND A.ORGANIZATION_ID=V.ORGANIZATION_ID
AND A.TRANSACTION_TYPE_ID=35
AND A.uom=UOM.uom_code
--MTL_LOC setup is optional(bug# 6761128).
--AND D.organization_id=V.organization_id
AND F.WORKORDER_ID=E.WORKORDER_ID
AND E.VISIT_TASK_ID = VT.VISIT_TASK_ID
AND E.MASTER_WORKORDER_FLAG = 'N'
--AND E.STATUS_CODE NOT IN ('17', '22')
AND E.STATUS_CODE IN ('3', '4', '20')
AND E.STATUS_CODE = WO_STS.LOOKUP_CODE
AND VT.VISIT_ID = V.VISIT_ID
AND V.ORGANIZATION_ID=W.ORGANIZATION_ID
AND W.organization_id = D.organization_id(+)
AND W.default_pull_supply_locator_id =D.inventory_location_id(+)
AND E.workorder_id = c_wid
AND B.INVENTORY_ITEM_ID = c_itemid
AND NVL(A.SERIAL_NUMBER, 'X') = NVL(c_SNO ,NVL(A.SERIAL_NUMBER, 'X'))
AND NVL(A.lot_number, 'X') = NVL(c_lotNumber ,NVL(A.LOT_NUMBER, 'X'))
AND NVL(A.revision, 'X') = NVL(c_rev ,NVL(A.REVISION, 'X'))
AND A.organization_id = c_ORG_ID;
SELECT 'x'
FROM CSI_ITEM_INSTANCES CII
WHERE CII.INSTANCE_ID = p_instance_id
AND CII.ACTIVE_START_DATE <= SYSDATE
AND ((CII.ACTIVE_END_DATE IS NULL) OR (CII.ACTIVE_END_DATE > SYSDATE))
AND CII.QUANTITY > 0
AND CII.LOCATION_TYPE_CODE = 'WIP'
AND CII.WIP_JOB_ID = p_wip_entity_id
AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND SYSDATE BETWEEN NVL(CIR.ACTIVE_START_DATE,SYSDATE) AND NVL(CIR.ACTIVE_END_DATE,SYSDATE));
SELECT DISTINCT
W.WORKORDER_ID,
T.ORGANIZATION_ID,
T.INVENTORY_ITEM_ID,
T.SERIAL_NUMBER,
T.LOT_NUMBER,
T.REVISION,
T.INSTANCE_ID,
W.WIP_ENTITY_ID,
V.INV_LOCATOR_ID
FROM
AHL_WORKORDER_MTL_TXNS T,
AHL_WORKORDERS W,
AHL_WORKORDER_OPERATIONS O,
AHL_VISITS_B V ' ;
SELECT W.WORKORDER_ID,
W.job_number,
W.job_status_meaning,
I.Description,
W.ORGANIZATION_ID,
csi.INVENTORY_ITEM_ID,
I.concatenated_segments,
csi.SERIAL_NUMBER ,
csi.LOT_NUMBER,
csi.INVENTORY_REVISION REVISION,
csi.INSTANCE_ID,
W.WIP_ENTITY_ID,
csi.quantity,
csi.Unit_Of_measure UOM,
UOM.unit_of_measure,
P.DEFAULT_PULL_SUPPLY_SUBINV,
P.DEFAULT_PULL_SUPPLY_LOCATOR_ID,
inv_project.GET_LOCSEGS(P.DEFAULT_PULL_SUPPLY_LOCATOR_ID, W.organization_id)|| '.'
||
DECODE(D.segment19,NULL,NULL,inv_project.GET_PROJECT_NUMBER(D.segment19)) || '.'
||
DECODE(D.segment20,NULL,NULL,inv_project.GET_TASK_NUMBER(D.segment20)) Locator,
(select inv_locator_id from ahl_visits_b where visit_id = w.visit_id) inv_locator_id
FROM
CSI_ITEM_INSTANCES CSI,
MTL_SYSTEM_ITEMS_KFV I,
AHL_SEARCH_WORKORDERS_v W,
WIP_PARAMETERS P,
MTL_UNITS_OF_MEASURE_VL UOM,
MTL_ITEM_LOCATIONS D,
MTL_PARAMETERS MP
WHERE
csi.inventory_item_id = I.inventory_item_id
AND W.organization_id = MP.organization_id
AND csi.inv_master_organization_id = mp.master_organization_id
AND MP.organization_id = I.organization_id
AND CSI.WIP_JOB_ID = W.WIP_ENTITY_ID
AND CSI.LOCATION_TYPE_CODE = 'WIP'
AND W.organization_id = P.organization_id
AND CSI.Unit_Of_Measure = UOM.UOM_CODE
AND P.default_pull_supply_locator_id = D.inventory_location_id(+)
AND P.organization_id = D.organization_id(+)
AND I.ENABLED_FLAG = 'Y'
--AND W.JOB_STATUS_CODE NOT IN ('5','7','12')
AND W.JOB_STATUS_CODE IN ('3','4','20')
AND ((I.START_DATE_ACTIVE IS NULL) OR (I.START_DATE_ACTIVE <= SYSDATE))
AND ((I.END_DATE_ACTIVE IS NULL) OR (I.END_DATE_ACTIVE >= SYSDATE))
AND UPPER(I.concatenated_segments) LIKE UPPER(nvl(p_item,I.concatenated_segments))
AND UPPER(W.ORGANIZATION_NAME) LIKE UPPER(NVL(p_org_name,W.ORGANIZATION_NAME))
AND UPPER(W.DEPARTMENT_NAME) LIKE UPPER(NVL(p_dept_name,W.DEPARTMENT_NAME))
AND UPPER(NVL(W.INCIDENT_NUMBER,'x')) LIKE UPPER(NVL(p_incident_number,NVL(W.INCIDENT_NUMBER,'x')))
AND NVL(W.PRIORITY,0) = NVL(p_priority,NVL(W.PRIORITY,0))
AND W.VISIT_NUMBER = NVL(p_visit_number,W.VISIT_NUMBER)
AND UPPER(W.JOB_NUMBER) LIKE UPPER(NVL(p_job_number,W.job_number))
AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CSI.INSTANCE_Id
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE,SYSDATE)
AND NVL(ACTIVE_END_DATE,SYSDATE))
/* fix for bug# 6310766: extra row being displayed with 0 qty
-- split this query into two to handle serialized and non-serialized items.
AND NOT EXISTS (SELECT 'x'
from ahl_workorder_mtl_txns txn, AHL_WORKORDER_OPERATIONS o
where txn.workorder_operation_id = o.workorder_operation_id
and o.workorder_id = w.workorder_id
and txn.instance_id = csi.instance_id
and txn.TRANSACTION_TYPE_ID = 35)
*/
-- for non-serialized items
/* AND NOT EXISTS (SELECT 'x'
from ahl_workorder_mtl_txns txn, AHL_WORKORDER_OPERATIONS o
where txn.workorder_operation_id = o.workorder_operation_id
and o.workorder_id = w.workorder_id
and txn.TRANSACTION_TYPE_ID = 35
and txn.serial_number is null
and txn.inventory_item_id = csi.inventory_item_id
and nvl(txn.REVISION, 'x') = nvl(csi.inventory_revision,'x')
and nvl(txn.lot_number,'x') = nvl(csi.lot_number,'x')
)
AND CSI.ACTIVE_START_DATE <= SYSDATE
AND ((CSI.ACTIVE_END_DATE IS NULL) OR (CSI.ACTIVE_END_DATE > SYSDATE))
AND CSI.quantity > 0; */
SELECT 'X'
FROM AHL_MTL_RET_DISPOSITIONS_V disp
WHERE disp.WORKORDER_ID= p_WORKORDER_ID
AND disp.instance_id = p_instance_id;
SELECT 'X'
FROM AHL_MTL_RET_DISPOSITIONS_V D
WHERE D.WORKORDER_ID = p_workorder_id
AND D.INVENTORY_ITEM_ID = p_inventory_item_id
AND nvl(D.Serial_Number, '1') = nvl(p_serial_number, '1')
AND nvl(D.lot_number, '1') = nvl(p_lot_number, '1')
AND nvl(D.ITEM_Revision, '1') = nvl(p_item_revision, '1');
SELECT W.WORKORDER_ID,
W.WORKORDER_NAME JOB_NUMBER,
MLU.MEANING JOB_STATUS_MEANING,
I.DESCRIPTION,
V.ORGANIZATION_ID,
C.INVENTORY_ITEM_ID,
I.CONCATENATED_SEGMENTS,
C.SERIAL_NUMBER ,
C.INSTANCE_NUMBER ,
C.LOT_NUMBER,
C.INVENTORY_REVISION REVISION,
C.INSTANCE_ID,
W.WIP_ENTITY_ID,
C.QUANTITY,
C.UNIT_OF_MEASURE UOM,
UOM.UNIT_OF_MEASURE,
P.DEFAULT_PULL_SUPPLY_SUBINV,
P.DEFAULT_PULL_SUPPLY_LOCATOR_ID,
INV_PROJECT.GET_LOCSEGS(P.DEFAULT_PULL_SUPPLY_LOCATOR_ID, V.ORGANIZATION_ID)
|| ''.''
|| DECODE(D.SEGMENT19,NULL,NULL, INV_PROJECT.GET_PROJECT_NUMBER(D.SEGMENT19))
|| ''.''
|| DECODE(D.SEGMENT20, NULL,NULL,INV_PROJECT.GET_TASK_NUMBER(D.SEGMENT20)) LOCATOR,
V.INV_LOCATOR_ID,
(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
FROM AHL_WORKORDERS W,
FND_LOOKUP_VALUES_VL MLU,
MTL_SYSTEM_ITEMS_KFV I,
AHL_VISITS_B V,
CSI_ITEM_INSTANCES C,
MTL_UNITS_OF_MEASURE_VL UOM,
WIP_PARAMETERS P,
MTL_ITEM_LOCATIONS D ';
(SELECT ''X''
FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = C.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = ''COMPONENT-OF''
AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE,SYSDATE) AND NVL(ACTIVE_END_DATE, SYSDATE)
)
AND C.ACTIVE_START_DATE <= SYSDATE
AND ( ( C.ACTIVE_END_DATE IS NULL )
OR ( C.ACTIVE_END_DATE > SYSDATE ) )
AND C.QUANTITY > 0 ';
SELECT
ORGANIZATION_ID,
ORGANIZATION_NAME
FROM
ORG_ORGANIZATION_DEFINITIONS
WHERE
NVL (OPERATING_UNIT, MO_GLOBAL.GET_CURRENT_ORG_ID()) =
MO_GLOBAL.GET_CURRENT_ORG_ID()
) ORG';
select * into x_row
from MTL_TRANSACTIONS_INTERFACE;
SELECT rsv.DEMAND_SOURCE_LINE_DETAIL schedule_material_id, rsv.demand_source_header_id,
rsv.demand_source_line_id, rsv.demand_source_type_id
FROM mtl_serial_numbers msn, mtl_reservations rsv
WHERE msn.reservation_id = rsv.reservation_id
AND msn.serial_number = p_serial_number
AND msn.current_organization_id = p_organization_id
AND msn.inventory_item_id = p_inventory_item_id;
SELECT 'x'
FROM AHL_WORKORDERS WO,
AHL_VISIT_TASKS_B VTS,
AHL_WORKORDERS WO1
WHERE WO.STATUS_CODE NOT IN ('7','12','17','22' ) -- Cancelled,Closed,Draft and Deleted
AND WO.VISIT_ID = WO1.VISIT_ID
AND wo1.workorder_id = C_WORKORDER_ID
AND VTS.VISIT_TASK_ID = WO.VISIT_TASK_ID
AND NVL(VTS.RETURN_TO_SUPPLY_FLAG,'N') = 'Y'
AND VTS.INSTANCE_ID = C_INS_ID;