[Home] [Help]
PACKAGE BODY: APPS.OKS_IMPORT_PURGE_WORKER
Source
1 PACKAGE BODY OKS_IMPORT_PURGE_WORKER AS
2 -- $Header: OKSPKIMPPRGWRB.pls 120.1 2007/08/20 14:00:59 vmutyala noship $
3 --+=======================================================================+
4 --| Copyright (c) 2003 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| OKSPKIMPPRGWRB.pls Created By Vamshi Mutyala |
10 --| |
11 --| DESCRIPTION |
12 --| Service Contracts Import Purge Worker Package |
13 --| |
14 --+========================================================================
15 --===================
16 -- GLOBALS
17 --===================
18
19 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKS_IMPORT_PURGE_WORKER';
20
21 --========================================================================
22 -- PRIVATE CONSTANTS AND VARIABLES
23 --========================================================================
24 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'oks.plsql.import.' || G_PKG_NAME;
25 G_WORKER_REQ_ID CONSTANT NUMBER := FND_GLOBAL.conc_request_id;
26 G_MODULE_HEAD CONSTANT VARCHAR2(200) := G_MODULE_NAME || '(Req Id = '||G_WORKER_REQ_ID||').';
27 G_LOG_LEVEL CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
28 G_UNEXPECTED_LOG CONSTANT BOOLEAN := fnd_log.level_unexpected >= G_LOG_LEVEL AND
29 fnd_log.TEST(fnd_log.level_unexpected, G_MODULE_HEAD);
30 G_ERROR_LOG CONSTANT BOOLEAN := G_UNEXPECTED_LOG AND fnd_log.level_error >= G_LOG_LEVEL;
31 G_EXCEPTION_LOG CONSTANT BOOLEAN := G_ERROR_LOG AND fnd_log.level_exception >= G_LOG_LEVEL;
32 G_EVENT_LOG CONSTANT BOOLEAN := G_EXCEPTION_LOG AND fnd_log.level_event >= G_LOG_LEVEL;
33 G_PROCEDURE_LOG CONSTANT BOOLEAN := G_EVENT_LOG AND fnd_log.level_procedure >= G_LOG_LEVEL;
34 G_STMT_LOG CONSTANT BOOLEAN := G_PROCEDURE_LOG AND fnd_log.level_statement >= G_LOG_LEVEL;
35
36 --=========================
37 -- PROCEDURES AND FUNCTIONS
38 --=========================
39
40 --========================================================================
41 -- PROCEDURE : Worker_purge PRIVATE
42 -- PARAMETERS: X_errbuf out Error message buffer
43 -- X_retcode out Return status code
44 -- P_commit_size in Work unit size
45 -- P_worker_id in Worker Id
46 -- P_Num_Workers in Number of workers
47 -- P_batch_id in Batch Id
48 -- COMMENT : This procedure is the worker in AD parallel framework
49 -- to delete interface records
50 --=========================================================================
51 PROCEDURE Worker_purge (X_errbuf OUT NOCOPY VARCHAR2,
52 X_retcode OUT NOCOPY VARCHAR2,
53 P_commit_size IN NUMBER,
54 P_worker_id IN NUMBER,
55 P_Num_Workers IN NUMBER,
56 P_batch_id IN NUMBER)
57 IS
58 l_stmt_num NUMBER := 0;
59 l_routine CONSTANT VARCHAR2(30) := 'Worker_purge';
60 l_product varchar2(30) := 'OKS';
61 l_table_name varchar2(30) := 'OKS_HEADERS_INTERFACE';
62 l_update_name varchar2(30);
63 l_status varchar2(30);
64 l_industry varchar2(30);
65 l_retstatus boolean;
66 l_table_owner varchar2(30);
67 l_any_rows_to_process boolean;
68 l_start_rowid rowid;
69 l_end_rowid rowid;
70 l_rows_processed number;
71 l_msg_data VARCHAR2(2000);
72 l_msg_count NUMBER;
73 l_row_count NUMBER := 0;
74 BEGIN
75 --
76 -- Worker processing for OKS_HEADERS_INTERFACE table
77 --
78 IF G_PROCEDURE_LOG THEN
79 fnd_log.string(fnd_log.level_procedure,
80 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
81 'Entering with ' ||
82 'P_batch_id = ' || P_batch_id ||','||
83 'P_Num_Workers = ' || P_Num_Workers ||','||
84 'P_commit_size = ' || P_commit_size ||','||
85 'P_worker_id = ' || P_worker_id);
86 END IF;
87
88 l_update_name := 'Purge'||P_batch_id;
89
90 l_stmt_num := 10;
91
92 --
93 -- get schema name of the table for ROWID range processing
94 --
95 l_retstatus := fnd_installation.get_app_info(l_product,
96 l_status,
97 l_industry,
98 l_table_owner);
99
100 if ((l_retstatus = FALSE) OR (l_table_owner is null)) then
101 IF G_ERROR_LOG THEN
102 fnd_log.string(fnd_log.level_error,
103 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
104 'Cannot get schema name for product : '||l_product);
105 END IF;
106 raise_application_error(-20001,'Cannot get schema name for product : '||l_product);
107 end if;
108
109 IF G_STMT_LOG THEN
110 fnd_log.string(fnd_log.level_statement,
111 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
112 'Table Owner ' || l_table_owner);
113 END IF;
114
115 l_stmt_num := 20;
116
117 ad_parallel_updates_pkg.initialize_rowid_range(ad_parallel_updates_pkg.ROWID_RANGE,
118 l_table_owner,
119 l_table_name,
120 l_update_name,
121 P_worker_id,
122 P_num_workers,
123 P_commit_size,
124 0);
125 l_stmt_num := 30;
126 ad_parallel_updates_pkg.get_rowid_range( l_start_rowid,
127 l_end_rowid,
128 l_any_rows_to_process,
129 P_commit_size,
130 TRUE);
131
132 WHILE (l_any_rows_to_process = TRUE)
133 LOOP
134
135 SELECT count(1) INTO l_row_count FROM OKS_HEADERS_INTERFACE
136 WHERE ROWID BETWEEN l_start_rowid AND l_end_rowid
137 AND BATCH_ID = P_batch_id
138 AND INTERFACE_STATUS = 'S'
139 AND rownum = 1;
140
141 IF l_row_count > 0 THEN
142
143 l_stmt_num := 40;
144
145 DELETE FROM OKS_SALES_CREDITS_INTERFACE
146 WHERE HEADER_INTERFACE_ID IN (SELECT HEADER_INTERFACE_ID FROM OKS_HEADERS_INTERFACE
147 WHERE ROWID BETWEEN l_start_rowid AND l_end_rowid
148 AND BATCH_ID = P_batch_id
149 AND INTERFACE_STATUS = 'S');
150 l_stmt_num := 50;
151
152 DELETE FROM OKS_NOTES_INTERFACE
153 WHERE HEADER_INTERFACE_ID IN (SELECT HEADER_INTERFACE_ID FROM OKS_HEADERS_INTERFACE
154 WHERE ROWID BETWEEN l_start_rowid AND l_end_rowid
155 AND BATCH_ID = P_batch_id
156 AND INTERFACE_STATUS = 'S')
157 OR LINE_INTERFACE_ID IN (SELECT OLI.LINE_INTERFACE_ID
158 FROM OKS_LINES_INTERFACE OLI, OKS_HEADERS_INTERFACE OHI
159 WHERE OHI.ROWID BETWEEN l_start_rowid AND l_end_rowid
160 AND OHI.BATCH_ID = P_batch_id
161 AND OHI.INTERFACE_STATUS = 'S'
162 AND OLI.HEADER_INTERFACE_ID = OHI.HEADER_INTERFACE_ID);
163 l_stmt_num := 60;
164
165 DELETE FROM OKS_COVERED_LEVELS_INTERFACE
166 WHERE LINE_INTERFACE_ID IN (SELECT OLI.LINE_INTERFACE_ID
167 FROM OKS_LINES_INTERFACE OLI, OKS_HEADERS_INTERFACE OHI
168 WHERE OHI.ROWID BETWEEN l_start_rowid AND l_end_rowid
169 AND OHI.BATCH_ID = P_batch_id
170 AND OHI.INTERFACE_STATUS = 'S'
171 AND OLI.HEADER_INTERFACE_ID = OHI.HEADER_INTERFACE_ID);
172 l_stmt_num := 70;
173
174 DELETE FROM OKS_USAGE_COUNTERS_INTERFACE
175 WHERE LINE_INTERFACE_ID IN (SELECT OLI.LINE_INTERFACE_ID
176 FROM OKS_LINES_INTERFACE OLI, OKS_HEADERS_INTERFACE OHI
177 WHERE OHI.ROWID BETWEEN l_start_rowid AND l_end_rowid
178 AND OHI.BATCH_ID = P_batch_id
179 AND OHI.INTERFACE_STATUS = 'S'
180 AND OLI.HEADER_INTERFACE_ID = OHI.HEADER_INTERFACE_ID);
181
182 l_stmt_num := 80;
183
184 DELETE FROM OKS_LINES_INTERFACE
185 WHERE HEADER_INTERFACE_ID IN (SELECT HEADER_INTERFACE_ID FROM OKS_HEADERS_INTERFACE
186 WHERE ROWID BETWEEN l_start_rowid AND l_end_rowid
187 AND BATCH_ID = P_batch_id
188 AND INTERFACE_STATUS = 'S');
189
190 l_stmt_num := 90;
191
192 DELETE FROM OKS_HEADERS_INTERFACE
193 WHERE ROWID BETWEEN l_start_rowid AND l_end_rowid
194 AND BATCH_ID = P_batch_id
195 AND INTERFACE_STATUS = 'S';
196
197 ELSE
198 l_rows_processed := 0;
199 END IF;
200
201 ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,
202 l_end_rowid);
203
204 commit;
205
206 ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
207 l_end_rowid,
208 l_any_rows_to_process,
209 P_commit_size,
210 FALSE);
211 END LOOP;
212
213 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
214 X_errbuf := ' ';
215
216 IF G_PROCEDURE_LOG THEN
217 fnd_log.string(fnd_log.level_procedure,
218 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
219 'Exit with ' ||
220 'X_errbuf = ' || X_errbuf ||','||
221 'X_retcode = ' || X_retcode);
222 END IF;
223 EXCEPTION
224 WHEN FND_API.G_EXC_ERROR THEN
225
226 FND_MSG_PUB.Count_And_Get
227 (p_encoded => FND_API.G_FALSE
228 ,p_count => l_msg_count
229 ,p_data => l_msg_data
230 );
231 X_retcode := '2';
232 X_errbuf := l_msg_data;
233
234 IF G_EXCEPTION_LOG THEN
235 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, G_MODULE_HEAD || l_routine , l_msg_data);
236 END IF;
237
238 WHEN OTHERS THEN
239 X_errbuf := 'Stmt no '||l_stmt_num||' '|| SQLCODE || substr(SQLERRM, 1, 200);
240 X_retcode := '2';
241 FND_MSG_PUB.Count_And_Get
242 (p_encoded => FND_API.G_FALSE
243 ,p_count => l_msg_count
244 ,p_data => l_msg_data
245 );
246
247 IF G_EXCEPTION_LOG THEN
248 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
249 , G_MODULE_HEAD || l_routine ||'.others_exc'
250 , 'others: ' || X_errbuf || ' ' || substr(l_msg_data, 1,250)
251 );
252 END IF;
253 END Worker_purge;
254
255 END OKS_IMPORT_PURGE_WORKER;