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