Db app
-- Insert new records into master table
INSERT INTO $master_table (
parent,
parent_sys_class_name,
parent_u_cmdb_ci_used_for,
parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code,
parent_ref_cmdb_ci_service_u_cmdb_ci_ser,
modified_date
)
SELECT
c.parent,
c.parent_sys_class_name,
c.parent_u_cmdb_ci_used_for,
c.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code,
c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser,
c.modified_date
FROM $child_table c
LEFT JOIN $master_table m ON c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser = m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser
WHERE m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser IS NULL;
-- Log inserted rows
SPOOL $inserted_rows_file
SELECT
c.parent,
c.parent_sys_class_name,
c.parent_u_cmdb_ci_used_for,
c.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code,
c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser,
c.modified_date
FROM $child_table c
LEFT JOIN $master_table m ON c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser = m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser
WHERE m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser IS NULL;
SPOOL OFF;
-- Update modified records in master table
MERGE INTO $master_table m
USING $child_table c
ON (m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser = c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser)
WHEN MATCHED THEN
UPDATE SET
m.parent = c.parent,
m.parent_sys_class_name = c.parent_sys_class_name,
m.parent_u_cmdb_ci_used_for = c.parent_u_cmdb_ci_used_for,
m.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code = c.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code,
m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser = c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser,
m.modified_date = SYSDATE
WHERE
m.parent != c.parent OR
m.parent_sys_class_name != c.parent_sys_class_name OR
m.parent_u_cmdb_ci_used_for != c.parent_u_cmdb_ci_used_for OR
m.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code != c.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code OR
m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser != c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser;
-- Log updated rows
SPOOL $updated_rows_file
SELECT
c.parent,
c.parent_sys_class_name,
c.parent_u_cmdb_ci_used_for,
c.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code,
c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser,
c.modified_date
FROM $child_table c
JOIN $master_table m ON c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser = m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser
WHERE
m.parent != c.parent OR
m.parent_sys_class_name != c.parent_sys_class_name OR
m.parent_u_cmdb_ci_used_for != c.parent_u_cmdb_ci_used_for OR
m.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code != c.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code OR
m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser != c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser;
SPOOL OFF;
Comments
Post a Comment