[Home] [Help]
1 PACKAGE BODY CSI_ML_REPLACE_PVT AS
2 -- $Header: csimrplb.pls 115.23 2003/01/08 21:57:19 jpwilson noship $
3
4 PROCEDURE process_replace (
5 p_txn_identifier IN VARCHAR2,
6 p_source_system_name IN VARCHAR2,
7 x_instance_tbl OUT NOCOPY csi_datastructures_pub.instance_tbl,
8 x_party_tbl OUT NOCOPY csi_datastructures_pub.party_tbl,
9 x_account_tbl OUT NOCOPY csi_datastructures_pub.party_account_tbl,
10 x_eav_tbl OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl,
11 x_price_tbl OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl,
12 x_org_assign_tbl OUT NOCOPY csi_datastructures_pub.organization_units_tbl,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_error_message OUT NOCOPY VARCHAR2
15 )
16 IS
17 CURSOR csi_intf_repl_cur (c_id IN VARCHAR2,
18 c_source IN VARCHAR2)
19 IS
20 SELECT *
21 FROM csi_instance_interface
22 WHERE transaction_identifier = c_id
23 AND source_system_name = c_source
24 ORDER BY inst_interface_id;
25
26 CURSOR csi_intf_party_acct_cur (c_inst_interface_id IN NUMBER)
27 IS
28 SELECT *
29 FROM csi_i_party_interface cpi
30 WHERE cpi.inst_interface_id = c_inst_interface_id
31 ORDER BY ip_interface_id;
32
33 CURSOR csi_intf_ext_attrib_cur (c_inst_interface_id IN NUMBER)
34 IS
35 SELECT *
36 FROM csi_iea_value_interface ceai
37 WHERE ceai.inst_interface_id = c_inst_interface_id
38 ORDER BY ieav_interface_id;
39
40 l_end_date DATE := SYSDATE + 1;
41 l_api_name VARCHAR2 (255)
42 := 'CSI_ML_REPLACE_PVT.PROCESS_REPLACE';
43 l_instance_header_rec csi_datastructures_pub.instance_header_rec;
44 l_msg_count NUMBER (3);
45 l_msg_data VARCHAR2 (200);
46 l_msg_index NUMBER (3);
47 l_sql_error VARCHAR2 (2000);
48 l_api_version NUMBER (3) := 1.0;
49 l_init_msg_list VARCHAR2 (1) := fnd_api.g_true;
50 l_commit VARCHAR2 (1) := fnd_api.g_false;
51 e_error EXCEPTION;
52 x_party_header_tbl csi_datastructures_pub.party_header_tbl;
53 x_party_cache_tbl csi_datastructures_pub.party_header_tbl;
54 x_party_account_header_tbl csi_datastructures_pub.party_account_header_tbl;
55 x_org_units_header_tbl csi_datastructures_pub.org_units_header_tbl;
56 x_pricing_attribs_tbl csi_datastructures_pub.pricing_attribs_tbl;
57 x_ext_attrib_value_tbl csi_datastructures_pub.extend_attrib_values_tbl;
58 x_extend_attrib_tbl csi_datastructures_pub.extend_attrib_tbl;
59 x_instance_asset_header_tbl csi_datastructures_pub.instance_asset_header_tbl;
60 l_price_index PLS_INTEGER;
61 l_org_index PLS_INTEGER;
62 l_party_index PLS_INTEGER;
63 l_party_account_index PLS_INTEGER;
64 l_ieav_index PLS_INTEGER;
65 inst_index PLS_INTEGER;
66 i PLS_INTEGER;
67 b_end_dated BOOLEAN := TRUE;
68 l_miss_char CONSTANT VARCHAR2 (1) := fnd_api.g_miss_char;
69 l_miss_num CONSTANT NUMBER := fnd_api.g_miss_num;
70 l_miss_date CONSTANT DATE := fnd_api.g_miss_date;
71 r_instance_id NUMBER;
72 FUNCTION get_parent_tbl_index (
73 p_instance_party_id IN NUMBER,
74 p_relationship_type_code IN VARCHAR2
75 )
76 RETURN PLS_INTEGER
77 IS
78 l_miss_char CONSTANT VARCHAR2 (1) := fnd_api.g_miss_char;
79 l_miss_num CONSTANT NUMBER := fnd_api.g_miss_num;
80 l_index PLS_INTEGER;
81 BEGIN
82 l_index := NULL;
83 IF x_party_tbl.COUNT >= 1
84 THEN
85 FOR i IN x_party_tbl.FIRST .. x_party_tbl.LAST
86 LOOP
87 IF x_party_tbl.EXISTS (i)
88 AND NVL (x_party_tbl (i).instance_party_id, l_miss_num) =
89 NVL (p_instance_party_id, l_miss_num)
90 AND NVL (
91 x_party_tbl (i).relationship_type_code,
92 l_miss_char
93 ) = NVL (p_relationship_type_code, l_miss_char)
94 THEN
95 l_index := i;
96 END IF;
97 END LOOP;
98 END IF;
99
100 RETURN l_index;
101 EXCEPTION
102 WHEN OTHERS
103 THEN
104 RETURN NULL;
105 END get_parent_tbl_index;
106
107 FUNCTION get_obj_ver_num (
108 p_party_tbl IN csi_datastructures_pub.party_header_tbl,
109 p_instance_party_id IN NUMBER
110 )
111 RETURN PLS_INTEGER
112 IS
113 l_num NUMBER (30) := NULL;
114 BEGIN
115 IF p_party_tbl.COUNT >= 1
116 THEN
117 FOR i IN x_party_tbl.FIRST .. x_party_tbl.LAST
118 LOOP
119 IF p_party_tbl.EXISTS (i)
120 AND p_party_tbl (i).instance_party_id =
121 p_instance_party_id
122 THEN
123 l_num := p_party_tbl (i).object_version_number;
124 END IF;
125 END LOOP;
126 END IF;
127
128 RETURN l_num;
129 EXCEPTION
130 WHEN OTHERS
131 THEN
132 RETURN NULL;
133 END get_obj_ver_num;
134 BEGIN
135
136 --dbms_output.put_line('BEGIN Replace group id is '||p_group_id);
137
138 x_party_tbl.DELETE;
139 x_account_tbl.DELETE;
140 x_price_tbl.DELETE;
141 x_org_assign_tbl.DELETE;
142 x_eav_tbl.DELETE;
143
144 FOR csi_intf_repl_rec IN csi_intf_repl_cur (p_txn_identifier,
145 p_source_system_name)
146 LOOP
147 BEGIN
148 SAVEPOINT instance_entity;
149 l_instance_header_rec.instance_id :=
150 csi_intf_repl_rec.instance_id;
151
152 --dbms_output.put_line('calling get item instance details ');
153 csi_item_instance_pub.get_item_instance_details (
154 p_api_version=> l_api_version,
155 p_commit=> l_commit,
156 p_init_msg_list=> l_init_msg_list,
157 p_validation_level=> fnd_api.g_valid_level_full,
158 p_instance_rec=> l_instance_header_rec,
159 p_get_parties=> fnd_api.g_true,
160 p_party_header_tbl=> x_party_header_tbl,
161 p_get_accounts=> fnd_api.g_true,
162 p_account_header_tbl=> x_party_account_header_tbl,
163 p_get_org_assignments=> fnd_api.g_true,
164 p_org_header_tbl=> x_org_units_header_tbl,
165 p_get_pricing_attribs=> fnd_api.g_true,
166 p_pricing_attrib_tbl=> x_pricing_attribs_tbl,
167 p_get_ext_attribs=> fnd_api.g_true,
168 p_ext_attrib_tbl=> x_ext_attrib_value_tbl,
169 p_ext_attrib_def_tbl=> x_extend_attrib_tbl,
170 p_get_asset_assignments=> fnd_api.g_false,
171 p_asset_header_tbl=> x_instance_asset_header_tbl,
172 p_resolve_id_columns=> fnd_api.g_false,
173 p_time_stamp=> NULL,
174 x_return_status=> x_return_status,
175 x_msg_count=> l_msg_count,
176 x_msg_data=> l_msg_data
177 );
178
179
180 --dbms_output.put_line('after get item instance details status : '||x_return_status);
181 IF x_return_status <> fnd_api.g_ret_sts_success
182 THEN
183 l_msg_index := 1;
184 x_error_message := l_msg_data;
185
186 WHILE l_msg_count > 0
187 LOOP
188 x_error_message := x_error_message
189 || fnd_msg_pub.get (
190 l_msg_index,
191 fnd_api.g_false
192 );
193 l_msg_index := l_msg_index
194 + 1;
195 l_msg_count := l_msg_count
196 - 1;
197 END LOOP;
198
199 RAISE e_error;
200 END IF;
201
202 x_instance_tbl(inst_index).instance_id := csi_intf_repl_rec.instance_id;
203 x_instance_tbl(inst_index).vld_organization_id :=
204 csi_intf_repl_rec.inv_vld_organization_id;
205
206
207 --dbms_output.put_line('after get item instance details ');
208 IF NOT ( NVL (csi_intf_repl_rec.location_id, l_miss_num) =
209 NVL (x_instance_tbl(inst_index).location_id, l_miss_num)
210 AND NVL (
211 csi_intf_repl_rec.inventory_revision,
212 l_miss_char
213 ) = NVL (
214 x_instance_tbl(inst_index).inventory_revision,
215 l_miss_char
216 )
217 AND NVL (csi_intf_repl_rec.lot_number, l_miss_char) =
218 NVL (x_instance_tbl(inst_index).lot_number, l_miss_char)
219 AND NVL (csi_intf_repl_rec.quantity, l_miss_num) =
220 NVL (x_instance_tbl(inst_index).location_id, l_miss_num)
221 AND NVL (csi_intf_repl_rec.unit_of_measure, l_miss_char) =
222 NVL (x_instance_tbl(inst_index).unit_of_measure, l_miss_char)
223 AND NVL (
224 csi_intf_repl_rec.accounting_class_code,
225 l_miss_char
226 ) = NVL (
227 x_instance_tbl(inst_index).accounting_class_code,
228 l_miss_char
229 )
230 AND NVL (csi_intf_repl_rec.instance_end_date, l_miss_date) =
231 NVL (x_instance_tbl(inst_index).active_end_date, l_miss_date)
232 AND NVL (
233 csi_intf_repl_rec.inv_subinventory_name,
234 l_miss_char
235 ) = NVL (
236 x_instance_tbl(inst_index).inv_subinventory_name,
237 l_miss_char
238 )
239 AND NVL (csi_intf_repl_rec.inv_locator_id, l_miss_num) =
240 NVL (x_instance_tbl(inst_index).inv_locator_id, l_miss_num)
241 AND NVL (csi_intf_repl_rec.project_id, l_miss_num) =
242 NVL (x_instance_tbl(inst_index).pa_project_id, l_miss_num)
243 AND NVL (csi_intf_repl_rec.task_id, l_miss_num) =
244 NVL (x_instance_tbl(inst_index).pa_project_task_id, l_miss_num)
245 AND NVL (
246 csi_intf_repl_rec.in_transit_order_line_id,
247 l_miss_num
248 ) = NVL (
249 x_instance_tbl(inst_index).in_transit_order_line_id,
250 l_miss_num
251 )
252 AND NVL (csi_intf_repl_rec.wip_job_id, l_miss_num) =
253 NVL (x_instance_tbl(inst_index).wip_job_id, l_miss_num)
254 AND NVL (csi_intf_repl_rec.po_order_line_id, l_miss_num) =
255 NVL (x_instance_tbl(inst_index).po_order_line_id, l_miss_num)
256 AND NVL (csi_intf_repl_rec.oe_order_line_id, l_miss_num) =
257 NVL (
258 x_instance_tbl(inst_index).last_oe_order_line_id,
259 l_miss_num
260 )
261 AND NVL (csi_intf_repl_rec.install_date, l_miss_date) =
262 NVL (x_instance_tbl(inst_index).install_date, l_miss_date)
263 AND NVL (csi_intf_repl_rec.return_by_date, l_miss_date) =
264 NVL (x_instance_tbl(inst_index).return_by_date, l_miss_date)
265 AND NVL (
266 csi_intf_repl_rec.actual_return_date,
267 l_miss_date
268 ) = NVL (
269 x_instance_tbl(inst_index).actual_return_date,
270 l_miss_date
271 )
272 AND NVL (csi_intf_repl_rec.instance_context, l_miss_char) =
273 NVL (x_instance_tbl(inst_index).CONTEXT, l_miss_char)
274 AND NVL (
275 csi_intf_repl_rec.instance_attribute1,
276 l_miss_char
277 ) = NVL (x_instance_tbl(inst_index).attribute1, l_miss_char)
278 AND NVL (
279 csi_intf_repl_rec.instance_attribute2,
280 l_miss_char
281 ) = NVL (x_instance_tbl(inst_index).attribute2, l_miss_char)
282 AND NVL (
283 csi_intf_repl_rec.instance_attribute3,
284 l_miss_char
285 ) = NVL (x_instance_tbl(inst_index).attribute3, l_miss_char)
286 AND NVL (
287 csi_intf_repl_rec.instance_attribute4,
288 l_miss_char
289 ) = NVL (x_instance_tbl(inst_index).attribute4, l_miss_char)
290 AND NVL (
291 csi_intf_repl_rec.instance_attribute5,
292 l_miss_char
293 ) = NVL (x_instance_tbl(inst_index).attribute5, l_miss_char)
294 AND NVL (
295 csi_intf_repl_rec.instance_attribute6,
296 l_miss_char
297 ) = NVL (x_instance_tbl(inst_index).attribute6, l_miss_char)
298 AND NVL (
299 csi_intf_repl_rec.instance_attribute7,
300 l_miss_char
301 ) = NVL (x_instance_tbl(inst_index).attribute7, l_miss_char)
302 AND NVL (
303 csi_intf_repl_rec.instance_attribute8,
304 l_miss_char
305 ) = NVL (x_instance_tbl(inst_index).attribute8, l_miss_char)
306 AND NVL (
307 csi_intf_repl_rec.instance_attribute9,
308 l_miss_char
309 ) = NVL (x_instance_tbl(inst_index).attribute9, l_miss_char)
310 AND NVL (
311 csi_intf_repl_rec.instance_attribute10,
312 l_miss_char
313 ) = NVL (x_instance_tbl(inst_index).attribute10, l_miss_char)
314 AND NVL (
315 csi_intf_repl_rec.instance_attribute11,
316 l_miss_char
317 ) = NVL (x_instance_tbl(inst_index).attribute11, l_miss_char)
318 AND NVL (
319 csi_intf_repl_rec.instance_attribute12,
320 l_miss_char
321 ) = NVL (x_instance_tbl(inst_index).attribute12, l_miss_char)
322 AND NVL (
323 csi_intf_repl_rec.instance_attribute13,
324 l_miss_char
325 ) = NVL (x_instance_tbl(inst_index).attribute13, l_miss_char)
326 AND NVL (
327 csi_intf_repl_rec.instance_attribute14,
328 l_miss_char
329 ) = NVL (x_instance_tbl(inst_index).attribute14, l_miss_char)
330 AND NVL (
331 csi_intf_repl_rec.instance_attribute15,
332 l_miss_char
333 ) = NVL (x_instance_tbl(inst_index).attribute15, l_miss_char)
334 )
335 THEN
336
337 --dbms_output.put_line('instance changes are there');
338 SELECT DECODE (
339 csi_intf_repl_rec.location_id,
340 l_instance_header_rec.location_id, l_miss_num,
341 NULL, DECODE (
342 l_instance_header_rec.location_id,
343 NULL, l_miss_num,
344 NULL
345 ),
346 csi_intf_repl_rec.location_id
347 ),
348 DECODE (
349 csi_intf_repl_rec.inventory_revision,
350 l_instance_header_rec.inventory_revision, l_miss_char,
351 NULL, DECODE (
352 l_instance_header_rec.inventory_revision,
353 NULL, l_miss_char,
354 NULL
355 ),
356 csi_intf_repl_rec.inventory_revision
357 ),
358 DECODE (
359 csi_intf_repl_rec.lot_number,
360 l_instance_header_rec.lot_number, l_miss_char,
361 NULL, DECODE (
362 l_instance_header_rec.lot_number,
363 NULL, l_miss_char,
364 NULL
365 ),
366 csi_intf_repl_rec.lot_number
367 ),
368 DECODE (
369 csi_intf_repl_rec.quantity,
370 l_instance_header_rec.quantity, l_miss_num,
371 NULL, DECODE (
372 l_instance_header_rec.quantity,
373 NULL, l_miss_num,
374 NULL
375 ),
376 csi_intf_repl_rec.quantity
377 ),
378 DECODE (
379 csi_intf_repl_rec.unit_of_measure_code,
380 l_instance_header_rec.unit_of_measure, l_miss_char,
381 NULL, DECODE (
382 l_instance_header_rec.unit_of_measure,
383 NULL, l_miss_char,
384 NULL
385 ),
386 csi_intf_repl_rec.unit_of_measure_code
387 ),
388 DECODE (
389 csi_intf_repl_rec.accounting_class_code,
390 l_instance_header_rec.accounting_class_code, l_miss_char,
391 NULL, DECODE (
392 l_instance_header_rec.accounting_class_code,
393 NULL, l_miss_char,
394 NULL
395 ),
396 csi_intf_repl_rec.accounting_class_code
397 ),
398 DECODE (
399 csi_intf_repl_rec.instance_end_date,
400 l_instance_header_rec.active_end_date, l_miss_date,
401 NULL, DECODE (
402 l_instance_header_rec.active_end_date,
403 NULL, l_miss_date,
404 NULL
405 ),
406 csi_intf_repl_rec.instance_end_date
407 ),
408 DECODE (
409 csi_intf_repl_rec.location_type_code,
410 l_instance_header_rec.location_type_code, l_miss_char,
411 NULL, DECODE (
412 l_instance_header_rec.location_type_code,
413 NULL, l_miss_char,
414 NULL
415 ),
416 csi_intf_repl_rec.location_type_code
417 ),
418 DECODE (
419 csi_intf_repl_rec.inv_subinventory_name,
420 l_instance_header_rec.inv_subinventory_name, l_miss_char,
421 NULL, DECODE (
422 l_instance_header_rec.inv_subinventory_name,
423 NULL, l_miss_char,
424 NULL
425 ),
426 csi_intf_repl_rec.inv_subinventory_name
427 ),
428 DECODE (
429 csi_intf_repl_rec.inv_locator_id,
430 l_instance_header_rec.inv_locator_id, l_miss_num,
431 NULL, DECODE (
432 l_instance_header_rec.inv_locator_id,
433 NULL, l_miss_num,
434 NULL
435 ),
436 csi_intf_repl_rec.inv_locator_id
437 ),
438 DECODE (
439 csi_intf_repl_rec.project_id,
440 l_instance_header_rec.pa_project_id, l_miss_num,
441 NULL, DECODE (
442 l_instance_header_rec.pa_project_id,
443 NULL, l_miss_num,
444 NULL
445 ),
446 csi_intf_repl_rec.project_id
447 ),
448 DECODE (
449 csi_intf_repl_rec.task_id,
450 l_instance_header_rec.pa_project_task_id, l_miss_num,
451 NULL, DECODE (
452 l_instance_header_rec.pa_project_task_id,
453 NULL, l_miss_num,
454 NULL
455 ),
456 csi_intf_repl_rec.task_id
457 ),
458 DECODE (
459 csi_intf_repl_rec.in_transit_order_line_id,
460 l_instance_header_rec.in_transit_order_line_id, l_miss_num,
461 NULL, DECODE (
462 l_instance_header_rec.in_transit_order_line_id,
463 NULL, l_miss_num,
464 NULL
465 ),
466 csi_intf_repl_rec.in_transit_order_line_id
467 ),
468 DECODE (
469 csi_intf_repl_rec.wip_job_id,
470 l_instance_header_rec.wip_job_id, l_miss_num,
471 NULL, DECODE (
472 l_instance_header_rec.wip_job_id,
473 NULL, l_miss_num,
474 NULL
475 ),
476 csi_intf_repl_rec.wip_job_id
477 ),
478 DECODE (
479 csi_intf_repl_rec.po_order_line_id,
480 l_instance_header_rec.po_order_line_id, l_miss_num,
481 NULL, DECODE (
482 l_instance_header_rec.po_order_line_id,
483 NULL, l_miss_num,
484 NULL
485 ),
486 csi_intf_repl_rec.po_order_line_id
487 ),
488 DECODE (
489 csi_intf_repl_rec.oe_order_line_id,
490 l_instance_header_rec.last_oe_order_line_id, l_miss_num,
491 NULL, DECODE (
492 l_instance_header_rec.last_oe_order_line_id,
493 NULL, l_miss_num,
494 NULL
495 ),
496 csi_intf_repl_rec.oe_order_line_id
497 ),
498 DECODE (
499 csi_intf_repl_rec.oe_rma_line_id,
500 l_instance_header_rec.last_oe_rma_line_id, l_miss_num,
501 NULL, DECODE (
502 l_instance_header_rec.last_oe_rma_line_id,
503 NULL, l_miss_num,
504 NULL
505 ),
506 csi_intf_repl_rec.oe_rma_line_id
507 ),
508 DECODE (
509 csi_intf_repl_rec.po_order_line_id,
510 l_instance_header_rec.last_po_po_line_id, l_miss_num,
511 NULL, DECODE (
512 l_instance_header_rec.po_order_line_id,
513 NULL, l_miss_num,
514 NULL
515 ),
516 csi_intf_repl_rec.po_order_line_id
517 ),
518 DECODE (
519 csi_intf_repl_rec.oe_po_number,
520 l_instance_header_rec.last_oe_po_number, l_miss_char,
521 NULL, DECODE (
522 l_instance_header_rec.last_oe_po_number,
523 NULL, l_miss_char,
524 NULL
525 ),
526 csi_intf_repl_rec.oe_po_number
527 ),
528 DECODE (
529 csi_intf_repl_rec.install_date,
530 l_instance_header_rec.install_date, l_miss_date,
531 NULL, DECODE (
532 l_instance_header_rec.install_date,
533 NULL, l_miss_date,
534 NULL
535 ),
536 csi_intf_repl_rec.install_date
537 ),
538 DECODE (
539 csi_intf_repl_rec.return_by_date,
540 l_instance_header_rec.return_by_date, l_miss_date,
541 NULL, DECODE (
542 l_instance_header_rec.return_by_date,
543 NULL, l_miss_date,
544 NULL
545 ),
546 csi_intf_repl_rec.return_by_date
547 ),
548 DECODE (
549 csi_intf_repl_rec.actual_return_date,
550 l_instance_header_rec.actual_return_date, l_miss_date,
551 NULL, DECODE (
552 l_instance_header_rec.actual_return_date,
553 NULL, l_miss_date,
554 NULL
555 ),
556 csi_intf_repl_rec.actual_return_date
557 ),
558 DECODE (
559 csi_intf_repl_rec.instance_context,
560 l_instance_header_rec.CONTEXT, l_miss_char,
561 NULL, DECODE (
562 l_instance_header_rec.CONTEXT,
563 NULL, l_miss_char,
564 NULL
565 ),
566 csi_intf_repl_rec.instance_context
567 ),
568 DECODE (
569 csi_intf_repl_rec.instance_attribute1,
570 l_instance_header_rec.attribute1, l_miss_char,
571 NULL, DECODE (
572 l_instance_header_rec.attribute1,
573 NULL, l_miss_char,
574 NULL
575 ),
576 csi_intf_repl_rec.instance_attribute1
577 ),
578 DECODE (
579 csi_intf_repl_rec.instance_attribute2,
580 l_instance_header_rec.attribute2, l_miss_char,
581 NULL, DECODE (
582 l_instance_header_rec.attribute2,
583 NULL, l_miss_char,
584 NULL
585 ),
586 csi_intf_repl_rec.instance_attribute2
587 ),
588 DECODE (
589 csi_intf_repl_rec.instance_attribute3,
590 l_instance_header_rec.attribute3, l_miss_char,
591 NULL, DECODE (
592 l_instance_header_rec.attribute3,
593 NULL, l_miss_char,
594 NULL
595 ),
596 csi_intf_repl_rec.instance_attribute3
597 ),
598 DECODE (
599 csi_intf_repl_rec.instance_attribute4,
600 l_instance_header_rec.attribute4, l_miss_char,
601 NULL, DECODE (
602 l_instance_header_rec.attribute4,
603 NULL, l_miss_char,
604 NULL
605 ),
606 csi_intf_repl_rec.instance_attribute4
607 ),
608 DECODE (
609 csi_intf_repl_rec.instance_attribute5,
610 l_instance_header_rec.attribute5, l_miss_char,
611 NULL, DECODE (
612 l_instance_header_rec.attribute5,
613 NULL, l_miss_char,
614 NULL
615 ),
616 csi_intf_repl_rec.instance_attribute5
617 ),
618 DECODE (
619 csi_intf_repl_rec.instance_attribute6,
620 l_instance_header_rec.attribute6, l_miss_char,
621 NULL, DECODE (
622 l_instance_header_rec.attribute6,
623 NULL, l_miss_char,
624 NULL
625 ),
626 csi_intf_repl_rec.instance_attribute6
627 ),
628 DECODE (
629 csi_intf_repl_rec.instance_attribute7,
630 l_instance_header_rec.attribute7, l_miss_char,
631 NULL, DECODE (
632 l_instance_header_rec.attribute7,
633 NULL, l_miss_char,
634 NULL
635 ),
636 csi_intf_repl_rec.instance_attribute7
637 ),
638 DECODE (
639 csi_intf_repl_rec.instance_attribute8,
640 l_instance_header_rec.attribute8, l_miss_char,
641 NULL, DECODE (
642 l_instance_header_rec.attribute8,
643 NULL, l_miss_char,
644 NULL
645 ),
646 csi_intf_repl_rec.instance_attribute8
647 ),
648 DECODE (
649 csi_intf_repl_rec.instance_attribute9,
650 l_instance_header_rec.attribute9, l_miss_char,
651 NULL, DECODE (
652 l_instance_header_rec.attribute9,
653 NULL, l_miss_char,
654 NULL
655 ),
656 csi_intf_repl_rec.instance_attribute9
657 ),
658 DECODE (
659 csi_intf_repl_rec.instance_attribute10,
660 l_instance_header_rec.attribute10, l_miss_char,
661 NULL, DECODE (
662 l_instance_header_rec.attribute10,
663 NULL, l_miss_char,
664 NULL
665 ),
666 csi_intf_repl_rec.instance_attribute10
667 ),
668 DECODE (
669 csi_intf_repl_rec.instance_attribute11,
670 l_instance_header_rec.attribute11, l_miss_char,
671 NULL, DECODE (
672 l_instance_header_rec.attribute11,
673 NULL, l_miss_char,
674 NULL
675 ),
676 csi_intf_repl_rec.instance_attribute11
677 ),
678 DECODE (
679 csi_intf_repl_rec.instance_attribute12,
680 l_instance_header_rec.attribute12, l_miss_char,
681 NULL, DECODE (
682 l_instance_header_rec.attribute12,
683 NULL, l_miss_char,
684 NULL
685 ),
686 csi_intf_repl_rec.instance_attribute12
687 ),
688 DECODE (
689 csi_intf_repl_rec.instance_attribute13,
690 l_instance_header_rec.attribute13, l_miss_char,
691 NULL, DECODE (
692 l_instance_header_rec.attribute13,
693 NULL, l_miss_char,
694 NULL
695 ),
696 csi_intf_repl_rec.instance_attribute13
697 ),
698 DECODE (
699 csi_intf_repl_rec.instance_attribute14,
700 l_instance_header_rec.attribute14, l_miss_char,
701 NULL, DECODE (
702 l_instance_header_rec.attribute14,
703 NULL, l_miss_char,
704 NULL
705 ),
706 csi_intf_repl_rec.instance_attribute14
707 ),
708 DECODE (
709 csi_intf_repl_rec.instance_attribute15,
710 l_instance_header_rec.attribute15, l_miss_char,
711 NULL, DECODE (
712 l_instance_header_rec.attribute15,
713 NULL, l_miss_char,
714 NULL
715 ),
716 csi_intf_repl_rec.instance_attribute15
717 ),
718 l_instance_header_rec.object_version_number
719 INTO x_instance_tbl(inst_index).location_id,
720 x_instance_tbl(inst_index).inventory_revision,
721 x_instance_tbl(inst_index).lot_number,
722 x_instance_tbl(inst_index).quantity,
723 x_instance_tbl(inst_index).unit_of_measure,
724 x_instance_tbl(inst_index).accounting_class_code,
725 x_instance_tbl(inst_index).active_end_date,
726 x_instance_tbl(inst_index).location_type_code,
727 x_instance_tbl(inst_index).inv_subinventory_name,
728 x_instance_tbl(inst_index).inv_locator_id,
729 x_instance_tbl(inst_index).pa_project_id,
730 x_instance_tbl(inst_index).pa_project_task_id,
731 x_instance_tbl(inst_index).in_transit_order_line_id,
732 x_instance_tbl(inst_index).wip_job_id,
733 x_instance_tbl(inst_index).po_order_line_id,
734 x_instance_tbl(inst_index).last_oe_order_line_id,
735 x_instance_tbl(inst_index).last_oe_rma_line_id,
736 x_instance_tbl(inst_index).last_po_po_line_id,
737 x_instance_tbl(inst_index).last_oe_po_number,
738 x_instance_tbl(inst_index).install_date,
739 x_instance_tbl(inst_index).return_by_date,
740 x_instance_tbl(inst_index).actual_return_date,
741 x_instance_tbl(inst_index).CONTEXT,
742 x_instance_tbl(inst_index).attribute1,
743 x_instance_tbl(inst_index).attribute2,
744 x_instance_tbl(inst_index).attribute3,
745 x_instance_tbl(inst_index).attribute4,
746 x_instance_tbl(inst_index).attribute5,
747 x_instance_tbl(inst_index).attribute6,
748 x_instance_tbl(inst_index).attribute7,
749 x_instance_tbl(inst_index).attribute8,
750 x_instance_tbl(inst_index).attribute9,
751 x_instance_tbl(inst_index).attribute10,
752 x_instance_tbl(inst_index).attribute11,
753 x_instance_tbl(inst_index).attribute12,
754 x_instance_tbl(inst_index).attribute13,
755 x_instance_tbl(inst_index).attribute14,
756 x_instance_tbl(inst_index).attribute15,
757 x_instance_tbl(inst_index).object_version_number
758 FROM DUAL;
759 END IF;
760
761
762 --dbms_output.put_line('after instance accounting class code:'||x_instance_tbl(inst_index).accounting_class_code);
763 --dbms_output.put_line('after instance next pricing attributes');
764 -- cases handled
765 -- current pricing attributes NONE - new NONE - no changes
766 -- current pricing attributes NONE - new ONE - to create
767 -- current pricing attributes ONE - new ONE - NO changes
768 -- current pricing attributes ONE - new ONE - changed attributes
769 -- current pricing attributes more than ONE - new more than ONE - changed attributes
770 -- current pricing attributes more than ONE - new more than ONE - no changes
771 IF x_pricing_attribs_tbl.COUNT >= 1
772 THEN
773 FOR i IN
774 x_pricing_attribs_tbl.FIRST .. x_pricing_attribs_tbl.LAST
775 LOOP
776 IF x_pricing_attribs_tbl.EXISTS (i)
777 AND x_pricing_attribs_tbl (i).active_end_date IS NOT NULL
778 THEN
779 x_pricing_attribs_tbl.DELETE (i);
780 END IF;
781 END LOOP;
782 END IF;
783
784
785 --dbms_output.put_line('Pricing Zero :'||x_pricing_Attribs_tbl.COUNT);
786
787 IF (x_pricing_attribs_tbl.COUNT >= 1)
788 THEN
789 FOR i IN
790 x_pricing_attribs_tbl.FIRST .. x_pricing_attribs_tbl.LAST
791 LOOP
792 IF x_pricing_attribs_tbl.EXISTS (i)
793 AND csi_intf_repl_rec.pricing_attribute_id IS NOT NULL
794 AND x_pricing_attribs_tbl (i).pricing_attribute_id =
795 csi_intf_repl_rec.pricing_attribute_id
796 THEN
797
798 --dbms_output.put_line('pricing attributes two');
799 IF NOT ( NVL (
800 x_pricing_attribs_tbl (i).pricing_attribute1,
801 l_miss_char
802 ) = NVL (
803 csi_intf_repl_rec.pricing_attribute1,
804 l_miss_char
805 )
806 AND NVL (
807 x_pricing_attribs_tbl (i).pricing_attribute2,
808 l_miss_char
809 ) = NVL (
810 csi_intf_repl_rec.pricing_attribute2,
811 l_miss_char
812 )
813 AND NVL (
814 x_pricing_attribs_tbl (i).pricing_attribute3,
815 l_miss_char
816 ) = NVL (
817 csi_intf_repl_rec.pricing_attribute3,
818 l_miss_char
819 )
820 AND NVL (
821 x_pricing_attribs_tbl (i).pricing_attribute4,
822 l_miss_char
823 ) = NVL (
824 csi_intf_repl_rec.pricing_attribute4,
825 l_miss_char
826 )
827 AND NVL (
828 x_pricing_attribs_tbl (i).pricing_attribute5,
829 l_miss_char
830 ) = NVL (
831 csi_intf_repl_rec.pricing_attribute5,
832 l_miss_char
833 )
834 AND NVL (
835 x_pricing_attribs_tbl (i).pricing_attribute6,
836 l_miss_char
837 ) = NVL (
838 csi_intf_repl_rec.pricing_attribute6,
839 l_miss_char
840 )
841 AND NVL (
842 x_pricing_attribs_tbl (i).pricing_attribute7,
843 l_miss_char
844 ) = NVL (
845 csi_intf_repl_rec.pricing_attribute7,
846 l_miss_char
847 )
848 AND NVL (
849 x_pricing_attribs_tbl (i).pricing_attribute8,
850 l_miss_char
851 ) = NVL (
852 csi_intf_repl_rec.pricing_attribute8,
853 l_miss_char
854 )
855 AND NVL (
856 x_pricing_attribs_tbl (i).pricing_attribute9,
857 l_miss_char
858 ) = NVL (
859 csi_intf_repl_rec.pricing_attribute9,
860 l_miss_char
861 )
862 AND NVL (
863 x_pricing_attribs_tbl (i).pricing_attribute10,
864 l_miss_char
865 ) =
866 NVL (
867 csi_intf_repl_rec.pricing_attribute10,
868 l_miss_char
869 )
870 AND NVL (
871 x_pricing_attribs_tbl (i).pricing_attribute11,
872 l_miss_char
873 ) =
874 NVL (
875 csi_intf_repl_rec.pricing_attribute11,
876 l_miss_char
877 )
878 AND NVL (
879 x_pricing_attribs_tbl (i).pricing_attribute12,
880 l_miss_char
881 ) =
882 NVL (
883 csi_intf_repl_rec.pricing_attribute12,
884 l_miss_char
885 )
886 AND NVL (
887 x_pricing_attribs_tbl (i).pricing_attribute13,
888 l_miss_char
889 ) =
890 NVL (
891 csi_intf_repl_rec.pricing_attribute13,
892 l_miss_char
893 )
894 AND NVL (
895 x_pricing_attribs_tbl (i).pricing_attribute14,
896 l_miss_char
897 ) =
898 NVL (
899 csi_intf_repl_rec.pricing_attribute14,
900 l_miss_char
901 )
902 AND NVL (
903 x_pricing_attribs_tbl (i).pricing_attribute15,
904 l_miss_char
905 ) =
906 NVL (
907 csi_intf_repl_rec.pricing_attribute15,
908 l_miss_char
909 )
910 AND NVL (
911 x_pricing_attribs_tbl (i).pricing_attribute16,
912 l_miss_char
913 ) =
914 NVL (
915 csi_intf_repl_rec.pricing_attribute16,
916 l_miss_char
917 )
918 AND NVL (
919 x_pricing_attribs_tbl (i).pricing_attribute17,
920 l_miss_char
921 ) =
922 NVL (
923 csi_intf_repl_rec.pricing_attribute17,
924 l_miss_char
925 )
926 AND NVL (
927 x_pricing_attribs_tbl (i).pricing_attribute18,
928 l_miss_char
929 ) =
930 NVL (
931 csi_intf_repl_rec.pricing_attribute18,
932 l_miss_char
933 )
934 AND NVL (
935 x_pricing_attribs_tbl (i).pricing_attribute19,
936 l_miss_char
937 ) =
938 NVL (
939 csi_intf_repl_rec.pricing_attribute19,
940 l_miss_char
941 )
942 AND NVL (
943 x_pricing_attribs_tbl (i).pricing_attribute20,
944 l_miss_char
945 ) =
946 NVL (
947 csi_intf_repl_rec.pricing_attribute20,
948 l_miss_char
949 )
950 AND NVL (
951 x_pricing_attribs_tbl (i).pricing_attribute21,
952 l_miss_char
953 ) =
954 NVL (
955 csi_intf_repl_rec.pricing_attribute21,
956 l_miss_char
957 )
958 AND NVL (
959 x_pricing_attribs_tbl (i).pricing_attribute22,
960 l_miss_char
961 ) =
962 NVL (
963 csi_intf_repl_rec.pricing_attribute22,
964 l_miss_char
965 )
966 AND NVL (
967 x_pricing_attribs_tbl (i).pricing_attribute23,
968 l_miss_char
969 ) =
970 NVL (
971 csi_intf_repl_rec.pricing_attribute23,
972 l_miss_char
973 )
974 AND NVL (
975 x_pricing_attribs_tbl (i).pricing_attribute24,
976 l_miss_char
977 ) =
978 NVL (
979 csi_intf_repl_rec.pricing_attribute24,
980 l_miss_char
981 )
982 AND NVL (
983 x_pricing_attribs_tbl (i).pricing_attribute25,
984 l_miss_char
985 ) =
986 NVL (
987 csi_intf_repl_rec.pricing_attribute25,
988 l_miss_char
989 )
990 AND NVL (
991 x_pricing_attribs_tbl (i).pricing_attribute26,
992 l_miss_char
993 ) =
994 NVL (
995 csi_intf_repl_rec.pricing_attribute26,
996 l_miss_char
997 )
998 AND NVL (
999 x_pricing_attribs_tbl (i).pricing_attribute27,
1000 l_miss_char
1001 ) =
1002 NVL (
1003 csi_intf_repl_rec.pricing_attribute27,
1004 l_miss_char
1005 )
1006 AND NVL (
1007 x_pricing_attribs_tbl (i).pricing_attribute28,
1008 l_miss_char
1009 ) =
1010 NVL (
1011 csi_intf_repl_rec.pricing_attribute28,
1012 l_miss_char
1013 )
1014 AND NVL (
1015 x_pricing_attribs_tbl (i).pricing_attribute29,
1016 l_miss_char
1017 ) =
1018 NVL (
1019 csi_intf_repl_rec.pricing_attribute29,
1020 l_miss_char
1021 )
1022 AND NVL (
1023 x_pricing_attribs_tbl (i).pricing_attribute30,
1024 l_miss_char
1025 ) =
1026 NVL (
1027 csi_intf_repl_rec.pricing_attribute30,
1028 l_miss_char
1029 )
1030 AND NVL (
1031 x_pricing_attribs_tbl (i).pricing_attribute31,
1032 l_miss_char
1033 ) =
1034 NVL (
1035 csi_intf_repl_rec.pricing_attribute31,
1036 l_miss_char
1037 )
1038 AND NVL (
1039 x_pricing_attribs_tbl (i).pricing_attribute32,
1040 l_miss_char
1041 ) =
1042 NVL (
1043 csi_intf_repl_rec.pricing_attribute32,
1044 l_miss_char
1045 )
1046 AND NVL (
1047 x_pricing_attribs_tbl (i).pricing_attribute33,
1048 l_miss_char
1049 ) =
1050 NVL (
1051 csi_intf_repl_rec.pricing_attribute33,
1052 l_miss_char
1053 )
1054 AND NVL (
1055 x_pricing_attribs_tbl (i).pricing_attribute34,
1056 l_miss_char
1057 ) =
1058 NVL (
1059 csi_intf_repl_rec.pricing_attribute34,
1060 l_miss_char
1061 )
1062 AND NVL (
1063 x_pricing_attribs_tbl (i).pricing_attribute35,
1064 l_miss_char
1065 ) =
1066 NVL (
1067 csi_intf_repl_rec.pricing_attribute35,
1068 l_miss_char
1069 )
1070 AND NVL (
1071 x_pricing_attribs_tbl (i).pricing_attribute36,
1072 l_miss_char
1073 ) =
1074 NVL (
1075 csi_intf_repl_rec.pricing_attribute36,
1076 l_miss_char
1077 )
1078 AND NVL (
1079 x_pricing_attribs_tbl (i).pricing_attribute37,
1080 l_miss_char
1081 ) =
1082 NVL (
1083 csi_intf_repl_rec.pricing_attribute37,
1084 l_miss_char
1085 )
1086 AND NVL (
1087 x_pricing_attribs_tbl (i).pricing_attribute38,
1088 l_miss_char
1089 ) =
1090 NVL (
1091 csi_intf_repl_rec.pricing_attribute38,
1092 l_miss_char
1093 )
1094 AND NVL (
1095 x_pricing_attribs_tbl (i).pricing_attribute39,
1096 l_miss_char
1097 ) =
1098 NVL (
1099 csi_intf_repl_rec.pricing_attribute39,
1100 l_miss_char
1101 )
1102 AND NVL (
1103 x_pricing_attribs_tbl (i).pricing_attribute40,
1104 l_miss_char
1105 ) =
1106 NVL (
1107 csi_intf_repl_rec.pricing_attribute40,
1108 l_miss_char
1109 )
1110 AND NVL (
1111 x_pricing_attribs_tbl (i).pricing_attribute41,
1112 l_miss_char
1113 ) =
1114 NVL (
1115 csi_intf_repl_rec.pricing_attribute41,
1116 l_miss_char
1117 )
1118 AND NVL (
1119 x_pricing_attribs_tbl (i).pricing_attribute42,
1120 l_miss_char
1121 ) =
1122 NVL (
1123 csi_intf_repl_rec.pricing_attribute42,
1124 l_miss_char
1125 )
1126 AND NVL (
1127 x_pricing_attribs_tbl (i).pricing_attribute43,
1128 l_miss_char
1129 ) =
1130 NVL (
1131 csi_intf_repl_rec.pricing_attribute43,
1132 l_miss_char
1133 )
1134 AND NVL (
1135 x_pricing_attribs_tbl (i).pricing_attribute44,
1136 l_miss_char
1137 ) =
1138 NVL (
1139 csi_intf_repl_rec.pricing_attribute44,
1140 l_miss_char
1141 )
1142 AND NVL (
1143 x_pricing_attribs_tbl (i).pricing_attribute45,
1144 l_miss_char
1145 ) =
1146 NVL (
1147 csi_intf_repl_rec.pricing_attribute45,
1148 l_miss_char
1149 )
1150 AND NVL (
1151 x_pricing_attribs_tbl (i).pricing_attribute46,
1152 l_miss_char
1153 ) =
1154 NVL (
1155 csi_intf_repl_rec.pricing_attribute46,
1156 l_miss_char
1157 )
1158 AND NVL (
1159 x_pricing_attribs_tbl (i).pricing_attribute47,
1160 l_miss_char
1161 ) =
1162 NVL (
1163 csi_intf_repl_rec.pricing_attribute47,
1164 l_miss_char
1165 )
1166 AND NVL (
1167 x_pricing_attribs_tbl (i).pricing_attribute48,
1168 l_miss_char
1169 ) =
1170 NVL (
1171 csi_intf_repl_rec.pricing_attribute48,
1172 l_miss_char
1173 )
1174 AND NVL (
1175 x_pricing_attribs_tbl (i).pricing_attribute49,
1176 l_miss_char
1177 ) =
1178 NVL (
1179 csi_intf_repl_rec.pricing_attribute49,
1180 l_miss_char
1181 )
1182 AND NVL (
1183 x_pricing_attribs_tbl (i).pricing_attribute50,
1184 l_miss_char
1185 ) =
1186 NVL (
1187 csi_intf_repl_rec.pricing_attribute50,
1188 l_miss_char
1189 )
1190 AND NVL (
1191 x_pricing_attribs_tbl (i).pricing_attribute51,
1192 l_miss_char
1193 ) =
1194 NVL (
1195 csi_intf_repl_rec.pricing_attribute51,
1196 l_miss_char
1197 )
1198 AND NVL (
1199 x_pricing_attribs_tbl (i).pricing_attribute52,
1200 l_miss_char
1201 ) =
1202 NVL (
1203 csi_intf_repl_rec.pricing_attribute52,
1204 l_miss_char
1205 )
1206 AND NVL (
1207 x_pricing_attribs_tbl (i).pricing_attribute53,
1208 l_miss_char
1209 ) =
1210 NVL (
1211 csi_intf_repl_rec.pricing_attribute53,
1212 l_miss_char
1213 )
1214 AND NVL (
1215 x_pricing_attribs_tbl (i).pricing_attribute54,
1216 l_miss_char
1217 ) =
1218 NVL (
1219 csi_intf_repl_rec.pricing_attribute54,
1220 l_miss_char
1221 )
1222 AND NVL (
1223 x_pricing_attribs_tbl (i).pricing_attribute55,
1224 l_miss_char
1225 ) =
1226 NVL (
1227 csi_intf_repl_rec.pricing_attribute55,
1228 l_miss_char
1229 )
1230 AND NVL (
1231 x_pricing_attribs_tbl (i).pricing_attribute56,
1232 l_miss_char
1233 ) =
1234 NVL (
1235 csi_intf_repl_rec.pricing_attribute56,
1236 l_miss_char
1237 )
1238 AND NVL (
1239 x_pricing_attribs_tbl (i).pricing_attribute57,
1240 l_miss_char
1241 ) =
1242 NVL (
1243 csi_intf_repl_rec.pricing_attribute57,
1244 l_miss_char
1245 )
1246 AND NVL (
1247 x_pricing_attribs_tbl (i).pricing_attribute58,
1248 l_miss_char
1249 ) =
1250 NVL (
1251 csi_intf_repl_rec.pricing_attribute58,
1252 l_miss_char
1253 )
1254 AND NVL (
1255 x_pricing_attribs_tbl (i).pricing_attribute59,
1256 l_miss_char
1257 ) =
1258 NVL (
1259 csi_intf_repl_rec.pricing_attribute59,
1260 l_miss_char
1261 )
1262 AND NVL (
1263 x_pricing_attribs_tbl (i).pricing_attribute60,
1264 l_miss_char
1265 ) =
1266 NVL (
1267 csi_intf_repl_rec.pricing_attribute60,
1268 l_miss_char
1269 )
1270 AND NVL (
1271 x_pricing_attribs_tbl (i).pricing_attribute61,
1272 l_miss_char
1273 ) =
1274 NVL (
1275 csi_intf_repl_rec.pricing_attribute61,
1276 l_miss_char
1277 )
1278 AND NVL (
1279 x_pricing_attribs_tbl (i).pricing_attribute62,
1280 l_miss_char
1281 ) =
1282 NVL (
1283 csi_intf_repl_rec.pricing_attribute62,
1284 l_miss_char
1285 )
1286 AND NVL (
1287 x_pricing_attribs_tbl (i).pricing_attribute63,
1288 l_miss_char
1289 ) =
1290 NVL (
1291 csi_intf_repl_rec.pricing_attribute63,
1292 l_miss_char
1293 )
1294 AND NVL (
1295 x_pricing_attribs_tbl (i).pricing_attribute64,
1296 l_miss_char
1297 ) =
1298 NVL (
1299 csi_intf_repl_rec.pricing_attribute64,
1300 l_miss_char
1301 )
1302 AND NVL (
1303 x_pricing_attribs_tbl (i).pricing_attribute65,
1304 l_miss_char
1305 ) =
1306 NVL (
1307 csi_intf_repl_rec.pricing_attribute65,
1308 l_miss_char
1309 )
1310 AND NVL (
1311 x_pricing_attribs_tbl (i).pricing_attribute66,
1312 l_miss_char
1313 ) =
1314 NVL (
1315 csi_intf_repl_rec.pricing_attribute66,
1316 l_miss_char
1317 )
1318 AND NVL (
1319 x_pricing_attribs_tbl (i).pricing_attribute67,
1320 l_miss_char
1321 ) =
1322 NVL (
1323 csi_intf_repl_rec.pricing_attribute67,
1324 l_miss_char
1325 )
1326 AND NVL (
1327 x_pricing_attribs_tbl (i).pricing_attribute68,
1328 l_miss_char
1329 ) =
1330 NVL (
1331 csi_intf_repl_rec.pricing_attribute68,
1332 l_miss_char
1333 )
1334 AND NVL (
1335 x_pricing_attribs_tbl (i).pricing_attribute69,
1336 l_miss_char
1337 ) =
1338 NVL (
1339 csi_intf_repl_rec.pricing_attribute69,
1340 l_miss_char
1341 )
1342 AND NVL (
1343 x_pricing_attribs_tbl (i).pricing_attribute70,
1344 l_miss_char
1345 ) =
1346 NVL (
1347 csi_intf_repl_rec.pricing_attribute70,
1348 l_miss_char
1349 )
1350 AND NVL (
1351 x_pricing_attribs_tbl (i).pricing_attribute71,
1352 l_miss_char
1353 ) =
1354 NVL (
1355 csi_intf_repl_rec.pricing_attribute71,
1356 l_miss_char
1357 )
1358 AND NVL (
1359 x_pricing_attribs_tbl (i).pricing_attribute72,
1360 l_miss_char
1361 ) =
1362 NVL (
1363 csi_intf_repl_rec.pricing_attribute72,
1364 l_miss_char
1365 )
1366 AND NVL (
1367 x_pricing_attribs_tbl (i).pricing_attribute73,
1368 l_miss_char
1369 ) =
1370 NVL (
1371 csi_intf_repl_rec.pricing_attribute73,
1372 l_miss_char
1373 )
1374 AND NVL (
1375 x_pricing_attribs_tbl (i).pricing_attribute74,
1376 l_miss_char
1377 ) =
1378 NVL (
1379 csi_intf_repl_rec.pricing_attribute74,
1380 l_miss_char
1381 )
1382 AND NVL (
1383 x_pricing_attribs_tbl (i).pricing_attribute75,
1384 l_miss_char
1385 ) =
1386 NVL (
1387 csi_intf_repl_rec.pricing_attribute75,
1388 l_miss_char
1389 )
1390 AND NVL (
1391 x_pricing_attribs_tbl (i).pricing_attribute76,
1392 l_miss_char
1393 ) =
1394 NVL (
1395 csi_intf_repl_rec.pricing_attribute76,
1396 l_miss_char
1397 )
1398 AND NVL (
1399 x_pricing_attribs_tbl (i).pricing_attribute77,
1400 l_miss_char
1401 ) =
1402 NVL (
1403 csi_intf_repl_rec.pricing_attribute77,
1404 l_miss_char
1405 )
1406 AND NVL (
1407 x_pricing_attribs_tbl (i).pricing_attribute78,
1408 l_miss_char
1409 ) =
1410 NVL (
1411 csi_intf_repl_rec.pricing_attribute78,
1412 l_miss_char
1413 )
1414 AND NVL (
1415 x_pricing_attribs_tbl (i).pricing_attribute79,
1416 l_miss_char
1417 ) =
1418 NVL (
1419 csi_intf_repl_rec.pricing_attribute79,
1420 l_miss_char
1421 )
1422 AND NVL (
1423 x_pricing_attribs_tbl (i).pricing_attribute80,
1424 l_miss_char
1425 ) =
1426 NVL (
1427 csi_intf_repl_rec.pricing_attribute80,
1428 l_miss_char
1429 )
1430 AND NVL (
1431 x_pricing_attribs_tbl (i).pricing_attribute81,
1432 l_miss_char
1433 ) =
1434 NVL (
1435 csi_intf_repl_rec.pricing_attribute81,
1436 l_miss_char
1437 )
1438 AND NVL (
1439 x_pricing_attribs_tbl (i).pricing_attribute82,
1440 l_miss_char
1441 ) =
1442 NVL (
1443 csi_intf_repl_rec.pricing_attribute82,
1444 l_miss_char
1445 )
1446 AND NVL (
1447 x_pricing_attribs_tbl (i).pricing_attribute83,
1448 l_miss_char
1449 ) =
1450 NVL (
1451 csi_intf_repl_rec.pricing_attribute83,
1452 l_miss_char
1453 )
1454 AND NVL (
1455 x_pricing_attribs_tbl (i).pricing_attribute84,
1456 l_miss_char
1457 ) =
1458 NVL (
1459 csi_intf_repl_rec.pricing_attribute84,
1460 l_miss_char
1461 )
1462 AND NVL (
1463 x_pricing_attribs_tbl (i).pricing_attribute85,
1464 l_miss_char
1465 ) =
1466 NVL (
1467 csi_intf_repl_rec.pricing_attribute85,
1468 l_miss_char
1469 )
1470 AND NVL (
1471 x_pricing_attribs_tbl (i).pricing_attribute86,
1472 l_miss_char
1473 ) =
1474 NVL (
1475 csi_intf_repl_rec.pricing_attribute86,
1476 l_miss_char
1477 )
1478 AND NVL (
1479 x_pricing_attribs_tbl (i).pricing_attribute87,
1480 l_miss_char
1481 ) =
1482 NVL (
1483 csi_intf_repl_rec.pricing_attribute87,
1484 l_miss_char
1485 )
1486 AND NVL (
1487 x_pricing_attribs_tbl (i).pricing_attribute88,
1488 l_miss_char
1489 ) =
1490 NVL (
1491 csi_intf_repl_rec.pricing_attribute88,
1492 l_miss_char
1493 )
1494 AND NVL (
1495 x_pricing_attribs_tbl (i).pricing_attribute89,
1496 l_miss_char
1497 ) =
1498 NVL (
1499 csi_intf_repl_rec.pricing_attribute89,
1500 l_miss_char
1501 )
1502 AND NVL (
1503 x_pricing_attribs_tbl (i).pricing_attribute90,
1504 l_miss_char
1505 ) =
1506 NVL (
1507 csi_intf_repl_rec.pricing_attribute90,
1508 l_miss_char
1509 )
1510 AND NVL (
1511 x_pricing_attribs_tbl (i).pricing_attribute91,
1512 l_miss_char
1513 ) =
1514 NVL (
1515 csi_intf_repl_rec.pricing_attribute91,
1516 l_miss_char
1517 )
1518 AND NVL (
1519 x_pricing_attribs_tbl (i).pricing_attribute92,
1520 l_miss_char
1521 ) =
1522 NVL (
1523 csi_intf_repl_rec.pricing_attribute92,
1524 l_miss_char
1525 )
1526 AND NVL (
1527 x_pricing_attribs_tbl (i).pricing_attribute93,
1528 l_miss_char
1529 ) =
1530 NVL (
1531 csi_intf_repl_rec.pricing_attribute93,
1532 l_miss_char
1533 )
1534 AND NVL (
1535 x_pricing_attribs_tbl (i).pricing_attribute94,
1536 l_miss_char
1537 ) =
1538 NVL (
1539 csi_intf_repl_rec.pricing_attribute94,
1540 l_miss_char
1541 )
1542 AND NVL (
1543 x_pricing_attribs_tbl (i).pricing_attribute95,
1544 l_miss_char
1545 ) =
1546 NVL (
1547 csi_intf_repl_rec.pricing_attribute95,
1548 l_miss_char
1549 )
1550 AND NVL (
1551 x_pricing_attribs_tbl (i).pricing_attribute96,
1552 l_miss_char
1553 ) =
1554 NVL (
1555 csi_intf_repl_rec.pricing_attribute96,
1556 l_miss_char
1557 )
1558 AND NVL (
1559 x_pricing_attribs_tbl (i).pricing_attribute97,
1560 l_miss_char
1561 ) =
1562 NVL (
1563 csi_intf_repl_rec.pricing_attribute97,
1564 l_miss_char
1565 )
1566 AND NVL (
1567 x_pricing_attribs_tbl (i).pricing_attribute98,
1568 l_miss_char
1569 ) =
1570 NVL (
1571 csi_intf_repl_rec.pricing_attribute98,
1572 l_miss_char
1573 )
1574 AND NVL (
1575 x_pricing_attribs_tbl (i).pricing_attribute99,
1576 l_miss_char
1577 ) =
1578 NVL (
1579 csi_intf_repl_rec.pricing_attribute99,
1580 l_miss_char
1581 )
1582 AND NVL (
1583 x_pricing_attribs_tbl (i).pricing_attribute100,
1584 l_miss_char
1585 ) =
1586 NVL (
1587 csi_intf_repl_rec.pricing_attribute100,
1588 l_miss_char
1589 )
1590 AND NVL (
1591 x_pricing_attribs_tbl (i).CONTEXT,
1592 l_miss_char
1593 ) =
1594 NVL (
1595 csi_intf_repl_rec.pricing_flex_context,
1596 l_miss_char
1597 )
1598 AND NVL (
1599 x_pricing_attribs_tbl (i).attribute1,
1600 l_miss_char
1601 ) =
1602 NVL (
1603 csi_intf_repl_rec.pricing_flex_attribute1,
1604 l_miss_char
1605 )
1606 AND NVL (
1607 x_pricing_attribs_tbl (i).attribute2,
1608 l_miss_char
1609 ) =
1610 NVL (
1611 csi_intf_repl_rec.pricing_flex_attribute2,
1612 l_miss_char
1613 )
1614 AND NVL (
1615 x_pricing_attribs_tbl (i).attribute3,
1616 l_miss_char
1617 ) =
1618 NVL (
1619 csi_intf_repl_rec.pricing_flex_attribute3,
1620 l_miss_char
1621 )
1622 AND NVL (
1623 x_pricing_attribs_tbl (i).attribute4,
1624 l_miss_char
1625 ) =
1626 NVL (
1627 csi_intf_repl_rec.pricing_flex_attribute4,
1628 l_miss_char
1629 )
1630 AND NVL (
1631 x_pricing_attribs_tbl (i).attribute5,
1632 l_miss_char
1633 ) =
1634 NVL (
1635 csi_intf_repl_rec.pricing_flex_attribute5,
1636 l_miss_char
1637 )
1638 AND NVL (
1639 x_pricing_attribs_tbl (i).attribute6,
1640 l_miss_char
1641 ) =
1642 NVL (
1643 csi_intf_repl_rec.pricing_flex_attribute6,
1644 l_miss_char
1645 )
1646 AND NVL (
1647 x_pricing_attribs_tbl (i).attribute7,
1648 l_miss_char
1649 ) =
1650 NVL (
1651 csi_intf_repl_rec.pricing_flex_attribute7,
1652 l_miss_char
1653 )
1654 AND NVL (
1655 x_pricing_attribs_tbl (i).attribute8,
1656 l_miss_char
1657 ) =
1658 NVL (
1659 csi_intf_repl_rec.pricing_flex_attribute8,
1660 l_miss_char
1661 )
1662 AND NVL (
1663 x_pricing_attribs_tbl (i).attribute9,
1664 l_miss_char
1665 ) =
1666 NVL (
1667 csi_intf_repl_rec.pricing_flex_attribute9,
1668 l_miss_char
1669 )
1670 AND NVL (
1671 x_pricing_attribs_tbl (i).attribute10,
1672 l_miss_char
1673 ) =
1674 NVL (
1675 csi_intf_repl_rec.pricing_flex_attribute10,
1676 l_miss_char
1677 )
1678 AND NVL (
1679 x_pricing_attribs_tbl (i).attribute11,
1680 l_miss_char
1681 ) =
1682 NVL (
1683 csi_intf_repl_rec.pricing_flex_attribute11,
1684 l_miss_char
1685 )
1686 AND NVL (
1687 x_pricing_attribs_tbl (i).attribute12,
1688 l_miss_char
1689 ) =
1690 NVL (
1691 csi_intf_repl_rec.pricing_flex_attribute12,
1692 l_miss_char
1693 )
1694 AND NVL (
1695 x_pricing_attribs_tbl (i).attribute13,
1696 l_miss_char
1697 ) =
1698 NVL (
1699 csi_intf_repl_rec.pricing_flex_attribute13,
1700 l_miss_char
1701 )
1702 AND NVL (
1703 x_pricing_attribs_tbl (i).attribute14,
1704 l_miss_char
1705 ) =
1706 NVL (
1707 csi_intf_repl_rec.pricing_flex_attribute14,
1708 l_miss_char
1709 )
1710 AND NVL (
1711 x_pricing_attribs_tbl (i).attribute15,
1712 l_miss_char
1713 ) =
1714 NVL (
1715 csi_intf_repl_rec.pricing_flex_attribute15,
1716 l_miss_char
1717 )
1718 )
1719 THEN
1720 l_price_index := x_price_tbl.COUNT
1721 + 1;
1722
1723 --dbms_output.put_line('pricing attributes three');
1724 x_price_tbl (l_price_index).pricing_attribute_id :=
1725 csi_intf_repl_rec.pricing_attribute_id;
1726 x_price_tbl (l_price_index).instance_id :=
1727 csi_intf_repl_rec.instance_id;
1728
1729 SELECT DECODE (
1730 csi_intf_repl_rec.pricing_attribute1,
1731 x_pricing_attribs_tbl (i).pricing_attribute1, l_miss_char,
1732 NULL, DECODE (
1733 x_pricing_attribs_tbl (i).pricing_attribute1,
1734 NULL, l_miss_char,
1735 NULL
1736 ),
1737 csi_intf_repl_rec.pricing_attribute1
1738 ),
1739 DECODE (
1740 csi_intf_repl_rec.pricing_attribute2,
1741 x_pricing_attribs_tbl (i).pricing_attribute2, l_miss_char,
1742 NULL, DECODE (
1743 x_pricing_attribs_tbl (i).pricing_attribute1,
1744 NULL, l_miss_char,
1745 NULL
1746 ),
1747 csi_intf_repl_rec.pricing_attribute2
1748 ),
1749 DECODE (
1750 csi_intf_repl_rec.pricing_attribute3,
1751 x_pricing_attribs_tbl (i).pricing_attribute3, l_miss_char,
1752 NULL, DECODE (
1753 x_pricing_attribs_tbl (i).pricing_attribute1,
1754 NULL, l_miss_char,
1755 NULL
1756 ),
1757 csi_intf_repl_rec.pricing_attribute3
1758 ),
1759 DECODE (
1760 csi_intf_repl_rec.pricing_attribute4,
1761 x_pricing_attribs_tbl (i).pricing_attribute4, l_miss_char,
1762 NULL, DECODE (
1763 x_pricing_attribs_tbl (i).pricing_attribute1,
1764 NULL, l_miss_char,
1765 NULL
1766 ),
1767 csi_intf_repl_rec.pricing_attribute4
1768 ),
1769 DECODE (
1770 csi_intf_repl_rec.pricing_attribute5,
1771 x_pricing_attribs_tbl (i).pricing_attribute5, l_miss_char,
1772 NULL, DECODE (
1773 x_pricing_attribs_tbl (i).pricing_attribute1,
1774 NULL, l_miss_char,
1775 NULL
1776 ),
1777 csi_intf_repl_rec.pricing_attribute5
1778 ),
1779 DECODE (
1780 csi_intf_repl_rec.pricing_attribute6,
1781 x_pricing_attribs_tbl (i).pricing_attribute6, l_miss_char,
1782 NULL, DECODE (
1783 x_pricing_attribs_tbl (i).pricing_attribute1,
1784 NULL, l_miss_char,
1785 NULL
1786 ),
1787 csi_intf_repl_rec.pricing_attribute6
1788 ),
1789 DECODE (
1790 csi_intf_repl_rec.pricing_attribute7,
1791 x_pricing_attribs_tbl (i).pricing_attribute7, l_miss_char,
1792 NULL, DECODE (
1793 x_pricing_attribs_tbl (i).pricing_attribute1,
1794 NULL, l_miss_char,
1795 NULL
1796 ),
1797 csi_intf_repl_rec.pricing_attribute7
1798 ),
1799 DECODE (
1800 csi_intf_repl_rec.pricing_attribute8,
1801 x_pricing_attribs_tbl (i).pricing_attribute8, l_miss_char,
1802 NULL, DECODE (
1803 x_pricing_attribs_tbl (i).pricing_attribute1,
1804 NULL, l_miss_char,
1805 NULL
1806 ),
1807 csi_intf_repl_rec.pricing_attribute8
1808 ),
1809 DECODE (
1810 csi_intf_repl_rec.pricing_attribute9,
1811 x_pricing_attribs_tbl (i).pricing_attribute9, l_miss_char,
1812 NULL, DECODE (
1813 x_pricing_attribs_tbl (i).pricing_attribute1,
1814 NULL, l_miss_char,
1815 NULL
1816 ),
1817 csi_intf_repl_rec.pricing_attribute9
1818 ),
1819 DECODE (
1820 csi_intf_repl_rec.pricing_attribute10,
1821 x_pricing_attribs_tbl (i).pricing_attribute10, l_miss_char,
1822 NULL, DECODE (
1823 x_pricing_attribs_tbl (i).pricing_attribute1,
1824 NULL, l_miss_char,
1825 NULL
1826 ),
1827 csi_intf_repl_rec.pricing_attribute10
1828 ),
1829 DECODE (
1830 csi_intf_repl_rec.pricing_attribute11,
1831 x_pricing_attribs_tbl (i).pricing_attribute11, l_miss_char,
1832 NULL, DECODE (
1833 x_pricing_attribs_tbl (i).pricing_attribute1,
1834 NULL, l_miss_char,
1835 NULL
1836 ),
1837 csi_intf_repl_rec.pricing_attribute11
1838 ),
1839 DECODE (
1840 csi_intf_repl_rec.pricing_attribute12,
1841 x_pricing_attribs_tbl (i).pricing_attribute12, l_miss_char,
1842 NULL, DECODE (
1843 x_pricing_attribs_tbl (i).pricing_attribute1,
1844 NULL, l_miss_char,
1845 NULL
1846 ),
1847 csi_intf_repl_rec.pricing_attribute12
1848 ),
1849 DECODE (
1850 csi_intf_repl_rec.pricing_attribute13,
1851 x_pricing_attribs_tbl (i).pricing_attribute13, l_miss_char,
1852 NULL, DECODE (
1853 x_pricing_attribs_tbl (i).pricing_attribute1,
1854 NULL, l_miss_char,
1855 NULL
1856 ),
1857 csi_intf_repl_rec.pricing_attribute13
1858 ),
1859 DECODE (
1860 csi_intf_repl_rec.pricing_attribute14,
1861 x_pricing_attribs_tbl (i).pricing_attribute14, l_miss_char,
1862 NULL, DECODE (
1863 x_pricing_attribs_tbl (i).pricing_attribute1,
1864 NULL, l_miss_char,
1865 NULL
1866 ),
1867 csi_intf_repl_rec.pricing_attribute14
1868 ),
1869 DECODE (
1870 csi_intf_repl_rec.pricing_attribute15,
1871 x_pricing_attribs_tbl (i).pricing_attribute15, l_miss_char,
1872 NULL, DECODE (
1873 x_pricing_attribs_tbl (i).pricing_attribute1,
1874 NULL, l_miss_char,
1875 NULL
1876 ),
1877 csi_intf_repl_rec.pricing_attribute15
1878 ),
1879 DECODE (
1880 csi_intf_repl_rec.pricing_attribute16,
1881 x_pricing_attribs_tbl (i).pricing_attribute16, l_miss_char,
1882 NULL, DECODE (
1883 x_pricing_attribs_tbl (i).pricing_attribute1,
1884 NULL, l_miss_char,
1885 NULL
1886 ),
1887 csi_intf_repl_rec.pricing_attribute16
1888 ),
1889 DECODE (
1890 csi_intf_repl_rec.pricing_attribute17,
1891 x_pricing_attribs_tbl (i).pricing_attribute17, l_miss_char,
1892 NULL, DECODE (
1893 x_pricing_attribs_tbl (i).pricing_attribute1,
1894 NULL, l_miss_char,
1895 NULL
1896 ),
1897 csi_intf_repl_rec.pricing_attribute17
1898 ),
1899 DECODE (
1900 csi_intf_repl_rec.pricing_attribute18,
1901 x_pricing_attribs_tbl (i).pricing_attribute18, l_miss_char,
1902 NULL, DECODE (
1903 x_pricing_attribs_tbl (i).pricing_attribute1,
1904 NULL, l_miss_char,
1905 NULL
1906 ),
1907 csi_intf_repl_rec.pricing_attribute18
1908 ),
1909 DECODE (
1910 csi_intf_repl_rec.pricing_attribute19,
1911 x_pricing_attribs_tbl (i).pricing_attribute19, l_miss_char,
1912 NULL, DECODE (
1913 x_pricing_attribs_tbl (i).pricing_attribute1,
1914 NULL, l_miss_char,
1915 NULL
1916 ),
1917 csi_intf_repl_rec.pricing_attribute19
1918 ),
1919 DECODE (
1920 csi_intf_repl_rec.pricing_attribute20,
1921 x_pricing_attribs_tbl (i).pricing_attribute20, l_miss_char,
1922 NULL, DECODE (
1923 x_pricing_attribs_tbl (i).pricing_attribute1,
1924 NULL, l_miss_char,
1925 NULL
1926 ),
1927 csi_intf_repl_rec.pricing_attribute20
1928 ),
1929 DECODE (
1930 csi_intf_repl_rec.pricing_attribute21,
1931 x_pricing_attribs_tbl (i).pricing_attribute21, l_miss_char,
1932 NULL, DECODE (
1933 x_pricing_attribs_tbl (i).pricing_attribute1,
1934 NULL, l_miss_char,
1935 NULL
1936 ),
1937 csi_intf_repl_rec.pricing_attribute21
1938 ),
1939 DECODE (
1940 csi_intf_repl_rec.pricing_attribute22,
1941 x_pricing_attribs_tbl (i).pricing_attribute22, l_miss_char,
1942 NULL, DECODE (
1943 x_pricing_attribs_tbl (i).pricing_attribute1,
1944 NULL, l_miss_char,
1945 NULL
1946 ),
1947 csi_intf_repl_rec.pricing_attribute22
1948 ),
1949 DECODE (
1950 csi_intf_repl_rec.pricing_attribute23,
1951 x_pricing_attribs_tbl (i).pricing_attribute23, l_miss_char,
1952 NULL, DECODE (
1953 x_pricing_attribs_tbl (i).pricing_attribute1,
1954 NULL, l_miss_char,
1955 NULL
1956 ),
1957 csi_intf_repl_rec.pricing_attribute23
1958 ),
1959 DECODE (
1960 csi_intf_repl_rec.pricing_attribute24,
1961 x_pricing_attribs_tbl (i).pricing_attribute24, l_miss_char,
1962 NULL, DECODE (
1963 x_pricing_attribs_tbl (i).pricing_attribute1,
1964 NULL, l_miss_char,
1965 NULL
1966 ),
1967 csi_intf_repl_rec.pricing_attribute24
1968 ),
1969 DECODE (
1970 csi_intf_repl_rec.pricing_attribute25,
1971 x_pricing_attribs_tbl (i).pricing_attribute25, l_miss_char,
1972 NULL, DECODE (
1973 x_pricing_attribs_tbl (i).pricing_attribute1,
1974 NULL, l_miss_char,
1975 NULL
1976 ),
1977 csi_intf_repl_rec.pricing_attribute25
1978 ),
1979 DECODE (
1980 csi_intf_repl_rec.pricing_attribute26,
1981 x_pricing_attribs_tbl (i).pricing_attribute26, l_miss_char,
1982 NULL, DECODE (
1983 x_pricing_attribs_tbl (i).pricing_attribute1,
1984 NULL, l_miss_char,
1985 NULL
1986 ),
1987 csi_intf_repl_rec.pricing_attribute26
1988 ),
1989 DECODE (
1990 csi_intf_repl_rec.pricing_attribute27,
1991 x_pricing_attribs_tbl (i).pricing_attribute27, l_miss_char,
1992 NULL, DECODE (
1993 x_pricing_attribs_tbl (i).pricing_attribute1,
1994 NULL, l_miss_char,
1995 NULL
1996 ),
1997 csi_intf_repl_rec.pricing_attribute27
1998 ),
1999 DECODE (
2000 csi_intf_repl_rec.pricing_attribute28,
2001 x_pricing_attribs_tbl (i).pricing_attribute28, l_miss_char,
2002 NULL, DECODE (
2003 x_pricing_attribs_tbl (i).pricing_attribute1,
2004 NULL, l_miss_char,
2005 NULL
2006 ),
2007 csi_intf_repl_rec.pricing_attribute28
2008 ),
2009 DECODE (
2010 csi_intf_repl_rec.pricing_attribute29,
2011 x_pricing_attribs_tbl (i).pricing_attribute29, l_miss_char,
2012 NULL, DECODE (
2013 x_pricing_attribs_tbl (i).pricing_attribute1,
2014 NULL, l_miss_char,
2015 NULL
2016 ),
2017 csi_intf_repl_rec.pricing_attribute29
2018 ),
2019 DECODE (
2020 csi_intf_repl_rec.pricing_attribute30,
2021 x_pricing_attribs_tbl (i).pricing_attribute30, l_miss_char,
2022 NULL, DECODE (
2023 x_pricing_attribs_tbl (i).pricing_attribute1,
2024 NULL, l_miss_char,
2025 NULL
2026 ),
2027 csi_intf_repl_rec.pricing_attribute30
2028 ),
2029 DECODE (
2030 csi_intf_repl_rec.pricing_attribute31,
2031 x_pricing_attribs_tbl (i).pricing_attribute31, l_miss_char,
2032 NULL, DECODE (
2033 x_pricing_attribs_tbl (i).pricing_attribute1,
2034 NULL, l_miss_char,
2035 NULL
2036 ),
2037 csi_intf_repl_rec.pricing_attribute31
2038 ),
2039 DECODE (
2040 csi_intf_repl_rec.pricing_attribute32,
2041 x_pricing_attribs_tbl (i).pricing_attribute32, l_miss_char,
2042 NULL, DECODE (
2043 x_pricing_attribs_tbl (i).pricing_attribute1,
2044 NULL, l_miss_char,
2045 NULL
2046 ),
2047 csi_intf_repl_rec.pricing_attribute32
2048 ),
2049 DECODE (
2050 csi_intf_repl_rec.pricing_attribute33,
2051 x_pricing_attribs_tbl (i).pricing_attribute33, l_miss_char,
2052 NULL, DECODE (
2053 x_pricing_attribs_tbl (i).pricing_attribute1,
2054 NULL, l_miss_char,
2055 NULL
2056 ),
2057 csi_intf_repl_rec.pricing_attribute33
2058 ),
2059 DECODE (
2060 csi_intf_repl_rec.pricing_attribute34,
2061 x_pricing_attribs_tbl (i).pricing_attribute34, l_miss_char,
2062 NULL, DECODE (
2063 x_pricing_attribs_tbl (i).pricing_attribute1,
2064 NULL, l_miss_char,
2065 NULL
2066 ),
2067 csi_intf_repl_rec.pricing_attribute34
2068 ),
2069 DECODE (
2070 csi_intf_repl_rec.pricing_attribute35,
2071 x_pricing_attribs_tbl (i).pricing_attribute35, l_miss_char,
2072 NULL, DECODE (
2073 x_pricing_attribs_tbl (i).pricing_attribute1,
2074 NULL, l_miss_char,
2075 NULL
2076 ),
2077 csi_intf_repl_rec.pricing_attribute35
2078 ),
2079 DECODE (
2080 csi_intf_repl_rec.pricing_attribute36,
2081 x_pricing_attribs_tbl (i).pricing_attribute36, l_miss_char,
2082 NULL, DECODE (
2083 x_pricing_attribs_tbl (i).pricing_attribute1,
2084 NULL, l_miss_char,
2085 NULL
2086 ),
2087 csi_intf_repl_rec.pricing_attribute36
2088 ),
2089 DECODE (
2090 csi_intf_repl_rec.pricing_attribute37,
2091 x_pricing_attribs_tbl (i).pricing_attribute37, l_miss_char,
2092 NULL, DECODE (
2093 x_pricing_attribs_tbl (i).pricing_attribute1,
2094 NULL, l_miss_char,
2095 NULL
2096 ),
2097 csi_intf_repl_rec.pricing_attribute37
2098 ),
2099 DECODE (
2100 csi_intf_repl_rec.pricing_attribute38,
2101 x_pricing_attribs_tbl (i).pricing_attribute38, l_miss_char,
2102 NULL, DECODE (
2103 x_pricing_attribs_tbl (i).pricing_attribute1,
2104 NULL, l_miss_char,
2105 NULL
2106 ),
2107 csi_intf_repl_rec.pricing_attribute38
2108 ),
2109 DECODE (
2110 csi_intf_repl_rec.pricing_attribute39,
2111 x_pricing_attribs_tbl (i).pricing_attribute39, l_miss_char,
2112 NULL, DECODE (
2113 x_pricing_attribs_tbl (i).pricing_attribute1,
2114 NULL, l_miss_char,
2115 NULL
2116 ),
2117 csi_intf_repl_rec.pricing_attribute39
2118 ),
2119 DECODE (
2120 csi_intf_repl_rec.pricing_attribute40,
2121 x_pricing_attribs_tbl (i).pricing_attribute40, l_miss_char,
2122 NULL, DECODE (
2123 x_pricing_attribs_tbl (i).pricing_attribute1,
2124 NULL, l_miss_char,
2125 NULL
2126 ),
2127 csi_intf_repl_rec.pricing_attribute40
2128 ),
2129 DECODE (
2130 csi_intf_repl_rec.pricing_attribute41,
2131 x_pricing_attribs_tbl (i).pricing_attribute41, l_miss_char,
2132 NULL, DECODE (
2133 x_pricing_attribs_tbl (i).pricing_attribute1,
2134 NULL, l_miss_char,
2135 NULL
2136 ),
2137 csi_intf_repl_rec.pricing_attribute41
2138 ),
2139 DECODE (
2140 csi_intf_repl_rec.pricing_attribute42,
2141 x_pricing_attribs_tbl (i).pricing_attribute42, l_miss_char,
2142 NULL, DECODE (
2143 x_pricing_attribs_tbl (i).pricing_attribute1,
2144 NULL, l_miss_char,
2145 NULL
2146 ),
2147 csi_intf_repl_rec.pricing_attribute42
2148 ),
2149 DECODE (
2150 csi_intf_repl_rec.pricing_attribute43,
2151 x_pricing_attribs_tbl (i).pricing_attribute43, l_miss_char,
2152 NULL, DECODE (
2153 x_pricing_attribs_tbl (i).pricing_attribute1,
2154 NULL, l_miss_char,
2155 NULL
2156 ),
2157 csi_intf_repl_rec.pricing_attribute43
2158 ),
2159 DECODE (
2160 csi_intf_repl_rec.pricing_attribute44,
2161 x_pricing_attribs_tbl (i).pricing_attribute44, l_miss_char,
2162 NULL, DECODE (
2163 x_pricing_attribs_tbl (i).pricing_attribute1,
2164 NULL, l_miss_char,
2165 NULL
2166 ),
2167 csi_intf_repl_rec.pricing_attribute44
2168 ),
2169 DECODE (
2170 csi_intf_repl_rec.pricing_attribute45,
2171 x_pricing_attribs_tbl (i).pricing_attribute45, l_miss_char,
2172 NULL, DECODE (
2173 x_pricing_attribs_tbl (i).pricing_attribute1,
2174 NULL, l_miss_char,
2175 NULL
2176 ),
2177 csi_intf_repl_rec.pricing_attribute45
2178 ),
2179 DECODE (
2180 csi_intf_repl_rec.pricing_attribute46,
2181 x_pricing_attribs_tbl (i).pricing_attribute46, l_miss_char,
2182 NULL, DECODE (
2183 x_pricing_attribs_tbl (i).pricing_attribute1,
2184 NULL, l_miss_char,
2185 NULL
2186 ),
2187 csi_intf_repl_rec.pricing_attribute46
2188 ),
2189 DECODE (
2190 csi_intf_repl_rec.pricing_attribute47,
2191 x_pricing_attribs_tbl (i).pricing_attribute47, l_miss_char,
2192 NULL, DECODE (
2193 x_pricing_attribs_tbl (i).pricing_attribute1,
2194 NULL, l_miss_char,
2195 NULL
2196 ),
2197 csi_intf_repl_rec.pricing_attribute47
2198 ),
2199 DECODE (
2200 csi_intf_repl_rec.pricing_attribute48,
2201 x_pricing_attribs_tbl (i).pricing_attribute48, l_miss_char,
2202 NULL, DECODE (
2203 x_pricing_attribs_tbl (i).pricing_attribute1,
2204 NULL, l_miss_char,
2205 NULL
2206 ),
2207 csi_intf_repl_rec.pricing_attribute48
2208 ),
2209 DECODE (
2210 csi_intf_repl_rec.pricing_attribute49,
2211 x_pricing_attribs_tbl (i).pricing_attribute49, l_miss_char,
2212 NULL, DECODE (
2213 x_pricing_attribs_tbl (i).pricing_attribute1,
2214 NULL, l_miss_char,
2215 NULL
2216 ),
2217 csi_intf_repl_rec.pricing_attribute49
2218 ),
2219 DECODE (
2220 csi_intf_repl_rec.pricing_attribute50,
2221 x_pricing_attribs_tbl (i).pricing_attribute50, l_miss_char,
2222 NULL, DECODE (
2223 x_pricing_attribs_tbl (i).pricing_attribute1,
2224 NULL, l_miss_char,
2225 NULL
2226 ),
2227 csi_intf_repl_rec.pricing_attribute50
2228 ),
2229 DECODE (
2230 csi_intf_repl_rec.pricing_attribute51,
2231 x_pricing_attribs_tbl (i).pricing_attribute51, l_miss_char,
2232 NULL, DECODE (
2233 x_pricing_attribs_tbl (i).pricing_attribute1,
2234 NULL, l_miss_char,
2235 NULL
2236 ),
2237 csi_intf_repl_rec.pricing_attribute51
2238 ),
2239 DECODE (
2240 csi_intf_repl_rec.pricing_attribute52,
2241 x_pricing_attribs_tbl (i).pricing_attribute52, l_miss_char,
2242 NULL, DECODE (
2243 x_pricing_attribs_tbl (i).pricing_attribute1,
2244 NULL, l_miss_char,
2245 NULL
2246 ),
2247 csi_intf_repl_rec.pricing_attribute52
2248 ),
2249 DECODE (
2250 csi_intf_repl_rec.pricing_attribute53,
2251 x_pricing_attribs_tbl (i).pricing_attribute53, l_miss_char,
2252 NULL, DECODE (
2253 x_pricing_attribs_tbl (i).pricing_attribute1,
2254 NULL, l_miss_char,
2255 NULL
2256 ),
2257 csi_intf_repl_rec.pricing_attribute53
2258 ),
2259 DECODE (
2260 csi_intf_repl_rec.pricing_attribute54,
2261 x_pricing_attribs_tbl (i).pricing_attribute54, l_miss_char,
2262 NULL, DECODE (
2263 x_pricing_attribs_tbl (i).pricing_attribute1,
2264 NULL, l_miss_char,
2265 NULL
2266 ),
2267 csi_intf_repl_rec.pricing_attribute54
2268 ),
2269 DECODE (
2270 csi_intf_repl_rec.pricing_attribute55,
2271 x_pricing_attribs_tbl (i).pricing_attribute55, l_miss_char,
2272 NULL, DECODE (
2273 x_pricing_attribs_tbl (i).pricing_attribute1,
2274 NULL, l_miss_char,
2275 NULL
2276 ),
2277 csi_intf_repl_rec.pricing_attribute55
2278 ),
2279 DECODE (
2280 csi_intf_repl_rec.pricing_attribute56,
2281 x_pricing_attribs_tbl (i).pricing_attribute56, l_miss_char,
2282 NULL, DECODE (
2283 x_pricing_attribs_tbl (i).pricing_attribute1,
2284 NULL, l_miss_char,
2285 NULL
2286 ),
2287 csi_intf_repl_rec.pricing_attribute55
2288 ),
2289 DECODE (
2290 csi_intf_repl_rec.pricing_attribute57,
2291 x_pricing_attribs_tbl (i).pricing_attribute57, l_miss_char,
2292 NULL, DECODE (
2293 x_pricing_attribs_tbl (i).pricing_attribute1,
2294 NULL, l_miss_char,
2295 NULL
2296 ),
2297 csi_intf_repl_rec.pricing_attribute55
2298 ),
2299 DECODE (
2300 csi_intf_repl_rec.pricing_attribute58,
2301 x_pricing_attribs_tbl (i).pricing_attribute58, l_miss_char,
2302 NULL, DECODE (
2303 x_pricing_attribs_tbl (i).pricing_attribute1,
2304 NULL, l_miss_char,
2305 NULL
2306 ),
2307 csi_intf_repl_rec.pricing_attribute58
2308 ),
2309 DECODE (
2310 csi_intf_repl_rec.pricing_attribute59,
2311 x_pricing_attribs_tbl (i).pricing_attribute59, l_miss_char,
2312 NULL, DECODE (
2313 x_pricing_attribs_tbl (i).pricing_attribute1,
2314 NULL, l_miss_char,
2315 NULL
2316 ),
2317 csi_intf_repl_rec.pricing_attribute59
2318 ),
2319 DECODE (
2320 csi_intf_repl_rec.pricing_attribute60,
2321 x_pricing_attribs_tbl (i).pricing_attribute60, l_miss_char,
2322 NULL, DECODE (
2323 x_pricing_attribs_tbl (i).pricing_attribute1,
2324 NULL, l_miss_char,
2325 NULL
2326 ),
2327 csi_intf_repl_rec.pricing_attribute60
2328 ),
2329 DECODE (
2330 csi_intf_repl_rec.pricing_attribute61,
2331 x_pricing_attribs_tbl (i).pricing_attribute61, l_miss_char,
2332 NULL, DECODE (
2333 x_pricing_attribs_tbl (i).pricing_attribute1,
2334 NULL, l_miss_char,
2335 NULL
2336 ),
2337 csi_intf_repl_rec.pricing_attribute61
2338 ),
2339 DECODE (
2340 csi_intf_repl_rec.pricing_attribute62,
2341 x_pricing_attribs_tbl (i).pricing_attribute62, l_miss_char,
2342 NULL, DECODE (
2343 x_pricing_attribs_tbl (i).pricing_attribute1,
2344 NULL, l_miss_char,
2345 NULL
2346 ),
2347 csi_intf_repl_rec.pricing_attribute62
2348 ),
2349 DECODE (
2350 csi_intf_repl_rec.pricing_attribute63,
2351 x_pricing_attribs_tbl (i).pricing_attribute63, l_miss_char,
2352 NULL, DECODE (
2353 x_pricing_attribs_tbl (i).pricing_attribute1,
2354 NULL, l_miss_char,
2355 NULL
2356 ),
2357 csi_intf_repl_rec.pricing_attribute63
2358 ),
2359 DECODE (
2360 csi_intf_repl_rec.pricing_attribute64,
2361 x_pricing_attribs_tbl (i).pricing_attribute64, l_miss_char,
2362 NULL, DECODE (
2363 x_pricing_attribs_tbl (i).pricing_attribute1,
2364 NULL, l_miss_char,
2365 NULL
2366 ),
2367 csi_intf_repl_rec.pricing_attribute64
2368 ),
2369 DECODE (
2370 csi_intf_repl_rec.pricing_attribute65,
2371 x_pricing_attribs_tbl (i).pricing_attribute65, l_miss_char,
2372 NULL, DECODE (
2373 x_pricing_attribs_tbl (i).pricing_attribute1,
2374 NULL, l_miss_char,
2375 NULL
2376 ),
2377 csi_intf_repl_rec.pricing_attribute65
2378 ),
2379 DECODE (
2380 csi_intf_repl_rec.pricing_attribute66,
2381 x_pricing_attribs_tbl (i).pricing_attribute66, l_miss_char,
2382 NULL, DECODE (
2383 x_pricing_attribs_tbl (i).pricing_attribute1,
2384 NULL, l_miss_char,
2385 NULL
2386 ),
2387 csi_intf_repl_rec.pricing_attribute66
2388 ),
2389 DECODE (
2390 csi_intf_repl_rec.pricing_attribute67,
2391 x_pricing_attribs_tbl (i).pricing_attribute67, l_miss_char,
2392 NULL, DECODE (
2393 x_pricing_attribs_tbl (i).pricing_attribute1,
2394 NULL, l_miss_char,
2395 NULL
2396 ),
2397 csi_intf_repl_rec.pricing_attribute67
2398 ),
2399 DECODE (
2400 csi_intf_repl_rec.pricing_attribute68,
2401 x_pricing_attribs_tbl (i).pricing_attribute68, l_miss_char,
2402 NULL, DECODE (
2403 x_pricing_attribs_tbl (i).pricing_attribute1,
2404 NULL, l_miss_char,
2405 NULL
2406 ),
2407 csi_intf_repl_rec.pricing_attribute68
2408 ),
2409 DECODE (
2410 csi_intf_repl_rec.pricing_attribute69,
2411 x_pricing_attribs_tbl (i).pricing_attribute69, l_miss_char,
2412 NULL, DECODE (
2413 x_pricing_attribs_tbl (i).pricing_attribute1,
2414 NULL, l_miss_char,
2415 NULL
2416 ),
2417 csi_intf_repl_rec.pricing_attribute69
2418 ),
2419 DECODE (
2420 csi_intf_repl_rec.pricing_attribute70,
2421 x_pricing_attribs_tbl (i).pricing_attribute70, l_miss_char,
2422 NULL, DECODE (
2423 x_pricing_attribs_tbl (i).pricing_attribute1,
2424 NULL, l_miss_char,
2425 NULL
2426 ),
2427 csi_intf_repl_rec.pricing_attribute70
2428 ),
2429 DECODE (
2430 csi_intf_repl_rec.pricing_attribute71,
2431 x_pricing_attribs_tbl (i).pricing_attribute71, l_miss_char,
2432 NULL, DECODE (
2433 x_pricing_attribs_tbl (i).pricing_attribute1,
2434 NULL, l_miss_char,
2435 NULL
2436 ),
2437 csi_intf_repl_rec.pricing_attribute71
2438 ),
2439 DECODE (
2440 csi_intf_repl_rec.pricing_attribute72,
2441 x_pricing_attribs_tbl (i).pricing_attribute72, l_miss_char,
2442 NULL, DECODE (
2443 x_pricing_attribs_tbl (i).pricing_attribute1,
2444 NULL, l_miss_char,
2445 NULL
2446 ),
2447 csi_intf_repl_rec.pricing_attribute72
2448 ),
2449 DECODE (
2450 csi_intf_repl_rec.pricing_attribute73,
2451 x_pricing_attribs_tbl (i).pricing_attribute73, l_miss_char,
2452 NULL, DECODE (
2453 x_pricing_attribs_tbl (i).pricing_attribute1,
2454 NULL, l_miss_char,
2455 NULL
2456 ),
2457 csi_intf_repl_rec.pricing_attribute73
2458 ),
2459 DECODE (
2460 csi_intf_repl_rec.pricing_attribute74,
2461 x_pricing_attribs_tbl (i).pricing_attribute74, l_miss_char,
2462 NULL, DECODE (
2463 x_pricing_attribs_tbl (i).pricing_attribute1,
2464 NULL, l_miss_char,
2465 NULL
2466 ),
2467 csi_intf_repl_rec.pricing_attribute74
2468 ),
2469 DECODE (
2470 csi_intf_repl_rec.pricing_attribute75,
2471 x_pricing_attribs_tbl (i).pricing_attribute75, l_miss_char,
2472 NULL, DECODE (
2473 x_pricing_attribs_tbl (i).pricing_attribute1,
2474 NULL, l_miss_char,
2475 NULL
2476 ),
2477 csi_intf_repl_rec.pricing_attribute75
2478 ),
2479 DECODE (
2480 csi_intf_repl_rec.pricing_attribute76,
2481 x_pricing_attribs_tbl (i).pricing_attribute76, l_miss_char,
2482 NULL, DECODE (
2483 x_pricing_attribs_tbl (i).pricing_attribute1,
2484 NULL, l_miss_char,
2485 NULL
2486 ),
2487 csi_intf_repl_rec.pricing_attribute76
2488 ),
2489 DECODE (
2490 csi_intf_repl_rec.pricing_attribute77,
2491 x_pricing_attribs_tbl (i).pricing_attribute77, l_miss_char,
2492 NULL, DECODE (
2493 x_pricing_attribs_tbl (i).pricing_attribute1,
2494 NULL, l_miss_char,
2495 NULL
2496 ),
2497 csi_intf_repl_rec.pricing_attribute77
2498 ),
2499 DECODE (
2500 csi_intf_repl_rec.pricing_attribute78,
2501 x_pricing_attribs_tbl (i).pricing_attribute78, l_miss_char,
2502 NULL, DECODE (
2503 x_pricing_attribs_tbl (i).pricing_attribute1,
2504 NULL, l_miss_char,
2505 NULL
2506 ),
2507 csi_intf_repl_rec.pricing_attribute78
2508 ),
2509 DECODE (
2510 csi_intf_repl_rec.pricing_attribute79,
2511 x_pricing_attribs_tbl (i).pricing_attribute79, l_miss_char,
2512 NULL, DECODE (
2513 x_pricing_attribs_tbl (i).pricing_attribute1,
2514 NULL, l_miss_char,
2515 NULL
2516 ),
2517 csi_intf_repl_rec.pricing_attribute79
2518 ),
2519 DECODE (
2520 csi_intf_repl_rec.pricing_attribute80,
2521 x_pricing_attribs_tbl (i).pricing_attribute80, l_miss_char,
2522 NULL, DECODE (
2523 x_pricing_attribs_tbl (i).pricing_attribute1,
2524 NULL, l_miss_char,
2525 NULL
2526 ),
2527 csi_intf_repl_rec.pricing_attribute80
2528 ),
2529 DECODE (
2530 csi_intf_repl_rec.pricing_attribute81,
2531 x_pricing_attribs_tbl (i).pricing_attribute81, l_miss_char,
2532 NULL, DECODE (
2533 x_pricing_attribs_tbl (i).pricing_attribute1,
2534 NULL, l_miss_char,
2535 NULL
2536 ),
2537 csi_intf_repl_rec.pricing_attribute81
2538 ),
2539 DECODE (
2540 csi_intf_repl_rec.pricing_attribute82,
2541 x_pricing_attribs_tbl (i).pricing_attribute82, l_miss_char,
2542 NULL, DECODE (
2543 x_pricing_attribs_tbl (i).pricing_attribute1,
2544 NULL, l_miss_char,
2545 NULL
2546 ),
2547 csi_intf_repl_rec.pricing_attribute82
2548 ),
2549 DECODE (
2550 csi_intf_repl_rec.pricing_attribute83,
2551 x_pricing_attribs_tbl (i).pricing_attribute83, l_miss_char,
2552 NULL, DECODE (
2553 x_pricing_attribs_tbl (i).pricing_attribute1,
2554 NULL, l_miss_char,
2555 NULL
2556 ),
2557 csi_intf_repl_rec.pricing_attribute83
2558 ),
2559 DECODE (
2560 csi_intf_repl_rec.pricing_attribute84,
2561 x_pricing_attribs_tbl (i).pricing_attribute84, l_miss_char,
2562 NULL, DECODE (
2563 x_pricing_attribs_tbl (i).pricing_attribute1,
2564 NULL, l_miss_char,
2565 NULL
2566 ),
2567 csi_intf_repl_rec.pricing_attribute84
2568 ),
2569 DECODE (
2570 csi_intf_repl_rec.pricing_attribute85,
2571 x_pricing_attribs_tbl (i).pricing_attribute85, l_miss_char,
2572 NULL, DECODE (
2573 x_pricing_attribs_tbl (i).pricing_attribute1,
2574 NULL, l_miss_char,
2575 NULL
2576 ),
2577 csi_intf_repl_rec.pricing_attribute85
2578 ),
2579 DECODE (
2580 csi_intf_repl_rec.pricing_attribute86,
2581 x_pricing_attribs_tbl (i).pricing_attribute86, l_miss_char,
2582 NULL, DECODE (
2583 x_pricing_attribs_tbl (i).pricing_attribute1,
2584 NULL, l_miss_char,
2585 NULL
2586 ),
2587 csi_intf_repl_rec.pricing_attribute86
2588 ),
2589 DECODE (
2590 csi_intf_repl_rec.pricing_attribute87,
2591 x_pricing_attribs_tbl (i).pricing_attribute87, l_miss_char,
2592 NULL, DECODE (
2593 x_pricing_attribs_tbl (i).pricing_attribute1,
2594 NULL, l_miss_char,
2595 NULL
2596 ),
2597 csi_intf_repl_rec.pricing_attribute87
2598 ),
2599 DECODE (
2600 csi_intf_repl_rec.pricing_attribute88,
2601 x_pricing_attribs_tbl (i).pricing_attribute88, l_miss_char,
2602 NULL, DECODE (
2603 x_pricing_attribs_tbl (i).pricing_attribute1,
2604 NULL, l_miss_char,
2605 NULL
2606 ),
2607 csi_intf_repl_rec.pricing_attribute88
2608 ),
2609 DECODE (
2610 csi_intf_repl_rec.pricing_attribute89,
2611 x_pricing_attribs_tbl (i).pricing_attribute89, l_miss_char,
2612 NULL, DECODE (
2613 x_pricing_attribs_tbl (i).pricing_attribute1,
2614 NULL, l_miss_char,
2615 NULL
2616 ),
2617 csi_intf_repl_rec.pricing_attribute89
2618 ),
2619 DECODE (
2620 csi_intf_repl_rec.pricing_attribute90,
2621 x_pricing_attribs_tbl (i).pricing_attribute90, l_miss_char,
2622 NULL, DECODE (
2623 x_pricing_attribs_tbl (i).pricing_attribute1,
2624 NULL, l_miss_char,
2625 NULL
2626 ),
2627 csi_intf_repl_rec.pricing_attribute90
2628 ),
2629 DECODE (
2630 csi_intf_repl_rec.pricing_attribute91,
2631 x_pricing_attribs_tbl (i).pricing_attribute91, l_miss_char,
2632 NULL, DECODE (
2633 x_pricing_attribs_tbl (i).pricing_attribute1,
2634 NULL, l_miss_char,
2635 NULL
2636 ),
2637 csi_intf_repl_rec.pricing_attribute91
2638 ),
2639 DECODE (
2640 csi_intf_repl_rec.pricing_attribute92,
2641 x_pricing_attribs_tbl (i).pricing_attribute92, l_miss_char,
2642 NULL, DECODE (
2643 x_pricing_attribs_tbl (i).pricing_attribute1,
2644 NULL, l_miss_char,
2645 NULL
2646 ),
2647 csi_intf_repl_rec.pricing_attribute92
2648 ),
2649 DECODE (
2650 csi_intf_repl_rec.pricing_attribute93,
2651 x_pricing_attribs_tbl (i).pricing_attribute93, l_miss_char,
2652 NULL, DECODE (
2653 x_pricing_attribs_tbl (i).pricing_attribute1,
2654 NULL, l_miss_char,
2655 NULL
2656 ),
2657 csi_intf_repl_rec.pricing_attribute93
2658 ),
2659 DECODE (
2660 csi_intf_repl_rec.pricing_attribute94,
2661 x_pricing_attribs_tbl (i).pricing_attribute94, l_miss_char,
2662 NULL, DECODE (
2663 x_pricing_attribs_tbl (i).pricing_attribute1,
2664 NULL, l_miss_char,
2665 NULL
2666 ),
2667 csi_intf_repl_rec.pricing_attribute94
2668 ),
2669 DECODE (
2670 csi_intf_repl_rec.pricing_attribute95,
2671 x_pricing_attribs_tbl (i).pricing_attribute95, l_miss_char,
2672 NULL, DECODE (
2673 x_pricing_attribs_tbl (i).pricing_attribute1,
2674 NULL, l_miss_char,
2675 NULL
2676 ),
2677 csi_intf_repl_rec.pricing_attribute95
2678 ),
2679 DECODE (
2680 csi_intf_repl_rec.pricing_attribute96,
2681 x_pricing_attribs_tbl (i).pricing_attribute96, l_miss_char,
2682 NULL, DECODE (
2683 x_pricing_attribs_tbl (i).pricing_attribute1,
2684 NULL, l_miss_char,
2685 NULL
2686 ),
2687 csi_intf_repl_rec.pricing_attribute96
2688 ),
2689 DECODE (
2690 csi_intf_repl_rec.pricing_attribute97,
2691 x_pricing_attribs_tbl (i).pricing_attribute97, l_miss_char,
2692 NULL, DECODE (
2693 x_pricing_attribs_tbl (i).pricing_attribute1,
2694 NULL, l_miss_char,
2695 NULL
2696 ),
2697 csi_intf_repl_rec.pricing_attribute97
2698 ),
2699 DECODE (
2700 csi_intf_repl_rec.pricing_attribute98,
2701 x_pricing_attribs_tbl (i).pricing_attribute98, l_miss_char,
2702 NULL, DECODE (
2703 x_pricing_attribs_tbl (i).pricing_attribute1,
2704 NULL, l_miss_char,
2705 NULL
2706 ),
2707 csi_intf_repl_rec.pricing_attribute98
2708 ),
2709 DECODE (
2710 csi_intf_repl_rec.pricing_attribute99,
2711 x_pricing_attribs_tbl (i).pricing_attribute99, l_miss_char,
2712 NULL, DECODE (
2713 x_pricing_attribs_tbl (i).pricing_attribute1,
2714 NULL, l_miss_char,
2715 NULL
2716 ),
2717 csi_intf_repl_rec.pricing_attribute99
2718 ),
2719 DECODE (
2720 csi_intf_repl_rec.pricing_attribute100,
2721 x_pricing_attribs_tbl (i).pricing_attribute100, l_miss_char,
2722 NULL, DECODE (
2723 x_pricing_attribs_tbl (i).pricing_attribute1,
2724 NULL, l_miss_char,
2725 NULL
2726 ),
2727 csi_intf_repl_rec.pricing_attribute100
2728 ),
2729 DECODE (
2730 csi_intf_repl_rec.pricing_flex_context,
2731 x_pricing_attribs_tbl (i).CONTEXT, l_miss_char,
2732 NULL, DECODE (
2733 x_pricing_attribs_tbl (i).CONTEXT,
2734 NULL, l_miss_char,
2735 NULL
2736 ),
2737 csi_intf_repl_rec.pricing_flex_context
2738 ),
2739 DECODE (
2740 csi_intf_repl_rec.pricing_flex_attribute1,
2741 x_pricing_attribs_tbl (i).attribute1, l_miss_char,
2742 NULL, DECODE (
2743 x_pricing_attribs_tbl (i).attribute1,
2744 NULL, l_miss_char,
2745 NULL
2746 ),
2747 csi_intf_repl_rec.pricing_flex_attribute1
2748 ),
2749 DECODE (
2750 csi_intf_repl_rec.pricing_flex_attribute2,
2751 x_pricing_attribs_tbl (i).attribute2, l_miss_char,
2752 NULL, DECODE (
2753 x_pricing_attribs_tbl (i).attribute2,
2754 NULL, l_miss_char,
2755 NULL
2756 ),
2757 csi_intf_repl_rec.pricing_flex_attribute2
2758 ),
2759 DECODE (
2760 csi_intf_repl_rec.pricing_flex_attribute3,
2761 x_pricing_attribs_tbl (i).attribute3, l_miss_char,
2762 NULL, DECODE (
2763 x_pricing_attribs_tbl (i).attribute3,
2764 NULL, l_miss_char,
2765 NULL
2766 ),
2767 csi_intf_repl_rec.pricing_flex_attribute3
2768 ),
2769 DECODE (
2770 csi_intf_repl_rec.pricing_flex_attribute4,
2771 x_pricing_attribs_tbl (i).attribute4, l_miss_char,
2772 NULL, DECODE (
2773 x_pricing_attribs_tbl (i).attribute4,
2774 NULL, l_miss_char,
2775 NULL
2776 ),
2777 csi_intf_repl_rec.pricing_flex_attribute4
2778 ),
2779 DECODE (
2780 csi_intf_repl_rec.pricing_flex_attribute5,
2781 x_pricing_attribs_tbl (i).attribute5, l_miss_char,
2782 NULL, DECODE (
2783 x_pricing_attribs_tbl (i).attribute5,
2784 NULL, l_miss_char,
2785 NULL
2786 ),
2787 csi_intf_repl_rec.pricing_flex_attribute5
2788 ),
2789 DECODE (
2790 csi_intf_repl_rec.pricing_flex_attribute6,
2791 x_pricing_attribs_tbl (i).attribute6, l_miss_char,
2792 NULL, DECODE (
2793 x_pricing_attribs_tbl (i).attribute6,
2794 NULL, l_miss_char,
2795 NULL
2796 ),
2797 csi_intf_repl_rec.pricing_flex_attribute6
2798 ),
2799 DECODE (
2800 csi_intf_repl_rec.pricing_flex_attribute7,
2801 x_pricing_attribs_tbl (i).attribute7, l_miss_char,
2802 NULL, DECODE (
2803 x_pricing_attribs_tbl (i).attribute7,
2804 NULL, l_miss_char,
2805 NULL
2806 ),
2807 csi_intf_repl_rec.pricing_flex_attribute7
2808 ),
2809 DECODE (
2810 csi_intf_repl_rec.pricing_flex_attribute8,
2811 x_pricing_attribs_tbl (i).attribute8, l_miss_char,
2812 NULL, DECODE (
2813 x_pricing_attribs_tbl (i).attribute8,
2814 NULL, l_miss_char,
2815 NULL
2816 ),
2817 csi_intf_repl_rec.pricing_flex_attribute8
2818 ),
2819 DECODE (
2820 csi_intf_repl_rec.pricing_flex_attribute9,
2821 x_pricing_attribs_tbl (i).attribute9, l_miss_char,
2822 NULL, DECODE (
2823 x_pricing_attribs_tbl (i).attribute9,
2824 NULL, l_miss_char,
2825 NULL
2826 ),
2827 csi_intf_repl_rec.pricing_flex_attribute9
2828 ),
2829 DECODE (
2830 csi_intf_repl_rec.pricing_flex_attribute10,
2831 x_pricing_attribs_tbl (i).attribute10, l_miss_char,
2832 NULL, DECODE (
2833 x_pricing_attribs_tbl (i).attribute10,
2834 NULL, l_miss_char,
2835 NULL
2836 ),
2837 csi_intf_repl_rec.pricing_flex_attribute10
2838 ),
2839 DECODE (
2840 csi_intf_repl_rec.pricing_flex_attribute11,
2841 x_pricing_attribs_tbl (i).attribute11, l_miss_char,
2842 NULL, DECODE (
2843 x_pricing_attribs_tbl (i).attribute11,
2844 NULL, l_miss_char,
2845 NULL
2846 ),
2847 csi_intf_repl_rec.pricing_flex_attribute11
2848 ),
2849 DECODE (
2850 csi_intf_repl_rec.pricing_flex_attribute12,
2851 x_pricing_attribs_tbl (i).attribute12, l_miss_char,
2852 NULL, DECODE (
2853 x_pricing_attribs_tbl (i).attribute12,
2854 NULL, l_miss_char,
2855 NULL
2856 ),
2857 csi_intf_repl_rec.pricing_flex_attribute12
2858 ),
2859 DECODE (
2860 csi_intf_repl_rec.pricing_flex_attribute13,
2861 x_pricing_attribs_tbl (i).attribute13, l_miss_char,
2862 NULL, DECODE (
2863 x_pricing_attribs_tbl (i).attribute13,
2864 NULL, l_miss_char,
2865 NULL
2866 ),
2867 csi_intf_repl_rec.pricing_flex_attribute13
2868 ),
2869 DECODE (
2870 csi_intf_repl_rec.pricing_flex_attribute14,
2871 x_pricing_attribs_tbl (i).attribute14, l_miss_char,
2872 NULL, DECODE (
2873 x_pricing_attribs_tbl (i).attribute14,
2874 NULL, l_miss_char,
2875 NULL
2876 ),
2877 csi_intf_repl_rec.pricing_flex_attribute14
2878 ),
2879 DECODE (
2880 csi_intf_repl_rec.pricing_flex_attribute15,
2881 x_pricing_attribs_tbl (i).attribute15, l_miss_char,
2882 NULL, DECODE (
2883 x_pricing_attribs_tbl (i).attribute15,
2884 NULL, l_miss_char,
2885 NULL
2886 ),
2887 csi_intf_repl_rec.pricing_flex_attribute15
2888 ),
2889 x_pricing_attribs_tbl (i).object_version_number
2890 INTO x_price_tbl (l_price_index).pricing_attribute1,
2891 x_price_tbl (l_price_index).pricing_attribute2,
2892 x_price_tbl (l_price_index).pricing_attribute3,
2893 x_price_tbl (l_price_index).pricing_attribute4,
2894 x_price_tbl (l_price_index).pricing_attribute5,
2895 x_price_tbl (l_price_index).pricing_attribute6,
2896 x_price_tbl (l_price_index).pricing_attribute7,
2897 x_price_tbl (l_price_index).pricing_attribute8,
2898 x_price_tbl (l_price_index).pricing_attribute9,
2899 x_price_tbl (l_price_index).pricing_attribute10,
2900 x_price_tbl (l_price_index).pricing_attribute11,
2901 x_price_tbl (l_price_index).pricing_attribute12,
2902 x_price_tbl (l_price_index).pricing_attribute13,
2903 x_price_tbl (l_price_index).pricing_attribute14,
2904 x_price_tbl (l_price_index).pricing_attribute15,
2905 x_price_tbl (l_price_index).pricing_attribute16,
2906 x_price_tbl (l_price_index).pricing_attribute17,
2907 x_price_tbl (l_price_index).pricing_attribute18,
2908 x_price_tbl (l_price_index).pricing_attribute19,
2909 x_price_tbl (l_price_index).pricing_attribute20,
2910 x_price_tbl (l_price_index).pricing_attribute21,
2911 x_price_tbl (l_price_index).pricing_attribute22,
2912 x_price_tbl (l_price_index).pricing_attribute23,
2913 x_price_tbl (l_price_index).pricing_attribute24,
2914 x_price_tbl (l_price_index).pricing_attribute25,
2915 x_price_tbl (l_price_index).pricing_attribute26,
2916 x_price_tbl (l_price_index).pricing_attribute27,
2917 x_price_tbl (l_price_index).pricing_attribute28,
2918 x_price_tbl (l_price_index).pricing_attribute29,
2919 x_price_tbl (l_price_index).pricing_attribute30,
2920 x_price_tbl (l_price_index).pricing_attribute31,
2921 x_price_tbl (l_price_index).pricing_attribute32,
2922 x_price_tbl (l_price_index).pricing_attribute33,
2923 x_price_tbl (l_price_index).pricing_attribute34,
2924 x_price_tbl (l_price_index).pricing_attribute35,
2925 x_price_tbl (l_price_index).pricing_attribute36,
2926 x_price_tbl (l_price_index).pricing_attribute37,
2927 x_price_tbl (l_price_index).pricing_attribute38,
2928 x_price_tbl (l_price_index).pricing_attribute39,
2929 x_price_tbl (l_price_index).pricing_attribute40,
2930 x_price_tbl (l_price_index).pricing_attribute41,
2931 x_price_tbl (l_price_index).pricing_attribute42,
2932 x_price_tbl (l_price_index).pricing_attribute43,
2933 x_price_tbl (l_price_index).pricing_attribute44,
2934 x_price_tbl (l_price_index).pricing_attribute45,
2935 x_price_tbl (l_price_index).pricing_attribute46,
2936 x_price_tbl (l_price_index).pricing_attribute47,
2937 x_price_tbl (l_price_index).pricing_attribute48,
2938 x_price_tbl (l_price_index).pricing_attribute49,
2939 x_price_tbl (l_price_index).pricing_attribute50,
2940 x_price_tbl (l_price_index).pricing_attribute51,
2941 x_price_tbl (l_price_index).pricing_attribute52,
2942 x_price_tbl (l_price_index).pricing_attribute53,
2943 x_price_tbl (l_price_index).pricing_attribute54,
2944 x_price_tbl (l_price_index).pricing_attribute55,
2945 x_price_tbl (l_price_index).pricing_attribute56,
2946 x_price_tbl (l_price_index).pricing_attribute57,
2947 x_price_tbl (l_price_index).pricing_attribute58,
2948 x_price_tbl (l_price_index).pricing_attribute59,
2949 x_price_tbl (l_price_index).pricing_attribute60,
2950 x_price_tbl (l_price_index).pricing_attribute61,
2951 x_price_tbl (l_price_index).pricing_attribute62,
2952 x_price_tbl (l_price_index).pricing_attribute63,
2953 x_price_tbl (l_price_index).pricing_attribute64,
2954 x_price_tbl (l_price_index).pricing_attribute65,
2955 x_price_tbl (l_price_index).pricing_attribute66,
2956 x_price_tbl (l_price_index).pricing_attribute67,
2957 x_price_tbl (l_price_index).pricing_attribute68,
2958 x_price_tbl (l_price_index).pricing_attribute69,
2959 x_price_tbl (l_price_index).pricing_attribute70,
2960 x_price_tbl (l_price_index).pricing_attribute71,
2961 x_price_tbl (l_price_index).pricing_attribute72,
2962 x_price_tbl (l_price_index).pricing_attribute73,
2963 x_price_tbl (l_price_index).pricing_attribute74,
2964 x_price_tbl (l_price_index).pricing_attribute75,
2965 x_price_tbl (l_price_index).pricing_attribute76,
2966 x_price_tbl (l_price_index).pricing_attribute77,
2967 x_price_tbl (l_price_index).pricing_attribute78,
2968 x_price_tbl (l_price_index).pricing_attribute79,
2969 x_price_tbl (l_price_index).pricing_attribute80,
2970 x_price_tbl (l_price_index).pricing_attribute81,
2971 x_price_tbl (l_price_index).pricing_attribute82,
2972 x_price_tbl (l_price_index).pricing_attribute83,
2973 x_price_tbl (l_price_index).pricing_attribute84,
2974 x_price_tbl (l_price_index).pricing_attribute85,
2975 x_price_tbl (l_price_index).pricing_attribute86,
2976 x_price_tbl (l_price_index).pricing_attribute87,
2977 x_price_tbl (l_price_index).pricing_attribute88,
2978 x_price_tbl (l_price_index).pricing_attribute89,
2979 x_price_tbl (l_price_index).pricing_attribute90,
2980 x_price_tbl (l_price_index).pricing_attribute91,
2981 x_price_tbl (l_price_index).pricing_attribute92,
2982 x_price_tbl (l_price_index).pricing_attribute93,
2983 x_price_tbl (l_price_index).pricing_attribute94,
2984 x_price_tbl (l_price_index).pricing_attribute95,
2985 x_price_tbl (l_price_index).pricing_attribute96,
2986 x_price_tbl (l_price_index).pricing_attribute97,
2987 x_price_tbl (l_price_index).pricing_attribute98,
2988 x_price_tbl (l_price_index).pricing_attribute99,
2989 x_price_tbl (l_price_index).pricing_attribute100,
2990 x_price_tbl (l_price_index).CONTEXT,
2991 x_price_tbl (l_price_index).attribute1,
2992 x_price_tbl (l_price_index).attribute2,
2993 x_price_tbl (l_price_index).attribute3,
2994 x_price_tbl (l_price_index).attribute4,
2995 x_price_tbl (l_price_index).attribute5,
2996 x_price_tbl (l_price_index).attribute6,
2997 x_price_tbl (l_price_index).attribute7,
2998 x_price_tbl (l_price_index).attribute8,
2999 x_price_tbl (l_price_index).attribute9,
3000 x_price_tbl (l_price_index).attribute10,
3001 x_price_tbl (l_price_index).attribute11,
3002 x_price_tbl (l_price_index).attribute12,
3003 x_price_tbl (l_price_index).attribute13,
3004 x_price_tbl (l_price_index).attribute14,
3005 x_price_tbl (l_price_index).attribute15,
3006 x_price_tbl (l_price_index).object_version_number
3007 FROM DUAL;
3008
3009 x_pricing_attribs_tbl.DELETE (i);
3010 ELSE
3011 x_pricing_attribs_tbl.DELETE (i);
3012 END IF;
3013 END IF;
3014 END LOOP;
3015 END IF;
3016
3017 IF x_pricing_attribs_tbl.COUNT >= 1
3018 THEN
3019 FOR i IN
3020 x_pricing_attribs_tbl.FIRST .. x_pricing_attribs_tbl.LAST
3021 LOOP
3022 IF x_pricing_attribs_tbl.EXISTS (i)
3023 AND x_pricing_attribs_tbl (i).pricing_attribute_id IS NOT NULL
3024 THEN
3025 --Pricing attributes are different
3026 --Expire the existing one
3027 l_price_index := x_price_tbl.COUNT
3028 + 1;
3029 x_price_tbl (l_price_index).pricing_attribute_id :=
3030 x_pricing_attribs_tbl (
3031 x_pricing_attribs_tbl.FIRST
3032 ).pricing_attribute_id;
3033 x_price_tbl (l_price_index).object_version_number :=
3034 x_pricing_attribs_tbl (
3035 x_pricing_attribs_tbl.FIRST
3036 ).object_version_number;
3037 x_price_tbl (l_price_index).active_end_date :=
3038 l_end_date;
3039 x_pricing_attribs_tbl.DELETE (i);
3040 END IF;
3041 END LOOP;
3042 END IF;
3043
3044 IF csi_intf_repl_rec.pricing_attribute_id IS NULL
3045 AND csi_intf_repl_rec.pricing_context IS NOT NULL
3046 THEN
3047 l_price_index := x_price_tbl.COUNT
3048 + 1;
3049 x_price_tbl (l_price_index).instance_id :=
3050 csi_intf_repl_rec.instance_id;
3051 x_price_tbl (l_price_index).active_start_date :=
3052 NVL (csi_intf_repl_rec.pricing_att_start_date, SYSDATE);
3053 x_price_tbl (l_price_index).pricing_context :=
3054 csi_intf_repl_rec.pricing_context;
3055 --dbms_output.put_line('FOUR two:'||x_price_tbl(l_price_index).pricing_context);
3056 x_price_tbl (l_price_index).pricing_attribute1 :=
3057 csi_intf_repl_rec.pricing_attribute1;
3058 x_price_tbl (l_price_index).pricing_attribute2 :=
3059 csi_intf_repl_rec.pricing_attribute2;
3060 x_price_tbl (l_price_index).pricing_attribute3 :=
3061 csi_intf_repl_rec.pricing_attribute3;
3062 x_price_tbl (l_price_index).pricing_attribute4 :=
3063 csi_intf_repl_rec.pricing_attribute4;
3064 x_price_tbl (l_price_index).pricing_attribute5 :=
3065 csi_intf_repl_rec.pricing_attribute5;
3066 x_price_tbl (l_price_index).pricing_attribute6 :=
3067 csi_intf_repl_rec.pricing_attribute6;
3068 x_price_tbl (l_price_index).pricing_attribute7 :=
3069 csi_intf_repl_rec.pricing_attribute7;
3070 x_price_tbl (l_price_index).pricing_attribute8 :=
3071 csi_intf_repl_rec.pricing_attribute8;
3072 x_price_tbl (l_price_index).pricing_attribute9 :=
3073 csi_intf_repl_rec.pricing_attribute9;
3074 x_price_tbl (l_price_index).pricing_attribute10 :=
3075 csi_intf_repl_rec.pricing_attribute10;
3076 x_price_tbl (l_price_index).pricing_attribute11 :=
3077 csi_intf_repl_rec.pricing_attribute11;
3078 x_price_tbl (l_price_index).pricing_attribute12 :=
3079 csi_intf_repl_rec.pricing_attribute12;
3080 x_price_tbl (l_price_index).pricing_attribute13 :=
3081 csi_intf_repl_rec.pricing_attribute13;
3082 x_price_tbl (l_price_index).pricing_attribute14 :=
3083 csi_intf_repl_rec.pricing_attribute14;
3084 x_price_tbl (l_price_index).pricing_attribute15 :=
3085 csi_intf_repl_rec.pricing_attribute15;
3086 x_price_tbl (l_price_index).pricing_attribute16 :=
3087 csi_intf_repl_rec.pricing_attribute16;
3088 x_price_tbl (l_price_index).pricing_attribute17 :=
3089 csi_intf_repl_rec.pricing_attribute17;
3090 x_price_tbl (l_price_index).pricing_attribute18 :=
3091 csi_intf_repl_rec.pricing_attribute18;
3092 x_price_tbl (l_price_index).pricing_attribute19 :=
3093 csi_intf_repl_rec.pricing_attribute19;
3094 x_price_tbl (l_price_index).pricing_attribute20 :=
3095 csi_intf_repl_rec.pricing_attribute20;
3096 x_price_tbl (l_price_index).pricing_attribute21 :=
3097 csi_intf_repl_rec.pricing_attribute21;
3098 x_price_tbl (l_price_index).pricing_attribute22 :=
3099 csi_intf_repl_rec.pricing_attribute22;
3100 x_price_tbl (l_price_index).pricing_attribute23 :=
3101 csi_intf_repl_rec.pricing_attribute23;
3102 x_price_tbl (l_price_index).pricing_attribute24 :=
3103 csi_intf_repl_rec.pricing_attribute24;
3104 x_price_tbl (l_price_index).pricing_attribute25 :=
3105 csi_intf_repl_rec.pricing_attribute25;
3106 x_price_tbl (l_price_index).pricing_attribute26 :=
3107 csi_intf_repl_rec.pricing_attribute26;
3108 x_price_tbl (l_price_index).pricing_attribute27 :=
3109 csi_intf_repl_rec.pricing_attribute27;
3110 x_price_tbl (l_price_index).pricing_attribute28 :=
3111 csi_intf_repl_rec.pricing_attribute28;
3112 x_price_tbl (l_price_index).pricing_attribute29 :=
3113 csi_intf_repl_rec.pricing_attribute29;
3114 x_price_tbl (l_price_index).pricing_attribute30 :=
3115 csi_intf_repl_rec.pricing_attribute30;
3116 x_price_tbl (l_price_index).pricing_attribute31 :=
3117 csi_intf_repl_rec.pricing_attribute31;
3118 x_price_tbl (l_price_index).pricing_attribute32 :=
3119 csi_intf_repl_rec.pricing_attribute32;
3120 x_price_tbl (l_price_index).pricing_attribute33 :=
3121 csi_intf_repl_rec.pricing_attribute33;
3122 x_price_tbl (l_price_index).pricing_attribute34 :=
3123 csi_intf_repl_rec.pricing_attribute34;
3124 x_price_tbl (l_price_index).pricing_attribute35 :=
3125 csi_intf_repl_rec.pricing_attribute35;
3126 x_price_tbl (l_price_index).pricing_attribute36 :=
3127 csi_intf_repl_rec.pricing_attribute36;
3128 x_price_tbl (l_price_index).pricing_attribute37 :=
3129 csi_intf_repl_rec.pricing_attribute37;
3130 x_price_tbl (l_price_index).pricing_attribute38 :=
3131 csi_intf_repl_rec.pricing_attribute38;
3132 x_price_tbl (l_price_index).pricing_attribute39 :=
3133 csi_intf_repl_rec.pricing_attribute39;
3134 x_price_tbl (l_price_index).pricing_attribute40 :=
3135 csi_intf_repl_rec.pricing_attribute40;
3136 x_price_tbl (l_price_index).pricing_attribute41 :=
3137 csi_intf_repl_rec.pricing_attribute41;
3138 x_price_tbl (l_price_index).pricing_attribute42 :=
3139 csi_intf_repl_rec.pricing_attribute42;
3140 x_price_tbl (l_price_index).pricing_attribute43 :=
3141 csi_intf_repl_rec.pricing_attribute43;
3142 x_price_tbl (l_price_index).pricing_attribute44 :=
3143 csi_intf_repl_rec.pricing_attribute44;
3144 x_price_tbl (l_price_index).pricing_attribute45 :=
3145 csi_intf_repl_rec.pricing_attribute45;
3146 x_price_tbl (l_price_index).pricing_attribute46 :=
3147 csi_intf_repl_rec.pricing_attribute46;
3148 x_price_tbl (l_price_index).pricing_attribute47 :=
3149 csi_intf_repl_rec.pricing_attribute47;
3150 x_price_tbl (l_price_index).pricing_attribute48 :=
3151 csi_intf_repl_rec.pricing_attribute48;
3152 x_price_tbl (l_price_index).pricing_attribute49 :=
3153 csi_intf_repl_rec.pricing_attribute49;
3154 x_price_tbl (l_price_index).pricing_attribute50 :=
3155 csi_intf_repl_rec.pricing_attribute50;
3156 x_price_tbl (l_price_index).pricing_attribute51 :=
3157 csi_intf_repl_rec.pricing_attribute51;
3158 x_price_tbl (l_price_index).pricing_attribute52 :=
3159 csi_intf_repl_rec.pricing_attribute52;
3160 x_price_tbl (l_price_index).pricing_attribute53 :=
3161 csi_intf_repl_rec.pricing_attribute53;
3162 x_price_tbl (l_price_index).pricing_attribute54 :=
3163 csi_intf_repl_rec.pricing_attribute54;
3164 x_price_tbl (l_price_index).pricing_attribute55 :=
3165 csi_intf_repl_rec.pricing_attribute55;
3166 x_price_tbl (l_price_index).pricing_attribute56 :=
3167 csi_intf_repl_rec.pricing_attribute56;
3168 x_price_tbl (l_price_index).pricing_attribute57 :=
3169 csi_intf_repl_rec.pricing_attribute57;
3170 x_price_tbl (l_price_index).pricing_attribute58 :=
3171 csi_intf_repl_rec.pricing_attribute58;
3172 x_price_tbl (l_price_index).pricing_attribute59 :=
3173 csi_intf_repl_rec.pricing_attribute59;
3174 x_price_tbl (l_price_index).pricing_attribute60 :=
3175 csi_intf_repl_rec.pricing_attribute60;
3176 x_price_tbl (l_price_index).pricing_attribute61 :=
3177 csi_intf_repl_rec.pricing_attribute61;
3178 x_price_tbl (l_price_index).pricing_attribute62 :=
3179 csi_intf_repl_rec.pricing_attribute62;
3180 x_price_tbl (l_price_index).pricing_attribute63 :=
3181 csi_intf_repl_rec.pricing_attribute63;
3182 x_price_tbl (l_price_index).pricing_attribute64 :=
3183 csi_intf_repl_rec.pricing_attribute64;
3184 x_price_tbl (l_price_index).pricing_attribute65 :=
3185 csi_intf_repl_rec.pricing_attribute65;
3186 x_price_tbl (l_price_index).pricing_attribute66 :=
3187 csi_intf_repl_rec.pricing_attribute66;
3188 x_price_tbl (l_price_index).pricing_attribute67 :=
3189 csi_intf_repl_rec.pricing_attribute67;
3190 x_price_tbl (l_price_index).pricing_attribute68 :=
3191 csi_intf_repl_rec.pricing_attribute68;
3192 x_price_tbl (l_price_index).pricing_attribute69 :=
3193 csi_intf_repl_rec.pricing_attribute69;
3194 x_price_tbl (l_price_index).pricing_attribute70 :=
3195 csi_intf_repl_rec.pricing_attribute70;
3196 x_price_tbl (l_price_index).pricing_attribute71 :=
3197 csi_intf_repl_rec.pricing_attribute71;
3198 x_price_tbl (l_price_index).pricing_attribute72 :=
3199 csi_intf_repl_rec.pricing_attribute72;
3200 x_price_tbl (l_price_index).pricing_attribute73 :=
3201 csi_intf_repl_rec.pricing_attribute73;
3202 x_price_tbl (l_price_index).pricing_attribute74 :=
3203 csi_intf_repl_rec.pricing_attribute74;
3204 x_price_tbl (l_price_index).pricing_attribute75 :=
3205 csi_intf_repl_rec.pricing_attribute75;
3206 x_price_tbl (l_price_index).pricing_attribute76 :=
3207 csi_intf_repl_rec.pricing_attribute76;
3208 x_price_tbl (l_price_index).pricing_attribute77 :=
3209 csi_intf_repl_rec.pricing_attribute77;
3210 x_price_tbl (l_price_index).pricing_attribute78 :=
3211 csi_intf_repl_rec.pricing_attribute78;
3212 x_price_tbl (l_price_index).pricing_attribute79 :=
3213 csi_intf_repl_rec.pricing_attribute79;
3214 x_price_tbl (l_price_index).pricing_attribute80 :=
3215 csi_intf_repl_rec.pricing_attribute80;
3216 x_price_tbl (l_price_index).pricing_attribute81 :=
3217 csi_intf_repl_rec.pricing_attribute81;
3218 x_price_tbl (l_price_index).pricing_attribute82 :=
3219 csi_intf_repl_rec.pricing_attribute82;
3220 x_price_tbl (l_price_index).pricing_attribute83 :=
3221 csi_intf_repl_rec.pricing_attribute83;
3222 x_price_tbl (l_price_index).pricing_attribute84 :=
3223 csi_intf_repl_rec.pricing_attribute84;
3224 x_price_tbl (l_price_index).pricing_attribute85 :=
3225 csi_intf_repl_rec.pricing_attribute85;
3226 x_price_tbl (l_price_index).pricing_attribute86 :=
3227 csi_intf_repl_rec.pricing_attribute86;
3228 x_price_tbl (l_price_index).pricing_attribute87 :=
3229 csi_intf_repl_rec.pricing_attribute87;
3230 x_price_tbl (l_price_index).pricing_attribute88 :=
3231 csi_intf_repl_rec.pricing_attribute88;
3232 x_price_tbl (l_price_index).pricing_attribute89 :=
3233 csi_intf_repl_rec.pricing_attribute89;
3234 x_price_tbl (l_price_index).pricing_attribute90 :=
3235 csi_intf_repl_rec.pricing_attribute90;
3236 x_price_tbl (l_price_index).pricing_attribute91 :=
3237 csi_intf_repl_rec.pricing_attribute91;
3238 x_price_tbl (l_price_index).pricing_attribute92 :=
3239 csi_intf_repl_rec.pricing_attribute92;
3240 x_price_tbl (l_price_index).pricing_attribute93 :=
3241 csi_intf_repl_rec.pricing_attribute93;
3242 x_price_tbl (l_price_index).pricing_attribute94 :=
3243 csi_intf_repl_rec.pricing_attribute94;
3244 x_price_tbl (l_price_index).pricing_attribute95 :=
3245 csi_intf_repl_rec.pricing_attribute95;
3246 x_price_tbl (l_price_index).pricing_attribute96 :=
3247 csi_intf_repl_rec.pricing_attribute96;
3248 x_price_tbl (l_price_index).pricing_attribute97 :=
3249 csi_intf_repl_rec.pricing_attribute97;
3250 x_price_tbl (l_price_index).pricing_attribute98 :=
3251 csi_intf_repl_rec.pricing_attribute98;
3252 x_price_tbl (l_price_index).pricing_attribute99 :=
3253 csi_intf_repl_rec.pricing_attribute99;
3254 x_price_tbl (l_price_index).pricing_attribute100 :=
3255 csi_intf_repl_rec.pricing_attribute100;
3256 x_price_tbl (l_price_index).CONTEXT :=
3257 csi_intf_repl_rec.pricing_flex_context;
3258 x_price_tbl (l_price_index).attribute1 :=
3259 csi_intf_repl_rec.pricing_flex_attribute1;
3260 x_price_tbl (l_price_index).attribute2 :=
3261 csi_intf_repl_rec.pricing_flex_attribute2;
3262 x_price_tbl (l_price_index).attribute3 :=
3263 csi_intf_repl_rec.pricing_flex_attribute3;
3264 x_price_tbl (l_price_index).attribute4 :=
3265 csi_intf_repl_rec.pricing_flex_attribute4;
3266 x_price_tbl (l_price_index).attribute5 :=
3267 csi_intf_repl_rec.pricing_flex_attribute5;
3268 x_price_tbl (l_price_index).attribute6 :=
3269 csi_intf_repl_rec.pricing_flex_attribute6;
3270 x_price_tbl (l_price_index).attribute7 :=
3271 csi_intf_repl_rec.pricing_flex_attribute7;
3272 x_price_tbl (l_price_index).attribute8 :=
3273 csi_intf_repl_rec.pricing_flex_attribute8;
3274 x_price_tbl (l_price_index).attribute9 :=
3275 csi_intf_repl_rec.pricing_flex_attribute9;
3276 x_price_tbl (l_price_index).attribute10 :=
3277 csi_intf_repl_rec.pricing_flex_attribute10;
3278 x_price_tbl (l_price_index).attribute11 :=
3279 csi_intf_repl_rec.pricing_flex_attribute11;
3280 x_price_tbl (l_price_index).attribute12 :=
3281 csi_intf_repl_rec.pricing_flex_attribute12;
3282 x_price_tbl (l_price_index).attribute13 :=
3283 csi_intf_repl_rec.pricing_flex_attribute13;
3284 x_price_tbl (l_price_index).attribute14 :=
3285 csi_intf_repl_rec.pricing_flex_attribute14;
3286 x_price_tbl (l_price_index).attribute15 :=
3287 csi_intf_repl_rec.pricing_flex_attribute15;
3288 END IF;
3289
3290
3291 --dbms_output.put_line('Org asignments zero count:'||x_org_units_header_tbl.COUNT);
3292
3293 IF x_org_units_header_tbl.COUNT >= 1
3294 THEN
3295 FOR i IN
3296 x_org_units_header_tbl.FIRST .. x_org_units_header_tbl.LAST
3297 LOOP
3298 IF x_org_units_header_tbl.EXISTS (i)
3299 AND x_org_units_header_tbl (i).active_end_date IS NOT NULL
3300 THEN
3301 IF NOT csi_intf_repl_rec.ou_end_date IS NULL
3302 THEN
3303 x_party_header_tbl.DELETE (i);
3304 END IF;
3305 --dbms_output.put_line('Org Asignments ONE');
3306 END IF;
3307 END LOOP;
3308 END IF;
3309
3310
3311 --dbms_output.put_line('Org asignments one count:'||x_org_units_header_tbl.COUNT);
3312 l_org_index := x_org_assign_tbl.COUNT
3313 + 1;
3314
3315 IF x_org_units_header_tbl.COUNT >= 1
3316 THEN
3317 FOR i IN
3318 x_org_units_header_tbl.FIRST .. x_org_units_header_tbl.LAST
3319 LOOP
3320 IF x_org_units_header_tbl.EXISTS (i)
3321 AND csi_intf_repl_rec.instance_ou_id IS NOT NULL
3322 AND x_org_units_header_tbl (i).instance_ou_id =
3323 csi_intf_repl_rec.instance_ou_id
3324 THEN
3325 IF NOT ( NVL (
3326 csi_intf_repl_rec.operating_unit,
3327 l_miss_num
3328 ) =
3329 NVL (
3330 x_org_units_header_tbl (i).operating_unit_id,
3331 l_miss_num
3332 )
3333 AND NVL (
3334 csi_intf_repl_rec.ou_relation_type,
3335 l_miss_char
3336 ) =
3337 NVL (
3338 x_org_units_header_tbl (i).relationship_type_code,
3339 l_miss_char
3340 )
3341 AND NVL (
3342 csi_intf_repl_rec.ou_end_date,
3343 l_miss_date
3344 ) =
3345 NVL (
3346 x_org_units_header_tbl (i).active_end_date,
3347 l_miss_date
3348 )
3349 )
3350 THEN
3351 x_org_assign_tbl (l_org_index).instance_ou_id :=
3352 x_org_units_header_tbl (1).instance_ou_id;
3353 x_org_assign_tbl (l_org_index).object_version_number :=
3354 x_org_units_header_tbl (1).object_version_number;
3355
3356 SELECT DECODE (
3357 csi_intf_repl_rec.ou_relation_type,
3358 x_org_units_header_tbl (1).relationship_type_code, l_miss_char,
3359 DECODE (
3360 x_org_units_header_tbl (i).relationship_type_code,
3361 NULL, l_miss_char,
3362 NULL
3363 ), csi_intf_repl_rec.ou_relation_type
3364 ),
3365 DECODE (
3366 csi_intf_repl_rec.operating_unit,
3367 x_org_units_header_tbl (1).operating_unit_id, l_miss_num,
3368 DECODE (
3369 x_org_units_header_tbl (i).operating_unit_id,
3370 NULL, l_miss_num,
3371 NULL
3372 ), csi_intf_repl_rec.operating_unit
3373 ),
3374 DECODE (
3375 csi_intf_repl_rec.ou_start_date,
3376 x_org_units_header_tbl (1).active_start_date, l_miss_date,
3377 DECODE (
3378 x_org_units_header_tbl (i).active_start_date,
3379 NULL, l_miss_date,
3380 NULL
3381 ), csi_intf_repl_rec.ou_start_date
3382 ),
3383 DECODE (
3384 csi_intf_repl_rec.ou_end_date,
3385 NULL, NULL,
3386 l_miss_date
3387 )
3388 INTO x_org_assign_tbl (l_org_index).relationship_type_code,
3389 x_org_assign_tbl (l_org_index).operating_unit_id,
3390 x_org_assign_tbl (l_org_index).active_start_date,
3391 x_org_assign_tbl (l_org_index).active_end_date
3392 FROM DUAL;
3393
3394 x_org_units_header_tbl.DELETE (i);
3395 ELSE
3396 x_org_units_header_tbl.DELETE (i);
3397 END IF;
3398 END IF;
3399 END LOOP;
3400 END IF;
3401
3402 IF x_org_units_header_tbl.COUNT >= 1
3403 THEN
3404 --Expire the existing one
3405 FOR i IN
3406 x_org_units_header_tbl.FIRST .. x_org_units_header_tbl.LAST
3407 LOOP
3408 IF x_org_units_header_tbl.EXISTS (i)
3409 THEN
3410 l_org_index := x_org_assign_tbl.COUNT
3411 + 1;
3412 x_org_assign_tbl (l_org_index).instance_ou_id :=
3413 x_org_units_header_tbl (1).instance_ou_id;
3414 x_org_assign_tbl (l_org_index).object_version_number :=
3415 x_org_units_header_tbl (1).object_version_number;
3416 x_org_assign_tbl (l_org_index).active_end_date :=
3417 l_end_date;
3418 x_org_units_header_tbl.DELETE (i);
3419 END IF;
3420 END LOOP;
3421 END IF;
3422
3423 IF csi_intf_repl_rec.instance_ou_id IS NULL
3424 AND csi_intf_repl_rec.operating_unit IS NOT NULL
3425 THEN
3426 l_org_index := x_org_assign_tbl.COUNT
3427 + 1;
3428 ---create a new org assignment at the end
3429 x_org_assign_tbl (l_org_index).instance_id :=
3430 csi_intf_repl_rec.instance_id;
3431 x_org_assign_tbl (l_org_index).active_start_date :=
3432 csi_intf_repl_rec.ou_start_date;
3433 x_org_assign_tbl (l_org_index).operating_unit_id :=
3434 csi_intf_repl_rec.operating_unit;
3435 x_org_assign_tbl (l_org_index).relationship_type_code :=
3436 csi_intf_repl_rec.ou_relation_type;
3437 END IF;
3438
3439
3440 --dbms_output.put_line('just before parties current count: '||to_char(x_party_header_tbl.COUNT));
3441 x_party_cache_tbl := x_party_header_tbl;
3442
3443 IF x_party_header_tbl.COUNT >= 1
3444 THEN
3445 FOR i IN x_party_header_tbl.FIRST .. x_party_header_tbl.LAST
3446 LOOP
3447 IF x_party_header_tbl.EXISTS (i)
3448 AND x_party_header_tbl (i).active_end_date IS NOT NULL
3449 THEN
3450 BEGIN
3451 b_end_dated := TRUE;
3452
3453 FOR csi_intf_party_acct_rec IN
3454 csi_intf_party_acct_cur (
3455 csi_intf_repl_rec.inst_interface_id
3456 )
3457 LOOP
3458 IF csi_intf_party_acct_rec.party_end_date IS NULL
3459 THEN
3460 b_end_dated := FALSE;
3461 EXIT;
3462 END IF;
3463 END LOOP;
3464 END;
3465
3466 IF b_end_dated
3467 THEN
3468 x_party_header_tbl.DELETE (i);
3469 END IF;
3470 --dbms_output.put_line('Parties ONE');
3471 END IF;
3472 END LOOP;
3473 END IF;
3474
3475 IF x_party_header_tbl.COUNT >= 1
3476 THEN
3477 FOR i IN x_party_header_tbl.FIRST .. x_party_header_tbl.LAST
3478 LOOP
3479 FOR csi_intf_party_acct_rec IN
3480 csi_intf_party_acct_cur (
3481 csi_intf_repl_rec.inst_interface_id
3482 )
3483 LOOP
3484 IF x_party_header_tbl.EXISTS (i)
3485 AND csi_intf_party_acct_rec.instance_party_id IS NOT NULL
3486 AND x_party_header_tbl (i).instance_party_id =
3487 csi_intf_party_acct_rec.instance_party_id
3488 THEN
3489
3490 --dbms_output.put_line('Parties four');
3491 IF NOT ( NVL (
3492 csi_intf_party_acct_rec.party_id,
3493 l_miss_num
3494 ) = NVL (
3495 x_party_header_tbl (i).party_id,
3496 l_miss_num
3497 )
3498 AND NVL (
3499 csi_intf_party_acct_rec.party_source_table,
3500 l_miss_char
3501 ) =
3502 NVL (
3503 x_party_header_tbl (i).party_source_table,
3504 l_miss_char
3505 )
3506 AND NVL (
3507 csi_intf_party_acct_rec.party_relationship_type_code,
3508 l_miss_char
3509 ) =
3510 NVL (
3511 x_party_header_tbl (i).relationship_type_code,
3512 l_miss_char
3513 )
3514 AND NVL (
3515 csi_intf_party_acct_rec.party_end_date,
3516 l_miss_date
3517 ) =
3518 NVL (
3519 x_party_header_tbl (i).active_end_date,
3520 l_miss_date
3521 )
3522 AND NVL (
3523 csi_intf_party_acct_rec.contact_flag,
3524 l_miss_char
3525 ) = NVL (
3526 x_party_header_tbl (i).contact_flag,
3527 l_miss_char
3528 )
3529 AND NVL (
3530 csi_intf_party_acct_rec.contact_ip_id,
3531 l_miss_num
3532 ) =
3533 NVL (
3534 x_party_header_tbl (i).contact_ip_id,
3535 l_miss_num
3536 )
3537 AND NVL (
3538 csi_intf_party_acct_rec.party_context,
3539 l_miss_char
3540 ) = NVL (
3541 x_party_header_tbl (i).CONTEXT,
3542 l_miss_char
3543 )
3544 AND NVL (
3545 csi_intf_party_acct_rec.party_attribute1,
3546 l_miss_char
3547 ) = NVL (
3548 x_party_header_tbl (i).attribute1,
3549 l_miss_char
3550 )
3551 AND NVL (
3552 csi_intf_party_acct_rec.party_attribute2,
3553 l_miss_char
3554 ) = NVL (
3555 x_party_header_tbl (i).attribute2,
3556 l_miss_char
3557 )
3558 AND NVL (
3559 csi_intf_party_acct_rec.party_attribute3,
3560 l_miss_char
3561 ) = NVL (
3562 x_party_header_tbl (i).attribute3,
3563 l_miss_char
3564 )
3565 AND NVL (
3566 csi_intf_party_acct_rec.party_attribute4,
3567 l_miss_char
3568 ) = NVL (
3569 x_party_header_tbl (i).attribute4,
3570 l_miss_char
3571 )
3572 AND NVL (
3573 csi_intf_party_acct_rec.party_attribute5,
3574 l_miss_char
3575 ) = NVL (
3576 x_party_header_tbl (i).attribute5,
3577 l_miss_char
3578 )
3579 AND NVL (
3580 csi_intf_party_acct_rec.party_attribute6,
3581 l_miss_char
3582 ) = NVL (
3583 x_party_header_tbl (i).attribute6,
3584 l_miss_char
3585 )
3586 AND NVL (
3587 csi_intf_party_acct_rec.party_attribute7,
3588 l_miss_char
3589 ) = NVL (
3590 x_party_header_tbl (i).attribute7,
3591 l_miss_char
3592 )
3593 AND NVL (
3594 csi_intf_party_acct_rec.party_attribute8,
3595 l_miss_char
3596 ) = NVL (
3597 x_party_header_tbl (i).attribute8,
3598 l_miss_char
3599 )
3600 AND NVL (
3601 csi_intf_party_acct_rec.party_attribute9,
3602 l_miss_char
3603 ) = NVL (
3604 x_party_header_tbl (i).attribute9,
3605 l_miss_char
3606 )
3607 AND NVL (
3608 csi_intf_party_acct_rec.party_attribute10,
3609 l_miss_char
3610 ) = NVL (
3611 x_party_header_tbl (i).attribute10,
3612 l_miss_char
3613 )
3614 AND NVL (
3615 csi_intf_party_acct_rec.party_attribute11,
3616 l_miss_char
3617 ) = NVL (
3618 x_party_header_tbl (i).attribute11,
3619 l_miss_char
3620 )
3621 AND NVL (
3622 csi_intf_party_acct_rec.party_attribute12,
3623 l_miss_char
3624 ) = NVL (
3625 x_party_header_tbl (i).attribute12,
3626 l_miss_char
3627 )
3628 AND NVL (
3629 csi_intf_party_acct_rec.party_attribute13,
3630 l_miss_char
3631 ) = NVL (
3632 x_party_header_tbl (i).attribute13,
3633 l_miss_char
3634 )
3635 AND NVL (
3636 csi_intf_party_acct_rec.party_attribute14,
3637 l_miss_char
3638 ) = NVL (
3639 x_party_header_tbl (i).attribute14,
3640 l_miss_char
3641 )
3642 AND NVL (
3643 csi_intf_party_acct_rec.party_attribute15,
3644 l_miss_char
3645 ) = NVL (
3646 x_party_header_tbl (i).attribute15,
3647 l_miss_char
3648 )
3649 )
3650 THEN
3651
3652 --dbms_output.put_line('Parties five');
3653 l_party_index := x_party_tbl.COUNT
3654 + 1;
3655 x_party_tbl (l_party_index).instance_party_id :=
3656 x_party_header_tbl (i).instance_party_id;
3657 x_party_tbl (l_party_index).instance_id :=
3658 x_party_header_tbl (i).instance_id;
3659 x_party_tbl (l_party_index).object_version_number :=
3660 x_party_header_tbl (i).object_version_number;
3661
3662 SELECT DECODE (
3663 csi_intf_party_acct_rec.party_source_table,
3664 x_party_header_tbl (i).party_source_table, l_miss_char,
3665 NULL, DECODE (
3666 x_party_header_tbl (i).party_source_table,
3667 NULL, l_miss_char,
3668 NULL
3669 ),
3670 csi_intf_party_acct_rec.party_source_table
3671 ),
3672 DECODE (
3673 csi_intf_party_acct_rec.party_id,
3674 x_party_header_tbl (i).party_id, l_miss_num,
3675 NULL, DECODE (
3676 x_party_header_tbl (i).party_id,
3677 NULL, l_miss_num,
3678 NULL
3679 ),
3680 csi_intf_party_acct_rec.party_id
3681 ),
3682 DECODE (
3683 csi_intf_party_acct_rec.party_relationship_type_code,
3684 'OWNER', 'OWNER',
3685 x_party_header_tbl (i).relationship_type_code, l_miss_char,
3686 NULL, DECODE (
3687 x_party_header_tbl (i).relationship_type_code,
3688 NULL, l_miss_char,
3689 NULL
3690 ),
3691 csi_intf_party_acct_rec.party_relationship_type_code
3692 ),
3693 DECODE (
3694 csi_intf_party_acct_rec.party_start_date,
3695 x_party_header_tbl (i).active_start_date, l_miss_date,
3696 DECODE (
3697 x_party_header_tbl (i).active_start_date,
3698 NULL, l_miss_date,
3699 NULL
3700 ), csi_intf_party_acct_rec.party_start_date
3701 ),
3702 DECODE (
3703 csi_intf_party_acct_rec.party_end_date,
3704 x_party_header_tbl (i).active_end_date, l_miss_date,
3705 NULL, DECODE (
3706 x_party_header_tbl (i).active_end_date,
3707 NULL, l_miss_date,
3708 NULL
3709 ),
3710 csi_intf_party_acct_rec.party_end_date
3711 ),
3712 DECODE (
3713 csi_intf_party_acct_rec.contact_flag,
3714 x_party_header_tbl (i).contact_flag, l_miss_char,
3715 NULL, DECODE (
3716 x_party_header_tbl (i).contact_flag,
3717 NULL, l_miss_char,
3718 NULL
3719 ),
3720 csi_intf_party_acct_rec.contact_flag
3721 ),
3722 DECODE (
3723 csi_intf_party_acct_rec.contact_ip_id,
3724 x_party_header_tbl (i).contact_ip_id, l_miss_num,
3725 NULL, DECODE (
3726 x_party_header_tbl (i).contact_ip_id,
3727 NULL, l_miss_num,
3728 NULL
3729 ),
3730 csi_intf_party_acct_rec.contact_ip_id
3731 ),
3732 DECODE (
3733 csi_intf_party_acct_rec.party_attribute1,
3734 x_party_header_tbl (i).attribute1, l_miss_char,
3735 NULL, DECODE (
3736 x_party_header_tbl (i).attribute1,
3737 NULL, l_miss_char,
3738 NULL
3739 ),
3740 csi_intf_party_acct_rec.party_attribute1
3741 ),
3742 DECODE (
3743 csi_intf_party_acct_rec.party_attribute2,
3744 x_party_header_tbl (i).attribute2, l_miss_char,
3745 NULL, DECODE (
3746 x_party_header_tbl (i).attribute2,
3747 NULL, l_miss_char,
3748 NULL
3749 ),
3750 csi_intf_party_acct_rec.party_attribute2
3751 ),
3752 DECODE (
3753 csi_intf_party_acct_rec.party_attribute3,
3754 x_party_header_tbl (i).attribute3, l_miss_char,
3755 NULL, DECODE (
3756 x_party_header_tbl (i).attribute3,
3757 NULL, l_miss_char,
3758 NULL
3759 ),
3760 csi_intf_party_acct_rec.party_attribute3
3761 ),
3762 DECODE (
3763 csi_intf_party_acct_rec.party_attribute4,
3764 x_party_header_tbl (i).attribute4, l_miss_char,
3765 NULL, DECODE (
3766 x_party_header_tbl (i).attribute4,
3767 NULL, l_miss_char,
3768 NULL
3769 ),
3770 csi_intf_party_acct_rec.party_attribute4
3771 ),
3772 DECODE (
3773 csi_intf_party_acct_rec.party_attribute5,
3774 x_party_header_tbl (i).attribute5, l_miss_char,
3775 NULL, DECODE (
3776 x_party_header_tbl (i).attribute5,
3777 NULL, l_miss_char,
3778 NULL
3779 ),
3780 csi_intf_party_acct_rec.party_attribute5
3781 ),
3782 DECODE (
3783 csi_intf_party_acct_rec.party_attribute6,
3784 x_party_header_tbl (i).attribute6, l_miss_char,
3785 NULL, DECODE (
3786 x_party_header_tbl (i).attribute6,
3787 NULL, l_miss_char,
3788 NULL
3789 ),
3790 csi_intf_party_acct_rec.party_attribute6
3791 ),
3792 DECODE (
3793 csi_intf_party_acct_rec.party_attribute7,
3794 x_party_header_tbl (i).attribute7, l_miss_char,
3795 NULL, DECODE (
3796 x_party_header_tbl (i).attribute7,
3797 NULL, l_miss_char,
3798 NULL
3799 ),
3800 csi_intf_party_acct_rec.party_attribute7
3801 ),
3802 DECODE (
3803 csi_intf_party_acct_rec.party_attribute8,
3804 x_party_header_tbl (i).attribute8, l_miss_char,
3805 NULL, DECODE (
3806 x_party_header_tbl (i).attribute8,
3807 NULL, l_miss_char,
3808 NULL
3809 ),
3810 csi_intf_party_acct_rec.party_attribute8
3811 ),
3812 DECODE (
3813 csi_intf_party_acct_rec.party_attribute9,
3814 x_party_header_tbl (i).attribute9, l_miss_char,
3815 NULL, DECODE (
3816 x_party_header_tbl (i).attribute9,
3817 NULL, l_miss_char,
3818 NULL
3819 ),
3820 csi_intf_party_acct_rec.party_attribute9
3821 ),
3822 DECODE (
3823 csi_intf_party_acct_rec.party_attribute10,
3824 x_party_header_tbl (i).attribute10, l_miss_char,
3825 NULL, DECODE (
3826 x_party_header_tbl (i).attribute10,
3827 NULL, l_miss_char,
3828 NULL
3829 ),
3830 csi_intf_party_acct_rec.party_attribute10
3831 ),
3832 DECODE (
3833 csi_intf_party_acct_rec.party_attribute11,
3834 x_party_header_tbl (i).attribute11, l_miss_char,
3835 NULL, DECODE (
3836 x_party_header_tbl (i).attribute11,
3837 NULL, l_miss_char,
3838 NULL
3839 ),
3840 csi_intf_party_acct_rec.party_attribute11
3841 ),
3842 DECODE (
3843 csi_intf_party_acct_rec.party_attribute12,
3844 x_party_header_tbl (i).attribute12, l_miss_char,
3845 NULL, DECODE (
3846 x_party_header_tbl (i).attribute12,
3847 NULL, l_miss_char,
3848 NULL
3849 ),
3850 csi_intf_party_acct_rec.party_attribute12
3851 ),
3852 DECODE (
3853 csi_intf_party_acct_rec.party_attribute13,
3854 x_party_header_tbl (i).attribute13, l_miss_char,
3855 NULL, DECODE (
3856 x_party_header_tbl (i).attribute13,
3857 NULL, l_miss_char,
3858 NULL
3859 ),
3860 csi_intf_party_acct_rec.party_attribute13
3861 ),
3862 DECODE (
3863 csi_intf_party_acct_rec.party_attribute14,
3864 x_party_header_tbl (i).attribute14, l_miss_char,
3865 NULL, DECODE (
3866 x_party_header_tbl (i).attribute14,
3867 NULL, l_miss_char,
3868 NULL
3869 ),
3870 csi_intf_party_acct_rec.party_attribute14
3871 ),
3872 DECODE (
3873 csi_intf_party_acct_rec.party_attribute15,
3874 x_party_header_tbl (i).attribute15, l_miss_char,
3875 NULL, DECODE (
3876 x_party_header_tbl (i).attribute15,
3877 NULL, l_miss_char,
3878 NULL
3879 ),
3880 csi_intf_party_acct_rec.party_attribute15
3881 )
3882 INTO x_party_tbl (l_party_index).party_source_table,
3883 x_party_tbl (l_party_index).party_id,
3884 x_party_tbl (l_party_index).relationship_type_code,
3885 x_party_tbl (l_party_index).active_start_date,
3886 x_party_tbl (l_party_index).active_end_date,
3887 x_party_tbl (l_party_index).contact_flag,
3888 x_party_tbl (l_party_index).contact_ip_id,
3889 x_party_tbl (l_party_index).attribute1,
3890 x_party_tbl (l_party_index).attribute2,
3891 x_party_tbl (l_party_index).attribute3,
3892 x_party_tbl (l_party_index).attribute4,
3893 x_party_tbl (l_party_index).attribute5,
3894 x_party_tbl (l_party_index).attribute6,
3895 x_party_tbl (l_party_index).attribute7,
3896 x_party_tbl (l_party_index).attribute8,
3897 x_party_tbl (l_party_index).attribute9,
3898 x_party_tbl (l_party_index).attribute10,
3899 x_party_tbl (l_party_index).attribute11,
3900 x_party_tbl (l_party_index).attribute12,
3901 x_party_tbl (l_party_index).attribute13,
3902 x_party_tbl (l_party_index).attribute14,
3903 x_party_tbl (l_party_index).attribute15
3904 FROM DUAL;
3905
3906 x_party_header_tbl.DELETE (i);
3907 ELSE
3908 x_party_header_tbl.DELETE (i);
3909 END IF;
3910 END IF;
3911 END LOOP;
3912 END LOOP;
3913 END IF;
3914
3915 IF x_party_header_tbl.COUNT >= 1
3916 THEN
3917 FOR i IN x_party_header_tbl.FIRST .. x_party_header_tbl.LAST
3918 LOOP
3919 IF x_party_header_tbl.EXISTS (i)
3920 AND x_party_header_tbl (i).active_end_date IS NULL
3921 THEN
3922 --dbms_output.put_line('Parties Seven');
3923 l_party_index := x_party_tbl.COUNT
3924 + 1;
3925 x_party_tbl (l_party_index).instance_party_id :=
3926 x_party_header_tbl (i).instance_party_id;
3927 --dbms_output.put_line('instance_party being end dated:'||x_party_header_tbl(i).instance_party_id);
3928 x_party_tbl (l_party_index).object_version_number :=
3929 x_party_header_tbl (i).object_version_number;
3930 x_party_tbl (l_party_index).active_end_date :=
3931 l_end_date;
3932 x_party_header_tbl.DELETE (i);
3933 END IF;
3934 END LOOP;
3935 END IF;
3936
3937 FOR csi_intf_party_acct_rec IN
3938 csi_intf_party_acct_cur (csi_intf_repl_rec.inst_interface_id)
3939 LOOP
3940 --dbms_output.put_line('Parties nine');
3941 IF csi_intf_party_acct_rec.instance_party_id IS NULL
3942 THEN
3943 --dbms_output.put_line('Parties ten');
3944 l_party_index := x_party_tbl.COUNT
3945 + 1;
3946 x_party_tbl (l_party_index).party_id :=
3947 csi_intf_party_acct_rec.party_id;
3948 x_party_tbl (l_party_index).party_source_table :=
3949 csi_intf_party_acct_rec.party_source_table;
3950 x_party_tbl (l_party_index).instance_id :=
3951 r_instance_id;
3952 x_party_tbl (l_party_index).relationship_type_code :=
3953 csi_intf_party_acct_rec.party_relationship_type_code;
3954 x_party_tbl (l_party_index).active_start_date :=
3955 csi_intf_party_acct_rec.party_start_date;
3956 x_party_tbl (l_party_index).contact_flag :=
3957 csi_intf_party_acct_rec.contact_flag;
3958 x_party_tbl (l_party_index).contact_ip_id :=
3959 csi_intf_party_acct_rec.contact_ip_id;
3960 x_party_tbl (l_party_index).CONTEXT :=
3961 csi_intf_party_acct_rec.party_context;
3962 x_party_tbl (l_party_index).attribute1 :=
3963 csi_intf_party_acct_rec.party_attribute1;
3964 x_party_tbl (l_party_index).attribute2 :=
3965 csi_intf_party_acct_rec.party_attribute2;
3966 x_party_tbl (l_party_index).attribute3 :=
3967 csi_intf_party_acct_rec.party_attribute3;
3968 x_party_tbl (l_party_index).attribute4 :=
3969 csi_intf_party_acct_rec.party_attribute4;
3970 x_party_tbl (l_party_index).attribute5 :=
3971 csi_intf_party_acct_rec.party_attribute5;
3972 x_party_tbl (l_party_index).attribute6 :=
3973 csi_intf_party_acct_rec.party_attribute6;
3974 x_party_tbl (l_party_index).attribute7 :=
3975 csi_intf_party_acct_rec.party_attribute7;
3976 x_party_tbl (l_party_index).attribute8 :=
3977 csi_intf_party_acct_rec.party_attribute8;
3978 x_party_tbl (l_party_index).attribute9 :=
3979 csi_intf_party_acct_rec.party_attribute9;
3980 x_party_tbl (l_party_index).attribute10 :=
3981 csi_intf_party_acct_rec.party_attribute10;
3982 x_party_tbl (l_party_index).attribute11 :=
3983 csi_intf_party_acct_rec.party_attribute11;
3984 x_party_tbl (l_party_index).attribute12 :=
3985 csi_intf_party_acct_rec.party_attribute12;
3986 x_party_tbl (l_party_index).attribute13 :=
3987 csi_intf_party_acct_rec.party_attribute13;
3988 x_party_tbl (l_party_index).attribute14 :=
3989 csi_intf_party_acct_rec.party_attribute14;
3990 x_party_tbl (l_party_index).attribute15 :=
3991 csi_intf_party_acct_rec.party_attribute15;
3992
3993 IF csi_intf_party_acct_rec.party_account1_id IS NOT NULL
3994 THEN
3995 l_party_account_index := x_account_tbl.COUNT
3996 + 1;
3997 x_account_tbl (l_party_account_index).parent_tbl_index :=
3998 l_party_index;
3999 x_account_tbl (l_party_account_index).party_account_id :=
4000 csi_intf_party_acct_rec.party_account1_id;
4001 x_account_tbl (l_party_account_index).relationship_type_code :=
4002 csi_intf_party_acct_rec.acct1_relationship_type_code;
4003 x_account_tbl (l_party_account_index).active_start_date :=
4004 csi_intf_party_acct_rec.party_acct1_start_date;
4005 x_account_tbl (l_party_account_index).bill_to_address :=
4006 csi_intf_party_acct_rec.bill_to_address1;
4007 x_account_tbl (l_party_account_index).ship_to_address :=
4008 csi_intf_party_acct_rec.ship_to_address1;
4009 x_account_tbl (l_party_account_index).CONTEXT :=
4010 csi_intf_party_acct_rec.account1_context;
4011 x_account_tbl (l_party_account_index).attribute1 :=
4012 csi_intf_party_acct_rec.account1_attribute1;
4013 x_account_tbl (l_party_account_index).attribute2 :=
4014 csi_intf_party_acct_rec.account1_attribute2;
4015 x_account_tbl (l_party_account_index).attribute3 :=
4016 csi_intf_party_acct_rec.account1_attribute3;
4017 x_account_tbl (l_party_account_index).attribute4 :=
4018 csi_intf_party_acct_rec.account1_attribute4;
4019 x_account_tbl (l_party_account_index).attribute5 :=
4020 csi_intf_party_acct_rec.account1_attribute5;
4021 x_account_tbl (l_party_account_index).attribute6 :=
4022 csi_intf_party_acct_rec.account1_attribute6;
4023 x_account_tbl (l_party_account_index).attribute7 :=
4024 csi_intf_party_acct_rec.account1_attribute7;
4025 x_account_tbl (l_party_account_index).attribute8 :=
4026 csi_intf_party_acct_rec.account1_attribute8;
4027 x_account_tbl (l_party_account_index).attribute9 :=
4028 csi_intf_party_acct_rec.account1_attribute9;
4029 x_account_tbl (l_party_account_index).attribute10 :=
4030 csi_intf_party_acct_rec.account1_attribute10;
4031 x_account_tbl (l_party_account_index).attribute11 :=
4032 csi_intf_party_acct_rec.account1_attribute11;
4033 x_account_tbl (l_party_account_index).attribute12 :=
4034 csi_intf_party_acct_rec.account1_attribute12;
4035 x_account_tbl (l_party_account_index).attribute13 :=
4036 csi_intf_party_acct_rec.account1_attribute13;
4037 x_account_tbl (l_party_account_index).attribute14 :=
4038 csi_intf_party_acct_rec.account1_attribute14;
4039 x_account_tbl (l_party_account_index).attribute15 :=
4040 csi_intf_party_acct_rec.account1_attribute15;
4041 END IF;
4042
4043 IF csi_intf_party_acct_rec.party_account2_id IS NOT NULL
4044 THEN
4045 l_party_account_index := x_account_tbl.COUNT
4046 + 1;
4047 x_account_tbl (l_party_account_index).parent_tbl_index :=
4048 l_party_index;
4049 x_account_tbl (l_party_account_index).party_account_id :=
4050 csi_intf_party_acct_rec.party_account2_id;
4051 x_account_tbl (l_party_account_index).relationship_type_code :=
4052 csi_intf_party_acct_rec.acct2_relationship_type_code;
4053 x_account_tbl (l_party_account_index).active_start_date :=
4054 csi_intf_party_acct_rec.party_acct2_start_date;
4055 x_account_tbl (l_party_account_index).bill_to_address :=
4056 csi_intf_party_acct_rec.bill_to_address2;
4057 x_account_tbl (l_party_account_index).ship_to_address :=
4058 csi_intf_party_acct_rec.ship_to_address2;
4059 x_account_tbl (l_party_account_index).CONTEXT :=
4060 csi_intf_party_acct_rec.account2_context;
4061 x_account_tbl (l_party_account_index).attribute1 :=
4062 csi_intf_party_acct_rec.account2_attribute1;
4063 x_account_tbl (l_party_account_index).attribute2 :=
4064 csi_intf_party_acct_rec.account2_attribute2;
4065 x_account_tbl (l_party_account_index).attribute3 :=
4066 csi_intf_party_acct_rec.account2_attribute3;
4067 x_account_tbl (l_party_account_index).attribute4 :=
4068 csi_intf_party_acct_rec.account2_attribute4;
4069 x_account_tbl (l_party_account_index).attribute5 :=
4070 csi_intf_party_acct_rec.account2_attribute5;
4071 x_account_tbl (l_party_account_index).attribute6 :=
4072 csi_intf_party_acct_rec.account2_attribute6;
4073 x_account_tbl (l_party_account_index).attribute7 :=
4074 csi_intf_party_acct_rec.account2_attribute7;
4075 x_account_tbl (l_party_account_index).attribute8 :=
4076 csi_intf_party_acct_rec.account2_attribute8;
4077 x_account_tbl (l_party_account_index).attribute9 :=
4078 csi_intf_party_acct_rec.account2_attribute9;
4079 x_account_tbl (l_party_account_index).attribute10 :=
4080 csi_intf_party_acct_rec.account2_attribute10;
4081 x_account_tbl (l_party_account_index).attribute11 :=
4082 csi_intf_party_acct_rec.account2_attribute11;
4083 x_account_tbl (l_party_account_index).attribute12 :=
4084 csi_intf_party_acct_rec.account2_attribute12;
4085 x_account_tbl (l_party_account_index).attribute13 :=
4086 csi_intf_party_acct_rec.account2_attribute13;
4087 x_account_tbl (l_party_account_index).attribute14 :=
4088 csi_intf_party_acct_rec.account2_attribute14;
4089 x_account_tbl (l_party_account_index).attribute15 :=
4090 csi_intf_party_acct_rec.account2_attribute15;
4091 END IF;
4092
4093 IF csi_intf_party_acct_rec.party_account3_id IS NOT NULL
4094 THEN
4095 l_party_account_index := x_account_tbl.COUNT
4096 + 1;
4097 x_account_tbl (l_party_account_index).parent_tbl_index :=
4098 l_party_index;
4099 x_account_tbl (l_party_account_index).party_account_id :=
4100 csi_intf_party_acct_rec.party_account3_id;
4101 x_account_tbl (l_party_account_index).relationship_type_code :=
4102 csi_intf_party_acct_rec.acct3_relationship_type_code;
4103 x_account_tbl (l_party_account_index).active_start_date :=
4104 csi_intf_party_acct_rec.party_acct3_start_date;
4105 x_account_tbl (l_party_account_index).bill_to_address :=
4106 csi_intf_party_acct_rec.bill_to_address3;
4107 x_account_tbl (l_party_account_index).ship_to_address :=
4108 csi_intf_party_acct_rec.ship_to_address3;
4109 x_account_tbl (l_party_account_index).CONTEXT :=
4110 csi_intf_party_acct_rec.account3_context;
4111 x_account_tbl (l_party_account_index).attribute1 :=
4112 csi_intf_party_acct_rec.account3_attribute1;
4113 x_account_tbl (l_party_account_index).attribute2 :=
4114 csi_intf_party_acct_rec.account3_attribute2;
4115 x_account_tbl (l_party_account_index).attribute3 :=
4116 csi_intf_party_acct_rec.account3_attribute3;
4117 x_account_tbl (l_party_account_index).attribute4 :=
4118 csi_intf_party_acct_rec.account3_attribute4;
4119 x_account_tbl (l_party_account_index).attribute5 :=
4120 csi_intf_party_acct_rec.account3_attribute5;
4121 x_account_tbl (l_party_account_index).attribute6 :=
4122 csi_intf_party_acct_rec.account3_attribute6;
4123 x_account_tbl (l_party_account_index).attribute7 :=
4124 csi_intf_party_acct_rec.account3_attribute7;
4125 x_account_tbl (l_party_account_index).attribute8 :=
4126 csi_intf_party_acct_rec.account3_attribute8;
4127 x_account_tbl (l_party_account_index).attribute9 :=
4128 csi_intf_party_acct_rec.account3_attribute9;
4129 x_account_tbl (l_party_account_index).attribute10 :=
4130 csi_intf_party_acct_rec.account3_attribute10;
4131 x_account_tbl (l_party_account_index).attribute11 :=
4132 csi_intf_party_acct_rec.account3_attribute11;
4133 x_account_tbl (l_party_account_index).attribute12 :=
4134 csi_intf_party_acct_rec.account3_attribute12;
4135 x_account_tbl (l_party_account_index).attribute13 :=
4136 csi_intf_party_acct_rec.account3_attribute13;
4137 x_account_tbl (l_party_account_index).attribute14 :=
4138 csi_intf_party_acct_rec.account3_attribute14;
4139 x_account_tbl (l_party_account_index).attribute15 :=
4140 csi_intf_party_acct_rec.account3_attribute15;
4141 END IF;
4142 END IF;
4143 END LOOP;
4144
4145
4146 --dbms_output.put_line('x_party_account.count before delete:'||x_party_account_header_tbl.COUNT);
4147
4148 IF x_party_account_header_tbl.COUNT >= 1
4149 THEN
4150 FOR i IN
4151 x_party_account_header_tbl.FIRST .. x_party_account_header_tbl.LAST
4152 LOOP
4153 IF x_party_account_header_tbl.EXISTS (i)
4154 AND x_party_account_header_tbl (i).active_end_date IS NOT NULL
4155 THEN
4156 x_party_account_header_tbl.DELETE (i);
4157 END IF;
4158 END LOOP;
4159 END IF;
4160
4161
4162 --dbms_output.put_line('x_party_account.count after delete:'||x_party_account_header_tbl.COUNT);
4163 IF x_party_account_header_tbl.COUNT >= 1
4164 THEN
4165 FOR i IN
4166 x_party_account_header_tbl.FIRST .. x_party_account_header_tbl.LAST
4167 LOOP
4168 FOR csi_intf_party_acct_rec IN
4169 csi_intf_party_acct_cur (
4170 csi_intf_repl_rec.inst_interface_id
4171 )
4172 LOOP
4173 IF x_party_account_header_tbl.EXISTS (i)
4174 AND csi_intf_party_acct_rec.ip_account1_id IS NOT NULL
4175 AND x_party_account_header_tbl (i).ip_account_id =
4176 csi_intf_party_acct_rec.ip_account1_id
4177 THEN
4178 IF NOT ( NVL (
4179 csi_intf_party_acct_rec.party_account1_id,
4180 l_miss_num
4181 ) =
4182 NVL (
4183 x_party_account_header_tbl (i).party_account_id,
4184 l_miss_num
4185 )
4186 AND NVL (
4187 csi_intf_party_acct_rec.instance_party_id,
4188 l_miss_num
4189 ) =
4190 NVL (
4191 x_party_account_header_tbl (i).instance_party_id,
4192 l_miss_num
4193 )
4194 AND NVL (
4195 csi_intf_party_acct_rec.acct1_relationship_type_code,
4196 l_miss_char
4197 ) =
4198 NVL (
4199 x_party_account_header_tbl (i).relationship_type_code,
4200 l_miss_char
4201 )
4202 AND NVL (
4203 csi_intf_party_acct_rec.bill_to_address1,
4204 l_miss_num
4205 ) =
4206 NVL (
4207 x_party_account_header_tbl (i).bill_to_address,
4208 l_miss_num
4209 )
4210 AND NVL (
4211 csi_intf_party_acct_rec.ship_to_address1,
4212 l_miss_num
4213 ) =
4214 NVL (
4215 x_party_account_header_tbl (i).ship_to_address,
4216 l_miss_num
4217 )
4218 AND NVL (
4219 csi_intf_party_acct_rec.account1_context,
4220 l_miss_char
4221 ) =
4222 NVL (
4223 x_party_account_header_tbl (i).CONTEXT,
4224 l_miss_char
4225 )
4226 AND NVL (
4227 csi_intf_party_acct_rec.account1_attribute1,
4228 l_miss_char
4229 ) =
4230 NVL (
4231 x_party_account_header_tbl (i).attribute1,
4232 l_miss_char
4233 )
4234 AND NVL (
4235 csi_intf_party_acct_rec.account1_attribute2,
4236 l_miss_char
4237 ) =
4238 NVL (
4239 x_party_account_header_tbl (i).attribute2,
4240 l_miss_char
4241 )
4242 AND NVL (
4243 csi_intf_party_acct_rec.account1_attribute3,
4244 l_miss_char
4245 ) =
4246 NVL (
4247 x_party_account_header_tbl (i).attribute3,
4248 l_miss_char
4249 )
4250 AND NVL (
4251 csi_intf_party_acct_rec.account1_attribute4,
4252 l_miss_char
4253 ) =
4254 NVL (
4255 x_party_account_header_tbl (i).attribute4,
4256 l_miss_char
4257 )
4258 AND NVL (
4259 csi_intf_party_acct_rec.account1_attribute5,
4260 l_miss_char
4261 ) =
4262 NVL (
4263 x_party_account_header_tbl (i).attribute5,
4264 l_miss_char
4265 )
4266 AND NVL (
4267 csi_intf_party_acct_rec.account1_attribute6,
4268 l_miss_char
4269 ) =
4270 NVL (
4271 x_party_account_header_tbl (i).attribute6,
4272 l_miss_char
4273 )
4274 AND NVL (
4275 csi_intf_party_acct_rec.account1_attribute7,
4276 l_miss_char
4277 ) =
4278 NVL (
4279 x_party_account_header_tbl (i).attribute7,
4280 l_miss_char
4281 )
4282 AND NVL (
4283 csi_intf_party_acct_rec.account1_attribute8,
4284 l_miss_char
4285 ) =
4286 NVL (
4287 x_party_account_header_tbl (i).attribute8,
4288 l_miss_char
4289 )
4290 AND NVL (
4291 csi_intf_party_acct_rec.account1_attribute9,
4292 l_miss_char
4293 ) =
4294 NVL (
4295 x_party_account_header_tbl (i).attribute9,
4296 l_miss_char
4297 )
4298 AND NVL (
4299 csi_intf_party_acct_rec.account1_attribute10,
4300 l_miss_char
4301 ) =
4302 NVL (
4303 x_party_account_header_tbl (i).attribute10,
4304 l_miss_char
4305 )
4306 AND NVL (
4307 csi_intf_party_acct_rec.account1_attribute11,
4308 l_miss_char
4309 ) =
4310 NVL (
4311 x_party_account_header_tbl (i).attribute11,
4312 l_miss_char
4313 )
4314 AND NVL (
4315 csi_intf_party_acct_rec.account1_attribute12,
4316 l_miss_char
4317 ) =
4318 NVL (
4319 x_party_account_header_tbl (i).attribute12,
4320 l_miss_char
4321 )
4322 AND NVL (
4323 csi_intf_party_acct_rec.account1_attribute13,
4324 l_miss_char
4325 ) =
4326 NVL (
4327 x_party_account_header_tbl (i).attribute13,
4328 l_miss_char
4329 )
4330 AND NVL (
4331 csi_intf_party_acct_rec.account1_attribute14,
4332 l_miss_char
4333 ) =
4334 NVL (
4335 x_party_account_header_tbl (i).attribute14,
4336 l_miss_char
4337 )
4338 AND NVL (
4339 csi_intf_party_acct_rec.account1_attribute15,
4340 l_miss_char
4341 ) =
4342 NVL (
4343 x_party_account_header_tbl (i).attribute15,
4344 l_miss_char
4345 )
4346 )
4347 THEN
4348 l_party_account_index := x_account_tbl.COUNT
4349 + 1;
4350 x_account_tbl (l_party_account_index).ip_account_id :=
4351 x_party_account_header_tbl (i).ip_account_id;
4352 x_account_tbl (l_party_account_index).instance_party_id :=
4353 x_party_account_header_tbl (i).instance_party_id;
4354 x_account_tbl (l_party_account_index).relationship_type_code :=
4355 x_party_account_header_tbl (i).relationship_type_code;
4356 x_account_tbl (l_party_account_index).object_version_number :=
4357 x_party_account_header_tbl (i).object_version_number;
4358 x_account_tbl (l_party_account_index).parent_tbl_index :=
4359 get_parent_tbl_index (
4360 x_account_tbl (l_party_account_index).instance_party_id,
4361 x_account_tbl (l_party_account_index).relationship_type_code
4362 );
4363
4364 IF x_account_tbl (l_party_account_index).parent_tbl_index IS NULL
4365 THEN
4366 l_party_index := x_party_tbl.COUNT
4367 + 1;
4368 x_party_tbl (l_party_index).instance_party_id :=
4369 x_account_tbl (l_party_account_index).instance_party_id;
4370 x_party_tbl (l_party_index).relationship_type_code :=
4371 x_account_tbl (l_party_account_index).relationship_type_code;
4372 x_party_tbl (l_party_index).object_version_number :=
4373 get_obj_ver_num (
4374 x_party_cache_tbl,
4375 x_account_tbl (i).instance_party_id
4376 );
4377 END IF;
4378
4379 --dbms_output.put_line('Before Select:');
4380
4381 SELECT DECODE (
4382 csi_intf_party_acct_rec.acct1_relationship_type_code,
4383 x_party_account_header_tbl (i).relationship_type_code, l_miss_char,
4384 DECODE (
4385 x_party_account_header_tbl (i).relationship_type_code,
4386 NULL, l_miss_char,
4387 NULL
4388 ), csi_intf_party_acct_rec.acct1_relationship_type_code
4389 ),
4390 DECODE (
4391 csi_intf_party_acct_rec.party_account1_id,
4392 x_party_account_header_tbl (i).party_account_id, l_miss_num,
4393 NULL, DECODE (
4394 x_party_account_header_tbl (i).party_account_id,
4395 NULL, l_miss_num,
4396 NULL
4397 ),
4398 csi_intf_party_acct_rec.party_account1_id
4399 ),
4400 DECODE (
4401 csi_intf_party_acct_rec.bill_to_address1,
4402 x_party_account_header_tbl (i).bill_to_address, l_miss_num,
4403 NULL, DECODE (
4404 x_party_account_header_tbl (i).bill_to_address,
4405 NULL, l_miss_num,
4406 NULL
4407 ),
4408 csi_intf_party_acct_rec.bill_to_address1
4409 ),
4410 DECODE (
4411 csi_intf_party_acct_rec.ship_to_address1,
4412 x_party_account_header_tbl (i).ship_to_address, l_miss_num,
4413 NULL, DECODE (
4414 x_party_account_header_tbl (i).ship_to_address,
4415 NULL, l_miss_num,
4416 NULL
4417 ),
4418 csi_intf_party_acct_rec.ship_to_address1
4419 ),
4420 DECODE (
4421 csi_intf_party_acct_rec.account1_context,
4422 x_party_account_header_tbl (i).CONTEXT, l_miss_char,
4423 NULL, DECODE (
4424 x_party_account_header_tbl (i).CONTEXT,
4425 NULL, l_miss_char,
4426 NULL
4427 ),
4428 csi_intf_party_acct_rec.account1_context
4429 ),
4430 DECODE (
4431 csi_intf_party_acct_rec.account1_attribute1,
4432 x_party_account_header_tbl (i).attribute1, l_miss_char,
4433 NULL, DECODE (
4434 x_party_account_header_tbl (i).attribute1,
4435 NULL, l_miss_char,
4436 NULL
4437 ),
4438 csi_intf_party_acct_rec.account1_attribute1
4439 ),
4440 DECODE (
4441 csi_intf_party_acct_rec.account1_attribute2,
4442 x_party_account_header_tbl (i).attribute2, l_miss_char,
4443 NULL, DECODE (
4444 x_party_account_header_tbl (i).attribute2,
4445 NULL, l_miss_char,
4446 NULL
4447 ),
4448 csi_intf_party_acct_rec.account1_attribute2
4449 ),
4450 DECODE (
4451 csi_intf_party_acct_rec.account1_attribute3,
4452 x_party_account_header_tbl (i).attribute3, l_miss_char,
4453 NULL, DECODE (
4454 x_party_account_header_tbl (i).attribute3,
4455 NULL, l_miss_char,
4456 NULL
4457 ),
4458 csi_intf_party_acct_rec.account1_attribute3
4459 ),
4460 DECODE (
4461 csi_intf_party_acct_rec.account1_attribute4,
4462 x_party_account_header_tbl (i).attribute4, l_miss_char,
4463 NULL, DECODE (
4464 x_party_account_header_tbl (i).attribute4,
4465 NULL, l_miss_char,
4466 NULL
4467 ),
4468 csi_intf_party_acct_rec.account1_attribute4
4469 ),
4470 DECODE (
4471 csi_intf_party_acct_rec.account1_attribute5,
4472 x_party_account_header_tbl (i).attribute5, l_miss_char,
4473 NULL, DECODE (
4474 x_party_account_header_tbl (i).attribute5,
4475 NULL, l_miss_char,
4476 NULL
4477 ),
4478 csi_intf_party_acct_rec.account1_attribute5
4479 ),
4480 DECODE (
4481 csi_intf_party_acct_rec.account1_attribute6,
4482 x_party_account_header_tbl (i).attribute6, l_miss_char,
4483 NULL, DECODE (
4484 x_party_account_header_tbl (i).attribute6,
4485 NULL, l_miss_char,
4486 NULL
4487 ),
4488 csi_intf_party_acct_rec.account1_attribute6
4489 ),
4490 DECODE (
4491 csi_intf_party_acct_rec.account1_attribute7,
4492 x_party_account_header_tbl (i).attribute7, l_miss_char,
4493 NULL, DECODE (
4494 x_party_account_header_tbl (i).attribute7,
4495 NULL, l_miss_char,
4496 NULL
4497 ),
4498 csi_intf_party_acct_rec.account1_attribute7
4499 ),
4500 DECODE (
4501 csi_intf_party_acct_rec.account1_attribute8,
4502 x_party_account_header_tbl (i).attribute8, l_miss_char,
4503 NULL, DECODE (
4504 x_party_account_header_tbl (i).attribute8,
4505 NULL, l_miss_char,
4506 NULL
4507 ),
4508 csi_intf_party_acct_rec.account1_attribute8
4509 ),
4510 DECODE (
4511 csi_intf_party_acct_rec.account1_attribute9,
4512 x_party_account_header_tbl (i).attribute9, l_miss_char,
4513 NULL, DECODE (
4514 x_party_account_header_tbl (i).attribute9,
4515 NULL, l_miss_char,
4516 NULL
4517 ),
4518 csi_intf_party_acct_rec.account1_attribute9
4519 ),
4520 DECODE (
4521 csi_intf_party_acct_rec.account1_attribute10,
4522 x_party_account_header_tbl (i).attribute10, l_miss_char,
4523 NULL, DECODE (
4524 x_party_account_header_tbl (i).attribute10,
4525 NULL, l_miss_char,
4526 NULL
4527 ),
4528 csi_intf_party_acct_rec.account1_attribute10
4529 ),
4530 DECODE (
4531 csi_intf_party_acct_rec.account1_attribute11,
4532 x_party_account_header_tbl (i).attribute11, l_miss_char,
4533 NULL, DECODE (
4534 x_party_account_header_tbl (i).attribute11,
4535 NULL, l_miss_char,
4536 NULL
4537 ),
4538 csi_intf_party_acct_rec.account1_attribute11
4539 ),
4540 DECODE (
4541 csi_intf_party_acct_rec.account1_attribute12,
4542 x_party_account_header_tbl (i).attribute12, l_miss_char,
4543 NULL, DECODE (
4544 x_party_account_header_tbl (i).attribute12,
4545 NULL, l_miss_char,
4546 NULL
4547 ),
4548 csi_intf_party_acct_rec.account1_attribute12
4549 ),
4550 DECODE (
4551 csi_intf_party_acct_rec.account1_attribute13,
4552 x_party_account_header_tbl (i).attribute13, l_miss_char,
4553 NULL, DECODE (
4554 x_party_account_header_tbl (i).attribute13,
4555 NULL, l_miss_char,
4556 NULL
4557 ),
4558 csi_intf_party_acct_rec.account1_attribute13
4559 ),
4560 DECODE (
4561 csi_intf_party_acct_rec.account1_attribute14,
4562 x_party_account_header_tbl (i).attribute14, l_miss_char,
4563 NULL, DECODE (
4564 x_party_account_header_tbl (i).attribute14,
4565 NULL, l_miss_char,
4566 NULL
4567 ),
4568 csi_intf_party_acct_rec.account1_attribute14
4569 ),
4570 DECODE (
4571 csi_intf_party_acct_rec.account1_attribute15,
4572 x_party_account_header_tbl (i).attribute15, l_miss_char,
4573 NULL, DECODE (
4574 x_party_account_header_tbl (i).attribute15,
4575 NULL, l_miss_char,
4576 NULL
4577 ),
4578 csi_intf_party_acct_rec.account1_attribute15
4579 )
4580 INTO x_account_tbl (l_party_account_index).relationship_type_code,
4581 x_account_tbl (l_party_account_index).party_account_id,
4582 x_account_tbl (l_party_account_index).bill_to_address,
4583 x_account_tbl (l_party_account_index).ship_to_address,
4584 x_account_tbl (l_party_account_index).CONTEXT,
4585 x_account_tbl (l_party_account_index).attribute1,
4586 x_account_tbl (l_party_account_index).attribute2,
4587 x_account_tbl (l_party_account_index).attribute3,
4588 x_account_tbl (l_party_account_index).attribute4,
4589 x_account_tbl (l_party_account_index).attribute5,
4590 x_account_tbl (l_party_account_index).attribute6,
4591 x_account_tbl (l_party_account_index).attribute7,
4592 x_account_tbl (l_party_account_index).attribute8,
4593 x_account_tbl (l_party_account_index).attribute9,
4594 x_account_tbl (l_party_account_index).attribute10,
4595 x_account_tbl (l_party_account_index).attribute11,
4596 x_account_tbl (l_party_account_index).attribute12,
4597 x_account_tbl (l_party_account_index).attribute13,
4598 x_account_tbl (l_party_account_index).attribute14,
4599 x_account_tbl (l_party_account_index).attribute15
4600 FROM DUAL;
4601
4602 x_party_account_header_tbl.DELETE (i);
4603 ELSE
4604 x_party_account_header_tbl.DELETE (i);
4605 END IF;
4606 END IF;
4607 END LOOP;
4608 END LOOP;
4609 END IF;
4610
4611 --dbms_output.put_line('HUNDRED one');
4612 IF x_party_account_header_tbl.COUNT >= 1
4613 THEN
4614 FOR i IN
4615 x_party_account_header_tbl.FIRST .. x_party_account_header_tbl.LAST
4616 LOOP
4617 FOR csi_intf_party_acct_rec IN
4618 csi_intf_party_acct_cur (
4619 csi_intf_repl_rec.inst_interface_id
4620 )
4621 LOOP
4622 IF x_party_account_header_tbl.EXISTS (i)
4623 AND csi_intf_party_acct_rec.ip_account2_id IS NOT NULL
4624 AND x_party_account_header_tbl (i).ip_account_id =
4625 csi_intf_party_acct_rec.ip_account2_id
4626 THEN
4627 IF NOT ( NVL (
4628 csi_intf_party_acct_rec.party_account2_id,
4629 l_miss_num
4630 ) =
4631 NVL (
4632 x_party_account_header_tbl (i).party_account_id,
4633 l_miss_num
4634 )
4635 AND NVL (
4636 csi_intf_party_acct_rec.instance_party_id,
4637 l_miss_num
4638 ) =
4639 NVL (
4640 x_party_account_header_tbl (i).instance_party_id,
4641 l_miss_num
4642 )
4643 AND NVL (
4644 csi_intf_party_acct_rec.acct2_relationship_type_code,
4645 l_miss_char
4646 ) =
4647 NVL (
4648 x_party_account_header_tbl (i).relationship_type_code,
4649 l_miss_char
4650 )
4651 AND NVL (
4652 csi_intf_party_acct_rec.bill_to_address2,
4653 l_miss_num
4654 ) =
4655 NVL (
4656 x_party_account_header_tbl (i).bill_to_address,
4657 l_miss_num
4658 )
4659 AND NVL (
4660 csi_intf_party_acct_rec.ship_to_address2,
4661 l_miss_num
4662 ) =
4663 NVL (
4664 x_party_account_header_tbl (i).ship_to_address,
4665 l_miss_num
4666 )
4667 AND NVL (
4668 csi_intf_party_acct_rec.account2_context,
4669 l_miss_char
4670 ) =
4671 NVL (
4672 x_party_account_header_tbl (i).CONTEXT,
4673 l_miss_char
4674 )
4675 AND NVL (
4676 csi_intf_party_acct_rec.account2_attribute1,
4677 l_miss_char
4678 ) =
4679 NVL (
4680 x_party_account_header_tbl (i).attribute1,
4681 l_miss_char
4682 )
4683 AND NVL (
4684 csi_intf_party_acct_rec.account2_attribute2,
4685 l_miss_char
4686 ) =
4687 NVL (
4688 x_party_account_header_tbl (i).attribute2,
4689 l_miss_char
4690 )
4691 AND NVL (
4692 csi_intf_party_acct_rec.account2_attribute3,
4693 l_miss_char
4694 ) =
4695 NVL (
4696 x_party_account_header_tbl (i).attribute3,
4697 l_miss_char
4698 )
4699 AND NVL (
4700 csi_intf_party_acct_rec.account2_attribute4,
4701 l_miss_char
4702 ) =
4703 NVL (
4704 x_party_account_header_tbl (i).attribute4,
4705 l_miss_char
4706 )
4707 AND NVL (
4708 csi_intf_party_acct_rec.account2_attribute5,
4709 l_miss_char
4710 ) =
4711 NVL (
4712 x_party_account_header_tbl (i).attribute5,
4713 l_miss_char
4714 )
4715 AND NVL (
4716 csi_intf_party_acct_rec.account2_attribute6,
4717 l_miss_char
4718 ) =
4719 NVL (
4720 x_party_account_header_tbl (i).attribute6,
4721 l_miss_char
4722 )
4723 AND NVL (
4724 csi_intf_party_acct_rec.account2_attribute7,
4725 l_miss_char
4726 ) =
4727 NVL (
4728 x_party_account_header_tbl (i).attribute7,
4729 l_miss_char
4730 )
4731 AND NVL (
4732 csi_intf_party_acct_rec.account2_attribute8,
4733 l_miss_char
4734 ) =
4735 NVL (
4736 x_party_account_header_tbl (i).attribute8,
4737 l_miss_char
4738 )
4739 AND NVL (
4740 csi_intf_party_acct_rec.account2_attribute9,
4741 l_miss_char
4742 ) =
4743 NVL (
4744 x_party_account_header_tbl (i).attribute9,
4745 l_miss_char
4746 )
4747 AND NVL (
4748 csi_intf_party_acct_rec.account2_attribute10,
4749 l_miss_char
4750 ) =
4751 NVL (
4752 x_party_account_header_tbl (i).attribute10,
4753 l_miss_char
4754 )
4755 AND NVL (
4756 csi_intf_party_acct_rec.account2_attribute11,
4757 l_miss_char
4758 ) =
4759 NVL (
4760 x_party_account_header_tbl (i).attribute11,
4761 l_miss_char
4762 )
4763 AND NVL (
4764 csi_intf_party_acct_rec.account2_attribute12,
4765 l_miss_char
4766 ) =
4767 NVL (
4768 x_party_account_header_tbl (i).attribute12,
4769 l_miss_char
4770 )
4771 AND NVL (
4772 csi_intf_party_acct_rec.account2_attribute13,
4773 l_miss_char
4774 ) =
4775 NVL (
4776 x_party_account_header_tbl (i).attribute13,
4777 l_miss_char
4778 )
4779 AND NVL (
4780 csi_intf_party_acct_rec.account2_attribute14,
4781 l_miss_char
4782 ) =
4783 NVL (
4784 x_party_account_header_tbl (i).attribute14,
4785 l_miss_char
4786 )
4787 AND NVL (
4788 csi_intf_party_acct_rec.account2_attribute15,
4789 l_miss_char
4790 ) =
4791 NVL (
4792 x_party_account_header_tbl (i).attribute15,
4793 l_miss_char
4794 )
4795 )
4796 THEN
4797 l_party_account_index := NVL (
4798 x_account_tbl.COUNT
4799 + 1,
4800 x_account_tbl.FIRST
4801 );
4802 x_account_tbl (l_party_account_index).ip_account_id :=
4803 x_party_account_header_tbl (i).ip_account_id;
4804 x_account_tbl (l_party_account_index).instance_party_id :=
4805 x_party_account_header_tbl (i).instance_party_id;
4806 x_account_tbl (l_party_account_index).object_version_number :=
4807 x_party_account_header_tbl (i).object_version_number;
4808 x_account_tbl (l_party_account_index).parent_tbl_index :=
4809 get_parent_tbl_index (
4810 x_account_tbl (l_party_account_index).instance_party_id,
4811 x_account_tbl (l_party_account_index).relationship_type_code
4812 );
4813
4814 IF x_account_tbl (l_party_account_index).parent_tbl_index IS NULL
4815 THEN
4816 l_party_index := x_party_tbl.COUNT
4817 + 1;
4818 x_party_tbl (l_party_index).instance_party_id :=
4819 x_account_tbl (l_party_account_index).instance_party_id;
4820 x_party_tbl (l_party_index).relationship_type_code :=
4821 x_account_tbl (l_party_account_index).relationship_type_code;
4822 x_party_tbl (l_party_index).object_version_number :=
4823 get_obj_ver_num (
4824 x_party_cache_tbl,
4825 x_account_tbl (i).instance_party_id
4826 );
4827 END IF;
4828
4829 SELECT DECODE (
4830 csi_intf_party_acct_rec.acct2_relationship_type_code,
4831 x_party_account_header_tbl (i).relationship_type_code, l_miss_char,
4832 NULL, DECODE (
4833 x_party_header_tbl (i).relationship_type_code,
4834 NULL, l_miss_char,
4835 NULL
4836 ),
4837 csi_intf_party_acct_rec.acct2_relationship_type_code
4838 ),
4839 DECODE (
4840 csi_intf_party_acct_rec.party_account2_id,
4841 x_party_account_header_tbl (i).party_account_id, l_miss_num,
4842 NULL, DECODE (
4843 x_party_account_header_tbl (i).party_account_id,
4844 NULL, l_miss_num,
4845 NULL
4846 ),
4847 csi_intf_party_acct_rec.party_account2_id
4848 ),
4849 DECODE (
4850 csi_intf_party_acct_rec.bill_to_address2,
4851 x_party_account_header_tbl (i).bill_to_address, l_miss_num,
4852 NULL, DECODE (
4853 x_party_account_header_tbl (i).bill_to_address,
4854 NULL, l_miss_num,
4855 NULL
4856 ),
4857 csi_intf_party_acct_rec.bill_to_address1
4858 ),
4859 DECODE (
4860 csi_intf_party_acct_rec.ship_to_address2,
4861 x_party_account_header_tbl (i).ship_to_address, l_miss_num,
4862 NULL, DECODE (
4863 x_party_account_header_tbl (i).ship_to_address,
4864 NULL, l_miss_num,
4865 NULL
4866 ),
4867 csi_intf_party_acct_rec.ship_to_address1
4868 ),
4869 DECODE (
4870 csi_intf_party_acct_rec.account2_context,
4871 x_party_account_header_tbl (i).CONTEXT, l_miss_char,
4872 NULL, DECODE (
4873 x_party_header_tbl (i).CONTEXT,
4874 NULL, l_miss_char,
4875 NULL
4876 ),
4877 csi_intf_party_acct_rec.account2_context
4878 ),
4879 DECODE (
4880 csi_intf_party_acct_rec.account2_attribute1,
4881 x_party_account_header_tbl (i).attribute1, l_miss_char,
4882 NULL, DECODE (
4883 x_party_header_tbl (i).attribute1,
4884 NULL, l_miss_char,
4885 NULL
4886 ),
4887 csi_intf_party_acct_rec.account2_attribute1
4888 ),
4889 DECODE (
4890 csi_intf_party_acct_rec.account2_attribute2,
4891 x_party_account_header_tbl (i).attribute2, l_miss_char,
4892 NULL, DECODE (
4893 x_party_header_tbl (i).attribute2,
4894 NULL, l_miss_char,
4895 NULL
4896 ),
4897 csi_intf_party_acct_rec.account2_attribute2
4898 ),
4899 DECODE (
4900 csi_intf_party_acct_rec.account2_attribute3,
4901 x_party_account_header_tbl (i).attribute3, l_miss_char,
4902 NULL, DECODE (
4903 x_party_header_tbl (i).attribute3,
4904 NULL, l_miss_char,
4905 NULL
4906 ),
4907 csi_intf_party_acct_rec.account2_attribute3
4908 ),
4909 DECODE (
4910 csi_intf_party_acct_rec.account2_attribute4,
4911 x_party_account_header_tbl (i).attribute4, l_miss_char,
4912 NULL, DECODE (
4913 x_party_header_tbl (i).attribute4,
4914 NULL, l_miss_char,
4915 NULL
4916 ),
4917 csi_intf_party_acct_rec.account2_attribute4
4918 ),
4919 DECODE (
4920 csi_intf_party_acct_rec.account2_attribute5,
4921 x_party_account_header_tbl (i).attribute5, l_miss_char,
4922 NULL, DECODE (
4923 x_party_header_tbl (i).attribute5,
4924 NULL, l_miss_char,
4925 NULL
4926 ),
4927 csi_intf_party_acct_rec.account2_attribute5
4928 ),
4929 DECODE (
4930 csi_intf_party_acct_rec.account2_attribute6,
4931 x_party_account_header_tbl (i).attribute6, l_miss_char,
4932 NULL, DECODE (
4933 x_party_header_tbl (i).attribute6,
4934 NULL, l_miss_char,
4935 NULL
4936 ),
4937 csi_intf_party_acct_rec.account2_attribute6
4938 ),
4939 DECODE (
4940 csi_intf_party_acct_rec.account2_attribute7,
4941 x_party_account_header_tbl (i).attribute7, l_miss_char,
4942 NULL, DECODE (
4943 x_party_header_tbl (i).attribute7,
4944 NULL, l_miss_char,
4945 NULL
4946 ),
4947 csi_intf_party_acct_rec.account2_attribute7
4948 ),
4949 DECODE (
4950 csi_intf_party_acct_rec.account2_attribute8,
4951 x_party_account_header_tbl (i).attribute8, l_miss_char,
4952 NULL, DECODE (
4953 x_party_header_tbl (i).attribute8,
4954 NULL, l_miss_char,
4955 NULL
4956 ),
4957 csi_intf_party_acct_rec.account2_attribute8
4958 ),
4959 DECODE (
4960 csi_intf_party_acct_rec.account2_attribute9,
4961 x_party_account_header_tbl (i).attribute9, l_miss_char,
4962 NULL, DECODE (
4963 x_party_header_tbl (i).attribute9,
4964 NULL, l_miss_char,
4965 NULL
4966 ),
4967 csi_intf_party_acct_rec.account2_attribute9
4968 ),
4969 DECODE (
4970 csi_intf_party_acct_rec.account2_attribute10,
4971 x_party_account_header_tbl (i).attribute10, l_miss_char,
4972 NULL, DECODE (
4973 x_party_header_tbl (i).attribute10,
4974 NULL, l_miss_char,
4975 NULL
4976 ),
4977 csi_intf_party_acct_rec.account2_attribute10
4978 ),
4979 DECODE (
4980 csi_intf_party_acct_rec.account2_attribute11,
4981 x_party_account_header_tbl (i).attribute11, l_miss_char,
4982 NULL, DECODE (
4983 x_party_header_tbl (i).attribute11,
4984 NULL, l_miss_char,
4985 NULL
4986 ),
4987 csi_intf_party_acct_rec.account2_attribute11
4988 ),
4989 DECODE (
4990 csi_intf_party_acct_rec.account2_attribute12,
4991 x_party_account_header_tbl (i).attribute12, l_miss_char,
4992 NULL, DECODE (
4993 x_party_header_tbl (i).attribute12,
4994 NULL, l_miss_char,
4995 NULL
4996 ),
4997 csi_intf_party_acct_rec.account2_attribute12
4998 ),
4999 DECODE (
5000 csi_intf_party_acct_rec.account2_attribute13,
5001 x_party_account_header_tbl (i).attribute13, l_miss_char,
5002 NULL, DECODE (
5003 x_party_header_tbl (i).attribute13,
5004 NULL, l_miss_char,
5005 NULL
5006 ),
5007 csi_intf_party_acct_rec.account2_attribute13
5008 ),
5009 DECODE (
5010 csi_intf_party_acct_rec.account2_attribute14,
5011 x_party_account_header_tbl (i).attribute14, l_miss_char,
5012 NULL, DECODE (
5013 x_party_header_tbl (i).attribute14,
5014 NULL, l_miss_char,
5015 NULL
5016 ),
5017 csi_intf_party_acct_rec.account2_attribute14
5018 ),
5019 DECODE (
5020 csi_intf_party_acct_rec.account2_attribute14,
5021 x_party_account_header_tbl (i).attribute14, l_miss_char,
5022 NULL, DECODE (
5023 x_party_header_tbl (i).attribute15,
5024 NULL, l_miss_char,
5025 NULL
5026 ),
5027 csi_intf_party_acct_rec.account2_attribute15
5028 )
5029 INTO x_account_tbl (l_party_account_index).relationship_type_code,
5030 x_account_tbl (l_party_account_index).party_account_id,
5031 x_account_tbl (l_party_account_index).bill_to_address,
5032 x_account_tbl (l_party_account_index).ship_to_address,
5033 x_account_tbl (l_party_account_index).CONTEXT,
5034 x_account_tbl (l_party_account_index).attribute1,
5035 x_account_tbl (l_party_account_index).attribute2,
5036 x_account_tbl (l_party_account_index).attribute3,
5037 x_account_tbl (l_party_account_index).attribute4,
5038 x_account_tbl (l_party_account_index).attribute5,
5039 x_account_tbl (l_party_account_index).attribute6,
5040 x_account_tbl (l_party_account_index).attribute7,
5041 x_account_tbl (l_party_account_index).attribute8,
5042 x_account_tbl (l_party_account_index).attribute9,
5043 x_account_tbl (l_party_account_index).attribute10,
5044 x_account_tbl (l_party_account_index).attribute11,
5045 x_account_tbl (l_party_account_index).attribute12,
5046 x_account_tbl (l_party_account_index).attribute13,
5047 x_account_tbl (l_party_account_index).attribute14,
5048 x_account_tbl (l_party_account_index).attribute15
5049 FROM DUAL;
5050
5051 x_party_account_header_tbl.DELETE (i);
5052 ELSE
5053 x_party_account_header_tbl.DELETE (i);
5054 END IF;
5055 END IF;
5056 END LOOP;
5057 END LOOP;
5058 END IF;
5059
5060 IF x_party_account_header_tbl.COUNT >= 1
5061 THEN
5062 FOR i IN
5063 x_party_account_header_tbl.FIRST .. x_party_account_header_tbl.LAST
5064 LOOP
5065 FOR csi_intf_party_acct_rec IN
5066 csi_intf_party_acct_cur (
5067 csi_intf_repl_rec.inst_interface_id
5068 )
5069 LOOP
5070 IF x_party_account_header_tbl.EXISTS (i)
5071 AND csi_intf_party_acct_rec.ip_account3_id IS NOT NULL
5072 AND x_party_account_header_tbl (i).ip_account_id =
5073 csi_intf_party_acct_rec.ip_account3_id
5074 THEN
5075 IF NOT ( NVL (
5076 csi_intf_party_acct_rec.party_account3_id,
5077 l_miss_num
5078 ) =
5079 NVL (
5080 x_party_account_header_tbl (i).party_account_id,
5081 l_miss_num
5082 )
5083 AND NVL (
5084 csi_intf_party_acct_rec.instance_party_id,
5085 l_miss_num
5086 ) =
5087 NVL (
5088 x_party_account_header_tbl (i).instance_party_id,
5089 l_miss_num
5090 )
5091 AND NVL (
5092 csi_intf_party_acct_rec.acct3_relationship_type_code,
5093 l_miss_char
5094 ) =
5095 NVL (
5096 x_party_account_header_tbl (i).relationship_type_code,
5097 l_miss_char
5098 )
5099 AND NVL (
5100 csi_intf_party_acct_rec.bill_to_address1,
5101 l_miss_num
5102 ) =
5103 NVL (
5104 x_party_account_header_tbl (i).bill_to_address,
5105 l_miss_num
5106 )
5107 AND NVL (
5108 csi_intf_party_acct_rec.ship_to_address1,
5109 l_miss_num
5110 ) =
5111 NVL (
5112 x_party_account_header_tbl (i).ship_to_address,
5113 l_miss_num
5114 )
5115 AND NVL (
5116 csi_intf_party_acct_rec.account3_context,
5117 l_miss_char
5118 ) =
5119 NVL (
5120 x_party_account_header_tbl (i).CONTEXT,
5121 l_miss_char
5122 )
5123 AND NVL (
5124 csi_intf_party_acct_rec.account3_attribute1,
5125 l_miss_char
5126 ) =
5127 NVL (
5128 x_party_account_header_tbl (i).attribute1,
5129 l_miss_char
5130 )
5131 AND NVL (
5132 csi_intf_party_acct_rec.account3_attribute2,
5133 l_miss_char
5134 ) =
5135 NVL (
5136 x_party_account_header_tbl (i).attribute2,
5137 l_miss_char
5138 )
5139 AND NVL (
5140 csi_intf_party_acct_rec.account3_attribute3,
5141 l_miss_char
5142 ) =
5143 NVL (
5144 x_party_account_header_tbl (i).attribute3,
5145 l_miss_char
5146 )
5147 AND NVL (
5148 csi_intf_party_acct_rec.account3_attribute4,
5149 l_miss_char
5150 ) =
5151 NVL (
5152 x_party_account_header_tbl (i).attribute4,
5153 l_miss_char
5154 )
5155 AND NVL (
5156 csi_intf_party_acct_rec.account3_attribute5,
5157 l_miss_char
5158 ) =
5159 NVL (
5160 x_party_account_header_tbl (i).attribute5,
5161 l_miss_char
5162 )
5163 AND NVL (
5164 csi_intf_party_acct_rec.account3_attribute6,
5165 l_miss_char
5166 ) =
5167 NVL (
5168 x_party_account_header_tbl (i).attribute6,
5169 l_miss_char
5170 )
5171 AND NVL (
5172 csi_intf_party_acct_rec.account3_attribute7,
5173 l_miss_char
5174 ) =
5175 NVL (
5176 x_party_account_header_tbl (i).attribute7,
5177 l_miss_char
5178 )
5179 AND NVL (
5180 csi_intf_party_acct_rec.account3_attribute8,
5181 l_miss_char
5182 ) =
5183 NVL (
5184 x_party_account_header_tbl (i).attribute8,
5185 l_miss_char
5186 )
5187 AND NVL (
5188 csi_intf_party_acct_rec.account3_attribute9,
5189 l_miss_char
5190 ) =
5191 NVL (
5192 x_party_account_header_tbl (i).attribute9,
5193 l_miss_char
5194 )
5195 AND NVL (
5196 csi_intf_party_acct_rec.account3_attribute10,
5197 l_miss_char
5198 ) =
5199 NVL (
5200 x_party_account_header_tbl (i).attribute10,
5201 l_miss_char
5202 )
5203 AND NVL (
5204 csi_intf_party_acct_rec.account3_attribute11,
5205 l_miss_char
5206 ) =
5207 NVL (
5208 x_party_account_header_tbl (i).attribute11,
5209 l_miss_char
5210 )
5211 AND NVL (
5212 csi_intf_party_acct_rec.account3_attribute12,
5213 l_miss_char
5214 ) =
5215 NVL (
5216 x_party_account_header_tbl (i).attribute12,
5217 l_miss_char
5218 )
5219 AND NVL (
5220 csi_intf_party_acct_rec.account3_attribute13,
5221 l_miss_char
5222 ) =
5223 NVL (
5224 x_party_account_header_tbl (i).attribute13,
5225 l_miss_char
5226 )
5227 AND NVL (
5228 csi_intf_party_acct_rec.account3_attribute14,
5229 l_miss_char
5230 ) =
5231 NVL (
5232 x_party_account_header_tbl (i).attribute14,
5233 l_miss_char
5234 )
5235 AND NVL (
5236 csi_intf_party_acct_rec.account3_attribute15,
5237 l_miss_char
5238 ) =
5239 NVL (
5240 x_party_account_header_tbl (i).attribute15,
5241 l_miss_char
5242 )
5243 )
5244 THEN
5245 l_party_account_index := x_account_tbl.COUNT
5246 + 1;
5247 x_account_tbl (l_party_account_index).ip_account_id :=
5248 x_party_account_header_tbl (i).ip_account_id;
5249 x_account_tbl (l_party_account_index).instance_party_id :=
5250 x_party_account_header_tbl (i).instance_party_id;
5251 x_account_tbl (l_party_account_index).object_version_number :=
5252 x_party_account_header_tbl (i).object_version_number;
5253 x_account_tbl (l_party_account_index).parent_tbl_index :=
5254 get_parent_tbl_index (
5255 x_account_tbl (l_party_account_index).instance_party_id,
5256 x_account_tbl (l_party_account_index).relationship_type_code
5257 );
5258
5259 IF x_account_tbl (l_party_account_index).parent_tbl_index IS NULL
5260 THEN
5261 l_party_index := x_party_tbl.COUNT
5262 + 1;
5263 x_party_tbl (l_party_index).instance_party_id :=
5264 x_account_tbl (l_party_account_index).instance_party_id;
5265 x_party_tbl (l_party_index).relationship_type_code :=
5266 x_account_tbl (l_party_account_index).relationship_type_code;
5267 x_party_tbl (l_party_index).object_version_number :=
5268 get_obj_ver_num (
5269 x_party_cache_tbl,
5270 x_account_tbl (i).instance_party_id
5271 );
5272 END IF;
5273
5274 SELECT DECODE (
5275 csi_intf_party_acct_rec.acct3_relationship_type_code,
5276 x_party_account_header_tbl (i).relationship_type_code, l_miss_char,
5277 NULL, DECODE (
5278 x_party_header_tbl (i).relationship_type_code,
5279 NULL, l_miss_char,
5280 NULL
5281 ),
5282 csi_intf_party_acct_rec.acct3_relationship_type_code
5283 ),
5284 DECODE (
5285 csi_intf_party_acct_rec.party_account3_id,
5286 x_party_account_header_tbl (i).party_account_id, l_miss_num,
5287 NULL, DECODE (
5288 x_party_account_header_tbl (i).party_account_id,
5289 NULL, l_miss_num,
5290 NULL
5291 ),
5292 csi_intf_party_acct_rec.party_account3_id
5293 ),
5294 DECODE (
5295 csi_intf_party_acct_rec.bill_to_address3,
5296 x_party_account_header_tbl (i).bill_to_address, l_miss_num,
5297 NULL, DECODE (
5298 x_party_account_header_tbl (i).bill_to_address,
5299 NULL, l_miss_num,
5300 NULL
5301 ),
5302 csi_intf_party_acct_rec.bill_to_address3
5303 ),
5304 DECODE (
5305 csi_intf_party_acct_rec.ship_to_address3,
5306 x_party_account_header_tbl (i).ship_to_address, l_miss_num,
5307 NULL, DECODE (
5308 x_party_account_header_tbl (i).ship_to_address,
5309 NULL, l_miss_num,
5310 NULL
5311 ),
5312 csi_intf_party_acct_rec.ship_to_address3
5313 ),
5314 DECODE (
5315 csi_intf_party_acct_rec.account3_context,
5316 x_party_account_header_tbl (i).CONTEXT, l_miss_char,
5317 NULL, DECODE (
5318 x_party_account_header_tbl (i).CONTEXT,
5319 NULL, l_miss_char,
5320 NULL
5321 ),
5322 csi_intf_party_acct_rec.account3_context
5323 ),
5324 DECODE (
5325 csi_intf_party_acct_rec.account3_attribute1,
5326 x_party_account_header_tbl (i).attribute1, l_miss_char,
5327 NULL, DECODE (
5328 x_party_account_header_tbl (i).attribute1,
5329 NULL, l_miss_char,
5330 NULL
5331 ),
5332 csi_intf_party_acct_rec.account3_attribute1
5333 ),
5334 DECODE (
5335 csi_intf_party_acct_rec.account3_attribute2,
5336 x_party_account_header_tbl (i).attribute2, l_miss_char,
5337 NULL, DECODE (
5338 x_party_account_header_tbl (i).attribute2,
5339 NULL, l_miss_char,
5340 NULL
5341 ),
5342 csi_intf_party_acct_rec.account3_attribute2
5343 ),
5344 DECODE (
5345 csi_intf_party_acct_rec.account3_attribute3,
5346 x_party_account_header_tbl (i).attribute3, l_miss_char,
5347 NULL, DECODE (
5348 x_party_account_header_tbl (i).attribute3,
5349 NULL, l_miss_char,
5350 NULL
5351 ),
5352 csi_intf_party_acct_rec.account3_attribute3
5353 ),
5354 DECODE (
5355 csi_intf_party_acct_rec.account3_attribute4,
5356 x_party_account_header_tbl (i).attribute4, l_miss_char,
5357 NULL, DECODE (
5358 x_party_account_header_tbl (i).attribute4,
5359 NULL, l_miss_char,
5360 NULL
5361 ),
5362 csi_intf_party_acct_rec.account3_attribute4
5363 ),
5364 DECODE (
5365 csi_intf_party_acct_rec.account3_attribute5,
5366 x_party_account_header_tbl (i).attribute5, l_miss_char,
5367 NULL, DECODE (
5368 x_party_account_header_tbl (i).attribute5,
5369 NULL, l_miss_char,
5370 NULL
5371 ),
5372 csi_intf_party_acct_rec.account3_attribute5
5373 ),
5374 DECODE (
5375 csi_intf_party_acct_rec.account3_attribute6,
5376 x_party_account_header_tbl (i).attribute6, l_miss_char,
5377 NULL, DECODE (
5378 x_party_account_header_tbl (i).attribute6,
5379 NULL, l_miss_char,
5380 NULL
5381 ),
5382 csi_intf_party_acct_rec.account3_attribute6
5383 ),
5384 DECODE (
5385 csi_intf_party_acct_rec.account3_attribute7,
5386 x_party_account_header_tbl (i).attribute7, l_miss_char,
5387 NULL, DECODE (
5388 x_party_account_header_tbl (i).attribute7,
5389 NULL, l_miss_char,
5390 NULL
5391 ),
5392 csi_intf_party_acct_rec.account3_attribute7
5393 ),
5394 DECODE (
5395 csi_intf_party_acct_rec.account3_attribute8,
5396 x_party_account_header_tbl (i).attribute8, l_miss_char,
5397 NULL, DECODE (
5398 x_party_account_header_tbl (i).attribute8,
5399 NULL, l_miss_char,
5400 NULL
5401 ),
5402 csi_intf_party_acct_rec.account3_attribute8
5403 ),
5404 DECODE (
5405 csi_intf_party_acct_rec.account3_attribute9,
5406 x_party_account_header_tbl (i).attribute9, l_miss_char,
5407 NULL, DECODE (
5408 x_party_account_header_tbl (i).attribute9,
5409 NULL, l_miss_char,
5410 NULL
5411 ),
5412 csi_intf_party_acct_rec.account3_attribute9
5413 ),
5414 DECODE (
5415 csi_intf_party_acct_rec.account3_attribute10,
5416 x_party_account_header_tbl (i).attribute10, l_miss_char,
5417 NULL, DECODE (
5418 x_party_account_header_tbl (i).attribute10,
5419 NULL, l_miss_char,
5420 NULL
5421 ),
5422 csi_intf_party_acct_rec.account3_attribute10
5423 ),
5424 DECODE (
5425 csi_intf_party_acct_rec.account3_attribute11,
5426 x_party_account_header_tbl (i).attribute11, l_miss_char,
5427 NULL, DECODE (
5428 x_party_account_header_tbl (i).attribute11,
5429 NULL, l_miss_char,
5430 NULL
5431 ),
5432 csi_intf_party_acct_rec.account3_attribute11
5433 ),
5434 DECODE (
5435 csi_intf_party_acct_rec.account3_attribute12,
5436 x_party_account_header_tbl (i).attribute12, l_miss_char,
5437 NULL, DECODE (
5438 x_party_account_header_tbl (i).attribute12,
5439 NULL, l_miss_char,
5440 NULL
5441 ),
5442 csi_intf_party_acct_rec.account3_attribute12
5443 ),
5444 DECODE (
5445 csi_intf_party_acct_rec.account3_attribute13,
5446 x_party_account_header_tbl (i).attribute13, l_miss_char,
5447 NULL, DECODE (
5448 x_party_account_header_tbl (i).attribute13,
5449 NULL, l_miss_char,
5450 NULL
5451 ),
5452 csi_intf_party_acct_rec.account3_attribute13
5453 ),
5454 DECODE (
5455 csi_intf_party_acct_rec.account3_attribute14,
5456 x_party_account_header_tbl (i).attribute14, l_miss_char,
5457 NULL, DECODE (
5458 x_party_account_header_tbl (i).attribute14,
5459 NULL, l_miss_char,
5460 NULL
5461 ),
5462 csi_intf_party_acct_rec.account3_attribute14
5463 ),
5464 DECODE (
5465 csi_intf_party_acct_rec.account3_attribute14,
5466 x_party_account_header_tbl (i).attribute14, l_miss_char,
5467 NULL, DECODE (
5468 x_party_account_header_tbl (i).attribute15,
5469 NULL, l_miss_char,
5470 NULL
5471 ),
5472 csi_intf_party_acct_rec.account3_attribute15
5473 )
5474 INTO x_account_tbl (l_party_account_index).relationship_type_code,
5475 x_account_tbl (l_party_account_index).party_account_id,
5476 x_account_tbl (l_party_account_index).bill_to_address,
5477 x_account_tbl (l_party_account_index).ship_to_address,
5478 x_account_tbl (l_party_account_index).CONTEXT,
5479 x_account_tbl (l_party_account_index).attribute1,
5480 x_account_tbl (l_party_account_index).attribute2,
5481 x_account_tbl (l_party_account_index).attribute3,
5482 x_account_tbl (l_party_account_index).attribute4,
5483 x_account_tbl (l_party_account_index).attribute5,
5484 x_account_tbl (l_party_account_index).attribute6,
5485 x_account_tbl (l_party_account_index).attribute7,
5486 x_account_tbl (l_party_account_index).attribute8,
5487 x_account_tbl (l_party_account_index).attribute9,
5488 x_account_tbl (l_party_account_index).attribute10,
5489 x_account_tbl (l_party_account_index).attribute11,
5490 x_account_tbl (l_party_account_index).attribute12,
5491 x_account_tbl (l_party_account_index).attribute13,
5492 x_account_tbl (l_party_account_index).attribute14,
5493 x_account_tbl (l_party_account_index).attribute15
5494 FROM DUAL;
5495
5496 x_party_account_header_tbl.DELETE (i);
5497 ELSE
5498 x_party_account_header_tbl.DELETE (i);
5499 END IF;
5500 END IF;
5501 END LOOP;
5502 END LOOP;
5503 END IF;
5504
5505 IF x_party_account_header_tbl.COUNT >= 1
5506 THEN
5507 FOR i IN
5508 x_party_account_header_tbl.FIRST .. x_party_account_header_tbl.LAST
5509 LOOP
5510 IF x_party_account_header_tbl.EXISTS (i)
5511 AND x_party_account_header_tbl (i).instance_party_id IS NOT NULL
5512 THEN
5513 l_party_account_index := x_account_tbl.COUNT
5514 + 1;
5515 x_account_tbl (l_party_account_index).instance_party_id :=
5516 x_party_account_header_tbl (i).instance_party_id;
5517 x_account_tbl (l_party_account_index).object_version_number :=
5518 x_party_account_header_tbl (i).object_version_number;
5519 x_account_tbl (l_party_account_index).active_end_date :=
5520 l_end_date;
5521 x_party_account_header_tbl.DELETE (i);
5522 END IF;
5523 END LOOP;
5524 END IF;
5525
5526
5527 --dbms_output.put_line('After party accounts count:'||x_account_tbl.COUNT);
5528 IF x_ext_attrib_value_tbl.COUNT >= 1
5529 THEN
5530 FOR i IN
5531 x_ext_attrib_value_tbl.FIRST .. x_ext_attrib_value_tbl.LAST
5532 LOOP
5533 IF x_ext_attrib_value_tbl (i).active_end_date IS NOT NULL
5534 THEN
5535 x_ext_attrib_value_tbl.DELETE (i);
5536 END IF;
5537 END LOOP;
5538 END IF;
5539
5540 IF x_ext_attrib_value_tbl.COUNT >= 1
5541 THEN
5542 FOR i IN
5543 x_ext_attrib_value_tbl.FIRST .. x_ext_attrib_value_tbl.LAST
5544 LOOP
5545 FOR csi_intf_ext_attrib_rec IN
5546 csi_intf_ext_attrib_cur (
5547 csi_intf_repl_rec.inst_interface_id
5548 )
5549 LOOP
5550 IF x_ext_attrib_value_tbl (i).attribute_value_id =
5551 csi_intf_ext_attrib_rec.attribute_value_id
5552 THEN
5553 IF NOT ( NVL (
5554 csi_intf_ext_attrib_rec.attribute_id,
5555 l_miss_num
5556 ) =
5557 NVL (
5558 x_ext_attrib_value_tbl (i).attribute_id,
5559 l_miss_num
5560 )
5561 AND NVL (
5562 csi_intf_ext_attrib_rec.attribute_value,
5563 l_miss_char
5564 ) =
5565 NVL (
5566 x_ext_attrib_value_tbl (i).attribute_value,
5567 l_miss_char
5568 )
5569 )
5570 THEN
5571 l_ieav_index :=
5572 NVL ( x_eav_tbl.COUNT
5573 + 1, x_eav_tbl.FIRST);
5574 x_eav_tbl (l_ieav_index).attribute_value_id :=
5575 x_ext_attrib_value_tbl (i).attribute_value_id;
5576 x_eav_tbl (l_ieav_index).instance_id :=
5577 r_instance_id;
5578 x_eav_tbl (l_ieav_index).object_version_number :=
5579 x_ext_attrib_value_tbl (i).object_version_number;
5580
5581 SELECT DECODE (
5582 csi_intf_ext_attrib_rec.attribute_value,
5583 x_ext_attrib_value_tbl (i).attribute_value, l_miss_char,
5584 NULL, DECODE (
5585 x_ext_attrib_value_tbl (i).attribute_value,
5586 NULL, l_miss_char,
5587 NULL
5588 ),
5589 csi_intf_ext_attrib_rec.attribute_value
5590 )
5591 INTO x_eav_tbl (l_ieav_index).attribute_value
5592 FROM DUAL;
5593
5594 x_ext_attrib_value_tbl.DELETE (i);
5595 ELSE
5596 x_ext_attrib_value_tbl.DELETE (i);
5597 END IF;
5598 END IF;
5599 END LOOP;
5600 END LOOP;
5601 END IF;
5602
5603 IF x_ext_attrib_value_tbl.COUNT >= 1
5604 THEN
5605 FOR i IN
5606 x_ext_attrib_value_tbl.FIRST .. x_ext_attrib_value_tbl.LAST
5607 LOOP
5608 IF x_ext_attrib_value_tbl.EXISTS (i)
5609 THEN
5610 l_ieav_index := x_eav_tbl.COUNT + 1;
5611 x_eav_tbl (l_ieav_index).attribute_value_id :=
5612 x_ext_attrib_value_tbl (i).attribute_value_id;
5613 x_eav_tbl (l_ieav_index).object_version_number :=
5614 x_ext_attrib_value_tbl (i).object_version_number;
5615 x_eav_tbl (l_ieav_index).active_end_date := l_end_date;
5616 x_ext_attrib_value_tbl.DELETE (i);
5617 END IF;
5618 END LOOP;
5619 END IF;
5620
5621 FOR csi_intf_ext_attrib_rec IN
5622 csi_intf_ext_attrib_cur (csi_intf_repl_rec.inst_interface_id)
5623 LOOP
5624 IF csi_intf_ext_attrib_rec.attribute_value_id IS NULL
5625 THEN
5626 l_ieav_index := x_eav_tbl.COUNT + 1;
5627 x_eav_tbl (l_ieav_index).attribute_id :=
5628 csi_intf_ext_attrib_rec.attribute_id;
5629 x_eav_tbl (l_ieav_index).attribute_value :=
5630 csi_intf_ext_attrib_rec.attribute_value;
5631 x_eav_tbl (l_ieav_index).instance_id :=
5632 r_instance_id;
5633 x_eav_tbl (l_ieav_index).active_start_date :=
5634 csi_intf_ext_attrib_rec.ieav_start_date;
5635 END IF;
5636 END LOOP;
5637 EXCEPTION
5638 WHEN e_error
5639 THEN
5640 ROLLBACK TO instance_entity;
5641 --dbms_output.put_line('in e_Error:'||substr(l_msg_data,1,200));
5642 x_error_message := fnd_message.get;
5643 x_return_status := fnd_api.g_ret_sts_error;
5644 WHEN OTHERS
5645 THEN
5646 l_sql_error := SQLERRM;
5647 fnd_message.set_name ('CSI', 'CSI_ML_UNEXP_SQL_ERROR');
5648 fnd_message.set_token ('API_NAME', l_api_name);
5649 fnd_message.set_token ('SQL_ERROR', SQLERRM);
5650 x_error_message := fnd_message.get;
5651 x_return_status := fnd_api.g_ret_sts_unexp_error;
5652 END;
5653 END LOOP; --csi_intf_repl_cur
5654
5655 END process_replace;
5656 END csi_ml_replace_pvt;
|
|
|
|