[Home] [Help]
PACKAGE BODY: APPS.CSI_ML_INTERFACE_TXN_PVT
Source
1 PACKAGE BODY CSI_ML_INTERFACE_TXN_PVT AS
2 -- $Header: csimtxnb.pls 120.22.12020000.3 2013/01/24 21:58:57 rasraja ship $
3
4 PROCEDURE instance_exists(p_inst_interface_id IN NUMBER,
5 x_instance_id OUT NOCOPY NUMBER,
6 x_return_status OUT NOCOPY VARCHAR2,
7 x_error_message OUT NOCOPY VARCHAR2) IS
8
9 l_instance_id NUMBER;
10 l_serial_code NUMBER;
11 l_fnd_unexpected VARCHAR2(1);
12 e_exists EXCEPTION;
13 CURSOR instance_id_cur(p_interface_id IN NUMBER) IS
14 SELECT instance_id FROM csi_instance_interface
15 WHERE inst_interface_id = p_interface_id;
16
17 CURSOR serial_cur(p_interface_id IN NUMBER) IS
18 SELECT cii.instance_id
19 FROM csi_item_instances csi,
20 csi_instance_interface cii
21 WHERE cii.inst_interface_id = p_interface_id
22 AND csi.inventory_item_id = cii.inventory_item_id
23 AND csi.serial_number = cii.serial_number;
24
25 CURSOR nonserial_cur(p_interface_id IN NUMBER) IS
26 SELECT a.instance_id
27 FROM csi_item_instances a,
28 csi_i_parties b,
29 csi_instance_interface c,
30 csi_i_party_interface d
31 WHERE a.instance_id = b.instance_id
32 AND a.inventory_item_id = c.inventory_item_id
33 AND c.inst_interface_id = d.inst_interface_id
34 AND c.inst_interface_id = p_interface_id
35 AND a.instance_usage_code NOT IN ('IN_RELATIONSHIP','RETURNED')
36 AND ( (a.inventory_revision IS NULL AND c.inventory_revision IS NULL) OR (a.inventory_revision IS NULL AND c.inventory_revision = FND_API.G_MISS_CHAR) OR (a.inventory_revision = c.inventory_revision))
37 AND ( (a.lot_number IS NULL AND c.lot_number IS NULL) OR (a.lot_number IS NULL AND c.lot_number = FND_API.G_MISS_CHAR) OR (a.lot_number = c.lot_number))
38 AND a.inv_organization_id = c.inv_organization_id
39 AND a.inv_subinventory_name = c.inv_subinventory_name
40 AND ( (a.inv_locator_id IS NULL AND c.inv_locator_id IS NULL) OR (a.inv_locator_id IS NULL AND c.inv_locator_id = FND_API.G_MISS_NUM) OR (a.inv_locator_id = c.inv_locator_id))
41 AND b.party_id = d.party_id
42 AND b.party_source_table = d.party_source_table
43 AND b.relationship_type_code = 'OWNER';
44
45 CURSOR serial_control_cur(p_interface_id IN NUMBER) IS
46 select msi.serial_number_control_code
47 from mtl_system_items msi,
48 csi_instance_interface cii
49 where msi.inventory_item_id = cii.inventory_item_id
50 and msi.organization_id = cii.inv_organization_id
51 and cii.inst_interface_id = p_interface_id;
52
53 BEGIN
54 l_fnd_unexpected := FND_API.G_RET_STS_UNEXP_ERROR;
55 x_return_status := 'E'; --fnd_api.g_ret_sts_success;
56 x_error_message := NULL;
57 OPEN instance_id_cur(p_inst_interface_id);
58 FETCH instance_id_cur INTO l_instance_id;
59 CLOSE instance_id_cur;
60 IF l_instance_id IS NOT NULL
61 THEN
62 RAISE e_exists;
63 END IF;
64 x_instance_id := l_instance_id;
65 EXCEPTION
66 WHEN e_exists THEN
67 x_instance_id := l_instance_id;
68 x_return_status := fnd_api.g_ret_sts_success;
69 WHEN others THEN
70 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
71 fnd_message.set_token('API_NAME','CSI_ML_INTERFACE_TXNS_PVT.INSTANCE_EXISTS');
72 fnd_message.set_token('SQL_ERROR',SQLERRM);
73 x_error_message := fnd_message.get;
74 x_return_status := l_fnd_unexpected;
75
76 END instance_exists;
77
78 PROCEDURE check_rel_exists(p_subect_id IN NUMBER,
79 p_object_id IN NUMBER,
80 x_exists OUT NOCOPY BOOLEAN,
81 x_relation_rec OUT NOCOPY csi_datastructures_pub.ii_relationship_rec) IS
82 CURSOR rel_cur(p_sub_id IN NUMBER,
83 p_ob_id IN NUMBER) IS
84 SELECT *
85 FROM csi_ii_relationships
86 WHERE subject_id = p_sub_id
87 AND object_id = p_ob_id;
88 t_rel_rec rel_cur%ROWTYPE;
89 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CSI_DEBUG_LEVEL'), '0'));
90 BEGIN
91 x_exists := FALSE;
92 OPEN rel_cur(p_subect_id,p_object_id);
93 FETCH rel_cur INTO t_rel_rec;
94 IF rel_cur%FOUND
95 THEN x_exists := TRUE;
96 x_relation_rec.relationship_id := t_rel_rec.relationship_id;
97 IF(l_debug_level>1) THEN
98 FND_File.Put_Line(Fnd_File.LOG,'relationship exists: '||t_rel_rec.relationship_id);
99 END IF;
100 x_relation_rec.relationship_type_code := t_rel_rec.relationship_type_code;
101 x_relation_rec.active_start_date:= t_rel_rec.active_start_date;
102 x_relation_rec.active_end_date:= t_rel_rec.active_end_date;
103 x_relation_rec.object_version_number := t_rel_rec.object_version_number;
104 x_relation_rec.display_order:= t_rel_rec.display_order;
105 x_relation_rec.position_reference:= t_rel_rec.position_reference;
106 END IF;
107 CLOSE rel_cur;
108 EXCEPTION
109 WHEN OTHERS
110 THEN x_exists:=FALSE;
111 END check_rel_exists;
112
113 PROCEDURE process_iface_txns(x_return_status OUT NOCOPY VARCHAR2 ,
114 x_error_message OUT NOCOPY VARCHAR2 ,
115 p_txn_from_date IN VARCHAR2 ,
116 p_txn_to_date IN VARCHAR2 ,
117 p_source_system_name IN VARCHAR2,
118 p_batch_name IN VARCHAR2,
119 p_resolve_ids IN VARCHAR2) IS
120 l_api_version NUMBER:=1.0;
121 l_return_status VARCHAR2(1);
122 l_msg_index NUMBER;
123 l_msg_count NUMBER;
124 l_msg_data VARCHAR2(2000);
125 l_error_message VARCHAR2(2000);
126 l_instance_id NUMBER;
127 l_def_usage_code VARCHAR2(30) := 'OUT_OF_ENTERPRISE';
128 c_instance_tbl csi_datastructures_pub.instance_tbl;
129 c_ext_attrib_tbl csi_datastructures_pub.extend_attrib_values_tbl;
130 c_party_tbl csi_datastructures_pub.party_tbl;
131 c_party_contact_tbl csi_ml_util_pvt.party_contact_tbl_type;
132 c_account_tbl csi_datastructures_pub.party_account_tbl;
133 c_price_tbl csi_datastructures_pub.pricing_attribs_tbl;
134 c_org_assign_tbl csi_datastructures_pub.organization_units_tbl;
135 c_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
136 c_txn_tbl csi_datastructures_pub.transaction_tbl;
137 c_rel_txn_rec csi_datastructures_pub.transaction_rec;
138 c_grp_error_tbl csi_datastructures_pub.grp_error_tbl;
139
140 u_instance_tbl csi_datastructures_pub.instance_tbl;
141 u_ext_attrib_tbl csi_datastructures_pub.extend_attrib_values_tbl;
142 u_party_tbl csi_datastructures_pub.party_tbl;
143 u_account_tbl csi_datastructures_pub.party_account_tbl;
144 u_price_tbl csi_datastructures_pub.pricing_attribs_tbl;
145 u_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
146 u_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
147 u_txn_rec csi_datastructures_pub.transaction_rec;
148 u_rel_txn_rec csi_datastructures_pub.transaction_rec;
149 u_grp_error_tbl csi_datastructures_pub.grp_upd_error_tbl;
150 u_instance_id_lst csi_datastructures_pub.id_tbl;
151
152 g_exc_error EXCEPTION;
153 g_inst_error EXCEPTION;
154 g_upd_error EXCEPTION;
155
156 l_found NUMBER:=0;
157 l_rel_success_count NUMBER:=0;
158 l_rel_failure_count NUMBER:=0;
159 l_counter NUMBER:=0;
160
161
162 c_relationship_tbl csi_datastructures_pub.ii_relationship_tbl;
163 u_relationship_tbl csi_datastructures_pub.ii_relationship_tbl;
164 l_rel_rec csi_datastructures_pub.ii_relationship_rec;
165 l_exists BOOLEAN;
166 inst_idx PLS_INTEGER;
167 prty_idx PLS_INTEGER;
168 ptyacc_idx PLS_INTEGER;
169 extatt_idx PLS_INTEGER;
170 orgass_idx PLS_INTEGER;
171 price_idx PLS_INTEGER;
172 rel_idx PLS_INTEGER;
173 prty_contact_idx PLS_INTEGER;
174 u_inst_idx PLS_INTEGER; --Added for open
175 asset_idx PLS_INTEGER; -- Asset Index
176
177 l_debug_level NUMBER:= to_number(nvl(fnd_profile.value('CSI_DEBUG_LEVEL'), '0'));
178
179
180 l_commit VARCHAR2(1) ;
181 l_init_msg_list VARCHAR2(1);
182 l_validation_level NUMBER;
183 l_fnd_success VARCHAR2(1);
184 l_fnd_unexpected VARCHAR2(1);
185 l_fnd_g_char VARCHAR2(1);
186 l_fnd_g_num NUMBER;
187 l_fnd_g_date DATE ;
188 l_fnd_g_true VARCHAR2(1);
189 l_fnd_g_false VARCHAR2(1); --bug#13072088
190 l_api_name VARCHAR2(255):=
191 'CSI_ML_INTERFACE_TXN_PVT.PROCESS_IFACE_TXN';
192 l_txn_type_id NUMBER;
193 g_int_party NUMBER;
194 l_txn_count NUMBER:=0;
195 l_success_count NUMBER:=0;
196 l_failure_count NUMBER:=0;
197 -- Bug 9526806
198 l_txn_from_date DATE := trunc(to_date(p_txn_from_date, 'YYYY/MM/DD HH24:MI:SS'));
199 l_txn_to_date DATE := trunc(to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS'));
200 l_enable_contracts_oi VARCHAR2(1) := NVL(FND_PROFILE.VALUE('CSI_ENABLE_CONTRACTS_FOR_MASS_LOAD'),'Y'); --bug#13072088
201
202
203 -- Bug 9526806
204 CURSOR get_txns_cur( p_source_system IN VARCHAR2,
205 p_batch IN VARCHAR2) IS
206 SELECT distinct transaction_identifier
207 FROM csi_instance_interface cii
208 WHERE (NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch,cii.batch_name)
209 OR NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch,'$CSI_NULL_VALUE$'))
210 AND cii.source_system_name = p_source_system
211 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))
212 AND cii.process_status = 'R';
213
214 CURSOR iface_det_cur(p_txn_ident IN VARCHAR2,
215 p_source_system IN VARCHAR2) IS
216 SELECT cii.*
217 FROM csi_instance_interface cii
218 WHERE cii.transaction_identifier = p_txn_ident
219 AND cii.source_system_name = p_source_system
220 AND cii.process_status = 'R';
221
222 CURSOR iparty_det_cur(p_inst_interface_id IN NUMBER) IS
223 SELECT cpi.*
224 FROM csi_i_party_interface cpi
225 WHERE cpi.inst_interface_id = p_inst_interface_id;
226
227 CURSOR ieav_det_cur(p_inst_interface_id IN NUMBER) IS
228 SELECT ci.*
229 FROM csi_iea_value_interface ci
230 WHERE ci.inst_interface_id = p_inst_interface_id;
231
232 CURSOR iasset_iface_cur(p_inst_interface_id IN NUMBER) IS
233 SELECT cia.*
234 FROM csi_i_asset_interface cia
235 WHERE cia.inst_interface_id = p_inst_interface_id; --bnarayan added for open interfaces R12
236
237
238 /*CURSOR irel_det_cur(p_txn_ident IN VARCHAR2,
239 p_source_system IN VARCHAR2) IS
240 SELECT ciri.relationship_type_code relationship_type_code,
241 ciri.subject_interface_id subject_interface_id,
242 ciri.object_interface_id object_interface_id,
243 ciri.position_reference position_reference,
244 ciri.relationship_start_date active_start_date,
245 ciri.relationship_end_date active_end_date,
246 ciri.display_order display_order,
247 ciri.mandatory_flag mandatory_flag,
248 ciri.context context,
249 ciri.attribute1 attribute1,
250 ciri.attribute2 attribute2,
251 ciri.attribute3 attribute3,
252 ciri.attribute4 attribute4,
253 ciri.attribute5 attribute5,
254 ciri.attribute6 attribute6,
255 ciri.attribute7 attribute7,
256 ciri.attribute8 attribute8,
257 ciri.attribute9 attribute9,
258 ciri.attribute10 attribute10,
259 ciri.attribute11 attribute11,
260 ciri.attribute12 attribute12,
261 ciri.attribute13 attribute13,
262 ciri.attribute14 attribute14,
263 ciri.attribute15 attribute15,
264 ciri.relationship_direction,
265 ciri.created_by created_by,
266 cii1.instance_id new_subject_id,
267 cii2.instance_id new_object_id,
268 cii1.source_transaction_date source_transaction_date,
269 cii1.transaction_identifier transaction_identifier
270 FROM csi_ii_relation_interface ciri,
271 csi_instance_interface cii1,
272 csi_instance_interface cii2
273 WHERE ciri.subject_interface_id = cii1.inst_interface_id
274 AND cii1.transaction_identifier = p_txn_ident
275 AND cii1.source_system_name = p_source_system
276 AND ciri.object_interface_id = cii2.inst_interface_id
277 AND cii2.transaction_identifier = p_txn_ident
278 AND cii2.source_system_name = p_source_system
279 AND cii1.process_status IN ('P')
280 AND cii2.process_status IN ('P'); */
281
282 CURSOR irel_det_cur IS
283 SELECT ciri.rel_interface_id rel_interface_id,
284 ciri.relationship_type_code relationship_type_code,
285 ciri.subject_interface_id subject_interface_id,
286 ciri.object_interface_id object_interface_id,
287 ciri.position_reference position_reference,
288 ciri.relationship_start_date active_start_date,
289 ciri.relationship_end_date active_end_date,
290 ciri.display_order display_order,
291 ciri.mandatory_flag mandatory_flag,
292 ciri.context context,
293 ciri.attribute1 attribute1,
294 ciri.attribute2 attribute2,
295 ciri.attribute3 attribute3,
296 ciri.attribute4 attribute4,
297 ciri.attribute5 attribute5,
298 ciri.attribute6 attribute6,
299 ciri.attribute7 attribute7,
300 ciri.attribute8 attribute8,
301 ciri.attribute9 attribute9,
302 ciri.attribute10 attribute10,
303 ciri.attribute11 attribute11,
304 ciri.attribute12 attribute12,
305 ciri.attribute13 attribute13,
306 ciri.attribute14 attribute14,
307 ciri.attribute15 attribute15,
308 ciri.relationship_direction,
309 ciri.created_by created_by,
310 ciri.subject_id subject_id,
311 ciri.object_id object_id
312 FROM csi_ii_relation_interface ciri
313 WHERE ciri.process_status IN ('R')
314 AND (nvl(ciri.source_system_name, '$CSI_NULL_VALUE$') = nvl(p_source_system_name, '$CSI_NULL_VALUE$')
315 or nvl(ciri.source_system_name, '$CSI_NULL_VALUE$') = nvl(p_source_system_name, ciri.source_system_name)
316 )/*Added for 6443959*/;
317
318
319 CURSOR internal_party_cur IS
320 SELECT internal_party_id FROM csi_install_parameters;
321
322 PROCEDURE UPDATE_INTERFACE_TBL
323 (p_instance_tbl IN csi_datastructures_pub.instance_tbl
324 ,p_grp_error_tbl IN csi_datastructures_pub.grp_error_tbl)
325 IS
326 --
327 l_intf_id_array dbms_sql.Number_Table;
328 l_error_array dbms_sql.Varchar2_Table;
329 l_status_array dbms_sql.Varchar2_Table;
330 l_num_of_rows NUMBER;
331 l_upd_stmt VARCHAR2(2000);
332 l_dummy NUMBER;
333 BEGIN
334 FOR j IN p_instance_tbl.FIRST .. p_instance_tbl.LAST
335 LOOP
336 IF p_instance_tbl.EXISTS(j) THEN
337 l_intf_id_array(j) := p_instance_tbl(j).interface_id;
338 IF p_grp_error_tbl(j).error_message IS NOT NULL THEN
339 l_error_array(j) := p_grp_error_tbl(j).error_message;
340 ELSIF p_grp_error_tbl(j).error_message IS NULL THEN
341 l_error_array(j) := 'One or more instances with this transaction identifier failed';
342 END IF;
343 l_status_array(j) := 'E';
344 END IF;
345 END LOOP;
346 --
347 IF l_intf_id_array.count > 0 THEN
348 BEGIN
349 l_upd_stmt := 'UPDATE CSI_INSTANCE_INTERFACE
350 SET error_text = :error_text
351 ,process_status = :status
352 WHERE inst_interface_id = :intf_id';
353 l_num_of_rows := dbms_sql.open_cursor;
354 dbms_sql.parse(l_num_of_rows,l_upd_stmt,dbms_sql.native);
355 dbms_sql.bind_array(l_num_of_rows,':intf_id',l_intf_id_array);
356 dbms_sql.bind_array(l_num_of_rows,':status',l_status_array);
357 dbms_sql.bind_array(l_num_of_rows,':error_text',l_error_array);
358 l_dummy := dbms_sql.execute(l_num_of_rows);
359 dbms_sql.close_cursor(l_num_of_rows);
360 EXCEPTION
361 WHEN OTHERS THEN
362 NULL;
363 END;
364 END IF;
365 END UPDATE_INTERFACE_TBL;
366
367 PROCEDURE UPDATE_INTERFACE_TBL
368 (p_instance_tbl IN csi_datastructures_pub.instance_tbl
369 ,p_grp_upd_error_tbl IN csi_datastructures_pub.grp_upd_error_tbl)
370 IS
371 --
372 l_intf_id_array dbms_sql.Number_Table;
373 l_error_array dbms_sql.Varchar2_Table;
374 l_status_array dbms_sql.Varchar2_Table;
375 l_num_of_rows NUMBER;
376 l_upd_stmt VARCHAR2(2000);
377 l_dummy NUMBER;
378 BEGIN
379 FOR j IN p_instance_tbl.FIRST .. p_instance_tbl.LAST
380 LOOP
381 IF p_instance_tbl.EXISTS(j) THEN
382 l_intf_id_array(j) := p_instance_tbl(j).interface_id;
383 IF p_grp_upd_error_tbl(j).error_message IS NOT NULL THEN
384 l_error_array(j) := p_grp_upd_error_tbl(j).error_message;
385 ELSIF p_grp_upd_error_tbl(j).error_message IS NULL THEN
386 l_error_array(j) := 'One or more instances with this transaction identifier failed';
387 END IF;
388 l_status_array(j) := 'E';
389 END IF;
390 END LOOP;
391 --
392 IF l_intf_id_array.count > 0 THEN
393 BEGIN
394 l_upd_stmt := 'UPDATE CSI_INSTANCE_INTERFACE
395 SET error_text = :error_text
396 ,process_status = :status
397 WHERE inst_interface_id = :intf_id';
398 l_num_of_rows := dbms_sql.open_cursor;
399 dbms_sql.parse(l_num_of_rows,l_upd_stmt,dbms_sql.native);
400 dbms_sql.bind_array(l_num_of_rows,':intf_id',l_intf_id_array);
401 dbms_sql.bind_array(l_num_of_rows,':status',l_status_array);
402 dbms_sql.bind_array(l_num_of_rows,':error_text',l_error_array);
403 l_dummy := dbms_sql.execute(l_num_of_rows);
404 dbms_sql.close_cursor(l_num_of_rows);
405 EXCEPTION
406 WHEN OTHERS THEN
407 NULL;
408 END;
409 END IF;
410 END UPDATE_INTERFACE_TBL;
411
412 BEGIN
413 l_commit := fnd_api.g_false;
414 l_init_msg_list := fnd_api.g_true;
415 l_validation_level := fnd_api.g_valid_level_full;
416 l_fnd_success := FND_API.G_RET_STS_SUCCESS;
417 l_fnd_unexpected := FND_API.G_RET_STS_UNEXP_ERROR;
418 l_fnd_g_char := FND_API.G_MISS_CHAR;
419 l_fnd_g_num := FND_API.G_MISS_NUM;
420 l_fnd_g_date := FND_API.G_MISS_DATE;
421 l_fnd_g_true := FND_API.G_TRUE;
422 l_fnd_g_false := FND_API.G_FALSE; --bug#13072088
423
424 IF(l_debug_level>1) THEN
425
426 FND_File.Put_Line(Fnd_File.LOG,'Process_iface_txns-P_Source_System_Name : '||p_source_system_name);
427 FND_File.Put_Line(Fnd_File.LOG,'Process_iface_txns-P_Batch_Name : '||p_batch_name);
428 END IF;
429
430 --bug#13072088
431 IF(l_debug_level>1) THEN
432 FND_File.Put_Line(Fnd_File.LOG,'Call to contract profile value : '||l_enable_contracts_oi);
433 END IF;
434 --bug#13072088
435
436 OPEN internal_party_cur;
437 FETCH internal_party_cur INTO g_int_party;
438 CLOSE internal_party_cur;
439
440 l_txn_type_id := cse_util_pkg.get_txn_type_id('OPEN_INTERFACE','CSI');
441
442
443 UPDATE CSI_INSTANCE_INTERFACE a
444 SET a.instance_id = (SELECT b.instance_id
445 FROM csi_item_instances b
446 WHERE a.instance_number = b.instance_number)
447 WHERE a.instance_number IS NOT NULL
448 and a.instance_id is null
449 AND a.SOURCE_SYSTEM_NAME = nvl(p_source_system_name, a.SOURCE_SYSTEM_NAME); --Added this condition for #6443959
450
451
452 BEGIN
453 fnd_message.set_name('CSI','CSI_INTERFACE_LOC_TYPE_CODE');
454 l_error_message := fnd_message.get;
455
456 UPDATE CSI_INSTANCE_INTERFACE cii
457 SET error_text =l_error_message , process_status ='E'
458 WHERE (NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,cii.batch_name)
459 OR NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,'$CSI_NULL_VALUE$'))
460 AND cii.source_system_name = p_source_system_name
461 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))
462 AND cii.process_status = 'R'
463 AND cii.location_type_code in ('INVENTORY','PO','IN_TRANSIT','WIP','PROJECT');
464
465 IF SQL%FOUND THEN
466 FND_File.Put_Line(Fnd_File.LOG, l_error_message||' Total Rows in this error : '||SQL%ROWCOUNT );
467 END IF;
468
469 END;
470
471 IF NVL(p_resolve_ids,'Y') = 'Y'
472 THEN
473 IF(l_debug_level>1) THEN
474 FND_File.Put_Line(Fnd_File.LOG,'Resolving the Ids based on user values ');
475 END IF;
476 CSI_ML_UTIL_PVT.resolve_ids(p_txn_from_date,
477 p_txn_to_date,
478 p_batch_name,
479 p_source_system_name,
480 l_return_status,
481 l_error_message);
482
483 IF NOT l_return_status = l_fnd_success THEN
484 IF(l_debug_level>1) THEN
485 FND_File.Put_Line(Fnd_File.LOG,'Error Resolving the Ids: ');
486 END IF;
487 RAISE g_exc_error;
488 END IF;
489 END IF;
490
491 /*---- This piece of code required resolved ids or IDS for processing ---*/
492 BEGIN
493 fnd_message.set_name('CSI','CSI_ML_NO_ASSET_FOR_CT');
494 l_error_message := fnd_message.get;
495
496 UPDATE CSI_INSTANCE_INTERFACE cii
497 SET error_text =l_error_message
498 ,process_status ='E'
499 WHERE (NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,cii.batch_name)
500 OR NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,'$CSI_NULL_VALUE$'))
501 AND cii.source_system_name = p_source_system_name
502 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))
503 AND cii.process_status = 'R'
504 AND exists ( SELECT 1
505 FROM csi_i_party_interface cipi
506 ,csi_i_asset_interface ciai
507 WHERE cipi.inst_interface_id = ciai.inst_interface_id
508 AND cipi.inst_interface_id = cii.inst_interface_id
509 AND nvl(cipi.party_id,0) <> g_int_party
510 AND cipi.party_relationship_type_code = 'OWNER'
511 );
512
513 IF SQL%FOUND THEN
514 IF(l_debug_level>1) THEN
515 FND_File.Put_Line(Fnd_File.LOG, l_error_message||' Total Rows in this error : '||SQL%ROWCOUNT );
516 END IF;
517 END IF;
518
519
520 fnd_message.set_name('CSI','CSI_NO_ASSET_ASSN_FOUND');
521 l_error_message := fnd_message.get;
522
523 UPDATE CSI_INSTANCE_INTERFACE cii
524 SET error_text =l_error_message
525 ,process_status ='E'
526 WHERE (NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,cii.batch_name)
527 OR NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,'$CSI_NULL_VALUE$'))
528 AND cii.source_system_name = p_source_system_name
529 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))
530 AND cii.process_status = 'R'
531 AND exists (SELECT 1
532 FROM csi_i_asset_interface ciai
533 WHERE cii.inst_interface_id = ciai.inst_interface_id
534 AND ciai.fa_asset_id IS NULL
535 AND ciai.fa_asset_number IS NULL
536 );
537
538 IF SQL%FOUND THEN
539 FND_File.Put_Line(Fnd_File.LOG, l_error_message ||' Total Rows in this error : '||SQL%ROWCOUNT);
540 END IF;
541
542
543 /* Commented for bug 9924463
544 /* it is not mandatory to have an asset association for an internally owned instance.
545 /* IB UI allows us to create an internally owned instance without an asset association.
546 fnd_message.set_name('CSI','CSI_API_ASSET_REQUIRED');
547 l_error_message := fnd_message.get;
548
549 UPDATE CSI_INSTANCE_INTERFACE cii
550 SET error_text =l_error_message
551 ,process_status ='E'
552 WHERE (NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,cii.batch_name)
553 OR NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,'$CSI_NULL_VALUE$'))
554 AND cii.source_system_name = p_source_system_name
555 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))
556 AND cii.process_status = 'R'
557 AND cii.location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS')
558 AND (exists (SELECT 1
559 FROM csi_i_party_interface cipi
560 WHERE cipi.inst_interface_id = cii.inst_interface_id
561 AND nvl(cipi.party_id,0) = g_int_party
562 AND cipi.party_relationship_type_code = 'OWNER'
563 )
564 AND not exists (SELECT 1
565 FROM csi_i_asset_interface ciai
566 WHERE cii.inst_interface_id = ciai.inst_interface_id
567 ));
568
569 IF SQL%FOUND THEN
570 FND_File.Put_Line(Fnd_File.LOG, l_error_message ||' Total Rows in this error : '||SQL%ROWCOUNT);
571 END IF;
572 */ --Commented for bug 9924463
573 END;
574 l_txn_count := 0;
575 FOR get_txns_rec IN get_txns_cur(p_source_system_name,
576 p_batch_name)
577 LOOP
578 l_txn_count := l_txn_count +1;
579 IF(l_debug_level>1) THEN
580 FND_File.Put_Line(Fnd_File.LOG,'Started Processing the Transaction Identifier : '||get_txns_rec.transaction_identifier);
581 END IF;
582 c_instance_tbl.DELETE;
583 c_ext_attrib_tbl.DELETE;
584 c_party_tbl.DELETE;
585 c_party_contact_tbl.DELETE;
586 c_account_tbl.DELETE;
587 c_price_tbl.DELETE;
588 c_org_assign_tbl.DELETE;
589 c_asset_assignment_tbl.DELETE;
590 c_txn_tbl.DELETE;
591 c_grp_error_tbl.DELETE;
592 c_rel_txn_rec.transaction_id := NULL;
593
594 u_instance_tbl.DELETE;
595 u_ext_attrib_tbl.DELETE;
596 u_party_tbl.DELETE;
597 u_account_tbl.DELETE;
598 u_price_tbl.DELETE;
599 u_org_assignments_tbl.DELETE;
600 u_asset_assignment_tbl.DELETE;
601 u_txn_rec.transaction_id := NULL;
602 u_rel_txn_rec.transaction_id := NULL;
603 u_grp_error_tbl.DELETE;
604 u_instance_id_lst.DELETE;
605
606 BEGIN
607 SAVEPOINT s_txnbegin;
608 inst_idx := 1;
609 prty_idx := 1;
610 ptyacc_idx := 1;
611 extatt_idx := 1;
612 price_idx := 1;
613 orgass_idx := 1;
614 prty_contact_idx := 1;
615 u_inst_idx := 1;
616 asset_idx := 1;--Added for open
617 FOR iface_det_rec IN iface_det_cur(get_txns_rec.transaction_identifier,
618 p_source_system_name)
619 LOOP
620 IF(l_debug_level>1) THEN
621 FND_File.Put_Line(Fnd_File.LOG,'Processing Inst_interface_id : '||iface_det_rec.inst_interface_id);
622 END IF;
623 --to find out whether the instance exists
624 instance_exists(iface_det_rec.inst_interface_id,
625 l_instance_id,
626 l_return_status,
627 l_error_message);
628 IF NOT l_return_status = l_fnd_success
629 THEN -- create item instance
630
631 IF(l_debug_level>1) THEN
632 FND_File.Put_Line(Fnd_File.LOG,'Create New Instance CASE:');
633 END IF;
634 x_return_status := l_fnd_success;
635
636 c_instance_tbl(inst_idx).INSTANCE_ID := NULL;
637 c_instance_tbl(inst_idx).INSTANCE_NUMBER := iface_det_rec.INSTANCE_NUMBER; -- Enhancement 6138587
638 c_instance_tbl(inst_idx).INVENTORY_ITEM_ID := iface_det_rec.inventory_item_id;
639 c_instance_tbl(inst_idx).INTERFACE_ID := iface_det_rec.inst_interface_id;
640 IF iface_det_rec.EXTERNAL_REFERENCE IS NULL THEN
641 c_instance_tbl(inst_idx).EXTERNAL_REFERENCE := l_fnd_g_char;
642 ELSE
643 c_instance_tbl(inst_idx).EXTERNAL_REFERENCE :=
644 iface_det_rec.EXTERNAL_REFERENCE ;
645 END IF;
646 -- need to uncomment once the APIs support these fields
647 IF iface_det_rec.config_inst_hdr_id IS NULL THEN
648 c_instance_tbl(inst_idx).config_inst_hdr_id := l_fnd_g_num;
649 ELSE
650 c_instance_tbl(inst_idx).config_inst_hdr_id :=
651 iface_det_rec.config_inst_hdr_id ;
652 END IF;
653 IF iface_det_rec.config_inst_rev_num IS NULL THEN
654 c_instance_tbl(inst_idx).config_inst_rev_num:= l_fnd_g_num;
655 ELSE
656 c_instance_tbl(inst_idx).config_inst_rev_num:=
657 iface_det_rec.config_inst_rev_num;
658 END IF;
659 IF iface_det_rec.config_inst_item_id IS NULL THEN
660 c_instance_tbl(inst_idx).config_inst_item_id:= l_fnd_g_num;
661 ELSE
662 c_instance_tbl(inst_idx).config_inst_item_id:=
663 iface_det_rec.config_inst_item_id;
664 END IF;
665 IF iface_det_rec.config_valid_status IS NULL THEN
666 c_instance_tbl(inst_idx).config_valid_status:= l_fnd_g_char;
667 ELSE
668 c_instance_tbl(inst_idx).config_valid_status:=
669 iface_det_rec.config_valid_status;
670 END IF;
671 /*
672 IF iface_det_rec.inv_vld_organization_id IS NULL THEN
673 c_instance_tbl(inst_idx).INV_MASTER_ORGANIZATION_ID := l_fnd_g_num;
674 ELSE
675 c_instance_tbl(inst_idx).INV_MASTER_ORGANIZATION_ID :=
676 iface_det_rec.inv_vld_organization_id;
677 END IF;
678 */ -- Code commented for bug 3347509
679
680 IF iface_det_rec.inv_vld_organization_id IS NULL THEN
681 c_instance_tbl(inst_idx).VLD_ORGANIZATION_ID := l_fnd_g_num;
682 ELSE
683 c_instance_tbl(inst_idx).VLD_ORGANIZATION_ID := iface_det_rec.inv_vld_organization_id;
684 END IF;
685
686 IF iface_det_rec.location_type_code IS NULL THEN
687 c_instance_tbl(inst_idx).LOCATION_TYPE_CODE := l_fnd_g_char;
688 ELSE
689 c_instance_tbl(inst_idx).LOCATION_TYPE_CODE := iface_det_rec.location_type_code;
690 END IF;
691
692 IF iface_det_rec.location_id IS NULL THEN
693 c_instance_tbl(inst_idx).LOCATION_ID := l_fnd_g_num;
694 ELSE
695 c_instance_tbl(inst_idx).LOCATION_ID := iface_det_rec.location_id;
696 END IF;
697
698 IF iface_det_rec.inv_organization_id IS NULL THEN
699 c_instance_tbl(inst_idx).INV_ORGANIZATION_ID := l_fnd_g_num;
700 ELSE
701 c_instance_tbl(inst_idx).INV_ORGANIZATION_ID := iface_det_rec.inv_organization_id;
702 END IF;
703
704 IF iface_det_rec.inv_subinventory_name IS NULL THEN
705 c_instance_tbl(inst_idx).INV_SUBINVENTORY_NAME := l_fnd_g_char;
706 ELSE
707 c_instance_tbl(inst_idx).INV_SUBINVENTORY_NAME := iface_det_rec.inv_subinventory_name;
708 END IF;
709
710 IF iface_det_rec.inv_locator_id IS NULL THEN
711 c_instance_tbl(inst_idx).INV_LOCATOR_ID := l_fnd_g_num;
712 ELSE
713 c_instance_tbl(inst_idx).INV_LOCATOR_ID := iface_det_rec.inv_locator_id;
714 END IF;
715
716 IF iface_det_rec.lot_number IS NULL THEN
717 c_instance_tbl(inst_idx).LOT_NUMBER := l_fnd_g_char;
718 ELSE
719 c_instance_tbl(inst_idx).LOT_NUMBER := iface_det_rec.lot_number;
720 END IF;
721
722 IF iface_det_rec.project_id IS NULL THEN
723 c_instance_tbl(inst_idx).PA_PROJECT_ID := l_fnd_g_num;
724 ELSE
725 c_instance_tbl(inst_idx).PA_PROJECT_ID := iface_det_rec.project_id;
726 END IF;
727
728 IF iface_det_rec.task_id IS NULL THEN
729 c_instance_tbl(inst_idx).PA_PROJECT_TASK_ID := l_fnd_g_num;
730 ELSE
731 c_instance_tbl(inst_idx).PA_PROJECT_TASK_ID := iface_det_rec.task_id;
732 END IF;
733
734 IF iface_det_rec.in_transit_order_line_id IS NULL THEN
735 c_instance_tbl(inst_idx).IN_TRANSIT_ORDER_LINE_ID := l_fnd_g_num;
736 ELSE
737 c_instance_tbl(inst_idx).IN_TRANSIT_ORDER_LINE_ID := iface_det_rec.in_transit_order_line_id;
738 END IF;
739
740 IF iface_det_rec.wip_job_id IS NULL THEN
741 c_instance_tbl(inst_idx).WIP_JOB_ID := l_fnd_g_num;
742 ELSE
743 c_instance_tbl(inst_idx).WIP_JOB_ID := iface_det_rec.wip_job_id;
744 END IF;
745
746 IF iface_det_rec.po_order_line_id IS NULL THEN
747 c_instance_tbl(inst_idx).PO_ORDER_LINE_ID := l_fnd_g_num;
748 ELSE
749 c_instance_tbl(inst_idx).PO_ORDER_LINE_ID := iface_det_rec.po_order_line_id;
750 END IF;
751
752 IF iface_det_rec.inventory_revision IS NULL THEN
753 c_instance_tbl(inst_idx).INVENTORY_REVISION := l_fnd_g_char;
754 ELSE
755 c_instance_tbl(inst_idx).INVENTORY_REVISION := iface_det_rec.inventory_revision;
756 END IF;
757
758 IF iface_det_rec.serial_number IS NULL THEN
759 c_instance_tbl(inst_idx).SERIAL_NUMBER := l_fnd_g_char;
760 ELSE
761 c_instance_tbl(inst_idx).SERIAL_NUMBER := iface_det_rec.serial_number;
762 END IF;
763
764 IF iface_det_rec.mfg_serial_number_flag IS NULL THEN
765 c_instance_tbl(inst_idx).MFG_SERIAL_NUMBER_FLAG := l_fnd_g_char;
766 ELSE
767 c_instance_tbl(inst_idx).MFG_SERIAL_NUMBER_FLAG := iface_det_rec.mfg_serial_number_flag;
768 END IF;
769
770 IF iface_det_rec.quantity IS NULL THEN
771 c_instance_tbl(inst_idx).QUANTITY := l_fnd_g_num;
772 ELSE
773 c_instance_tbl(inst_idx).QUANTITY := iface_det_rec.quantity;
774 END IF;
775
776 IF iface_det_rec.unit_of_measure_code IS NULL THEN
777 c_instance_tbl(inst_idx).UNIT_OF_MEASURE := l_fnd_g_char;
778 ELSE
779 c_instance_tbl(inst_idx).UNIT_OF_MEASURE := iface_det_rec.unit_of_measure_code;
780 END IF;
781
782 IF iface_det_rec.accounting_class_code IS NULL THEN
783 c_instance_tbl(inst_idx).ACCOUNTING_CLASS_CODE := l_fnd_g_char;
784 ELSE
785 c_instance_tbl(inst_idx).ACCOUNTING_CLASS_CODE := iface_det_rec.accounting_class_code;
786 END IF;
787
788 IF iface_det_rec.instance_condition_id IS NULL THEN
789 c_instance_tbl(inst_idx).INSTANCE_CONDITION_ID := l_fnd_g_num;
790 ELSE
791 c_instance_tbl(inst_idx).INSTANCE_CONDITION_ID := iface_det_rec.instance_condition_id;
792 END IF;
793
794 IF iface_det_rec.instance_status_id IS NULL THEN
795 c_instance_tbl(inst_idx).INSTANCE_STATUS_ID := l_fnd_g_num;
796 ELSE
797 c_instance_tbl(inst_idx).INSTANCE_STATUS_ID := iface_det_rec.instance_status_id;
798 END IF;
799
800 IF iface_det_rec.customer_view_flag IS NULL THEN
801 c_instance_tbl(inst_idx).CUSTOMER_VIEW_FLAG := l_fnd_g_char;
802 ELSE
803 c_instance_tbl(inst_idx).CUSTOMER_VIEW_FLAG := iface_det_rec.customer_view_flag;
804 END IF;
805
806 IF iface_det_rec.merchant_view_flag IS NULL THEN
807 c_instance_tbl(inst_idx).MERCHANT_VIEW_FLAG := l_fnd_g_char;
808 ELSE
809 c_instance_tbl(inst_idx).MERCHANT_VIEW_FLAG := iface_det_rec.merchant_view_flag;
810 END IF;
811
812 IF iface_det_rec.sellable_flag IS NULL THEN
813 c_instance_tbl(inst_idx).SELLABLE_FLAG := l_fnd_g_char;
814 ELSE
815 c_instance_tbl(inst_idx).SELLABLE_FLAG := iface_det_rec.sellable_flag;
816 END IF;
817
818 IF iface_det_rec.system_id IS NULL THEN
819 c_instance_tbl(inst_idx).SYSTEM_ID := l_fnd_g_num;
820 ELSE
821 c_instance_tbl(inst_idx).SYSTEM_ID := iface_det_rec.system_id ;
822 END IF;
823
824 IF iface_det_rec.instance_type_code IS NULL THEN
825 c_instance_tbl(inst_idx).INSTANCE_TYPE_CODE := l_fnd_g_char;
826 ELSE
827 c_instance_tbl(inst_idx).INSTANCE_TYPE_CODE := iface_det_rec.instance_type_code;
828 END IF;
829
830 IF iface_det_rec.instance_end_date IS NULL THEN
831 c_instance_tbl(inst_idx).ACTIVE_END_DATE := l_fnd_g_date;
832 ELSE
833 c_instance_tbl(inst_idx).ACTIVE_END_DATE := iface_det_rec.instance_end_date;
834 END IF;
835 -- Added
836 IF iface_det_rec.instance_start_date IS NULL THEN
837 c_instance_tbl(inst_idx).ACTIVE_START_DATE := l_fnd_g_date;
838 ELSE
839 c_instance_tbl(inst_idx).ACTIVE_START_DATE := iface_det_rec.instance_start_date;
840 END IF;
841
842 IF iface_det_rec.oe_order_line_id IS NULL THEN
843 c_instance_tbl(inst_idx).last_oe_order_line_id := l_fnd_g_num;
844 ELSE
845 c_instance_tbl(inst_idx).last_oe_order_line_id := iface_det_rec.oe_order_line_id;
846 END IF;
847
848 IF iface_det_rec.oe_rma_line_id IS NULL THEN
849 c_instance_tbl(inst_idx).last_oe_rma_line_id := l_fnd_g_num;
850 ELSE
851 c_instance_tbl(inst_idx).last_oe_rma_line_id := iface_det_rec.oe_rma_line_id;
852 END IF;
853 -- End addition
854 --c_instance_tbl(inst_idx).LAST_OE_ORDER_LINE_ID := l_fnd_g_num; -- LAST_OE_ORDER_LINE_ID
855 --c_instance_tbl(inst_idx).LAST_OE_RMA_LINE_ID :=l_fnd_g_num; -- LAST_OE_RMA_LINE_ID
856 c_instance_tbl(inst_idx).LAST_PO_PO_LINE_ID :=l_fnd_g_num; -- LAST_PO_PO_LINE_ID
857 c_instance_tbl(inst_idx).LAST_OE_PO_NUMBER :=l_fnd_g_char; -- LAST_OE_PO_NUMBER
858 c_instance_tbl(inst_idx).LAST_WIP_JOB_ID :=l_fnd_g_num; -- LAST_WIP_JOB_ID
859 c_instance_tbl(inst_idx).LAST_PA_PROJECT_ID := l_fnd_g_num; -- LAST_PA_PROJECT_ID
860 c_instance_tbl(inst_idx).LAST_PA_TASK_ID :=l_fnd_g_num; -- LAST_PA_TASK_ID
861 c_instance_tbl(inst_idx).LAST_OE_AGREEMENT_ID :=l_fnd_g_num; -- LAST_OE_AGREEMENT_ID
862
863 IF iface_det_rec.install_date IS NULL THEN
864 c_instance_tbl(inst_idx).install_date := l_fnd_g_date;
865 ELSE
866 c_instance_tbl(inst_idx).install_date := iface_det_rec.install_date;
867 END IF;
868
869 c_instance_tbl(inst_idx).MANUALLY_CREATED_FLAG := l_fnd_g_char; -- MANUALLY_CREATED_FLAG
870
871 IF iface_det_rec.return_by_date IS NULL THEN
872 c_instance_tbl(inst_idx).RETURN_BY_DATE := l_fnd_g_date;
873 ELSE
874 c_instance_tbl(inst_idx).RETURN_BY_DATE := iface_det_rec.return_by_date;
875 END IF;
876
877 IF iface_det_rec.actual_return_date IS NULL THEN
878 c_instance_tbl(inst_idx).ACTUAL_RETURN_DATE := l_fnd_g_date;
879 ELSE
880 c_instance_tbl(inst_idx).ACTUAL_RETURN_DATE := iface_det_rec.actual_return_date;
881 END IF;
882
883 c_instance_tbl(inst_idx).CREATION_COMPLETE_FLAG := l_fnd_g_char; --CREATION_COMPLETE_FLAG
884 c_instance_tbl(inst_idx).COMPLETENESS_FLAG := l_fnd_g_char; --COMPLETENESS_FLAG
885 c_instance_tbl(inst_idx).VERSION_LABEL := l_fnd_g_char; --VERSION_LABEL
886 c_instance_tbl(inst_idx).VERSION_LABEL_DESCRIPTION := l_fnd_g_char; --VERSION_LABEL_DESCRIPTION
887
888 IF iface_det_rec.instance_context IS NULL THEN
889 c_instance_tbl(inst_idx).CONTEXT := l_fnd_g_char;
890 ELSE
891 c_instance_tbl(inst_idx).CONTEXT := iface_det_rec.instance_context;
892 END IF;
893
894 IF iface_det_rec.instance_attribute1 IS NULL THEN
895 c_instance_tbl(inst_idx).ATTRIBUTE1 := l_fnd_g_char;
896 ELSE
897 c_instance_tbl(inst_idx).ATTRIBUTE1 := iface_det_rec.instance_attribute1;
898 END IF;
899
900 IF iface_det_rec.instance_attribute2 IS NULL THEN
901 c_instance_tbl(inst_idx).ATTRIBUTE2 := l_fnd_g_char;
902 ELSE
903 c_instance_tbl(inst_idx).ATTRIBUTE2 := iface_det_rec.instance_attribute2;
904 END IF;
905
906 IF iface_det_rec.instance_attribute3 IS NULL THEN
907 c_instance_tbl(inst_idx).ATTRIBUTE3 := l_fnd_g_char;
908 ELSE
909 c_instance_tbl(inst_idx).ATTRIBUTE3 := iface_det_rec.instance_attribute3;
910 END IF;
911
912 IF iface_det_rec.instance_attribute4 IS NULL THEN
913 c_instance_tbl(inst_idx).ATTRIBUTE4 := l_fnd_g_char;
914 ELSE
915 c_instance_tbl(inst_idx).ATTRIBUTE4 := iface_det_rec.instance_attribute4;
916 END IF;
917
918 IF iface_det_rec.instance_attribute5 IS NULL THEN
919 c_instance_tbl(inst_idx).ATTRIBUTE5 := l_fnd_g_char;
920 ELSE
921 c_instance_tbl(inst_idx).ATTRIBUTE5 := iface_det_rec.instance_attribute5;
922 END IF;
923
924 IF iface_det_rec.instance_attribute6 IS NULL THEN
925 c_instance_tbl(inst_idx).ATTRIBUTE6 := l_fnd_g_char;
926 ELSE
927 c_instance_tbl(inst_idx).ATTRIBUTE6 := iface_det_rec.instance_attribute6;
928 END IF;
929
930 IF iface_det_rec.instance_attribute7 IS NULL THEN
931 c_instance_tbl(inst_idx).ATTRIBUTE7 := l_fnd_g_char;
932 ELSE
933 c_instance_tbl(inst_idx).ATTRIBUTE7 := iface_det_rec.instance_attribute7;
934 END IF;
935
936 IF iface_det_rec.instance_attribute8 IS NULL THEN
937 c_instance_tbl(inst_idx).ATTRIBUTE8 := l_fnd_g_char;
938 ELSE
939 c_instance_tbl(inst_idx).ATTRIBUTE8 := iface_det_rec.instance_attribute8;
940 END IF;
941
942 IF iface_det_rec.instance_attribute9 IS NULL THEN
943 c_instance_tbl(inst_idx).ATTRIBUTE9 := l_fnd_g_char;
944 ELSE
945 c_instance_tbl(inst_idx).ATTRIBUTE9 := iface_det_rec.instance_attribute9;
946 END IF;
947
948 IF iface_det_rec.instance_attribute10 IS NULL THEN
949 c_instance_tbl(inst_idx).ATTRIBUTE10 := l_fnd_g_char;
950 ELSE
951 c_instance_tbl(inst_idx).ATTRIBUTE10 := iface_det_rec.instance_attribute10;
952 END IF;
953
954 IF iface_det_rec.instance_attribute11 IS NULL THEN
955 c_instance_tbl(inst_idx).ATTRIBUTE11 := l_fnd_g_char;
956 ELSE
957 c_instance_tbl(inst_idx).ATTRIBUTE11 := iface_det_rec.instance_attribute11;
958 END IF;
959
960 IF iface_det_rec.instance_attribute12 IS NULL THEN
961 c_instance_tbl(inst_idx).ATTRIBUTE12 := l_fnd_g_char;
962 ELSE
963 c_instance_tbl(inst_idx).ATTRIBUTE12:= iface_det_rec.instance_attribute12;
964 END IF;
965
966 IF iface_det_rec.instance_attribute13 IS NULL THEN
967 c_instance_tbl(inst_idx).ATTRIBUTE13 := l_fnd_g_char;
968 ELSE
969 c_instance_tbl(inst_idx).ATTRIBUTE13:= iface_det_rec.instance_attribute13;
970 END IF;
971
972 IF iface_det_rec.instance_attribute14 IS NULL THEN
973 c_instance_tbl(inst_idx).ATTRIBUTE14 := l_fnd_g_char;
974 ELSE
975 c_instance_tbl(inst_idx).ATTRIBUTE14:= iface_det_rec.instance_attribute14;
976 END IF;
977
978 IF iface_det_rec.instance_attribute15 IS NULL THEN
979 c_instance_tbl(inst_idx).ATTRIBUTE15 := l_fnd_g_char;
980 ELSE
981 c_instance_tbl(inst_idx).ATTRIBUTE15 := iface_det_rec.instance_attribute15;
982 END IF;
983
984 --Code Addition start for 9045308--
985 IF iface_det_rec.instance_attribute16 IS NULL THEN
986 c_instance_tbl(inst_idx).ATTRIBUTE16 := l_fnd_g_char;
987 ELSE
988 c_instance_tbl(inst_idx).ATTRIBUTE16 := iface_det_rec.instance_attribute16;
989 END IF;
990
991 IF iface_det_rec.instance_attribute17 IS NULL THEN
992 c_instance_tbl(inst_idx).ATTRIBUTE17 := l_fnd_g_char;
993 ELSE
994 c_instance_tbl(inst_idx).ATTRIBUTE17 := iface_det_rec.instance_attribute17;
995 END IF;
996
997 IF iface_det_rec.instance_attribute18 IS NULL THEN
998 c_instance_tbl(inst_idx).ATTRIBUTE18 := l_fnd_g_char;
999 ELSE
1000 c_instance_tbl(inst_idx).ATTRIBUTE18 := iface_det_rec.instance_attribute18;
1001 END IF;
1002
1003 IF iface_det_rec.instance_attribute19 IS NULL THEN
1004 c_instance_tbl(inst_idx).ATTRIBUTE19 := l_fnd_g_char;
1005 ELSE
1006 c_instance_tbl(inst_idx).ATTRIBUTE19 := iface_det_rec.instance_attribute19;
1007 END IF;
1008
1009 IF iface_det_rec.instance_attribute20 IS NULL THEN
1010 c_instance_tbl(inst_idx).ATTRIBUTE20 := l_fnd_g_char;
1011 ELSE
1012 c_instance_tbl(inst_idx).ATTRIBUTE20 := iface_det_rec.instance_attribute20;
1013 END IF;
1014
1015 IF iface_det_rec.instance_attribute21 IS NULL THEN
1016 c_instance_tbl(inst_idx).ATTRIBUTE21 := l_fnd_g_char;
1017 ELSE
1018 c_instance_tbl(inst_idx).ATTRIBUTE21 := iface_det_rec.instance_attribute21;
1019 END IF;
1020
1021 IF iface_det_rec.instance_attribute22 IS NULL THEN
1022 c_instance_tbl(inst_idx).ATTRIBUTE22 := l_fnd_g_char;
1023 ELSE
1024 c_instance_tbl(inst_idx).ATTRIBUTE22 := iface_det_rec.instance_attribute22;
1025 END IF;
1026
1027 IF iface_det_rec.instance_attribute23 IS NULL THEN
1028 c_instance_tbl(inst_idx).ATTRIBUTE23 := l_fnd_g_char;
1029 ELSE
1030 c_instance_tbl(inst_idx).ATTRIBUTE23 := iface_det_rec.instance_attribute23;
1031 END IF;
1032
1033 IF iface_det_rec.instance_attribute24 IS NULL THEN
1034 c_instance_tbl(inst_idx).ATTRIBUTE24 := l_fnd_g_char;
1035 ELSE
1036 c_instance_tbl(inst_idx).ATTRIBUTE24 := iface_det_rec.instance_attribute24;
1037 END IF;
1038
1039 IF iface_det_rec.instance_attribute25 IS NULL THEN
1040 c_instance_tbl(inst_idx).ATTRIBUTE25 := l_fnd_g_char;
1041 ELSE
1042 c_instance_tbl(inst_idx).ATTRIBUTE25 := iface_det_rec.instance_attribute25;
1043 END IF;
1044
1045 IF iface_det_rec.instance_attribute26 IS NULL THEN
1046 c_instance_tbl(inst_idx).ATTRIBUTE26 := l_fnd_g_char;
1047 ELSE
1048 c_instance_tbl(inst_idx).ATTRIBUTE26 := iface_det_rec.instance_attribute26;
1049 END IF;
1050
1051 IF iface_det_rec.instance_attribute27 IS NULL THEN
1052 c_instance_tbl(inst_idx).ATTRIBUTE27 := l_fnd_g_char;
1053 ELSE
1054 c_instance_tbl(inst_idx).ATTRIBUTE27 := iface_det_rec.instance_attribute27;
1055 END IF;
1056
1057 IF iface_det_rec.instance_attribute28 IS NULL THEN
1058 c_instance_tbl(inst_idx).ATTRIBUTE28 := l_fnd_g_char;
1059 ELSE
1060 c_instance_tbl(inst_idx).ATTRIBUTE28 := iface_det_rec.instance_attribute28;
1061 END IF;
1062
1063 IF iface_det_rec.instance_attribute29 IS NULL THEN
1064 c_instance_tbl(inst_idx).ATTRIBUTE29 := l_fnd_g_char;
1065 ELSE
1066 c_instance_tbl(inst_idx).ATTRIBUTE29 := iface_det_rec.instance_attribute29;
1067 END IF;
1068
1069 IF iface_det_rec.instance_attribute30 IS NULL THEN
1070 c_instance_tbl(inst_idx).ATTRIBUTE30 := l_fnd_g_char;
1071 ELSE
1072 c_instance_tbl(inst_idx).ATTRIBUTE30 := iface_det_rec.instance_attribute30;
1073 END IF;
1074 --Code end for bug9045308--
1075
1076 c_instance_tbl(inst_idx).OBJECT_VERSION_NUMBER := 1;
1077 c_instance_tbl(inst_idx).LAST_TXN_LINE_DETAIL_ID := l_fnd_g_num;
1078
1079 IF iface_det_rec.install_location_type_code IS NULL THEN
1080 c_instance_tbl(inst_idx).INSTALL_LOCATION_TYPE_CODE := l_fnd_g_char;
1081 ELSE
1082 c_instance_tbl(inst_idx).INSTALL_LOCATION_TYPE_CODE := iface_det_rec.install_location_type_code;
1083 END IF;
1084
1085 IF iface_det_rec.install_location_id IS NULL THEN
1086 c_instance_tbl(inst_idx).INSTALL_LOCATION_ID := l_fnd_g_num;
1087 ELSE
1088 c_instance_tbl(inst_idx).INSTALL_LOCATION_ID := iface_det_rec.install_location_id;
1089 END IF;
1090
1091
1092
1093 c_instance_tbl(inst_idx).CHECK_FOR_INSTANCE_EXPIRY := l_fnd_g_true;
1094 -- Added for bug 3150717
1095 IF iface_det_rec.instance_description IS NULL THEN
1096 c_instance_tbl(inst_idx).instance_description := l_fnd_g_char;
1097 ELSE
1098 c_instance_tbl(inst_idx).instance_description := iface_det_rec.instance_description;
1099 END IF;
1100 -- End addition for bug 3150717
1101
1102 IF iface_det_rec.operational_status_code IS NULL THEN
1103 c_instance_tbl(inst_idx).operational_status_code := l_fnd_g_char;
1104 ELSE
1105 c_instance_tbl(inst_idx).operational_status_code := iface_det_rec.operational_status_code;
1106 END IF;
1107 -- If operational_status_code has a value, then copy it to instance_usage_code
1108 -- else default it to out_of_enterprise
1109 IF c_instance_tbl(inst_idx).operational_status_code IS NOT NULL AND
1110 c_instance_tbl(inst_idx).operational_status_code <> l_fnd_g_char
1111 THEN
1112 c_instance_tbl(inst_idx).instance_usage_code := iface_det_rec.operational_status_code;
1113 ELSE
1114 c_instance_tbl(inst_idx).instance_usage_code := l_def_usage_code;
1115 END IF;
1116 --bug#13072088
1117 IF l_enable_contracts_oi = 'Y' THEN
1118 c_instance_tbl(inst_idx).CALL_CONTRACTS := l_fnd_g_true;
1119 ELSE
1120 c_instance_tbl(inst_idx).CALL_CONTRACTS := l_fnd_g_false;
1121 END IF;
1122 --bug#13072088
1123 FOR iparty_det_rec in iparty_det_cur(iface_det_rec.inst_interface_id) LOOP
1124 -- Loop and create Party Table
1125
1126 c_party_tbl(prty_idx).instance_party_id := NULL;
1127 c_party_tbl(prty_idx).instance_id := NULL;
1128 c_party_tbl(prty_idx).parent_tbl_index := inst_idx;
1129
1130 IF iparty_det_rec.inst_interface_id IS NULL THEN
1131 c_party_tbl(prty_idx).interface_id := l_fnd_g_num;
1132 ELSE
1133 c_party_tbl(prty_idx).interface_id := iparty_det_rec.inst_interface_id;
1134 END IF;
1135
1136 IF iparty_det_rec.party_source_table IS NULL THEN
1137 c_party_tbl(prty_idx).party_source_table := l_fnd_g_char;
1138 ELSE
1139 c_party_tbl(prty_idx).party_source_table := iparty_det_rec.party_source_table;
1140 END IF;
1141
1142 IF iparty_det_rec.party_id IS NULL THEN
1143 c_party_tbl(prty_idx).party_id := l_fnd_g_num;
1144 ELSE
1145 c_party_tbl(prty_idx).party_id := iparty_det_rec.party_id;
1146 END IF;
1147 /* -- No need for the following code
1148 IF iparty_det_rec.party_id = g_int_party
1149 THEN
1150 c_instance_tbl(inst_idx).instance_usage_code:= 'IN_INVENTORY';
1151 ELSE
1152 c_instance_tbl(inst_idx).instance_usage_code:= 'OUT_OF_ENTERPRISE';
1153 END IF;
1154 */
1155 IF iparty_det_rec.party_relationship_type_code IS NULL THEN
1156 c_party_tbl(prty_idx).relationship_type_code := l_fnd_g_char;
1157 ELSE
1158 c_party_tbl(prty_idx).relationship_type_code := iparty_det_rec.party_relationship_type_code;
1159 END IF;
1160
1161 c_party_tbl(prty_idx).contact_flag := iparty_det_rec.contact_flag;
1162
1163 -- Create table with contact Parties
1164 IF c_party_tbl(prty_idx).contact_flag = 'Y' THEN
1165 c_party_contact_tbl(prty_contact_idx).ip_interface_id := iparty_det_rec.ip_interface_id;
1166 c_party_contact_tbl(prty_contact_idx).inst_interface_id := iparty_det_rec.inst_interface_id;
1167 c_party_contact_tbl(prty_contact_idx).contact_party_id := iparty_det_rec.contact_party_id;
1168 c_party_contact_tbl(prty_contact_idx).contact_party_number := iparty_det_rec.contact_party_number;
1169 c_party_contact_tbl(prty_contact_idx).contact_party_name := iparty_det_rec.contact_party_name;
1170 c_party_contact_tbl(prty_contact_idx).contact_party_rel_type := iparty_det_rec.contact_party_rel_type;
1171 c_party_contact_tbl(prty_contact_idx).parent_tbl_idx := prty_idx;
1172 prty_contact_idx := prty_contact_idx + 1;
1173 END IF;
1174
1175 c_party_tbl(prty_idx).contact_ip_id := l_fnd_g_num;
1176 --c_party_tbl(prty_idx).active_start_date := l_fnd_g_date;
1177 -- Added
1178 IF iparty_det_rec.party_start_date IS NULL THEN
1179 c_party_tbl(prty_idx).active_start_date := l_fnd_g_date;
1180 ELSE
1181 c_party_tbl(prty_idx).active_start_date := iparty_det_rec.party_start_date;
1182 END IF;
1183 -- End addition
1184 IF iparty_det_rec.party_end_date IS NULL THEN
1185 c_party_tbl(prty_idx).active_end_date := l_fnd_g_date;
1186 ELSE
1187 c_party_tbl(prty_idx).active_end_date := iparty_det_rec.party_end_date;
1188 END IF;
1189
1190 IF iparty_det_rec.party_context IS NULL THEN
1191 c_party_tbl(prty_idx).context := l_fnd_g_char;
1192 ELSE
1193 c_party_tbl(prty_idx).context := iparty_det_rec.party_context;
1194 END IF;
1195
1196 IF iparty_det_rec.party_attribute1 IS NULL THEN
1197 c_party_tbl(prty_idx).attribute1 := l_fnd_g_char;
1198 ELSE
1199 c_party_tbl(prty_idx).attribute1 := iparty_det_rec.party_attribute1;
1200 END IF;
1201
1202 IF iparty_det_rec.party_attribute2 IS NULL THEN
1203 c_party_tbl(prty_idx).attribute2 := l_fnd_g_char;
1204 ELSE
1205 c_party_tbl(prty_idx).attribute2 := iparty_det_rec.party_attribute2;
1206 END IF;
1207
1208 IF iparty_det_rec.party_attribute3 IS NULL THEN
1209 c_party_tbl(prty_idx).attribute3 := l_fnd_g_char;
1210 ELSE
1211 c_party_tbl(prty_idx).attribute3 := iparty_det_rec.party_attribute3;
1212 END IF;
1213
1214 IF iparty_det_rec.party_attribute4 IS NULL THEN
1215 c_party_tbl(prty_idx).attribute4 := l_fnd_g_char;
1216 ELSE
1217 c_party_tbl(prty_idx).attribute4 := iparty_det_rec.party_attribute4;
1218 END IF;
1219
1220 IF iparty_det_rec.party_attribute5 IS NULL THEN
1221 c_party_tbl(prty_idx).attribute5 := l_fnd_g_char;
1222 ELSE
1223 c_party_tbl(prty_idx).attribute5 := iparty_det_rec.party_attribute5;
1224 END IF;
1225
1226 IF iparty_det_rec.party_attribute6 IS NULL THEN
1227 c_party_tbl(prty_idx).attribute6 := l_fnd_g_char;
1228 ELSE
1229 c_party_tbl(prty_idx).attribute6 := iparty_det_rec.party_attribute6;
1230 END IF;
1231
1232 IF iparty_det_rec.party_attribute7 IS NULL THEN
1233 c_party_tbl(prty_idx).attribute7 := l_fnd_g_char;
1234 ELSE
1235 c_party_tbl(prty_idx).attribute7 := iparty_det_rec.party_attribute7;
1236 END IF;
1237
1238 IF iparty_det_rec.party_attribute8 IS NULL THEN
1239 c_party_tbl(prty_idx).attribute8 := l_fnd_g_char;
1240 ELSE
1241 c_party_tbl(prty_idx).attribute8 := iparty_det_rec.party_attribute8;
1242 END IF;
1243
1244 IF iparty_det_rec.party_attribute8 IS NULL THEN
1245 c_party_tbl(prty_idx).attribute9 := l_fnd_g_char;
1246 ELSE
1247 c_party_tbl(prty_idx).attribute9 := iparty_det_rec.party_attribute9;
1248 END IF;
1249
1250 IF iparty_det_rec.party_attribute10 IS NULL THEN
1251 c_party_tbl(prty_idx).attribute10 := l_fnd_g_char;
1252 ELSE
1253 c_party_tbl(prty_idx).attribute10 := iparty_det_rec.party_attribute10;
1254 END IF;
1255
1256 IF iparty_det_rec.party_attribute11 IS NULL THEN
1257 c_party_tbl(prty_idx).attribute11 := l_fnd_g_char;
1258 ELSE
1259 c_party_tbl(prty_idx).attribute11 := iparty_det_rec.party_attribute11;
1260 END IF;
1261
1262 IF iparty_det_rec.party_attribute12 IS NULL THEN
1263 c_party_tbl(prty_idx).attribute12 := l_fnd_g_char;
1264 ELSE
1265 c_party_tbl(prty_idx).attribute12 := iparty_det_rec.party_attribute12;
1266 END IF;
1267
1268 IF iparty_det_rec.party_attribute13 IS NULL THEN
1269 c_party_tbl(prty_idx).attribute13 := l_fnd_g_char;
1270 ELSE
1271 c_party_tbl(prty_idx).attribute13 := iparty_det_rec.party_attribute13;
1272 END IF;
1273
1274 IF iparty_det_rec.party_attribute14 IS NULL THEN
1275 c_party_tbl(prty_idx).attribute14 := l_fnd_g_char;
1276 ELSE
1277 c_party_tbl(prty_idx).attribute14 := iparty_det_rec.party_attribute14;
1278 END IF;
1279
1280 IF iparty_det_rec.party_attribute15 IS NULL THEN
1281 c_party_tbl(prty_idx).attribute15 := l_fnd_g_char;
1282 ELSE
1283 c_party_tbl(prty_idx).attribute15 := iparty_det_rec.party_attribute15;
1284 END IF;
1285
1286 c_party_tbl(prty_idx).OBJECT_VERSION_NUMBER := 1; -- OBJECT_VERSION_NUMBER
1287 c_party_tbl(prty_idx).PRIMARY_FLAG := l_fnd_g_char; -- PRIMARY_FLAG
1288 c_party_tbl(prty_idx).PREFERRED_FLAG := l_fnd_g_char; -- PREFERRED_FLAG
1289
1290 IF iparty_det_rec.party_account1_id IS NOT NULL THEN -- Put record in Table
1291
1292 c_account_tbl(ptyacc_idx).ip_account_id := l_fnd_g_num;
1293 c_account_tbl(ptyacc_idx).instance_party_id := l_fnd_g_num;
1294 c_account_tbl(ptyacc_idx).parent_tbl_index := prty_idx;
1295
1296 IF iparty_det_rec.party_account1_id IS NULL THEN
1297 c_account_tbl(ptyacc_idx).party_account_id := l_fnd_g_num;
1298 ELSE
1299 c_account_tbl(ptyacc_idx).party_account_id := iparty_det_rec.party_account1_id;
1300 END IF;
1301
1302 IF iparty_det_rec.acct1_relationship_type_code IS NULL THEN
1303 c_account_tbl(ptyacc_idx).relationship_type_code := l_fnd_g_char;
1304 ELSE
1305 c_account_tbl(ptyacc_idx).relationship_type_code := iparty_det_rec.acct1_relationship_type_code;
1306 END IF;
1307
1308 IF iparty_det_rec.bill_to_address1 IS NULL THEN
1309 c_account_tbl(ptyacc_idx).bill_to_address := l_fnd_g_num;
1310 ELSE
1311 c_account_tbl(ptyacc_idx).bill_to_address := iparty_det_rec.bill_to_address1;
1312 END IF;
1313
1314 IF iparty_det_rec.ship_to_address1 IS NULL THEN
1315 c_account_tbl(ptyacc_idx).ship_to_address := l_fnd_g_num;
1316 ELSE
1317 c_account_tbl(ptyacc_idx).ship_to_address := iparty_det_rec.ship_to_address1;
1318 END IF;
1319
1320 --c_account_tbl(ptyacc_idx).ACTIVE_START_DATE := l_fnd_g_date; -- ACTIVE_START_DATE
1321
1322 IF iparty_det_rec.party_acct1_start_date IS NULL THEN
1323 c_account_tbl(ptyacc_idx).active_start_date := l_fnd_g_date;
1324 ELSE
1325 c_account_tbl(ptyacc_idx).active_start_date := iparty_det_rec.party_acct1_start_date;
1326 END IF;
1327
1328 IF iparty_det_rec.party_acct1_end_date IS NULL THEN
1329 c_account_tbl(ptyacc_idx).active_end_date := l_fnd_g_date;
1330 ELSE
1331 c_account_tbl(ptyacc_idx).active_end_date := iparty_det_rec.party_acct1_end_date;
1332 END IF;
1333
1334 IF iparty_det_rec.account1_context IS NULL THEN
1335 c_account_tbl(ptyacc_idx).context := l_fnd_g_char;
1336 ELSE
1337 c_account_tbl(ptyacc_idx).context := iparty_det_rec.account1_context;
1338 END IF;
1339
1340 IF iparty_det_rec.account1_attribute1 IS NULL THEN
1341 c_account_tbl(ptyacc_idx).attribute1 := l_fnd_g_char;
1342 ELSE
1343 c_account_tbl(ptyacc_idx).attribute1 := iparty_det_rec.account1_attribute1;
1344 END IF;
1345
1346 IF iparty_det_rec.account1_attribute2 IS NULL THEN
1347 c_account_tbl(ptyacc_idx).attribute2 := l_fnd_g_char;
1348 ELSE
1349 c_account_tbl(ptyacc_idx).attribute2 := iparty_det_rec.account1_attribute2;
1350 END IF;
1351
1352 IF iparty_det_rec.account1_attribute3 IS NULL THEN
1353 c_account_tbl(ptyacc_idx).attribute3 := l_fnd_g_char;
1354 ELSE
1355 c_account_tbl(ptyacc_idx).attribute3 := iparty_det_rec.account1_attribute3;
1356 END IF;
1357
1358 IF iparty_det_rec.account1_attribute4 IS NULL THEN
1359 c_account_tbl(ptyacc_idx).attribute4 := l_fnd_g_char;
1360 ELSE
1361 c_account_tbl(ptyacc_idx).attribute4 := iparty_det_rec.account1_attribute4;
1362 END IF;
1363
1364 IF iparty_det_rec.account1_attribute5 IS NULL THEN
1365 c_account_tbl(ptyacc_idx).attribute5 := l_fnd_g_char;
1366 ELSE
1367 c_account_tbl(ptyacc_idx).attribute5 := iparty_det_rec.account1_attribute5;
1368 END IF;
1369
1370 IF iparty_det_rec.account1_attribute6 IS NULL THEN
1371 c_account_tbl(ptyacc_idx).attribute6 := l_fnd_g_char;
1372 ELSE
1373 c_account_tbl(ptyacc_idx).attribute6 := iparty_det_rec.account1_attribute6;
1374 END IF;
1375
1376 IF iparty_det_rec.account1_attribute7 IS NULL THEN
1377 c_account_tbl(ptyacc_idx).attribute7 := l_fnd_g_char;
1378 ELSE
1379 c_account_tbl(ptyacc_idx).attribute7 := iparty_det_rec.account1_attribute7;
1380 END IF;
1381
1382 IF iparty_det_rec.account1_attribute8 IS NULL THEN
1383 c_account_tbl(ptyacc_idx).attribute8 := l_fnd_g_char;
1384 ELSE
1385 c_account_tbl(ptyacc_idx).attribute8 := iparty_det_rec.account1_attribute8;
1386 END IF;
1387
1388 IF iparty_det_rec.account1_attribute9 IS NULL THEN
1389 c_account_tbl(ptyacc_idx).attribute9 := l_fnd_g_char;
1390 ELSE
1391 c_account_tbl(ptyacc_idx).attribute9 := iparty_det_rec.account1_attribute9;
1392 END IF;
1393
1394 IF iparty_det_rec.account1_attribute10 IS NULL THEN
1395 c_account_tbl(ptyacc_idx).attribute10 := l_fnd_g_char;
1396 ELSE
1397 c_account_tbl(ptyacc_idx).attribute10 := iparty_det_rec.account1_attribute10;
1398 END IF;
1399
1400 IF iparty_det_rec.account1_attribute11 IS NULL THEN
1401 c_account_tbl(ptyacc_idx).attribute11 := l_fnd_g_char;
1402 ELSE
1403 c_account_tbl(ptyacc_idx).attribute11 := iparty_det_rec.account1_attribute11;
1404 END IF;
1405
1406 IF iparty_det_rec.account1_attribute12 IS NULL THEN
1407 c_account_tbl(ptyacc_idx).attribute12 := l_fnd_g_char;
1408 ELSE
1409 c_account_tbl(ptyacc_idx).attribute12 := iparty_det_rec.account1_attribute12;
1410 END IF;
1411
1412 IF iparty_det_rec.account1_attribute13 IS NULL THEN
1413 c_account_tbl(ptyacc_idx).attribute13 := l_fnd_g_char;
1414 ELSE
1415 c_account_tbl(ptyacc_idx).attribute13 := iparty_det_rec.account1_attribute13;
1416 END IF;
1417
1418 IF iparty_det_rec.account1_attribute14 IS NULL THEN
1419 c_account_tbl(ptyacc_idx).attribute14 := l_fnd_g_char;
1420 ELSE
1421 c_account_tbl(ptyacc_idx).attribute14 := iparty_det_rec.account1_attribute14;
1422 END IF;
1423
1424 IF iparty_det_rec.account1_attribute15 IS NULL THEN
1425 c_account_tbl(ptyacc_idx).attribute15 := l_fnd_g_char;
1426 ELSE
1427 c_account_tbl(ptyacc_idx).attribute15 := iparty_det_rec.account1_attribute15;
1428 END IF;
1429
1430 c_account_tbl(ptyacc_idx).OBJECT_VERSION_NUMBER := 1;
1431 c_account_tbl(ptyacc_idx).CALL_CONTRACTS := l_fnd_g_true;
1432 c_account_tbl(ptyacc_idx).VLD_ORGANIZATION_ID := l_fnd_g_num;
1433 ptyacc_idx := ptyacc_idx + 1;
1434
1435 END IF;
1436
1437 IF iparty_det_rec.party_account2_id IS NOT NULL THEN -- Put record in Table
1438
1439 c_account_tbl(ptyacc_idx).ip_account_id := l_fnd_g_num;
1440 c_account_tbl(ptyacc_idx).instance_party_id := l_fnd_g_num;
1441 c_account_tbl(ptyacc_idx).parent_tbl_index := prty_idx;
1442
1443 IF iparty_det_rec.party_account2_id IS NULL THEN
1444 c_account_tbl(ptyacc_idx).party_account_id := l_fnd_g_num;
1445 ELSE
1446 c_account_tbl(ptyacc_idx).party_account_id := iparty_det_rec.party_account2_id;
1447 END IF;
1448
1449 IF iparty_det_rec.acct2_relationship_type_code IS NULL THEN
1450 c_account_tbl(ptyacc_idx).relationship_type_code := l_fnd_g_char;
1451 ELSE
1452 c_account_tbl(ptyacc_idx).relationship_type_code := iparty_det_rec.acct2_relationship_type_code;
1453 END IF;
1454
1455 IF iparty_det_rec.bill_to_address2 IS NULL THEN
1456 c_account_tbl(ptyacc_idx).bill_to_address := l_fnd_g_num;
1457 ELSE
1458 c_account_tbl(ptyacc_idx).bill_to_address := iparty_det_rec.bill_to_address2;
1459 END IF;
1460
1461 IF iparty_det_rec.ship_to_address2 IS NULL THEN
1462 c_account_tbl(ptyacc_idx).ship_to_address := l_fnd_g_num;
1463 ELSE
1464 c_account_tbl(ptyacc_idx).ship_to_address := iparty_det_rec.ship_to_address2;
1465 END IF;
1466
1467 --c_account_tbl(ptyacc_idx).ACTIVE_START_DATE := l_fnd_g_date; -- ACTIVE_START_DATE
1468 -- Added
1469 IF iparty_det_rec.party_acct2_start_date IS NULL THEN
1470 c_account_tbl(ptyacc_idx).active_start_date := l_fnd_g_date;
1471 ELSE
1472 c_account_tbl(ptyacc_idx).active_start_date := iparty_det_rec.party_acct2_start_date;
1473 END IF;
1474 -- End addition
1475 IF iparty_det_rec.party_acct2_end_date IS NULL THEN
1476 c_account_tbl(ptyacc_idx).active_end_date := l_fnd_g_date;
1477 ELSE
1478 c_account_tbl(ptyacc_idx).active_end_date := iparty_det_rec.party_acct2_end_date;
1479 END IF;
1480
1481 IF iparty_det_rec.account2_context IS NULL THEN
1482 c_account_tbl(ptyacc_idx).context := l_fnd_g_char;
1483 ELSE
1484 c_account_tbl(ptyacc_idx).context := iparty_det_rec.account2_context;
1485 END IF;
1486
1487 IF iparty_det_rec.account2_attribute1 IS NULL THEN
1488 c_account_tbl(ptyacc_idx).attribute1 := l_fnd_g_char;
1489 ELSE
1490 c_account_tbl(ptyacc_idx).attribute1 := iparty_det_rec.account2_attribute1;
1491 END IF;
1492
1493 IF iparty_det_rec.account2_attribute2 IS NULL THEN
1494 c_account_tbl(ptyacc_idx).attribute2 := l_fnd_g_char;
1495 ELSE
1496 c_account_tbl(ptyacc_idx).attribute2 := iparty_det_rec.account2_attribute2;
1497 END IF;
1498
1499 IF iparty_det_rec.account2_attribute3 IS NULL THEN
1500 c_account_tbl(ptyacc_idx).attribute3 := l_fnd_g_char;
1501 ELSE
1502 c_account_tbl(ptyacc_idx).attribute3 := iparty_det_rec.account2_attribute3;
1503 END IF;
1504
1505 IF iparty_det_rec.account2_attribute4 IS NULL THEN
1506 c_account_tbl(ptyacc_idx).attribute4 := l_fnd_g_char;
1507 ELSE
1508 c_account_tbl(ptyacc_idx).attribute4 := iparty_det_rec.account2_attribute4;
1509 END IF;
1510
1511 IF iparty_det_rec.account2_attribute5 IS NULL THEN
1512 c_account_tbl(ptyacc_idx).attribute5 := l_fnd_g_char;
1513 ELSE
1514 c_account_tbl(ptyacc_idx).attribute5 := iparty_det_rec.account2_attribute5;
1515 END IF;
1516
1517 IF iparty_det_rec.account2_attribute6 IS NULL THEN
1518 c_account_tbl(ptyacc_idx).attribute6 := l_fnd_g_char;
1519 ELSE
1520 c_account_tbl(ptyacc_idx).attribute6 := iparty_det_rec.account2_attribute6;
1521 END IF;
1522
1523 IF iparty_det_rec.account2_attribute7 IS NULL THEN
1524 c_account_tbl(ptyacc_idx).attribute7 := l_fnd_g_char;
1525 ELSE
1526 c_account_tbl(ptyacc_idx).attribute7 := iparty_det_rec.account2_attribute7;
1527 END IF;
1528
1529 IF iparty_det_rec.account2_attribute8 IS NULL THEN
1530 c_account_tbl(ptyacc_idx).attribute8 := l_fnd_g_char;
1531 ELSE
1532 c_account_tbl(ptyacc_idx).attribute8 := iparty_det_rec.account2_attribute8;
1533 END IF;
1534
1535 IF iparty_det_rec.account2_attribute9 IS NULL THEN
1536 c_account_tbl(ptyacc_idx).attribute9 := l_fnd_g_char;
1537 ELSE
1538 c_account_tbl(ptyacc_idx).attribute9 := iparty_det_rec.account2_attribute9;
1539 END IF;
1540
1541 IF iparty_det_rec.account2_attribute10 IS NULL THEN
1542 c_account_tbl(ptyacc_idx).attribute10 := l_fnd_g_char;
1543 ELSE
1544 c_account_tbl(ptyacc_idx).attribute10 := iparty_det_rec.account2_attribute10;
1545 END IF;
1546
1547 IF iparty_det_rec.account2_attribute11 IS NULL THEN
1548 c_account_tbl(ptyacc_idx).attribute11 := l_fnd_g_char;
1549 ELSE
1550 c_account_tbl(ptyacc_idx).attribute11 := iparty_det_rec.account2_attribute11;
1551 END IF;
1552
1553 IF iparty_det_rec.account2_attribute12 IS NULL THEN
1554 c_account_tbl(ptyacc_idx).attribute12 := l_fnd_g_char;
1555 ELSE
1556 c_account_tbl(ptyacc_idx).attribute12 := iparty_det_rec.account2_attribute12;
1557 END IF;
1558
1559 IF iparty_det_rec.account2_attribute13 IS NULL THEN
1560 c_account_tbl(ptyacc_idx).attribute13 := l_fnd_g_char;
1561 ELSE
1562 c_account_tbl(ptyacc_idx).attribute13 := iparty_det_rec.account2_attribute13;
1563 END IF;
1564
1565 IF iparty_det_rec.account2_attribute14 IS NULL THEN
1566 c_account_tbl(ptyacc_idx).attribute14 := l_fnd_g_char;
1567 ELSE
1568 c_account_tbl(ptyacc_idx).attribute14 := iparty_det_rec.account2_attribute14;
1569 END IF;
1570
1571 IF iparty_det_rec.account2_attribute15 IS NULL THEN
1572 c_account_tbl(ptyacc_idx).attribute15 := l_fnd_g_char;
1573 ELSE
1574 c_account_tbl(ptyacc_idx).attribute15 := iparty_det_rec.account2_attribute15;
1575 END IF;
1576
1577 c_account_tbl(ptyacc_idx).OBJECT_VERSION_NUMBER := 1;
1578 c_account_tbl(ptyacc_idx).CALL_CONTRACTS := l_fnd_g_true;
1579 c_account_tbl(ptyacc_idx).VLD_ORGANIZATION_ID := l_fnd_g_num;
1580 ptyacc_idx := ptyacc_idx + 1;
1581
1582 END IF;
1583
1584 IF iparty_det_rec.party_account3_id IS NOT NULL THEN -- Put record in Table
1585
1586 c_account_tbl(ptyacc_idx).ip_account_id := l_fnd_g_num;
1587 c_account_tbl(ptyacc_idx).instance_party_id := l_fnd_g_num;
1588 c_account_tbl(ptyacc_idx).parent_tbl_index := prty_idx;
1589
1590 IF iparty_det_rec.party_account3_id IS NULL THEN
1591 c_account_tbl(ptyacc_idx).party_account_id := l_fnd_g_num;
1592 ELSE
1593 c_account_tbl(ptyacc_idx).party_account_id := iparty_det_rec.party_account3_id;
1594 END IF;
1595
1596 IF iparty_det_rec.acct3_relationship_type_code IS NULL THEN
1597 c_account_tbl(ptyacc_idx).relationship_type_code := l_fnd_g_char;
1598 ELSE
1599 c_account_tbl(ptyacc_idx).relationship_type_code := iparty_det_rec.acct3_relationship_type_code;
1600 END IF;
1601
1602 IF iparty_det_rec.bill_to_address3 IS NULL THEN
1603 c_account_tbl(ptyacc_idx).bill_to_address := l_fnd_g_num;
1604 ELSE
1605 c_account_tbl(ptyacc_idx).bill_to_address := iparty_det_rec.bill_to_address3;
1606 END IF;
1607
1608 IF iparty_det_rec.ship_to_address3 IS NULL THEN
1609 c_account_tbl(ptyacc_idx).ship_to_address := l_fnd_g_num;
1610 ELSE
1611 c_account_tbl(ptyacc_idx).ship_to_address := iparty_det_rec.ship_to_address3;
1612 END IF;
1613
1614 --c_account_tbl(ptyacc_idx).ACTIVE_START_DATE := l_fnd_g_date; -- ACTIVE_START_DATE
1615 -- Added
1616 IF iparty_det_rec.party_acct3_start_date IS NULL THEN
1617 c_account_tbl(ptyacc_idx).active_start_date := l_fnd_g_date;
1618 ELSE
1619 c_account_tbl(ptyacc_idx).active_start_date := iparty_det_rec.party_acct3_start_date;
1620 END IF;
1621 -- End addition
1622 IF iparty_det_rec.party_acct3_end_date IS NULL THEN
1623 c_account_tbl(ptyacc_idx).active_end_date := l_fnd_g_date;
1624 ELSE
1625 c_account_tbl(ptyacc_idx).active_end_date := iparty_det_rec.party_acct3_end_date;
1626 END IF;
1627
1628 IF iparty_det_rec.account3_context IS NULL THEN
1629 c_account_tbl(ptyacc_idx).context := l_fnd_g_char;
1630 ELSE
1631 c_account_tbl(ptyacc_idx).context := iparty_det_rec.account3_context;
1632 END IF;
1633
1634 IF iparty_det_rec.account3_attribute1 IS NULL THEN
1635 c_account_tbl(ptyacc_idx).attribute1 := l_fnd_g_char;
1636 ELSE
1637 c_account_tbl(ptyacc_idx).attribute1 := iparty_det_rec.account3_attribute1;
1638 END IF;
1639
1640 IF iparty_det_rec.account3_attribute2 IS NULL THEN
1641 c_account_tbl(ptyacc_idx).attribute2 := l_fnd_g_char;
1642 ELSE
1643 c_account_tbl(ptyacc_idx).attribute2 := iparty_det_rec.account3_attribute2;
1644 END IF;
1645
1646 IF iparty_det_rec.account3_attribute3 IS NULL THEN
1647 c_account_tbl(ptyacc_idx).attribute3 := l_fnd_g_char;
1648 ELSE
1649 c_account_tbl(ptyacc_idx).attribute3 := iparty_det_rec.account3_attribute3;
1650 END IF;
1651
1652 IF iparty_det_rec.account3_attribute4 IS NULL THEN
1653 c_account_tbl(ptyacc_idx).attribute4 := l_fnd_g_char;
1654 ELSE
1655 c_account_tbl(ptyacc_idx).attribute4 := iparty_det_rec.account3_attribute4;
1656 END IF;
1657
1658 IF iparty_det_rec.account3_attribute5 IS NULL THEN
1659 c_account_tbl(ptyacc_idx).attribute5 := l_fnd_g_char;
1660 ELSE
1661 c_account_tbl(ptyacc_idx).attribute5 := iparty_det_rec.account3_attribute5;
1662 END IF;
1663
1664 IF iparty_det_rec.account3_attribute6 IS NULL THEN
1665 c_account_tbl(ptyacc_idx).attribute6 := l_fnd_g_char;
1666 ELSE
1667 c_account_tbl(ptyacc_idx).attribute6 := iparty_det_rec.account3_attribute6;
1668 END IF;
1669
1670 IF iparty_det_rec.account3_attribute7 IS NULL THEN
1671 c_account_tbl(ptyacc_idx).attribute7 := l_fnd_g_char;
1672 ELSE
1673 c_account_tbl(ptyacc_idx).attribute7 := iparty_det_rec.account3_attribute7;
1674 END IF;
1675
1676 IF iparty_det_rec.account3_attribute8 IS NULL THEN
1677 c_account_tbl(ptyacc_idx).attribute8 := l_fnd_g_char;
1678 ELSE
1679 c_account_tbl(ptyacc_idx).attribute8 := iparty_det_rec.account3_attribute8;
1680 END IF;
1681
1682 IF iparty_det_rec.account3_attribute9 IS NULL THEN
1683 c_account_tbl(ptyacc_idx).attribute9 := l_fnd_g_char;
1684 ELSE
1685 c_account_tbl(ptyacc_idx).attribute9 := iparty_det_rec.account3_attribute9;
1686 END IF;
1687
1688 IF iparty_det_rec.account3_attribute10 IS NULL THEN
1689 c_account_tbl(ptyacc_idx).attribute10 := l_fnd_g_char;
1690 ELSE
1691 c_account_tbl(ptyacc_idx).attribute10 := iparty_det_rec.account3_attribute10;
1692 END IF;
1693
1694 IF iparty_det_rec.account3_attribute11 IS NULL THEN
1695 c_account_tbl(ptyacc_idx).attribute11 := l_fnd_g_char;
1696 ELSE
1697 c_account_tbl(ptyacc_idx).attribute11 := iparty_det_rec.account3_attribute11;
1698 END IF;
1699
1700 IF iparty_det_rec.account3_attribute12 IS NULL THEN
1701 c_account_tbl(ptyacc_idx).attribute12 := l_fnd_g_char;
1702 ELSE
1703 c_account_tbl(ptyacc_idx).attribute12 := iparty_det_rec.account3_attribute12;
1704 END IF;
1705
1706 IF iparty_det_rec.account3_attribute13 IS NULL THEN
1707 c_account_tbl(ptyacc_idx).attribute13 := l_fnd_g_char;
1708 ELSE
1709 c_account_tbl(ptyacc_idx).attribute13 := iparty_det_rec.account3_attribute13;
1710 END IF;
1711
1712 IF iparty_det_rec.account3_attribute14 IS NULL THEN
1713 c_account_tbl(ptyacc_idx).attribute14 := l_fnd_g_char;
1714 ELSE
1715 c_account_tbl(ptyacc_idx).attribute14 := iparty_det_rec.account3_attribute14;
1716 END IF;
1717
1718 IF iparty_det_rec.account3_attribute15 IS NULL THEN
1719 c_account_tbl(ptyacc_idx).attribute15 := l_fnd_g_char;
1720 ELSE
1721 c_account_tbl(ptyacc_idx).attribute15 := iparty_det_rec.account3_attribute15;
1722 END IF;
1723
1724 c_account_tbl(ptyacc_idx).OBJECT_VERSION_NUMBER := 1;
1725 c_account_tbl(ptyacc_idx).CALL_CONTRACTS := l_fnd_g_true;
1726 c_account_tbl(ptyacc_idx).VLD_ORGANIZATION_ID := l_fnd_g_num;
1727 ptyacc_idx := ptyacc_idx + 1;
1728 END IF; -- Party Account 3
1729
1730 -- Added the following code to handle contacts.
1731 -- If contact_party_id is passed then I assume
1732 -- a contact should be created for the party.
1733 IF iparty_det_rec.contact_party_id IS NOT NULL AND
1734 iparty_det_rec.contact_party_id <> fnd_api.g_miss_num
1735 THEN
1736 prty_idx:=prty_idx + 1;
1737 c_party_tbl(prty_idx).instance_party_id:=fnd_api.g_miss_num;
1738 c_party_tbl(prty_idx).instance_id:=fnd_api.g_miss_num;
1739 c_party_tbl(prty_idx).party_source_table:=iparty_det_rec.party_source_table;
1740 c_party_tbl(prty_idx).party_id:=iparty_det_rec.contact_party_id;
1741 c_party_tbl(prty_idx).relationship_type_code:=iparty_det_rec.contact_party_rel_type;
1742 c_party_tbl(prty_idx).contact_flag:='Y';
1743 c_party_tbl(prty_idx).contact_parent_tbl_index:=prty_idx-1;
1744 c_party_tbl(prty_idx).parent_tbl_index:=inst_idx;
1745 END IF;
1746 -- End addition for contacts.
1747
1748 prty_idx := prty_idx + 1;
1749 END LOOP; -- End of Party and Party Account LOOP
1750
1751 --bnarayan added for R12 Open Interfaces
1752 /* Loop through the Cursor based on asset interface table */
1753 FOR iasset_iface_rec in iasset_iface_cur(iface_det_rec.inst_interface_id)
1754 LOOP
1755 c_asset_assignment_tbl( asset_idx ).OBJECT_VERSION_NUMBER := 1;
1756 c_asset_assignment_tbl( asset_idx ).parent_tbl_index := inst_idx;
1757
1758 IF iasset_iface_rec.instance_asset_id IS NULL THEN
1759 c_asset_assignment_tbl( asset_idx ).instance_asset_id := l_fnd_g_num;
1760 ELSE
1761 c_asset_assignment_tbl( asset_idx ).instance_asset_id := iasset_iface_rec.instance_asset_id ;
1762 END IF;
1763
1764 IF iasset_iface_rec.instance_id IS NULL THEN
1765 c_asset_assignment_tbl( asset_idx ).instance_id := l_fnd_g_num;
1766 ELSE
1767 c_asset_assignment_tbl( asset_idx ).instance_id := iasset_iface_rec.instance_id ;
1768 END IF;
1769
1770 IF iasset_iface_rec.fa_asset_id IS NULL THEN
1771 c_asset_assignment_tbl( asset_idx ).fa_asset_id := l_fnd_g_num;
1772 ELSE
1773 c_asset_assignment_tbl( asset_idx ).fa_asset_id := iasset_iface_rec.fa_asset_id ;
1774 END IF;
1775
1776 IF iasset_iface_rec.fa_book_type_code IS NULL THEN
1777 c_asset_assignment_tbl( asset_idx ).fa_book_type_code := l_fnd_g_char;
1778 ELSE
1779 c_asset_assignment_tbl( asset_idx ).fa_book_type_code := iasset_iface_rec.fa_book_type_code;
1780 END IF;
1781
1782 IF iasset_iface_rec.fa_location_id IS NULL THEN
1783 c_asset_assignment_tbl( asset_idx ).fa_location_id := l_fnd_g_num;
1784 ELSE
1785 c_asset_assignment_tbl( asset_idx ).fa_location_id := iasset_iface_rec.fa_location_id ;
1786 END IF;
1787
1788 IF iasset_iface_rec.asset_quantity IS NULL THEN
1789 c_asset_assignment_tbl( asset_idx ).asset_quantity := l_fnd_g_num;
1790 ELSE
1791 c_asset_assignment_tbl( asset_idx ).asset_quantity := iasset_iface_rec.asset_quantity ;
1792 END IF;
1793
1794 IF iasset_iface_rec.update_status IS NULL THEN
1795 c_asset_assignment_tbl( asset_idx ).update_status := l_fnd_g_char;
1796 ELSE
1797 c_asset_assignment_tbl( asset_idx ).update_status := iasset_iface_rec.update_status ;
1798 END IF;
1799
1800 IF iasset_iface_rec.active_start_date IS NULL THEN
1801 c_asset_assignment_tbl( asset_idx ).active_start_date := l_fnd_g_date;
1802 ELSE
1803 c_asset_assignment_tbl( asset_idx ).active_start_date := iasset_iface_rec.active_start_date ;
1804 END IF;
1805
1806 IF iasset_iface_rec.active_end_date IS NULL THEN
1807 c_asset_assignment_tbl( asset_idx ).active_end_date := l_fnd_g_date;
1808 ELSE
1809 c_asset_assignment_tbl( asset_idx ).active_end_date := iasset_iface_rec.active_end_date;
1810 END IF;
1811 c_asset_assignment_tbl( asset_idx ).fa_sync_flag := iasset_iface_rec.fa_sync_flag;
1812 asset_idx := asset_idx + 1; -- Increment asset index
1813 END LOOP; --end of the asset loop
1814
1815 FOR ieav_det_rec in ieav_det_cur (iface_det_rec.inst_interface_id) LOOP
1816 -- Extended Attribute Values
1817
1818 IF iface_det_rec.transaction_identifier is NOT NULL THEN
1819
1820 c_ext_attrib_tbl(extatt_idx).attribute_value_id := NULL;
1821 c_ext_attrib_tbl(extatt_idx).instance_id := NULL;
1822 c_ext_attrib_tbl(extatt_idx).parent_tbl_index := inst_idx;
1823
1824 IF ieav_det_rec.attribute_id IS NULL THEN
1825 c_ext_attrib_tbl(extatt_idx).attribute_id := l_fnd_g_num;
1826 ELSE
1827 c_ext_attrib_tbl(extatt_idx).attribute_id := ieav_det_rec.attribute_id;
1828 END IF;
1829
1830 IF ieav_det_rec.attribute_code IS NULL THEN
1831 c_ext_attrib_tbl(extatt_idx).attribute_code := l_fnd_g_char;
1832 ELSE
1833 c_ext_attrib_tbl(extatt_idx).attribute_code := ieav_det_rec.attribute_code;
1834 END IF;
1835
1836 IF ieav_det_rec.attribute_value IS NULL THEN
1837 c_ext_attrib_tbl(extatt_idx).attribute_value := l_fnd_g_char;
1838 ELSE
1839 c_ext_attrib_tbl(extatt_idx).attribute_value := ieav_det_rec.attribute_value;
1840 END IF;
1841
1842 c_ext_attrib_tbl(extatt_idx).ACTIVE_START_DATE := l_fnd_g_date;
1843
1844 IF ieav_det_rec.ieav_end_date IS NULL THEN
1845 c_ext_attrib_tbl(extatt_idx).active_end_date := l_fnd_g_date;
1846 ELSE
1847 c_ext_attrib_tbl(extatt_idx).active_end_date := ieav_det_rec.ieav_end_date;
1848 END IF;
1849
1850 c_ext_attrib_tbl(extatt_idx).context := l_fnd_g_char;
1851 c_ext_attrib_tbl(extatt_idx).attribute1 := l_fnd_g_char;
1852 c_ext_attrib_tbl(extatt_idx).attribute2 := l_fnd_g_char;
1853 c_ext_attrib_tbl(extatt_idx).attribute3 := l_fnd_g_char;
1854 c_ext_attrib_tbl(extatt_idx).attribute4 := l_fnd_g_char;
1855 c_ext_attrib_tbl(extatt_idx).attribute5 := l_fnd_g_char;
1856 c_ext_attrib_tbl(extatt_idx).attribute6 := l_fnd_g_char;
1857 c_ext_attrib_tbl(extatt_idx).attribute7 := l_fnd_g_char;
1858 c_ext_attrib_tbl(extatt_idx).attribute8 := l_fnd_g_char;
1859 c_ext_attrib_tbl(extatt_idx).attribute9 := l_fnd_g_char;
1860 c_ext_attrib_tbl(extatt_idx).attribute10 := l_fnd_g_char;
1861 c_ext_attrib_tbl(extatt_idx).attribute11 := l_fnd_g_char;
1862 c_ext_attrib_tbl(extatt_idx).attribute12 := l_fnd_g_char;
1863 c_ext_attrib_tbl(extatt_idx).attribute13 := l_fnd_g_char;
1864 c_ext_attrib_tbl(extatt_idx).attribute14 := l_fnd_g_char;
1865 c_ext_attrib_tbl(extatt_idx).attribute15 := l_fnd_g_char;
1866 c_ext_attrib_tbl(extatt_idx).object_version_number := 1;
1867
1868 extatt_idx := extatt_idx + 1;
1869 END IF;
1870 END LOOP; -- End of Extended Attributes
1871
1872 IF (iface_det_rec.pricing_att_start_date IS NOT NULL OR
1873 iface_det_rec.pricing_att_end_date IS NOT NULL OR
1874 iface_det_rec.pricing_context IS NOT NULL OR
1875 iface_det_rec.pricing_attribute1 IS NOT NULL OR
1876 iface_det_rec.pricing_attribute2 IS NOT NULL OR
1877 iface_det_rec.pricing_attribute3 IS NOT NULL OR
1878 iface_det_rec.pricing_attribute4 IS NOT NULL OR
1879 iface_det_rec.pricing_attribute5 IS NOT NULL OR
1880 iface_det_rec.pricing_attribute6 IS NOT NULL OR
1881 iface_det_rec.pricing_attribute7 IS NOT NULL OR
1882 iface_det_rec.pricing_attribute8 IS NOT NULL OR
1883 iface_det_rec.pricing_attribute9 IS NOT NULL OR
1884 iface_det_rec.pricing_attribute10 IS NOT NULL OR
1885 iface_det_rec.pricing_attribute11 IS NOT NULL OR
1886 iface_det_rec.pricing_attribute12 IS NOT NULL OR
1887 iface_det_rec.pricing_attribute13 IS NOT NULL OR
1888 iface_det_rec.pricing_attribute14 IS NOT NULL OR
1889 iface_det_rec.pricing_attribute15 IS NOT NULL OR
1890 iface_det_rec.pricing_attribute16 IS NOT NULL OR
1891 iface_det_rec.pricing_attribute17 IS NOT NULL OR
1892 iface_det_rec.pricing_attribute18 IS NOT NULL OR
1893 iface_det_rec.pricing_attribute19 IS NOT NULL OR
1894 iface_det_rec.pricing_attribute20 IS NOT NULL OR
1895 iface_det_rec.pricing_attribute21 IS NOT NULL OR
1896 iface_det_rec.pricing_attribute22 IS NOT NULL OR
1897 iface_det_rec.pricing_attribute23 IS NOT NULL OR
1898 iface_det_rec.pricing_attribute24 IS NOT NULL OR
1899 iface_det_rec.pricing_attribute25 IS NOT NULL OR
1900 iface_det_rec.pricing_attribute26 IS NOT NULL OR
1901 iface_det_rec.pricing_attribute27 IS NOT NULL OR
1902 iface_det_rec.pricing_attribute28 IS NOT NULL OR
1903 iface_det_rec.pricing_attribute29 IS NOT NULL OR
1904 iface_det_rec.pricing_attribute30 IS NOT NULL OR
1905 iface_det_rec.pricing_attribute31 IS NOT NULL OR
1906 iface_det_rec.pricing_attribute32 IS NOT NULL OR
1907 iface_det_rec.pricing_attribute33 IS NOT NULL OR
1908 iface_det_rec.pricing_attribute34 IS NOT NULL OR
1909 iface_det_rec.pricing_attribute35 IS NOT NULL OR
1910 iface_det_rec.pricing_attribute36 IS NOT NULL OR
1911 iface_det_rec.pricing_attribute37 IS NOT NULL OR
1912 iface_det_rec.pricing_attribute38 IS NOT NULL OR
1913 iface_det_rec.pricing_attribute39 IS NOT NULL OR
1914 iface_det_rec.pricing_attribute40 IS NOT NULL OR
1915 iface_det_rec.pricing_attribute41 IS NOT NULL OR
1916 iface_det_rec.pricing_attribute42 IS NOT NULL OR
1917 iface_det_rec.pricing_attribute43 IS NOT NULL OR
1918 iface_det_rec.pricing_attribute44 IS NOT NULL OR
1919 iface_det_rec.pricing_attribute45 IS NOT NULL OR
1920 iface_det_rec.pricing_attribute46 IS NOT NULL OR
1921 iface_det_rec.pricing_attribute47 IS NOT NULL OR
1922 iface_det_rec.pricing_attribute48 IS NOT NULL OR
1923 iface_det_rec.pricing_attribute49 IS NOT NULL OR
1924 iface_det_rec.pricing_attribute50 IS NOT NULL OR
1925 iface_det_rec.pricing_attribute51 IS NOT NULL OR
1926 iface_det_rec.pricing_attribute52 IS NOT NULL OR
1927 iface_det_rec.pricing_attribute53 IS NOT NULL OR
1928 iface_det_rec.pricing_attribute54 IS NOT NULL OR
1929 iface_det_rec.pricing_attribute55 IS NOT NULL OR
1930 iface_det_rec.pricing_attribute56 IS NOT NULL OR
1931 iface_det_rec.pricing_attribute57 IS NOT NULL OR
1932 iface_det_rec.pricing_attribute58 IS NOT NULL OR
1933 iface_det_rec.pricing_attribute59 IS NOT NULL OR
1934 iface_det_rec.pricing_attribute60 IS NOT NULL OR
1935 iface_det_rec.pricing_attribute61 IS NOT NULL OR
1936 iface_det_rec.pricing_attribute62 IS NOT NULL OR
1937 iface_det_rec.pricing_attribute63 IS NOT NULL OR
1938 iface_det_rec.pricing_attribute64 IS NOT NULL OR
1939 iface_det_rec.pricing_attribute65 IS NOT NULL OR
1940 iface_det_rec.pricing_attribute66 IS NOT NULL OR
1941 iface_det_rec.pricing_attribute67 IS NOT NULL OR
1942 iface_det_rec.pricing_attribute68 IS NOT NULL OR
1943 iface_det_rec.pricing_attribute69 IS NOT NULL OR
1944 iface_det_rec.pricing_attribute70 IS NOT NULL OR
1945 iface_det_rec.pricing_attribute71 IS NOT NULL OR
1946 iface_det_rec.pricing_attribute72 IS NOT NULL OR
1947 iface_det_rec.pricing_attribute73 IS NOT NULL OR
1948 iface_det_rec.pricing_attribute74 IS NOT NULL OR
1949 iface_det_rec.pricing_attribute75 IS NOT NULL OR
1950 iface_det_rec.pricing_attribute76 IS NOT NULL OR
1951 iface_det_rec.pricing_attribute77 IS NOT NULL OR
1952 iface_det_rec.pricing_attribute78 IS NOT NULL OR
1953 iface_det_rec.pricing_attribute79 IS NOT NULL OR
1954 iface_det_rec.pricing_attribute80 IS NOT NULL OR
1955 iface_det_rec.pricing_attribute81 IS NOT NULL OR
1956 iface_det_rec.pricing_attribute82 IS NOT NULL OR
1957 iface_det_rec.pricing_attribute83 IS NOT NULL OR
1958 iface_det_rec.pricing_attribute84 IS NOT NULL OR
1959 iface_det_rec.pricing_attribute85 IS NOT NULL OR
1960 iface_det_rec.pricing_attribute86 IS NOT NULL OR
1961 iface_det_rec.pricing_attribute87 IS NOT NULL OR
1962 iface_det_rec.pricing_attribute88 IS NOT NULL OR
1963 iface_det_rec.pricing_attribute89 IS NOT NULL OR
1964 iface_det_rec.pricing_attribute90 IS NOT NULL OR
1965 iface_det_rec.pricing_attribute91 IS NOT NULL OR
1966 iface_det_rec.pricing_attribute92 IS NOT NULL OR
1967 iface_det_rec.pricing_attribute93 IS NOT NULL OR
1968 iface_det_rec.pricing_attribute94 IS NOT NULL OR
1969 iface_det_rec.pricing_attribute95 IS NOT NULL OR
1970 iface_det_rec.pricing_attribute96 IS NOT NULL OR
1971 iface_det_rec.pricing_attribute97 IS NOT NULL OR
1972 iface_det_rec.pricing_attribute98 IS NOT NULL OR
1973 iface_det_rec.pricing_attribute99 IS NOT NULL OR
1974 iface_det_rec.pricing_attribute100 IS NOT NULL) THEN
1975
1976 IF iface_det_rec.transaction_identifier is NOT NULL THEN
1977
1978 c_price_tbl(price_idx).pricing_attribute_id := NULL;
1979 c_price_tbl(price_idx).instance_id := NULL;
1980 c_price_tbl(price_idx).parent_tbl_index := inst_idx;
1981 c_price_tbl(price_idx).active_start_date := l_fnd_g_date;
1982
1983 IF iface_det_rec.pricing_att_end_date IS NULL THEN
1984 c_price_tbl(price_idx).active_end_date := l_fnd_g_date;
1985 ELSE
1986 c_price_tbl(price_idx).active_end_date := iface_det_rec.pricing_att_end_date;
1987 END IF;
1988
1989 IF iface_det_rec.pricing_context IS NULL THEN
1990 c_price_tbl(price_idx).pricing_context := l_fnd_g_char;
1991 --changed from context tar 4102867.999
1992 ELSE
1993 c_price_tbl(price_idx).pricing_context := iface_det_rec.pricing_context;
1994 --changed from context tar 4102867.999
1995 END IF;
1996
1997 IF iface_det_rec.pricing_attribute1 IS NULL THEN
1998 c_price_tbl(price_idx).pricing_attribute1 := l_fnd_g_char;
1999 ELSE
2000 c_price_tbl(price_idx).pricing_attribute1 := iface_det_rec.pricing_attribute1;
2001 END IF;
2002
2003 IF iface_det_rec.pricing_attribute2 IS NULL THEN
2004 c_price_tbl(price_idx).pricing_attribute2 := l_fnd_g_char;
2005 ELSE
2006 c_price_tbl(price_idx).pricing_attribute2 := iface_det_rec.pricing_attribute2;
2007 END IF;
2008
2009 IF iface_det_rec.pricing_attribute3 IS NULL THEN
2010 c_price_tbl(price_idx).pricing_attribute3 := l_fnd_g_char;
2011 ELSE
2012 c_price_tbl(price_idx).pricing_attribute3 := iface_det_rec.pricing_attribute3;
2013 END IF;
2014
2015 IF iface_det_rec.pricing_attribute4 IS NULL THEN
2016 c_price_tbl(price_idx).pricing_attribute4 := l_fnd_g_char;
2017 ELSE
2018 c_price_tbl(price_idx).pricing_attribute4 := iface_det_rec.pricing_attribute4;
2019 END IF;
2020
2021 IF iface_det_rec.pricing_attribute5 IS NULL THEN
2022 c_price_tbl(price_idx).pricing_attribute5 := l_fnd_g_char;
2023 ELSE
2024 c_price_tbl(price_idx).pricing_attribute5 := iface_det_rec.pricing_attribute5;
2025 END IF;
2026
2027 IF iface_det_rec.pricing_attribute6 IS NULL THEN
2028 c_price_tbl(price_idx).pricing_attribute6 := l_fnd_g_char;
2029 ELSE
2030 c_price_tbl(price_idx).pricing_attribute6 := iface_det_rec.pricing_attribute6;
2031 END IF;
2032
2033 IF iface_det_rec.pricing_attribute7 IS NULL THEN
2034 c_price_tbl(price_idx).pricing_attribute7 := l_fnd_g_char;
2035 ELSE
2036 c_price_tbl(price_idx).pricing_attribute7 := iface_det_rec.pricing_attribute7;
2037 END IF;
2038
2039 IF iface_det_rec.pricing_attribute8 IS NULL THEN
2040 c_price_tbl(price_idx).pricing_attribute8 := l_fnd_g_char;
2041 ELSE
2042 c_price_tbl(price_idx).pricing_attribute8 := iface_det_rec.pricing_attribute8;
2043 END IF;
2044
2045 IF iface_det_rec.pricing_attribute9 IS NULL THEN
2046 c_price_tbl(price_idx).pricing_attribute9 := l_fnd_g_char;
2047 ELSE
2048 c_price_tbl(price_idx).pricing_attribute9 := iface_det_rec.pricing_attribute9;
2049 END IF;
2050
2051 IF iface_det_rec.pricing_attribute10 IS NULL THEN
2052 c_price_tbl(price_idx).pricing_attribute10 := l_fnd_g_char;
2053 ELSE
2054 c_price_tbl(price_idx).pricing_attribute10 := iface_det_rec.pricing_attribute10;
2055 END IF;
2056
2057 IF iface_det_rec.pricing_attribute11 IS NULL THEN
2058 c_price_tbl(price_idx).pricing_attribute11 := l_fnd_g_char;
2059 ELSE
2060 c_price_tbl(price_idx).pricing_attribute11 := iface_det_rec.pricing_attribute11;
2061 END IF;
2062
2063 IF iface_det_rec.pricing_attribute12 IS NULL THEN
2064 c_price_tbl(price_idx).pricing_attribute12 := l_fnd_g_char;
2065 ELSE
2066 c_price_tbl(price_idx).pricing_attribute12 := iface_det_rec.pricing_attribute12;
2067 END IF;
2068
2069 IF iface_det_rec.pricing_attribute13 IS NULL THEN
2070 c_price_tbl(price_idx).pricing_attribute13 := l_fnd_g_char;
2071 ELSE
2072 c_price_tbl(price_idx).pricing_attribute13 := iface_det_rec.pricing_attribute13;
2073 END IF;
2074
2075 IF iface_det_rec.pricing_attribute14 IS NULL THEN
2076 c_price_tbl(price_idx).pricing_attribute14 := l_fnd_g_char;
2077 ELSE
2078 c_price_tbl(price_idx).pricing_attribute14 := iface_det_rec.pricing_attribute14;
2079 END IF;
2080
2081 IF iface_det_rec.pricing_attribute15 IS NULL THEN
2082 c_price_tbl(price_idx).pricing_attribute15 := l_fnd_g_char;
2083 ELSE
2084 c_price_tbl(price_idx).pricing_attribute15 := iface_det_rec.pricing_attribute15;
2085 END IF;
2086
2087 IF iface_det_rec.pricing_attribute16 IS NULL THEN
2088 c_price_tbl(price_idx).pricing_attribute16 := l_fnd_g_char;
2089 ELSE
2090 c_price_tbl(price_idx).pricing_attribute16 := iface_det_rec.pricing_attribute16;
2091 END IF;
2092
2093 IF iface_det_rec.pricing_attribute17 IS NULL THEN
2094 c_price_tbl(price_idx).pricing_attribute17 := l_fnd_g_char;
2095 ELSE
2096 c_price_tbl(price_idx).pricing_attribute17 := iface_det_rec.pricing_attribute17;
2097 END IF;
2098
2099 IF iface_det_rec.pricing_attribute18 IS NULL THEN
2100 c_price_tbl(price_idx).pricing_attribute18 := l_fnd_g_char;
2101 ELSE
2102 c_price_tbl(price_idx).pricing_attribute18 := iface_det_rec.pricing_attribute18;
2103 END IF;
2104
2105 IF iface_det_rec.pricing_attribute19 IS NULL THEN
2106 c_price_tbl(price_idx).pricing_attribute19 := l_fnd_g_char;
2107 ELSE
2108 c_price_tbl(price_idx).pricing_attribute19 := iface_det_rec.pricing_attribute19;
2109 END IF;
2110
2111 IF iface_det_rec.pricing_attribute20 IS NULL THEN
2112 c_price_tbl(price_idx).pricing_attribute20 := l_fnd_g_char;
2113 ELSE
2114 c_price_tbl(price_idx).pricing_attribute20 := iface_det_rec.pricing_attribute20;
2115 END IF;
2116
2117 IF iface_det_rec.pricing_attribute21 IS NULL THEN
2118 c_price_tbl(price_idx).pricing_attribute21 := l_fnd_g_char;
2119 ELSE
2120 c_price_tbl(price_idx).pricing_attribute21 := iface_det_rec.pricing_attribute21;
2121 END IF;
2122
2123 IF iface_det_rec.pricing_attribute22 IS NULL THEN
2124 c_price_tbl(price_idx).pricing_attribute22 := l_fnd_g_char;
2125 ELSE
2126 c_price_tbl(price_idx).pricing_attribute22 := iface_det_rec.pricing_attribute22;
2127 END IF;
2128
2129 IF iface_det_rec.pricing_attribute23 IS NULL THEN
2130 c_price_tbl(price_idx).pricing_attribute23 := l_fnd_g_char;
2131 ELSE
2132 c_price_tbl(price_idx).pricing_attribute23 := iface_det_rec.pricing_attribute23;
2133 END IF;
2134
2135 IF iface_det_rec.pricing_attribute24 IS NULL THEN
2136 c_price_tbl(price_idx).pricing_attribute24 := l_fnd_g_char;
2137 ELSE
2138 c_price_tbl(price_idx).pricing_attribute24 := iface_det_rec.pricing_attribute24;
2139 END IF;
2140
2141 IF iface_det_rec.pricing_attribute25 IS NULL THEN
2142 c_price_tbl(price_idx).pricing_attribute25 := l_fnd_g_char;
2143 ELSE
2144 c_price_tbl(price_idx).pricing_attribute25 := iface_det_rec.pricing_attribute25;
2145 END IF;
2146
2147 IF iface_det_rec.pricing_attribute26 IS NULL THEN
2148 c_price_tbl(price_idx).pricing_attribute26 := l_fnd_g_char;
2149 ELSE
2150 c_price_tbl(price_idx).pricing_attribute26 := iface_det_rec.pricing_attribute26;
2151 END IF;
2152
2153 IF iface_det_rec.pricing_attribute27 IS NULL THEN
2154 c_price_tbl(price_idx).pricing_attribute27 := l_fnd_g_char;
2155 ELSE
2156 c_price_tbl(price_idx).pricing_attribute27 := iface_det_rec.pricing_attribute27;
2157 END IF;
2158
2159 IF iface_det_rec.pricing_attribute28 IS NULL THEN
2160 c_price_tbl(price_idx).pricing_attribute28 := l_fnd_g_char;
2161 ELSE
2162 c_price_tbl(price_idx).pricing_attribute28 := iface_det_rec.pricing_attribute28;
2163 END IF;
2164
2165 IF iface_det_rec.pricing_attribute29 IS NULL THEN
2166 c_price_tbl(price_idx).pricing_attribute29 := l_fnd_g_char;
2167 ELSE
2168 c_price_tbl(price_idx).pricing_attribute29 := iface_det_rec.pricing_attribute29;
2169 END IF;
2170
2171 IF iface_det_rec.pricing_attribute30 IS NULL THEN
2172 c_price_tbl(price_idx).pricing_attribute30 := l_fnd_g_char;
2173 ELSE
2174 c_price_tbl(price_idx).pricing_attribute30 := iface_det_rec.pricing_attribute30;
2175 END IF;
2176
2177 IF iface_det_rec.pricing_attribute31 IS NULL THEN
2178 c_price_tbl(price_idx).pricing_attribute31 := l_fnd_g_char;
2179 ELSE
2180 c_price_tbl(price_idx).pricing_attribute31 := iface_det_rec.pricing_attribute31;
2181 END IF;
2182
2183 IF iface_det_rec.pricing_attribute32 IS NULL THEN
2184 c_price_tbl(price_idx).pricing_attribute32 := l_fnd_g_char;
2185 ELSE
2186 c_price_tbl(price_idx).pricing_attribute32 := iface_det_rec.pricing_attribute32;
2187 END IF;
2188
2189 IF iface_det_rec.pricing_attribute33 IS NULL THEN
2190 c_price_tbl(price_idx).pricing_attribute33 := l_fnd_g_char;
2191 ELSE
2192 c_price_tbl(price_idx).pricing_attribute33 := iface_det_rec.pricing_attribute33;
2193 END IF;
2194
2195 IF iface_det_rec.pricing_attribute34 IS NULL THEN
2196 c_price_tbl(price_idx).pricing_attribute34 := l_fnd_g_char;
2197 ELSE
2198 c_price_tbl(price_idx).pricing_attribute34 := iface_det_rec.pricing_attribute34;
2199 END IF;
2200
2201 IF iface_det_rec.pricing_attribute35 IS NULL THEN
2202 c_price_tbl(price_idx).pricing_attribute35 := l_fnd_g_char;
2203 ELSE
2204 c_price_tbl(price_idx).pricing_attribute35 := iface_det_rec.pricing_attribute35;
2205 END IF;
2206
2207 IF iface_det_rec.pricing_attribute36 IS NULL THEN
2208 c_price_tbl(price_idx).pricing_attribute36 := l_fnd_g_char;
2209 ELSE
2210 c_price_tbl(price_idx).pricing_attribute36 := iface_det_rec.pricing_attribute36;
2211 END IF;
2212
2213 IF iface_det_rec.pricing_attribute37 IS NULL THEN
2214 c_price_tbl(price_idx).pricing_attribute37 := l_fnd_g_char;
2215 ELSE
2216 c_price_tbl(price_idx).pricing_attribute37 := iface_det_rec.pricing_attribute37;
2217 END IF;
2218
2219 IF iface_det_rec.pricing_attribute38 IS NULL THEN
2220 c_price_tbl(price_idx).pricing_attribute38 := l_fnd_g_char;
2221 ELSE
2222 c_price_tbl(price_idx).pricing_attribute38 := iface_det_rec.pricing_attribute38;
2223 END IF;
2224
2225 IF iface_det_rec.pricing_attribute39 IS NULL THEN
2226 c_price_tbl(price_idx).pricing_attribute39 := l_fnd_g_char;
2227 ELSE
2228 c_price_tbl(price_idx).pricing_attribute39 := iface_det_rec.pricing_attribute39;
2229 END IF;
2230
2231 IF iface_det_rec.pricing_attribute40 IS NULL THEN
2232 c_price_tbl(price_idx).pricing_attribute40 := l_fnd_g_char;
2233 ELSE
2234 c_price_tbl(price_idx).pricing_attribute40 := iface_det_rec.pricing_attribute40;
2235 END IF;
2236
2237 IF iface_det_rec.pricing_attribute41 IS NULL THEN
2238 c_price_tbl(price_idx).pricing_attribute41 := l_fnd_g_char;
2239 ELSE
2240 c_price_tbl(price_idx).pricing_attribute41 := iface_det_rec.pricing_attribute41;
2241 END IF;
2242
2243 IF iface_det_rec.pricing_attribute42 IS NULL THEN
2244 c_price_tbl(price_idx).pricing_attribute42 := l_fnd_g_char;
2245 ELSE
2246 c_price_tbl(price_idx).pricing_attribute42 := iface_det_rec.pricing_attribute42;
2247 END IF;
2248
2249 IF iface_det_rec.pricing_attribute43 IS NULL THEN
2250 c_price_tbl(price_idx).pricing_attribute43 := l_fnd_g_char;
2251 ELSE
2252 c_price_tbl(price_idx).pricing_attribute43 := iface_det_rec.pricing_attribute43;
2253 END IF;
2254
2255 IF iface_det_rec.pricing_attribute44 IS NULL THEN
2256 c_price_tbl(price_idx).pricing_attribute44 := l_fnd_g_char;
2257 ELSE
2258 c_price_tbl(price_idx).pricing_attribute44 := iface_det_rec.pricing_attribute44;
2259 END IF;
2260
2261 IF iface_det_rec.pricing_attribute45 IS NULL THEN
2262 c_price_tbl(price_idx).pricing_attribute45 := l_fnd_g_char;
2263 ELSE
2264 c_price_tbl(price_idx).pricing_attribute45 := iface_det_rec.pricing_attribute45;
2265 END IF;
2266
2267 IF iface_det_rec.pricing_attribute46 IS NULL THEN
2268 c_price_tbl(price_idx).pricing_attribute46 := l_fnd_g_char;
2269 ELSE
2270 c_price_tbl(price_idx).pricing_attribute46 := iface_det_rec.pricing_attribute46;
2271 END IF;
2272
2273 IF iface_det_rec.pricing_attribute47 IS NULL THEN
2274 c_price_tbl(price_idx).pricing_attribute47 := l_fnd_g_char;
2275 ELSE
2276 c_price_tbl(price_idx).pricing_attribute47 := iface_det_rec.pricing_attribute47;
2277 END IF;
2278
2279 IF iface_det_rec.pricing_attribute48 IS NULL THEN
2280 c_price_tbl(price_idx).pricing_attribute48 := l_fnd_g_char;
2281 ELSE
2282 c_price_tbl(price_idx).pricing_attribute48 := iface_det_rec.pricing_attribute48;
2283 END IF;
2284
2285 IF iface_det_rec.pricing_attribute49 IS NULL THEN
2286 c_price_tbl(price_idx).pricing_attribute49 := l_fnd_g_char;
2287 ELSE
2288 c_price_tbl(price_idx).pricing_attribute49 := iface_det_rec.pricing_attribute49;
2289 END IF;
2290
2291 IF iface_det_rec.pricing_attribute50 IS NULL THEN
2292 c_price_tbl(price_idx).pricing_attribute50 := l_fnd_g_char;
2293 ELSE
2294 c_price_tbl(price_idx).pricing_attribute50 := iface_det_rec.pricing_attribute50;
2295 END IF;
2296
2297 IF iface_det_rec.pricing_attribute51 IS NULL THEN
2298 c_price_tbl(price_idx).pricing_attribute51 := l_fnd_g_char;
2299 ELSE
2300 c_price_tbl(price_idx).pricing_attribute51 := iface_det_rec.pricing_attribute51;
2301 END IF;
2302
2303 IF iface_det_rec.pricing_attribute52 IS NULL THEN
2304 c_price_tbl(price_idx).pricing_attribute52 := l_fnd_g_char;
2305 ELSE
2306 c_price_tbl(price_idx).pricing_attribute52 := iface_det_rec.pricing_attribute52;
2307 END IF;
2308
2309 IF iface_det_rec.pricing_attribute53 IS NULL THEN
2310 c_price_tbl(price_idx).pricing_attribute53 := l_fnd_g_char;
2311 ELSE
2312 c_price_tbl(price_idx).pricing_attribute53 := iface_det_rec.pricing_attribute53;
2313 END IF;
2314
2315 IF iface_det_rec.pricing_attribute54 IS NULL THEN
2316 c_price_tbl(price_idx).pricing_attribute54 := l_fnd_g_char;
2317 ELSE
2318 c_price_tbl(price_idx).pricing_attribute54 := iface_det_rec.pricing_attribute54;
2319 END IF;
2320
2321 IF iface_det_rec.pricing_attribute55 IS NULL THEN
2322 c_price_tbl(price_idx).pricing_attribute55 := l_fnd_g_char;
2323 ELSE
2324 c_price_tbl(price_idx).pricing_attribute55 := iface_det_rec.pricing_attribute55;
2325 END IF;
2326
2327 IF iface_det_rec.pricing_attribute56 IS NULL THEN
2328 c_price_tbl(price_idx).pricing_attribute56 := l_fnd_g_char;
2329 ELSE
2330 c_price_tbl(price_idx).pricing_attribute56 := iface_det_rec.pricing_attribute56;
2331 END IF;
2332
2333 IF iface_det_rec.pricing_attribute57 IS NULL THEN
2334 c_price_tbl(price_idx).pricing_attribute57 := l_fnd_g_char;
2335 ELSE
2336 c_price_tbl(price_idx).pricing_attribute57 := iface_det_rec.pricing_attribute57;
2337 END IF;
2338
2339 IF iface_det_rec.pricing_attribute58 IS NULL THEN
2340 c_price_tbl(price_idx).pricing_attribute58 := l_fnd_g_char;
2341 ELSE
2342 c_price_tbl(price_idx).pricing_attribute58 := iface_det_rec.pricing_attribute58;
2343 END IF;
2344
2345 IF iface_det_rec.pricing_attribute59 IS NULL THEN
2346 c_price_tbl(price_idx).pricing_attribute59 := l_fnd_g_char;
2347 ELSE
2348 c_price_tbl(price_idx).pricing_attribute59 := iface_det_rec.pricing_attribute59;
2349 END IF;
2350
2351 IF iface_det_rec.pricing_attribute60 IS NULL THEN
2352 c_price_tbl(price_idx).pricing_attribute60 := l_fnd_g_char;
2353 ELSE
2354 c_price_tbl(price_idx).pricing_attribute60 := iface_det_rec.pricing_attribute60;
2355 END IF;
2356
2357 IF iface_det_rec.pricing_attribute61 IS NULL THEN
2358 c_price_tbl(price_idx).pricing_attribute61 := l_fnd_g_char;
2359 ELSE
2360 c_price_tbl(price_idx).pricing_attribute61 := iface_det_rec.pricing_attribute61;
2361 END IF;
2362
2363 IF iface_det_rec.pricing_attribute62 IS NULL THEN
2364 c_price_tbl(price_idx).pricing_attribute62 := l_fnd_g_char;
2365 ELSE
2366 c_price_tbl(price_idx).pricing_attribute62 := iface_det_rec.pricing_attribute62;
2367 END IF;
2368
2369 IF iface_det_rec.pricing_attribute63 IS NULL THEN
2370 c_price_tbl(price_idx).pricing_attribute63 := l_fnd_g_char;
2371 ELSE
2372 c_price_tbl(price_idx).pricing_attribute63 := iface_det_rec.pricing_attribute63;
2373 END IF;
2374
2375 IF iface_det_rec.pricing_attribute64 IS NULL THEN
2376 c_price_tbl(price_idx).pricing_attribute64 := l_fnd_g_char;
2377 ELSE
2378 c_price_tbl(price_idx).pricing_attribute64 := iface_det_rec.pricing_attribute64;
2379 END IF;
2380
2381 IF iface_det_rec.pricing_attribute5 IS NULL THEN
2382 c_price_tbl(price_idx).pricing_attribute65 := l_fnd_g_char;
2383 ELSE
2384 c_price_tbl(price_idx).pricing_attribute65 := iface_det_rec.pricing_attribute65;
2385 END IF;
2386
2387 IF iface_det_rec.pricing_attribute66 IS NULL THEN
2388 c_price_tbl(price_idx).pricing_attribute66 := l_fnd_g_char;
2389 ELSE
2390 c_price_tbl(price_idx).pricing_attribute66 := iface_det_rec.pricing_attribute66;
2391 END IF;
2392
2393 IF iface_det_rec.pricing_attribute67 IS NULL THEN
2394 c_price_tbl(price_idx).pricing_attribute67 := l_fnd_g_char;
2395 ELSE
2396 c_price_tbl(price_idx).pricing_attribute67 := iface_det_rec.pricing_attribute67;
2397 END IF;
2398
2399 IF iface_det_rec.pricing_attribute68 IS NULL THEN
2400 c_price_tbl(price_idx).pricing_attribute68 := l_fnd_g_char;
2401 ELSE
2402 c_price_tbl(price_idx).pricing_attribute68 := iface_det_rec.pricing_attribute68;
2403 END IF;
2404
2405 IF iface_det_rec.pricing_attribute69 IS NULL THEN
2406 c_price_tbl(price_idx).pricing_attribute69 := l_fnd_g_char;
2407 ELSE
2408 c_price_tbl(price_idx).pricing_attribute69 := iface_det_rec.pricing_attribute69;
2409 END IF;
2410
2411 IF iface_det_rec.pricing_attribute70 IS NULL THEN
2412 c_price_tbl(price_idx).pricing_attribute70 := l_fnd_g_char;
2413 ELSE
2414 c_price_tbl(price_idx).pricing_attribute70 := iface_det_rec.pricing_attribute70;
2415 END IF;
2416
2417 IF iface_det_rec.pricing_attribute71 IS NULL THEN
2418 c_price_tbl(price_idx).pricing_attribute71 := l_fnd_g_char;
2419 ELSE
2420 c_price_tbl(price_idx).pricing_attribute71 := iface_det_rec.pricing_attribute71;
2421 END IF;
2422
2423 IF iface_det_rec.pricing_attribute72 IS NULL THEN
2424 c_price_tbl(price_idx).pricing_attribute72 := l_fnd_g_char;
2425 ELSE
2426 c_price_tbl(price_idx).pricing_attribute72 := iface_det_rec.pricing_attribute72;
2427 END IF;
2428
2429 IF iface_det_rec.pricing_attribute73 IS NULL THEN
2430 c_price_tbl(price_idx).pricing_attribute73 := l_fnd_g_char;
2431 ELSE
2432 c_price_tbl(price_idx).pricing_attribute73 := iface_det_rec.pricing_attribute73;
2433 END IF;
2434
2435 IF iface_det_rec.pricing_attribute74 IS NULL THEN
2436 c_price_tbl(price_idx).pricing_attribute74 := l_fnd_g_char;
2437 ELSE
2438 c_price_tbl(price_idx).pricing_attribute74 := iface_det_rec.pricing_attribute74;
2439 END IF;
2440
2441 IF iface_det_rec.pricing_attribute75 IS NULL THEN
2442 c_price_tbl(price_idx).pricing_attribute75 := l_fnd_g_char;
2443 ELSE
2444 c_price_tbl(price_idx).pricing_attribute75 := iface_det_rec.pricing_attribute75;
2445 END IF;
2446
2447 IF iface_det_rec.pricing_attribute76 IS NULL THEN
2448 c_price_tbl(price_idx).pricing_attribute76 := l_fnd_g_char;
2449 ELSE
2450 c_price_tbl(price_idx).pricing_attribute76 := iface_det_rec.pricing_attribute76;
2451 END IF;
2452
2453 IF iface_det_rec.pricing_attribute77 IS NULL THEN
2454 c_price_tbl(price_idx).pricing_attribute77 := l_fnd_g_char;
2455 ELSE
2456 c_price_tbl(price_idx).pricing_attribute77 := iface_det_rec.pricing_attribute77;
2457 END IF;
2458
2459 IF iface_det_rec.pricing_attribute78 IS NULL THEN
2460 c_price_tbl(price_idx).pricing_attribute78 := l_fnd_g_char;
2461 ELSE
2462 c_price_tbl(price_idx).pricing_attribute78 := iface_det_rec.pricing_attribute78;
2463 END IF;
2464
2465 IF iface_det_rec.pricing_attribute79 IS NULL THEN
2466 c_price_tbl(price_idx).pricing_attribute79 := l_fnd_g_char;
2467 ELSE
2468 c_price_tbl(price_idx).pricing_attribute79 := iface_det_rec.pricing_attribute79;
2469 END IF;
2470
2471 IF iface_det_rec.pricing_attribute80 IS NULL THEN
2472 c_price_tbl(price_idx).pricing_attribute80 := l_fnd_g_char;
2473 ELSE
2474 c_price_tbl(price_idx).pricing_attribute80 := iface_det_rec.pricing_attribute80;
2475 END IF;
2476
2477 IF iface_det_rec.pricing_attribute81 IS NULL THEN
2478 c_price_tbl(price_idx).pricing_attribute81 := l_fnd_g_char;
2479 ELSE
2480 c_price_tbl(price_idx).pricing_attribute81 := iface_det_rec.pricing_attribute81;
2481 END IF;
2482
2483 IF iface_det_rec.pricing_attribute82 IS NULL THEN
2484 c_price_tbl(price_idx).pricing_attribute82 := l_fnd_g_char;
2485 ELSE
2486 c_price_tbl(price_idx).pricing_attribute82 := iface_det_rec.pricing_attribute82;
2487 END IF;
2488
2489 IF iface_det_rec.pricing_attribute83 IS NULL THEN
2490 c_price_tbl(price_idx).pricing_attribute83 := l_fnd_g_char;
2491 ELSE
2492 c_price_tbl(price_idx).pricing_attribute83 := iface_det_rec.pricing_attribute83;
2493 END IF;
2494
2495 IF iface_det_rec.pricing_attribute84 IS NULL THEN
2496 c_price_tbl(price_idx).pricing_attribute84 := l_fnd_g_char;
2497 ELSE
2498 c_price_tbl(price_idx).pricing_attribute84 := iface_det_rec.pricing_attribute84;
2499 END IF;
2500
2501 IF iface_det_rec.pricing_attribute85 IS NULL THEN
2502 c_price_tbl(price_idx).pricing_attribute85 := l_fnd_g_char;
2503 ELSE
2504 c_price_tbl(price_idx).pricing_attribute85 := iface_det_rec.pricing_attribute85;
2505 END IF;
2506
2507 IF iface_det_rec.pricing_attribute86 IS NULL THEN
2508 c_price_tbl(price_idx).pricing_attribute86 := l_fnd_g_char;
2509 ELSE
2510 c_price_tbl(price_idx).pricing_attribute86 := iface_det_rec.pricing_attribute86;
2511 END IF;
2512
2513 IF iface_det_rec.pricing_attribute87 IS NULL THEN
2514 c_price_tbl(price_idx).pricing_attribute87 := l_fnd_g_char;
2515 ELSE
2516 c_price_tbl(price_idx).pricing_attribute87 := iface_det_rec.pricing_attribute87;
2517 END IF;
2518
2519 IF iface_det_rec.pricing_attribute88 IS NULL THEN
2520 c_price_tbl(price_idx).pricing_attribute88 := l_fnd_g_char;
2521 ELSE
2522 c_price_tbl(price_idx).pricing_attribute88 := iface_det_rec.pricing_attribute88;
2523 END IF;
2524
2525 IF iface_det_rec.pricing_attribute89 IS NULL THEN
2526 c_price_tbl(price_idx).pricing_attribute89 := l_fnd_g_char;
2527 ELSE
2528 c_price_tbl(price_idx).pricing_attribute89 := iface_det_rec.pricing_attribute89;
2529 END IF;
2530
2531 IF iface_det_rec.pricing_attribute90 IS NULL THEN
2532 c_price_tbl(price_idx).pricing_attribute90 := l_fnd_g_char;
2533 ELSE
2534 c_price_tbl(price_idx).pricing_attribute90 := iface_det_rec.pricing_attribute90;
2535 END IF;
2536
2537 IF iface_det_rec.pricing_attribute91 IS NULL THEN
2538 c_price_tbl(price_idx).pricing_attribute91 := l_fnd_g_char;
2539 ELSE
2540 c_price_tbl(price_idx).pricing_attribute91 := iface_det_rec.pricing_attribute91;
2541 END IF;
2542
2543 IF iface_det_rec.pricing_attribute92 IS NULL THEN
2544 c_price_tbl(price_idx).pricing_attribute92 := l_fnd_g_char;
2545 ELSE
2546 c_price_tbl(price_idx).pricing_attribute92 := iface_det_rec.pricing_attribute92;
2547 END IF;
2548
2549 IF iface_det_rec.pricing_attribute93 IS NULL THEN
2550 c_price_tbl(price_idx).pricing_attribute93 := l_fnd_g_char;
2551 ELSE
2552 c_price_tbl(price_idx).pricing_attribute93 := iface_det_rec.pricing_attribute93;
2553 END IF;
2554
2555 IF iface_det_rec.pricing_attribute94 IS NULL THEN
2556 c_price_tbl(price_idx).pricing_attribute94 := l_fnd_g_char;
2557 ELSE
2558 c_price_tbl(price_idx).pricing_attribute94 := iface_det_rec.pricing_attribute94;
2559 END IF;
2560
2561 IF iface_det_rec.pricing_attribute95 IS NULL THEN
2562 c_price_tbl(price_idx).pricing_attribute95 := l_fnd_g_char;
2563 ELSE
2564 c_price_tbl(price_idx).pricing_attribute95 := iface_det_rec.pricing_attribute95;
2565 END IF;
2566
2567 IF iface_det_rec.pricing_attribute96 IS NULL THEN
2568 c_price_tbl(price_idx).pricing_attribute96 := l_fnd_g_char;
2569 ELSE
2570 c_price_tbl(price_idx).pricing_attribute96 := iface_det_rec.pricing_attribute96;
2571 END IF;
2572
2573 IF iface_det_rec.pricing_attribute97 IS NULL THEN
2574 c_price_tbl(price_idx).pricing_attribute97 := l_fnd_g_char;
2575 ELSE
2576 c_price_tbl(price_idx).pricing_attribute97 := iface_det_rec.pricing_attribute97;
2577 END IF;
2578
2579 IF iface_det_rec.pricing_attribute98 IS NULL THEN
2580 c_price_tbl(price_idx).pricing_attribute98 := l_fnd_g_char;
2581 ELSE
2582 c_price_tbl(price_idx).pricing_attribute98 := iface_det_rec.pricing_attribute98;
2583 END IF;
2584
2585 IF iface_det_rec.pricing_attribute99 IS NULL THEN
2586 c_price_tbl(price_idx).pricing_attribute99 := l_fnd_g_char;
2587 ELSE
2588 c_price_tbl(price_idx).pricing_attribute99 := iface_det_rec.pricing_attribute99;
2589 END IF;
2590
2591 IF iface_det_rec.pricing_attribute100 IS NULL THEN
2592 c_price_tbl(price_idx).pricing_attribute100 := l_fnd_g_char;
2593 ELSE
2594 c_price_tbl(price_idx).pricing_attribute100 := iface_det_rec.pricing_attribute100;
2595 END IF;
2596
2597 IF iface_det_rec.pricing_flex_context IS NULL THEN
2598 c_price_tbl(price_idx).pricing_context := l_fnd_g_char;
2599 ELSE
2600 c_price_tbl(price_idx).pricing_context := iface_det_rec.pricing_context;
2601 END IF;
2602
2603 IF iface_det_rec.pricing_flex_attribute1 IS NULL THEN
2604 c_price_tbl(price_idx).attribute1 := l_fnd_g_char;
2605 ELSE
2606 c_price_tbl(price_idx).attribute1 := iface_det_rec.pricing_flex_attribute1;
2607 END IF;
2608
2609 IF iface_det_rec.pricing_flex_attribute2 IS NULL THEN
2610 c_price_tbl(price_idx).attribute2 := l_fnd_g_char;
2611 ELSE
2612 c_price_tbl(price_idx).attribute2 := iface_det_rec.pricing_flex_attribute2;
2613 END IF;
2614
2615 IF iface_det_rec.pricing_flex_attribute3 IS NULL THEN
2616 c_price_tbl(price_idx).attribute3 := l_fnd_g_char;
2617 ELSE
2618 c_price_tbl(price_idx).attribute3 := iface_det_rec.pricing_flex_attribute3;
2619 END IF;
2620
2621 IF iface_det_rec.pricing_flex_attribute4 IS NULL THEN
2622 c_price_tbl(price_idx).attribute4 := l_fnd_g_char;
2623 ELSE
2624 c_price_tbl(price_idx).attribute4 := iface_det_rec.pricing_flex_attribute4;
2625 END IF;
2626
2627 IF iface_det_rec.pricing_flex_attribute5 IS NULL THEN
2628 c_price_tbl(price_idx).attribute5 := l_fnd_g_char;
2629 ELSE
2630 c_price_tbl(price_idx).attribute5 := iface_det_rec.pricing_flex_attribute5;
2631 END IF;
2632
2633 IF iface_det_rec.pricing_flex_attribute6 IS NULL THEN
2634 c_price_tbl(price_idx).attribute6 := l_fnd_g_char;
2635 ELSE
2636 c_price_tbl(price_idx).attribute6 := iface_det_rec.pricing_flex_attribute6;
2637 END IF;
2638
2639 IF iface_det_rec.pricing_flex_attribute7 IS NULL THEN
2640 c_price_tbl(price_idx).attribute7 := l_fnd_g_char;
2641 ELSE
2642 c_price_tbl(price_idx).attribute7 := iface_det_rec.pricing_flex_attribute7;
2643 END IF;
2644
2645 IF iface_det_rec.pricing_flex_attribute8 IS NULL THEN
2646 c_price_tbl(price_idx).attribute8 := l_fnd_g_char;
2647 ELSE
2648 c_price_tbl(price_idx).attribute8 := iface_det_rec.pricing_flex_attribute8;
2649 END IF;
2650
2651 IF iface_det_rec.pricing_flex_attribute9 IS NULL THEN
2652 c_price_tbl(price_idx).attribute9 := l_fnd_g_char;
2653 ELSE
2654 c_price_tbl(price_idx).attribute9 := iface_det_rec.pricing_flex_attribute9;
2655 END IF;
2656
2657 IF iface_det_rec.pricing_flex_attribute10 IS NULL THEN
2658 c_price_tbl(price_idx).attribute10 := l_fnd_g_char;
2659 ELSE
2660 c_price_tbl(price_idx).attribute10 := iface_det_rec.pricing_flex_attribute10;
2661 END IF;
2662
2663 IF iface_det_rec.pricing_flex_attribute11 IS NULL THEN
2664 c_price_tbl(price_idx).attribute11 := l_fnd_g_char;
2665 ELSE
2666 c_price_tbl(price_idx).attribute11 := iface_det_rec.pricing_flex_attribute11;
2667 END IF;
2668
2669 IF iface_det_rec.pricing_flex_attribute12 IS NULL THEN
2670 c_price_tbl(price_idx).attribute12 := l_fnd_g_char;
2671 ELSE
2672 c_price_tbl(price_idx).attribute12 := iface_det_rec.pricing_flex_attribute12;
2673 END IF;
2674
2675 IF iface_det_rec.pricing_flex_attribute13 IS NULL THEN
2676 c_price_tbl(price_idx).attribute13 := l_fnd_g_char;
2677 ELSE
2678 c_price_tbl(price_idx).attribute13 := iface_det_rec.pricing_flex_attribute13;
2679 END IF;
2680
2681 IF iface_det_rec.pricing_flex_attribute14 IS NULL THEN
2682 c_price_tbl(price_idx).attribute14 := l_fnd_g_char;
2683 ELSE
2684 c_price_tbl(price_idx).attribute14 := iface_det_rec.pricing_flex_attribute14;
2685 END IF;
2686
2687 IF iface_det_rec.pricing_flex_attribute15 IS NULL THEN
2688 c_price_tbl(price_idx).attribute15 := l_fnd_g_char;
2689 ELSE
2690 c_price_tbl(price_idx).attribute15 := iface_det_rec.pricing_flex_attribute15;
2691 END IF;
2692
2693 c_price_tbl(price_idx).object_version_number := 1;
2694
2695 END IF;
2696
2697 price_idx := price_idx + 1;
2698 END IF; -- End of Pricing Attributes
2699
2700 -- Org Assignments
2701 IF (iface_det_rec.operating_unit IS NOT NULL OR
2702 iface_det_rec.ou_relation_type IS NOT NULL OR
2703 iface_det_rec.ou_start_date IS NOT NULL OR
2704 iface_det_rec.ou_end_date IS NOT NULL) THEN
2705
2706 IF iface_det_rec.transaction_identifier IS NOT NULL THEN
2707
2708 c_org_assign_tbl(orgass_idx).instance_ou_id := NULL;
2709 c_org_assign_tbl(orgass_idx).instance_id := NULL;
2710 c_org_assign_tbl(orgass_idx).parent_tbl_index := inst_idx;
2711
2712 IF iface_det_rec.operating_unit IS NULL THEN
2713 c_org_assign_tbl(orgass_idx).operating_unit_id := l_fnd_g_num;
2714 ELSE
2715 c_org_assign_tbl(orgass_idx).operating_unit_id := iface_det_rec.operating_unit;
2716 END IF;
2717
2718 IF iface_det_rec.ou_relation_type IS NULL THEN
2719 c_org_assign_tbl(orgass_idx).relationship_type_code := l_fnd_g_char;
2720 ELSE
2721 c_org_assign_tbl(orgass_idx).relationship_type_code := iface_det_rec.ou_relation_type;
2722 END IF;
2723
2724 c_org_assign_tbl(orgass_idx).active_start_date := l_fnd_g_date;
2725
2726 IF iface_det_rec.ou_end_date IS NULL THEN
2727 c_org_assign_tbl(orgass_idx).active_end_date := l_fnd_g_date;
2728 ELSE
2729 c_org_assign_tbl(orgass_idx).active_end_date := iface_det_rec.ou_end_date;
2730 END IF;
2731
2732 c_org_assign_tbl(orgass_idx).context := l_fnd_g_char;
2733 c_org_assign_tbl(orgass_idx).attribute1 := l_fnd_g_char;
2734 c_org_assign_tbl(orgass_idx).attribute2 := l_fnd_g_char;
2735 c_org_assign_tbl(orgass_idx).attribute3 := l_fnd_g_char;
2736 c_org_assign_tbl(orgass_idx).attribute4 := l_fnd_g_char;
2737 c_org_assign_tbl(orgass_idx).attribute5 := l_fnd_g_char;
2738 c_org_assign_tbl(orgass_idx).attribute6 := l_fnd_g_char;
2739 c_org_assign_tbl(orgass_idx).attribute7 := l_fnd_g_char;
2740 c_org_assign_tbl(orgass_idx).attribute8 := l_fnd_g_char;
2741 c_org_assign_tbl(orgass_idx).attribute9 := l_fnd_g_char;
2742 c_org_assign_tbl(orgass_idx).attribute10 := l_fnd_g_char;
2743 c_org_assign_tbl(orgass_idx).attribute11 := l_fnd_g_char;
2744 c_org_assign_tbl(orgass_idx).attribute12 := l_fnd_g_char;
2745 c_org_assign_tbl(orgass_idx).attribute13 := l_fnd_g_char;
2746 c_org_assign_tbl(orgass_idx).attribute14 := l_fnd_g_char;
2747 c_org_assign_tbl(orgass_idx).attribute15 := l_fnd_g_char;
2748 c_org_assign_tbl(orgass_idx).object_version_number := 1;
2749
2750 END IF;
2751
2752 orgass_idx := orgass_idx + 1;
2753 END IF; -- End of Org Assignments
2754
2755 -- Transaction Table
2756 c_txn_tbl(inst_idx).transaction_date := iface_det_rec.source_transaction_date;
2757 c_txn_tbl(inst_idx).source_transaction_date := iface_det_rec.source_transaction_date;
2758 c_txn_tbl(inst_idx).transaction_type_id:= l_txn_type_id;
2759 c_txn_tbl(inst_idx).source_group_ref:= iface_det_rec.transaction_identifier;
2760 c_txn_tbl(inst_idx).transaction_quantity:= iface_det_rec.quantity;
2761 c_txn_tbl(inst_idx).transaction_uom_code:= iface_det_rec.unit_of_measure_code;
2762 c_txn_tbl(inst_idx).transacted_by := iface_det_rec.created_by;
2763 c_txn_tbl(inst_idx).transaction_status_code := 'COMPLETE';
2764 c_txn_tbl(inst_idx).transaction_action_code := NULL;
2765 c_txn_tbl(inst_idx).object_version_number := 1;
2766
2767 inst_idx := inst_idx + 1;
2768 ELSE -- update candidate
2769 IF(l_debug_level>1) THEN
2770 FND_File.Put_Line(Fnd_File.LOG,'CASE -update item instance id: '||l_instance_id);
2771 END IF;
2772 --u_instance_tbl(inst_idx).instance_id := l_instance_id;
2773 u_instance_tbl(u_inst_idx).instance_id := l_instance_id; --Added for open
2774 -- call resolve_update_ids
2775 BEGIN
2776 update csi_instance_interface
2777 set instance_id = l_instance_id
2778 where inst_interface_id = iface_Det_rec.inst_interface_id;
2779 END;
2780
2781 u_inst_idx:=u_inst_idx+1; -- Added for open
2782 END IF;-- Added for open
2783
2784 -- Added for open
2785 u_txn_rec.transaction_date := iface_det_rec.source_transaction_date;
2786 u_txn_rec.source_transaction_date := iface_det_rec.source_transaction_date;
2787 u_txn_rec.transaction_type_id:= l_txn_type_id;
2788 u_txn_rec.source_group_ref:= iface_det_rec.transaction_identifier;
2789 u_txn_rec.transaction_quantity:= iface_det_rec.quantity;
2790 u_txn_rec.transaction_uom_code:= iface_det_rec.unit_of_measure_code;
2791 u_txn_rec.transacted_by := iface_det_rec.created_by;
2792 u_txn_rec.transaction_status_code := 'COMPLETE';
2793 u_txn_rec.transaction_action_code := NULL;
2794 u_txn_rec.object_version_number := 1;
2795 -- End addition for open
2796 END LOOP;
2797
2798 IF u_instance_tbl.COUNT >0
2799 THEN
2800 IF(l_debug_level>1) THEN
2801 FND_File.Put_Line(Fnd_File.LOG,'Resolving Update Related Ids:');
2802 END IF;
2803 csi_ml_util_pvt.resolve_update_ids
2804 (p_source_system_name => p_source_system_name,
2805 p_txn_identifier => get_txns_rec.transaction_identifier,
2806 x_return_status => l_return_status,
2807 x_error_message => l_error_message);
2808 IF NOT l_return_status = l_fnd_success
2809 THEN
2810 IF(l_debug_level>1) THEN
2811 FND_File.Put_Line(Fnd_File.LOG,'Error from csi_ml_util_pvt.resolve_update_ids');
2812 END IF;
2813 SAVEPOINT s_txnmiddle;
2814 RAISE g_exc_error;
2815 END IF;
2816
2817 csi_ml_update_pvt.populate_recs(
2818 p_txn_identifier =>u_txn_rec.source_group_ref, --iface_det_rec.transaction_identifier,
2819 p_source_system_name =>p_source_system_name, -- modified txn_identifer for open
2820 x_instance_tbl => u_instance_tbl,
2821 x_party_tbl => u_party_tbl,
2822 x_account_tbl => u_account_tbl,
2823 x_ext_attrib_value_tbl => u_ext_attrib_tbl,
2824 x_price_tbl => u_price_tbl,
2825 x_org_assign_tbl => u_org_assignments_tbl,
2826 x_asset_assignment_tbl => u_asset_assignment_tbl, -- bnarayan added for R12
2827 x_return_status => l_return_status,
2828 x_error_message=> l_error_message);
2829
2830
2831 IF NOT l_return_status = l_fnd_success
2832 THEN
2833 IF(l_debug_level>1) THEN
2834 FND_File.Put_Line(Fnd_File.LOG,'Error from csi_ml_update_pvt.populate_recs ');
2835 END IF;
2836 SAVEPOINT s_txnmiddle;
2837 RAISE g_exc_error;
2838 END IF;
2839 END IF;
2840 /* Commented for open
2841 u_txn_rec.transaction_date := iface_det_rec.source_transaction_date;
2842 u_txn_rec.source_transaction_date := iface_det_rec.source_transaction_date;
2843 u_txn_rec.transaction_type_id:= l_txn_type_id;
2844 u_txn_rec.source_group_ref:= iface_det_rec.transaction_identifier;
2845 u_txn_rec.transaction_quantity:= iface_det_rec.quantity;
2846 u_txn_rec.transaction_uom_code:= iface_det_rec.unit_of_measure_code;
2847 u_txn_rec.transacted_by := iface_det_rec.created_by;
2848 u_txn_rec.transaction_status_code := 'COMPLETE';
2849 u_txn_rec.transaction_action_code := NULL;
2850 u_txn_rec.object_version_number := 1;
2851 */
2852 -- END IF; -- End of Update or Create If -- commented for open
2853 -- END LOOP; -- commented for open
2854
2855 IF c_instance_tbl.COUNT > 0
2856 THEN
2857 l_return_status := NULL;
2858 csi_item_instance_grp.create_item_instance (
2859 p_api_version => l_api_version
2860 ,p_commit => l_commit
2861 ,p_init_msg_list => l_init_msg_list
2862 ,p_validation_level => l_validation_level
2863 ,p_instance_tbl => c_instance_tbl
2864 ,p_ext_attrib_values_tbl => c_ext_attrib_tbl
2865 ,p_party_tbl => c_party_tbl
2866 ,p_account_tbl => c_account_tbl
2867 ,p_pricing_attrib_tbl => c_price_tbl
2868 ,p_org_assignments_tbl => c_org_assign_tbl
2869 ,p_asset_assignment_tbl => c_asset_assignment_tbl
2870 ,p_txn_tbl => c_txn_tbl
2871 ,p_grp_error_tbl => c_grp_error_tbl
2872 ,x_return_status => l_return_status
2873 ,x_msg_count => l_msg_count
2874 ,x_msg_data => l_msg_data);
2875
2876 IF NOT l_return_status = l_fnd_success
2877 THEN
2878 l_msg_index := 1;
2879 l_Error_Message := l_Msg_Data;
2880 WHILE l_msg_count > 0 LOOP
2881 l_Error_Message := FND_MSG_PUB.GET(l_msg_index, FND_API.G_FALSE);
2882 l_msg_index := l_msg_index + 1;
2883 l_Msg_Count := l_Msg_Count - 1;
2884 END LOOP;
2885 SAVEPOINT s_txnmiddle;
2886 RAISE g_exc_error;
2887 END IF;
2888 END IF;
2889
2890 IF c_instance_tbl.COUNT >0
2891 THEN
2892 FOR i in c_instance_tbl.FIRST .. c_instance_tbl.LAST
2893 LOOP
2894 IF c_instance_tbl.EXISTS(i)
2895 THEN
2896 IF c_instance_tbl(i).processed_flag = 'E'
2897 THEN
2898 SAVEPOINT s_txnmiddle;
2899 RAISE g_inst_error;
2900 END IF;
2901 END IF;
2902 END LOOP;
2903 END IF;
2904
2905 IF c_instance_tbl.COUNT >0
2906 THEN
2907 FOR i IN 1 .. c_instance_tbl.COUNT LOOP
2908 IF c_instance_tbl(i).instance_id IS NOT NULL
2909 THEN
2910 IF(l_debug_level>1) THEN
2911 FND_File.Put_Line(Fnd_File.LOG,'Instance Crearted with number : '||c_instance_tbl(i).instance_id);
2912 END IF;
2913 END IF;
2914 END LOOP;
2915 END IF;
2916 IF u_instance_tbl.COUNT > 0
2917 THEN
2918 csi_item_instance_grp.update_item_instance (
2919 p_api_version => l_api_version
2920 ,p_commit => l_commit
2921 ,p_init_msg_list => l_init_msg_list
2922 ,p_validation_level => l_validation_level
2923 ,p_instance_tbl => u_instance_tbl
2924 ,p_ext_attrib_values_tbl => u_ext_attrib_tbl
2925 ,p_party_tbl => u_party_tbl
2926 ,p_account_tbl => u_account_tbl
2927 ,p_pricing_attrib_tbl => u_price_tbl
2928 ,p_org_assignments_tbl => u_org_assignments_tbl
2929 ,p_asset_assignment_tbl => u_asset_assignment_tbl
2930 ,p_txn_rec => u_txn_rec
2931 ,x_instance_id_lst => u_instance_id_lst
2932 ,p_grp_upd_error_tbl => u_grp_error_tbl
2933 ,x_return_status => l_return_status
2934 ,x_msg_count => l_msg_count
2935 ,x_msg_data => l_msg_data);
2936
2937 IF NOT l_return_status = l_fnd_success
2938 THEN
2939 l_msg_index := 1;
2940 l_Error_Message := l_Msg_Data;
2941 WHILE l_msg_count > 0
2942 LOOP
2943 l_Error_Message := FND_MSG_PUB.GET(l_msg_index, FND_API.G_FALSE);
2944 l_msg_index := l_msg_index + 1;
2945 l_Msg_Count := l_Msg_Count - 1;
2946 END LOOP;
2947 SAVEPOINT s_txnmiddle;
2948 RAISE g_exc_error;
2949 END IF;
2950 END IF;
2951
2952 IF u_instance_tbl.COUNT >0
2953 THEN
2954 FOR i in u_instance_tbl.FIRST .. u_instance_tbl.LAST
2955 LOOP
2956 IF u_instance_tbl.EXISTS(i)
2957 THEN
2958 IF u_instance_tbl(i).processed_flag = 'E'
2959 THEN
2960 SAVEPOINT s_txnmiddle;
2961 RAISE g_upd_error;
2962 ELSE
2963 UPDATE csi_instance_interface
2964 SET process_status = 'P'
2965 WHERE instance_id = u_instance_tbl(i).instance_id;
2966 END IF;
2967 END IF;
2968 END LOOP;
2969 END IF;
2970 /*
2971 rel_idx := 1;
2972 c_relationship_tbl.DELETE;
2973 u_relationship_tbl.DELETE;
2974 FOR irel_det_rec IN irel_det_cur(get_txns_rec.transaction_identifier,
2975 p_source_system_name)
2976 LOOP
2977 check_rel_exists(irel_det_rec.new_subject_id,
2978 irel_det_rec.new_object_id,
2979 l_exists,
2980 l_rel_rec);
2981 IF NOT l_exists
2982 THEN
2983 c_relationship_tbl(rel_idx).subject_id :=irel_det_rec.new_subject_id;
2984 c_relationship_tbl(rel_idx).object_id := irel_det_rec.new_object_id;
2985 c_relationship_tbl(rel_idx).relationship_type_code := irel_det_rec.relationship_type_code;
2986 c_relationship_tbl(rel_idx).active_start_date:=irel_det_rec.active_start_date;
2987 c_relationship_tbl(rel_idx).active_end_date:=irel_det_rec.active_end_date;
2988 c_relationship_tbl(rel_idx).position_reference:= irel_det_rec.position_reference;
2989 c_relationship_tbl(rel_idx).display_order:= irel_det_rec.display_order;
2990 c_relationship_tbl(rel_idx).object_version_number := 1;
2991 c_rel_txn_rec.transaction_date := irel_det_rec.source_transaction_date;
2992 c_rel_txn_rec.source_transaction_date := irel_det_rec.source_transaction_date;
2993 c_rel_txn_rec.transaction_type_id:= l_txn_type_id;
2994 c_rel_txn_rec.source_group_ref:= irel_det_rec.transaction_identifier;
2995 c_rel_txn_rec.transaction_quantity:= NULL;
2996 c_rel_txn_rec.transaction_uom_code:= NULL;
2997 c_rel_txn_rec.transacted_by := irel_det_rec.created_by;
2998 c_rel_txn_rec.transaction_status_code := 'COMPLETE';
2999 c_rel_txn_rec.transaction_action_code := NULL;
3000 c_rel_txn_rec.object_version_number := 1;
3001 rel_idx := rel_idx + 1;
3002 ELSE
3003 u_relationship_tbl(rel_idx).relationship_id :=l_rel_rec.relationship_id;
3004 u_relationship_tbl(rel_idx).subject_id :=irel_det_rec.new_subject_id;
3005 u_relationship_tbl(rel_idx).object_id := irel_det_rec.new_object_id;
3006 u_relationship_tbl(rel_idx).object_version_number := l_rel_rec.object_version_number;
3007 /* Commented for bug 3150717
3008 IF NOT l_rel_rec.relationship_type_code = irel_det_rec.relationship_type_code
3009 THEN
3010
3011 u_relationship_tbl(rel_idx).relationship_type_code := irel_det_rec.relationship_type_code;
3012 /*
3013 END IF;
3014
3015 IF NOT l_rel_rec.active_start_date= irel_det_rec.active_start_date
3016 THEN
3017 u_relationship_tbl(rel_idx).active_start_date:=irel_det_rec.active_start_date;
3018 END IF;
3019 IF NOT l_rel_rec.active_end_date= irel_det_rec.active_end_date
3020 THEN
3021 u_relationship_tbl(rel_idx).active_end_date:=irel_det_rec.active_end_date;
3022 END IF;
3023 IF NOT l_rel_rec.position_reference= irel_det_rec.position_reference
3024 THEN
3025 u_relationship_tbl(rel_idx).position_reference:= irel_det_rec.position_reference;
3026 END IF;
3027 IF NOT l_rel_rec.display_order= irel_det_rec.display_order
3028 THEN
3029 u_relationship_tbl(rel_idx).display_order:= irel_det_rec.display_order;
3030 END IF;
3031 IF NOT l_rel_rec.mandatory_flag= irel_det_rec.mandatory_flag
3032 THEN
3033 u_relationship_tbl(rel_idx).mandatory_flag:= irel_det_rec.mandatory_flag;
3034 END IF;
3035 IF NOT l_rel_rec.context = irel_det_rec.context
3036 THEN
3037 u_relationship_tbl(rel_idx).context:= irel_det_rec.context;
3038 END IF;
3039 --IF NOT l_rel_rec.relationship_direction= irel_det_rec.relationship_direction
3040 --THEN
3041 --u_relationship_tbl(rel_idx).relationship_direction:= irel_det_rec.relationship_direction;
3042 --END IF;
3043 IF NOT l_rel_rec.attribute1 = irel_det_rec.attribute1
3044 THEN
3045 u_relationship_tbl(rel_idx).attribute1 := irel_det_rec.attribute1;
3046 END IF;
3047 IF NOT l_rel_rec.attribute2 = irel_det_rec.attribute2
3048 THEN
3049 u_relationship_tbl(rel_idx).attribute2 := irel_det_rec.attribute2;
3050 END IF;
3051 IF NOT l_rel_rec.attribute3 = irel_det_rec.attribute3
3052 THEN
3053 u_relationship_tbl(rel_idx).attribute3 := irel_det_rec.attribute3;
3054 END IF;
3055 IF NOT l_rel_rec.attribute4 = irel_det_rec.attribute4
3056 THEN
3057 u_relationship_tbl(rel_idx).attribute4 := irel_det_rec.attribute4;
3058 END IF;
3059 IF NOT l_rel_rec.attribute5 = irel_det_rec.attribute5
3060 THEN
3061 u_relationship_tbl(rel_idx).attribute5 := irel_det_rec.attribute5;
3062 END IF;
3063 IF NOT l_rel_rec.attribute6 = irel_det_rec.attribute6
3064 THEN
3065 u_relationship_tbl(rel_idx).attribute6 := irel_det_rec.attribute6;
3066 END IF;
3067 IF NOT l_rel_rec.attribute7 = irel_det_rec.attribute7
3068 THEN
3069 u_relationship_tbl(rel_idx).attribute7 := irel_det_rec.attribute7;
3070 END IF;
3071 IF NOT l_rel_rec.attribute8 = irel_det_rec.attribute8
3072 THEN
3073 u_relationship_tbl(rel_idx).attribute8 := irel_det_rec.attribute8;
3074 END IF;
3075 IF NOT l_rel_rec.attribute9 = irel_det_rec.attribute9
3076 THEN
3077 u_relationship_tbl(rel_idx).attribute9 := irel_det_rec.attribute9;
3078 END IF;
3079 IF NOT l_rel_rec.attribute10 = irel_det_rec.attribute10
3080 THEN
3081 u_relationship_tbl(rel_idx).attribute10 := irel_det_rec.attribute10;
3082 END IF;
3083 IF NOT l_rel_rec.attribute11 = irel_det_rec.attribute11
3084 THEN
3085 u_relationship_tbl(rel_idx).attribute11 := irel_det_rec.attribute11;
3086 END IF;
3087 IF NOT l_rel_rec.attribute12 = irel_det_rec.attribute12
3088 THEN
3089 u_relationship_tbl(rel_idx).attribute12 := irel_det_rec.attribute12;
3090 END IF;
3091 IF NOT l_rel_rec.attribute13 = irel_det_rec.attribute13
3092 THEN
3093 u_relationship_tbl(rel_idx).attribute13 := irel_det_rec.attribute13;
3094 END IF;
3095 IF NOT l_rel_rec.attribute14 = irel_det_rec.attribute14
3096 THEN
3097 u_relationship_tbl(rel_idx).attribute14 := irel_det_rec.attribute14;
3098 END IF;
3099 IF NOT l_rel_rec.attribute15 = irel_det_rec.attribute15
3100 THEN
3101 u_relationship_tbl(rel_idx).attribute15 := irel_det_rec.attribute15;
3102 END IF;
3103 u_rel_txn_rec.transaction_date := irel_det_rec.source_transaction_date;
3104 u_rel_txn_rec.source_transaction_date := irel_det_rec.source_transaction_date;
3105 u_rel_txn_rec.transaction_type_id:= l_txn_type_id;
3106 u_rel_txn_rec.source_group_ref:= irel_det_rec.transaction_identifier;
3107 u_rel_txn_rec.transaction_quantity:= NULL;
3108 u_rel_txn_rec.transaction_uom_code:= NULL;
3109 u_rel_txn_rec.transacted_by := irel_det_rec.created_by;
3110 u_rel_txn_rec.transaction_status_code := 'COMPLETE';
3111 u_rel_txn_rec.transaction_action_code := NULL;
3112 u_rel_txn_rec.object_version_number := 1;
3113 rel_idx := rel_idx + 1;
3114 END IF;
3115 END LOOP;
3116
3117 IF c_relationship_tbl.COUNT>0
3118 THEN
3119 FND_File.Put_Line(Fnd_File.LOG,'creating relationships :');
3120 csi_ii_relationships_pub.create_relationship(
3121 p_api_version => l_api_version
3122 ,p_commit => l_commit
3123 ,p_init_msg_list => l_init_msg_list
3124 ,p_validation_level => l_validation_level
3125 ,p_relationship_tbl => c_relationship_tbl
3126 ,p_txn_rec => c_rel_txn_rec
3127 ,x_return_status => l_return_status
3128 ,x_msg_count => l_msg_count
3129 ,x_msg_data => l_msg_data);
3130
3131 IF NOT l_return_status = l_fnd_success
3132 THEN
3133 l_msg_index := 1;
3134 l_Error_Message := l_Msg_Data;
3135 WHILE l_msg_count > 0 LOOP
3136 l_Error_Message := FND_MSG_PUB.GET(l_msg_index, FND_API.G_FALSE);
3137 l_msg_index := l_msg_index + 1;
3138 l_Msg_Count := l_Msg_Count - 1;
3139 END LOOP;
3140 RAISE g_exc_error;
3141 END IF;
3142 END IF;
3143
3144 IF u_relationship_tbl.COUNT>0
3145 THEN
3146
3147 FND_File.Put_Line(Fnd_File.LOG,'updating relationships :');
3148 csi_ii_relationships_pub.update_relationship(
3149 p_api_version => l_api_version
3150 ,p_commit => l_commit
3151 ,p_init_msg_list => l_init_msg_list
3152 ,p_validation_level => l_validation_level
3153 ,p_relationship_tbl => u_relationship_tbl
3154 ,p_txn_rec => u_rel_txn_rec
3155 ,x_return_status => l_return_status
3156 ,x_msg_count => l_msg_count
3157 ,x_msg_data => l_msg_data);
3158
3159 IF NOT l_return_status = l_fnd_success
3160 THEN
3161 l_msg_index := 1;
3162 l_Error_Message := l_Msg_Data;
3163 WHILE l_msg_count > 0 LOOP
3164 l_Error_Message := FND_MSG_PUB.GET(l_msg_index, FND_API.G_FALSE);
3165 l_msg_index := l_msg_index + 1;
3166 l_Msg_Count := l_Msg_Count - 1;
3167 END LOOP;
3168 RAISE g_exc_error;
3169 END IF;
3170 END IF;
3171
3172 */
3173 l_success_count := l_success_count + 1; --Bug No: 9260500. removed this out of comment
3174 COMMIT;
3175 EXCEPTION
3176 WHEN g_inst_error THEN
3177 ROLLBACK TO s_txnmiddle;
3178 UPDATE_INTERFACE_TBL(c_instance_tbl,
3179 c_grp_error_tbl);
3180 l_failure_count := l_failure_count +1;
3181
3182 WHEN g_upd_error THEN
3183 ROLLBACK TO s_txnmiddle;
3184 UPDATE_INTERFACE_TBL(u_instance_tbl,
3185 u_grp_error_tbl);
3186 l_failure_count := l_failure_count +1;
3187
3188 WHEN g_exc_error THEN
3189 FND_File.Put_Line(Fnd_File.LOG,'error:'||l_error_message);
3190
3191 ROLLBACK TO s_txnmiddle;
3192 UPDATE csi_instance_interface
3193 SET process_Status = 'E',
3194 error_text = l_error_message
3195 WHERE transaction_identifier = get_txns_rec.transaction_identifier
3196 AND source_system_name = p_source_system_name;
3197 l_failure_count := l_failure_count +1;
3198 END;
3199 END LOOP;
3200
3201 l_return_status := fnd_api.g_ret_sts_success;
3202
3203 IF(l_debug_level>1) THEN
3204 FND_File.Put_Line(Fnd_File.LOG,'No of Transactions Processed :'||l_txn_count);
3205 FND_File.Put_Line(Fnd_File.LOG,'No of successful Transactions :'||l_success_count);
3206 FND_File.Put_Line(Fnd_File.LOG,'No of failed Transactions :'||l_failure_count);
3207 END IF;
3208
3209 BEGIN
3210 -- Added for releationships
3211 SELECT count(*)
3212 INTO l_found
3213 FROM csi_ii_relation_interface
3214 WHERE process_status='R';
3215
3216 IF l_found>0
3217 THEN
3218
3219 csi_ml_util_pvt.resolve_rel_ids
3220 (p_source_system => p_source_system_name
3221 ,p_txn_from_date => p_txn_from_date
3222 ,p_txn_to_date => p_txn_to_date
3223 ,x_return_status => l_return_status
3224 ,x_error_message => l_error_message
3225 );
3226 -- Need to check l_error_message
3227 IF(l_debug_level>1) THEN
3228 FND_File.Put_Line(Fnd_File.LOG,'Start for creating rel in single thread :'||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
3229 END IF;
3230 FOR irel_det_rec IN irel_det_cur
3231 LOOP
3232 check_rel_exists(irel_det_rec.subject_id,
3233 irel_det_rec.object_id,
3234 l_exists,
3235 l_rel_rec);
3236 SAVEPOINT create_update_relship;
3237 l_counter:=l_counter+1;
3238 IF NOT l_exists
3239 THEN
3240 c_relationship_tbl.DELETE;
3241 c_relationship_tbl(1).subject_id :=irel_det_rec.subject_id;
3242 c_relationship_tbl(1).object_id := irel_det_rec.object_id;
3243 c_relationship_tbl(1).relationship_type_code := irel_det_rec.relationship_type_code;
3244 c_relationship_tbl(1).active_start_date:=irel_det_rec.active_start_date;
3245 c_relationship_tbl(1).active_end_date:=irel_det_rec.active_end_date;
3246 c_relationship_tbl(1).position_reference:= irel_det_rec.position_reference;
3247 c_relationship_tbl(1).display_order:= irel_det_rec.display_order;
3248 c_relationship_tbl(1).object_version_number := 1;
3249 c_rel_txn_rec.transaction_date := sysdate; --irel_det_rec.source_transaction_date;
3250 c_rel_txn_rec.source_transaction_date :=sysdate; -- irel_det_rec.source_transaction_date;
3251 c_rel_txn_rec.transaction_type_id:= l_txn_type_id;
3252 --c_rel_txn_rec.source_group_ref:= irel_det_rec.transaction_identifier;
3253 c_rel_txn_rec.transaction_quantity:= NULL;
3254 c_rel_txn_rec.transaction_uom_code:= NULL;
3255 c_rel_txn_rec.transacted_by := -1; --irel_det_rec.created_by;
3256 c_rel_txn_rec.transaction_status_code := 'COMPLETE';
3257 c_rel_txn_rec.transaction_action_code := NULL;
3258 c_rel_txn_rec.object_version_number := 1;
3259 IF(l_debug_level>1) THEN
3260 FND_File.Put_Line(Fnd_File.LOG,'creating relationships :');
3261 END IF;
3262
3263 csi_ii_relationships_pvt.create_relationship(
3264 p_api_version => l_api_version
3265 ,p_commit => l_commit
3266 ,p_init_msg_list => l_init_msg_list
3267 ,p_validation_level => l_validation_level
3268 ,p_relationship_tbl => c_relationship_tbl
3269 ,p_txn_rec => c_rel_txn_rec
3270 ,x_return_status => l_return_status
3271 ,x_msg_count => l_msg_count
3272 ,x_msg_data => l_msg_data);
3273
3274 IF NOT l_return_status = l_fnd_success
3275 THEN
3276 l_msg_index := 1;
3277 l_Error_Message := l_Msg_Data;
3278 WHILE l_msg_count > 0 LOOP
3279 l_Error_Message := FND_MSG_PUB.GET(l_msg_index, FND_API.G_FALSE);
3280 l_msg_index := l_msg_index + 1;
3281 l_Msg_Count := l_Msg_Count - 1;
3282 END LOOP;
3283 --RAISE g_exc_error;
3284 UPDATE csi_ii_relation_interface
3285 SET process_status ='E'
3286 ,error_text = l_Error_Message
3287 WHERE rel_interface_id = irel_det_rec.rel_interface_id;
3288 l_rel_failure_count := l_rel_failure_count +1;
3289 ELSE
3290 UPDATE csi_ii_relation_interface
3291 SET process_status ='P'
3292 WHERE rel_interface_id = irel_det_rec.rel_interface_id;
3293 l_rel_success_count := l_rel_success_count + 1;
3294 END IF;
3295 ELSE
3296 u_relationship_tbl.DELETE;
3297 u_relationship_tbl(1).relationship_id :=l_rel_rec.relationship_id;
3298 u_relationship_tbl(1).subject_id :=irel_det_rec.subject_id;
3299 u_relationship_tbl(1).object_id := irel_det_rec.object_id;
3300 u_relationship_tbl(1).object_version_number := l_rel_rec.object_version_number;
3301 u_relationship_tbl(1).relationship_type_code := irel_det_rec.relationship_type_code;
3302
3303 IF NOT l_rel_rec.active_start_date= irel_det_rec.active_start_date
3304 THEN
3305 u_relationship_tbl(1).active_start_date:=irel_det_rec.active_start_date;
3306 END IF;
3307 IF NOT l_rel_rec.active_end_date= irel_det_rec.active_end_date
3308 THEN
3309 u_relationship_tbl(1).active_end_date:=irel_det_rec.active_end_date;
3310 END IF;
3311 IF NOT l_rel_rec.position_reference= irel_det_rec.position_reference
3312 THEN
3313 u_relationship_tbl(1).position_reference:= irel_det_rec.position_reference;
3314 END IF;
3315 IF NOT l_rel_rec.display_order= irel_det_rec.display_order
3316 THEN
3317 u_relationship_tbl(1).display_order:= irel_det_rec.display_order;
3318 END IF;
3319 IF NOT l_rel_rec.mandatory_flag= irel_det_rec.mandatory_flag
3320 THEN
3321 u_relationship_tbl(1).mandatory_flag:= irel_det_rec.mandatory_flag;
3322 END IF;
3323 IF NOT l_rel_rec.context = irel_det_rec.context
3324 THEN
3325 u_relationship_tbl(1).context:= irel_det_rec.context;
3326 END IF;
3327
3328 IF NOT l_rel_rec.attribute1 = irel_det_rec.attribute1
3329 THEN
3330 u_relationship_tbl(1).attribute1 := irel_det_rec.attribute1;
3331 END IF;
3332 IF NOT l_rel_rec.attribute2 = irel_det_rec.attribute2
3333 THEN
3334 u_relationship_tbl(1).attribute2 := irel_det_rec.attribute2;
3335 END IF;
3336 IF NOT l_rel_rec.attribute3 = irel_det_rec.attribute3
3337 THEN
3338 u_relationship_tbl(1).attribute3 := irel_det_rec.attribute3;
3339 END IF;
3340 IF NOT l_rel_rec.attribute4 = irel_det_rec.attribute4
3341 THEN
3342 u_relationship_tbl(1).attribute4 := irel_det_rec.attribute4;
3343 END IF;
3344 IF NOT l_rel_rec.attribute5 = irel_det_rec.attribute5
3345 THEN
3346 u_relationship_tbl(1).attribute5 := irel_det_rec.attribute5;
3347 END IF;
3348 IF NOT l_rel_rec.attribute6 = irel_det_rec.attribute6
3349 THEN
3350 u_relationship_tbl(1).attribute6 := irel_det_rec.attribute6;
3351 END IF;
3352 IF NOT l_rel_rec.attribute7 = irel_det_rec.attribute7
3353 THEN
3354 u_relationship_tbl(1).attribute7 := irel_det_rec.attribute7;
3355 END IF;
3356 IF NOT l_rel_rec.attribute8 = irel_det_rec.attribute8
3357 THEN
3358 u_relationship_tbl(1).attribute8 := irel_det_rec.attribute8;
3359 END IF;
3360 IF NOT l_rel_rec.attribute9 = irel_det_rec.attribute9
3361 THEN
3362 u_relationship_tbl(1).attribute9 := irel_det_rec.attribute9;
3363 END IF;
3364 IF NOT l_rel_rec.attribute10 = irel_det_rec.attribute10
3365 THEN
3366 u_relationship_tbl(1).attribute10 := irel_det_rec.attribute10;
3367 END IF;
3368 IF NOT l_rel_rec.attribute11 = irel_det_rec.attribute11
3369 THEN
3370 u_relationship_tbl(1).attribute11 := irel_det_rec.attribute11;
3371 END IF;
3372 IF NOT l_rel_rec.attribute12 = irel_det_rec.attribute12
3373 THEN
3374 u_relationship_tbl(1).attribute12 := irel_det_rec.attribute12;
3375 END IF;
3376 IF NOT l_rel_rec.attribute13 = irel_det_rec.attribute13
3377 THEN
3378 u_relationship_tbl(1).attribute13 := irel_det_rec.attribute13;
3379 END IF;
3380 IF NOT l_rel_rec.attribute14 = irel_det_rec.attribute14
3381 THEN
3382 u_relationship_tbl(1).attribute14 := irel_det_rec.attribute14;
3383 END IF;
3384 IF NOT l_rel_rec.attribute15 = irel_det_rec.attribute15
3385 THEN
3386 u_relationship_tbl(1).attribute15 := irel_det_rec.attribute15;
3387 END IF;
3388 u_rel_txn_rec.transaction_date :=sysdate; -- irel_det_rec.source_transaction_date;
3389 u_rel_txn_rec.source_transaction_date := sysdate; --irel_det_rec.source_transaction_date;
3390 u_rel_txn_rec.transaction_type_id:= l_txn_type_id;
3391 --u_rel_txn_rec.source_group_ref:= irel_det_rec.transaction_identifier;
3392 u_rel_txn_rec.transaction_quantity:= NULL;
3393 u_rel_txn_rec.transaction_uom_code:= NULL;
3394 u_rel_txn_rec.transacted_by := -1; --irel_det_rec.created_by;
3395 u_rel_txn_rec.transaction_status_code := 'COMPLETE';
3396 u_rel_txn_rec.transaction_action_code := NULL;
3397 u_rel_txn_rec.object_version_number := 1;
3398
3399 IF(l_debug_level>1) THEN
3400 FND_File.Put_Line(Fnd_File.LOG,'updating relationships :');
3401 END IF;
3402 csi_ii_relationships_pvt.update_relationship
3403 ( p_api_version => l_api_version
3404 ,p_commit => l_commit
3405 ,p_init_msg_list => l_init_msg_list
3406 ,p_validation_level => l_validation_level
3407 ,p_relationship_tbl => u_relationship_tbl
3408 ,p_txn_rec => u_rel_txn_rec
3409 ,x_return_status => l_return_status
3410 ,x_msg_count => l_msg_count
3411 ,x_msg_data => l_msg_data
3412 );
3413
3414
3415 IF NOT l_return_status = l_fnd_success
3416 THEN
3417 l_msg_index := 1;
3418 l_Error_Message := l_Msg_Data;
3419 WHILE l_msg_count > 0 LOOP
3420 l_Error_Message := FND_MSG_PUB.GET(l_msg_index, FND_API.G_FALSE);
3421 l_msg_index := l_msg_index + 1;
3422 l_Msg_Count := l_Msg_Count - 1;
3423 END LOOP;
3424 --RAISE g_exc_error;
3425 UPDATE csi_ii_relation_interface
3426 SET process_status ='E'
3427 ,error_text = l_Error_Message
3428 WHERE rel_interface_id = irel_det_rec.rel_interface_id;
3429 l_rel_failure_count := l_rel_failure_count +1;
3430 ELSE
3431 UPDATE csi_ii_relation_interface
3432 SET process_status ='P'
3433 WHERE rel_interface_id = irel_det_rec.rel_interface_id;
3434 l_rel_success_count := l_rel_success_count + 1;
3435 END IF;
3436 END IF;
3437 IF mod(l_counter,1000)=0
3438 THEN
3439 COMMIT;
3440 END IF;
3441 END LOOP;
3442 IF(l_debug_level>1) THEN
3443 FND_File.Put_Line(Fnd_File.LOG,'End for creating rel in single thread :'||to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
3444 FND_File.Put_Line(Fnd_File.LOG,'No of relation transactions processed :'||l_found);
3445 FND_File.Put_Line(Fnd_File.LOG,'No of successful relation transactions :'||l_rel_success_count);
3446 FND_File.Put_Line(Fnd_File.LOG,'No of failed relation transactions :'||l_rel_failure_count);
3447 END IF;
3448 END IF;
3449 EXCEPTION
3450 WHEN g_exc_error THEN
3451 ROLLBACK TO create_update_relship;
3452 END;
3453 COMMIT;
3454
3455
3456 -- End addition for relationships
3457
3458
3459 EXCEPTION
3460 WHEN g_exc_error THEN
3461 x_return_status := l_return_status;
3462 x_error_message := l_error_message;
3463 WHEN others THEN
3464 fnd_message.set_name('CSI','CSI_ML_UNEXP_SQL_ERROR');
3465 fnd_message.set_token('API_NAME',l_api_name);
3466 fnd_message.set_token('SQL_ERROR',SQLERRM);
3467 x_error_message := fnd_message.get;
3468 x_return_status := l_fnd_unexpected;
3469
3470 END process_iface_txns;
3471
3472 END CSI_ML_INTERFACE_TXN_PVT;