eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CSI_ML_PROGRAM_PUB

Source


1 PACKAGE BODY CSI_ML_PROGRAM_PUB AS
2 -- $Header: csimconb.pls 120.8 2011/11/22 08:16:09 jputta ship $
3 
4 PROCEDURE asset_vld_preprocessor
5   (
6       p_source_system_name     IN VARCHAR2,
7       x_error_message          OUT NOCOPY VARCHAR2,
8       x_return_status          OUT NOCOPY VARCHAR2
9 
10   ) IS
11 
12    TYPE NUM_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
13    l_inst_intr_tbl           NUM_TBL;
14    l_sync_sts                NUMBER;
15    l_ctr                     NUMBER := 0 ;
16    l_error_message           VARCHAR2(2000);
17    l_return_status           VARCHAR2(1);
18    l_syncup_family_seq       NUMBER := 0;
19    l_syncup_vld_flag         VARCHAR2(1) := 'Y';
20    l_process_status          VARCHAR2(1) := 'R';
21    l_Asset_Error_text        VARCHAR2(2000);
22    l_debug_level             NUMBER      := to_number(nvl(fnd_profile.value('CSI_DEBUG_LEVEL'), '0'));
23 
24   CURSOR syncup_instance_cur IS
25     SELECT csii.inst_interface_id,
26            csii.instance_id,
27            csii.quantity ,
28            csii.location_id ,
29            csii.location_type_code
30     FROM   csi_instance_interface csii
31     WHERE  process_status = 'R'
32     AND    source_system_name = nvl(p_source_system_name,source_system_name)
33     AND    EXISTS ( SELECT 1 FROM csi_i_asset_interface csiai
34                     WHERE  csiai.inst_interface_id = csii.inst_interface_id )
35     AND    syncup_family IS NULL;
36 
37    l_syncup_instance_rec syncup_instance_cur%ROWTYPE;
38 
39    l_instance_sync_tbl          CSI_ASSET_PVT.instance_sync_tbl;
40    l_instance_asset_sync_tbl    CSI_ASSET_PVT.instance_asset_sync_tbl;
41    l_fa_asset_sync_tbl          CSI_ASSET_PVT.fa_asset_sync_tbl;
42    l_inst_interface_id          NUMBER ;
43  BEGIN
44 
45   IF csi_gen_utility_pvt.is_eib_installed = 'Y' THEN
46 
47     UPDATE csi_instance_interface
48     SET    syncup_family = NULL
49     WHERE source_system_name = nvl(p_source_system_name,source_system_name)
50     AND   syncup_family is not null
51     AND   process_status = 'R';
52     COMMIT;
53 
54      IF(l_debug_level>1) THEN
55     FND_File.Put_Line(Fnd_File.LOG,'Invoking Pre-processor');
56     END IF;
57 
58     LOOP
59       OPEN syncup_instance_cur;
60       FETCH syncup_instance_cur INTO l_syncup_instance_rec;
61       EXIT WHEN syncup_instance_cur%NOTFOUND;
62       CLOSE syncup_instance_cur;
63 
64       IF syncup_instance_cur%ISOPEN THEN
65          CLOSE syncup_instance_cur;
66       END IF;
67 
68       l_instance_sync_tbl(1).inst_interface_id :=l_syncup_instance_rec.inst_interface_id;
69       l_instance_sync_tbl(1).instance_id       :=l_syncup_instance_rec.instance_id;
70       l_instance_sync_tbl(1).instance_quantity :=l_syncup_instance_rec.quantity;
71       l_instance_sync_tbl(1).location_id       :=l_syncup_instance_rec.location_id;
72       l_instance_sync_tbl(1).location_type_code:=l_syncup_instance_rec.location_type_code;
73 
74       l_inst_interface_id := l_syncup_instance_rec.inst_interface_id;
75       csi_asset_pvt.get_syncup_tree
76       (    p_source_system_name          => p_source_system_name,
77            p_called_from_grp             => fnd_api.g_true,
78            px_instance_sync_tbl          => l_instance_sync_tbl,
79            px_instance_asset_sync_tbl    => l_instance_asset_sync_tbl,
80            x_fa_asset_sync_tbl           => l_fa_asset_sync_tbl,
81            x_return_status               => l_return_status,
82            x_error_msg                   => l_error_message
83       );
84       IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
85          IF(l_debug_level>1) THEN
86          FND_File.Put_Line(Fnd_File.LOG,'Error : '||l_error_message);
87          end if;
88          RAISE fnd_api.g_exc_error;
89       END IF;
90 
91       csi_asset_pvt.asset_syncup_validation
92       (    px_instance_sync_tbl          => l_instance_sync_tbl,
93            px_instance_asset_sync_tbl    => l_instance_asset_sync_tbl,
94            px_fa_asset_sync_tbl          => l_fa_asset_sync_tbl,
95            x_return_status               => l_return_status,
96            x_error_msg                   => l_error_message
97       );
98       IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
99         IF(l_debug_level>1) THEN
100          FND_File.Put_Line(Fnd_File.LOG,'Error : '||l_error_message);
101         END IF;
102          RAISE fnd_api.g_exc_error;
103       END IF;
104 
105        l_ctr := 0;
106 
107       IF l_instance_sync_tbl.count > 0 THEN
108          FOR c_inst IN l_instance_sync_tbl.FIRST .. l_instance_sync_tbl.LAST
109          LOOP
110            IF l_instance_sync_tbl(c_inst).inst_interface_id IS NOT NULL THEN
111               l_ctr := l_ctr + 1 ;
112               l_inst_intr_tbl(l_ctr) := l_instance_sync_tbl(c_inst).inst_interface_id;
113            END IF;
114          END LOOP;
115          l_syncup_vld_flag    := l_instance_sync_tbl(1).vld_status;
116          l_syncup_family_seq  := l_syncup_family_seq +1 ;
117 
118          IF l_syncup_vld_flag  = 'E' THEN
119            FOR i_asst IN l_inst_intr_tbl.first .. l_inst_intr_tbl.last
120            LOOP
121               UPDATE csi_i_asset_interface
122               SET    fa_sync_flag     =  'N'
123               WHERE inst_interface_id =  l_inst_intr_tbl(i_asst)
124               AND   fa_sync_flag      =  'Y' ;
125 
126            END LOOP;
127          ELSE
128             l_Asset_Error_text  := NULL;
129             l_process_status    := NULL;
130          END IF;
131 
132          FORALL i_asst IN l_inst_intr_tbl.FIRST..l_inst_intr_tbl.LAST
133          UPDATE csi_instance_interface csii
134          SET syncup_family       =  l_syncup_family_seq,
135              process_status      =  NVL(l_process_status ,csii.process_status),
136              error_text          =  DECODE(l_process_status,'E',l_Asset_Error_text,
137                                            error_text)
138          WHERE inst_interface_id = l_inst_intr_tbl(i_asst);
139       COMMIT;
140       END IF;
141       l_inst_intr_tbl.delete;
142       l_instance_sync_tbl.delete;
143       l_instance_asset_sync_tbl.delete;
144       l_fa_asset_sync_tbl.delete;
145     END LOOP;
146    END IF;
147    EXCEPTION
148    WHEN fnd_api.g_exc_error THEN
149         x_return_status  := l_return_status ;
150         x_error_message  := l_error_message ;
151 END asset_vld_preprocessor;
152 
153 PROCEDURE execute_openinterface
154  (
155     errbuf                  OUT NOCOPY VARCHAR2,
156     retcode                 OUT NOCOPY NUMBER,
157     p_txn_from_date         IN     VARCHAR2,
158     p_txn_to_date           IN     VARCHAR2,
159     p_source_system_name    IN     VARCHAR2,
160     p_batch_name            IN     VARCHAR2,
161     p_resolve_ids           IN     VARCHAR2,
162     p_purge_processed_recs  IN     VARCHAR2,
163     p_reprocess_option      IN     VARCHAR2) IS
164 
165   l_return_status           VARCHAR2(1);
166   l_error_message           VARCHAR2(2000);
167   l_msg_count               NUMBER;
168   l_msg_data                VARCHAR2(2000);
169   l_msg_index               NUMBER;
170   l_sql_error               VARCHAR2(2000);
171   l_fnd_success             VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
172   l_fnd_error               VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
173   l_fnd_unexpected          VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
174   l_api_name                VARCHAR2(255) := 'CSI_ML_CREATE_PUB.EXECUTE_OPENINTERFACE';
175 
176   l_debug_level             NUMBER      := to_number(nvl(fnd_profile.value('CSI_DEBUG_LEVEL'), '0'));
177 
178 CURSOR purge_cur is
179 SELECT inst_interface_id
180   FROM csi_instance_interface
181  WHERE process_status='P'
182   AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
183 
184 
185   TYPE NumTabType IS VARRAY(10000) OF NUMBER;
186        inst_intf_id_del         NumTabType;
187        max_buffer_size          NUMBER := 9999;
188   BEGIN
189 
190    -- x_return_status := l_fnd_success;
191 
192     IF p_reprocess_option ='ALL'
193     THEN
194     UPDATE csi_instance_interface cii
195     SET process_status = 'R'
196     WHERE  (NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,cii.batch_name)
197           OR NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,'$CSI_NULL_VALUE$'))
198     AND   cii.source_system_name = p_source_system_name
199     AND   cii.process_Status = 'E';
200     COMMIT;
201     END IF;
202 
203 
204  IF NVL(p_purge_processed_recs,'Y') = 'Y'
205  THEN
206  OPEN purge_cur;
207    LOOP
208       FETCH purge_cur BULK COLLECT INTO
209       inst_intf_id_del
210       LIMIT max_buffer_size;
211 
212       FORALL i1 IN 1 .. inst_intf_id_del.COUNT
213        DELETE FROM CSI_INSTANCE_INTERFACE
214         WHERE inst_interface_id=inst_intf_id_del(i1)
215         AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
216 
217 
218       FORALL i1 IN 1 .. inst_intf_id_del.COUNT
219        DELETE FROM CSI_I_PARTY_INTERFACE cipi
220         WHERE inst_interface_id=inst_intf_id_del(i1);
221 
222  /* bnarayan Added to purge processed asset interface records */
223       FORALL i1 IN 1 .. inst_intf_id_del.COUNT
224         DELETE FROM CSI_I_ASSET_INTERFACE
225         WHERE inst_interface_id=inst_intf_id_del(i1);
226 
227 
228       FORALL i1 IN 1 .. inst_intf_id_del.COUNT
229        DELETE FROM CSI_IEA_VALUE_INTERFACE
230         WHERE inst_interface_id=inst_intf_id_del(i1);
231 
232       FORALL i1 IN 1 .. inst_intf_id_del.COUNT
233        DELETE FROM CSI_II_RELATION_INTERFACE
234         WHERE subject_interface_id=inst_intf_id_del(i1)
235         AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
236 
237       FORALL i1 IN 1 .. inst_intf_id_del.COUNT
238        DELETE FROM CSI_II_RELATION_INTERFACE
239         WHERE object_interface_id=inst_intf_id_del(i1)
240         AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
241  COMMIT;
242        EXIT WHEN purge_cur%NOTFOUND;
243    END LOOP;
244      COMMIT;
245  CLOSE purge_cur;
246  END IF;
247 
248     asset_vld_preprocessor
249     (
250       p_source_system_name => p_source_system_name
251      ,x_error_message      => l_error_message
252      ,x_return_status      => l_return_status
253     );
254     IF NOT l_return_status = l_fnd_success THEN
255       RAISE fnd_api.g_exc_error;
256     END IF;
257 
258     -- Step 1: Run the create process to create all instances in the
259     --         Interface Tables.
260 
261      IF(l_debug_level>1) THEN
262         FND_File.Put_Line(Fnd_File.LOG,'Calling Process_iface_txns: ');
263      END IF;
264     CSI_ML_interface_txn_pvt.process_iface_txns(l_error_message,
265                                        l_return_status,
266                                        p_txn_from_date,
267                                        p_txn_to_date,
268                                        p_source_system_name,
269                                        p_batch_name,
270                                        p_resolve_ids );
271 
272     IF NOT l_return_status = l_fnd_success THEN
273       RAISE fnd_api.g_exc_error;
274     END IF;
275 
276     EXCEPTION
277      WHEN fnd_api.g_exc_error THEN
278        IF(l_debug_level>1) THEN
279        FND_File.Put_Line(Fnd_File.LOG,l_error_message);
280        END IF;
281 
282 
283      WHEN others THEN
284        l_sql_error := SQLERRM;
285        fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
286        fnd_message.set_token('API_NAME',l_api_name);
287        fnd_message.set_token('SQL_ERROR',SQLERRM);
288        FND_File.Put_Line(Fnd_File.LOG,'CSI_ML_PROGRAM_PUB.execute_openinterface - Into when others exception ');
289 
290 
291 END execute_openinterface;
292 
293 PROCEDURE execute_parallel_create
294  (
295     errbuf                  OUT NOCOPY VARCHAR2,
296     retcode                 OUT NOCOPY NUMBER,
297     p_txn_from_date         IN     varchar2,
298     p_txn_to_date           IN     varchar2,
299     p_source_system_name    IN     VARCHAR2,
300     p_worker_count          IN     NUMBER,
301     p_resolve_ids           IN     VARCHAR2,
302     p_purge_processed_recs  IN     VARCHAR2) IS
303 
304   i                         PLS_INTEGER := 1;
305   l_txn_from_date           DATE;
306   l_txn_to_date             DATE;
307   l_worker_count            NUMBER;
308   l_r_worker_count          NUMBER;
309   l_count                   NUMBER := 1;
310   l_request_id              NUMBER;
311   l_errbuf                  VARCHAR2(2000);
312   x_r_count                 NUMBER := 0;
313   x_count                   NUMBER := 0;
314   l_return_status           VARCHAR2(1);
315   l_error_message           VARCHAR2(2000);
316   l_msg_count               NUMBER;
317   l_msg_data                VARCHAR2(2000);
318   l_msg_index               NUMBER;
319   l_sql_error               VARCHAR2(2000);
320   l_fnd_success             VARCHAR2(1);
321   l_fnd_error               VARCHAR2(1);
322   l_fnd_unexpected          VARCHAR2(1);
323   l_api_name                VARCHAR2(255) :=
324                               'CSI_ML_CREATE_PUB.EXECUTE_PARALLEL_CREATE';
325   l_inst_id_tbl             CSI_ML_UTIL_PVT.INST_INTERFACE_TBL_TYPE;
326   ii                        PLS_INTEGER;
327   j                         PLS_INTEGER;
328   l_tbl_count               NUMBER :=0;
329   l_dummy                   NUMBER;
330   l_debug_level             NUMBER      := to_number(nvl(fnd_profile.value('CSI_DEBUG_LEVEL'), '0'));
331 cursor c_id (pc_worker_id IN NUMBER) is
332   SELECT inst_interface_id,parallel_worker_id
333   FROM csi_instance_interface
334   WHERE parallel_worker_id = pc_worker_id
335   AND process_status = 'R'
336   AND source_system_name = nvl(p_source_system_name,source_system_name);
337 
338  CURSOR candidates_exist_cur IS
339     SELECT distinct parallel_worker_id
340     FROM csi_instance_interface
341     WHERE process_status = 'R'
342     AND source_system_name = nvl(p_source_system_name,source_system_name) --included for bug5949328
343     AND parallel_worker_id IS NOT NULL
344     AND transaction_identifier IS NULL;
345 
346  CURSOR SRL_CUR IS
347    select serial_number
348    from csi_instance_interface
349    where source_system_name = nvl(p_source_system_name,source_system_name)
350    and   serial_number is not null
351    and   process_status = 'R'
352    group by serial_number
353    having count(*) > 1;
354 
355    -- start rel_enh
356  CURSOR relations_exist_cur IS
357     SELECT distinct parallel_worker_id
358     FROM csi_ii_relation_interface
359     WHERE process_status = 'R'
360     AND source_system_name = nvl(p_source_system_name,source_system_name) --included for bug5949328
361     AND parallel_worker_id IS NOT NULL
362     AND transaction_identifier IS NULL;
363   l_rel_dummy                   NUMBER;
364 -- end rel_enh
365 
366    --
367    TYPE SRL_TBL IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
368    --
369    l_srl_tbl          SRL_TBL;
370    l_ctr              NUMBER;
371    --
372   r_id   c_id%rowtype;
373   req_data      VARCHAR2(10);
374   l_old_error   NUMBER;
375   l_new_error   NUMBER;
376   l_req_count   NUMBER :=0;
377 
378    l_rel_count    NUMBER :=0;
379   l_valid_count  NUMBER :=0;
380   l_insert_count NUMBER :=0;
381   l_update_count NUMBER :=0;
382 
383 
384 CURSOR purge_cur IS
385 SELECT inst_interface_id
386   FROM csi_instance_interface
387  WHERE process_status='P'
388  AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
389 
390   TYPE NumTabType IS VARRAY(10000) OF NUMBER;
391        inst_intf_id_del         NumTabType;
392        max_buffer_size          NUMBER := 9999;
393 BEGIN
394 
395    req_data:= fnd_conc_global.request_data;
396    IF(l_debug_level>1) THEN
397    FND_File.Put_Line(Fnd_File.LOG,'Value of req_data is :'||req_data);
398    END IF;
399 IF req_data IS NULL
400 THEN
401     -- Since req_data is null, I'll assume this is the first run.
402   BEGIN
403    SELECT COUNT(*)
404    INTO   l_old_error
405    FROM   csi_instance_interface
406    WHERE  process_status='E'
407    AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
408 
409   EXCEPTION
410    WHEN OTHERS THEN
411      l_old_error:=0;
412   END;
413 
414 
415   l_fnd_success             := FND_API.G_RET_STS_SUCCESS;
416   l_fnd_error               := FND_API.G_RET_STS_ERROR;
417   l_fnd_unexpected          := FND_API.G_RET_STS_UNEXP_ERROR;
418 
419 
420  IF NVL(p_purge_processed_recs,'Y') = 'Y'
421  THEN
422  OPEN purge_cur;
423    LOOP
424       FETCH purge_cur BULK COLLECT INTO
425       inst_intf_id_del
426       LIMIT max_buffer_size;
427 
428       FORALL i1 IN 1 .. inst_intf_id_del.COUNT
429        DELETE FROM CSI_INSTANCE_INTERFACE
430         WHERE inst_interface_id=inst_intf_id_del(i1)
431         AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328;
432 
433 
434       FORALL i1 IN 1 .. inst_intf_id_del.COUNT
435        DELETE FROM CSI_I_PARTY_INTERFACE cipi
436         WHERE inst_interface_id=inst_intf_id_del(i1);
437 
438   /* bnarayan Added to purge processed asset interface records */
439       FORALL i1 IN 1 .. inst_intf_id_del.COUNT
440         DELETE FROM CSI_I_ASSET_INTERFACE
441         WHERE inst_interface_id=inst_intf_id_del(i1);
442 
443 
444       FORALL i1 IN 1 .. inst_intf_id_del.COUNT
445        DELETE FROM CSI_IEA_VALUE_INTERFACE
446         WHERE inst_interface_id=inst_intf_id_del(i1);
447 
448       FORALL i1 IN 1 .. inst_intf_id_del.COUNT
449        DELETE FROM CSI_II_RELATION_INTERFACE
450         WHERE subject_interface_id=inst_intf_id_del(i1)
451         AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328;;
452 
453       FORALL i1 IN 1 .. inst_intf_id_del.COUNT
454        DELETE FROM CSI_II_RELATION_INTERFACE
455         WHERE object_interface_id=inst_intf_id_del(i1)
456         AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328;;
457 
458  COMMIT;
459        EXIT WHEN purge_cur%NOTFOUND;
460    END LOOP;
461      COMMIT;
462  CLOSE purge_cur;
463  END IF;
464 
465  asset_vld_preprocessor
466     (
467       p_source_system_name => p_source_system_name
468      ,x_error_message      => l_error_message
469      ,x_return_status      => l_return_status
470     );
471     IF NOT l_return_status = l_fnd_success THEN
472       RAISE fnd_api.g_exc_error;
473     END IF;
474     IF(l_debug_level>1) THEN
475       FND_File.Put_Line(Fnd_File.LOG,'Begin Execute paralle create: '||p_worker_count);
476     END IF;
477   --  x_return_status := l_fnd_success;
478 
479     -- If the worker number is not provided default to 32
480 
481      IF(l_debug_level>1) THEN
482       FND_File.Put_Line(Fnd_File.LOG,'p_worker_count: '||p_worker_count);
483     END IF;
484     IF (p_worker_count is NULL OR
485         p_worker_count > 32) THEN
486       l_worker_count := 32;
487     ELSE
488       l_worker_count := p_worker_count;
489     END IF;
490     -- srramakr In order to avoid creating multiple instances with serial number violation
491     -- we need to assign all the instances having the same serial number to worker 1.
492     -- This way, when the instances get validated by the API, if serial uniqueness is violated
493     -- the record will error out.
494     --
495     l_ctr := 0;
496     l_srl_tbl.DELETE;
497     --
498     FOR v_rec in SRL_CUR LOOP
499        l_ctr := l_ctr + 1;
500        l_srl_tbl(l_ctr) := v_rec.serial_number;
501     END LOOP;
502     --
503      l_txn_from_date := TRUNC(to_date(p_txn_from_date, 'YYYY/MM/DD HH24:MI:SS'));   --#bug13354985
504      l_txn_to_date := TRUNC(to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS'));      --#bug13354985
505 
506 
507     IF l_srl_tbl.count > 0 THEN
508        FORALL j in l_srl_tbl.FIRST .. l_srl_tbl.LAST
509 	  UPDATE CSI_INSTANCE_INTERFACE
510 	  set parallel_worker_id = 1
511 	  where serial_number = l_srl_tbl(j)
512 	  AND trunc(source_transaction_date) BETWEEN
513                 nvl(l_txn_from_date,trunc(source_transaction_date)) AND
514                 nvl(l_txn_to_date,trunc(source_transaction_date))  --#bug13354985
515 	  and   source_system_name = nvl(p_source_system_name,source_system_name)
516 	  and   process_status = 'R';
517     END IF;
518     commit;
519     --
520     -- Get the count of the remaining records and divide that number by the number
521     -- of workers and assign that number to each record.
522     SELECT ceil(count(1)/l_worker_count)
523     INTO x_count
524     FROM csi_instance_interface
525     WHERE trunc(source_transaction_date) BETWEEN
526                 nvl(l_txn_from_date,trunc(source_transaction_date)) AND
527                 nvl(l_txn_to_date,trunc(source_transaction_date))
528     AND transaction_identifier IS NULL
529     AND process_status = 'R'
530     AND source_system_name = nvl(p_source_system_name,source_system_name)
531     AND parallel_worker_id = -1;
532     --
533     -- After we get the number of workers and how many recs per worker
534     -- loop thru resolve IDs so that we are able to do the uniqueness checks
535 
536     FOR l_count in 1 .. l_worker_count LOOP
537 
538       UPDATE csi_instance_interface
539       SET parallel_worker_id = l_count
540       WHERE rownum <= x_count
541       AND trunc(source_transaction_date) BETWEEN
542                 nvl(l_txn_from_date,trunc(source_transaction_date)) AND
543                 nvl(l_txn_to_date,trunc(source_transaction_date))   --#bug13354985
544       AND parallel_worker_id = -1
545       AND source_system_name = nvl(p_source_system_name,source_system_name) --Added for bug 3621991
546       AND process_status = 'R'; --Added for bug 3621991
547 
548       COMMIT;
549 
550       l_inst_id_tbl.delete;
551       ii := 1;
552       FOR r_id IN c_id (l_count) LOOP -- Worker ID
553          l_inst_id_tbl(ii).inst_interface_id     := r_id.inst_interface_id;
554       ii := ii + 1;
555       END LOOP;
556 
557       l_tbl_count := 0;
558       l_tbl_count := l_inst_id_tbl.count;
559 
560       IF(l_debug_level>1) THEN
561       FND_File.Put_Line(Fnd_File.LOG,'Records In Table: '||l_tbl_count);
562       END IF;
563 
564     -- Now that the parallel_worker_id column is updated and committed
565     -- we can run the create procedure using multiple concurrent workers
566     -- We will do this in the same loop so that the process can get started.
567 
568     -- Set FND security valiables
569     END LOOP;
570 
571      IF(l_debug_level>1) THEN
572       FND_File.Put_Line(Fnd_File.LOG,'Before apps initialize: ');
573      END IF;
574 
575     OPEN candidates_exist_cur;
576     FETCH candidates_exist_cur INTO l_dummy;
577       IF candidates_exist_cur%NOTFOUND
578       THEN l_dummy := NULL;
579       END IF;
580     CLOSE candidates_exist_cur;
581 
582     IF NOT l_dummy IS NULL
583     THEN
584     FOR l_count in 1..l_worker_count LOOP
585 
586     IF(l_debug_level>1) THEN
587       FND_File.Put_Line(Fnd_File.LOG,'Before submit request: ');
588     END IF;
589        l_request_id := FND_REQUEST.SUBMIT_REQUEST
590                             ('CSI',
591                              'CSIMCPAW',
592                              'Open Interface Parallel Instance Creation Program',
593                               NULL,
594                               TRUE,
595                               p_txn_from_date,       -- Argument1
596                               p_txn_to_date,         -- Argument2
597                               p_source_system_name,  -- Argument3
598                               l_count,               -- Argument4 Worker ID
599                               p_resolve_ids);        -- Resolve IDS
600 
601      IF(l_debug_level>1) THEN
602        FND_File.Put_Line(Fnd_File.LOG,'Calling Open Interface Parallel Instance Creation Process');
603        FND_File.Put_Line(Fnd_File.LOG,'Request ID: '||l_request_id||' has been submitted');
604        FND_File.Put_Line(Fnd_File.LOG,'');
605     END IF;
606 
607        IF (l_request_id = 0) THEN
608          l_req_count:=l_req_count+1;
609          l_errbuf  := FND_MESSAGE.GET;
610         IF(l_debug_level>1) THEN
611          FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,76,150));
612          FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,151,225));
613          FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,226,300));
614         END IF;
615        END IF;
616      COMMIT;
617      END LOOP;
618 
619         IF l_req_count>0
620         THEN
621           IF(l_debug_level>1) THEN
622          fnd_file.put_line(FND_FILE.OUTPUT,'--------------------------------------------------------------------------');
623          fnd_file.put_line(FND_FILE.OUTPUT,'                    PARALLEL WORKER SUBMISSION ERROR                      ');
624          fnd_file.put_line(FND_FILE.OUTPUT,'--------------------------------------------------------------------------');
625          fnd_file.put_line(FND_FILE.OUTPUT,'One or more sub-requests/parallel workers were not submitted successfully.');
626          fnd_file.put_line(FND_FILE.OUTPUT,'--------------------------------------------------------------------------');
627          END IF;
628         END IF;
629 
630  IF(l_debug_level>1) THEN
631    FND_File.Put_Line(Fnd_File.LOG,'Note : Please check the request "view log" of all the child processors for any un-expected error messages.');
632 END IF;
633         -- All the child requests were successfully requested.
634         -- Now I'll put the parent program to sleep/paused state.
635         req_data:=to_char(l_old_error);
636         fnd_conc_global.set_req_globals (conc_status  => 'PAUSED',
637                                          request_data => req_data);
638         IF(l_debug_level>1) THEN
639         FND_File.Put_Line(Fnd_File.LOG,'Value of request_data that was passed to fnd_conc_global.set_req_globals is: '||FND_CONC_GLOBAL.request_data);
640         END IF;
641 		errbuf := 'sub-requests submitted';
642 		retcode := 0;
643         return;
644     ELSE
645 
646          BEGIN
647            SELECT COUNT(*)
648              INTO l_rel_count
649              FROM csi_ii_relation_interface
650             WHERE process_status = 'R'
651             AND source_system_name = nvl(p_source_system_name,source_system_name) --included for bug5949328;
652             AND parallel_worker_id = -1;
653           EXCEPTION
654             WHEN NO_DATA_FOUND THEN
655              l_rel_count :=0;
656           END;
657 
658         IF l_rel_count>0
659         THEN
660           FND_File.Put_Line(Fnd_File.LOG,'l_rel_count <> zero');
661           FND_File.Put_Line(Fnd_File.LOG,'Begin - parallel creation of relationships: '||p_worker_count);
662           FND_File.Put_Line(Fnd_File.LOG,'p_worker_count: '||p_worker_count);
663           IF (p_worker_count IS NULL OR
664               p_worker_count > 32)
665           THEN
666             l_r_worker_count := 32;
667           ELSE
668             l_r_worker_count := p_worker_count;
669           END IF;
670 
671           SELECT ceil(count(1)/l_r_worker_count)
672             INTO x_r_count
673             FROM csi_ii_relation_interface
674            WHERE trunc(source_transaction_date) BETWEEN
675                  nvl(l_txn_from_date,trunc(source_transaction_date)) AND
676                  nvl(l_txn_to_date,trunc(source_transaction_date))
677              AND transaction_identifier IS NULL
678              AND process_status = 'R'
679              AND source_system_name = nvl(p_source_system_name,source_system_name)
680              AND parallel_worker_id = -1;
681 
682           FOR l_r_count IN 1 .. l_r_worker_count LOOP
683 
684              UPDATE csi_ii_relation_interface
685                 SET parallel_worker_id = l_r_count
686               WHERE ROWNUM <= x_r_count
687                 AND parallel_worker_id = -1
688                 AND source_system_name = nvl(p_source_system_name,source_system_name)
689                 AND process_status = 'R';
690              COMMIT;
691           END LOOP;
692 
693        IF(l_debug_level>1) THEN
694           FND_File.Put_Line(Fnd_File.LOG,'Before calling csi_ml_util_pvt.resolve_rel_ids');
695        END IF;
696         -- To resolve id columns
697            csi_ml_util_pvt.resolve_rel_ids
698            (p_source_system => p_source_system_name
699            ,p_txn_from_date => p_txn_from_date
700            ,p_txn_to_date   => p_txn_to_date
701            ,x_return_status => l_return_status
702            ,x_error_message => l_error_message
703             );
704         IF(l_debug_level>1) THEN
705           FND_File.Put_Line(Fnd_File.LOG,'After calling csi_ml_util_pvt.resolve_rel_ids');
706         END IF;
707 
708            csi_ml_util_pvt.eliminate_dup_records;
709            csi_ml_util_pvt.eliminate_dup_subject;
710            csi_ml_util_pvt.check_cyclic;
711 
712         OPEN relations_exist_cur;
713         FETCH relations_exist_cur INTO l_rel_dummy;
714              IF relations_exist_cur%NOTFOUND
715              THEN
716                l_rel_dummy := NULL;
717              END IF;
718         CLOSE relations_exist_cur;
719 
720           IF NOT l_rel_dummy IS NULL
721           THEN
722            FOR l_count in 1..l_r_worker_count
723            LOOP
724             IF(l_debug_level>1) THEN
725             FND_File.Put_Line(Fnd_File.LOG,'Before submitting request for relationships: ');
726             FND_File.Put_Line(Fnd_File.LOG,'Start time in validate mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
727             END IF;
728             l_request_id := FND_REQUEST.SUBMIT_REQUEST
729                             ( 'CSI'
730                              ,'CSIMCREL'
731                              ,'Open Interface Parallel Relationship Creation Program'
732                              ,NULL
733                              ,TRUE
734                              ,'VALIDATE'
735                              ,l_count
736                              ,p_txn_from_date
737                              ,p_txn_to_date
738                              ,p_source_system_name
739                              );
740             IF(l_debug_level>1) THEN
741             FND_File.Put_Line(Fnd_File.LOG,'End time in validate mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
742             FND_File.Put_Line(Fnd_File.LOG,'Calling Open Interface Parallel Relationship Creation Process');
743             FND_File.Put_Line(Fnd_File.LOG,'Request ID: '||l_request_id||' has been submitted');
744             FND_File.Put_Line(Fnd_File.LOG,'');
745             END IF;
746 
747             IF (l_request_id = 0) THEN
748                l_req_count:=l_req_count+1;
749                l_errbuf  := FND_MESSAGE.GET;
750                IF(l_debug_level>1) THEN
751                FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,76,150));
752                FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,151,225));
753                FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,226,300));
754                END IF;
755             END IF;
756             COMMIT;
757            END LOOP;
758           END IF;
759 
760          req_data:=to_char(l_rel_count);
761          fnd_conc_global.set_req_globals (conc_status  => 'PAUSED',
762                                           request_data => req_data);
763          errbuf := 'sub-requests submitted';
764 		 retcode := 0;
765          RETURN;
766         ELSE
767 
768         IF(l_debug_level>1) THEN
769         FND_File.Put_Line(Fnd_File.LOG,'No candidate records in the interface tables: ');
770         END IF;
771         l_old_error:=0;
772         l_new_error:=0;
773         errbuf := 'Done!';
774 		retcode := 0;
775          COMMIT;
776           RETURN;
777         END IF;
778 
779     END IF;
780 
781 ELSE
782 
783  IF(l_debug_level>1) THEN
784  FND_File.Put_Line(Fnd_File.LOG,'Start time RELATIONSHIP: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
785  END IF;
786    -- Added for relationship interface
787      BEGIN
788        SELECT COUNT(*)
789          INTO l_rel_count
790          FROM csi_ii_relation_interface
791         WHERE process_status = 'R'
792         AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
793         -- AND parallel_worker_id = -1;
794      EXCEPTION
795        WHEN NO_DATA_FOUND THEN
796         l_rel_count :=0;
797      END;
798       IF(l_debug_level>1) THEN
799       FND_File.Put_Line(Fnd_File.LOG,'Value of l_rel_count is: '||l_rel_count);
800       END IF;
801      BEGIN
802        SELECT COUNT(*)
803          INTO l_valid_count
804          FROM csi_ii_relation_interface
805         WHERE process_status = 'V'
806          AND source_system_name = nvl(p_source_system_name,source_system_name);	--included for bug5949328
807          --AND parallel_worker_id = -1;
808 
809      EXCEPTION
810        WHEN NO_DATA_FOUND THEN
811         l_valid_count :=0;
812      END;
813       IF(l_debug_level>1) THEN
814       FND_File.Put_Line(Fnd_File.LOG,'Value of l_valid_count is: '||l_valid_count);
815       END IF;
816      BEGIN
817        SELECT COUNT(*)
818          INTO l_update_count
819          FROM csi_ii_relation_interface
820         WHERE process_status = 'U'
821         AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
822        --   AND parallel_worker_id = -1;
823 
824      EXCEPTION
825        WHEN NO_DATA_FOUND THEN
826         l_update_count :=0;
827      END;
828 
829       IF(l_debug_level>1) THEN
830       FND_File.Put_Line(Fnd_File.LOG,'Value of l_valid_count is: '||l_valid_count);
831       END IF;
832 
833      BEGIN
834        SELECT COUNT(*)
835          INTO l_insert_count
836          FROM csi_ii_relation_interface
837         WHERE process_status = 'I'
838          AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
839           --AND parallel_worker_id = -1;
840 
841      EXCEPTION
842        WHEN NO_DATA_FOUND THEN
843         l_insert_count :=0;
844      END;
845       IF(l_debug_level>1) THEN
846       FND_File.Put_Line(Fnd_File.LOG,'Value of l_insert_count is: '||l_insert_count);
847       END IF;
848  -- End addition for relationship interface
849  IF (   l_rel_count    = 0
850     AND l_valid_count  = 0
851     AND l_update_count = 0
852     AND l_insert_count = 0 )
853  THEN
854     IF(l_debug_level>1) THEN
855     FND_File.Put_Line(Fnd_File.LOG,'Values for l_rel_count l_valid_count l_update_count and l_insert_count are zero');
856     END IF;
857 
858     -- Parent program wakes up after successful completion of child programs.
859     l_old_error:=to_number(req_data);
860     BEGIN
861       SELECT COUNT(*)
862       INTO   l_new_error
863       FROM   csi_instance_interface
864       WHERE  process_status='E'
865        AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
866 
867     EXCEPTION
868      WHEN OTHERS THEN
869          l_new_error:=0;
870     END;
871 
872 
873 
874 
875     IF l_new_error>l_old_error
876     THEN
877     -- I found that there were some new errors for the current run.
878     --FND_File.Put_Line(Fnd_File.LOG,'Total error records in interface table before submission of program are :'||l_old_error);
879     --FND_File.Put_Line(Fnd_File.LOG,'Total error records in interface table after submission of program are  :'||l_new_error);
880     IF(l_debug_level>1) THEN
881     fnd_file.put_line(FND_FILE.OUTPUT,' ');
882     fnd_file.put_line(FND_FILE.OUTPUT,' ');
883     fnd_file.put_line(FND_FILE.OUTPUT,' ');
884     fnd_file.put_line(FND_FILE.OUTPUT,'-----------------------------------------------------------------------------------');
885     fnd_file.put_line(FND_FILE.OUTPUT,'                  OPEN INTERFACE - PARALLEL WORKER ERROR RECORDS                   ');
886     fnd_file.put_line(FND_FILE.OUTPUT,'-----------------------------------------------------------------------------------');
887     fnd_file.put_line(FND_FILE.OUTPUT,to_number(l_new_error-l_old_error)||' records were completed with error. Please check the instance interface table');
888     fnd_file.put_line(FND_FILE.OUTPUT,' for detailed error message.');
889     fnd_file.put_line(FND_FILE.OUTPUT,'-----------------------------------------------------------------------------------');
890     END IF;
891       l_old_error:=0;
892       l_new_error:=0;
893     ELSE
894       l_old_error:=0;
895       l_new_error:=0;
896     END IF;
897 
898      errbuf := 'Done!';
899      retcode := 0;
900 
901        COMMIT;
902         RETURN;
903  ELSE
904 -- ******************************************************************
905 -- Start relationship creation
906    IF l_rel_count <> 0
907    THEN
908      IF(l_debug_level>1) THEN
909      FND_File.Put_Line(Fnd_File.LOG,'l_rel_count <> zero');
910      FND_File.Put_Line(Fnd_File.LOG,'Begin - parallel creation of relationships: '||p_worker_count);
911      FND_File.Put_Line(Fnd_File.LOG,'p_worker_count: '||p_worker_count);
912      END IF;
913 
914        IF (p_worker_count IS NULL OR
915            p_worker_count > 32)
916        THEN
917          l_r_worker_count := 32;
918        ELSE
919          l_r_worker_count := p_worker_count;
920        END IF;
921 
922       FND_File.Put_Line(Fnd_File.LOG,'source' || p_source_system_name);
923 
924       SELECT ceil(count(1)/l_r_worker_count)
925         INTO x_r_count
926         FROM csi_ii_relation_interface
927        WHERE trunc(source_transaction_date) BETWEEN
928              nvl(l_txn_from_date,trunc(source_transaction_date)) AND
929              nvl(l_txn_to_date,trunc(source_transaction_date))
930         AND transaction_identifier IS NULL
931         AND process_status = 'R'
932         AND source_system_name = nvl(p_source_system_name,source_system_name)
933         AND parallel_worker_id = -1;
934 
935       FND_File.Put_Line(Fnd_File.LOG,'xrcount' || x_r_count);
936       FND_File.Put_Line(Fnd_File.LOG,'wkcount' || l_r_worker_count);
937 
938 
939       FOR l_r_count in 1 .. l_r_worker_count
940       LOOP
941 
942         FND_File.Put_Line(Fnd_File.LOG,'updating CIRI');
943         UPDATE csi_ii_relation_interface
944            SET parallel_worker_id = l_r_count
945          WHERE ROWNUM <= x_r_count
946            AND parallel_worker_id = -1
947            AND source_system_name = nvl(p_source_system_name,source_system_name)
948            AND process_status = 'R';
949         COMMIT;
950       END LOOP;
951 
952     IF(l_debug_level>1) THEN
953      FND_File.Put_Line(Fnd_File.LOG,'Before calling csi_ml_util_pvt.resolve_rel_ids');
954     END IF;
955     -- To resolve id columns
956            csi_ml_util_pvt.resolve_rel_ids
957            (p_source_system => p_source_system_name
958            ,p_txn_from_date => p_txn_from_date
959            ,p_txn_to_date   => p_txn_to_date
960            ,x_return_status => l_return_status
961            ,x_error_message => l_error_message
962             );
963      IF(l_debug_level>1) THEN
964      FND_File.Put_Line(Fnd_File.LOG,'After calling csi_ml_util_pvt.resolve_rel_ids');
965      END IF;
966 
967      csi_ml_util_pvt.eliminate_dup_records;
968      csi_ml_util_pvt.eliminate_dup_subject;
969      csi_ml_util_pvt.check_cyclic;
970 
971       OPEN relations_exist_cur;
972       FETCH relations_exist_cur INTO l_rel_dummy;
973         IF relations_exist_cur%NOTFOUND
974         THEN l_rel_dummy := NULL;
975         END IF;
976       CLOSE relations_exist_cur;
977 
978       IF NOT l_rel_dummy IS NULL
979       THEN
980        FOR l_count in 1..l_r_worker_count
981        LOOP
982        IF(l_debug_level>1) THEN
983         FND_File.Put_Line(Fnd_File.LOG,'Before submitting request for relationships: ');
984         FND_File.Put_Line(Fnd_File.LOG,'Start time in validate mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
985        END IF;
986         l_request_id := FND_REQUEST.SUBMIT_REQUEST
987                         ( 'CSI'
988                          ,'CSIMCREL'
989                          ,'Open Interface Parallel Relationship Creation Program'
990                          ,NULL
991                          ,TRUE
992                          ,'VALIDATE'
993                          ,l_count
994                          ,p_txn_from_date
995                          ,p_txn_to_date
996                          ,p_source_system_name
997                          );
998         IF(l_debug_level>1) THEN
999         FND_File.Put_Line(Fnd_File.LOG,'End time in validate mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
1000         FND_File.Put_Line(Fnd_File.LOG,'Calling Open Interface Parallel Relationship Creation Process');
1001         FND_File.Put_Line(Fnd_File.LOG,'Request ID: '||l_request_id||' has been submitted');
1002         FND_File.Put_Line(Fnd_File.LOG,'');
1003         END IF;
1004 
1005          IF (l_request_id = 0) THEN
1006              l_req_count:=l_req_count+1;
1007              l_errbuf  := FND_MESSAGE.GET;
1008              IF(l_debug_level>1) THEN
1009              FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,76,150));
1010              FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,151,225));
1011              FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,226,300));
1012              END IF;
1013          END IF;
1014           COMMIT;
1015        END LOOP;
1016       END IF;
1017 
1018         req_data:=to_char(l_rel_count);
1019         fnd_conc_global.set_req_globals (conc_status  => 'PAUSED',
1020                                          request_data => req_data);
1021         errbuf := 'sub-requests submitted';
1022 		retcode := 0;
1023         RETURN;
1024    ELSIF l_valid_count <> 0
1025    THEN
1026        SELECT COUNT(DISTINCT(parallel_worker_id))
1027          INTO l_r_worker_count
1028          FROM csi_ii_relation_interface
1029         WHERE process_status='V'
1030          AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
1031 
1032       FOR l_count IN 1..l_r_worker_count
1033       LOOP
1034        IF(l_debug_level>1) THEN
1035         FND_File.Put_Line(Fnd_File.LOG,'Before submit request for l_valid_count <> 0: ');
1036         FND_File.Put_Line(Fnd_File.LOG,'Start time in update mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
1037        END IF;
1038           l_request_id := FND_REQUEST.SUBMIT_REQUEST
1039                             ( 'CSI'
1040                              ,'CSIMCREL'
1041                              ,'Open Interface Parallel Relationship Creation Program'
1042                              ,NULL
1043                              ,TRUE
1044                              ,'UPDATE'
1045                              ,l_count
1046                              ,p_txn_from_date
1047                              ,p_txn_to_date
1048                              ,p_source_system_name
1049                              );
1050         IF(l_debug_level>1) THEN
1051         FND_File.Put_Line(Fnd_File.LOG,'End time in update mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
1052         FND_File.Put_Line(Fnd_File.LOG,'Calling Open Interface Parallel Relationship Creation Process');
1053         FND_File.Put_Line(Fnd_File.LOG,'Request ID: '||l_request_id||' has been submitted');
1054         FND_File.Put_Line(Fnd_File.LOG,'');
1055         END IF;
1056 
1057         IF (l_request_id = 0) THEN
1058             l_req_count:=l_req_count+1;
1059             l_errbuf  := FND_MESSAGE.GET;
1060             IF(l_debug_level>1) THEN
1061             FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,76,150));
1062             FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,151,225));
1063             FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,226,300));
1064             END IF;
1065         END IF;
1066        COMMIT;
1067       END LOOP;
1068          req_data:=to_char(l_valid_count);
1069          fnd_conc_global.set_req_globals (conc_status  => 'PAUSED',
1070                                           request_data => req_data);
1071          errbuf := 'sub-requests submitted';
1072 		 retcode := 0;
1073          RETURN;
1074 
1075    ELSIF l_update_count <> 0
1076    THEN
1077        SELECT COUNT(DISTINCT(parallel_worker_id))
1078          INTO l_r_worker_count
1079          FROM csi_ii_relation_interface
1080         WHERE process_status='U'
1081          AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328;
1082 
1083 
1084       FOR l_count IN 1..l_r_worker_count
1085       LOOP
1086       IF(l_debug_level>1) THEN
1087         FND_File.Put_Line(Fnd_File.LOG,'Before submit request for l_update_count <> 0: ');
1088         FND_File.Put_Line(Fnd_File.LOG,'Start time in update mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
1089       END IF;
1090          l_request_id := FND_REQUEST.SUBMIT_REQUEST
1091                          ( 'CSI'
1092                           ,'CSIMCREL'
1093                           ,'Open Interface Parallel Relationship Creation Program'
1094                           ,NULL
1095                           ,TRUE
1096                           ,'RE-UPDATE'
1097                           ,l_count
1098                           ,p_txn_from_date
1099                           ,p_txn_to_date
1100                           ,p_source_system_name
1101                           );
1102         IF(l_debug_level>1) THEN
1103         FND_File.Put_Line(Fnd_File.LOG,'End time in update mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
1104         FND_File.Put_Line(Fnd_File.LOG,'Calling Open Interface Parallel Relationship Creation Process');
1105         FND_File.Put_Line(Fnd_File.LOG,'Request ID: '||l_request_id||' has been submitted');
1106         FND_File.Put_Line(Fnd_File.LOG,'');
1107         END IF;
1108 
1109          IF (l_request_id = 0) THEN
1110            l_req_count:=l_req_count+1;
1111            l_errbuf  := FND_MESSAGE.GET;
1112            IF(l_debug_level>1) THEN
1113            FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,76,150));
1114            FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,151,225));
1115            FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,226,300));
1116            END IF;
1117          END IF;
1118         COMMIT;
1119       END LOOP;
1120         req_data:=to_char(l_update_count);
1121         fnd_conc_global.set_req_globals (conc_status  => 'PAUSED',
1122                                          request_data => req_data);
1123         errbuf := 'sub-requests submitted';
1124 		retcode := 0;
1125         RETURN;
1126 
1127    ELSIF l_insert_count <> 0
1128    THEN
1129         SELECT COUNT(DISTINCT(parallel_worker_id))
1130           INTO l_r_worker_count
1131           FROM csi_ii_relation_interface
1132          WHERE process_status='I'
1133          AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328;
1134 
1135 
1136       FOR l_count in 1..l_r_worker_count
1137       LOOP
1138       IF(l_debug_level>1) THEN
1139         FND_File.Put_Line(Fnd_File.LOG,'Before submit request for l_insert_count <> 0: ');
1140         FND_File.Put_Line(Fnd_File.LOG,'Start time in insert mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
1141       END IF;
1142          l_request_id := FND_REQUEST.SUBMIT_REQUEST
1143                            ( 'CSI'
1144                             ,'CSIMCREL'
1145                             ,'Open Interface Parallel Relationship Creation Program'
1146                             ,NULL
1147                             ,TRUE
1148                             ,'INSERT'
1149                             ,l_count
1150                             ,p_txn_from_date
1151                             ,p_txn_to_date
1152                             ,p_source_system_name
1153                             );
1154         IF(l_debug_level>1) THEN
1155         FND_File.Put_Line(Fnd_File.LOG,'End time in insert mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
1156         FND_File.Put_Line(Fnd_File.LOG,'Calling Open Interface Parallel Relationship Creation Process');
1157         FND_File.Put_Line(Fnd_File.LOG,'Request ID: '||l_request_id||' has been submitted');
1158         FND_File.Put_Line(Fnd_File.LOG,'');
1159         END IF;
1160 
1161          IF (l_request_id = 0) THEN
1162            l_req_count:=l_req_count+1;
1163            l_errbuf  := FND_MESSAGE.GET;
1164            IF(l_debug_level>1) THEN
1165            FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,76,150));
1166            FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,151,225));
1167            FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,226,300));
1168            END IF;
1169          END IF;
1170        COMMIT;
1171       END LOOP;
1172         req_data:=to_char(l_insert_count);
1173         fnd_conc_global.set_req_globals (conc_status  => 'PAUSED',
1174                                          request_data => req_data);
1175         errbuf := 'sub-requests submitted';
1176 		retcode := 0;
1177         RETURN;
1178   END IF; -- l_rel_count <> 0
1179 
1180  END IF;
1181 -- End relationship creation
1182 -- ******************************************************************
1183      errbuf := 'Done!';
1184      retcode := 0;
1185      COMMIT;
1186      RETURN;
1187 
1188 
1189 END IF;
1190 
1191 
1192     EXCEPTION
1193 
1194      WHEN others THEN
1195      FND_File.Put_Line(Fnd_File.LOG,'csi_ml_program_pub.execute_parallel_create -Into when others exception ' || SQLERRM);
1196        l_sql_error := SQLERRM;
1197        fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
1198        fnd_message.set_token('API_NAME',l_api_name);
1199        fnd_message.set_token('SQL_ERROR',SQLERRM);
1200 
1201 
1202 END execute_parallel_create;
1203 
1204 END CSI_ML_PROGRAM_PUB;