[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT access_id, user_id
FROM csm_item_instances_acc
WHERE instance_id = p_instance_id;
CSM_ACC_PKG.Update_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_ACCESS_ID => r_instance_users_rec.access_id
,P_USER_ID => r_instance_users_rec.user_id
);
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
,P_ACC_TABLE_NAME => g_acc_table_name2
,P_SEQ_NAME => g_acc_sequence_name2
,P_PK1_NAME => g_pk1_name2
,P_PK1_NUM_VALUE => p_relationship_id
,P_USER_ID => p_user_id
);
SELECT civ.version_label
FROM csi_i_version_labels civ
WHERE (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(civ.active_start_date,SYSDATE))
AND TRUNC(NVL(civ.active_end_date,SYSDATE)))
AND civ.instance_id = c_instance_id;
SELECT CIR.OBJECT_ID
FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND CIR.SUBJECT_ID = c_instance_id;
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_SEQ_NAME => g_acc_sequence_name1
,P_PK1_NAME => g_pk1_name1
,P_PK1_NUM_VALUE => p_instance_id
,P_USER_ID => p_user_id
);
SELECT cii.inventory_item_id,NVL(cii.LAST_VLD_ORGANIZATION_ID,cii.inv_master_organization_id)
INTO l_inv_item_id,l_lst_vld_org_id
FROM csi_item_instances cii
WHERE cii.instance_id = p_instance_id;
UPDATE csm_item_instances_acc
SET PARENT_INSTANCE_ID = l_parent_ins,
VERSION_LABEL = l_label
WHERE USER_ID = p_user_id
AND INSTANCE_ID = p_instance_id;
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
,P_ACC_TABLE_NAME => g_acc_table_name2
,P_PK1_NAME => g_pk1_name2
,P_PK1_NUM_VALUE => p_relationship_id
,P_USER_ID => p_user_id
);
SELECT 1
FROM csm_item_instances_acc a,
cs_incidents_all_b b,
csm_incidents_all_acc c
WHERE a.instance_id = l_instance_id
AND a.user_id = l_user_id
AND a.counter = 1
AND a.instance_id = b.customer_product_id
AND b.incident_id =c.incident_id
AND c.user_id = l_user_id;
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_PK1_NAME => g_pk1_name1
,P_PK1_NUM_VALUE => p_instance_id
,P_USER_ID => p_user_id
);
SELECT cii.inventory_item_id,NVL(cii.LAST_VLD_ORGANIZATION_ID,cii.inv_master_organization_id)
INTO l_inv_item_id,l_lst_vld_org_id
FROM csi_item_instances cii
WHERE cii.instance_id = p_instance_id;
SELECT nvl(last_run_date, (sysdate - 365*50))
FROM jtm_con_request_data
WHERE package_name = 'CSM_ITEM_INSTANCE_EVENT_PKG'
AND procedure_name = 'REFRESH_INSTANCES_ACC';
SELECT acc.access_id,
acc.user_ID
--acc.INSTANCE_ID
FROM csm_item_instances_acc acc
, csi_item_instances cii
, csi_instance_statuses iis
, asg_user asg
WHERE cii.instance_id = acc.instance_id
AND cii.location_type_code = 'INVENTORY'
AND cii.instance_status_id = iis.instance_status_id
AND asg.user_id = asg.owner_id
AND asg.user_id = acc.user_id
AND ( NOT (SYSDATE BETWEEN NVL(cii.active_start_date,SYSDATE)
AND NVL(cii.active_end_date,SYSDATE))
OR (NVL(iis.terminated_flag,'N') = 'Y')
);
SELECT acc.access_id,
acc.user_ID,
acc.INSTANCE_ID
FROM csm_item_instances_acc acc
, csi_item_instances cii
, asg_user asg
WHERE cii.instance_id = acc.instance_id
AND cii.location_type_code = 'INVENTORY'
AND asg.user_id = asg.owner_id
AND asg.user_id = acc.user_id
AND NOT EXISTS
(SELECT 1
FROM csm_mtl_onhand_qty_acc ohqacc
WHERE ohqacc.user_id = acc.user_id
AND ohqacc.inventory_item_id = cii.inventory_item_id
AND ohqacc.organization_id = cii.inv_organization_id
AND ohqacc.subinventory_code = cii.inv_subinventory_name
AND ((ohqacc.LOCATOR_ID IS NULL AND cii.INV_LOCATOR_ID IS NULL)
OR (ohqacc.LOCATOR_ID = cii.INV_LOCATOR_ID))
AND ((ohqacc.LOT_NUMBER IS NULL AND cii.LOT_NUMBER IS NULL)
OR (ohqacc.LOT_NUMBER = cii.LOT_NUMBER))
AND ((ohqacc.REVISION IS NULL AND cii.INVENTORY_REVISION IS NULL)
OR (ohqacc.REVISION = cii.INVENTORY_REVISION))
)
;
SELECT /* index (acc CSM_ITEM_INSTANCES_ACC_U1) */
acc.user_id,
--acc.instance_id,
acc.access_id
FROM csm_item_instances_acc acc,
csi_item_instances cii,
asg_user asg
WHERE cii.instance_id = acc.instance_id
AND asg.user_id = asg.owner_id
AND asg.user_id = acc.user_id
AND asg.ENABLED = 'Y'
AND cii.last_update_date >= p_last_run_date;
SELECT CSM_ITEM_INSTANCES_ACC_S.NEXTVAL as access_id,
cqa.user_id,
cii.instance_id,
cqa.user_id,
cii.instance_id,
civ.version_label,
CIR.OBJECT_ID
FROM csm_mtl_onhand_qty_acc cqa
, csi_item_instances cii
, csi_instance_statuses iis
, asg_user asg
, csi_i_version_labels civ
, CSI_II_RELATIONSHIPS CIR
WHERE cii.inventory_item_id = cqa.inventory_item_id
AND cii.inv_organization_id = cqa.organization_id
AND cii.inv_subinventory_name = cqa.subinventory_code
AND ((cqa.LOCATOR_ID IS NULL AND cii.INV_LOCATOR_ID IS NULL)
OR (cqa.LOCATOR_ID = cii.INV_LOCATOR_ID))
AND ((cqa.LOT_NUMBER IS NULL AND cii.LOT_NUMBER IS NULL)
OR (cqa.LOT_NUMBER = cii.LOT_NUMBER))
AND ((cqa.REVISION IS NULL AND cii.INVENTORY_REVISION IS NULL)
OR (cqa.REVISION = cii.INVENTORY_REVISION))
AND cii.location_type_code = 'INVENTORY'
AND SYSDATE BETWEEN NVL(cii.active_start_date, SYSDATE) AND NVL(cii.active_end_date, SYSDATE)
AND cii.INSTANCE_STATUS_ID = iis.instance_status_id
AND NVL(iis.terminated_flag,'N') = 'N'
AND asg.user_id = asg.owner_id
AND asg.user_id = cqa.user_id
AND asg.ENABLED = 'Y'
AND cii.instance_id = civ.instance_id(+)
AND (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(civ.active_start_date,SYSDATE))
AND TRUNC(NVL(civ.active_end_date,SYSDATE)))
AND CII.INSTANCE_ID = CIR.SUBJECT_ID(+)
AND CIR.RELATIONSHIP_TYPE_CODE(+) = 'COMPONENT-OF'
AND NOT EXISTS
( SELECT 1
FROM csm_item_instances_acc cia
WHERE cia.user_id = cqa.user_id
AND cia.instance_id = cii.instance_id
);
SELECT /*+ index(cii CSI_II_RELATIONSHIPS_U01) */acc.access_id,
acc.user_id
--, acc.relationship_id
FROM csm_ii_relationships_acc acc,
csi_ii_relationships cii
WHERE cii.relationship_id = acc.relationship_id
AND NOT EXISTS
(SELECT 1
FROM csm_item_instances_acc ins_acc
WHERE ins_acc.user_id = acc.user_id
AND (ins_acc.instance_id = cii.object_id OR ins_acc.instance_id = cii.subject_id)
);
SELECT /*+ index(acc csm_ii_relationships_acc_u1) */acc.access_id,
acc.user_id
--,acc.relationship_id
FROM csm_ii_relationships_acc acc
WHERE NOT EXISTS
(SELECT 1
FROM csi_ii_relationships cii
WHERE cii.relationship_id = acc.relationship_id
AND SYSDATE BETWEEN NVL(cii.active_start_date, SYSDATE) AND NVL(cii.active_end_date, SYSDATE)
);
SELECT /* index (acc CSM_II_RELATIONSHIPS_ACC_U1) */
acc.user_id,
acc.access_id,
ii.subject_id,
ii.object_id
FROM csm_ii_relationships_acc acc,
csi_ii_relationships ii
WHERE ii.relationship_id = acc.relationship_id
AND ii.last_update_date >= p_last_run_date;
SELECT DISTINCT cii.relationship_id
FROM csi_ii_relationships cii
WHERE cii.relationship_type_code = 'COMPONENT-OF'
AND (cii.object_id = b_instance_id OR cii.subject_id = b_instance_id)
AND SYSDATE BETWEEN NVL(cii.active_start_date, SYSDATE) AND NVL(cii.active_end_date, SYSDATE)
AND NOT EXISTS (SELECT 1 FROM CSM_II_RELATIONSHIPS_ACC ACC
WHERE ACC.user_id = b_useR_id
AND ACC.relationship_id = cii.relationship_id);
l_acc_id_lst.DELETE;
l_user_id_lst.DELETE;
DELETE FROM CSM_ITEM_INSTANCES_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
COMMIT;--IB Deletes are commited
l_acc_id_lst.DELETE;
l_user_id_lst.DELETE;
l_acc_id_lst.DELETE;
l_user_id_lst.DELETE;
l_instance_id_lst.DELETE;
DELETE CSM_ITEM_INSTANCES_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
COMMIT;--IB Updates are commited
l_acc_id_lst.DELETE;
l_user_id_lst.DELETE;
l_acc_id_lst.DELETE;
l_user_id_lst.DELETE;
l_instance_id_lst.DELETE;
l_inst_id_lst_bkp.DELETE;
l_user_id_lst_bkp.DELETE;
l_ver_label_lst.DELETE;
l_parent_inst_id_lst.DELETE;
INSERT INTO CSM_ITEM_INSTANCES_ACC (ACCESS_ID, USER_ID, INSTANCE_ID,
COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,VERSION_LABEL, PARENT_INSTANCE_ID)
VALUES (l_acc_id_lst(i), l_user_id_lst(i), l_instance_id_lst(i),
1, 1, l_current_run_date,1,l_current_run_date,
1,l_ver_label_lst(i), l_parent_inst_id_lst(i));
COMMIT;--IB Inserts are commited
l_acc_id_lst.DELETE;
l_user_id_lst.DELETE;
l_rel_id_lst.DELETE;
l_ver_label_lst.DELETE;
l_parent_inst_id_lst.DELETE;
DELETE CSM_II_RELATIONSHIPS_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
COMMIT;--IB Relation Deletes are commited
l_acc_id_lst.DELETE;
l_user_id_lst.DELETE;
DELETE CSM_II_RELATIONSHIPS_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
COMMIT;--IB Relation Deletes are commited
l_acc_id_lst.DELETE;
l_user_id_lst.DELETE;
l_instance_id_lst.DELETE;
l_parent_inst_id_lst.DELETE;
l_acc_id_lst.DELETE;
UPDATE csm_item_instances_acc
SET PARENT_INSTANCE_ID = l_parent_inst_id_lst(i)
WHERE USER_ID = l_user_id_lst(i)
AND INSTANCE_ID = l_instance_id_lst(i)
RETURNING access_id BULK COLLECT INTO l_acc_id_lst ;
COMMIT;--IB Relation Updates are commited
l_instance_id_lst.DELETE;
l_parent_inst_id_lst.DELETE;
l_acc_id_lst.delete;
l_rel_id_lst.DELETE;
l_user_id_lst.DELETE;
SELECT CSM_II_RELATIONSHIPS_ACC_S.NEXTVAL
INTO l_acc_id_lst(J)
FROM dual;
INSERT INTO CSM_II_RELATIONSHIPS_ACC (ACCESS_ID, USER_ID, RELATIONSHIP_ID,
COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
VALUES (l_acc_id_lst(j), l_user_id_lst_bkp(I), l_rel_id_lst(j), 1, 1, l_current_run_date,1,l_current_run_date,1);
UPDATE jtm_con_request_data
SET last_run_date = l_current_run_date
WHERE product_code = 'CSM'
AND package_name = 'CSM_ITEM_INSTANCE_EVENT_PKG'
AND procedure_name = 'REFRESH_INSTANCES_ACC';
SELECT object_id AS instance_id
FROM CSI_II_RELATIONSHIPS
WHERE RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND SUBJECT_ID = p_instance_id
AND SYSDATE BETWEEN nvl(active_start_date, SYSDATE)
AND nvl(active_end_date, SYSDATE)
;
SELECT acc.instance_id
FROM CSM_ITEM_INSTANCES_ACC acc, CSI_ITEM_INSTANCES cii
WHERE acc.instance_id = cii.instance_id
AND acc.user_id = p_user_id
AND owner_party_id = p_party_id
AND ( ( cii.location_id = p_party_site_id
AND cii.location_type_code = 'HZ_PARTY_SITES'
) OR
( cii.location_id = p_location_id
AND cii.location_type_code = 'HZ_LOCATIONS'
)
)
AND acc.instance_id NOT IN
(
SELECT acc.instance_id
FROM CSM_ITEM_INSTANCES_ACC acc
WHERE acc.user_id = p_user_id
AND acc.instance_id IN (p_instance_id, p_parent_instance_id)
UNION
SELECT acc.instance_id
FROM CSM_ITEM_INSTANCES_ACC acc
WHERE acc.user_id = p_user_id
AND acc.instance_id IN
(
SELECT subject_id
FROM CSI_II_RELATIONSHIPS
START WITH object_id = p_instance_id
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND SYSDATE BETWEEN NVL(active_start_date, SYSDATE)
AND NVL(active_end_date, SYSDATE)
CONNECT BY object_id = PRIOR subject_id
)
) ;
SELECT cii.instance_id
FROM CSI_ITEM_INSTANCES cii, MTL_SYSTEM_ITEMS_B si
WHERE si.inventory_item_id = cii.inventory_item_id
AND si.organization_id = NVL( cii.inv_organization_id,
cii.inv_master_organization_id )
AND cii.instance_id NOT IN
( SELECT acc.instance_id FROM CSM_ITEM_INSTANCES_ACC acc
WHERE acc.user_id = p_user_id
)
AND owner_party_id = p_party_id
AND ( ( cii.location_id = p_party_site_id
AND cii.location_type_code = 'HZ_PARTY_SITES'
) OR
( cii.location_id = p_location_id
AND cii.location_type_code = 'HZ_LOCATIONS'
)
)
-- AND si.service_item_flag = 'N'
AND nvl(si.enabled_flag,'Y') = 'Y'
AND si.serv_req_enabled_code = 'E'
AND si.contract_item_type_code IS NULL
;
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_SEQ_NAME => g_acc_sequence_name1
,P_PK1_NAME => g_pk1_name1
,P_PK1_NUM_VALUE => c_exist_ib_items.instance_id
,P_USER_ID => p_user_id
);
/** Insert For other IB's at location */
FOR c_ib_items IN c_new_ib_at_location_csr (
p_user_id, p_party_site_id , p_location_id, p_party_id )
LOOP
IF csm_sr_event_pkg.g_ib_count < l_max_ib_at_location
THEN
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_SEQ_NAME => g_acc_sequence_name1
,P_PK1_NAME => g_pk1_name1
,P_PK1_NUM_VALUE => c_ib_items.instance_id
,P_USER_ID => p_user_id
);
SELECT cntrs.counter_id
FROM CS_CSI_COUNTER_GROUPS cntr_grps,
CSI_COUNTERS_B cntrs,
CSI_COUNTER_ASSOCIATIONS cas
WHERE cntrs.counter_id = cas.counter_id
AND cas.source_object_code = 'CP'
AND cntrs.counter_type = 'REGULAR'
AND cntr_grps.counter_group_id(+) = cntrs.group_id
AND cas.source_object_id = p_instance_id
-- get only records for the instance belonging to the user
AND EXISTS (SELECT 1
FROM csm_item_instances_acc acc
WHERE acc.user_id = p_user_id
AND acc.instance_id = cas.source_object_id) ;
PROCEDURE DELETE_IB_AT_LOCATION(p_incident_id IN NUMBER, p_instance_id IN NUMBER, p_party_site_id IN NUMBER, p_party_id IN NUMBER,
p_location_id IN NUMBER, p_user_id IN NUMBER, p_flow_type IN VARCHAR2)
IS
l_sqlerrno VARCHAR2(20);
SELECT acc.instance_id
FROM CSM_ITEM_INSTANCES_ACC acc, CSI_ITEM_INSTANCES cii,
csm_incidents_all_acc iacc, cs_incidents_all_b cia
WHERE acc.instance_id = cii.instance_id
AND acc.user_id = p_user_id
AND acc.counter <> 1 -- do not delete if there is just 1 instance of the IB item
AND acc.instance_id <> p_instance_id
AND owner_party_id = p_party_id
AND ( ( cii.location_id = p_party_site_id
AND cii.location_type_code = 'HZ_PARTY_SITES' )
OR ( cii.location_id = p_location_id
AND cii.location_type_code = 'HZ_LOCATIONS') )
AND iacc.user_id = acc.user_id
AND iacc.incident_id <> p_incident_id
AND iacc.incident_id = cia.incident_id
AND cia.customer_product_id <> acc.instance_id
AND NOT EXISTS
(SELECT 1
FROM (SELECT * FROM CSI_II_RELATIONSHIPS CIRo
START WITH CIRo.OBJECT_ID = p_instance_id
AND CIRo.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND SYSDATE BETWEEN NVL(CIRo.active_start_date, SYSDATE)
AND NVL(CIRo.active_end_date, SYSDATE)
CONNECT BY CIRo.OBJECT_ID = PRIOR CIRo.SUBJECT_ID
) CIR,
CSI_ITEM_INSTANCES CII
WHERE CII.INSTANCE_ID = CIR.SUBJECT_ID
AND cii.instance_id = acc.instance_id
AND SYSDATE BETWEEN NVL ( CII.ACTIVE_START_DATE , SYSDATE )
AND NVL ( CII.ACTIVE_END_DATE , SYSDATE)
);
CSM_UTIL_PKG.LOG('Entering DELETE_IB_AT_LOCATION for instance_id: ' || p_instance_id ||
' and party_site_id: ' || p_party_site_id,'CSM_ITEM_INSTANCE_EVENT_PKG.GET_IB_AT_LOCATION',FND_LOG.LEVEL_PROCEDURE);
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_PK1_NAME => g_pk1_name1
,P_PK1_NUM_VALUE => c_ib_items.instance_id
,P_USER_ID => p_user_id
);
CSM_UTIL_PKG.LOG('Leaving DELETE_IB_AT_LOCATION for instance_id: ' || p_instance_id ||
' and party_site_id: ' || p_party_site_id,'CSM_ITEM_INSTANCE_EVENT_PKG.GET_IB_AT_LOCATION',FND_LOG.LEVEL_PROCEDURE);
l_error_msg := ' Exception in DELETE_IB_AT_LOCATION for instance_id: ' || p_instance_id || ' and party_site_id:'
|| to_char(p_party_site_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_AT_LOCATION',FND_LOG.LEVEL_EXCEPTION);
END DELETE_IB_AT_LOCATION;
SELECT cntrs.counter_id
FROM CS_CSI_COUNTER_GROUPS cntr_grps,
CSI_COUNTERS_B cntrs,
CSI_COUNTER_ASSOCIATIONS cas
WHERE cntrs.counter_id = cas.counter_id
AND cas.source_object_code = 'CP'
AND cntrs.counter_type = 'REGULAR'
AND cntr_grps.counter_group_id(+) = cntrs.group_id
AND cas.source_object_id = p_instance_id
AND EXISTS (SELECT 1
FROM CSM_COUNTERS_ACC acc
WHERE acc.user_id = p_user_id
AND acc.counter_id = cntrs.counter_id);
PROCEDURE DELETE_IB_NOTIN_INV (p_inv_item_id IN NUMBER, p_org_id IN NUMBER, p_user_id IN NUMBER)
IS
l_sqlerrno VARCHAR2(20);
SELECT acc.access_id,
acc.user_ID,
acc.INSTANCE_ID
FROM csm_item_instances_acc acc
, csi_item_instances cii
WHERE cii.instance_id = acc.instance_id
AND cii.location_type_code = 'INVENTORY'
AND cii.inventory_item_id = p_inv_item_id
AND cii.inv_organization_id = p_org_id
AND acc.user_id = p_user_id;
CSM_UTIL_PKG.LOG('Entering CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV ',
'CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV',FND_LOG.LEVEL_PROCEDURE);
l_acc_id_lst.DELETE;
l_user_id_lst.DELETE;
l_instance_id_lst.DELETE;
DELETE CSM_ITEM_INSTANCES_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
CSM_UTIL_PKG.LOG('Leaving CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV ',
'CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV',FND_LOG.LEVEL_PROCEDURE);
l_error_msg := l_error_msg || '- Exception in DELETE_IB_NOTIN_INV ' || ':'
|| l_sqlerrno || ':' || l_sqlerrmsg;
CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV',FND_LOG.LEVEL_EXCEPTION);
END DELETE_IB_NOTIN_INV;