[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ ordered rowid(a) use_nl(i t cii1 cii2 mp1 mp2) */
a.rowid, a.incident_owner_id, a.old_incident_owner_id,
a.change_incident_owner_flag, a.resource_type,
a.old_resource_type, a.change_resource_type_flag,
a.group_id, a.old_group_id, a.change_group_flag,
a.group_type, a.old_group_type, a.change_group_type_flag,
a.incident_id, a.updated_entity_code, a.updated_entity_id,
a.inv_organization_id, a.old_inv_organization_id,
cii1.instance_id, cii2.instance_id,
mp1.master_organization_id, mp2.master_organization_id,
a.customer_product_id, a.old_customer_product_id, t.maintenance_flag,
a.maint_organization_id, a.old_maint_organization_id
FROM cs_incidents_audit_b a,
cs_incidents_all_b i,
cs_incident_types_b t,
csi_item_instances cii1,
csi_item_instances cii2,
mtl_parameters mp1,
mtl_parameters mp2
WHERE a.rowid between c_start_rowid and c_end_rowid
AND a.incident_id = i.incident_id
AND i.incident_type_id = t.incident_type_id
AND a.item_serial_number = cii1.serial_number (+)
AND a.inventory_item_id = cii1.inventory_item_id (+)
AND a.inv_organization_id = mp1.organization_id (+)
AND a.old_item_serial_number = cii2.serial_number (+)
AND a.old_inventory_item_id = cii2.inventory_item_id (+)
AND a.old_inv_organization_id = mp2.organization_id (+)
AND (a.resource_type IN ('RS_GROUP', 'RS_TEAM')
OR a.old_resource_type IN ('RS_GROUP', 'RS_TEAM')
OR (a.updated_entity_code IS NULL
AND a.updated_entity_id IS NULL)
OR (a.maint_organization_id IS NULL
AND t.maintenance_flag = 'Y')
OR (a.old_incident_type_id IS NULL -- 4438560, only for creation audit
AND a.group_type IS NULL
AND a.group_id IS NOT NULL));
l_update_name VARCHAR2(30) := 'csxaownb.pls.120.0';
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_update_name,
x_worker_id,
x_num_workers,
x_batch_size, 0);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
x_batch_size,
TRUE);
UPDATE cs_incidents_audit_b
SET incident_owner_id = DECODE(res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NULL,
inc_owner_id(i)),
old_incident_owner_id = DECODE(old_res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NULL,
old_inc_owner_id(i)),
change_incident_owner_flag = DECODE(
DECODE(res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NULL,
inc_owner_id(i)),
DECODE(old_res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NULL,
old_inc_owner_id(i)),
'N', 'Y'),
resource_type = DECODE(res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NULL,
res_type(i)),
old_resource_type = DECODE(old_res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NULL,
old_res_type(i)),
change_resource_type_flag = DECODE(
DECODE(res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NULL,
res_type(i)),
DECODE(old_res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NULL,
old_res_type(i)),
'N', 'Y'),
--item_serial_number = NULL,
--old_item_serial_number = NULL,
group_id = DECODE(res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NVL(grp_id(i), inc_owner_id(i)),
DECODE(grp_type(i), 'RS_TEAM', NULL, grp_id(i))),
old_group_id = DECODE(old_res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NVL(old_grp_id(i), old_inc_owner_id(i)),
DECODE(old_grp_type(i), 'RS_TEAM', NULL, old_grp_id(i))),
change_group_flag = DECODE(
DECODE(res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NVL(grp_id(i), inc_owner_id(i)),
DECODE(grp_type(i), 'RS_TEAM', NULL, grp_id(i))),
DECODE(old_res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NVL(old_grp_id(i), old_inc_owner_id(i)),
DECODE(old_grp_type(i), 'RS_TEAM', NULL, old_grp_id(i))),
'N', 'Y'), --change_own_flag(i),
group_type = DECODE(res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NVL(grp_type(i), res_type(i)),
DECODE(grp_type(i),
'RS_TEAM', NULL,
DECODE(old_incident_type_id, -- only for creation audit
NULL, DECODE(grp_id(i),
NULL, NULL, NVL(grp_type(i), 'RS_GROUP')), -- if group id is not null, set group type to RS_GROUP
grp_type(i)))),
old_group_type = DECODE(old_res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NVL(old_grp_type(i), old_res_type(i)),
decode(old_grp_type(i), 'RS_TEAM', NULL, old_grp_type(i))),
change_group_type_flag = DECODE(DECODE(res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NVL(grp_type(i), res_type(i)),
DECODE(grp_type(i),
'RS_TEAM', NULL,
DECODE(old_incident_type_id,
NULL, DECODE(grp_id(i),
NULL, NULL, NVL(grp_type(i), 'RS_GROUP')),
grp_type(i)))),
DECODE(old_res_type(i),
'RS_TEAM', NULL,
'RS_GROUP', NVL(old_grp_type(i), old_res_type(i)),
DECODE(old_grp_type(i), 'RS_TEAM', NULL, old_grp_type(i))),
'N', 'Y'), --change_res_type_flag(i),
updated_entity_code = NVL(upd_entity_code(i), 'SR_HEADER'),
updated_entity_id = NVL(upd_entity_id(i), inc_id(i)),
maint_organization_id = NVL(maint_org_id1(i),
DECODE(maint_flag(i), 'Y', inv_org_id1(i), NULL)),
old_maint_organization_id = NVL(maint_org_id2(i),
DECODE(maint_flag(i), 'Y', inv_org_id2(i), NULL)),
inv_organization_id = DECODE(maint_flag(i),
'Y', DECODE(maint_org_id1(i), NULL, master_org_id1(i), inv_org_id1(i)),
inv_org_id1(i)),
old_inv_organization_id = DECODE(maint_flag(i),
'Y', DECODE(maint_org_id2(i), NULL, master_org_id2(i), inv_org_id2(i)),
inv_org_id2(i)),
change_inv_organization_flag = DECODE(
DECODE(maint_flag(i), 'Y', DECODE(maint_org_id1(i), NULL, master_org_id1(i), inv_org_id1(i)), inv_org_id1(i)),
DECODE(maint_flag(i), 'Y', DECODE(maint_org_id2(i), NULL, master_org_id2(i), inv_org_id2(i)), inv_org_id2(i)),
'N', 'Y'),
customer_product_id = DECODE(maint_flag(i), 'Y', DECODE(maint_org_id1(i), NULL, eam_inst_id1(i), cust_prod_id1(i)), cust_prod_id1(i)),
old_customer_product_id = DECODE(maint_flag(i), 'Y', DECODE(maint_org_id2(i), NULL, eam_inst_id2(i), cust_prod_id2(i)), cust_prod_id2(i)),
change_customer_product_flag = DECODE(
DECODE(maint_flag(i), 'Y', decode(maint_org_id1(i), NULL, eam_inst_id1(i), cust_prod_id1(i)), cust_prod_id1(i)),
DECODE(maint_flag(i), 'Y', decode(maint_org_id2(i), NULL, eam_inst_id2(i), cust_prod_id2(i)), cust_prod_id2(i)),
'N', 'Y')
WHERE rowid = rowid_arr(i);
ad_parallel_updates_pkg.processed_rowid_range(
l_cur_fetch,
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
x_batch_size,
FALSE);