[Home] [Help]
PACKAGE BODY: APPS.FUN_OPEN_INTERFACE_PKG
Source
1 PACKAGE BODY FUN_OPEN_INTERFACE_PKG AS
2 /* $Header: funximpb.pls 120.32.12020000.3 2012/08/28 09:45:19 srampure ship $ */
3
4 G_DEBUG VARCHAR2(5);
5
6 PROCEDURE Print
7 (
8 P_string IN VARCHAR2
9 ) IS
10
11 stemp VARCHAR2(80);
12 nlength NUMBER := 1;
13
14 BEGIN
15 -- print only if debgu is set on
16 IF G_DEBUG='Y' THEN
17 WHILE(length(P_string) >= nlength)
18 LOOP
19 stemp := substrb(P_string, nlength, 80);
20 fnd_file.put_line(FND_FILE.LOG, stemp);
21 nlength := (nlength + 80);
22 END LOOP;
23 END IF;
24
25 EXCEPTION
26 WHEN OTHERS THEN
27 APP_EXCEPTION.RAISE_EXCEPTION;
28 END Print;
29
30 PROCEDURE Derive_Batch_Attributes(
31 p_initiator_name IN VARCHAR2,
32 p_from_le_name IN VARCHAR2,
33 p_trx_type_name IN VARCHAR2,
34 x_return_status OUT NOCOPY VARCHAR2,
35 p_initiator_id IN OUT NOCOPY NUMBER,
36 p_from_le_id IN OUT NOCOPY NUMBER,
37 p_from_ledger_id IN OUT NOCOPY NUMBER,
38 p_trx_type_id IN OUT NOCOPY NUMBER,
39 p_trx_type_code IN OUT NOCOPY VARCHAR2
40 ) IS
41
42 l_initiator_id_per_name fun_interface_batches.initiator_id%type;
43 l_From_Le_id_per_name fun_interface_batches.from_le_id%type;
44 l_From_Le_id_per_initiator_id fun_interface_batches.from_le_id%type;
45 l_Trx_type_id_per_name fun_interface_batches.trx_type_id%type;
46 l_Trx_type_id_per_code fun_interface_batches.trx_type_id%type;
47 l_From_Ledger_id_per_le fun_interface_batches.from_ledger_id%type;
48
49 Cursor c_Init_id_per_name(l_initiator_name IN VARCHAR2) IS
50 SELECT p.party_id
51 FROM hz_parties p
52 WHERE p.party_name = l_initiator_name
53 and p.party_type='ORGANIZATION'
54 and exists (select u.party_usg_assignment_id from hz_party_usg_assignments u
55 where u.party_usage_code = 'INTERCOMPANY_ORG'
56 and u.party_id = p.party_id);
57
58 Cursor c_Le_Id_per_name(l_From_le_name IN VARCHAR2) IS
59 SELECT legal_entity_id
60 FROM xle_firstparty_information_v
61 WHERE name=l_from_le_name;
62
63 /*SELECT hp.party_id
64 FROM hz_parties hp, hz_code_assignments hca
65 WHERE hp.party_name = l_From_Le_name
66 and hca.owner_table_name like 'HZ_PARTIES'
67 and hca.owner_table_id = hp.party_id
68 and hca.class_code like 'LEGAL_ENTITY'
69 and hca.class_category like 'LEGAL_FUNCTION';*/
70
71 Cursor c_ledger_id(l_from_le_id IN NUMBER) IS
72 SELECT ledger_id
73 FROM gl_ledger_le_v
74 WHERE legal_entity_id = l_from_le_id
75 AND ledger_category_code = 'PRIMARY';
76
77 Cursor c_trx_type_id_per_name(l_trx_type_name IN VARCHAR2) IS
78 SELECT trx_type_id
79 FROM fun_Trx_types_vl
80 WHERE trx_type_name = l_Trx_type_name;
81
82 Cursor c_trx_type_id_per_code(l_trx_type_code IN VARCHAR2) IS
83 SELECT trx_type_id
84 FROM fun_Trx_types_vl
85 WHERE trx_type_code = l_Trx_type_code;
86
87 Cursor c_trx_type_code_per_id(l_trx_type_id IN VARCHAR2) IS
88 SELECT trx_type_code
89 FROM fun_Trx_types_vl
90 WHERE trx_type_id = l_Trx_type_id;
91
92 Cursor c_from_le_id (l_party_id IN NUMBER) IS --3603338 new cursor
93 SELECT legal_entity_id
94 FROM xle_firstparty_information_v
95 WHERE party_id = l_party_id;
96
97 BEGIN
98
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100
101 --Print('Main Package ~~~'||'Deriving Initiator Id from Initiator Name ');
102 fun_trx_entry_util.log_debug(FND_LOG.LEVEL_PROCEDURE,
103 'FUN_OPEN_INTERFACE_PKG.Derive_Batch_Attributes',
104 'Main Package');
105 -- Derive Initiator Id from Initiator Name if Initiator Id is not populated
106
107 IF (p_initiator_name is not null) THEN
108 --validate initiator name and retrieve initiator id
109
110 open c_init_id_per_name(p_initiator_name);
111 fetch c_init_id_per_name into l_initiator_id_per_name;
112 If (c_init_id_per_name%notfound) then
113 Print('Main Package ~~~'||'No Initiator Exists by this name');
114 close c_init_id_per_name;
115 Raise FND_API.G_EXC_ERROR;
116 Elsif (c_init_id_per_name%rowcount > 1) then
117 Print('Main Package ~~~'||'Multiple Parties with the Initiator name given');
118 close c_init_id_per_name;
119 Raise FND_API.G_EXC_ERROR;
120
121 End If;
122 close c_init_id_per_name;
123
124 IF (p_initiator_id is not null) then
125 If (p_initiator_id <> l_initiator_id_per_name) then
126 Print('Main Package ~~~'||'Initiator Id and Initiator Name are inconsistent');
127 Raise FND_API.G_EXC_ERROR;
128 End if;
129 Else
130 p_initiator_id := l_initiator_id_per_name;
131 End If;
132 END IF;
133
134 Print('Main Package ~~~'||'Deriving From_Le_Id populated from From_Le_Name');
135 -- Derive From_Le_Id populated from From_Le_Name.
136
137 IF (p_From_Le_name is not null) THEN
138 --validate From_Le name and retrieve From_Le_ id
139
140 open c_le_id_per_name(p_from_le_name);
141 fetch c_le_id_per_name into l_from_le_id_per_name;
142 If (c_le_id_per_name%notfound) then
143 Print('Main Package ~~~'||'No Legal Entity Exists by this name');
144 close c_le_id_per_name;
145 Raise FND_API.G_EXC_ERROR;
146 Elsif (c_le_id_per_name%rowcount > 1) then
147 Print('Main Package ~~~'||'Multiple Legal Entities for the name given');
148 close c_le_id_per_name;
149 Raise FND_API.G_EXC_ERROR;
150 End If;
151 close c_le_id_per_name;
152
153 IF (p_From_Le_id is not null) then
154 If (p_From_Le_id <> l_From_Le_id_per_name) then
155 Print('Main Package ~~~'||'From Le Id and From Le Name are inconsistent');
156 Raise FND_API.G_EXC_ERROR;
157 End if;
158
159 Else
160 p_From_Le_id := l_From_Le_id_per_name;
161 End If;
162 END IF;
163
164 l_From_Le_id_per_initiator_id := fun_tca_pkg.get_le_id(p_initiator_id);
165
166
167 Print('Main Package ~~~'||' Initiator Id '||p_initiator_id);
168 /*If ((p_from_le_id is not null) and (l_From_Le_id_per_initiator_id is not null)) then
169 If (p_From_Le_id <> l_From_Le_id_per_initiator_id) then
170 Print('Main Package ~~~'||'From Le Id and Initiator Id '||l_from_le_id_per_initiator_id||' are inconsistent');
171 -- Raise FND_API.G_EXC_ERROR;
172 End if;
173 Else */
174
175 If p_from_le_id is null then
176 If(l_from_le_id_per_initiator_id is not null) then
177 open c_from_le_id ( l_From_Le_id_per_initiator_id );
178 fetch c_from_le_id into p_From_Le_id ;
179 close c_from_le_id;
180 --p_From_Le_id := l_From_Le_id_per_initiator_id; 3603338 added the above three lines and commented this line out
181 End if;
182 End If;
183
184 Print('Main Package ~~~'||'Deriving From_Ledger_Id populated from From_Le_Id');
185 /* Derive From_Ledger_Id from From Le Id*/
186 --3603338 Uncomment ledger_id fetch
187
188 IF (p_From_Le_id is not null) THEN
189 --validate From_Le_Id and retrieve From_Ledger_ id
190
191 OPEN c_ledger_id(p_from_le_id);
192 fetch c_ledger_id into l_from_ledger_id_per_le;
193 If (c_ledger_id%notfound) then
194 Print('Main Package ~~~'||'No Primary Ledger attached to the Legal Entity');
195 close c_ledger_id;
196 Raise FND_API.G_EXC_ERROR;
197 End If;
198 close c_ledger_id;
199 IF (p_From_Ledger_id is not null) then
200 If (p_From_Ledger_id <> l_From_Ledger_id_per_le) then
201 Print('Main Package ~~~'||'From Ledger Id and From Le Id are inconsistent');
202 Raise FND_API.G_EXC_ERROR;
203 End if;
204 Else
205 p_From_Ledger_id := l_From_Ledger_id_per_le;
206 End If;
207
208 END IF;
209
210 -- Uncomment ledger_id fetch */
211 Print('Main Package ~~~'||'Deriving Trx_Type_Id from Trx_Type_Name');
212 -- Derive Trx_Type_Id from Trx_Type_Name
213
214 IF (p_Trx_type_name is not null) THEN
215 --validate Trx_type name and retrieve Trx_type_ id
216
217 OPEN c_trx_type_id_per_name(p_trx_type_name);
218 fetch c_trx_type_id_per_name into l_Trx_type_id_per_name;
219 If (c_trx_type_id_per_name%notfound) then
220 Print('Main Package ~~~'||'Main Package ~~~'||'No Transaction Type with this name');
221 close c_trx_type_id_per_name;
222 Raise FND_API.G_EXC_ERROR;
223 End If;
224 close c_trx_type_id_per_name;
225
226 IF (p_Trx_type_id is not null) then
227 If (p_Trx_type_id <> l_Trx_type_id_per_name) then
228 Print('Main Package ~~~'||'Trx_Type_Id and Trx_Type_Name are inconsistent');
229 Raise FND_API.G_EXC_ERROR;
230 End if;
231 Else
232 p_Trx_type_id := l_Trx_type_id_per_name;
233 End If;
234 END IF;
235
236 Print('Main Package ~~~'||'Deriving Trx_Type_Code from Trx_Type_Id');
237 -- Derive Trx_Type_Code from Trx_Type_Id
238
239 IF (p_Trx_type_code is not null) THEN
240 --validate Trx_type code and retrieve Trx_type_ id
241
242 OPEN c_trx_type_id_per_code(p_trx_type_code);
243 fetch c_trx_type_id_per_code into l_Trx_type_id_per_code;
244 If (c_trx_type_id_per_code%notfound) then
245 Print('Main Package ~~~'||'No Transaction Type with this code');
246 close c_trx_type_id_per_code;
247 Raise FND_API.G_EXC_ERROR;
248 End If;
249 close c_trx_type_id_per_code;
250 Else
251
252 OPEN c_trx_type_code_per_id(p_trx_type_id);
253 fetch c_trx_type_code_per_id into p_trx_type_code;
254 If (c_trx_type_code_per_id%notfound) then
255 Print('Main Package ~~~'||'No Transaction Type with this Id');
256 close c_trx_type_code_per_id;
257 Raise FND_API.G_EXC_ERROR;
258 End If;
259 close c_trx_type_code_per_id;
260 END IF;
261
262 IF ((p_Trx_type_id is not null) and (l_trx_type_id_per_code is not null)) then
263 If (p_Trx_type_id <> l_Trx_type_id_per_code) then
264 Print('Main Package ~~~'||'Trx_Type_Code and Trx_Type_Id are inconsistent');
265 Raise FND_API.G_EXC_ERROR;
266 End if;
267 Else
268 If (l_trx_type_id_per_code is not null) then
269 p_Trx_type_id := l_Trx_type_id_per_code;
270 End If;
271 End If;
272
273 EXCEPTION
274 WHEN FND_API.G_EXC_ERROR THEN
275 x_return_status := FND_API.G_RET_STS_ERROR;
276 WHEN OTHERS THEN
277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
278 END Derive_Batch_Attributes;
279
280
281 PROCEDURE Derive_Transaction_Attributes(
282 p_recipient_name IN VARCHAR2,
283 p_to_le_name IN VARCHAR2,
284 x_return_status OUT NOCOPY VARCHAR2,
285 p_recipient_id IN OUT NOCOPY NUMBER,
286 p_to_ledger_id IN OUT NOCOPY NUMBER,
287 p_to_le_id IN OUT NOCOPY NUMBER
288
289 ) IS
290
291 l_recipient_id_per_name fun_interface_headers.recipient_id%type;
292 l_To_Le_id_per_name fun_interface_headers.to_le_id%type;
293 l_To_Le_id_per_recipient_id fun_interface_headers.to_le_id%type;
294 l_To_Ledger_id_per_le fun_interface_headers.to_ledger_id%type;
295
296 Cursor c_reci_id_per_name(l_recipient_name IN VARCHAR2) IS
297 SELECT p.party_id
298 FROM hz_parties p
299 WHERE p.party_name = l_recipient_name
300 and p.party_type='ORGANIZATION'
301 and exists (select u.party_usg_assignment_id from hz_party_usg_assignments u
302 where u.party_usage_code = 'INTERCOMPANY_ORG'
303 and u.party_id = p.party_id);
304
305
306 Cursor c_Le_Id_per_name(l_to_le_name IN VARCHAR2) IS
307 SELECT legal_entity_id
308 FROM xle_firstparty_information_v
309 WHERE name=l_to_le_name;
310
311
312 /*SELECT hp.party_id
313 FROM hz_parties hp, hz_code_assignments hca
314 WHERE hp.party_name = l_to_Le_name
315 and hca.owner_table_name like 'HZ_PARTIES'
316 and hca.owner_table_id = hp.party_id;
317 and hca.class_code like 'LEGAL_ENTITY'
318 and hca.class_category like 'LEGAL_FUNCTION';*/
319
320 Cursor c_ledger_id(l_to_le_id IN NUMBER) IS
321 SELECT ledger_id
322 FROM gl_ledger_le_v
323 WHERE legal_entity_id = l_to_le_id
324 AND ledger_category_code = 'PRIMARY';
325 Cursor c_from_le_id (l_party_id IN NUMBER) IS --3603338 new cursor
326 SELECT legal_entity_id
327 FROM xle_firstparty_information_v
328 WHERE party_id = l_party_id;
329
330 BEGIN
331
332 x_return_status := FND_API.G_RET_STS_SUCCESS;
333
334 Print('Main Package ~~~'||'Deriving Recipient Id from Recipient Name');
335 fun_trx_entry_util.log_debug(FND_LOG.LEVEL_PROCEDURE,
336 'FUN_OPEN_INTERFACE_PKG.Derive_Transaction_Attributes',
337 'Main Package');
338 -- Derive Recipient Id from Recipient Name if Recipient Id is not populated
339
340 IF (p_recipient_name is not null) THEN
341 --validate recipient name and retrieve recipient id
342 open c_reci_id_per_name(p_recipient_name);
343 fetch c_reci_id_per_name into l_recipient_id_per_name;
344 If (c_reci_id_per_name%notfound) then
345 Print('Main Package ~~~'||'No Recipient Exists by this name');
346 close c_reci_id_per_name;
347 Raise FND_API.G_EXC_ERROR;
348 Elsif (c_reci_id_per_name%rowcount > 1) then
349 Print('Main Package ~~~'||'Multiple Parties with the Recipient name');
350 close c_reci_id_per_name;
351 Raise FND_API.G_EXC_ERROR;
352 End If;
353 close c_reci_id_per_name;
354
355 IF (p_recipient_id is not null) then
356 If (p_recipient_id <> l_recipient_id_per_name) then
357 Print('Recipient Id and Recipient Name are inconsistent');
358 Raise FND_API.G_EXC_ERROR;
359 End if;
360 Else
361 p_recipient_id := l_recipient_id_per_name;
362 End If;
363 END IF;
364
365 Print('Main Package ~~~'||'Derive To_Le_Id populated from To_Le_Name');
366 -- Derive To_Le_Id populated from To_Le_Name.
367
368 IF (p_To_Le_name is not null) THEN
369 --validate To_Le name and retrieve To_Le_ id
370
371 open c_le_id_per_name(p_to_le_name);
372 fetch c_le_id_per_name into l_to_le_id_per_name;
373 If (c_le_id_per_name%notfound) then
374 Print('Main Package ~~~'||'No Legal Entity Exists by this name');
375 close c_le_id_per_name;
376 Raise FND_API.G_EXC_ERROR;
377 Elsif (c_le_id_per_name%rowcount > 1) then
378 Print('Main Package ~~~'||'Multiple Legal Entities for the name given');
379 close c_le_id_per_name;
380 Raise FND_API.G_EXC_ERROR;
381 End If;
382 close c_le_id_per_name;
383 IF (p_To_Le_id is not null) then
384 If (p_To_Le_id <> l_To_Le_id_per_name) then
385 Print('Main Package ~~~'||'To Le Id and To Le Name are inconsistent');
386 Raise FND_API.G_EXC_ERROR;
387 End if;
388 Else
389 p_To_Le_id := l_To_Le_id_per_name;
390 End If;
391 END IF;
392
393 If p_to_le_id is null
394 then
395 l_To_Le_id_per_recipient_id := fun_tca_pkg.get_le_id(p_recipient_id);
396
397 open c_from_le_id(l_To_Le_id_per_recipient_id) ;
398 fetch c_from_le_id into p_To_Le_id;
399 close c_from_le_id ;
400 End If;
401
402 Print('Main Package ~~~'||'Derive To_Ledger_Id from To_Le_Id');
403 /* Derive To_Ledger_Id from To_Le_Id*/
404 --/* Uncomment ledger_id fetch 3603338
405 IF (p_To_Le_id is not null) THEN
406 --validate To_Le_Id and retrieve To_Ledger_ id
407
408 OPEN c_ledger_id(p_to_le_id);
409 fetch c_ledger_id into l_to_ledger_id_per_le;
410 If (c_ledger_id%notfound) then
411 Print('No Primary Ledger attached to the Legal Entity');
412 close c_ledger_id;
413 Raise FND_API.G_EXC_ERROR;
414 End If;
415 close c_ledger_id;
416
417 IF (p_To_Ledger_id is not null) then
418 If (p_To_Ledger_id <> l_To_Ledger_id_per_le) then
419 Print('To Ledger Id and To Le Id are inconsistent');
420 --to be changed Raise FND_API.G_EXC_ERROR;
421 End if;
422 Else
423 p_To_Ledger_id := l_To_Ledger_id_per_le;
424 End If;
425 END IF;
426 --/* Uncomment ledger_id fetch 3603338
427 EXCEPTION
428 WHEN FND_API.G_EXC_ERROR THEN
429 x_return_status := FND_API.G_RET_STS_ERROR;
430 WHEN OTHERS THEN
431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
432 END Derive_Transaction_Attributes;
433
434
435 PROCEDURE MAIN
436 (
437 p_errbuff OUT NOCOPY VARCHAR2,
438 p_retcode OUT NOCOPY NUMBER,
439 p_source IN VARCHAR2,
440 p_group_id IN NUMBER,
441 p_batch_id IN NUMBER,
442 p_import_transaction_as_sent IN VARCHAR2 ,
443 p_rejected_only IN VARCHAR2 ,
444 p_debug IN VARCHAR2
445 )
446 IS
447
448
449
450 Cursor c_head(l_batch_id IN NUMBER) is
451 Select * from fun_interface_headers
452 Where batch_id = l_batch_id;
453
454 Cursor c_dist_lines(l_trx_id IN NUMBER) is
455 Select * from fun_interface_dist_lines
456 Where trx_id = l_trx_id;
457
458 Cursor c_batch_dist (l_batch_id IN NUMBER) is
459 Select * from fun_interface_batchdists
460 Where batch_id = l_batch_id;
461
462 Cursor c_inv_flag(c_trx_type_id IN NUMBER ) IS
463 Select allow_invoicing_flag
464 from fun_trx_types_vl
465 where trx_type_id = c_trx_type_id;
466
467 Curr_batch fun_interface_batches%rowtype;
468 Curr_head fun_interface_headers%rowtype;
469 Curr_dist_line fun_interface_dist_lines%rowtype;
470 Curr_batch_dist fun_interface_batchdists%rowtype;
471
472 Overall_status varchar2(1);
473
474 l_return_status varchar2(1);
475 l_msg_data varchar2(2000);
476 l_msg_count number;
477
478
479 l_parameter_list_out wf_parameter_list_t default null;
480
481 l_event_key Varchar2(240);
482
483 l_batch_rec FUN_TRX_PUB.FULL_BATCH_REC_TYPE;
484 l_trx_tbl FUN_TRX_PUB.FULL_TRX_TBL_TYPE;
485 l_init_dist_tbl FUN_TRX_PUB.FULL_INIT_DIST_TBL_TYPE;
486 l_dist_lines_tbl FUN_TRX_PUB.FULL_DIST_LINE_TBL_TYPE;
487
488 l_count number;
489 l_count_lines number;
490
491 l_validation_level number;
492
493 v_request_id number;
494
495 l_reject_allow_flag varchar2(1);
496 l_invoice_flag varchar2(1);
497 l_exchg_rate_type varchar2(30);
498 l_default_currency varchar2(15);
499 l_running_total_dr number;
500 l_running_total_cr number;
501 l_batch_num varchar2(20);
502 l_numbering_type varchar2(30);
503 l_batch_count number;
504 l_control_date_tbl fun_seq.control_date_tbl_type;
505 l_seq_version_id NUMBER;
506 l_assignment_id NUMBER;
507 l_error_code VARCHAR2(30);
508 l_user_id NUMBER;
509 l_grantee_key VARCHAR2(30);
510
511 BATCH_NOT_FOUND Exception;
512
513 BEGIN
514
515 SELECT hzp.party_id
516 INTO l_user_id
517 FROM hz_parties hzp,
518 fnd_user u,
519 per_all_people_f pap,
520 (SELECT fnd_global.user_id() AS
521 user_id
522 FROM dual)
523 curr
524 WHERE curr.user_id = u.user_id
525 AND u.employee_id = pap.person_id
526 AND pap.party_id = hzp.party_id
527 AND SYSDATE between u.start_date AND nvl(u.end_date,sysdate) --bug 10026066
528 AND SYSDATE between pap.effective_start_date AND pap.effective_end_date; --bug #10021420
529
530 l_grantee_key := 'HZ_PARTY:' || to_char(l_user_id);
531
532
533
534 --removed default on p_generate_accounting as it is not used
535 Print('Main Package ~~~'||'Start of Import Program');
536
537 fun_trx_entry_util.log_debug(FND_LOG.LEVEL_PROCEDURE,
538 'FUN_OPEN_INTERFACE_PKG.Main',
539 'Main Package');
540 -- Set the debug flag
541 G_DEBUG := p_debug;
542
543 Print('Main Package ~~~'||'Deleting any Rejections from previous failed imports');
544
545 -- Bug: 7595873
546 /* Delete any rejections from previous failed imports*/
547 Delete from fun_interface_rejections ftr
548 where ftr.batch_id in(select ftb.batch_id
549 from fun_interface_batches ftb
550 where ftb.source = p_source
551 and ftb.group_id = p_group_id);
552
553
554 /*Set the Overall Status to get the status of one whole logical transaction*/
555 Overall_status := 'A';
556
557 /* If transaction is not Sent then only Minimum Validations are to be performed.
558 Set l_validation_level It is sent as parameter to validation api's. Complete Validations are done if it is
559 set to full. Minimum Validations are done if it is set to 50.
560 */
561
562 If (nvl(p_import_transaction_as_sent,'N') = 'N') then
563 l_validation_level := 50;
564 Else
565 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
566 end if;
567
568 -- Derive reject_allow_flag from System Options
569
570 If(FUN_SYSTEM_OPTIONS_PKG.get_allow_reject = TRUE) THEN
571 l_reject_allow_flag := 'Y';
572 Else
573 l_reject_allow_flag := 'N';
574 End If;
575
576 SELECT exchg_rate_type, default_currency
577 INTO l_exchg_rate_type, l_default_currency
578 FROM fun_system_options;
579
580 /*Select Batches for a given Source and Group.*/
581 Print('Main Package ~~~'||'Reject Allowed Derived from System Options');
582 l_batch_count := 0;
583
584 Print('Main Package ~~~'|| 'Fetch Batches');
585 FOR curr_batch in (
586 SELECT * FROM fun_interface_batches
587 WHERE source = p_source
588 AND group_id = p_group_id
589 AND batch_id = NVL(p_batch_id,batch_id)
590 AND batch_id IN(SELECT DISTINCT fib.batch_id
591 FROM fun_interface_batches fib,
592 hz_parties hzp,
593 fnd_grants fg,
594 fnd_object_instance_sets fois,
595 hz_relationships hzr,
596 hz_org_contacts hc,
597 hz_org_contact_roles hcr
598 WHERE hzp.party_type = 'ORGANIZATION'
599 AND EXISTS
600 (SELECT 1
601 FROM hz_party_usg_assignments hua
602 WHERE hua.party_id = hzp.party_id
603 AND hua.party_usage_code = 'INTERCOMPANY_ORG'
604 AND hua.effective_start_date <= sysdate
605 AND(hua.effective_end_date >= sysdate OR effective_end_date IS NULL))
606 AND fg.parameter1 = to_char(hzp.party_id)
607 AND fg.instance_set_id = fois.instance_set_id
608 AND fois.instance_set_name = 'FUN_TRX_BATCHES_SET'
609 AND hzr.relationship_code = 'CONTACT_OF'
610 AND hzr.relationship_type = 'CONTACT'
611 AND hzr.directional_flag = 'F'
612 AND hzr.subject_table_name = 'HZ_PARTIES'
613 AND hzr.object_table_name = 'HZ_PARTIES'
614 AND hzr.subject_type = 'PERSON'
615 AND hzr.object_id = hzp.party_id
616 AND fg.grantee_key = l_grantee_key
617 AND hzp.party_id = fib.initiator_id --10030555
618 AND hc.party_relationship_id = hzr.relationship_id
619 AND hcr.org_contact_id = hc.org_contact_id
620 AND hcr.role_type = 'INTERCOMPANY_CONTACT_FOR'
621 AND hzr.subject_id = l_user_id
622 AND hzr.status = 'A'))
623 LOOP
624 l_batch_count := l_batch_count + 1;
625 Print('Main Package ~~~'||'Populating Batch Record');
626
627 SELECT numbering_type
628 INTO l_numbering_type
629 FROM fun_system_options;
630
631 IF l_numbering_type = 'SYS' then
632 /* SELECT FUN_SEQ_S1.nextval
633 INTO l_batch_num
634 FROM dual;
635 */
636 l_control_date_tbl := fun_seq.control_date_tbl_type();
637 l_control_date_tbl.extend(1);
638 l_control_date_tbl(1).date_type := 'CREATION_DATE';
639 l_control_date_tbl(1).date_value := sysdate;
640
641 FUN_SEQ.Get_Sequence_Number(
642 p_context_type => 'INTERCOMPANY_BATCH_SOURCE',
643 p_context_value => 'LOCAL',
644 p_application_id => 435,
645 p_table_name => 'FUN_TRX_BATCHES',
646 p_event_code => 'CREATION',
647 p_control_attribute_rec => NULL,
648 p_control_date_tbl => l_control_date_tbl,
649 p_suppress_error => 'N',
650 x_seq_version_id => l_seq_version_id,
651 x_sequence_number => l_batch_num,
652 x_assignment_id => l_assignment_id,
653 x_error_code => l_error_code);
654 ELSE
655 l_batch_num := curr_batch.batch_number;
656 END IF;
657
658 l_batch_rec.batch_id := curr_batch.batch_id;
659 l_batch_rec.batch_number := l_batch_num;
660 l_batch_rec.initiator_id := curr_batch.initiator_id;
661 l_batch_rec.from_le_id := curr_batch.from_le_id;
662 l_batch_rec.from_ledger_id := curr_batch.from_ledger_id;
663 l_batch_rec.control_total := curr_batch.control_total;
664 l_batch_rec.currency_code := NVL(curr_batch.currency_code, l_default_currency);
665 l_batch_rec.exchange_rate_type := NVL(curr_batch.exchange_rate_type, l_exchg_rate_type);
666 l_batch_rec.status := 'NEW';
667 l_batch_rec.description := curr_batch.description;
668 l_batch_rec.trx_type_id := curr_batch.trx_type_id;
669 l_batch_rec.trx_type_code := curr_batch.trx_type_code;
670 l_batch_rec.gl_date := trunc(curr_batch.gl_date);
671 l_batch_rec.batch_date := curr_batch.batch_date;
672 l_batch_rec.reject_allow_flag := l_reject_allow_flag;
673 l_batch_rec.from_recurring_batch_id := curr_batch.from_recurring_batch_id;
674
675 l_batch_rec.attribute1 := curr_batch.attribute1;
676 l_batch_rec.attribute2 := curr_batch.attribute2;
677 l_batch_rec.attribute3 := curr_batch.attribute3;
678 l_batch_rec.attribute4 := curr_batch.attribute4;
679 l_batch_rec.attribute5 := curr_batch.attribute5;
680 l_batch_rec.attribute6 := curr_batch.attribute6;
681 l_batch_rec.attribute7 := curr_batch.attribute7;
682 l_batch_rec.attribute8 := curr_batch.attribute8;
683 l_batch_rec.attribute9 := curr_batch.attribute9;
684 l_batch_rec.attribute10 := curr_batch.attribute10;
685 l_batch_rec.attribute11 := curr_batch.attribute11;
686 l_batch_rec.attribute12 := curr_batch.attribute12;
687 l_batch_rec.attribute13 := curr_batch.attribute13;
688 l_batch_rec.attribute14 := curr_batch.attribute14;
689 l_batch_rec.attribute15 := curr_batch.attribute15;
690 l_batch_rec.attribute_category := curr_batch.attribute_category;
691 l_batch_rec.note := curr_batch.note;
692
693
694 --calculate running total for batch from headers
695 SELECT nvl(sum(init_amount_dr),0), nvl(sum(init_amount_cr),0)
696 INTO l_running_total_dr, l_running_total_cr
697 FROM fun_interface_headers
698 WHERE batch_id = curr_batch.batch_id;
699
700 l_batch_rec.running_total_dr := l_running_total_dr;
701 l_batch_rec.running_total_cr := l_running_total_cr;
702
703 Print('Main Package ~~~'||'Details of batch');
704 Print('-----------------------------------------------------');
705 Print(' Batch_id'||l_batch_rec.batch_id );
706 Print(' Batch Number'||l_batch_rec.batch_number);
707 Print(' Initiator Id'||l_batch_rec.initiator_id);
708 Print(' From Le Id'||l_batch_rec.from_le_id);
709 Print(' Control Total'||l_batch_rec.control_total);
710
711 Print('Main Package ~~~'||' Derive Base Attributes for a Batch ');
712 -- Derive Base Attributes for a Batch
713
714 Derive_Batch_Attributes(
715 x_return_status => l_return_status,
716 p_initiator_id => l_batch_rec.initiator_id,
717 p_initiator_name => curr_batch.initiator_name,
718 p_from_ledger_id => l_batch_rec.from_ledger_id,
719 p_from_le_id => l_batch_rec.from_le_id,
720 p_from_le_name => curr_batch.from_le_name,
721 p_trx_type_id => l_batch_rec.trx_type_id,
722 p_trx_type_name => curr_batch.trx_type_name,
723 p_trx_type_code => l_batch_rec.trx_type_code
724 );
725 Print('Main Package ~~~'||' Update the missing Attributes for a Batch in interface tables');
726 Update fun_interface_batches
727 set initiator_id=l_batch_rec.initiator_id,
728 from_le_id=l_batch_rec.from_le_id,
729 from_ledger_id=l_batch_rec.from_ledger_id,
730 trx_type_id=l_batch_rec.trx_type_id,
731 trx_type_code=l_batch_rec.trx_type_code
732 where batch_id=curr_batch.batch_id;
733
734 --Derive Invoicing Rule
735 Print('Main Package ~~~'||' Derive Invoicing Flag ');
736 open c_inv_flag(l_batch_rec.trx_type_id);
737 fetch c_inv_flag into l_invoice_flag;
738 IF c_inv_flag%NOTFOUND THEN
739 close c_inv_flag;
740
741 Print('Main Package ~~~'||' Trx type ID'||l_batch_rec.trx_type_id);
742 l_return_status:=FND_API.G_RET_STS_ERROR;
743 END IF;
744
745 Print('Main Package ~~~'||' Value'||l_invoice_flag||'Return Staus '|| l_return_status);
746
747 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
748 RAISE FND_API.G_EXC_ERROR;
749 ELSE
750 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
752 END IF;
753 End if;
754 CLOSE c_inv_flag;
755
756
757 l_count := 1;
758 l_count_lines := 1;
759 Print('Main Package ~~~'||' Populate Trx Header Records into l_trx_tbl ');
760 -- Populate l_trx_tbl, l_init_dist_tbl, l_dist_lines_tbl;
761
762 OPEN c_head(curr_batch.batch_id);
763 LOOP
764 FETCH C_HEAD INTO CURR_HEAD;
765 EXIT WHEN C_HEAD%NOTFOUND;
766 l_trx_tbl(l_count).trx_id := curr_head.trx_id;
767 l_trx_tbl(l_count).trx_number := curr_head.trx_number;
768 l_trx_tbl(l_count).recipient_id := curr_head.recipient_id;
769 l_trx_tbl(l_count).to_le_id := curr_head.to_le_id;
770 l_trx_tbl(l_count).to_ledger_id := curr_head.to_ledger_id;
771 l_trx_tbl(l_count).batch_id := curr_head.batch_id;
772 l_trx_tbl(l_count).status := 'NEW';
773 l_trx_tbl(l_count).init_amount_cr := curr_head.init_amount_cr;
774 l_trx_tbl(l_count).init_amount_dr := curr_head.init_amount_dr;
775 l_trx_tbl(l_count).invoice_flag := l_invoice_flag;
776 l_trx_tbl(l_count).from_recurring_trx_id := curr_head.from_recurring_trx_id;
777 l_trx_tbl(l_count).initiator_instance_flag := curr_head.initiator_instance_flag;
778 l_trx_tbl(l_count).recipient_instance_flag := curr_head.recipient_instance_flag;
779 l_trx_tbl(l_count).reci_amount_cr := curr_head.init_amount_dr;
780 l_trx_tbl(l_count).reci_amount_dr := curr_head.init_amount_cr;
781
782 l_trx_tbl(l_count).attribute1 := curr_head.attribute1;
783 l_trx_tbl(l_count).attribute2 := curr_head.attribute2;
784 l_trx_tbl(l_count).attribute3 := curr_head.attribute3;
785 l_trx_tbl(l_count).attribute4 := curr_head.attribute4;
786 l_trx_tbl(l_count).attribute5 := curr_head.attribute5;
787 l_trx_tbl(l_count).attribute6 := curr_head.attribute6;
788 l_trx_tbl(l_count).attribute7 := curr_head.attribute7;
789 l_trx_tbl(l_count).attribute8 := curr_head.attribute8;
790 l_trx_tbl(l_count).attribute9 := curr_head.attribute9;
791 l_trx_tbl(l_count).attribute10 := curr_head.attribute10;
792 l_trx_tbl(l_count).attribute11 := curr_head.attribute11;
793 l_trx_tbl(l_count).attribute12 := curr_head.attribute12;
794 l_trx_tbl(l_count).attribute13 := curr_head.attribute13;
795 l_trx_tbl(l_count).attribute14 := curr_head.attribute14;
796 l_trx_tbl(l_count).attribute15 := curr_head.attribute15;
797 l_trx_tbl(l_count).attribute_category := curr_head.attribute_category;
798 l_trx_tbl(l_count).description := curr_head.description;
799 l_trx_tbl(l_count).initiator_id := l_batch_rec.initiator_id;
800
801
802
803 -- Derive Base Attributes for a Transaction Header
804 Print('Main Package ~~~'||'Derive Base Attributes for a Transaction Header ');
805
806 Derive_Transaction_Attributes(
807 x_return_status => l_return_status,
808 p_recipient_id => l_trx_tbl(l_count).recipient_id,
809 p_recipient_name => curr_head.recipient_name,
810 p_to_ledger_id => l_trx_tbl(l_count).to_ledger_id,
811 p_to_le_id => l_trx_tbl(l_count).to_le_id,
812 p_to_le_name => curr_head.to_le_name
813 );
814
815 Update fun_trx_headers
816 set recipient_id=l_trx_tbl(l_count).recipient_id,
817 to_ledger_id=l_trx_tbl(l_count).to_ledger_id,
818 to_le_id=l_trx_tbl(l_count).to_le_id
819 where trx_id=curr_head.trx_id;
820
821 Print('Main Package ~~~'|| 'Return Status from Transaction Attributes'||l_return_status);
822
823 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
824 RAISE FND_API.G_EXC_ERROR;
825 ELSE
826 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
828 END IF;
829 End if;
830 Print('Main Package ~~~'||' Populating Dist Lines into l_dist_lines_tbl ');
831
832 OPEN c_dist_lines(curr_head.trx_id);
833 LOOP
834 FETCH C_DIST_LINES INTO curr_dist_line;
835 EXIT WHEN C_DIST_LINES%NOTFOUND;
836 l_dist_lines_tbl(l_count_lines).trx_id := curr_dist_line.trx_id;
837 l_dist_lines_tbl(l_count_lines).dist_id := curr_dist_line.dist_id;
838 l_dist_lines_tbl(l_count_lines).party_id := curr_dist_line.party_id;
839 l_dist_lines_tbl(l_count_lines).party_type_flag :=curr_dist_line.party_type_flag;
840 l_dist_lines_tbl(l_count_lines).dist_type_flag :=curr_dist_line.dist_type_flag;
841 l_dist_lines_tbl(l_count_lines).batch_dist_id := curr_dist_line.batch_dist_id;
842 l_dist_lines_tbl(l_count_lines).amount_cr := curr_dist_line.amount_cr;
843 l_dist_lines_tbl(l_count_lines).amount_dr := curr_dist_line.amount_dr;
844 l_dist_lines_tbl(l_count_lines).ccid := curr_dist_line.ccid;
845
846 l_dist_lines_tbl(l_count_lines).description :=curr_dist_line.description;
847 l_dist_lines_tbl(l_count_lines).attribute1 :=curr_dist_line.attribute1;
848 l_dist_lines_tbl(l_count_lines).attribute2 :=curr_dist_line.attribute2;
849 l_dist_lines_tbl(l_count_lines).attribute3 :=curr_dist_line.attribute3;
850 l_dist_lines_tbl(l_count_lines).attribute4 :=curr_dist_line.attribute4;
851 l_dist_lines_tbl(l_count_lines).attribute5 :=curr_dist_line.attribute5;
852 l_dist_lines_tbl(l_count_lines).attribute6 :=curr_dist_line.attribute6;
853 l_dist_lines_tbl(l_count_lines).attribute7 :=curr_dist_line.attribute7;
854 l_dist_lines_tbl(l_count_lines).attribute8 :=curr_dist_line.attribute8;
855 l_dist_lines_tbl(l_count_lines).attribute9 :=curr_dist_line.attribute9;
856 l_dist_lines_tbl(l_count_lines).attribute10 :=curr_dist_line.attribute10;
857 l_dist_lines_tbl(l_count_lines).attribute11 :=curr_dist_line.attribute11;
858 l_dist_lines_tbl(l_count_lines).attribute12 :=curr_dist_line.attribute12;
859 l_dist_lines_tbl(l_count_lines).attribute13 :=curr_dist_line.attribute13;
860 l_dist_lines_tbl(l_count_lines).attribute14 :=curr_dist_line.attribute14;
861 l_dist_lines_tbl(l_count_lines).attribute15 :=curr_dist_line.attribute15;
862 l_dist_lines_tbl(l_count_lines).attribute_category :=curr_dist_line.attribute_category;
863
864 l_count_lines := l_count_lines + 1;
865 END LOOP; -- DIST_LINES CURSOR
866
867 CLOSE C_DIST_LINES;
868 l_count := l_count+1;
869
870 END LOOP; -- TRX HEADERS CURSOR
871 CLOSE C_HEAD;
872
873 Print('Main Package ~~~'||' Populating Batch Dist into l_init_dist_tbl');
874 /* Populating l_init_dist_tbl */
875
876 l_count_lines := 1;
877
878 OPEN c_batch_dist(curr_batch.batch_id);
879 LOOP
880 FETCH C_BATCH_DIST INTO CURR_BATCH_DIST;
881 EXIT WHEN C_BATCH_DIST %NOTFOUND;
882 l_init_dist_tbl(l_count_lines).batch_dist_id := curr_batch_dist.batch_dist_id;
883 l_init_dist_tbl(l_count_lines).line_number := curr_batch_dist.line_number;
884 l_init_dist_tbl(l_count_lines).batch_id := curr_batch_dist.batch_id;
885 l_init_dist_tbl(l_count_lines).ccid := curr_batch_dist.ccid;
886 l_init_dist_tbl(l_count_lines).amount_cr := curr_batch_dist.amount_cr;
887 l_init_dist_tbl(l_count_lines).amount_dr := curr_batch_dist.amount_dr;
888
889
890 l_init_dist_tbl(l_count_lines).attribute1 :=curr_batch_dist.attribute1;
891 l_init_dist_tbl(l_count_lines).attribute2 :=curr_batch_dist.attribute2;
892 l_init_dist_tbl(l_count_lines).attribute3 :=curr_batch_dist.attribute3;
893 l_init_dist_tbl(l_count_lines).attribute4 :=curr_batch_dist.attribute4;
894 l_init_dist_tbl(l_count_lines).attribute5 :=curr_batch_dist.attribute5;
895 l_init_dist_tbl(l_count_lines).attribute6 :=curr_batch_dist.attribute6;
896 l_init_dist_tbl(l_count_lines).attribute7 :=curr_batch_dist.attribute7;
897 l_init_dist_tbl(l_count_lines).attribute8 :=curr_batch_dist.attribute8;
898 l_init_dist_tbl(l_count_lines).attribute9 :=curr_batch_dist.attribute9;
899 l_init_dist_tbl(l_count_lines).attribute10 :=curr_batch_dist.attribute10;
900 l_init_dist_tbl(l_count_lines).attribute11 :=curr_batch_dist.attribute11;
901 l_init_dist_tbl(l_count_lines).attribute12 :=curr_batch_dist.attribute12;
902 l_init_dist_tbl(l_count_lines).attribute13 :=curr_batch_dist.attribute13;
903 l_init_dist_tbl(l_count_lines).attribute14 :=curr_batch_dist.attribute14;
904 l_init_dist_tbl(l_count_lines).attribute15 :=curr_batch_dist.attribute15;
905
906 l_init_dist_tbl(l_count_lines).attribute_category :=curr_batch_dist.attribute_category;
907
908
909 l_count_lines := l_count_lines+1;
910
911 END LOOP; -- BATCH DIST CURSOR
912 CLOSE C_BATCH_DIST;
913 Print('Main Package ~~~'||' Call the Public API to Validate and Insert Intercompany Transactions');
914 ----Call the Public API to Validate and Insert Transactions (Batches,Headers,Lines and Distributions).
915
916 FUN_TRX_PUB.CREATE_BATCH(
917 p_api_version => 1.0,
918 p_init_msg_list => FND_API.G_TRUE,
919 p_validation_level => l_validation_level,
920 x_return_status => l_return_status,
921 x_msg_count => l_msg_count,
922 x_msg_data => l_msg_data,
923 p_sent => nvl(p_import_transaction_as_sent,'N'),
924 p_calling_sequence => 'Intercompany Import Program',
925 p_insert => FND_API.G_TRUE,
926 p_batch_rec => l_batch_rec,
927 p_trx_tbl => l_trx_tbl,
928 p_init_dist_tbl => l_init_dist_tbl,
929 p_dist_lines_tbl => l_dist_lines_tbl,
930 p_debug =>p_debug
931 );
932 Print('Main Package ~~~'||'Validation and Insertion Complete with Status' || l_return_status);
933 /* If l_return_status is Unexpected - Raise Unexpected Error*/
934
935 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
936 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
937 END IF;
938
939 Print('Main Package ~~~ Raise batch.send Business Event');
940 /* Raise a Business Event (Send) */
941
942 If l_return_status = FND_API.G_RET_STS_SUCCESS then
943 Overall_Status := 'A';
944 commit work;
945 else
946 Overall_Status := 'R';
947 end if; ---- Overall Status is Accepted
948
949 /*Set Import_status_code of Batch with Overall Status; */
950 UPDATE fun_interface_batches set import_status_code = Overall_Status
951 Where batch_id = curr_batch.batch_id;
952
953 l_trx_tbl.delete;
954 l_init_dist_tbl.delete;
955 l_dist_lines_tbl.delete;
956 END LOOP; -- BATCHES CURSOR
957
958
959
960 IF l_batch_count = 0 then
961 raise BATCH_NOT_FOUND;
962 END If;
963
964 /* Call the Report with the parameters - Source, Group, p_rejected_only */
965 Print('Main Package ~~~'||'Invoking the Import Execution Report');
966 v_request_id := FND_REQUEST.SUBMIT_REQUEST('FUN',
967 'FUNIMPER',
968 '',
969 '',
970 FALSE,
971 p_group_id,
972 nvl(p_rejected_only,'Y'),
973 p_source
974 );
975
976 /*update control table with the request id */
977
978 update fun_interface_controls
979 set request_id = v_request_id,
980 date_processed = sysdate
981 where source = p_source
982 and group_id = p_group_id;
983
984
985 p_errbuff := NULL;
986 p_retcode := 0;
987
988 EXCEPTION
989 WHEN FND_API.G_EXC_ERROR THEN
990 IF c_head%ISOPEN Then
991 Close c_head;
992 END IF;
993 IF c_dist_lines%ISOPEN Then
994 Close c_dist_lines;
995 END IF;
996
997 IF c_batch_dist %ISOPEN Then
998 Close c_batch_dist;
999 END IF;
1000 p_errbuff := 'FND_API.G_EXC_ERROR';
1001 p_retcode := 2;
1002 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1003
1004 IF c_head%ISOPEN Then
1005 Close c_head;
1006 END IF;
1007
1008 IF c_dist_lines%ISOPEN Then
1009 Close c_dist_lines;
1010 END IF;
1011
1012 IF c_batch_dist %ISOPEN Then
1013 Close c_batch_dist;
1014 END IF;
1015
1016 p_errbuff := 'FND_API.G_EXC_UNEXPECTED_ERROR';
1017 p_retcode := 2;
1018
1019 WHEN BATCH_NOT_FOUND THEN
1020
1021 p_errbuff := 'ERROR: Combination of Source and Group ID not found in Interface Table or user does not have acess to initiator for any batch in the group.';
1022 p_retcode := 2;
1023
1024 WHEN OTHERS THEN
1025
1026 IF c_head%ISOPEN Then
1027 Close c_head;
1028 END IF;
1029 IF c_dist_lines%ISOPEN Then
1030 Close c_dist_lines;
1031 END IF;
1032
1033 IF c_batch_dist%ISOPEN Then
1034 Close c_batch_dist;
1035 END IF;
1036
1037 p_errbuff :=SQLERRM;
1038 p_retcode := 2;
1039
1040 END; --- Main Procedure
1041
1042
1043 ---Procedure to Purge Accepted Records from the Interface Tables
1044
1045 PROCEDURE Purge_Interface_Table
1046 (
1047 p_source IN VARCHAR2,
1048 p_group_id IN VARCHAR2
1049 ) IS
1050
1051 l_count number;
1052
1053 BEGIN
1054
1055 Delete from fun_interface_dist_lines where
1056 Trx_id in (select trx_id from fun_interface_headers where
1057 batch_id in (select batch_id from fun_interface_batches where
1058 source = p_source and group_id = p_group_id and import_status_code = 'A')) ;
1059
1060 Delete from fun_interface_batchdists where
1061 batch_id in (select batch_id from fun_interface_batches where
1062 source = p_source and group_id = p_group_id and import_status_code = 'A');
1063
1064 Delete from fun_interface_headers where
1065 Batch_id in (select batch_id from fun_interface_batches where
1066 source = p_source and group_id = p_group_id and import_status_code = 'A');
1067
1068 Delete from fun_interface_batches where
1069 source = p_source and group_id = p_group_id
1070 and import_status_code = 'A';
1071
1072 -- Bug: 7595873
1073 select count(*) into l_count
1074 from fun_interface_rejections ftr
1075 where ftr.batch_id in(select ftb.batch_id
1076 from fun_interface_batches ftb
1077 where ftb.source = p_source
1078 and ftb.group_id = p_group_id);
1079
1080 if l_count = 0 then
1081 Delete from fun_interface_controls where source = p_source
1082 and group_id = p_group_id;
1083 end if;
1084
1085 EXCEPTION
1086
1087 WHEN OTHERS THEN
1088 APP_EXCEPTION.RAISE_EXCEPTION;
1089 END Purge_Interface_Table;
1090
1091
1092 PROCEDURE purge_interface_tables(p_batch_id IN NUMBER) IS
1093
1094 BEGIN
1095 -- porcedure called to purge the rejected Data only.
1096 DELETE FROM fun_interface_dist_lines
1097 WHERE trx_id IN
1098 (SELECT trx_id
1099 FROM fun_interface_headers
1100 WHERE batch_id = p_batch_id)
1101 ;
1102
1103 DELETE FROM fun_interface_batchdists
1104 WHERE batch_id = p_batch_id;
1105
1106 DELETE FROM fun_interface_headers
1107 WHERE batch_id = p_batch_id;
1108
1109 DELETE FROM fun_interface_batches
1110 WHERE batch_id = p_batch_id
1111 AND import_status_code = 'R';
1112
1113 DELETE FROM fun_interface_rejections
1114 where batch_id=p_batch_id;
1115
1116 EXCEPTION
1117
1118 WHEN others THEN
1119 app_exception.raise_exception;
1120
1121 END purge_interface_tables;
1122
1123 PROCEDURE clob_to_file(p_xml_clob IN CLOB) IS
1124
1125 l_clob_size NUMBER;
1126 l_offset NUMBER;
1127 l_chunk_size INTEGER;
1128 l_chunk VARCHAR2(32767);
1129 l_log_module VARCHAR2(240);
1130
1131 BEGIN
1132 -- Procedure to write teh xml data from clob to file.
1133 l_clob_size := dbms_lob.getlength(p_xml_clob);
1134
1135 IF(l_clob_size = 0) THEN
1136 RETURN;
1137 END IF;
1138
1139 l_offset := 1;
1140 l_chunk_size := 3000;
1141
1142 WHILE(l_clob_size > 0)
1143 LOOP
1144 l_chunk := dbms_lob.SUBSTR(p_xml_clob, l_chunk_size, l_offset);
1145 fnd_file.put(which => fnd_file.OUTPUT, buff => l_chunk);
1146
1147 l_clob_size := l_clob_size -l_chunk_size;
1148 l_offset := l_offset + l_chunk_size;
1149 END LOOP;
1150
1151 fnd_file.new_line(fnd_file.OUTPUT, 1);
1152
1153 EXCEPTION
1154 WHEN others THEN
1155 app_exception.raise_exception;
1156
1157 END clob_to_file;
1158
1159 PROCEDURE put_starttag(tag_name IN VARCHAR2) IS
1160 BEGIN
1161 -- putting the start tag for the xml data
1162 fnd_file.PUT_LINE(fnd_file.OUTPUT, '<' || tag_name || '>');
1163 --fnd_file.new_line(fnd_file.output,1);
1164
1165 EXCEPTION
1166
1167 WHEN others THEN
1168 app_exception.raise_exception;
1169
1170 END;
1171
1172 PROCEDURE put_endtag(tag_name IN VARCHAR2) IS
1173 BEGIN
1174 -- putting the end tag for the xml data
1175 fnd_file.PUT_LINE(fnd_file.OUTPUT, '</' || tag_name || '>');
1176 --fnd_file.new_line(fnd_file.output,1);
1177
1178 EXCEPTION
1179
1180 WHEN others THEN
1181 app_exception.raise_exception;
1182
1183 END;
1184
1185 PROCEDURE put_element(tag_name IN VARCHAR2, VALUE IN VARCHAR2) IS
1186 BEGIN
1187 -- putting the start tag +element+end tag for the xml data
1188 fnd_file.put(fnd_file.OUTPUT, '<' || tag_name || '>');
1189 fnd_file.put(fnd_file.OUTPUT, '<![CDATA[');
1190 fnd_file.put(fnd_file.OUTPUT, VALUE);
1191 fnd_file.put(fnd_file.OUTPUT, ']]>');
1192 fnd_file.PUT_LINE(fnd_file.OUTPUT, '</' || tag_name || '>');
1193
1194 EXCEPTION
1195
1196 WHEN others THEN
1197 app_exception.raise_exception;
1198
1199 END;
1200
1201 PROCEDURE import_data_purge(errbuf OUT nocopy VARCHAR2, retcode OUT nocopy NUMBER, p_source IN VARCHAR2, p_group_id IN NUMBER DEFAULT NULL, p_review_required IN VARCHAR2) IS
1202
1203 l_qryctx dbms_xmlgen.ctxhandle;
1204 l_result_clob CLOB;
1205 l_current_calling_sequence VARCHAR2(2000);
1206 l_debug_info VARCHAR2(200);
1207
1208 l_report_name VARCHAR2(80) := 'Intercompany Import Data Purge Report';
1209 l_curr_batch_count NUMBER;
1210 l_batch_count NUMBER;
1211 l_total_batch_count NUMBER;
1212 l_trx_count NUMBER;
1213 l_temp_batch_count NUMBER;
1214 l_temp_trx_count NUMBER;
1215 l_encoding VARCHAR2(20);
1216 l_grantee_key VARCHAR2(80);
1217 l_user_id NUMBER;
1218
1219 BEGIN
1220 --This procedure selects the batches for purging and calls the purge api.
1221 l_current_calling_sequence := 'FUN_OPEN_INTERFACE_PKG.import_data_purge';
1222 l_debug_info := 'Group id info..';
1223
1224 l_total_batch_count := 0;
1225 -- xml encoding data fetch
1226 SELECT tag
1227 INTO l_encoding
1228 FROM fnd_lookup_values
1229 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
1230 AND lookup_code =
1231 (SELECT VALUE
1232 FROM v$nls_parameters
1233 WHERE parameter = 'NLS_CHARACTERSET')
1234 AND LANGUAGE = 'US';
1235
1236 --setting the bind variables
1237 -- Bug: 13652693
1238 SELECT hzp.party_id
1239 INTO l_user_id
1240 FROM hz_parties hzp,
1241 fnd_user u,
1242 per_all_people_f pap,
1243 (SELECT fnd_global.user_id() AS
1244 user_id
1245 FROM dual)
1246 curr
1247 WHERE curr.user_id = u.user_id
1248 AND u.employee_id = pap.person_id
1249 AND pap.party_id = hzp.party_id
1250 AND trunc(SYSDATE) between trunc(u.start_date) AND trunc(nvl(u.end_date, sysdate))
1251 AND SYSDATE between pap.effective_start_date AND pap.effective_end_date; --bug #10021420
1252 l_grantee_key := 'HZ_PARTY:' || to_char(l_user_id);
1253
1254 --xml header tags
1255 put_starttag('?xml version="1.0" encoding="' || l_encoding || '"?');
1256 put_starttag('PURGE_REJECTED_INTERCOMPANY_TRANSACTIONS_REPORT');
1257 put_starttag('PROGRAM_PARAMETERS');
1258 put_element('BATCH_SOURCE', p_source);
1259 put_element('GROUP_ID', p_group_id);
1260 put_element('REVIEW_FLAG', p_review_required);
1261 put_endtag('PROGRAM_PARAMETERS');
1262
1263 l_debug_info := 'Select Group ids and Batches...';
1264
1265 put_starttag('GROUP_ID_SET');
1266
1267 --grouping by group_id
1268 FOR rec IN
1269 (SELECT DISTINCT group_id
1270 FROM fun_interface_batches
1271 WHERE source = p_source
1272 AND(decode(p_group_id, NULL, 1, group_id)) =(decode(p_group_id, NULL, 1, p_group_id))
1273 AND import_status_code = 'R')
1274 LOOP
1275 l_batch_count := 0;
1276 put_starttag('GROUP_ID_RECORD');
1277
1278 put_element('GROUP_ID', rec.group_id);
1279
1280 --grouping by the currency_code
1281 FOR rec2 IN
1282 (SELECT DISTINCT currency_code
1283 FROM fun_interface_batches
1284 WHERE group_id = rec.group_id
1285 AND import_status_code = 'R')
1286 LOOP
1287 put_starttag('CURRENCY_CODE_RECORD');
1288 l_curr_batch_count := 0;
1289 put_element('CURRENCY_CODE', rec2.currency_code);
1290
1291 --grouping by the Legal Entity Name
1292 FOR rec3 IN
1293 (SELECT DISTINCT from_le_name
1294 FROM fun_interface_batches
1295 WHERE group_id = rec.group_id
1296 AND currency_code = rec2.currency_code
1297 AND import_status_code = 'R')
1298 LOOP
1299 put_starttag('FROM_LE_NAME_RECORD');
1300
1301 put_element('FROM_LE_NAME', rec3.from_le_name);
1302 put_element('GROUP_ID', rec.group_id);
1303
1304 --Batch Record generation
1305 l_qryctx := dbms_xmlgen.newcontext('SELECT FIB.GROUP_ID GROUP_ID,
1306 FIB.CURRENCY_CODE CURRENCY_CODE,
1307 FIB.FROM_LE_NAME FROM_LE_NAME,
1308 FIB.FROM_LE_ID FROM_LE_ID,
1309 FIB.INITIATOR_NAME INITIATOR_NAME,
1310 FIB.INITIATOR_ID INITIATOR_ID,
1311 FIB.BATCH_NUMBER BATCH_NUMBER,
1312 FIB.BATCH_DATE BATCH_DATE,
1313 FIH.RECIPIENT_NAME RECIPIENT_NAME,
1314 DECODE(FIH.INIT_AMOUNT_CR,NULL,0,FIH.INIT_AMOUNT_CR) INIT_AMOUNT_CR,
1315 DECODE(FIH.INIT_AMOUNT_DR,NULL,0,FIH.INIT_AMOUNT_DR) INIT_AMOUNT_DR
1316 FROM FUN_INTERFACE_BATCHES FIB,
1317 FUN_INTERFACE_HEADERS FIH,
1318 HZ_PARTIES HZP,
1319 FND_GRANTS FG,
1320 FND_OBJECT_INSTANCE_SETS FOIS,
1321 HZ_RELATIONSHIPS HZR,
1322 HZ_ORG_CONTACTS HC,
1323 HZ_ORG_CONTACT_ROLES HCR
1324 WHERE FIB.BATCH_ID=FIH.BATCH_ID
1325 AND FIB.GROUP_ID=:GROUP_ID
1326 AND FIB.IMPORT_STATUS_CODE=''R''
1327 AND FIB.CURRENCY_CODE=:CURRENCY_CODE
1328 AND FIB.FROM_LE_NAME=:FROM_LE_NAME
1329 AND HZP.PARTY_TYPE = ''ORGANIZATION''
1330 AND EXISTS
1331 (SELECT 1
1332 FROM HZ_PARTY_USG_ASSIGNMENTS HUA
1333 WHERE HUA.PARTY_ID = HZP.PARTY_ID
1334 AND HUA.PARTY_USAGE_CODE = ''INTERCOMPANY_ORG''
1335 AND HUA.EFFECTIVE_START_DATE <= SYSDATE
1336 AND(HUA.EFFECTIVE_END_DATE >= SYSDATE OR EFFECTIVE_END_DATE IS NULL))
1337 AND FG.PARAMETER1 = TO_CHAR(HZP.PARTY_ID)
1338 AND FG.INSTANCE_SET_ID = FOIS.INSTANCE_SET_ID
1339 AND FOIS.INSTANCE_SET_NAME = ''FUN_TRX_BATCHES_SET''
1340 AND FG.GRANTEE_KEY = :GRANTEE_KEY
1341 AND HZR.RELATIONSHIP_CODE = ''CONTACT_OF''
1342 AND HZR.RELATIONSHIP_TYPE = ''CONTACT''
1343 AND HZR.DIRECTIONAL_FLAG = ''F''
1344 AND HZR.SUBJECT_TABLE_NAME = ''HZ_PARTIES''
1345 AND HZR.OBJECT_TABLE_NAME = ''HZ_PARTIES''
1346 AND HZR.SUBJECT_TYPE = ''PERSON''
1347 AND HZR.OBJECT_ID=HZP.PARTY_ID
1348 AND HZP.PARTY_NAME = FIB.INITIATOR_NAME
1349 AND HC.PARTY_RELATIONSHIP_ID = HZR.RELATIONSHIP_ID
1350 AND HCR.ORG_CONTACT_ID = HC.ORG_CONTACT_ID
1351 AND HCR.ROLE_TYPE = ''INTERCOMPANY_CONTACT_FOR''
1352 AND HZR.SUBJECT_ID = :SUBJECT_ID
1353 AND HZR.STATUS = ''A''');
1354
1355 dbms_xmlgen.setrowsettag(l_qryctx, 'BATCH_SET');
1356 dbms_xmlgen.setrowtag(l_qryctx, 'BATCH_RECORD');
1357 dbms_xmlgen.setbindvalue(l_qryctx, 'GROUP_ID', rec.group_id);
1358 dbms_xmlgen.setbindvalue(l_qryctx, 'CURRENCY_CODE', rec2.currency_code);
1359 dbms_xmlgen.setbindvalue(l_qryctx, 'FROM_LE_NAME', rec3.from_le_name);
1360 dbms_xmlgen.setbindvalue(l_qryctx, 'GRANTEE_KEY', l_grantee_key);
1361 dbms_xmlgen.setbindvalue(l_qryctx, 'SUBJECT_ID', l_user_id);
1362 l_result_clob := dbms_xmlgen.getxml(l_qryctx);
1363 l_result_clob := SUBSTR(l_result_clob, instr(l_result_clob, '>') + 1);
1364 dbms_xmlgen.closecontext(l_qryctx);
1365 clob_to_file(l_result_clob);
1366 l_temp_batch_count := 0;
1367
1368 --selecting teh batches to be purged.
1369 FOR rec4 IN
1370 (SELECT DISTINCT fib.batch_id
1371 FROM fun_interface_batches fib,
1372 hz_parties hzp,
1373 fnd_grants fg,
1374 fnd_object_instance_sets fois,
1375 hz_relationships hzr,
1376 hz_org_contacts hc,
1377 hz_org_contact_roles hcr
1378 WHERE fib.group_id = rec.group_id
1379 AND fib.import_status_code = 'R'
1380 AND fib.currency_code = rec2.currency_code
1381 AND fib.from_le_name = rec3.from_le_name
1382 AND hzp.party_type = 'ORGANIZATION'
1383 AND EXISTS
1384 (SELECT 1
1385 FROM hz_party_usg_assignments hua
1386 WHERE hua.party_id = hzp.party_id
1387 AND hua.party_usage_code = 'INTERCOMPANY_ORG'
1388 AND hua.effective_start_date <= sysdate
1389 AND(hua.effective_end_date >= sysdate OR effective_end_date IS NULL))
1390 AND fg.parameter1 = to_char(hzp.party_id)
1391 AND fg.instance_set_id = fois.instance_set_id
1392 AND fois.instance_set_name = 'FUN_TRX_BATCHES_SET'
1393 AND fg.grantee_key = l_grantee_key
1394 AND hzr.relationship_code = 'CONTACT_OF'
1395 AND hzr.relationship_type = 'CONTACT'
1396 AND hzr.directional_flag = 'F'
1397 AND hzr.subject_table_name = 'HZ_PARTIES'
1398 AND hzr.object_table_name = 'HZ_PARTIES'
1399 AND hzr.subject_type = 'PERSON'
1400 AND hzr.object_id = hzp.party_id
1401 AND hzp.party_name = fib.initiator_name
1402 AND hc.party_relationship_id = hzr.relationship_id
1403 AND hcr.org_contact_id = hc.org_contact_id
1404 AND hcr.role_type = 'INTERCOMPANY_CONTACT_FOR'
1405 AND hzr.subject_id = l_user_id
1406 AND hzr.status = 'A')
1407 LOOP
1408 l_temp_batch_count := l_temp_batch_count + 1;
1409
1410 IF(p_review_required = 'N') THEN
1411 purge_interface_tables(rec4.batch_id);
1412 END IF;
1413
1414 END LOOP;
1415
1416 l_curr_batch_count := l_curr_batch_count + l_temp_batch_count;
1417 put_element('LE_BATCH_COUNT', l_temp_batch_count);
1418 put_endtag('FROM_LE_NAME_RECORD');
1419
1420 END LOOP;
1421
1422 put_element('CURR_BATCH_COUNT', l_curr_batch_count);
1423 put_endtag('CURRENCY_CODE_RECORD');
1424 l_batch_count := l_batch_count + l_curr_batch_count;
1425 END LOOP;
1426
1427 put_element('GROUP_BATCH_COUNT', l_batch_count);
1428 put_endtag('GROUP_ID_RECORD');
1429 l_total_batch_count := l_total_batch_count + l_batch_count;
1430
1431 IF (l_batch_count > 0) and(p_review_required='N') THEN
1432
1433 DELETE FROM fun_interface_controls
1434 WHERE source = p_source
1435 AND group_id = rec.group_id;
1436 END IF;
1437
1438 END LOOP;
1439
1440 put_element('TOTAL_BATCH_COUNT', l_total_batch_count);
1441 put_endtag('GROUP_ID_SET');
1442
1443 put_endtag('PURGE_REJECTED_INTERCOMPANY_TRANSACTIONS_REPORT');
1444
1445 EXCEPTION
1446
1447 WHEN others THEN
1448 fun_util.log_conc_unexp(l_current_calling_sequence, sqlerrm);
1449 app_exception.raise_exception;
1450
1451 END import_data_purge;
1452
1453 END FUN_OPEN_INTERFACE_PKG;