[Home] [Help]
PACKAGE BODY: APPS.OKS_IMPORT_CONTRACTS_WORKER
Source
1 PACKAGE BODY OKS_IMPORT_CONTRACTS_WORKER AS
2 -- $Header: OKSPKIMPWRB.pls 120.3.12010000.2 2009/03/20 11:15:32 harlaksh ship $
3 --+=======================================================================+
4 --| Copyright (c) 2003 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| OKSPKIMPWRB.pls Created By Vamshi Mutyala |
10 --| |
11 --| DESCRIPTION |
12 --| Service Contracts Import Worker Package |
13 --| Bug:8222469 CAN IMPORT ACTIVE CONTRACT WITH NO CONTRACT SUBLINES | |
14 --+========================================================================
15
16 --===================
17 -- GLOBALS
18 --===================
19
20 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKS_IMPORT_CONTRACTS_WORKER';
21
22 --========================================================================
23 -- PRIVATE CONSTANTS AND VARIABLES
24 --========================================================================
25 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'oks.plsql.import.' || G_PKG_NAME;
26 G_WORKER_REQ_ID CONSTANT NUMBER := FND_GLOBAL.conc_request_id;
27 G_MODULE_HEAD CONSTANT VARCHAR2(200) := G_MODULE_NAME || '(Req Id = '||G_WORKER_REQ_ID||').';
28 G_LOG_LEVEL CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
29 G_UNEXPECTED_LOG CONSTANT BOOLEAN := fnd_log.level_unexpected >= G_LOG_LEVEL AND
30 fnd_log.TEST(fnd_log.level_unexpected, G_MODULE_HEAD);
31 G_ERROR_LOG CONSTANT BOOLEAN := G_UNEXPECTED_LOG AND fnd_log.level_error >= G_LOG_LEVEL;
32 G_EXCEPTION_LOG CONSTANT BOOLEAN := G_ERROR_LOG AND fnd_log.level_exception >= G_LOG_LEVEL;
33 G_EVENT_LOG CONSTANT BOOLEAN := G_EXCEPTION_LOG AND fnd_log.level_event >= G_LOG_LEVEL;
34 G_PROCEDURE_LOG CONSTANT BOOLEAN := G_EVENT_LOG AND fnd_log.level_procedure >= G_LOG_LEVEL;
35 G_STMT_LOG CONSTANT BOOLEAN := G_PROCEDURE_LOG AND fnd_log.level_statement >= G_LOG_LEVEL;
36
37 --=========================
38 -- PROCEDURES AND FUNCTIONS
39 --=========================
40 --========================================================================
41 -- PROCEDURE : PreInsert_Rollup_errors PRIVATE
42 -- PARAMETERS:
43 -- COMMENT : This procedure is to roll up the validation errors to
44 -- headers staging table before the insert phase
45 --=========================================================================
46
47 PROCEDURE PreInsert_Rollup_errors
48 IS
49 l_stmt_num NUMBER := 0;
50 l_routine CONSTANT VARCHAR2(30) := 'PreInsert_Rollup_errors';
51 BEGIN
52
53 IF G_PROCEDURE_LOG THEN
54 fnd_log.string(fnd_log.level_procedure,
55 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
56 'Entering.');
57 END IF;
58
59 l_stmt_num := 10;
60
61 UPDATE OKS_INT_HEADER_STG_TEMP hst
62 SET hst.INTERFACE_STATUS = (CASE WHEN EXISTS (SELECT 'X' FROM OKS_INT_ERROR_STG_TEMP
63 WHERE hst.HEADER_INTERFACE_ROWID = HEADER_INTERFACE_ROWID)
64 THEN 'E'
65 ELSE 'S'
66 END);
67
68 IF G_PROCEDURE_LOG THEN
69 fnd_log.string(fnd_log.level_procedure,
70 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
71 'Exit.');
72 END IF;
73 EXCEPTION
74 WHEN FND_API.G_EXC_ERROR THEN
75 RAISE FND_API.G_EXC_ERROR;
76 WHEN OTHERS THEN
77 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
78 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
79 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
80 FND_MSG_PUB.Add;
81 RAISE FND_API.G_EXC_ERROR;
82 END PreInsert_Rollup_errors;
83
84 --========================================================================
85 -- PROCEDURE : Rollup_errors PRIVATE
86 -- PARAMETERS: P_mode in Validate Only, Import flag
87 -- P_batch_id in Batch Id
88 -- P_start_rowid in start rowid
89 -- P_end_rowid in end rowid
90 -- X_rows_processed OUT number of rows processed in headers interface
91 -- COMMENT : This procedure is to roll up the validation errors to corresponding
92 -- records in the interface tables and finally to the headers interface
93 --=========================================================================
94 PROCEDURE Rollup_errors ( P_mode IN VARCHAR2,
95 P_batch_id IN NUMBER,
96 P_parent_request_id IN NUMBER,
97 P_start_rowid IN rowid,
98 P_end_rowid IN rowid,
99 X_rows_processed OUT NOCOPY NUMBER)
100 IS
101 l_stmt_num NUMBER := 0;
102 l_routine CONSTANT VARCHAR2(30) := 'Rollup_errors';
103 BEGIN
104
105 IF G_PROCEDURE_LOG THEN
106 fnd_log.string(fnd_log.level_procedure,
107 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
108 'Entering with ' ||
109 'P_mode = ' || P_mode ||','||
110 'P_parent_request_id = ' || P_parent_request_id ||','||
111 'P_batch_id = ' || P_batch_id ||','||
112 'P_start_rowid = ' || P_start_rowid ||','||
113 'P_end_rowid = ' || P_end_rowid);
114 END IF;
115
116 l_stmt_num := 10;
117
118 INSERT INTO OKS_IMP_ERRORS
119 (REQUEST_ID,
120 PARENT_REQUEST_ID,
121 INTERFACE_TABLE,
122 HEADER_INTERFACE_ID,
123 INTERFACE_ID,
124 ERROR_MESSAGE)
125 SELECT OIES.CONCURRENT_REQUEST_ID,
126 P_parent_request_id,
127 OIES.INTERFACE_SOURCE_TABLE,
128 OHI.HEADER_INTERFACE_ID,
129 OIES.INTERFACE_ID,
130 OIES.ERROR_MSG
131 FROM OKS_INT_ERROR_STG_TEMP OIES, OKS_HEADERS_INTERFACE OHI
132 WHERE OIES.HEADER_INTERFACE_ROWID = OHI.ROWID;
133
134 l_stmt_num := 20;
135
136 UPDATE OKS_HEADERS_INTERFACE ohi
137 SET ohi.INTERFACE_STATUS = (CASE WHEN EXISTS (SELECT 'X' FROM OKS_INT_ERROR_STG_TEMP WHERE ohi.ROWID = HEADER_INTERFACE_ROWID)
138 THEN 'E'
139 WHEN P_mode = 'I' THEN 'S'
140 ELSE NULL
141 END),
142 ohi.PARENT_REQUEST_ID = P_parent_request_id
143 WHERE ohi.rowid between P_start_rowid and P_end_rowid
144 AND ohi.batch_id = P_batch_id
145 AND (ohi.interface_status IS NULL OR ohi.interface_status = 'R');
146
147 X_rows_processed := SQL%ROWCOUNT;
148
149 IF G_PROCEDURE_LOG THEN
150 fnd_log.string(fnd_log.level_procedure,
151 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
152 'Exit with ' ||
153 'X_rows_processed = '|| X_rows_processed);
154 END IF;
155 EXCEPTION
156 WHEN FND_API.G_EXC_ERROR THEN
157 RAISE FND_API.G_EXC_ERROR;
158 WHEN OTHERS THEN
159 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
160 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
161 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
162 FND_MSG_PUB.Add;
163 RAISE FND_API.G_EXC_ERROR;
164 END Rollup_errors;
165
166 --========================================================================
167 -- PROCEDURE : Gather_Statistics PRIVATE
168 -- PARAMETERS: P_mode in Validate Only, Import flag
169 -- P_batch_id in Batch Id
170 -- P_parent_request_id in Parent Request Id
171 -- COMMENT : This procedure is to insert records into statistics table
172 --=========================================================================
173 PROCEDURE Gather_Statistics ( P_mode IN VARCHAR2,
174 P_batch_id IN NUMBER,
175 P_parent_request_id IN NUMBER)
176 IS
177 l_stmt_num NUMBER := 0;
178 l_routine CONSTANT VARCHAR2(30) := 'Gather_Statistics';
179
180 BEGIN
181
182 IF G_PROCEDURE_LOG THEN
183 fnd_log.string(fnd_log.level_procedure,
184 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
185 'Entering with ' ||
186 'P_mode = ' || P_mode ||','||
187 'P_parent_request_id = ' || P_parent_request_id ||','||
188 'P_batch_id = ' || P_batch_id);
189 END IF;
190
191 /* The statistic type is decided by FND lookup 'OKS_IMPORT_STAT_TYPE' */
192 l_stmt_num := 10;
193
194 INSERT ALL
195 WHEN (STAT_TYPE = 1) THEN
196 INTO OKS_IMPORT_STATISTICS
197 (BATCH_ID,
198 PARENT_REQUEST_ID,
199 REQUEST_ID,
200 STATISTIC_TYPE_ID,
201 HEADERS_STAT,
202 LINES_STAT,
203 COVERED_LEVELS_STAT,
204 USAGE_COUNTERS_STAT,
205 SALES_CREDITS_STAT,
206 NOTES_STAT)
207 VALUES(
208 P_batch_id,
209 P_parent_request_id,
210 G_WORKER_REQ_ID,
211 1,
212 HEADERS_SELECTED,
213 LINES_SELECTED,
214 COVERED_LEVELS_SELECTED,
215 USAGE_COUNTERS_SELECTED,
216 SALES_CREDITS_SELECTED,
217 NOTES_SELECTED)
218 WHEN (STAT_TYPE = 2) THEN
219 INTO OKS_IMPORT_STATISTICS
220 (BATCH_ID,
221 PARENT_REQUEST_ID,
222 REQUEST_ID,
223 STATISTIC_TYPE_ID,
224 HEADERS_STAT,
225 LINES_STAT,
226 COVERED_LEVELS_STAT,
227 USAGE_COUNTERS_STAT,
228 SALES_CREDITS_STAT,
229 NOTES_STAT)
230 VALUES(
231 P_batch_id,
232 P_parent_request_id,
233 G_WORKER_REQ_ID,
234 decode(P_mode, 'I', 3, 4),
235 HEADERS_IMPORTED,
236 LINES_IMPORTED,
237 COVERED_LEVELS_IMPORTED,
238 USAGE_COUNTERS_IMPORTED,
239 SALES_CREDITS_IMPORTED,
240 NOTES_IMPORTED)
241 SELECT SUM(COUNT_Q.HEADERS_COUNT) HEADERS_SELECTED,
242 SUM(COUNT_Q.LINES_COUNT) LINES_SELECTED,
243 SUM(COUNT_Q.COVERED_LEVELS_COUNT) COVERED_LEVELS_SELECTED,
244 SUM(COUNT_Q.USAGE_COUNTERS_COUNT) USAGE_COUNTERS_SELECTED,
245 SUM(COUNT_Q.SALES_CREDITS_COUNT) SALES_CREDITS_SELECTED,
246 SUM(COUNT_Q.NOTES1_COUNT) + SUM(COUNT_Q.NOTES2_COUNT) NOTES_SELECTED,
247 SUM(decode(nvl(COUNT_Q.INTERFACE_STATUS,'S'), 'S', COUNT_Q.HEADERS_COUNT,0)) HEADERS_IMPORTED,
248 SUM(decode(nvl(COUNT_Q.INTERFACE_STATUS,'S'), 'S', COUNT_Q.LINES_COUNT,0)) LINES_IMPORTED,
249 SUM(decode(nvl(COUNT_Q.INTERFACE_STATUS,'S'), 'S', COUNT_Q.COVERED_LEVELS_COUNT,0)) COVERED_LEVELS_IMPORTED,
250 SUM(decode(nvl(COUNT_Q.INTERFACE_STATUS,'S'), 'S', COUNT_Q.USAGE_COUNTERS_COUNT,0)) USAGE_COUNTERS_IMPORTED,
251 SUM(decode(nvl(COUNT_Q.INTERFACE_STATUS,'S'), 'S', COUNT_Q.SALES_CREDITS_COUNT,0)) SALES_CREDITS_IMPORTED,
252 SUM(decode(nvl(COUNT_Q.INTERFACE_STATUS,'S'), 'S', COUNT_Q.NOTES1_COUNT,0)) +
253 SUM(decode(nvl(COUNT_Q.INTERFACE_STATUS,'S'), 'S', COUNT_Q.NOTES2_COUNT,0)) NOTES_IMPORTED,
254 FL.STAT_TYPE STAT_TYPE
255 FROM (SELECT rownum STAT_TYPE from dual connect by level <= 2) FL,
256 (SELECT distinct OHST.INTERFACE_STATUS,
257 count(distinct OHST.rowid) over (partition by OHST.INTERFACE_STATUS) HEADERS_COUNT,
258 count(distinct OLST.rowid) over (partition by OHST.INTERFACE_STATUS) LINES_COUNT,
259 count(distinct OCLST.rowid) over (partition by OHST.INTERFACE_STATUS) COVERED_LEVELS_COUNT,
260 count(distinct OUCST.rowid) over (partition by OHST.INTERFACE_STATUS) USAGE_COUNTERS_COUNT,
261 count(distinct OSCST.rowid) over (partition by OHST.INTERFACE_STATUS) SALES_CREDITS_COUNT,
262 count(distinct ONI1.rowid) over (partition by OHST.INTERFACE_STATUS) NOTES1_COUNT,
263 count(distinct ONI2.rowid) over (partition by OHST.INTERFACE_STATUS) NOTES2_COUNT
264 FROM OKS_INT_HEADER_STG_TEMP OHST,
265 OKS_INT_LINE_STG_TEMP OLST,
266 OKS_INT_SALES_CREDIT_STG_TEMP OSCST,
267 OKS_INT_COVERED_LEVEL_STG_TEMP OCLST,
268 OKS_INT_USAGE_COUNTER_STG_TEMP OUCST,
269 OKS_NOTES_INTERFACE ONI1,
270 OKS_NOTES_INTERFACE ONI2
271 WHERE OHST.HEADER_INTERFACE_ID = OLST.HEADER_INTERFACE_ID (+)
272 AND OHST.HEADER_INTERFACE_ID = OSCST.HEADER_INTERFACE_ID (+)
273 AND OLST.LINE_INTERFACE_ID = OCLST.LINE_INTERFACE_ID (+)
274 AND OLST.LINE_INTERFACE_ID = OUCST.LINE_INTERFACE_ID (+)
275 AND OHST.HEADER_INTERFACE_ID = ONI1.HEADER_INTERFACE_ID (+)
276 AND ONI1.LINE_INTERFACE_ID (+) IS NULL
277 AND OLST.LINE_INTERFACE_ID = ONI2.LINE_INTERFACE_ID (+)) COUNT_Q
278 GROUP BY FL.STAT_TYPE;
279
280 l_stmt_num := 20;
281
282 INSERT INTO OKS_IMPORT_STATISTICS
283 (BATCH_ID,
284 PARENT_REQUEST_ID,
285 REQUEST_ID,
286 STATISTIC_TYPE_ID,
287 HEADERS_STAT,
288 LINES_STAT,
289 COVERED_LEVELS_STAT,
290 USAGE_COUNTERS_STAT,
291 SALES_CREDITS_STAT,
292 NOTES_STAT)
293 SELECT P_batch_id BATCH_ID,
294 P_parent_request_id PARENT_REQUEST_ID,
295 G_WORKER_REQ_ID REQUEST_ID,
296 2 STATISTIC_TYPE_ID,
297 nvl(SUM(decode(INVALID_Q.INTERFACE_SOURCE_TABLE, 'OKS_HEADERS_INTERFACE', INVALID_Q.INVALID_COUNT, 0)), 0) HEADERS_INVALID,
298 nvl(SUM(decode(INVALID_Q.INTERFACE_SOURCE_TABLE, 'OKS_LINES_INTERFACE', INVALID_Q.INVALID_COUNT, 0)), 0) LINES_INVALID,
299 nvl(SUM(decode(INVALID_Q.INTERFACE_SOURCE_TABLE, 'OKS_COVERED_LEVELS_INTERFACE', INVALID_Q.INVALID_COUNT, 0)), 0) COVERED_LEVELS_INVALID,
300 nvl(SUM(decode(INVALID_Q.INTERFACE_SOURCE_TABLE, 'OKS_USAGE_COUNTERS_INTERFACE', INVALID_Q.INVALID_COUNT, 0)), 0) USAGE_COUNTERS_INVALID,
301 nvl(SUM(decode(INVALID_Q.INTERFACE_SOURCE_TABLE, 'OKS_SALES_CREDITS_INTERFACE', INVALID_Q.INVALID_COUNT, 0)), 0) SALES_CREDITS_INVALID,
302 nvl(SUM(decode(INVALID_Q.INTERFACE_SOURCE_TABLE, 'OKS_NOTES_INTERFACE', INVALID_Q.INVALID_COUNT, 0)), 0) NOTES_INVALID
303 FROM (SELECT distinct INTERFACE_SOURCE_TABLE,
304 count(distinct INTERFACE_ID) over (partition by INTERFACE_SOURCE_TABLE) INVALID_COUNT
305 FROM OKS_INT_ERROR_STG_TEMP) INVALID_Q;
306
307 IF G_PROCEDURE_LOG THEN
308 fnd_log.string(fnd_log.level_procedure,
309 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
310 'Exit.');
311 END IF;
312 EXCEPTION
313 WHEN FND_API.G_EXC_ERROR THEN
314 RAISE FND_API.G_EXC_ERROR;
315 WHEN OTHERS THEN
316 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
317 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
318 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
319 FND_MSG_PUB.Add;
320 RAISE FND_API.G_EXC_ERROR;
321 END Gather_Statistics;
322
323 --========================================================================
324 -- PROCEDURE : Truncate_stg_tables PRIVATE
325 -- PARAMETERS:
326 -- COMMENT : This procedure is to truncate staging tables so that next
327 -- rowid range can be processed by the same concurrent request
328 --=========================================================================
329 PROCEDURE Truncate_stg_tables
330 IS
331 l_stmt_num NUMBER := 0;
332 l_routine CONSTANT VARCHAR2(30) := 'Truncate_stg_tables';
333 BEGIN
334
335 IF G_PROCEDURE_LOG THEN
336 fnd_log.string(fnd_log.level_procedure,
337 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
338 'Entering.');
339 END IF;
340
341 l_stmt_num := 10;
342
343 /* Replace with TRUNCATE statement */
344 DELETE FROM OKS_INT_HEADER_STG_TEMP;
345 DELETE FROM OKS_INT_LINE_STG_TEMP;
346 DELETE FROM OKS_COVERED_INSTANCE_STG_TEMP;
347 DELETE FROM OKS_COVERED_ITEM_STG_TEMP;
348 DELETE FROM OKS_COVERED_PARTY_STG_TEMP;
349 DELETE FROM OKS_COVERED_ACCOUNT_STG_TEMP;
350 DELETE FROM OKS_COVERED_SITE_STG_TEMP;
351 DELETE FROM OKS_COVERED_SYSTEM_STG_TEMP;
352 DELETE FROM OKS_INT_COVERED_LEVEL_STG_TEMP;
353 DELETE FROM OKS_INT_USAGE_COUNTER_STG_TEMP;
354 DELETE FROM OKS_INT_ERROR_STG_TEMP;
355 DELETE FROM OKS_INT_SALES_CREDIT_STG_TEMP;
356
357 IF G_PROCEDURE_LOG THEN
358 fnd_log.string(fnd_log.level_procedure,
359 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
360 'Exit.');
361 END IF;
362
363 EXCEPTION
364 WHEN FND_API.G_EXC_ERROR THEN
365 RAISE FND_API.G_EXC_ERROR;
366 WHEN OTHERS THEN
367 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
368 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
369 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
370 FND_MSG_PUB.Add;
371 RAISE FND_API.G_EXC_ERROR;
372 END Truncate_stg_tables;
373
377 -- X_retcode out Return status code
374 --========================================================================
375 -- PROCEDURE : Worker_process PRIVATE
376 -- PARAMETERS: X_errbuf out Error message buffer
378 -- P_commit_size in Work unit size
379 -- P_worker_id in Worker Id
380 -- P_Num_Workers in Number of workers
381 -- P_mode in Validate Only, Import flag
382 -- P_batch_id in Batch Id
383 -- COMMENT : This procedure is the worker in AD parallel framework
384 -- to validate and import interface records
385 --=========================================================================
386 PROCEDURE Worker_process (X_errbuf OUT NOCOPY VARCHAR2,
387 X_retcode OUT NOCOPY VARCHAR2,
388 P_commit_size IN NUMBER,
389 P_worker_id IN NUMBER,
390 P_Num_Workers IN NUMBER,
391 P_mode IN VARCHAR2,
392 P_batch_id IN NUMBER,
393 P_parent_request_id IN NUMBER)
394 IS
395 l_stmt_num NUMBER := 0;
396 l_routine CONSTANT VARCHAR2(30) := 'Worker_process';
397 l_product varchar2(30) := 'OKS';
398 l_table_name varchar2(30) := 'OKS_HEADERS_INTERFACE';
399 l_update_name varchar2(30);
400 l_status varchar2(30);
401 l_industry varchar2(30);
402 l_retstatus boolean;
403 l_table_owner varchar2(30);
404 l_any_rows_to_process boolean;
405 l_start_rowid rowid;
406 l_end_rowid rowid;
407 l_rows_processed number;
408 l_msg_data VARCHAR2(2000);
409 l_msg_count NUMBER;
410 l_row_count NUMBER := 0;
411 CURSOR process(q_batch_id Number) is /*BUG:8222469*/
412 SELECT OHI.header_interface_id ,OHI.category,OLI.line_interface_id,
413 NVL(osci.header_interface_id,-1) Sales_Credits,
414 DECODE(UPPER(ohi.category),'WARRANTY',NVL(OCLI.line_interface_id,NVL2(OLI.line_interface_id,-2,-1))
415 ,'SERVICE',DECODE(OLI.line_interface_id, NULL,-1
416 ,DECODE (UPPER(OLI.LINE_TYPE),'USAGE',NVL(OUCI.LINE_INTERFACE_ID,-3)
417 ,NVL(OCLI.LINE_INTERFACE_ID,-2)))
418 ,'SUBSCRIPTION',NVL(OLI.line_interface_id,-1) ) line_flow
419 FROM OKS_HEADERS_INTERFACE OHI
420 ,OKS_LINES_INTERFACE OLI
421 ,OKS_COVERED_LEVELS_INTERFACE OCLI
422 ,OKS_USAGE_COUNTERS_INTERFACE OUCI
423 ,OKS_SALES_CREDITS_INTERFACE OSCI
424 WHERE OHI.batch_id = q_batch_id
425 AND OLI.HEADER_INTERFACE_ID(+) = OHI.HEADER_INTERFACE_ID
426 AND OCLI.LINE_INTERFACE_ID(+) = OLI.line_interface_id
427 AND OUCI.LINE_INTERFACE_ID(+) = OLI.line_interface_id
428 AND OHI.HEADER_INTERFACE_ID = OSCI.HEADER_INTERFACE_ID(+);
429
430 BEGIN
431 --
432 -- Worker processing for OKS_HEADERS_INTERFACE table
433 --
434 IF G_PROCEDURE_LOG THEN
435 fnd_log.string(fnd_log.level_procedure,
436 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
437 'Entering with ' ||
438 'P_mode = ' || P_mode ||','||
439 'P_parent_request_id = ' || P_parent_request_id ||','||
440 'P_batch_id = ' || P_batch_id ||','||
441 'P_Num_Workers = ' || P_Num_Workers ||','||
442 'P_commit_size = ' || P_commit_size ||','||
443 'P_worker_id = ' || P_worker_id);
444 END IF;
445
446 l_update_name := 'Import'||P_batch_id;
447
448 l_stmt_num := 10;
449
450 --
451 -- get schema name of the table for ROWID range processing
452 --
453 l_retstatus := fnd_installation.get_app_info(l_product,
454 l_status,
455 l_industry,
456 l_table_owner);
457
458 if ((l_retstatus = FALSE) OR (l_table_owner is null)) then
459 IF G_ERROR_LOG THEN
460 fnd_log.string(fnd_log.level_error,
461 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
462 'Cannot get schema name for product : '||l_product);
463 END IF;
464 raise_application_error(-20001,'Cannot get schema name for product : '||l_product);
465 end if;
466
467 IF G_STMT_LOG THEN
468 fnd_log.string(fnd_log.level_statement,
469 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
470 'Table Owner ' || l_table_owner);
471 END IF;
472
473 l_stmt_num := 20;
474
475 ad_parallel_updates_pkg.initialize_rowid_range(ad_parallel_updates_pkg.ROWID_RANGE,
476 l_table_owner,
477 l_table_name,
478 l_update_name,
479 P_worker_id,
480 P_num_workers,
481 P_commit_size,
482 0);
483 l_stmt_num := 30;
484 ad_parallel_updates_pkg.get_rowid_range( l_start_rowid,
485 l_end_rowid,
486 l_any_rows_to_process,
487 P_commit_size,
488 TRUE);
489
490 WHILE (l_any_rows_to_process = TRUE)
491 LOOP
492 l_stmt_num := 40;
493 SELECT count(1) INTO l_row_count FROM OKS_HEADERS_INTERFACE
494 WHERE ROWID BETWEEN l_start_rowid AND l_end_rowid
495 AND BATCH_ID = P_batch_id
499 IF l_row_count > 0 THEN /*BUG:8222469*/
496 AND (INTERFACE_STATUS IS NULL OR INTERFACE_STATUS = 'R')
497 AND rownum = 1;
498
500 FOR process_rec IN process(p_batch_id)
501 LOOP
502 IF(process_rec.sales_credits = -1)THEN
503
504 UPDATE oks_headers_interface
505 SET interface_status ='E'
506 WHERE header_interface_id =process_rec.HEADER_INTERFACE_ID;
507
508 INSERT INTO OKS_IMP_ERRORS
509 (REQUEST_ID,
510 PARENT_REQUEST_ID,
511 INTERFACE_TABLE,
512 HEADER_INTERFACE_ID,
513 INTERFACE_ID,
514 ERROR_MESSAGE)
515 VALUES
516 (G_WORKER_REQ_ID,
517 P_parent_request_id,
518 'OKS_HEADERS_INTERFACE',
519 process_rec.HEADER_INTERFACE_ID,
520 process_rec.HEADER_INTERFACE_ID,
521 'OKS_IMP_HDR_SAL_CREDIT' );
522 END IF;
523 IF(process_rec.line_flow = -1)THEN
524 UPDATE oks_headers_interface
525 SET interface_status ='E'
526 WHERE header_interface_id =process_rec.HEADER_INTERFACE_ID;
527
528 INSERT INTO OKS_IMP_ERRORS
529 (REQUEST_ID,
530 PARENT_REQUEST_ID,
531 INTERFACE_TABLE,
532 HEADER_INTERFACE_ID,
533 INTERFACE_ID,
534 ERROR_MESSAGE)
535 VALUES
536 (G_WORKER_REQ_ID,
537 P_parent_request_id,
538 'OKS_HEADERS_INTERFACE',
539 process_rec.HEADER_INTERFACE_ID,
540 process_rec.HEADER_INTERFACE_ID,
541 'OKS_IMP_HDR_INVALID_LINE' );
542 END IF;
543 IF(process_rec.line_flow =-2)THEN
544 UPDATE oks_headers_interface
545 SET interface_status ='E'
546 WHERE header_interface_id =process_rec.HEADER_INTERFACE_ID;
547
548 INSERT INTO OKS_IMP_ERRORS
549 (REQUEST_ID,
550 PARENT_REQUEST_ID,
551 INTERFACE_TABLE,
552 HEADER_INTERFACE_ID,
553 INTERFACE_ID,
554 ERROR_MESSAGE)
555 VALUES
556 (G_WORKER_REQ_ID,
557 P_parent_request_id,
558 'OKS_LINES_INTERFACE',
559 process_rec.HEADER_INTERFACE_ID,
560 process_rec.LINE_INTERFACE_ID,
561 'OKS_IMP_LINE_INVALID_COVL' );
562 END IF;
563 IF(process_rec.line_flow =-3)THEN
564 UPDATE oks_headers_interface
565 SET interface_status ='E'
566 WHERE header_interface_id =process_rec.HEADER_INTERFACE_ID;
567 INSERT INTO OKS_IMP_ERRORS
568 (REQUEST_ID,
569 PARENT_REQUEST_ID,
570 INTERFACE_TABLE,
571 HEADER_INTERFACE_ID,
572 INTERFACE_ID,
573 ERROR_MESSAGE)
574 VALUES
575 (G_WORKER_REQ_ID,
576 P_parent_request_id,
577 'OKS_LINES_INTERFACE',
578 process_rec.HEADER_INTERFACE_ID,
579 process_rec.LINE_INTERFACE_ID,
580 'OKS_IMP_LINE_INVALID_USAGE' );
581 END IF;
582 END LOOP;
583 --call the validation APIs
584 l_stmt_num := 50;
585 OKS_IMPORT_VALIDATE.Validate_Contracts(P_batch_id,
586 l_start_rowid,
587 l_end_rowid);
588 l_stmt_num := 60;
589 PreInsert_Rollup_errors;
590 l_stmt_num := 70;
591 Gather_Statistics (P_mode,
592 P_batch_id,
593 P_parent_request_id );
594
595 IF P_mode = 'I' THEN
596 l_stmt_num := 80;
597 OKS_IMPORT_INSERT.Insert_Contracts;
598
599 -- Invoking Post Insert Routines
600 l_stmt_num := 85;
601 OKS_IMPORT_POST_INSERT.Import_Post_Insert;
602
603 l_stmt_num := 86;
604
605 -- Invoking Post Processing Routine
606 OKS_IMPORT_POST_PROCESS.Import_Post_Process;
607 END IF;
608 l_stmt_num := 90;
609 Rollup_errors( P_mode,
610 P_batch_id,
611 P_parent_request_id,
612 l_start_rowid,
613 l_end_rowid,
614 l_rows_processed);
615 l_stmt_num := 100;
616 Truncate_stg_tables;
617 ELSE
618 l_rows_processed := 0;
619 END IF;
620 l_stmt_num := 110;
621 ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,
622 l_end_rowid);
623
624 commit;
625 l_stmt_num := 120;
626 ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
627 l_end_rowid,
628 l_any_rows_to_process,
629 P_commit_size,
630 FALSE);
631 END LOOP;
632
633 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
634 X_errbuf := ' ';
635
636 IF G_PROCEDURE_LOG THEN
637 fnd_log.string(fnd_log.level_procedure,
638 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
639 'Exit with ' ||
640 'X_errbuf = ' || X_errbuf ||','||
641 'X_retcode = ' || X_retcode);
642 END IF;
646 FND_MSG_PUB.Count_And_Get
643 EXCEPTION
644 WHEN FND_API.G_EXC_ERROR THEN
645
647 (p_encoded => FND_API.G_FALSE
648 ,p_count => l_msg_count
649 ,p_data => l_msg_data
650 );
651 X_retcode := '2';
652 X_errbuf := l_msg_data;
653
654 IF G_EXCEPTION_LOG THEN
655 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, G_MODULE_HEAD || l_routine , l_msg_data);
656 END IF;
657
658 WHEN OTHERS THEN
659 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
660 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
661 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
662 X_errbuf := FND_MESSAGE.GET;
663 X_retcode := '2';
664 FND_MSG_PUB.Count_And_Get
665 (p_encoded => FND_API.G_FALSE
666 ,p_count => l_msg_count
667 ,p_data => l_msg_data
668 );
669
670 IF G_EXCEPTION_LOG THEN
671 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
672 , G_MODULE_HEAD || l_routine ||'.others_exc'
673 , 'others: ' || X_errbuf || ' ' || substr(l_msg_data, 1,250)
674 );
675 END IF;
676 END Worker_process;
677
678 END OKS_IMPORT_CONTRACTS_WORKER;