[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT crl.repair_line_id,
wdj.wip_entity_id,
wdj.organization_id,
wdj.start_quantity,
wdj.primary_item_id,
crl.inventory_item_id,
crl.serial_number,
msi.primary_uom_code,
we.wip_entity_name,
csc.name,
csc.service_code_id,
cii.quantity
BULK COLLECT INTO l_post_wipml_tbl
FROM csd_recall_lines crl,
wip_entities we,
wip_discrete_jobs wdj,
csd_service_codes_tl csc,
mtl_system_items_b msi,
csi_item_instances cii
WHERE crl.processing_group_id = p_rec_group_id
AND crl.repair_line_id = wdj.source_line_id
AND NVL(g_upgrade_item_id,crl.inventory_item_id) = wdj.primary_item_id
AND wdj.wip_entity_id = we.wip_entity_id
AND csc.service_code_id = g_sc_id
AND csc.language = userenv('lang')
AND msi.organization_id = wdj.organization_id
AND msi.inventory_item_id = crl.inventory_item_id
AND crl.instance_id = cii.instance_id;
p_last_updated_by => l_user_id,
p_last_update_date => SYSDATE,
p_last_update_login => l_user_id,
p_repair_line_id => l_post_wipml_tbl(i).repair_line_id,
p_wip_entity_id => l_post_wipml_tbl(i).wip_entity_id,
p_group_id => p_req_group_id,
p_organization_id => l_post_wipml_tbl(i).organization_id,
p_quantity => l_post_wipml_tbl(i).quantity,
p_inventory_item_id => l_post_wipml_tbl(i).inventory_item_id,
p_item_revision => NULL,
p_object_version_number => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_quantity_completed => NULL,
p_job_name => l_post_wipml_tbl(i).job_name,
p_source_type_code => l_post_wipml_tbl(i).service_code,
p_source_id1 => l_post_wipml_tbl(i).service_code_id,
p_ro_service_code_id => l_post_wipml_tbl(i).service_code_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_program_update_date => NULL,
p_created_by => l_user_id,
p_creation_date => SYSDATE,
p_last_updated_by => l_user_id,
p_last_update_date => SYSDATE,
p_repair_line_id => l_post_wipml_tbl(i).repair_line_id,
p_event_code => 'JS',
p_event_date => SYSDATE,
p_quantity => l_post_wipml_tbl(i).quantity,
p_paramn1 => l_post_wipml_tbl(i).wip_entity_id,
p_paramn2 => l_post_wipml_tbl(i).organization_id,
p_paramn3 => NULL,
p_paramn4 => NULL,
p_paramn5 => l_post_wipml_tbl(i).quantity,
p_paramn6 => NULL,
p_paramn8 => NULL,
p_paramn9 => NULL,
p_paramn10 => NULL,
p_paramc1 => l_post_wipml_tbl(i).job_name,
p_paramc2 => NULL,
p_paramc3 => NULL,
p_paramc4 => NULL,
p_paramc5 => NULL,
p_paramc6 => NULL,
p_paramc7 => NULL,
p_paramc8 => NULL,
p_paramc9 => NULL,
p_paramc10 => NULL,
p_paramd1 => NULL ,
p_paramd2 => NULL ,
p_paramd3 => NULL ,
p_paramd4 => NULL ,
p_paramd5 => SYSDATE,
p_paramd6 => NULL ,
p_paramd7 => NULL ,
p_paramd8 => NULL ,
p_paramd9 => NULL ,
p_paramd10 => NULL ,
p_attribute_category => NULL ,
p_attribute1 => NULL ,
p_attribute2 => NULL ,
p_attribute3 => NULL ,
p_attribute4 => NULL ,
p_attribute5 => NULL ,
p_attribute6 => NULL ,
p_attribute7 => NULL ,
p_attribute8 => NULL ,
p_attribute9 => NULL ,
p_attribute10 => NULL ,
p_attribute11 => NULL ,
p_attribute12 => NULL ,
p_attribute13 => NULL ,
p_attribute14 => NULL ,
p_attribute15 => NULL ,
p_last_update_login => l_user_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT MIN(operation_seq_num)
INTO l_operation_seq_num
FROM wip_operations
WHERE wip_entity_id = l_post_wipml_tbl(i).wip_entity_id;
SELECT department_id, supply_subinventory
INTO l_department_id, l_supply_subinventory
FROM csd_recall_parameters
WHERE organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG');
SELECT department_id, supply_subinventory
INTO l_department_id, l_supply_subinventory
FROM csd_recall_parameters
WHERE organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG');
SELECT cii.instance_number,
crl.serial_number,
cr.repair_number,
cs.incident_number,
cr.quantity,
msi.concatenated_segments
FROM csd_recall_lines crl,
csi_item_instances cii,
csd_repairs cr,
cs_incidents_all_b cs,
mtl_system_items_kfv msi
WHERE crl.processing_group_id = p_group_id
AND crl.instance_id = cii.instance_id
AND crl.repair_line_id = cr.repair_line_id (+)
AND crl.incident_id = cs.incident_id (+)
AND crl.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG');
SELECT recall_number
INTO l_recall_number
FROM csd_recall_lines crl,
csd_recall_headers_b crh
WHERE crl.processing_group_id = p_group_id
AND crl.recall_id = crh.recall_id
AND rownum < 2;
Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Update the recall lines as processing');
SELECT csd_recall_lines_group_s1.NEXTVAL INTO l_group_id FROM dual;
-- update the selected recall line id's as processing flag = 'Y';
UPDATE CSD_RECALL_LINES crl SET processing_flag = 'Y', processing_group_id = l_group_id
WHERE crl.recall_line_id IN (SELECT * FROM TABLE(CAST(p_recall_line_ids as JTF_NUMBER_TABLE)))
AND nvl(crl.processing_flag,'N') = 'N';
UPDATE CSD_RECALL_LINES crl SET processing_flag = 'N'
WHERE crl.recall_line_id IN (SELECT * FROM TABLE(CAST(p_recall_line_ids as JTF_NUMBER_TABLE)))
AND nvl(crl.processing_flag,'N') = 'Y'
AND crl.processing_group_id = l_group_id;
/* all the successful recall lines. Updates the csd_recall_lines table */
/* with the SR id, RO line id and wip entity id when done with the */
/* processing. Once done, will reset the processing_flag to N */
/* -- History: 24/03/2010, Created by Sudheer Bhat. */
/****************************************************************************************/
PROCEDURE PROCESS_RECALL_WORK (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_group_id IN NUMBER,
p_sr_type_id IN NUMBER,
p_ro_type_id IN NUMBER DEFAULT NULL,
p_service_code_id IN NUMBER DEFAULT NULL,
p_wip_accounting_class IN VARCHAR2 DEFAULT NULL,
p_upgrade_item_id IN NUMBER,
p_wip_inv_org_id IN NUMBER )
IS
lc_api_name CONSTANT VARCHAR2(60) := 'CSD.PLSQL.CP.CSD_RECALLS_PVT.CSD_RECALLS_PVT';
SELECT crl.recall_line_id,
crl.instance_id,
crl.owner_account_id,
crl.owner_party_id,
crl.inventory_item_id,
crl.revision,
crl.serial_number,
crl.lot_number,
crl.incident_id,
crl.repair_line_id,
crl.wip_entity_id,
cii.unit_of_measure,
cii.quantity
FROM csd_recall_lines crl,csi_item_instances cii
WHERE crl.processing_group_id = p_group_id
AND crl.processing_flag = 'Y'
AND crl.instance_id = cii.instance_id;
UPDATE csd_recall_lines SET processing_flag = 'N'
WHERE processing_group_id = l_rec_group_id;
SELECT hpu.party_site_use_id,
hpu1.party_site_use_id
INTO g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id,
g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id
FROM hz_party_sites hps,
hz_party_site_uses hpu,
hz_party_sites hps1,
hz_party_site_uses hpu1
WHERE hps.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
AND hps.party_site_id = hpu.party_site_id
AND hpu.site_use_type = 'BILL_TO'
AND hpu.primary_per_type = 'Y'
AND hps1.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
AND hps1.party_site_id = hpu1.party_site_id
AND hpu1.site_use_type = 'SHIP_TO'
AND hpu1.primary_per_type = 'Y'
AND rownum < 2;
SELECT party_type
INTO g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).caller_type
FROM hz_parties
WHERE party_id = l_csd_recall_lines_tbl(l_index).owner_party_id;
SELECT sr_status,
sr_severity,
sr_urgency,
sr_owner,
sr_summary
INTO g_sr_status,
g_sr_severity,
g_sr_urgency,
g_sr_owner,
g_sr_summary
FROM csd_recall_parameters
WHERE organization_id = FND_PROFILE.VALUE('CSD_DEF_REP_INV_ORG');
UPDATE csd_recall_lines SET incident_id = x_incident_id
WHERE recall_line_id = l_csd_recall_lines_tbl(l_index).recall_line_id;
SELECT auto_process_rma,
business_process_id,
repair_mode
INTO g_auto_process_rma,
g_business_process_id,
g_repair_mode
FROM csd_repair_types_b
WHERE repair_type_id = p_ro_type_id;
SELECT hpu.party_site_use_id,
hpu1.party_site_use_id
INTO g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).bill_to_site_use_id,
g_csd_shipto_billto_cache(l_csd_recall_lines_tbl(l_index).owner_party_id).ship_to_site_use_id
FROM hz_party_sites hps,
hz_party_site_uses hpu,
hz_party_sites hps1,
hz_party_site_uses hpu1
WHERE hps.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
AND hps.party_site_id = hpu.party_site_id
AND hpu.site_use_type = 'BILL_TO'
AND hpu.primary_per_type = 'Y'
AND hps1.party_id = l_csd_recall_lines_tbl(l_index).owner_party_id
AND hps1.party_site_id = hpu1.party_site_id
AND hpu1.site_use_type = 'SHIP_TO'
AND hpu1.primary_per_type = 'Y'
AND rownum < 2;
UPDATE csd_recall_lines SET repair_line_id = x_repair_line_id
WHERE recall_line_id = l_csd_recall_lines_tbl(l_index).recall_line_id;
SELECT wip_job_status
INTO l_job_status
FROM csd_recall_parameters
WHERE organization_id = FND_PROFILE.VALUE('CSD_DEF_REP_INV_ORG');
SELECT bom.assembly_item_id bom_reference_id,
bom.alternate_bom_designator,
bor.assembly_item_id routing_reference_id,
bor.alternate_routing_designator,
bor.completion_subinventory,
bor. completion_locator_id
BULK COLLECT INTO
g_bill_id,
g_alt_bill,
g_routing_id,
g_alt_routing,
g_completion_subinv,
g_completion_locid
FROM csd_sc_work_entities cscwe,
bom_bill_of_materials bom ,
bom_operational_routings bor
WHERE cscwe.service_code_id = p_service_code_id
AND cscwe.work_entity_type_code = 'BOM'
AND cscwe.work_entity_id3 = fnd_profile.value('csd_def_rep_inv_org')
AND cscwe.work_entity_id1 = bom.bill_sequence_id (+)
AND cscwe.work_entity_id2 = bor.routing_sequence_id (+);
l_job_header_tbl(l_job_header_index).last_update_date := SYSDATE;
l_job_header_tbl(l_job_header_index).last_updated_by := fnd_global.user_id;
l_job_header_tbl(l_job_header_index).last_update_login := fnd_global.login_id;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
l_job_header_tbl(l_job_header_index).last_update_date := SYSDATE;
l_job_header_tbl(l_job_header_index).last_updated_by := fnd_global.user_id;
l_job_header_tbl(l_job_header_index).last_update_login := fnd_global.login_id;
SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
UPDATE csd_recall_lines SET processing_flag = 'N'
WHERE processing_group_id = p_group_id;
-- insert the job header rec into wip_job_schedule_interface_table and call the CP.
FORALL j IN 1 ..l_job_header_tbl.COUNT
INSERT INTO wip_job_schedule_interface VALUES l_job_header_tbl(j);
UPDATE csd_recall_lines SET processing_flag = 'N'
WHERE processing_group_id = p_group_id;
UPDATE csd_recall_lines SET processing_flag = 'N'
WHERE processing_group_id = p_group_id;
SELECT metric_id
INTO l_metric_ids(1)
FROM csd_recall_metrics
WHERE recall_number = p_recall_number;
INSERT INTO csd_recall_metrics(metric_id,recall_number) values(csd_recall_metrics_s1.NEXTVAL,p_recall_number)
RETURNING metric_id INTO l_metric_ids(1);
INSERT INTO csd_recall_metrics(metric_id,recall_number)
(SELECT csd_recall_metrics_s1.NEXTVAL,recall_number
FROM csd_recall_headers_b,csd_recall_statuses_b crs
WHERE recall_flow_status_id = crs.status_id
AND crs.status_code <> 'C'
AND NOT EXISTS ( SELECT 'exists'
FROM csd_recall_metrics crm
WHERE crm.recall_number = recall_number )
AND recall_id in (SELECT MAX(recall_id) FROM csd_recall_headers_b
GROUP BY recall_number ));
SELECT DISTINCT metric_id
BULK COLLECT INTO l_metric_ids
FROM csd_recall_metrics crm,
csd_recall_headers_b crh,
csd_recall_statuses_b crs
WHERE crh.recall_number = crm.recall_number
AND crh.recall_flow_status_id = crs.status_id
AND crs.status_code <> 'C'
AND crm.metric_id NOT IN (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)));
SELECT crh.recall_number,SUM(cii.quantity)
BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
FROM csd_recall_metrics crm,
csd_recall_headers_b crh,
csd_recall_lines crl,
csi_item_instances cii
WHERE crm.metric_id IN (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
AND crm.recall_number = crh.recall_number
AND crh.recall_id = crl.recall_id
AND crl.instance_id = cii.instance_id
GROUP BY crh.recall_number;
UPDATE csd_recall_metrics SET recalled_units = l_gen_num_tbl(i)
WHERE recall_number = l_recall_numbers(i);
--dbms_output.put_line('after for all update');
SELECT crh.recall_number, COUNT(DISTINCT crl.owner_party_id)
BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
FROM csd_recall_metrics crm,
csd_recall_headers_b crh,
csd_recall_lines crl
WHERE crm.metric_id IN (SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
AND crm.recall_number = crh.recall_number
AND crh.recall_id = crl.recall_id
GROUP BY crh.recall_number;
UPDATE csd_recall_metrics SET customers_impacted = l_gen_num_tbl(i)
WHERE recall_number = l_recall_numbers(i);
--dbms_output.put_line('after for all update');
SELECT crh.recall_number,
SUM(wpb.tl_resource_in+pl_resource_in+tl_overhead_in+pl_material_in+pl_material_overhead_in+pl_overhead_in)
BULK COLLECT INTO l_recall_numbers, l_accumulated_cost
FROM csd_recall_metrics crm,
csd_recall_headers_b crh,
csd_recall_lines crl,
csd_repair_job_xref crj,
wip_period_balances wpb
WHERE crm.metric_id IN
(SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
AND crm.recall_number = crh.recall_number
AND crh.recall_id = crl.recall_id
AND crl.repair_line_id = crj.repair_line_id
AND crj.wip_entity_id = wpb.wip_entity_id
GROUP BY crh.recall_number
ORDER BY crh.recall_number;
UPDATE csd_recall_metrics SET accumulated_costs = l_accumulated_cost(i)
WHERE recall_number = l_recall_numbers(i);
SELECT recall_number,
SUM(QUANTITY)
BULK COLLECT INTO l_recall_numbers, l_wip_jobs_without_costs
FROM
(SELECT crh.recall_number,
SUM(crj.quantity) quantity
FROM csd_recall_metrics crm,
csd_recall_headers_b crh,
csd_recall_lines crl,
csd_repair_job_xref crj
WHERE crm.metric_id IN
(SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)) )
AND crm.recall_number = crh.recall_number
AND crh.recall_id = crl.recall_id
AND crl.repair_line_id = crj.repair_line_id
AND NOT EXISTS
(SELECT 'exists'
FROM wip_period_balances wpb,
wip_discrete_jobs wdj
WHERE wpb.wip_entity_id = crj.wip_entity_id
AND wpb.wip_entity_id = wdj.wip_entity_id
AND wdj.status_type NOT IN (7,12,4,5)
GROUP BY wpb.wip_entity_id
HAVING SUM(tl_resource_in+pl_resource_in+tl_overhead_in+pl_material_in+pl_material_overhead_in+pl_overhead_in) > 0
)
GROUP BY crh.recall_number
UNION ALL
SELECT crh.recall_number,
SUM(cii.quantity) quantity
FROM csd_recall_metrics crm,
csd_recall_headers_b crh,
csd_recall_lines crl,
csi_item_instances cii,
cs_incidents_all_b sr,
cs_incident_statuses_b cis
WHERE crm.metric_id IN
(SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
AND crm.recall_number = crh.recall_number
AND crh.recall_id = crl.recall_id
AND crl.instance_id = cii.instance_id
AND crl.incident_id IS NOT NULL
AND crl.incident_id = sr.incident_id
AND sr.incident_status_id = cis.incident_status_id
AND cis.status_code <> 'CLOSED'
AND NOT EXISTS
(SELECT 'exists'
FROM csd_repairs cr,
csd_repair_job_xref crj1
WHERE cr.incident_id = crl.incident_id
AND cr.repair_line_id = crj1.repair_line_id
)
GROUP BY crh.recall_number
)
GROUP BY recall_number
ORDER BY recall_number;
SELECT crh.recall_number, SUM(crj.quantity)
BULK COLLECT INTO l_recall_numbers,l_wip_jobs_with_costs
FROM csd_recall_headers_b crh,
csd_recall_lines crl,
csd_repair_job_xref crj
WHERE crh.recall_number IN
( SELECT * FROM TABLE(CAST(l_recall_numbers_temp AS JTF_VARCHAR2_TABLE_100)))
AND crh.recall_id = crl.recall_id
AND crl.repair_line_id = crj.repair_line_id
AND EXISTS (
SELECT 'exists'
FROM wip_period_balances
WHERE wip_entity_id = crj.wip_entity_id
GROUP BY wip_entity_id
HAVING SUM(tl_resource_in+pl_resource_in+tl_overhead_in+pl_material_in+pl_material_overhead_in+pl_overhead_in) > 0)
GROUP BY crh.recall_number
ORDER BY crh.recall_number;
-- update the actual cost, and estimated cost remaining.
FORALL i IN 1 ..l_recall_numbers.COUNT
UPDATE csd_recall_metrics SET remaining_cost = (l_wip_jobs_without_costs(i) * (accumulated_costs/l_wip_jobs_with_costs(i)))
WHERE recall_number = l_recall_numbers(i);
SELECT crh.recall_number,
SUM(cii.quantity)
BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
FROM csd_recall_metrics crm,
csd_recall_headers_b crh,
csd_recall_lines crl,
cs_incidents_all_b sr,
cs_incident_statuses_b cis,
csi_item_instances cii
WHERE crm.metric_id IN
(SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
AND crm.recall_number = crh.recall_number
AND crh.recall_id = crl.recall_id
AND crl.incident_id = sr.incident_id
AND sr.incident_status_id = cis.incident_status_id
AND cis.status_code = 'CLOSED'
AND crl.instance_id = cii.instance_id
GROUP BY crh.recall_number;
UPDATE csd_recall_metrics SET remediated_units = 0
WHERE metric_id = l_metric_ids(i)
AND recall_number NOT IN
(SELECT * FROM TABLE(CAST(l_recall_numbers AS JTF_VARCHAR2_TABLE_100))) ;
UPDATE csd_recall_metrics SET remediated_units = l_gen_num_tbl(i)
WHERE recall_number = l_recall_numbers(i);
UPDATE csd_recall_metrics SET un_remediated_units = (recalled_units-nvl(remediated_units,0))
WHERE metric_id = l_metric_ids(i);
SELECT recall_number, COUNT(party_id)
BULK COLLECT INTO l_recall_numbers,l_gen_num_tbl
FROM (
SELECT crh.recall_number,
COUNT(crl.owner_party_id) party_id
FROM csd_recall_metrics crm,
csd_recall_headers_b crh,
csd_recall_lines crl,
cs_incidents_all_b sr,
cs_incident_statuses_b cis
WHERE crm.metric_id IN
(SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
AND crm.recall_number = crh.recall_number
AND crh.recall_id = crl.recall_id
AND crl.incident_id = sr.incident_id
AND sr.incident_status_id = cis.incident_status_id
AND cis.status_code = 'CLOSED'
AND NOT EXISTS
(SELECT 'exists'
FROM csd_recall_headers_b crh1,
csd_recall_lines crl1,
cs_incidents_all_b sr1,
cs_incident_statuses_b cis1,
csd_recall_metrics crm1
WHERE crm1.metric_id IN
(SELECT * FROM TABLE(CAST(l_metric_ids AS JTF_NUMBER_TABLE)))
AND crm1.recall_number = crh1.recall_number
AND crh1.recall_id = crl1.recall_id
AND crl1.owner_party_id = crl.owner_party_id
AND crl1.incident_id = sr1.incident_id
AND sr1.incident_status_id = cis1.incident_status_id
AND cis1.status_code <> 'CLOSED'
)
GROUP BY crh.recall_number,crl.owner_party_id
) GROUP BY recall_number;
UPDATE csd_recall_metrics SET customers_remediated = 0
WHERE metric_id = l_metric_ids(i)
AND recall_number NOT IN
(SELECT * FROM TABLE(CAST(l_recall_numbers AS JTF_VARCHAR2_TABLE_100))) ;
UPDATE csd_recall_metrics SET customers_remediated = NVL(l_gen_num_tbl(i),0)
WHERE recall_number = l_recall_numbers(i);
-- customers un remediated. also update the WHO columns.
FORALL i IN 1 ..l_metric_ids.COUNT
UPDATE csd_recall_metrics SET customers_un_remediated = (customers_impacted-nvl(customers_remediated,0)),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.user_id,
object_version_number = object_version_number+1
WHERE metric_id = l_metric_ids(i);