[Home] [Help]
PACKAGE BODY: APPS.CSI_ML_UPDATE_PVT
Source
1 PACKAGE BODY CSI_ML_UPDATE_PVT AS
2 -- $Header: csimupdb.pls 120.7 2007/11/27 02:30:22 anjgupta ship $
3
4 g_action_replace CONSTANT VARCHAR2 (30) := 'R';
5 g_procstat_valid CONSTANT VARCHAR2 (30) := 'R';
6 g_procstat_processed CONSTANT VARCHAR2 (30) := 'P';
7 g_procstat_error CONSTANT VARCHAR2 (30) := 'E';
8 l_miss_char CONSTANT VARCHAR2 (1) := fnd_api.g_miss_char;
9 l_miss_num CONSTANT NUMBER := fnd_api.g_miss_num;
10 l_miss_date CONSTANT DATE := fnd_api.g_miss_date;
11
12 PROCEDURE populate_recs (
13 p_txn_identifier IN VARCHAR2,
14 p_source_system_name IN VARCHAR2,
15 x_instance_tbl OUT NOCOPY csi_datastructures_pub.instance_tbl,
16 x_party_tbl OUT NOCOPY csi_datastructures_pub.party_tbl,
17 x_account_tbl OUT NOCOPY csi_datastructures_pub.party_account_tbl,
18 x_ext_attrib_value_tbl OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl,
19 x_price_tbl OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl,
20 x_org_assign_tbl OUT NOCOPY csi_datastructures_pub.organization_units_tbl,
21 x_asset_assignment_tbl OUT NOCOPY csi_datastructures_pub.instance_asset_tbl,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_error_message OUT NOCOPY VARCHAR2
24 )
25 IS
26 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CSI_DEBUG_LEVEL'), '0'));
27 CURSOR inst_intf_cur (p_txn_id IN VARCHAR2,
28 p_source_name IN VARCHAR2)
29 IS
30 SELECT cii.*
31 FROM csi_instance_interface cii
32 WHERE cii.transaction_identifier = p_txn_id
33 AND cii.source_system_name = p_source_name
34 AND cii.instance_id IS NOT NULL --Added for open
35 AND cii.process_status='R'; -- added for bug 3260033
36
37
38 CURSOR party_intf_cur (p_id IN NUMBER)
39 IS
40 SELECT cip.*
41 FROM csi_i_party_interface cip
42 WHERE cip.inst_interface_id = p_id;
43
44 CURSOR item_obj_ver_cur (p_instance_id IN NUMBER)
45 IS
46 SELECT object_version_number,quantity,last_update_date
47 FROM csi_item_instances
48 WHERE instance_id = p_instance_id;
49
50 CURSOR ou_obj_ver_cur (p_instance_id IN NUMBER)
51 IS
52 SELECT object_version_number,last_update_date
53 FROM csi_i_org_assignments
54 WHERE instance_id = p_instance_id;
55
56 CURSOR price_obj_ver_cur (p_instance_id IN NUMBER)
57 IS
58 SELECT object_version_number,last_update_date
59 FROM csi_i_pricing_attribs
60 WHERE instance_id = p_instance_id;
61
62 CURSOR ip_obj_ver_cur (p_ip_id IN NUMBER)
63 IS
64 SELECT object_version_number,last_update_date
65 FROM csi_i_parties
66 WHERE instance_party_id = p_ip_id;
67
68 CURSOR ipa_obj_ver_cur (p_ip_account_id IN NUMBER)
69 IS
70 SELECT object_version_number,last_update_date
71 FROM csi_ip_accounts
72 WHERE ip_account_id = p_ip_account_id;
73
74 --bnarayan added for R12
75 CURSOR asset_attrib_intf_cur (p_id IN NUMBER, p_instance_id IN NUMBER)
76 IS
77 SELECT csiai.*,csia.instance_asset_id csia_instance_asset_id, nvl(csia.object_version_number,1) asset_object_ver_num
78 FROM csi_i_asset_interface csiai, csi_i_assets csia
79 WHERE csiai.inst_interface_id = p_id
80 AND csia.instance_id(+) = p_instance_id
81 AND csia.fa_asset_id(+) = csiai.fa_asset_id
82 AND csia.fa_location_id(+) = csiai.fa_location_id ;
83
84
85 CURSOR ext_attrib_intf_cur (p_id IN NUMBER)
86 IS
87 SELECT ceai.*, a.object_version_number ieav_object_ver_num
88 FROM csi_iea_value_interface ceai, csi_iea_values a
89 WHERE ceai.inst_interface_id= p_id
90 AND ceai.attribute_value_id = a.attribute_value_id(+);
91
92 l_old_quantity NUMBER;
93 l_item_object_version NUMBER;
94 l_ou_object_version NUMBER;
95 l_price_object_version NUMBER;
96 l_party_object_version NUMBER;
97 l_ipa1_object_version NUMBER;
98 l_ipa2_object_version NUMBER;
99 l_ipa3_object_version NUMBER;
100 l_last_update_date DATE;
101 l_error_message VARCHAR2(250);
102 inst_idx PLS_INTEGER := 0;
103 prty_idx PLS_INTEGER := 0;
104 ptyacc_idx PLS_INTEGER := 0;
105 extatt_idx PLS_INTEGER := 0;
106 org_idx PLS_INTEGER := 0;
107 price_idx PLS_INTEGER := 0;
108 asset_idx PLS_INTEGER := 0; -- Asset index
109 e_restriction EXCEPTION;
110 BEGIN
111 x_return_status := fnd_api.g_ret_sts_success;
112 x_error_message := NULL;
113
114 FOR inst_intf_rec IN inst_intf_cur (p_txn_identifier,
115 p_source_system_name)
116 LOOP
117 -- Start code addition for bug 6368180, section 1 of 2
118 IF inst_intf_rec.instance_id <> fnd_api.g_miss_num
119 THEN
120 -- We populate the record only if it is not for creating a new instance
121 -- End code addition for bug 6368180, section 1 of 2
122 inst_idx := inst_idx + 1;
123 org_idx := org_idx + 1 ;
124 price_idx := price_idx + 1;
125 asset_idx := asset_idx + 1; --bnarayan added for R12
126 x_instance_tbl(inst_idx).instance_id := inst_intf_rec.instance_id;
127 OPEN item_obj_ver_cur (inst_intf_rec.instance_id);
128 FETCH item_obj_ver_cur INTO l_item_object_version,
129 l_old_quantity,
130 l_last_update_date;
131 CLOSE item_obj_ver_cur;
132
133 IF l_last_update_date > inst_intf_rec.source_transaction_date
134 THEN
135 IF(l_debug_level>1) THEN
136 FND_File.Put_Line(Fnd_File.LOG,'Value of l_last_update_date='||to_char(l_last_update_date,'dd-mon-yy hh24:mi:ss'));
137 FND_File.Put_Line(Fnd_File.LOG,'Value of inst_intf_rec.source_transaction_date='||to_char(inst_intf_rec.source_transaction_date,'dd-mon-yy hh24:mi:ss'));
138 end if;
139 fnd_message.set_name('CSI','CSI_INTERFACE_RESTRICTION');
140 fnd_message.set_token('INSTANCE_NUMBER',inst_intf_rec.instance_id);
141 fnd_message.set_token('SOURCE_DATE',inst_intf_rec.source_transaction_date);
142 l_error_message :=substr(fnd_message.get,1,208);
143 RAISE e_restriction;
144 END IF;
145
146 OPEN ou_obj_ver_cur (inst_intf_rec.instance_id);
147 FETCH ou_obj_ver_cur INTO l_ou_object_version,
148 l_last_update_date;
149 CLOSE ou_obj_ver_cur;
150 IF l_last_update_date > inst_intf_rec.source_transaction_date
151 THEN
152 fnd_message.set_name('CSI','CSI_INTERFACE_RESTRICTION');
153 fnd_message.set_token('instance_id',inst_intf_rec.instance_id);
154 l_error_message :=substr(fnd_message.get,1,208);
155 RAISE e_restriction;
156 END IF;
157 OPEN price_obj_ver_cur (inst_intf_rec.instance_id);
158 FETCH price_obj_ver_cur INTO l_price_object_version,
159 l_last_update_date;
160 CLOSE price_obj_ver_cur;
161 IF l_last_update_date > inst_intf_rec.source_transaction_date
162 THEN
163 fnd_message.set_name('CSI','CSI_INTERFACE_RESTRICTION');
164 fnd_message.set_token('instance_id',inst_intf_rec.instance_id);
165 l_error_message :=substr(fnd_message.get,1,208);
166 RAISE e_restriction;
167 END IF;
168
169 SELECT l_miss_num /* DECODE (
170 inst_intf_rec.inv_vld_organization_id,
171 NULL, l_miss_num,
172 l_miss_num, NULL,
173 inst_intf_rec.inv_vld_organization_id
174 ) */ -- Code commented for bug 3347509
175 ,
176 DECODE (
177 inst_intf_rec.inv_vld_organization_id,
178 NULL, l_miss_num,
179 l_miss_num, NULL,
180 inst_intf_rec.inv_vld_organization_id
181 ),
182 DECODE (
183 inst_intf_rec.inventory_item_id,
184 NULL, l_miss_num,
185 l_miss_num, NULL,
186 inst_intf_rec.inventory_item_id
187 ),
188 DECODE (
189 inst_intf_rec.location_type_code,
190 NULL, l_miss_char,
191 l_miss_char, NULL,
192 inst_intf_rec.location_type_code
193 ),
194 DECODE (
195 inst_intf_rec.location_id,
196 NULL, l_miss_num,
197 l_miss_num, NULL,
198 inst_intf_rec.location_id
199 ),
200 DECODE (
201 inst_intf_rec.inv_organization_id,
202 NULL, l_miss_num,
203 l_miss_num, NULL,
204 inst_intf_rec.inv_organization_id
205 ),
206 DECODE (
207 inst_intf_rec.inv_subinventory_name,
208 NULL, l_miss_char,
209 l_miss_char, NULL,
210 inst_intf_rec.inv_subinventory_name
211 ),
212 DECODE (
213 inst_intf_rec.inv_locator_id,
214 NULL, l_miss_num,
215 l_miss_num, NULL,
216 inst_intf_rec.inv_locator_id
217 ),
218 DECODE (
219 inst_intf_rec.lot_number,
220 NULL, l_miss_char,
221 l_miss_char, NULL,
222 inst_intf_rec.lot_number
223 ),
224 DECODE (
225 inst_intf_rec.project_id,
226 NULL, l_miss_num,
227 l_miss_num, NULL,
228 inst_intf_rec.project_id
229 ),
230 DECODE (
231 inst_intf_rec.task_id,
232 NULL, l_miss_num,
233 l_miss_num, NULL,
234 inst_intf_rec.task_id
235 ),
236 DECODE (
237 inst_intf_rec.in_transit_order_line_id,
238 NULL, l_miss_num,
239 l_miss_num, NULL,
240 inst_intf_rec.in_transit_order_line_id
241 ),
242 DECODE (
243 inst_intf_rec.wip_job_id,
244 NULL, l_miss_num,
245 l_miss_num, NULL,
246 inst_intf_rec.wip_job_id
247 ),
248 DECODE (
249 inst_intf_rec.po_order_line_id,
250 NULL, l_miss_num,
251 l_miss_num, NULL,
252 inst_intf_rec.po_order_line_id
253 ),
254 DECODE (
255 inst_intf_rec.inventory_revision,
256 NULL, l_miss_char,
257 l_miss_char, NULL,
258 inst_intf_rec.inventory_revision
259 ),
260 DECODE (
261 inst_intf_rec.serial_number,
262 NULL, l_miss_char,
263 l_miss_char, NULL,
264 inst_intf_rec.serial_number
265 ),
266 DECODE (
267 inst_intf_rec.mfg_serial_number_flag,
268 NULL, l_miss_char,
269 l_miss_char, NULL,
270 inst_intf_rec.mfg_serial_number_flag
271 ),
272 DECODE (
273 inst_intf_rec.quantity,
274 NULL, l_miss_num,
275 l_miss_num, NULL,inst_intf_rec.quantity
276 -- inst_intf_rec.quantity+l_old_quantity
277 ),
278 DECODE (
279 inst_intf_rec.unit_of_measure_code,
280 NULL, l_miss_char,
281 l_miss_char, NULL,
282 inst_intf_rec.unit_of_measure_code
283 ),
284 DECODE (
285 inst_intf_rec.accounting_class_code,
286 NULL, l_miss_char,
287 l_miss_char, NULL,
288 inst_intf_rec.accounting_class_code
289 ),
290 DECODE (
291 inst_intf_rec.instance_condition_id,
292 NULL, l_miss_num,
293 l_miss_num, NULL,
294 inst_intf_rec.instance_condition_id
295 ),
296 DECODE (
297 inst_intf_rec.instance_status_id,
298 NULL, l_miss_num,
299 l_miss_num, NULL,
300 inst_intf_rec.instance_status_id
301 ),
302 DECODE (
303 inst_intf_rec.customer_view_flag,
304 NULL, l_miss_char,
305 l_miss_char, NULL,
306 inst_intf_rec.customer_view_flag
307 ),
308 DECODE (
309 inst_intf_rec.merchant_view_flag,
310 NULL, l_miss_char,
311 l_miss_char, NULL,
312 inst_intf_rec.merchant_view_flag
313 ),
314 DECODE (
315 inst_intf_rec.sellable_flag,
316 NULL, l_miss_char,
317 l_miss_char, NULL,
318 inst_intf_rec.sellable_flag
319 ),
320 DECODE (
321 inst_intf_rec.system_id,
322 NULL, l_miss_num,
323 l_miss_num, NULL,
324 inst_intf_rec.system_id
325 ),
326 DECODE (
327 inst_intf_rec.instance_type_code,
328 NULL, l_miss_char,
329 l_miss_char, NULL,
330 inst_intf_rec.instance_type_code
331 ),
332 DECODE (
333 inst_intf_rec.instance_end_date,
334 NULL, l_miss_date,
335 l_miss_date, NULL,
336 inst_intf_rec.instance_end_date
337 ),
338 l_miss_num, -- LAST_OE_ORDER_LINE_ID
339 l_miss_num, -- LAST_OE_RMA_LINE_ID
340 l_miss_num, -- LAST_PO_PO_LINE_ID
341 l_miss_char, -- LAST_OE_PO_NUMBER
342 l_miss_num, -- LAST_WIP_JOB_ID
343 l_miss_num, -- LAST_PA_PROJECT_ID
344 l_miss_num, -- LAST_PA_TASK_ID
345 l_miss_num, -- LAST_OE_AGREEMENT_ID
346 DECODE (
347 inst_intf_rec.install_date,
348 NULL, l_miss_date,
349 l_miss_date, NULL,
350 inst_intf_rec.install_date
351 ),
352 l_miss_char, -- MANUALLY_CREATED_FLAG
353 DECODE (
354 inst_intf_rec.return_by_date,
355 NULL, l_miss_date,
356 l_miss_date, NULL,
357 inst_intf_rec.return_by_date
358 ),
359 DECODE (
360 inst_intf_rec.actual_return_date,
361 NULL, l_miss_date,
362 l_miss_date, NULL,
363 inst_intf_rec.actual_return_date
364 ),
365 l_miss_char, --CREATION_COMPLETE_FLAG
366 l_miss_char, --COMPLETENESS_FLAG
367 l_miss_char, --VERSION_LABEL
368 l_miss_char, --VERSION_LABEL_DESCRIPTION
369 DECODE (
370 inst_intf_rec.instance_context,
371 NULL, l_miss_char,
372 l_miss_char, NULL,
373 inst_intf_rec.instance_context
374 ),
375 DECODE (
376 inst_intf_rec.instance_attribute1,
377 NULL, l_miss_char,
378 l_miss_char, NULL,
379 inst_intf_rec.instance_attribute1
380 ),
381 DECODE (
382 inst_intf_rec.instance_attribute2,
383 NULL, l_miss_char,
384 l_miss_char, NULL,
385 inst_intf_rec.instance_attribute2
386 ),
387 DECODE (
388 inst_intf_rec.instance_attribute3,
389 NULL, l_miss_char,
390 l_miss_char, NULL,
391 inst_intf_rec.instance_attribute3
392 ),
393 DECODE (
394 inst_intf_rec.instance_attribute4,
395 NULL, l_miss_char,
396 l_miss_char, NULL,
397 inst_intf_rec.instance_attribute4
398 ),
399 DECODE (
400 inst_intf_rec.instance_attribute5,
401 NULL, l_miss_char,
402 l_miss_char, NULL,
403 inst_intf_rec.instance_attribute5
404 ),
405 DECODE (
406 inst_intf_rec.instance_attribute6,
407 NULL, l_miss_char,
408 l_miss_char, NULL,
409 inst_intf_rec.instance_attribute6
410 ),
411 DECODE (
412 inst_intf_rec.instance_attribute7,
413 NULL, l_miss_char,
414 l_miss_char, NULL,
415 inst_intf_rec.instance_attribute7
416 ),
417 DECODE (
418 inst_intf_rec.instance_attribute8,
419 NULL, l_miss_char,
420 l_miss_char, NULL,
421 inst_intf_rec.instance_attribute8
422 ),
423 DECODE (
424 inst_intf_rec.instance_attribute9,
425 NULL, l_miss_char,
426 l_miss_char, NULL,
427 inst_intf_rec.instance_attribute9
428 ),
429 DECODE (
430 inst_intf_rec.instance_attribute10,
431 NULL, l_miss_char,
432 l_miss_char, NULL,
433 inst_intf_rec.instance_attribute10
434 ),
435 DECODE (
436 inst_intf_rec.instance_attribute11,
437 NULL, l_miss_char,
438 l_miss_char, NULL,
439 inst_intf_rec.instance_attribute11
440 ),
441 DECODE (
442 inst_intf_rec.instance_attribute12,
443 NULL, l_miss_char,
444 l_miss_char, NULL,
445 inst_intf_rec.instance_attribute12
446 ),
447 DECODE (
448 inst_intf_rec.instance_attribute13,
449 NULL, l_miss_char,
450 l_miss_char, NULL,
451 inst_intf_rec.instance_attribute13
452 ),
453 DECODE (
454 inst_intf_rec.instance_attribute14,
455 NULL, l_miss_char,
456 l_miss_char, NULL,
457 inst_intf_rec.instance_attribute14
458 ),
459 DECODE (
460 inst_intf_rec.instance_attribute15,
461 NULL, l_miss_char,
462 l_miss_char, NULL,
463 inst_intf_rec.instance_attribute15
464 ),
465 DECODE (
466 inst_intf_rec.instance_attribute16,
467 NULL, l_miss_char,
468 l_miss_char, NULL,
469 inst_intf_rec.instance_attribute16
470 ),
471 DECODE (
472 inst_intf_rec.instance_attribute17,
473 NULL, l_miss_char,
474 l_miss_char, NULL,
475 inst_intf_rec.instance_attribute17
476 ),
477 DECODE (
478 inst_intf_rec.instance_attribute18,
479 NULL, l_miss_char,
480 l_miss_char, NULL,
481 inst_intf_rec.instance_attribute18
482 ),
483 DECODE (
484 inst_intf_rec.instance_attribute19,
485 NULL, l_miss_char,
486 l_miss_char, NULL,
487 inst_intf_rec.instance_attribute19
488 ),
489 DECODE (
490 inst_intf_rec.instance_attribute20,
491 NULL, l_miss_char,
492 l_miss_char, NULL,
493 inst_intf_rec.instance_attribute20
494 ),
495 DECODE (
496 inst_intf_rec.instance_attribute21,
497 NULL, l_miss_char,
498 l_miss_char, NULL,
499 inst_intf_rec.instance_attribute21
500 ),
501 DECODE (
502 inst_intf_rec.instance_attribute22,
503 NULL, l_miss_char,
504 l_miss_char, NULL,
505 inst_intf_rec.instance_attribute22
506 ),
507 DECODE (
508 inst_intf_rec.instance_attribute23,
509 NULL, l_miss_char,
510 l_miss_char, NULL,
511 inst_intf_rec.instance_attribute23
512 ),
513 DECODE (
514 inst_intf_rec.instance_attribute24,
515 NULL, l_miss_char,
516 l_miss_char, NULL,
517 inst_intf_rec.instance_attribute24
518 ),
519 DECODE (
520 inst_intf_rec.instance_attribute25,
521 NULL, l_miss_char,
522 l_miss_char, NULL,
523 inst_intf_rec.instance_attribute25
524 ),
525 DECODE (
526 inst_intf_rec.instance_attribute26,
527 NULL, l_miss_char,
528 l_miss_char, NULL,
529 inst_intf_rec.instance_attribute26
530 ),
531 DECODE (
532 inst_intf_rec.instance_attribute27,
533 NULL, l_miss_char,
534 l_miss_char, NULL,
535 inst_intf_rec.instance_attribute27
536 ),
537 DECODE (
538 inst_intf_rec.instance_attribute28,
539 NULL, l_miss_char,
540 l_miss_char, NULL,
541 inst_intf_rec.instance_attribute28
542 ),
543 DECODE (
544 inst_intf_rec.instance_attribute29,
545 NULL, l_miss_char,
546 l_miss_char, NULL,
547 inst_intf_rec.instance_attribute29
548 ),
549 DECODE (
550 inst_intf_rec.instance_attribute30,
551 NULL, l_miss_char,
552 l_miss_char, NULL,
553 inst_intf_rec.instance_attribute30
554 ),
555 l_item_object_version, -- OBJECT_VERSION_NUMBER
556 l_miss_num, -- LAST_TXN_LINE_DETAIL_ID
557 DECODE (
558 inst_intf_rec.install_location_type_code,
559 NULL, l_miss_char,
560 l_miss_char, NULL,
561 inst_intf_rec.install_location_type_code
562 ),
563 DECODE (
564 inst_intf_rec.install_location_id,
565 NULL, l_miss_num,
566 l_miss_num, NULL,
567 inst_intf_rec.install_location_id
568 ),
569 DECODE (
570 inst_intf_rec.network_asset_flag ,
571 null, l_miss_char,
572 l_miss_char, null,
573 inst_intf_rec.network_asset_flag
574 ),
575 DECODE (
576 inst_intf_rec.maintainable_flag,
577 null, l_miss_char,
578 l_miss_char, null,
579 inst_intf_rec.maintainable_flag
580 ),
581 DECODE (
582 inst_intf_rec.equipment_gen_object_id,
583 null, l_miss_num,
584 l_miss_num, null,
585 inst_intf_rec.equipment_gen_object_id
586 ),
587 DECODE (
588 inst_intf_rec.asset_criticality_code,
589 null, l_miss_char,
590 l_miss_char, null,
591 inst_intf_rec.asset_criticality_code
592 ),
593 DECODE (
594 inst_intf_rec.operational_log_flag ,
595 null, l_miss_char,
596 l_miss_char, null,
597 inst_intf_rec.operational_log_flag
598 ),
599 DECODE (
600 inst_intf_rec.supplier_warranty_exp_date,
601 null, l_miss_date,
602 l_miss_date, null,
603 inst_intf_rec.supplier_warranty_exp_date
604 ),
605 DECODE (
606 inst_intf_rec.instantiation_flag,
607 null, l_miss_char,
608 l_miss_char, null,
609 inst_intf_rec.instantiation_flag
610 ),
611
612 DECODE (
613 inst_intf_rec.category_id,
614 null, l_miss_num,
615 l_miss_num, null,
616 inst_intf_rec.category_id
617 ),
618 l_miss_char, -- INSTANCE_USAGE_CODE
619 -- Commenting the code as existence of instance_usage_code
620 -- in csi_instance_interface is under discussion
621 /*
622 DECODE (
623 inst_intf_rec.instance_usage_code,
624 NULL, l_miss_char,
625 l_miss_char, NULL,
626 inst_intf_rec.instance_usage_code
627 ),
628 */
629 fnd_api.g_true, -- CHECK_FOR_INSTANCE_EXPIRY
630 DECODE (
631 inst_intf_rec.instance_description,
632 NULL, l_miss_char,
633 l_miss_char, NULL,
634 inst_intf_rec.instance_description
635 ),
636 DECODE (
637 inst_intf_rec.operational_status_code,
638 NULL, l_miss_char,
639 l_miss_char, NULL,
640 inst_intf_rec.operational_status_code
641 )
642 INTO x_instance_tbl(inst_idx).inv_master_organization_id,
643 x_instance_tbl(inst_idx).vld_organization_id,
644 x_instance_tbl(inst_idx).inventory_item_id,
645 x_instance_tbl(inst_idx).location_type_code,
646 x_instance_tbl(inst_idx).location_id,
647 x_instance_tbl(inst_idx).inv_organization_id,
648 x_instance_tbl(inst_idx).inv_subinventory_name,
649 x_instance_tbl(inst_idx).inv_locator_id,
650 x_instance_tbl(inst_idx).lot_number,
651 x_instance_tbl(inst_idx).pa_project_id,
652 x_instance_tbl(inst_idx).pa_project_task_id,
653 x_instance_tbl(inst_idx).in_transit_order_line_id,
654 x_instance_tbl(inst_idx).wip_job_id,
655 x_instance_tbl(inst_idx).po_order_line_id,
656 x_instance_tbl(inst_idx).inventory_revision,
657 x_instance_tbl(inst_idx).serial_number,
658 x_instance_tbl(inst_idx).mfg_serial_number_flag,
659 x_instance_tbl(inst_idx).quantity,
660 x_instance_tbl(inst_idx).unit_of_measure,
661 x_instance_tbl(inst_idx).accounting_class_code,
662 x_instance_tbl(inst_idx).instance_condition_id,
663 x_instance_tbl(inst_idx).instance_status_id,
664 x_instance_tbl(inst_idx).customer_view_flag,
665 x_instance_tbl(inst_idx).merchant_view_flag,
666 x_instance_tbl(inst_idx).sellable_flag,
667 x_instance_tbl(inst_idx).system_id,
668 x_instance_tbl(inst_idx).instance_type_code,
669 x_instance_tbl(inst_idx).active_end_date,
670 x_instance_tbl(inst_idx).last_oe_order_line_id,
671 x_instance_tbl(inst_idx).last_oe_rma_line_id,
672 x_instance_tbl(inst_idx).last_po_po_line_id,
673 x_instance_tbl(inst_idx).last_oe_po_number,
674 x_instance_tbl(inst_idx).last_wip_job_id,
675 x_instance_tbl(inst_idx).last_pa_project_id,
676 x_instance_tbl(inst_idx).last_pa_task_id,
677 x_instance_tbl(inst_idx).last_oe_agreement_id,
678 x_instance_tbl(inst_idx).install_date,
679 x_instance_tbl(inst_idx).manually_created_flag,
680 x_instance_tbl(inst_idx).return_by_date,
681 x_instance_tbl(inst_idx).actual_return_date,
682 x_instance_tbl(inst_idx).creation_complete_flag,
683 x_instance_tbl(inst_idx).completeness_flag,
684 x_instance_tbl(inst_idx).version_label,
685 x_instance_tbl(inst_idx).version_label_description,
686 x_instance_tbl(inst_idx).CONTEXT,
687 x_instance_tbl(inst_idx).attribute1,
688 x_instance_tbl(inst_idx).attribute2,
689 x_instance_tbl(inst_idx).attribute3,
690 x_instance_tbl(inst_idx).attribute4,
691 x_instance_tbl(inst_idx).attribute5,
692 x_instance_tbl(inst_idx).attribute6,
693 x_instance_tbl(inst_idx).attribute7,
694 x_instance_tbl(inst_idx).attribute8,
695 x_instance_tbl(inst_idx).attribute9,
696 x_instance_tbl(inst_idx).attribute10,
697 x_instance_tbl(inst_idx).attribute11,
698 x_instance_tbl(inst_idx).attribute12,
699 x_instance_tbl(inst_idx).attribute13,
700 x_instance_tbl(inst_idx).attribute14,
701 x_instance_tbl(inst_idx).attribute15,
702 x_instance_tbl(inst_idx).attribute16,
703 x_instance_tbl(inst_idx).attribute17,
704 x_instance_tbl(inst_idx).attribute18,
705 x_instance_tbl(inst_idx).attribute19,
706 x_instance_tbl(inst_idx).attribute20,
707 x_instance_tbl(inst_idx).attribute21,
708 x_instance_tbl(inst_idx).attribute22,
709 x_instance_tbl(inst_idx).attribute23,
710 x_instance_tbl(inst_idx).attribute24,
711 x_instance_tbl(inst_idx).attribute25,
712 x_instance_tbl(inst_idx).attribute26,
713 x_instance_tbl(inst_idx).attribute27,
714 x_instance_tbl(inst_idx).attribute28,
715 x_instance_tbl(inst_idx).attribute29,
716 x_instance_tbl(inst_idx).attribute30,
717 x_instance_tbl(inst_idx).object_version_number,
718 x_instance_tbl(inst_idx).last_txn_line_detail_id,
719 x_instance_tbl(inst_idx).install_location_type_code,
720 x_instance_tbl(inst_idx).install_location_id,
721 x_instance_tbl(inst_idx).network_asset_flag,
722 x_instance_tbl(inst_idx).maintainable_flag,
723 x_instance_tbl(inst_idx).equipment_gen_object_id,
724 x_instance_tbl(inst_idx).asset_criticality_code,
725 x_instance_tbl(inst_idx).operational_log_flag,
726 x_instance_tbl(inst_idx).supplier_warranty_exp_date,
727 x_instance_tbl(inst_idx).instantiation_flag,
728 x_instance_tbl(inst_idx).category_id,
729 x_instance_tbl(inst_idx).instance_usage_code,
730 x_instance_tbl(inst_idx).check_for_instance_expiry,
731 x_instance_tbl(inst_idx).instance_description,
732 x_instance_tbl(inst_idx).operational_status_code
733 FROM DUAL;
734
735 x_instance_tbl(inst_idx).INTERFACE_ID := inst_intf_rec.inst_interface_id;
736
737 ---Populate Pricing attribites
738 IF inst_intf_rec.pricing_attribute_id IS NOT NULL
739 THEN
740 x_price_tbl(price_idx).pricing_attribute_id :=
741 inst_intf_rec.pricing_attribute_id;
742 END IF;
743
744
745 IF ( inst_intf_rec.pricing_attribute1 IS NOT NULL
746 OR inst_intf_rec.pricing_attribute2 IS NOT NULL
747 OR inst_intf_rec.pricing_attribute3 IS NOT NULL
748 OR inst_intf_rec.pricing_attribute4 IS NOT NULL
749 OR inst_intf_rec.pricing_attribute5 IS NOT NULL
750 OR inst_intf_rec.pricing_attribute6 IS NOT NULL
751 OR inst_intf_rec.pricing_attribute7 IS NOT NULL
752 OR inst_intf_rec.pricing_attribute8 IS NOT NULL
753 OR inst_intf_rec.pricing_attribute9 IS NOT NULL
754 OR inst_intf_rec.pricing_attribute10 IS NOT NULL
755 OR inst_intf_rec.pricing_attribute11 IS NOT NULL
756 OR inst_intf_rec.pricing_attribute12 IS NOT NULL
757 OR inst_intf_rec.pricing_attribute13 IS NOT NULL
758 OR inst_intf_rec.pricing_attribute14 IS NOT NULL
759 OR inst_intf_rec.pricing_attribute15 IS NOT NULL
760 OR inst_intf_rec.pricing_attribute16 IS NOT NULL
761 OR inst_intf_rec.pricing_attribute17 IS NOT NULL
762 OR inst_intf_rec.pricing_attribute18 IS NOT NULL
763 OR inst_intf_rec.pricing_attribute19 IS NOT NULL
764 OR inst_intf_rec.pricing_attribute20 IS NOT NULL
765 OR inst_intf_rec.pricing_attribute21 IS NOT NULL
766 OR inst_intf_rec.pricing_attribute22 IS NOT NULL
767 OR inst_intf_rec.pricing_attribute23 IS NOT NULL
768 OR inst_intf_rec.pricing_attribute24 IS NOT NULL
769 OR inst_intf_rec.pricing_attribute25 IS NOT NULL
770 OR inst_intf_rec.pricing_attribute26 IS NOT NULL
771 OR inst_intf_rec.pricing_attribute27 IS NOT NULL
772 OR inst_intf_rec.pricing_attribute28 IS NOT NULL
773 OR inst_intf_rec.pricing_attribute29 IS NOT NULL
774 OR inst_intf_rec.pricing_attribute30 IS NOT NULL
775 OR inst_intf_rec.pricing_attribute31 IS NOT NULL
776 OR inst_intf_rec.pricing_attribute32 IS NOT NULL
777 OR inst_intf_rec.pricing_attribute33 IS NOT NULL
778 OR inst_intf_rec.pricing_attribute34 IS NOT NULL
779 OR inst_intf_rec.pricing_attribute35 IS NOT NULL
780 OR inst_intf_rec.pricing_attribute36 IS NOT NULL
781 OR inst_intf_rec.pricing_attribute37 IS NOT NULL
782 OR inst_intf_rec.pricing_attribute38 IS NOT NULL
783 OR inst_intf_rec.pricing_attribute39 IS NOT NULL
784 OR inst_intf_rec.pricing_attribute40 IS NOT NULL
785 OR inst_intf_rec.pricing_attribute41 IS NOT NULL
786 OR inst_intf_rec.pricing_attribute42 IS NOT NULL
787 OR inst_intf_rec.pricing_attribute43 IS NOT NULL
788 OR inst_intf_rec.pricing_attribute44 IS NOT NULL
789 OR inst_intf_rec.pricing_attribute45 IS NOT NULL
790 OR inst_intf_rec.pricing_attribute46 IS NOT NULL
791 OR inst_intf_rec.pricing_attribute47 IS NOT NULL
792 OR inst_intf_rec.pricing_attribute48 IS NOT NULL
793 OR inst_intf_rec.pricing_attribute49 IS NOT NULL
794 OR inst_intf_rec.pricing_attribute50 IS NOT NULL
795 OR inst_intf_rec.pricing_attribute51 IS NOT NULL
796 OR inst_intf_rec.pricing_attribute52 IS NOT NULL
797 OR inst_intf_rec.pricing_attribute53 IS NOT NULL
798 OR inst_intf_rec.pricing_attribute54 IS NOT NULL
799 OR inst_intf_rec.pricing_attribute55 IS NOT NULL
800 OR inst_intf_rec.pricing_attribute56 IS NOT NULL
801 OR inst_intf_rec.pricing_attribute57 IS NOT NULL
802 OR inst_intf_rec.pricing_attribute58 IS NOT NULL
803 OR inst_intf_rec.pricing_attribute59 IS NOT NULL
804 OR inst_intf_rec.pricing_attribute60 IS NOT NULL
805 OR inst_intf_rec.pricing_attribute61 IS NOT NULL
806 OR inst_intf_rec.pricing_attribute62 IS NOT NULL
807 OR inst_intf_rec.pricing_attribute63 IS NOT NULL
808 OR inst_intf_rec.pricing_attribute64 IS NOT NULL
809 OR inst_intf_rec.pricing_attribute65 IS NOT NULL
810 OR inst_intf_rec.pricing_attribute66 IS NOT NULL
811 OR inst_intf_rec.pricing_attribute67 IS NOT NULL
812 OR inst_intf_rec.pricing_attribute68 IS NOT NULL
813 OR inst_intf_rec.pricing_attribute69 IS NOT NULL
814 OR inst_intf_rec.pricing_attribute70 IS NOT NULL
815 OR inst_intf_rec.pricing_attribute71 IS NOT NULL
816 OR inst_intf_rec.pricing_attribute72 IS NOT NULL
817 OR inst_intf_rec.pricing_attribute73 IS NOT NULL
818 OR inst_intf_rec.pricing_attribute74 IS NOT NULL
819 OR inst_intf_rec.pricing_attribute75 IS NOT NULL
820 OR inst_intf_rec.pricing_attribute76 IS NOT NULL
821 OR inst_intf_rec.pricing_attribute77 IS NOT NULL
822 OR inst_intf_rec.pricing_attribute78 IS NOT NULL
823 OR inst_intf_rec.pricing_attribute79 IS NOT NULL
824 OR inst_intf_rec.pricing_attribute80 IS NOT NULL
825 OR inst_intf_rec.pricing_attribute81 IS NOT NULL
826 OR inst_intf_rec.pricing_attribute82 IS NOT NULL
827 OR inst_intf_rec.pricing_attribute83 IS NOT NULL
828 OR inst_intf_rec.pricing_attribute84 IS NOT NULL
829 OR inst_intf_rec.pricing_attribute85 IS NOT NULL
830 OR inst_intf_rec.pricing_attribute86 IS NOT NULL
831 OR inst_intf_rec.pricing_attribute87 IS NOT NULL
832 OR inst_intf_rec.pricing_attribute88 IS NOT NULL
833 OR inst_intf_rec.pricing_attribute89 IS NOT NULL
834 OR inst_intf_rec.pricing_attribute90 IS NOT NULL
835 OR inst_intf_rec.pricing_attribute91 IS NOT NULL
836 OR inst_intf_rec.pricing_attribute92 IS NOT NULL
837 OR inst_intf_rec.pricing_attribute93 IS NOT NULL
838 OR inst_intf_rec.pricing_attribute94 IS NOT NULL
839 OR inst_intf_rec.pricing_attribute95 IS NOT NULL
840 OR inst_intf_rec.pricing_attribute96 IS NOT NULL
841 OR inst_intf_rec.pricing_attribute97 IS NOT NULL
842 OR inst_intf_rec.pricing_attribute98 IS NOT NULL
843 OR inst_intf_rec.pricing_attribute99 IS NOT NULL
844 OR inst_intf_rec.pricing_attribute100 IS NOT NULL
845 )
846 THEN
847
848 SELECT inst_intf_rec.instance_id,
849 l_miss_date, -- ACTIVE_START_DATE
850 DECODE (
851 inst_intf_rec.pricing_att_end_date,
852 NULL, l_miss_date,
853 l_miss_date, NULL,
854 inst_intf_rec.pricing_att_end_date
855 ),
856 DECODE (
857 inst_intf_rec.pricing_context,
858 NULL, l_miss_char,
859 l_miss_char, NULL,
860 inst_intf_rec.pricing_context
861 ),
862 DECODE (
863 inst_intf_rec.pricing_attribute1,
864 NULL, l_miss_char,
865 l_miss_char, NULL,
866 inst_intf_rec.pricing_attribute1
867 ),
868 DECODE (
869 inst_intf_rec.pricing_attribute2,
870 NULL, l_miss_char,
871 l_miss_char, NULL,
872 inst_intf_rec.pricing_attribute2
873 ),
874 DECODE (
875 inst_intf_rec.pricing_attribute3,
876 NULL, l_miss_char,
877 l_miss_char, NULL,
878 inst_intf_rec.pricing_attribute3
879 ),
880 DECODE (
881 inst_intf_rec.pricing_attribute4,
882 NULL, l_miss_char,
883 l_miss_char, NULL,
884 inst_intf_rec.pricing_attribute4
885 ),
886 DECODE (
887 inst_intf_rec.pricing_attribute5,
888 NULL, l_miss_char,
889 l_miss_char, NULL,
890 inst_intf_rec.pricing_attribute5
891 ),
892 DECODE (
893 inst_intf_rec.pricing_attribute6,
894 NULL, l_miss_char,
895 l_miss_char, NULL,
896 inst_intf_rec.pricing_attribute6
897 ),
898 DECODE (
899 inst_intf_rec.pricing_attribute7,
900 NULL, l_miss_char,
901 l_miss_char, NULL,
902 inst_intf_rec.pricing_attribute7
903 ),
904 DECODE (
905 inst_intf_rec.pricing_attribute8,
906 NULL, l_miss_char,
907 l_miss_char, NULL,
908 inst_intf_rec.pricing_attribute8
909 ),
910 DECODE (
911 inst_intf_rec.pricing_attribute9,
912 NULL, l_miss_char,
913 l_miss_char, NULL,
914 inst_intf_rec.pricing_attribute9
915 ),
916 DECODE (
917 inst_intf_rec.pricing_attribute10,
918 NULL, l_miss_char,
919 l_miss_char, NULL,
920 inst_intf_rec.pricing_attribute10
921 ),
922 DECODE (
923 inst_intf_rec.pricing_attribute11,
924 NULL, l_miss_char,
925 l_miss_char, NULL,
926 inst_intf_rec.pricing_attribute11
927 ),
928 DECODE (
929 inst_intf_rec.pricing_attribute12,
930 NULL, l_miss_char,
931 l_miss_char, NULL,
932 inst_intf_rec.pricing_attribute12
933 ),
934 DECODE (
935 inst_intf_rec.pricing_attribute13,
936 NULL, l_miss_char,
937 l_miss_char, NULL,
938 inst_intf_rec.pricing_attribute13
939 ),
940 DECODE (
941 inst_intf_rec.pricing_attribute14,
942 NULL, l_miss_char,
943 l_miss_char, NULL,
944 inst_intf_rec.pricing_attribute14
945 ),
946 DECODE (
947 inst_intf_rec.pricing_attribute15,
948 NULL, l_miss_char,
949 l_miss_char, NULL,
950 inst_intf_rec.pricing_attribute15
951 ),
952 DECODE (
953 inst_intf_rec.pricing_attribute16,
954 NULL, l_miss_char,
955 l_miss_char, NULL,
956 inst_intf_rec.pricing_attribute16
957 ),
958 DECODE (
959 inst_intf_rec.pricing_attribute17,
960 NULL, l_miss_char,
961 l_miss_char, NULL,
962 inst_intf_rec.pricing_attribute17
963 ),
964 DECODE (
965 inst_intf_rec.pricing_attribute18,
966 NULL, l_miss_char,
967 l_miss_char, NULL,
968 inst_intf_rec.pricing_attribute18
969 ),
970 DECODE (
971 inst_intf_rec.pricing_attribute19,
972 NULL, l_miss_char,
973 l_miss_char, NULL,
974 inst_intf_rec.pricing_attribute19
975 ),
976 DECODE (
977 inst_intf_rec.pricing_attribute20,
978 NULL, l_miss_char,
979 l_miss_char, NULL,
980 inst_intf_rec.pricing_attribute20
981 ),
982 DECODE (
983 inst_intf_rec.pricing_attribute21,
984 NULL, l_miss_char,
985 l_miss_char, NULL,
986 inst_intf_rec.pricing_attribute21
987 ),
988 DECODE (
989 inst_intf_rec.pricing_attribute22,
990 NULL, l_miss_char,
991 l_miss_char, NULL,
992 inst_intf_rec.pricing_attribute22
993 ),
994 DECODE (
995 inst_intf_rec.pricing_attribute23,
996 NULL, l_miss_char,
997 l_miss_char, NULL,
998 inst_intf_rec.pricing_attribute23
999 ),
1000 DECODE (
1001 inst_intf_rec.pricing_attribute24,
1002 NULL, l_miss_char,
1003 l_miss_char, NULL,
1004 inst_intf_rec.pricing_attribute24
1005 ),
1006 DECODE (
1007 inst_intf_rec.pricing_attribute25,
1008 NULL, l_miss_char,
1009 l_miss_char, NULL,
1010 inst_intf_rec.pricing_attribute25
1011 ),
1012 DECODE (
1013 inst_intf_rec.pricing_attribute26,
1014 NULL, l_miss_char,
1015 l_miss_char, NULL,
1016 inst_intf_rec.pricing_attribute26
1017 ),
1018 DECODE (
1019 inst_intf_rec.pricing_attribute27,
1020 NULL, l_miss_char,
1021 l_miss_char, NULL,
1022 inst_intf_rec.pricing_attribute27
1023 ),
1024 DECODE (
1025 inst_intf_rec.pricing_attribute28,
1026 NULL, l_miss_char,
1027 l_miss_char, NULL,
1028 inst_intf_rec.pricing_attribute28
1029 ),
1030 DECODE (
1031 inst_intf_rec.pricing_attribute29,
1032 NULL, l_miss_char,
1033 l_miss_char, NULL,
1034 inst_intf_rec.pricing_attribute29
1035 ),
1036 DECODE (
1037 inst_intf_rec.pricing_attribute30,
1038 NULL, l_miss_char,
1039 l_miss_char, NULL,
1040 inst_intf_rec.pricing_attribute30
1041 ),
1042 DECODE (
1043 inst_intf_rec.pricing_attribute31,
1044 NULL, l_miss_char,
1045 l_miss_char, NULL,
1046 inst_intf_rec.pricing_attribute31
1047 ),
1048 DECODE (
1049 inst_intf_rec.pricing_attribute32,
1050 NULL, l_miss_char,
1051 l_miss_char, NULL,
1052 inst_intf_rec.pricing_attribute32
1053 ),
1054 DECODE (
1055 inst_intf_rec.pricing_attribute33,
1056 NULL, l_miss_char,
1057 l_miss_char, NULL,
1058 inst_intf_rec.pricing_attribute33
1059 ),
1060 DECODE (
1061 inst_intf_rec.pricing_attribute34,
1062 NULL, l_miss_char,
1063 l_miss_char, NULL,
1064 inst_intf_rec.pricing_attribute34
1065 ),
1066 DECODE (
1067 inst_intf_rec.pricing_attribute35,
1068 NULL, l_miss_char,
1069 l_miss_char, NULL,
1070 inst_intf_rec.pricing_attribute35
1071 ),
1072 DECODE (
1073 inst_intf_rec.pricing_attribute36,
1074 NULL, l_miss_char,
1075 l_miss_char, NULL,
1076 inst_intf_rec.pricing_attribute36
1077 ),
1078 DECODE (
1079 inst_intf_rec.pricing_attribute37,
1080 NULL, l_miss_char,
1081 l_miss_char, NULL,
1082 inst_intf_rec.pricing_attribute37
1083 ),
1084 DECODE (
1085 inst_intf_rec.pricing_attribute38,
1086 NULL, l_miss_char,
1087 l_miss_char, NULL,
1088 inst_intf_rec.pricing_attribute38
1089 ),
1090 DECODE (
1091 inst_intf_rec.pricing_attribute39,
1092 NULL, l_miss_char,
1093 l_miss_char, NULL,
1094 inst_intf_rec.pricing_attribute39
1095 ),
1096 DECODE (
1097 inst_intf_rec.pricing_attribute40,
1098 NULL, l_miss_char,
1099 l_miss_char, NULL,
1100 inst_intf_rec.pricing_attribute40
1101 ),
1102 DECODE (
1103 inst_intf_rec.pricing_attribute41,
1104 NULL, l_miss_char,
1105 l_miss_char, NULL,
1106 inst_intf_rec.pricing_attribute41
1107 ),
1108 DECODE (
1109 inst_intf_rec.pricing_attribute42,
1110 NULL, l_miss_char,
1111 l_miss_char, NULL,
1112 inst_intf_rec.pricing_attribute42
1113 ),
1114 DECODE (
1115 inst_intf_rec.pricing_attribute43,
1116 NULL, l_miss_char,
1117 l_miss_char, NULL,
1118 inst_intf_rec.pricing_attribute43
1119 ),
1120 DECODE (
1121 inst_intf_rec.pricing_attribute44,
1122 NULL, l_miss_char,
1123 l_miss_char, NULL,
1124 inst_intf_rec.pricing_attribute44
1125 ),
1126 DECODE (
1127 inst_intf_rec.pricing_attribute45,
1128 NULL, l_miss_char,
1129 l_miss_char, NULL,
1130 inst_intf_rec.pricing_attribute45
1131 ),
1132 DECODE (
1133 inst_intf_rec.pricing_attribute46,
1134 NULL, l_miss_char,
1135 l_miss_char, NULL,
1136 inst_intf_rec.pricing_attribute46
1137 ),
1138 DECODE (
1139 inst_intf_rec.pricing_attribute47,
1140 NULL, l_miss_char,
1141 l_miss_char, NULL,
1142 inst_intf_rec.pricing_attribute47
1143 ),
1144 DECODE (
1145 inst_intf_rec.pricing_attribute48,
1146 NULL, l_miss_char,
1147 l_miss_char, NULL,
1148 inst_intf_rec.pricing_attribute48
1149 ),
1150 DECODE (
1151 inst_intf_rec.pricing_attribute49,
1152 NULL, l_miss_char,
1153 l_miss_char, NULL,
1154 inst_intf_rec.pricing_attribute49
1155 ),
1156 DECODE (
1157 inst_intf_rec.pricing_attribute50,
1158 NULL, l_miss_char,
1159 l_miss_char, NULL,
1160 inst_intf_rec.pricing_attribute50
1161 ),
1162 DECODE (
1163 inst_intf_rec.pricing_attribute51,
1164 NULL, l_miss_char,
1165 l_miss_char, NULL,
1166 inst_intf_rec.pricing_attribute51
1167 ),
1168 DECODE (
1169 inst_intf_rec.pricing_attribute52,
1170 NULL, l_miss_char,
1171 l_miss_char, NULL,
1172 inst_intf_rec.pricing_attribute52
1173 ),
1174 DECODE (
1175 inst_intf_rec.pricing_attribute53,
1176 NULL, l_miss_char,
1177 l_miss_char, NULL,
1178 inst_intf_rec.pricing_attribute53
1179 ),
1180 DECODE (
1181 inst_intf_rec.pricing_attribute54,
1182 NULL, l_miss_char,
1183 l_miss_char, NULL,
1184 inst_intf_rec.pricing_attribute54
1185 ),
1186 DECODE (
1187 inst_intf_rec.pricing_attribute55,
1188 NULL, l_miss_char,
1189 l_miss_char, NULL,
1190 inst_intf_rec.pricing_attribute55
1191 ),
1192 DECODE (
1193 inst_intf_rec.pricing_attribute56,
1194 NULL, l_miss_char,
1195 l_miss_char, NULL,
1196 inst_intf_rec.pricing_attribute56
1197 ),
1198 DECODE (
1199 inst_intf_rec.pricing_attribute57,
1200 NULL, l_miss_char,
1201 l_miss_char, NULL,
1202 inst_intf_rec.pricing_attribute57
1203 ),
1204 DECODE (
1205 inst_intf_rec.pricing_attribute58,
1206 NULL, l_miss_char,
1207 l_miss_char, NULL,
1208 inst_intf_rec.pricing_attribute58
1209 ),
1210 DECODE (
1211 inst_intf_rec.pricing_attribute59,
1212 NULL, l_miss_char,
1213 l_miss_char, NULL,
1214 inst_intf_rec.pricing_attribute59
1215 ),
1216 DECODE (
1217 inst_intf_rec.pricing_attribute60,
1218 NULL, l_miss_char,
1219 l_miss_char, NULL,
1220 inst_intf_rec.pricing_attribute60
1221 ),
1222 DECODE (
1223 inst_intf_rec.pricing_attribute61,
1224 NULL, l_miss_char,
1225 l_miss_char, NULL,
1226 inst_intf_rec.pricing_attribute61
1227 ),
1228 DECODE (
1229 inst_intf_rec.pricing_attribute62,
1230 NULL, l_miss_char,
1231 l_miss_char, NULL,
1232 inst_intf_rec.pricing_attribute62
1233 ),
1234 DECODE (
1235 inst_intf_rec.pricing_attribute63,
1236 NULL, l_miss_char,
1237 l_miss_char, NULL,
1238 inst_intf_rec.pricing_attribute63
1239 ),
1240 DECODE (
1241 inst_intf_rec.pricing_attribute64,
1242 NULL, l_miss_char,
1243 l_miss_char, NULL,
1244 inst_intf_rec.pricing_attribute64
1245 ),
1246 DECODE (
1247 inst_intf_rec.pricing_attribute65,
1248 NULL, l_miss_char,
1249 l_miss_char, NULL,
1250 inst_intf_rec.pricing_attribute65
1251 ),
1252 DECODE (
1253 inst_intf_rec.pricing_attribute66,
1254 NULL, l_miss_char,
1255 l_miss_char, NULL,
1256 inst_intf_rec.pricing_attribute66
1257 ),
1258 DECODE (
1259 inst_intf_rec.pricing_attribute67,
1260 NULL, l_miss_char,
1261 l_miss_char, NULL,
1262 inst_intf_rec.pricing_attribute67
1263 ),
1264 DECODE (
1265 inst_intf_rec.pricing_attribute68,
1266 NULL, l_miss_char,
1267 l_miss_char, NULL,
1268 inst_intf_rec.pricing_attribute68
1269 ),
1270 DECODE (
1271 inst_intf_rec.pricing_attribute69,
1272 NULL, l_miss_char,
1273 l_miss_char, NULL,
1274 inst_intf_rec.pricing_attribute69
1275 ),
1276 DECODE (
1277 inst_intf_rec.pricing_attribute70,
1278 NULL, l_miss_char,
1279 l_miss_char, NULL,
1280 inst_intf_rec.pricing_attribute70
1281 ),
1282 DECODE (
1283 inst_intf_rec.pricing_attribute71,
1284 NULL, l_miss_char,
1285 l_miss_char, NULL,
1286 inst_intf_rec.pricing_attribute71
1287 ),
1288 DECODE (
1289 inst_intf_rec.pricing_attribute72,
1290 NULL, l_miss_char,
1291 l_miss_char, NULL,
1292 inst_intf_rec.pricing_attribute72
1293 ),
1294 DECODE (
1295 inst_intf_rec.pricing_attribute73,
1296 NULL, l_miss_char,
1297 l_miss_char, NULL,
1298 inst_intf_rec.pricing_attribute73
1299 ),
1300 DECODE (
1301 inst_intf_rec.pricing_attribute74,
1302 NULL, l_miss_char,
1303 l_miss_char, NULL,
1304 inst_intf_rec.pricing_attribute74
1305 ),
1306 DECODE (
1307 inst_intf_rec.pricing_attribute75,
1308 NULL, l_miss_char,
1309 l_miss_char, NULL,
1310 inst_intf_rec.pricing_attribute75
1311 ),
1312 DECODE (
1313 inst_intf_rec.pricing_attribute76,
1314 NULL, l_miss_char,
1315 l_miss_char, NULL,
1316 inst_intf_rec.pricing_attribute76
1317 ),
1318 DECODE (
1319 inst_intf_rec.pricing_attribute77,
1320 NULL, l_miss_char,
1321 l_miss_char, NULL,
1322 inst_intf_rec.pricing_attribute77
1323 ),
1324 DECODE (
1325 inst_intf_rec.pricing_attribute78,
1326 NULL, l_miss_char,
1327 l_miss_char, NULL,
1328 inst_intf_rec.pricing_attribute78
1329 ),
1330 DECODE (
1331 inst_intf_rec.pricing_attribute79,
1332 NULL, l_miss_char,
1333 l_miss_char, NULL,
1334 inst_intf_rec.pricing_attribute79
1335 ),
1336 DECODE (
1337 inst_intf_rec.pricing_attribute80,
1338 NULL, l_miss_char,
1339 l_miss_char, NULL,
1340 inst_intf_rec.pricing_attribute80
1341 ),
1342 DECODE (
1343 inst_intf_rec.pricing_attribute81,
1344 NULL, l_miss_char,
1345 l_miss_char, NULL,
1346 inst_intf_rec.pricing_attribute81
1347 ),
1348 DECODE (
1349 inst_intf_rec.pricing_attribute82,
1350 NULL, l_miss_char,
1351 l_miss_char, NULL,
1352 inst_intf_rec.pricing_attribute82
1353 ),
1354 DECODE (
1355 inst_intf_rec.pricing_attribute83,
1356 NULL, l_miss_char,
1357 l_miss_char, NULL,
1358 inst_intf_rec.pricing_attribute83
1359 ),
1360 DECODE (
1361 inst_intf_rec.pricing_attribute84,
1362 NULL, l_miss_char,
1363 l_miss_char, NULL,
1364 inst_intf_rec.pricing_attribute84
1365 ),
1366 DECODE (
1367 inst_intf_rec.pricing_attribute85,
1368 NULL, l_miss_char,
1369 l_miss_char, NULL,
1370 inst_intf_rec.pricing_attribute85
1371 ),
1372 DECODE (
1373 inst_intf_rec.pricing_attribute86,
1374 NULL, l_miss_char,
1375 l_miss_char, NULL,
1376 inst_intf_rec.pricing_attribute86
1377 ),
1378 DECODE (
1379 inst_intf_rec.pricing_attribute87,
1380 NULL, l_miss_char,
1381 l_miss_char, NULL,
1382 inst_intf_rec.pricing_attribute87
1383 ),
1384 DECODE (
1385 inst_intf_rec.pricing_attribute88,
1386 NULL, l_miss_char,
1387 l_miss_char, NULL,
1388 inst_intf_rec.pricing_attribute88
1389 ),
1390 DECODE (
1391 inst_intf_rec.pricing_attribute89,
1392 NULL, l_miss_char,
1393 l_miss_char, NULL,
1394 inst_intf_rec.pricing_attribute89
1395 ),
1396 DECODE (
1397 inst_intf_rec.pricing_attribute90,
1398 NULL, l_miss_char,
1399 l_miss_char, NULL,
1400 inst_intf_rec.pricing_attribute90
1401 ),
1402 DECODE (
1403 inst_intf_rec.pricing_attribute91,
1404 NULL, l_miss_char,
1405 l_miss_char, NULL,
1406 inst_intf_rec.pricing_attribute91
1407 ),
1408 DECODE (
1409 inst_intf_rec.pricing_attribute92,
1410 NULL, l_miss_char,
1411 l_miss_char, NULL,
1412 inst_intf_rec.pricing_attribute92
1413 ),
1414 DECODE (
1415 inst_intf_rec.pricing_attribute93,
1416 NULL, l_miss_char,
1417 l_miss_char, NULL,
1418 inst_intf_rec.pricing_attribute93
1419 ),
1420 DECODE (
1421 inst_intf_rec.pricing_attribute94,
1422 NULL, l_miss_char,
1423 l_miss_char, NULL,
1424 inst_intf_rec.pricing_attribute94
1425 ),
1426 DECODE (
1427 inst_intf_rec.pricing_attribute95,
1428 NULL, l_miss_char,
1429 l_miss_char, NULL,
1430 inst_intf_rec.pricing_attribute95
1431 ),
1432 DECODE (
1433 inst_intf_rec.pricing_attribute96,
1434 NULL, l_miss_char,
1435 l_miss_char, NULL,
1436 inst_intf_rec.pricing_attribute96
1437 ),
1438 DECODE (
1439 inst_intf_rec.pricing_attribute97,
1440 NULL, l_miss_char,
1441 l_miss_char, NULL,
1442 inst_intf_rec.pricing_attribute97
1443 ),
1444 DECODE (
1445 inst_intf_rec.pricing_attribute98,
1446 NULL, l_miss_char,
1447 l_miss_char, NULL,
1448 inst_intf_rec.pricing_attribute98
1449 ),
1450 DECODE (
1451 inst_intf_rec.pricing_attribute99,
1452 NULL, l_miss_char,
1453 l_miss_char, NULL,
1454 inst_intf_rec.pricing_attribute99
1455 ),
1456 DECODE (
1457 inst_intf_rec.pricing_attribute100,
1458 NULL, l_miss_char,
1459 l_miss_char, NULL,
1460 inst_intf_rec.pricing_attribute100
1461 ),
1462 DECODE (
1463 inst_intf_rec.pricing_flex_context,
1464 NULL, l_miss_char,
1465 l_miss_char, NULL,
1466 inst_intf_rec.pricing_flex_context
1467 ),
1468 DECODE (
1469 inst_intf_rec.pricing_flex_attribute1,
1470 NULL, l_miss_char,
1471 l_miss_char, NULL,
1472 inst_intf_rec.pricing_flex_attribute1
1473 ),
1474 DECODE (
1475 inst_intf_rec.pricing_flex_attribute2,
1476 NULL, l_miss_char,
1477 l_miss_char, NULL,
1478 inst_intf_rec.pricing_flex_attribute2
1479 ),
1480 DECODE (
1481 inst_intf_rec.pricing_flex_attribute3,
1482 NULL, l_miss_char,
1483 l_miss_char, NULL,
1484 inst_intf_rec.pricing_flex_attribute3
1485 ),
1486 DECODE (
1487 inst_intf_rec.pricing_flex_attribute4,
1488 NULL, l_miss_char,
1489 l_miss_char, NULL,
1490 inst_intf_rec.pricing_flex_attribute4
1491 ),
1492 DECODE (
1493 inst_intf_rec.pricing_flex_attribute5,
1494 NULL, l_miss_char,
1495 l_miss_char, NULL,
1496 inst_intf_rec.pricing_flex_attribute5
1497 ),
1498 DECODE (
1499 inst_intf_rec.pricing_flex_attribute6,
1500 NULL, l_miss_char,
1501 l_miss_char, NULL,
1502 inst_intf_rec.pricing_flex_attribute6
1503 ),
1504 DECODE (
1505 inst_intf_rec.pricing_flex_attribute7,
1506 NULL, l_miss_char,
1507 l_miss_char, NULL,
1508 inst_intf_rec.pricing_flex_attribute7
1509 ),
1510 DECODE (
1511 inst_intf_rec.pricing_flex_attribute8,
1512 NULL, l_miss_char,
1513 l_miss_char, NULL,
1514 inst_intf_rec.pricing_flex_attribute8
1515 ),
1516 DECODE (
1517 inst_intf_rec.pricing_flex_attribute9,
1518 NULL, l_miss_char,
1519 l_miss_char, NULL,
1520 inst_intf_rec.pricing_flex_attribute9
1521 ),
1522 DECODE (
1523 inst_intf_rec.pricing_flex_attribute10,
1524 NULL, l_miss_char,
1525 l_miss_char, NULL,
1526 inst_intf_rec.pricing_flex_attribute10
1527 ),
1528 DECODE (
1529 inst_intf_rec.pricing_flex_attribute11,
1530 NULL, l_miss_char,
1531 l_miss_char, NULL,
1532 inst_intf_rec.pricing_flex_attribute11
1533 ),
1534 DECODE (
1535 inst_intf_rec.pricing_flex_attribute12,
1536 NULL, l_miss_char,
1537 l_miss_char, NULL,
1538 inst_intf_rec.pricing_flex_attribute12
1539 ),
1540 DECODE (
1541 inst_intf_rec.pricing_flex_attribute13,
1542 NULL, l_miss_char,
1543 l_miss_char, NULL,
1544 inst_intf_rec.pricing_flex_attribute13
1545 ),
1546 DECODE (
1547 inst_intf_rec.pricing_flex_attribute14,
1548 NULL, l_miss_char,
1549 l_miss_char, NULL,
1550 inst_intf_rec.pricing_flex_attribute14
1551 ),
1552 DECODE (
1553 inst_intf_rec.pricing_flex_attribute15,
1554 NULL, l_miss_char,
1555 l_miss_char, NULL,
1556 inst_intf_rec.pricing_flex_attribute15
1557 ),
1558 l_price_object_version -- OBJECT_VERSION_NUMBER
1559 INTO x_price_tbl(price_idx).instance_id,
1560 x_price_tbl(price_idx).active_start_date,
1561 x_price_tbl(price_idx).active_end_date,
1562 x_price_tbl(price_idx).pricing_context,
1563 x_price_tbl(price_idx).pricing_attribute1,
1564 x_price_tbl(price_idx).pricing_attribute2,
1565 x_price_tbl(price_idx).pricing_attribute3,
1566 x_price_tbl(price_idx).pricing_attribute4,
1567 x_price_tbl(price_idx).pricing_attribute5,
1568 x_price_tbl(price_idx).pricing_attribute6,
1569 x_price_tbl(price_idx).pricing_attribute7,
1570 x_price_tbl(price_idx).pricing_attribute8,
1571 x_price_tbl(price_idx).pricing_attribute9,
1572 x_price_tbl(price_idx).pricing_attribute10,
1573 x_price_tbl(price_idx).pricing_attribute11,
1574 x_price_tbl(price_idx).pricing_attribute12,
1575 x_price_tbl(price_idx).pricing_attribute13,
1576 x_price_tbl(price_idx).pricing_attribute14,
1577 x_price_tbl(price_idx).pricing_attribute15,
1578 x_price_tbl(price_idx).pricing_attribute16,
1579 x_price_tbl(price_idx).pricing_attribute17,
1580 x_price_tbl(price_idx).pricing_attribute18,
1581 x_price_tbl(price_idx).pricing_attribute19,
1582 x_price_tbl(price_idx).pricing_attribute20,
1583 x_price_tbl(price_idx).pricing_attribute21,
1584 x_price_tbl(price_idx).pricing_attribute22,
1585 x_price_tbl(price_idx).pricing_attribute23,
1586 x_price_tbl(price_idx).pricing_attribute24,
1587 x_price_tbl(price_idx).pricing_attribute25,
1588 x_price_tbl(price_idx).pricing_attribute26,
1589 x_price_tbl(price_idx).pricing_attribute27,
1590 x_price_tbl(price_idx).pricing_attribute28,
1591 x_price_tbl(price_idx).pricing_attribute29,
1592 x_price_tbl(price_idx).pricing_attribute30,
1593 x_price_tbl(price_idx).pricing_attribute31,
1594 x_price_tbl(price_idx).pricing_attribute32,
1595 x_price_tbl(price_idx).pricing_attribute33,
1596 x_price_tbl(price_idx).pricing_attribute34,
1597 x_price_tbl(price_idx).pricing_attribute35,
1598 x_price_tbl(price_idx).pricing_attribute36,
1599 x_price_tbl(price_idx).pricing_attribute37,
1600 x_price_tbl(price_idx).pricing_attribute38,
1601 x_price_tbl(price_idx).pricing_attribute39,
1602 x_price_tbl(price_idx).pricing_attribute40,
1603 x_price_tbl(price_idx).pricing_attribute41,
1604 x_price_tbl(price_idx).pricing_attribute42,
1605 x_price_tbl(price_idx).pricing_attribute43,
1606 x_price_tbl(price_idx).pricing_attribute44,
1607 x_price_tbl(price_idx).pricing_attribute45,
1608 x_price_tbl(price_idx).pricing_attribute46,
1609 x_price_tbl(price_idx).pricing_attribute47,
1610 x_price_tbl(price_idx).pricing_attribute48,
1611 x_price_tbl(price_idx).pricing_attribute49,
1612 x_price_tbl(price_idx).pricing_attribute50,
1613 x_price_tbl(price_idx).pricing_attribute51,
1614 x_price_tbl(price_idx).pricing_attribute52,
1615 x_price_tbl(price_idx).pricing_attribute53,
1616 x_price_tbl(price_idx).pricing_attribute54,
1617 x_price_tbl(price_idx).pricing_attribute55,
1618 x_price_tbl(price_idx).pricing_attribute56,
1619 x_price_tbl(price_idx).pricing_attribute57,
1620 x_price_tbl(price_idx).pricing_attribute58,
1621 x_price_tbl(price_idx).pricing_attribute59,
1622 x_price_tbl(price_idx).pricing_attribute60,
1623 x_price_tbl(price_idx).pricing_attribute61,
1624 x_price_tbl(price_idx).pricing_attribute62,
1625 x_price_tbl(price_idx).pricing_attribute63,
1626 x_price_tbl(price_idx).pricing_attribute64,
1627 x_price_tbl(price_idx).pricing_attribute65,
1628 x_price_tbl(price_idx).pricing_attribute66,
1629 x_price_tbl(price_idx).pricing_attribute67,
1630 x_price_tbl(price_idx).pricing_attribute68,
1631 x_price_tbl(price_idx).pricing_attribute69,
1632 x_price_tbl(price_idx).pricing_attribute70,
1633 x_price_tbl(price_idx).pricing_attribute71,
1634 x_price_tbl(price_idx).pricing_attribute72,
1635 x_price_tbl(price_idx).pricing_attribute73,
1636 x_price_tbl(price_idx).pricing_attribute74,
1637 x_price_tbl(price_idx).pricing_attribute75,
1638 x_price_tbl(price_idx).pricing_attribute76,
1639 x_price_tbl(price_idx).pricing_attribute77,
1640 x_price_tbl(price_idx).pricing_attribute78,
1641 x_price_tbl(price_idx).pricing_attribute79,
1642 x_price_tbl(price_idx).pricing_attribute80,
1643 x_price_tbl(price_idx).pricing_attribute81,
1644 x_price_tbl(price_idx).pricing_attribute82,
1645 x_price_tbl(price_idx).pricing_attribute83,
1646 x_price_tbl(price_idx).pricing_attribute84,
1647 x_price_tbl(price_idx).pricing_attribute85,
1648 x_price_tbl(price_idx).pricing_attribute86,
1649 x_price_tbl(price_idx).pricing_attribute87,
1650 x_price_tbl(price_idx).pricing_attribute88,
1651 x_price_tbl(price_idx).pricing_attribute89,
1652 x_price_tbl(price_idx).pricing_attribute90,
1653 x_price_tbl(price_idx).pricing_attribute91,
1654 x_price_tbl(price_idx).pricing_attribute92,
1655 x_price_tbl(price_idx).pricing_attribute93,
1656 x_price_tbl(price_idx).pricing_attribute94,
1657 x_price_tbl(price_idx).pricing_attribute95,
1658 x_price_tbl(price_idx).pricing_attribute96,
1659 x_price_tbl(price_idx).pricing_attribute97,
1660 x_price_tbl(price_idx).pricing_attribute98,
1661 x_price_tbl(price_idx).pricing_attribute99,
1662 x_price_tbl(price_idx).pricing_attribute100,
1663 x_price_tbl(price_idx).CONTEXT,
1664 x_price_tbl(price_idx).attribute1,
1665 x_price_tbl(price_idx).attribute2,
1666 x_price_tbl(price_idx).attribute3,
1667 x_price_tbl(price_idx).attribute4,
1668 x_price_tbl(price_idx).attribute5,
1669 x_price_tbl(price_idx).attribute6,
1670 x_price_tbl(price_idx).attribute7,
1671 x_price_tbl(price_idx).attribute8,
1672 x_price_tbl(price_idx).attribute9,
1673 x_price_tbl(price_idx).attribute10,
1674 x_price_tbl(price_idx).attribute11,
1675 x_price_tbl(price_idx).attribute12,
1676 x_price_tbl(price_idx).attribute13,
1677 x_price_tbl(price_idx).attribute14,
1678 x_price_tbl(price_idx).attribute15,
1679 x_price_tbl(price_idx).object_version_number
1680 FROM DUAL;
1681 END IF;
1682
1683
1684 ---Populate Org Assignments
1685 IF inst_intf_rec.instance_ou_id IS NOT NULL
1686 THEN
1687 x_org_assign_tbl(org_idx).instance_ou_id :=
1688 inst_intf_rec.instance_ou_id;
1689 END IF;
1690
1691 IF ( inst_intf_rec.operating_unit IS NOT NULL
1692 OR inst_intf_rec.ou_relation_type IS NOT NULL
1693 OR inst_intf_rec.ou_end_date IS NOT NULL
1694 )
1695 THEN
1696 SELECT DECODE (
1697 inst_intf_rec.instance_id,
1698 NULL, l_miss_num,
1699 l_miss_num, NULL,
1700 inst_intf_rec.instance_id
1701 ),
1702 DECODE (
1703 inst_intf_rec.operating_unit,
1704 NULL, l_miss_num,
1705 l_miss_num, NULL,
1706 inst_intf_rec.operating_unit
1707 ),
1708 DECODE (
1709 inst_intf_rec.ou_relation_type,
1710 NULL, l_miss_char,
1711 l_miss_char, NULL,
1712 inst_intf_rec.ou_relation_type
1713 ),
1714 l_miss_date, -- ACTIVE_START_DATE
1715 DECODE (
1716 inst_intf_rec.ou_end_date,
1717 NULL, l_miss_date,
1718 l_miss_date, NULL,
1719 inst_intf_rec.ou_end_date
1720 ),
1721 l_miss_char, -- CONTEXT
1722 l_miss_char, -- ATTRIBUTE1
1723 l_miss_char, -- ATTRIBUTE2
1724 l_miss_char, -- ATTRIBUTE3
1725 l_miss_char, -- ATTRIBUTE4
1726 l_miss_char, -- ATTRIBUTE5
1727 l_miss_char, -- ATTRIBUTE6
1728 l_miss_char, -- ATTRIBUTE7
1729 l_miss_char, -- ATTRIBUTE8
1730 l_miss_char, -- ATTRIBUTE9
1731 l_miss_char, -- ATTRIBUTE10
1732 l_miss_char, -- ATTRIBUTE11
1733 l_miss_char, -- ATTRIBUTE12
1734 l_miss_char, -- ATTRIBUTE13
1735 l_miss_char, -- ATTRIBUTE14
1736 l_miss_char, -- ATTRIBUTE15
1737 l_ou_object_version -- OBJECT_VERSION_NUMBER
1738 INTO x_org_assign_tbl(org_idx).instance_id,
1739 x_org_assign_tbl(org_idx).operating_unit_id,
1740 x_org_assign_tbl(org_idx).relationship_type_code,
1741 x_org_assign_tbl(org_idx).active_start_date,
1742 x_org_assign_tbl(org_idx).active_end_date,
1743 x_org_assign_tbl(org_idx).CONTEXT,
1744 x_org_assign_tbl(org_idx).attribute1,
1745 x_org_assign_tbl(org_idx).attribute2,
1746 x_org_assign_tbl(org_idx).attribute3,
1747 x_org_assign_tbl(org_idx).attribute4,
1748 x_org_assign_tbl(org_idx).attribute5,
1749 x_org_assign_tbl(org_idx).attribute6,
1750 x_org_assign_tbl(org_idx).attribute7,
1751 x_org_assign_tbl(org_idx).attribute8,
1752 x_org_assign_tbl(org_idx).attribute9,
1753 x_org_assign_tbl(org_idx).attribute10,
1754 x_org_assign_tbl(org_idx).attribute11,
1755 x_org_assign_tbl(org_idx).attribute12,
1756 x_org_assign_tbl(org_idx).attribute13,
1757 x_org_assign_tbl(org_idx).attribute14,
1758 x_org_assign_tbl(org_idx).attribute15,
1759 x_org_assign_tbl(org_idx).object_version_number
1760 FROM DUAL;
1761 END IF; --Org Assignments
1762
1763
1764 ----Populate Party
1765 FOR party_intf_rec IN party_intf_cur (inst_intf_rec.inst_interface_id)
1766 LOOP
1767 prty_idx := prty_idx
1768 + 1;
1769
1770 x_party_tbl (prty_idx).instance_party_id :=
1771 party_intf_rec.instance_party_id;
1772 x_party_tbl (prty_idx).instance_id := inst_intf_rec.instance_id;
1773
1774 OPEN ip_obj_ver_cur (party_intf_rec.instance_party_id);
1775 FETCH ip_obj_ver_cur INTO l_party_object_version,
1776 l_last_update_date ;
1777 CLOSE ip_obj_ver_cur;
1778 IF l_last_update_date > inst_intf_rec.source_transaction_date
1779 THEN
1780 fnd_message.set_name('CSI','CSI_INTERFACE_RESTRICTION');
1781 fnd_message.set_token('instance_id',inst_intf_rec.instance_id);
1782 l_error_message := fnd_message.get;
1783 RAISE e_restriction;
1784 END IF;
1785 SELECT DECODE (
1786 party_intf_rec.party_source_table,
1787 NULL, l_miss_char,
1788 l_miss_char, NULL,
1789 party_intf_rec.party_source_table
1790 ),
1791 DECODE (
1792 party_intf_rec.party_id,
1793 NULL, l_miss_num,
1794 l_miss_num, NULL,
1795 party_intf_rec.party_id
1796 ),
1797 DECODE (
1798 party_intf_rec.party_relationship_type_code,
1799 NULL, l_miss_char,
1800 l_miss_char, NULL,
1801 party_intf_rec.party_relationship_type_code
1802 ),
1803 DECODE (
1804 party_intf_rec.contact_flag,
1805 NULL, l_miss_char,
1806 l_miss_char, NULL,
1807 party_intf_rec.contact_flag
1808 ),
1809 DECODE (
1810 party_intf_rec.contact_ip_id,
1811 NULL, l_miss_num,
1812 l_miss_num, NULL,
1813 party_intf_rec.contact_ip_id
1814 ),
1815 DECODE (
1816 party_intf_rec.party_start_date,
1817 NULL, l_miss_date,
1818 l_miss_date, NULL,
1819 party_intf_rec.party_start_date
1820 ),
1821 DECODE (
1822 party_intf_rec.party_end_date,
1823 NULL, l_miss_date,
1824 l_miss_date, NULL,
1825 party_intf_rec.party_end_date
1826 ),
1827 DECODE (
1828 party_intf_rec.party_context,
1829 NULL, l_miss_char,
1830 l_miss_char, NULL,
1831 party_intf_rec.party_context
1832 ),
1833 DECODE (
1834 party_intf_rec.party_attribute1,
1835 NULL, l_miss_char,
1836 l_miss_char, NULL,
1837 party_intf_rec.party_attribute1
1838 ),
1839 DECODE (
1840 party_intf_rec.party_attribute2,
1841 NULL, l_miss_char,
1842 l_miss_char, NULL,
1843 party_intf_rec.party_attribute2
1844 ),
1845 DECODE (
1846 party_intf_rec.party_attribute3,
1847 NULL, l_miss_char,
1848 l_miss_char, NULL,
1849 party_intf_rec.party_attribute3
1850 ),
1851 DECODE (
1852 party_intf_rec.party_attribute4,
1853 NULL, l_miss_char,
1854 l_miss_char, NULL,
1855 party_intf_rec.party_attribute4
1856 ),
1857 DECODE (
1858 party_intf_rec.party_attribute5,
1859 NULL, l_miss_char,
1860 l_miss_char, NULL,
1861 party_intf_rec.party_attribute5
1862 ),
1863 DECODE (
1864 party_intf_rec.party_attribute6,
1865 NULL, l_miss_char,
1866 l_miss_char, NULL,
1867 party_intf_rec.party_attribute6
1868 ),
1869 DECODE (
1870 party_intf_rec.party_attribute7,
1871 NULL, l_miss_char,
1872 l_miss_char, NULL,
1873 party_intf_rec.party_attribute7
1874 ),
1875 DECODE (
1876 party_intf_rec.party_attribute8,
1877 NULL, l_miss_char,
1878 l_miss_char, NULL,
1879 party_intf_rec.party_attribute8
1880 ),
1881 DECODE (
1882 party_intf_rec.party_attribute9,
1883 NULL, l_miss_char,
1884 l_miss_char, NULL,
1885 party_intf_rec.party_attribute9
1886 ),
1887 DECODE (
1888 party_intf_rec.party_attribute10,
1889 NULL, l_miss_char,
1890 l_miss_char, NULL,
1891 party_intf_rec.party_attribute10
1892 ),
1893 DECODE (
1894 party_intf_rec.party_attribute11,
1895 NULL, l_miss_char,
1896 l_miss_char, NULL,
1897 party_intf_rec.party_attribute11
1898 ),
1899 DECODE (
1900 party_intf_rec.party_attribute12,
1901 NULL, l_miss_char,
1902 l_miss_char, NULL,
1903 party_intf_rec.party_attribute12
1904 ),
1905 DECODE (
1906 party_intf_rec.party_attribute13,
1907 NULL, l_miss_char,
1908 l_miss_char, NULL,
1909 party_intf_rec.party_attribute13
1910 ),
1911 DECODE (
1912 party_intf_rec.party_attribute14,
1913 NULL, l_miss_char,
1914 l_miss_char, NULL,
1915 party_intf_rec.party_attribute14
1916 ),
1917 DECODE (
1918 party_intf_rec.party_attribute15,
1919 NULL, l_miss_char,
1920 l_miss_char, NULL,
1921 party_intf_rec.party_attribute15
1922 ),
1923 l_party_object_version -- OBJECT_VERSION_NUMBER
1924 INTO x_party_tbl (prty_idx).party_source_table,
1925 x_party_tbl (prty_idx).party_id,
1926 x_party_tbl (prty_idx).relationship_type_code,
1927 x_party_tbl (prty_idx).contact_flag,
1928 x_party_tbl (prty_idx).contact_ip_id,
1929 x_party_tbl (prty_idx).active_start_date,
1930 x_party_tbl (prty_idx).active_end_date,
1931 x_party_tbl (prty_idx).CONTEXT,
1932 x_party_tbl (prty_idx).attribute1,
1933 x_party_tbl (prty_idx).attribute2,
1934 x_party_tbl (prty_idx).attribute3,
1935 x_party_tbl (prty_idx).attribute4,
1936 x_party_tbl (prty_idx).attribute5,
1937 x_party_tbl (prty_idx).attribute6,
1938 x_party_tbl (prty_idx).attribute7,
1939 x_party_tbl (prty_idx).attribute8,
1940 x_party_tbl (prty_idx).attribute9,
1941 x_party_tbl (prty_idx).attribute10,
1942 x_party_tbl (prty_idx).attribute11,
1943 x_party_tbl (prty_idx).attribute12,
1944 x_party_tbl (prty_idx).attribute13,
1945 x_party_tbl (prty_idx).attribute14,
1946 x_party_tbl (prty_idx).attribute15,
1947 x_party_tbl (prty_idx).object_version_number
1948 FROM DUAL;
1949
1950 x_party_tbl (prty_idx).parent_tbl_index:=inst_idx;
1951 IF (x_party_tbl(prty_idx).contact_flag = 'Y'
1952 AND x_party_tbl(prty_idx).contact_ip_id IS NULL)
1953 THEN
1954 FOR i IN 1 .. prty_idx LOOP
1955 IF (x_party_tbl(i).party_id = party_intf_rec.contact_party_id AND x_party_tbl(i).relationship_type_code = party_intf_rec.contact_party_rel_type)
1956 THEN x_party_tbl(prty_idx).contact_parent_tbl_index := i;
1957 END IF;
1958 EXIT;
1959 END LOOP;
1960 END IF;
1961
1962 ---Populate Party Account1
1963 IF party_intf_rec.party_account1_id IS NOT NULL
1964 THEN -- Put record in Table
1965 ptyacc_idx := ptyacc_idx
1966 + 1;
1967 x_account_tbl (ptyacc_idx).ip_account_id :=
1968 party_intf_rec.ip_account1_id;
1969 x_account_tbl (ptyacc_idx).instance_party_id :=
1970 party_intf_rec.instance_party_id;
1971 x_account_tbl (ptyacc_idx).parent_tbl_index := prty_idx;
1972 OPEN ipa_obj_ver_cur (party_intf_rec.ip_account1_id);
1973 FETCH ipa_obj_ver_cur INTO l_ipa1_object_version,
1974 l_last_update_date;
1975 CLOSE ipa_obj_ver_cur;
1976 IF l_last_update_date > inst_intf_rec.source_transaction_date
1977 THEN
1978 fnd_message.set_name('CSI','CSI_INTERFACE_RESTRICTION');
1979 fnd_message.set_token('instance_id',inst_intf_rec.instance_id);
1980 l_error_message := fnd_message.get;
1981 RAISE e_restriction;
1982 END IF;
1983 x_account_tbl (ptyacc_idx).object_version_number :=
1984 l_ipa1_object_version;
1985
1986 SELECT DECODE (
1987 party_intf_rec.party_account1_id,
1988 NULL, l_miss_num,
1989 l_miss_num, NULL,
1990 party_intf_rec.party_account1_id
1991 ),
1992 DECODE (
1993 party_intf_rec.acct1_relationship_type_code,
1994 NULL, l_miss_char,
1995 l_miss_char, NULL,
1996 party_intf_rec.acct1_relationship_type_code
1997 ),
1998 DECODE (
1999 party_intf_rec.bill_to_address1,
2000 NULL, l_miss_num,
2001 l_miss_num, NULL,
2002 party_intf_rec.bill_to_address1
2003 ),
2004 DECODE (
2005 party_intf_rec.ship_to_address1,
2006 NULL, l_miss_num,
2007 l_miss_num, NULL,
2008 party_intf_rec.ship_to_address1
2009 ),
2010 l_miss_date, -- ACTIVE_START_DATE
2011 DECODE (
2012 party_intf_rec.party_acct1_end_date,
2013 NULL, l_miss_date,
2014 l_miss_date, NULL,
2015 party_intf_rec.party_acct1_end_date
2016 ),
2017 DECODE (
2018 party_intf_rec.account1_context,
2019 NULL, l_miss_char,
2020 l_miss_char, NULL,
2021 party_intf_rec.account1_context
2022 ),
2023 DECODE (
2024 party_intf_rec.account1_attribute1,
2025 NULL, l_miss_char,
2026 l_miss_char, NULL,
2027 party_intf_rec.account1_attribute1
2028 ),
2029 DECODE (
2030 party_intf_rec.account1_attribute2,
2031 NULL, l_miss_char,
2032 l_miss_char, NULL,
2033 party_intf_rec.account1_attribute2
2034 ),
2035 DECODE (
2036 party_intf_rec.account1_attribute3,
2037 NULL, l_miss_char,
2038 l_miss_char, NULL,
2039 party_intf_rec.account1_attribute3
2040 ),
2041 DECODE (
2042 party_intf_rec.account1_attribute4,
2043 NULL, l_miss_char,
2044 l_miss_char, NULL,
2045 party_intf_rec.account1_attribute4
2046 ),
2047 DECODE (
2048 party_intf_rec.account1_attribute5,
2049 NULL, l_miss_char,
2050 l_miss_char, NULL,
2051 party_intf_rec.account1_attribute5
2052 ),
2053 DECODE (
2054 party_intf_rec.account1_attribute6,
2055 NULL, l_miss_char,
2056 l_miss_char, NULL,
2057 party_intf_rec.account1_attribute6
2058 ),
2059 DECODE (
2060 party_intf_rec.account1_attribute7,
2061 NULL, l_miss_char,
2062 l_miss_char, NULL,
2063 party_intf_rec.account1_attribute7
2064 ),
2065 DECODE (
2066 party_intf_rec.account1_attribute8,
2067 NULL, l_miss_char,
2068 l_miss_char, NULL,
2069 party_intf_rec.account1_attribute8
2070 ),
2071 DECODE (
2072 party_intf_rec.account1_attribute9,
2073 NULL, l_miss_char,
2074 l_miss_char, NULL,
2075 party_intf_rec.account1_attribute9
2076 ),
2077 DECODE (
2078 party_intf_rec.account1_attribute10,
2079 NULL, l_miss_char,
2080 l_miss_char, NULL,
2081 party_intf_rec.account1_attribute10
2082 ),
2083 DECODE (
2084 party_intf_rec.account1_attribute11,
2085 NULL, l_miss_char,
2086 l_miss_char, NULL,
2087 party_intf_rec.account1_attribute11
2088 ),
2089 DECODE (
2090 party_intf_rec.account1_attribute12,
2091 NULL, l_miss_char,
2092 l_miss_char, NULL,
2093 party_intf_rec.account1_attribute12
2094 ),
2095 DECODE (
2096 party_intf_rec.account1_attribute13,
2097 NULL, l_miss_char,
2098 l_miss_char, NULL,
2099 party_intf_rec.account1_attribute13
2100 ),
2101 DECODE (
2102 party_intf_rec.account1_attribute14,
2103 NULL, l_miss_char,
2104 l_miss_char, NULL,
2105 party_intf_rec.account1_attribute14
2106 ),
2107 DECODE (
2108 party_intf_rec.account1_attribute15,
2109 NULL, l_miss_char,
2110 l_miss_char, NULL,
2111 party_intf_rec.account1_attribute15
2112 ),
2113 fnd_api.g_false, --CALL_CONTRACTS
2114 l_miss_num --VLD_ORGANIZATION_ID
2115 INTO x_account_tbl (ptyacc_idx).party_account_id,
2116 x_account_tbl (ptyacc_idx).relationship_type_code,
2117 x_account_tbl (ptyacc_idx).bill_to_address,
2118 x_account_tbl (ptyacc_idx).ship_to_address,
2119 x_account_tbl (ptyacc_idx).active_start_date,
2120 x_account_tbl (ptyacc_idx).active_end_date,
2121 x_account_tbl (ptyacc_idx).CONTEXT,
2122 x_account_tbl (ptyacc_idx).attribute1,
2123 x_account_tbl (ptyacc_idx).attribute2,
2124 x_account_tbl (ptyacc_idx).attribute3,
2125 x_account_tbl (ptyacc_idx).attribute4,
2126 x_account_tbl (ptyacc_idx).attribute5,
2127 x_account_tbl (ptyacc_idx).attribute6,
2128 x_account_tbl (ptyacc_idx).attribute7,
2129 x_account_tbl (ptyacc_idx).attribute8,
2130 x_account_tbl (ptyacc_idx).attribute9,
2131 x_account_tbl (ptyacc_idx).attribute10,
2132 x_account_tbl (ptyacc_idx).attribute11,
2133 x_account_tbl (ptyacc_idx).attribute12,
2134 x_account_tbl (ptyacc_idx).attribute13,
2135 x_account_tbl (ptyacc_idx).attribute14,
2136 x_account_tbl (ptyacc_idx).attribute15,
2137 x_account_tbl (ptyacc_idx).call_contracts,
2138 x_account_tbl (ptyacc_idx).vld_organization_id
2139 FROM DUAL;
2140 END IF; ---party_account1
2141
2142 ---Populate Party Account2
2143 IF party_intf_rec.party_account2_id IS NOT NULL
2144 THEN -- Put record in Table
2145 ptyacc_idx := ptyacc_idx
2146 + 1;
2147 x_account_tbl (ptyacc_idx).ip_account_id :=
2148 party_intf_rec.ip_account2_id;
2149 x_account_tbl (ptyacc_idx).instance_party_id :=
2150 party_intf_rec.instance_party_id;
2151 x_account_tbl (ptyacc_idx).parent_tbl_index := prty_idx;
2152 OPEN ipa_obj_ver_cur (party_intf_rec.ip_account2_id);
2153 FETCH ipa_obj_ver_cur INTO l_ipa2_object_version,
2154 l_last_update_date;
2155 CLOSE ipa_obj_ver_cur;
2156 IF l_last_update_date > inst_intf_rec.source_transaction_date
2157 THEN
2158 fnd_message.set_name('CSI','CSI_INTERFACE_RESTRICTION');
2159 fnd_message.set_token('instance_id',inst_intf_rec.instance_id);
2160 l_error_message := fnd_message.get;
2161 RAISE e_restriction;
2162 END IF;
2163 x_account_tbl (ptyacc_idx).object_version_number :=
2164 l_ipa2_object_version;
2165
2166 SELECT DECODE (
2167 party_intf_rec.party_account2_id,
2168 NULL, l_miss_num,
2169 l_miss_num, NULL,
2170 party_intf_rec.party_account2_id
2171 ),
2172 DECODE (
2173 party_intf_rec.acct2_relationship_type_code,
2174 NULL, l_miss_char,
2175 l_miss_char, NULL,
2176 party_intf_rec.acct2_relationship_type_code
2177 ),
2178 DECODE (
2179 party_intf_rec.bill_to_address2,
2180 NULL, l_miss_num,
2181 l_miss_num, NULL,
2182 party_intf_rec.bill_to_address2
2183 ),
2184 DECODE (
2185 party_intf_rec.ship_to_address2,
2186 NULL, l_miss_num,
2187 l_miss_num, NULL,
2188 party_intf_rec.ship_to_address2
2189 ),
2190 l_miss_date, -- ACTIVE_START_DATE
2191 DECODE (
2192 party_intf_rec.party_acct2_end_date,
2193 NULL, l_miss_date,
2194 l_miss_date, NULL,
2195 party_intf_rec.party_acct2_end_date
2196 ),
2197 DECODE (
2198 party_intf_rec.account2_context,
2199 NULL, l_miss_char,
2200 l_miss_char, NULL,
2201 party_intf_rec.account2_context
2202 ),
2203 DECODE (
2204 party_intf_rec.account2_attribute1,
2205 NULL, l_miss_char,
2206 l_miss_char, NULL,
2207 party_intf_rec.account2_attribute1
2208 ),
2209 DECODE (
2210 party_intf_rec.account2_attribute2,
2211 NULL, l_miss_char,
2212 l_miss_char, NULL,
2213 party_intf_rec.account2_attribute2
2214 ),
2215 DECODE (
2216 party_intf_rec.account2_attribute3,
2217 NULL, l_miss_char,
2218 l_miss_char, NULL,
2219 party_intf_rec.account2_attribute3
2220 ),
2221 DECODE (
2222 party_intf_rec.account2_attribute4,
2223 NULL, l_miss_char,
2224 l_miss_char, NULL,
2225 party_intf_rec.account2_attribute4
2226 ),
2227 DECODE (
2228 party_intf_rec.account2_attribute5,
2229 NULL, l_miss_char,
2230 l_miss_char, NULL,
2231 party_intf_rec.account2_attribute5
2232 ),
2233 DECODE (
2234 party_intf_rec.account2_attribute6,
2235 NULL, l_miss_char,
2236 l_miss_char, NULL,
2237 party_intf_rec.account2_attribute6
2238 ),
2239 DECODE (
2240 party_intf_rec.account2_attribute7,
2241 NULL, l_miss_char,
2242 l_miss_char, NULL,
2243 party_intf_rec.account2_attribute7
2244 ),
2245 DECODE (
2246 party_intf_rec.account2_attribute8,
2247 NULL, l_miss_char,
2248 l_miss_char, NULL,
2249 party_intf_rec.account2_attribute8
2250 ),
2251 DECODE (
2252 party_intf_rec.account2_attribute9,
2253 NULL, l_miss_char,
2254 l_miss_char, NULL,
2255 party_intf_rec.account2_attribute9
2256 ),
2257 DECODE (
2258 party_intf_rec.account2_attribute10,
2259 NULL, l_miss_char,
2260 l_miss_char, NULL,
2261 party_intf_rec.account2_attribute10
2262 ),
2263 DECODE (
2264 party_intf_rec.account2_attribute11,
2265 NULL, l_miss_char,
2266 l_miss_char, NULL,
2267 party_intf_rec.account2_attribute11
2268 ),
2269 DECODE (
2270 party_intf_rec.account2_attribute12,
2271 NULL, l_miss_char,
2272 l_miss_char, NULL,
2273 party_intf_rec.account2_attribute12
2274 ),
2275 DECODE (
2276 party_intf_rec.account2_attribute13,
2277 NULL, l_miss_char,
2278 l_miss_char, NULL,
2279 party_intf_rec.account2_attribute13
2280 ),
2281 DECODE (
2282 party_intf_rec.account2_attribute14,
2283 NULL, l_miss_char,
2284 l_miss_char, NULL,
2285 party_intf_rec.account2_attribute14
2286 ),
2287 DECODE (
2288 party_intf_rec.account2_attribute15,
2289 NULL, l_miss_char,
2290 l_miss_char, NULL,
2291 party_intf_rec.account2_attribute15
2292 ),
2293 fnd_api.g_false, --CALL_CONTRACTS
2294 l_miss_num --VLD_ORGANIZATION_ID
2295 INTO x_account_tbl (ptyacc_idx).party_account_id,
2296 x_account_tbl (ptyacc_idx).relationship_type_code,
2297 x_account_tbl (ptyacc_idx).bill_to_address,
2298 x_account_tbl (ptyacc_idx).ship_to_address,
2299 x_account_tbl (ptyacc_idx).active_start_date,
2300 x_account_tbl (ptyacc_idx).active_end_date,
2301 x_account_tbl (ptyacc_idx).CONTEXT,
2302 x_account_tbl (ptyacc_idx).attribute1,
2303 x_account_tbl (ptyacc_idx).attribute2,
2304 x_account_tbl (ptyacc_idx).attribute3,
2305 x_account_tbl (ptyacc_idx).attribute4,
2306 x_account_tbl (ptyacc_idx).attribute5,
2307 x_account_tbl (ptyacc_idx).attribute6,
2308 x_account_tbl (ptyacc_idx).attribute7,
2309 x_account_tbl (ptyacc_idx).attribute8,
2310 x_account_tbl (ptyacc_idx).attribute9,
2311 x_account_tbl (ptyacc_idx).attribute10,
2312 x_account_tbl (ptyacc_idx).attribute11,
2313 x_account_tbl (ptyacc_idx).attribute12,
2314 x_account_tbl (ptyacc_idx).attribute13,
2315 x_account_tbl (ptyacc_idx).attribute14,
2316 x_account_tbl (ptyacc_idx).attribute15,
2317 x_account_tbl (ptyacc_idx).call_contracts,
2318 x_account_tbl (ptyacc_idx).vld_organization_id
2319 FROM DUAL;
2320 END IF; ---party_account2
2321
2322 ---Populate Party Account3
2323 IF party_intf_rec.party_account3_id IS NOT NULL
2324 THEN -- Put record in Table
2325 ptyacc_idx := ptyacc_idx
2326 + 1;
2327 x_account_tbl (ptyacc_idx).ip_account_id :=
2328 party_intf_rec.ip_account3_id;
2329 x_account_tbl (ptyacc_idx).instance_party_id :=
2330 party_intf_rec.instance_party_id;
2331 x_account_tbl (ptyacc_idx).parent_tbl_index := prty_idx;
2332 OPEN ipa_obj_ver_cur (party_intf_rec.ip_account3_id);
2333 FETCH ipa_obj_ver_cur INTO l_ipa3_object_version,
2334 l_last_update_date;
2335 CLOSE ipa_obj_ver_cur;
2336 IF l_last_update_date > inst_intf_rec.source_transaction_date
2337 THEN
2338 fnd_message.set_name('CSI','CSI_INTERFACE_RESTRICTION');
2339 fnd_message.set_token('instance_id',inst_intf_rec.instance_id);
2340 l_error_message := fnd_message.get;
2341 RAISE e_restriction;
2342 END IF;
2343 x_account_tbl (ptyacc_idx).object_version_number :=
2344 l_ipa3_object_version;
2345
2346 SELECT DECODE (
2347 party_intf_rec.party_account3_id,
2348 NULL, l_miss_num,
2349 l_miss_num, NULL,
2350 party_intf_rec.party_account3_id
2351 ),
2352 DECODE (
2353 party_intf_rec.acct3_relationship_type_code,
2354 NULL, l_miss_char,
2355 l_miss_char, NULL,
2356 party_intf_rec.acct3_relationship_type_code
2357 ),
2358 DECODE (
2359 party_intf_rec.bill_to_address3,
2360 NULL, l_miss_num,
2361 l_miss_num, NULL,
2362 party_intf_rec.bill_to_address3
2363 ),
2364 DECODE (
2365 party_intf_rec.ship_to_address3,
2366 NULL, l_miss_num,
2367 l_miss_num, NULL,
2368 party_intf_rec.ship_to_address3
2369 ),
2370 l_miss_date, -- ACTIVE_START_DATE
2371 DECODE (
2372 party_intf_rec.party_acct3_end_date,
2373 NULL, l_miss_date,
2374 l_miss_date, NULL,
2375 party_intf_rec.party_acct3_end_date
2376 ),
2377 DECODE (
2378 party_intf_rec.account3_context,
2379 NULL, l_miss_char,
2380 l_miss_char, NULL,
2381 party_intf_rec.account3_context
2382 ),
2383 DECODE (
2384 party_intf_rec.account3_attribute1,
2385 NULL, l_miss_char,
2386 l_miss_char, NULL,
2387 party_intf_rec.account3_attribute1
2388 ),
2389 DECODE (
2390 party_intf_rec.account3_attribute2,
2391 NULL, l_miss_char,
2392 l_miss_char, NULL,
2393 party_intf_rec.account3_attribute2
2394 ),
2395 DECODE (
2396 party_intf_rec.account3_attribute3,
2397 NULL, l_miss_char,
2398 l_miss_char, NULL,
2399 party_intf_rec.account3_attribute3
2400 ),
2401 DECODE (
2402 party_intf_rec.account3_attribute4,
2403 NULL, l_miss_char,
2404 l_miss_char, NULL,
2405 party_intf_rec.account3_attribute4
2406 ),
2407 DECODE (
2408 party_intf_rec.account3_attribute5,
2409 NULL, l_miss_char,
2410 l_miss_char, NULL,
2411 party_intf_rec.account3_attribute5
2412 ),
2413 DECODE (
2414 party_intf_rec.account3_attribute6,
2415 NULL, l_miss_char,
2416 l_miss_char, NULL,
2417 party_intf_rec.account3_attribute6
2418 ),
2419 DECODE (
2420 party_intf_rec.account3_attribute7,
2421 NULL, l_miss_char,
2422 l_miss_char, NULL,
2423 party_intf_rec.account3_attribute7
2424 ),
2425 DECODE (
2426 party_intf_rec.account3_attribute8,
2427 NULL, l_miss_char,
2428 l_miss_char, NULL,
2429 party_intf_rec.account3_attribute8
2430 ),
2431 DECODE (
2432 party_intf_rec.account3_attribute9,
2433 NULL, l_miss_char,
2434 l_miss_char, NULL,
2435 party_intf_rec.account3_attribute9
2436 ),
2437 DECODE (
2438 party_intf_rec.account3_attribute10,
2439 NULL, l_miss_char,
2440 l_miss_char, NULL,
2441 party_intf_rec.account3_attribute10
2442 ),
2443 DECODE (
2444 party_intf_rec.account3_attribute11,
2445 NULL, l_miss_char,
2446 l_miss_char, NULL,
2447 party_intf_rec.account3_attribute11
2448 ),
2449 DECODE (
2450 party_intf_rec.account3_attribute12,
2451 NULL, l_miss_char,
2452 l_miss_char, NULL,
2453 party_intf_rec.account3_attribute12
2454 ),
2455 DECODE (
2456 party_intf_rec.account3_attribute13,
2457 NULL, l_miss_char,
2458 l_miss_char, NULL,
2459 party_intf_rec.account3_attribute13
2460 ),
2461 DECODE (
2462 party_intf_rec.account3_attribute14,
2463 NULL, l_miss_char,
2464 l_miss_char, NULL,
2465 party_intf_rec.account3_attribute14
2466 ),
2467 DECODE (
2468 party_intf_rec.account3_attribute15,
2469 NULL, l_miss_char,
2470 l_miss_char, NULL,
2471 party_intf_rec.account3_attribute15
2472 ),
2473 fnd_api.g_false, --CALL_CONTRACTS
2474 l_miss_num --VLD_ORGANIZATION_ID
2475 INTO x_account_tbl (ptyacc_idx).party_account_id,
2476 x_account_tbl (ptyacc_idx).relationship_type_code,
2477 x_account_tbl (ptyacc_idx).bill_to_address,
2478 x_account_tbl (ptyacc_idx).ship_to_address,
2479 x_account_tbl (ptyacc_idx).active_start_date,
2480 x_account_tbl (ptyacc_idx).active_end_date,
2481 x_account_tbl (ptyacc_idx).CONTEXT,
2482 x_account_tbl (ptyacc_idx).attribute1,
2483 x_account_tbl (ptyacc_idx).attribute2,
2484 x_account_tbl (ptyacc_idx).attribute3,
2485 x_account_tbl (ptyacc_idx).attribute4,
2486 x_account_tbl (ptyacc_idx).attribute5,
2487 x_account_tbl (ptyacc_idx).attribute6,
2488 x_account_tbl (ptyacc_idx).attribute7,
2489 x_account_tbl (ptyacc_idx).attribute8,
2490 x_account_tbl (ptyacc_idx).attribute9,
2491 x_account_tbl (ptyacc_idx).attribute10,
2492 x_account_tbl (ptyacc_idx).attribute11,
2493 x_account_tbl (ptyacc_idx).attribute12,
2494 x_account_tbl (ptyacc_idx).attribute13,
2495 x_account_tbl (ptyacc_idx).attribute14,
2496 x_account_tbl (ptyacc_idx).attribute15,
2497 x_account_tbl (ptyacc_idx).call_contracts,
2498 x_account_tbl (ptyacc_idx).vld_organization_id
2499 FROM DUAL;
2500 END IF; ---party_account3
2501
2502 IF party_intf_rec.contact_party_id IS NOT NULL AND
2503 party_intf_rec.contact_party_id <> fnd_api.g_miss_num
2504 THEN
2505 prty_idx:=prty_idx + 1;
2506 x_party_tbl(prty_idx).instance_party_id:=fnd_api.g_miss_num;
2507 x_party_tbl(prty_idx).instance_id:=inst_intf_rec.instance_id;
2508 x_party_tbl(prty_idx).party_source_table:=party_intf_rec.party_source_table;
2509 x_party_tbl(prty_idx).party_id:=party_intf_rec.contact_party_id;
2510 x_party_tbl(prty_idx).relationship_type_code:=party_intf_rec.contact_party_rel_type;
2511 x_party_tbl(prty_idx).contact_flag:='Y';
2512 x_party_tbl(prty_idx).contact_parent_tbl_index:=prty_idx-1;
2513 x_party_tbl(prty_idx).parent_tbl_index:=inst_idx;
2514 x_party_tbl(prty_idx).contact_ip_id:=party_intf_rec.instance_party_id ;
2515 END IF;
2516
2517 END LOOP; ---party_intf_cur
2518
2519
2520 ---Populate Extended Attributes
2521
2522 FOR ext_attrib_intf_rec IN ext_attrib_intf_cur (inst_intf_rec.inst_interface_id)
2523 LOOP
2524 extatt_idx := extatt_idx
2525 + 1;
2526
2527 SELECT DECODE (
2528 inst_intf_rec.instance_id,
2529 NULL, l_miss_num,
2530 l_miss_num, NULL,
2531 inst_intf_rec.instance_id
2532 ),
2533 DECODE (
2534 ext_attrib_intf_rec.attribute_id,
2535 NULL, l_miss_num,
2536 l_miss_num, NULL,
2537 ext_attrib_intf_rec.attribute_id
2538 ),
2539 DECODE (
2540 ext_attrib_intf_rec.attribute_value_id,
2541 NULL, l_miss_num,
2542 l_miss_num, NULL,
2543 ext_attrib_intf_rec.attribute_value_id
2544 ),
2545 DECODE (
2546 ext_attrib_intf_rec.attribute_code,
2547 NULL, l_miss_char,
2548 l_miss_char, NULL,
2549 ext_attrib_intf_rec.attribute_code
2550 ),
2551 DECODE (
2552 ext_attrib_intf_rec.attribute_value,
2553 NULL, l_miss_char,
2554 l_miss_char, NULL,
2555 ext_attrib_intf_rec.attribute_value
2556 ),
2557 l_miss_date, -- ACTIVE_START_DATE
2558 DECODE (
2559 ext_attrib_intf_rec.ieav_end_date,
2560 NULL, l_miss_date,
2561 l_miss_date, NULL,
2562 ext_attrib_intf_rec.ieav_end_date
2563 ),
2564 l_miss_char,
2565 l_miss_char,
2566 l_miss_char,
2567 l_miss_char,
2568 l_miss_char,
2569 l_miss_char,
2570 l_miss_char,
2571 l_miss_char,
2572 l_miss_char,
2573 l_miss_char,
2574 l_miss_char,
2575 l_miss_char,
2576 l_miss_char,
2577 l_miss_char,
2578 l_miss_char,
2579 l_miss_char,
2580 DECODE (
2581 ext_attrib_intf_rec.ieav_object_ver_num,
2582 NULL, l_miss_num,
2583 l_miss_num, NULL,
2584 ext_attrib_intf_rec.ieav_object_ver_num
2585 )
2586 -- OBJECT_VERSION_NUMBER
2587 INTO x_ext_attrib_value_tbl (extatt_idx).instance_id,
2588 x_ext_attrib_value_tbl (extatt_idx).attribute_id,
2589 x_ext_attrib_value_tbl (extatt_idx).attribute_value_id,
2590 x_ext_attrib_value_tbl (extatt_idx).attribute_code,
2591 x_ext_attrib_value_tbl (extatt_idx).attribute_value,
2592 x_ext_attrib_value_tbl (extatt_idx).active_start_date,
2593 x_ext_attrib_value_tbl (extatt_idx).active_end_date,
2594 x_ext_attrib_value_tbl (extatt_idx).CONTEXT,
2595 x_ext_attrib_value_tbl (extatt_idx).attribute1,
2596 x_ext_attrib_value_tbl (extatt_idx).attribute2,
2597 x_ext_attrib_value_tbl (extatt_idx).attribute3,
2598 x_ext_attrib_value_tbl (extatt_idx).attribute4,
2599 x_ext_attrib_value_tbl (extatt_idx).attribute5,
2600 x_ext_attrib_value_tbl (extatt_idx).attribute6,
2601 x_ext_attrib_value_tbl (extatt_idx).attribute7,
2602 x_ext_attrib_value_tbl (extatt_idx).attribute8,
2603 x_ext_attrib_value_tbl (extatt_idx).attribute9,
2604 x_ext_attrib_value_tbl (extatt_idx).attribute10,
2605 x_ext_attrib_value_tbl (extatt_idx).attribute11,
2606 x_ext_attrib_value_tbl (extatt_idx).attribute12,
2607 x_ext_attrib_value_tbl (extatt_idx).attribute13,
2608 x_ext_attrib_value_tbl (extatt_idx).attribute14,
2609 x_ext_attrib_value_tbl (extatt_idx).attribute15,
2610 x_ext_attrib_value_tbl (extatt_idx).object_version_number
2611 FROM DUAL;
2612 END LOOP; ---ext_attrib_intf_cur
2613
2614 --bnarayan added for R12
2615 -- Populate asset records
2616
2617 FOR asset_attrib_intf_rec IN asset_attrib_intf_cur (inst_intf_rec.inst_interface_id, inst_intf_rec.instance_id)
2618 LOOP
2619
2620 IF asset_attrib_intf_rec.csia_instance_asset_id IS NULL THEN
2621 x_asset_assignment_tbl( asset_idx ).instance_asset_id := l_miss_num;
2622 ELSE
2623 x_asset_assignment_tbl( asset_idx ).instance_asset_id := asset_attrib_intf_rec.csia_instance_asset_id ;
2624 END IF;
2625
2626 IF inst_intf_rec.instance_id IS NULL THEN
2627 x_asset_assignment_tbl( asset_idx ).instance_id := l_miss_num;
2628 ELSE
2629 x_asset_assignment_tbl( asset_idx ).instance_id := inst_intf_rec.instance_id ;
2630 END IF;
2631
2632 IF asset_attrib_intf_rec.fa_asset_id IS NULL THEN
2633 x_asset_assignment_tbl( asset_idx ).fa_asset_id := l_miss_num;
2634 ELSE
2635 x_asset_assignment_tbl( asset_idx ).fa_asset_id := asset_attrib_intf_rec.fa_asset_id ;
2636 END IF;
2637
2638 IF asset_attrib_intf_rec.fa_book_type_code IS NULL THEN
2639 x_asset_assignment_tbl( asset_idx ).fa_book_type_code := l_miss_char;
2640 ELSE
2641 x_asset_assignment_tbl( asset_idx ).fa_book_type_code := asset_attrib_intf_rec.fa_book_type_code;
2642 END IF;
2643
2644 IF asset_attrib_intf_rec.fa_location_id IS NULL THEN
2645 x_asset_assignment_tbl( asset_idx ).fa_location_id :=l_miss_num;
2646 ELSE
2647 x_asset_assignment_tbl( asset_idx ).fa_location_id := asset_attrib_intf_rec.fa_location_id ;
2648 END IF;
2649
2650 IF asset_attrib_intf_rec.asset_quantity IS NULL THEN
2651 x_asset_assignment_tbl( asset_idx ).asset_quantity := l_miss_num;
2652 ELSE
2653 x_asset_assignment_tbl( asset_idx ).asset_quantity := asset_attrib_intf_rec.asset_quantity ;
2654 END IF;
2655
2656 IF asset_attrib_intf_rec.update_status IS NULL THEN
2657 x_asset_assignment_tbl( asset_idx ).update_status :=l_miss_char;
2658 ELSE
2659 x_asset_assignment_tbl( asset_idx ).update_status := asset_attrib_intf_rec.update_status ;
2660 END IF;
2661
2662 IF asset_attrib_intf_rec.active_start_date IS NULL THEN
2663 x_asset_assignment_tbl( asset_idx ).active_start_date := l_miss_date;
2664 ELSE
2665 x_asset_assignment_tbl( asset_idx ).active_start_date := asset_attrib_intf_rec.active_start_date ;
2666 END IF;
2667
2668 IF asset_attrib_intf_rec.active_end_date IS NULL THEN
2669 x_asset_assignment_tbl( asset_idx ).active_end_date := l_miss_date;
2670 ELSE
2671 x_asset_assignment_tbl( asset_idx ).active_end_date := asset_attrib_intf_rec.active_end_date;
2672 END IF;
2673
2674 x_asset_assignment_tbl( asset_idx ).fa_sync_flag :=asset_attrib_intf_rec.fa_sync_flag;
2675 x_asset_assignment_tbl( asset_idx ).object_version_number:=asset_attrib_intf_rec.asset_object_ver_num;
2676 x_asset_assignment_tbl( asset_idx ).parent_tbl_index :=inst_idx;
2677 asset_idx := asset_idx + 1; -- Increment asset index
2678
2679 END LOOP; --end of asset loop
2680 -- Start code addition for bug 6368180, section 2 of 2
2681 END IF; ---IF inst_intf_rec.instance_id <> fnd_api.g_miss_num
2682 -- End code addition for bug 6368180, section 2 of 2
2683 END LOOP; ---inst_intf_cur
2684 EXCEPTION
2685 WHEN e_restriction
2686 THEN
2687 x_return_status := fnd_api.g_ret_sts_error;
2688 x_error_message := l_error_message;
2689 WHEN OTHERS
2690 THEN
2691 x_return_status := fnd_api.g_ret_sts_unexp_error;
2692 x_error_message := SQLERRM;
2693
2694 END populate_recs;
2695
2696 END csi_ml_update_pvt;
2697