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