[Home] [Help]
PACKAGE BODY: APPS.CSI_ML_CREATE_PUB
Source
1 PACKAGE BODY CSI_ML_CREATE_PUB AS
2 -- $Header: csimcrpb.pls 120.4 2007/11/27 02:35:22 anjgupta ship $
3
4 PROCEDURE create_instances
5 (
6 x_msg_data OUT NOCOPY VARCHAR2,
7 x_return_status OUT NOCOPY VARCHAR2,
8 p_txn_from_date IN VARCHAR2,
9 p_txn_to_date IN VARCHAR2,
10 p_batch_name IN VARCHAR2,
11 p_source_system_name IN VARCHAR2,
12 p_resolve_ids IN VARCHAR2) IS
13
14 l_txn_tbl CSI_DATASTRUCTURES_PUB.TRANSACTION_TBL;
15 l_return_status VARCHAR2(1);
16 l_error_message VARCHAR2(2000);
17 l_instance_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_TBL;
18 l_new_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
19 l_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL;
20 l_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
21 l_ext_attrib_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
22 l_price_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
23 l_org_assign_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
24 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
25 l_grp_error_tbl CSI_DATASTRUCTURES_PUB.GRP_ERROR_TBL;
26 l_api_version NUMBER := 1.0;
27 l_commit VARCHAR2(1) := fnd_api.g_false;
28 l_init_msg_list VARCHAR2(1) := fnd_api.g_true;
29 l_validation_level NUMBER := fnd_api.g_valid_level_full;
30 l_msg_count NUMBER;
31 l_msg_data VARCHAR2(2000);
32 l_msg_index NUMBER;
33 l_sql_error VARCHAR2(2000);
34 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
35 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
36 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
37 l_api_name VARCHAR2(255) :=
38 'CSI_ML_CREATE_PUB.CREATE_INSTANCES';
39 x_count NUMBER := 0;
40 l_count NUMBER := 0;
41 l_commit_recs NUMBER := 0;
42 l_ext_tbl_count NUMBER := 0;
43
44 l_inst_id_tbl CSI_ML_UTIL_PVT.INST_INTERFACE_TBL_TYPE;
45 l_party_contact_tbl CSI_ML_UTIL_PVT.PARTY_CONTACT_TBL_TYPE;
46 i PLS_INTEGER;
47 j PLS_INTEGER;
48 d PLS_INTEGER;
49 c PLS_INTEGER;
50 l_tbl_count NUMBER :=0;
51 l_txn_from_date DATE;
52 l_txn_to_date DATE;
53 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CSI_DEBUG_LEVEL'), '0'));
54
55 CURSOR c_id is
56 SELECT a.inst_interface_id
57 FROM csi_instance_interface a
58 WHERE a.process_status = 'X';
59
60 r_id c_id%rowtype;
61 RESOLVE_ERROR EXCEPTION;
62
63 BEGIN
64
65 x_return_status := l_fnd_success;
66
67 -- Get the number of recs that should be processed before doing a
68 -- commit;
69
70 SELECT nvl(FND_PROFILE.VALUE('CSI_OPEN_INTERFACE_COMMIT_RECS'),1000)
71 INTO l_commit_recs
72 FROM dual;
73 l_txn_from_date := to_date(p_txn_from_date, 'YYYY/MM/DD HH24:MI:SS');
74 l_txn_to_date := to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS');
75 -- get the number of records and fiqure out how many loops to do
76 SELECT ceil(count(1)/l_commit_recs)
77 INTO x_count
78 FROM csi_instance_interface
79 WHERE trunc(source_transaction_date) BETWEEN
80 nvl(l_txn_from_date,trunc(source_transaction_date)) AND
81 nvl(l_txn_to_date,trunc(source_transaction_date))
82 AND process_status = 'R'
83 AND parallel_worker_id is NULL
84 AND source_system_name = nvl(p_source_system_name,source_system_name);
85
86 IF(l_debug_level>1) THEN
87 FND_File.Put_Line(Fnd_File.LOG,'Number of Loops: '||to_char(x_count));
88 end if;
89
90 FOR l_count in 1 .. x_count LOOP
91
92 -- first update the tables to have 'R' in the process status field.
93 -- Otherwise they will be null
94
95 -- Process Statuses
96 -- R = Ready Status
97 -- X = Intermediate Process Status
98 -- P = Processed No Error
99 -- E = Error
100
101 UPDATE csi_instance_interface a
102 SET process_status = 'X'
103 WHERE trunc(source_transaction_date) BETWEEN
104 nvl(l_txn_from_date,trunc(source_transaction_date)) AND
105 nvl(l_txn_to_date,trunc(source_transaction_date))
106 AND process_status = 'R'
107 AND source_system_name = nvl(p_source_system_name,source_system_name)
108 AND parallel_worker_id is NULL
109 AND rownum <= l_commit_recs;
110
111 l_inst_id_tbl.delete;
112 i := 1;
113 FOR r_id IN c_id LOOP
114 l_inst_id_tbl(i).inst_interface_id := r_id.inst_interface_id;
115 i := i + 1;
116 END LOOP;
117
118 l_tbl_count := 0;
119 l_tbl_count := l_inst_id_tbl.count;
120
121 IF nvl(p_resolve_ids,'Y') = 'Y'
122 THEN
123 -- Resolve all ID fields from Descriptive Fields
124
125 CSI_ML_UTIL_PVT.resolve_ids(p_txn_from_date,
126 p_txn_to_date,
127 p_batch_name,
128 p_source_system_name,
129 l_return_status,
130 l_error_message);
131
132 IF NOT l_return_status = l_fnd_success THEN
133 RAISE RESOLVE_ERROR; --fnd_api.g_exc_error;
134 END IF;
135 END IF;
136 -- After getting IDs now create PL/SQL Tables
137
138 SAVEPOINT create_instances;
139
140 CSI_ML_CREATE_PVT.get_iface_create_recs (p_txn_from_date,
141 p_txn_to_date,
142 p_source_system_name,
143 NULL, -- p_worker_id,
144 l_commit_recs,
145 l_instance_tbl,
146 l_party_tbl,
147 l_account_tbl,
148 l_ext_attrib_tbl,
149 l_price_tbl,
150 l_org_assign_tbl,
151 l_txn_tbl,
152 l_party_contact_tbl,
153 l_asset_assignment_tbl,
154 l_return_status,
155 l_error_message);
156
157 IF NOT l_return_status = l_fnd_success THEN
158 RAISE fnd_api.g_exc_error;
159 END IF;
160
161 -- commented the following unnecessary code
162 /*
163 IF l_party_contact_tbl.count > 0 THEN
164
165 FOR c in l_party_contact_tbl.FIRST .. l_party_contact_tbl.LAST LOOP
166 d := 1;
167 FOR d in l_party_tbl.FIRST .. l_party_tbl.LAST LOOP
168 IF l_party_contact_tbl(c).inst_interface_id = l_party_tbl(d).interface_id AND
169 l_party_contact_tbl(c).contact_party_id = l_party_tbl(d).party_id AND
170 l_party_contact_tbl(c).contact_party_rel_type = l_party_tbl(d).relationship_type_code AND
171 l_party_tbl(d).contact_flag = 'N' THEN
172 l_party_tbl(l_party_contact_tbl(c).parent_tbl_idx).contact_parent_tbl_index := d;
173 END IF;
174 END LOOP;
175 END LOOP;
176 END IF;
177 */
178 csi_item_instance_grp.create_item_instance
179 (p_api_version => l_api_version,
180 p_commit => l_commit,
181 p_init_msg_list => l_init_msg_list,
182 p_validation_level => l_validation_level,
183 p_instance_tbl => l_instance_tbl,
184 p_ext_attrib_values_tbl => l_ext_attrib_tbl,
185 p_party_tbl => l_party_tbl,
186 p_account_tbl => l_account_tbl,
187 p_pricing_attrib_tbl => l_price_tbl,
188 p_org_assignments_tbl => l_org_assign_tbl,
189 p_asset_assignment_tbl => l_asset_assignment_tbl,
190 p_txn_tbl => l_txn_tbl,
191 p_grp_error_tbl => l_grp_error_tbl,
192 x_return_status => l_return_status,
193 x_msg_count => l_msg_count,
194 x_msg_data => l_msg_data);
195
196 IF NOT l_Return_Status = l_fnd_success THEN
197 l_msg_index := 1;
198 l_Error_Message := l_Msg_Data;
199 WHILE l_msg_count > 0 LOOP
200 l_Error_Message := l_Error_Message||FND_MSG_PUB.GET(l_msg_index,
201 FND_API.G_FALSE);
202 l_msg_index := l_msg_index + 1;
203 l_Msg_Count := l_Msg_Count - 1;
204 END LOOP;
205 RAISE fnd_api.g_exc_error;
206 END IF;
207
208 l_tbl_count := 0;
209 l_tbl_count := l_instance_tbl.count;
210 l_ext_tbl_count := l_ext_attrib_tbl.count;
211 IF(l_debug_level>1) THEN
212 FND_File.Put_Line(Fnd_File.LOG,'Updating Status of Inst Children '||
213 l_tbl_count);
214 FND_File.Put_Line(Fnd_File.LOG,'Ext Attr Status Recs '||l_ext_tbl_count);
215 FND_File.Put_Line(Fnd_File.LOG,'Before Loop to set Child Status: '||
216 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
217 END IF;
218
219 j := 1;
220 IF(l_debug_level>1) THEN
221 FND_File.Put_Line(Fnd_File.LOG,'After Loop to set Child Status: '||
222 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
223 END IF;
224 COMMIT;
225 END LOOP; -- End of For Loop
226
227 -- Display errors in Concurrent Manager Log
228 CSI_ML_UTIL_PVT.log_create_errors(p_txn_from_date,
229 p_txn_to_date,
230 l_return_status,
231 l_error_message);
232
233 IF(l_debug_level>1) THEN
234 FND_File.Put_Line(Fnd_File.LOG,'After Log Errors: '||
235 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
236 END IF;
237
238 COMMIT;
239
240 EXCEPTION
241 WHEN RESOLVE_ERROR THEN
242 IF(l_debug_level>1) THEN
243 FND_File.Put_Line(Fnd_File.LOG,'Resolve IDs Errored out...');
244 FND_File.Put_Line(Fnd_File.LOG,l_error_message);
245 END IF;
246 x_return_status := l_fnd_error;
247 x_msg_data := l_error_message;
248 WHEN fnd_api.g_exc_error THEN
249 ROLLBACK to create_instances;
250 IF(l_debug_level>1) THEN
251 FND_File.Put_Line(Fnd_File.LOG,'In EXPECTED Exception in:'||l_api_name);
252 FND_File.Put_Line(Fnd_File.LOG,l_error_message);
253 END IF;
254 x_return_status := l_fnd_error;
255 x_msg_data := l_error_message;
256
257 WHEN others THEN
258 ROLLBACK to create_instances;
259 IF(l_debug_level>1) THEN
260 FND_File.Put_Line(Fnd_File.LOG,'In WHEN OTHERS Exception in:'||l_api_name);
261 END IF;
262 l_sql_error := SQLERRM;
263 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
264 fnd_message.set_token('API_NAME',l_api_name);
265 fnd_message.set_token('SQL_ERROR',SQLERRM);
266 x_msg_data := fnd_message.get;
267 FND_File.Put_Line(Fnd_File.LOG,x_msg_data);
268 x_return_status := l_fnd_unexpected;
269
270 END create_instances;
271
272 PROCEDURE create_parallel_instances
273 (
274 x_msg_data OUT NOCOPY VARCHAR2,
275 x_return_status OUT NOCOPY VARCHAR2,
276 p_txn_from_date IN VARCHAR2,
277 p_txn_to_date IN VARCHAR2,
278 p_source_system_name IN VARCHAR2,
279 p_worker_id IN NUMBER,
280 p_resolve_ids IN VARCHAR2) IS
281
282 l_txn_tbl CSI_DATASTRUCTURES_PUB.TRANSACTION_TBL;
283 l_return_status VARCHAR2(1);
284 l_error_message VARCHAR2(2000);
285 l_instance_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_TBL;
286 l_new_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
287 l_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL;
288 l_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
289 l_ext_attrib_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
290 l_price_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
291 l_org_assign_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
292 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
293 l_grp_error_tbl CSI_DATASTRUCTURES_PUB.GRP_ERROR_TBL;
294 l_api_version NUMBER := 1.0;
295 l_commit VARCHAR2(1) := fnd_api.g_false;
296 l_init_msg_list VARCHAR2(1) := fnd_api.g_true;
297 l_validation_level NUMBER := fnd_api.g_valid_level_full;
298 l_msg_count NUMBER;
299 l_msg_data VARCHAR2(2000);
300 l_msg_index NUMBER;
301 l_sql_error VARCHAR2(2000);
302 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
303 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
304 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
305 l_api_name VARCHAR2(255) :=
306 'CSI_ML_CREATE_PUB.CREATE_PARALLEL_INSTANCES';
307 x_count NUMBER := 0;
308 l_count NUMBER := 0;
309 l_commit_recs NUMBER := 0;
310 l_ext_tbl_count NUMBER := 0;
311
312 l_inst_id_tbl CSI_ML_UTIL_PVT.INST_INTERFACE_TBL_TYPE;
313 l_party_contact_tbl CSI_ML_UTIL_PVT.PARTY_CONTACT_TBL_TYPE;
314 i PLS_INTEGER;
315 j PLS_INTEGER;
316 d PLS_INTEGER;
317 c PLS_INTEGER;
318 l_tbl_count NUMBER :=0;
319 l_source_flag VARCHAR2(1);
320 l_count1 NUMBER;
321 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CSI_DEBUG_LEVEL'), '0'));
322
323 CURSOR c_id (pc_parallel_worker_id IN NUMBER) is
324 SELECT inst_interface_id,process_status
325 FROM csi_instance_interface
326 WHERE process_status = 'X'
327 AND nvl(transaction_identifier,'-1') = '-1'
328 AND parallel_worker_id = pc_parallel_worker_id;
329
330 r_id c_id%rowtype;
331 RESOLVE_ERROR EXCEPTION;
332 l_txn_from_date DATE;
333 l_txn_to_date DATE;
334
335 BEGIN
336
337 IF(l_debug_level>1) THEN
338 FND_File.Put_Line(Fnd_File.LOG,'Start Time of Pub: '||
339 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
340 END IF;
341 x_return_status := l_fnd_success;
342
343 -- Get the number of recs that should be processed before doing a
344 -- commit;
345
346 SELECT nvl(FND_PROFILE.VALUE('CSI_OPEN_INTERFACE_COMMIT_RECS'),1000)
347 INTO l_commit_recs
348 FROM dual;
349
350 FND_File.Put_Line(Fnd_File.LOG,'After Commit Profile: '||
351 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
352
353 l_txn_from_date := to_date(p_txn_from_date, 'YYYY/MM/DD HH24:MI:SS');
354 l_txn_to_date := to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS');
355
356 -- Get the number of records and fiqure out how many loops to do
357 SELECT ceil(count(1)/l_commit_recs)
358 INTO x_count
359 FROM csi_instance_interface
360 WHERE trunc(source_transaction_date) BETWEEN
361 nvl(l_txn_from_date,trunc(source_transaction_date)) AND
362 nvl(l_txn_to_date,trunc(source_transaction_date))
363 AND nvl(transaction_identifier,'-1') = '-1'
364 AND process_status = 'R'
365 AND source_system_name = nvl(p_source_system_name,source_system_name)
366 AND parallel_worker_id = p_worker_id;
367
368 IF(l_debug_level>1) THEN
369 FND_File.Put_Line(Fnd_File.LOG,'After Fiqure out Loops: '||
370 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
374 FOR l_count in 1 .. x_count LOOP
371 FND_File.Put_Line(Fnd_File.LOG,'Number of Loops: '||to_char(x_count));
372 END IF;
373
375 -- Update the tables to have 'X' in the process status field.
376 -- Otherwise they will be null
377
378 -- Process Statuses
379 -- R = Ready Status
380 -- X = Intermediate Process Status
381 -- P = Processed No Error
382 -- E = Error
383
384 UPDATE csi_instance_interface a
385 SET process_status = 'X'
386 WHERE trunc(source_transaction_date) BETWEEN
387 nvl(l_txn_from_date,trunc(source_transaction_date)) AND
388 nvl(l_txn_to_date,trunc(source_transaction_date))
389 AND nvl(transaction_identifier,'-1') = '-1'
390 AND process_status = 'R'
391 AND source_system_name = nvl(p_source_system_name,source_system_name)
392 AND parallel_worker_id = p_worker_id
393 AND rownum <= l_commit_recs;
394
395 IF(l_debug_level>1) THEN
396
397 FND_File.Put_Line(Fnd_File.LOG,'After setting INST IFACE to X: '||
398 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
399 END IF;
400
401 l_inst_id_tbl.delete;
402 i := 1;
403 FOR r_id IN c_id (p_worker_id) LOOP
404 l_inst_id_tbl(i).inst_interface_id := r_id.inst_interface_id;
405 i := i + 1;
406 END LOOP;
407
408 COMMIT;
409
410 IF(l_debug_level>1) THEN
411 FND_File.Put_Line(Fnd_File.LOG,'After ID PL/SQL Table Create: '||
412 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
413 END IF;
414
415 l_tbl_count := 0;
416 l_tbl_count := l_inst_id_tbl.count;
417 IF(l_debug_level>1) THEN
418 FND_File.Put_Line(Fnd_File.LOG,'Records Found: '||l_tbl_count);
419 FND_File.Put_Line(Fnd_File.LOG,'After Looping Child Tables: '||
420 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
421
422 -- Resolve all ID fields from Descriptive Fields
423 FND_File.Put_Line(Fnd_File.LOG,'Before Resolve IDs: '||
424 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
425 END IF;
426 IF l_tbl_count>0 -- Added
427 THEN
428 IF NVL(p_resolve_ids,'Y') = 'Y' THEN
429 CSI_ML_UTIL_PVT.resolve_pw_ids(p_txn_from_date,
430 p_txn_to_date,
431 p_source_system_name,
432 p_worker_id,
433 l_return_status,
434 l_error_message);
435 IF(l_debug_level>1) THEN
436 FND_File.Put_Line(Fnd_File.LOG,'After Resolve IDs: '||
437 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
438 END IF;
439
440 IF NOT l_return_status = l_fnd_success THEN
441 RAISE RESOLVE_ERROR; --fnd_api.g_exc_error;
442 END IF;
443 END IF;
444
445 -- After getting IDs now create PL/SQL Tables
446 IF(l_debug_level>1) THEN
447 FND_File.Put_Line(Fnd_File.LOG,'Before PL/SQL Table Create: '||
448 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
449 END IF;
450
451 SAVEPOINT create_parallel_instances;
452
453 CSI_ML_CREATE_PVT.get_iface_create_recs (p_txn_from_date,
454 p_txn_to_date,
455 p_source_system_name,
456 p_worker_id,
457 l_commit_recs,
458 l_instance_tbl,
459 l_party_tbl,
460 l_account_tbl,
461 l_ext_attrib_tbl,
462 l_price_tbl,
463 l_org_assign_tbl,
464 l_txn_tbl,
465 l_party_contact_tbl,
466 l_asset_assignment_tbl,
467 l_return_status,
468 l_error_message);
469 IF(l_debug_level>1) THEN
470 FND_File.Put_Line(Fnd_File.LOG,'After PL/SQL Table Create: '||
471 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
472 END IF;
473
474 IF NOT l_return_status = l_fnd_success THEN
475 RAISE fnd_api.g_exc_error;
476 END IF;
477
478 IF(l_debug_level>1) THEN
479 FND_File.Put_Line(Fnd_File.LOG,'Before Contact Loop: '||
480 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
481
482 -- commented the following unnecessary code
483 /*
484 IF l_party_contact_tbl.count > 0 THEN
485
486 FOR c in l_party_contact_tbl.FIRST .. l_party_contact_tbl.LAST LOOP
487 d := 1;
488 FOR d in l_party_tbl.FIRST .. l_party_tbl.LAST LOOP
489 IF l_party_contact_tbl(c).inst_interface_id = l_party_tbl(d).interface_id AND
490 l_party_contact_tbl(c).contact_party_id = l_party_tbl(d).party_id AND
491 l_party_contact_tbl(c).contact_party_rel_type = l_party_tbl(d).relationship_type_code AND
492 l_party_tbl(d).contact_flag = 'N' THEN
493 l_party_tbl(l_party_contact_tbl(c).parent_tbl_idx).contact_parent_tbl_index := d;
494 END IF;
495 END LOOP;
496 END LOOP;
500 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
497 END IF;
498 */
499 FND_File.Put_Line(Fnd_File.LOG,'After Contact Loop: '||
501
502 FND_File.Put_Line(Fnd_File.LOG,'Before GRP API: '||
503 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
504 END IF;
505 csi_item_instance_grp.create_item_instance
506 (p_api_version => l_api_version,
507 p_commit => l_commit,
508 p_init_msg_list => l_init_msg_list,
509 p_validation_level => l_validation_level,
510 p_instance_tbl => l_instance_tbl,
511 p_ext_attrib_values_tbl => l_ext_attrib_tbl,
512 p_party_tbl => l_party_tbl,
513 p_account_tbl => l_account_tbl,
514 p_pricing_attrib_tbl => l_price_tbl,
515 p_org_assignments_tbl => l_org_assign_tbl,
516 p_asset_assignment_tbl => l_asset_assignment_tbl,
517 p_txn_tbl => l_txn_tbl,
518 p_grp_error_tbl => l_grp_error_tbl,
519 x_return_status => l_return_status,
520 x_msg_count => l_msg_count,
521 x_msg_data => l_msg_data);
522
523
524
525 IF NOT l_Return_Status = l_fnd_success THEN
526 l_msg_index := 1;
527 l_Error_Message := l_Msg_Data;
528 WHILE l_msg_count > 0 LOOP
529 l_Error_Message := l_Error_Message||FND_MSG_PUB.GET(l_msg_index,
530 FND_API.G_FALSE);
531 l_msg_index := l_msg_index + 1;
532 l_Msg_Count := l_Msg_Count - 1;
533 END LOOP;
534 RAISE fnd_api.g_exc_error;
535 END IF;
536 IF(l_debug_level>1) THEN
537 FND_File.Put_Line(Fnd_File.LOG,'After GRP API: '||
538 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
539 END IF;
540 l_tbl_count := 0;
541 l_tbl_count := l_instance_tbl.count;
542 l_ext_tbl_count := l_ext_attrib_tbl.count;
543 IF(l_debug_level>1) THEN
544 FND_File.Put_Line(Fnd_File.LOG,'Updating Status of Inst Children '||
545 l_tbl_count);
546 FND_File.Put_Line(Fnd_File.LOG,'Ext Attr Status Recs '||l_ext_tbl_count);
547 FND_File.Put_Line(Fnd_File.LOG,'Before Loop to set Child Status: '||
548 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
549
550 FND_File.Put_Line(Fnd_File.LOG,'After Loop to set Child Status: '||
551 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
552 END IF;
553 END IF; -- End addition
554 COMMIT;
555 END LOOP; -- End of For Loop
556
557 CSI_ML_UTIL_PVT.log_create_pw_errors(p_txn_from_date,
558 p_txn_to_date,
559 p_source_system_name,
560 p_worker_id,
561 l_return_status,
562 l_error_message);
563
564 IF(l_debug_level>1) THEN
565 FND_File.Put_Line(Fnd_File.LOG,'After Log Errors: '||
566 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
567 END IF;
568 COMMIT;
569
570 EXCEPTION
571 WHEN RESOLVE_ERROR THEN
572 IF(l_debug_level>1) THEN
573 FND_File.Put_Line(Fnd_File.LOG,'Resolve Parallel IDs Errored out...');
574 END IF;
575 j := 1;
576 FOR j in l_inst_id_tbl.FIRST .. l_inst_id_tbl.LAST LOOP
577
578 UPDATE csi_instance_interface
579 SET parallel_worker_id = -1
580 WHERE inst_interface_id = l_inst_id_tbl(j).inst_interface_id;
581
582 END LOOP;
583 COMMIT;
584 WHEN fnd_api.g_exc_error THEN
585 ROLLBACK to create_parallel_instances;
586
587 IF(l_debug_level>1) THEN
588 FND_File.Put_Line(Fnd_File.LOG,'In EXPECTED Exception in:'||l_api_name);
589 END IF;
590 j := 1;
591 FOR j in l_inst_id_tbl.FIRST .. l_inst_id_tbl.LAST LOOP
592
593 UPDATE csi_instance_interface
594 SET parallel_worker_id = -1
595 WHERE inst_interface_id = l_inst_id_tbl(j).inst_interface_id;
596
597 END LOOP;
598 COMMIT;
599
600 FND_File.Put_Line(Fnd_File.LOG,l_error_message);
601 x_return_status := l_fnd_error;
602 x_msg_data := l_error_message;
603
604 WHEN others THEN
605 ROLLBACK to create_parallel_instances;
606
607 FND_File.Put_Line(Fnd_File.LOG,'In WHEN OTHERS Exception in:'||l_api_name);
608
609 j := 1;
610 FOR j in l_inst_id_tbl.FIRST .. l_inst_id_tbl.LAST LOOP
611
612 UPDATE csi_instance_interface
613 SET parallel_worker_id = -1
614 WHERE inst_interface_id = l_inst_id_tbl(j).inst_interface_id;
615
616 END LOOP;
617 COMMIT;
618
619 l_sql_error := SQLERRM;
620 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
621 fnd_message.set_token('API_NAME',l_api_name);
622 fnd_message.set_token('SQL_ERROR',SQLERRM);
623 x_msg_data := fnd_message.get;
624 FND_File.Put_Line(Fnd_File.LOG,substr(x_msg_data,1,200));
625 x_return_status := l_fnd_unexpected;
626
627 END create_parallel_instances;
628
629 PROCEDURE create_relationships
630 (
631 x_msg_data OUT NOCOPY VARCHAR2,
632 x_return_status OUT NOCOPY VARCHAR2,
633 p_txn_from_date IN VARCHAR2,
634 p_txn_to_date IN VARCHAR2,
635 p_source_system_name IN VARCHAR2) IS
636
637 l_return_status VARCHAR2(10);
638 l_error_message VARCHAR2(2000);
639 l_relationship_tbl CSI_DATASTRUCTURES_PUB.II_RELATIONSHIP_TBL;
640 l_txn_tbl CSI_DATASTRUCTURES_PUB.TRANSACTION_TBL;
641 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
642 l_api_version NUMBER := 1.0;
643 l_commit VARCHAR2(1) := fnd_api.g_false;
644 l_init_msg_list VARCHAR2(1) := fnd_api.g_true;
645 l_validation_level NUMBER := fnd_api.g_valid_level_full;
646 l_msg_count NUMBER;
647 l_msg_data VARCHAR2(2000);
648 l_msg_index NUMBER;
649 l_sql_error VARCHAR2(2000);
650 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
651 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
652 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
653 l_api_name VARCHAR2(255) :=
654 'CSI_ML_CREATE_PUB.CREATE_RELATIONSHIPS';
655 x_count NUMBER := 0;
656 l_count NUMBER := 0;
657 l_commit_recs NUMBER := 0;
658 l_relationship_count NUMBER := 0;
659 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CSI_DEBUG_LEVEL'), '0'));
660
661 BEGIN
662
663 IF(l_debug_level>1) THEN
664 FND_File.Put_Line(Fnd_File.LOG,'Start of: '||l_api_name);
665 END IF;
666
667 x_return_status := l_fnd_success;
668
669 EXCEPTION
670 WHEN fnd_api.g_exc_error THEN
671 ROLLBACK to create_relationships;
672
673 IF(l_debug_level>1) THEN
674 FND_File.Put_Line(Fnd_File.LOG,'In EXPECTED Exception in:'||l_api_name);
675 FND_File.Put_Line(Fnd_File.LOG,l_error_message);
676 END IF;
677 x_return_status := l_fnd_error;
678 x_msg_data := l_error_message;
679
680 WHEN others THEN
681 ROLLBACK to create_relationships;
682 IF(l_debug_level>1) THEN
683 FND_File.Put_Line(Fnd_File.LOG,'In WHEN OTHERS Exception in:'||l_api_name);
684 END IF;
685 l_sql_error := SQLERRM;
686 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
687 fnd_message.set_token('API_NAME',l_api_name);
688 fnd_message.set_token('SQL_ERROR',SQLERRM);
689 x_msg_data := fnd_message.get;
690 IF(l_debug_level>1) THEN
691 FND_File.Put_Line(Fnd_File.LOG,x_msg_data);
692 END IF;
693 x_return_status := l_fnd_unexpected;
694
695 END create_relationships;
696
697 END CSI_ML_CREATE_PUB;