[Home] [Help]
PACKAGE BODY: APPS.CSI_ML_UPDATE_PVT
Source
1 PACKAGE BODY CSI_ML_UPDATE_PVT AS
2 -- $Header: csimupdb.pls 120.7.12020000.2 2012/08/24 06:47:34 rasraja 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,
364 ),
361 NULL, l_miss_date,
362 l_miss_date, NULL,
363 inst_intf_rec.actual_return_date
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,
502 inst_intf_rec.instance_attribute22,
499 inst_intf_rec.instance_attribute21
500 ),
501 DECODE (
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.external_reference,
641 ),
638 NULL, l_miss_char,
639 l_miss_char, NULL,
640 inst_intf_rec.external_reference
642 DECODE (
643 inst_intf_rec.operational_status_code,
644 NULL, l_miss_char,
645 l_miss_char, NULL,
646 inst_intf_rec.operational_status_code
647 )
648 INTO x_instance_tbl(inst_idx).inv_master_organization_id,
649 x_instance_tbl(inst_idx).vld_organization_id,
650 x_instance_tbl(inst_idx).inventory_item_id,
651 x_instance_tbl(inst_idx).location_type_code,
652 x_instance_tbl(inst_idx).location_id,
653 x_instance_tbl(inst_idx).inv_organization_id,
654 x_instance_tbl(inst_idx).inv_subinventory_name,
655 x_instance_tbl(inst_idx).inv_locator_id,
656 x_instance_tbl(inst_idx).lot_number,
657 x_instance_tbl(inst_idx).pa_project_id,
658 x_instance_tbl(inst_idx).pa_project_task_id,
659 x_instance_tbl(inst_idx).in_transit_order_line_id,
660 x_instance_tbl(inst_idx).wip_job_id,
661 x_instance_tbl(inst_idx).po_order_line_id,
662 x_instance_tbl(inst_idx).inventory_revision,
663 x_instance_tbl(inst_idx).serial_number,
664 x_instance_tbl(inst_idx).mfg_serial_number_flag,
665 x_instance_tbl(inst_idx).quantity,
666 x_instance_tbl(inst_idx).unit_of_measure,
667 x_instance_tbl(inst_idx).accounting_class_code,
668 x_instance_tbl(inst_idx).instance_condition_id,
669 x_instance_tbl(inst_idx).instance_status_id,
670 x_instance_tbl(inst_idx).customer_view_flag,
671 x_instance_tbl(inst_idx).merchant_view_flag,
672 x_instance_tbl(inst_idx).sellable_flag,
673 x_instance_tbl(inst_idx).system_id,
674 x_instance_tbl(inst_idx).instance_type_code,
675 x_instance_tbl(inst_idx).active_end_date,
676 x_instance_tbl(inst_idx).last_oe_order_line_id,
677 x_instance_tbl(inst_idx).last_oe_rma_line_id,
678 x_instance_tbl(inst_idx).last_po_po_line_id,
679 x_instance_tbl(inst_idx).last_oe_po_number,
680 x_instance_tbl(inst_idx).last_wip_job_id,
681 x_instance_tbl(inst_idx).last_pa_project_id,
682 x_instance_tbl(inst_idx).last_pa_task_id,
683 x_instance_tbl(inst_idx).last_oe_agreement_id,
684 x_instance_tbl(inst_idx).install_date,
685 x_instance_tbl(inst_idx).manually_created_flag,
686 x_instance_tbl(inst_idx).return_by_date,
687 x_instance_tbl(inst_idx).actual_return_date,
688 x_instance_tbl(inst_idx).creation_complete_flag,
689 x_instance_tbl(inst_idx).completeness_flag,
690 x_instance_tbl(inst_idx).version_label,
691 x_instance_tbl(inst_idx).version_label_description,
692 x_instance_tbl(inst_idx).CONTEXT,
693 x_instance_tbl(inst_idx).attribute1,
694 x_instance_tbl(inst_idx).attribute2,
695 x_instance_tbl(inst_idx).attribute3,
696 x_instance_tbl(inst_idx).attribute4,
697 x_instance_tbl(inst_idx).attribute5,
698 x_instance_tbl(inst_idx).attribute6,
699 x_instance_tbl(inst_idx).attribute7,
700 x_instance_tbl(inst_idx).attribute8,
701 x_instance_tbl(inst_idx).attribute9,
702 x_instance_tbl(inst_idx).attribute10,
703 x_instance_tbl(inst_idx).attribute11,
704 x_instance_tbl(inst_idx).attribute12,
705 x_instance_tbl(inst_idx).attribute13,
706 x_instance_tbl(inst_idx).attribute14,
707 x_instance_tbl(inst_idx).attribute15,
708 x_instance_tbl(inst_idx).attribute16,
709 x_instance_tbl(inst_idx).attribute17,
710 x_instance_tbl(inst_idx).attribute18,
711 x_instance_tbl(inst_idx).attribute19,
712 x_instance_tbl(inst_idx).attribute20,
713 x_instance_tbl(inst_idx).attribute21,
714 x_instance_tbl(inst_idx).attribute22,
715 x_instance_tbl(inst_idx).attribute23,
716 x_instance_tbl(inst_idx).attribute24,
717 x_instance_tbl(inst_idx).attribute25,
718 x_instance_tbl(inst_idx).attribute26,
719 x_instance_tbl(inst_idx).attribute27,
720 x_instance_tbl(inst_idx).attribute28,
721 x_instance_tbl(inst_idx).attribute29,
722 x_instance_tbl(inst_idx).attribute30,
723 x_instance_tbl(inst_idx).object_version_number,
724 x_instance_tbl(inst_idx).last_txn_line_detail_id,
725 x_instance_tbl(inst_idx).install_location_type_code,
726 x_instance_tbl(inst_idx).install_location_id,
727 x_instance_tbl(inst_idx).network_asset_flag,
728 x_instance_tbl(inst_idx).maintainable_flag,
729 x_instance_tbl(inst_idx).equipment_gen_object_id,
730 x_instance_tbl(inst_idx).asset_criticality_code,
731 x_instance_tbl(inst_idx).operational_log_flag,
732 x_instance_tbl(inst_idx).supplier_warranty_exp_date,
733 x_instance_tbl(inst_idx).instantiation_flag,
734 x_instance_tbl(inst_idx).category_id,
735 x_instance_tbl(inst_idx).instance_usage_code,
736 x_instance_tbl(inst_idx).check_for_instance_expiry,
737 x_instance_tbl(inst_idx).instance_description,
738 x_instance_tbl(inst_idx).external_reference,
739 x_instance_tbl(inst_idx).operational_status_code
740 FROM DUAL;
741
742 x_instance_tbl(inst_idx).INTERFACE_ID := inst_intf_rec.inst_interface_id;
743
744 ---Populate Pricing attribites
748 inst_intf_rec.pricing_attribute_id;
745 IF inst_intf_rec.pricing_attribute_id IS NOT NULL
746 THEN
747 x_price_tbl(price_idx).pricing_attribute_id :=
749 END IF;
750
751
752 IF ( inst_intf_rec.pricing_attribute1 IS NOT NULL
753 OR inst_intf_rec.pricing_attribute2 IS NOT NULL
754 OR inst_intf_rec.pricing_attribute3 IS NOT NULL
755 OR inst_intf_rec.pricing_attribute4 IS NOT NULL
756 OR inst_intf_rec.pricing_attribute5 IS NOT NULL
757 OR inst_intf_rec.pricing_attribute6 IS NOT NULL
758 OR inst_intf_rec.pricing_attribute7 IS NOT NULL
759 OR inst_intf_rec.pricing_attribute8 IS NOT NULL
760 OR inst_intf_rec.pricing_attribute9 IS NOT NULL
761 OR inst_intf_rec.pricing_attribute10 IS NOT NULL
762 OR inst_intf_rec.pricing_attribute11 IS NOT NULL
763 OR inst_intf_rec.pricing_attribute12 IS NOT NULL
764 OR inst_intf_rec.pricing_attribute13 IS NOT NULL
765 OR inst_intf_rec.pricing_attribute14 IS NOT NULL
766 OR inst_intf_rec.pricing_attribute15 IS NOT NULL
767 OR inst_intf_rec.pricing_attribute16 IS NOT NULL
768 OR inst_intf_rec.pricing_attribute17 IS NOT NULL
769 OR inst_intf_rec.pricing_attribute18 IS NOT NULL
770 OR inst_intf_rec.pricing_attribute19 IS NOT NULL
771 OR inst_intf_rec.pricing_attribute20 IS NOT NULL
772 OR inst_intf_rec.pricing_attribute21 IS NOT NULL
773 OR inst_intf_rec.pricing_attribute22 IS NOT NULL
774 OR inst_intf_rec.pricing_attribute23 IS NOT NULL
775 OR inst_intf_rec.pricing_attribute24 IS NOT NULL
776 OR inst_intf_rec.pricing_attribute25 IS NOT NULL
777 OR inst_intf_rec.pricing_attribute26 IS NOT NULL
778 OR inst_intf_rec.pricing_attribute27 IS NOT NULL
779 OR inst_intf_rec.pricing_attribute28 IS NOT NULL
780 OR inst_intf_rec.pricing_attribute29 IS NOT NULL
781 OR inst_intf_rec.pricing_attribute30 IS NOT NULL
782 OR inst_intf_rec.pricing_attribute31 IS NOT NULL
783 OR inst_intf_rec.pricing_attribute32 IS NOT NULL
784 OR inst_intf_rec.pricing_attribute33 IS NOT NULL
785 OR inst_intf_rec.pricing_attribute34 IS NOT NULL
786 OR inst_intf_rec.pricing_attribute35 IS NOT NULL
787 OR inst_intf_rec.pricing_attribute36 IS NOT NULL
788 OR inst_intf_rec.pricing_attribute37 IS NOT NULL
789 OR inst_intf_rec.pricing_attribute38 IS NOT NULL
790 OR inst_intf_rec.pricing_attribute39 IS NOT NULL
791 OR inst_intf_rec.pricing_attribute40 IS NOT NULL
792 OR inst_intf_rec.pricing_attribute41 IS NOT NULL
793 OR inst_intf_rec.pricing_attribute42 IS NOT NULL
794 OR inst_intf_rec.pricing_attribute43 IS NOT NULL
795 OR inst_intf_rec.pricing_attribute44 IS NOT NULL
796 OR inst_intf_rec.pricing_attribute45 IS NOT NULL
797 OR inst_intf_rec.pricing_attribute46 IS NOT NULL
798 OR inst_intf_rec.pricing_attribute47 IS NOT NULL
799 OR inst_intf_rec.pricing_attribute48 IS NOT NULL
800 OR inst_intf_rec.pricing_attribute49 IS NOT NULL
801 OR inst_intf_rec.pricing_attribute50 IS NOT NULL
802 OR inst_intf_rec.pricing_attribute51 IS NOT NULL
803 OR inst_intf_rec.pricing_attribute52 IS NOT NULL
804 OR inst_intf_rec.pricing_attribute53 IS NOT NULL
805 OR inst_intf_rec.pricing_attribute54 IS NOT NULL
806 OR inst_intf_rec.pricing_attribute55 IS NOT NULL
807 OR inst_intf_rec.pricing_attribute56 IS NOT NULL
808 OR inst_intf_rec.pricing_attribute57 IS NOT NULL
809 OR inst_intf_rec.pricing_attribute58 IS NOT NULL
810 OR inst_intf_rec.pricing_attribute59 IS NOT NULL
811 OR inst_intf_rec.pricing_attribute60 IS NOT NULL
812 OR inst_intf_rec.pricing_attribute61 IS NOT NULL
813 OR inst_intf_rec.pricing_attribute62 IS NOT NULL
814 OR inst_intf_rec.pricing_attribute63 IS NOT NULL
815 OR inst_intf_rec.pricing_attribute64 IS NOT NULL
816 OR inst_intf_rec.pricing_attribute65 IS NOT NULL
817 OR inst_intf_rec.pricing_attribute66 IS NOT NULL
818 OR inst_intf_rec.pricing_attribute67 IS NOT NULL
819 OR inst_intf_rec.pricing_attribute68 IS NOT NULL
820 OR inst_intf_rec.pricing_attribute69 IS NOT NULL
821 OR inst_intf_rec.pricing_attribute70 IS NOT NULL
822 OR inst_intf_rec.pricing_attribute71 IS NOT NULL
823 OR inst_intf_rec.pricing_attribute72 IS NOT NULL
824 OR inst_intf_rec.pricing_attribute73 IS NOT NULL
825 OR inst_intf_rec.pricing_attribute74 IS NOT NULL
826 OR inst_intf_rec.pricing_attribute75 IS NOT NULL
827 OR inst_intf_rec.pricing_attribute76 IS NOT NULL
828 OR inst_intf_rec.pricing_attribute77 IS NOT NULL
829 OR inst_intf_rec.pricing_attribute78 IS NOT NULL
830 OR inst_intf_rec.pricing_attribute79 IS NOT NULL
831 OR inst_intf_rec.pricing_attribute80 IS NOT NULL
832 OR inst_intf_rec.pricing_attribute81 IS NOT NULL
833 OR inst_intf_rec.pricing_attribute82 IS NOT NULL
834 OR inst_intf_rec.pricing_attribute83 IS NOT NULL
835 OR inst_intf_rec.pricing_attribute84 IS NOT NULL
836 OR inst_intf_rec.pricing_attribute85 IS NOT NULL
837 OR inst_intf_rec.pricing_attribute86 IS NOT NULL
838 OR inst_intf_rec.pricing_attribute87 IS NOT NULL
839 OR inst_intf_rec.pricing_attribute88 IS NOT NULL
840 OR inst_intf_rec.pricing_attribute89 IS NOT NULL
841 OR inst_intf_rec.pricing_attribute90 IS NOT NULL
845 OR inst_intf_rec.pricing_attribute94 IS NOT NULL
842 OR inst_intf_rec.pricing_attribute91 IS NOT NULL
843 OR inst_intf_rec.pricing_attribute92 IS NOT NULL
844 OR inst_intf_rec.pricing_attribute93 IS NOT NULL
846 OR inst_intf_rec.pricing_attribute95 IS NOT NULL
847 OR inst_intf_rec.pricing_attribute96 IS NOT NULL
848 OR inst_intf_rec.pricing_attribute97 IS NOT NULL
849 OR inst_intf_rec.pricing_attribute98 IS NOT NULL
850 OR inst_intf_rec.pricing_attribute99 IS NOT NULL
851 OR inst_intf_rec.pricing_attribute100 IS NOT NULL
852 )
853 THEN
854
855 SELECT inst_intf_rec.instance_id,
856 l_miss_date, -- ACTIVE_START_DATE
857 DECODE (
858 inst_intf_rec.pricing_att_end_date,
859 NULL, l_miss_date,
860 l_miss_date, NULL,
861 inst_intf_rec.pricing_att_end_date
862 ),
863 DECODE (
864 inst_intf_rec.pricing_context,
865 NULL, l_miss_char,
866 l_miss_char, NULL,
867 inst_intf_rec.pricing_context
868 ),
869 DECODE (
870 inst_intf_rec.pricing_attribute1,
871 NULL, l_miss_char,
872 l_miss_char, NULL,
873 inst_intf_rec.pricing_attribute1
874 ),
875 DECODE (
876 inst_intf_rec.pricing_attribute2,
877 NULL, l_miss_char,
878 l_miss_char, NULL,
879 inst_intf_rec.pricing_attribute2
880 ),
881 DECODE (
882 inst_intf_rec.pricing_attribute3,
883 NULL, l_miss_char,
884 l_miss_char, NULL,
885 inst_intf_rec.pricing_attribute3
886 ),
887 DECODE (
888 inst_intf_rec.pricing_attribute4,
889 NULL, l_miss_char,
890 l_miss_char, NULL,
891 inst_intf_rec.pricing_attribute4
892 ),
893 DECODE (
894 inst_intf_rec.pricing_attribute5,
895 NULL, l_miss_char,
896 l_miss_char, NULL,
897 inst_intf_rec.pricing_attribute5
898 ),
899 DECODE (
900 inst_intf_rec.pricing_attribute6,
901 NULL, l_miss_char,
902 l_miss_char, NULL,
903 inst_intf_rec.pricing_attribute6
904 ),
905 DECODE (
906 inst_intf_rec.pricing_attribute7,
907 NULL, l_miss_char,
908 l_miss_char, NULL,
909 inst_intf_rec.pricing_attribute7
910 ),
911 DECODE (
912 inst_intf_rec.pricing_attribute8,
913 NULL, l_miss_char,
914 l_miss_char, NULL,
915 inst_intf_rec.pricing_attribute8
916 ),
917 DECODE (
918 inst_intf_rec.pricing_attribute9,
919 NULL, l_miss_char,
920 l_miss_char, NULL,
921 inst_intf_rec.pricing_attribute9
922 ),
923 DECODE (
924 inst_intf_rec.pricing_attribute10,
925 NULL, l_miss_char,
926 l_miss_char, NULL,
927 inst_intf_rec.pricing_attribute10
928 ),
929 DECODE (
930 inst_intf_rec.pricing_attribute11,
931 NULL, l_miss_char,
932 l_miss_char, NULL,
933 inst_intf_rec.pricing_attribute11
934 ),
935 DECODE (
936 inst_intf_rec.pricing_attribute12,
937 NULL, l_miss_char,
938 l_miss_char, NULL,
939 inst_intf_rec.pricing_attribute12
940 ),
941 DECODE (
942 inst_intf_rec.pricing_attribute13,
943 NULL, l_miss_char,
944 l_miss_char, NULL,
945 inst_intf_rec.pricing_attribute13
946 ),
947 DECODE (
948 inst_intf_rec.pricing_attribute14,
949 NULL, l_miss_char,
950 l_miss_char, NULL,
951 inst_intf_rec.pricing_attribute14
952 ),
953 DECODE (
954 inst_intf_rec.pricing_attribute15,
955 NULL, l_miss_char,
956 l_miss_char, NULL,
957 inst_intf_rec.pricing_attribute15
958 ),
959 DECODE (
960 inst_intf_rec.pricing_attribute16,
961 NULL, l_miss_char,
962 l_miss_char, NULL,
963 inst_intf_rec.pricing_attribute16
964 ),
965 DECODE (
966 inst_intf_rec.pricing_attribute17,
967 NULL, l_miss_char,
968 l_miss_char, NULL,
969 inst_intf_rec.pricing_attribute17
973 NULL, l_miss_char,
970 ),
971 DECODE (
972 inst_intf_rec.pricing_attribute18,
974 l_miss_char, NULL,
975 inst_intf_rec.pricing_attribute18
976 ),
977 DECODE (
978 inst_intf_rec.pricing_attribute19,
979 NULL, l_miss_char,
980 l_miss_char, NULL,
981 inst_intf_rec.pricing_attribute19
982 ),
983 DECODE (
984 inst_intf_rec.pricing_attribute20,
985 NULL, l_miss_char,
986 l_miss_char, NULL,
987 inst_intf_rec.pricing_attribute20
988 ),
989 DECODE (
990 inst_intf_rec.pricing_attribute21,
991 NULL, l_miss_char,
992 l_miss_char, NULL,
993 inst_intf_rec.pricing_attribute21
994 ),
995 DECODE (
996 inst_intf_rec.pricing_attribute22,
997 NULL, l_miss_char,
998 l_miss_char, NULL,
999 inst_intf_rec.pricing_attribute22
1000 ),
1001 DECODE (
1002 inst_intf_rec.pricing_attribute23,
1003 NULL, l_miss_char,
1004 l_miss_char, NULL,
1005 inst_intf_rec.pricing_attribute23
1006 ),
1007 DECODE (
1008 inst_intf_rec.pricing_attribute24,
1009 NULL, l_miss_char,
1010 l_miss_char, NULL,
1011 inst_intf_rec.pricing_attribute24
1012 ),
1013 DECODE (
1014 inst_intf_rec.pricing_attribute25,
1015 NULL, l_miss_char,
1016 l_miss_char, NULL,
1017 inst_intf_rec.pricing_attribute25
1018 ),
1019 DECODE (
1020 inst_intf_rec.pricing_attribute26,
1021 NULL, l_miss_char,
1022 l_miss_char, NULL,
1023 inst_intf_rec.pricing_attribute26
1024 ),
1025 DECODE (
1026 inst_intf_rec.pricing_attribute27,
1027 NULL, l_miss_char,
1028 l_miss_char, NULL,
1029 inst_intf_rec.pricing_attribute27
1030 ),
1031 DECODE (
1032 inst_intf_rec.pricing_attribute28,
1033 NULL, l_miss_char,
1034 l_miss_char, NULL,
1035 inst_intf_rec.pricing_attribute28
1036 ),
1037 DECODE (
1038 inst_intf_rec.pricing_attribute29,
1039 NULL, l_miss_char,
1040 l_miss_char, NULL,
1041 inst_intf_rec.pricing_attribute29
1042 ),
1043 DECODE (
1044 inst_intf_rec.pricing_attribute30,
1045 NULL, l_miss_char,
1046 l_miss_char, NULL,
1047 inst_intf_rec.pricing_attribute30
1048 ),
1049 DECODE (
1050 inst_intf_rec.pricing_attribute31,
1051 NULL, l_miss_char,
1052 l_miss_char, NULL,
1053 inst_intf_rec.pricing_attribute31
1054 ),
1055 DECODE (
1056 inst_intf_rec.pricing_attribute32,
1057 NULL, l_miss_char,
1058 l_miss_char, NULL,
1059 inst_intf_rec.pricing_attribute32
1060 ),
1061 DECODE (
1062 inst_intf_rec.pricing_attribute33,
1063 NULL, l_miss_char,
1064 l_miss_char, NULL,
1065 inst_intf_rec.pricing_attribute33
1066 ),
1067 DECODE (
1068 inst_intf_rec.pricing_attribute34,
1069 NULL, l_miss_char,
1070 l_miss_char, NULL,
1071 inst_intf_rec.pricing_attribute34
1072 ),
1073 DECODE (
1074 inst_intf_rec.pricing_attribute35,
1075 NULL, l_miss_char,
1076 l_miss_char, NULL,
1077 inst_intf_rec.pricing_attribute35
1078 ),
1079 DECODE (
1080 inst_intf_rec.pricing_attribute36,
1081 NULL, l_miss_char,
1082 l_miss_char, NULL,
1083 inst_intf_rec.pricing_attribute36
1084 ),
1085 DECODE (
1086 inst_intf_rec.pricing_attribute37,
1087 NULL, l_miss_char,
1088 l_miss_char, NULL,
1089 inst_intf_rec.pricing_attribute37
1090 ),
1091 DECODE (
1092 inst_intf_rec.pricing_attribute38,
1093 NULL, l_miss_char,
1094 l_miss_char, NULL,
1095 inst_intf_rec.pricing_attribute38
1096 ),
1097 DECODE (
1098 inst_intf_rec.pricing_attribute39,
1099 NULL, l_miss_char,
1103 DECODE (
1100 l_miss_char, NULL,
1101 inst_intf_rec.pricing_attribute39
1102 ),
1104 inst_intf_rec.pricing_attribute40,
1105 NULL, l_miss_char,
1106 l_miss_char, NULL,
1107 inst_intf_rec.pricing_attribute40
1108 ),
1109 DECODE (
1110 inst_intf_rec.pricing_attribute41,
1111 NULL, l_miss_char,
1112 l_miss_char, NULL,
1113 inst_intf_rec.pricing_attribute41
1114 ),
1115 DECODE (
1116 inst_intf_rec.pricing_attribute42,
1117 NULL, l_miss_char,
1118 l_miss_char, NULL,
1119 inst_intf_rec.pricing_attribute42
1120 ),
1121 DECODE (
1122 inst_intf_rec.pricing_attribute43,
1123 NULL, l_miss_char,
1124 l_miss_char, NULL,
1125 inst_intf_rec.pricing_attribute43
1126 ),
1127 DECODE (
1128 inst_intf_rec.pricing_attribute44,
1129 NULL, l_miss_char,
1130 l_miss_char, NULL,
1131 inst_intf_rec.pricing_attribute44
1132 ),
1133 DECODE (
1134 inst_intf_rec.pricing_attribute45,
1135 NULL, l_miss_char,
1136 l_miss_char, NULL,
1137 inst_intf_rec.pricing_attribute45
1138 ),
1139 DECODE (
1140 inst_intf_rec.pricing_attribute46,
1141 NULL, l_miss_char,
1142 l_miss_char, NULL,
1143 inst_intf_rec.pricing_attribute46
1144 ),
1145 DECODE (
1146 inst_intf_rec.pricing_attribute47,
1147 NULL, l_miss_char,
1148 l_miss_char, NULL,
1149 inst_intf_rec.pricing_attribute47
1150 ),
1151 DECODE (
1152 inst_intf_rec.pricing_attribute48,
1153 NULL, l_miss_char,
1154 l_miss_char, NULL,
1155 inst_intf_rec.pricing_attribute48
1156 ),
1157 DECODE (
1158 inst_intf_rec.pricing_attribute49,
1159 NULL, l_miss_char,
1160 l_miss_char, NULL,
1161 inst_intf_rec.pricing_attribute49
1162 ),
1163 DECODE (
1164 inst_intf_rec.pricing_attribute50,
1165 NULL, l_miss_char,
1166 l_miss_char, NULL,
1167 inst_intf_rec.pricing_attribute50
1168 ),
1169 DECODE (
1170 inst_intf_rec.pricing_attribute51,
1171 NULL, l_miss_char,
1172 l_miss_char, NULL,
1173 inst_intf_rec.pricing_attribute51
1174 ),
1175 DECODE (
1176 inst_intf_rec.pricing_attribute52,
1177 NULL, l_miss_char,
1178 l_miss_char, NULL,
1179 inst_intf_rec.pricing_attribute52
1180 ),
1181 DECODE (
1182 inst_intf_rec.pricing_attribute53,
1183 NULL, l_miss_char,
1184 l_miss_char, NULL,
1185 inst_intf_rec.pricing_attribute53
1186 ),
1187 DECODE (
1188 inst_intf_rec.pricing_attribute54,
1189 NULL, l_miss_char,
1190 l_miss_char, NULL,
1191 inst_intf_rec.pricing_attribute54
1192 ),
1193 DECODE (
1194 inst_intf_rec.pricing_attribute55,
1195 NULL, l_miss_char,
1196 l_miss_char, NULL,
1197 inst_intf_rec.pricing_attribute55
1198 ),
1199 DECODE (
1200 inst_intf_rec.pricing_attribute56,
1201 NULL, l_miss_char,
1202 l_miss_char, NULL,
1203 inst_intf_rec.pricing_attribute56
1204 ),
1205 DECODE (
1206 inst_intf_rec.pricing_attribute57,
1207 NULL, l_miss_char,
1208 l_miss_char, NULL,
1209 inst_intf_rec.pricing_attribute57
1210 ),
1211 DECODE (
1212 inst_intf_rec.pricing_attribute58,
1213 NULL, l_miss_char,
1214 l_miss_char, NULL,
1215 inst_intf_rec.pricing_attribute58
1216 ),
1217 DECODE (
1218 inst_intf_rec.pricing_attribute59,
1219 NULL, l_miss_char,
1220 l_miss_char, NULL,
1221 inst_intf_rec.pricing_attribute59
1222 ),
1223 DECODE (
1224 inst_intf_rec.pricing_attribute60,
1225 NULL, l_miss_char,
1226 l_miss_char, NULL,
1227 inst_intf_rec.pricing_attribute60
1228 ),
1229 DECODE (
1233 inst_intf_rec.pricing_attribute61
1230 inst_intf_rec.pricing_attribute61,
1231 NULL, l_miss_char,
1232 l_miss_char, NULL,
1234 ),
1235 DECODE (
1236 inst_intf_rec.pricing_attribute62,
1237 NULL, l_miss_char,
1238 l_miss_char, NULL,
1239 inst_intf_rec.pricing_attribute62
1240 ),
1241 DECODE (
1242 inst_intf_rec.pricing_attribute63,
1243 NULL, l_miss_char,
1244 l_miss_char, NULL,
1245 inst_intf_rec.pricing_attribute63
1246 ),
1247 DECODE (
1248 inst_intf_rec.pricing_attribute64,
1249 NULL, l_miss_char,
1250 l_miss_char, NULL,
1251 inst_intf_rec.pricing_attribute64
1252 ),
1253 DECODE (
1254 inst_intf_rec.pricing_attribute65,
1255 NULL, l_miss_char,
1256 l_miss_char, NULL,
1257 inst_intf_rec.pricing_attribute65
1258 ),
1259 DECODE (
1260 inst_intf_rec.pricing_attribute66,
1261 NULL, l_miss_char,
1262 l_miss_char, NULL,
1263 inst_intf_rec.pricing_attribute66
1264 ),
1265 DECODE (
1266 inst_intf_rec.pricing_attribute67,
1267 NULL, l_miss_char,
1268 l_miss_char, NULL,
1269 inst_intf_rec.pricing_attribute67
1270 ),
1271 DECODE (
1272 inst_intf_rec.pricing_attribute68,
1273 NULL, l_miss_char,
1274 l_miss_char, NULL,
1275 inst_intf_rec.pricing_attribute68
1276 ),
1277 DECODE (
1278 inst_intf_rec.pricing_attribute69,
1279 NULL, l_miss_char,
1280 l_miss_char, NULL,
1281 inst_intf_rec.pricing_attribute69
1282 ),
1283 DECODE (
1284 inst_intf_rec.pricing_attribute70,
1285 NULL, l_miss_char,
1286 l_miss_char, NULL,
1287 inst_intf_rec.pricing_attribute70
1288 ),
1289 DECODE (
1290 inst_intf_rec.pricing_attribute71,
1291 NULL, l_miss_char,
1292 l_miss_char, NULL,
1293 inst_intf_rec.pricing_attribute71
1294 ),
1295 DECODE (
1296 inst_intf_rec.pricing_attribute72,
1297 NULL, l_miss_char,
1298 l_miss_char, NULL,
1299 inst_intf_rec.pricing_attribute72
1300 ),
1301 DECODE (
1302 inst_intf_rec.pricing_attribute73,
1303 NULL, l_miss_char,
1304 l_miss_char, NULL,
1305 inst_intf_rec.pricing_attribute73
1306 ),
1307 DECODE (
1308 inst_intf_rec.pricing_attribute74,
1309 NULL, l_miss_char,
1310 l_miss_char, NULL,
1311 inst_intf_rec.pricing_attribute74
1312 ),
1313 DECODE (
1314 inst_intf_rec.pricing_attribute75,
1315 NULL, l_miss_char,
1316 l_miss_char, NULL,
1317 inst_intf_rec.pricing_attribute75
1318 ),
1319 DECODE (
1320 inst_intf_rec.pricing_attribute76,
1321 NULL, l_miss_char,
1322 l_miss_char, NULL,
1323 inst_intf_rec.pricing_attribute76
1324 ),
1325 DECODE (
1326 inst_intf_rec.pricing_attribute77,
1327 NULL, l_miss_char,
1328 l_miss_char, NULL,
1329 inst_intf_rec.pricing_attribute77
1330 ),
1331 DECODE (
1332 inst_intf_rec.pricing_attribute78,
1333 NULL, l_miss_char,
1334 l_miss_char, NULL,
1335 inst_intf_rec.pricing_attribute78
1336 ),
1337 DECODE (
1338 inst_intf_rec.pricing_attribute79,
1339 NULL, l_miss_char,
1340 l_miss_char, NULL,
1341 inst_intf_rec.pricing_attribute79
1342 ),
1343 DECODE (
1344 inst_intf_rec.pricing_attribute80,
1345 NULL, l_miss_char,
1346 l_miss_char, NULL,
1347 inst_intf_rec.pricing_attribute80
1348 ),
1349 DECODE (
1350 inst_intf_rec.pricing_attribute81,
1351 NULL, l_miss_char,
1352 l_miss_char, NULL,
1353 inst_intf_rec.pricing_attribute81
1354 ),
1355 DECODE (
1356 inst_intf_rec.pricing_attribute82,
1357 NULL, l_miss_char,
1358 l_miss_char, NULL,
1359 inst_intf_rec.pricing_attribute82
1363 NULL, l_miss_char,
1360 ),
1361 DECODE (
1362 inst_intf_rec.pricing_attribute83,
1364 l_miss_char, NULL,
1365 inst_intf_rec.pricing_attribute83
1366 ),
1367 DECODE (
1368 inst_intf_rec.pricing_attribute84,
1369 NULL, l_miss_char,
1370 l_miss_char, NULL,
1371 inst_intf_rec.pricing_attribute84
1372 ),
1373 DECODE (
1374 inst_intf_rec.pricing_attribute85,
1375 NULL, l_miss_char,
1376 l_miss_char, NULL,
1377 inst_intf_rec.pricing_attribute85
1378 ),
1379 DECODE (
1380 inst_intf_rec.pricing_attribute86,
1381 NULL, l_miss_char,
1382 l_miss_char, NULL,
1383 inst_intf_rec.pricing_attribute86
1384 ),
1385 DECODE (
1386 inst_intf_rec.pricing_attribute87,
1387 NULL, l_miss_char,
1388 l_miss_char, NULL,
1389 inst_intf_rec.pricing_attribute87
1390 ),
1391 DECODE (
1392 inst_intf_rec.pricing_attribute88,
1393 NULL, l_miss_char,
1394 l_miss_char, NULL,
1395 inst_intf_rec.pricing_attribute88
1396 ),
1397 DECODE (
1398 inst_intf_rec.pricing_attribute89,
1399 NULL, l_miss_char,
1400 l_miss_char, NULL,
1401 inst_intf_rec.pricing_attribute89
1402 ),
1403 DECODE (
1404 inst_intf_rec.pricing_attribute90,
1405 NULL, l_miss_char,
1406 l_miss_char, NULL,
1407 inst_intf_rec.pricing_attribute90
1408 ),
1409 DECODE (
1410 inst_intf_rec.pricing_attribute91,
1411 NULL, l_miss_char,
1412 l_miss_char, NULL,
1413 inst_intf_rec.pricing_attribute91
1414 ),
1415 DECODE (
1416 inst_intf_rec.pricing_attribute92,
1417 NULL, l_miss_char,
1418 l_miss_char, NULL,
1419 inst_intf_rec.pricing_attribute92
1420 ),
1421 DECODE (
1422 inst_intf_rec.pricing_attribute93,
1423 NULL, l_miss_char,
1424 l_miss_char, NULL,
1425 inst_intf_rec.pricing_attribute93
1426 ),
1427 DECODE (
1428 inst_intf_rec.pricing_attribute94,
1429 NULL, l_miss_char,
1430 l_miss_char, NULL,
1431 inst_intf_rec.pricing_attribute94
1432 ),
1433 DECODE (
1434 inst_intf_rec.pricing_attribute95,
1435 NULL, l_miss_char,
1436 l_miss_char, NULL,
1437 inst_intf_rec.pricing_attribute95
1438 ),
1439 DECODE (
1440 inst_intf_rec.pricing_attribute96,
1441 NULL, l_miss_char,
1442 l_miss_char, NULL,
1443 inst_intf_rec.pricing_attribute96
1444 ),
1445 DECODE (
1446 inst_intf_rec.pricing_attribute97,
1447 NULL, l_miss_char,
1448 l_miss_char, NULL,
1449 inst_intf_rec.pricing_attribute97
1450 ),
1451 DECODE (
1452 inst_intf_rec.pricing_attribute98,
1453 NULL, l_miss_char,
1454 l_miss_char, NULL,
1455 inst_intf_rec.pricing_attribute98
1456 ),
1457 DECODE (
1458 inst_intf_rec.pricing_attribute99,
1459 NULL, l_miss_char,
1460 l_miss_char, NULL,
1461 inst_intf_rec.pricing_attribute99
1462 ),
1463 DECODE (
1464 inst_intf_rec.pricing_attribute100,
1465 NULL, l_miss_char,
1466 l_miss_char, NULL,
1467 inst_intf_rec.pricing_attribute100
1468 ),
1469 DECODE (
1470 inst_intf_rec.pricing_flex_context,
1471 NULL, l_miss_char,
1472 l_miss_char, NULL,
1473 inst_intf_rec.pricing_flex_context
1474 ),
1475 DECODE (
1476 inst_intf_rec.pricing_flex_attribute1,
1477 NULL, l_miss_char,
1478 l_miss_char, NULL,
1479 inst_intf_rec.pricing_flex_attribute1
1480 ),
1481 DECODE (
1482 inst_intf_rec.pricing_flex_attribute2,
1483 NULL, l_miss_char,
1484 l_miss_char, NULL,
1485 inst_intf_rec.pricing_flex_attribute2
1486 ),
1487 DECODE (
1488 inst_intf_rec.pricing_flex_attribute3,
1489 NULL, l_miss_char,
1493 DECODE (
1490 l_miss_char, NULL,
1491 inst_intf_rec.pricing_flex_attribute3
1492 ),
1494 inst_intf_rec.pricing_flex_attribute4,
1495 NULL, l_miss_char,
1496 l_miss_char, NULL,
1497 inst_intf_rec.pricing_flex_attribute4
1498 ),
1499 DECODE (
1500 inst_intf_rec.pricing_flex_attribute5,
1501 NULL, l_miss_char,
1502 l_miss_char, NULL,
1503 inst_intf_rec.pricing_flex_attribute5
1504 ),
1505 DECODE (
1506 inst_intf_rec.pricing_flex_attribute6,
1507 NULL, l_miss_char,
1508 l_miss_char, NULL,
1509 inst_intf_rec.pricing_flex_attribute6
1510 ),
1511 DECODE (
1512 inst_intf_rec.pricing_flex_attribute7,
1513 NULL, l_miss_char,
1514 l_miss_char, NULL,
1515 inst_intf_rec.pricing_flex_attribute7
1516 ),
1517 DECODE (
1518 inst_intf_rec.pricing_flex_attribute8,
1519 NULL, l_miss_char,
1520 l_miss_char, NULL,
1521 inst_intf_rec.pricing_flex_attribute8
1522 ),
1523 DECODE (
1524 inst_intf_rec.pricing_flex_attribute9,
1525 NULL, l_miss_char,
1526 l_miss_char, NULL,
1527 inst_intf_rec.pricing_flex_attribute9
1528 ),
1529 DECODE (
1530 inst_intf_rec.pricing_flex_attribute10,
1531 NULL, l_miss_char,
1532 l_miss_char, NULL,
1533 inst_intf_rec.pricing_flex_attribute10
1534 ),
1535 DECODE (
1536 inst_intf_rec.pricing_flex_attribute11,
1537 NULL, l_miss_char,
1538 l_miss_char, NULL,
1539 inst_intf_rec.pricing_flex_attribute11
1540 ),
1541 DECODE (
1542 inst_intf_rec.pricing_flex_attribute12,
1543 NULL, l_miss_char,
1544 l_miss_char, NULL,
1545 inst_intf_rec.pricing_flex_attribute12
1546 ),
1547 DECODE (
1548 inst_intf_rec.pricing_flex_attribute13,
1549 NULL, l_miss_char,
1550 l_miss_char, NULL,
1551 inst_intf_rec.pricing_flex_attribute13
1552 ),
1553 DECODE (
1554 inst_intf_rec.pricing_flex_attribute14,
1555 NULL, l_miss_char,
1556 l_miss_char, NULL,
1557 inst_intf_rec.pricing_flex_attribute14
1558 ),
1559 DECODE (
1560 inst_intf_rec.pricing_flex_attribute15,
1561 NULL, l_miss_char,
1562 l_miss_char, NULL,
1563 inst_intf_rec.pricing_flex_attribute15
1564 ),
1565 l_price_object_version -- OBJECT_VERSION_NUMBER
1566 INTO x_price_tbl(price_idx).instance_id,
1567 x_price_tbl(price_idx).active_start_date,
1568 x_price_tbl(price_idx).active_end_date,
1569 x_price_tbl(price_idx).pricing_context,
1570 x_price_tbl(price_idx).pricing_attribute1,
1571 x_price_tbl(price_idx).pricing_attribute2,
1572 x_price_tbl(price_idx).pricing_attribute3,
1573 x_price_tbl(price_idx).pricing_attribute4,
1574 x_price_tbl(price_idx).pricing_attribute5,
1575 x_price_tbl(price_idx).pricing_attribute6,
1576 x_price_tbl(price_idx).pricing_attribute7,
1577 x_price_tbl(price_idx).pricing_attribute8,
1578 x_price_tbl(price_idx).pricing_attribute9,
1579 x_price_tbl(price_idx).pricing_attribute10,
1580 x_price_tbl(price_idx).pricing_attribute11,
1581 x_price_tbl(price_idx).pricing_attribute12,
1582 x_price_tbl(price_idx).pricing_attribute13,
1583 x_price_tbl(price_idx).pricing_attribute14,
1584 x_price_tbl(price_idx).pricing_attribute15,
1585 x_price_tbl(price_idx).pricing_attribute16,
1586 x_price_tbl(price_idx).pricing_attribute17,
1587 x_price_tbl(price_idx).pricing_attribute18,
1588 x_price_tbl(price_idx).pricing_attribute19,
1589 x_price_tbl(price_idx).pricing_attribute20,
1590 x_price_tbl(price_idx).pricing_attribute21,
1591 x_price_tbl(price_idx).pricing_attribute22,
1592 x_price_tbl(price_idx).pricing_attribute23,
1593 x_price_tbl(price_idx).pricing_attribute24,
1594 x_price_tbl(price_idx).pricing_attribute25,
1595 x_price_tbl(price_idx).pricing_attribute26,
1596 x_price_tbl(price_idx).pricing_attribute27,
1597 x_price_tbl(price_idx).pricing_attribute28,
1598 x_price_tbl(price_idx).pricing_attribute29,
1599 x_price_tbl(price_idx).pricing_attribute30,
1600 x_price_tbl(price_idx).pricing_attribute31,
1601 x_price_tbl(price_idx).pricing_attribute32,
1602 x_price_tbl(price_idx).pricing_attribute33,
1606 x_price_tbl(price_idx).pricing_attribute37,
1603 x_price_tbl(price_idx).pricing_attribute34,
1604 x_price_tbl(price_idx).pricing_attribute35,
1605 x_price_tbl(price_idx).pricing_attribute36,
1607 x_price_tbl(price_idx).pricing_attribute38,
1608 x_price_tbl(price_idx).pricing_attribute39,
1609 x_price_tbl(price_idx).pricing_attribute40,
1610 x_price_tbl(price_idx).pricing_attribute41,
1611 x_price_tbl(price_idx).pricing_attribute42,
1612 x_price_tbl(price_idx).pricing_attribute43,
1613 x_price_tbl(price_idx).pricing_attribute44,
1614 x_price_tbl(price_idx).pricing_attribute45,
1615 x_price_tbl(price_idx).pricing_attribute46,
1616 x_price_tbl(price_idx).pricing_attribute47,
1617 x_price_tbl(price_idx).pricing_attribute48,
1618 x_price_tbl(price_idx).pricing_attribute49,
1619 x_price_tbl(price_idx).pricing_attribute50,
1620 x_price_tbl(price_idx).pricing_attribute51,
1621 x_price_tbl(price_idx).pricing_attribute52,
1622 x_price_tbl(price_idx).pricing_attribute53,
1623 x_price_tbl(price_idx).pricing_attribute54,
1624 x_price_tbl(price_idx).pricing_attribute55,
1625 x_price_tbl(price_idx).pricing_attribute56,
1626 x_price_tbl(price_idx).pricing_attribute57,
1627 x_price_tbl(price_idx).pricing_attribute58,
1628 x_price_tbl(price_idx).pricing_attribute59,
1629 x_price_tbl(price_idx).pricing_attribute60,
1630 x_price_tbl(price_idx).pricing_attribute61,
1631 x_price_tbl(price_idx).pricing_attribute62,
1632 x_price_tbl(price_idx).pricing_attribute63,
1633 x_price_tbl(price_idx).pricing_attribute64,
1634 x_price_tbl(price_idx).pricing_attribute65,
1635 x_price_tbl(price_idx).pricing_attribute66,
1636 x_price_tbl(price_idx).pricing_attribute67,
1637 x_price_tbl(price_idx).pricing_attribute68,
1638 x_price_tbl(price_idx).pricing_attribute69,
1639 x_price_tbl(price_idx).pricing_attribute70,
1640 x_price_tbl(price_idx).pricing_attribute71,
1641 x_price_tbl(price_idx).pricing_attribute72,
1642 x_price_tbl(price_idx).pricing_attribute73,
1643 x_price_tbl(price_idx).pricing_attribute74,
1644 x_price_tbl(price_idx).pricing_attribute75,
1645 x_price_tbl(price_idx).pricing_attribute76,
1646 x_price_tbl(price_idx).pricing_attribute77,
1647 x_price_tbl(price_idx).pricing_attribute78,
1648 x_price_tbl(price_idx).pricing_attribute79,
1649 x_price_tbl(price_idx).pricing_attribute80,
1650 x_price_tbl(price_idx).pricing_attribute81,
1651 x_price_tbl(price_idx).pricing_attribute82,
1652 x_price_tbl(price_idx).pricing_attribute83,
1653 x_price_tbl(price_idx).pricing_attribute84,
1654 x_price_tbl(price_idx).pricing_attribute85,
1655 x_price_tbl(price_idx).pricing_attribute86,
1656 x_price_tbl(price_idx).pricing_attribute87,
1657 x_price_tbl(price_idx).pricing_attribute88,
1658 x_price_tbl(price_idx).pricing_attribute89,
1659 x_price_tbl(price_idx).pricing_attribute90,
1660 x_price_tbl(price_idx).pricing_attribute91,
1661 x_price_tbl(price_idx).pricing_attribute92,
1662 x_price_tbl(price_idx).pricing_attribute93,
1663 x_price_tbl(price_idx).pricing_attribute94,
1664 x_price_tbl(price_idx).pricing_attribute95,
1665 x_price_tbl(price_idx).pricing_attribute96,
1666 x_price_tbl(price_idx).pricing_attribute97,
1667 x_price_tbl(price_idx).pricing_attribute98,
1668 x_price_tbl(price_idx).pricing_attribute99,
1669 x_price_tbl(price_idx).pricing_attribute100,
1670 x_price_tbl(price_idx).CONTEXT,
1671 x_price_tbl(price_idx).attribute1,
1672 x_price_tbl(price_idx).attribute2,
1673 x_price_tbl(price_idx).attribute3,
1674 x_price_tbl(price_idx).attribute4,
1675 x_price_tbl(price_idx).attribute5,
1676 x_price_tbl(price_idx).attribute6,
1677 x_price_tbl(price_idx).attribute7,
1678 x_price_tbl(price_idx).attribute8,
1679 x_price_tbl(price_idx).attribute9,
1680 x_price_tbl(price_idx).attribute10,
1681 x_price_tbl(price_idx).attribute11,
1682 x_price_tbl(price_idx).attribute12,
1683 x_price_tbl(price_idx).attribute13,
1684 x_price_tbl(price_idx).attribute14,
1685 x_price_tbl(price_idx).attribute15,
1686 x_price_tbl(price_idx).object_version_number
1687 FROM DUAL;
1688 END IF;
1689
1690
1691 ---Populate Org Assignments
1692 IF inst_intf_rec.instance_ou_id IS NOT NULL
1693 THEN
1694 x_org_assign_tbl(org_idx).instance_ou_id :=
1695 inst_intf_rec.instance_ou_id;
1696 END IF;
1697
1698 IF ( inst_intf_rec.operating_unit IS NOT NULL
1699 OR inst_intf_rec.ou_relation_type IS NOT NULL
1700 OR inst_intf_rec.ou_end_date IS NOT NULL
1701 )
1702 THEN
1703 SELECT DECODE (
1704 inst_intf_rec.instance_id,
1708 ),
1705 NULL, l_miss_num,
1706 l_miss_num, NULL,
1707 inst_intf_rec.instance_id
1709 DECODE (
1710 inst_intf_rec.operating_unit,
1711 NULL, l_miss_num,
1712 l_miss_num, NULL,
1713 inst_intf_rec.operating_unit
1714 ),
1715 DECODE (
1716 inst_intf_rec.ou_relation_type,
1717 NULL, l_miss_char,
1718 l_miss_char, NULL,
1719 inst_intf_rec.ou_relation_type
1720 ),
1721 l_miss_date, -- ACTIVE_START_DATE
1722 DECODE (
1723 inst_intf_rec.ou_end_date,
1724 NULL, l_miss_date,
1725 l_miss_date, NULL,
1726 inst_intf_rec.ou_end_date
1727 ),
1728 l_miss_char, -- CONTEXT
1729 l_miss_char, -- ATTRIBUTE1
1730 l_miss_char, -- ATTRIBUTE2
1731 l_miss_char, -- ATTRIBUTE3
1732 l_miss_char, -- ATTRIBUTE4
1733 l_miss_char, -- ATTRIBUTE5
1734 l_miss_char, -- ATTRIBUTE6
1735 l_miss_char, -- ATTRIBUTE7
1736 l_miss_char, -- ATTRIBUTE8
1737 l_miss_char, -- ATTRIBUTE9
1738 l_miss_char, -- ATTRIBUTE10
1739 l_miss_char, -- ATTRIBUTE11
1740 l_miss_char, -- ATTRIBUTE12
1741 l_miss_char, -- ATTRIBUTE13
1742 l_miss_char, -- ATTRIBUTE14
1743 l_miss_char, -- ATTRIBUTE15
1744 l_ou_object_version -- OBJECT_VERSION_NUMBER
1745 INTO x_org_assign_tbl(org_idx).instance_id,
1746 x_org_assign_tbl(org_idx).operating_unit_id,
1747 x_org_assign_tbl(org_idx).relationship_type_code,
1748 x_org_assign_tbl(org_idx).active_start_date,
1749 x_org_assign_tbl(org_idx).active_end_date,
1750 x_org_assign_tbl(org_idx).CONTEXT,
1751 x_org_assign_tbl(org_idx).attribute1,
1752 x_org_assign_tbl(org_idx).attribute2,
1753 x_org_assign_tbl(org_idx).attribute3,
1754 x_org_assign_tbl(org_idx).attribute4,
1755 x_org_assign_tbl(org_idx).attribute5,
1756 x_org_assign_tbl(org_idx).attribute6,
1757 x_org_assign_tbl(org_idx).attribute7,
1758 x_org_assign_tbl(org_idx).attribute8,
1759 x_org_assign_tbl(org_idx).attribute9,
1760 x_org_assign_tbl(org_idx).attribute10,
1761 x_org_assign_tbl(org_idx).attribute11,
1762 x_org_assign_tbl(org_idx).attribute12,
1763 x_org_assign_tbl(org_idx).attribute13,
1764 x_org_assign_tbl(org_idx).attribute14,
1765 x_org_assign_tbl(org_idx).attribute15,
1766 x_org_assign_tbl(org_idx).object_version_number
1767 FROM DUAL;
1768 END IF; --Org Assignments
1769
1770
1771 ----Populate Party
1772 FOR party_intf_rec IN party_intf_cur (inst_intf_rec.inst_interface_id)
1773 LOOP
1774 prty_idx := prty_idx
1775 + 1;
1776
1777 x_party_tbl (prty_idx).instance_party_id :=
1778 party_intf_rec.instance_party_id;
1779 x_party_tbl (prty_idx).instance_id := inst_intf_rec.instance_id;
1780
1781 OPEN ip_obj_ver_cur (party_intf_rec.instance_party_id);
1782 FETCH ip_obj_ver_cur INTO l_party_object_version,
1783 l_last_update_date ;
1784 CLOSE ip_obj_ver_cur;
1785 IF l_last_update_date > inst_intf_rec.source_transaction_date
1786 THEN
1787 fnd_message.set_name('CSI','CSI_INTERFACE_RESTRICTION');
1788 fnd_message.set_token('instance_id',inst_intf_rec.instance_id);
1789 l_error_message := fnd_message.get;
1790 RAISE e_restriction;
1791 END IF;
1792 SELECT DECODE (
1793 party_intf_rec.party_source_table,
1794 NULL, l_miss_char,
1795 l_miss_char, NULL,
1796 party_intf_rec.party_source_table
1797 ),
1798 DECODE (
1799 party_intf_rec.party_id,
1800 NULL, l_miss_num,
1801 l_miss_num, NULL,
1802 party_intf_rec.party_id
1803 ),
1804 DECODE (
1805 party_intf_rec.party_relationship_type_code,
1806 NULL, l_miss_char,
1807 l_miss_char, NULL,
1808 party_intf_rec.party_relationship_type_code
1809 ),
1810 DECODE (
1811 party_intf_rec.contact_flag,
1812 NULL, l_miss_char,
1813 l_miss_char, NULL,
1814 party_intf_rec.contact_flag
1815 ),
1816 DECODE (
1817 party_intf_rec.contact_ip_id,
1818 NULL, l_miss_num,
1819 l_miss_num, NULL,
1820 party_intf_rec.contact_ip_id
1821 ),
1822 DECODE (
1823 party_intf_rec.party_start_date,
1824 NULL, l_miss_date,
1825 l_miss_date, NULL,
1826 party_intf_rec.party_start_date
1827 ),
1828 DECODE (
1829 party_intf_rec.party_end_date,
1833 ),
1830 NULL, l_miss_date,
1831 l_miss_date, NULL,
1832 party_intf_rec.party_end_date
1834 DECODE (
1835 party_intf_rec.party_context,
1836 NULL, l_miss_char,
1837 l_miss_char, NULL,
1838 party_intf_rec.party_context
1839 ),
1840 DECODE (
1841 party_intf_rec.party_attribute1,
1842 NULL, l_miss_char,
1843 l_miss_char, NULL,
1844 party_intf_rec.party_attribute1
1845 ),
1846 DECODE (
1847 party_intf_rec.party_attribute2,
1848 NULL, l_miss_char,
1849 l_miss_char, NULL,
1850 party_intf_rec.party_attribute2
1851 ),
1852 DECODE (
1853 party_intf_rec.party_attribute3,
1854 NULL, l_miss_char,
1855 l_miss_char, NULL,
1856 party_intf_rec.party_attribute3
1857 ),
1858 DECODE (
1859 party_intf_rec.party_attribute4,
1860 NULL, l_miss_char,
1861 l_miss_char, NULL,
1862 party_intf_rec.party_attribute4
1863 ),
1864 DECODE (
1865 party_intf_rec.party_attribute5,
1866 NULL, l_miss_char,
1867 l_miss_char, NULL,
1868 party_intf_rec.party_attribute5
1869 ),
1870 DECODE (
1871 party_intf_rec.party_attribute6,
1872 NULL, l_miss_char,
1873 l_miss_char, NULL,
1874 party_intf_rec.party_attribute6
1875 ),
1876 DECODE (
1877 party_intf_rec.party_attribute7,
1878 NULL, l_miss_char,
1879 l_miss_char, NULL,
1880 party_intf_rec.party_attribute7
1881 ),
1882 DECODE (
1883 party_intf_rec.party_attribute8,
1884 NULL, l_miss_char,
1885 l_miss_char, NULL,
1886 party_intf_rec.party_attribute8
1887 ),
1888 DECODE (
1889 party_intf_rec.party_attribute9,
1890 NULL, l_miss_char,
1891 l_miss_char, NULL,
1892 party_intf_rec.party_attribute9
1893 ),
1894 DECODE (
1895 party_intf_rec.party_attribute10,
1896 NULL, l_miss_char,
1897 l_miss_char, NULL,
1898 party_intf_rec.party_attribute10
1899 ),
1900 DECODE (
1901 party_intf_rec.party_attribute11,
1902 NULL, l_miss_char,
1903 l_miss_char, NULL,
1904 party_intf_rec.party_attribute11
1905 ),
1906 DECODE (
1907 party_intf_rec.party_attribute12,
1908 NULL, l_miss_char,
1909 l_miss_char, NULL,
1910 party_intf_rec.party_attribute12
1911 ),
1912 DECODE (
1913 party_intf_rec.party_attribute13,
1914 NULL, l_miss_char,
1915 l_miss_char, NULL,
1916 party_intf_rec.party_attribute13
1917 ),
1918 DECODE (
1919 party_intf_rec.party_attribute14,
1920 NULL, l_miss_char,
1921 l_miss_char, NULL,
1922 party_intf_rec.party_attribute14
1923 ),
1924 DECODE (
1925 party_intf_rec.party_attribute15,
1926 NULL, l_miss_char,
1927 l_miss_char, NULL,
1928 party_intf_rec.party_attribute15
1929 ),
1930 l_party_object_version -- OBJECT_VERSION_NUMBER
1931 INTO x_party_tbl (prty_idx).party_source_table,
1932 x_party_tbl (prty_idx).party_id,
1933 x_party_tbl (prty_idx).relationship_type_code,
1934 x_party_tbl (prty_idx).contact_flag,
1935 x_party_tbl (prty_idx).contact_ip_id,
1936 x_party_tbl (prty_idx).active_start_date,
1937 x_party_tbl (prty_idx).active_end_date,
1938 x_party_tbl (prty_idx).CONTEXT,
1939 x_party_tbl (prty_idx).attribute1,
1940 x_party_tbl (prty_idx).attribute2,
1941 x_party_tbl (prty_idx).attribute3,
1942 x_party_tbl (prty_idx).attribute4,
1943 x_party_tbl (prty_idx).attribute5,
1944 x_party_tbl (prty_idx).attribute6,
1945 x_party_tbl (prty_idx).attribute7,
1946 x_party_tbl (prty_idx).attribute8,
1947 x_party_tbl (prty_idx).attribute9,
1948 x_party_tbl (prty_idx).attribute10,
1949 x_party_tbl (prty_idx).attribute11,
1950 x_party_tbl (prty_idx).attribute12,
1951 x_party_tbl (prty_idx).attribute13,
1952 x_party_tbl (prty_idx).attribute14,
1953 x_party_tbl (prty_idx).attribute15,
1954 x_party_tbl (prty_idx).object_version_number
1955 FROM DUAL;
1956
1957 x_party_tbl (prty_idx).parent_tbl_index:=inst_idx;
1958 IF (x_party_tbl(prty_idx).contact_flag = 'Y'
1959 AND x_party_tbl(prty_idx).contact_ip_id IS NULL)
1960 THEN
1964 END IF;
1961 FOR i IN 1 .. prty_idx LOOP
1962 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)
1963 THEN x_party_tbl(prty_idx).contact_parent_tbl_index := i;
1965 EXIT;
1966 END LOOP;
1967 END IF;
1968
1969 ---Populate Party Account1
1970 IF party_intf_rec.party_account1_id IS NOT NULL
1971 THEN -- Put record in Table
1972 ptyacc_idx := ptyacc_idx
1973 + 1;
1974 x_account_tbl (ptyacc_idx).ip_account_id :=
1975 party_intf_rec.ip_account1_id;
1976 x_account_tbl (ptyacc_idx).instance_party_id :=
1977 party_intf_rec.instance_party_id;
1978 x_account_tbl (ptyacc_idx).parent_tbl_index := prty_idx;
1979 OPEN ipa_obj_ver_cur (party_intf_rec.ip_account1_id);
1980 FETCH ipa_obj_ver_cur INTO l_ipa1_object_version,
1981 l_last_update_date;
1982 CLOSE ipa_obj_ver_cur;
1983 IF l_last_update_date > inst_intf_rec.source_transaction_date
1984 THEN
1985 fnd_message.set_name('CSI','CSI_INTERFACE_RESTRICTION');
1986 fnd_message.set_token('instance_id',inst_intf_rec.instance_id);
1987 l_error_message := fnd_message.get;
1988 RAISE e_restriction;
1989 END IF;
1990 x_account_tbl (ptyacc_idx).object_version_number :=
1991 l_ipa1_object_version;
1992
1993 SELECT DECODE (
1994 party_intf_rec.party_account1_id,
1995 NULL, l_miss_num,
1996 l_miss_num, NULL,
1997 party_intf_rec.party_account1_id
1998 ),
1999 DECODE (
2000 party_intf_rec.acct1_relationship_type_code,
2001 NULL, l_miss_char,
2002 l_miss_char, NULL,
2003 party_intf_rec.acct1_relationship_type_code
2004 ),
2005 DECODE (
2006 party_intf_rec.bill_to_address1,
2007 NULL, l_miss_num,
2008 l_miss_num, NULL,
2009 party_intf_rec.bill_to_address1
2010 ),
2011 DECODE (
2012 party_intf_rec.ship_to_address1,
2013 NULL, l_miss_num,
2014 l_miss_num, NULL,
2015 party_intf_rec.ship_to_address1
2016 ),
2017 l_miss_date, -- ACTIVE_START_DATE
2018 DECODE (
2019 party_intf_rec.party_acct1_end_date,
2020 NULL, l_miss_date,
2021 l_miss_date, NULL,
2022 party_intf_rec.party_acct1_end_date
2023 ),
2024 DECODE (
2025 party_intf_rec.account1_context,
2026 NULL, l_miss_char,
2027 l_miss_char, NULL,
2028 party_intf_rec.account1_context
2029 ),
2030 DECODE (
2031 party_intf_rec.account1_attribute1,
2032 NULL, l_miss_char,
2033 l_miss_char, NULL,
2034 party_intf_rec.account1_attribute1
2035 ),
2036 DECODE (
2037 party_intf_rec.account1_attribute2,
2038 NULL, l_miss_char,
2039 l_miss_char, NULL,
2040 party_intf_rec.account1_attribute2
2041 ),
2042 DECODE (
2043 party_intf_rec.account1_attribute3,
2044 NULL, l_miss_char,
2045 l_miss_char, NULL,
2046 party_intf_rec.account1_attribute3
2047 ),
2048 DECODE (
2049 party_intf_rec.account1_attribute4,
2050 NULL, l_miss_char,
2051 l_miss_char, NULL,
2052 party_intf_rec.account1_attribute4
2053 ),
2054 DECODE (
2055 party_intf_rec.account1_attribute5,
2056 NULL, l_miss_char,
2057 l_miss_char, NULL,
2058 party_intf_rec.account1_attribute5
2059 ),
2060 DECODE (
2061 party_intf_rec.account1_attribute6,
2062 NULL, l_miss_char,
2063 l_miss_char, NULL,
2064 party_intf_rec.account1_attribute6
2065 ),
2066 DECODE (
2067 party_intf_rec.account1_attribute7,
2068 NULL, l_miss_char,
2069 l_miss_char, NULL,
2070 party_intf_rec.account1_attribute7
2071 ),
2072 DECODE (
2073 party_intf_rec.account1_attribute8,
2074 NULL, l_miss_char,
2075 l_miss_char, NULL,
2076 party_intf_rec.account1_attribute8
2077 ),
2078 DECODE (
2079 party_intf_rec.account1_attribute9,
2080 NULL, l_miss_char,
2081 l_miss_char, NULL,
2082 party_intf_rec.account1_attribute9
2083 ),
2084 DECODE (
2088 party_intf_rec.account1_attribute10
2085 party_intf_rec.account1_attribute10,
2086 NULL, l_miss_char,
2087 l_miss_char, NULL,
2089 ),
2090 DECODE (
2091 party_intf_rec.account1_attribute11,
2092 NULL, l_miss_char,
2093 l_miss_char, NULL,
2094 party_intf_rec.account1_attribute11
2095 ),
2096 DECODE (
2097 party_intf_rec.account1_attribute12,
2098 NULL, l_miss_char,
2099 l_miss_char, NULL,
2100 party_intf_rec.account1_attribute12
2101 ),
2102 DECODE (
2103 party_intf_rec.account1_attribute13,
2104 NULL, l_miss_char,
2105 l_miss_char, NULL,
2106 party_intf_rec.account1_attribute13
2107 ),
2108 DECODE (
2109 party_intf_rec.account1_attribute14,
2110 NULL, l_miss_char,
2111 l_miss_char, NULL,
2112 party_intf_rec.account1_attribute14
2113 ),
2114 DECODE (
2115 party_intf_rec.account1_attribute15,
2116 NULL, l_miss_char,
2117 l_miss_char, NULL,
2118 party_intf_rec.account1_attribute15
2119 ),
2120 fnd_api.g_false, --CALL_CONTRACTS
2121 l_miss_num --VLD_ORGANIZATION_ID
2122 INTO x_account_tbl (ptyacc_idx).party_account_id,
2123 x_account_tbl (ptyacc_idx).relationship_type_code,
2124 x_account_tbl (ptyacc_idx).bill_to_address,
2125 x_account_tbl (ptyacc_idx).ship_to_address,
2126 x_account_tbl (ptyacc_idx).active_start_date,
2127 x_account_tbl (ptyacc_idx).active_end_date,
2128 x_account_tbl (ptyacc_idx).CONTEXT,
2129 x_account_tbl (ptyacc_idx).attribute1,
2130 x_account_tbl (ptyacc_idx).attribute2,
2131 x_account_tbl (ptyacc_idx).attribute3,
2132 x_account_tbl (ptyacc_idx).attribute4,
2133 x_account_tbl (ptyacc_idx).attribute5,
2134 x_account_tbl (ptyacc_idx).attribute6,
2135 x_account_tbl (ptyacc_idx).attribute7,
2136 x_account_tbl (ptyacc_idx).attribute8,
2137 x_account_tbl (ptyacc_idx).attribute9,
2138 x_account_tbl (ptyacc_idx).attribute10,
2139 x_account_tbl (ptyacc_idx).attribute11,
2140 x_account_tbl (ptyacc_idx).attribute12,
2141 x_account_tbl (ptyacc_idx).attribute13,
2142 x_account_tbl (ptyacc_idx).attribute14,
2143 x_account_tbl (ptyacc_idx).attribute15,
2144 x_account_tbl (ptyacc_idx).call_contracts,
2145 x_account_tbl (ptyacc_idx).vld_organization_id
2146 FROM DUAL;
2147 END IF; ---party_account1
2148
2149 ---Populate Party Account2
2150 IF party_intf_rec.party_account2_id IS NOT NULL
2151 THEN -- Put record in Table
2152 ptyacc_idx := ptyacc_idx
2153 + 1;
2154 x_account_tbl (ptyacc_idx).ip_account_id :=
2155 party_intf_rec.ip_account2_id;
2156 x_account_tbl (ptyacc_idx).instance_party_id :=
2157 party_intf_rec.instance_party_id;
2158 x_account_tbl (ptyacc_idx).parent_tbl_index := prty_idx;
2159 OPEN ipa_obj_ver_cur (party_intf_rec.ip_account2_id);
2160 FETCH ipa_obj_ver_cur INTO l_ipa2_object_version,
2161 l_last_update_date;
2162 CLOSE ipa_obj_ver_cur;
2163 IF l_last_update_date > inst_intf_rec.source_transaction_date
2164 THEN
2165 fnd_message.set_name('CSI','CSI_INTERFACE_RESTRICTION');
2166 fnd_message.set_token('instance_id',inst_intf_rec.instance_id);
2167 l_error_message := fnd_message.get;
2168 RAISE e_restriction;
2169 END IF;
2170 x_account_tbl (ptyacc_idx).object_version_number :=
2171 l_ipa2_object_version;
2172
2173 SELECT DECODE (
2174 party_intf_rec.party_account2_id,
2175 NULL, l_miss_num,
2176 l_miss_num, NULL,
2177 party_intf_rec.party_account2_id
2178 ),
2179 DECODE (
2180 party_intf_rec.acct2_relationship_type_code,
2181 NULL, l_miss_char,
2182 l_miss_char, NULL,
2183 party_intf_rec.acct2_relationship_type_code
2184 ),
2185 DECODE (
2186 party_intf_rec.bill_to_address2,
2187 NULL, l_miss_num,
2188 l_miss_num, NULL,
2189 party_intf_rec.bill_to_address2
2190 ),
2191 DECODE (
2192 party_intf_rec.ship_to_address2,
2193 NULL, l_miss_num,
2194 l_miss_num, NULL,
2195 party_intf_rec.ship_to_address2
2196 ),
2197 l_miss_date, -- ACTIVE_START_DATE
2198 DECODE (
2199 party_intf_rec.party_acct2_end_date,
2200 NULL, l_miss_date,
2201 l_miss_date, NULL,
2205 party_intf_rec.account2_context,
2202 party_intf_rec.party_acct2_end_date
2203 ),
2204 DECODE (
2206 NULL, l_miss_char,
2207 l_miss_char, NULL,
2208 party_intf_rec.account2_context
2209 ),
2210 DECODE (
2211 party_intf_rec.account2_attribute1,
2212 NULL, l_miss_char,
2213 l_miss_char, NULL,
2214 party_intf_rec.account2_attribute1
2215 ),
2216 DECODE (
2217 party_intf_rec.account2_attribute2,
2218 NULL, l_miss_char,
2219 l_miss_char, NULL,
2220 party_intf_rec.account2_attribute2
2221 ),
2222 DECODE (
2223 party_intf_rec.account2_attribute3,
2224 NULL, l_miss_char,
2225 l_miss_char, NULL,
2226 party_intf_rec.account2_attribute3
2227 ),
2228 DECODE (
2229 party_intf_rec.account2_attribute4,
2230 NULL, l_miss_char,
2231 l_miss_char, NULL,
2232 party_intf_rec.account2_attribute4
2233 ),
2234 DECODE (
2235 party_intf_rec.account2_attribute5,
2236 NULL, l_miss_char,
2237 l_miss_char, NULL,
2238 party_intf_rec.account2_attribute5
2239 ),
2240 DECODE (
2241 party_intf_rec.account2_attribute6,
2242 NULL, l_miss_char,
2243 l_miss_char, NULL,
2244 party_intf_rec.account2_attribute6
2245 ),
2246 DECODE (
2247 party_intf_rec.account2_attribute7,
2248 NULL, l_miss_char,
2249 l_miss_char, NULL,
2250 party_intf_rec.account2_attribute7
2251 ),
2252 DECODE (
2253 party_intf_rec.account2_attribute8,
2254 NULL, l_miss_char,
2255 l_miss_char, NULL,
2256 party_intf_rec.account2_attribute8
2257 ),
2258 DECODE (
2259 party_intf_rec.account2_attribute9,
2260 NULL, l_miss_char,
2261 l_miss_char, NULL,
2262 party_intf_rec.account2_attribute9
2263 ),
2264 DECODE (
2265 party_intf_rec.account2_attribute10,
2266 NULL, l_miss_char,
2267 l_miss_char, NULL,
2268 party_intf_rec.account2_attribute10
2269 ),
2270 DECODE (
2271 party_intf_rec.account2_attribute11,
2272 NULL, l_miss_char,
2273 l_miss_char, NULL,
2274 party_intf_rec.account2_attribute11
2275 ),
2276 DECODE (
2277 party_intf_rec.account2_attribute12,
2278 NULL, l_miss_char,
2279 l_miss_char, NULL,
2280 party_intf_rec.account2_attribute12
2281 ),
2282 DECODE (
2283 party_intf_rec.account2_attribute13,
2284 NULL, l_miss_char,
2285 l_miss_char, NULL,
2286 party_intf_rec.account2_attribute13
2287 ),
2288 DECODE (
2289 party_intf_rec.account2_attribute14,
2290 NULL, l_miss_char,
2291 l_miss_char, NULL,
2292 party_intf_rec.account2_attribute14
2293 ),
2294 DECODE (
2295 party_intf_rec.account2_attribute15,
2296 NULL, l_miss_char,
2297 l_miss_char, NULL,
2298 party_intf_rec.account2_attribute15
2299 ),
2300 fnd_api.g_false, --CALL_CONTRACTS
2301 l_miss_num --VLD_ORGANIZATION_ID
2302 INTO x_account_tbl (ptyacc_idx).party_account_id,
2303 x_account_tbl (ptyacc_idx).relationship_type_code,
2304 x_account_tbl (ptyacc_idx).bill_to_address,
2305 x_account_tbl (ptyacc_idx).ship_to_address,
2306 x_account_tbl (ptyacc_idx).active_start_date,
2307 x_account_tbl (ptyacc_idx).active_end_date,
2308 x_account_tbl (ptyacc_idx).CONTEXT,
2309 x_account_tbl (ptyacc_idx).attribute1,
2310 x_account_tbl (ptyacc_idx).attribute2,
2311 x_account_tbl (ptyacc_idx).attribute3,
2312 x_account_tbl (ptyacc_idx).attribute4,
2313 x_account_tbl (ptyacc_idx).attribute5,
2314 x_account_tbl (ptyacc_idx).attribute6,
2315 x_account_tbl (ptyacc_idx).attribute7,
2316 x_account_tbl (ptyacc_idx).attribute8,
2317 x_account_tbl (ptyacc_idx).attribute9,
2318 x_account_tbl (ptyacc_idx).attribute10,
2319 x_account_tbl (ptyacc_idx).attribute11,
2320 x_account_tbl (ptyacc_idx).attribute12,
2321 x_account_tbl (ptyacc_idx).attribute13,
2322 x_account_tbl (ptyacc_idx).attribute14,
2323 x_account_tbl (ptyacc_idx).attribute15,
2327 END IF; ---party_account2
2324 x_account_tbl (ptyacc_idx).call_contracts,
2325 x_account_tbl (ptyacc_idx).vld_organization_id
2326 FROM DUAL;
2328
2329 ---Populate Party Account3
2330 IF party_intf_rec.party_account3_id IS NOT NULL
2331 THEN -- Put record in Table
2332 ptyacc_idx := ptyacc_idx
2333 + 1;
2334 x_account_tbl (ptyacc_idx).ip_account_id :=
2335 party_intf_rec.ip_account3_id;
2336 x_account_tbl (ptyacc_idx).instance_party_id :=
2337 party_intf_rec.instance_party_id;
2338 x_account_tbl (ptyacc_idx).parent_tbl_index := prty_idx;
2339 OPEN ipa_obj_ver_cur (party_intf_rec.ip_account3_id);
2340 FETCH ipa_obj_ver_cur INTO l_ipa3_object_version,
2341 l_last_update_date;
2342 CLOSE ipa_obj_ver_cur;
2343 IF l_last_update_date > inst_intf_rec.source_transaction_date
2344 THEN
2345 fnd_message.set_name('CSI','CSI_INTERFACE_RESTRICTION');
2346 fnd_message.set_token('instance_id',inst_intf_rec.instance_id);
2347 l_error_message := fnd_message.get;
2348 RAISE e_restriction;
2349 END IF;
2350 x_account_tbl (ptyacc_idx).object_version_number :=
2351 l_ipa3_object_version;
2352
2353 SELECT DECODE (
2354 party_intf_rec.party_account3_id,
2355 NULL, l_miss_num,
2356 l_miss_num, NULL,
2357 party_intf_rec.party_account3_id
2358 ),
2359 DECODE (
2360 party_intf_rec.acct3_relationship_type_code,
2361 NULL, l_miss_char,
2362 l_miss_char, NULL,
2363 party_intf_rec.acct3_relationship_type_code
2364 ),
2365 DECODE (
2366 party_intf_rec.bill_to_address3,
2367 NULL, l_miss_num,
2368 l_miss_num, NULL,
2369 party_intf_rec.bill_to_address3
2370 ),
2371 DECODE (
2372 party_intf_rec.ship_to_address3,
2373 NULL, l_miss_num,
2374 l_miss_num, NULL,
2375 party_intf_rec.ship_to_address3
2376 ),
2377 l_miss_date, -- ACTIVE_START_DATE
2378 DECODE (
2379 party_intf_rec.party_acct3_end_date,
2380 NULL, l_miss_date,
2381 l_miss_date, NULL,
2382 party_intf_rec.party_acct3_end_date
2383 ),
2384 DECODE (
2385 party_intf_rec.account3_context,
2386 NULL, l_miss_char,
2387 l_miss_char, NULL,
2388 party_intf_rec.account3_context
2389 ),
2390 DECODE (
2391 party_intf_rec.account3_attribute1,
2392 NULL, l_miss_char,
2393 l_miss_char, NULL,
2394 party_intf_rec.account3_attribute1
2395 ),
2396 DECODE (
2397 party_intf_rec.account3_attribute2,
2398 NULL, l_miss_char,
2399 l_miss_char, NULL,
2400 party_intf_rec.account3_attribute2
2401 ),
2402 DECODE (
2403 party_intf_rec.account3_attribute3,
2404 NULL, l_miss_char,
2405 l_miss_char, NULL,
2406 party_intf_rec.account3_attribute3
2407 ),
2408 DECODE (
2409 party_intf_rec.account3_attribute4,
2410 NULL, l_miss_char,
2411 l_miss_char, NULL,
2412 party_intf_rec.account3_attribute4
2413 ),
2414 DECODE (
2415 party_intf_rec.account3_attribute5,
2416 NULL, l_miss_char,
2417 l_miss_char, NULL,
2418 party_intf_rec.account3_attribute5
2419 ),
2420 DECODE (
2421 party_intf_rec.account3_attribute6,
2422 NULL, l_miss_char,
2423 l_miss_char, NULL,
2424 party_intf_rec.account3_attribute6
2425 ),
2426 DECODE (
2427 party_intf_rec.account3_attribute7,
2428 NULL, l_miss_char,
2429 l_miss_char, NULL,
2430 party_intf_rec.account3_attribute7
2431 ),
2432 DECODE (
2433 party_intf_rec.account3_attribute8,
2434 NULL, l_miss_char,
2435 l_miss_char, NULL,
2436 party_intf_rec.account3_attribute8
2437 ),
2438 DECODE (
2439 party_intf_rec.account3_attribute9,
2440 NULL, l_miss_char,
2441 l_miss_char, NULL,
2442 party_intf_rec.account3_attribute9
2443 ),
2444 DECODE (
2445 party_intf_rec.account3_attribute10,
2446 NULL, l_miss_char,
2447 l_miss_char, NULL,
2448 party_intf_rec.account3_attribute10
2452 NULL, l_miss_char,
2449 ),
2450 DECODE (
2451 party_intf_rec.account3_attribute11,
2453 l_miss_char, NULL,
2454 party_intf_rec.account3_attribute11
2455 ),
2456 DECODE (
2457 party_intf_rec.account3_attribute12,
2458 NULL, l_miss_char,
2459 l_miss_char, NULL,
2460 party_intf_rec.account3_attribute12
2461 ),
2462 DECODE (
2463 party_intf_rec.account3_attribute13,
2464 NULL, l_miss_char,
2465 l_miss_char, NULL,
2466 party_intf_rec.account3_attribute13
2467 ),
2468 DECODE (
2469 party_intf_rec.account3_attribute14,
2470 NULL, l_miss_char,
2471 l_miss_char, NULL,
2472 party_intf_rec.account3_attribute14
2473 ),
2474 DECODE (
2475 party_intf_rec.account3_attribute15,
2476 NULL, l_miss_char,
2477 l_miss_char, NULL,
2478 party_intf_rec.account3_attribute15
2479 ),
2480 fnd_api.g_false, --CALL_CONTRACTS
2481 l_miss_num --VLD_ORGANIZATION_ID
2482 INTO x_account_tbl (ptyacc_idx).party_account_id,
2483 x_account_tbl (ptyacc_idx).relationship_type_code,
2484 x_account_tbl (ptyacc_idx).bill_to_address,
2485 x_account_tbl (ptyacc_idx).ship_to_address,
2486 x_account_tbl (ptyacc_idx).active_start_date,
2487 x_account_tbl (ptyacc_idx).active_end_date,
2488 x_account_tbl (ptyacc_idx).CONTEXT,
2489 x_account_tbl (ptyacc_idx).attribute1,
2490 x_account_tbl (ptyacc_idx).attribute2,
2491 x_account_tbl (ptyacc_idx).attribute3,
2492 x_account_tbl (ptyacc_idx).attribute4,
2493 x_account_tbl (ptyacc_idx).attribute5,
2494 x_account_tbl (ptyacc_idx).attribute6,
2495 x_account_tbl (ptyacc_idx).attribute7,
2496 x_account_tbl (ptyacc_idx).attribute8,
2497 x_account_tbl (ptyacc_idx).attribute9,
2498 x_account_tbl (ptyacc_idx).attribute10,
2499 x_account_tbl (ptyacc_idx).attribute11,
2500 x_account_tbl (ptyacc_idx).attribute12,
2501 x_account_tbl (ptyacc_idx).attribute13,
2502 x_account_tbl (ptyacc_idx).attribute14,
2503 x_account_tbl (ptyacc_idx).attribute15,
2504 x_account_tbl (ptyacc_idx).call_contracts,
2505 x_account_tbl (ptyacc_idx).vld_organization_id
2506 FROM DUAL;
2507 END IF; ---party_account3
2508
2509 IF party_intf_rec.contact_party_id IS NOT NULL AND
2510 party_intf_rec.contact_party_id <> fnd_api.g_miss_num
2511 THEN
2512 prty_idx:=prty_idx + 1;
2513 x_party_tbl(prty_idx).instance_party_id:=fnd_api.g_miss_num;
2514 x_party_tbl(prty_idx).instance_id:=inst_intf_rec.instance_id;
2515 x_party_tbl(prty_idx).party_source_table:=party_intf_rec.party_source_table;
2516 x_party_tbl(prty_idx).party_id:=party_intf_rec.contact_party_id;
2517 x_party_tbl(prty_idx).relationship_type_code:=party_intf_rec.contact_party_rel_type;
2518 x_party_tbl(prty_idx).contact_flag:='Y';
2519 x_party_tbl(prty_idx).contact_parent_tbl_index:=prty_idx-1;
2520 x_party_tbl(prty_idx).parent_tbl_index:=inst_idx;
2521 x_party_tbl(prty_idx).contact_ip_id:=party_intf_rec.instance_party_id ;
2522 END IF;
2523
2524 END LOOP; ---party_intf_cur
2525
2526
2527 ---Populate Extended Attributes
2528
2529 FOR ext_attrib_intf_rec IN ext_attrib_intf_cur (inst_intf_rec.inst_interface_id)
2530 LOOP
2531 extatt_idx := extatt_idx
2532 + 1;
2533
2534 SELECT DECODE (
2535 inst_intf_rec.instance_id,
2536 NULL, l_miss_num,
2537 l_miss_num, NULL,
2538 inst_intf_rec.instance_id
2539 ),
2540 DECODE (
2541 ext_attrib_intf_rec.attribute_id,
2542 NULL, l_miss_num,
2543 l_miss_num, NULL,
2544 ext_attrib_intf_rec.attribute_id
2545 ),
2546 DECODE (
2547 ext_attrib_intf_rec.attribute_value_id,
2548 NULL, l_miss_num,
2549 l_miss_num, NULL,
2550 ext_attrib_intf_rec.attribute_value_id
2551 ),
2552 DECODE (
2553 ext_attrib_intf_rec.attribute_code,
2554 NULL, l_miss_char,
2555 l_miss_char, NULL,
2556 ext_attrib_intf_rec.attribute_code
2557 ),
2558 DECODE (
2559 ext_attrib_intf_rec.attribute_value,
2560 NULL, l_miss_char,
2561 l_miss_char, NULL,
2562 ext_attrib_intf_rec.attribute_value
2563 ),
2564 l_miss_date, -- ACTIVE_START_DATE
2565 DECODE (
2566 ext_attrib_intf_rec.ieav_end_date,
2567 NULL, l_miss_date,
2568 l_miss_date, NULL,
2569 ext_attrib_intf_rec.ieav_end_date
2570 ),
2571 l_miss_char,
2575 l_miss_char,
2572 l_miss_char,
2573 l_miss_char,
2574 l_miss_char,
2576 l_miss_char,
2577 l_miss_char,
2578 l_miss_char,
2579 l_miss_char,
2580 l_miss_char,
2581 l_miss_char,
2582 l_miss_char,
2583 l_miss_char,
2584 l_miss_char,
2585 l_miss_char,
2586 l_miss_char,
2587 DECODE (
2588 ext_attrib_intf_rec.ieav_object_ver_num,
2589 NULL, l_miss_num,
2590 l_miss_num, NULL,
2591 ext_attrib_intf_rec.ieav_object_ver_num
2592 )
2593 -- OBJECT_VERSION_NUMBER
2594 INTO x_ext_attrib_value_tbl (extatt_idx).instance_id,
2595 x_ext_attrib_value_tbl (extatt_idx).attribute_id,
2596 x_ext_attrib_value_tbl (extatt_idx).attribute_value_id,
2597 x_ext_attrib_value_tbl (extatt_idx).attribute_code,
2598 x_ext_attrib_value_tbl (extatt_idx).attribute_value,
2599 x_ext_attrib_value_tbl (extatt_idx).active_start_date,
2600 x_ext_attrib_value_tbl (extatt_idx).active_end_date,
2601 x_ext_attrib_value_tbl (extatt_idx).CONTEXT,
2602 x_ext_attrib_value_tbl (extatt_idx).attribute1,
2603 x_ext_attrib_value_tbl (extatt_idx).attribute2,
2604 x_ext_attrib_value_tbl (extatt_idx).attribute3,
2605 x_ext_attrib_value_tbl (extatt_idx).attribute4,
2606 x_ext_attrib_value_tbl (extatt_idx).attribute5,
2607 x_ext_attrib_value_tbl (extatt_idx).attribute6,
2608 x_ext_attrib_value_tbl (extatt_idx).attribute7,
2609 x_ext_attrib_value_tbl (extatt_idx).attribute8,
2610 x_ext_attrib_value_tbl (extatt_idx).attribute9,
2611 x_ext_attrib_value_tbl (extatt_idx).attribute10,
2612 x_ext_attrib_value_tbl (extatt_idx).attribute11,
2613 x_ext_attrib_value_tbl (extatt_idx).attribute12,
2614 x_ext_attrib_value_tbl (extatt_idx).attribute13,
2615 x_ext_attrib_value_tbl (extatt_idx).attribute14,
2616 x_ext_attrib_value_tbl (extatt_idx).attribute15,
2617 x_ext_attrib_value_tbl (extatt_idx).object_version_number
2618 FROM DUAL;
2619 END LOOP; ---ext_attrib_intf_cur
2620
2621 --bnarayan added for R12
2622 -- Populate asset records
2623
2624 FOR asset_attrib_intf_rec IN asset_attrib_intf_cur (inst_intf_rec.inst_interface_id, inst_intf_rec.instance_id)
2625 LOOP
2626
2627 IF asset_attrib_intf_rec.csia_instance_asset_id IS NULL THEN
2628 x_asset_assignment_tbl( asset_idx ).instance_asset_id := l_miss_num;
2629 ELSE
2630 x_asset_assignment_tbl( asset_idx ).instance_asset_id := asset_attrib_intf_rec.csia_instance_asset_id ;
2631 END IF;
2632
2633 IF inst_intf_rec.instance_id IS NULL THEN
2634 x_asset_assignment_tbl( asset_idx ).instance_id := l_miss_num;
2635 ELSE
2636 x_asset_assignment_tbl( asset_idx ).instance_id := inst_intf_rec.instance_id ;
2637 END IF;
2638
2639 IF asset_attrib_intf_rec.fa_asset_id IS NULL THEN
2640 x_asset_assignment_tbl( asset_idx ).fa_asset_id := l_miss_num;
2641 ELSE
2642 x_asset_assignment_tbl( asset_idx ).fa_asset_id := asset_attrib_intf_rec.fa_asset_id ;
2643 END IF;
2644
2645 IF asset_attrib_intf_rec.fa_book_type_code IS NULL THEN
2646 x_asset_assignment_tbl( asset_idx ).fa_book_type_code := l_miss_char;
2647 ELSE
2648 x_asset_assignment_tbl( asset_idx ).fa_book_type_code := asset_attrib_intf_rec.fa_book_type_code;
2649 END IF;
2650
2651 IF asset_attrib_intf_rec.fa_location_id IS NULL THEN
2652 x_asset_assignment_tbl( asset_idx ).fa_location_id :=l_miss_num;
2653 ELSE
2654 x_asset_assignment_tbl( asset_idx ).fa_location_id := asset_attrib_intf_rec.fa_location_id ;
2655 END IF;
2656
2657 IF asset_attrib_intf_rec.asset_quantity IS NULL THEN
2658 x_asset_assignment_tbl( asset_idx ).asset_quantity := l_miss_num;
2659 ELSE
2660 x_asset_assignment_tbl( asset_idx ).asset_quantity := asset_attrib_intf_rec.asset_quantity ;
2661 END IF;
2662
2663 IF asset_attrib_intf_rec.update_status IS NULL THEN
2664 x_asset_assignment_tbl( asset_idx ).update_status :=l_miss_char;
2665 ELSE
2666 x_asset_assignment_tbl( asset_idx ).update_status := asset_attrib_intf_rec.update_status ;
2667 END IF;
2668
2669 IF asset_attrib_intf_rec.active_start_date IS NULL THEN
2670 x_asset_assignment_tbl( asset_idx ).active_start_date := l_miss_date;
2671 ELSE
2672 x_asset_assignment_tbl( asset_idx ).active_start_date := asset_attrib_intf_rec.active_start_date ;
2673 END IF;
2674
2675 IF asset_attrib_intf_rec.active_end_date IS NULL THEN
2676 x_asset_assignment_tbl( asset_idx ).active_end_date := l_miss_date;
2677 ELSE
2678 x_asset_assignment_tbl( asset_idx ).active_end_date := asset_attrib_intf_rec.active_end_date;
2679 END IF;
2680
2681 x_asset_assignment_tbl( asset_idx ).fa_sync_flag :=asset_attrib_intf_rec.fa_sync_flag;
2682 x_asset_assignment_tbl( asset_idx ).object_version_number:=asset_attrib_intf_rec.asset_object_ver_num;
2683 x_asset_assignment_tbl( asset_idx ).parent_tbl_index :=inst_idx;
2684 asset_idx := asset_idx + 1; -- Increment asset index
2685
2686 END LOOP; --end of asset loop
2687 -- Start code addition for bug 6368180, section 2 of 2
2688 END IF; ---IF inst_intf_rec.instance_id <> fnd_api.g_miss_num
2689 -- End code addition for bug 6368180, section 2 of 2
2690 END LOOP; ---inst_intf_cur
2691 EXCEPTION
2692 WHEN e_restriction
2693 THEN
2694 x_return_status := fnd_api.g_ret_sts_error;
2695 x_error_message := l_error_message;
2696 WHEN OTHERS
2697 THEN
2698 x_return_status := fnd_api.g_ret_sts_unexp_error;
2699 x_error_message := SQLERRM;
2700
2701 END populate_recs;
2702
2703 END csi_ml_update_pvt;
2704