[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, p_last_update_from_date IN VARCHAR2
, p_last_update_to_date IN VARCHAR2
, x_creation_from_date OUT NOCOPY DATE
, x_creation_to_date OUT NOCOPY DATE
, x_last_update_from_date OUT NOCOPY DATE
, x_last_update_to_date OUT NOCOPY DATE
, p_not_updated_since IN VARCHAR2
, p_customer_id IN NUMBER
, p_customer_acc_id IN NUMBER
, p_item_category_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_history_size IN NUMBER
, p_number_of_workers IN NUMBER
, p_purge_batch_size IN NUMBER
, p_purge_source_with_open_task IN VARCHAR2
, p_audit_required IN VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
);
, p_last_update_from_date IN DATE
, p_last_update_to_date IN DATE
, p_customer_id IN NUMBER
, p_customer_acc_id IN NUMBER
, p_item_category_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_history_size IN NUMBER
, p_number_of_workers IN OUT NOCOPY NUMBER
, p_purge_batch_size IN NUMBER
, p_request_id IN NUMBER
, p_row_count OUT NOCOPY NUMBER
);
* CS_INCIDENTS_ALL_B and result of this query is inserted into a staging table
* CS_INCIDENTS_PURGE_STAGING after which the rows are divided among the number
* of worker concurrent programs using a formula 'mod(rownum - 1,
* ) + 1'. After that the child concurrent requests are
* launched and the SRs are purged. This procedure waits for all the child
* concurrent requests to complete purging the SRs allocated to them and then
* ends.
* @param errbuf This parameter is not used but is a standard parameter for
* concurrent program procedures. The function fnd_concurrent.
* set_completion_status is called instead.
* @param errcode This parameter is not used but is a standard parameter
* for concurrent program procedures. The function fnd_concurrent.
* set_completion_status is called instead.
* @param p_incident_id Indicates that SR with this id needs to be purged
* @param p_incident_status_id Indicates that SR with this status id needs
* to be purged
* @param p_incident_type_id Indicates that SRs with this type id needs to
* be purged
* @param p_creation_from_date Indicates the lower end of the range of dates
* that need to be compared with CREATION_DATE of the SR to pick it up for purge
* @param p_creation_to_date Indicates the higher end of the range of dates that
* need to be compared with CREATION_DATE of the SR to pick it up for purge
* @param p_last_update_from_date Indicates the lower end of the range of dates
* that need to be compared with LAST_UPDATED_DATE of the SR to pick it
* up for purge
* @param p_last_update_to_date Indicates the higher end of the range of dates
* that need to be compared with LAST_UPDATED_DATE of the SR to pick it up for
* purge
* @param p_not_updated_since This is a set of values like 1Y,2Y etc. which
* shall be compared with the LAST_UPDATED_DATE of the the SR to pick it up
* for purge
* @param p_customer_id Indicates that SRs with this customer_id need to
* be purged.
* @param p_customer_acc_id Indicates that SRs with this customer acc id need
* to be purged
* @param p_item_category_id Indicates that SRs created for items falling
* under this category need to be purged
* @param p_inventory_item_id Indicates that SRs created for this item need
* to be purged
* @param p_history_size Number of customer SR's to retain while purging SRs
* identified using other parameters. This parameter alone CANNOT be used to
* identify a valid purgeset.
* @param p_number_of_workers Number of workers that needs to be launched
* for purging Service Requests
* @param p_purge_batch_size Number of Service Requests that needs to be purged
* in a batch
* @param p_purge_source_with_open_task This signifies if the Tasks Validation
* API can delete tasks that are open. If this is N, only SRs linked to closed
* Tasks are allowed to be purged. If this is Y, all SRs, irrespective of
* whether the Tasks linked to them are open or closed, can be deleted.
* @param p_audit_required This indicates if the SR Delete API should write
* the purge audit information. If this is N, no rows are inserted into the
* table CS_INCIDENTS_PURGE_AUDIT_B and TL. If this is Y, audit rows are
* inserted into these tables.
* @rep:scope internal
* @rep:product CS
* @rep:displayname Purge Service Requests Concurrent Program
*/
PROCEDURE Purge_ServiceRequests
(
errbuf IN OUT NOCOPY VARCHAR2
, errcode IN OUT NOCOPY INTEGER
, p_api_version_number IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_validation_level IN NUMBER
, p_incident_id IN NUMBER
, p_incident_status_id IN NUMBER
, p_incident_type_id IN NUMBER
, p_creation_from_date IN VARCHAR2
, p_creation_to_date IN VARCHAR2
, p_last_update_from_date IN VARCHAR2
, p_last_update_to_date IN VARCHAR2
, p_not_updated_since IN VARCHAR2
, p_customer_id IN NUMBER
, p_customer_acc_id IN NUMBER
, p_item_category_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_history_size IN NUMBER
, p_number_of_workers IN NUMBER
, p_purge_batch_size IN NUMBER
, p_purge_source_with_open_task IN VARCHAR2
, p_audit_required IN VARCHAR2
)
IS
--------------------------------------------------------------------------------
L_API_VERSION CONSTANT NUMBER := 1.0;
l_last_update_from_date DATE;
l_last_update_to_date DATE;
SELECT
request_id
FROM
fnd_concurrent_requests
WHERE
parent_request_id = c_request_id;
, 'p_last_update_from_date:' || p_last_update_from_date
);
, 'p_last_update_to_date:' || p_last_update_to_date
);
, 'p_not_updated_since:' || p_not_updated_since
);
DELETE cs_incidents_purge_staging
WHERE
concurrent_request_id IN
(
SELECT
request_id
FROM
fnd_concurrent_requests r
, fnd_concurrent_programs p
WHERE
r.phase_code = 'C'
AND p.concurrent_program_id = r.concurrent_program_id
AND p.concurrent_program_name = 'CSSRPGP'
AND p.application_id = 170
);
, p_last_update_from_date => p_last_update_from_date
, p_last_update_to_date => p_last_update_to_date
, p_not_updated_since => p_not_updated_since
, p_customer_id => p_customer_id
, p_customer_acc_id => p_customer_acc_id
, p_item_category_id => p_item_category_id
, p_inventory_item_id => p_inventory_item_id
, p_history_size => p_history_size
, p_number_of_workers => p_number_of_workers
, p_purge_batch_size => p_purge_batch_size
, p_purge_source_with_open_task => p_purge_source_with_open_task
, p_audit_required => p_audit_required
, x_creation_from_date => l_creation_from_date
, x_creation_to_date => l_creation_to_date
, x_last_update_from_date => l_last_update_from_date
, x_last_update_to_date => l_last_update_to_date
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
, p_last_update_from_date => l_last_update_from_date
, p_last_update_to_date => l_last_update_to_date
, p_customer_id => p_customer_id
, p_customer_acc_id => p_customer_acc_id
, p_item_category_id => p_item_category_id
, p_inventory_item_id => p_inventory_item_id
, p_history_size => p_history_size
, p_number_of_workers => l_number_of_workers
, p_purge_batch_size => p_purge_batch_size
, p_request_id => l_request_id
, p_row_count => l_row_count
);
AND l_worker_conc_req_dev_status IN ('ERROR', 'DELETED', 'TERMINATED')
THEN
l_main_conc_req_dev_status := 'ERROR';
DELETE cs_incidents_purge_staging
WHERE
concurrent_request_id = l_request_id;
* in batches of size purge_batch_size through a cursor and bulk inserts
* these rows into the global temp table JTF_OBJECT_PURGE_PARAM_TMP and calls
* the SR Delete API. At any point in time, several copies of this procedure
* may be running in parallel since the Purge Concurrent Program will generate
* multiple Worker Concurrent Programs based on its parameter no_of_workers.
* @param errbuf This parameter is not used but is a standard parameter for
* concurrent program procedures. The function fnd_concurrent.
* set_completion_status is called instead.
* @param errcode This parameter is not used but is a standard parameter
* for concurrent program procedures. The function
* fnd_concurrent.set_completion_status is called instead.
* @param p_worker_id The number assigned to this worker which enables the
* worker concurrent program to identify the SRs in the staging table that
* it needs to purge
* @param p_purge_set_id The concurrent request id of the parent concurrent
* request. This is used in addition to the worker id to identify the SRs
* in the staging table that need to be purged.
* @param p_purge_batch_size Number of SRs that need to be processed in
* one call to the SR Delete API. At any point in time, a maximum of
* batch_size number of rows will be inserted into the table
* JTF_OBJECT_PURGE_PARAM_TMP, which will be picked up by the SR Delete
* API to purge SRs.
* @param p_purge_source_with_open_task This signifies if the Tasks
* Validation API can delete tasks that are open. If this is N, only SRs
* linked to closed Tasks are allowed to be purged. If this is
* Y, all SRs, irrespective of whether the Tasks linked to them are
* open or closed, can be deleted.
* @param p_audit_required This indicates if the SR Delete API should write
* the purge audit information. If this is N, no rows are inserted into the
* table CS_INCIDENTS_PURGE_AUDIT_B and TL. If this is Y, audit rows
* are inserted into these tables.
* @rep:scope internal
* @rep:product CS
* @rep:displayname Purge Service Requests Worker Concurrent Program
*/
PROCEDURE Purge_Sr_Worker
(
errbuf IN OUT NOCOPY VARCHAR2
, errcode IN OUT NOCOPY INTEGER
, p_api_version_number IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_validation_level IN NUMBER
, p_worker_id IN NUMBER
, p_purge_batch_size IN NUMBER
, p_purge_set_id IN NUMBER
, p_purge_source_with_open_task IN VARCHAR2
, p_audit_required IN VARCHAR2
)
IS
--------------------------------------------------------------------------------
L_API_VERSION CONSTANT NUMBER := 1.0;
SELECT
incident_id
FROM
cs_incidents_purge_staging
WHERE
worker_id = p_worker_id
AND concurrent_request_id = p_purge_set_id
AND purge_status IS NULL;
SELECT
1
INTO
l_row_count
FROM
fnd_concurrent_requests r
, fnd_concurrent_programs p
WHERE
r.request_id = p_purge_set_id
AND p.concurrent_program_id = r.concurrent_program_id
AND p.concurrent_program_name = 'CSSRPGP'
AND p.application_id = 170
AND r.status_code <> 'C';
SELECT
jtf_object_purge_proc_set_s.NEXTVAL
INTO
l_processing_set_id
FROM
dual;
, L_LOG_MODULE || 'insert_temp_start'
, 'inserting incident ids into global temp table '
|| 'JTF_OBJECT_PURGE_PARAM_TMP'
);
INSERT INTO jtf_object_purge_param_tmp
(
object_id
, object_type
, processing_set_id
)
VALUES
(
l_incident_id_tbl(j)
, 'SR'
, l_processing_set_id
);
, L_LOG_MODULE || 'insert_temp_end'
, 'after inserting incident ids into global temp table '
|| 'JTF_OBJECT_PURGE_PARAM_TMP ' || l_row_count
);
, 'calling the service request delete private api'
);
CS_SERVICEREQUEST_PVT.Delete_ServiceRequest
(
p_api_version_number => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, p_processing_set_id => l_processing_set_id
, p_purge_set_id => p_purge_set_id
, p_purge_source_with_open_task => p_purge_source_with_open_task
, p_audit_required => p_audit_required
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
, 'after calling the service request delete private api'
);
SELECT
object_id
, purge_error_message
BULK COLLECT INTO
l_err_incident_id_tbl
, l_purge_error_message_tbl
FROM
jtf_object_purge_param_tmp
WHERE
processing_set_id = l_processing_set_id
AND object_type = 'SR'
AND NVL(purge_status, 'S') = 'E';
, L_LOG_MODULE || 'update_validation_errors'
, 'updating validation errors to staging table again'
);
UPDATE cs_incidents_purge_staging
SET
purge_status = 'E'
, purge_error_message = l_purge_error_message_tbl(j)
WHERE
incident_id = l_err_incident_id_tbl(j);
, L_LOG_MODULE || 'update_validation_errors'
, 'updating validation errors to staging table again ' || l_row_count
);
, L_LOG_MODULE || 'update_oracle_errors'
, 'updating oracle errors to staging table - '
|| 'CS:CS_SR_PURG_BATCH_FAIL~' || x_msg_data
);
UPDATE cs_incidents_purge_staging
SET
purge_status = 'E'
, purge_error_message = 'CS:CS_SR_PURG_BATCH_FAIL~' || x_msg_data
WHERE
incident_id = l_incident_id_tbl(j)
AND NVL(purge_status, 'S') = 'S';
, L_LOG_MODULE || 'update_oracle_errors'
, 'after updating oracle errors to staging table ' || l_row_count
);
SELECT
count(*)
INTO
l_sr_rows
FROM
cs_incidents_all_b b
WHERE
NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
)
AND status_flag = 'C';
SELECT
count(*)
INTO
l_sr_contacts
FROM
cs_hz_sr_contact_points cp
, cs_incidents_all_b b
WHERE
NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
)
AND b.incident_id = cp.incident_id
AND b.status_flag = 'C';
SELECT
count(*)
INTO
l_sr_cont_attrs
FROM
cs_sr_contacts_ext ex
, cs_incidents_all_b b
WHERE
NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
)
AND b.incident_id = ex.incident_id
AND b.status_flag = 'C';
SELECT
count(*)
INTO
l_sr_links
FROM
cs_incident_links l
, cs_incidents_all_b b
WHERE
(
l.subject_id = b.incident_id
AND l.subject_type = 'SR'
OR l.object_id = b.incident_id
AND l.object_type = 'SR'
)
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
)
AND b.status_flag = 'C';
SELECT
count(*)
INTO
l_sr_msgs
FROM
cs_messages msg
, cs_incidents_all_b b
WHERE
msg.source_object_int_id = b.incident_id
AND msg.source_object_type_code = 'INC'
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
)
AND b.status_flag = 'C';
SELECT
count(*)
INTO
l_sr_kb_links
FROM
cs_kb_set_links k
, cs_incidents_all_b b
WHERE
k.object_code = 'SR'
AND k.other_id = b.incident_id
AND b.status_flag = 'C'
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
);
SELECT
count(*)
INTO
l_sr_estimates
FROM
cs_estimate_details es
, cs_incidents_all_b b
WHERE
b.incident_id = es.incident_id
AND b.status_flag = 'C'
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
);
SELECT
count(*)
INTO
l_sr_tasks
FROM
jtf_tasks_b j
, cs_incidents_all_b b
WHERE
b.incident_id = j.source_object_id
AND j.source_object_type_code = 'SR'
AND b.status_flag = 'C'
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
);
SELECT
count(*)
INTO
l_sr_notes
FROM
jtf_notes_b j
, cs_incidents_all_b b
WHERE
b.incident_id = j.source_object_id
AND j.source_object_code = 'SR'
AND b.status_flag = 'C'
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
);
SELECT
count(*)
INTO
l_sr_activities
FROM
jtf_ih_activities j
, cs_incidents_all_b b
WHERE
b.incident_id = j.doc_id
AND j.doc_ref = 'SR'
AND b.status_flag = 'C'
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
);
SELECT
count(*)
INTO
l_sr_attachs
FROM
fnd_attached_documents d
, cs_incidents_all_b b
WHERE
b.incident_id = d.pk1_value
AND d.entity_name = 'CS_INCIDENTS'
AND b.status_flag = 'C'
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
);
SELECT
count(*)
INTO
l_sr_work_items
FROM
ieu_uwqm_items u
, cs_incidents_all_b b
WHERE
b.incident_id = u.workitem_pk_id
AND u.workitem_obj_code = 'SR'
AND b.status_flag = 'C'
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
);
SELECT
count(*)
INTO
l_sr_audit_1
FROM
cs_incidents_ext_audit a
, cs_incidents_all_b b
WHERE
b.incident_id = a.incident_id
AND b.status_flag = 'C'
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
);
SELECT
count(*)
INTO
l_sr_audit_2
FROM
cs_sr_contacts_ext_audit a
, cs_incidents_all_b b
WHERE
b.incident_id = a.incident_id
AND b.status_flag = 'C'
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
);
SELECT
count(*)
INTO
l_sr_audit_3
FROM
cs_incidents_audit_tl a
, cs_incidents_all_b b
WHERE
b.incident_id = a.incident_id
AND b.status_flag = 'C'
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
);
SELECT
count(*)
INTO
l_sr_audit_4
FROM
cs_incidents_audit_b a
, cs_incidents_all_b b
WHERE
b.incident_id = a.incident_id
AND b.status_flag = 'C'
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
);
SELECT
count(*)
INTO
l_sr_attr_1
FROM
cug_incidnt_attr_vals_b a
, cs_incidents_all_b b
WHERE
b.incident_id = a.incident_id
AND b.status_flag = 'C'
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
);
SELECT
count(*)
INTO
l_sr_attr_2
FROM
cs_incidents_ext a
, cs_incidents_all_b b
WHERE
b.incident_id = a.incident_id
AND b.status_flag = 'C'
AND NOT EXISTS
(
SELECT
1
FROM
csd_repairs
WHERE
incident_id = b.incident_id
)
AND NOT EXISTS
(
SELECT
1
FROM
cs_incident_types_b
WHERE
incident_type_id = b.incident_type_id
AND
(
NVL
(
maintenance_flag
, 'N'
) = 'Y'
OR NVL
(
cmro_flag
, 'N'
) = 'Y'
)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_rows)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_attrs)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_contacts)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_cont_attrs)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_links)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_msgs)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_kb_links)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_estimates)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_tasks)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_notes)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_activities)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_attachs)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_work_items)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_audits)
);
FND_CONC_SUMMARIZER.Insert_Row
(
l_string
, to_char(l_sr_total_rows)
);
* dates and last updated from/to dates to avoid too many parameters
* being passed back and forth.
* @param x_creation_from_date If the p_creation_from_date is supplied,
* the validated and converted value is returned into this parameter
* @param x_creation_to_date If the p_creation_to_date is supplied, the
* validated and converted value is returned into this parameter
* @param x_last_update_from_date If the p_last_update_from_date is supplied,
* the validated and converted value is returned into this parameter
* @param x_last_update_to_date If the p_last_update_to_date is supplied,
* the validated and converted value is returned into this parameter
* @param p_incident_id Indicates that SR with this id needs to be purged
* @param p_incident_status_id Indicates that SR with this status id needs
* to be purged
* @param p_incident_type_id Indicates that SRs with this type id needs to
* be purged
* @param p_creation_from_date Indicates the lower end of the range of
* dates that need to be compared with CREATION_DATE of the SR to pick
* it up for purge
* @param p_creation_to_date Indicates the higher end of the range of
* dates that need to be compared with CREATION_DATE of the SR to pick
* it up for purge
* @param p_last_update_from_date Indicates the lower end of the range of
* dates that need to be compared with LAST_UPDATED_DATE of the SR to
* pick it up for purge
* @param p_last_update_to_date Indicates the higher end of the range of
* dates that need to be compared with LAST_UPDATED_DATE of the SR to pick
* it up for purge
* @param p_not_updated_since This is a set of values like 1Y,2Y etc.
* which shall be compared with the LAST_UPDATED_DATE of the the SR to pick
* it up for purge
* @param p_customer_id Indicates that SRs with this customer_id need to
* be purged.
* @param p_customer_acc_id Indicates that SRs with this customer acc id
* need to be purged
* @param p_item_category_id Indicates that SRs created for items falling
* under this category need to be purged
* @param p_inventory_item_id Indicates that SRs created for this item
* need to be purged
* @param p_history_size Number of customer SR's to retain while purging
* SRs identified using other parameters. This parameter alone CANNOT be
* used to identify a valid purgeset.
* @param p_number_of_workers Number of workers that needs to be launched
* for purging Service Requests
* @param p_purge_batch_size Number of Service Requests that needs to
* be purged in a batch
* @param p_purge_source_with_open_task This signifies if the Tasks
* Validation API can delete tasks that are open. If this is N, only SRs
* linked to closed Tasks are allowed to be purged. If this is Y, all SRs,
* irrespective of whether the Tasks linked to them are open or closed,
* can be deleted.
* @param p_audit_required This indicates if the SR Delete API should write
* the purge audit information. If this is N, no rows are inserted into the
* table CS_INCIDENTS_PURGE_AUDIT_B and TL. If this is Y, audit rows are
* inserted into these tables.
* @rep:scope internal
* @rep:product CS
* @rep:displayname Validate Purge Parameters
*/
PROCEDURE Validate_Purge_Params
(
p_incident_id IN NUMBER
, p_incident_status_id IN NUMBER
, p_incident_type_id IN NUMBER
, p_creation_from_date IN VARCHAR2
, p_creation_to_date IN VARCHAR2
, p_last_update_from_date IN VARCHAR2
, p_last_update_to_date IN VARCHAR2
, x_creation_from_date OUT NOCOPY DATE
, x_creation_to_date OUT NOCOPY DATE
, x_last_update_from_date OUT NOCOPY DATE
, x_last_update_to_date OUT NOCOPY DATE
, p_not_updated_since IN VARCHAR2
, p_customer_id IN NUMBER
, p_customer_acc_id IN NUMBER
, p_item_category_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_history_size IN NUMBER
, p_number_of_workers IN NUMBER
, p_purge_batch_size IN NUMBER
, p_purge_source_with_open_task IN VARCHAR2
, p_audit_required IN VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
--------------------------------------------------------------------------------
L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_PURGE_PARAMS';
l_not_updated_since VARCHAR2(10);
, 'p_last_update_from_date:' || p_last_update_from_date
);
, 'p_last_update_to_date:' || p_last_update_to_date
);
, 'p_not_updated_since:' || p_not_updated_since
);
AND p_last_update_from_date IS NULL
AND p_last_update_to_date IS NULL
AND p_not_updated_since IS NULL
AND p_customer_id IS NULL
AND p_customer_acc_id IS NULL
AND p_item_category_id IS NULL
AND p_inventory_item_id IS NULL
AND p_history_size IS NULL
THEN
IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_unexpected
, L_LOG_MODULE || 'no_params'
, 'no parameters were supplied to the purge program'
);
SELECT
form_left_prompt
INTO
l_prompt
FROM
fnd_descr_flex_col_usage_vl
WHERE
end_user_column_name = 'P_CREATION_FROM_DATE'
AND application_id = 170
AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
SELECT
form_left_prompt
INTO
l_prompt
FROM
fnd_descr_flex_col_usage_vl
WHERE
end_user_column_name = 'P_CREATION_TO_DATE'
AND application_id = 170
AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
IF p_last_update_from_date IS NOT NULL
THEN
IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_statement
, L_LOG_MODULE || 'check_date_format_start_3'
, 'checking if p_last_update_from_date is in the format '
|| fnd_date.user_mask
);
x_last_update_from_date := fnd_date.string_to_date
(
p_last_update_from_date
, fnd_date.user_mask
);
IF x_last_update_from_date IS NULL
THEN
IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_unexpected
, L_LOG_MODULE || 'lstupdfrmdt_format_invalid'
, 'format of field p_last_update_from_date is invalid. should be '
|| fnd_date.user_mask
);
SELECT
form_left_prompt
INTO
l_prompt
FROM
fnd_descr_flex_col_usage_vl
WHERE
end_user_column_name = 'P_LAST_UPDATE_FROM_DATE'
AND application_id = 170
AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
, 'after checking if p_last_update_from_date is in the format '
|| fnd_date.user_mask
);
IF p_last_update_to_date IS NOT NULL
THEN
IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_statement
, L_LOG_MODULE || 'check_date_format_start_4'
, 'checking if p_last_update_to_date is in the format '
|| fnd_date.user_mask
);
x_last_update_to_date := fnd_date.string_to_date
(
p_last_update_to_date
, fnd_date.user_mask
);
IF x_last_update_to_date IS NULL
THEN
IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_unexpected
, L_LOG_MODULE || 'lstupdtodt_format_invalid'
, 'format of field p_last_update_to_date is invalid. should be '
|| fnd_date.user_mask
);
SELECT
form_left_prompt
INTO
l_prompt
FROM
fnd_descr_flex_col_usage_vl
WHERE
end_user_column_name = 'P_LAST_UPDATE_TO_DATE'
AND application_id = 170
AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
, 'after checking if p_last_update_to_date is in the format '
|| fnd_date.user_mask
);
IF TRUNC(x_last_update_to_date) = x_last_update_to_date
THEN
IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_statement
, L_LOG_MODULE || 'add_time_to_todate_start'
, 'adding time to x_last_update_to_date as it does not have time'
);
x_last_update_to_date := x_last_update_to_date + TIME_23_59_59;
, 'after adding time to x_last_update_to_date as it '
|| 'does not have time '
|| TO_CHAR(x_last_update_to_date, 'DD-MON-YYYY HH24:MI:SS')
);
IF p_not_updated_since IS NOT NULL
AND p_last_update_from_date IS NULL
AND p_last_update_to_date IS NULL
-- Consider the p_not_updated_since parameter only
-- if the parameters last_updated_from_date and
-- last_updated_to_date are omitted. Otherwise, use
-- the explisit values provided in the parameters.
THEN
-- Assign the value of p_not_updated_since to l_not_updated_since
-- just to indicate that the value of the parameter p_not_updated_since
-- is considered for framing the purge set.
l_not_updated_since := p_not_updated_since;
, L_LOG_MODULE || 'not_updated_since_start'
, 'computing last_updated_from_date and last_updated_to_date'
);
l_month_loc := INSTR(p_not_updated_since, 'M');
l_year_loc := INSTR(p_not_updated_since, 'Y');
, 'getting month and year values from p_not_updated_since'
);
l_str_month_part := SUBSTR(p_not_updated_since, 1, l_month_loc - 1);
OR l_month_loc < LENGTH(p_not_updated_since)
THEN
IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_unexpected
, L_LOG_MODULE || 'month_year_loc_err_2'
, 'error while getting the month/year combination from the lookup'
);
l_str_year_part := SUBSTR(p_not_updated_since, 1, l_year_loc - 1);
OR l_year_loc < LENGTH(p_not_updated_since)
THEN
IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_unexpected
, L_LOG_MODULE || 'month_year_loc_err_3'
, 'error while getting the month/year combination from the lookup'
);
l_str_year_part := SUBSTR(p_not_updated_since, 1, l_year_loc - 1);
p_not_updated_since
, l_year_loc + 1
, l_month_loc - l_year_loc - 1
);
OR l_month_loc < LENGTH(p_not_updated_since)
THEN
IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_unexpected
, L_LOG_MODULE || 'month_year_loc_err_4'
, 'error while getting the month/year combination from the lookup'
);
l_str_month_part := SUBSTR(p_not_updated_since, 1, l_month_loc - 1);
p_not_updated_since
, l_month_loc + 1
, l_year_loc - l_month_loc - 1
);
OR l_year_loc < LENGTH(p_not_updated_since)
THEN
IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_unexpected
, L_LOG_MODULE || 'month_year_loc_err_7'
, 'error while getting the month/year combination from the lookup'
);
|| 'from p_not_updated_since ' ||
l_month_part || ' ' || l_year_part
);
p_not_updated_since || ' ' || l_interval
);
x_last_update_to_date := trunc(SYSDATE)
- to_yminterval(l_interval)
+ TIME_23_59_59;
x_last_update_from_date := NULL;
, L_LOG_MODULE || 'not_updated_since_1'
, 'x_last_update_from_date:' || x_last_update_from_date
);
, L_LOG_MODULE || 'not_updated_since_2'
, 'x_last_update_to_date:'
|| TO_CHAR(x_last_update_to_date, 'DD-MON-YYYY HH24:MI:SS')
);
, L_LOG_MODULE || 'not_updated_since_end'
, 'after computing last_updated_from_date and last_updated_to_date'
);
IF x_last_update_from_date IS NOT NULL
THEN
IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_statement
, L_LOG_MODULE || 'check_lupdfromdt_start'
, 'checking value for field x_last_update_from_date'
);
IF x_last_update_from_date > SYSDATE
THEN
IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_unexpected
, L_LOG_MODULE || 'lupddtfrom_invalid'
, 'x_last_update_from_date is invalid'
);
, 'after checking value for field x_last_update_from_date'
);
IF x_last_update_to_date IS NOT NULL
THEN
IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_statement
, L_LOG_MODULE || 'check_lupdtodt_start'
, 'checking value for field x_last_update_to_date'
);
IF x_last_update_to_date > SYSDATE
THEN
IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_unexpected
, L_LOG_MODULE || 'lupddtto_invalid'
, 'x_last_update_to_date is invalid'
);
, 'after checking value for field x_last_update_to_date'
);
SELECT
form_left_prompt
INTO
l_prompt
FROM
fnd_descr_flex_col_usage_vl
WHERE
end_user_column_name = 'P_CREATION_FROM_DATE'
AND application_id = 170
AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
SELECT
form_left_prompt
INTO
l_prompt
FROM
fnd_descr_flex_col_usage_vl
WHERE
end_user_column_name = 'P_CREATION_TO_DATE'
AND application_id = 170
AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
AND x_last_update_from_date IS NOT NULL
THEN
IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_statement
, L_LOG_MODULE || 'date_crossvalid_start_2'
, 'doing cross field validations x_creation_from_date > '
|| 'x_last_update_from_date '
);
IF x_creation_from_date > x_last_update_from_date
THEN
IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_unexpected
, L_LOG_MODULE || 'crtfrmdt_after_lupdfrmdt'
, 'it is invalid to have x_creation_from_date > '
|| 'x_last_update_from_date'
);
SELECT
form_left_prompt
INTO
l_prompt
FROM
fnd_descr_flex_col_usage_vl
WHERE
end_user_column_name = 'P_CREATION_FROM_DATE'
AND application_id = 170
AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
SELECT
form_left_prompt
INTO
l_prompt
FROM
fnd_descr_flex_col_usage_vl
WHERE
end_user_column_name = DECODE
(
l_not_updated_since
, NULL
, 'P_LAST_UPDATE_FROM_DATE'
, 'P_NOT_UPDATED_SINCE'
)
AND application_id = 170
AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
|| 'x_last_update_from_date '
);
AND x_last_update_to_date IS NOT NULL
THEN
IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_statement
, L_LOG_MODULE || 'date_crossvalid_start_3'
, 'doing cross field validations x_creation_from_date > '
|| 'x_last_update_to_date'
);
IF x_creation_from_date > x_last_update_to_date
THEN
IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_unexpected
, L_LOG_MODULE || 'crttodt_after_lupdtodt'
, 'it is invalid to have x_creation_from_date > '
|| 'x_last_update_to_date'
);
SELECT
form_left_prompt
INTO
l_prompt
FROM
fnd_descr_flex_col_usage_vl
WHERE
end_user_column_name = 'P_CREATION_FROM_DATE'
AND application_id = 170
AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
SELECT
form_left_prompt
INTO
l_prompt
FROM
fnd_descr_flex_col_usage_vl
WHERE
end_user_column_name = DECODE
(
l_not_updated_since
, NULL
, 'P_LAST_UPDATE_TO_DATE'
, 'P_NOT_UPDATED_SINCE'
)
AND application_id = 170
AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
|| 'x_last_update_to_date'
);
AND x_last_update_to_date IS NOT NULL
THEN
IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_statement
, L_LOG_MODULE || 'date_crossvalid_start_4'
, 'doing cross field validations x_creation_to_date > '
|| 'x_last_update_to_date'
);
IF x_creation_to_date > x_last_update_to_date
THEN
IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_unexpected
, L_LOG_MODULE || 'crttodt_after_lupdtodt'
, 'it is invalid to have x_creation_to_date > x_last_update_to_date'
);
SELECT
form_left_prompt
INTO
l_prompt
FROM
fnd_descr_flex_col_usage_vl
WHERE
end_user_column_name = 'P_CREATION_TO_DATE'
AND application_id = 170
AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
SELECT
form_left_prompt
INTO
l_prompt
FROM
fnd_descr_flex_col_usage_vl
WHERE
end_user_column_name = DECODE
(
l_not_updated_since
, NULL
, 'P_LAST_UPDATE_TO_DATE'
, 'P_NOT_UPDATED_SINCE'
)
AND application_id = 170
AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
|| 'x_last_update_to_date'
);
IF x_last_update_from_date IS NOT NULL
AND x_last_update_to_date IS NOT NULL
THEN
IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_statement
, L_LOG_MODULE || 'date_crossvalid_start_5'
, 'doing cross field validations x_last_update_from_date > '
|| 'x_last_update_to_date'
);
IF x_last_update_from_date > x_last_update_to_date
THEN
IF FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_unexpected
, L_LOG_MODULE || 'lupdfrmdt_after_lupdtodt'
, 'it is invalid to have x_last_update_from_date > '
|| 'x_last_update_to_date'
);
SELECT
form_left_prompt
INTO
l_prompt
FROM
fnd_descr_flex_col_usage_vl
WHERE
end_user_column_name = 'P_LAST_UPDATE_FROM_DATE'
AND application_id = 170
AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
SELECT
form_left_prompt
INTO
l_prompt
FROM
fnd_descr_flex_col_usage_vl
WHERE
end_user_column_name = 'P_LAST_UPDATE_TO_DATE'
AND application_id = 170
AND descriptive_flexfield_name = '$SRS$.CSSRPGP';
, 'after doing cross field validations x_last_update_from_date > '
|| 'x_last_update_to_date'
);
* inserts SR ids that can be purged into the staging table. Bind variables are
* created and used in the dynamic SQL.
* @param p_request_id Concurrent Request Id of the current request
* @param p_incident_id Indicates that SR with this id needs to be purged
* @param p_incident_status_id Indicates that SR with this status id needs to
* be purged
* @param p_incident_type_id Indicates that SRs with this type id needs to be
* purged
* @param p_creation_from_date Indicates the lower end of the range of dates
* that need to be compared with CREATION_DATE of the SR to pick it up for
* purge
* @param p_creation_to_date Indicates the higher end of the range of dates
* that need to be compared with CREATION_DATE of the SR to pick it up for purge
* @param p_last_update_from_date Indicates the lower end of the range of dates
* that need to be compared with LAST_UPDATED_DATE of the SR to pick it up for
* purge
* @param p_last_update_to_date Indicates the higher end of the range of dates
* that need to be compared with LAST_UPDATED_DATE of the SR to pick it up for
* purge
* @param p_customer_id Indicates that SRs with this customer_id need to be
* purged.
* @param p_customer_acc_id Indicates that SRs with this customer acc id need
* to be purged
* @param p_item_category_id Indicates that SRs created for items falling under
* this category need to be purged
* @param p_inventory_item_id Indicates that SRs created for this item need to
* be purged
* @param p_history_size Number of customer SR's to retain while purging SRs
* identified using other parameters. This parameter alone CANNOT be used to
* identify a valid purgeset.
* @param p_number_of_workers Number of workers that needs to be launched for
* purging Service Requests
* @param p_row_count Number of rows inserted into the staging table
* @rep:scope internal
* @rep:product CS
* @rep:displayname Form and Execute SQL Statement
*/
PROCEDURE Form_And_Exec_Statement
(
p_incident_id IN NUMBER
, p_incident_status_id IN NUMBER
, p_incident_type_id IN NUMBER
, p_creation_from_date IN DATE
, p_creation_to_date IN DATE
, p_last_update_from_date IN DATE
, p_last_update_to_date IN DATE
, p_customer_id IN NUMBER
, p_customer_acc_id IN NUMBER
, p_item_category_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_history_size IN NUMBER
, p_number_of_workers IN OUT NOCOPY NUMBER
, p_purge_batch_size IN NUMBER
, p_request_id IN NUMBER
, p_row_count OUT NOCOPY NUMBER
)
IS
--------------------------------------------------------------------------------
L_API_NAME CONSTANT VARCHAR2(30) := 'FORM_AND_EXEC_STATEMENT';
, 'p_last_update_from_date:' || p_last_update_from_date
);
, 'p_last_update_to_date:' || p_last_update_to_date
);
IF p_last_update_from_date IS NOT NULL
THEN
IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_statement
, L_LOG_MODULE || 'where_last_update_from_date_start'
, 'framing where clause for p_last_update_from_date'
);
l_where_clause_arr(l_bind_var_ctr) := ' last_update_date >= :bind'
|| l_bind_var_ctr || ' ';
p_last_update_from_date
, 'DD-MM-RRRR HH24:MI:SS'
);
, L_LOG_MODULE || 'where_last_update_from_date_1'
, 'l_where_clause_arr(l_bind_var_ctr):'
|| l_where_clause_arr(l_bind_var_ctr)
);
, L_LOG_MODULE || 'where_last_update_from_date_2'
, 'l_bind_var_val_arr(l_bind_var_ctr):'
|| l_bind_var_val_arr(l_bind_var_ctr)
);
, L_LOG_MODULE || 'where_last_update_from_date_end'
, 'after framing where clause for p_last_update_from_date'
);
IF p_last_update_to_date IS NOT NULL
THEN
IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
THEN
FND_LOG.String
(
FND_LOG.level_statement
, L_LOG_MODULE || 'where_last_update_to_date_start'
, 'framing where clause for p_last_update_to_date'
);
l_where_clause_arr(l_bind_var_ctr) := ' last_update_date <= :bind'
|| l_bind_var_ctr || ' ';
p_last_update_to_date
, 'DD-MM-RRRR HH24:MI:SS'
);
, L_LOG_MODULE || 'where_last_update_to_date_1'
, 'l_where_clause_arr(l_bind_var_ctr):'
|| l_where_clause_arr(l_bind_var_ctr)
);
, L_LOG_MODULE || 'where_last_update_to_date_2'
, 'l_bind_var_val_arr(l_bind_var_ctr):'
|| l_bind_var_val_arr(l_bind_var_ctr)
);
, L_LOG_MODULE || 'where_last_update_to_date_end'
, 'after framing where clause for p_last_update_to_date'
);
|| ' SELECT '
|| ' 1 '
|| ' FROM '
|| ' csd_repairs '
|| ' WHERE '
|| ' incident_id = basetbl.incident_id '
|| ' ) ';
|| ' SELECT '
|| ' 1 '
|| ' FROM '
|| ' cs_incident_types_b '
|| ' WHERE '
|| ' incident_type_id = basetbl.incident_type_id '
|| ' AND '
|| ' ( '
|| ' NVL(maintenance_flag, ''N'') = ''Y'' '
|| ' OR NVL(cmro_flag, ''N'') = ''Y'' '
|| ' ) '
|| ' ) ';
, L_LOG_MODULE || 'final_select_start'
, 'framing final select statement'
);
l_sql_statement := ' INSERT /*+ APPEND */ INTO cs_incidents_purge_staging '
|| ' ( '
|| ' incident_id '
|| ' , worker_id '
|| ' , concurrent_request_id '
|| ' ) '
|| ' SELECT '
|| ' incident_id '
|| ' , NULL '
|| ' , :request_id ';
|| ' SELECT '
|| ' incident_id '
|| ' , RANK() OVER '
|| ' ( '
|| ' PARTITION BY '
|| ' customer_id '
|| ' ORDER BY '
|| ' creation_date DESC '
|| ' , incident_id DESC '
|| ' ) AS group_row_num '
|| ' FROM '
|| ' cs_incidents_all_b basetbl '
|| ' WHERE '
|| l_where_clause
|| ' ) inner '
|| ' WHERE '
|| ' inner.group_row_num > :histoy_size ';
, L_LOG_MODULE || 'final_select_end'
, 'l_sql_statement:' || l_sql_statement
);
, L_LOG_MODULE || 'final_select_end'
, 'after framing final select statement'
);
, 'Executing the SQL framed to insert SRs to staging table'
);
, 'After executing the SQL framed to insert SRs to '
|| 'staging table - inserted ' || p_row_count || ' rows'
);
UPDATE cs_incidents_purge_staging
SET
worker_id = MOD
(
ROWNUM - 1
, l_number_of_workers
) + 1;
* be inserted into the output file at any point in time. This parameter is
* the same batch size that is used while picking up SRs for purging.
* @param p_request_id Concurrent Request id for which output needs to be
* generated.
* @param p_worker_id Worker Number for which the output needs to be generated.
* This field can be left NULL if the output is to be generated for the parent
* request.
* @rep:scope internal
* @rep:product CS
* @rep:displayname Write Purge Program Output
*/
PROCEDURE Write_Purge_Output
(
p_purge_batch_size IN NUMBER
, p_request_id IN NUMBER
, p_worker_id IN NUMBER := NULL
)
IS
--------------------------------------------------------------------------------
L_API_NAME CONSTANT VARCHAR2(30) := 'WRITE_PURGE_OUTPUT';
SELECT
b.incident_number incident_number
, p.party_number customer_number
, i.segment1 item_number
, t.summary summary
, s.purge_error_message purge_error_message
FROM
cs_incidents_purge_staging s
, cs_incidents_all_b b
, cs_incidents_all_tl t
, mtl_system_items_b i
, hz_parties p
WHERE
s.purge_status = 'E'
AND s.incident_id = b.incident_id
AND s.incident_id = t.incident_id
AND b.inventory_item_id = i.inventory_item_id(+)
AND b.inv_organization_id = i.organization_id(+)
AND b.customer_id = p.party_id
AND t.language = USERENV('LANG')
AND s.concurrent_request_id = p_request_id
AND s.worker_id = NVL(p_worker_id, s.worker_id)
ORDER BY
b.incident_number;
SELECT
count(1)
INTO
l_row_count
FROM
cs_incidents_purge_staging s
WHERE
s.worker_id = NVL(p_worker_id, s.worker_id);
SELECT
count(1)
INTO
l_row_count
FROM
cs_incidents_purge_staging s
WHERE
purge_status = 'S'
AND s.worker_id = NVL(p_worker_id, s.worker_id);
SELECT
count(1)
INTO
l_row_count
FROM
cs_incidents_purge_staging s
WHERE
purge_status IS NULL
AND s.worker_id = NVL(p_worker_id, s.worker_id);
SELECT
count(1)
INTO
l_row_count
FROM
cs_incidents_purge_staging s
WHERE
purge_status = 'E'
AND s.worker_id = NVL(p_worker_id, s.worker_id);