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.6 2007/11/27 02:36:30 anjgupta 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.
495     l_ctr := 0;
492     -- This way, when the instances get validated by the API, if serial uniqueness is violated
493     -- the record will error out.
494     --
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     IF l_srl_tbl.count > 0 THEN
504        FORALL j in l_srl_tbl.FIRST .. l_srl_tbl.LAST
505 	  UPDATE CSI_INSTANCE_INTERFACE
506 	  set parallel_worker_id = 1
507 	  where serial_number = l_srl_tbl(j)
508 	  and   source_system_name = nvl(p_source_system_name,source_system_name)
509 	  and   process_status = 'R';
510     END IF;
511     commit;
512     --
513     -- Get the count of the remaining records and divide that number by the number
514     -- of workers and assign that number to each record.
515      l_txn_from_date := to_date(p_txn_from_date, 'YYYY/MM/DD HH24:MI:SS');
516      l_txn_to_date := to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS');
517     SELECT ceil(count(1)/l_worker_count)
518     INTO x_count
519     FROM csi_instance_interface
520     WHERE trunc(source_transaction_date) BETWEEN
521                 nvl(l_txn_from_date,trunc(source_transaction_date)) AND
522                 nvl(l_txn_to_date,trunc(source_transaction_date))
523     AND transaction_identifier IS NULL
524     AND process_status = 'R'
525     AND source_system_name = nvl(p_source_system_name,source_system_name)
526     AND parallel_worker_id = -1;
527     --
528     -- After we get the number of workers and how many recs per worker
529     -- loop thru resolve IDs so that we are able to do the uniqueness checks
530 
531     FOR l_count in 1 .. l_worker_count LOOP
532 
533       UPDATE csi_instance_interface
534       SET parallel_worker_id = l_count
535       WHERE rownum <= x_count
536       AND parallel_worker_id = -1
537       AND source_system_name = nvl(p_source_system_name,source_system_name) --Added for bug 3621991
538       AND process_status = 'R'; --Added for bug 3621991
539 
540       COMMIT;
541 
542       l_inst_id_tbl.delete;
543       ii := 1;
544       FOR r_id IN c_id (l_count) LOOP -- Worker ID
545          l_inst_id_tbl(ii).inst_interface_id     := r_id.inst_interface_id;
546       ii := ii + 1;
547       END LOOP;
548 
549       l_tbl_count := 0;
550       l_tbl_count := l_inst_id_tbl.count;
551 
552       IF(l_debug_level>1) THEN
553       FND_File.Put_Line(Fnd_File.LOG,'Records In Table: '||l_tbl_count);
554       END IF;
555 
556     -- Now that the parallel_worker_id column is updated and committed
557     -- we can run the create procedure using multiple concurrent workers
558     -- We will do this in the same loop so that the process can get started.
559 
560     -- Set FND security valiables
561     END LOOP;
562 
563      IF(l_debug_level>1) THEN
564       FND_File.Put_Line(Fnd_File.LOG,'Before apps initialize: ');
565      END IF;
566 
567     OPEN candidates_exist_cur;
568     FETCH candidates_exist_cur INTO l_dummy;
569       IF candidates_exist_cur%NOTFOUND
570       THEN l_dummy := NULL;
571       END IF;
572     CLOSE candidates_exist_cur;
573 
574     IF NOT l_dummy IS NULL
575     THEN
576     FOR l_count in 1..l_worker_count LOOP
577 
578     IF(l_debug_level>1) THEN
579       FND_File.Put_Line(Fnd_File.LOG,'Before submit request: ');
580     END IF;
581        l_request_id := FND_REQUEST.SUBMIT_REQUEST
582                             ('CSI',
583                              'CSIMCPAW',
584                              'Open Interface Parallel Instance Creation Program',
585                               NULL,
586                               TRUE,
587                               p_txn_from_date,       -- Argument1
588                               p_txn_to_date,         -- Argument2
589                               p_source_system_name,  -- Argument3
590                               l_count,               -- Argument4 Worker ID
591                               p_resolve_ids);        -- Resolve IDS
592 
593      IF(l_debug_level>1) THEN
594        FND_File.Put_Line(Fnd_File.LOG,'Calling Open Interface Parallel Instance Creation Process');
595        FND_File.Put_Line(Fnd_File.LOG,'Request ID: '||l_request_id||' has been submitted');
596        FND_File.Put_Line(Fnd_File.LOG,'');
597     END IF;
598 
599        IF (l_request_id = 0) THEN
600          l_req_count:=l_req_count+1;
601          l_errbuf  := FND_MESSAGE.GET;
602         IF(l_debug_level>1) THEN
603          FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,76,150));
604          FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,151,225));
605          FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,226,300));
606         END IF;
607        END IF;
608      COMMIT;
609      END LOOP;
610 
611         IF l_req_count>0
612         THEN
613           IF(l_debug_level>1) THEN
614          fnd_file.put_line(FND_FILE.OUTPUT,'--------------------------------------------------------------------------');
615          fnd_file.put_line(FND_FILE.OUTPUT,'                    PARALLEL WORKER SUBMISSION ERROR                      ');
616          fnd_file.put_line(FND_FILE.OUTPUT,'--------------------------------------------------------------------------');
617          fnd_file.put_line(FND_FILE.OUTPUT,'One or more sub-requests/parallel workers were not submitted successfully.');
618          fnd_file.put_line(FND_FILE.OUTPUT,'--------------------------------------------------------------------------');
619          END IF;
620         END IF;
621 
622  IF(l_debug_level>1) THEN
623    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.');
624 END IF;
625         -- All the child requests were successfully requested.
629                                          request_data => req_data);
626         -- Now I'll put the parent program to sleep/paused state.
627         req_data:=to_char(l_old_error);
628         fnd_conc_global.set_req_globals (conc_status  => 'PAUSED',
630         IF(l_debug_level>1) THEN
631         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);
632         END IF;
633 		errbuf := 'sub-requests submitted';
634 		retcode := 0;
635         return;
636     ELSE
637 
638          BEGIN
639            SELECT COUNT(*)
640              INTO l_rel_count
641              FROM csi_ii_relation_interface
642             WHERE process_status = 'R'
643             AND source_system_name = nvl(p_source_system_name,source_system_name) --included for bug5949328;
644             AND parallel_worker_id = -1;
645           EXCEPTION
646             WHEN NO_DATA_FOUND THEN
647              l_rel_count :=0;
648           END;
649 
650         IF l_rel_count>0
651         THEN
652           FND_File.Put_Line(Fnd_File.LOG,'l_rel_count <> zero');
653           FND_File.Put_Line(Fnd_File.LOG,'Begin - parallel creation of relationships: '||p_worker_count);
654           FND_File.Put_Line(Fnd_File.LOG,'p_worker_count: '||p_worker_count);
655           IF (p_worker_count IS NULL OR
656               p_worker_count > 32)
657           THEN
658             l_r_worker_count := 32;
659           ELSE
660             l_r_worker_count := p_worker_count;
661           END IF;
662 
663           SELECT ceil(count(1)/l_r_worker_count)
664             INTO x_r_count
665             FROM csi_ii_relation_interface
666            WHERE trunc(source_transaction_date) BETWEEN
667                  nvl(l_txn_from_date,trunc(source_transaction_date)) AND
668                  nvl(l_txn_to_date,trunc(source_transaction_date))
669              AND transaction_identifier IS NULL
670              AND process_status = 'R'
671              AND source_system_name = nvl(p_source_system_name,source_system_name)
672              AND parallel_worker_id = -1;
673 
674           FOR l_r_count IN 1 .. l_r_worker_count LOOP
675 
676              UPDATE csi_ii_relation_interface
677                 SET parallel_worker_id = l_r_count
678               WHERE ROWNUM <= x_r_count
679                 AND parallel_worker_id = -1
680                 AND source_system_name = nvl(p_source_system_name,source_system_name)
681                 AND process_status = 'R';
682              COMMIT;
683           END LOOP;
684 
685        IF(l_debug_level>1) THEN
686           FND_File.Put_Line(Fnd_File.LOG,'Before calling csi_ml_util_pvt.resolve_rel_ids');
687        END IF;
688         -- To resolve id columns
689            csi_ml_util_pvt.resolve_rel_ids
690            (p_source_system => p_source_system_name
691            ,p_txn_from_date => p_txn_from_date
692            ,p_txn_to_date   => p_txn_to_date
693            ,x_return_status => l_return_status
694            ,x_error_message => l_error_message
695             );
696         IF(l_debug_level>1) THEN
697           FND_File.Put_Line(Fnd_File.LOG,'After calling csi_ml_util_pvt.resolve_rel_ids');
698         END IF;
699 
700            csi_ml_util_pvt.eliminate_dup_records;
701            csi_ml_util_pvt.eliminate_dup_subject;
702            csi_ml_util_pvt.check_cyclic;
703 
704         OPEN relations_exist_cur;
705         FETCH relations_exist_cur INTO l_rel_dummy;
706              IF relations_exist_cur%NOTFOUND
707              THEN
708                l_rel_dummy := NULL;
709              END IF;
710         CLOSE relations_exist_cur;
711 
712           IF NOT l_rel_dummy IS NULL
713           THEN
714            FOR l_count in 1..l_r_worker_count
715            LOOP
716             IF(l_debug_level>1) THEN
717             FND_File.Put_Line(Fnd_File.LOG,'Before submitting request for relationships: ');
718             FND_File.Put_Line(Fnd_File.LOG,'Start time in validate mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
719             END IF;
720             l_request_id := FND_REQUEST.SUBMIT_REQUEST
721                             ( 'CSI'
722                              ,'CSIMCREL'
723                              ,'Open Interface Parallel Relationship Creation Program'
724                              ,NULL
725                              ,TRUE
726                              ,'VALIDATE'
727                              ,l_count
728                              ,p_txn_from_date
729                              ,p_txn_to_date
730                              ,p_source_system_name
731                              );
732             IF(l_debug_level>1) THEN
733             FND_File.Put_Line(Fnd_File.LOG,'End time in validate mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
734             FND_File.Put_Line(Fnd_File.LOG,'Calling Open Interface Parallel Relationship Creation Process');
735             FND_File.Put_Line(Fnd_File.LOG,'Request ID: '||l_request_id||' has been submitted');
736             FND_File.Put_Line(Fnd_File.LOG,'');
737             END IF;
738 
739             IF (l_request_id = 0) THEN
740                l_req_count:=l_req_count+1;
741                l_errbuf  := FND_MESSAGE.GET;
742                IF(l_debug_level>1) THEN
743                FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,76,150));
744                FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,151,225));
745                FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,226,300));
746                END IF;
747             END IF;
748             COMMIT;
749            END LOOP;
750           END IF;
751 
752          req_data:=to_char(l_rel_count);
753          fnd_conc_global.set_req_globals (conc_status  => 'PAUSED',
757          RETURN;
754                                           request_data => req_data);
755          errbuf := 'sub-requests submitted';
756 		 retcode := 0;
758         ELSE
759 
760         IF(l_debug_level>1) THEN
761         FND_File.Put_Line(Fnd_File.LOG,'No candidate records in the interface tables: ');
762         END IF;
763         l_old_error:=0;
764         l_new_error:=0;
765         errbuf := 'Done!';
766 		retcode := 0;
767          COMMIT;
768           RETURN;
769         END IF;
770 
771     END IF;
772 
773 ELSE
774 
775  IF(l_debug_level>1) THEN
776  FND_File.Put_Line(Fnd_File.LOG,'Start time RELATIONSHIP: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
777  END IF;
778    -- Added for relationship interface
779      BEGIN
780        SELECT COUNT(*)
781          INTO l_rel_count
782          FROM csi_ii_relation_interface
783         WHERE process_status = 'R'
784         AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
785         -- AND parallel_worker_id = -1;
786      EXCEPTION
787        WHEN NO_DATA_FOUND THEN
788         l_rel_count :=0;
789      END;
790       IF(l_debug_level>1) THEN
791       FND_File.Put_Line(Fnd_File.LOG,'Value of l_rel_count is: '||l_rel_count);
792       END IF;
793      BEGIN
794        SELECT COUNT(*)
795          INTO l_valid_count
796          FROM csi_ii_relation_interface
797         WHERE process_status = 'V'
798          AND source_system_name = nvl(p_source_system_name,source_system_name);	--included for bug5949328
799          --AND parallel_worker_id = -1;
800 
801      EXCEPTION
802        WHEN NO_DATA_FOUND THEN
803         l_valid_count :=0;
804      END;
805       IF(l_debug_level>1) THEN
806       FND_File.Put_Line(Fnd_File.LOG,'Value of l_valid_count is: '||l_valid_count);
807       END IF;
808      BEGIN
809        SELECT COUNT(*)
810          INTO l_update_count
811          FROM csi_ii_relation_interface
812         WHERE process_status = 'U'
813         AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
814        --   AND parallel_worker_id = -1;
815 
816      EXCEPTION
817        WHEN NO_DATA_FOUND THEN
818         l_update_count :=0;
819      END;
820 
821       IF(l_debug_level>1) THEN
822       FND_File.Put_Line(Fnd_File.LOG,'Value of l_valid_count is: '||l_valid_count);
823       END IF;
824 
825      BEGIN
826        SELECT COUNT(*)
827          INTO l_insert_count
828          FROM csi_ii_relation_interface
829         WHERE process_status = 'I'
830          AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
831           --AND parallel_worker_id = -1;
832 
833      EXCEPTION
834        WHEN NO_DATA_FOUND THEN
835         l_insert_count :=0;
836      END;
837       IF(l_debug_level>1) THEN
838       FND_File.Put_Line(Fnd_File.LOG,'Value of l_insert_count is: '||l_insert_count);
839       END IF;
840  -- End addition for relationship interface
841  IF (   l_rel_count    = 0
842     AND l_valid_count  = 0
843     AND l_update_count = 0
844     AND l_insert_count = 0 )
845  THEN
846     IF(l_debug_level>1) THEN
847     FND_File.Put_Line(Fnd_File.LOG,'Values for l_rel_count l_valid_count l_update_count and l_insert_count are zero');
848     END IF;
849 
850     -- Parent program wakes up after successful completion of child programs.
851     l_old_error:=to_number(req_data);
852     BEGIN
853       SELECT COUNT(*)
854       INTO   l_new_error
855       FROM   csi_instance_interface
856       WHERE  process_status='E'
857        AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
858 
859     EXCEPTION
860      WHEN OTHERS THEN
861          l_new_error:=0;
862     END;
863 
864 
865 
866 
867     IF l_new_error>l_old_error
868     THEN
869     -- I found that there were some new errors for the current run.
870     --FND_File.Put_Line(Fnd_File.LOG,'Total error records in interface table before submission of program are :'||l_old_error);
871     --FND_File.Put_Line(Fnd_File.LOG,'Total error records in interface table after submission of program are  :'||l_new_error);
872     IF(l_debug_level>1) THEN
873     fnd_file.put_line(FND_FILE.OUTPUT,' ');
874     fnd_file.put_line(FND_FILE.OUTPUT,' ');
875     fnd_file.put_line(FND_FILE.OUTPUT,' ');
876     fnd_file.put_line(FND_FILE.OUTPUT,'-----------------------------------------------------------------------------------');
877     fnd_file.put_line(FND_FILE.OUTPUT,'                  OPEN INTERFACE - PARALLEL WORKER ERROR RECORDS                   ');
878     fnd_file.put_line(FND_FILE.OUTPUT,'-----------------------------------------------------------------------------------');
879     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');
880     fnd_file.put_line(FND_FILE.OUTPUT,' for detailed error message.');
881     fnd_file.put_line(FND_FILE.OUTPUT,'-----------------------------------------------------------------------------------');
882     END IF;
883       l_old_error:=0;
884       l_new_error:=0;
885     ELSE
886       l_old_error:=0;
887       l_new_error:=0;
888     END IF;
889 
890      errbuf := 'Done!';
891      retcode := 0;
892 
893        COMMIT;
894         RETURN;
895  ELSE
896 -- ******************************************************************
897 -- Start relationship creation
898    IF l_rel_count <> 0
899    THEN
900      IF(l_debug_level>1) THEN
901      FND_File.Put_Line(Fnd_File.LOG,'l_rel_count <> zero');
905 
902      FND_File.Put_Line(Fnd_File.LOG,'Begin - parallel creation of relationships: '||p_worker_count);
903      FND_File.Put_Line(Fnd_File.LOG,'p_worker_count: '||p_worker_count);
904      END IF;
906        IF (p_worker_count IS NULL OR
907            p_worker_count > 32)
908        THEN
909          l_r_worker_count := 32;
910        ELSE
911          l_r_worker_count := p_worker_count;
912        END IF;
913 
914       FND_File.Put_Line(Fnd_File.LOG,'source' || p_source_system_name);
915 
916       SELECT ceil(count(1)/l_r_worker_count)
917         INTO x_r_count
918         FROM csi_ii_relation_interface
919        WHERE trunc(source_transaction_date) BETWEEN
920              nvl(l_txn_from_date,trunc(source_transaction_date)) AND
921              nvl(l_txn_to_date,trunc(source_transaction_date))
922         AND transaction_identifier IS NULL
923         AND process_status = 'R'
924         AND source_system_name = nvl(p_source_system_name,source_system_name)
925         AND parallel_worker_id = -1;
926 
927       FND_File.Put_Line(Fnd_File.LOG,'xrcount' || x_r_count);
928       FND_File.Put_Line(Fnd_File.LOG,'wkcount' || l_r_worker_count);
929 
930 
931       FOR l_r_count in 1 .. l_r_worker_count
932       LOOP
933 
934         FND_File.Put_Line(Fnd_File.LOG,'updating CIRI');
935         UPDATE csi_ii_relation_interface
936            SET parallel_worker_id = l_r_count
937          WHERE ROWNUM <= x_r_count
938            AND parallel_worker_id = -1
939            AND source_system_name = nvl(p_source_system_name,source_system_name)
940            AND process_status = 'R';
941         COMMIT;
942       END LOOP;
943 
944     IF(l_debug_level>1) THEN
945      FND_File.Put_Line(Fnd_File.LOG,'Before calling csi_ml_util_pvt.resolve_rel_ids');
946     END IF;
947     -- To resolve id columns
948            csi_ml_util_pvt.resolve_rel_ids
949            (p_source_system => p_source_system_name
950            ,p_txn_from_date => p_txn_from_date
951            ,p_txn_to_date   => p_txn_to_date
952            ,x_return_status => l_return_status
953            ,x_error_message => l_error_message
954             );
955      IF(l_debug_level>1) THEN
956      FND_File.Put_Line(Fnd_File.LOG,'After calling csi_ml_util_pvt.resolve_rel_ids');
957      END IF;
958 
959      csi_ml_util_pvt.eliminate_dup_records;
960      csi_ml_util_pvt.eliminate_dup_subject;
961      csi_ml_util_pvt.check_cyclic;
962 
963       OPEN relations_exist_cur;
964       FETCH relations_exist_cur INTO l_rel_dummy;
965         IF relations_exist_cur%NOTFOUND
966         THEN l_rel_dummy := NULL;
967         END IF;
968       CLOSE relations_exist_cur;
969 
970       IF NOT l_rel_dummy IS NULL
971       THEN
972        FOR l_count in 1..l_r_worker_count
973        LOOP
974        IF(l_debug_level>1) THEN
975         FND_File.Put_Line(Fnd_File.LOG,'Before submitting request for relationships: ');
976         FND_File.Put_Line(Fnd_File.LOG,'Start time in validate mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
977        END IF;
978         l_request_id := FND_REQUEST.SUBMIT_REQUEST
979                         ( 'CSI'
980                          ,'CSIMCREL'
981                          ,'Open Interface Parallel Relationship Creation Program'
982                          ,NULL
983                          ,TRUE
984                          ,'VALIDATE'
985                          ,l_count
986                          ,p_txn_from_date
987                          ,p_txn_to_date
988                          ,p_source_system_name
989                          );
990         IF(l_debug_level>1) THEN
991         FND_File.Put_Line(Fnd_File.LOG,'End time in validate mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
992         FND_File.Put_Line(Fnd_File.LOG,'Calling Open Interface Parallel Relationship Creation Process');
993         FND_File.Put_Line(Fnd_File.LOG,'Request ID: '||l_request_id||' has been submitted');
994         FND_File.Put_Line(Fnd_File.LOG,'');
995         END IF;
996 
997          IF (l_request_id = 0) THEN
998              l_req_count:=l_req_count+1;
999              l_errbuf  := FND_MESSAGE.GET;
1000              IF(l_debug_level>1) THEN
1001              FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,76,150));
1002              FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,151,225));
1003              FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,226,300));
1004              END IF;
1005          END IF;
1006           COMMIT;
1007        END LOOP;
1008       END IF;
1009 
1010         req_data:=to_char(l_rel_count);
1011         fnd_conc_global.set_req_globals (conc_status  => 'PAUSED',
1012                                          request_data => req_data);
1013         errbuf := 'sub-requests submitted';
1014 		retcode := 0;
1015         RETURN;
1016    ELSIF l_valid_count <> 0
1017    THEN
1018        SELECT COUNT(DISTINCT(parallel_worker_id))
1019          INTO l_r_worker_count
1020          FROM csi_ii_relation_interface
1021         WHERE process_status='V'
1022          AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328
1023 
1024       FOR l_count IN 1..l_r_worker_count
1025       LOOP
1026        IF(l_debug_level>1) THEN
1027         FND_File.Put_Line(Fnd_File.LOG,'Before submit request for l_valid_count <> 0: ');
1028         FND_File.Put_Line(Fnd_File.LOG,'Start time in update mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
1029        END IF;
1030           l_request_id := FND_REQUEST.SUBMIT_REQUEST
1031                             ( 'CSI'
1032                              ,'CSIMCREL'
1033                              ,'Open Interface Parallel Relationship Creation Program'
1034                              ,NULL
1035                              ,TRUE
1039                              ,p_txn_to_date
1036                              ,'UPDATE'
1037                              ,l_count
1038                              ,p_txn_from_date
1040                              ,p_source_system_name
1041                              );
1042         IF(l_debug_level>1) THEN
1043         FND_File.Put_Line(Fnd_File.LOG,'End time in update mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
1044         FND_File.Put_Line(Fnd_File.LOG,'Calling Open Interface Parallel Relationship Creation Process');
1045         FND_File.Put_Line(Fnd_File.LOG,'Request ID: '||l_request_id||' has been submitted');
1046         FND_File.Put_Line(Fnd_File.LOG,'');
1047         END IF;
1048 
1049         IF (l_request_id = 0) THEN
1050             l_req_count:=l_req_count+1;
1051             l_errbuf  := FND_MESSAGE.GET;
1052             IF(l_debug_level>1) THEN
1053             FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,76,150));
1054             FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,151,225));
1055             FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,226,300));
1056             END IF;
1057         END IF;
1058        COMMIT;
1059       END LOOP;
1060          req_data:=to_char(l_valid_count);
1061          fnd_conc_global.set_req_globals (conc_status  => 'PAUSED',
1062                                           request_data => req_data);
1063          errbuf := 'sub-requests submitted';
1064 		 retcode := 0;
1065          RETURN;
1066 
1067    ELSIF l_update_count <> 0
1068    THEN
1069        SELECT COUNT(DISTINCT(parallel_worker_id))
1070          INTO l_r_worker_count
1071          FROM csi_ii_relation_interface
1072         WHERE process_status='U'
1073          AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328;
1074 
1075 
1076       FOR l_count IN 1..l_r_worker_count
1077       LOOP
1078       IF(l_debug_level>1) THEN
1079         FND_File.Put_Line(Fnd_File.LOG,'Before submit request for l_update_count <> 0: ');
1080         FND_File.Put_Line(Fnd_File.LOG,'Start time in update mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
1081       END IF;
1082          l_request_id := FND_REQUEST.SUBMIT_REQUEST
1083                          ( 'CSI'
1084                           ,'CSIMCREL'
1085                           ,'Open Interface Parallel Relationship Creation Program'
1086                           ,NULL
1087                           ,TRUE
1088                           ,'RE-UPDATE'
1089                           ,l_count
1090                           ,p_txn_from_date
1091                           ,p_txn_to_date
1092                           ,p_source_system_name
1093                           );
1094         IF(l_debug_level>1) THEN
1095         FND_File.Put_Line(Fnd_File.LOG,'End time in update mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
1096         FND_File.Put_Line(Fnd_File.LOG,'Calling Open Interface Parallel Relationship Creation Process');
1097         FND_File.Put_Line(Fnd_File.LOG,'Request ID: '||l_request_id||' has been submitted');
1098         FND_File.Put_Line(Fnd_File.LOG,'');
1099         END IF;
1100 
1101          IF (l_request_id = 0) THEN
1102            l_req_count:=l_req_count+1;
1103            l_errbuf  := FND_MESSAGE.GET;
1104            IF(l_debug_level>1) THEN
1105            FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,76,150));
1106            FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,151,225));
1107            FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,226,300));
1108            END IF;
1109          END IF;
1110         COMMIT;
1111       END LOOP;
1112         req_data:=to_char(l_update_count);
1113         fnd_conc_global.set_req_globals (conc_status  => 'PAUSED',
1114                                          request_data => req_data);
1115         errbuf := 'sub-requests submitted';
1116 		retcode := 0;
1117         RETURN;
1118 
1119    ELSIF l_insert_count <> 0
1120    THEN
1121         SELECT COUNT(DISTINCT(parallel_worker_id))
1122           INTO l_r_worker_count
1123           FROM csi_ii_relation_interface
1124          WHERE process_status='I'
1125          AND source_system_name = nvl(p_source_system_name,source_system_name); --included for bug5949328;
1126 
1127 
1128       FOR l_count in 1..l_r_worker_count
1129       LOOP
1130       IF(l_debug_level>1) THEN
1131         FND_File.Put_Line(Fnd_File.LOG,'Before submit request for l_insert_count <> 0: ');
1132         FND_File.Put_Line(Fnd_File.LOG,'Start time in insert mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
1133       END IF;
1134          l_request_id := FND_REQUEST.SUBMIT_REQUEST
1135                            ( 'CSI'
1136                             ,'CSIMCREL'
1137                             ,'Open Interface Parallel Relationship Creation Program'
1138                             ,NULL
1139                             ,TRUE
1140                             ,'INSERT'
1141                             ,l_count
1142                             ,p_txn_from_date
1143                             ,p_txn_to_date
1144                             ,p_source_system_name
1145                             );
1146         IF(l_debug_level>1) THEN
1147         FND_File.Put_Line(Fnd_File.LOG,'End time in insert mode: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
1148         FND_File.Put_Line(Fnd_File.LOG,'Calling Open Interface Parallel Relationship Creation Process');
1149         FND_File.Put_Line(Fnd_File.LOG,'Request ID: '||l_request_id||' has been submitted');
1150         FND_File.Put_Line(Fnd_File.LOG,'');
1151         END IF;
1152 
1153          IF (l_request_id = 0) THEN
1154            l_req_count:=l_req_count+1;
1155            l_errbuf  := FND_MESSAGE.GET;
1156            IF(l_debug_level>1) THEN
1157            FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,76,150));
1158            FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,151,225));
1159            FND_File.Put_Line(Fnd_File.LOG,' :'||substr(l_errbuf,226,300));
1160            END IF;
1161          END IF;
1162        COMMIT;
1163       END LOOP;
1164         req_data:=to_char(l_insert_count);
1165         fnd_conc_global.set_req_globals (conc_status  => 'PAUSED',
1166                                          request_data => req_data);
1167         errbuf := 'sub-requests submitted';
1168 		retcode := 0;
1169         RETURN;
1170   END IF; -- l_rel_count <> 0
1171 
1172  END IF;
1173 -- End relationship creation
1174 -- ******************************************************************
1175      errbuf := 'Done!';
1176      retcode := 0;
1177      COMMIT;
1178      RETURN;
1179 
1180 
1181 END IF;
1182 
1183 
1184     EXCEPTION
1185 
1186      WHEN others THEN
1187      FND_File.Put_Line(Fnd_File.LOG,'csi_ml_program_pub.execute_parallel_create -Into when others exception ' || SQLERRM);
1188        l_sql_error := SQLERRM;
1189        fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
1190        fnd_message.set_token('API_NAME',l_api_name);
1191        fnd_message.set_token('SQL_ERROR',SQLERRM);
1192 
1193 
1194 END execute_parallel_create;
1195 
1196 END CSI_ML_PROGRAM_PUB;