[Home] [Help]
PACKAGE BODY: APPS.CSI_ML_UTIL_PVT
Source
1 PACKAGE BODY CSI_ML_UTIL_PVT AS
2 -- $Header: csimutlb.pls 120.7 2007/10/31 00:49:13 anjgupta ship $
3
4 PROCEDURE resolve_ids
5 ( p_txn_from_date IN VARCHAR2,
6 p_txn_to_date IN VARCHAR2,
7 p_batch_name IN VARCHAR2,
8 p_source_system_name IN VARCHAR2,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_error_message OUT NOCOPY VARCHAR2) IS
11
12 l_txn_from_date DATE := to_date(p_txn_from_date, 'YYYY/MM/DD HH24:MI:SS');
13 l_txn_to_date DATE := to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS');
14
15 CURSOR ins_intf_cur IS
16 SELECT inst_interface_id
17 FROM csi_instance_interface
18 WHERE trunc(source_transaction_date)
19 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
20 AND nvl(l_txn_to_date,trunc(source_transaction_date))
21 AND process_status IN ('X','R')
22 AND parallel_worker_id IS NULL
23 AND transaction_identifier IS NOT NULL
24 AND source_system_name = nvl(p_source_system_name,source_system_name);
25
26 CURSOR pty1_intf_cur IS
27 SELECT ip_interface_id
28 FROM csi_i_party_interface cpi
29 WHERE cpi.party_source_table = 'HZ_PARTIES'
30 AND cpi.inst_interface_id IN (SELECT inst_interface_id
31 FROM csi_instance_interface cii
32 WHERE cii.transaction_identifier IS NOT NULL
33 AND trunc(source_transaction_date)
34 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
35 AND nvl(l_txn_to_date,trunc(source_transaction_date))
36 AND process_status IN ('X','R')
37 AND parallel_worker_id IS NULL
38 AND source_system_name =
39 nvl(p_source_system_name,source_system_name));
40 CURSOR pty2_intf_cur IS
41 SELECT ip_interface_id
42 FROM csi_i_party_interface cpi
43 WHERE cpi.party_source_table = 'EMPLOYEE'
44 AND cpi.inst_interface_id IN (SELECT inst_interface_id
45 FROM csi_instance_interface cii
46 WHERE cii.transaction_identifier IS NOT NULL
47 AND trunc(source_transaction_date)
48 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
49 AND nvl(l_txn_to_date,trunc(source_transaction_date))
50 AND process_status IN ('X','R')
51 AND parallel_worker_id IS NULL
52 AND source_system_name =
53 nvl(p_source_system_name,source_system_name));
54 CURSOR pty3_intf_cur IS
55 SELECT ip_interface_id
56 FROM csi_i_party_interface cpi
57 WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
58 FROM csi_instance_interface cii
59 WHERE cii.transaction_identifier IS NOT NULL
60 AND trunc(source_transaction_date)
61 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
62 AND nvl(l_txn_to_date,trunc(source_transaction_date))
63 AND process_status IN ('X','R')
64 AND parallel_worker_id IS NULL
65 AND source_system_name =
66 nvl(p_source_system_name,source_system_name));
67 CURSOR iea_intf_cur IS
68 SELECT ieav_interface_id
69 FROM csi_iea_value_interface a
70 WHERE a.attribute_level = 'ITEM'
71 AND a.inst_interface_id IN (SELECT inst_interface_id
72 FROM csi_instance_interface cii
73 WHERE cii.transaction_identifier IS NOT NULL
74 AND trunc(source_transaction_date)
75 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
76 AND nvl(l_txn_to_date,trunc(source_transaction_date))
77 AND process_status IN ('X','R')
78 AND parallel_worker_id IS NULL
79 AND source_system_name =
80 nvl(p_source_system_name,source_system_name));
81
82 CURSOR asst_intf_cur IS
83 SELECT ia_interface_id
84 FROM csi_i_asset_interface a
85 WHERE a.inst_interface_id IN (SELECT inst_interface_id
86 FROM csi_instance_interface cii
87 WHERE cii.transaction_identifier IS NOT NULL
88 AND trunc(source_transaction_date)
89 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
90 AND nvl(l_txn_to_date,trunc(source_transaction_date))
91 AND process_status IN ('X','R')
92 AND parallel_worker_id IS NULL
93 AND source_system_name =
94 nvl(p_source_system_name,source_system_name));
95
96 TYPE NumTabType IS VARRAY(10000) OF NUMBER;
97 inst_intf_id_upd NumTabType;
98 ip_intf_id_upd1 NumTabType;
99 ip_intf_id_upd2 NumTabType;
100 ip_intf_id_upd3 NumTabType;
101 iea_intf_id_upd NumTabType;
102 asst_intf_id_upd NumTabType;
103 max_buffer_size NUMBER := 1000;
104
105 l_api_name VARCHAR2(255) := 'CSI_ML_UTIL_PVT.RESOLVE_IDS';
106 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
107 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
108 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
109 l_fnd_g_num NUMBER := FND_API.G_MISS_NUM;
110 l_fnd_g_char VARCHAR2(1) := FND_API.G_MISS_CHAR;
111 l_fnd_g_date DATE := FND_API.G_MISS_DATE;
112 l_sql_error VARCHAR2(2000);
113 BEGIN
114
115 x_return_status := l_fnd_success;
116 -- Get the source system id for all of the rows
117
118 OPEN ins_intf_cur;
119 LOOP
120 FETCH ins_intf_cur BULK COLLECT INTO
121 inst_intf_id_upd
122 LIMIT max_buffer_size;
123
124 FORALL i1 in 1 .. inst_intf_id_upd.count
125 UPDATE csi_instance_interface a
126 SET a.inventory_item_id =
127 (SELECT inventory_item_id
128 FROM mtl_system_items_kfv
129 WHERE concatenated_segments =
130 a.inv_concatenated_segments
131 AND ROWNUM=1)
132 WHERE inst_interface_id=inst_intf_id_upd(i1)
133 AND a.inventory_item_id IS NULL
134 AND a.inv_concatenated_segments IS NOT NULL;
135
136 FORALL i2 in 1 .. inst_intf_id_upd.count
137 UPDATE csi_instance_interface a
138 SET a.inv_vld_organization_id =
139 (SELECT organization_id
140 FROM hr_all_organization_units
141 WHERE name = a.inv_vld_organization_name)
142 WHERE inst_interface_id=inst_intf_id_upd(i2)
143 AND a.inv_vld_organization_id IS NULL
144 AND a.inv_vld_organization_name IS NOT NULL;
145
146 FORALL i3 in 1 .. inst_intf_id_upd.count
147 UPDATE csi_instance_interface a
148 SET a.instance_condition_id =
149 (SELECT status_id
150 FROM mtl_material_statuses
151 WHERE status_code = a.instance_condition)
152 WHERE inst_interface_id=inst_intf_id_upd(i3)
153 AND a.instance_condition_id IS NULL
154 AND a.instance_condition IS NOT NULL;
155
156 FORALL i4 in 1 .. inst_intf_id_upd.count
157 UPDATE csi_instance_interface a
158 SET a.instance_status_id =
159 (SELECT instance_status_id
160 FROM csi_instance_statuses
161 WHERE name = a.instance_status)
162 WHERE inst_interface_id=inst_intf_id_upd(i4)
163 AND a.instance_status_id IS NULL
164 AND a.instance_status IS NOT NULL;
165
166
167 FORALL i6 in 1 .. inst_intf_id_upd.count
168 UPDATE csi_instance_interface a
169 SET a.system_id = (SELECT system_id
170 FROM csi_systems_b
171 WHERE system_number = a.system_number)
172 WHERE inst_interface_id=inst_intf_id_upd(i6)
173 AND a.system_id IS NULL
174 AND a.system_number IS NOT NULL;
175
176 FORALL i7 in 1 .. inst_intf_id_upd.count
177 UPDATE csi_instance_interface a
178 SET a.unit_of_measure_code =
179 (SELECT uom_code
180 FROM mtl_units_of_measure_vl
181 WHERE unit_of_measure_tl = a.unit_of_measure)
182 WHERE inst_interface_id=inst_intf_id_upd(i7)
183 AND a.unit_of_measure_code IS NULL
184 AND a.unit_of_measure IS NOT NULL;
185
186 FORALL i8 in 1 .. inst_intf_id_upd.count
187 UPDATE csi_instance_interface a
188 SET a.inv_organization_id =
189 (SELECT organization_id
190 FROM hr_all_organization_units
191 WHERE NAME = a.inv_organization_name)
192 WHERE inst_interface_id=inst_intf_id_upd(i8)
193 AND a.inv_organization_id IS NULL
194 AND a.inv_organization_name IS NOT NULL;
195
196 FORALL i9 in 1 .. inst_intf_id_upd.count
197 UPDATE csi_instance_interface a
198 SET a.project_id = (SELECT project_id
199 FROM pa_projects_all
200 WHERE segment1 = a.project_number)
201 WHERE inst_interface_id=inst_intf_id_upd(i9)
202 AND a.project_id IS NULL
203 AND a.project_number IS NOT NULL;
204
205 FORALL i10 in 1 .. inst_intf_id_upd.count
206 UPDATE csi_instance_interface a
207 SET a.task_id = (SELECT task_id
208 FROM pa_tasks pt,
209 pa_projects_all pp
210 WHERE pt.task_number = a.task_number
211 AND pp.segment1 = a.project_number
212 AND pt.project_id = pp.project_id)
213 WHERE inst_interface_id=inst_intf_id_upd(i10)
214 AND a.task_id IS NULL
215 AND a.task_number IS NOT NULL
216 AND a.project_number IS NOT NULL;
217
218 FORALL i11 in 1 .. inst_intf_id_upd.count
219 UPDATE csi_instance_interface a
220 SET a.wip_job_id = (SELECT wip_entity_id
221 FROM wip_entities
222 WHERE wip_entity_name = a.wip_job_name)
223 WHERE inst_interface_id=inst_intf_id_upd(i11)
224 AND a.wip_job_id IS NULL
225 AND a.wip_job_name IS NOT NULL;
226
227
228 FORALL i16 in 1 .. inst_intf_id_upd.count
229 UPDATE csi_instance_interface a
230 SET a.operating_unit=(SELECT organization_id
231 FROM hr_operating_units
232 WHERE name = a.operating_unit_name)
233 WHERE inst_interface_id=inst_intf_id_upd(i16)
234 AND a.operating_unit IS NULL
235 AND a.operating_unit_name IS NOT NULL;
236
237 COMMIT;
238 EXIT WHEN ins_intf_cur%NOTFOUND;
239 END LOOP;
240 COMMIT;
241 CLOSE ins_intf_cur;
242
243 OPEN pty1_intf_cur;
244 LOOP
245 FETCH pty1_intf_cur BULK COLLECT INTO
246 ip_intf_id_upd1
247 LIMIT max_buffer_size;
248
249 FORALL i1 in 1 .. ip_intf_id_upd1.count
250 UPDATE csi_i_party_interface cpi
251 SET cpi.party_id = (SELECT party_id
252 FROM hz_parties
253 WHERE party_name = cpi.party_name
254 AND party_number = NVL(cpi.party_number,party_number))
255 WHERE ip_interface_id=ip_intf_id_upd1(i1)
256 AND cpi.party_id IS NULL
257 AND cpi.party_name IS NOT NULL;
258
259 FORALL i2 in 1 .. ip_intf_id_upd1.count
260 UPDATE csi_i_party_interface cpi
261 SET cpi.contact_party_id =
262 (SELECT party_id
263 FROM hz_parties
264 WHERE party_name = cpi.contact_party_name
265 AND party_number = NVL(cpi.contact_party_number,party_number))
266 WHERE ip_interface_id=ip_intf_id_upd1(i2)
267 AND cpi.contact_party_id IS NULL
268 AND cpi.contact_party_name IS NOT NULL;
269
270 COMMIT;
271 EXIT WHEN pty1_intf_cur%NOTFOUND;
272 END LOOP;
273 COMMIT;
274 CLOSE pty1_intf_cur;
275
276 UPDATE CSI_I_PARTY_INTERFACE cpi
277 SET party_id=(SELECT vendor_id
278 FROM po_vendors
279 WHERE vendor_name = cpi.party_name
280 )
281 WHERE cpi.party_source_table = 'PO_VENDORS'
282 AND cpi.inst_interface_id IN (SELECT inst_interface_id
283 FROM csi_instance_interface cii
284 WHERE cii.transaction_identifier IS NOT NULL
285 AND trunc(source_transaction_date)
286 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
287 AND nvl(l_txn_to_date,trunc(source_transaction_date))
288 AND process_status IN ('X','R')
289 AND parallel_worker_id IS NULL
290 AND source_system_name =
291 nvl(p_source_system_name,source_system_name))
292 AND cpi.party_id IS NULL
293 AND cpi.party_name IS NOT NULL;
294
295 OPEN pty2_intf_cur;
296 LOOP
297 FETCH pty2_intf_cur BULK COLLECT INTO
298 ip_intf_id_upd2
299 LIMIT max_buffer_size;
300
301 FORALL i1 in 1 .. ip_intf_id_upd2.count
302 UPDATE csi_i_party_interface cpi
303 SET cpi.party_id = (SELECT person_id
304 FROM per_all_people_f
305 WHERE full_name = cpi.party_name)
306 WHERE ip_interface_id=ip_intf_id_upd2(i1)
307 AND cpi.party_id IS NULL
308 AND cpi.party_name IS NOT NULL;
309
310 FORALL i2 in 1 .. ip_intf_id_upd2.count
311 UPDATE csi_i_party_interface cpi
312 SET cpi.contact_party_id=
313 (SELECT party_id
314 FROM hz_parties
315 WHERE party_name = cpi.contact_party_name
316 AND party_number = NVL(cpi.contact_party_number,party_number))
317 WHERE ip_interface_id=ip_intf_id_upd2(i2)
318 AND cpi.contact_party_id IS NULL
319 AND cpi.contact_party_name IS NOT NULL;
320
321 COMMIT;
322 EXIT WHEN pty2_intf_cur%NOTFOUND;
323 END LOOP;
324 COMMIT;
325 CLOSE pty2_intf_cur;
326
327 UPDATE CSI_I_PARTY_INTERFACE cpi
328 SET party_id=(SELECT team_id
329 FROM jtf_rs_teams_vl
330 WHERE team_name = cpi.party_name)
331 WHERE cpi.party_source_table = 'TEAM'
332 AND cpi.inst_interface_id IN (SELECT inst_interface_id
333 FROM csi_instance_interface cii
334 WHERE cii.transaction_identifier IS NOT NULL
335 AND trunc(source_transaction_date)
336 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
337 AND nvl(l_txn_to_date,trunc(source_transaction_date))
338 AND parallel_worker_id IS NULL
339 AND source_system_name = nvl(p_source_system_name,source_system_name))
340 AND cpi.party_id IS NULL
341 AND cpi.party_name IS NOT NULL;
342
343 UPDATE CSI_I_PARTY_INTERFACE cpi
344 SET party_id = (SELECT group_id
345 FROM jtf_rs_groups_vl
346 WHERE group_name = cpi.party_name)
347 WHERE cpi.party_source_table = 'GROUP'
348 AND cpi.inst_interface_id IN (SELECT inst_interface_id
349 FROM csi_instance_interface cii
350 WHERE cii.transaction_identifier IS NOT NULL
351 AND trunc(source_transaction_date)
352 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
353 AND nvl(l_txn_to_date,trunc(source_transaction_date))
354 AND process_status IN ('X','R')
355 AND parallel_worker_id IS NULL
356 AND source_system_name = nvl(p_source_system_name,source_system_name))
357 AND cpi.party_id IS NULL
358 AND cpi.party_name IS NOT NULL;
359
360 OPEN pty3_intf_cur;
361 LOOP
362 FETCH pty3_intf_cur BULK COLLECT INTO
363 ip_intf_id_upd3
364 LIMIT max_buffer_size;
365
366 FORALL i1 in 1 .. ip_intf_id_upd3.count
367 UPDATE csi_i_party_interface cpi
368 SET cpi.party_account1_id=
369 (SELECT cust_account_id
370 FROM hz_cust_accounts
371 WHERE account_number = cpi.party_account1_number
372 AND party_id = cpi.party_id)
373 WHERE ip_interface_id=ip_intf_id_upd3(i1)
374 AND cpi.party_account1_id IS NULL
375 AND cpi.party_account1_number IS NOT NULL
376 AND cpi.party_id IS NOT NULL;
377
378 FORALL i2 in 1 .. ip_intf_id_upd3.count
379 UPDATE csi_i_party_interface cpi
380 SET cpi.party_account2_id=
381 (SELECT cust_account_id
382 FROM hz_cust_accounts
383 WHERE account_number = cpi.party_account2_number
384 AND party_id = cpi.party_id)
385 WHERE ip_interface_id=ip_intf_id_upd3(i2)
386 AND cpi.party_account2_id IS NULL
387 AND cpi.party_account2_number IS NOT NULL
388 AND cpi.party_id IS NOT NULL;
389
390 FORALL i3 in 1 .. ip_intf_id_upd3.count
391 UPDATE csi_i_party_interface cpi
392 SET cpi.party_account3_id=
393 (SELECT cust_account_id
394 FROM hz_cust_accounts
395 WHERE account_number = cpi.party_account3_number
396 AND party_id = cpi.party_id)
397 WHERE ip_interface_id=ip_intf_id_upd3(i3)
398 AND cpi.party_account3_id IS NULL
399 AND cpi.party_account3_number IS NOT NULL
400 AND cpi.party_id IS NOT NULL;
401
402 COMMIT;
403 EXIT WHEN pty3_intf_cur%NOTFOUND;
404 END LOOP;
405 COMMIT;
406 CLOSE pty3_intf_cur;
407
408
409 OPEN asst_intf_cur;
410 LOOP
411 FETCH asst_intf_cur BULK COLLECT INTO
412 asst_intf_id_upd
413 LIMIT max_buffer_size;
414
415 FORALL asst1 in 1 .. asst_intf_id_upd.count
416 UPDATE csi_i_asset_interface a
417 SET a.fa_asset_id = (SELECT asset_id
418 FROM fa_additions_b
419 WHERE asset_number =
420 a.fa_asset_number
421 )
422 WHERE a.ia_interface_id=asst_intf_id_upd(asst1)
423 AND a.fa_asset_id IS NULL
424 AND a.fa_asset_number IS NOT NULL;
425 COMMIT;
426 EXIT WHEN asst_intf_cur%NOTFOUND;
427 END LOOP;
428
429
430 -- Extended Attribute Interface Table Values
431
432 BEGIN
433 OPEN iea_intf_cur;
434 LOOP
435 FETCH iea_intf_cur BULK COLLECT INTO
436 iea_intf_id_upd
437 LIMIT max_buffer_size;
438
439 FORALL i1 in 1 .. iea_intf_id_upd.count
440 UPDATE csi_iea_value_interface a
441 SET a.inventory_item_id =
442 (SELECT inventory_item_id
443 FROM mtl_system_items_kfv
444 WHERE concatenated_segments =
445 a.inv_concatenated_segments
446 AND ROWNUM=1)
447 WHERE ieav_interface_id=iea_intf_id_upd(i1)
448 AND a.inventory_item_id IS NULL
449 AND a.inv_concatenated_segments IS NOT NULL;
450
451 FORALL i2 in 1 .. iea_intf_id_upd.count
452 UPDATE csi_iea_value_interface a
453 SET a.master_organization_id =
454 (SELECT organization_id
455 FROM hr_all_organization_units
456 WHERE name = master_organization_name)
457 WHERE ieav_interface_id=iea_intf_id_upd(i2)
458 AND a.master_organization_id IS NULL
459 AND master_organization_name IS NOT NULL;
460
461 COMMIT;
462 EXIT WHEN iea_intf_cur%NOTFOUND;
463 END LOOP;
464 COMMIT;
465 CLOSE iea_intf_cur;
466
467 UPDATE csi_iea_value_interface a
468 SET a.attribute_id=(SELECT attribute_id
469 FROM csi_i_extended_attribs
470 WHERE attribute_level = a.attribute_level
471 AND attribute_code = a.attribute_code)
472 WHERE a.attribute_level = 'GLOBAL'
473 AND a.inst_interface_id IN (SELECT inst_interface_id
474 FROM csi_instance_interface cii
475 WHERE cii.transaction_identifier IS NOT NULL
476 AND trunc(source_transaction_date)
477 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
478 AND nvl(l_txn_to_date,trunc(source_transaction_date))
479 AND process_status IN ('X','R')
480 AND parallel_worker_id IS NULL
481 AND source_system_name = nvl(p_source_system_name,source_system_name))
482 AND a.attribute_id IS NULL
483 AND a.attribute_level IS NOT NULL
484 AND a.attribute_code IS NOT NULL;
485
486 UPDATE csi_iea_value_interface a
487 SET a.attribute_id = (SELECT attribute_id
488 FROM csi_i_extended_attribs
489 WHERE attribute_level = a.attribute_level
490 AND attribute_code = a.attribute_code
491 AND inventory_item_id = a.inventory_item_id
492 AND a.attribute_id IS NULL
493 AND master_organization_id = a.master_organization_id
494 AND NVL(attribute_category,'$CSI_NULL_VALUE$')=
495 NVL(a.attribute_category,'$CSI_NULL_VALUE$'))
496 WHERE a.attribute_level = 'ITEM'
497 AND a.inst_interface_id IN (SELECT inst_interface_id
498 FROM csi_instance_interface cii
499 WHERE cii.transaction_identifier IS NOT NULL
500 AND trunc(source_transaction_date)
501 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
502 AND nvl(l_txn_to_date,trunc(source_transaction_date))
503 AND process_status IN ('X','R')
504 AND parallel_worker_id IS NULL
505 AND source_system_name = nvl(p_source_system_name,source_system_name))
506 AND a.attribute_id IS NULL
507 AND a.attribute_level IS NOT NULL
508 AND a.attribute_code IS NOT NULL
509 AND a.inventory_item_id IS NOT NULL;
510
511 EXCEPTION
512 WHEN others THEN
513 fnd_message.set_name('CSI','CSI_ML_EXT_ATTR_ID_ERROR');
514 fnd_message.set_token('SQL_ERROR',SQLERRM);
515 x_error_message := fnd_message.get;
516 x_return_status := l_fnd_unexpected;
517 END;
518
519 EXCEPTION
520 WHEN others THEN
521 l_sql_error := SQLERRM;
522 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
523 fnd_message.set_token('API_NAME',l_api_name);
524 fnd_message.set_token('SQL_ERROR',SQLERRM);
525 x_error_message := fnd_message.get;
526 x_return_status := l_fnd_unexpected;
527
528 END resolve_ids;
529
530 PROCEDURE resolve_pw_ids
531 (
532 p_txn_from_date IN VARCHAR2,
533 p_txn_to_date IN VARCHAR2,
534 p_source_system_name IN VARCHAR2,
535 p_worker_id IN NUMBER,
536 x_return_status OUT NOCOPY VARCHAR2,
537 x_error_message OUT NOCOPY VARCHAR2) IS
538
539 l_txn_from_date DATE := to_date(p_txn_from_date, 'YYYY/MM/DD HH24:MI:SS');
540 l_txn_to_date DATE := to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS');
541
542 CURSOR ins_intf_cur IS
543 SELECT inst_interface_id
544 FROM csi_instance_interface
545 WHERE trunc(source_transaction_date)
546 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
547 AND nvl(l_txn_to_date,trunc(source_transaction_date))
548 AND nvl(transaction_identifier,'-1') = '-1'
549 AND process_status = 'X'
550 AND source_system_name = nvl(p_source_system_name,source_system_name)
551 AND parallel_worker_id = p_worker_id;
552
553 CURSOR pty1_intf_cur IS
554 SELECT ip_interface_id
555 FROM csi_i_party_interface cpi
556 WHERE cpi.party_source_table = 'HZ_PARTIES'
557 AND cpi.inst_interface_id IN (SELECT inst_interface_Id
558 FROM csi_instance_interface
559 WHERE transaction_identifier IS NULL
560 AND trunc(source_transaction_date)
561 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
562 AND nvl(l_txn_to_date,trunc(source_transaction_date))
563 AND process_status = 'X'
564 AND source_system_name =
565 nvl(p_source_system_name,source_system_name)
566 AND parallel_worker_id = p_worker_id);
567
568 CURSOR pty2_intf_cur IS
569 SELECT ip_interface_id
570 FROM csi_i_party_interface cpi
571 WHERE cpi.party_source_table = 'EMPLOYEE'
572 AND cpi.inst_interface_id IN (SELECT inst_interface_Id
573 FROM csi_instance_interface
574 WHERE transaction_identifier IS NULL
575 AND trunc(source_transaction_date)
576 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
577 AND nvl(l_txn_to_date,trunc(source_transaction_date))
578 AND process_status = 'X'
579 AND source_system_name = nvl(p_source_system_name,source_system_name)
580 AND parallel_worker_id = p_worker_id);
581 CURSOR pty3_intf_cur IS
582 SELECT ip_interface_id
583 FROM csi_i_party_interface cpi
584 WHERE cpi.inst_interface_id IN (SELECT inst_interface_Id
585 FROM csi_instance_interface
586 WHERE transaction_identifier IS NULL
587 AND trunc(source_transaction_date)
588 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
589 AND nvl(l_txn_to_date,trunc(source_transaction_date))
590 AND process_status = 'X'
591 AND source_system_name = nvl(p_source_system_name,source_system_name)
592 AND parallel_worker_id = p_worker_id);
593
594 CURSOR iea_intf_cur IS
595 SELECT ieav_interface_id
596 FROM csi_iea_value_interface a
597 WHERE a.attribute_level = 'ITEM'
598 AND a.inst_interface_id IN (SELECT inst_interface_Id
599 FROM csi_instance_interface
600 WHERE transaction_identifier IS NULL
601 AND trunc(source_transaction_date)
602 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
603 AND nvl(l_txn_to_date,trunc(source_transaction_date))
604 AND process_status = 'X'
605 AND source_system_name = nvl(p_source_system_name,source_system_name)
606 AND parallel_worker_id = p_worker_id);
607
608 CURSOR asst_intf_cur IS
609 SELECT ia_interface_id
610 FROM csi_i_asset_interface a
611 WHERE a.inst_interface_id IN (SELECT inst_interface_Id
612 FROM csi_instance_interface
613 WHERE transaction_identifier IS NULL
614 AND trunc(source_transaction_date)
615 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
616 AND nvl(l_txn_to_date,trunc(source_transaction_date))
617 AND process_status = 'X'
618 AND source_system_name = nvl(p_source_system_name,source_system_name)
619 AND parallel_worker_id = p_worker_id);
620
621 TYPE NumTabType IS VARRAY(10000) OF NUMBER;
622 inst_intf_id_upd NumTabType;
623 ip_intf_id_upd1 NumTabType;
624 ip_intf_id_upd2 NumTabType;
625 ip_intf_id_upd3 NumTabType;
626 iea_intf_id_upd NumTabType;
627 asst_intf_id_upd NumTabType;
628 max_buffer_size NUMBER := 1000;
629
630 l_api_name VARCHAR2(255) := 'CSI_ML_UTIL_PVT.RESOLVE_IDS';
631 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
632 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
633 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
634 l_fnd_g_num NUMBER := FND_API.G_MISS_NUM;
635 l_fnd_g_char VARCHAR2(1) := FND_API.G_MISS_CHAR;
636 l_fnd_g_date DATE := FND_API.G_MISS_DATE;
637 l_sql_error VARCHAR2(2000);
638
639 BEGIN
640
641 x_return_status := l_fnd_success;
642
643 -- Get the source system id for all of the rows
644
645 -- Get all of the ID values in the Interface Tables where the source system
646 -- has the derive id flag = Y
647 OPEN ins_intf_cur;
648 LOOP
649 FETCH ins_intf_cur BULK COLLECT INTO
650 inst_intf_id_upd
651 LIMIT max_buffer_size;
652
653 FORALL i1 in 1 .. inst_intf_id_upd.count
654 UPDATE csi_instance_interface a
655 SET a.inventory_item_id =
656 (SELECT inventory_item_id
657 FROM mtl_system_items_kfv
658 WHERE concatenated_segments =
659 a.inv_concatenated_segments
660 AND ROWNUM=1)
661 WHERE inst_interface_id=inst_intf_id_upd(i1)
662 AND a.inventory_item_id IS NULL
663 AND a.inv_concatenated_segments IS NOT NULL;
664
665 FORALL i2 in 1 .. inst_intf_id_upd.count
666 UPDATE csi_instance_interface a
667 SET a.inv_vld_organization_id =
668 (SELECT organization_id
669 FROM hr_all_organization_units
670 WHERE name = a.inv_vld_organization_name)
671 WHERE inst_interface_id=inst_intf_id_upd(i2)
672 AND a.inv_vld_organization_id IS NULL
673 AND a.inv_vld_organization_name IS NOT NULL;
674
675 FORALL i3 in 1 .. inst_intf_id_upd.count
676 UPDATE csi_instance_interface a
677 SET a.instance_condition_id =
678 (SELECT status_id
679 FROM mtl_material_statuses
680 WHERE status_code = a.instance_condition)
681 WHERE inst_interface_id=inst_intf_id_upd(i3)
682 AND a.instance_condition_id IS NULL
683 AND a.instance_condition IS NOT NULL;
684
685 FORALL i4 in 1 .. inst_intf_id_upd.count
686 UPDATE csi_instance_interface a
687 SET a.instance_status_id =
688 (SELECT instance_status_id
689 FROM csi_instance_statuses
690 WHERE name = a.instance_status)
691 WHERE inst_interface_id=inst_intf_id_upd(i4)
692 AND a.instance_status_id IS NULL
693 AND a.instance_status IS NOT NULL;
694
695 FORALL i6 in 1 .. inst_intf_id_upd.count
696 UPDATE csi_instance_interface a
697 SET a.system_id = (SELECT system_id
698 FROM csi_systems_b
699 WHERE system_number = a.system_number)
700 WHERE inst_interface_id=inst_intf_id_upd(i6)
701 AND a.system_id IS NULL
702 AND a.system_number IS NOT NULL;
703
704 FORALL i7 in 1 .. inst_intf_id_upd.count
705 UPDATE csi_instance_interface a
706 SET a.unit_of_measure_code =
707 (SELECT uom_code
708 FROM mtl_units_of_measure_vl
709 WHERE unit_of_measure_tl = a.unit_of_measure)
710 WHERE inst_interface_id=inst_intf_id_upd(i7)
711 AND a.unit_of_measure_code IS NULL
712 AND a.unit_of_measure IS NOT NULL;
713
714 FORALL i8 in 1 .. inst_intf_id_upd.count
715 UPDATE csi_instance_interface a
716 SET a.inv_organization_id =
717 (SELECT organization_id
718 FROM hr_all_organization_units
719 WHERE NAME = a.inv_organization_name)
720 WHERE inst_interface_id=inst_intf_id_upd(i8)
721 AND a.inv_organization_id IS NULL
722 AND a.inv_organization_name IS NOT NULL;
723
724 FORALL i9 in 1 .. inst_intf_id_upd.count
725 UPDATE csi_instance_interface a
726 SET a.project_id = (SELECT project_id
727 FROM pa_projects_all
728 WHERE segment1 = a.project_number)
729 WHERE inst_interface_id=inst_intf_id_upd(i9)
730 AND a.project_id IS NULL
731 AND a.project_number IS NOT NULL;
732
733 FORALL i10 in 1 .. inst_intf_id_upd.count
734 UPDATE csi_instance_interface a
735 SET a.task_id = (SELECT task_id
736 FROM pa_tasks pt,
737 pa_projects_all pp
738 WHERE pt.task_number = a.task_number
739 AND pp.segment1 = a.project_number
740 AND pt.project_id = pp.project_id)
741 WHERE inst_interface_id=inst_intf_id_upd(i10)
742 AND a.task_id IS NULL
743 AND a.task_number IS NOT NULL
744 AND a.project_number IS NOT NULL;
745
746 FORALL i11 in 1 .. inst_intf_id_upd.count
747 UPDATE csi_instance_interface a
748 SET a.wip_job_id = (SELECT wip_entity_id
749 FROM wip_entities
750 WHERE wip_entity_name = a.wip_job_name)
751 WHERE inst_interface_id=inst_intf_id_upd(i11)
752 AND a.wip_job_id IS NULL
753 AND a.wip_job_name IS NOT NULL;
754
755 FORALL i16 in 1 .. inst_intf_id_upd.count
756 UPDATE csi_instance_interface a
757 SET a.operating_unit=(SELECT organization_id
758 FROM hr_operating_units
759 WHERE name = a.operating_unit_name)
760 WHERE inst_interface_id=inst_intf_id_upd(i16)
761 AND a.operating_unit IS NULL
762 AND a.operating_unit_name IS NOT NULL;
763
764 COMMIT;
765 EXIT WHEN ins_intf_cur%NOTFOUND;
766 END LOOP;
767 COMMIT;
768 CLOSE ins_intf_cur;
769
770 OPEN pty1_intf_cur;
771 LOOP
772 FETCH pty1_intf_cur BULK COLLECT INTO
773 ip_intf_id_upd1
774 LIMIT max_buffer_size;
775
776 FORALL i1 in 1 .. ip_intf_id_upd1.count
777 UPDATE csi_i_party_interface cpi
778 SET cpi.party_id = (SELECT party_id
779 FROM hz_parties
780 WHERE party_name = cpi.party_name
781 AND party_number = NVL(cpi.party_number,party_number))
782 WHERE ip_interface_id=ip_intf_id_upd1(i1)
783 AND cpi.party_id IS NULL
784 AND cpi.party_name IS NOT NULL;
785
786 FORALL i2 in 1 .. ip_intf_id_upd1.count
787 UPDATE csi_i_party_interface cpi
788 SET cpi.contact_party_id =
789 (SELECT party_id
790 FROM hz_parties
791 WHERE party_name = cpi.contact_party_name
792 AND party_number = NVL(cpi.contact_party_number,party_number))
793 WHERE ip_interface_id=ip_intf_id_upd1(i2)
794 AND cpi.contact_party_id IS NULL
795 AND cpi.contact_party_name IS NOT NULL;
796
797 COMMIT;
798 EXIT WHEN pty1_intf_cur%NOTFOUND;
799 END LOOP;
800 COMMIT;
801 CLOSE pty1_intf_cur;
802 UPDATE CSI_I_PARTY_INTERFACE cpi
803 SET party_id=(SELECT vendor_id
804 FROM po_vendors
805 WHERE vendor_name = cpi.party_name)
806 WHERE cpi.party_source_table = 'PO_VENDORS'
807 AND cpi.inst_interface_id IN (SELECT inst_interface_Id
808 FROM csi_instance_interface
809 WHERE transaction_identifier IS NULL
810 AND trunc(source_transaction_date)
811 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
812 AND nvl(l_txn_to_date,trunc(source_transaction_date))
813 AND process_status = 'X'
814 AND source_system_name =
815 nvl(p_source_system_name,source_system_name)
816 AND parallel_worker_id = p_worker_id)
817 AND cpi.party_id IS NULL
818 AND cpi.party_name IS NOT NULL;
819
820 OPEN pty2_intf_cur;
821 LOOP
822 FETCH pty2_intf_cur BULK COLLECT INTO
823 ip_intf_id_upd2
824 LIMIT max_buffer_size;
825
826 FORALL i1 in 1 .. ip_intf_id_upd2.count
827 UPDATE csi_i_party_interface cpi
828 SET cpi.party_id = (SELECT person_id
829 FROM per_all_people_f
830 WHERE full_name = cpi.party_name)
831 WHERE ip_interface_id=ip_intf_id_upd2(i1)
832 AND cpi.party_id IS NULL
833 AND cpi.party_name IS NOT NULL;
834
835 FORALL i2 in 1 .. ip_intf_id_upd2.count
836 UPDATE csi_i_party_interface cpi
837 SET cpi.contact_party_id=
838 (SELECT party_id
839 FROM hz_parties
840 WHERE party_name = cpi.contact_party_name
841 AND party_number = NVL(cpi.contact_party_number,party_number))
842 WHERE ip_interface_id=ip_intf_id_upd2(i2)
843 AND cpi.contact_party_id IS NULL
844 AND cpi.contact_party_name IS NOT NULL;
845
846 COMMIT;
847 EXIT WHEN pty2_intf_cur%NOTFOUND;
848 END LOOP;
849 COMMIT;
850 CLOSE pty2_intf_cur;
851
852 UPDATE CSI_I_PARTY_INTERFACE cpi
853 SET party_id = (SELECT team_id
854 FROM jtf_rs_teams_vl
855 WHERE team_name = cpi.party_name)
856 WHERE cpi.party_source_table = 'TEAM'
857 AND cpi.inst_interface_id IN (SELECT inst_interface_Id
858 FROM csi_instance_interface
859 WHERE transaction_identifier IS NULL
860 AND trunc(source_transaction_date)
861 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
862 AND nvl(l_txn_to_date,trunc(source_transaction_date))
863 AND process_status = 'X'
864 AND source_system_name = nvl(p_source_system_name,source_system_name)
865 AND parallel_worker_id = p_worker_id)
866 AND cpi.party_id IS NULL
867 AND cpi.party_name IS NOT NULL;
868
869 UPDATE CSI_I_PARTY_INTERFACE cpi
870 SET party_id = (SELECT group_id
871 FROM jtf_rs_groups_vl
872 WHERE group_name = cpi.party_name)
873 WHERE cpi.party_source_table = 'GROUP'
874 AND cpi.inst_interface_id IN (SELECT inst_interface_Id
875 FROM csi_instance_interface
876 WHERE transaction_identifier IS NULL
877 AND trunc(source_transaction_date)
878 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
879 AND nvl(l_txn_to_date,trunc(source_transaction_date))
880 AND process_status = 'X'
881 AND source_system_name = nvl(p_source_system_name,source_system_name)
882 AND parallel_worker_id = p_worker_id)
883 AND cpi.party_id IS NULL
884 AND cpi.party_name IS NOT NULL;
885
886 OPEN pty3_intf_cur;
887 LOOP
888 FETCH pty3_intf_cur BULK COLLECT INTO
889 ip_intf_id_upd3
890 LIMIT max_buffer_size;
891
892 FORALL i1 in 1 .. ip_intf_id_upd3.count
893 UPDATE csi_i_party_interface cpi
894 SET cpi.party_account1_id=
895 (SELECT cust_account_id
896 FROM hz_cust_accounts
897 WHERE account_number = cpi.party_account1_number
898 AND party_id = cpi.party_id)
899 WHERE ip_interface_id=ip_intf_id_upd3(i1)
900 AND cpi.party_account1_id IS NULL
901 AND cpi.party_account1_number IS NOT NULL;
902
903 FORALL i2 in 1 .. ip_intf_id_upd3.count
904 UPDATE csi_i_party_interface cpi
905 SET cpi.party_account2_id=
906 (SELECT cust_account_id
907 FROM hz_cust_accounts
908 WHERE account_number = cpi.party_account2_number
909 AND party_id = cpi.party_id)
910 WHERE ip_interface_id=ip_intf_id_upd3(i2)
911 AND cpi.party_account2_id IS NULL
912 AND cpi.party_account2_number IS NOT NULL;
913
914 FORALL i3 in 1 .. ip_intf_id_upd3.count
915 UPDATE csi_i_party_interface cpi
916 SET cpi.party_account3_id=
917 (SELECT cust_account_id
918 FROM hz_cust_accounts
919 WHERE account_number = cpi.party_account3_number
920 AND party_id = cpi.party_id)
921 WHERE ip_interface_id=ip_intf_id_upd3(i3)
922 AND cpi.party_account3_id IS NULL
923 AND cpi.party_account3_number IS NOT NULL
924 AND cpi.party_id IS NOT NULL;
925
926 COMMIT;
927 EXIT WHEN pty3_intf_cur%NOTFOUND;
928 END LOOP;
929 COMMIT;
930 CLOSE pty3_intf_cur;
931
932 OPEN asst_intf_cur;
933 LOOP
934 FETCH asst_intf_cur BULK COLLECT INTO
935 asst_intf_id_upd
936 LIMIT max_buffer_size;
937
938 FORALL asst1 in 1 .. asst_intf_id_upd.count
939 UPDATE csi_i_asset_interface a
940 SET a.fa_asset_id = (SELECT asset_id
941 FROM fa_additions_b
942 WHERE asset_number =
943 a.fa_asset_number
944 )
945 WHERE a.ia_interface_id=asst_intf_id_upd(asst1)
946 AND a.fa_asset_id IS NULL
947 AND a.fa_asset_number IS NOT NULL;
948 COMMIT;
949 EXIT WHEN asst_intf_cur%NOTFOUND;
950 END LOOP;
951
952
953 -- Extended Attribute Interface Table Values
954
955 BEGIN
956 OPEN iea_intf_cur;
957 LOOP
958 FETCH iea_intf_cur BULK COLLECT INTO
959 iea_intf_id_upd
960 LIMIT max_buffer_size;
961
962 FORALL i1 in 1 .. iea_intf_id_upd.count
963 UPDATE csi_iea_value_interface a
964 SET a.inventory_item_id =
965 (SELECT inventory_item_id
966 FROM mtl_system_items_kfv
967 WHERE concatenated_segments =
968 a.inv_concatenated_segments
969 AND ROWNUM=1)
970 WHERE ieav_interface_id=iea_intf_id_upd(i1)
971 AND a.inventory_item_id IS NULL
972 AND a.inv_concatenated_segments IS NOT NULL;
973
974 FORALL i2 in 1 .. iea_intf_id_upd.count
975 UPDATE csi_iea_value_interface a
976 SET a.master_organization_id =
977 (SELECT organization_id
978 FROM hr_all_organization_units
979 WHERE name = master_organization_name)
980 WHERE ieav_interface_id=iea_intf_id_upd(i2)
981 AND a.master_organization_id IS NULL
982 AND a.master_organization_name IS NOT NULL;
983
984 COMMIT;
985 EXIT WHEN iea_intf_cur%NOTFOUND;
986 END LOOP;
987 COMMIT;
988 CLOSE iea_intf_cur;
989
990 UPDATE csi_iea_value_interface a
991 SET a.attribute_id=(SELECT attribute_id
992 FROM csi_i_extended_attribs
993 WHERE attribute_level = a.attribute_level
994 AND attribute_code = a.attribute_code)
995 WHERE a.attribute_level = 'GLOBAL'
996 AND a.inst_interface_id IN (SELECT inst_interface_Id
997 FROM csi_instance_interface
998 WHERE transaction_identifier IS NULL
999 AND trunc(source_transaction_date)
1000 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
1001 AND nvl(l_txn_to_date,trunc(source_transaction_date))
1002 AND process_status = 'X'
1003 AND source_system_name = nvl(p_source_system_name,source_system_name)
1004 AND parallel_worker_id = p_worker_id)
1005 AND a.attribute_id IS NULL
1006 AND a.attribute_level IS NOT NULL
1007 AND a.attribute_code IS NOT NULL;
1008
1009 UPDATE csi_iea_value_interface a
1010 SET a.attribute_id=(SELECT attribute_id
1011 FROM csi_i_extended_attribs
1012 WHERE attribute_level = a.attribute_level
1013 AND attribute_code = a.attribute_code
1014 AND inventory_item_id = a.inventory_item_id
1015 AND master_organization_id = a.master_organization_id
1016 AND NVL(attribute_category,l_fnd_g_char)=
1017 NVL(a.attribute_category,l_fnd_g_char) )
1018 WHERE a.attribute_level = 'ITEM'
1019 AND a.inst_interface_id IN (SELECT inst_interface_Id
1020 FROM csi_instance_interface
1021 WHERE transaction_identifier IS NULL
1022 AND trunc(source_transaction_date)
1023 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
1024 AND nvl(l_txn_to_date,trunc(source_transaction_date))
1025 AND process_status = 'X'
1026 AND source_system_name = nvl(p_source_system_name,source_system_name)
1027 AND parallel_worker_id = p_worker_id)
1028 AND a.attribute_id IS NULL
1029 AND a.attribute_level IS NOT NULL
1030 AND a.attribute_code IS NOT NULL
1031 AND a.inventory_item_id IS NOT NULL
1032 AND a.master_organization_id IS NOT NULL;
1033
1034 EXCEPTION
1035 WHEN others THEN
1036 fnd_message.set_name('CSI','CSI_ML_EXT_ATTR_ID_ERROR');
1037 fnd_message.set_token('SQL_ERROR',SQLERRM);
1038 x_error_message := fnd_message.get;
1039 x_return_status := l_fnd_unexpected;
1040 END;
1041
1042 EXCEPTION
1043 WHEN others THEN
1044 l_sql_error := SQLERRM;
1045 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
1046 fnd_message.set_token('API_NAME',l_api_name);
1047 fnd_message.set_token('SQL_ERROR',SQLERRM);
1048 x_error_message := fnd_message.get;
1049 x_return_status := l_fnd_unexpected;
1050
1051 END resolve_pw_ids;
1052
1053 PROCEDURE resolve_update_ids
1054 ( p_source_system_name IN VARCHAR2,
1055 p_txn_identifier IN VARCHAR2,
1056 x_return_status OUT NOCOPY VARCHAR2,
1057 x_error_message OUT NOCOPY VARCHAR2) IS
1058
1059 CURSOR pty1_intf_cur IS
1060 SELECT ip_interface_id
1061 FROM csi_i_party_interface cpi
1062 WHERE cpi.party_source_table = 'HZ_PARTIES'
1063 AND cpi.inst_interface_id IN (SELECT inst_interface_id
1064 FROM csi_instance_interface cii
1065 WHERE cii.transaction_identifier = p_txn_identifier)
1066 AND cpi.party_source_table = 'HZ_PARTIES';
1067
1068 CURSOR pty3_intf_cur IS
1069 SELECT ip_interface_id
1070 FROM csi_i_party_interface cpi
1071 WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
1072 FROM csi_instance_interface cii
1073 WHERE cii.transaction_identifier = p_txn_identifier
1074 AND cii.source_system_name = p_source_system_name);
1075
1076 TYPE NumTabType IS VARRAY(10000) OF NUMBER;
1077 ip_intf_id_upd1 NumTabType;
1078 ip_intf_id_upd3 NumTabType;
1079 max_buffer_size NUMBER := 1000;
1080
1081 l_api_name VARCHAR2(255) := 'CSI_ML_UTIL_PVT.RESOLVE_UPDATE_IDS';
1082 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1083 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
1084 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
1085 l_fnd_g_num NUMBER := FND_API.G_MISS_NUM;
1086 l_fnd_g_char VARCHAR2(1) := FND_API.G_MISS_CHAR;
1087 l_fnd_g_date DATE := FND_API.G_MISS_DATE;
1088 l_sql_error VARCHAR2(2000);
1089 g_derive_ids VARCHAR2(1);
1090
1091
1092 BEGIN
1093
1094 x_return_status := l_fnd_success;
1095
1096 UPDATE csi_instance_interface a
1097 SET pricing_attribute_id =
1098 (SELECT pricing_attribute_id
1099 FROM csi_i_pricing_attribs
1100 WHERE instance_id = a.instance_id
1101 AND pricing_context = a.pricing_context)
1102 WHERE transaction_identifier = p_txn_identifier
1103 AND source_system_name = p_source_system_name
1104 AND a.pricing_attribute_id IS NULL
1105 AND a.instance_id IS NOT NULL
1106 AND a.pricing_context IS NOT NULL;
1107
1108 UPDATE csi_instance_interface a
1109 SET instance_ou_id = (SELECT instance_ou_id
1110 FROM csi_i_org_assignments
1111 WHERE instance_id = a.instance_id
1112 AND operating_unit_id = a.operating_unit
1113 AND relationship_type_code = a.ou_relation_type)
1114 WHERE transaction_identifier = p_txn_identifier
1115 AND source_system_name = p_source_system_name
1116 AND a.instance_ou_id IS NULL
1117 AND a.instance_id IS NOT NULL
1118 AND a.operating_unit IS NOT NULL
1119 AND a.ou_relation_type IS NOT NULL;
1120
1121 OPEN pty1_intf_cur;
1122 LOOP
1123 FETCH pty1_intf_cur BULK COLLECT INTO
1124 ip_intf_id_upd1
1125 LIMIT max_buffer_size;
1126
1127 FORALL i1 in 1 .. ip_intf_id_upd1.count
1128 UPDATE csi_i_party_interface cpi
1129 SET cpi.party_id = (SELECT party_id
1130 FROM hz_parties
1131 WHERE party_name = cpi.party_name
1132 AND party_number = NVL(cpi.party_number,party_number))
1133 WHERE ip_interface_id=ip_intf_id_upd1(i1)
1134 AND cpi.party_id IS NULL
1135 AND cpi.party_name IS NOT NULL;
1136
1137 FORALL i2 in 1 .. ip_intf_id_upd1.count
1138 UPDATE csi_i_party_interface cpi
1139 SET cpi.contact_party_id =
1140 (SELECT party_id
1141 FROM hz_parties
1142 WHERE party_name = cpi.contact_party_name
1143 AND party_number = NVL(cpi.contact_party_number,party_number))
1144 WHERE ip_interface_id=ip_intf_id_upd1(i2)
1145 AND cpi.contact_party_id IS NULL
1146 AND cpi.contact_party_name IS NOT NULL;
1147
1148 COMMIT;
1149 EXIT WHEN pty1_intf_cur%NOTFOUND;
1150 END LOOP;
1151 COMMIT;
1152 CLOSE pty1_intf_cur;
1153
1154 UPDATE CSI_I_PARTY_INTERFACE cpi
1155 SET party_id = (SELECT vendor_id
1156 FROM po_vendors
1157 WHERE vendor_name = cpi.party_name)
1158 WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
1159 FROM csi_instance_interface cii
1160 WHERE cii.transaction_identifier = p_txn_identifier)
1161 AND cpi.party_source_table = 'PO_VENDORS'
1162 AND cpi.party_id IS NULL
1163 AND cpi.party_name IS NOT NULL;
1164
1165 UPDATE CSI_I_PARTY_INTERFACE cpi
1166 SET party_id = (SELECT person_id
1167 FROM per_all_people_f
1168 WHERE full_name = cpi.party_name)
1169 WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
1170 FROM csi_instance_interface cii
1171 WHERE cii.transaction_identifier = p_txn_identifier)
1172 AND cpi.party_source_table = 'EMPLOYEE'
1173 AND cpi.party_id IS NULL
1174 AND cpi.party_name IS NOT NULL;
1175
1176 UPDATE CSI_I_PARTY_INTERFACE cpi
1177 SET party_id = (SELECT team_id
1178 FROM jtf_rs_teams_vl
1179 WHERE team_name = cpi.party_name)
1180 WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
1181 FROM csi_instance_interface cii
1182 WHERE cii.transaction_identifier = p_txn_identifier)
1183 AND cpi.party_source_table = 'TEAM'
1184 AND cpi.party_id IS NULL
1185 AND cpi.party_name IS NOT NULL;
1186
1187 UPDATE CSI_I_PARTY_INTERFACE cpi
1188 SET party_id = (SELECT group_id
1189 FROM jtf_rs_groups_vl
1190 WHERE group_name = cpi.party_name)
1191 WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
1192 FROM csi_instance_interface cii
1193 WHERE cii.transaction_identifier = p_txn_identifier)
1194 AND cpi.party_source_table = 'GROUP'
1195 AND cpi.party_id IS NULL
1196 AND cpi.party_name IS NOT NULL;
1197
1198 OPEN pty3_intf_cur;
1199 LOOP
1200 FETCH pty3_intf_cur BULK COLLECT INTO
1201 ip_intf_id_upd3
1202 LIMIT max_buffer_size;
1203
1204 FORALL i1 in 1 .. ip_intf_id_upd3.count
1205 UPDATE csi_i_party_interface cpi
1206 SET cpi.party_account1_id=
1207 (SELECT cust_account_id
1208 FROM hz_cust_accounts
1209 WHERE account_number = cpi.party_account1_number
1210 AND party_id = cpi.party_id)
1211 WHERE ip_interface_id=ip_intf_id_upd3(i1)
1212 AND cpi.party_account1_id IS NULL
1213 AND cpi.party_account1_number IS NOT NULL
1214 AND cpi.party_id IS NOT NULL;
1215
1216 FORALL i2 in 1 .. ip_intf_id_upd3.count
1217 UPDATE csi_i_party_interface cpi
1218 SET cpi.party_account2_id=
1219 (SELECT cust_account_id
1220 FROM hz_cust_accounts
1221 WHERE account_number = cpi.party_account2_number
1222 AND party_id = cpi.party_id)
1223 WHERE ip_interface_id=ip_intf_id_upd3(i2)
1224 AND cpi.party_account2_id IS NULL
1225 AND cpi.party_account2_number IS NOT NULL
1226 AND cpi.party_id IS NOT NULL;
1227
1228 FORALL i3 in 1 .. ip_intf_id_upd3.count
1229 UPDATE csi_i_party_interface cpi
1230 SET cpi.party_account3_id=
1231 (SELECT cust_account_id
1232 FROM hz_cust_accounts
1233 WHERE account_number = cpi.party_account3_number
1234 AND party_id = cpi.party_id)
1235 WHERE ip_interface_id=ip_intf_id_upd3(i3)
1236 AND cpi.party_account3_id IS NULL
1237 AND cpi.party_account3_number IS NOT NULL
1238 AND cpi.party_id IS NOT NULL;
1239
1240 COMMIT;
1241 EXIT WHEN pty3_intf_cur%NOTFOUND;
1242 END LOOP;
1243 COMMIT;
1244 CLOSE pty3_intf_cur;
1245
1246 UPDATE csi_i_party_interface a
1247 SET instance_party_id = (SELECT instance_party_id
1248 FROM csi_i_parties
1249 WHERE party_id = a.party_id
1250 AND instance_id =
1251 (SELECT instance_id
1252 FROM csi_instance_interface cii
1253 WHERE cii.inst_interface_id=a.inst_interface_id)
1254 AND relationship_type_code =
1255 a.party_relationship_type_code
1256 AND contact_flag <>'Y')
1257 WHERE a.inst_interface_id IN (SELECT inst_interface_id
1258 FROM csi_instance_interface cii
1259 WHERE cii.transaction_identifier = p_txn_identifier
1260 AND cii.source_system_name = p_source_system_name)
1261 AND instance_party_id IS NULL
1262 AND a.party_id IS NOT NULL;
1263
1264 UPDATE csi_i_party_interface a
1265 SET contact_ip_id = (SELECT instance_party_id
1266 FROM csi_i_parties
1267 WHERE party_id = a.contact_party_id
1268 AND instance_id =
1269 (SELECT instance_id
1270 FROM csi_instance_interface cii
1271 WHERE cii.inst_interface_id=a.inst_interface_id)
1272 AND relationship_type_code =
1273 a.contact_party_rel_type
1274 AND contact_flag <>'Y')
1275 WHERE a.inst_interface_id IN (SELECT inst_interface_id
1276 FROM csi_instance_interface cii
1277 WHERE cii.transaction_identifier = p_txn_identifier
1278 AND cii.source_system_name = p_source_system_name)
1279 AND contact_ip_id IS NULL
1280 AND a.contact_party_id IS NOT NULL;
1281
1282 UPDATE csi_i_party_interface a
1283 SET instance_party_id = (SELECT instance_party_id
1284 FROM csi_i_parties
1285 WHERE instance_id =
1286 (SELECT instance_id
1287 FROM csi_instance_interface cii
1288 WHERE cii.inst_interface_id=a.inst_interface_id)
1289 AND relationship_type_code =
1290 a.party_relationship_type_code)
1291 WHERE a.inst_interface_id IN (SELECT inst_interface_id
1292 FROM csi_instance_interface cii
1293 WHERE cii.transaction_identifier = p_txn_identifier
1294 AND cii.source_system_name = p_source_system_name)
1295 AND party_relationship_type_code = 'OWNER'
1296 AND instance_party_id IS NULL;
1297
1298 UPDATE csi_i_party_interface a
1299 SET ip_account1_id=(SELECT ip_account_id
1300 FROM csi_ip_accounts
1301 WHERE instance_party_id = a.instance_party_id
1302 AND party_account_id = a.party_account1_id
1303 AND relationship_type_code =
1304 a.acct1_relationship_type_code)
1305 WHERE a.inst_interface_id IN (SELECT inst_interface_id
1306 FROM csi_instance_interface cii
1307 WHERE cii.transaction_identifier = p_txn_identifier
1308 AND cii.source_system_name = p_source_system_name)
1309 AND ip_account1_id IS NULL ;
1310
1311 UPDATE csi_i_party_interface a
1312 SET ip_account2_id = (SELECT ip_account_id
1313 FROM csi_ip_accounts
1314 WHERE instance_party_id = a.instance_party_id
1315 AND party_account_id = a.party_account2_id
1316 AND relationship_type_code =
1317 a.acct2_relationship_type_code)
1318 WHERE a.inst_interface_id IN (SELECT inst_interface_id
1319 FROM csi_instance_interface cii
1320 WHERE cii.transaction_identifier = p_txn_identifier
1321 AND cii.source_system_name = p_source_system_name)
1322 AND ip_account2_id IS NULL;
1323
1324 UPDATE csi_i_party_interface a
1325 SET ip_account3_id = (SELECT ip_account_id
1326 FROM csi_ip_accounts
1327 WHERE instance_party_id = a.instance_party_id
1328 AND party_account_id = a.party_account3_id
1329 AND relationship_type_code =
1330 a.acct3_relationship_type_code)
1331 WHERE a.inst_interface_id IN (SELECT inst_interface_id
1332 FROM csi_instance_interface cii
1333 WHERE cii.transaction_identifier = p_txn_identifier
1334 AND cii.source_system_name = p_source_system_name)
1335 AND ip_account3_id IS NULL;
1336
1337 UPDATE csi_i_party_interface a
1338 SET ip_account1_id = (SELECT ip_account_id
1339 FROM csi_ip_accounts
1340 WHERE instance_party_id = a.instance_party_id
1341 AND relationship_type_code =
1342 a.acct1_relationship_type_code)
1343 WHERE a.inst_interface_id IN (SELECT inst_interface_id
1344 FROM csi_instance_interface cii
1345 WHERE cii.transaction_identifier = p_txn_identifier
1346 AND cii.source_system_name = p_source_system_name)
1347 AND acct1_relationship_type_code = 'OWNER'
1348 AND ip_account1_id IS NULL;
1349
1350 UPDATE csi_i_party_interface a
1351 SET ip_account2_id = (SELECT ip_account_id
1352 FROM csi_ip_accounts
1353 WHERE instance_party_id = a.instance_party_id
1354 AND relationship_type_code =
1355 a.acct2_relationship_type_code)
1356 WHERE a.inst_interface_id IN (SELECT inst_interface_id
1357 FROM csi_instance_interface cii
1358 WHERE cii.transaction_identifier = p_txn_identifier
1359 AND cii.source_system_name = p_source_system_name)
1360 AND acct2_relationship_type_code = 'OWNER'
1361 AND ip_account2_id IS NULL;
1362
1363 UPDATE csi_i_party_interface a
1364 SET ip_account3_id = (SELECT ip_account_id
1365 FROM csi_ip_accounts
1366 WHERE instance_party_id = a.instance_party_id
1367 AND relationship_type_code =
1368 a.acct3_relationship_type_code)
1369 WHERE a.inst_interface_id IN (SELECT inst_interface_id
1370 FROM csi_instance_interface cii
1371 WHERE cii.transaction_identifier = p_txn_identifier
1372 AND cii.source_system_name = p_source_system_name)
1373 AND acct3_relationship_type_code = 'OWNER'
1374 AND ip_account3_id IS NULL;
1375
1376 UPDATE csi_iea_value_interface a
1377 SET attribute_id =(SELECT attribute_id
1378 FROM csi_i_extended_attribs
1379 WHERE attribute_level = a.attribute_level
1380 AND attribute_code = a.attribute_code)
1381 WHERE a.inst_interface_id IN (SELECT inst_interface_id
1382 FROM csi_instance_interface cii
1383 WHERE cii.transaction_identifier = p_txn_identifier
1384 AND cii.source_system_name = p_source_system_name)
1385 AND attribute_level = 'GLOBAL'
1386 AND attribute_id IS NULL;
1387
1388 UPDATE csi_iea_value_interface a
1389 SET attribute_id=(SELECT attribute_id
1390 FROM csi_i_extended_attribs
1391 WHERE attribute_level = a.attribute_level
1392 AND attribute_code = a.attribute_code
1393 AND inventory_item_id = a.inventory_item_id
1394 AND master_organization_id = a.master_organization_id
1395 AND NVL(attribute_category,l_fnd_g_char)=
1396 NVL(a.attribute_category,l_fnd_g_char))
1397 WHERE a.inst_interface_id IN (SELECT inst_interface_id
1398 FROM csi_instance_interface cii
1399 WHERE cii.transaction_identifier = p_txn_identifier
1400 AND cii.source_system_name = p_source_system_name)
1401 AND attribute_level = 'ITEM'
1402 AND attribute_id IS NULL;
1403
1404 UPDATE csi_iea_value_interface a
1405 SET attribute_value_id = (SELECT attribute_value_id
1406 FROM csi_iea_values
1407 WHERE attribute_id = a.attribute_id
1408 AND attribute_value = a.attribute_value
1409 AND instance_id =
1410 (SELECT cii1.instance_id
1411 FROM csi_instance_interface cii1
1412 WHERE cii1.inst_interface_id =a.inst_interface_id))
1413 WHERE a.inst_interface_id IN (SELECT inst_interface_id
1414 FROM csi_instance_interface cii
1415 WHERE cii.transaction_identifier = p_txn_identifier
1416 AND cii.source_system_name = p_source_system_name)
1417 AND attribute_value_id IS NULL;
1418
1419 EXCEPTION
1420 WHEN others THEN
1421 l_sql_error := SQLERRM;
1422 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
1423 fnd_message.set_token('API_NAME',l_api_name);
1424 fnd_message.set_token('SQL_ERROR',SQLERRM);
1425 x_error_message := fnd_message.get;
1426 x_return_status := l_fnd_unexpected;
1427
1428 END resolve_update_ids;
1429
1430 FUNCTION Get_Txn_Type_Id(P_Txn_Type IN VARCHAR2,
1431 P_App_Short_Name IN VARCHAR2) RETURN NUMBER IS
1432 l_Txn_Type_Id NUMBER;
1433
1434 CURSOR Txn_Type_Cur IS
1435 SELECT ctt.Transaction_Type_Id Transaction_Type_Id
1436 FROM CSI_Txn_Types ctt,
1437 FND_Application fa
1438 WHERE ctt.Source_Transaction_Type = P_Txn_Type
1439 AND fa.application_id = ctt.Source_Application_ID
1440 AND fa.Application_Short_Name = P_App_Short_Name;
1441 BEGIN
1442 OPEN Txn_Type_Cur;
1443 FETCH Txn_Type_Cur INTO l_Txn_Type_Id;
1444 CLOSE Txn_Type_Cur;
1445 RETURN l_Txn_Type_Id;
1446 END Get_Txn_Type_Id;
1447
1448 PROCEDURE log_create_errors (p_txn_from_date IN VARCHAR2,
1449 p_txn_to_date IN VARCHAR2,
1450 x_return_status OUT NOCOPY VARCHAR2,
1451 x_error_message OUT NOCOPY VARCHAR2) IS
1452
1453 l_txn_from_date DATE := to_date(p_txn_from_date, 'YYYY/MM/DD HH24:MI:SS');
1454 l_txn_to_date DATE := to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS');
1455
1456 CURSOR c_error is
1457 SELECT * from csi_instance_interface
1458 WHERE process_status = 'E'
1459 AND trunc(source_transaction_date) BETWEEN
1460 nvl(l_txn_from_date,trunc(source_transaction_date)) AND
1461 nvl(l_txn_to_date,trunc(source_transaction_date))
1462 AND parallel_worker_id IS NULL
1463 ORDER BY inst_interface_id;
1464
1465 r_error c_error%rowtype;
1466
1467 l_api_name VARCHAR2(255) := 'CSI_ML_UTIL_PVT.LOG_CREATE_ERRORS';
1468 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1469 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
1470 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
1471 l_sql_error VARCHAR2(2000);
1472 BEGIN
1473
1474 x_return_status := l_fnd_success;
1475
1476 FND_File.Put_Line(Fnd_File.LOG,'************BEGIN OF MASS LOAD ERRORS************');
1477 FND_File.Put_Line(Fnd_File.LOG,'INST_INTERFACE_ID ERROR TEXT');
1478 FND_File.Put_Line(Fnd_File.LOG,'---------------------- ----------------------------------------------------------');
1479 FOR r_error in c_error LOOP
1480 FND_File.Put_Line(Fnd_File.LOG,r_error.inst_interface_id||' '||r_error.error_text);
1481 END LOOP;
1482
1483 FND_File.Put_Line(Fnd_File.LOG,'************END OF MASS LOAD ERRORS************');
1484
1485 EXCEPTION
1486 WHEN others THEN
1487 l_sql_error := SQLERRM;
1488 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
1489 fnd_message.set_token('API_NAME',l_api_name);
1490 fnd_message.set_token('SQL_ERROR',SQLERRM);
1491 x_error_message := fnd_message.get;
1492 x_return_status := l_fnd_unexpected;
1493
1494 END log_create_errors;
1495
1496 PROCEDURE log_create_pw_errors (p_txn_from_date IN VARCHAR2,
1497 p_txn_to_date IN VARCHAR2,
1498 p_source_system_name IN VARCHAR2,
1499 p_worker_id IN NUMBER,
1500 x_return_status OUT NOCOPY VARCHAR2,
1501 x_error_message OUT NOCOPY VARCHAR2) IS
1502
1503
1504 CURSOR c_error (pc_txn_from_date IN DATE,
1505 pc_txn_to_date IN DATE,
1506 pc_source_system_name IN VARCHAR2,
1507 pc_worker_id IN NUMBER) is
1508 SELECT * from csi_instance_interface
1509 WHERE process_status = 'E'
1510 AND trunc(source_transaction_date) BETWEEN
1511 nvl(pc_txn_from_date,trunc(source_transaction_date)) AND
1512 nvl(pc_txn_to_date,trunc(source_transaction_date))
1513 AND parallel_worker_id = pc_worker_id
1514 AND source_system_name = nvl(pc_source_system_name,source_system_name)
1515 ORDER BY inst_interface_id;
1516
1517 r_error c_error%rowtype;
1518
1519 l_api_name VARCHAR2(255) := 'CSI_ML_UTIL_PVT.LOG_CREATE_PW_ERRORS';
1520 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1521 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
1522 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
1523 l_sql_error VARCHAR2(2000);
1524 l_txn_from_date DATE := to_date(p_txn_from_date, 'YYYY/MM/DD HH24:MI:SS');
1525 l_txn_to_date DATE := to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS');
1526 BEGIN
1527
1528 x_return_status := l_fnd_success;
1529
1530 FND_File.Put_Line(Fnd_File.LOG,'************BEGIN OF MASS LOAD ERRORS************');
1531 FND_File.Put_Line(Fnd_File.LOG,'INST_INTERFACE_ID ERROR TEXT');
1532 FND_File.Put_Line(Fnd_File.LOG,'---------------------- ----------------------------------------------------------');
1533 FOR r_error in c_error(l_txn_from_date,
1534 l_txn_to_date,
1535 p_source_system_name,
1536 p_worker_id) LOOP
1537 FND_File.Put_Line(Fnd_File.LOG,r_error.inst_interface_id||' '||r_error.error_text);
1538 END LOOP;
1539
1540 FND_File.Put_Line(Fnd_File.LOG,'************END OF MASS LOAD ERRORS************');
1541
1542 EXCEPTION
1543 WHEN others THEN
1544 l_sql_error := SQLERRM;
1545 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
1546 fnd_message.set_token('API_NAME',l_api_name);
1547 fnd_message.set_token('SQL_ERROR',SQLERRM);
1548 x_error_message := fnd_message.get;
1549 x_return_status := l_fnd_unexpected;
1550
1551 END log_create_pw_errors;
1552
1553 PROCEDURE set_pty_process_status (p_txn_from_date IN VARCHAR2,
1554 p_txn_to_date IN VARCHAR2,
1555 x_return_status OUT NOCOPY VARCHAR2,
1556 x_error_message OUT NOCOPY VARCHAR2) IS
1557
1558
1559 l_api_name VARCHAR2(255) := 'CSI_ML_UTIL_PVT.SET_PTY_PROCESS_STATUS';
1560 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1561 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
1562 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
1563 l_sql_error VARCHAR2(2000);
1564 l_txn_from_date DATE := to_date(p_txn_from_date, 'YYYY/MM/DD HH24:MI:SS');
1565 l_txn_to_date DATE := to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS');
1566 BEGIN
1567
1568 x_return_status := l_fnd_success;
1569
1570 EXCEPTION
1571 WHEN others THEN
1572 l_sql_error := SQLERRM;
1573 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
1574 fnd_message.set_token('API_NAME',l_api_name);
1575 fnd_message.set_token('SQL_ERROR',SQLERRM);
1576 x_error_message := fnd_message.get;
1577 x_return_status := l_fnd_unexpected;
1578
1579 END set_pty_process_status;
1580
1581 PROCEDURE set_ext_process_status (p_txn_from_date IN VARCHAR2,
1582 p_txn_to_date IN VARCHAR2,
1583 x_return_status OUT NOCOPY VARCHAR2,
1584 x_error_message OUT NOCOPY VARCHAR2) IS
1585
1586
1587 l_api_name VARCHAR2(255) := 'CSI_ML_UTIL_PVT.SET_EXT_PROCESS_STATUS';
1588 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1589 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
1590 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
1591 l_sql_error VARCHAR2(2000);
1592 l_txn_from_date DATE := to_date(p_txn_from_date, 'YYYY/MM/DD HH24:MI:SS');
1593 l_txn_to_date DATE := to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS');
1594 BEGIN
1595
1596 x_return_status := l_fnd_success;
1597
1598 EXCEPTION
1599 WHEN others THEN
1600 l_sql_error := SQLERRM;
1601 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
1602 fnd_message.set_token('API_NAME',l_api_name);
1603 fnd_message.set_token('SQL_ERROR',SQLERRM);
1604 x_error_message := fnd_message.get;
1605 x_return_status := l_fnd_unexpected;
1606
1607 END set_ext_process_status;
1608
1609 PROCEDURE resolve_rel_ids
1610 ( p_source_system IN VARCHAR2,
1611 p_txn_from_date IN varchar2,
1612 p_txn_to_date IN varchar2,
1613 x_return_status OUT NOCOPY VARCHAR2,
1614 x_error_message OUT NOCOPY VARCHAR2) IS
1615
1616
1617 TYPE NumTabType IS VARRAY(10000) OF NUMBER;
1618 rel_intf_id_upd NumTabType;
1619 object_id_upd NumTabType;
1620 subject_id_upd NumTabType;
1621 max_buffer_size NUMBER := 1000;
1622
1623 l_api_name VARCHAR2(255) := 'CSI_ML_UTIL_PVT.RESOLVE_REL_IDS';
1624 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1625 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
1626 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
1627 l_fnd_g_num NUMBER := FND_API.G_MISS_NUM;
1628 l_fnd_g_char VARCHAR2(1) := FND_API.G_MISS_CHAR;
1629 l_fnd_g_date DATE := FND_API.G_MISS_DATE;
1630 l_sql_error VARCHAR2(2000);
1631 BEGIN
1632
1633
1634 x_return_status := l_fnd_success;
1635 -- Get the source system id for all of the rows
1636 LOOP
1637 COMMIT;
1638
1639 --Changed for bug 5875269
1640 UPDATE csi_ii_relation_interface cir
1641 SET cir.object_id = decode(cir.object_id,NULL
1642 ,(SELECT cii.instance_id
1643 FROM csi_instance_interface cii
1644 WHERE cii.inst_interface_id = cir.object_interface_id)
1645 ,cir.object_id ),
1646 cir.subject_id = decode(cir.subject_id,NULL
1647 ,(SELECT cii.instance_id
1648 FROM csi_instance_interface cii
1649 WHERE cii.inst_interface_id = cir.subject_interface_id)
1650 ,cir.subject_id )
1651 WHERE ((cir.object_id IS NULL AND EXISTS (SELECT 'x' FROM csi_instance_interface WHERE
1652 inst_interface_id = cir.object_interface_id AND instance_id IS NOT NULL))
1653 OR (cir.subject_id IS NULL AND EXISTS (SELECT 'x' FROM csi_instance_interface WHERE
1654 inst_interface_id = cir.subject_interface_id AND instance_id IS NOT NULL)))
1655 AND cir.process_status='R'
1656 AND ROWNUM<10001;
1657
1658 EXIT WHEN SQL%NOTFOUND;
1659
1660 END LOOP;
1661 COMMIT;
1662
1663
1664 EXCEPTION
1665 WHEN others THEN
1666 l_sql_error := SQLERRM;
1667 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
1668 fnd_message.set_token('API_NAME',l_api_name);
1669 fnd_message.set_token('SQL_ERROR',SQLERRM);
1670 FND_File.Put_Line(Fnd_File.LOG,'Error is ' || l_sql_error);
1671 x_error_message := fnd_message.get;
1672 x_return_status := l_fnd_unexpected;
1673
1674 END resolve_rel_ids;
1675
1676 PROCEDURE Get_Next_Level
1677 (p_object_id IN NUMBER,
1678 p_rel_tbl OUT NOCOPY csi_ml_util_pvt.ii_rel_interface_tbl
1679 ) IS
1680 --
1681 CURSOR REL_CUR IS
1682 SELECT rel_interface_id
1683 ,relationship_type_code
1684 ,object_id
1685 ,subject_id
1686 FROM csi_ii_relation_interface cir
1687 WHERE cir.object_id = p_object_id
1688 AND cir.process_status = 'R'
1689 AND cir.relationship_type_code<>'CONNECTED-TO';
1690 --
1691 l_ctr NUMBER := 0;
1692 BEGIN
1693 FOR rel in REL_CUR LOOP
1694 l_ctr := l_ctr + 1;
1695 p_rel_tbl(l_ctr).rel_interface_id := rel.rel_interface_id;
1696 p_rel_tbl(l_ctr).relationship_type_code := rel.relationship_type_code;
1697 p_rel_tbl(l_ctr).object_id := rel.object_id;
1698 p_rel_tbl(l_ctr).subject_id := rel.subject_id;
1699 END LOOP;
1700 END Get_Next_Level;
1701
1702
1703 PROCEDURE Get_Children
1704 (p_object_id IN NUMBER,
1705 p_rel_tbl OUT NOCOPY csi_ml_util_pvt.ii_rel_interface_tbl
1706 ) IS
1707 --
1708 l_rel_tbl csi_ml_util_pvt.ii_rel_interface_tbl;
1709 l_rel_tbl_next_lvl csi_ml_util_pvt.ii_rel_interface_tbl;
1710 l_rel_tbl_temp csi_ml_util_pvt.ii_rel_interface_tbl;
1711 l_rel_tbl_final csi_ml_util_pvt.ii_rel_interface_tbl;
1712 l_next_ind NUMBER := 0;
1713 l_final_ind NUMBER := 0;
1714 l_ctr NUMBER := 0;
1715 l_found NUMBER;
1716 BEGIN
1717 csi_ml_util_pvt.Get_Next_Level
1718 ( p_object_id => p_object_id,
1719 p_rel_tbl => l_rel_tbl
1720 );
1721
1722 <<Next_Level>>
1723
1724 l_rel_tbl_next_lvl.delete;
1725 l_next_ind := 0;
1726 --
1727 IF l_rel_tbl.count > 0 THEN
1728 FOR l_ind IN l_rel_tbl.FIRST .. l_rel_tbl.LAST LOOP
1729 l_final_ind := l_final_ind + 1;
1730 l_rel_tbl_final(l_final_ind) := l_rel_tbl(l_ind);
1731 /* get the next level using this Subject ID as the parent */
1732 csi_ml_util_pvt.Get_Next_Level
1733 ( p_object_id => l_rel_tbl(l_ind).subject_id,
1734 p_rel_tbl => l_rel_tbl_temp
1735 );
1736 --
1737 IF l_rel_tbl_temp.count > 0 THEN
1738 FOR l_temp_ind IN l_rel_tbl_temp.FIRST .. l_rel_tbl_temp.LAST LOOP
1739 IF l_rel_tbl_final.count > 0 THEN
1740 l_found := 0;
1741 FOR i IN l_rel_tbl_final.FIRST .. l_rel_tbl_final.LAST LOOP
1742 IF l_rel_tbl_final(i).object_id = l_rel_tbl_temp(l_temp_ind).object_id THEN
1743 l_found := 1;
1744 exit;
1745 END IF;
1746 END LOOP;
1747 END IF;
1748 IF l_found = 0 THEN
1749 l_next_ind := l_next_ind + 1;
1750 l_rel_tbl_next_lvl(l_next_ind) := l_rel_tbl_temp(l_temp_ind);
1751 END IF;
1752 END LOOP;
1753 END IF;
1754 END LOOP;
1755 --
1756 IF l_rel_tbl_next_lvl.count > 0 THEN
1757 l_rel_tbl.DELETE;
1758 l_rel_tbl := l_rel_tbl_next_lvl;
1759 --
1760 goto Next_Level;
1761 END IF;
1762 END IF;
1763 --
1764 p_rel_tbl := l_rel_tbl_final;
1765
1766 END Get_Children;
1767
1768
1769 PROCEDURE Get_top_most_parent
1770 ( p_subject_id IN NUMBER,
1771 p_rel_type_code IN VARCHAR2,
1772 p_process_status IN VARCHAR2,
1773 p_object_id OUT NOCOPY NUMBER
1774 ) IS
1775 l_object_id NUMBER;
1776 l_subject_id NUMBER := p_subject_id;
1777 BEGIN
1778 IF p_rel_type_code IS NULL OR
1779 p_subject_id IS NULL
1780 THEN
1781 l_object_id := -9999;
1782 p_object_id := l_object_id;
1783 RETURN;
1784 END IF;
1785 LOOP
1786 BEGIN
1787 SELECT cir.object_id
1788 INTO l_object_id
1789 FROM csi_ii_relation_interface cir
1790 WHERE cir.subject_id = l_subject_id
1791 AND cir.relationship_type_code = p_rel_type_code
1792 AND nvl(cir.relationship_end_date,(sysdate+1)) > sysdate
1793 AND EXISTS (SELECT 'x'
1794 FROM csi_item_instances cii
1795 WHERE cii.instance_id = cir.object_id
1796 AND cii.location_type_code NOT IN ('INVENTORY','PO','IN_TRANSIT','WIP','PROJECT')
1797 );
1798 l_subject_id := l_object_id;
1799 EXCEPTION
1800 WHEN NO_DATA_FOUND THEN
1801 p_object_id := l_subject_id;
1802 EXIT;
1803 END;
1804 END LOOP;
1805 END Get_top_most_parent;
1806
1807
1808 PROCEDURE Validate_relationship(
1809 x_msg_data OUT NOCOPY VARCHAR2,
1810 x_return_status OUT NOCOPY VARCHAR2,
1811 p_mode IN VARCHAR2,
1812 p_worker_id IN NUMBER,
1813 p_txn_from_date IN varchar2,
1814 p_txn_to_date IN varchar2,
1815 p_source_system_name IN VARCHAR2
1816 )
1817 IS
1818 l_exists VARCHAR2(1);
1819 l_quantity NUMBER;
1820 l_rel_id_array dbms_sql.Number_Table;
1821 l_status_array dbms_sql.Varchar2_Table;
1822 l_error_array dbms_sql.Varchar2_Table;
1823 l_iface_error_text VARCHAR2(2000);
1824 rel_row NUMBER;
1825 l_upd_stmt VARCHAR2(2000);
1826 l_num_of_rows NUMBER;
1827 l_upd_obj_id NUMBER;
1828
1829 l_instance_rec csi_datastructures_pub.instance_rec;
1830 l_instance_id_lst csi_datastructures_pub.id_tbl;
1831 l_item_attribute_tbl csi_item_instance_pvt.item_attribute_tbl;
1832 l_location_tbl csi_item_instance_pvt.location_tbl;
1833 l_generic_id_tbl csi_item_instance_pvt.generic_id_tbl;
1834 l_lookup_tbl csi_item_instance_pvt.lookup_tbl;
1835 l_party_tbl csi_datastructures_pub.party_tbl;
1836 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
1837 l_ins_count_rec csi_item_instance_pvt.ins_count_rec;
1838 l_txn_rec csi_datastructures_pub.transaction_rec;
1839 l_ii_relationship_rec_tab csi_ml_util_pvt.ii_relationship_rec_tab;
1840 l_rel_hist_tbl csi_diagnostics_pkg.T_NUM;
1841 l_txn_id_tbl csi_diagnostics_pkg.T_NUM;
1842 l_config_root_tbl csi_diagnostics_pkg.T_NUM;
1843 l_msg_data VARCHAR2(2000);
1844 l_msg_index NUMBER;
1845 l_msg_count NUMBER;
1846 x_msg_count NUMBER;
1847 l_dummy NUMBER;
1848 l_ins NUMBER;
1849 l_txn_type_id NUMBER;
1850 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1851 l_login_id NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
1852 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1853 SKIP_ERROR EXCEPTION;
1854 px_oks_txn_inst_tbl oks_ibint_pub.txn_instance_tbl;
1855 px_child_inst_tbl csi_item_instance_grp.child_inst_tbl;
1856
1857 CURSOR c_id (pc_parallel_worker_id IN NUMBER, l_txn_from_date DATE, l_txn_to_date DATE) IS
1858 SELECT rel_interface_id
1859 ,process_status
1860 ,object_id
1861 ,subject_id
1862 ,relationship_type_code
1863 ,relationship_end_date
1864 FROM csi_ii_relation_interface
1865 WHERE trunc(source_transaction_date)
1866 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
1867 AND nvl(l_txn_to_date,trunc(source_transaction_date))
1868 AND nvl(transaction_identifier,'-1') = '-1'
1869 AND process_status = 'R'
1870 AND source_system_name = nvl(p_source_system_name,source_system_name)
1871 AND parallel_worker_id = pc_parallel_worker_id;
1872
1873
1874 CURSOR upd_ins_csr (pc_parallel_worker_id IN NUMBER, l_txn_from_date DATE, l_txn_to_date DATE) IS
1875 SELECT rel_interface_id
1876 ,subject_id
1877 ,relationship_type_code
1878 FROM csi_ii_relation_interface
1879 WHERE trunc(source_transaction_date)
1880 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
1881 AND nvl(l_txn_to_date,trunc(source_transaction_date))
1882 AND nvl(transaction_identifier,'-1') = '-1'
1883 AND process_status = 'V'
1884 AND source_system_name = nvl(p_source_system_name,source_system_name)
1885 AND parallel_worker_id = pc_parallel_worker_id;
1886
1887 CURSOR re_upd_csr (pc_parallel_worker_id IN NUMBER, l_txn_from_date DATE, l_txn_to_date DATE) IS
1888 SELECT rel_interface_id
1889 ,subject_id
1890 ,relationship_type_code
1891 ,config_root_node
1892 FROM csi_ii_relation_interface
1893 WHERE trunc(source_transaction_date)
1894 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
1895 AND nvl(l_txn_to_date,trunc(source_transaction_date))
1896 AND nvl(transaction_identifier,'-1') = '-1'
1897 AND process_status = 'U'
1898 AND source_system_name = nvl(p_source_system_name,source_system_name)
1899 AND parallel_worker_id = pc_parallel_worker_id;
1900
1901 CURSOR ins_rel_csr (pc_parallel_worker_id IN NUMBER, l_txn_from_date DATE, l_txn_to_date DATE) IS
1902 SELECT rel_interface_id
1903 ,subject_id
1904 ,relationship_type_code
1905 ,object_id
1906 ,position_reference
1907 ,relationship_start_date
1908 ,relationship_end_date
1909 ,display_order
1910 ,mandatory_flag
1911 ,context
1912 ,attribute1
1913 ,attribute2
1914 ,attribute3
1915 ,attribute4
1916 ,attribute5
1917 ,attribute6
1918 ,attribute7
1919 ,attribute8
1920 ,attribute9
1921 ,attribute10
1922 ,attribute11
1923 ,attribute12
1924 ,attribute13
1925 ,attribute14
1926 ,attribute15
1927 FROM csi_ii_relation_interface
1928 WHERE trunc(source_transaction_date)
1929 BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
1930 AND nvl(l_txn_to_date,trunc(source_transaction_date))
1931 AND nvl(transaction_identifier,'-1') = '-1'
1932 AND process_status = 'I'
1933 AND source_system_name = nvl(p_source_system_name,source_system_name)
1934 AND parallel_worker_id = pc_parallel_worker_id;
1935
1936 l_txn_from_date DATE;
1937 l_txn_to_date DATE;
1938
1939 BEGIN
1940
1941 l_txn_from_date := to_date(p_txn_from_date, 'YYYY/MM/DD HH24:MI:SS');
1942 l_txn_to_date := to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS');
1943
1944
1945 FND_File.Put_Line(Fnd_File.LOG,'Mode p_mode is :'||p_mode);
1946 FND_File.Put_Line(Fnd_File.LOG,'Worker id p_worker_id is :'||p_worker_id);
1947 IF p_mode='VALIDATE'
1948 THEN
1949 FND_File.Put_Line(Fnd_File.LOG,'Inside for Validate mode');
1950 rel_row:=0;
1951 FOR r_id IN c_id (p_worker_id, l_txn_from_date, l_txn_to_date ) LOOP
1952 l_iface_error_text := NULL;
1953 rel_row:=rel_row+1;
1954 FND_MSG_PUB.initialize;
1955 BEGIN
1956
1957 -- 1. Validate relationship_type_code
1958 BEGIN
1959 SELECT 'x'
1960 INTO l_exists
1961 FROM csi_ii_relation_types
1962 WHERE relationship_type_code=r_id.relationship_type_code;
1963 EXCEPTION
1964 WHEN NO_DATA_FOUND THEN
1965 fnd_message.set_name('CSI', 'CSI_INVALID_RELSHIP_CODE');
1966 fnd_message.set_token('RELATIONSHIP_TYPE_CODE',r_id.relationship_type_code);
1967 fnd_msg_pub.add;
1968 l_msg_index := 1;
1969 fnd_msg_pub.count_and_get
1970 (p_count => x_msg_count,
1971 p_data => x_msg_data
1972 );
1973 l_msg_count := x_msg_count;
1974 WHILE l_msg_count > 0
1975 LOOP
1976 x_msg_data := fnd_msg_pub.get
1977 (l_msg_index,
1978 fnd_api.g_false
1979 );
1980 csi_gen_utility_pvt.put_line( ' MESSAGE DATA = '||x_msg_data);
1981 l_msg_index := l_msg_index + 1;
1982 l_msg_count := l_msg_count - 1;
1983 END LOOP;
1984 l_iface_error_text := substr(x_msg_data,1,2000);
1985 RAISE SKIP_ERROR;
1986 END;
1987
1988
1989
1990 -- 2. Validate object_id
1991 IF r_id.object_id IS NOT NULL
1992 THEN
1993 BEGIN
1994 SELECT quantity
1995 INTO l_quantity
1996 FROM csi_item_instances
1997 WHERE instance_id=r_id.object_id
1998 AND (SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE));
1999
2000 IF l_quantity <> 1
2001 THEN
2002 fnd_message.set_name('CSI', 'CSI_NON_ATO_PTO_ITEM');
2003 fnd_message.set_token('OBJECT_ID',r_id.object_id);
2004 fnd_msg_pub.add;
2005 l_msg_index := 1;
2006 fnd_msg_pub.count_and_get
2007 (p_count => x_msg_count,
2008 p_data => x_msg_data
2009 );
2010 l_msg_count := x_msg_count;
2011 WHILE l_msg_count > 0
2012 LOOP
2013 x_msg_data := fnd_msg_pub.get
2014 (l_msg_index,
2015 fnd_api.g_false
2016 );
2017 csi_gen_utility_pvt.put_line( ' MESSAGE DATA = '||x_msg_data);
2018 l_msg_index := l_msg_index + 1;
2019 l_msg_count := l_msg_count - 1;
2020 END LOOP;
2021 l_iface_error_text := substr(x_msg_data,1,2000);
2022 RAISE SKIP_ERROR;
2023 END IF;
2024 EXCEPTION
2025 WHEN NO_DATA_FOUND THEN
2026 fnd_message.set_name('CSI', 'CSI_EXPIRED_OBJECT');
2027 fnd_message.set_token('OBJECT_ID',r_id.object_id);
2028 fnd_msg_pub.add;
2029 l_msg_index := 1;
2030 fnd_msg_pub.count_and_get
2031 (p_count => x_msg_count,
2032 p_data => x_msg_data
2033 );
2034 l_msg_count := x_msg_count;
2035 WHILE l_msg_count > 0
2036 LOOP
2037 x_msg_data := fnd_msg_pub.get
2038 (l_msg_index,
2039 fnd_api.g_false
2040 );
2041 csi_gen_utility_pvt.put_line( ' MESSAGE DATA = '||x_msg_data);
2042 l_msg_index := l_msg_index + 1;
2043 l_msg_count := l_msg_count - 1;
2044 END LOOP;
2045 l_iface_error_text := substr(x_msg_data,1,2000);
2046 RAISE SKIP_ERROR;
2047 END;
2048 -- 2.1 Validate MACD lock functionality for object_id
2049 IF csi_item_instance_pvt.check_item_instance_lock
2050 ( p_instance_id => r_id.object_id)
2051 THEN
2052 fnd_message.set_name('CSI','CSI_LOCKED_INSTANCE');
2053 fnd_message.set_token('INSTANCE_ID',r_id.object_id);
2054 fnd_msg_pub.add;
2055 l_msg_index := 1;
2056 fnd_msg_pub.count_and_get
2057 (p_count => x_msg_count,
2058 p_data => x_msg_data
2059 );
2060 l_msg_count := x_msg_count;
2061 WHILE l_msg_count > 0
2062 LOOP
2063 x_msg_data := fnd_msg_pub.get
2064 (l_msg_index,
2065 fnd_api.g_false
2066 );
2067 csi_gen_utility_pvt.put_line( ' MESSAGE DATA = '||x_msg_data);
2068 l_msg_index := l_msg_index + 1;
2069 l_msg_count := l_msg_count - 1;
2070 END LOOP;
2071 l_iface_error_text := substr(x_msg_data,1,2000);
2072 RAISE SKIP_ERROR;
2073 END IF;
2074 END IF;
2075
2076 -- 3. Validate subject_id
2077 IF ((r_id.subject_id IS NOT NULL) AND
2078 (r_id.subject_id <> fnd_api.g_miss_num))
2079 THEN
2080 BEGIN
2081 SELECT 'x'
2082 INTO l_exists
2083 FROM csi_item_instances
2084 WHERE instance_id=r_id.subject_id
2085 AND (SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE));
2086 EXCEPTION
2087 WHEN NO_DATA_FOUND THEN
2088 fnd_message.set_name('CSI', 'CSI_EXPIRED_SUBJECT');
2089 fnd_message.set_token('SUBJECT_ID',r_id.subject_id);
2090 fnd_msg_pub.add;
2091 l_msg_index := 1;
2092 fnd_msg_pub.count_and_get
2093 (p_count => x_msg_count,
2094 p_data => x_msg_data
2095 );
2096 l_msg_count := x_msg_count;
2097 WHILE l_msg_count > 0
2098 LOOP
2099 x_msg_data := fnd_msg_pub.get
2100 (l_msg_index,
2101 fnd_api.g_false
2102 );
2103 csi_gen_utility_pvt.put_line( ' MESSAGE DATA = '||x_msg_data);
2104 l_msg_index := l_msg_index + 1;
2105 l_msg_count := l_msg_count - 1;
2106 END LOOP;
2107 l_iface_error_text := substr(x_msg_data,1,2000);
2108 RAISE SKIP_ERROR;
2109 END;
2110 -- 3.1 Validate MACD lock functionality for subject_id
2111 IF csi_item_instance_pvt.check_item_instance_lock
2112 ( p_instance_id => r_id.subject_id)
2113 THEN
2114 fnd_message.set_name('CSI','CSI_LOCKED_INSTANCE');
2115 fnd_message.set_token('INSTANCE_ID',r_id.subject_id);
2116 fnd_msg_pub.add;
2117 l_msg_index := 1;
2118 fnd_msg_pub.count_and_get
2119 (p_count => x_msg_count,
2120 p_data => x_msg_data
2121 );
2122 l_msg_count := x_msg_count;
2123 WHILE l_msg_count > 0
2124 LOOP
2125 x_msg_data := fnd_msg_pub.get
2126 (l_msg_index,
2127 fnd_api.g_false
2128 );
2129 csi_gen_utility_pvt.put_line( ' MESSAGE DATA = '||x_msg_data);
2130 l_msg_index := l_msg_index + 1;
2131 l_msg_count := l_msg_count - 1;
2132 END LOOP;
2133 l_iface_error_text := substr(x_msg_data,1,2000);
2134 RAISE SKIP_ERROR;
2135 END IF;
2136 END IF;
2137
2138 -- 4. Validate active_end_date
2139 IF ((r_id.relationship_end_date IS NOT NULL) AND
2140 (r_id.relationship_end_date <> fnd_api.g_miss_date))
2141 THEN
2142 fnd_message.set_name('CSI', 'CSI_ACTIVE_END_DATE');
2143 fnd_message.set_token('ACTIVE_END_DATE',r_id.relationship_end_date);
2144 fnd_msg_pub.add;
2145 l_msg_index := 1;
2146 fnd_msg_pub.count_and_get
2147 (p_count => x_msg_count,
2148 p_data => x_msg_data
2149 );
2150 l_msg_count := x_msg_count;
2151 WHILE l_msg_count > 0
2152 LOOP
2153 x_msg_data := fnd_msg_pub.get
2154 (l_msg_index,
2155 fnd_api.g_false
2156 );
2157 csi_gen_utility_pvt.put_line( ' MESSAGE DATA = '||x_msg_data);
2158 l_msg_index := l_msg_index + 1;
2159 l_msg_count := l_msg_count - 1;
2160 END LOOP;
2161 l_iface_error_text := substr(x_msg_data,1,2000);
2162 RAISE SKIP_ERROR;
2163 END IF;
2164 -- 5. Validate
2165 IF r_id.relationship_type_code <> 'CONNECTED-TO'
2166 THEN
2167 -- 5.1 Validate for existence of subject_id (subject_exists routine)
2168 BEGIN
2169 SELECT 'x'
2170 INTO l_exists
2171 FROM csi_ii_relationships
2172 WHERE subject_id=r_id.subject_id
2173 AND relationship_type_code = r_id.relationship_type_code
2174 AND (active_end_date IS NULL OR active_end_date > SYSDATE)
2175 AND ROWNUM=1;
2176 fnd_message.set_name('CSI','CSI_SUB_RELCODE_EXIST');
2177 fnd_message.set_token('RELATIONSHIP_TYPE_CODE',r_id.relationship_type_code);
2178 fnd_message.set_token('SUBJECT_ID',r_id.subject_id);
2179 fnd_msg_pub.add;
2180 l_msg_index := 1;
2181 fnd_msg_pub.count_and_get
2182 (p_count => x_msg_count,
2183 p_data => x_msg_data
2184 );
2185 l_msg_count := x_msg_count;
2186 WHILE l_msg_count > 0
2187 LOOP
2188 x_msg_data := fnd_msg_pub.get
2189 (l_msg_index,
2190 fnd_api.g_false
2191 );
2192 csi_gen_utility_pvt.put_line( ' MESSAGE DATA = '||x_msg_data);
2193 l_msg_index := l_msg_index + 1;
2194 l_msg_count := l_msg_count - 1;
2195 END LOOP;
2196 l_iface_error_text := substr(x_msg_data,1,2000);
2197 RAISE SKIP_ERROR;
2198 EXCEPTION
2199 WHEN NO_DATA_FOUND THEN
2200 NULL;
2201 END;
2202 -- 5.2 Validate check for object
2203 csi_ii_relationships_pvt.check_for_object
2204 (p_subject_id =>r_id.subject_id,
2205 p_object_id =>r_id.object_id,
2206 p_relationship_type_code =>r_id.relationship_type_code,
2207 x_return_status =>x_return_status,
2208 x_msg_count =>x_msg_count,
2209 x_msg_data =>x_msg_data
2210 );
2211 IF x_return_status<>fnd_api.g_ret_sts_success THEN
2212 l_msg_index := 1;
2213 fnd_msg_pub.count_and_get
2214 (p_count => x_msg_count,
2215 p_data => x_msg_data
2216 );
2217 l_msg_count := x_msg_count;
2218 WHILE l_msg_count > 0
2219 LOOP
2220 x_msg_data := fnd_msg_pub.get
2221 (l_msg_index,
2222 fnd_api.g_false
2223 );
2224 csi_gen_utility_pvt.put_line( ' MESSAGE DATA = '||x_msg_data);
2225 l_msg_index := l_msg_index + 1;
2226 l_msg_count := l_msg_count - 1;
2227 END LOOP;
2228 l_iface_error_text := substr(x_msg_data,1,2000);
2229 RAISE SKIP_ERROR;
2230 END IF;
2231 ELSIF r_id.relationship_type_code='CONNECTED-TO'
2232 THEN
2233 BEGIN
2234 SELECT 'x'
2235 INTO l_exists
2236 FROM csi_ii_relationships
2237 WHERE (( subject_id=r_id.object_id AND
2238 object_id=r_id.subject_id)
2239 OR ( subject_id=r_id.subject_id AND
2240 object_id=r_id.object_id))
2241 AND relationship_type_code = r_id.relationship_type_code
2242 AND (active_end_date IS NULL OR active_end_date > SYSDATE)
2243 AND ROWNUM = 1;
2244 fnd_message.set_name('CSI','CSI_RELATIONSHIP_EXISTS');
2245 fnd_message.set_token('RELATIONSHIP_TYPE',r_id.relationship_type_code);
2246 fnd_message.set_token('SUBJECT_ID',r_id.subject_id);
2247 fnd_message.set_token('OBJECT_ID',r_id.object_id);
2248 fnd_msg_pub.add;
2249 l_msg_index := 1;
2250 fnd_msg_pub.count_and_get
2251 (p_count => x_msg_count,
2252 p_data => x_msg_data
2253 );
2254 l_msg_count := x_msg_count;
2255 WHILE l_msg_count > 0
2256 LOOP
2257 x_msg_data := fnd_msg_pub.get
2258 (l_msg_index,
2259 fnd_api.g_false
2260 );
2261 csi_gen_utility_pvt.put_line( ' MESSAGE DATA = '||x_msg_data);
2262 l_msg_index := l_msg_index + 1;
2263 l_msg_count := l_msg_count - 1;
2264 END LOOP;
2265 l_iface_error_text := substr(x_msg_data,1,2000);
2266 RAISE SKIP_ERROR;
2267 EXCEPTION
2268 WHEN NO_DATA_FOUND THEN
2269 NULL;
2270 END;
2271 IF csi_ii_relationships_pvt.Is_link_type
2272 (p_instance_id => r_id.object_id )
2273 THEN
2274 IF csi_ii_relationships_pvt.relationship_for_link
2275 (p_instance_id => r_id.object_id
2276 ,p_mode => 'CREATE'
2277 ,p_relationship_id => NULL )
2278 THEN
2279 fnd_message.set_name('CSI','CSI_LINK_EXISTS');
2280 fnd_message.set_token('INSTANCE_ID',r_id.object_id);
2281 fnd_msg_pub.add;
2282 l_msg_index := 1;
2283 fnd_msg_pub.count_and_get
2284 (p_count => x_msg_count,
2285 p_data => x_msg_data
2286 );
2287 l_msg_count := x_msg_count;
2288 WHILE l_msg_count > 0
2289 LOOP
2290 x_msg_data := fnd_msg_pub.get
2291 (l_msg_index,
2292 fnd_api.g_false
2293 );
2294 csi_gen_utility_pvt.put_line( ' MESSAGE DATA = '||x_msg_data);
2295 l_msg_index := l_msg_index + 1;
2296 l_msg_count := l_msg_count - 1;
2297 END LOOP;
2298 l_iface_error_text := substr(x_msg_data,1,2000);
2299 RAISE SKIP_ERROR;
2300 END IF;
2301 END IF;
2302
2303 IF csi_ii_relationships_pvt.Is_link_type
2304 (p_instance_id => r_id.subject_id )
2305 THEN
2306 IF csi_ii_relationships_pvt.relationship_for_link
2307 (p_instance_id => r_id.subject_id
2308 ,p_mode => 'CREATE'
2309 ,p_relationship_id => NULL )
2310 THEN
2311 fnd_message.set_name('CSI','CSI_LINK_EXISTS');
2312 fnd_message.set_token('INSTANCE_ID',r_id.subject_id);
2313 fnd_msg_pub.add;
2314 l_msg_index := 1;
2315 fnd_msg_pub.count_and_get
2316 (p_count => x_msg_count,
2317 p_data => x_msg_data
2318 );
2319 l_msg_count := x_msg_count;
2320 WHILE l_msg_count > 0
2321 LOOP
2322 x_msg_data := fnd_msg_pub.get
2323 (l_msg_index,
2324 fnd_api.g_false
2325 );
2326 csi_gen_utility_pvt.put_line( ' MESSAGE DATA = '||x_msg_data);
2327 l_msg_index := l_msg_index + 1;
2328 l_msg_count := l_msg_count - 1;
2329 END LOOP;
2330 l_iface_error_text := substr(x_msg_data,1,2000);
2331 RAISE SKIP_ERROR;
2332 END IF;
2333 END IF;
2334
2335 END IF; -- <IF p_relationship_tbl(1).relationship_type_code <> 'CONNECTED-TO'>
2336
2337
2338 l_error_array(rel_row) := NULL;
2339 l_status_array(rel_row) := 'V';
2340 l_rel_id_array(rel_row) := r_id.rel_interface_id;
2341 EXCEPTION
2342 WHEN SKIP_ERROR THEN
2343 FND_File.Put_Line(Fnd_File.LOG,'After validation setting status E');
2344 l_error_array(rel_row) := l_iface_error_text;
2345 l_status_array(rel_row) := 'E';
2346 l_rel_id_array(rel_row) := r_id.rel_interface_id;
2347
2348 WHEN others THEN
2349 FND_File.Put_Line(Fnd_File.LOG,'In others status E' || SQLERRM);
2350 l_error_array(rel_row) := l_iface_error_text;
2351 l_status_array(rel_row) := 'E';
2352 l_rel_id_array(rel_row) := r_id.rel_interface_id;
2353 END;
2354 END LOOP;
2355
2356 FND_File.Put_Line(Fnd_File.LOG,'Trying to update count');
2357 -- Update Interface Table
2358 IF l_rel_id_array.count > 0 THEN
2359 FND_File.Put_Line(Fnd_File.LOG,'Updating status in validate mode');
2360 BEGIN
2361 l_upd_stmt := 'UPDATE csi_ii_relation_interface
2362 SET error_text = :error_text
2363 ,process_status = :status
2364 WHERE rel_interface_id = :intf_id';
2365 l_num_of_rows := dbms_sql.open_cursor;
2366 dbms_sql.parse(l_num_of_rows,l_upd_stmt,dbms_sql.native);
2367 dbms_sql.bind_array(l_num_of_rows,':intf_id',l_rel_id_array);
2368 dbms_sql.bind_array(l_num_of_rows,':status',l_status_array);
2369 dbms_sql.bind_array(l_num_of_rows,':error_text',l_error_array);
2370 l_dummy := dbms_sql.execute(l_num_of_rows);
2371 dbms_sql.close_cursor(l_num_of_rows);
2372 EXCEPTION
2373 WHEN OTHERS THEN
2374 NULL;
2375 END;
2376 COMMIT;
2377 END IF;
2378 -- The program that called this program in VALIDATE mode has to go into
2379 -- a wait mode until the completion of all the VALIDATE mode parallel
2380 -- requests.
2381 ELSIF p_mode='UPDATE'
2382 THEN
2383 FND_File.Put_Line(Fnd_File.LOG,'Inside for Update mode');
2384 rel_row := 0;
2385 -- Get the root node and update the instance for updating the instance(subject) location
2386 FOR l_upd_ins_csr IN upd_ins_csr(p_worker_id, l_txn_from_date, l_txn_to_date)
2387 LOOP
2388 BEGIN
2389 l_iface_error_text := NULL;
2390 rel_row:=rel_row+1;
2391 FND_MSG_PUB.initialize;
2392 csi_ml_util_pvt.Get_top_most_parent
2393 ( p_subject_id => l_upd_ins_csr.subject_id
2394 ,p_rel_type_code => l_upd_ins_csr.relationship_type_code
2395 ,p_process_status => 'V'
2396 ,p_object_id => l_upd_obj_id
2397 );
2398
2399 IF l_upd_ins_csr.subject_id = l_upd_obj_id
2400 THEN
2401 l_iface_error_text :='Cannot cascade location to INV,PO,IN-TRANSIT,WIP OR PROJECT using OI';
2402 RAISE SKIP_ERROR;
2403 END IF;
2404
2405 l_instance_rec.instance_usage_code :='IN_RELATIONSHIP';
2406
2407 SELECT active_end_date
2408 ,location_type_code
2409 ,location_id
2410 ,inv_organization_id
2411 ,inv_subinventory_name
2412 ,inv_locator_id
2413 ,pa_project_id
2414 ,pa_project_task_id
2415 ,in_transit_order_line_id
2416 ,wip_job_id
2417 ,po_order_line_id
2418 INTO l_instance_rec.active_end_date
2419 ,l_instance_rec.location_type_code
2420 ,l_instance_rec.location_id
2421 ,l_instance_rec.inv_organization_id
2422 ,l_instance_rec.inv_subinventory_name
2423 ,l_instance_rec.inv_locator_id
2424 ,l_instance_rec.pa_project_id
2425 ,l_instance_rec.pa_project_task_id
2426 ,l_instance_rec.in_transit_order_line_id
2427 ,l_instance_rec.wip_job_id
2428 ,l_instance_rec.po_order_line_id
2429 FROM csi_item_instances
2430 WHERE instance_id=l_upd_obj_id;
2431
2432 SELECT instance_id
2433 ,object_version_number
2434 INTO l_instance_rec.instance_id
2435 ,l_instance_rec.object_version_number
2436 FROM csi_item_instances
2437 WHERE instance_id = l_upd_ins_csr.subject_id;
2438
2439
2440 l_return_status := FND_API.G_RET_STS_SUCCESS;
2441 /*
2442 FND_File.Put_Line(Fnd_File.LOG,'l_instance_rec.instance_id := '||l_instance_rec.instance_id);
2443 FND_File.Put_Line(Fnd_File.LOG,'l_instance_rec.object_version_number := '||l_instance_rec.object_version_number);
2444 FND_File.Put_Line(Fnd_File.LOG,'l_instance_rec.active_end_date := '||l_instance_rec.active_end_date);
2445 FND_File.Put_Line(Fnd_File.LOG,'l_instance_rec.location_type_code := '||l_instance_rec.location_type_code);
2446 FND_File.Put_Line(Fnd_File.LOG,'l_instance_rec.location_id := '||l_instance_rec.location_id);
2447 FND_File.Put_Line(Fnd_File.LOG,'l_instance_rec.inv_organization_id := '||l_instance_rec.inv_organization_id);
2448 FND_File.Put_Line(Fnd_File.LOG,'l_instance_rec.inv_subinventory_name := '||l_instance_rec.inv_subinventory_name);
2449 FND_File.Put_Line(Fnd_File.LOG,'l_instance_rec.inv_locator_id := '||l_instance_rec.inv_locator_id);
2450 FND_File.Put_Line(Fnd_File.LOG,'l_instance_rec.pa_project_id := '||l_instance_rec.pa_project_id);
2451 FND_File.Put_Line(Fnd_File.LOG,'l_instance_rec.pa_project_task_id := '||l_instance_rec.pa_project_task_id);
2452 FND_File.Put_Line(Fnd_File.LOG,'l_instance_rec.in_transit_order_line_id := '||l_instance_rec.in_transit_order_line_id);
2453 FND_File.Put_Line(Fnd_File.LOG,'l_instance_rec.wip_job_id := '||l_instance_rec.wip_job_id);
2454 FND_File.Put_Line(Fnd_File.LOG,'l_instance_rec.po_order_line_id := '||l_instance_rec.po_order_line_id);
2455 FND_File.Put_Line(Fnd_File.LOG,'Before update l_return_status := '||l_return_status);
2456 */
2457 l_txn_rec.transaction_type_id := csi_ml_util_pvt.get_txn_type_id('OPEN_INTERFACE','CSI');
2458 l_txn_rec.transaction_date := sysdate;
2459 l_txn_rec.source_transaction_date := sysdate;
2460
2461 csi_item_instance_pvt.update_item_instance
2462 (p_api_version => 1.0
2463 ,p_commit => fnd_api.g_false
2464 ,p_init_msg_list => fnd_api.g_false
2465 ,p_validation_level => fnd_api.g_valid_level_full
2466 ,p_instance_rec => l_instance_rec
2467 ,p_txn_rec => l_txn_rec
2468 ,x_instance_id_lst => l_instance_id_lst
2469 ,x_return_status => l_return_status
2470 ,x_msg_count => x_msg_count
2471 ,x_msg_data => x_msg_data
2472 ,p_item_attribute_tbl => l_item_attribute_tbl
2473 ,p_location_tbl => l_location_tbl
2474 ,p_generic_id_tbl => l_generic_id_tbl
2475 ,p_lookup_tbl => l_lookup_tbl
2476 ,p_ins_count_rec => l_ins_count_rec
2477 ,p_called_from_rel => fnd_api.g_false
2478 ,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
2479 ,p_child_inst_tbl => px_child_inst_tbl
2480 ,p_validation_mode => 'V'
2481 );
2482 FND_File.Put_Line(Fnd_File.LOG,'After update l_return_status := '||l_return_status);
2483 FND_File.Put_Line(Fnd_File.LOG,'After update x_msg_data := '||x_msg_data);
2484
2485
2486
2487 IF NOT(l_return_status = fnd_api.g_ret_sts_success)
2488 THEN
2489 /*
2490 fnd_message.set_name('CSI','CSI_FAILED_TO_VALIDATE_INS');
2491 fnd_message.set_token('instance_id',l_instance_rec.instance_id);
2492 fnd_msg_pub.add;
2493 */
2494 l_msg_index := 1;
2495
2496 fnd_msg_pub.count_and_get
2497 ( p_count => x_msg_count
2498 ,p_data => x_msg_data
2499 );
2500 l_msg_count := x_msg_count;
2501 WHILE l_msg_count > 0
2502 LOOP
2503 x_msg_data := fnd_msg_pub.get
2504 ( l_msg_index
2505 ,fnd_api.g_false
2506 );
2507 csi_gen_utility_pvt.put_line( ' MESSAGE DATA = '||x_msg_data);
2508 l_msg_index := l_msg_index + 1;
2509 l_msg_count := l_msg_count - 1;
2510 END LOOP;
2511 l_iface_error_text := substr(x_msg_data,1,2000);
2512 RAISE SKIP_ERROR;
2513 END IF;
2514
2515 l_error_array(rel_row) := NULL;
2516 l_status_array(rel_row) := 'U';
2517 l_rel_id_array(rel_row) := l_upd_ins_csr.rel_interface_id;
2518 l_config_root_tbl(rel_row) := l_upd_obj_id;
2519 EXCEPTION
2520 WHEN SKIP_ERROR THEN
2521 l_error_array(rel_row) := l_iface_error_text;
2522 l_status_array(rel_row) := 'E';
2523 l_rel_id_array(rel_row) := l_upd_ins_csr.rel_interface_id;
2524 l_config_root_tbl(rel_row) := l_upd_obj_id;
2525 END;
2526 END LOOP;
2527
2528 -- Update Interface Table
2529 IF l_rel_id_array.count > 0 THEN
2530 FND_File.Put_Line(Fnd_File.LOG,'Updating status in update mode');
2531 BEGIN
2532 l_upd_stmt := 'UPDATE csi_ii_relation_interface
2533 SET error_text = :error_text
2534 ,process_status = :status
2535 WHERE rel_interface_id = :intf_id';
2536 l_num_of_rows := dbms_sql.open_cursor;
2537 dbms_sql.parse(l_num_of_rows,l_upd_stmt,dbms_sql.native);
2538 dbms_sql.bind_array(l_num_of_rows,':intf_id',l_rel_id_array);
2539 dbms_sql.bind_array(l_num_of_rows,':status',l_status_array);
2540 dbms_sql.bind_array(l_num_of_rows,':error_text',l_error_array);
2541 l_dummy := dbms_sql.execute(l_num_of_rows);
2542 dbms_sql.close_cursor(l_num_of_rows);
2543 EXCEPTION
2544 WHEN OTHERS THEN
2545 NULL;
2546 END;
2547 COMMIT;
2548 END IF;
2549
2550 ELSIF p_mode='RE-UPDATE'
2551 THEN
2552 FND_File.Put_Line(Fnd_File.LOG,'Inside for re-update mode');
2553 rel_row := 0;
2554 -- Get the root node and update the instance for updating the instance(subject) location
2555 FOR l_re_upd_csr IN re_upd_csr(p_worker_id, l_txn_from_date, l_txn_to_date)
2556 LOOP
2557 BEGIN
2558 l_iface_error_text := NULL;
2559 rel_row := rel_row+1;
2560 FND_MSG_PUB.initialize;
2561
2562 csi_ml_util_pvt.Get_top_most_parent
2563 ( p_subject_id => l_re_upd_csr.subject_id
2564 ,p_rel_type_code => l_re_upd_csr.relationship_type_code
2565 ,p_process_status => 'U'
2566 ,p_object_id => l_upd_obj_id
2567 );
2568
2569 IF l_re_upd_csr.subject_id = l_upd_obj_id
2570 THEN
2571 l_iface_error_text :='Cannot cascade location to INV,PO,IN-TRANSIT,WIP OR PROJECT using OI';
2572 RAISE SKIP_ERROR;
2573 END IF;
2574
2575 l_instance_rec.instance_usage_code :='IN_RELATIONSHIP';
2576
2577 SELECT active_end_date
2578 ,location_type_code
2579 ,location_id
2580 ,inv_organization_id
2581 ,inv_subinventory_name
2582 ,inv_locator_id
2583 ,pa_project_id
2584 ,pa_project_task_id
2585 ,in_transit_order_line_id
2586 ,wip_job_id
2587 ,po_order_line_id
2588 INTO l_instance_rec.active_end_date
2589 ,l_instance_rec.location_type_code
2590 ,l_instance_rec.location_id
2591 ,l_instance_rec.inv_organization_id
2592 ,l_instance_rec.inv_subinventory_name
2593 ,l_instance_rec.inv_locator_id
2594 ,l_instance_rec.pa_project_id
2595 ,l_instance_rec.pa_project_task_id
2596 ,l_instance_rec.in_transit_order_line_id
2597 ,l_instance_rec.wip_job_id
2598 ,l_instance_rec.po_order_line_id
2599 FROM csi_item_instances
2600 WHERE instance_id=l_upd_obj_id;
2601
2602 SELECT instance_id
2603 ,object_version_number
2604 INTO l_instance_rec.instance_id
2605 ,l_instance_rec.object_version_number
2606 FROM csi_item_instances
2607 WHERE instance_id = l_re_upd_csr.subject_id;
2608
2609 l_return_status := FND_API.G_RET_STS_SUCCESS;
2610
2611 l_txn_rec.transaction_type_id := csi_ml_util_pvt.get_txn_type_id('OPEN_INTERFACE','CSI');
2612 l_txn_rec.transaction_date := sysdate;
2613 l_txn_rec.source_transaction_date := sysdate;
2614
2615 csi_item_instance_pvt.update_item_instance
2616 (p_api_version => 1.0
2617 ,p_commit => fnd_api.g_false
2618 ,p_init_msg_list => fnd_api.g_false
2619 ,p_validation_level => fnd_api.g_valid_level_full
2620 ,p_instance_rec => l_instance_rec
2621 ,p_txn_rec => l_txn_rec
2622 ,x_instance_id_lst => l_instance_id_lst
2623 ,x_return_status => l_return_status
2624 ,x_msg_count => x_msg_count
2625 ,x_msg_data => x_msg_data
2626 ,p_item_attribute_tbl => l_item_attribute_tbl
2627 ,p_location_tbl => l_location_tbl
2628 ,p_generic_id_tbl => l_generic_id_tbl
2629 ,p_lookup_tbl => l_lookup_tbl
2630 ,p_ins_count_rec => l_ins_count_rec
2631 ,p_called_from_rel => fnd_api.g_false
2632 ,p_oks_txn_inst_tbl => px_oks_txn_inst_tbl
2633 ,p_child_inst_tbl => px_child_inst_tbl
2634 ,p_validation_mode => 'U'
2635 );
2636
2637 FND_File.Put_Line(Fnd_File.LOG,'After update x_msg_data := '||x_msg_data);
2638 FND_File.Put_Line(Fnd_File.LOG,'After update l_return_status := '||l_return_status);
2639
2640
2641 IF NOT(l_return_status = fnd_api.g_ret_sts_success)
2642 THEN
2643
2644 l_msg_index := 1;
2645
2646 fnd_msg_pub.count_and_get
2647 ( p_count => x_msg_count
2648 ,p_data => x_msg_data
2649 );
2650 l_msg_count := x_msg_count;
2651 WHILE l_msg_count > 0
2652 LOOP
2653 x_msg_data := fnd_msg_pub.get
2654 ( l_msg_index
2655 ,fnd_api.g_false
2656 );
2657 csi_gen_utility_pvt.put_line( ' MESSAGE DATA = '||x_msg_data);
2658 l_msg_index := l_msg_index + 1;
2659 l_msg_count := l_msg_count - 1;
2660 END LOOP;
2661 l_iface_error_text := substr(x_msg_data,1,2000);
2662 RAISE SKIP_ERROR;
2663 END IF;
2664 l_error_array(rel_row) := NULL;
2665 l_status_array(rel_row) := 'I';
2666 l_rel_id_array(rel_row) := l_re_upd_csr.rel_interface_id;
2667 l_config_root_tbl(rel_row) := l_upd_obj_id;
2668 EXCEPTION
2669 WHEN SKIP_ERROR THEN
2670
2671 l_error_array(rel_row) := l_iface_error_text;
2672 l_status_array(rel_row) := 'E';
2673 l_rel_id_array(rel_row) := l_re_upd_csr.rel_interface_id;
2674 l_config_root_tbl(rel_row) := l_upd_obj_id;
2675 END;
2676
2677 END LOOP;
2678
2679 -- Update Interface Table
2680 IF l_rel_id_array.count > 0 THEN
2681 FND_File.Put_Line(Fnd_File.LOG,'Updating status in re-update mode');
2682 BEGIN
2683 l_upd_stmt := 'UPDATE csi_ii_relation_interface
2684 SET error_text = :error_text
2685 ,process_status = :status
2686 WHERE rel_interface_id = :intf_id';
2687 l_num_of_rows := dbms_sql.open_cursor;
2688 dbms_sql.parse(l_num_of_rows,l_upd_stmt,dbms_sql.native);
2689 dbms_sql.bind_array(l_num_of_rows,':intf_id',l_rel_id_array);
2690 dbms_sql.bind_array(l_num_of_rows,':status',l_status_array);
2691 dbms_sql.bind_array(l_num_of_rows,':error_text',l_error_array);
2692 l_dummy := dbms_sql.execute(l_num_of_rows);
2693 dbms_sql.close_cursor(l_num_of_rows);
2694 EXCEPTION
2695 WHEN OTHERS THEN
2696 NULL;
2697 END;
2698 COMMIT;
2699 END IF;
2700
2701 ELSIF p_mode='INSERT'
2702 THEN
2703 FND_File.Put_Line(Fnd_File.LOG,'Inside for Insert mode');
2704 -- This run also has to insert records into csi_ii_relationships
2705 -- ,csi_ii_relationships_h and csi_transactions
2706 l_ins:=0;
2707 l_txn_type_id := csi_ml_util_pvt.get_txn_type_id('OPEN_INTERFACE','CSI');
2708
2709 FOR l_ins_rel_csr IN ins_rel_csr(p_worker_id, l_txn_from_date, l_txn_to_date)
2710 LOOP
2711 l_ins:=l_ins+1;
2712 SELECT csi_ii_relationships_h_s.NEXTVAL
2713 INTO l_rel_hist_tbl(l_ins)
2714 FROM dual;
2715
2716 SELECT csi_ii_relationships_s.NEXTVAL
2717 INTO l_ii_relationship_rec_tab.rel_interface_id(l_ins)
2718 FROM dual;
2719
2720 SELECT csi_transactions_s.NEXTVAL
2721 INTO l_txn_id_tbl(l_ins)
2722 FROM dual;
2723
2724 l_status_array(l_ins) := 'P';
2725 l_rel_id_array(l_ins) := l_ins_rel_csr.rel_interface_id;
2726
2727 l_ii_relationship_rec_tab.relationship_type_code(l_ins) := l_ins_rel_csr.relationship_type_code;
2728 l_ii_relationship_rec_tab.object_id(l_ins) := l_ins_rel_csr.object_id;
2729 l_ii_relationship_rec_tab.subject_id(l_ins) := l_ins_rel_csr.subject_id;
2730 l_ii_relationship_rec_tab.position_reference(l_ins) := l_ins_rel_csr.position_reference;
2731 l_ii_relationship_rec_tab.active_start_date(l_ins) := l_ins_rel_csr.relationship_start_date;
2732 l_ii_relationship_rec_tab.active_end_date(l_ins) := l_ins_rel_csr.relationship_end_date;
2733 l_ii_relationship_rec_tab.display_order(l_ins) := l_ins_rel_csr.display_order;
2734 l_ii_relationship_rec_tab.mandatory_flag(l_ins) := l_ins_rel_csr.mandatory_flag;
2735 l_ii_relationship_rec_tab.context(l_ins) := l_ins_rel_csr.context;
2736 l_ii_relationship_rec_tab.attribute1(l_ins) := l_ins_rel_csr.attribute1;
2737 l_ii_relationship_rec_tab.attribute2(l_ins) := l_ins_rel_csr.attribute2;
2738 l_ii_relationship_rec_tab.attribute3(l_ins) := l_ins_rel_csr.attribute3;
2739 l_ii_relationship_rec_tab.attribute4(l_ins) := l_ins_rel_csr.attribute4;
2740 l_ii_relationship_rec_tab.attribute5(l_ins) := l_ins_rel_csr.attribute5;
2741 l_ii_relationship_rec_tab.attribute6(l_ins) := l_ins_rel_csr.attribute6;
2742 l_ii_relationship_rec_tab.attribute7(l_ins) := l_ins_rel_csr.attribute7;
2743 l_ii_relationship_rec_tab.attribute8(l_ins) := l_ins_rel_csr.attribute8;
2744 l_ii_relationship_rec_tab.attribute9(l_ins) := l_ins_rel_csr.attribute9;
2745 l_ii_relationship_rec_tab.attribute10(l_ins) := l_ins_rel_csr.attribute10;
2746 l_ii_relationship_rec_tab.attribute11(l_ins) := l_ins_rel_csr.attribute11;
2747 l_ii_relationship_rec_tab.attribute12(l_ins) := l_ins_rel_csr.attribute12;
2748 l_ii_relationship_rec_tab.attribute13(l_ins) := l_ins_rel_csr.attribute13;
2749 l_ii_relationship_rec_tab.attribute14(l_ins) := l_ins_rel_csr.attribute14;
2750 l_ii_relationship_rec_tab.attribute15(l_ins) := l_ins_rel_csr.attribute15;
2751 END LOOP;
2752
2753 FORALL i in 1 .. l_ii_relationship_rec_tab.rel_interface_id.count
2754 INSERT INTO CSI_II_RELATIONSHIPS(
2755 RELATIONSHIP_ID
2756 ,RELATIONSHIP_TYPE_CODE
2757 ,OBJECT_ID
2758 ,SUBJECT_ID
2759 ,POSITION_REFERENCE
2760 ,ACTIVE_START_DATE
2761 ,ACTIVE_END_DATE
2762 ,DISPLAY_ORDER
2763 ,MANDATORY_FLAG
2764 ,CONTEXT
2765 ,ATTRIBUTE1
2766 ,ATTRIBUTE2
2767 ,ATTRIBUTE3
2768 ,ATTRIBUTE4
2769 ,ATTRIBUTE5
2770 ,ATTRIBUTE6
2771 ,ATTRIBUTE7
2772 ,ATTRIBUTE8
2773 ,ATTRIBUTE9
2774 ,ATTRIBUTE10
2775 ,ATTRIBUTE11
2776 ,ATTRIBUTE12
2777 ,ATTRIBUTE13
2778 ,ATTRIBUTE14
2779 ,ATTRIBUTE15
2780 ,CREATED_BY
2781 ,CREATION_DATE
2782 ,LAST_UPDATED_BY
2783 ,LAST_UPDATE_DATE
2784 ,LAST_UPDATE_LOGIN
2785 ,OBJECT_VERSION_NUMBER)
2786 VALUES(
2787 l_ii_relationship_rec_tab.REL_INTERFACE_ID(i)
2788 ,l_ii_relationship_rec_tab.RELATIONSHIP_TYPE_CODE(i)
2789 ,l_ii_relationship_rec_tab.OBJECT_ID(i)
2790 ,l_ii_relationship_rec_tab.SUBJECT_ID(i)
2791 ,l_ii_relationship_rec_tab.POSITION_REFERENCE(i)
2792 ,l_ii_relationship_rec_tab.ACTIVE_START_DATE(i)
2793 ,l_ii_relationship_rec_tab.ACTIVE_END_DATE(i)
2794 ,l_ii_relationship_rec_tab.DISPLAY_ORDER(i)
2795 ,l_ii_relationship_rec_tab.MANDATORY_FLAG(i)
2796 ,l_ii_relationship_rec_tab.CONTEXT(i)
2797 ,l_ii_relationship_rec_tab.ATTRIBUTE1(i)
2798 ,l_ii_relationship_rec_tab.ATTRIBUTE2(i)
2799 ,l_ii_relationship_rec_tab.ATTRIBUTE3(i)
2800 ,l_ii_relationship_rec_tab.ATTRIBUTE4(i)
2801 ,l_ii_relationship_rec_tab.ATTRIBUTE5(i)
2802 ,l_ii_relationship_rec_tab.ATTRIBUTE6(i)
2803 ,l_ii_relationship_rec_tab.ATTRIBUTE7(i)
2804 ,l_ii_relationship_rec_tab.ATTRIBUTE8(i)
2805 ,l_ii_relationship_rec_tab.ATTRIBUTE9(i)
2806 ,l_ii_relationship_rec_tab.ATTRIBUTE10(i)
2807 ,l_ii_relationship_rec_tab.ATTRIBUTE11(i)
2808 ,l_ii_relationship_rec_tab.ATTRIBUTE12(i)
2809 ,l_ii_relationship_rec_tab.ATTRIBUTE13(i)
2810 ,l_ii_relationship_rec_tab.ATTRIBUTE14(i)
2811 ,l_ii_relationship_rec_tab.ATTRIBUTE15(i)
2812 ,l_user_id
2813 ,sysdate
2814 ,l_user_id
2815 ,sysdate
2816 ,-1
2817 ,1);
2818
2819
2820 FORALL i in 1 .. l_ii_relationship_rec_tab.rel_interface_id.count
2821 INSERT INTO CSI_II_RELATIONSHIPS_H(
2822 RELATIONSHIP_HISTORY_ID
2823 ,RELATIONSHIP_ID
2824 ,TRANSACTION_ID
2825 ,NEW_SUBJECT_ID
2826 ,NEW_POSITION_REFERENCE
2827 ,NEW_ACTIVE_START_DATE
2828 ,NEW_ACTIVE_END_DATE
2829 ,NEW_MANDATORY_FLAG
2830 ,NEW_CONTEXT
2831 ,NEW_ATTRIBUTE1
2832 ,NEW_ATTRIBUTE2
2833 ,NEW_ATTRIBUTE3
2834 ,NEW_ATTRIBUTE4
2835 ,NEW_ATTRIBUTE5
2836 ,NEW_ATTRIBUTE6
2837 ,NEW_ATTRIBUTE7
2838 ,NEW_ATTRIBUTE8
2839 ,NEW_ATTRIBUTE9
2840 ,NEW_ATTRIBUTE10
2841 ,NEW_ATTRIBUTE11
2842 ,NEW_ATTRIBUTE12
2843 ,NEW_ATTRIBUTE13
2844 ,NEW_ATTRIBUTE14
2845 ,NEW_ATTRIBUTE15
2846 ,FULL_DUMP_FLAG
2847 ,CREATED_BY
2848 ,CREATION_DATE
2849 ,LAST_UPDATED_BY
2850 ,LAST_UPDATE_DATE
2851 ,LAST_UPDATE_LOGIN
2852 ,OBJECT_VERSION_NUMBER)
2853 VALUES(
2854 l_rel_hist_tbl(i)
2855 ,l_ii_relationship_rec_tab.REL_INTERFACE_ID(i)
2856 ,l_txn_id_tbl(i)
2857 ,l_ii_relationship_rec_tab.SUBJECT_ID(i)
2858 ,l_ii_relationship_rec_tab.POSITION_REFERENCE(i)
2859 ,l_ii_relationship_rec_tab.ACTIVE_START_DATE(i)
2860 ,l_ii_relationship_rec_tab.ACTIVE_END_DATE(i)
2861 ,l_ii_relationship_rec_tab.MANDATORY_FLAG(i)
2862 ,l_ii_relationship_rec_tab.CONTEXT(i)
2863 ,l_ii_relationship_rec_tab.ATTRIBUTE1(i)
2864 ,l_ii_relationship_rec_tab.ATTRIBUTE2(i)
2865 ,l_ii_relationship_rec_tab.ATTRIBUTE3(i)
2866 ,l_ii_relationship_rec_tab.ATTRIBUTE4(i)
2867 ,l_ii_relationship_rec_tab.ATTRIBUTE5(i)
2868 ,l_ii_relationship_rec_tab.ATTRIBUTE6(i)
2869 ,l_ii_relationship_rec_tab.ATTRIBUTE7(i)
2870 ,l_ii_relationship_rec_tab.ATTRIBUTE8(i)
2871 ,l_ii_relationship_rec_tab.ATTRIBUTE9(i)
2872 ,l_ii_relationship_rec_tab.ATTRIBUTE10(i)
2873 ,l_ii_relationship_rec_tab.ATTRIBUTE11(i)
2874 ,l_ii_relationship_rec_tab.ATTRIBUTE12(i)
2875 ,l_ii_relationship_rec_tab.ATTRIBUTE13(i)
2876 ,l_ii_relationship_rec_tab.ATTRIBUTE14(i)
2877 ,l_ii_relationship_rec_tab.ATTRIBUTE15(i)
2878 ,'Y'
2879 ,l_user_id
2880 ,sysdate
2881 ,l_user_id
2882 ,sysdate
2883 ,-1
2884 ,1);
2885
2886
2887 FORALL i in 1 .. l_txn_id_tbl.count
2888 INSERT INTO CSI_TRANSACTIONS(
2889 TRANSACTION_ID
2890 ,TRANSACTION_DATE
2891 ,SOURCE_TRANSACTION_DATE
2892 ,SOURCE_HEADER_REF
2893 ,TRANSACTION_TYPE_ID
2894 ,CREATED_BY
2895 ,CREATION_DATE
2896 ,LAST_UPDATED_BY
2897 ,LAST_UPDATE_DATE
2898 ,LAST_UPDATE_LOGIN
2899 ,OBJECT_VERSION_NUMBER
2900 )
2901 VALUES(
2902 l_txn_id_tbl(i)
2903 ,SYSDATE
2904 ,SYSDATE
2905 ,'Full Dump Insert'
2906 ,l_txn_type_id
2907 ,l_user_id
2908 ,sysdate
2909 ,l_user_id
2910 ,sysdate
2911 ,-1
2912 ,1
2913 );
2914
2915 IF l_rel_id_array.count > 0 THEN
2916 FND_File.Put_Line(Fnd_File.LOG,'Updating status in insert mode');
2917 BEGIN
2918 l_upd_stmt := 'UPDATE csi_ii_relation_interface
2919 SET process_status = :status
2920 WHERE rel_interface_id = :intf_id';
2921 l_num_of_rows := dbms_sql.open_cursor;
2922 dbms_sql.parse(l_num_of_rows,l_upd_stmt,dbms_sql.native);
2923 dbms_sql.bind_array(l_num_of_rows,':intf_id',l_rel_id_array);
2924 dbms_sql.bind_array(l_num_of_rows,':status',l_status_array);
2925 l_dummy := dbms_sql.execute(l_num_of_rows);
2926 dbms_sql.close_cursor(l_num_of_rows);
2927 EXCEPTION
2928 WHEN OTHERS THEN
2929 NULL;
2930 END;
2931 COMMIT;
2932 END IF;
2933
2934 END IF; -- p_mode='VALIDATE'
2935 FND_File.Put_Line(Fnd_File.LOG,'End time RELATIONSHIP: '||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
2936 END validate_relationship;
2937
2938 -- Duplicate subject
2939 PROCEDURE Eliminate_dup_subject IS
2940 CURSOR dup_csr IS
2941 SELECT subject_id
2942 ,relationship_type_code
2943 ,count(*)
2944 FROM csi_ii_relation_interface
2945 WHERE process_status='R'
2946 AND relationship_type_code <> 'CONNECTED-TO'
2947 GROUP BY subject_id,relationship_type_code
2948 HAVING count(*) > 1;
2949
2950 CURSOR upd_csr (p_rel_id IN NUMBER
2951 ,p_subject_id IN NUMBER
2952 ,p_rel_type IN VARCHAR2) IS
2953 SELECT cir.rel_interface_id
2954 FROM csi_ii_relation_interface cir
2955 WHERE cir.subject_id = p_subject_id
2956 AND cir.relationship_type_code = p_rel_type
2957 AND cir.rel_interface_id <> p_rel_id;
2958
2959 l_ret_relationship_id NUMBER;
2960 TYPE NUMLIST IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2961 l_rel_id_tbl NUMLIST;
2962 l_ctr NUMBER := 0;
2963
2964 TYPE NumTabType IS VARRAY(10000) OF NUMBER;
2965 TYPE V30TabType IS VARRAY(10000) OF VARCHAR2(30);
2966
2967 l_subject_id_upd NumTabType;
2968 l_count_upd NumTabType;
2969 l_rel_type_code_upd V30TabType;
2970
2971 MAX_BUFFER_SIZE NUMBER := 1000;
2972 BEGIN
2973 OPEN dup_csr;
2974 LOOP
2975 FETCH dup_csr BULK COLLECT INTO
2976 l_subject_id_upd,
2977 l_rel_type_code_upd,
2978 l_count_upd
2979 LIMIT MAX_BUFFER_SIZE;
2980 --
2981 FOR k in 1..l_subject_id_upd.count
2982 LOOP
2983 l_ret_relationship_id := -9999;
2984 BEGIN
2985 SELECT MAX(cir.rel_interface_id)
2986 INTO l_ret_relationship_id
2987 FROM csi_ii_relation_interface cir
2988 WHERE cir.subject_id = l_subject_id_upd(k)
2989 AND cir.relationship_type_code = l_rel_type_code_upd(k);
2990 END;
2991
2992 FOR upd_rec IN upd_csr(l_ret_relationship_id
2993 ,l_subject_id_upd(k)
2994 ,l_rel_type_code_upd(k))
2995 LOOP
2996 l_ctr := l_ctr + 1;
2997 l_rel_id_tbl(l_ctr) := upd_rec.rel_interface_id;
2998 END LOOP;
2999 END LOOP;
3000 EXIT WHEN dup_csr%NOTFOUND;
3001 END LOOP;
3002
3003 IF dup_csr%ISOPEN THEN
3004 CLOSE dup_csr;
3005 END IF;
3006
3007 IF l_rel_id_tbl.count > 0
3008 THEN
3009 FORALL j IN l_rel_id_tbl.FIRST .. l_rel_id_tbl.LAST
3010 UPDATE csi_ii_relation_interface
3011 SET process_status='E'
3012 ,error_text='Duplicate subject_id record'
3013 ,parallel_worker_id=0
3014 WHERE rel_interface_id = l_rel_id_tbl(j);
3015 COMMIT;
3016 END IF;
3017 END Eliminate_dup_subject;
3018 -- End duplicate subject
3019
3020 -- Duplicate records
3021 PROCEDURE Eliminate_dup_records IS
3022 CURSOR dup_csr IS
3023 SELECT object_id
3024 ,subject_id
3025 ,relationship_type_code
3026 ,count(*)
3027 FROM csi_ii_relation_interface
3028 WHERE process_status='R'
3029 GROUP BY object_id,subject_id,relationship_type_code
3030 HAVING count(*) > 1;
3031
3032 CURSOR upd_csr (p_rel_id IN NUMBER
3033 ,p_object_id IN NUMBER
3034 ,p_subject_id IN NUMBER
3035 ,p_rel_type IN VARCHAR2) IS
3036 SELECT cir.rel_interface_id
3037 FROM csi_ii_relation_interface cir
3038 WHERE cir.object_id = p_object_id
3039 AND cir.subject_id = p_subject_id
3040 AND cir.relationship_type_code = p_rel_type
3041 AND cir.rel_interface_id <> p_rel_id;
3042
3043 l_ret_relationship_id NUMBER;
3044 TYPE NUMLIST IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3045 l_rel_id_tbl NUMLIST;
3046 l_ctr NUMBER := 0;
3047
3048 TYPE NumTabType IS VARRAY(10000) OF NUMBER;
3049 TYPE V30TabType IS VARRAY(10000) OF VARCHAR2(30);
3050
3051 l_object_id_upd NumTabType;
3052 l_subject_id_upd NumTabType;
3053 l_count_upd NumTabType;
3054 l_rel_type_code_upd V30TabType;
3055
3056 MAX_BUFFER_SIZE NUMBER := 1000;
3057 BEGIN
3058 OPEN dup_csr;
3059 LOOP
3060 FETCH dup_csr BULK COLLECT INTO
3061 l_object_id_upd,
3062 l_subject_id_upd,
3063 l_rel_type_code_upd,
3064 l_count_upd
3065 LIMIT MAX_BUFFER_SIZE;
3066 --
3067 FOR k IN 1..l_object_id_upd.count
3068 LOOP
3069 l_ret_relationship_id := -9999;
3070 BEGIN
3071 SELECT MAX(cir.rel_interface_id)
3072 INTO l_ret_relationship_id
3073 FROM csi_ii_relation_interface cir
3074 WHERE cir.subject_id = l_subject_id_upd(k)
3075 AND cir.object_id = l_object_id_upd(k)
3076 AND cir.relationship_type_code = l_rel_type_code_upd(k);
3077 END;
3078
3079 FOR upd_rec IN upd_csr(l_ret_relationship_id
3080 ,l_object_id_upd(k)
3081 ,l_subject_id_upd(k)
3082 ,l_rel_type_code_upd(k))
3083 LOOP
3084 l_ctr := l_ctr + 1;
3085 l_rel_id_tbl(l_ctr) := upd_rec.rel_interface_id;
3086 END LOOP;
3087 END LOOP;
3088 EXIT WHEN dup_csr%NOTFOUND;
3089 END LOOP;
3090
3091 IF dup_csr%ISOPEN THEN
3092 CLOSE dup_csr;
3093 END IF;
3094
3095 IF l_rel_id_tbl.count > 0
3096 THEN
3097 FORALL j IN l_rel_id_tbl.FIRST .. l_rel_id_tbl.LAST
3098 UPDATE csi_ii_relation_interface
3099 SET process_status='E'
3100 ,error_text='Duplicate record'
3101 ,parallel_worker_id=0
3102 WHERE rel_interface_id = l_rel_id_tbl(j);
3103 COMMIT;
3104 END IF;
3105 END Eliminate_dup_records;
3106 -- End duplicate records
3107
3108 -- Start check cyclic
3109 PROCEDURE check_cyclic IS
3110 CURSOR chk_cyclic_csr IS
3111 SELECT object_id
3112 ,subject_id
3113 ,rel_interface_id
3114 ,relationship_type_code
3115 FROM csi_ii_relation_interface
3116 WHERE process_status='R';
3117 l_rel_tbl csi_ml_util_pvt.ii_rel_interface_tbl;
3118 l_error BOOLEAN :=FALSE;
3119 l_rel_id_array dbms_sql.Number_Table;
3120 l_status_array dbms_sql.Varchar2_Table;
3121 l_error_array dbms_sql.Varchar2_Table;
3122 l_num_of_rows NUMBER;
3123 l_dummy NUMBER;
3124 rel_row NUMBER;
3125 l_upd_stmt VARCHAR2(2000);
3126 BEGIN
3127 l_rel_tbl.DELETE;
3128 FOR l_chk_cyclic_csr IN chk_cyclic_csr
3129 LOOP
3130 l_error := FALSE;
3131 csi_ml_util_pvt.get_children
3132 (p_object_id => l_chk_cyclic_csr.subject_id,
3133 p_rel_tbl => l_rel_tbl
3134 );
3135
3136 IF l_rel_tbl.count > 0
3137 THEN
3138 FOR j IN l_rel_tbl.FIRST .. l_rel_tbl.LAST
3139 LOOP
3140 IF l_rel_tbl(j).subject_id = l_chk_cyclic_csr.subject_id
3141 THEN
3142 l_error := TRUE;
3143 exit;
3144 END IF;
3145 END LOOP;
3146 IF l_error
3147 THEN
3148 rel_row:=0;
3149 FOR i IN l_rel_tbl.FIRST .. l_rel_tbl.LAST
3150 LOOP
3151 rel_row:=rel_row+1;
3152 l_error_array(rel_row) := 'You are trying to create a parent child loop.';
3153 l_status_array(rel_row) := 'E';
3154 l_rel_id_array(rel_row) := l_rel_tbl(i).rel_interface_id;
3155 END LOOP;
3156
3157 IF l_rel_id_array.count > 0 THEN
3158 FND_File.Put_Line(Fnd_File.LOG,'Updating status in for parent child loop ');
3159 BEGIN
3160 l_upd_stmt := 'UPDATE csi_ii_relation_interface
3161 SET process_status = :status
3162 ,error_text = :error_text
3163 ,parallel_worker_id =0
3164 WHERE rel_interface_id = :intf_id';
3165 l_num_of_rows := dbms_sql.open_cursor;
3166 dbms_sql.parse(l_num_of_rows,l_upd_stmt,dbms_sql.native);
3167 dbms_sql.bind_array(l_num_of_rows,':intf_id',l_rel_id_array);
3168 dbms_sql.bind_array(l_num_of_rows,':status',l_status_array);
3169 dbms_sql.bind_array(l_num_of_rows,':error_text',l_error_array);
3170 l_dummy := dbms_sql.execute(l_num_of_rows);
3171 dbms_sql.close_cursor(l_num_of_rows);
3172 EXCEPTION
3173 WHEN OTHERS THEN
3174 NULL;
3175 END;
3176 COMMIT;
3177 END IF;
3178 END IF;
3179 END IF;
3180 END LOOP;
3181 END check_cyclic;
3182 -- End check cyclic
3183
3184 END CSI_ML_UTIL_PVT;