1 PACKAGE BODY FUN_OPEN_INTERFACE_PKG AS
2 /* $Header: funximpb.pls 120.22 2007/12/31 18:46:01 shnaraya 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_import_transaction_as_sent IN VARCHAR2 ,
442 p_rejected_only IN VARCHAR2 ,
443 p_debug IN VARCHAR2
444 )
445 IS
446
447 CURSOR c_batches IS
448 SELECT * FROM fun_interface_batches
449 WHERE source = p_source
450 AND group_id = p_group_id;
451
452 Cursor c_head(l_batch_id IN NUMBER) is
453 Select * from fun_interface_headers
454 Where batch_id = l_batch_id;
455
456 Cursor c_dist_lines(l_trx_id IN NUMBER) is
457 Select * from fun_interface_dist_lines
458 Where trx_id = l_trx_id;
459
460 Cursor c_batch_dist (l_batch_id IN NUMBER) is
461 Select * from fun_interface_batchdists
462 Where batch_id = l_batch_id;
463
464 Cursor c_inv_flag(c_trx_type_id IN NUMBER ) IS
465 Select allow_invoicing_flag
466 from fun_trx_types_vl
467 where trx_type_id = c_trx_type_id;
468
469 Curr_batch fun_interface_batches%rowtype;
470 Curr_head fun_interface_headers%rowtype;
471 Curr_dist_line fun_interface_dist_lines%rowtype;
472 Curr_batch_dist fun_interface_batchdists%rowtype;
473
474 Overall_status varchar2(1);
475
476 l_return_status varchar2(1);
477 l_msg_data varchar2(2000);
478 l_msg_count number;
479
480
481 l_parameter_list_out wf_parameter_list_t default null;
482
483 l_event_key Varchar2(240);
484
485 l_batch_rec FUN_TRX_PUB.FULL_BATCH_REC_TYPE;
486 l_trx_tbl FUN_TRX_PUB.FULL_TRX_TBL_TYPE;
487 l_init_dist_tbl FUN_TRX_PUB.FULL_INIT_DIST_TBL_TYPE;
488 l_dist_lines_tbl FUN_TRX_PUB.FULL_DIST_LINE_TBL_TYPE;
489
490 l_count number;
491 l_count_lines number;
492
493 l_validation_level number;
494
495 v_request_id number;
496
497 l_reject_allow_flag varchar2(1);
498 l_invoice_flag varchar2(1);
499 l_exchg_rate_type varchar2(30);
500 l_default_currency varchar2(15);
501 l_running_total_dr number;
502 l_running_total_cr number;
503 l_batch_num varchar2(15);
504 l_numbering_type varchar2(30);
505 l_batch_count number;
506 l_control_date_tbl fun_seq.control_date_tbl_type;
507 l_seq_version_id NUMBER;
508 l_assignment_id NUMBER;
509 l_error_code VARCHAR2(30);
510
511
512 BATCH_NOT_FOUND Exception;
513
514 BEGIN
515 --removed default on p_generate_accounting as it is not used
516 Print('Main Package ~~~'||'Start of Import Program');
517 fun_trx_entry_util.log_debug(FND_LOG.LEVEL_PROCEDURE,
518 'FUN_OPEN_INTERFACE_PKG.Main',
519 'Main Package');
520 -- Set the debug flag
521 G_DEBUG := p_debug;
522
523 Print('Main Package ~~~'||'Deleting any Rejections from previous failed imports');
524
525 /* Delete any rejections from previous failed imports*/
526 Delete from fun_interface_rejections;
527
528
529 /*Set the Overall Status to get the status of one whole logical transaction*/
530 Overall_status := 'A';
531
532 /* If transaction is not Sent then only Minimum Validations are to be performed.
533 Set l_validation_level It is sent as parameter to validation api's. Complete Validations are done if it is
534 set to full. Minimum Validations are done if it is set to 50.
535 */
536
537 If (nvl(p_import_transaction_as_sent,'N') = 'N') then
538 l_validation_level := 50;
539 Else
540 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
541 end if;
542
543 -- Derive reject_allow_flag from System Options
544
545 If(FUN_SYSTEM_OPTIONS_PKG.get_allow_reject = TRUE) THEN
546 l_reject_allow_flag := 'Y';
547 Else
548 l_reject_allow_flag := 'N';
549 End If;
550
551 SELECT exchg_rate_type, default_currency
552 INTO l_exchg_rate_type, l_default_currency
553 FROM fun_system_options;
554
555 /*Select Batches for a given Source and Group.*/
556 Print('Main Package ~~~'||'Reject Allowed Derived from System Options');
557 l_batch_count := 0;
558
559 OPEN C_BATCHES;
560 LOOP
561 FETCH C_BATCHES INTO CURR_BATCH;
562 Print('Main Package ~~~'|| 'Fetch Batches');
563 EXIT WHEN C_BATCHES%NOTFOUND;
564 l_batch_count := l_batch_count + 1;
565 Print('Main Package ~~~'||'Populating Batch Record');
566
567 SELECT numbering_type
568 INTO l_numbering_type
569 FROM fun_system_options;
570
571 IF l_numbering_type = 'SYS' then
572 /* SELECT FUN_SEQ_S1.nextval
573 INTO l_batch_num
574 FROM dual;
575 */
576 l_control_date_tbl := fun_seq.control_date_tbl_type();
577 l_control_date_tbl.extend(1);
578 l_control_date_tbl(1).date_type := 'CREATION_DATE';
579 l_control_date_tbl(1).date_value := sysdate;
580
581 FUN_SEQ.Get_Sequence_Number(
582 p_context_type => 'INTERCOMPANY_BATCH_SOURCE',
583 p_context_value => 'LOCAL',
584 p_application_id => 435,
585 p_table_name => 'FUN_TRX_BATCHES',
586 p_event_code => 'CREATION',
587 p_control_attribute_rec => NULL,
588 p_control_date_tbl => l_control_date_tbl,
589 p_suppress_error => 'N',
590 x_seq_version_id => l_seq_version_id,
591 x_sequence_number => l_batch_num,
592 x_assignment_id => l_assignment_id,
593 x_error_code => l_error_code);
594 ELSE
595 l_batch_num := curr_batch.batch_number;
596 END IF;
597
598 l_batch_rec.batch_id := curr_batch.batch_id;
599 l_batch_rec.batch_number := l_batch_num;
600 l_batch_rec.initiator_id := curr_batch.initiator_id;
601 l_batch_rec.from_le_id := curr_batch.from_le_id;
602 l_batch_rec.from_ledger_id := curr_batch.from_ledger_id;
603 l_batch_rec.control_total := curr_batch.control_total;
604 l_batch_rec.currency_code := NVL(curr_batch.currency_code, l_default_currency);
605 l_batch_rec.exchange_rate_type := NVL(curr_batch.exchange_rate_type, l_exchg_rate_type);
606 l_batch_rec.status := 'NEW';
607 l_batch_rec.description := curr_batch.description;
608 l_batch_rec.trx_type_id := curr_batch.trx_type_id;
609 l_batch_rec.trx_type_code := curr_batch.trx_type_code;
610 l_batch_rec.gl_date := trunc(curr_batch.gl_date);
611 l_batch_rec.batch_date := curr_batch.batch_date;
612 l_batch_rec.reject_allow_flag := l_reject_allow_flag;
613 l_batch_rec.from_recurring_batch_id := curr_batch.from_recurring_batch_id;
614
615 l_batch_rec.attribute1 := curr_batch.attribute1;
616 l_batch_rec.attribute2 := curr_batch.attribute2;
617 l_batch_rec.attribute3 := curr_batch.attribute3;
618 l_batch_rec.attribute4 := curr_batch.attribute4;
619 l_batch_rec.attribute5 := curr_batch.attribute5;
620 l_batch_rec.attribute6 := curr_batch.attribute6;
621 l_batch_rec.attribute7 := curr_batch.attribute7;
622 l_batch_rec.attribute8 := curr_batch.attribute8;
623 l_batch_rec.attribute9 := curr_batch.attribute9;
624 l_batch_rec.attribute10 := curr_batch.attribute10;
625 l_batch_rec.attribute11 := curr_batch.attribute11;
626 l_batch_rec.attribute12 := curr_batch.attribute12;
627 l_batch_rec.attribute13 := curr_batch.attribute13;
628 l_batch_rec.attribute14 := curr_batch.attribute14;
629 l_batch_rec.attribute15 := curr_batch.attribute15;
630 l_batch_rec.attribute_category := curr_batch.attribute_category;
631 l_batch_rec.note := curr_batch.note;
632
633
634 --calculate running total for batch from headers
635 SELECT nvl(sum(init_amount_dr),0), nvl(sum(init_amount_cr),0)
636 INTO l_running_total_dr, l_running_total_cr
637 FROM fun_interface_headers
638 WHERE batch_id = curr_batch.batch_id;
639
640 l_batch_rec.running_total_dr := l_running_total_dr;
641 l_batch_rec.running_total_cr := l_running_total_cr;
642
643 Print('Main Package ~~~'||'Details of batch');
644 Print('-----------------------------------------------------');
645 Print(' Batch_id'||l_batch_rec.batch_id );
646 Print(' Batch Number'||l_batch_rec.batch_number);
647 Print(' Initiator Id'||l_batch_rec.initiator_id);
648 Print(' From Le Id'||l_batch_rec.from_le_id);
649 Print(' Control Total'||l_batch_rec.control_total);
650
651 Print('Main Package ~~~'||' Derive Base Attributes for a Batch ');
652 -- Derive Base Attributes for a Batch
653
654 Derive_Batch_Attributes(
655 x_return_status => l_return_status,
656 p_initiator_id => l_batch_rec.initiator_id,
657 p_initiator_name => curr_batch.initiator_name,
658 p_from_ledger_id => l_batch_rec.from_ledger_id,
659 p_from_le_id => l_batch_rec.from_le_id,
660 p_from_le_name => curr_batch.from_le_name,
661 p_trx_type_id => l_batch_rec.trx_type_id,
662 p_trx_type_name => curr_batch.trx_type_name,
663 p_trx_type_code => l_batch_rec.trx_type_code
664 );
665
666 --Derive Invoicing Rule
667 Print('Main Package ~~~'||' Derive Invoicing Flag ');
668 open c_inv_flag(l_batch_rec.trx_type_id);
669 fetch c_inv_flag into l_invoice_flag;
670 IF c_inv_flag%NOTFOUND THEN
671 close c_inv_flag;
672
673 Print('Main Package ~~~'||' Trx type ID'||l_batch_rec.trx_type_id);
674 l_return_status:=FND_API.G_RET_STS_ERROR;
675 END IF;
676
677 Print('Main Package ~~~'||' Value'||l_invoice_flag||'Return Staus '|| l_return_status);
678
679 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
680 RAISE FND_API.G_EXC_ERROR;
681 ELSE
682 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
683 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
684 END IF;
685 End if;
686 CLOSE c_inv_flag;
687
688
689 l_count := 1;
690 l_count_lines := 1;
691 Print('Main Package ~~~'||' Populate Trx Header Records into l_trx_tbl ');
692 -- Populate l_trx_tbl, l_init_dist_tbl, l_dist_lines_tbl;
693
694 OPEN c_head(curr_batch.batch_id);
695 LOOP
696 FETCH C_HEAD INTO CURR_HEAD;
697 EXIT WHEN C_HEAD%NOTFOUND;
698 l_trx_tbl(l_count).trx_id := curr_head.trx_id;
699 l_trx_tbl(l_count).trx_number := curr_head.trx_number;
700 l_trx_tbl(l_count).recipient_id := curr_head.recipient_id;
701 l_trx_tbl(l_count).to_le_id := curr_head.to_le_id;
702 l_trx_tbl(l_count).to_ledger_id := curr_head.to_ledger_id;
703 l_trx_tbl(l_count).batch_id := curr_head.batch_id;
704 l_trx_tbl(l_count).status := 'NEW';
705 l_trx_tbl(l_count).init_amount_cr := curr_head.init_amount_cr;
706 l_trx_tbl(l_count).init_amount_dr := curr_head.init_amount_dr;
707 l_trx_tbl(l_count).invoice_flag := l_invoice_flag;
708 l_trx_tbl(l_count).from_recurring_trx_id := curr_head.from_recurring_trx_id;
709 l_trx_tbl(l_count).initiator_instance_flag := curr_head.initiator_instance_flag;
710 l_trx_tbl(l_count).recipient_instance_flag := curr_head.recipient_instance_flag;
711 l_trx_tbl(l_count).reci_amount_cr := curr_head.init_amount_dr;
712 l_trx_tbl(l_count).reci_amount_dr := curr_head.init_amount_cr;
713
714 l_trx_tbl(l_count).attribute1 := curr_head.attribute1;
715 l_trx_tbl(l_count).attribute2 := curr_head.attribute2;
716 l_trx_tbl(l_count).attribute3 := curr_head.attribute3;
717 l_trx_tbl(l_count).attribute4 := curr_head.attribute4;
718 l_trx_tbl(l_count).attribute5 := curr_head.attribute5;
719 l_trx_tbl(l_count).attribute6 := curr_head.attribute6;
720 l_trx_tbl(l_count).attribute7 := curr_head.attribute7;
721 l_trx_tbl(l_count).attribute8 := curr_head.attribute8;
722 l_trx_tbl(l_count).attribute9 := curr_head.attribute9;
723 l_trx_tbl(l_count).attribute10 := curr_head.attribute10;
724 l_trx_tbl(l_count).attribute11 := curr_head.attribute11;
725 l_trx_tbl(l_count).attribute12 := curr_head.attribute12;
726 l_trx_tbl(l_count).attribute13 := curr_head.attribute13;
727 l_trx_tbl(l_count).attribute14 := curr_head.attribute14;
728 l_trx_tbl(l_count).attribute15 := curr_head.attribute15;
729 l_trx_tbl(l_count).attribute_category := curr_head.attribute_category;
730 l_trx_tbl(l_count).description := curr_head.description;
731 l_trx_tbl(l_count).initiator_id := l_batch_rec.initiator_id;
732
733
734
735 -- Derive Base Attributes for a Transaction Header
736 Print('Main Package ~~~'||'Derive Base Attributes for a Transaction Header ');
737
738 Derive_Transaction_Attributes(
739 x_return_status => l_return_status,
740 p_recipient_id => l_trx_tbl(l_count).recipient_id,
741 p_recipient_name => curr_head.recipient_name,
742 p_to_ledger_id => l_trx_tbl(l_count).to_ledger_id,
743 p_to_le_id => l_trx_tbl(l_count).to_le_id,
744 p_to_le_name => curr_head.to_le_name
745 );
746
747 Print('Main Package ~~~'|| 'Return Status from Transaction Attributes'||l_return_status);
748
749 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
750 RAISE FND_API.G_EXC_ERROR;
751 ELSE
752 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
753 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
754 END IF;
755 End if;
756 Print('Main Package ~~~'||' Populating Dist Lines into l_dist_lines_tbl ');
757
758 OPEN c_dist_lines(curr_head.trx_id);
759 LOOP
760 FETCH C_DIST_LINES INTO curr_dist_line;
761 EXIT WHEN C_DIST_LINES%NOTFOUND;
762 l_dist_lines_tbl(l_count_lines).trx_id := curr_dist_line.trx_id;
763 l_dist_lines_tbl(l_count_lines).dist_id := curr_dist_line.dist_id;
764 l_dist_lines_tbl(l_count_lines).party_id := curr_dist_line.party_id;
765 l_dist_lines_tbl(l_count_lines).party_type_flag :=curr_dist_line.party_type_flag;
766 l_dist_lines_tbl(l_count_lines).dist_type_flag :=curr_dist_line.dist_type_flag;
767 l_dist_lines_tbl(l_count_lines).batch_dist_id := curr_dist_line.batch_dist_id;
768 l_dist_lines_tbl(l_count_lines).amount_cr := curr_dist_line.amount_cr;
769 l_dist_lines_tbl(l_count_lines).amount_dr := curr_dist_line.amount_dr;
770 l_dist_lines_tbl(l_count_lines).ccid := curr_dist_line.ccid;
771
772 l_dist_lines_tbl(l_count_lines).description :=curr_dist_line.description;
773 l_dist_lines_tbl(l_count_lines).attribute1 :=curr_dist_line.attribute1;
774 l_dist_lines_tbl(l_count_lines).attribute2 :=curr_dist_line.attribute2;
775 l_dist_lines_tbl(l_count_lines).attribute3 :=curr_dist_line.attribute3;
776 l_dist_lines_tbl(l_count_lines).attribute4 :=curr_dist_line.attribute4;
777 l_dist_lines_tbl(l_count_lines).attribute5 :=curr_dist_line.attribute5;
778 l_dist_lines_tbl(l_count_lines).attribute6 :=curr_dist_line.attribute6;
779 l_dist_lines_tbl(l_count_lines).attribute7 :=curr_dist_line.attribute7;
780 l_dist_lines_tbl(l_count_lines).attribute8 :=curr_dist_line.attribute8;
781 l_dist_lines_tbl(l_count_lines).attribute9 :=curr_dist_line.attribute9;
782 l_dist_lines_tbl(l_count_lines).attribute10 :=curr_dist_line.attribute10;
783 l_dist_lines_tbl(l_count_lines).attribute11 :=curr_dist_line.attribute11;
784 l_dist_lines_tbl(l_count_lines).attribute12 :=curr_dist_line.attribute12;
785 l_dist_lines_tbl(l_count_lines).attribute13 :=curr_dist_line.attribute13;
786 l_dist_lines_tbl(l_count_lines).attribute14 :=curr_dist_line.attribute14;
787 l_dist_lines_tbl(l_count_lines).attribute15 :=curr_dist_line.attribute15;
788 l_dist_lines_tbl(l_count_lines).attribute_category :=curr_dist_line.attribute_category;
789
790 l_count_lines := l_count_lines + 1;
791 END LOOP; -- DIST_LINES CURSOR
792
793 CLOSE C_DIST_LINES;
794 l_count := l_count+1;
795
796 END LOOP; -- TRX HEADERS CURSOR
797 CLOSE C_HEAD;
798
799 Print('Main Package ~~~'||' Populating Batch Dist into l_init_dist_tbl');
800 /* Populating l_init_dist_tbl */
801
802 l_count_lines := 1;
803
804 OPEN c_batch_dist(curr_batch.batch_id);
805 LOOP
806 FETCH C_BATCH_DIST INTO CURR_BATCH_DIST;
807 EXIT WHEN C_BATCH_DIST %NOTFOUND;
808 l_init_dist_tbl(l_count_lines).batch_dist_id := curr_batch_dist.batch_dist_id;
809 l_init_dist_tbl(l_count_lines).line_number := curr_batch_dist.line_number;
810 l_init_dist_tbl(l_count_lines).batch_id := curr_batch_dist.batch_id;
811 l_init_dist_tbl(l_count_lines).ccid := curr_batch_dist.ccid;
812 l_init_dist_tbl(l_count_lines).amount_cr := curr_batch_dist.amount_cr;
813 l_init_dist_tbl(l_count_lines).amount_dr := curr_batch_dist.amount_dr;
814
815
816 l_init_dist_tbl(l_count_lines).attribute1 :=curr_batch_dist.attribute1;
817 l_init_dist_tbl(l_count_lines).attribute2 :=curr_batch_dist.attribute2;
818 l_init_dist_tbl(l_count_lines).attribute3 :=curr_batch_dist.attribute3;
819 l_init_dist_tbl(l_count_lines).attribute4 :=curr_batch_dist.attribute4;
820 l_init_dist_tbl(l_count_lines).attribute5 :=curr_batch_dist.attribute5;
821 l_init_dist_tbl(l_count_lines).attribute6 :=curr_batch_dist.attribute6;
822 l_init_dist_tbl(l_count_lines).attribute7 :=curr_batch_dist.attribute7;
823 l_init_dist_tbl(l_count_lines).attribute8 :=curr_batch_dist.attribute8;
824 l_init_dist_tbl(l_count_lines).attribute9 :=curr_batch_dist.attribute9;
825 l_init_dist_tbl(l_count_lines).attribute10 :=curr_batch_dist.attribute10;
826 l_init_dist_tbl(l_count_lines).attribute11 :=curr_batch_dist.attribute11;
827 l_init_dist_tbl(l_count_lines).attribute12 :=curr_batch_dist.attribute12;
828 l_init_dist_tbl(l_count_lines).attribute13 :=curr_batch_dist.attribute13;
829 l_init_dist_tbl(l_count_lines).attribute14 :=curr_batch_dist.attribute14;
830 l_init_dist_tbl(l_count_lines).attribute15 :=curr_batch_dist.attribute15;
831
832 l_init_dist_tbl(l_count_lines).attribute_category :=curr_batch_dist.attribute_category;
833
834
835 l_count_lines := l_count_lines+1;
836
837 END LOOP; -- BATCH DIST CURSOR
838 CLOSE C_BATCH_DIST;
839 Print('Main Package ~~~'||' Call the Public API to Validate and Insert Intercompany Transactions');
840 ----Call the Public API to Validate and Insert Transactions (Batches,Headers,Lines and Distributions).
841
842 FUN_TRX_PUB.CREATE_BATCH(
843 p_api_version => 1.0,
844 p_init_msg_list => FND_API.G_TRUE,
845 p_validation_level => l_validation_level,
846 x_return_status => l_return_status,
847 x_msg_count => l_msg_count,
848 x_msg_data => l_msg_data,
849 p_sent => nvl(p_import_transaction_as_sent,'N'),
850 p_calling_sequence => 'Intercompany Import Program',
851 p_insert => FND_API.G_TRUE,
852 p_batch_rec => l_batch_rec,
853 p_trx_tbl => l_trx_tbl,
854 p_init_dist_tbl => l_init_dist_tbl,
855 p_dist_lines_tbl => l_dist_lines_tbl,
856 p_debug =>p_debug
857 );
858 Print('Main Package ~~~'||'Validation and Insertion Complete with Status' || l_return_status);
859 /* If l_return_status is Unexpected - Raise Unexpected Error*/
860
861 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
863 END IF;
864
865 Print('Main Package ~~~ Raise batch.send Business Event');
866 /* Raise a Business Event (Send) */
867
868 If l_return_status = FND_API.G_RET_STS_SUCCESS then
869 Overall_Status := 'A';
870 commit work;
871 else
872 Overall_Status := 'R';
873 end if; ---- Overall Status is Accepted
874
875 /*Set Import_status_code of Batch with Overall Status; */
876 UPDATE fun_interface_batches set import_status_code = Overall_Status
877 Where batch_id = curr_batch.batch_id;
878
879 l_trx_tbl.delete;
880 l_init_dist_tbl.delete;
881 l_dist_lines_tbl.delete;
882 END LOOP; -- BATCHES CURSOR
883 CLOSE C_BATCHES;
884
885 IF l_batch_count = 0 then
886 raise BATCH_NOT_FOUND;
887 END If;
888
889 /* Call the Report with the parameters - Source, Group, p_rejected_only */
890 Print('Main Package ~~~'||'Invoking the Import Execution Report');
891 v_request_id := FND_REQUEST.SUBMIT_REQUEST('FUN',
892 'FUNIMPER',
893 '',
894 '',
895 FALSE,
896 p_group_id,
897 nvl(p_rejected_only,'Y'),
898 p_source
899 );
900
901 /*update control table with the request id */
902
903 update fun_interface_controls
904 set request_id = v_request_id,
905 date_processed = sysdate
906 where source = p_source
907 and group_id = p_group_id;
908
909
910 p_errbuff := NULL;
911 p_retcode := 0;
912
913 EXCEPTION
914 WHEN FND_API.G_EXC_ERROR THEN
915 IF c_batches%ISOPEN Then
916 Close c_batches;
917 END IF;
918 IF c_head%ISOPEN Then
919 Close c_head;
920 END IF;
921 IF c_dist_lines%ISOPEN Then
922 Close c_dist_lines;
923 END IF;
924
925 IF c_batch_dist %ISOPEN Then
926 Close c_batch_dist;
927 END IF;
928 p_errbuff := 'FND_API.G_EXC_ERROR';
929 p_retcode := 2;
930 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
931 IF c_batches%ISOPEN Then
932 Close c_batches;
933 END IF;
934 IF c_head%ISOPEN Then
935 Close c_head;
936 END IF;
937
938 IF c_dist_lines%ISOPEN Then
939 Close c_dist_lines;
940 END IF;
941
942 IF c_batch_dist %ISOPEN Then
943 Close c_batch_dist;
944 END IF;
945
946 p_errbuff := 'FND_API.G_EXC_UNEXPECTED_ERROR';
947 p_retcode := 2;
948
949 WHEN BATCH_NOT_FOUND THEN
950 IF c_batches%ISOPEN Then
951 Close c_batches;
952 END IF;
953 p_errbuff := 'ERROR: Combination of Source and Group ID not found in Interface Table.';
954 p_retcode := 2;
955
956 WHEN OTHERS THEN
957
958 IF c_batches%ISOPEN Then
959 Close c_batches;
960 END IF;
961 IF c_head%ISOPEN Then
962 Close c_head;
963 END IF;
964 IF c_dist_lines%ISOPEN Then
965 Close c_dist_lines;
966 END IF;
967
968 IF c_batch_dist%ISOPEN Then
969 Close c_batch_dist;
970 END IF;
971
972 p_errbuff :=SQLERRM;
973 p_retcode := 2;
974
975 END; --- Main Procedure
976
977
978 ---Procedure to Purge Accepted Records from the Interface Tables
979
980 PROCEDURE Purge_Interface_Table
981 (
982 p_source IN VARCHAR2,
983 p_group_id IN VARCHAR2
984 ) IS
985
986 l_count number;
987
988 BEGIN
989
990 Delete from fun_interface_dist_lines where
991 Trx_id in (select trx_id from fun_interface_headers where
992 batch_id in (select batch_id from fun_interface_batches where
993 source = p_source and group_id = p_group_id and import_status_code = 'A')) ;
994
995 Delete from fun_interface_batchdists where
996 batch_id in (select batch_id from fun_interface_batches where
997 source = p_source and group_id = p_group_id and import_status_code = 'A');
998
999 Delete from fun_interface_headers where
1000 Batch_id in (select batch_id from fun_interface_batches where
1001 source = p_source and group_id = p_group_id and import_status_code = 'A');
1002
1003 Delete from fun_interface_batches where
1004 source = p_source and group_id = p_group_id
1005 and import_status_code = 'A';
1006
1007 select count(*) into l_count
1008 from fun_interface_rejections;
1009
1010 if l_count = 0 then
1011 Delete from fun_interface_controls where source = p_source
1012 and group_id = p_group_id;
1013 end if;
1014
1015 EXCEPTION
1016
1017 WHEN OTHERS THEN
1018 APP_EXCEPTION.RAISE_EXCEPTION;
1019 END Purge_Interface_Table;
1020
1021 END FUN_OPEN_INTERFACE_PKG;
[Home] [Help]