[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Delete_Contacts
(
p_api_version_number IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_object_type IN VARCHAR2
, p_processing_set_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE Delete_Audit_Records
(
p_api_version_number IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_object_type IN VARCHAR2
, p_processing_set_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE Delete_Sr_Attributes
(
p_api_version_number IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_object_type IN VARCHAR2
, p_processing_set_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
);
, p_inc_last_update_date_tbl IN t_date_tbl
, p_incident_id_tl_tbl IN t_number_tbl
, p_language_tbl IN t_string_tbl
, p_source_lang_tbl IN t_string_tbl
, p_summary_tbl IN t_long_string_tbl
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
);
* the work to another helper API named cs_sr_delete_util.delete_servicerequest
* @param p_object_type Type of object for which this procedure is being called.
* (Here it will be 'SR')
* @param p_processing_set_id Id that helps the API in identifying the set of
* SRs for which the child objects have to be deleted.
* @param p_purge_source_with_open_task Indicates whether the SRs containing
* OPEN non field service tasks should be purged or not
* @rep:scope internal
* @rep:product CS
* @rep:displayname Service Request Delete Validations
*/
PROCEDURE Delete_Sr_Validations
(
p_api_version_number IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_object_type IN VARCHAR2
, p_processing_set_id IN NUMBER
, p_purge_source_with_open_task IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
--------------------------------------------------------------------------------
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_SR_VALIDATIONS';
END Delete_Sr_Validations;
* performing validations wherever required. This procedure calls the delete
* APIs for deleting child objects and directly deletes the rows in the tables
* cs_incidents_all_b and tl. This procedure also updates the staging table
* with the errors generated while performing validations on SRs with all child
* objects so that a log of these errors can be generated at the end of the
* purge process.
* @param p_object_type Type of object for which this procedure is being
* called. (Here it will be 'SR')
* @param p_processing_set_id Id that helps the API in identifying the set of
* SRs for which the child objects have to be deleted.
* @param p_purge_set_id Id that helps identify a set of SRs that were purged
* in a single batch. This can be passed as NULL if the SR Delete API is called
* separately. In that case, the purge_set_id will be generated in this
* procedure.
* @param p_audit_required Indicates if audit information has to be generated
* after purging the service requests
* @rep:scope internal
* @rep:product CS
* @rep:displayname Delete Service Request Helper Procedure
*/
PROCEDURE Delete_ServiceRequest
(
p_api_version_number IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_purge_set_id IN NUMBER
, p_processing_set_id IN NUMBER
, p_object_type IN VARCHAR2
, p_audit_required IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
--------------------------------------------------------------------------------
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2 (30) := 'DELETE_SERVICEREQUEST';
l_inc_last_update_date_tbl t_date_tbl;
SELECT
cs_incidents_purge_set_s.NEXTVAL
INTO
l_purge_set_id
FROM
dual
WHERE
ROWNUM = 1;
, 'calling the Messages purge API cs_messages_pkg.delete_message'
);
CS_MESSAGES_PKG.Delete_Message
(
p_api_version_number => '1.0'
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_processing_set_id => p_processing_set_id
, p_object_type => p_object_type
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
|| 'cs_sr_delete_util.delete_contacts'
);
CS_SR_DELETE_UTIL.Delete_Contacts
(
p_api_version_number => '1.0'
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_processing_set_id => p_processing_set_id
, p_object_type => p_object_type
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
|| 'cs_sr_delete_util.delete_sr_attributes'
);
CS_SR_DELETE_UTIL.Delete_Sr_Attributes
(
p_api_version_number => '1.0'
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_processing_set_id => p_processing_set_id
, p_object_type => p_object_type
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
|| 'cs_sr_delete_util.delete_audit_records'
);
CS_SR_DELETE_UTIL.Delete_Audit_Records
(
p_api_version_number => '1.0'
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_processing_set_id => p_processing_set_id
, p_object_type => p_object_type
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
|| 'cs_incidentlinks_pvt.delete_incidentlink'
);
CS_INCIDENTLINKS_PVT.Delete_IncidentLink
(
p_api_version_number => '1.0'
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_processing_set_id => p_processing_set_id
, p_object_type => p_object_type
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
|| 'jtf_ih_purge.p_delete_interactions'
);
JTF_IH_PURGE.P_Delete_Interactions
(
p_api_version => '1.0'
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_processing_set_id => p_processing_set_id
, p_object_type => p_object_type
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
|| 'delete attachments, one at a time'
);
SELECT
object_id
BULK COLLECT INTO
l_incident_id_tbl
FROM
jtf_object_purge_param_tmp
WHERE
object_type = 'SR'
AND p_processing_set_id = processing_set_id
AND NVL(purge_status, 'S') = 'S';
|| 'fnd_attached_documents2_pkg.delete_attachments'
);
FND_ATTACHED_DOCUMENTS2_PKG.Delete_Attachments
(
x_entity_name => 'CS_INCIDENTS'
, x_pk1_value => l_incident_id_tbl(j)
, x_pk2_value => null
, x_pk3_value => null
, x_pk4_value => null
, x_pk5_value => null
, x_delete_document_flag => 'Y'
, x_automatically_added_flag => null
);
SELECT
incident_number
, incident_type_id
, customer_id
, inv_organization_id
, inventory_item_id
, customer_product_id
, creation_date
, last_update_date
BULK COLLECT INTO
l_incident_number_tbl
, l_incident_type_id_tbl
, l_customer_id_tbl
, l_inv_organization_id_tbl
, l_inventory_item_id_tbl
, l_customer_product_id_tbl
, l_inc_creation_date_tbl
, l_inc_last_update_date_tbl
FROM
cs_incidents_all_b c
, jtf_object_purge_param_tmp j
WHERE
j.object_type = 'SR'
AND j.object_id = c.incident_id
AND NVL
(
j.purge_status
, 'S'
) = 'S'
AND j.processing_set_id = p_processing_set_id;
SELECT
incident_id
, language
, source_lang
, summary
BULK COLLECT INTO
l_incident_id_tl_tbl
, l_language_tbl
, l_source_lang_tbl
, l_summary_tbl
FROM
cs_incidents_all_tl c
, jtf_object_purge_param_tmp j
WHERE
j.object_type = 'SR'
AND j.object_id = c.incident_id
AND NVL
(
j.purge_status
, 'S'
) = 'S'
AND j.processing_set_id = p_processing_set_id;
SELECT
object_id
, purge_error_message
BULK COLLECT INTO
l_incident_id_status_tbl
, l_purge_error_message_tbl
FROM
jtf_object_purge_param_tmp j
WHERE
j.object_type = 'SR'
AND NVL
(
j.purge_status
, 'S'
) = 'E'
AND j.processing_set_id = p_processing_set_id;
DELETE /*+ index(t) */ cs_incidents_all_tl t
WHERE
incident_id IN
(
SELECT /*+ no_unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
object_type = 'SR'
AND p_processing_set_id = processing_set_id
AND NVL
(
purge_status
, 'S'
) = 'S'
);
DELETE /*+ index(b) */ cs_incidents_all_b b
WHERE
incident_id IN
(
SELECT /*+ no_unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
object_type = 'SR'
AND p_processing_set_id = processing_set_id
AND NVL
(
purge_status
, 'S'
) = 'S'
);
UPDATE cs_incidents_purge_staging
SET
purge_status = 'E'
, purge_error_message = l_purge_error_message_tbl(j)
WHERE
incident_id = l_incident_id_status_tbl(j);
UPDATE cs_incidents_purge_staging
SET
purge_status = 'S'
WHERE
incident_id IN
(
SELECT
object_id
FROM
jtf_object_purge_param_tmp j
WHERE
j.object_type = 'SR'
AND NVL
(
j.purge_status
, 'S'
) = 'S'
AND j.processing_set_id = p_processing_set_id
);
, p_inc_last_update_date_tbl => l_inc_last_update_date_tbl
, p_incident_id_tl_tbl => l_incident_id_tl_tbl
, p_language_tbl => l_language_tbl
, p_source_lang_tbl => l_source_lang_tbl
, p_summary_tbl => l_summary_tbl
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
END Delete_ServiceRequest;
* SRs for which the child objects have to be deleted.
* @rep:scope internal
* @rep:product CS
* @rep:displayname Delete Contacts
*/
PROCEDURE Delete_Contacts
(
p_api_version_number IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_object_type IN VARCHAR2
, p_processing_set_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
--------------------------------------------------------------------------------
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_CONTACTS';
DELETE /*+ index(a) */ cs_sr_contacts_ext_audit a
WHERE
incident_id IN
(
SELECT /*+ unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
object_type = 'SR'
AND p_processing_set_id = processing_set_id
AND NVL
(
purge_status
, 'S'
) = 'S'
);
DELETE /*+ index(e) */ cs_sr_contacts_ext_tl e
WHERE incident_id IN
(
SELECT /*+ no_unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
object_type = 'SR'
AND p_processing_set_id = processing_set_id
AND NVL(purge_status, 'S') = 'S'
);
DELETE /*+ index(e) */ cs_sr_contacts_ext e
WHERE incident_id IN
(
SELECT /*+ no_unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
object_type = 'SR'
AND p_processing_set_id = processing_set_id
AND NVL(purge_status, 'S') = 'S'
);
DELETE /*+ index(a) */ cs_hz_sr_contact_pnts_audit a
WHERE incident_id IN
(
SELECT /*+ no_unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
object_type = 'SR'
AND p_processing_set_id = processing_set_id
AND NVL(purge_status, 'S') = 'S'
);
DELETE /*+ index(c) */ cs_hz_sr_contact_points c
WHERE incident_id IN
(
SELECT /*+ no_unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
object_type = 'SR'
AND p_processing_set_id = processing_set_id
AND NVL(purge_status, 'S') = 'S'
);
END Delete_Contacts;
* This procedure deletes all the audit information related to SRs that are
* present in the global temp table with purge status NULL.
* @param p_object_type Type of object for which this procedure is being called.
* (Here it will be 'SR')
* @param p_processing_set_id Id that helps the API in identifying the set of
* SRs for which the child objects have to be deleted.
* @rep:scope internal
* @rep:product CS
* @rep:displayname Delete Audit Requests
*/
PROCEDURE Delete_Audit_Records
(
p_api_version_number IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_object_type IN VARCHAR2
, p_processing_set_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
--------------------------------------------------------------------------------
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_AUDIT_RECORDS';
DELETE /*+ index(t) */ cs_incidents_audit_tl t
WHERE incident_id IN
(
SELECT /*+ no_unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
object_type = 'SR'
AND p_processing_set_id = processing_set_id
AND NVL(purge_status, 'S') = 'S'
);
DELETE /*+ index(b) */ cs_incidents_audit_b b
WHERE incident_id IN
(
SELECT /*+ unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
object_type = 'SR'
AND p_processing_set_id = processing_set_id
AND NVL(purge_status, 'S') = 'S'
);
END Delete_Audit_Records;
* This procedure deletes all the extended attributes and CIC attributes
* associated with the SRs present in the global temp table with purge status
* NULL along with the audit information captured for the extended attributes.
* @param p_object_type Type of object for which this procedure is being called.
* (Here it will be 'SR')
* @param p_processing_set_id Id that helps the API in identifying the set of
* SRs for which the child objects have to be deleted.
* @rep:scope internal
* @rep:product CS
* @rep:displayname Delete Service Request Attributes
*/
PROCEDURE Delete_Sr_Attributes
(
p_api_version_number IN NUMBER
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_object_type IN VARCHAR2
, p_processing_set_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
--------------------------------------------------------------------------------
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_SR_ATTRIBUTES';
DELETE /*+ index(t) */ cug_incidnt_attr_vals_tl t
WHERE incidnt_attr_val_id IN
(
SELECT /*+ no_unnest no_semijoin leading(j) use_concat cardinality(10) */
c.incidnt_attr_val_id
FROM
jtf_object_purge_param_tmp j
, cug_incidnt_attr_vals_b c
WHERE
j.object_type = 'SR'
AND p_processing_set_id = j.processing_set_id
AND NVL(j.purge_status, 'S') = 'S'
AND c.incident_id = j.object_id
);
DELETE /*+ index(b) */ cug_incidnt_attr_vals_b b
WHERE incident_id IN
(
SELECT /*+ no_unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
object_type = 'SR'
AND p_processing_set_id = processing_set_id
AND NVL(purge_status, 'S') = 'S'
);
DELETE /*+ index(a) */ cs_incidents_ext_audit a
WHERE audit_extension_id IN
(
SELECT /*+ unnest no_semijoin leading(j) use_nl(c) cardinality(10) */
audit_extension_id
FROM
jtf_object_purge_param_tmp j
, cs_incidents_ext_audit c
WHERE
j.object_type = 'SR'
AND p_processing_set_id = j.processing_set_id
AND NVL(j.purge_status, 'S') = 'S'
AND c.incident_id = j.object_id
);
DELETE /*+ index(e) */ cs_incidents_ext_tl e
WHERE incident_id IN
(
SELECT /*+ no_unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
object_type = 'SR'
AND p_processing_set_id = processing_set_id
AND NVL(purge_status, 'S') = 'S'
);
DELETE /*+ index(e) */ cs_incidents_ext e
WHERE incident_id IN
(
SELECT /*+ no_unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
object_type = 'SR'
AND p_processing_set_id = processing_set_id
AND NVL(purge_status, 'S') = 'S'
);
END Delete_Sr_Attributes;
* program. It is called from the DeleteServiceRequest procedure with pl/sql
* tables containing all the data that need to be preserved.
* @param p_purge_set_id Id that helps the API in identifying the set of SRs
* for which the child objects have to be deleted.
* @param p_incident_id_tbl pl/sql table containing all the SR ids that have
* been purged successfully
* @param p_incident_number_tbl pl/sql table containing all the SR numbers that
* have been purged successfully
* @param p_incident_type_id_tbl pl/sql table containing type ids of all the
* SRs that have been purged successfully
* @param p_customer_id_tbl pl/sql table containing customer ids of all the
* SRs that have been purged successfully
* @param p_inv_organization_id_tbl pl/sql table containing org ids of all the
* SRs that have been purged successfully
* @param p_inventory_item_id_tbl pl/sql table containing item ids of all the
* SRs that have been purged successfully
* @param p_customer_product_id_tbl pl/sql table containing instance ids of
* all the SRs that have been purged successfully
* @param p_inc_creation_date_tbl pl/sql table containing creation dates of
* all the SRs that have been purged successfully
* @param p_inc_last_update_date_tbl pl/sql table containing last update
* dates ids of all the SRs that have been purged successfully
* @param p_incident_id_tl_tbl pl/sql table containing SRs ids (as in TL
* table) of all the SRs that have been purged successfully
* @param p_language_tbl pl/sql table containing language of all the SRs
* that have been purged successfully
* @param p_source_lang_tbl pl/sql table containing source lang of all the SRs
* that have been purged successfully
* @param p_summary_tbl pl/sql table containing summary of all the SRs
* that have been purged successfully
* @rep:scope internal
* @rep:product CS
* @rep:displayname Create Purge Audit Records
*/
PROCEDURE Create_Purgeaudit_Records
(
p_api_version_number IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_purge_set_id IN NUMBER
, p_incident_id_tbl IN t_number_tbl
, p_incident_number_tbl IN t_long_string_tbl
, p_incident_type_id_tbl IN t_number_tbl
, p_customer_id_tbl IN t_number_tbl
, p_inv_organization_id_tbl IN t_number_tbl
, p_inventory_item_id_tbl IN t_number_tbl
, p_customer_product_id_tbl IN t_number_tbl
, p_inc_creation_date_tbl IN t_date_tbl
, p_inc_last_update_date_tbl IN t_date_tbl
, p_incident_id_tl_tbl IN t_number_tbl
, p_language_tbl IN t_string_tbl
, p_source_lang_tbl IN t_string_tbl
, p_summary_tbl IN t_long_string_tbl
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
--------------------------------------------------------------------------------
L_API_VERSION CONSTANT NUMBER := 1.0;
, 'Inserting data into table CS_INCIDENTS_PURGE_AUDIT_B'
);
INSERT INTO cs_incidents_purge_audit_b
(
purge_id
, incident_id
, incident_number
, incident_type_id
, customer_id
, inv_organization_id
, inventory_item_id
, customer_product_id
, inc_creation_date
, inc_last_update_date
, purged_date
, purged_by
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES
(
p_purge_set_id
, p_incident_id_tbl(j)
, p_incident_number_tbl(j)
, p_incident_type_id_tbl(j)
, p_customer_id_tbl(j)
, p_inv_organization_id_tbl(j)
, p_inventory_item_id_tbl(j)
, p_customer_product_id_tbl(j)
, p_inc_creation_date_tbl(j)
, p_inc_last_update_date_tbl(j)
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
);
, 'After inserting data into table CS_INCIDENTS_PURGE_AUDIT_B '
|| l_row_count || ' rows'
);
, 'While inserting data into table CS_INCIDENTS_PURGE_AUDIT_B '
|| 'p_incident_id_tbl had ' || p_incident_id_tbl.COUNT || ' rows'
);
, 'Inserting data into table CS_INCIDENTS_PURGE_AUDIT_TL'
);
INSERT INTO cs_incidents_purge_audit_tl
(
purge_id
, incident_id
, language
, source_lang
, summary
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES
(
p_purge_set_id
, p_incident_id_tl_tbl(j)
, p_language_tbl(j)
, p_source_lang_tbl(j)
, p_summary_tbl(j)
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
);
, 'After inserting data into table CS_INCIDENTS_PURGE_AUDIT_TL '
|| l_row_count || ' rows'
);
, 'While inserting data into table CS_INCIDENTS_PURGE_AUDIT_TL '
|| 'p_incident_id_tl_tbl had ' || p_incident_id_tl_tbl.COUNT || ' rows'
);