[Home] [Help]
PACKAGE BODY: APPS.CSI_MASS_EDIT_PUB
Source
1 PACKAGE BODY CSI_MASS_EDIT_PUB as
2 /* $Header: csipmeeb.pls 120.17.12010000.2 2008/11/06 20:25:42 mashah ship $ */
3 -- Start of Comments
4 -- Package name : CSI_MASS_EDIT_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSI_MASS_EDIT_PUB';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csipmeeb.pls';
12
13 /* local routine to wrap the gen utility debug stuff */
14
15 PROCEDURE debug(
16 p_message IN varchar2)
17 IS
18 BEGIN
19 FND_FILE.PUT_LINE (FND_FILE.LOG, p_message );
20 csi_t_gen_utility_pvt.add(p_message);
21 END debug;
22
23 PROCEDURE debug_out(
24 p_message IN varchar2)
25 IS
26 BEGIN
27 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, p_message );
28 END debug_out;
29
30
31
32 PROCEDURE conc_report (
33 p_temp_merror_tbl IN CSI_MASS_EDIT_PUB.Mass_Upd_Rep_Error_Tbl,
34 p_name IN VARCHAR2,
35 p_batch_id IN NUMBER,
36 p_batch_name IN VARCHAR2
37 ) IS
38 l_temp_merror_tbl csi_mass_edit_pub.Mass_Upd_Rep_Error_Tbl;
39 l_repo_merror_tbl csi_mass_edit_pub.Mass_Upd_Rep_Error_Tbl;
40 l_rep_instance_id NUMBER;
41 l_rep_txn_line_id NUMBER;
42
43 BEGIN
44 -- Sorting the error table based on the Instance_Id
45 l_temp_merror_tbl.delete;
46 l_temp_merror_tbl := p_temp_merror_tbl;
47 For i in l_temp_merror_tbl.FIRST .. l_temp_merror_tbl.LAST
48 LOOP
49 If l_temp_merror_tbl.exists(i)
50 Then
51 l_repo_merror_tbl(l_repo_merror_tbl.count + 1) := l_temp_merror_tbl(i);
52 l_rep_instance_id := l_temp_merror_tbl(i).Instance_Id;
53 -- l_rep_txn_line_id := l_temp_merror_tbl(i).txn_line_detail_id;
54 l_temp_merror_tbl.DELETE(i);
55 If l_temp_merror_tbl.count > 0
56 Then
57 For j in l_temp_merror_tbl.FIRST .. l_temp_merror_tbl.LAST
58 Loop
59 If l_temp_merror_tbl.EXISTS(j)
60 Then
61 If l_temp_merror_tbl(j).INSTANCE_ID = l_rep_instance_id
62 -- AND
63 -- l_temp_merror_tbl(j).txn_line_detail_id = l_rep_txn_line_id
64 Then
65 l_repo_merror_tbl(l_repo_merror_tbl.count + 1) := l_temp_merror_tbl(j);
66 l_temp_merror_tbl.DELETE(j);
67 End If;
68 End If;
69 End Loop;
70 End If;
71 End If;
72 End Loop;
73
74 If l_repo_merror_tbl.count > 0
75 Then
76 /*
77 -- Log File
78 debug(' ');
79 debug('************************ Begin Report for Batch ('||P_BATCH_NAME||') ************************');
80 debug(' ');
81 debug('Instance Error Code Error Message');
82 debug('-------- ---------- -------------');
83 For i in l_repo_merror_tbl.FIRST .. l_repo_merror_tbl.LAST
84 LOOP
85 debug(rpad(to_char(l_repo_merror_tbl(i).instance_id),10,' ')||rpad(l_repo_merror_tbl(i).error_code,12,' ')||substr(l_repo_merror_tbl(i).error_message,1,length(l_repo_merror_tbl(i).error_message)));
86 ENd Loop;
87 debug(' ');
88 debug('************************ End Of Report for Batch ('||P_BATCH_NAME||') ************************');
89 debug(' ');
90 */
91 Debug(' ');
92 Debug('Mass Edit Batch Status : FAILED');
93 Debug(' ');
94
95 -- Out File
96 Debug_out(' ');
97 Debug_out('Mass Edit Batch Status : FAILED');
98 Debug_out(' ');
99 debug_out(' ');
100 debug_out('************************ Begin Report for Batch ('||P_BATCH_NAME||') ************************');
101 debug_out(' ');
102 Debug_out('Instance/s in this batch completed with the following error.');
103 For i in l_repo_merror_tbl.FIRST .. l_repo_merror_tbl.FIRST
104 LOOP
105 Debug_out('Error message: '||substr(l_repo_merror_tbl(i).error_message,1,length(l_repo_merror_tbl(i).error_message)));
106 end loop;
107 Debug_out(' ');
108 /* -- Commented for bug 5167944
109 debug_out('Instance Error Code Error Message');
110 debug_out('-------- ---------- -------------');
111 For i in l_repo_merror_tbl.FIRST .. l_repo_merror_tbl.LAST
112 LOOP
113 debug_out(rpad(to_char(l_repo_merror_tbl(i).instance_id),10,' ')||rpad(l_repo_merror_tbl(i).error_code,12,' ')||substr(l_repo_merror_tbl(i).error_message,1,length(l_repo_merror_tbl(i).error_message)));
114 ENd Loop;
115 debug_out(' ');
116 */
117 debug_out('************************ End Of Report for Batch ('||P_BATCH_NAME||') ************************');
118 debug_out(' ');
119
120 End If;
121
122 End conc_report;
123
124
125 PROCEDURE update_error_status (
126 p_error_tbl IN csi_mass_edit_pub.Mass_Upd_Rep_Error_Tbl,
127 p_txn_line_id IN NUMBER,
128 p_entry_id IN NUMBER
129 ) is
130 l_instance_id NUMBER;
131 l_error_flag VARCHAR2(1) := 'N';
132 PRAGMA AUTONOMOUS_TRANSACTION;
133 Begin
134
135 -- Updating Batch status
136 UPDATE csi_mass_edit_entries_b
137 SET status_code = 'FAILED'
138 WHERE entry_id = p_entry_id;
139
140 -- Updating status for each failed transaction_line_detail_id with instance
141 FOR j in p_error_tbl.FIRST .. p_error_tbl.LAST
142 LOOP
143 -- UPDATE csi_t_txn_line_details
144 -- SET error_explanation = p_grp_error_tbl(j).Error_Message,
145 -- processing_status = 'ERROR',
146 -- error_code = 'E'
147 -- WHERE transaction_line_id = p_txn_line_id
148 -- AND instance_id = p_grp_error_tbl(j).Instance_ID;
149 If l_instance_id is null
150 Then
151 l_instance_id := p_error_tbl(j).instance_id;
152 End If;
153
154 If l_instance_id = p_error_tbl(j).instance_id
155 Then
156 If p_error_tbl(j).ERROR_CODE in ('E','U')
157 Then
158 l_error_flag := 'E';
159 ElsIf p_error_tbl(j).ERROR_CODE = 'W'
160 AND
161 l_error_flag <> 'E'
162 Then
163 l_error_flag := 'W';
164 End If;
165 End If;
166
167 If l_instance_id <> p_error_tbl(j).instance_id
168 Then
169 If l_error_flag = 'E'
170 Then
171 UPDATE csi_t_txn_line_details
172 SET processing_status = 'ERROR',
173 ERROR_CODE = 'E'
174 WHERE transaction_line_id = p_txn_line_id
175 AND instance_id = l_instance_id;
176 Else
177 UPDATE csi_t_txn_line_details
178 SET processing_status = 'WARNING',
179 ERROR_CODE = 'W'
180 WHERE transaction_line_id = p_txn_line_id
181 AND instance_id = l_instance_id;
182 End If;
183 l_error_flag := 'N';
184 If p_error_tbl(j).ERROR_CODE in ('E','U')
185 Then
186 l_error_flag := 'E';
187 ElsIf p_error_tbl(j).ERROR_CODE = 'W'
188 Then
189 l_error_flag := 'W';
190 End If;
191 l_instance_id := p_error_tbl(j).instance_id;
192 End If;
193 END LOOP;
194
195 Commit;
196
197 End update_error_status;
198
199 PROCEDURE UPDATE_MUSYS_ERR_STATUS (
200 p_entry_id IN NUMBER
201 ) IS
202 PRAGMA AUTONOMOUS_TRANSACTION;
203 BEGIN
204
205 -- Updating Batch status
206 UPDATE csi_mass_edit_entries_b
207 SET status_code = 'FAILED'
208 WHERE entry_id = p_entry_id;
209
210 COMMIT;
211
212 END UPDATE_MUSYS_ERR_STATUS;
213
214 PROCEDURE validate_loc_pty
215 ( p_instance_id IN NUMBER ,
216 p_txn_line_id IN NUMBER ,
217 x_return_status OUT NOCOPY VARCHAR2
218 ) IS
219
220 CURSOR core_csr (p_instance_id IN NUMBER) IS
221 SELECT cii.location_id location_id
222 ,cii.location_type_code location_type_code
223 ,cii.install_location_id install_location_id
224 ,cii.install_location_type_code install_location_type_code
225 ,cip.party_id party_id
226 ,cip.party_source_table party_source_table
227 FROM csi_item_instances cii
228 ,csi_i_parties cip
229 WHERE cii.instance_id = p_instance_id
230 AND cip.instance_id = cii.instance_id
231 AND cip.relationship_type_code = 'OWNER';
232
233 CURSOR source_csr (p_txn_line_id IN NUMBER,p_instance_id IN NUMBER) IS
234 SELECT cil.location_id location_id
235 ,cil.location_type_code location_type_code
236 ,cil.install_location_id install_location_id
237 ,cil.install_location_type_code install_location_type_code
238 ,cid.party_source_id party_id
239 ,cid.party_source_table party_source_table
240 FROM csi_t_txn_line_details cil
241 ,csi_t_party_details cid
242 WHERE cil.transaction_line_id = p_txn_line_id
243 AND cil.instance_id = p_instance_id
244 AND cid.txn_line_detail_id = cil.txn_line_detail_id
245 AND cid.relationship_type_code = 'OWNER';
246
247 l_core_csr core_csr%ROWTYPE;
248 l_source_csr source_csr%ROWTYPE;
249
250 BEGIN
251
252 OPEN core_csr (p_instance_id);
253 FETCH core_csr INTO l_core_csr;
254 CLOSE core_csr;
255
256 OPEN source_csr (p_txn_line_id,p_instance_id);
257 FETCH source_csr INTO l_source_csr;
258 CLOSE source_csr;
259
260 IF NVL(l_core_csr.location_id,-1) <> NVL(l_source_csr.location_id,-1)
261 OR NVL(l_core_csr.location_type_code,'#@*') <> NVL(l_source_csr.location_type_code,'#@*')
262 OR NVL(l_core_csr.install_location_id,-1) <> NVL(l_source_csr.install_location_id,-1) --third addition
263 OR NVL(l_core_csr.install_location_type_code,'#@*') <> NVL(l_source_csr.install_location_type_code,'#@*')
264 OR l_core_csr.party_id <> l_source_csr.party_id
265 OR l_core_csr.party_source_table <> l_source_csr.party_source_table
266 THEN
267 x_return_status:='F';
268 END IF;
269 x_return_status:='T';
270 EXCEPTION
271 WHEN OTHERS THEN
272 x_return_status:='F';
273 END;
274
275 /* ---------------------------------------------------------------- */
276 -- Procedure Initiate_Mass_Edit is registered as concurrent program
277 -- This program fires periodically at the specified time interval
278 -- and kicks of the Process_mass_edit_batch(Another concurrent process)
279 -- whenever it finds records with status SCHEDULED.
280 /* ---------------------------------------------------------------- */
281 PROCEDURE Initiate_Mass_Edit
282 (
283 errbuf OUT NOCOPY VARCHAR2
284 ,retcode OUT NOCOPY NUMBER
285 ,p_entry_id IN NUMBER
286 )
287 IS
288 l_api_name CONSTANT VARCHAR2(30) := 'Initiate_Mass_Edit';
289 l_api_version CONSTANT NUMBER := 1.0;
290 l_return_status_full VARCHAR2(1);
291 l_access_flag VARCHAR2(1);
292 l_line_count NUMBER;
293 l_debug_level NUMBER;
294 l_entry_id NUMBER := 0;
295 l_request_id NUMBER ;
296 l_errbuf VARCHAR2(2000);
297 l_mass_edit_tbl csi_mass_edit_pub.mass_edit_tbl;
298 i NUMBER;
299 --l_txn_rec csi_datastructures_pub.transaction_rec;
300 l_count NUMBER;
301 l_warning NUMBER := 0;
302 l_error NUMBER := 0;
303 l_success NUMBER := 0;
304 l_temp BOOLEAN;
305
306 CURSOR mass_edit_csr(p_entry_id in NUMBER) IS
307 SELECT entry_id
308 FROM csi_mass_edit_entries_vl
309 WHERE status_code = 'SCHEDULED'
310 AND schedule_date <= SYSDATE
311 AND entry_id = nvl(p_entry_id,entry_id);
312
313 BEGIN
314
315 debug(' ');
316 debug( 'Initiate Mass Edit Concurrent Process');
317 debug(' ');
318
319 debug_out(' ');
320 debug_out( 'Initiate Mass Edit Concurrent Program');
321 debug_out(' ');
322
323 BEGIN
324 i:=0;
325 FOR l_mass_edit_csr IN mass_edit_csr(p_entry_id)
326 LOOP
327 i:=i+1;
328 l_mass_edit_tbl(i).entry_id := l_mass_edit_csr.entry_id;
329 END LOOP;
330 EXCEPTION
331 WHEN NO_DATA_FOUND THEN
332 NULL;
333 END;
334 l_count:=l_mass_edit_tbl.count;
335
336
337 IF l_count > 0
338 THEN
339 FOR call_csr IN 1..l_count
340 LOOP
341 Process_mass_edit_batch(errbuf,retcode,l_mass_edit_tbl(call_csr).entry_id);
342 /*
343 l_request_id := FND_REQUEST.SUBMIT_REQUEST('CSI', 'CSIMEDT', 'Mass Edit Concurrent program ' , NULL, FALSE, l_mass_edit_tbl(call_csr).entry_id);--, l_txn_rec);
344
345 debug('Calling Process Mass Edit Batch Concurrent Process');
346 debug('');
347 debug('Submitting with Parameters');
348 debug('Batch Id : '|| l_mass_edit_tbl(call_csr).entry_id);
349 debug('');
350 IF (l_request_id = 0) THEN
351 l_errbuf := FND_MESSAGE.GET;
352 debug('Call to Process Mass Edit Batch Concurrent Process has errored');
353 debug('For Batch Id'||l_mass_edit_tbl(call_csr).entry_id);
354 debug('Error message :'||substr(l_errbuf,1,75));
355 debug(' :'||substr(l_errbuf,76,150));
356 debug(' :'||substr(l_errbuf,151,225));
357 debug(' :'||substr(l_errbuf,226,300));
358 END IF;
359 */
360 If errbuf = 'W'
361 Then
362 l_warning := l_warning + 1;
363 Elsif errbuf = 'E'
364 Then
365 l_error := l_error + 1;
366 Else
367 l_success := l_success + 1;
368 End If;
369 END LOOP;
370
371 If l_error > 0
372 Then
373 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
374 ElsIf l_warning > 0
375 Then
376 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING','');
377 End If;
378
379 ELSE
380 debug(' ');
381 debug('Unable to find any records to call Process Mass Edit Batch Concurrent Process ');
382 debug(' ');
383 END IF;
384
385 --
386 -- End of API body
387 --
388
389 EXCEPTION
390 WHEN OTHERS THEN
391 debug('When others exception from Initiate_Mass_Edit');
392 END Initiate_Mass_Edit;
393
394 /* ------------------------------------------------------------------- */
395 -- Procedure Process_mass_edit_batch is registered as concurrent program
396 -- This program gets initiated by initiate_mass_edit concurren program.
397 /* ------------------------------------------------------------------- */
398 PROCEDURE Process_mass_edit_batch
399 ( errbuf OUT NOCOPY VARCHAR2
400 ,retcode OUT NOCOPY NUMBER
401 ,p_Entry_id IN NUMBER
402 ) IS
403
404 -- Cursors
405 CURSOR selected_instance_csr (p_txn_line_id IN NUMBER) IS
406 SELECT ctld.instance_id
407 ,ctld.transaction_line_id
408 ,nvl(ctld.location_id, FND_API.G_MISS_NUM) location_id
409 ,nvl(ctld.location_type_code, FND_API.G_MISS_CHAR) location_type_code
410 ,nvl(ctld.install_location_id, FND_API.G_MISS_NUM) install_location_id
411 ,nvl(ctld.install_location_type_code, FND_API.G_MISS_CHAR) install_location_type_code
412 ,cii.object_version_number
413 ,cii.instance_usage_code
414 FROM csi_t_txn_line_details ctld,
415 csi_item_instances cii
416 WHERE ctld.transaction_line_id = p_txn_line_id
417 AND ctld.INSTANCE_ID is not null
418 AND ctld.preserve_detail_flag = 'Y'
419 AND cii.instance_id = ctld.instance_id;
420
421 CURSOR core_inst_pty_acct_csr (p_txn_line_id IN NUMBER) IS
422 SELECT cip.instance_party_id pty_instance_party_id,
423 cip.instance_id pty_instance_id,
424 cip.party_source_table pty_party_source_table,
425 cip.party_id pty_party_id,
426 cip.relationship_type_code pty_rel_type_code,
427 cip.object_version_number pty_obj_version_number
428 ,cia.ip_account_id pty_acc_ip_account_id,
429 cia.instance_party_id pty_acc_instance_party_id,
430 cia.party_account_id pty_acc_party_account_id,
431 cia.relationship_type_code pty_acct_rel_type_code,
432 cia.object_version_number pty_acct_obj_version_number
433 FROM csi_i_parties cip,
434 csi_ip_accounts cia,
435 csi_t_txn_line_details ctld
436 WHERE cip.instance_id = ctld.instance_id
437 AND cip.contact_flag ='N'
438 AND cip.relationship_type_code = 'OWNER'
439 AND ctld.transaction_line_id = p_txn_line_id
440 AND ctld.instance_exists_flag = 'Y'
441 AND ctld.preserve_detail_flag = 'Y'
442 AND cia.instance_party_id = cip.instance_party_id
443 AND cia.relationship_type_code = 'OWNER';
444
445 CURSOR curr_asso_csr (p_instance_id IN NUMBER) IS
446 SELECT cip.instance_party_id pty_instance_party_id,
447 cip.instance_id pty_instance_id,
448 cip.party_source_table pty_party_source_table,
449 cip.party_id pty_party_id,
450 cip.relationship_type_code pty_rel_type_code,
451 cip.object_version_number pty_obj_version_number
452 FROM csi_i_parties cip
453 WHERE cip.instance_id = p_instance_id
454 AND cip.contact_flag = 'N'
455 AND nvl(cip.active_end_date,sysdate) >= sysdate;
456
457 CURSOR core_iea_val_csr (p_txn_line_id IN NUMBER) IS
458 SELECT ciev.attribute_value_id
459 ,ciev.attribute_id
460 ,ciev.instance_id
461 ,ciev.attribute_value
462 ,ciev.object_version_number
463 FROM csi_iea_values ciev,
464 csi_t_txn_line_details ctld
465 WHERE ciev.instance_id = ctld.instance_id
466 AND ctld.transaction_line_id = p_txn_line_id
467 AND ctld.instance_id is not null;
468 -- Local Variables
469 l_txn_rec csi_datastructures_pub.transaction_rec;
470
471 -- Variables for Dummy record details
472 d_txn_line_query_rec csi_t_datastructures_grp.txn_line_query_rec;
473 d_txn_line_detail_query_rec csi_t_datastructures_grp.txn_line_detail_query_rec;
474 d_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
475 d_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
476 d_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
477 d_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
478 d_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
479 d_ext_attrib_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
480 d_csi_ea_tbl csi_t_datastructures_grp.csi_ext_attribs_tbl;
481 d_csi_eav_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl;
482 d_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
483 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
484 l_msg_count NUMBER;
485 l_msg_data VARCHAR2(2000);
486
487 -- New Tables for the selected instances in the batch
488 n_instance_tbl csi_datastructures_pub.instance_tbl;
489 n_party_tbl csi_datastructures_pub.party_tbl;
490 n_party_account_tbl csi_datastructures_pub.party_account_tbl;
491 n_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
492 n_price_tbl csi_datastructures_pub.pricing_attribs_tbl;
493 n_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
494 n_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
495 n_txn_rec csi_datastructures_pub.transaction_rec;
496 n_rel_txn_rec csi_datastructures_pub.transaction_rec;
497 n_grp_error_tbl csi_datastructures_pub.grp_upd_error_tbl;
498 n_instance_id_lst csi_datastructures_pub.id_tbl;
499
500 l_api_version NUMBER:=1.0;
501 l_commit VARCHAR2(1) ;
502 l_msg_index NUMBER;
503 l_error_message VARCHAR2(2000);
504 l_warning_message VARCHAR2(2000);
505 l_errbuf VARCHAR2(2000);
506 n_inst_ind NUMBER := 0;
507 l_parent_tbl_index NUMBER := 0;
508 l_pty_index NUMBER := 0;
509 l_acct_index NUMBER := 0;
510 l_cont_index number := 0;
511 l_ext_attr_ind NUMBER := 0;
512
513 -- NEW OWNER Party variables
514 l_pty_src_table VARCHAR2(80);
515 l_pty_party_id NUMBER;
516 l_pty_rel_typ VARCHAR2(80);
517 -- Assoc to expire
518 l_assoc_src_table VARCHAR2(80);
519 l_assoc_party_id NUMBER;
520 l_assoc_rel_typ VARCHAR2(80);
521 l_assoc_end_date DATE;
522 -- NEW OWNER Account variables
523 l_pry_account_id NUMBER;
524 l_acct_rel_typ VARCHAR2(80);
525 l_acct_bill_to NUMBER;
526 l_acct_ship_to NUMBER;
527 l_party_detail_id NUMBER;
528 l_pty_tbl_ind NUMBER := 0;
529
530
531 l_debug_level NUMBER;
532 l_status VARCHAR2(1) :='N';
533 l_txn_line_id NUMBER;
534 l_instance_id NUMBER;
535 l_batch_type VARCHAR2(30);
536 l_found_batch VARCHAR2(30);
537 l_conc_status BOOLEAN;
538
539 l_medit_error_tbl csi_mass_edit_pub.mass_edit_error_tbl;
540 l_temp_merror_tbl csi_mass_edit_pub.Mass_Upd_Rep_Error_Tbl;
541 l_repo_merror_tbl csi_mass_edit_pub.Mass_Upd_Rep_Error_Tbl;
542 l_rep_instance_id NUMBER;
543 l_rep_txn_line_id NUMBER;
544 l_found_error VARCHAR2(1) := 'N';
545
546 px_medit_rec csi_mass_edit_pub.mass_edit_rec;
547
548 l_note_id NUMBER;
549 l_batch_desc VARCHAR2(2000);
550 l_name VARCHAR2(50);
551 l_batch_meaning VARCHAR2(50);
552 l_batch_sch_date DATE;
553 l_jtf_error VARCHAR2(1) := 'N';
554 l_jtf_err_ind NUMBER := 0;
555 l_grp_api_ind NUMBER := 0;
556
557 l_system_cascade VARCHAR2(1) := 'N'; -- ER 6031179
558 l_perform_system_mu VARCHAR2(1) := 'N';
559 l_new_owner VARCHAR2(1);
560 l_old_owner VARCHAR2(1);
561
562
563 BEGIN
564 l_debug_level:=fnd_profile.value('DEBUG_LEVEL');
565
566 debug(' Processing .... ');
567 debug(' ');
568
569 -- Checking for Batch Status
570 BEGIN
571 SELECT a.txn_line_id,
572 a.batch_type,
573 b.meaning,
574 a.description,
575 a.name,
576 a.schedule_date,
577 a.system_cascade
578 INTO l_txn_line_id,
579 l_batch_type,
580 l_batch_meaning,
581 l_batch_desc,
582 l_name,
583 l_batch_sch_date,
584 l_system_cascade
585 FROM csi_mass_edit_entries_vl a,
586 csi_lookups b
587 WHERE a.entry_id = p_entry_id
588 AND a.status_code = 'SCHEDULED'
589 AND a.schedule_date <= SYSDATE
590 AND b.lookup_type = 'CSI_IB_TXN_TYPE_CODE'
591 AND b.lookup_code = a.batch_type;
592
593 l_status := 'Y';
594 debug('Found data for mass edit processing... ');
595 debug(' ');
596
597 EXCEPTION
598 WHEN NO_DATA_FOUND THEN
599 debug('No data for processing Mass Edit... ');
600 debug('Aborting Concurrent Program');
601 debug(' ');
602 errbuf := 'E';
603 WHEN OTHERS THEN
604 debug('When others exception from Mass Edit Entry processing query..'||to_char(sqlcode)||'-'||substr(sqlerrm, 1, 255));
605 raise fnd_api.g_exc_error;
606 END;
607
608 debug(' Processing Mass Edit Batch :'||l_name);
609 debug(' Batch Id : '||p_entry_id);
610 debug(' Batch Type : '||l_batch_meaning);
611 debug(' ');
612
613 debug_out(' ');
614 debug_out(' ');
615 debug_out('Processing Mass Edit Batch : '||l_name);
616 debug_out(' Batch Id : '||p_entry_id);
617 debug_out(' Batch Type : '||l_batch_meaning);
618 debug_out(' ');
619
620
621 -- Building Transaction rec
622 n_txn_rec.transaction_type_id := 3;
623 --n_txn_rec.source_transaction_date := SYSDATE;
624 n_txn_rec.source_transaction_date := l_batch_sch_date;
625 n_txn_rec.transaction_date := SYSDATE;
626 n_txn_rec.SOURCE_HEADER_REF_ID := p_entry_id;
627 n_txn_rec.SOURCE_GROUP_REF := l_batch_type;
628
629 -- Batch Type Validation
630 BEGIN
631 SELECT ib_txn_type_code
632 INTO l_found_batch
633 FROM csi_txn_sub_types
634 WHERE transaction_type_id = 3
635 AND ib_txn_type_code = l_batch_type;
636
637 EXCEPTION
638 WHEN NO_DATA_FOUND THEN
639 debug('Batch Type is not set for processing Mass Edit... ');
640 debug('Aborting Concurrent Program');
641 errbuf := 'E';
642 debug(' ');
643 WHEN OTHERS THEN
644 debug('When others exception from Mass Edit Entry processing query..'||to_char(sqlcode)||'-'||substr(sqlerrm, 1, 255));
645 raise fnd_api.g_exc_error;
646 END;
647
648 IF l_found_batch is not null
649 THEN
650 l_status := 'Y';
651 ELSE
652 l_status := 'N';
653 END IF;
654
655 IF l_status = 'Y'
656 THEN
657
658 -- Setting the Batch Status to 'Processing'
659 UPDATE csi_mass_edit_entries_b
660 SET status_code = 'PROCESSING',
661 request_id = FND_GLOBAL.CONC_REQUEST_ID
662 WHERE entry_id = p_entry_id;
663 COMMIT;
664
665 -- Common Validations
666 -- Validating the Current Location Attributes and Owner Party of the
667 -- Instances selecetd for processing with actual table values
668
669 -- px_medit_rec.entry_id := p_entry_id;
670 px_medit_rec.txn_line_id := l_txn_line_id;
671 px_medit_rec.batch_type := l_batch_type;
672 px_medit_rec.name := l_name;
673 -- px_medit_rec.description := l_batch_desc;
674
675 csi_mass_edit_pvt.validate_batch(
676 px_mass_edit_rec => px_medit_rec,
677 p_mode => 'CP',
678 x_mass_edit_error_tbl => l_medit_error_tbl,
679 x_return_status => l_return_status
680 );
681 debug('Return status from validation routine : '|| l_return_status);
682
683 If l_return_status <> fnd_api.g_ret_sts_success
684 Then
685 If l_medit_error_tbl.count > 0
686 Then
687 l_temp_merror_tbl.delete;
688 For e in l_medit_error_tbl.FIRST .. l_medit_error_tbl.LAST
689 Loop
690 l_temp_merror_tbl(e).Entry_Id := l_medit_error_tbl(e).entry_id;
691 l_temp_merror_tbl(e).Txn_Line_Detail_Id := l_medit_error_tbl(e).Txn_Line_Detail_Id;
692 l_temp_merror_tbl(e).Instance_Id := l_medit_error_tbl(e).Instance_Id;
693 l_temp_merror_tbl(e).Error_Message := l_medit_error_tbl(e).Error_Text;
694 l_temp_merror_tbl(e).Error_Code := l_medit_error_tbl(e).Error_Code;
695 l_temp_merror_tbl(e).NAME := l_medit_error_tbl(e).NAME;
696 l_temp_merror_tbl(e).Entity_Name := 'TXNLINE';
697 End Loop;
698 End If;
699 If l_return_status = 'E'
700 Then
701 l_found_error := 'E';
702 -- debug('Call update error status');
703 update_error_status(l_temp_merror_tbl,l_txn_line_id,p_entry_id);
704 -- debug('Call error report');
705 conc_report ( p_temp_merror_tbl => l_temp_merror_tbl,
706 p_name => 'VALERROR',
707 p_batch_id => p_entry_id,
708 p_batch_name => l_name
709 );
710 debug('Aborting Concurrent Program by raising exception');
711 -- l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
712 errbuf := 'E';
713 Else
714 If l_return_status = 'W'
715 Then
716 l_found_error := 'W';
717 errbuf := 'W';
718 End If;
719 l_return_status := 'T';
720 End If;
721 Else
722 l_return_status := 'T';
723 End If;
724
725 BEGIN
726 savepoint START_PROCESSING;
727 IF l_return_status = 'T'
728 THEN
729 --l_total_count:=0;
730 --l_error_count:=0;
731
732 -- Populating the New Data from dummy record
733 d_txn_line_query_rec.TRANSACTION_LINE_ID := l_txn_line_id;
734 d_txn_line_query_rec.SOURCE_TRANSACTION_TABLE := 'CSI_MASS_EDIT_ENTRIES';
735 d_txn_line_query_rec.source_transaction_id := p_entry_id;
736 d_txn_line_detail_query_rec.TRANSACTION_LINE_ID := l_txn_line_id;
737 d_txn_line_detail_query_rec.instance_exists_flag := 'N';
738
739 csi_t_txn_details_grp.get_transaction_details(
740 p_api_version => 1.0,
741 p_commit => fnd_api.g_false,
742 p_init_msg_list => fnd_api.g_true,
743 p_validation_level => fnd_api.g_valid_level_full,
744 p_txn_line_query_rec => d_txn_line_query_rec,
745 p_txn_line_detail_query_rec => d_txn_line_detail_query_rec,
746 x_txn_line_detail_tbl => d_line_dtl_tbl,
747 p_get_parties_flag => fnd_api.g_true,
748 x_txn_party_detail_tbl => d_pty_dtl_tbl,
749 p_get_pty_accts_flag => fnd_api.g_true,
750 x_txn_pty_acct_detail_tbl => d_pty_acct_tbl,
751 p_get_ii_rltns_flag => fnd_api.g_true,
752 x_txn_ii_rltns_tbl => d_ii_rltns_tbl,
753 p_get_org_assgns_flag => fnd_api.g_true,
754 x_txn_org_assgn_tbl => d_org_assgn_tbl,
755 p_get_ext_attrib_vals_flag => fnd_api.g_true,
756 x_txn_ext_attrib_vals_tbl => d_ext_attrib_tbl,
757 p_get_csi_attribs_flag => fnd_api.g_false,
758 x_csi_ext_attribs_tbl => d_csi_ea_tbl,
759 p_get_csi_iea_values_flag => fnd_api.g_false,
760 x_csi_iea_values_tbl => d_csi_eav_tbl,
761 p_get_txn_systems_flag => fnd_api.g_false,
762 x_txn_systems_tbl => d_txn_systems_tbl,
763 x_return_status => l_return_status,
764 x_msg_count => l_msg_count,
765 x_msg_data => l_msg_data);
766
767 IF l_return_status <> fnd_api.g_ret_sts_success THEN
768 RAISE fnd_api.g_exc_error;
769 END IF;
770
771 debug('After get_transaction_details :');
772 debug(' line_dtl_tbl.count :'||d_line_dtl_tbl.count);
773 debug(' pty_dtl_tbl.count :'||d_pty_dtl_tbl.count);
774 debug(' pty_acct_tbl.count :'||d_pty_acct_tbl.count);
775 debug(' ext_attrib_tbl.count :'||d_ext_attrib_tbl.count);
776
777 debug('Dump Txn Details Information .....');
778
779 csi_t_gen_utility_pvt.dump_txn_tables(
780 p_ids_or_index_based => 'I',
781 p_line_detail_tbl => d_line_dtl_tbl,
782 p_party_detail_tbl => d_pty_dtl_tbl,
783 p_pty_acct_tbl => d_pty_acct_tbl,
784 p_ii_rltns_tbl => d_ii_rltns_tbl,
785 p_org_assgn_tbl => d_org_assgn_tbl,
786 p_ea_vals_tbl => d_ext_attrib_tbl);
787
788 -- Building Instance rec
789 FOR l_instance_csr IN selected_instance_csr(l_txn_line_id)
790 LOOP
791 n_inst_ind := n_inst_ind + 1;
792 n_instance_tbl(n_inst_ind).instance_id := l_instance_csr.instance_id;
793 n_instance_tbl(n_inst_ind).object_version_number := l_instance_csr.object_version_number;
794 -- System
795 If nvl(d_line_dtl_tbl(1).csi_system_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
796 Then
797 n_instance_tbl(n_inst_ind).system_id := d_line_dtl_tbl(1).csi_system_id;
798 End If;
799 -- Instance Status
800 If nvl(d_line_dtl_tbl(1).instance_status_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
801 Then
802 n_instance_tbl(n_inst_ind).instance_status_id := d_line_dtl_tbl(1).instance_status_id;
803 End If;
804 -- External Reference
805 If nvl(d_line_dtl_tbl(1).external_reference,fnd_api.g_miss_char) <> fnd_api.g_miss_char
806 Then
807 n_instance_tbl(n_inst_ind).external_reference := d_line_dtl_tbl(1).external_reference;
808 End If;
809 -- Version Label
810 If nvl(d_line_dtl_tbl(1).version_label,fnd_api.g_miss_char) <> fnd_api.g_miss_char
811 Then
812 n_instance_tbl(n_inst_ind).version_label := d_line_dtl_tbl(1).version_label;
813 End If ;
814 -- Transfer Date
815 IF nvl(d_line_dtl_tbl(1).active_start_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date
816 Then
817 n_instance_tbl(n_inst_ind).active_start_date := d_line_dtl_tbl(1).active_start_date;
818 End If;
819 -- Termination Date
820 IF nvl(d_line_dtl_tbl(1).active_end_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date
821 Then
822 n_instance_tbl(n_inst_ind).active_end_date := d_line_dtl_tbl(1).active_end_date;
823 End If;
824
825 IF nvl(d_line_dtl_tbl(1).active_end_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date THEN
826 debug('Setting Termination Date in instance Rec');
827 n_txn_rec.source_transaction_date := d_line_dtl_tbl(1).active_end_date;
828 End If;
829 debug('Termination Date.........................:'||n_txn_rec.source_transaction_date);
830
831 -- Install Date
832 IF nvl(d_line_dtl_tbl(1).installation_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date
833 Then
834 n_instance_tbl(n_inst_ind).install_date := d_line_dtl_tbl(1).installation_date;
835 End If;
836
837 -- Install and Current Location Changes
838 IF l_batch_type in ('XFER','MOVE')
839 Then
840 IF nvl(l_instance_csr.instance_usage_code,fnd_api.g_miss_char) = 'IN_RELATIONSHIP'
841 Then
842 n_instance_tbl(n_inst_ind).location_id := fnd_api.g_miss_num;
843 n_instance_tbl(n_inst_ind).location_type_code := fnd_api.g_miss_char;
844 n_instance_tbl(n_inst_ind).install_location_id := fnd_api.g_miss_num;
845 n_instance_tbl(n_inst_ind).install_location_type_code := fnd_api.g_miss_char;
846 Else
847 IF nvl(d_line_dtl_tbl(1).location_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
848 Then
849 n_instance_tbl(n_inst_ind).location_id := d_line_dtl_tbl(1).location_id;
850 End If;
851 IF nvl(d_line_dtl_tbl(1).location_type_code,fnd_api.g_miss_char) <> fnd_api.g_miss_char
852 Then
853 n_instance_tbl(n_inst_ind).location_type_code := d_line_dtl_tbl(1).location_type_code;
854 End If;
855 IF nvl(d_line_dtl_tbl(1).install_location_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
856 Then
857 n_instance_tbl(n_inst_ind).install_location_id := d_line_dtl_tbl(1).install_location_id;
858 End If;
859 IF nvl(d_line_dtl_tbl(1).install_location_type_code,fnd_api.g_miss_char) <> fnd_api.g_miss_char
860 Then
861 n_instance_tbl(n_inst_ind).install_location_type_code := d_line_dtl_tbl(1).install_location_type_code;
862 End If;
863 End If;
864 End If;
865 END LOOP;
866
867 -- Identifying New Owner Account details
868 IF d_pty_acct_tbl.count > 0
869 THEN
870 FOR i in d_pty_acct_tbl.FIRST .. d_pty_acct_tbl.LAST
871 LOOP
872 IF d_pty_acct_tbl(i).relationship_type_code = 'OWNER'
873 THEN
874 l_pry_account_id := d_pty_acct_tbl(i).ACCOUNT_ID;
875 l_acct_rel_typ := d_pty_acct_tbl(i).RELATIONSHIP_TYPE_CODE;
876 l_acct_bill_to := d_pty_acct_tbl(i).BILL_TO_ADDRESS_ID;
877 l_acct_ship_to := d_pty_acct_tbl(i).SHIP_TO_ADDRESS_ID;
878 END IF;
879 END LOOP;
880 END IF;
881 l_parent_tbl_index := 0;
882 l_pty_index := 0;
883 l_acct_index := 0;
884 l_cont_index := 0;
885 FOR l_core_inst_pty_det IN core_inst_pty_acct_csr(l_txn_line_id)
886 LOOP
887 -- debug('l_core_inst_pty_det.instance_id '||l_core_inst_pty_det.pty_instance_id);
888 -- Party Table
889 l_parent_tbl_index := l_parent_tbl_index + 1;
890
891 IF d_pty_dtl_tbl.count > 0 THEN
892
893 FOR j in d_pty_dtl_tbl.FIRST .. d_pty_dtl_tbl.LAST
894 LOOP
895 -- Building New owner party
896 IF d_pty_dtl_tbl(j).relationship_type_code = 'OWNER'
897 AND
898 nvl(d_pty_dtl_tbl(j).contact_flag,fnd_api.g_miss_char) = 'N'
899 AND
900 l_batch_type = 'XFER'
901 THEN
902 l_pty_index := l_pty_index + 1;
903 n_party_tbl(l_pty_index).parent_tbl_index := l_parent_tbl_index;
904 n_party_tbl(l_pty_index).instance_party_id := l_core_inst_pty_det.pty_instance_party_id;
905 n_party_tbl(l_pty_index).instance_id := l_core_inst_pty_det.pty_instance_id;
906 n_party_tbl(l_pty_index).party_source_table := d_pty_dtl_tbl(j).PARTY_SOURCE_TABLE;
907 n_party_tbl(l_pty_index).party_id := d_pty_dtl_tbl(j).PARTY_SOURCE_ID;
908 n_party_tbl(l_pty_index).relationship_type_code := d_pty_dtl_tbl(j).RELATIONSHIP_TYPE_CODE;
909 n_party_tbl(l_pty_index).object_version_number := l_core_inst_pty_det.pty_obj_version_number;
910 n_party_tbl(l_pty_index).contact_flag := 'N';
911
912
913 IF nvl(d_pty_dtl_tbl(j).active_start_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date THEN
914 debug('Setting Party Transfer Date in Transaction Rec');
915 n_txn_rec.source_transaction_date := d_pty_dtl_tbl(j).active_start_date;
916 End If;
917 debug('Transfer Date.........................:'||n_txn_rec.source_transaction_date);
918
919 l_party_detail_id := d_pty_dtl_tbl(j).txn_party_detail_id;
920 l_pty_tbl_ind := l_pty_index;
921
922 l_acct_index := l_acct_index + 1;
923 n_party_account_tbl(l_acct_index).ip_account_id := l_core_inst_pty_det.pty_acc_ip_account_id;
924 n_party_account_tbl(l_acct_index).instance_party_id := l_core_inst_pty_det.pty_acc_instance_party_id;
925 n_party_account_tbl(l_acct_index).parent_tbl_index := l_pty_index;
926 n_party_account_tbl(l_acct_index).party_account_id := l_pry_account_id;
927 n_party_account_tbl(l_acct_index).relationship_type_code := l_acct_rel_typ;
928 n_party_account_tbl(l_acct_index).bill_to_address := l_acct_bill_to;
929 n_party_account_tbl(l_acct_index).ship_to_address := l_acct_ship_to;
930 n_party_account_tbl(l_acct_index).object_version_number := l_core_inst_pty_det.pty_acct_obj_version_number;
931 ELSIF d_pty_dtl_tbl(j).relationship_type_code = 'OWNER'
932 AND
933 nvl(d_pty_dtl_tbl(j).contact_flag,fnd_api.g_miss_char) = 'N'
934 AND
935 l_batch_type = 'MOVE'
936 THEN
937 l_pty_index := l_pty_index + 1;
938 n_party_tbl(l_pty_index).parent_tbl_index := l_parent_tbl_index;
939 n_party_tbl(l_pty_index).instance_party_id := l_core_inst_pty_det.pty_instance_party_id;
940 n_party_tbl(l_pty_index).instance_id := l_core_inst_pty_det.pty_instance_id;
941 n_party_tbl(l_pty_index).party_source_table := l_core_inst_pty_det.pty_party_source_table;
942 n_party_tbl(l_pty_index).party_id := l_core_inst_pty_det.pty_party_id;
943 n_party_tbl(l_pty_index).relationship_type_code := l_core_inst_pty_det.pty_rel_type_code;
944 n_party_tbl(l_pty_index).object_version_number := l_core_inst_pty_det.pty_obj_version_number;
945 n_party_tbl(l_pty_index).contact_flag := 'N';
946
947 l_party_detail_id := d_pty_dtl_tbl(j).txn_party_detail_id;
948 l_pty_tbl_ind := l_pty_index;
949
950 l_acct_index := l_acct_index + 1;
951 n_party_account_tbl(l_acct_index).ip_account_id := l_core_inst_pty_det.pty_acc_ip_account_id;
952 n_party_account_tbl(l_acct_index).instance_party_id := l_core_inst_pty_det.pty_acc_instance_party_id;
953 n_party_account_tbl(l_acct_index).parent_tbl_index := l_pty_index;
954 n_party_account_tbl(l_acct_index).party_account_id := l_core_inst_pty_det.pty_acc_party_account_id;
955 n_party_account_tbl(l_acct_index).relationship_type_code := l_core_inst_pty_det.pty_acct_rel_type_code;
956 n_party_account_tbl(l_acct_index).bill_to_address := l_acct_bill_to;
957 n_party_account_tbl(l_acct_index).ship_to_address := l_acct_ship_to;
958 n_party_account_tbl(l_acct_index).object_version_number := l_core_inst_pty_det.pty_acct_obj_version_number;
959
960 -- Building New Party other than owner
961 ELSIF d_pty_dtl_tbl(j).relationship_type_code <> 'OWNER'
962 AND
963 nvl(d_pty_dtl_tbl(j).contact_flag,fnd_api.g_miss_char) = 'N'
964 AND
965 d_pty_dtl_tbl(j).active_end_date is null
966 AND
967 l_batch_type in ('XFER','MOVE','GEN')
968 THEN
969 l_pty_index := l_pty_index + 1;
970 n_party_tbl(l_pty_index).parent_tbl_index := l_parent_tbl_index;
971 n_party_tbl(l_pty_index).instance_id := l_core_inst_pty_det.pty_instance_id;
972 n_party_tbl(l_pty_index).party_source_table := d_pty_dtl_tbl(j).PARTY_SOURCE_TABLE;
973 n_party_tbl(l_pty_index).party_id := d_pty_dtl_tbl(j).PARTY_SOURCE_ID;
974 n_party_tbl(l_pty_index).relationship_type_code := d_pty_dtl_tbl(j).RELATIONSHIP_TYPE_CODE;
975 n_party_tbl(l_pty_index).contact_flag := 'N';
976 l_party_detail_id := d_pty_dtl_tbl(j).txn_party_detail_id;
977 l_pty_tbl_ind := l_pty_index;
978 -- Building Association to be expired
979 ELSIF d_pty_dtl_tbl(j).relationship_type_code <> 'OWNER'
980 AND
981 nvl(d_pty_dtl_tbl(j).contact_flag,fnd_api.g_miss_char) = 'N'
982 AND
983 nvl(d_pty_dtl_tbl(j).active_end_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date
984 AND
985 l_batch_type in ('XFER','MOVE','GEN')
986 THEN
987 FOR l_asso_exp_rec IN curr_asso_csr(l_core_inst_pty_det.pty_instance_id)
988 LOOP
989 IF l_asso_exp_rec.pty_party_source_table = d_pty_dtl_tbl(j).PARTY_SOURCE_TABLE
990 AND
991 l_asso_exp_rec.pty_party_id = d_pty_dtl_tbl(j).PARTY_SOURCE_ID
992 AND
993 l_asso_exp_rec.pty_rel_type_code = d_pty_dtl_tbl(j).RELATIONSHIP_TYPE_CODE
994 THEN
995 l_pty_index := l_pty_index + 1;
996 n_party_tbl(l_pty_index).parent_tbl_index := l_parent_tbl_index;
997 n_party_tbl(l_pty_index).instance_party_id := l_asso_exp_rec.pty_instance_party_id;
998 n_party_tbl(l_pty_index).instance_id := l_core_inst_pty_det.pty_instance_id;
999 n_party_tbl(l_pty_index).party_source_table := d_pty_dtl_tbl(j).PARTY_SOURCE_TABLE;
1000 n_party_tbl(l_pty_index).party_id := d_pty_dtl_tbl(j).PARTY_SOURCE_ID;
1001 n_party_tbl(l_pty_index).relationship_type_code := d_pty_dtl_tbl(j).RELATIONSHIP_TYPE_CODE;
1002 n_party_tbl(l_pty_index).active_end_date := d_pty_dtl_tbl(j).ACTIVE_END_DATE;
1003 n_party_tbl(l_pty_index).object_version_number := l_asso_exp_rec.pty_obj_version_number;
1004 n_party_tbl(l_pty_index).contact_flag := 'N';
1005 END IF;
1006 END LOOP;
1007 END IF;
1008 IF l_party_detail_id is not null
1009 THEN
1010 -- Building Accounts
1011 IF d_pty_acct_tbl.count > 0
1012 THEN
1013 FOR k IN d_pty_acct_tbl.FIRST .. d_pty_acct_tbl.LAST
1014 LOOP
1015 IF d_pty_acct_tbl(K).TXN_PARTY_DETAIL_ID = l_party_detail_id
1016 THEN
1017 IF d_pty_acct_tbl(K).relationship_type_code <> 'OWNER'
1018 THEN
1019 l_acct_index := l_acct_index + 1;
1020 n_party_account_tbl(l_acct_index).parent_tbl_index := l_pty_tbl_ind;
1021 n_party_account_tbl(l_acct_index).party_account_id := d_pty_acct_tbl(K).ACCOUNT_ID;
1022 n_party_account_tbl(l_acct_index).relationship_type_code := d_pty_acct_tbl(K).RELATIONSHIP_TYPE_CODE;
1023 n_party_account_tbl(l_acct_index).bill_to_address := d_pty_acct_tbl(K).BILL_TO_ADDRESS_ID;
1024 n_party_account_tbl(l_acct_index).ship_to_address := d_pty_acct_tbl(K).SHIP_TO_ADDRESS_ID;
1025 END IF;
1026 END IF;
1027 END LOOP;
1028 END IF;
1029 -- Building Contacts
1030 FOR l in d_pty_dtl_tbl.FIRST .. d_pty_dtl_tbl.LAST
1031 LOOP
1032 IF d_pty_dtl_tbl(l).contact_party_id = l_party_detail_id
1033 THEN
1034 l_pty_index := l_pty_index + 1;
1035 n_party_tbl(l_pty_index).parent_tbl_index := l_parent_tbl_index;
1036 n_party_tbl(l_pty_index).contact_parent_tbl_index := l_pty_tbl_ind;
1037 n_party_tbl(l_pty_index).instance_id := l_core_inst_pty_det.pty_instance_id;
1038 n_party_tbl(l_pty_index).party_source_table := d_pty_dtl_tbl(l).PARTY_SOURCE_TABLE;
1039 n_party_tbl(l_pty_index).party_id := d_pty_dtl_tbl(l).PARTY_SOURCE_ID;
1040 n_party_tbl(l_pty_index).relationship_type_code := d_pty_dtl_tbl(l).RELATIONSHIP_TYPE_CODE;
1041 n_party_tbl(l_pty_index).contact_flag := 'Y';
1042 END IF;
1043 END LOOP;
1044 l_party_detail_id := '';
1045 END IF;
1046 END LOOP; -- Loop end for each record in party_detail table
1047 END IF;
1048 -- Building extended attribs
1049 IF d_ext_attrib_tbl.count > 0
1050 THEN
1051 FOR m in d_ext_attrib_tbl.FIRST .. d_ext_attrib_tbl.LAST
1052 LOOP
1053 FOR l_core_ieav_val_rec IN core_iea_val_csr(l_txn_line_id)
1054 LOOP
1055 l_ext_attr_ind := 0;
1056 IF l_core_ieav_val_rec.instance_id = l_core_inst_pty_det.pty_instance_id
1057 AND
1058 l_core_ieav_val_rec.attribute_id = d_ext_attrib_tbl(m).attribute_source_id
1059 THEN
1060 l_ext_attr_ind := l_ext_attr_ind + 1;
1061 -- Attribute already existing to this Instance
1062 n_ext_attrib_values_tbl(l_ext_attr_ind).parent_tbl_index := l_parent_tbl_index;
1063 n_ext_attrib_values_tbl(l_ext_attr_ind).attribute_value_id := l_core_ieav_val_rec.attribute_value_id;
1064 n_ext_attrib_values_tbl(l_ext_attr_ind).attribute_value := d_ext_attrib_tbl(m).attribute_value;
1065 n_ext_attrib_values_tbl(l_ext_attr_ind).object_version_number := l_core_ieav_val_rec.object_version_number;
1066 ELSE
1067 l_ext_attr_ind := l_ext_attr_ind + 1;
1068 -- Create new attribute to this Instance
1069 n_ext_attrib_values_tbl(l_ext_attr_ind).parent_tbl_index := l_parent_tbl_index;
1070 n_ext_attrib_values_tbl(l_ext_attr_ind).instance_id := l_core_ieav_val_rec.instance_id;
1071 n_ext_attrib_values_tbl(l_ext_attr_ind).attribute_id := d_ext_attrib_tbl(m).attribute_source_id;
1072 n_ext_attrib_values_tbl(l_ext_attr_ind).attribute_value := d_ext_attrib_tbl(m).attribute_value;
1073 END IF;
1074 END LOOP;
1075 END LOOP;
1076 END IF;
1077 END LOOP; -- Loop end for each instance rec
1078 debug('Details passed to group API : ');
1079 debug(' Instance_tbl '||n_instance_tbl.count);
1080 debug(' party_tbl '||n_party_tbl.count);
1081 debug(' party_account_tbl '||n_party_account_tbl.count);
1082 debug(' Extendedattr_tbl '||n_ext_attrib_values_tbl.count);
1083 IF n_instance_tbl.count > 0
1084 THEN
1085
1086 csi_t_gen_utility_pvt.dump_csi_instance_tbl(n_instance_tbl);
1087 csi_t_gen_utility_pvt.dump_csi_party_tbl(n_party_tbl);
1088 csi_t_gen_utility_pvt.dump_csi_account_tbl(n_party_account_tbl);
1089 debug('Source Transaction Date in Transaction: '||n_txn_rec.source_transaction_date);
1090 debug('Transaction Date in Transaction: '||n_txn_rec.transaction_date);
1091
1092 csi_item_instance_grp.update_item_instance (
1093 p_api_version => 1.0
1094 ,p_commit => fnd_api.g_false
1095 ,p_init_msg_list => fnd_api.g_true
1096 ,p_validation_level => fnd_api.g_valid_level_full
1097 ,p_instance_tbl => n_instance_tbl
1098 ,p_ext_attrib_values_tbl => n_ext_attrib_values_tbl
1099 ,p_party_tbl => n_party_tbl
1100 ,p_account_tbl => n_party_account_tbl
1101 ,p_pricing_attrib_tbl => n_price_tbl
1102 ,p_org_assignments_tbl => n_org_assignments_tbl
1103 ,p_asset_assignment_tbl => n_asset_assignment_tbl
1104 ,p_txn_rec => n_txn_rec
1105 ,x_instance_id_lst => n_instance_id_lst
1106 ,p_grp_upd_error_tbl => n_grp_error_tbl
1107 ,x_return_status => l_return_status
1108 ,x_msg_count => l_msg_count
1109 ,x_msg_data => l_msg_data);
1110 debug('Group API Return status :'|| l_return_status);
1111 -- IF NOT l_return_status = fnd_api.g_ret_sts_success
1112 -- THEN
1113 -- l_msg_index := 1;
1114 -- l_Error_Message := l_Msg_Data;
1115 -- WHILE l_msg_count > 0
1116 -- LOOP
1117 -- l_Error_Message := FND_MSG_PUB.GET(l_msg_index, FND_API.G_FALSE);
1118 -- l_msg_index := l_msg_index + 1;
1119 -- l_Msg_Count := l_Msg_Count - 1;
1120 -- END LOOP;
1121 -- RAISE fnd_api.g_exc_error;
1122 -- END IF;
1123 -- END IF;
1124
1125 If n_grp_error_tbl.count > 0
1126 Then
1127 l_return_status := fnd_api.g_ret_sts_error;
1128 errbuf := 'E';
1129 -- Added for bug 5169999
1130 Elsif l_return_status = 'W'
1131 Then
1132 errbuf := 'W';
1133 l_errbuf :='W';
1134 l_msg_index := 1;
1135
1136 l_warning_message := l_msg_data;
1137 WHILE l_msg_count > 0
1138 LOOP
1139 l_warning_message := FND_MSG_PUB.GET(l_msg_index, FND_API.G_FALSE);
1140 debug_out('Warning message '||l_msg_index||' from OKS API is :'||l_warning_message);
1141 l_msg_index := l_msg_index + 1;
1142 l_msg_count := l_msg_count - 1;
1143 END LOOP;
1144 End If;
1145
1146
1147 If NOT (l_return_status = fnd_api.g_ret_sts_success) AND
1148 errbuf <>'W'
1149 Then
1150 -- Buildinig the error table for sorting and reporting
1151 If nvl(n_grp_error_tbl.count,0) > 0
1152 Then
1153 If l_found_error = 'W'
1154 Then
1155 For i in n_grp_error_tbl.FIRST .. n_grp_error_tbl.LAST
1156 LOOP
1157 l_grp_api_ind := l_temp_merror_tbl.count + 1;
1158 l_temp_merror_tbl(l_grp_api_ind).Entry_Id := p_entry_id;
1159 -- l_temp_merror_tbl(l_grp_api_ind).Txn_Line_Detail_Id := n_grp_error_tbl(i).Txn_Line_Detail_Id;
1160 l_temp_merror_tbl(l_grp_api_ind).Instance_Id := n_grp_error_tbl(i).Instance_id;
1161 l_temp_merror_tbl(l_grp_api_ind).Error_Message := n_grp_error_tbl(i).Error_Message;
1162 l_temp_merror_tbl(l_grp_api_ind).Error_Code := 'E';
1163 l_temp_merror_tbl(l_grp_api_ind).NAME := l_name;
1164 l_temp_merror_tbl(l_grp_api_ind).Entity_Name := n_grp_error_tbl(i).Entity_Name;
1165 End Loop;
1166 Else
1167 For i in n_grp_error_tbl.FIRST .. n_grp_error_tbl.LAST
1168 LOOP
1169 l_temp_merror_tbl(i).Entry_Id := p_entry_id;
1170 -- l_temp_merror_tbl(l_temp_merror_tbl.count + 1).Txn_Line_Detail_Id := n_grp_error_tbl(i).Txn_Line_Detail_Id;
1171 l_temp_merror_tbl(i).Instance_Id := n_grp_error_tbl(i).Instance_id;
1172 l_temp_merror_tbl(i).Error_Message := n_grp_error_tbl(i).Error_Message;
1173 l_temp_merror_tbl(i).Error_Code := 'E';
1174 l_temp_merror_tbl(i).NAME := l_name;
1175 l_temp_merror_tbl(i).Entity_Name := n_grp_error_tbl(i).Entity_Name;
1176 End Loop;
1177 End If;
1178 END IF;
1179 -- debug('Call update error status');
1180 update_error_status(l_temp_merror_tbl,l_txn_line_id,p_entry_id);
1181 -- debug('Call error report');
1182 conc_report ( p_temp_merror_tbl => l_temp_merror_tbl,
1183 p_name => 'GRPERROR',
1184 p_batch_id => p_entry_id,
1185 p_batch_name => l_name
1186 );
1187 debug('Aborting Concurrent Program by raising exception');
1188 RAISE fnd_api.g_exc_error;
1189 Else
1190 -- For each Instance need to Create Notes
1191 IF n_instance_tbl.count > 0
1192 THEN
1193 IF l_batch_desc IS NOT NULL
1194 THEN
1195 l_temp_merror_tbl.delete;
1196 FOR i in n_instance_tbl.FIRST .. n_instance_tbl.LAST
1197 LOOP
1198 JTF_NOTES_PUB.CREATE_NOTE
1199 (
1200 p_parent_note_id => NULL,
1201 p_api_version => 1,
1202 p_init_msg_list => NULL,
1203 p_commit => FND_API.G_FALSE,
1204 p_validation_level => fnd_api.g_valid_level_full, --0,
1205 x_return_status => l_return_status,
1206 x_msg_count => l_msg_count,
1207 x_msg_data => l_msg_data,
1208 x_jtf_note_id => l_note_id,
1209 p_org_id => NULL,
1210 p_source_object_id => n_instance_tbl(i).instance_id,
1211 p_source_object_code => 'CP',
1212 p_notes => l_batch_desc,
1213 -- p_notes_detail => COMMENTS,
1214 p_note_status => 'I',
1215 p_entered_by => FND_GLOBAL.USER_ID,
1216 p_entered_date => SYSDATE,
1217 p_last_update_date => SYSDATE,
1218 p_last_updated_by => FND_GLOBAL.USER_ID,
1219 p_creation_date => SYSDATE,
1220 p_created_by => FND_GLOBAL.USER_ID
1221 );
1222
1223 debug('JTF API Return status :'||l_return_status);
1224 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS)
1225 Then
1226 If l_jtf_error = 'N'
1227 Then
1228 l_jtf_error := 'Y';
1229 errbuf := 'E';
1230 End If;
1231
1232 l_msg_index := 1;
1233 l_Error_Message := l_Msg_Data;
1234 WHILE l_msg_count > 0
1235 LOOP
1236 l_Error_Message := FND_MSG_PUB.GET(l_msg_index, FND_API.G_FALSE);
1237 l_msg_index := l_msg_index + 1;
1238 l_Msg_Count := l_Msg_Count - 1;
1239 END LOOP;
1240
1241 l_jtf_err_ind := l_temp_merror_tbl.count + 1;
1242 l_temp_merror_tbl(l_jtf_err_ind).Entry_Id := p_entry_id;
1243 -- l_temp_merror_tbl(l_jtf_err_ind).Txn_Line_Detail_Id := n_grp_error_tbl(i).Txn_Line_Detail_Id;
1244 l_temp_merror_tbl(l_jtf_err_ind).Instance_Id := n_grp_error_tbl(i).Instance_id;
1245 l_temp_merror_tbl(l_jtf_err_ind).Error_Message := l_Error_Message;
1246 l_temp_merror_tbl(l_jtf_err_ind).Error_Code := 'E';
1247 l_temp_merror_tbl(l_jtf_err_ind).NAME := l_name;
1248 l_temp_merror_tbl(l_jtf_err_ind).Entity_Name := 'JTFNOTES';
1249 END IF;
1250 END LOOP;
1251 END IF;
1252 If l_jtf_error <> 'Y'
1253 Then
1254 Debug('Notes created for instance successfully');
1255 Debug_out('Notes created for instance successfully');
1256 Debug('Checking for system mass update');
1257 Debug_out('Checking for system mass update');
1258 -- Bug 7483403
1259 IF NVL(l_system_cascade, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
1260 l_system_cascade := 'N';
1261 END IF; -- NVL(l_system_cascade, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1262 IF l_system_cascade = 'N' THEN
1263 -- The system cascade is not set
1264 -- Update the mass update status and exit
1265 -- Updating the status of transaction line for the batch
1266 UPDATE csi_t_transaction_lines
1267 SET processing_status = 'PROCESSED'
1268 WHERE transaction_line_id = l_txn_line_id;
1269
1270 -- Update the status of all the transaction line details for the batch
1271 UPDATE csi_t_txn_line_details
1272 SET error_explanation = '',
1273 processing_status = 'PROCESSED',
1274 error_code = 'P'
1275 WHERE transaction_line_id = l_txn_line_id
1276 AND instance_id is not null;
1277
1278 -- Updating batch status
1279 UPDATE csi_mass_edit_entries_b
1280 SET status_code = 'SUCCESSFUL'
1281 WHERE entry_id = p_entry_id;
1282
1283 IF l_errbuf='W'
1284 THEN
1285 Debug(' ');
1286 Debug(' **** Mass Edit Batch ('||l_name||') Status : WARNING');
1287 Debug(' ');
1288
1289 Debug_out(' ');
1290 Debug_out('Mass Edit Batch ('||l_name||') Status : WARNING');
1291 Debug_out(' ');
1292 Debug_out('---------------------------------------------------');
1293 ELSE
1294 Debug(' ');
1295 Debug(' **** Mass Edit Batch ('||l_name||') Status : SUCCESSFUL');
1296 Debug(' ');
1297
1298 Debug_out(' ');
1299 Debug_out('Mass Edit Batch ('||l_name||') Status : SUCCESSFUL');
1300 Debug_out(' ');
1301 Debug_out('---------------------------------------------------');
1302 END IF;
1303 END IF; -- l_system_cascade = 'N'
1304 -- End Bug 7483403
1305
1306 Else
1307 -- debug('Call update error status');
1308 update_error_status(l_temp_merror_tbl,l_txn_line_id,p_entry_id);
1309
1310 -- debug('Call error report');
1311 conc_report ( p_temp_merror_tbl => l_temp_merror_tbl,
1312 p_name => 'JTFERROR',
1313 p_batch_id => p_entry_id,
1314 p_batch_name => l_name
1315 );
1316 debug('Aborting Concurrent Program by raising exception');
1317 RAISE fnd_api.g_exc_error;
1318 End If;
1319 END IF; -- End If for Notes API
1320 END IF; -- End If for Failure/success
1321 END IF; -- End If for Grp APi
1322
1323 -- Added for the ER 6031179
1324 -- Checking to see if there are instances which needs to be updated
1325 -- This block will update systems for transfer owner batch
1326 -- The system will be updated if all the active item instances
1327 -- which are part of the system is included in the transfer owner batch
1328
1329 IF NVL(l_system_cascade, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
1330 l_system_cascade := 'N';
1331 END IF;
1332
1333 debug('System Cascade : ' || l_system_cascade);
1334 Debug_out('System Cascade :' || l_system_cascade);
1335
1336 BEGIN
1337 SELECT 'x' INTO l_new_owner
1338 FROM CSI_T_PARTY_DETAILS WHERE TXN_LINE_DETAIL_ID IN(
1339 SELECT TXN_LINE_DETAIL_ID FROM CSI_T_TXN_LINE_DETAILS CTLD, CSI_MASS_EDIT_ENTRIES_VL CMEE
1340 WHERE CTLD.TRANSACTION_LINE_ID = CMEE.TXN_LINE_ID
1341 AND CMEE.ENTRY_ID = p_entry_id
1342 AND INSTANCE_ID IS NULL)
1343 AND PARTY_SOURCE_TABLE = 'HZ_PARTIES'
1344 AND RELATIONSHIP_TYPE_CODE = 'OWNER'
1345 AND ROWNUM = 1;
1346
1347 SELECT 'x' INTO l_old_owner
1348 FROM CSI_T_PARTY_DETAILS WHERE TXN_LINE_DETAIL_ID IN(
1349 SELECT TXN_LINE_DETAIL_ID FROM CSI_T_TXN_LINE_DETAILS CTLD, CSI_MASS_EDIT_ENTRIES_VL CMEE
1350 WHERE CTLD.TRANSACTION_LINE_ID = CMEE.TXN_LINE_ID
1351 AND CMEE.ENTRY_ID = p_entry_id
1352 AND INSTANCE_ID IS NOT NULL)
1353 AND PARTY_SOURCE_TABLE = 'HZ_PARTIES'
1354 AND RELATIONSHIP_TYPE_CODE = 'OWNER'
1355 AND ROWNUM = 1;
1356
1357 debug('l_old_owner type - ' || l_old_owner);
1358 debug('l_new_owner type - ' || l_new_owner);
1359
1360 IF l_old_owner IS NOT NULL AND l_new_owner IS NOT NULL THEN
1361 l_perform_system_mu := 'Y';
1362 END IF;
1363
1364 EXCEPTION
1365 WHEN OTHERS THEN
1366 debug('The Batch doesn not qualify for system mass update
1367 as the owner type is not HZ_PARTIES');
1368 l_perform_system_mu := 'N';
1369 END;
1370
1371 -- The processing of systems must proceed only if the owner type is HZ_PARTIES
1372 IF l_batch_type in ('XFER') AND l_system_cascade = 'Y' AND l_perform_system_mu = 'Y' THEN
1373 IF n_instance_tbl.count > 0 THEN
1374
1375 -- Call the PROCESS_SYSTEM_MASS_UPDATE to
1376 -- 1. Identify the systems which are part of the mass update transfer owner batch
1377 -- 2. After identifying the systems, update them
1378
1379 PROCESS_SYSTEM_MASS_UPDATE (
1380 p_api_version => 1.0
1381 ,p_commit => fnd_api.g_false
1382 ,p_Entry_id => p_Entry_id
1383 ,p_instance_tbl => n_instance_tbl
1384 ,p_ext_attrib_values_tbl => n_ext_attrib_values_tbl -- Not used, retained for future enhancements
1385 ,p_party_tbl => n_party_tbl
1386 ,p_account_tbl => n_party_account_tbl
1387 ,p_txn_rec => n_txn_rec
1388 ,x_return_status => l_return_status
1389 ,x_msg_count => l_msg_count
1390 ,x_msg_data => l_msg_data);
1391
1392 IF NOT l_return_status = fnd_api.g_ret_sts_success
1393 THEN
1394 l_msg_index := 1;
1395 l_Error_Message := l_Msg_Data;
1396 WHILE l_msg_count > 0
1397 LOOP
1398 l_Error_Message := FND_MSG_PUB.GET(l_msg_index, FND_API.G_FALSE);
1399 l_msg_index := l_msg_index + 1;
1400 l_Msg_Count := l_Msg_Count - 1;
1401 END LOOP;
1402 RAISE fnd_api.g_exc_error;
1403 END IF;
1404 -- END IF;
1405
1406 IF l_return_status = fnd_api.g_ret_sts_success THEN
1407 -- Updating the status of transaction line for the batch
1408 UPDATE csi_t_transaction_lines
1409 SET processing_status = 'PROCESSED'
1410 WHERE transaction_line_id = l_txn_line_id;
1411
1412 -- Update the status of all the transaction line details for the batch
1413 UPDATE csi_t_txn_line_details
1414 SET error_explanation = '',
1415 processing_status = 'PROCESSED',
1416 error_code = 'P'
1417 WHERE transaction_line_id = l_txn_line_id
1418 AND instance_id is not null;
1419
1420 -- Updating batch status
1421 UPDATE csi_mass_edit_entries_b
1422 SET status_code = 'SUCCESSFUL'
1423 WHERE entry_id = p_entry_id;
1424
1425 IF l_errbuf='W'
1426 THEN
1427 Debug(' ');
1428 Debug(' **** Mass Edit Batch ('||l_name||') Status : WARNING');
1429 Debug(' ');
1430
1431 Debug_out(' ');
1432 Debug_out('Mass Edit Batch ('||l_name||') Status : WARNING');
1433 Debug_out(' ');
1434 Debug_out('---------------------------------------------------');
1435 ELSE
1436 Debug(' ');
1437 Debug(' **** Mass Edit Batch ('||l_name||') Status : SUCCESSFUL');
1438 Debug(' ');
1439
1440 Debug_out(' ');
1441 Debug_out('Mass Edit Batch ('||l_name||') Status : SUCCESSFUL');
1442 Debug_out(' ');
1443 Debug_out('---------------------------------------------------');
1444 END IF; -- l_errbuf='W'
1445 ELSE
1446 -- debug('Call update error status');
1447 update_error_status(l_temp_merror_tbl,l_txn_line_id,p_entry_id);
1448
1449 -- debug('Call error report');
1450 conc_report ( p_temp_merror_tbl => l_temp_merror_tbl,
1451 p_name => 'JTFERROR',
1452 p_batch_id => p_entry_id,
1453 p_batch_name => l_name
1454 );
1455
1456 UPDATE_MUSYS_ERR_STATUS(p_entry_id);
1457
1458 debug('Aborting Concurrent Program by raising exception');
1459 RAISE fnd_api.g_exc_error;
1460 END IF; -- l_return_status = fnd_api.g_ret_sts_success
1461
1462
1463 END IF; -- n_instance_tbl.count Loop
1464 END IF; -- l_batch_type in ('XFER')
1465 -- End of Addition for the ER 6031179
1466
1467 END IF; -- Common Validations
1468 END;
1469 END IF; -- Batch Not Found
1470
1471 Commit;
1472
1473 EXCEPTION
1474 WHEN fnd_api.g_exc_error THEN
1475 ROLLBACK TO START_PROCESSING;
1476 debug( 'Program Errored : ');
1477 -- l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
1478 errbuf := 'E';
1479 WHEN fnd_api.g_exc_unexpected_error THEN
1480 ROLLBACK TO START_PROCESSING;
1481 debug( 'Program Errored : ');
1482 -- l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
1483 errbuf := 'E';
1484 WHEN OTHERS THEN
1485 ROLLBACK TO START_PROCESSING;
1486 debug( 'When others exception from Process_mass_edit_batch');
1487 debug( to_char(SQLCODE)||substr(SQLERRM, 1, 255));
1488 -- l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
1489 errbuf := 'E';
1490 END Process_mass_edit_batch;
1491
1492 PROCEDURE CREATE_MASS_EDIT_BATCH
1493 (
1494 p_api_version IN NUMBER,
1495 p_commit IN VARCHAR2 := fnd_api.g_false,
1496 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1497 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1498 px_mass_edit_rec IN OUT NOCOPY csi_mass_edit_pub.mass_edit_rec,
1499 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec ,
1500 px_mass_edit_inst_tbl IN OUT NOCOPY csi_mass_edit_pub.mass_edit_inst_tbl,
1501 px_txn_line_detail_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec,
1502 px_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
1503 px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
1504 px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
1505 x_mass_edit_error_tbl OUT NOCOPY mass_edit_error_tbl,
1506 x_return_status OUT NOCOPY VARCHAR2,
1507 x_msg_count OUT NOCOPY NUMBER,
1508 x_msg_data OUT NOCOPY VARCHAR2
1509
1510 ) IS
1511 l_api_version NUMBER := 1.0;
1512 l_api_name VARCHAR2(30) := 'CREATE_MASS_EDIT_BATCH_PUB';
1513
1514 l_debug_level NUMBER;
1515
1516 l_return_status VARCHAR2(1) := FND_API.G_ret_sts_success;
1517 l_msg_count NUMBER;
1518 l_msg_data VARCHAR2(512);
1519
1520
1521 Begin
1522 -- Standard Start of API savepoint
1523 SAVEPOINT create_mass_edit_pub;
1524
1525 csi_t_gen_utility_pvt.add('API Being Executed : CREATE_MASS_EDIT_BATCH_PUB');
1526 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
1527
1528 -- Initialize message list if p_init_msg_list is set to TRUE.
1529 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1530 FND_MSG_PUB.Initialize;
1531 END IF;
1532
1533 -- Initialize API return status to success
1534 x_return_status := FND_API.G_RET_STS_SUCCESS;
1535
1536 -- Standard call to check for call compatibility.
1537 IF NOT
1538
1539 FND_API.Compatible_API_Call (
1540 p_current_version_number => l_api_version,
1541 p_caller_version_number => p_api_version,
1542 p_api_name => l_api_name,
1543 p_pkg_name => g_pkg_name) THEN
1544
1545 RAISE FND_API.G_Exc_Unexpected_Error;
1546
1547 END IF;
1548 -- main code starts here
1549 --
1550 -- This procedure check if the installed base is active, If not active
1551 -- populates the error message in the message queue and raises the
1552 -- fnd_api.g_exc_error exception
1553 --
1554
1555 csi_utility_grp.check_ib_active;
1556
1557 debug('px_txn_line_detail_tbl.count: '||px_mass_edit_inst_tbl.count);
1558
1559 csi_mass_edit_pvt.create_mass_edit_batch
1560 (
1561 p_api_version => l_api_version,
1562 p_commit => p_commit,
1563 p_init_msg_list => p_init_msg_list,
1564 p_validation_level => p_validation_level,
1565 px_mass_edit_rec => px_mass_edit_rec,
1566 px_txn_line_rec => px_txn_line_rec,
1567 px_mass_edit_inst_tbl => px_mass_edit_inst_tbl,
1568 px_txn_line_detail_rec => px_txn_line_detail_rec,
1569 px_txn_party_detail_tbl => px_txn_party_detail_tbl,
1570 px_txn_pty_acct_detail_tbl => px_txn_pty_acct_detail_tbl,
1571 px_txn_ext_attrib_vals_tbl => px_txn_ext_attrib_vals_tbl,
1572 x_mass_edit_error_tbl => x_mass_edit_error_tbl,
1573 x_return_status => x_return_status,
1574 x_msg_count => x_msg_count,
1575 x_msg_data => x_msg_data
1576 );
1577
1578
1579 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1580 RAISE fnd_api.g_exc_error;
1581 END IF;
1582
1583 -- Standard check of p_commit.
1584 IF FND_API.To_Boolean( p_commit ) THEN
1585 COMMIT WORK;
1586 END IF;
1587
1588 -- Standard call to get message count and IF count is get message info.
1589 FND_MSG_PUB.Count_And_Get
1590 (p_count => x_msg_count,
1591 p_data => x_msg_data
1592 );
1593
1594 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
1595 EXCEPTION
1596 WHEN FND_API.G_EXC_ERROR THEN
1597 ROLLBACK TO create_mass_edit_pub;
1598 x_return_status := FND_API.G_RET_STS_ERROR ;
1599 FND_MSG_PUB.Count_And_Get
1600 (p_count => x_msg_count,
1601 p_data => x_msg_data
1602 );
1603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1605 ROLLBACK TO create_mass_edit_pub;
1606 FND_MSG_PUB.Count_And_Get
1607 ( p_count => x_msg_count,
1608 p_data => x_msg_data
1609 );
1610 WHEN OTHERS THEN
1611 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1612 ROLLBACK TO create_mass_edit_pub;
1613 IF FND_MSG_PUB.Check_Msg_Level
1614 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1615 THEN
1616 FND_MSG_PUB.Add_Exc_Msg
1617 (G_PKG_NAME ,
1618 l_api_name
1619 );
1620 END IF;
1621 FND_MSG_PUB.Count_And_Get
1622 (p_count => x_msg_count,
1623 p_data => x_msg_data
1624 );
1625
1626 End CREATE_MASS_EDIT_BATCH;
1627
1628 Procedure validate_dup_batch_instances
1629 (
1630 p_txn_line_id IN NUMBER,
1631 px_mass_edit_inst_tbl IN mass_edit_inst_tbl,
1632 x_output in out nocopy varchar2
1633 )
1634 IS
1635 CURSOR selected_instance_csr (p_txn_line_id IN NUMBER) IS
1636 select cii.instance_id,
1637 cii.instance_number
1638 from csi_instance_search_v cii,
1639 csi_t_txn_line_details tld
1640 where tld.transaction_line_id = p_txn_line_id and
1641 tld.instance_id is not null and
1642 tld.instance_id = cii.instance_id and
1643 nvl(tld.active_end_date(+),sysdate+1) > sysdate;
1644 BEGIN
1645 FOR l_instance_csr IN selected_instance_csr(p_txn_line_id)
1646 LOOP
1647 if px_mass_edit_inst_tbl.count > 0 then
1648 for i in px_mass_edit_inst_tbl.first..px_mass_edit_inst_tbl.last
1649 loop
1650 IF l_instance_csr.instance_id = px_mass_edit_inst_tbl(i).instance_id THEN
1651 IF x_output IS NOT NULL THEN
1652 x_output := x_output || ',' ;
1653 END IF;
1654 x_output := x_output || l_instance_csr.instance_number;
1655 END IF;
1656 end loop;
1657 end if;
1658 END LOOP;
1659 END;
1660
1661 PROCEDURE UPDATE_MASS_EDIT_BATCH (
1662 p_api_version IN NUMBER,
1663 p_commit IN VARCHAR2 := fnd_api.g_false,
1664 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1665 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1666 px_mass_edit_rec IN OUT NOCOPY csi_mass_edit_pub.mass_edit_rec,
1667 px_txn_line_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_rec ,
1668 px_mass_edit_inst_tbl IN OUT NOCOPY mass_edit_inst_tbl,
1669 px_txn_line_detail_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec,
1670 px_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
1671 px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
1672 px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
1673 x_mass_edit_error_tbl OUT NOCOPY mass_edit_error_tbl,
1674 x_return_status OUT NOCOPY VARCHAR2,
1675 x_msg_count OUT NOCOPY NUMBER,
1676 x_msg_data OUT NOCOPY VARCHAR2) IS
1677
1678
1679 l_api_version NUMBER := 1.0;
1680 l_api_name VARCHAR2(30) := 'UPDATE_MASS_EDIT_BATCH_PUB';
1681 l_msg_count NUMBER;
1682 l_msg_data VARCHAR2(200);
1683 l_return_status VARCHAR2(1);
1684 x_output varchar2(4000);
1685 BEGIN
1686
1687 -- Standard Start of API savepoint
1688 SAVEPOINT update_mass_edit_pub;
1689
1690 csi_t_gen_utility_pvt.add('API Being Executed : UPDATE_MASS_EDIT_BATCH_PUB');
1691 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
1692
1693 -- Initialize message list if p_init_msg_list is set to TRUE.
1694 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1695 FND_MSG_PUB.Initialize;
1696 END IF;
1697
1698 -- Initialize API return status to success
1699 x_return_status := FND_API.G_RET_STS_SUCCESS;
1700
1701 -- Standard call to check for call compatibility.
1702 IF NOT
1703
1704 FND_API.Compatible_API_Call (
1705 p_current_version_number => l_api_version,
1706 p_caller_version_number => p_api_version,
1707 p_api_name => l_api_name,
1708 p_pkg_name => g_pkg_name) THEN
1709
1710 RAISE FND_API.G_Exc_Unexpected_Error;
1711
1712 END IF;
1713 -- main code starts here
1714
1715 validate_dup_batch_instances(
1716 px_txn_line_rec.TRANSACTION_LINE_ID,
1717 px_mass_edit_inst_tbl,
1718 x_output);
1719
1720 IF x_output IS NOT NULL THEN
1721 FND_MESSAGE.set_name('CSI','CSI_MU_DUP_BATCH_INSTANCES');
1722 FND_MESSAGE.set_token('INST_NUM',x_output);
1723 FND_MSG_PUB.add;
1724 RAISE FND_API.g_exc_error;
1725 END IF;
1726
1727 -- call API to run
1728 csi_t_gen_utility_pvt.add('px_txn_line_detail_tbl.count: '||px_mass_edit_inst_tbl.count);
1729
1730
1731 csi_mass_edit_pvt.update_mass_edit_batch
1732 (
1733 p_api_version => l_api_version,
1734 p_commit => p_commit,
1735 p_init_msg_list => p_init_msg_list,
1736 p_validation_level => p_validation_level,
1737 px_mass_edit_rec => px_mass_edit_rec,
1738 px_txn_line_rec => px_txn_line_rec,
1739 px_mass_edit_inst_tbl => px_mass_edit_inst_tbl,
1740 px_txn_line_detail_rec => px_txn_line_detail_rec,
1741 px_txn_party_detail_tbl => px_txn_party_detail_tbl,
1742 px_txn_pty_acct_detail_tbl => px_txn_pty_acct_detail_tbl,
1743 px_txn_ext_attrib_vals_tbl => px_txn_ext_attrib_vals_tbl,
1744 x_mass_edit_error_tbl => x_mass_edit_error_tbl,
1745 x_return_status => l_return_status,
1746 x_msg_count => l_msg_count,
1747 x_msg_data => l_msg_data
1748 );
1749
1750
1751 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1752 RAISE fnd_api.g_exc_error;
1753 END IF;
1754
1755 -- Standard check of p_commit.
1756 IF FND_API.To_Boolean( p_commit ) THEN
1757 COMMIT WORK;
1758 END IF;
1759
1760 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
1761 EXCEPTION
1762 WHEN FND_API.G_EXC_ERROR THEN
1763 ROLLBACK TO update_mass_edit_pub;
1764 x_return_status := FND_API.G_RET_STS_ERROR ;
1765 FND_MSG_PUB.Count_And_Get
1766 (p_count => x_msg_count,
1767 p_data => x_msg_data
1768 );
1769 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1771 ROLLBACK TO update_mass_edit_pub;
1772 FND_MSG_PUB.Count_And_Get
1773 ( p_count => x_msg_count,
1774 p_data => x_msg_data
1775 );
1776 WHEN OTHERS THEN
1777 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1778 ROLLBACK TO update_mass_edit_pub;
1779 IF FND_MSG_PUB.Check_Msg_Level
1780 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1781 THEN
1782 FND_MSG_PUB.Add_Exc_Msg
1783 (G_PKG_NAME ,
1784 l_api_name
1785 );
1786 END IF;
1787 FND_MSG_PUB.Count_And_Get
1788 (p_count => x_msg_count,
1789 p_data => x_msg_data
1790 );
1791
1792 END update_mass_edit_batch;
1793
1794 PROCEDURE DELETE_MASS_EDIT_BATCH
1795 (
1796 p_api_version IN NUMBER,
1797 p_commit IN VARCHAR2 := fnd_api.g_false,
1798 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1799 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1800 p_mass_edit_rec IN mass_edit_rec,
1801 x_return_status OUT NOCOPY VARCHAR2,
1802 x_msg_count OUT NOCOPY NUMBER,
1803 x_msg_data OUT NOCOPY VARCHAR2
1804
1805 ) IS
1806
1807 l_api_version NUMBER := 1.0;
1808 l_api_name VARCHAR2(30) := 'DELETE_MASS_EDIT_BATCH';
1809 l_msg_count NUMBER;
1810 l_msg_data VARCHAR2(2000);
1811 l_return_status VARCHAR2(1);
1812
1813 BEGIN
1814
1815 -- Standard Start of API savepoint
1816 SAVEPOINT delete_mass_edit_batch;
1817
1818 csi_t_gen_utility_pvt.add('API Being Executed : DELETE_MASS_EDIT_BATCH');
1819 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
1820
1821 -- Initialize message list if p_init_msg_list is set to TRUE.
1822 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1823 FND_MSG_PUB.Initialize;
1824 END IF;
1825
1826 -- Initialize API return status to success
1827 x_return_status := FND_API.G_RET_STS_SUCCESS;
1828
1829 -- Standard call to check for call compatibility.
1830 IF NOT
1831 FND_API.Compatible_API_Call (
1832 p_current_version_number => l_api_version,
1833 p_caller_version_number => p_api_version,
1834 p_api_name => l_api_name,
1835 p_pkg_name => g_pkg_name) THEN
1836
1837 RAISE FND_API.G_Exc_Unexpected_Error;
1838 END IF;
1839 -- This procedure check if the installed base is active, If not active
1840 -- populates the error message in the message queue and raises the
1841 -- fnd_api.g_exc_error exception
1842 --
1843 csi_utility_grp.check_ib_active;
1844
1845 -- check required params
1846 -- either the batch id OR the batch name is required for a delete
1847 IF ( nvl(p_mass_edit_rec.entry_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
1848 AND nvl(p_mass_edit_rec.name, fnd_api.g_miss_char) = fnd_api.g_miss_char ) THEN
1849
1850 FND_MESSAGE.set_name('CSI','CSI_API_REQD_PARAM_MISSING');
1851 FND_MESSAGE.set_token('API_NAME',l_api_name);
1852 FND_MESSAGE.set_token('MISSING_PARAM','Batch ID OR Batch Name');
1853 FND_MSG_PUB.add;
1854 RAISE FND_API.g_exc_error;
1855 END IF;
1856
1857 csi_mass_edit_pvt.delete_mass_edit_batch (
1858 p_api_version => p_api_version,
1859 p_commit => p_commit,
1860 p_init_msg_list => p_init_msg_list,
1861 p_validation_level => p_validation_level,
1862 p_mass_edit_rec => p_mass_edit_rec,
1863 x_return_status => l_return_status,
1864 x_msg_count => l_msg_count,
1865 x_msg_data => l_msg_data
1866 );
1867
1868 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1869 RAISE fnd_api.g_exc_error;
1870 END IF;
1871
1872 -- Standard check of p_commit.
1873 IF FND_API.To_Boolean( p_commit ) THEN
1874 COMMIT WORK;
1875 END IF;
1876
1877 -- Standard call to get message count and IF count is get message info.
1878 FND_MSG_PUB.Count_And_Get
1879 (p_count => x_msg_count,
1880 p_data => x_msg_data
1881 );
1882
1883 csi_t_gen_utility_pvt.add('API Executed : Delete Mass Edit Batch');
1884 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
1885
1886 csi_t_gen_utility_pvt.set_debug_off;
1887
1888 EXCEPTION
1889 WHEN FND_API.G_EXC_ERROR THEN
1890
1891 ROLLBACK TO Delete_Mass_Edit_Batch;
1892 x_return_status := FND_API.G_RET_STS_ERROR ;
1893 FND_MSG_PUB.Count_And_Get (
1894 p_count => x_msg_count,
1895 p_data => x_msg_data);
1896
1897 csi_t_gen_utility_pvt.set_debug_off;
1898
1899 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1900
1901 ROLLBACK TO Delete_Mass_Edit_Batch;
1902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1903
1904 FND_MSG_PUB.Count_And_Get(
1905 p_count => x_msg_count,
1906 p_data => x_msg_data);
1907
1908 csi_t_gen_utility_pvt.set_debug_off;
1909
1910 WHEN OTHERS THEN
1911
1912 ROLLBACK TO Delete_Mass_Edit_Batch;
1913 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1914
1915 IF FND_MSG_PUB.Check_Msg_Level(
1916 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1917
1918 FND_MSG_PUB.Add_Exc_Msg(
1919 p_pkg_name => G_PKG_NAME,
1920 p_procedure_name => l_api_name);
1921
1922 END IF;
1923
1924 FND_MSG_PUB.Count_And_Get(
1925 p_count => x_msg_count,
1926 p_data => x_msg_data);
1927
1928 csi_t_gen_utility_pvt.set_debug_off;
1929
1930 END Delete_Mass_Edit_Batch;
1931
1932 /* This is the wrapper API to handle multiple batch deletes. Calls the DELETE_MASS_EDIT_BATCH */
1933
1934 PROCEDURE DELETE_MASS_EDIT_BATCHES
1935 (
1936 p_api_version IN NUMBER,
1937 p_commit IN VARCHAR2 := fnd_api.g_false,
1938 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1939 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1940 p_mass_edit_tbl IN mass_edit_tbl,
1941 x_return_status OUT NOCOPY VARCHAR2,
1942 x_msg_count OUT NOCOPY NUMBER,
1943 x_msg_data OUT NOCOPY VARCHAR2
1944
1945 ) IS
1946
1947 l_api_version NUMBER := 1.0;
1948 l_api_name VARCHAR2(30) := 'DELETE_MASS_EDIT_BATCHES';
1949 l_msg_count NUMBER;
1950 l_msg_data VARCHAR2(2000);
1951 l_return_status VARCHAR2(1);
1952
1953 BEGIN
1954
1955 -- Standard Start of API savepoint
1956 SAVEPOINT delete_mass_edit_batches;
1957
1958 csi_t_gen_utility_pvt.add('API Being Executed : DELETE_MASS_EDIT_BATCHES');
1959 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
1960
1961 -- Initialize message list if p_init_msg_list is set to TRUE.
1962 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1963 FND_MSG_PUB.Initialize;
1964 END IF;
1965
1966 -- Initialize API return status to success
1967 x_return_status := FND_API.G_RET_STS_SUCCESS;
1968
1969 -- Standard call to check for call compatibility.
1970 IF NOT
1971 FND_API.Compatible_API_Call (
1972 p_current_version_number => l_api_version,
1973 p_caller_version_number => p_api_version,
1974 p_api_name => l_api_name,
1975 p_pkg_name => g_pkg_name) THEN
1976
1977 RAISE FND_API.G_Exc_Unexpected_Error;
1978 END IF;
1979 -- This procedure check if the installed base is active, If not active
1980 -- populates the error message in the message queue and raises the
1981 -- fnd_api.g_exc_error exception
1982 --
1983 csi_utility_grp.check_ib_active;
1984
1985 -- check required params
1986 -- either the batch id OR the batch name is required for a delete
1987 IF p_mass_edit_tbl.count > 0 THEN
1988 FOR m_ind in p_mass_edit_tbl.FIRST .. p_mass_edit_tbl.LAST LOOP
1989 IF ( nvl(p_mass_edit_tbl(m_ind).entry_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
1990 AND nvl(p_mass_edit_tbl(m_ind).name, fnd_api.g_miss_char) = fnd_api.g_miss_char ) THEN
1991
1992 FND_MESSAGE.set_name('CSI','CSI_API_REQD_PARAM_MISSING');
1993 FND_MESSAGE.set_token('API_NAME',l_api_name);
1994 FND_MESSAGE.set_token('MISSING_PARAM','Batch ID OR Batch Name');
1995 FND_MSG_PUB.add;
1996 RAISE FND_API.g_exc_error;
1997 END IF;
1998
1999 csi_mass_edit_pvt.delete_mass_edit_batch (
2000 p_api_version => p_api_version,
2001 p_commit => p_commit,
2002 p_init_msg_list => p_init_msg_list,
2003 p_validation_level => p_validation_level,
2004 p_mass_edit_rec => p_mass_edit_tbl(m_ind),
2005 x_return_status => l_return_status,
2006 x_msg_count => l_msg_count,
2007 x_msg_data => l_msg_data
2008 );
2009
2010 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2011 RAISE fnd_api.g_exc_error;
2012 END IF;
2013 END LOOP;
2014 END IF;
2015
2016 -- Standard check of p_commit.
2017 IF FND_API.To_Boolean( p_commit ) THEN
2018 COMMIT WORK;
2019 END IF;
2020
2021 -- Standard call to get message count and IF count is get message info.
2022 FND_MSG_PUB.Count_And_Get
2023 (p_count => x_msg_count,
2024 p_data => x_msg_data
2025 );
2026
2027 csi_t_gen_utility_pvt.add('API Executed : Delete Mass Edit Batches');
2028 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
2029
2030 csi_t_gen_utility_pvt.set_debug_off;
2031
2032 EXCEPTION
2033 WHEN FND_API.G_EXC_ERROR THEN
2034
2035 ROLLBACK TO Delete_Mass_Edit_Batches;
2036 x_return_status := FND_API.G_RET_STS_ERROR ;
2037 FND_MSG_PUB.Count_And_Get (
2038 p_count => x_msg_count,
2039 p_data => x_msg_data);
2040
2041 csi_t_gen_utility_pvt.set_debug_off;
2042
2043 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2044
2045 ROLLBACK TO Delete_Mass_Edit_Batches;
2046 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2047
2048 FND_MSG_PUB.Count_And_Get(
2049 p_count => x_msg_count,
2050 p_data => x_msg_data);
2051
2052 csi_t_gen_utility_pvt.set_debug_off;
2053
2054 WHEN OTHERS THEN
2055
2056 ROLLBACK TO Delete_Mass_Edit_Batches;
2057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2058
2059 IF FND_MSG_PUB.Check_Msg_Level(
2060 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2061
2062 FND_MSG_PUB.Add_Exc_Msg(
2063 p_pkg_name => G_PKG_NAME,
2064 p_procedure_name => l_api_name);
2065 END IF;
2066
2067 FND_MSG_PUB.Count_And_Get(
2068 p_count => x_msg_count,
2069 p_data => x_msg_data);
2070
2071 csi_t_gen_utility_pvt.set_debug_off;
2072
2073 END Delete_Mass_Edit_Batches;
2074
2075 /*
2076 This API gets all the transaction line details and also the child records for each of
2077 these line details, for a given mass edit batch.
2078 */
2079
2080 PROCEDURE GET_MASS_EDIT_DETAILS (
2081 p_api_version IN NUMBER,
2082 p_commit IN VARCHAR2 := fnd_api.g_false,
2083 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2084 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2085 px_mass_edit_rec IN OUT NOCOPY mass_edit_rec,
2086 x_txn_line_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl ,
2087 x_txn_party_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
2088 x_txn_pty_acct_detail_tbl OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
2089 x_txn_ext_attrib_vals_tbl OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
2090 x_return_status OUT NOCOPY VARCHAR2,
2091 x_msg_count OUT NOCOPY NUMBER ,
2092 x_msg_data OUT NOCOPY VARCHAR2)
2093 IS
2094
2095 l_api_version NUMBER := 1.0;
2096 l_api_name VARCHAR2(30) := 'GET_MASS_EDIT_DETAILS';
2097 l_msg_count NUMBER;
2098 l_msg_data VARCHAR2(2000);
2099 l_return_status VARCHAR2(1);
2100
2101 BEGIN
2102
2103 -- Standard Start of API savepoint
2104 SAVEPOINT get_mass_edit_details;
2105
2106 csi_t_gen_utility_pvt.add('API Being Executed : GET_MASS_EDIT_DETAILS');
2107 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
2108
2109 -- Initialize message list if p_init_msg_list is set to TRUE.
2110 IF FND_API.To_Boolean( p_init_msg_list ) THEN
2111 FND_MSG_PUB.Initialize;
2112 END IF;
2113
2114 -- Initialize API return status to success
2115 x_return_status := FND_API.G_RET_STS_SUCCESS;
2116
2117 -- Standard call to check for call compatibility.
2118 IF NOT
2119 FND_API.Compatible_API_Call (
2120 p_current_version_number => l_api_version,
2121 p_caller_version_number => p_api_version,
2122 p_api_name => l_api_name,
2123 p_pkg_name => g_pkg_name) THEN
2124
2125 RAISE FND_API.G_Exc_Unexpected_Error;
2126 END IF;
2127 -- This procedure check if the installed base is active, If not active
2128 -- populates the error message in the message queue and raises the
2129 -- fnd_api.g_exc_error exception
2130 --
2131 csi_utility_grp.check_ib_active;
2132
2133 -- check required params
2134 -- Currently the Get queries txn details for a given source transaction id (batch). So a unique
2135 -- identifier like the batch id/txn line ID OR the batch name is required for a Get
2136
2137 IF ( nvl(px_mass_edit_rec.entry_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
2138 AND nvl(px_mass_edit_rec.name, fnd_api.g_miss_char) = fnd_api.g_miss_char
2139 AND nvl(px_mass_edit_rec.txn_line_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
2140 ) THEN
2141 FND_MESSAGE.set_name('CSI','CSI_API_REQD_PARAM_MISSING');
2142 FND_MESSAGE.set_token('API_NAME',l_api_name);
2143 FND_MESSAGE.set_token('MISSING_PARAM','Batch ID / Batch Name / Transaction Line ID');
2144 FND_MSG_PUB.add;
2145 RAISE FND_API.g_exc_error;
2146 END IF;
2147
2148 csi_mass_edit_pvt.get_mass_edit_details (
2149 p_api_version => p_api_version,
2150 p_commit => p_commit,
2151 p_init_msg_list => p_init_msg_list,
2152 p_validation_level => p_validation_level,
2153 px_mass_edit_rec => px_mass_edit_rec,
2154 x_txn_line_detail_tbl => x_txn_line_detail_tbl,
2155 x_txn_party_detail_tbl => x_txn_party_detail_tbl,
2156 x_txn_pty_acct_detail_tbl => x_txn_pty_acct_detail_tbl,
2157 x_txn_ext_attrib_vals_tbl => x_txn_ext_attrib_vals_tbl,
2158 x_return_status => l_return_status,
2159 x_msg_count => l_msg_count,
2160 x_msg_data => l_msg_data
2161 );
2162
2163 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2164 RAISE fnd_api.g_exc_error;
2165 END IF;
2166
2167 -- Standard check of p_commit.
2168 IF FND_API.To_Boolean( p_commit ) THEN
2169 COMMIT WORK;
2170 END IF;
2171
2172 -- Standard call to get message count and IF count is get message info.
2173 FND_MSG_PUB.Count_And_Get
2174 (p_count => x_msg_count,
2175 p_data => x_msg_data
2176 );
2177
2178 csi_t_gen_utility_pvt.add('API Executed : Get Mass Edit Details');
2179 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
2180
2181 csi_t_gen_utility_pvt.set_debug_off;
2182
2183 EXCEPTION
2184 WHEN FND_API.G_EXC_ERROR THEN
2185
2186 ROLLBACK TO Get_mass_edit_details;
2187 x_return_status := FND_API.G_RET_STS_ERROR ;
2188 FND_MSG_PUB.Count_And_Get (
2189 p_count => x_msg_count,
2190 p_data => x_msg_data);
2191
2192 csi_t_gen_utility_pvt.set_debug_off;
2193
2194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2195
2196 ROLLBACK TO Get_mass_edit_details;
2197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2198
2199 FND_MSG_PUB.Count_And_Get(
2200 p_count => x_msg_count,
2201 p_data => x_msg_data);
2202
2203 csi_t_gen_utility_pvt.set_debug_off;
2204
2205 WHEN OTHERS THEN
2206
2207 ROLLBACK TO Get_mass_edit_details;
2208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2209
2210 IF FND_MSG_PUB.Check_Msg_Level(
2211 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2212
2213 FND_MSG_PUB.Add_Exc_Msg(
2214 p_pkg_name => G_PKG_NAME,
2215 p_procedure_name => l_api_name);
2216
2217 END IF;
2218
2219 FND_MSG_PUB.Count_And_Get(
2220 p_count => x_msg_count,
2221 p_data => x_msg_data);
2222
2223 csi_t_gen_utility_pvt.set_debug_off;
2224
2225 END Get_mass_edit_details;
2226
2227 /*----------------------------------------------------*/
2228 /* Procedure name: PROCESS_SYSTEM_MASS_UPDATE */
2229 /* Description : procedure used to update System in */
2230 /* mass update batch */
2231 /*----------------------------------------------------*/
2232
2233 PROCEDURE PROCESS_SYSTEM_MASS_UPDATE
2234 (
2235 p_api_version IN NUMBER
2236 ,p_commit IN VARCHAR2 := fnd_api.g_false
2237 ,p_entry_id IN NUMBER
2238 ,p_instance_tbl IN OUT NOCOPY csi_datastructures_pub.instance_tbl
2239 ,p_ext_attrib_values_tbl IN OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl
2240 ,p_party_tbl IN OUT NOCOPY csi_datastructures_pub.party_tbl
2241 ,p_account_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
2242 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
2243 ,x_return_status OUT NOCOPY VARCHAR2
2244 ,x_msg_count OUT NOCOPY NUMBER
2245 ,x_msg_data OUT NOCOPY VARCHAR2
2246 )
2247 IS
2248
2249 CURSOR mu_new_party_csr(p_transaction_line_id NUMBER) IS
2250 SELECT tp.party_source_table PartySourceTable,
2251 tp.party_source_id PartyId,
2252 ta.account_id AccountId,
2253 party.party_number PartyNumber
2254 FROM csi_t_txn_line_details tld,
2255 csi_t_party_details tp,
2256 csi_t_party_accounts ta,
2257 hz_parties party,
2258 hz_cust_accounts account
2259 WHERE tld.txn_line_detail_id = tp.txn_line_detail_id
2260 AND tld.transaction_line_id = p_transaction_line_id
2261 AND tld.INSTANCE_ID IS NULL
2262 AND tp.txn_party_detail_id = ta.txn_party_detail_id
2263 AND tp.party_source_id = party.party_id
2264 AND ta.account_id = account.cust_account_id
2265 AND tp.relationship_type_code = 'OWNER'
2266 AND tp.party_source_table = 'HZ_PARTIES';
2267 -- Bug 7350165
2268 -- CURSOR mu_customer_id_csr (p_party_id IN NUMBER, p_account_number IN NUMBER) IS
2269 -- SELECT CUST_ACCOUNT_ID CUSTOMER_ID
2270 -- FROM HZ_CUST_ACCOUNTS
2271 -- WHERE PARTY_ID = p_party_id
2272 -- AND ACCOUNT_NUMBER = p_account_number;
2273
2274 CURSOR mu_system_csr (p_system_id IN NUMBER) IS
2275 SELECT SYSTEM_TYPE_CODE,
2276 SYSTEM_NUMBER,
2277 PARENT_SYSTEM_ID,
2278 COTERMINATE_DAY_MONTH,
2279 START_DATE_ACTIVE,
2280 END_DATE_ACTIVE,
2281 CONTEXT,
2282 ATTRIBUTE1,
2283 ATTRIBUTE2,
2284 ATTRIBUTE3,
2285 ATTRIBUTE4,
2286 ATTRIBUTE5,
2287 ATTRIBUTE6,
2288 ATTRIBUTE7,
2289 ATTRIBUTE8,
2290 ATTRIBUTE9,
2291 ATTRIBUTE10,
2292 ATTRIBUTE11,
2293 ATTRIBUTE12,
2294 ATTRIBUTE13,
2295 ATTRIBUTE14,
2296 ATTRIBUTE15,
2297 OBJECT_VERSION_NUMBER,
2298 OPERATING_UNIT_ID,
2299 PROGRAM_APPLICATION_ID,
2300 PROGRAM_ID
2301 FROM CSI_SYSTEMS_B
2302 WHERE SYSTEM_ID = p_system_id;
2303
2304 CURSOR mu_system_tl_csr (p_system_id IN NUMBER) IS
2305 SELECT NAME,
2306 DESCRIPTION
2307 FROM CSI_SYSTEMS_TL
2308 WHERE SYSTEM_ID = p_system_id;
2309
2310
2311
2312 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_SYSTEM_MASS_UPDATE';
2313 l_api_version CONSTANT NUMBER := 1.0;
2314 l_debug_level NUMBER;
2315 l_txn_line_id NUMBER;
2316
2317 upd_system_tbl csi_datastructures_pub.mu_systems_tbl;
2318 l_txn_sys_rec csi_datastructures_pub.system_rec;
2319 l_t_txn_sys_rec csi_t_datastructures_grp.txn_system_rec;
2320 l_mu_new_party_rec mu_new_party_csr%ROWTYPE;
2321 --l_mu_customer_id_rec mu_customer_id_csr%ROWTYPE; -- 7350165
2322 l_mu_system_rec mu_system_csr%ROWTYPE;
2323 l_mu_system_tl_rec mu_system_tl_csr%ROWTYPE;
2324
2325
2326 l_msg_count NUMBER;
2327 l_msg_data VARCHAR2(2000);
2328 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2329
2330 BEGIN
2331
2332 -- Standard Start of API savepoint
2333 SAVEPOINT PROCESS_SYSTEM_MASS_UPDATE;
2334
2335 debug('Inside PROCESS_SYSTEM_MASS_UPDATE');
2336
2337 csi_utility_grp.check_ib_active;
2338
2339 -- Standard call to check for call compatibility.
2340 IF NOT FND_API.Compatible_API_Call (l_api_version,
2341 p_api_version,
2342 l_api_name ,
2343 G_PKG_NAME )
2344 THEN
2345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2346 END IF;
2347
2348 -- Initialize API return status to success
2349 x_return_status := FND_API.G_RET_STS_SUCCESS;
2350
2351 -- Check the profile option debug_level for debug message reporting
2352 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2353
2354 -- If debug_level = 1 then dump the procedure name
2355 IF (l_debug_level > 0) THEN
2356 csi_gen_utility_pvt.put_line( 'PROCESS_SYSTEM_MASS_UPDATE');
2357 END IF;
2358
2359 debug('Starting System Cascade for Entry ID - ' || p_entry_id);
2360 -- Identifying systems
2361 -- Fetching the transaction line id
2362 BEGIN
2363 SELECT cmee.txn_line_id
2364 INTO l_txn_line_id
2365 FROM csi_mass_edit_entries_vl cmee,
2366 csi_lookups clkps
2367 WHERE cmee.entry_id = p_entry_id
2368 --AND cmee.status_code = 'SCHEDULED'
2369 AND cmee.schedule_date <= SYSDATE
2370 AND clkps.lookup_type = 'CSI_IB_TXN_TYPE_CODE'
2371 AND clkps.lookup_code = cmee.batch_type;
2372
2373 EXCEPTION
2374 WHEN NO_DATA_FOUND THEN
2375 debug('No data Found while fetching txn line id for system cascade');
2376 RAISE FND_API.G_EXC_ERROR;
2377 WHEN OTHERS THEN
2378 debug('Others Exception while fetching txn line id for system cascade');
2379 RAISE FND_API.G_EXC_ERROR;
2380 END;
2381 debug('l_txn_line_id - ' || l_txn_line_id);
2382
2383 IDENTIFY_SYSTEM_FOR_UPDATE (
2384 p_txn_line_id => l_txn_line_id
2385 ,p_upd_system_tbl => upd_system_tbl
2386 ,x_return_status => l_return_status);
2387
2388 IF NOT l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2389 RAISE FND_API.G_EXC_ERROR;
2390 END IF;
2391
2392 IF upd_system_tbl.COUNT > 0 THEN
2393
2394 -- Validations of the identified systems
2395 VALIDATE_SYSTEM_BATCH (
2396 p_entry_id => p_entry_id
2397 ,p_txn_line_id => l_txn_line_id
2398 ,p_upd_system_tbl => upd_system_tbl
2399 ,x_return_status => l_return_status);
2400
2401 IF NOT l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2402 RAISE FND_API.G_EXC_ERROR;
2403 END IF;
2404
2405 -- System update
2406 -- Fetch the new Party Information (Party Id, Account Id)
2407 BEGIN
2408 OPEN mu_new_party_csr (l_txn_line_id);
2409 FETCH mu_new_party_csr INTO l_mu_new_party_rec;
2410 CLOSE mu_new_party_csr;
2411 EXCEPTION
2412 WHEN OTHERS THEN
2413 debug('In to Others Exception while finding new party information');
2414 debug( to_char(SQLCODE)||substr(SQLERRM, 1, 255));
2415 RAISE FND_API.G_EXC_ERROR;
2416 END;
2417
2418 -- Fetch Customer id from party id and account id
2419 /* BEGIN
2420 OPEN mu_customer_id_csr (l_mu_new_party_rec.PartyId, l_mu_new_party_rec.AccountId);
2421 FETCH mu_customer_id_csr INTO l_mu_customer_id_rec;
2422 CLOSE mu_customer_id_csr;
2423 EXCEPTION
2424 WHEN NO_DATA_FOUND THEN
2425 fnd_message.set_name('CSI', 'CSI_CUSTOMER_ID_NOT_FOUND');
2426 fnd_message.set_token('PARTY_ID',l_mu_new_party_rec.PartyId);
2427 fnd_message.set_token('ACCOUNT_NUMBER',l_mu_new_party_rec.AccountId);
2428 fnd_msg_pub.add;
2429 RAISE FND_API.G_EXC_ERROR;
2430
2431 WHEN TOO_MANY_ROWS THEN
2432 fnd_message.set_name('CSI', 'CSI_MULTIPLE_CUSTOMER_ID_FOUND');
2433 fnd_message.set_token('PARTY_ID',l_mu_new_party_rec.PartyId);
2434 fnd_message.set_token('ACCOUNT_NUMBER',l_mu_new_party_rec.AccountId);
2435 fnd_msg_pub.add;
2436 RAISE FND_API.G_EXC_ERROR;
2437 WHEN OTHERS THEN
2438 debug('In to Others Exception while finding customer id');
2439 RAISE FND_API.G_EXC_ERROR;
2440 END;
2441 */
2442 FOR system_rec_ind IN upd_system_tbl.FIRST .. upd_system_tbl.LAST
2443 LOOP
2444 l_txn_sys_rec.SYSTEM_ID := upd_system_tbl(system_rec_ind).SYSTEM_ID;
2445 -- Fix for the bug 7350165
2446 l_txn_sys_rec.CUSTOMER_ID := l_mu_new_party_rec.AccountId;
2447 --l_txn_sys_rec.CUSTOMER_ID := l_mu_customer_id_rec.CUSTOMER_ID;
2448 l_txn_sys_rec.REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
2449
2450 -- Assign null values to the contact ids, site use ids
2451 -- TODO New enhancement has to be made at the mass update page
2452 -- to capture contact ids, site use id and that information
2453 -- should be populated here
2454
2455 -- Important : Also note the base update package has been modified to
2456 -- accept null values update_row_for_mu
2457 l_txn_sys_rec.SHIP_TO_CONTACT_ID := FND_API.G_MISS_NUM;
2458 l_txn_sys_rec.BILL_TO_CONTACT_ID := FND_API.G_MISS_NUM;
2459 l_txn_sys_rec.TECHNICAL_CONTACT_ID := FND_API.G_MISS_NUM;
2460 l_txn_sys_rec.SERVICE_ADMIN_CONTACT_ID := FND_API.G_MISS_NUM;
2461 l_txn_sys_rec.SHIP_TO_SITE_USE_ID := FND_API.G_MISS_NUM;
2462 l_txn_sys_rec.BILL_TO_SITE_USE_ID := FND_API.G_MISS_NUM;
2463 l_txn_sys_rec.INSTALL_SITE_USE_ID := FND_API.G_MISS_NUM;
2464
2465 -- Fetch the existing values of the system
2466 -- Other information which are non related to the system party owner
2467 -- is retained
2468 OPEN mu_system_csr (upd_system_tbl(system_rec_ind).SYSTEM_ID);
2469 FETCH mu_system_csr INTO l_mu_system_rec;
2470 CLOSE mu_system_csr;
2471
2472 OPEN mu_system_tl_csr (upd_system_tbl(system_rec_ind).SYSTEM_ID);
2473 FETCH mu_system_tl_csr INTO l_mu_system_tl_rec;
2474 CLOSE mu_system_tl_csr;
2475
2476 -- retaining the existing values of the system
2477 l_txn_sys_rec.SYSTEM_TYPE_CODE := l_mu_system_rec.SYSTEM_TYPE_CODE;
2478 l_txn_sys_rec.SYSTEM_NUMBER := l_mu_system_rec.SYSTEM_NUMBER;
2479 l_txn_sys_rec.PARENT_SYSTEM_ID := l_mu_system_rec.PARENT_SYSTEM_ID;
2480 l_txn_sys_rec.COTERMINATE_DAY_MONTH := l_mu_system_rec.COTERMINATE_DAY_MONTH;
2481 l_txn_sys_rec.START_DATE_ACTIVE := l_mu_system_rec.START_DATE_ACTIVE;
2482 l_txn_sys_rec.END_DATE_ACTIVE := l_mu_system_rec.END_DATE_ACTIVE;
2483 l_txn_sys_rec.CONTEXT := l_mu_system_rec.CONTEXT;
2484 l_txn_sys_rec.NAME := l_mu_system_tl_rec.NAME;
2485 l_txn_sys_rec.DESCRIPTION := l_mu_system_tl_rec.DESCRIPTION;
2486 l_txn_sys_rec.ATTRIBUTE1 := l_mu_system_rec.ATTRIBUTE1;
2487 l_txn_sys_rec.ATTRIBUTE2 := l_mu_system_rec.ATTRIBUTE2;
2488 l_txn_sys_rec.ATTRIBUTE3 := l_mu_system_rec.ATTRIBUTE3;
2489 l_txn_sys_rec.ATTRIBUTE4 := l_mu_system_rec.ATTRIBUTE4;
2490 l_txn_sys_rec.ATTRIBUTE5 := l_mu_system_rec.ATTRIBUTE5;
2491 l_txn_sys_rec.ATTRIBUTE6 := l_mu_system_rec.ATTRIBUTE6;
2492 l_txn_sys_rec.ATTRIBUTE7 := l_mu_system_rec.ATTRIBUTE7;
2493 l_txn_sys_rec.ATTRIBUTE8 := l_mu_system_rec.ATTRIBUTE8;
2494 l_txn_sys_rec.ATTRIBUTE9 := l_mu_system_rec.ATTRIBUTE9;
2495 l_txn_sys_rec.ATTRIBUTE10 := l_mu_system_rec.ATTRIBUTE10;
2496 l_txn_sys_rec.ATTRIBUTE11 := l_mu_system_rec.ATTRIBUTE11;
2497 l_txn_sys_rec.ATTRIBUTE12 := l_mu_system_rec.ATTRIBUTE12;
2498 l_txn_sys_rec.ATTRIBUTE13 := l_mu_system_rec.ATTRIBUTE13;
2499 l_txn_sys_rec.ATTRIBUTE14 := l_mu_system_rec.ATTRIBUTE14;
2500 l_txn_sys_rec.ATTRIBUTE15 := l_mu_system_rec.ATTRIBUTE15;
2501 l_txn_sys_rec.OBJECT_VERSION_NUMBER := l_mu_system_rec.OBJECT_VERSION_NUMBER;
2502 l_txn_sys_rec.OPERATING_UNIT_ID := l_mu_system_rec.OPERATING_UNIT_ID;
2503 l_txn_sys_rec.PROGRAM_APPLICATION_ID := l_mu_system_rec.PROGRAM_APPLICATION_ID;
2504 l_txn_sys_rec.PROGRAM_ID := l_mu_system_rec.PROGRAM_ID;
2505
2506 -- To Create System transaction lines in CSI_T_TXN_SYSTEMS
2507 -- Construct system transaction record
2508 -- This transaction record is not really used for MU TR processing, but
2509 -- transactions are created for any future enhacements/tracking
2510 --
2511 l_t_txn_sys_rec.TRANSACTION_LINE_ID := l_txn_line_id;
2512 l_t_txn_sys_rec.SYSTEM_NAME := l_mu_system_tl_rec.NAME;
2513 l_t_txn_sys_rec.DESCRIPTION := l_mu_system_tl_rec.DESCRIPTION;
2514 l_t_txn_sys_rec.SYSTEM_TYPE_CODE := l_mu_system_rec.SYSTEM_TYPE_CODE;
2515 l_t_txn_sys_rec.SYSTEM_NUMBER := l_mu_system_rec.SYSTEM_NUMBER;
2516 -- Bug 7350165
2517 l_t_txn_sys_rec.CUSTOMER_ID := l_mu_new_party_rec.AccountId;
2518 --l_t_txn_sys_rec.CUSTOMER_ID := l_mu_customer_id_rec.CUSTOMER_ID;
2519 l_t_txn_sys_rec.BILL_TO_CONTACT_ID := FND_API.G_MISS_NUM;
2520 l_t_txn_sys_rec.SHIP_TO_CONTACT_ID := FND_API.G_MISS_NUM;
2521 l_t_txn_sys_rec.TECHNICAL_CONTACT_ID := FND_API.G_MISS_NUM;
2522 l_t_txn_sys_rec.SERVICE_ADMIN_CONTACT_ID := FND_API.G_MISS_NUM;
2523 l_t_txn_sys_rec.SHIP_TO_SITE_USE_ID := FND_API.G_MISS_NUM;
2524 l_t_txn_sys_rec.BILL_TO_SITE_USE_ID := FND_API.G_MISS_NUM;
2525 l_t_txn_sys_rec.INSTALL_SITE_USE_ID := FND_API.G_MISS_NUM;
2526 l_t_txn_sys_rec.COTERMINATE_DAY_MONTH := l_mu_system_rec.COTERMINATE_DAY_MONTH;
2527 l_t_txn_sys_rec.START_DATE_ACTIVE := l_mu_system_rec.START_DATE_ACTIVE;
2528 l_t_txn_sys_rec.END_DATE_ACTIVE := l_mu_system_rec.END_DATE_ACTIVE;
2529 l_t_txn_sys_rec.CONTEXT := l_txn_line_id;
2530 l_t_txn_sys_rec.ATTRIBUTE1 := l_mu_system_rec.ATTRIBUTE1;
2531 l_t_txn_sys_rec.ATTRIBUTE2 := l_mu_system_rec.ATTRIBUTE2;
2532 l_t_txn_sys_rec.ATTRIBUTE3 := l_mu_system_rec.ATTRIBUTE3;
2533 l_t_txn_sys_rec.ATTRIBUTE4 := l_mu_system_rec.ATTRIBUTE4;
2534 l_t_txn_sys_rec.ATTRIBUTE5 := l_mu_system_rec.ATTRIBUTE5;
2535 l_t_txn_sys_rec.ATTRIBUTE6 := l_mu_system_rec.ATTRIBUTE6;
2536 l_t_txn_sys_rec.ATTRIBUTE7 := l_mu_system_rec.ATTRIBUTE7;
2537 l_t_txn_sys_rec.ATTRIBUTE8 := l_mu_system_rec.ATTRIBUTE8;
2538 l_t_txn_sys_rec.ATTRIBUTE9 := l_mu_system_rec.ATTRIBUTE9;
2539 l_t_txn_sys_rec.ATTRIBUTE10 := l_mu_system_rec.ATTRIBUTE10;
2540 l_t_txn_sys_rec.ATTRIBUTE11 := l_mu_system_rec.ATTRIBUTE11;
2541 l_t_txn_sys_rec.ATTRIBUTE12 := l_mu_system_rec.ATTRIBUTE12;
2542 l_t_txn_sys_rec.ATTRIBUTE13 := l_mu_system_rec.ATTRIBUTE13;
2543 l_t_txn_sys_rec.ATTRIBUTE14 := l_mu_system_rec.ATTRIBUTE14;
2544 l_t_txn_sys_rec.ATTRIBUTE15 := l_mu_system_rec.ATTRIBUTE15;
2545 l_t_txn_sys_rec.OBJECT_VERSION_NUMBER := l_mu_system_rec.OBJECT_VERSION_NUMBER;
2546
2547 debug('Creating system t transaction');
2548 csi_t_txn_systems_pvt.create_txn_system(
2549 p_api_version => 1.0,
2550 p_commit => FND_API.G_FALSE,
2551 p_init_msg_list => FND_API.G_FALSE,
2552 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2553 p_txn_system_rec => l_t_txn_sys_rec,
2554 x_txn_system_id => upd_system_tbl(system_rec_ind).SYSTEM_ID,
2555 x_return_status => l_return_status,
2556 x_msg_count => l_msg_count,
2557 x_msg_data => l_msg_data
2558 );
2559
2560 debug('Calling UPDATE_SYSTEM');
2561 -- Calling CSI_SYSTEMS_PVT to update the system
2562 -- with l_txn_sys_rec as the system parameter
2563 CSI_SYSTEMS_PVT.UPDATE_SYSTEM(
2564 p_api_version => 1.0,
2565 p_commit => FND_API.G_FALSE,
2566 p_init_msg_list => FND_API.G_FALSE,
2567 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2568 p_system_rec => l_txn_sys_rec,
2569 p_txn_rec => p_txn_rec,
2570 x_return_status => l_return_status,
2571 x_msg_count => l_msg_count,
2572 x_msg_data => l_msg_data);
2573
2574 IF NOT l_return_status = fnd_api.g_ret_sts_success
2575 THEN
2576 debug('Error updating systems in PROCESS_SYSTEM_MASS_UPDATE - System ID - ' || upd_system_tbl(system_rec_ind).SYSTEM_ID);
2577 RAISE FND_API.G_EXC_ERROR;
2578 END IF;
2579
2580 END LOOP; -- upd_system_tbl
2581
2582 ELSE -- upd_system_tbl.COUNT > 0
2583 x_return_status := fnd_api.g_ret_sts_success;
2584 END IF; -- upd_system_tbl.COUNT > 0
2585
2586 EXCEPTION
2587 WHEN FND_API.G_EXC_ERROR THEN
2588 ROLLBACK TO PROCESS_SYSTEM_MASS_UPDATE;
2589 x_return_status := FND_API.G_RET_STS_ERROR ;
2590 FND_MSG_PUB.Count_And_Get
2591 ( p_count => x_msg_count,
2592 p_data => x_msg_data
2593 );
2594
2595 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2596 ROLLBACK TO PROCESS_SYSTEM_MASS_UPDATE;
2597 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2598 FND_MSG_PUB.Count_And_Get
2599 ( p_count => x_msg_count,
2600 p_data => x_msg_data
2601 );
2602
2603 WHEN OTHERS THEN
2604 ROLLBACK TO PROCESS_SYSTEM_MASS_UPDATE;
2605 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2606 IF FND_MSG_PUB.Check_Msg_Level
2607 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2608 THEN
2609 FND_MSG_PUB.Add_Exc_Msg
2610 ( G_PKG_NAME ,
2611 l_api_name
2612 );
2613 END IF;
2614 FND_MSG_PUB.Count_And_Get
2615 ( p_count => x_msg_count,
2616 p_data => x_msg_data
2617 );
2618 END PROCESS_SYSTEM_MASS_UPDATE; -- PROCESS_SYSTEM_MASS_UPDATE
2619
2620 /*----------------------------------------------------*/
2621 /* Procedure name: IDENTIFY_SYSTEM_FOR_UPDATE */
2622 /* Description : procedure used to identifies System for */
2623 /* mass update batch */
2624 /*----------------------------------------------------*/
2625 PROCEDURE IDENTIFY_SYSTEM_FOR_UPDATE
2626 (
2627 p_txn_line_id IN NUMBER
2628 ,p_upd_system_tbl OUT NOCOPY csi_datastructures_pub.mu_systems_tbl
2629 ,x_return_status OUT NOCOPY VARCHAR2
2630 )
2631 IS
2632
2633 -- Cursors
2634 CURSOR distinct_system_cur (txn_line_id IN NUMBER) IS
2635 SELECT distinct cii.system_id system_id
2636 FROM csi_t_txn_line_details ctld,
2637 csi_item_instances cii
2638 WHERE ctld.transaction_line_id = txn_line_id
2639 AND ctld.INSTANCE_ID is not null
2640 AND ctld.preserve_detail_flag = 'Y'
2641 AND cii.instance_id = ctld.instance_id;
2642
2643 l_active_instance_count NUMBER := 0;
2644 l_mu_instance_count NUMBER := 0;
2645 l_sys_ind NUMBER := 0;
2646 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2647
2648
2649 BEGIN
2650
2651 debug('Inside IDENTIFY_SYSTEM_FOR_UPDATE');
2652
2653 FOR l_mu_systems_csr IN distinct_system_cur(p_txn_line_id)
2654 LOOP
2655 debug ('System ID - ' || l_mu_systems_csr.system_id);
2656 IF l_mu_systems_csr.system_id IS NOT NULL THEN
2657 -- To get the count of active item instances in the system
2658 SELECT COUNT(1)
2659 INTO l_active_instance_count
2660 FROM csi_item_instances cisv,
2661 CSI_SYSTEMS_B csb
2662 WHERE cisv.SYSTEM_ID = l_mu_systems_csr.system_id
2663 AND cisv.system_id = csb.system_id
2664 AND NVL(cisv.ACTIVE_END_DATE,sysdate+1) > sysdate
2665 AND NVL(csb.END_DATE_ACTIVE,sysdate +1) > sysdate;
2666
2667 debug('Total Active Instance in the System - ' || l_active_instance_count);
2668
2669 -- To get total instances included in the mass update batch belonging to the system
2670 SELECT COUNT(1)
2671 INTO l_mu_instance_count
2672 FROM csi_t_txn_line_details ctld,
2673 csi_item_instances cii
2674 WHERE ctld.transaction_line_id = p_txn_line_id
2675 AND cii.system_id = l_mu_systems_csr.system_id
2676 AND ctld.INSTANCE_ID IS NOT NULL
2677 AND ctld.preserve_detail_flag = 'Y'
2678 AND cii.instance_id = ctld.instance_id;
2679
2680 debug('Total Instances in Batch belonging to System - ' || l_mu_instance_count);
2681
2682 IF l_mu_instance_count > 0 THEN
2683 IF l_mu_instance_count = l_active_instance_count THEN
2684 -- All the active item instances which are part of the system is included in the transfer owner batch
2685 -- Adding the system id to be updated
2686 l_sys_ind := l_sys_ind + 1;
2687 p_upd_system_tbl(l_sys_ind).SYSTEM_ID := l_mu_systems_csr.system_id;
2688 debug('System Qualifying for Mass Update - ' || l_mu_systems_csr.system_id);
2689 debug_out('System Qualifying for Mass Update - ' || l_mu_systems_csr.system_id);
2690
2691 END IF; -- l_mu_instance_count = l_active_instance_count
2692 END IF; -- l_mu_instance_count > 0
2693 END IF; -- l_mu_systems_csr.system_id IS NOT NULL
2694 END LOOP; -- distinct_system_cur(p_txn_line_id)
2695
2696 debug('Total Number of Systems to be updated - ' || p_upd_system_tbl.COUNT);
2697 debug_out('Total Number of Systems to be updated - ' || p_upd_system_tbl.COUNT);
2698 x_return_status := FND_API.G_RET_STS_SUCCESS;
2699 debug('End IDENTIFY_SYSTEM_FOR_UPDATE');
2700 EXCEPTION
2701 WHEN OTHERS THEN
2702 debug('Exception in IDENTIFY_SYSTEM_FOR_UPDATE');
2703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2704
2705 END IDENTIFY_SYSTEM_FOR_UPDATE; -- IDENTIFY_SYSTEM_FOR_UPDATE
2706
2707 /*----------------------------------------------------*/
2708 /* Procedure name: VALIDATE_SYSTEM_BATCH */
2709 /* Description : procedure to validate systems before*/
2710 /* before mass update */
2711 /*----------------------------------------------------*/
2712 PROCEDURE VALIDATE_SYSTEM_BATCH
2713 (
2714 p_entry_id IN NUMBER
2715 ,p_txn_line_id IN NUMBER
2716 ,p_upd_system_tbl IN csi_datastructures_pub.mu_systems_tbl
2717 ,x_return_status OUT NOCOPY VARCHAR2
2718 )
2719 IS
2720
2721 CURSOR SYS_ACCOUNT_CSR (p_entry_id IN NUMBER) IS
2722 SELECT TA.ACCOUNT_ID ACCOUNT_ID,
2723 TP.PARTY_SOURCE_ID PARTY_ID
2724 FROM CSI_T_PARTY_DETAILS TP,
2725 CSI_T_PARTY_ACCOUNTS TA ,
2726 CSI_T_TXN_LINE_DETAILS CTLD ,
2727 CSI_MASS_EDIT_ENTRIES_VL CMEE
2728 WHERE CMEE.ENTRY_ID = p_entry_id
2729 AND TA.TXN_PARTY_DETAIL_ID = TP.TXN_PARTY_DETAIL_ID
2730 AND TP.TXN_LINE_DETAIL_ID = CTLD.TXN_LINE_DETAIL_ID
2731 AND CTLD.TRANSACTION_LINE_ID = CMEE.TXN_LINE_ID
2732 AND CTLD.INSTANCE_ID IS NOT NULL
2733 AND ROWNUM = 1;
2734
2735 -- Bug 7350165
2736 --CURSOR SYS_CUST_CSR (p_party_id IN NUMBER, p_account_number IN NUMBER) IS
2737 -- SELECT CUST_ACCOUNT_ID CUSTOMER_ID
2738 -- FROM HZ_CUST_ACCOUNTS
2739 -- WHERE PARTY_ID = p_party_id
2740 -- AND ACCOUNT_NUMBER = p_account_number;
2741
2742 -- l_sys_cust_rec SYS_CUST_CSR%ROWTYPE; bug 7350165
2743 l_sys_acct_rec SYS_ACCOUNT_CSR%ROWTYPE;
2744 l_mu_sys_error_tbl csi_mass_edit_pub.mass_edit_sys_error_tbl;
2745 l_errors_found VARCHAR2(1) := 'N';
2746 l_warnings_found VARCHAR2(1) := 'N';
2747
2748 BEGIN
2749
2750 debug('Inside VALIDATE_SYSTEM_BATCH');
2751
2752 -- Validation logic
2753 x_return_status := FND_API.G_RET_STS_SUCCESS;
2754
2755 OPEN SYS_ACCOUNT_CSR (p_entry_id);
2756 FETCH SYS_ACCOUNT_CSR INTO l_sys_acct_rec;
2757 CLOSE SYS_ACCOUNT_CSR;
2758
2759 -- Bug 7350165
2760 -- Fetch Customer id from party id and account id
2761 /* BEGIN
2762 OPEN SYS_CUST_CSR (l_sys_acct_rec.Party_Id, l_sys_acct_rec.Account_Id);
2763 FETCH SYS_CUST_CSR INTO l_sys_cust_rec;
2764 CLOSE SYS_CUST_CSR;
2765 EXCEPTION
2766 WHEN NO_DATA_FOUND THEN
2767 fnd_message.set_name('CSI', 'CSI_CUSTOMER_ID_NOT_FOUND');
2768 fnd_message.set_token('PARTY_ID',l_sys_acct_rec.Party_Id);
2769 fnd_message.set_token('ACCOUNT_NUMBER',l_sys_acct_rec.Account_Id);
2770 fnd_msg_pub.add;
2771 RAISE FND_API.G_EXC_ERROR;
2772
2773 WHEN TOO_MANY_ROWS THEN
2774 fnd_message.set_name('CSI', 'CSI_MULTIPLE_CUSTOMER_ID_FOUND');
2775 fnd_message.set_token('PARTY_ID',l_sys_acct_rec.Party_Id);
2776 fnd_message.set_token('ACCOUNT_NUMBER',l_sys_acct_rec.Account_Id);
2777 fnd_msg_pub.add;
2778 RAISE FND_API.G_EXC_ERROR;
2779 WHEN OTHERS THEN
2780 debug('In to Others Exception while finding customer id');
2781 RAISE FND_API.G_EXC_ERROR;
2782 END;
2783 */
2784 debug('Total Number of Systems qualifying for mass update - ' || p_upd_system_tbl.COUNT);
2785 IF p_upd_system_tbl.COUNT > 0 THEN
2786 FOR system_rec_ind IN p_upd_system_tbl.FIRST .. p_upd_system_tbl.LAST
2787 LOOP
2788
2789 -- Validate the system with the transaction details
2790 -- The validation verify whether the system got updated after the batch has been created
2791 -- Following validations take place
2792 --
2793 -- 1. Whether the System is active or not - VLD_SYSTEM_ACTIVE
2794 -- 2. To check if the current owner of the system matches with the
2795 -- current owner for the batch - VLD_SYSTEM_CURRENT_OWNER
2796 -- 3. To check if the transfer ownership date doesnt exceed the sysdate
2797 -- and system termination date - VLD_SYSTEM_TERM_DATE
2798 -- 4. To check if the system location ids changed after batch was
2799 -- scheduled - VLD_SYSTEM_LOCATION_CHGD
2800
2801 IF NVL(p_upd_system_tbl(system_rec_ind).SYSTEM_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
2802
2803 -- Whether the System is active or not
2804 debug('Executing VLD_SYSTEM_ACTIVE');
2805 CSI_MASS_EDIT_PVT.VLD_SYSTEM_ACTIVE(
2806 p_upd_system_tbl(system_rec_ind).SYSTEM_ID,
2807 p_txn_line_id,
2808 l_mu_sys_error_tbl);
2809
2810 -- Checking current owner
2811 debug('Executing VLD_SYSTEM_CURRENT_OWNER');
2812 CSI_MASS_EDIT_PVT.VLD_SYSTEM_CURRENT_OWNER(
2813 p_upd_system_tbl(system_rec_ind).SYSTEM_ID,
2814 l_sys_acct_rec.Account_Id,
2815 --l_sys_cust_rec.CUSTOMER_ID,
2816 p_txn_line_id,
2817 l_mu_sys_error_tbl);
2818
2819 -- Checking Transfer ownerhips date doesnt exceed system termination date
2820 -- Commented since the system updation happens at sysdate
2821 -- and VLD_SYSTEM_ACTIVE would have checked this condition
2822 /*debug('Executing VLD_SYSTEM_TERM_DATE');
2823 CSI_MASS_EDIT_PVT.VLD_SYSTEM_TERM_DATE(
2824 upd_system_tbl(system_rec_ind).SYSTEM_ID,
2825 l_mu_sys_error_tbl); */
2826
2827 -- Checking whether location id/contact id changed after batch was created
2828 -- This procedure is not implemented for the ER 6031179 as the locations will
2829 -- be cleared. But this is retained for future enhacenments
2830 -- This should check to make sure that location id hasnt changed since
2831 -- the batch was created. If it has changed an error message must be
2832 -- displayed
2833 debug('Executing VLD_SYSTEM_LOCATION_CHGD');
2834 CSI_MASS_EDIT_PVT.VLD_SYSTEM_LOCATION_CHGD(
2835 p_upd_system_tbl(system_rec_ind).SYSTEM_ID,
2836 p_txn_line_id,
2837 l_mu_sys_error_tbl);
2838 END IF; -- NVL(l_system_rec.system_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
2839 END LOOP; -- system_rec_ind IN
2840 END IF; -- p_upd_system_tbl.COUNT
2841
2842 -- Check the Error table
2843 IF l_mu_sys_error_tbl.count > 0 THEN
2844 debug('Total Number of errors after system validation: '||l_mu_sys_error_tbl.count);
2845 FOR f in l_mu_sys_error_tbl.first .. l_mu_sys_error_tbl.last LOOP
2846 IF (l_mu_sys_error_tbl(f).error_code = fnd_api.g_ret_sts_error AND
2847 l_errors_found = 'N') THEN
2848 l_errors_found := 'Y';
2849 debug('Errors found from system validation');
2850 debug('Error message: '||substr(l_mu_sys_error_tbl(f).ERROR_TEXT,1,length(l_mu_sys_error_tbl(f).ERROR_TEXT)));
2851
2852 debug_out('Errors found from system validation');
2853 debug_out('Error message: '||substr(l_mu_sys_error_tbl(f).ERROR_TEXT,1,length(l_mu_sys_error_tbl(f).ERROR_TEXT)));
2854 ELSIF (l_mu_sys_error_tbl(f).error_code = 'W' AND
2855 l_warnings_found = 'N') THEN
2856 l_warnings_found := 'Y';
2857 debug('Warnings found from system validation');
2858 debug('Warning message: '||substr(l_mu_sys_error_tbl(f).ERROR_TEXT,1,length(l_mu_sys_error_tbl(f).ERROR_TEXT)));
2859
2860 debug_out('Warnings found from system validation');
2861 debug_out('Warning message: '||substr(l_mu_sys_error_tbl(f).ERROR_TEXT,1,length(l_mu_sys_error_tbl(f).ERROR_TEXT)));
2862
2863 END IF;
2864 END LOOP;
2865
2866 IF (l_errors_found = 'Y' and l_warnings_found = 'Y' OR
2867 l_errors_found = 'Y' and l_warnings_found = 'N') THEN
2868 debug('Errors found from VALIDATE_SYSTEM_BATCH and raising FND_API.G_EXC_ERROR');
2869 RAISE FND_API.G_EXC_ERROR;
2870 ELSIF (l_errors_found = 'N' and l_warnings_found = 'Y') THEN
2871 x_return_status := 'W';
2872 END IF;
2873 debug('Return Status from VALIDATE_SYSTEM_BATCH: '||x_return_status);
2874 ELSE
2875 x_return_status := fnd_api.g_ret_sts_success;
2876 END IF;
2877
2878 EXCEPTION
2879 WHEN FND_API.G_EXC_ERROR THEN
2880 debug('Encountered FND_API.G_EXC_ERROR in VALIDATE_SYSTEM_BATCH');
2881 x_return_status := FND_API.G_RET_STS_ERROR ;
2882 WHEN OTHERS THEN
2883 debug('Encountered WHEN OTHERS in VALIDATE_SYSTEM_BATCH');
2884 debug( to_char(SQLCODE)||substr(SQLERRM, 1, 255));
2885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2886
2887 END VALIDATE_SYSTEM_BATCH; -- VALIDATE_SYSTEM_BATCH
2888
2889 End CSI_MASS_EDIT_PUB;