[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Instance_Routing (
p_inst_routing_id IN NUMBER,
p_object_version_number IN NUMBER,
p_inst_routing_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2);
PROCEDURE Delete_Instance_Routing (
p_inst_routing_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2);
SELECT COUNT(1) from ahl_visit_ir_uc_details;
SELECT COUNT(1) FROM AHL_VISIT_IR_UC_DETAILS WHERE visit_id = c_visit_id;
SELECT COUNT(1) FROM AHL_VISIT_IR_UC_DETAILS WHERE root_instance_id = c_instance_id;
/* This is global temparary table with ON COMMIT DELETE ROWS, hence its all values will be in sync with respective origin always-
If the visit in context is changed then we need to populate the temp table again.
If the visit in context is same and user has selected another unit/instance, retain the values in the table
There will be records in table only for one visit
*/
IF l_count_visit = 0 THEN
IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
'Deleting all rows in ahl_visit_ir_uc_details');
DELETE FROM ahl_visit_ir_uc_details;
'all rows in ahl_visit_ir_uc_details deleted');
INSERT INTO ahl_visit_ir_uc_details
(
visit_ir_uc_id,
root_instance_id,
unit_header_id,
unit_name,
path_position_id,
relationship_id,
parent_rel_id,
curr_item_id,
curr_instance_id,
parent_instance_id,
curr_item_number,
curr_instance_number,
curr_serial_number,
position,
position_image,
item_group_id,
item_group_name,
reserved_serial_num,
available_parts,
unit_nha,
is_unit,
is_warranty_available,
mr_count,
task_count,
is_visit_available,
mc_header_id,
is_sb_rule_available,
route_dispositions_flag,
rd_rework_percent,
rd_replace_percent,
position_material_flag,
bg_position,
bg_instance,
inst_routing_id,
inst_routing_ovn,
visit_id,
inst_routing_code,
inst_routing_meaning,
inst_routing_flag,
qty_per_position,
installed_qty,
leaf_node_flag,
node_level,
hgrid_node_path
)VALUES
(
x_visit_ir_uc_details_tbl(i).visit_ir_uc_id,
x_visit_ir_uc_details_tbl(i).root_instance_id,
x_visit_ir_uc_details_tbl(i).unit_header_id,
x_visit_ir_uc_details_tbl(i).unit_name,
x_visit_ir_uc_details_tbl(i).path_position_id,
x_visit_ir_uc_details_tbl(i).relationship_id,
x_visit_ir_uc_details_tbl(i).parent_rel_id,
x_visit_ir_uc_details_tbl(i).curr_item_id,
x_visit_ir_uc_details_tbl(i).curr_instance_id,
x_visit_ir_uc_details_tbl(i).parent_instance_id,
x_visit_ir_uc_details_tbl(i).curr_item_number,
x_visit_ir_uc_details_tbl(i).curr_instance_number,
x_visit_ir_uc_details_tbl(i).curr_serial_number,
x_visit_ir_uc_details_tbl(i).position,
x_visit_ir_uc_details_tbl(i).position_image,
x_visit_ir_uc_details_tbl(i).item_group_id,
x_visit_ir_uc_details_tbl(i).item_group_name,
x_visit_ir_uc_details_tbl(i).reserved_serial_num,
x_visit_ir_uc_details_tbl(i).available_parts,
x_visit_ir_uc_details_tbl(i).unit_nha,
x_visit_ir_uc_details_tbl(i).is_unit,
x_visit_ir_uc_details_tbl(i).is_warranty_available,
x_visit_ir_uc_details_tbl(i).mr_count,
x_visit_ir_uc_details_tbl(i).task_count,
x_visit_ir_uc_details_tbl(i).is_visit_available,
x_visit_ir_uc_details_tbl(i).mc_header_id,
x_visit_ir_uc_details_tbl(i).is_sb_rule_available,
x_visit_ir_uc_details_tbl(i).route_dispositions_flag,
x_visit_ir_uc_details_tbl(i).rd_rework_percent,
x_visit_ir_uc_details_tbl(i).rd_replace_percent,
x_visit_ir_uc_details_tbl(i).position_material_flag,
x_visit_ir_uc_details_tbl(i).bg_position,
x_visit_ir_uc_details_tbl(i).bg_instance,
x_visit_ir_uc_details_tbl(i).inst_routing_id,
x_visit_ir_uc_details_tbl(i).inst_routing_ovn,
x_visit_ir_uc_details_tbl(i).visit_id,
x_visit_ir_uc_details_tbl(i).inst_routing_code,
x_visit_ir_uc_details_tbl(i).inst_routing_meaning,
x_visit_ir_uc_details_tbl(i).inst_routing_flag,
x_visit_ir_uc_details_tbl(i).qty_per_position,
x_visit_ir_uc_details_tbl(i).installed_qty,
x_visit_ir_uc_details_tbl(i).leaf_node_flag,
x_visit_ir_uc_details_tbl(i).node_level,
x_visit_ir_uc_details_tbl(i).hgrid_node_path
);
'Newly Inserted Rows' || x_visit_ir_uc_details_tbl.count);
'Update Done root build goal ' || l_count);
Update_Instance_Routing (
p_inst_routing_id => p_x_instance_routings_tbl(i).inst_routing_id,
p_object_version_number => p_x_instance_routings_tbl(i).object_version_number,
p_inst_routing_code => p_x_instance_routings_tbl(i).inst_routing_code,
x_return_status => l_return_status
);
Delete_Instance_Routing (
p_inst_routing_id => p_x_instance_routings_tbl(i).inst_routing_id,
x_return_status => l_return_status
);
SELECT curr_instance_id
FROM AHL_VISIT_IR_UC_DETAILS
WHERE parent_instance_id = c_instance_id
AND curr_instance_id IS NOT NULL;
SELECT bg_instance, leaf_node_flag
FROM AHL_VISIT_IR_UC_DETAILS
WHERE curr_instance_id = c_instance_id;
UPDATE AHL_VISIT_IR_UC_DETAILS
SET bg_position = bg_per
WHERE curr_instance_id = p_current_instance_id;
SELECT (NVL(MAX(visit_ir_uc_id),0) + 1) from ahl_visit_ir_uc_details;
SELECT name,csi_item_instance_id FROM AHL_UNIT_CONFIG_HEADERS
WHERE unit_config_header_id = c_unit_header_id;
SELECT 'U' FROM AHL_UNIT_CONFIG_HEADERS WHERE csi_item_instance_id = c_instance_id;
SELECT MTL.inventory_item_id,MTL.concatenated_segments,CSI.instance_number,CSI.serial_number, CSI.quantity
FROM MTL_SYSTEM_ITEMS_VL MTL, CSI_ITEM_INSTANCES CSI
WHERE CSI.instance_id = c_instance_id
AND CSI.inventory_item_id = MTL.inventory_item_id
AND CSI.inv_master_organization_id = MTL.organization_id;
SELECT MCR.mc_header_id, MCR.item_group_id, GRP.name item_group_name, MCR.quantity
FROM AHL_MC_RELATIONSHIPS MCR, AHL_ITEM_GROUPS_B GRP
WHERE MCR.item_group_id = GRP.item_group_id (+)
AND MCR.relationship_id = c_relationship_id;
SELECT CASE COUNT(rule_id)
WHEN 0 THEN 'N'
ELSE 'Y'
END sb_rules_flag
FROM AHL_SB_POSITION_RULES
WHERE mc_header_id = c_mc_header_id
AND relationship_id = c_relationship_id
AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
SELECT VIR.inst_routing_id, VIR.object_version_number, VIR.inst_routing_code, IRC.meaning
FROM AHL_VISIT_INST_ROUTINGS VIR, FND_LOOKUP_VALUES_VL IRC
WHERE VIR.instance_id = c_instance_id
AND VIR.visit_id = c_visit_id
AND IRC.lookup_code (+) = VIR.inst_routing_code
AND IRC.lookup_type (+) = 'AHL_INSTANCE_ROUTING';
SELECT COUNT(1) MR_COUNT
FROM (SELECT DISTINCT unit_effectivity_id
FROM AHL_VISIT_TASKS_B TSK
WHERE TSK.instance_id = c_instance_id
AND TSK.visit_id = c_visit_id
AND NVL(TSK.status_code,'X') <> 'DELETED'
AND TSK.TASK_TYPE_CODE = 'SUMMARY'
AND TSK.unit_effectivity_id IS NOT NULL
--Commenting this line as it is redundant check
--AND NVL(TSK.TASK_TYPE_CODE , 'X') <> 'STAGE'
);
SELECT COUNT(TSK.VISIT_TASK_ID) TASK_ID_COUNT
FROM AHL_VISIT_TASKS_B TSK
WHERE TSK.instance_id = c_instance_id
AND TSK.visit_id = c_visit_id
AND NVL(TSK.status_code,'X') <> 'DELETED'
AND NVL(TSK.TASK_TYPE_CODE , 'X') <> 'STAGE';
SELECT CASE COUNT(visit_id)
WHEN 0 THEN 'N'
ELSE 'Y'
END visits_count
FROM AHL_VISITS_B VST
WHERE VST.item_instance_id = c_instance_id
AND VST.visit_id <> c_visit_id;
SELECT *
FROM (SELECT ROM.rework_percent, ROM.replace_percent
FROM AHL_SCHEDULE_MATERIALS ASL,
AHL_RT_OPER_MATERIALS ROM
WHERE ASL.position_path_id IS NOT NULL
AND ASL.relationship_id = c_relationship_id
AND ASL.visit_id = c_visit_id
AND ASL.rt_oper_material_id = ROM.rt_oper_material_id
AND ROM.association_type_code = 'DISPOSITION'
AND ASL.status = 'ACTIVE'
--AND ASL.scheduled_quantity > 0
UNION ALL
-- For item based dispositions
SELECT ROM.rework_percent, ROM.replace_percent
FROM AHL_SCHEDULE_MATERIALS ASL,
AHL_RT_OPER_MATERIALS ROM
WHERE ASL.position_path_id IS NULL
AND ASL.inventory_item_id = c_inventory_item_id
AND ASL.visit_id = c_visit_id
AND ASL.rt_oper_material_id = ROM.rt_oper_material_id
AND ROM.association_type_code = 'DISPOSITION'
AND ASL.status = 'ACTIVE'
--AND ASL.scheduled_quantity > 0
)
WHERE rownum < 3;
SELECT CASE COUNT(scheduled_material_id)
WHEN 0 THEN 'N'
WHEN 1 THEN 'Y'
ELSE 'M'
END position_material
FROM AHL_SCHEDULE_MATERIALS ASM, AHL_MC_RELATIONSHIPS MCR
WHERE MCR.relationship_id = c_relationship_id
AND ASM.visit_id = c_visit_id
AND ASM.mc_header_id = MCR.mc_header_id
AND ASM.position_key = MCR.position_key;
SELECT (NVL(MAX(visit_ir_uc_id),0) + 1) from ahl_visit_ir_uc_details;
SELECT MTL.inventory_item_id,MTL.concatenated_segments,CSI.instance_number,CSI.serial_number, CSI.quantity
FROM MTL_SYSTEM_ITEMS_VL MTL, CSI_ITEM_INSTANCES CSI
WHERE CSI.instance_id = c_instance_id
AND CSI.inventory_item_id = MTL.inventory_item_id
AND CSI.inv_master_organization_id = MTL.organization_id;
SELECT VIR.inst_routing_id, VIR.object_version_number, VIR.inst_routing_code, IRC.meaning
FROM AHL_VISIT_INST_ROUTINGS VIR, FND_LOOKUP_VALUES_VL IRC
WHERE VIR.instance_id = c_instance_id
AND VIR.visit_id = c_visit_id
AND IRC.lookup_code (+) = VIR.inst_routing_code
AND IRC.lookup_type (+) = 'AHL_INSTANCE_ROUTING';
SELECT COUNT(MRH.MR_HEADER_ID) MR_HEADER_ID_COUNT
FROM AHL_VISIT_TASKS_B TSK, AHL_MR_HEADERS_B MRH
WHERE TSK.instance_id = c_instance_id
AND TSK.visit_id = c_visit_id
AND TSK.mr_id = MRH.mr_header_id
AND NVL(TSK.status_code,'X') <> 'DELETED'
AND TSK.TASK_TYPE_CODE = 'SUMMARY';
SELECT COUNT(TSK.VISIT_TASK_ID) TASK_ID_COUNT
FROM AHL_VISIT_TASKS_B TSK
WHERE TSK.instance_id = c_instance_id
AND TSK.visit_id = c_visit_id
AND NVL(TSK.status_code,'X') <> 'DELETED'
AND NVL(TSK.TASK_TYPE_CODE , 'X') <> 'STAGE';
SELECT CASE COUNT(visit_id)
WHEN 0 THEN 'F'
ELSE 'T'
END visits_count
FROM AHL_VISITS_B VST
WHERE VST.item_instance_id = c_instance_id
AND VST.visit_id <> c_visit_id;
SELECT 'X'
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID = c_item_instance_id;
SELECT 'X'
FROM AHL_VISITS_B
WHERE visit_id = c_visit_id;
INSERT INTO AHL_VISIT_INST_ROUTINGS
(
INST_ROUTING_ID,
VISIT_ID,
INSTANCE_ID,
INST_ROUTING_CODE,
OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
values
(
l_inst_routing_id,
p_visit_id,
p_item_instance_id,
p_inst_routing_code,
1,
NULL,
SYSDATE,
Fnd_Global.USER_ID,
SYSDATE,
Fnd_Global.USER_ID,
Fnd_Global.LOGIN_ID
);
PROCEDURE Update_Instance_Routing (
p_inst_routing_id IN NUMBER,
p_object_version_number IN NUMBER,
p_inst_routing_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
-- cursor to validate the given routing id
CURSOR validate_routing_csr (c_inst_routing_id NUMBER) IS
SELECT *
FROM AHL_VISIT_INST_ROUTINGS
WHERE inst_routing_id = c_inst_routing_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Instance_Routing';
'Update_Instance_Routing. instance routing id = ' || p_inst_routing_id||
'inst_routing_code = ' || p_inst_routing_code
);
UPDATE AHL_VISIT_INST_ROUTINGS SET
inst_routing_code = p_inst_routing_code,
object_version_number = object_version_number + 1,
security_group_id = NULL,
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE inst_routing_id = p_inst_routing_id;
'Update_Instance_Routing, x_return_status=' || x_return_status);
END Update_Instance_Routing;
PROCEDURE Delete_Instance_Routing (
p_inst_routing_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Instance_Routing';
SELECT 'X'
FROM AHL_VISIT_INST_ROUTINGS
WHERE inst_routing_id = c_inst_routing_id;
DELETE FROM AHL_VISIT_INST_ROUTINGS
WHERE inst_routing_id = p_inst_routing_id;
END Delete_Instance_Routing;
instance_id Instance_id for which instance routing is defined/updated/deleted.
p_old_inst_rtng_code Old Instance routing (Lookup Code) for instance.
p_new_inst_rtng_code New Instnace routing (Lookup Code) for instance.
*/
PROCEDURE Process_Inst_Rtng_Notes(
p_visit_id IN NUMBER,
p_instance_id IN NUMBER,
p_old_inst_rtng_code IN VARCHAR2 := NULL,
p_new_inst_rtng_code IN VARCHAR2 := NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
--Local Variable Declaration.
l_msg_count NUMBER;
SELECT WO.WORKORDER_ID WORKORDER_ID
FROM
AHL_VISITS_B VST, AHL_VISIT_TASKS_B TSK, AHL_WORKORDERS WO
WHERE
VST.VISIT_ID = p_visit_id
AND VST.VISIT_ID = TSK.VISIT_ID
AND TSK.INSTANCE_ID = p_instance_id
AND TSK.VISIT_TASK_ID = WO.VISIT_TASK_ID
AND WO.STATUS_CODE NOT IN (7,12,4,5,22); -- Cancelled, Closed,Complete,Complete No-charge,Deleted
SELECT CII.instance_id, CII.location_type_code,
CII.instance_usage_code, CII.wip_job_id, CII.inv_locator_id,
MIL.segment19, MIL.segment20, MIL.concatenated_segments inv_locator_name,
MIL.subinventory_code, WO.workorder_name
FROM CSI_ITEM_INSTANCES CII , MTL_ITEM_LOCATIONS_KFV MIL, AHL_WORKORDERS WO
WHERE CII.instance_id = c_instance_id
AND CII.inv_locator_id = MIL.inventory_location_id (+)
AND CII.wip_job_id = WO.wip_entity_id(+);*/
SELECT CII.instance_id, CII.location_type_code,
CII.instance_usage_code, CII.wip_job_id, CII.inv_locator_id,
MIL.segment19, MIL.segment20, MIL_kfv.concatenated_segments inv_locator_name,
MIL.subinventory_code, WO.workorder_name
FROM CSI_ITEM_INSTANCES CII , MTL_ITEM_LOCATIONS MIL, MTL_ITEM_LOCATIONS_KFV MIL_kfv, AHL_WORKORDERS WO
WHERE CII.instance_id = c_instance_id
AND CII.inv_locator_id = MIL.inventory_location_id (+)
AND MIL_kfv.INVENTORY_LOCATION_ID(+) = MIL.INVENTORY_LOCATION_ID
AND MIL_kfv.ORGANIZATION_ID(+) = MIL.ORGANIZATION_ID
AND CII.wip_job_id = WO.wip_entity_id(+);
SELECT item_instance_id, visit_number
FROM AHL_VISITS_B
WHERE visit_id = c_visit_id;
SELECT vst.visit_number
FROM ahl_visits_b vst, ahl_visit_tasks_b vts
WHERE
vst.project_id = c_project_id
and vst.visit_id = vts.visit_id
and vts.project_task_id = c_project_task_id
and rownum < 2;
SELECT vst.visit_number,osph.osp_order_number
FROM ahl_visits_b vst, ahl_visit_tasks_b vts, ahl_workorders wo,ahl_osp_orders_b osph, ahl_osp_order_lines ospl
WHERE
wo.wip_entity_id = c_wip_entity_id
and wo.visit_task_id IS NOT NULL
and wo.visit_task_id = vts.visit_task_id
and vts.visit_id = vst.visit_id
and wo.workorder_id = ospl.workorder_id (+)
and ospl.osp_order_id = osph.osp_order_id (+)
--PRAKKUM :: 28/03/2012 :: Bug 13579487 :: OSP Line status is null (non null values are used only for cancelled, deleted cases)
--and NVL(OSPL.STATUS_CODE(+), 'ENTERED') NOT IN ( 'PO_DELETED' , 'PO_CANCELLED' )
and OSPL.STATUS_CODE IS NULL
AND NVL(OSPH.STATUS_CODE(+), 'ENTERED') NOT IN ( 'CLOSED' );
select OSP_ORDER_NUMBER from
(
select OSP_ORDER_NUMBER
from CSI_ITEM_INSTANCES CII , ahl_osp_orders_b osph, ahl_osp_order_lines ospl
WHERE CII.instance_id = c_instance_id
and CII.inventory_item_id = ospl.inventory_item_id
and CII.serial_number = ospl.serial_number
and ospl.osp_order_id = osph.osp_order_id (+)
--and NVL(OSPL.STATUS_CODE(+), 'ENTERED') NOT IN ( 'PO_DELETED' , 'PO_CANCELLED' )
and OSPL.STATUS_CODE IS NULL
AND NVL(OSPH.STATUS_CODE(+), 'ENTERED') NOT IN ( 'CLOSED' )
order by ospl.osp_order_line_id desc
)
where rownum=1;
SELECT SUM(quantity)
FROM (
/* STHILAK bug #13641891 : commented the query and framed the modified query
SELECT 1 quantity
FROM AHL_PRD_DISPOSITIONS_B DISP,
AHL_WORKORDERS WO,
CSI_ITEM_INSTANCES CSI
WHERE DISP.workorder_id = WO.workorder_id
AND DISP.instance_id = CSI.instance_id
AND CSI.wip_job_id = WO.wip_entity_id
AND DISP.status_code NOT IN ('TERMINATED','COMPLETED')
AND CSI.location_type_code = 'WIP'
AND CSI.serial_number IS NOT NULL
AND CSI.instance_id = c_instance_id
UNION ALL
-- For non serial controlled items
SELECT DISP.quantity quantity
FROM AHL_PRD_DISPOSITIONS_B DISP,
AHL_WORKORDERS WO,
CSI_ITEM_INSTANCES CSI
WHERE DISP.workorder_id = WO.workorder_id
AND DISP.inventory_item_id = CSI.inventory_item_id
AND CSI.wip_job_id = WO.wip_entity_id
AND DISP.status_code NOT IN ('TERMINATED','COMPLETED')
AND CSI.location_type_code = 'WIP'
AND NVL(DISP.lot_number,'x') = NVL(CSI.lot_number,'x')
AND NVL(DISP.item_revision,'x') = NVL(CSI.inventory_revision,'x')
AND CSI.serial_number IS NULL
AND CSI.instance_id = c_instance_id */
SELECT 1 quantity
FROM AHL_PRD_DISPOSITIONS_B DISP,
CSI_ITEM_INSTANCES CSI
WHERE DISP.instance_id = CSI.instance_id
AND CSI.instance_id = c_instance_id
AND CSI.location_type_code = 'WIP'
AND CSI.serial_number IS NOT NULL
AND disp.workorder_id IN
(SELECT workorder_id FROM AHL_WORKORDERS WHERE visit_id =c_visit_id
)
AND NVL(DISP.status_code,'x') NOT IN ('TERMINATED','COMPLETED')
UNION ALL
SELECT DISP.quantity quantity
FROM AHL_PRD_DISPOSITIONS_B DISP,
CSI_ITEM_INSTANCES CSI
WHERE DISP.inventory_item_id = CSI.inventory_item_id
AND CSI.location_type_code = 'WIP'
AND CSI.serial_number IS NULL
AND CSI.instance_id = c_instance_id
AND NVL(DISP.status_code,'x') NOT IN ('TERMINATED','COMPLETED')
AND NVL(DISP.lot_number,'x') = NVL(CSI.lot_number,'x')
AND NVL(DISP.item_revision,'x') = NVL(CSI.inventory_revision,'x')
AND DISP.workorder_id IN
(SELECT workorder_id FROM AHL_WORKORDERS WHERE visit_id =c_visit_id
)
);