[Home] [Help]
PACKAGE BODY: APPS.CSI_ML_CREATE_PUB
Source
1 PACKAGE BODY CSI_ML_CREATE_PUB AS
2 -- $Header: csimcrpb.pls 120.5 2011/01/18 18:56:06 devijay 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'));
371 FND_File.Put_Line(Fnd_File.LOG,'Number of Loops: '||to_char(x_count));
372 END IF;
373
374 SAVEPOINT OUTER_LOOP_CR_PARALLEL_INST;
375
376 FOR l_count in 1 .. x_count LOOP
377
378 -- Bug 11073343
379 BEGIN
380 -- Update the tables to have 'X' in the process status field.
381 -- Otherwise they will be null
382
383 -- Process Statuses
384 -- R = Ready Status
388
385 -- X = Intermediate Process Status
386 -- P = Processed No Error
387 -- E = Error
389 UPDATE csi_instance_interface a
390 SET process_status = 'X'
391 WHERE trunc(source_transaction_date) BETWEEN
392 nvl(l_txn_from_date,trunc(source_transaction_date)) AND
393 nvl(l_txn_to_date,trunc(source_transaction_date))
394 AND nvl(transaction_identifier,'-1') = '-1'
395 AND process_status = 'R'
396 AND source_system_name = nvl(p_source_system_name,source_system_name)
397 AND parallel_worker_id = p_worker_id
398 AND rownum <= l_commit_recs;
399
400 IF(l_debug_level>1) THEN
401
402 FND_File.Put_Line(Fnd_File.LOG,'After setting INST IFACE to X: '||
403 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
404 END IF;
405
406 l_inst_id_tbl.delete;
407 i := 1;
408 FOR r_id IN c_id (p_worker_id) LOOP
409 l_inst_id_tbl(i).inst_interface_id := r_id.inst_interface_id;
410 i := i + 1;
411 END LOOP;
412
413 COMMIT;
414
415 IF(l_debug_level>1) THEN
416 FND_File.Put_Line(Fnd_File.LOG,'After ID PL/SQL Table Create: '||
417 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
418 END IF;
419
420 l_tbl_count := 0;
421 l_tbl_count := l_inst_id_tbl.count;
422 IF(l_debug_level>1) THEN
423 FND_File.Put_Line(Fnd_File.LOG,'Records Found: '||l_tbl_count);
424 FND_File.Put_Line(Fnd_File.LOG,'After Looping Child Tables: '||
425 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
426
427 -- Resolve all ID fields from Descriptive Fields
428 FND_File.Put_Line(Fnd_File.LOG,'Before Resolve IDs: '||
429 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
430 END IF;
431 IF l_tbl_count>0 -- Added
432 THEN
433 IF NVL(p_resolve_ids,'Y') = 'Y' THEN
434 CSI_ML_UTIL_PVT.resolve_pw_ids(p_txn_from_date,
435 p_txn_to_date,
436 p_source_system_name,
437 p_worker_id,
438 l_return_status,
439 l_error_message);
440 IF(l_debug_level>1) THEN
441 FND_File.Put_Line(Fnd_File.LOG,'After Resolve IDs: '||
442 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
443 END IF;
444
445 IF NOT l_return_status = l_fnd_success THEN
446 RAISE RESOLVE_ERROR; --fnd_api.g_exc_error;
447 END IF;
448 END IF;
449
450 -- After getting IDs now create PL/SQL Tables
451 IF(l_debug_level>1) THEN
452 FND_File.Put_Line(Fnd_File.LOG,'Before PL/SQL Table Create: '||
453 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
454 END IF;
455
456 SAVEPOINT create_parallel_instances;
457
458 CSI_ML_CREATE_PVT.get_iface_create_recs (p_txn_from_date,
459 p_txn_to_date,
460 p_source_system_name,
461 p_worker_id,
462 l_commit_recs,
463 l_instance_tbl,
464 l_party_tbl,
465 l_account_tbl,
466 l_ext_attrib_tbl,
467 l_price_tbl,
468 l_org_assign_tbl,
469 l_txn_tbl,
470 l_party_contact_tbl,
471 l_asset_assignment_tbl,
472 l_return_status,
473 l_error_message);
474 IF(l_debug_level>1) THEN
475 FND_File.Put_Line(Fnd_File.LOG,'After PL/SQL Table Create: '||
476 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
477 END IF;
478
479 IF NOT l_return_status = l_fnd_success THEN
480 RAISE fnd_api.g_exc_error;
481 END IF;
482
483 IF(l_debug_level>1) THEN
484 FND_File.Put_Line(Fnd_File.LOG,'Before Contact Loop: '||
485 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
486
487 -- commented the following unnecessary code
488 /*
489 IF l_party_contact_tbl.count > 0 THEN
490
491 FOR c in l_party_contact_tbl.FIRST .. l_party_contact_tbl.LAST LOOP
492 d := 1;
493 FOR d in l_party_tbl.FIRST .. l_party_tbl.LAST LOOP
494 IF l_party_contact_tbl(c).inst_interface_id = l_party_tbl(d).interface_id AND
495 l_party_contact_tbl(c).contact_party_id = l_party_tbl(d).party_id AND
496 l_party_contact_tbl(c).contact_party_rel_type = l_party_tbl(d).relationship_type_code AND
497 l_party_tbl(d).contact_flag = 'N' THEN
498 l_party_tbl(l_party_contact_tbl(c).parent_tbl_idx).contact_parent_tbl_index := d;
499 END IF;
500 END LOOP;
501 END LOOP;
502 END IF;
503 */
504 FND_File.Put_Line(Fnd_File.LOG,'After Contact Loop: '||
505 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
506
507 FND_File.Put_Line(Fnd_File.LOG,'Before GRP API: '||
508 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
509 END IF;
510 csi_item_instance_grp.create_item_instance
511 (p_api_version => l_api_version,
512 p_commit => l_commit,
513 p_init_msg_list => l_init_msg_list,
514 p_validation_level => l_validation_level,
518 p_account_tbl => l_account_tbl,
515 p_instance_tbl => l_instance_tbl,
516 p_ext_attrib_values_tbl => l_ext_attrib_tbl,
517 p_party_tbl => l_party_tbl,
519 p_pricing_attrib_tbl => l_price_tbl,
520 p_org_assignments_tbl => l_org_assign_tbl,
521 p_asset_assignment_tbl => l_asset_assignment_tbl,
522 p_txn_tbl => l_txn_tbl,
523 p_grp_error_tbl => l_grp_error_tbl,
524 x_return_status => l_return_status,
525 x_msg_count => l_msg_count,
526 x_msg_data => l_msg_data);
527
528
529
530 IF NOT l_Return_Status = l_fnd_success THEN
531 l_msg_index := 1;
532 l_Error_Message := l_Msg_Data;
533 WHILE l_msg_count > 0 LOOP
534 l_Error_Message := l_Error_Message||FND_MSG_PUB.GET(l_msg_index,
535 FND_API.G_FALSE);
536 l_msg_index := l_msg_index + 1;
537 l_Msg_Count := l_Msg_Count - 1;
538 END LOOP;
539 RAISE fnd_api.g_exc_error;
540 END IF;
541 IF(l_debug_level>1) THEN
542 FND_File.Put_Line(Fnd_File.LOG,'After GRP API: '||
543 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
544 END IF;
545 l_tbl_count := 0;
546 l_tbl_count := l_instance_tbl.count;
547 l_ext_tbl_count := l_ext_attrib_tbl.count;
548 IF(l_debug_level>1) THEN
549 FND_File.Put_Line(Fnd_File.LOG,'Updating Status of Inst Children '||
550 l_tbl_count);
551 FND_File.Put_Line(Fnd_File.LOG,'Ext Attr Status Recs '||l_ext_tbl_count);
552 FND_File.Put_Line(Fnd_File.LOG,'Before Loop to set Child Status: '||
553 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
554
555 FND_File.Put_Line(Fnd_File.LOG,'After Loop to set Child Status: '||
556 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
557 END IF;
558 END IF; -- End addition
559 COMMIT;
560
561 -- Bug 11073343
562 EXCEPTION
563 WHEN fnd_api.g_exc_error THEN
564 ROLLBACK to create_parallel_instances;
565
566 IF(l_debug_level>1) THEN
567 FND_File.Put_Line(Fnd_File.LOG,'In Inner Loop Exception in:'||l_api_name);
568 END IF;
569 j := 1;
570
571 FOR j in l_inst_id_tbl.FIRST .. l_inst_id_tbl.LAST LOOP
572 UPDATE csi_instance_interface
573 SET parallel_worker_id = -1
574 WHERE inst_interface_id = l_inst_id_tbl(j).inst_interface_id;
575 END LOOP;
576 COMMIT;
577 FND_File.Put_Line(Fnd_File.LOG,l_error_message);
578 x_return_status := l_fnd_error;
579 x_msg_data := l_error_message;
580
581 END; -- Bug 11073343
582
583 END LOOP; -- End of For Loop
584
585 CSI_ML_UTIL_PVT.log_create_pw_errors(p_txn_from_date,
586 p_txn_to_date,
587 p_source_system_name,
588 p_worker_id,
589 l_return_status,
590 l_error_message);
591
592 IF(l_debug_level>1) THEN
593 FND_File.Put_Line(Fnd_File.LOG,'After Log Errors: '||
594 to_char(sysdate,'DD-MON-YYYY HH:MI:SS:SS'));
595 END IF;
596 COMMIT;
597
598 EXCEPTION
599 WHEN RESOLVE_ERROR THEN
600 IF(l_debug_level>1) THEN
601 FND_File.Put_Line(Fnd_File.LOG,'Resolve Parallel IDs Errored out...');
602 END IF;
603 j := 1;
604 FOR j in l_inst_id_tbl.FIRST .. l_inst_id_tbl.LAST LOOP
605
606 UPDATE csi_instance_interface
607 SET parallel_worker_id = -1
608 WHERE inst_interface_id = l_inst_id_tbl(j).inst_interface_id;
609
610 END LOOP;
611 COMMIT;
612 WHEN fnd_api.g_exc_error THEN
613 ROLLBACK to OUTER_LOOP_CR_PARALLEL_INST;
614
615 IF(l_debug_level>1) THEN
616 FND_File.Put_Line(Fnd_File.LOG,'In EXPECTED Exception in:'||l_api_name);
617 END IF;
618 j := 1;
619 FOR j in l_inst_id_tbl.FIRST .. l_inst_id_tbl.LAST LOOP
620
621 UPDATE csi_instance_interface
622 SET parallel_worker_id = -1
623 WHERE inst_interface_id = l_inst_id_tbl(j).inst_interface_id;
624
625 END LOOP;
626 COMMIT;
627
628 FND_File.Put_Line(Fnd_File.LOG,l_error_message);
629 x_return_status := l_fnd_error;
630 x_msg_data := l_error_message;
631
632 WHEN others THEN
633 ROLLBACK to OUTER_LOOP_CR_PARALLEL_INST;
634
635 FND_File.Put_Line(Fnd_File.LOG,'In WHEN OTHERS Exception in:'||l_api_name);
636
637 j := 1;
638 FOR j in l_inst_id_tbl.FIRST .. l_inst_id_tbl.LAST LOOP
639
640 UPDATE csi_instance_interface
641 SET parallel_worker_id = -1
642 WHERE inst_interface_id = l_inst_id_tbl(j).inst_interface_id;
643
644 END LOOP;
645 COMMIT;
646
647 l_sql_error := SQLERRM;
648 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
649 fnd_message.set_token('API_NAME',l_api_name);
650 fnd_message.set_token('SQL_ERROR',SQLERRM);
651 x_msg_data := fnd_message.get;
652 FND_File.Put_Line(Fnd_File.LOG,substr(x_msg_data,1,200));
653 x_return_status := l_fnd_unexpected;
654
655 END create_parallel_instances;
656
657 PROCEDURE create_relationships
658 (
659 x_msg_data OUT NOCOPY VARCHAR2,
660 x_return_status OUT NOCOPY VARCHAR2,
661 p_txn_from_date IN VARCHAR2,
662 p_txn_to_date IN VARCHAR2,
663 p_source_system_name IN VARCHAR2) IS
664
665 l_return_status VARCHAR2(10);
666 l_error_message VARCHAR2(2000);
667 l_relationship_tbl CSI_DATASTRUCTURES_PUB.II_RELATIONSHIP_TBL;
668 l_txn_tbl CSI_DATASTRUCTURES_PUB.TRANSACTION_TBL;
669 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
670 l_api_version NUMBER := 1.0;
671 l_commit VARCHAR2(1) := fnd_api.g_false;
672 l_init_msg_list VARCHAR2(1) := fnd_api.g_true;
673 l_validation_level NUMBER := fnd_api.g_valid_level_full;
674 l_msg_count NUMBER;
675 l_msg_data VARCHAR2(2000);
676 l_msg_index NUMBER;
677 l_sql_error VARCHAR2(2000);
678 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
679 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
680 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
681 l_api_name VARCHAR2(255) :=
682 'CSI_ML_CREATE_PUB.CREATE_RELATIONSHIPS';
683 x_count NUMBER := 0;
684 l_count NUMBER := 0;
685 l_commit_recs NUMBER := 0;
686 l_relationship_count NUMBER := 0;
687 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CSI_DEBUG_LEVEL'), '0'));
688
689 BEGIN
690
691 IF(l_debug_level>1) THEN
692 FND_File.Put_Line(Fnd_File.LOG,'Start of: '||l_api_name);
693 END IF;
694
695 x_return_status := l_fnd_success;
696
697 EXCEPTION
698 WHEN fnd_api.g_exc_error THEN
699 ROLLBACK to create_relationships;
700
701 IF(l_debug_level>1) THEN
702 FND_File.Put_Line(Fnd_File.LOG,'In EXPECTED Exception in:'||l_api_name);
703 FND_File.Put_Line(Fnd_File.LOG,l_error_message);
704 END IF;
705 x_return_status := l_fnd_error;
706 x_msg_data := l_error_message;
707
708 WHEN others THEN
709 ROLLBACK to create_relationships;
710 IF(l_debug_level>1) THEN
711 FND_File.Put_Line(Fnd_File.LOG,'In WHEN OTHERS Exception in:'||l_api_name);
712 END IF;
713 l_sql_error := SQLERRM;
714 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
715 fnd_message.set_token('API_NAME',l_api_name);
716 fnd_message.set_token('SQL_ERROR',SQLERRM);
717 x_msg_data := fnd_message.get;
718 IF(l_debug_level>1) THEN
719 FND_File.Put_Line(Fnd_File.LOG,x_msg_data);
720 END IF;
721 x_return_status := l_fnd_unexpected;
722
723 END create_relationships;
724
725 END CSI_ML_CREATE_PUB;