The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR SELECTED_SITES_CUR IS
SELECT SITE_ID
FROM RRS_SITES_INTF
WHERE SESSION_ID = p_session_id;
CURSOR SELECTED_SITE_DET_CUR( c_site_id IN NUMBER ) IS
SELECT PARTY_SITE_ID
FROM RRS_SITES_B
WHERE SITE_ID = c_site_id ;
SELECT cii.SUBJECT_ID
,csi.INVENTORY_ITEM_ID
,csi.LAST_VLD_ORGANIZATION_ID
,LEVEL
FROM CSI_II_RELATIONSHIPS cii,
CSI_ITEM_INSTANCES csi
WHERE cii.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND cii.SUBJECT_ID = csi.INSTANCE_ID
AND TRUNC(NVL(cii.ACTIVE_START_DATE , SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(cii.ACTIVE_END_DATE , SYSDATE )) >= TRUNC(SYSDATE)
START WITH cii.OBJECT_ID = p_source_instance_id
CONNECT BY PRIOR cii.SUBJECT_ID = cii.OBJECT_ID
ORDER BY LEVEL;
SELECT cii.SUBJECT_ID OBJECT_ID
,csi.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
FROM CSI_II_RELATIONSHIPS cii,
CSI_ITEM_INSTANCES csi
WHERE cii.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND cii.OBJECT_ID = csi.INSTANCE_ID
AND TRUNC(NVL(cii.ACTIVE_START_DATE , SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(cii.ACTIVE_END_DATE , SYSDATE )) >= TRUNC(SYSDATE)
AND cii.OBJECT_ID = p_source_instance_id
START WITH cii.SUBJECT_ID = c_subject_id
CONNECT BY PRIOR cii.OBJECT_ID = cii.SUBJECT_ID ;
SELECT CSI.INSTANCE_ID
,CSI.INVENTORY_ITEM_ID
FROM CSI_ITEM_INSTANCES CSI
WHERE LOCATION_ID = c_party_site_id
AND NOT EXISTS (SELECT 1 FROM CSI_II_RELATIONSHIPS CII WHERE CII.SUBJECT_ID = CSI.INSTANCE_ID) ;
FOR siterec IN SELECTED_SITES_CUR LOOP
OPEN SELECTED_SITE_DET_CUR(siterec.SITE_ID);
FETCH SELECTED_SITE_DET_CUR INTO l_party_site_id;
CLOSE SELECTED_SITE_DET_CUR;
SELECT 'Y'
INTO l_exists_flag
FROM DUAL
WHERE EXISTS ( SELECT 'Y'
FROM CSI_ITEM_INSTANCES csi
WHERE csi.location_id = l_party_site_id
--AND csi.instance_id = rec.subject_id Bug#4548344
AND csi.inventory_item_id = rec.inventory_item_id --csi.inventory_item_id
AND csi.last_vld_organization_id = rec.last_vld_organization_id
) ;
END LOOP ; -- end SELECTED_SITES_CUR FOR
DELETE FROM RRS_SITES_INTF WHERE SESSION_ID = p_session_id ;
,p_last_updated_by IN NUMBER
,p_last_update_date IN DATE
,p_last_update_login IN NUMBER
)
IS
BEGIN
FORALL i in 1..p_site_ids.count
INSERT INTO RRS_SITES_INTF
( session_id
,site_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login )
values
( p_session_id
,p_site_ids(i)
,p_created_by
,p_creation_date
,p_last_updated_by
,p_last_update_date
,p_last_update_login
);