[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inst_interface_id
FROM csi_instance_interface
WHERE trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status IN ('X','R')
AND parallel_worker_id IS NULL
AND transaction_identifier IS NOT NULL
AND source_system_name = nvl(p_source_system_name,source_system_name);
SELECT ip_interface_id
FROM csi_i_party_interface cpi
WHERE cpi.party_source_table = 'HZ_PARTIES'
AND cpi.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier IS NOT NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status IN ('X','R')
AND parallel_worker_id IS NULL
AND source_system_name =
nvl(p_source_system_name,source_system_name));
SELECT ip_interface_id
FROM csi_i_party_interface cpi
WHERE cpi.party_source_table = 'EMPLOYEE'
AND cpi.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier IS NOT NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status IN ('X','R')
AND parallel_worker_id IS NULL
AND source_system_name =
nvl(p_source_system_name,source_system_name));
SELECT ip_interface_id
FROM csi_i_party_interface cpi
WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier IS NOT NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status IN ('X','R')
AND parallel_worker_id IS NULL
AND source_system_name =
nvl(p_source_system_name,source_system_name));
SELECT ieav_interface_id
FROM csi_iea_value_interface a
WHERE a.attribute_level = 'ITEM'
AND a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier IS NOT NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status IN ('X','R')
AND parallel_worker_id IS NULL
AND source_system_name =
nvl(p_source_system_name,source_system_name));
SELECT ia_interface_id
FROM csi_i_asset_interface a
WHERE a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier IS NOT NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status IN ('X','R')
AND parallel_worker_id IS NULL
AND source_system_name =
nvl(p_source_system_name,source_system_name));
UPDATE csi_instance_interface a
SET a.inventory_item_id =
(SELECT inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments =
a.inv_concatenated_segments
AND ROWNUM=1)
WHERE inst_interface_id=inst_intf_id_upd(i1)
AND a.inventory_item_id IS NULL
AND a.inv_concatenated_segments IS NOT NULL;
UPDATE csi_instance_interface a
SET a.inv_vld_organization_id =
(SELECT organization_id
FROM hr_all_organization_units
WHERE name = a.inv_vld_organization_name)
WHERE inst_interface_id=inst_intf_id_upd(i2)
AND a.inv_vld_organization_id IS NULL
AND a.inv_vld_organization_name IS NOT NULL;
UPDATE csi_instance_interface a
SET a.instance_condition_id =
(SELECT status_id
FROM mtl_material_statuses
WHERE status_code = a.instance_condition)
WHERE inst_interface_id=inst_intf_id_upd(i3)
AND a.instance_condition_id IS NULL
AND a.instance_condition IS NOT NULL;
UPDATE csi_instance_interface a
SET a.instance_status_id =
(SELECT instance_status_id
FROM csi_instance_statuses
WHERE name = a.instance_status)
WHERE inst_interface_id=inst_intf_id_upd(i4)
AND a.instance_status_id IS NULL
AND a.instance_status IS NOT NULL;
UPDATE csi_instance_interface a
SET a.system_id = (SELECT system_id
FROM csi_systems_b
WHERE system_number = a.system_number)
WHERE inst_interface_id=inst_intf_id_upd(i6)
AND a.system_id IS NULL
AND a.system_number IS NOT NULL;
UPDATE csi_instance_interface a
SET a.unit_of_measure_code =
(SELECT uom_code
FROM mtl_units_of_measure_vl
WHERE unit_of_measure_tl = a.unit_of_measure)
WHERE inst_interface_id=inst_intf_id_upd(i7)
AND a.unit_of_measure_code IS NULL
AND a.unit_of_measure IS NOT NULL;
UPDATE csi_instance_interface a
SET a.inv_organization_id =
(SELECT organization_id
FROM hr_all_organization_units
WHERE NAME = a.inv_organization_name)
WHERE inst_interface_id=inst_intf_id_upd(i8)
AND a.inv_organization_id IS NULL
AND a.inv_organization_name IS NOT NULL;
UPDATE csi_instance_interface a
SET a.project_id = (SELECT project_id
FROM pa_projects_all
WHERE segment1 = a.project_number)
WHERE inst_interface_id=inst_intf_id_upd(i9)
AND a.project_id IS NULL
AND a.project_number IS NOT NULL;
UPDATE csi_instance_interface a
SET a.task_id = (SELECT task_id
FROM pa_tasks pt,
pa_projects_all pp
WHERE pt.task_number = a.task_number
AND pp.segment1 = a.project_number
AND pt.project_id = pp.project_id)
WHERE inst_interface_id=inst_intf_id_upd(i10)
AND a.task_id IS NULL
AND a.task_number IS NOT NULL
AND a.project_number IS NOT NULL;
UPDATE csi_instance_interface a
SET a.wip_job_id = (SELECT wip_entity_id
FROM wip_entities
WHERE wip_entity_name = a.wip_job_name)
WHERE inst_interface_id=inst_intf_id_upd(i11)
AND a.wip_job_id IS NULL
AND a.wip_job_name IS NOT NULL;
UPDATE csi_instance_interface a
SET a.operating_unit=(SELECT organization_id
FROM hr_operating_units
WHERE name = a.operating_unit_name)
WHERE inst_interface_id=inst_intf_id_upd(i16)
AND a.operating_unit IS NULL
AND a.operating_unit_name IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.party_id = (SELECT party_id
FROM hz_parties
WHERE party_name = cpi.party_name
AND party_number = NVL(cpi.party_number,party_number))
WHERE ip_interface_id=ip_intf_id_upd1(i1)
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.contact_party_id =
(SELECT party_id
FROM hz_parties
WHERE party_name = cpi.contact_party_name
AND party_number = NVL(cpi.contact_party_number,party_number))
WHERE ip_interface_id=ip_intf_id_upd1(i2)
AND cpi.contact_party_id IS NULL
AND cpi.contact_party_name IS NOT NULL;
UPDATE CSI_I_PARTY_INTERFACE cpi
SET party_id=(SELECT vendor_id
FROM po_vendors
WHERE vendor_name = cpi.party_name
)
WHERE cpi.party_source_table = 'PO_VENDORS'
AND cpi.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier IS NOT NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status IN ('X','R')
AND parallel_worker_id IS NULL
AND source_system_name =
nvl(p_source_system_name,source_system_name))
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.party_id = (SELECT person_id
FROM per_all_people_f
WHERE full_name = cpi.party_name)
WHERE ip_interface_id=ip_intf_id_upd2(i1)
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.contact_party_id=
(SELECT party_id
FROM hz_parties
WHERE party_name = cpi.contact_party_name
AND party_number = NVL(cpi.contact_party_number,party_number))
WHERE ip_interface_id=ip_intf_id_upd2(i2)
AND cpi.contact_party_id IS NULL
AND cpi.contact_party_name IS NOT NULL;
UPDATE CSI_I_PARTY_INTERFACE cpi
SET party_id=(SELECT team_id
FROM jtf_rs_teams_vl
WHERE team_name = cpi.party_name)
WHERE cpi.party_source_table = 'TEAM'
AND cpi.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier IS NOT NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND parallel_worker_id IS NULL
AND source_system_name = nvl(p_source_system_name,source_system_name))
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE CSI_I_PARTY_INTERFACE cpi
SET party_id = (SELECT group_id
FROM jtf_rs_groups_vl
WHERE group_name = cpi.party_name)
WHERE cpi.party_source_table = 'GROUP'
AND cpi.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier IS NOT NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status IN ('X','R')
AND parallel_worker_id IS NULL
AND source_system_name = nvl(p_source_system_name,source_system_name))
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.party_account1_id=
(SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number = cpi.party_account1_number
AND party_id = cpi.party_id)
WHERE ip_interface_id=ip_intf_id_upd3(i1)
AND cpi.party_account1_id IS NULL
AND cpi.party_account1_number IS NOT NULL
AND cpi.party_id IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.party_account2_id=
(SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number = cpi.party_account2_number
AND party_id = cpi.party_id)
WHERE ip_interface_id=ip_intf_id_upd3(i2)
AND cpi.party_account2_id IS NULL
AND cpi.party_account2_number IS NOT NULL
AND cpi.party_id IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.party_account3_id=
(SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number = cpi.party_account3_number
AND party_id = cpi.party_id)
WHERE ip_interface_id=ip_intf_id_upd3(i3)
AND cpi.party_account3_id IS NULL
AND cpi.party_account3_number IS NOT NULL
AND cpi.party_id IS NOT NULL;
UPDATE csi_i_asset_interface a
SET a.fa_asset_id = (SELECT asset_id
FROM fa_additions_b
WHERE asset_number =
a.fa_asset_number
)
WHERE a.ia_interface_id=asst_intf_id_upd(asst1)
AND a.fa_asset_id IS NULL
AND a.fa_asset_number IS NOT NULL;
UPDATE csi_iea_value_interface a
SET a.inventory_item_id =
(SELECT inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments =
a.inv_concatenated_segments
AND ROWNUM=1)
WHERE ieav_interface_id=iea_intf_id_upd(i1)
AND a.inventory_item_id IS NULL
AND a.inv_concatenated_segments IS NOT NULL;
UPDATE csi_iea_value_interface a
SET a.master_organization_id =
(SELECT organization_id
FROM hr_all_organization_units
WHERE name = master_organization_name)
WHERE ieav_interface_id=iea_intf_id_upd(i2)
AND a.master_organization_id IS NULL
AND master_organization_name IS NOT NULL;
UPDATE csi_iea_value_interface a
SET a.attribute_id=(SELECT attribute_id
FROM csi_i_extended_attribs
WHERE attribute_level = a.attribute_level
AND attribute_code = a.attribute_code)
WHERE a.attribute_level = 'GLOBAL'
AND a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier IS NOT NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status IN ('X','R')
AND parallel_worker_id IS NULL
AND source_system_name = nvl(p_source_system_name,source_system_name))
AND a.attribute_id IS NULL
AND a.attribute_level IS NOT NULL
AND a.attribute_code IS NOT NULL;
UPDATE csi_iea_value_interface a
SET a.attribute_id = (SELECT attribute_id
FROM csi_i_extended_attribs
WHERE attribute_level = a.attribute_level
AND attribute_code = a.attribute_code
AND inventory_item_id = a.inventory_item_id
AND a.attribute_id IS NULL
AND master_organization_id = a.master_organization_id
AND NVL(attribute_category,'$CSI_NULL_VALUE$')=
NVL(a.attribute_category,'$CSI_NULL_VALUE$'))
WHERE a.attribute_level = 'ITEM'
AND a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier IS NOT NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status IN ('X','R')
AND parallel_worker_id IS NULL
AND source_system_name = nvl(p_source_system_name,source_system_name))
AND a.attribute_id IS NULL
AND a.attribute_level IS NOT NULL
AND a.attribute_code IS NOT NULL
AND a.inventory_item_id IS NOT NULL;
SELECT inst_interface_id
FROM csi_instance_interface
WHERE trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND nvl(transaction_identifier,'-1') = '-1'
AND process_status = 'X'
AND source_system_name = nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = p_worker_id;
SELECT ip_interface_id
FROM csi_i_party_interface cpi
WHERE cpi.party_source_table = 'HZ_PARTIES'
AND cpi.inst_interface_id IN (SELECT inst_interface_Id
FROM csi_instance_interface
WHERE transaction_identifier IS NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status = 'X'
AND source_system_name =
nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = p_worker_id);
SELECT ip_interface_id
FROM csi_i_party_interface cpi
WHERE cpi.party_source_table = 'EMPLOYEE'
AND cpi.inst_interface_id IN (SELECT inst_interface_Id
FROM csi_instance_interface
WHERE transaction_identifier IS NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status = 'X'
AND source_system_name = nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = p_worker_id);
SELECT ip_interface_id
FROM csi_i_party_interface cpi
WHERE cpi.inst_interface_id IN (SELECT inst_interface_Id
FROM csi_instance_interface
WHERE transaction_identifier IS NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status = 'X'
AND source_system_name = nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = p_worker_id);
SELECT ieav_interface_id
FROM csi_iea_value_interface a
WHERE a.attribute_level = 'ITEM'
AND a.inst_interface_id IN (SELECT inst_interface_Id
FROM csi_instance_interface
WHERE transaction_identifier IS NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status = 'X'
AND source_system_name = nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = p_worker_id);
SELECT ia_interface_id
FROM csi_i_asset_interface a
WHERE a.inst_interface_id IN (SELECT inst_interface_Id
FROM csi_instance_interface
WHERE transaction_identifier IS NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status = 'X'
AND source_system_name = nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = p_worker_id);
UPDATE csi_instance_interface a
SET a.inventory_item_id =
(SELECT inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments =
a.inv_concatenated_segments
AND ROWNUM=1)
WHERE inst_interface_id=inst_intf_id_upd(i1)
AND a.inventory_item_id IS NULL
AND a.inv_concatenated_segments IS NOT NULL;
UPDATE csi_instance_interface a
SET a.inv_vld_organization_id =
(SELECT organization_id
FROM hr_all_organization_units
WHERE name = a.inv_vld_organization_name)
WHERE inst_interface_id=inst_intf_id_upd(i2)
AND a.inv_vld_organization_id IS NULL
AND a.inv_vld_organization_name IS NOT NULL;
UPDATE csi_instance_interface a
SET a.instance_condition_id =
(SELECT status_id
FROM mtl_material_statuses
WHERE status_code = a.instance_condition)
WHERE inst_interface_id=inst_intf_id_upd(i3)
AND a.instance_condition_id IS NULL
AND a.instance_condition IS NOT NULL;
UPDATE csi_instance_interface a
SET a.instance_status_id =
(SELECT instance_status_id
FROM csi_instance_statuses
WHERE name = a.instance_status)
WHERE inst_interface_id=inst_intf_id_upd(i4)
AND a.instance_status_id IS NULL
AND a.instance_status IS NOT NULL;
UPDATE csi_instance_interface a
SET a.system_id = (SELECT system_id
FROM csi_systems_b
WHERE system_number = a.system_number)
WHERE inst_interface_id=inst_intf_id_upd(i6)
AND a.system_id IS NULL
AND a.system_number IS NOT NULL;
UPDATE csi_instance_interface a
SET a.unit_of_measure_code =
(SELECT uom_code
FROM mtl_units_of_measure_vl
WHERE unit_of_measure_tl = a.unit_of_measure)
WHERE inst_interface_id=inst_intf_id_upd(i7)
AND a.unit_of_measure_code IS NULL
AND a.unit_of_measure IS NOT NULL;
UPDATE csi_instance_interface a
SET a.inv_organization_id =
(SELECT organization_id
FROM hr_all_organization_units
WHERE NAME = a.inv_organization_name)
WHERE inst_interface_id=inst_intf_id_upd(i8)
AND a.inv_organization_id IS NULL
AND a.inv_organization_name IS NOT NULL;
UPDATE csi_instance_interface a
SET a.project_id = (SELECT project_id
FROM pa_projects_all
WHERE segment1 = a.project_number)
WHERE inst_interface_id=inst_intf_id_upd(i9)
AND a.project_id IS NULL
AND a.project_number IS NOT NULL;
UPDATE csi_instance_interface a
SET a.task_id = (SELECT task_id
FROM pa_tasks pt,
pa_projects_all pp
WHERE pt.task_number = a.task_number
AND pp.segment1 = a.project_number
AND pt.project_id = pp.project_id)
WHERE inst_interface_id=inst_intf_id_upd(i10)
AND a.task_id IS NULL
AND a.task_number IS NOT NULL
AND a.project_number IS NOT NULL;
UPDATE csi_instance_interface a
SET a.wip_job_id = (SELECT wip_entity_id
FROM wip_entities
WHERE wip_entity_name = a.wip_job_name)
WHERE inst_interface_id=inst_intf_id_upd(i11)
AND a.wip_job_id IS NULL
AND a.wip_job_name IS NOT NULL;
UPDATE csi_instance_interface a
SET a.operating_unit=(SELECT organization_id
FROM hr_operating_units
WHERE name = a.operating_unit_name)
WHERE inst_interface_id=inst_intf_id_upd(i16)
AND a.operating_unit IS NULL
AND a.operating_unit_name IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.party_id = (SELECT party_id
FROM hz_parties
WHERE party_name = cpi.party_name
AND party_number = NVL(cpi.party_number,party_number))
WHERE ip_interface_id=ip_intf_id_upd1(i1)
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.contact_party_id =
(SELECT party_id
FROM hz_parties
WHERE party_name = cpi.contact_party_name
AND party_number = NVL(cpi.contact_party_number,party_number))
WHERE ip_interface_id=ip_intf_id_upd1(i2)
AND cpi.contact_party_id IS NULL
AND cpi.contact_party_name IS NOT NULL;
UPDATE CSI_I_PARTY_INTERFACE cpi
SET party_id=(SELECT vendor_id
FROM po_vendors
WHERE vendor_name = cpi.party_name)
WHERE cpi.party_source_table = 'PO_VENDORS'
AND cpi.inst_interface_id IN (SELECT inst_interface_Id
FROM csi_instance_interface
WHERE transaction_identifier IS NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status = 'X'
AND source_system_name =
nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = p_worker_id)
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.party_id = (SELECT person_id
FROM per_all_people_f
WHERE full_name = cpi.party_name)
WHERE ip_interface_id=ip_intf_id_upd2(i1)
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.contact_party_id=
(SELECT party_id
FROM hz_parties
WHERE party_name = cpi.contact_party_name
AND party_number = NVL(cpi.contact_party_number,party_number))
WHERE ip_interface_id=ip_intf_id_upd2(i2)
AND cpi.contact_party_id IS NULL
AND cpi.contact_party_name IS NOT NULL;
UPDATE CSI_I_PARTY_INTERFACE cpi
SET party_id = (SELECT team_id
FROM jtf_rs_teams_vl
WHERE team_name = cpi.party_name)
WHERE cpi.party_source_table = 'TEAM'
AND cpi.inst_interface_id IN (SELECT inst_interface_Id
FROM csi_instance_interface
WHERE transaction_identifier IS NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status = 'X'
AND source_system_name = nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = p_worker_id)
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE CSI_I_PARTY_INTERFACE cpi
SET party_id = (SELECT group_id
FROM jtf_rs_groups_vl
WHERE group_name = cpi.party_name)
WHERE cpi.party_source_table = 'GROUP'
AND cpi.inst_interface_id IN (SELECT inst_interface_Id
FROM csi_instance_interface
WHERE transaction_identifier IS NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status = 'X'
AND source_system_name = nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = p_worker_id)
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.party_account1_id=
(SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number = cpi.party_account1_number
AND party_id = cpi.party_id)
WHERE ip_interface_id=ip_intf_id_upd3(i1)
AND cpi.party_account1_id IS NULL
AND cpi.party_account1_number IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.party_account2_id=
(SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number = cpi.party_account2_number
AND party_id = cpi.party_id)
WHERE ip_interface_id=ip_intf_id_upd3(i2)
AND cpi.party_account2_id IS NULL
AND cpi.party_account2_number IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.party_account3_id=
(SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number = cpi.party_account3_number
AND party_id = cpi.party_id)
WHERE ip_interface_id=ip_intf_id_upd3(i3)
AND cpi.party_account3_id IS NULL
AND cpi.party_account3_number IS NOT NULL
AND cpi.party_id IS NOT NULL;
UPDATE csi_i_asset_interface a
SET a.fa_asset_id = (SELECT asset_id
FROM fa_additions_b
WHERE asset_number =
a.fa_asset_number
)
WHERE a.ia_interface_id=asst_intf_id_upd(asst1)
AND a.fa_asset_id IS NULL
AND a.fa_asset_number IS NOT NULL;
UPDATE csi_iea_value_interface a
SET a.inventory_item_id =
(SELECT inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments =
a.inv_concatenated_segments
AND ROWNUM=1)
WHERE ieav_interface_id=iea_intf_id_upd(i1)
AND a.inventory_item_id IS NULL
AND a.inv_concatenated_segments IS NOT NULL;
UPDATE csi_iea_value_interface a
SET a.master_organization_id =
(SELECT organization_id
FROM hr_all_organization_units
WHERE name = master_organization_name)
WHERE ieav_interface_id=iea_intf_id_upd(i2)
AND a.master_organization_id IS NULL
AND a.master_organization_name IS NOT NULL;
UPDATE csi_iea_value_interface a
SET a.attribute_id=(SELECT attribute_id
FROM csi_i_extended_attribs
WHERE attribute_level = a.attribute_level
AND attribute_code = a.attribute_code)
WHERE a.attribute_level = 'GLOBAL'
AND a.inst_interface_id IN (SELECT inst_interface_Id
FROM csi_instance_interface
WHERE transaction_identifier IS NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status = 'X'
AND source_system_name = nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = p_worker_id)
AND a.attribute_id IS NULL
AND a.attribute_level IS NOT NULL
AND a.attribute_code IS NOT NULL;
UPDATE csi_iea_value_interface a
SET a.attribute_id=(SELECT attribute_id
FROM csi_i_extended_attribs
WHERE attribute_level = a.attribute_level
AND attribute_code = a.attribute_code
AND inventory_item_id = a.inventory_item_id
AND master_organization_id = a.master_organization_id
AND NVL(attribute_category,l_fnd_g_char)=
NVL(a.attribute_category,l_fnd_g_char) )
WHERE a.attribute_level = 'ITEM'
AND a.inst_interface_id IN (SELECT inst_interface_Id
FROM csi_instance_interface
WHERE transaction_identifier IS NULL
AND trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND process_status = 'X'
AND source_system_name = nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = p_worker_id)
AND a.attribute_id IS NULL
AND a.attribute_level IS NOT NULL
AND a.attribute_code IS NOT NULL
AND a.inventory_item_id IS NOT NULL
AND a.master_organization_id IS NOT NULL;
PROCEDURE resolve_update_ids
( p_source_system_name IN VARCHAR2,
p_txn_identifier IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2) IS
CURSOR pty1_intf_cur IS
SELECT ip_interface_id
FROM csi_i_party_interface cpi
WHERE cpi.party_source_table = 'HZ_PARTIES'
AND cpi.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier)
AND cpi.party_source_table = 'HZ_PARTIES';
SELECT ip_interface_id
FROM csi_i_party_interface cpi
WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier
AND cii.source_system_name = p_source_system_name);
l_api_name VARCHAR2(255) := 'CSI_ML_UTIL_PVT.RESOLVE_UPDATE_IDS';
UPDATE csi_instance_interface a
SET pricing_attribute_id =
(SELECT pricing_attribute_id
FROM csi_i_pricing_attribs
WHERE instance_id = a.instance_id
AND pricing_context = a.pricing_context)
WHERE transaction_identifier = p_txn_identifier
AND source_system_name = p_source_system_name
AND a.pricing_attribute_id IS NULL
AND a.instance_id IS NOT NULL
AND a.pricing_context IS NOT NULL;
UPDATE csi_instance_interface a
SET instance_ou_id = (SELECT instance_ou_id
FROM csi_i_org_assignments
WHERE instance_id = a.instance_id
AND operating_unit_id = a.operating_unit
AND relationship_type_code = a.ou_relation_type)
WHERE transaction_identifier = p_txn_identifier
AND source_system_name = p_source_system_name
AND a.instance_ou_id IS NULL
AND a.instance_id IS NOT NULL
AND a.operating_unit IS NOT NULL
AND a.ou_relation_type IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.party_id = (SELECT party_id
FROM hz_parties
WHERE party_name = cpi.party_name
AND party_number = NVL(cpi.party_number,party_number))
WHERE ip_interface_id=ip_intf_id_upd1(i1)
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.contact_party_id =
(SELECT party_id
FROM hz_parties
WHERE party_name = cpi.contact_party_name
AND party_number = NVL(cpi.contact_party_number,party_number))
WHERE ip_interface_id=ip_intf_id_upd1(i2)
AND cpi.contact_party_id IS NULL
AND cpi.contact_party_name IS NOT NULL;
UPDATE CSI_I_PARTY_INTERFACE cpi
SET party_id = (SELECT vendor_id
FROM po_vendors
WHERE vendor_name = cpi.party_name)
WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier)
AND cpi.party_source_table = 'PO_VENDORS'
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE CSI_I_PARTY_INTERFACE cpi
SET party_id = (SELECT person_id
FROM per_all_people_f
WHERE full_name = cpi.party_name)
WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier)
AND cpi.party_source_table = 'EMPLOYEE'
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE CSI_I_PARTY_INTERFACE cpi
SET party_id = (SELECT team_id
FROM jtf_rs_teams_vl
WHERE team_name = cpi.party_name)
WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier)
AND cpi.party_source_table = 'TEAM'
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE CSI_I_PARTY_INTERFACE cpi
SET party_id = (SELECT group_id
FROM jtf_rs_groups_vl
WHERE group_name = cpi.party_name)
WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier)
AND cpi.party_source_table = 'GROUP'
AND cpi.party_id IS NULL
AND cpi.party_name IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.party_account1_id=
(SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number = cpi.party_account1_number
AND party_id = cpi.party_id)
WHERE ip_interface_id=ip_intf_id_upd3(i1)
AND cpi.party_account1_id IS NULL
AND cpi.party_account1_number IS NOT NULL
AND cpi.party_id IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.party_account2_id=
(SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number = cpi.party_account2_number
AND party_id = cpi.party_id)
WHERE ip_interface_id=ip_intf_id_upd3(i2)
AND cpi.party_account2_id IS NULL
AND cpi.party_account2_number IS NOT NULL
AND cpi.party_id IS NOT NULL;
UPDATE csi_i_party_interface cpi
SET cpi.party_account3_id=
(SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number = cpi.party_account3_number
AND party_id = cpi.party_id)
WHERE ip_interface_id=ip_intf_id_upd3(i3)
AND cpi.party_account3_id IS NULL
AND cpi.party_account3_number IS NOT NULL
AND cpi.party_id IS NOT NULL;
UPDATE csi_i_party_interface a
SET instance_party_id = (SELECT instance_party_id
FROM csi_i_parties
WHERE party_id = a.party_id
AND instance_id =
(SELECT instance_id
FROM csi_instance_interface cii
WHERE cii.inst_interface_id=a.inst_interface_id)
AND relationship_type_code =
a.party_relationship_type_code
AND contact_flag <>'Y')
WHERE a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier
AND cii.source_system_name = p_source_system_name)
AND instance_party_id IS NULL
AND a.party_id IS NOT NULL;
UPDATE csi_i_party_interface a
SET contact_ip_id = (SELECT instance_party_id
FROM csi_i_parties
WHERE party_id = a.contact_party_id
AND instance_id =
(SELECT instance_id
FROM csi_instance_interface cii
WHERE cii.inst_interface_id=a.inst_interface_id)
AND relationship_type_code =
a.contact_party_rel_type
AND contact_flag <>'Y')
WHERE a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier
AND cii.source_system_name = p_source_system_name)
AND contact_ip_id IS NULL
AND a.contact_party_id IS NOT NULL;
UPDATE csi_i_party_interface a
SET instance_party_id = (SELECT instance_party_id
FROM csi_i_parties
WHERE instance_id =
(SELECT instance_id
FROM csi_instance_interface cii
WHERE cii.inst_interface_id=a.inst_interface_id)
AND relationship_type_code =
a.party_relationship_type_code)
WHERE a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier
AND cii.source_system_name = p_source_system_name)
AND party_relationship_type_code = 'OWNER'
AND instance_party_id IS NULL;
UPDATE csi_i_party_interface a
SET ip_account1_id=(SELECT ip_account_id
FROM csi_ip_accounts
WHERE instance_party_id = a.instance_party_id
AND party_account_id = a.party_account1_id
AND relationship_type_code =
a.acct1_relationship_type_code)
WHERE a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier
AND cii.source_system_name = p_source_system_name)
AND ip_account1_id IS NULL ;
UPDATE csi_i_party_interface a
SET ip_account2_id = (SELECT ip_account_id
FROM csi_ip_accounts
WHERE instance_party_id = a.instance_party_id
AND party_account_id = a.party_account2_id
AND relationship_type_code =
a.acct2_relationship_type_code)
WHERE a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier
AND cii.source_system_name = p_source_system_name)
AND ip_account2_id IS NULL;
UPDATE csi_i_party_interface a
SET ip_account3_id = (SELECT ip_account_id
FROM csi_ip_accounts
WHERE instance_party_id = a.instance_party_id
AND party_account_id = a.party_account3_id
AND relationship_type_code =
a.acct3_relationship_type_code)
WHERE a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier
AND cii.source_system_name = p_source_system_name)
AND ip_account3_id IS NULL;
UPDATE csi_i_party_interface a
SET ip_account1_id = (SELECT ip_account_id
FROM csi_ip_accounts
WHERE instance_party_id = a.instance_party_id
AND relationship_type_code =
a.acct1_relationship_type_code)
WHERE a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier
AND cii.source_system_name = p_source_system_name)
AND acct1_relationship_type_code = 'OWNER'
AND ip_account1_id IS NULL;
UPDATE csi_i_party_interface a
SET ip_account2_id = (SELECT ip_account_id
FROM csi_ip_accounts
WHERE instance_party_id = a.instance_party_id
AND relationship_type_code =
a.acct2_relationship_type_code)
WHERE a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier
AND cii.source_system_name = p_source_system_name)
AND acct2_relationship_type_code = 'OWNER'
AND ip_account2_id IS NULL;
UPDATE csi_i_party_interface a
SET ip_account3_id = (SELECT ip_account_id
FROM csi_ip_accounts
WHERE instance_party_id = a.instance_party_id
AND relationship_type_code =
a.acct3_relationship_type_code)
WHERE a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier
AND cii.source_system_name = p_source_system_name)
AND acct3_relationship_type_code = 'OWNER'
AND ip_account3_id IS NULL;
UPDATE csi_iea_value_interface a
SET attribute_id =(SELECT attribute_id
FROM csi_i_extended_attribs
WHERE attribute_level = a.attribute_level
AND attribute_code = a.attribute_code)
WHERE a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier
AND cii.source_system_name = p_source_system_name)
AND attribute_level = 'GLOBAL'
AND attribute_id IS NULL;
UPDATE csi_iea_value_interface a
SET attribute_id=(SELECT attribute_id
FROM csi_i_extended_attribs
WHERE attribute_level = a.attribute_level
AND attribute_code = a.attribute_code
AND inventory_item_id = a.inventory_item_id
AND master_organization_id = a.master_organization_id
AND NVL(attribute_category,l_fnd_g_char)=
NVL(a.attribute_category,l_fnd_g_char))
WHERE a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier
AND cii.source_system_name = p_source_system_name)
AND attribute_level = 'ITEM'
AND attribute_id IS NULL;
UPDATE csi_iea_value_interface a
SET attribute_value_id = (SELECT attribute_value_id
FROM csi_iea_values
WHERE attribute_id = a.attribute_id
AND attribute_value = a.attribute_value
AND instance_id =
(SELECT cii1.instance_id
FROM csi_instance_interface cii1
WHERE cii1.inst_interface_id =a.inst_interface_id))
WHERE a.inst_interface_id IN (SELECT inst_interface_id
FROM csi_instance_interface cii
WHERE cii.transaction_identifier = p_txn_identifier
AND cii.source_system_name = p_source_system_name)
AND attribute_value_id IS NULL;
END resolve_update_ids;
SELECT ctt.Transaction_Type_Id Transaction_Type_Id
FROM CSI_Txn_Types ctt,
FND_Application fa
WHERE ctt.Source_Transaction_Type = P_Txn_Type
AND fa.application_id = ctt.Source_Application_ID
AND fa.Application_Short_Name = P_App_Short_Name;
SELECT * from csi_instance_interface
WHERE process_status = 'E'
AND trunc(source_transaction_date) BETWEEN
nvl(l_txn_from_date,trunc(source_transaction_date)) AND
nvl(l_txn_to_date,trunc(source_transaction_date))
AND parallel_worker_id IS NULL
ORDER BY inst_interface_id;
SELECT * from csi_instance_interface
WHERE process_status = 'E'
AND trunc(source_transaction_date) BETWEEN
nvl(pc_txn_from_date,trunc(source_transaction_date)) AND
nvl(pc_txn_to_date,trunc(source_transaction_date))
AND parallel_worker_id = pc_worker_id
AND source_system_name = nvl(pc_source_system_name,source_system_name)
ORDER BY inst_interface_id;
UPDATE csi_ii_relation_interface cir
SET cir.object_id = decode(cir.object_id,NULL
,(SELECT cii.instance_id
FROM csi_instance_interface cii
WHERE cii.inst_interface_id = cir.object_interface_id)
,cir.object_id ),
cir.subject_id = decode(cir.subject_id,NULL
,(SELECT cii.instance_id
FROM csi_instance_interface cii
WHERE cii.inst_interface_id = cir.subject_interface_id)
,cir.subject_id )
WHERE ((cir.object_id IS NULL AND EXISTS (SELECT 'x' FROM csi_instance_interface WHERE
inst_interface_id = cir.object_interface_id AND instance_id IS NOT NULL))
OR (cir.subject_id IS NULL AND EXISTS (SELECT 'x' FROM csi_instance_interface WHERE
inst_interface_id = cir.subject_interface_id AND instance_id IS NOT NULL)))
AND cir.process_status='R'
AND ROWNUM<10001;
SELECT rel_interface_id
,relationship_type_code
,object_id
,subject_id
FROM csi_ii_relation_interface cir
WHERE cir.object_id = p_object_id
AND cir.process_status = 'R'
AND cir.relationship_type_code<>'CONNECTED-TO';
l_rel_tbl_next_lvl.delete;
l_rel_tbl.DELETE;
SELECT cir.object_id
INTO l_object_id
FROM csi_ii_relation_interface cir
WHERE cir.subject_id = l_subject_id
AND cir.relationship_type_code = p_rel_type_code
AND nvl(cir.relationship_end_date,(sysdate+1)) > sysdate
AND EXISTS (SELECT 'x'
FROM csi_item_instances cii
WHERE cii.instance_id = cir.object_id
AND cii.location_type_code NOT IN ('INVENTORY','PO','IN_TRANSIT','WIP','PROJECT')
);
SELECT rel_interface_id
,process_status
,object_id
,subject_id
,relationship_type_code
,relationship_end_date
FROM csi_ii_relation_interface
WHERE trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND nvl(transaction_identifier,'-1') = '-1'
AND process_status = 'R'
AND source_system_name = nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = pc_parallel_worker_id;
SELECT rel_interface_id
,subject_id
,relationship_type_code
FROM csi_ii_relation_interface
WHERE trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND nvl(transaction_identifier,'-1') = '-1'
AND process_status = 'V'
AND source_system_name = nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = pc_parallel_worker_id;
SELECT rel_interface_id
,subject_id
,relationship_type_code
,config_root_node
FROM csi_ii_relation_interface
WHERE trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND nvl(transaction_identifier,'-1') = '-1'
AND process_status = 'U'
AND source_system_name = nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = pc_parallel_worker_id;
SELECT rel_interface_id
,subject_id
,relationship_type_code
,object_id
,position_reference
,relationship_start_date
,relationship_end_date
,display_order
,mandatory_flag
,context
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
FROM csi_ii_relation_interface
WHERE trunc(source_transaction_date)
BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
AND nvl(l_txn_to_date,trunc(source_transaction_date))
AND nvl(transaction_identifier,'-1') = '-1'
AND process_status = 'I'
AND source_system_name = nvl(p_source_system_name,source_system_name)
AND parallel_worker_id = pc_parallel_worker_id;
SELECT 'x'
INTO l_exists
FROM csi_ii_relation_types
WHERE relationship_type_code=r_id.relationship_type_code;
SELECT quantity
INTO l_quantity
FROM csi_item_instances
WHERE instance_id=r_id.object_id
AND (SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE));
SELECT 'x'
INTO l_exists
FROM csi_item_instances
WHERE instance_id=r_id.subject_id
AND (SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE));
SELECT 'x'
INTO l_exists
FROM csi_ii_relationships
WHERE subject_id=r_id.subject_id
AND relationship_type_code = r_id.relationship_type_code
AND (active_end_date IS NULL OR active_end_date > SYSDATE)
AND ROWNUM=1;
SELECT 'x'
INTO l_exists
FROM csi_ii_relationships
WHERE (( subject_id=r_id.object_id AND
object_id=r_id.subject_id)
OR ( subject_id=r_id.subject_id AND
object_id=r_id.object_id))
AND relationship_type_code = r_id.relationship_type_code
AND (active_end_date IS NULL OR active_end_date > SYSDATE)
AND ROWNUM = 1;
FND_File.Put_Line(Fnd_File.LOG,'Trying to update count');
l_upd_stmt := 'UPDATE csi_ii_relation_interface
SET error_text = :error_text
,process_status = :status
WHERE rel_interface_id = :intf_id';
ELSIF p_mode='UPDATE'
THEN
FND_File.Put_Line(Fnd_File.LOG,'Inside for Update mode');
SELECT active_end_date
,location_type_code
,location_id
,inv_organization_id
,inv_subinventory_name
,inv_locator_id
,pa_project_id
,pa_project_task_id
,in_transit_order_line_id
,wip_job_id
,po_order_line_id
INTO l_instance_rec.active_end_date
,l_instance_rec.location_type_code
,l_instance_rec.location_id
,l_instance_rec.inv_organization_id
,l_instance_rec.inv_subinventory_name
,l_instance_rec.inv_locator_id
,l_instance_rec.pa_project_id
,l_instance_rec.pa_project_task_id
,l_instance_rec.in_transit_order_line_id
,l_instance_rec.wip_job_id
,l_instance_rec.po_order_line_id
FROM csi_item_instances
WHERE instance_id=l_upd_obj_id;
SELECT instance_id
,object_version_number
INTO l_instance_rec.instance_id
,l_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_upd_ins_csr.subject_id;
FND_File.Put_Line(Fnd_File.LOG,'Before update l_return_status := '||l_return_status);
csi_item_instance_pvt.update_item_instance
(p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,p_instance_rec => l_instance_rec
,p_txn_rec => l_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_called_from_rel => fnd_api.g_false
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
,p_validation_mode => 'V'
);
FND_File.Put_Line(Fnd_File.LOG,'After update l_return_status := '||l_return_status);
FND_File.Put_Line(Fnd_File.LOG,'After update x_msg_data := '||x_msg_data);
FND_File.Put_Line(Fnd_File.LOG,'Updating status in update mode');
l_upd_stmt := 'UPDATE csi_ii_relation_interface
SET error_text = :error_text
,process_status = :status
WHERE rel_interface_id = :intf_id';
ELSIF p_mode='RE-UPDATE'
THEN
FND_File.Put_Line(Fnd_File.LOG,'Inside for re-update mode');
SELECT active_end_date
,location_type_code
,location_id
,inv_organization_id
,inv_subinventory_name
,inv_locator_id
,pa_project_id
,pa_project_task_id
,in_transit_order_line_id
,wip_job_id
,po_order_line_id
INTO l_instance_rec.active_end_date
,l_instance_rec.location_type_code
,l_instance_rec.location_id
,l_instance_rec.inv_organization_id
,l_instance_rec.inv_subinventory_name
,l_instance_rec.inv_locator_id
,l_instance_rec.pa_project_id
,l_instance_rec.pa_project_task_id
,l_instance_rec.in_transit_order_line_id
,l_instance_rec.wip_job_id
,l_instance_rec.po_order_line_id
FROM csi_item_instances
WHERE instance_id=l_upd_obj_id;
SELECT instance_id
,object_version_number
INTO l_instance_rec.instance_id
,l_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_re_upd_csr.subject_id;
csi_item_instance_pvt.update_item_instance
(p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,p_instance_rec => l_instance_rec
,p_txn_rec => l_txn_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_item_attribute_tbl => l_item_attribute_tbl
,p_location_tbl => l_location_tbl
,p_generic_id_tbl => l_generic_id_tbl
,p_lookup_tbl => l_lookup_tbl
,p_ins_count_rec => l_ins_count_rec
,p_called_from_rel => fnd_api.g_false
,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
,p_child_inst_tbl => px_child_inst_tbl
,p_validation_mode => 'U'
);
FND_File.Put_Line(Fnd_File.LOG,'After update x_msg_data := '||x_msg_data);
FND_File.Put_Line(Fnd_File.LOG,'After update l_return_status := '||l_return_status);
FND_File.Put_Line(Fnd_File.LOG,'Updating status in re-update mode');
l_upd_stmt := 'UPDATE csi_ii_relation_interface
SET error_text = :error_text
,process_status = :status
WHERE rel_interface_id = :intf_id';
ELSIF p_mode='INSERT'
THEN
FND_File.Put_Line(Fnd_File.LOG,'Inside for Insert mode');
SELECT csi_ii_relationships_h_s.NEXTVAL
INTO l_rel_hist_tbl(l_ins)
FROM dual;
SELECT csi_ii_relationships_s.NEXTVAL
INTO l_ii_relationship_rec_tab.rel_interface_id(l_ins)
FROM dual;
SELECT csi_transactions_s.NEXTVAL
INTO l_txn_id_tbl(l_ins)
FROM dual;
INSERT INTO CSI_II_RELATIONSHIPS(
RELATIONSHIP_ID
,RELATIONSHIP_TYPE_CODE
,OBJECT_ID
,SUBJECT_ID
,POSITION_REFERENCE
,ACTIVE_START_DATE
,ACTIVE_END_DATE
,DISPLAY_ORDER
,MANDATORY_FLAG
,CONTEXT
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER)
VALUES(
l_ii_relationship_rec_tab.REL_INTERFACE_ID(i)
,l_ii_relationship_rec_tab.RELATIONSHIP_TYPE_CODE(i)
,l_ii_relationship_rec_tab.OBJECT_ID(i)
,l_ii_relationship_rec_tab.SUBJECT_ID(i)
,l_ii_relationship_rec_tab.POSITION_REFERENCE(i)
,l_ii_relationship_rec_tab.ACTIVE_START_DATE(i)
,l_ii_relationship_rec_tab.ACTIVE_END_DATE(i)
,l_ii_relationship_rec_tab.DISPLAY_ORDER(i)
,l_ii_relationship_rec_tab.MANDATORY_FLAG(i)
,l_ii_relationship_rec_tab.CONTEXT(i)
,l_ii_relationship_rec_tab.ATTRIBUTE1(i)
,l_ii_relationship_rec_tab.ATTRIBUTE2(i)
,l_ii_relationship_rec_tab.ATTRIBUTE3(i)
,l_ii_relationship_rec_tab.ATTRIBUTE4(i)
,l_ii_relationship_rec_tab.ATTRIBUTE5(i)
,l_ii_relationship_rec_tab.ATTRIBUTE6(i)
,l_ii_relationship_rec_tab.ATTRIBUTE7(i)
,l_ii_relationship_rec_tab.ATTRIBUTE8(i)
,l_ii_relationship_rec_tab.ATTRIBUTE9(i)
,l_ii_relationship_rec_tab.ATTRIBUTE10(i)
,l_ii_relationship_rec_tab.ATTRIBUTE11(i)
,l_ii_relationship_rec_tab.ATTRIBUTE12(i)
,l_ii_relationship_rec_tab.ATTRIBUTE13(i)
,l_ii_relationship_rec_tab.ATTRIBUTE14(i)
,l_ii_relationship_rec_tab.ATTRIBUTE15(i)
,l_user_id
,sysdate
,l_user_id
,sysdate
,-1
,1);
INSERT INTO CSI_II_RELATIONSHIPS_H(
RELATIONSHIP_HISTORY_ID
,RELATIONSHIP_ID
,TRANSACTION_ID
,NEW_SUBJECT_ID
,NEW_POSITION_REFERENCE
,NEW_ACTIVE_START_DATE
,NEW_ACTIVE_END_DATE
,NEW_MANDATORY_FLAG
,NEW_CONTEXT
,NEW_ATTRIBUTE1
,NEW_ATTRIBUTE2
,NEW_ATTRIBUTE3
,NEW_ATTRIBUTE4
,NEW_ATTRIBUTE5
,NEW_ATTRIBUTE6
,NEW_ATTRIBUTE7
,NEW_ATTRIBUTE8
,NEW_ATTRIBUTE9
,NEW_ATTRIBUTE10
,NEW_ATTRIBUTE11
,NEW_ATTRIBUTE12
,NEW_ATTRIBUTE13
,NEW_ATTRIBUTE14
,NEW_ATTRIBUTE15
,FULL_DUMP_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER)
VALUES(
l_rel_hist_tbl(i)
,l_ii_relationship_rec_tab.REL_INTERFACE_ID(i)
,l_txn_id_tbl(i)
,l_ii_relationship_rec_tab.SUBJECT_ID(i)
,l_ii_relationship_rec_tab.POSITION_REFERENCE(i)
,l_ii_relationship_rec_tab.ACTIVE_START_DATE(i)
,l_ii_relationship_rec_tab.ACTIVE_END_DATE(i)
,l_ii_relationship_rec_tab.MANDATORY_FLAG(i)
,l_ii_relationship_rec_tab.CONTEXT(i)
,l_ii_relationship_rec_tab.ATTRIBUTE1(i)
,l_ii_relationship_rec_tab.ATTRIBUTE2(i)
,l_ii_relationship_rec_tab.ATTRIBUTE3(i)
,l_ii_relationship_rec_tab.ATTRIBUTE4(i)
,l_ii_relationship_rec_tab.ATTRIBUTE5(i)
,l_ii_relationship_rec_tab.ATTRIBUTE6(i)
,l_ii_relationship_rec_tab.ATTRIBUTE7(i)
,l_ii_relationship_rec_tab.ATTRIBUTE8(i)
,l_ii_relationship_rec_tab.ATTRIBUTE9(i)
,l_ii_relationship_rec_tab.ATTRIBUTE10(i)
,l_ii_relationship_rec_tab.ATTRIBUTE11(i)
,l_ii_relationship_rec_tab.ATTRIBUTE12(i)
,l_ii_relationship_rec_tab.ATTRIBUTE13(i)
,l_ii_relationship_rec_tab.ATTRIBUTE14(i)
,l_ii_relationship_rec_tab.ATTRIBUTE15(i)
,'Y'
,l_user_id
,sysdate
,l_user_id
,sysdate
,-1
,1);
INSERT INTO CSI_TRANSACTIONS(
TRANSACTION_ID
,TRANSACTION_DATE
,SOURCE_TRANSACTION_DATE
,SOURCE_HEADER_REF
,TRANSACTION_TYPE_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
)
VALUES(
l_txn_id_tbl(i)
,SYSDATE
,SYSDATE
,'Full Dump Insert'
,l_txn_type_id
,l_user_id
,sysdate
,l_user_id
,sysdate
,-1
,1
);
FND_File.Put_Line(Fnd_File.LOG,'Updating status in insert mode');
l_upd_stmt := 'UPDATE csi_ii_relation_interface
SET process_status = :status
WHERE rel_interface_id = :intf_id';
SELECT subject_id
,relationship_type_code
,count(*)
FROM csi_ii_relation_interface
WHERE process_status='R'
AND relationship_type_code <> 'CONNECTED-TO'
GROUP BY subject_id,relationship_type_code
HAVING count(*) > 1;
SELECT cir.rel_interface_id
FROM csi_ii_relation_interface cir
WHERE cir.subject_id = p_subject_id
AND cir.relationship_type_code = p_rel_type
AND cir.rel_interface_id <> p_rel_id;
SELECT MAX(cir.rel_interface_id)
INTO l_ret_relationship_id
FROM csi_ii_relation_interface cir
WHERE cir.subject_id = l_subject_id_upd(k)
AND cir.relationship_type_code = l_rel_type_code_upd(k);
UPDATE csi_ii_relation_interface
SET process_status='E'
,error_text='Duplicate subject_id record'
,parallel_worker_id=0
WHERE rel_interface_id = l_rel_id_tbl(j);
SELECT object_id
,subject_id
,relationship_type_code
,count(*)
FROM csi_ii_relation_interface
WHERE process_status='R'
GROUP BY object_id,subject_id,relationship_type_code
HAVING count(*) > 1;
SELECT cir.rel_interface_id
FROM csi_ii_relation_interface cir
WHERE cir.object_id = p_object_id
AND cir.subject_id = p_subject_id
AND cir.relationship_type_code = p_rel_type
AND cir.rel_interface_id <> p_rel_id;
SELECT MAX(cir.rel_interface_id)
INTO l_ret_relationship_id
FROM csi_ii_relation_interface cir
WHERE cir.subject_id = l_subject_id_upd(k)
AND cir.object_id = l_object_id_upd(k)
AND cir.relationship_type_code = l_rel_type_code_upd(k);
UPDATE csi_ii_relation_interface
SET process_status='E'
,error_text='Duplicate record'
,parallel_worker_id=0
WHERE rel_interface_id = l_rel_id_tbl(j);
SELECT object_id
,subject_id
,rel_interface_id
,relationship_type_code
FROM csi_ii_relation_interface
WHERE process_status='R';
l_rel_tbl.DELETE;
l_upd_stmt := 'UPDATE csi_ii_relation_interface
SET process_status = :status
,error_text = :error_text
,parallel_worker_id =0
WHERE rel_interface_id = :intf_id';