[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(1) from AHL_UC_DETAILS_FOR_HGRID;
SELECT COUNT(UHC.UNIT_NODE_ID)
FROM AHL_UC_DETAILS_FOR_HGRID UHC,
AHL_UNIT_CONFIG_HEADERS UC
WHERE UC.unit_config_header_id = c_unit_header_id
AND UHC.root_instance_id = UC.csi_item_instance_id;
INSERT INTO AHL_UC_DETAILS_FOR_HGRID
(
unit_node_id,
root_instance_id,
path_position_id,
relationship_id,
parent_rel_id,
parent_instance_id,
parent_instance_number,
csi_ii_relationship_id,
csi_ii_object_version_num,
part_change_id,
curr_item_id,
curr_instance_id,
curr_item_number,
curr_instance_obj_ver_num,
curr_instance_number,
curr_serial_number,
curr_lot_number,
curr_quantity,
unit_of_measure,
ata,
position,
position_image,
is_position_srl_controlled,
is_controlled_position,
node_type,
qty_per_position,
operation_sequence_num,
inst_instance_id,
inst_item_number,
inst_instance_number,
inst_serial_number,
inst_instance_obj_ver_num,
instance_quantity,
install_quantity,
install_date,
removal_date,
reason_id,
reason_name,
removal_code_id,
removal_meaning,
not_leaf_node_flag,
node_level,
hgrid_node_path,
select_disable_flag,
search_flag,
disposition_id,
disposition_obj_ver_num,
disposition_status,
disposition_qty,
comments,
immediate_disposition_code,
immediate_disposition,
secondary_disposition_code,
secondary_disposition,
sr_incident_id,
collection_id,
condition_id,
condition_meaning,
problem_code,
problem_meaning,
severity_id,
severity_name,
resolution_code,
resolution_code_id,
estimated_duration,
service_type,
service_type_id,
summary,
disposition_count,
qty_ui_element_typ,
mrb_quality_typ,
bulk_process_flag
)VALUES
(
x_uc_details_tbl(i).unit_node_id,
x_uc_details_tbl(i).root_instance_id,
x_uc_details_tbl(i).path_position_id,
x_uc_details_tbl(i).relationship_id,
x_uc_details_tbl(i).parent_rel_id,
x_uc_details_tbl(i).parent_instance_id,
x_uc_details_tbl(i).parent_instance_number,
x_uc_details_tbl(i).csi_ii_relationship_id,
x_uc_details_tbl(i).csi_ii_object_version_num,
x_uc_details_tbl(i). part_change_id,
x_uc_details_tbl(i).curr_item_id,
x_uc_details_tbl(i).curr_instance_id,
x_uc_details_tbl(i).curr_item_number,
x_uc_details_tbl(i).curr_instance_obj_ver_num,
x_uc_details_tbl(i).curr_instance_number,
x_uc_details_tbl(i).curr_serial_number,
x_uc_details_tbl(i).curr_lot_number,
x_uc_details_tbl(i).curr_quantity,
x_uc_details_tbl(i).unit_of_measure,
x_uc_details_tbl(i).ata,
x_uc_details_tbl(i).position,
x_uc_details_tbl(i).position_image,
x_uc_details_tbl(i).is_position_srl_controlled,
x_uc_details_tbl(i).is_controlled_position,
x_uc_details_tbl(i).node_type,
x_uc_details_tbl(i).qty_per_position,
x_uc_details_tbl(i).operation_sequence_num,
x_uc_details_tbl(i).inst_instance_id,
x_uc_details_tbl(i).inst_item_number,
x_uc_details_tbl(i).inst_instance_number,
x_uc_details_tbl(i).inst_serial_number,
x_uc_details_tbl(i).inst_instance_obj_ver_num,
x_uc_details_tbl(i).instance_quantity,
x_uc_details_tbl(i).install_quantity,
x_uc_details_tbl(i).install_date,
x_uc_details_tbl(i).removal_date,
x_uc_details_tbl(i).reason_id,
x_uc_details_tbl(i).reason_name,
x_uc_details_tbl(i).removal_code_id,
x_uc_details_tbl(i).removal_meaning,
x_uc_details_tbl(i).not_leaf_node_flag,
x_uc_details_tbl(i).node_level,
x_uc_details_tbl(i).hgrid_node_path,
x_uc_details_tbl(i).select_disable_flag,
x_uc_details_tbl(i).search_flag,
x_uc_details_tbl(i).disposition_id,
x_uc_details_tbl(i).disposition_obj_ver_num,
x_uc_details_tbl(i).disposition_status,
x_uc_details_tbl(i).disposition_qty,
x_uc_details_tbl(i).comments,
x_uc_details_tbl(i).immediate_disposition_code,
x_uc_details_tbl(i).immediate_disposition,
x_uc_details_tbl(i).secondary_disposition_code,
x_uc_details_tbl(i).secondary_disposition,
x_uc_details_tbl(i).sr_incident_id,
x_uc_details_tbl(i).collection_id,
x_uc_details_tbl(i).condition_id,
x_uc_details_tbl(i).condition_meaning,
x_uc_details_tbl(i).problem_code,
x_uc_details_tbl(i).problem_meaning,
x_uc_details_tbl(i).severity_id,
x_uc_details_tbl(i).severity_name,
x_uc_details_tbl(i).resolution_code,
x_uc_details_tbl(i).resolution_code_id,
x_uc_details_tbl(i).estimated_duration,
x_uc_details_tbl(i).service_type,
x_uc_details_tbl(i).service_type_id,
x_uc_details_tbl(i).summary,
x_uc_details_tbl(i).disposition_count,
x_uc_details_tbl(i).qty_ui_element_typ,
x_uc_details_tbl(i).mrb_quality_typ,
p_bulk_process_flag
);
'Newly Inserted Rows' || x_uc_details_tbl.count);
SELECT name,csi_item_instance_id,master_config_id FROM AHL_UNIT_CONFIG_HEADERS
WHERE unit_config_header_id = c_unit_header_id;
SELECT WO.visit_id,VWP.organization_id FROM AHL_WORKORDERS WO,AHL_VISITS_B VWP
WHERE WO.workorder_id = p_workorder_id
AND WO.visit_id = VWP.visit_id;
SELECT MTL.inventory_item_id, MTL.concatenated_segments, CSI.instance_number, CSI.serial_number, CSI.quantity,
CSI.lot_number, UOM.unit_of_measure, CSI.object_version_number
FROM MTL_SYSTEM_ITEMS_VL MTL, CSI_ITEM_INSTANCES CSI, MTL_UNITS_OF_MEASURE UOM
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
AND CSI.unit_of_measure = UOM.uom_code;
SELECT (NVL(MAX(unit_node_id),0) + 1) from AHL_UC_DETAILS_FOR_HGRID;
SELECT disp.disposition_id ,
disp.object_version_number ,
disp.condition_id ,
disp.condition_code ,
disp.status ,
disp.immediate_disposition_code ,
disp.secondary_disposition_code ,
disp.collection_id ,
disp.quantity ,
disp.comments ,
disp.operation_sequence ,
disp.part_change_id,
nr.sr_resolution_code,
nr.sr_resolution_meaning,
nr.sr_problem_code,
nr.sr_problem_meaning,
nr.sr_problem_summary,
nr.service_type_code,
nr.service_type,
nr.duration,
nr.sr_incident_severity,
nr.sr_incident_severity_id,
nr.sr_incident_id
FROM AHL_PRD_DISPOSITIONS_V disp,
AHL_MC_PATH_POSITIONS pp,
AHL_SR_NON_ROUTINES_V nr
WHERE disp.path_pos_common_id = pp.path_pos_common_id
AND pp.path_position_id = c_path_position_id
AND disp.part_change_id IS NULL
AND disp.workorder_id = p_workorder_id
AND NVL(disp.immediate_disposition_code,'NULL') <> 'NOT_RECEIVED'
AND (disp.status_code IS NULL
OR disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
AND disp.primary_service_request_id = nr.SR_INCIDENT_ID(+)
AND disp.instance_id IN
(SELECT CI1.instance_id
FROM CSI_ITEM_INSTANCES CI1,
CSI_ITEM_INSTANCES CI2
WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
AND NVL(CI1.SERIAL_NUMBER,'x') = NVL(CI2.SERIAL_NUMBER,'x')
AND CI2.instance_id = c_instance_id
)
UNION
SELECT disp.disposition_id ,
disp.object_version_number ,
disp.condition_id ,
disp.condition_code ,
disp.status ,
disp.immediate_disposition_code ,
disp.secondary_disposition_code ,
disp.collection_id ,
disp.quantity ,
disp.comments ,
disp.operation_sequence,
disp.part_change_id,
nr.sr_resolution_code,
nr.sr_resolution_meaning,
nr.sr_problem_code,
nr.sr_problem_meaning,
nr.sr_problem_summary,
nr.service_type_code,
nr.service_type,
nr.duration,
nr.sr_incident_severity,
nr.sr_incident_severity_id,
nr.sr_incident_id
FROM AHL_PRD_DISPOSITIONS_V disp,AHL_SR_NON_ROUTINES_V nr
WHERE disp.path_position_id IS NULL
AND disp.part_change_id IS NULL
AND disp.workorder_id = p_workorder_id
AND NVL(disp.immediate_disposition_code,'NULL') <> 'NOT_RECEIVED'
AND (disp.status_code IS NULL
OR disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
AND disp.primary_service_request_id = nr.SR_INCIDENT_ID(+)
AND disp.instance_id IN
(SELECT CI1.instance_id
FROM CSI_ITEM_INSTANCES CI1,
CSI_ITEM_INSTANCES CI2
WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
AND NVL(CI1.SERIAL_NUMBER,'x') = NVL(CI2.SERIAL_NUMBER,'x')
AND CI2.instance_id = c_instance_id
);
SELECT nr.sr_resolution_code,
nr.sr_resolution_meaning,
nr.sr_problem_code,
nr.sr_problem_meaning,
nr.sr_problem_summary,
nr.service_type_code,
nr.service_type,
nr.duration,
nr.sr_incident_severity,
nr.sr_incident_severity_id
FROM AHL_SR_NON_ROUTINES_V nr
WHERE nr.SR_INCIDENT_ID = c_sr_incident_id;
SELECT ATA.meaning ata_meaning
FROM AHL_MC_RELATIONSHIPS MCR, FND_LOOKUP_VALUES_VL ATA
WHERE MCR.relationship_id = c_relationship_id
AND ATA.lookup_code (+) = MCR.ata_code
AND ATA.lookup_type (+) = 'AHL_ATA_CODE';
SELECT plan_id FROM qa_results
WHERE collection_id = c_collection_id;
SELECT relationship_id,object_version_number
FROM csi_ii_relationships
WHERE subject_id = c_instance_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);--Modified by Sunil
SELECT CSI.instance_number
FROM CSI_ITEM_INSTANCES CSI
WHERE CSI.instance_id = c_instance_id;
SELECT 'Y', MCRS.object_attribute1
FROM ahl_mc_path_position_nodes mcpp,
ahl_mc_rule_statements mcrs,
ahl_mc_headers_b mch,
ahl_mc_rules_b mcr
WHERE mch.mc_header_id = c_mc_header_id
AND mcpp.mc_id = mch.mc_id
AND mch.version_number = NVL(mcpp.version_number, mch.version_number)
AND mcpp.position_key IN (select position_key from ahl_mc_path_position_nodes where path_position_id = c_path_position_id)
AND mcrs.subject_id = mcpp.path_position_id
AND mcrs.subject_type = 'POSITION'
AND mcrs.operator IN ('MUST_HAVE', 'HAVE')
AND mcrs.object_type = 'TOT_CHILD_QUANTITY'
AND mch.config_status_code = 'COMPLETE'
AND mcr.rule_id = mcrs.rule_id
AND mcr.rule_type_code = 'MANDATORY'
AND NVL(mcr.active_start_date, SYSDATE - 1) < SYSDATE
AND NVL(mcr.active_end_date, SYSDATE + 1) > SYSDATE;
SELECT QP.plan_id
FROM QA_PLANS_VAL_V QP,
QA_PLAN_TRANSACTIONS QPT,
QA_PLAN_COLLECTION_TRIGGERS QPCT
WHERE QP.plan_id = QPT.plan_id
AND QPT.plan_transaction_id = QPCT.plan_transaction_id
AND QP.organization_id = c_org_id
AND QPT.transaction_number = 2004 -- MRB_TRANSACTION_NUMBER
AND QPCT.collection_trigger_id = 87 -- Inspection_Type
AND QPCT.low_value = (FND_PROFILE.VALUE('AHL_MRB_DISP_PLAN_TYPE'));
SELECT OP.operation_sequence_num, DECODE(APC.part_change_type, 'R', APC.removal_date, 'I', APC.installation_date),
APC.removal_code,REM.meaning,APC.removal_reason_id,RSN.reason_name,APC.removed_instance_id
FROM AHL_PART_CHANGES APC,
AHL_WORKORDER_OPERATIONS OP,
MTL_TRANSACTION_REASONS RSN,
FND_LOOKUPS REM
WHERE APC.part_change_id = (SELECT max(APC1.part_change_id)
FROM AHL_PART_CHANGES APC1,
AHL_WORKORDER_OPERATIONS OP1
WHERE APC1.unit_config_header_id = p_unit_header_id
AND APC1.mc_relationship_id = c_path_position_id -- Table AHL_PART_CHANGES stores path position id in mc_relationship_id field
AND APC1.part_change_type = p_bulk_process_flag
AND DECODE(APC1.part_change_type, 'R', nvl(c_instance_id,-1), 'I', APC1.installed_instance_id) = nvl(c_instance_id,-1)
AND OP1.workorder_operation_id = APC1.workorder_operation_id
AND OP1.workorder_id IN (SELECT workorder_id
FROM AHL_WORKORDERS
WHERE visit_id = c_visit_id))
AND OP.workorder_operation_id = APC.workorder_operation_id
AND RSN.reason_id(+) = APC.removal_reason_id
AND REM.LOOKUP_CODE (+) = APC.removal_code
AND REM.lookup_type (+) = 'AHL_REMOVAL_CODE';
SELECT unit_of_measure
FROM MTL_UNITS_OF_MEASURE_VL
WHERE uom_code = c_uom_code;
/* select_disable_flag will be 'Y' or HGrid rows in Bulk Part Change UIs will be read only for:
* - Root Node
* - Control Positions
* - In Bulk Part Install page, for:
* - Extra Nodes
* - Fully Installed Positions
* - Children of Control Positions if Control Position is Fully Installed
* - In Bulk Part Removal page, for:
* - Empty Positions
*/
l_uc_details_tbl(l_curr_row).select_disable_flag := 'N';
l_uc_details_tbl(l_curr_row).select_disable_flag := 'Y';
l_uc_details_tbl(l_curr_row).select_disable_flag := 'Y';
l_uc_details_tbl(l_curr_row).select_disable_flag := 'Y';
l_uc_details_tbl(l_curr_row).select_disable_flag := 'Y';
l_uc_details_tbl(l_curr_row).select_disable_flag := 'Y';
SELECT iasso.quantity Itm_qty,
iasso.uom_code Itm_uom_code,
reln.quantity Posn_qty,
reln.uom_code Posn_uom_code,
csi.inventory_item_id,
csi.quantity Inst_qty,
csi.unit_of_measure Inst_uom_code
FROM AHL_MC_RELATIONSHIPS reln,
AHL_ITEM_ASSOCIATIONS_B iasso,
CSI_ITEM_INSTANCES csi
WHERE reln.relationship_id = p_mc_relationship_id
AND csi.instance_id = p_instance_id
AND iasso.item_group_id = reln.item_group_id
AND iasso.inventory_item_id = csi.inventory_item_id
AND (iasso.revision IS NULL OR iasso.revision = csi.inventory_revision)
AND iasso.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
UNION ALL
SELECT to_number(null) Itm_qty,
to_char(null) Itm_uom_code,
quantity Posn_qty,
uom_code Posn_uom_code,
to_number(null) Inventory_item_id,
to_number(null) Inst_qty,
to_char(null) Inst_uom_code
FROM AHL_MC_RELATIONSHIPS
WHERE p_instance_id IS NULL
AND relationship_id = p_mc_relationship_id;
SELECT unit_of_measure
FROM MTL_UNITS_OF_MEASURE_VL
WHERE uom_code = c_uom_code;
SELECT unit_node_id,
relationship_id,
position,
curr_instance_id,
parent_instance_id,
qty_per_position,
unit_of_measure,
install_quantity
FROM AHL_UC_DETAILS_FOR_HGRID
WHERE root_instance_id = p_root_instance_id
AND is_controlled_position = 'Y';
SELECT unit_of_measure
FROM AHL_MC_RELATIONSHIPS RELN,
MTL_UNITS_OF_MEASURE_VL UOM
WHERE RELN.relationship_id = c_relationship_id
AND UOM.uom_code = RELN.uom_code;
SELECT NVL(SUM(curr_quantity), 0)
FROM AHL_UC_DETAILS_FOR_HGRID
WHERE root_instance_id = p_root_instance_id
AND parent_rel_id = c_relationship_id
AND NVL(parent_instance_id, -10) = NVL(c_instance_id, NVL(c_parent_instance_id, -10));
SELECT unit_node_id,
position,
qty_per_position,
install_quantity,
curr_quantity,
inst_instance_id,
instance_quantity,
select_disable_flag,
path_position_id,
curr_instance_id,
operation_sequence_num,
install_date
FROM AHL_UC_DETAILS_FOR_HGRID
WHERE root_instance_id = p_root_instance_id
AND parent_rel_id = c_relationship_id
AND NVL(parent_instance_id, -10) = NVL(c_instance_id, NVL(c_parent_instance_id, -10));
SELECT OP.operation_sequence_num,
APC.installation_date
FROM AHL_PART_CHANGES APC,
AHL_WORKORDER_OPERATIONS OP
WHERE APC.part_change_id = (SELECT max(APC1.part_change_id)
FROM AHL_PART_CHANGES APC1,
AHL_WORKORDER_OPERATIONS OP1
WHERE APC1.unit_config_header_id = p_unit_header_id
AND APC1.mc_relationship_id = c_path_position_id -- Table AHL_PART_CHANGES stores path position id in mc_relationship_id field
AND APC1.part_change_type = 'I'
AND APC1.installed_instance_id = c_instance_id
AND OP1.workorder_operation_id = APC1.workorder_operation_id
AND OP1.workorder_id IN (SELECT workorder_id
FROM AHL_WORKORDERS
WHERE visit_id = (SELECT visit_id
FROM AHL_WORKORDERS
WHERE workorder_id = p_workorder_id)))
AND OP.workorder_operation_id = APC.workorder_operation_id;
l_select_disable_flag VARCHAR2(1);
UPDATE AHL_UC_DETAILS_FOR_HGRID
SET install_quantity = control_pos_rec.install_quantity,
curr_quantity = control_pos_rec.install_quantity,
unit_of_measure = control_pos_rec.unit_of_measure
WHERE unit_node_id = control_pos_rec.unit_node_id;
l_select_disable_flag := 'Y';
l_select_disable_flag := 'N';
IF (l_select_disable_flag = 'Y' AND child_pos_rec.select_disable_flag = 'N') THEN
child_pos_rec.select_disable_flag := 'Y';
UPDATE AHL_UC_DETAILS_FOR_HGRID
SET install_quantity = child_pos_rec.install_quantity,
qty_per_position = child_pos_rec.qty_per_position,
select_disable_flag = child_pos_rec.select_disable_flag,
operation_sequence_num = child_pos_rec.operation_sequence_num,
install_date = child_pos_rec.install_date
WHERE unit_node_id = child_pos_rec.unit_node_id;
PROCEDURE Calc_Qty_On_Inst_Selection(
p_x_uc_details_rec IN OUT NOCOPY uc_details_hgrid_rec_type
) IS
-- Cursor to determine whether the given position is a child of control position
CURSOR is_child_control_pos_csr IS
SELECT 'Y', path_position_id
FROM AHL_UC_DETAILS_FOR_HGRID
WHERE root_instance_id = p_x_uc_details_rec.root_instance_id
AND relationship_id = p_x_uc_details_rec.parent_rel_id
AND NVL(curr_instance_id, NVL(parent_instance_id, -10)) = NVL(p_x_uc_details_rec.parent_instance_id, -10)
AND is_controlled_position = 'Y';
SELECT master_config_id
FROM AHL_UNIT_CONFIG_HEADERS
WHERE csi_item_instance_id = p_x_uc_details_rec.root_instance_id;
SELECT MCRS.object_attribute1
FROM AHL_MC_PATH_POSITION_NODES MCPP,
AHL_MC_RULE_STATEMENTS MCRS,
AHL_MC_HEADERS_B MCH,
AHL_MC_RULES_B MCR
WHERE MCH.mc_header_id = c_mc_header_id
AND MCPP.mc_id = MCH.mc_id
AND MCH.version_number = NVL(MCPP.version_number, MCH.version_number)
AND MCPP.position_key IN (SELECT position_key
FROM AHL_MC_PATH_POSITION_NODES
WHERE path_position_id = c_control_path_position_id)
AND MCRS.subject_id = MCPP.path_position_id
AND MCRS.subject_type = 'POSITION'
AND MCRS.operator IN ('MUST_HAVE', 'HAVE')
AND MCRS.object_type = 'TOT_CHILD_QUANTITY'
AND MCH.config_status_code = 'COMPLETE'
AND MCR.rule_id = MCRS.rule_id
AND MCR.rule_type_code = 'MANDATORY'
AND NVL(MCR.active_start_date, SYSDATE - 1) < SYSDATE
AND NVL(MCR.active_end_date, SYSDATE + 1) > SYSDATE;
SELECT NVL(SUM(curr_quantity), 0)
FROM AHL_UC_DETAILS_FOR_HGRID
WHERE root_instance_id = p_x_uc_details_rec.root_instance_id
AND parent_rel_id = p_x_uc_details_rec.parent_rel_id
AND NVL(parent_instance_id, -10) = NVL(p_x_uc_details_rec.parent_instance_id, -10);
l_api_name CONSTANT VARCHAR2(30) := 'Calc_Qty_On_Inst_Selection';
* Quanitities will always be 1 and will not change on Instance selection.
*/
IF (p_x_uc_details_rec.is_position_srl_controlled = 'N') THEN
IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Recalculating Position and Install Quantity for selected instance_id = ' || p_x_uc_details_rec.inst_instance_id);
END Calc_Qty_On_Inst_Selection;
SELECT COUNT(UHC.unit_node_id)
FROM AHL_UC_DETAILS_FOR_HGRID UHC,
AHL_UNIT_CONFIG_HEADERS UC
WHERE UC.unit_config_header_id = c_unit_header_id
AND UHC.root_instance_id = UC.csi_item_instance_id;
SELECT hgrid_node_path
FROM AHL_UC_DETAILS_FOR_HGRID
WHERE unit_node_id = c_unit_node_id;
l_sql_cri_str := ' SELECT unit_node_id FROM AHL_UC_DETAILS_FOR_HGRID WHERE 1 = 1 ';
l_sql_cri_str := l_sql_cri_str || ' AND root_instance_id = (SELECT csi_item_instance_id FROM AHL_UNIT_CONFIG_HEADERS ';
UPDATE AHL_UC_DETAILS_FOR_HGRID
SET search_flag = 'N'
WHERE search_flag = 'Y';
UPDATE AHL_UC_DETAILS_FOR_HGRID
SET search_flag = 'Y'
WHERE curr_instance_id = (SELECT csi_item_instance_id FROM AHL_UNIT_CONFIG_HEADERS WHERE unit_config_header_id = l_unit_header_id);
UPDATE AHL_UC_DETAILS_FOR_HGRID
SET search_flag = 'Y'
WHERE unit_node_id = l_current_unit_node_id;
select MTL.concatenated_segments,csi.serial_number from csi_item_instances csi, mtl_system_items_kfv MTL
where csi.instance_number like p_instance_number
and csi.inventory_item_id = MTL.inventory_item_id
and CSI.INV_MASTER_ORGANIZATION_ID = MTL.ORGANIZATION_ID;
select object_id, subject_id, root_object_id
from AHL_CONFIG_COMPONENTS A1 where root_object_id = -1
start with A1.object_id IN (select A2.object_id from AHL_CONFIG_COMPONENTS A2 where not exists(
select 'x' from AHL_CONFIG_COMPONENTS A3 where A3.subject_id = A2.object_id and root_object_id = -1)
)
AND root_object_id = -1
connect by prior subject_id = object_id
order by level desc;
select object_id, subject_id, root_object_id
from AHL_CONFIG_COMPONENTS A1 where root_object_id = -2
start with A1.object_id IN (select A2.object_id from AHL_CONFIG_COMPONENTS A2 where not exists(
select 'x' from AHL_CONFIG_COMPONENTS A3 where A3.subject_id = A2.object_id and root_object_id = -2)
)
AND root_object_id = -2
connect by prior subject_id = object_id
order by level asc;
DELETE AHL_CONFIG_COMPONENTS;
INSERT INTO ahl_config_components (object_id, subject_id, root_object_id)
VALUES (P_PARTS_REC_TBL(i).PARENT_INSTALLED_INSTANCE_NUM,P_PARTS_REC_TBL(i).REMOVED_INSTANCE_NUM,-1);
INSERT INTO ahl_config_components (object_id, subject_id, root_object_id)
VALUES (P_PARTS_REC_TBL(i).PARENT_INSTALLED_INSTANCE_NUM,P_PARTS_REC_TBL(i).INSTALLED_INSTANCE_NUM,-2);