[Home] [Help]
137: -- This procedure accepts the above list of parameters to identify the SRs
138: -- that need to be purged. It constructs a WHERE clause out of these
139: -- parameters after validating them. This WHERE clause is appended to a
140: -- query on the table CS_INCIDENTS_ALL_B and result of this query is
141: -- inserted into a staging table CS_INCIDENTS_PURGE_STAGING after which
142: -- the rows are divided among the number of worker concurrent programs
143: -- using a formula 'mod(rownum - 1,
144: -- the child concurrent requests are launched and the SRs are purged. This
145: -- procedure waits for all the child concurrent requests to complete
157: * This procedure accepts the above list of parameters to identify the SRs that
158: * need to be purged. It constructs a WHERE clause out of these parameters after
159: * validating them. This WHERE clause is appended to a query on the table
160: * CS_INCIDENTS_ALL_B and result of this query is inserted into a staging table
161: * CS_INCIDENTS_PURGE_STAGING after which the rows are divided among the number
162: * of worker concurrent programs using a formula 'mod(rownum - 1,
163: *
164: * launched and the SRs are purged. This procedure waits for all the child
165: * concurrent requests to complete purging the SRs allocated to them and then
559: , 'deleting rows in staging table that were not cleared earlier'
560: );
561: END IF;
562:
563: DELETE cs_incidents_purge_staging
564: WHERE
565: concurrent_request_id IN
566: (
567: SELECT
1049: END IF;
1050:
1051: -- Cleaning up the staging table
1052:
1053: DELETE cs_incidents_purge_staging
1054: WHERE
1055: concurrent_request_id = l_request_id;
1056:
1057: l_row_count := SQL%ROWCOUNT;
1606: CURSOR c_staging IS
1607: SELECT
1608: incident_id
1609: FROM
1610: cs_incidents_purge_staging
1611: WHERE
1612: worker_id = p_worker_id
1613: AND concurrent_request_id = p_purge_set_id
1614: AND purge_status IS NULL;
1853: FND_LOG.String
1854: (
1855: FND_LOG.level_statement
1856: , L_LOG_MODULE || 'fetch_start'
1857: , 'fetching rows from the cursor on the table CS_INCIDENTS_PURGE_STAGING'
1858: );
1859: END IF ;
1860:
1861: -- fetch a batch of records at a time
1871: (
1872: FND_LOG.level_statement
1873: , L_LOG_MODULE || 'fetch_end'
1874: , 'after fetching rows from the cursor on the table '
1875: || 'CS_INCIDENTS_PURGE_STAGING '
1876: || l_incident_id_tbl.COUNT
1877: );
1878: END IF ;
1879:
2166: -- Updating the staging table with the errors
2167: -- that occurred due to validations.
2168:
2169: FORALL j IN l_err_incident_id_tbl.FIRST..l_err_incident_id_tbl.LAST
2170: UPDATE cs_incidents_purge_staging
2171: SET
2172: purge_status = 'E'
2173: , purge_error_message = l_purge_error_message_tbl(j)
2174: WHERE
2203: -- that occurred due to failure during execution
2204: -- of the SR delete API.
2205:
2206: FORALL j IN l_incident_id_tbl.FIRST..l_incident_id_tbl.LAST
2207: UPDATE cs_incidents_purge_staging
2208: SET
2209: purge_status = 'E'
2210: , purge_error_message = 'CS:CS_SR_PURG_BATCH_FAIL~' || x_msg_data
2211: WHERE
6652: -- purged into the STAGING table. The APPEND hint is used
6653: -- to make sure no redo log is generated. After executing
6654: -- this statement, a COMMIT is mandated.
6655:
6656: l_sql_statement := ' INSERT /*+ APPEND */ INTO cs_incidents_purge_staging '
6657: || ' ( '
6658: || ' incident_id '
6659: || ' , worker_id '
6660: || ' , concurrent_request_id '
6932: -- which helps in dividing the work among the
6933: -- number of workers actually required for working
6934: -- on the current purge set.
6935:
6936: UPDATE cs_incidents_purge_staging
6937: SET
6938: worker_id = MOD
6939: (
6940: ROWNUM - 1
7046: , i.segment1 item_number
7047: , t.summary summary
7048: , s.purge_error_message purge_error_message
7049: FROM
7050: cs_incidents_purge_staging s
7051: , cs_incidents_all_b b
7052: , cs_incidents_all_tl t
7053: , mtl_system_items_b i
7054: , hz_parties p
7171: count(1)
7172: INTO
7173: l_row_count
7174: FROM
7175: cs_incidents_purge_staging s
7176: WHERE
7177: s.worker_id = NVL(p_worker_id, s.worker_id);
7178:
7179: fnd_file.put_line
7228: count(1)
7229: INTO
7230: l_row_count
7231: FROM
7232: cs_incidents_purge_staging s
7233: WHERE
7234: purge_status = 'S'
7235: AND s.worker_id = NVL(p_worker_id, s.worker_id);
7236:
7286: count(1)
7287: INTO
7288: l_row_count
7289: FROM
7290: cs_incidents_purge_staging s
7291: WHERE
7292: purge_status IS NULL
7293: AND s.worker_id = NVL(p_worker_id, s.worker_id);
7294:
7355: count(1)
7356: INTO
7357: l_row_count
7358: FROM
7359: cs_incidents_purge_staging s
7360: WHERE
7361: purge_status = 'E'
7362: AND s.worker_id = NVL(p_worker_id, s.worker_id);
7363: