[Home] [Help]
PACKAGE BODY: APPS.FUN_WEBADI_PKG
Source
1 Package Body FUN_WEBADI_PKG As
2 -- $Header: funwadib.pls 120.25.12020000.2 2012/07/26 23:12:17 shnaraya ship $
3
4 Procedure Insert_Header(
5 p_recipient_name In Fun_Interface_Headers.recipient_name%type,
6 p_to_le_name In Fun_Interface_Headers.to_le_name%type,
7 p_trx_tbl In FUN_TRX_PUB.full_trx_tbl_type) Is
8 Begin
9 If p_trx_tbl.count = 1 Then
10 Insert Into Fun_Interface_Headers(
11 trx_id,
12 trx_number,
13 recipient_id,
14 recipient_name,
15 to_le_id,
16 to_le_name,
17 to_ledger_id,
18 batch_id,
19 init_amount_cr,
20 init_amount_dr,
21 invoicing_rule_flag,
22 from_recurring_trx_id,
23 initiator_instance_flag,
24 recipient_instance_flag,
25 description,
26 attribute1,
27 attribute2,
28 attribute3,
29 attribute4,
30 attribute5,
31 attribute6,
32 attribute7,
33 attribute8,
34 attribute9,
35 attribute10,
36 attribute11,
37 attribute12,
38 attribute13,
39 attribute14,
40 attribute15,
41 attribute_category,
42 created_by,
43 creation_date,
44 last_updated_by,
45 last_update_date,
46 last_update_login,
47 import_status_code)
48 Values(
49 p_trx_tbl(1).trx_id,
50 p_trx_tbl(1).trx_number,
51 p_trx_tbl(1).recipient_id,
52 p_recipient_name,
53 p_trx_tbl(1).to_le_id,
54 p_to_le_name,
55 p_trx_tbl(1).to_ledger_id,
56 p_trx_tbl(1).batch_id,
57 p_trx_tbl(1).init_amount_cr,
58 p_trx_tbl(1).init_amount_dr,
59 p_trx_tbl(1).invoice_flag,
60 p_trx_tbl(1).from_recurring_trx_id,
61 p_trx_tbl(1).initiator_instance_flag,
62 p_trx_tbl(1).recipient_instance_flag,
63 p_trx_tbl(1).description,
64 p_trx_tbl(1).attribute1,
65 p_trx_tbl(1).attribute2,
66 p_trx_tbl(1).attribute3,
67 p_trx_tbl(1).attribute4,
68 p_trx_tbl(1).attribute5,
69 p_trx_tbl(1).attribute6,
70 p_trx_tbl(1).attribute7,
71 p_trx_tbl(1).attribute8,
72 p_trx_tbl(1).attribute9,
73 p_trx_tbl(1).attribute10,
74 p_trx_tbl(1).attribute11,
75 p_trx_tbl(1).attribute12,
76 p_trx_tbl(1).attribute13,
77 p_trx_tbl(1).attribute14,
78 p_trx_tbl(1).attribute15,
79 p_trx_tbl(1).attribute_category,
80 fnd_global.user_id,
81 sysdate,
82 fnd_global.user_id,
83 sysdate,
84 fnd_global.login_id,
85 null);
86 End If;
87 End Insert_Header;
88
89 Procedure Insert_Dists(
90 p_dist_lines_tbl In FUN_TRX_PUB.full_dist_line_tbl_type,
91 p_count In Number) Is
92 Begin
93 Insert Into Fun_Interface_Dist_Lines(
94 trx_id,
95 dist_id,
96 batch_dist_id,
97 dist_number,
98 party_id,
99 party_type_flag,
100 dist_type_flag,
101 amount_cr,
102 amount_dr,
103 ccid,
104 attribute1,
105 attribute2,
106 attribute3,
107 attribute4,
108 attribute5,
109 attribute6,
110 attribute7,
111 attribute8,
112 attribute9,
113 attribute10,
114 attribute11,
115 attribute12,
116 attribute13,
117 attribute14,
118 attribute15,
119 attribute_category,
120 created_by,
121 creation_date,
122 last_updated_by,
123 last_update_date,
124 last_update_login,
125 import_status_code,
126 description)
127 Values(
128 p_dist_lines_tbl(p_count).trx_id,
129 p_dist_lines_tbl(p_count).dist_id,
130 null,
131 p_dist_lines_tbl(p_count).dist_number,
132 p_dist_lines_tbl(p_count).party_id,
133 p_dist_lines_tbl(p_count).party_type_flag,
134 p_dist_lines_tbl(p_count).dist_type_flag,
135 p_dist_lines_tbl(p_count).amount_cr,
136 p_dist_lines_tbl(p_count).amount_dr,
137 p_dist_lines_tbl(p_count).ccid,
138 p_dist_lines_tbl(p_count).attribute1,
139 p_dist_lines_tbl(p_count).attribute2,
140 p_dist_lines_tbl(p_count).attribute3,
141 p_dist_lines_tbl(p_count).attribute4,
142 p_dist_lines_tbl(p_count).attribute5,
143 p_dist_lines_tbl(p_count).attribute6,
144 p_dist_lines_tbl(p_count).attribute7,
145 p_dist_lines_tbl(p_count).attribute8,
146 p_dist_lines_tbl(p_count).attribute9,
147 p_dist_lines_tbl(p_count).attribute10,
148 p_dist_lines_tbl(p_count).attribute11,
149 p_dist_lines_tbl(p_count).attribute12,
150 p_dist_lines_tbl(p_count).attribute13,
151 p_dist_lines_tbl(p_count).attribute14,
152 p_dist_lines_tbl(p_count).attribute15,
153 p_dist_lines_tbl(p_count).attribute_category,
154 fnd_global.user_id,
155 sysdate,
156 fnd_global.user_id,
157 sysdate,
158 fnd_global.login_id,
159 null,
160 p_dist_lines_tbl(p_count).description);
161 End Insert_Dists;
162
163 Procedure Insert_Batch(
164 p_batch_rec In FUN_TRX_PUB.full_batch_rec_type,
165 p_initiator_name In Fun_Interface_Batches.initiator_name%type,
166 p_trx_type_name In Fun_Interface_Batches.trx_type_name%type,
167 p_from_le_name In Fun_Interface_Headers.to_le_name%type,
168 p_insert_flag In Varchar2) Is
169
170 l_group_id NUMBER;
171 Begin
172
173 SELECT fun_interface_controls_s.nextval
174 INTO l_group_id
175 FROM dual;
176
177 If p_insert_flag = 'Y' Then
178 Insert Into Fun_Interface_Batches(
179 source,
180 group_id,
181 batch_id,
182 batch_number,
183 initiator_id,
184 initiator_name,
185 from_le_id,
186 from_le_name,
187 from_ledger_id,
188 control_total,
189 running_total_cr,
190 running_total_dr,
191 currency_code,
192 exchange_rate_type,
193 description,
194 trx_type_id,
195 trx_type_code,
196 trx_type_name,
197 gl_date,
198 batch_date,
199 reject_allowed_flag,
200 from_recurring_batch_id,
201 attribute1,
202 attribute2,
203 attribute3,
204 attribute4,
205 attribute5,
206 attribute6,
207 attribute7,
208 attribute8,
209 attribute9,
210 attribute10,
211 attribute11,
212 attribute12,
213 attribute13,
214 attribute14,
215 attribute15,
216 attribute_category,
217 created_by,
218 creation_date,
219 last_updated_by,
220 last_update_date,
221 last_update_login,
222 import_status_code,
223 note)
224 Values(
225 'Global Intercompany',
226 l_group_id,
227 p_batch_rec.batch_id,
228 p_batch_rec.batch_number,
229 p_batch_rec.initiator_id,
230 p_initiator_name,
231 p_batch_rec.from_le_id,
232 p_from_le_name,
233 p_batch_rec.from_ledger_id,
234 p_batch_rec.control_total,
235 null,
236 null,
237 p_batch_rec.currency_code,
238 p_batch_rec.exchange_rate_type,
239 p_batch_rec.description,
240 p_batch_rec.trx_type_id,
241 p_batch_rec.trx_type_code,
242 p_trx_type_name,
243 p_batch_rec.gl_Date,
244 p_batch_rec.batch_Date,
245 p_batch_rec.reject_allow_flag,
246 p_batch_rec.from_recurring_batch_id,
247 p_batch_rec.attribute1,
248 p_batch_rec.attribute2,
249 p_batch_rec.attribute3,
250 p_batch_rec.attribute4,
251 p_batch_rec.attribute5,
252 p_batch_rec.attribute6,
253 p_batch_rec.attribute7,
254 p_batch_rec.attribute8,
255 p_batch_rec.attribute9,
256 p_batch_rec.attribute10,
257 p_batch_rec.attribute11,
258 p_batch_rec.attribute12,
259 p_batch_rec.attribute13,
260 p_batch_rec.attribute14,
261 p_batch_rec.attribute15 ,
262 p_batch_rec.attribute_category,
263 fnd_global.user_id,
264 sysdate,
265 fnd_global.user_id,
266 sysdate,
267 fnd_global.login_id,
268 null,
269 p_batch_rec.note);
270
271 Insert into Fun_Interface_Controls(
272 source,
273 group_id)
274 Values(
275 'Global Intercompany',
276 l_group_id);
277 Elsif p_insert_flag = 'N' Then
278 Update Fun_Interface_Batches Set
279 currency_code = p_batch_rec.currency_code,
280 exchange_rate_type = p_batch_rec.exchange_rate_type,
281 description = p_batch_rec.description,
282 trx_type_id = p_batch_rec.trx_type_id,
283 trx_type_code = p_batch_rec.trx_type_code,
284 trx_type_name = p_trx_type_name,
285 gl_date = p_batch_rec.gl_Date,
286 batch_date = p_batch_rec.batch_Date,
287 reject_allowed_flag = p_batch_rec.reject_allow_flag,
288 from_recurring_batch_id = p_batch_rec.from_recurring_batch_id,
289 attribute1 = p_batch_rec.attribute1,
290 attribute2 = p_batch_rec.attribute2,
291 attribute3 = p_batch_rec.attribute3,
292 attribute4 = p_batch_rec.attribute4,
293 attribute5 = p_batch_rec.attribute5,
294 attribute6 = p_batch_rec.attribute6,
295 attribute7 = p_batch_rec.attribute7,
296 attribute8 = p_batch_rec.attribute8,
297 attribute9 = p_batch_rec.attribute9,
298 attribute10 = p_batch_rec.attribute10,
299 attribute11 = p_batch_rec.attribute11,
300 attribute12 = p_batch_rec.attribute12,
301 attribute13 = p_batch_rec.attribute13,
302 attribute14 = p_batch_rec.attribute14,
303 attribute15 = p_batch_rec.attribute15,
304 attribute_category = p_batch_rec.attribute_category,
305 last_updated_by = fnd_global.user_id,
306 last_update_date = sysdate,
307 last_update_login = fnd_global.login_id,
308 note = p_batch_rec.note
309 Where batch_number = p_batch_rec.batch_number;
310 End If;
311 End Insert_Batch;
312
313 Procedure Validate_Record(
314 p_batch_rec In FUN_TRX_PUB.full_batch_rec_type,
315 p_trx_tbl In FUN_TRX_PUB.full_trx_tbl_type,
316 p_dist_lines_tbl In FUN_TRX_PUB.full_dist_line_tbl_type,
317 p_batch_insert In Varchar2) Is
318 l_batch_rec Fun_Trx_Pvt.batch_rec_type;
319 l_trx_tbl Fun_Trx_Pvt.trx_tbl_type;
320 l_dist_lines_tbl Fun_Trx_Pvt.dist_line_tbl_type;
321 l_init_dist_tbl Fun_Trx_Pvt.init_dist_tbl_type;
322 l_trx_rec_type Fun_Trx_Pvt.trx_rec_type;
323 l_dist_lines_rec_type Fun_Trx_Pvt.dist_line_rec_type;
324 l_validation_level Number := 50;
325 l_return_status Varchar2(1);
326 l_msg_count Number;
327 l_msg_data Varchar2(2000);
328 l_insert Varchar2(1);
329
330 -- 24-10-2007 MAKANSAL
331 -- For Bug # 6249898 Introduced to keep the recipient party id and recipient legal entity id
332 l_le_party_id Xle_Firstparty_Information_V.party_id%type;
333 l_to_le_id GL_LEDGER_LE_BSV_SPECIFIC_V.LEGAL_ENTITY_ID%type;
334
335 -- 24-10-2007 MAKANSAL
336 -- For Bug # 6249898 Introduced the Cursor to fetch the Recipient Legal Entity Id
337 Cursor C_Le_Id(cp_le_party_id In Xle_Entity_Profiles.party_id%type) Is
338 Select legal_entity_id
339 From Xle_Firstparty_Information_V
340 Where party_id = cp_le_party_id;
341 Begin
342 l_batch_rec.batch_id := p_batch_rec.batch_id;
343 l_batch_rec.batch_number := p_batch_rec.batch_number;
344 l_batch_rec.initiator_id := p_batch_rec.initiator_id;
345 l_batch_rec.from_le_id := p_batch_rec.from_le_id;
346 l_batch_rec.from_ledger_id := p_batch_rec.from_ledger_id;
347 l_batch_rec.control_total := p_batch_rec.control_total;
348 l_batch_rec.currency_code := p_batch_rec.currency_code;
349 l_batch_rec.exchange_rate_type := p_batch_rec.exchange_rate_type;
350 l_batch_rec.status := p_batch_rec.status;
351 l_batch_rec.description := p_batch_rec.description;
352 l_batch_rec.trx_type_id := p_batch_rec.trx_type_id;
353 l_batch_rec.trx_type_code := p_batch_rec.trx_type_code;
354 l_batch_rec.gl_date := p_batch_rec.gl_date;
355 l_batch_rec.batch_date := p_batch_rec.batch_date;
356 l_batch_rec.reject_allowed := p_batch_rec.reject_allow_flag;
357 l_batch_rec.from_recurring_batch := p_batch_rec.from_recurring_batch_id;
358 l_batch_rec.automatic_proration_flag := 'N';
359
360 For l_count In 1..p_trx_tbl.count Loop
361 l_trx_tbl(l_count).trx_id := p_trx_tbl(l_count).trx_id;
362 l_trx_tbl(l_count).initiator_id := p_trx_tbl(l_count).initiator_id;
363 l_trx_tbl(l_count).recipient_id := p_trx_tbl(l_count).recipient_id;
364 l_trx_tbl(l_count).to_le_id := p_trx_tbl(l_count).to_le_id;
365 l_trx_tbl(l_count).to_ledger_id := p_trx_tbl(l_count).to_ledger_id;
366 l_trx_tbl(l_count).batch_id := p_trx_tbl(l_count).batch_id;
367 l_trx_tbl(l_count).status := p_trx_tbl(l_count).status;
368 l_trx_tbl(l_count).init_amount_cr := p_trx_tbl(l_count).init_amount_cr;
369 l_trx_tbl(l_count).init_amount_dr := p_trx_tbl(l_count).init_amount_dr;
370 l_trx_tbl(l_count).reci_amount_cr := p_trx_tbl(l_count).reci_amount_cr;
371 l_trx_tbl(l_count).reci_amount_dr := p_trx_tbl(l_count).reci_amount_dr;
372 l_trx_tbl(l_count).invoicing_rule := p_trx_tbl(l_count).invoice_flag;
373 l_trx_tbl(l_count).approver_id := p_trx_tbl(l_count).approver_id;
374 l_trx_tbl(l_count).approval_date := p_trx_tbl(l_count).approval_date;
375 l_trx_tbl(l_count).original_trx_id := p_trx_tbl(l_count).original_trx_id;
376 l_trx_tbl(l_count).reversed_trx_id := p_trx_tbl(l_count).reversed_trx_id;
377 l_trx_tbl(l_count).from_recurring_trx_id := p_trx_tbl(l_count).from_recurring_trx_id;
378 l_trx_tbl(l_count).initiator_instance := p_trx_tbl(l_count).initiator_instance_flag;
379 l_trx_tbl(l_count).recipient_instance := p_trx_tbl(l_count).recipient_instance_flag;
380 l_trx_tbl(l_count).automatic_proration_flag := 'N';
381 l_trx_tbl(l_count).trx_number := p_trx_tbl(l_count).trx_number;
382 End Loop;
383
384 If p_dist_lines_tbl is not null Then
385 For l_count In 1..p_dist_lines_tbl.Count Loop
386 l_dist_lines_tbl(l_count).dist_id := p_dist_lines_tbl(l_count).dist_id;
387 l_dist_lines_tbl(l_count).dist_number := p_dist_lines_tbl(l_count).dist_number;
388 l_dist_lines_tbl(l_count).trx_id := p_dist_lines_tbl(l_count).trx_id;
389 l_dist_lines_tbl(l_count).line_id := p_dist_lines_tbl(l_count).line_id;
390 l_dist_lines_tbl(l_count).party_id := p_dist_lines_tbl(l_count).party_id ;
391 l_dist_lines_tbl(l_count).party_type := p_dist_lines_tbl(l_count).party_type_flag;
392 l_dist_lines_tbl(l_count).dist_type := p_dist_lines_tbl(l_count).dist_type_flag;
393 l_dist_lines_tbl(l_count).batch_dist_id := p_dist_lines_tbl(l_count).batch_dist_id;
394 l_dist_lines_tbl(l_count).amount_cr := p_dist_lines_tbl(l_count).amount_cr;
395 l_dist_lines_tbl(l_count).amount_dr := p_dist_lines_tbl(l_count).amount_dr;
396 l_dist_lines_tbl(l_count).ccid := p_dist_lines_tbl(l_count).ccid;
397 End Loop;
398 End If;
399
400 IF (p_batch_insert ='Y') THEN
401 l_insert := FND_API.G_TRUE;
402 ELSE
403 l_insert := FND_API.G_FALSE;
404 END IF;
405
406 Fun_Trx_Pvt.Init_Batch_Validate (
407 1.0,
408 Fnd_Api.G_TRUE,
409 l_validation_level,
410 l_return_status,
411 l_msg_count,
412 l_msg_data,
413 l_insert,
414 l_batch_rec,
415 l_trx_tbl,
416 l_init_dist_tbl,
417 l_dist_lines_tbl);
418 If l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR Then
419 Raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
420 Elsif l_return_status = Fnd_Api.G_RET_STS_ERROR Then
421 Raise Fnd_Api.G_EXC_ERROR;
422 End If;
423
424 l_trx_tbl.Delete;
425 l_init_dist_tbl.Delete;
426 l_dist_lines_tbl.Delete;
427
428 For l_count In 1..p_trx_tbl.count Loop
429 l_trx_rec_type.trx_id := p_trx_tbl(l_count).trx_id;
430 l_trx_rec_type.initiator_id := p_trx_tbl(l_count).initiator_id;
431 l_trx_rec_type.recipient_id := p_trx_tbl(l_count).recipient_id;
432 l_trx_rec_type.to_le_id := p_trx_tbl(l_count).to_le_id;
433 l_trx_rec_type.to_ledger_id := p_trx_tbl(l_count).to_ledger_id;
434 l_trx_rec_type.batch_id := p_trx_tbl(l_count).batch_id;
435 l_trx_rec_type.status := p_trx_tbl(l_count).status;
436 l_trx_rec_type.init_amount_cr := p_trx_tbl(l_count).init_amount_cr;
437 l_trx_rec_type.init_amount_dr := p_trx_tbl(l_count).init_amount_dr;
438 l_trx_rec_type.reci_amount_cr := p_trx_tbl(l_count).reci_amount_cr;
439 l_trx_rec_type.reci_amount_dr := p_trx_tbl(l_count).reci_amount_dr;
440 l_trx_rec_type.invoicing_rule := p_trx_tbl(l_count).invoice_flag;
441 l_trx_rec_type.approver_id := p_trx_tbl(l_count).approver_id;
442 l_trx_rec_type.approval_date := p_trx_tbl(l_count).approval_date;
443 l_trx_rec_type.original_trx_id := p_trx_tbl(l_count).original_trx_id;
444 l_trx_rec_type.reversed_trx_id := p_trx_tbl(l_count).reversed_trx_id;
445 l_trx_rec_type.from_recurring_trx_id := p_trx_tbl(l_count).from_recurring_trx_id;
446 l_trx_rec_type.initiator_instance := p_trx_tbl(l_count).initiator_instance_flag;
447 l_trx_rec_type.recipient_instance := p_trx_tbl(l_count).recipient_instance_flag;
448 l_trx_rec_type.trx_number := p_trx_tbl(l_count).trx_number;
449
450 If p_dist_lines_tbl is not null Then
451 For l_count In 1..p_dist_lines_tbl.Count Loop
452 l_dist_lines_tbl(l_count).dist_id := p_dist_lines_tbl(l_count).dist_id;
453 l_dist_lines_tbl(l_count).dist_number := p_dist_lines_tbl(l_count).dist_number;
454 l_dist_lines_tbl(l_count).trx_id := p_dist_lines_tbl(l_count).trx_id;
455 l_dist_lines_tbl(l_count).line_id := p_dist_lines_tbl(l_count).line_id;
456 l_dist_lines_tbl(l_count).party_id := p_dist_lines_tbl(l_count).party_id ;
457 l_dist_lines_tbl(l_count).party_type := p_dist_lines_tbl(l_count).party_type_flag;
458 l_dist_lines_tbl(l_count).dist_type := p_dist_lines_tbl(l_count).dist_type_flag;
459 l_dist_lines_tbl(l_count).batch_dist_id := p_dist_lines_tbl(l_count).batch_dist_id;
460 l_dist_lines_tbl(l_count).amount_cr := p_dist_lines_tbl(l_count).amount_cr;
461 l_dist_lines_tbl(l_count).amount_dr := p_dist_lines_tbl(l_count).amount_dr;
462 l_dist_lines_tbl(l_count).ccid := p_dist_lines_tbl(l_count).ccid;
463 End Loop;
464 End If;
465 End Loop;
466
467 l_return_status := null;
468 l_msg_count := null;
469 l_msg_data := null;
470
471 Fun_Trx_Pvt.Init_Trx_Validate (
472 1.0,
473 Fnd_Api.G_TRUE,
474 l_validation_level,
475 l_return_status,
476 l_msg_count,
477 l_msg_data,
478 l_trx_rec_type,
479 l_dist_lines_tbl,
480 l_batch_rec.currency_code,
481 l_batch_rec.gl_date,
482 l_batch_rec.batch_date,
483 l_batch_rec.exchange_rate_type);
484 If l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR Then
485 Raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
486 Elsif l_return_status = Fnd_Api.G_RET_STS_ERROR Then
487 Raise Fnd_Api.G_EXC_ERROR;
488 End If;
489
490 If p_dist_lines_tbl is not null Then
491 For l_count In 1..p_dist_lines_tbl.count Loop
492 l_dist_lines_rec_type.dist_id := p_dist_lines_tbl(l_count).dist_id;
493 l_dist_lines_rec_type.line_id := p_dist_lines_tbl(l_count).line_id;
494 l_dist_lines_rec_type.party_id := p_dist_lines_tbl(l_count).party_id;
495 l_dist_lines_rec_type.party_type := p_dist_lines_tbl(l_count).party_type_flag;
496 l_dist_lines_rec_type.dist_type := p_dist_lines_tbl(l_count).dist_type_flag;
497 l_dist_lines_rec_type.batch_dist_id := p_dist_lines_tbl(l_count).batch_dist_id;
498 l_dist_lines_rec_type.amount_cr := p_dist_lines_tbl(l_count).amount_cr;
499 l_dist_lines_rec_type.amount_dr := p_dist_lines_tbl(l_count).amount_dr;
500 l_dist_lines_rec_type.ccid := p_dist_lines_tbl(l_count).ccid;
501
502 l_return_status := null;
503 l_msg_count := null;
504 l_msg_data := null;
505
506
507 -- 24-10-2007 Changes made by MAKANSAl for Bug # 6249898
508 -- If the distribution line has the party type as 'R' then the recipient
509 -- legal entity id is passed so that the validation for BSV linkage
510 -- is successfully.
511
512 If l_dist_lines_rec_type.party_type = 'R' Then
513
514 --Fectch the recipient Legal Entity Id
515 l_le_party_id := null;
516 l_le_party_id := Fun_Tca_Pkg.Get_Le_Id(l_dist_lines_rec_type.party_id, sysdate);
517
518 For C_Le_Id_Rec In C_Le_Id(l_le_party_id) Loop
519 l_to_le_id := C_Le_Id_Rec.legal_entity_id;
520 End Loop;
521
522 -- Pass Recipient Legal entity Id
523
524 Fun_Trx_Pvt.Init_IC_Dist_Validate (
525 1.0,
526 Fnd_Api.G_TRUE,
527 l_validation_level,
528 l_to_le_id,
529 l_trx_rec_type.to_ledger_id,
530 --p_batch_rec.from_ledger_id,
531 l_return_status,
532 l_msg_count,
533 l_msg_data,
534 l_dist_lines_rec_type);
535 Else
536
537 -- Changes complete for Bug # 6249898
538
539 Fun_Trx_Pvt.Init_IC_Dist_Validate (
540 1.0,
541 Fnd_Api.G_TRUE,
542 l_validation_level,
543 p_batch_rec.from_le_id,
544 p_batch_rec.from_ledger_id,
545 l_return_status,
546 l_msg_count,
547 l_msg_data,
548 l_dist_lines_rec_type);
549
550 End If;
551
552 If l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR Then
553 Raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
554 Elsif l_return_status = Fnd_Api.G_RET_STS_ERROR Then
555 Raise Fnd_Api.G_EXC_ERROR;
556 End If;
557 End Loop;
558 End If;
559 End Validate_Record;
560
561 Procedure Get_Message_Text(p_data In Out NOCOPY Varchar2) Is
562 Begin
563 p_data := Fnd_Msg_Pub.Get(
564 p_msg_index => Fnd_Msg_Pub.G_FIRST,
565 p_encoded => Fnd_Api.G_FALSE);
566 End Get_Message_Text;
567
568 Procedure Upload_Batch(
569 p_batch_number In Fun_Interface_Batches.batch_number%type,
570 p_initiator_name In Fun_Interface_Batches.initiator_name%type,
571 p_currency_code In Fun_Interface_Batches.currency_code%type,
572 p_batch_date In Fun_Interface_Batches.batch_date%type,
573 p_gl_date In Fun_Interface_Batches.gl_date%type,
574 p_trx_type_name In Fun_Interface_Batches.trx_type_name%type,
575 p_description In Fun_Interface_Batches.description%type,
576 p_note In Fun_Interface_Batches.note%type,
577 p_attribute1 In Fun_Interface_Batches.attribute1%type,
578 p_attribute2 In Fun_Interface_Batches.attribute2%type,
579 p_attribute3 In Fun_Interface_Batches.attribute3%type,
580 p_attribute4 In Fun_Interface_Batches.attribute4%type,
581 p_attribute5 In Fun_Interface_Batches.attribute5%type,
582 p_attribute6 In Fun_Interface_Batches.attribute6%type,
583 p_attribute7 In Fun_Interface_Batches.attribute7%type,
584 p_attribute8 In Fun_Interface_Batches.attribute8%type,
585 p_attribute9 In Fun_Interface_Batches.attribute9%type,
586 p_attribute10 In Fun_Interface_Batches.attribute10%type,
587 p_attribute11 In Fun_Interface_Batches.attribute11%type,
588 p_attribute12 In Fun_Interface_Batches.attribute12%type,
589 p_attribute13 In Fun_Interface_Batches.attribute13%type,
590 p_attribute14 In Fun_Interface_Batches.attribute14%type,
591 p_attribute15 In Fun_Interface_Batches.attribute15%type,
592 p_attribute_category In Fun_Interface_Batches.attribute_category%type,
593 p_trx_number In Fun_Interface_Headers.trx_number%type,
594 p_recipient_name In Fun_Interface_Headers.recipient_name%type,
595 p_init_amount_dr In Fun_Interface_Headers.init_amount_dr%type,
596 p_init_amount_cr In Fun_Interface_Headers.init_amount_cr%type,
597 p_h_attribute1 In Fun_Interface_Headers.attribute1%type,
598 p_h_attribute2 In Fun_Interface_Headers.attribute2%type,
599 p_h_attribute3 In Fun_Interface_Headers.attribute3%type,
600 p_h_attribute4 In Fun_Interface_Headers.attribute4%type,
601 p_h_attribute5 In Fun_Interface_Headers.attribute5%type,
602 p_h_attribute6 In Fun_Interface_Headers.attribute6%type,
603 p_h_attribute7 In Fun_Interface_Headers.attribute7%type,
604 p_h_attribute8 In Fun_Interface_Headers.attribute8%type,
605 p_h_attribute9 In Fun_Interface_Headers.attribute9%type,
606 p_h_attribute10 In Fun_Interface_Headers.attribute10%type,
607 p_h_attribute11 In Fun_Interface_Headers.attribute11%type,
608 p_h_attribute12 In Fun_Interface_Headers.attribute12%type,
609 p_h_attribute13 In Fun_Interface_Headers.attribute13%type,
610 p_h_attribute14 In Fun_Interface_Headers.attribute14%type,
611 p_h_attribute15 In Fun_Interface_Headers.attribute15%type,
612 p_h_attribute_category In Fun_Interface_Headers.attribute_category%type,
613 p_id_ccid In Fun_Interface_Dist_Lines.ccid%type,
614 p_id_amount_dr In Fun_Interface_Dist_Lines.amount_dr%type,
615 p_id_amount_cr In Fun_Interface_Dist_Lines.amount_cr%type,
616 p_id_description In Fun_Interface_Dist_Lines.description%type,
617 p_id_attribute1 In Fun_Interface_Dist_Lines.attribute1%type,
618 p_id_attribute2 In Fun_Interface_Dist_Lines.attribute2%type,
619 p_id_attribute3 In Fun_Interface_Dist_Lines.attribute3%type,
620 p_id_attribute4 In Fun_Interface_Dist_Lines.attribute4%type,
621 p_id_attribute5 In Fun_Interface_Dist_Lines.attribute5%type,
622 p_id_attribute6 In Fun_Interface_Dist_Lines.attribute6%type,
623 p_id_attribute7 In Fun_Interface_Dist_Lines.attribute7%type,
624 p_id_attribute8 In Fun_Interface_Dist_Lines.attribute8%type,
625 p_id_attribute9 In Fun_Interface_Dist_Lines.attribute9%type,
626 p_id_attribute10 In Fun_Interface_Dist_Lines.attribute10%type,
627 p_id_attribute11 In Fun_Interface_Dist_Lines.attribute11%type,
628 p_id_attribute12 In Fun_Interface_Dist_Lines.attribute12%type,
629 p_id_attribute13 In Fun_Interface_Dist_Lines.attribute13%type,
630 p_id_attribute14 In Fun_Interface_Dist_Lines.attribute14%type,
631 p_id_attribute15 In Fun_Interface_Dist_Lines.attribute15%type,
632 p_id_attribute_category In Fun_Interface_Dist_Lines.attribute_category%type,
633 p_rd_ccid_segments In Varchar2,
634 p_rd_ccid In Fun_Interface_Dist_Lines.ccid%type,
635 p_rd_amount_dr In Fun_Interface_Dist_Lines.amount_dr%type,
636 p_rd_amount_cr In Fun_Interface_Dist_Lines.amount_cr%type,
637 p_rd_description In Fun_Interface_Dist_Lines.description%type,
638 p_dist_number In Fun_Interface_Dist_Lines.dist_number%type) Is
639
640 Cursor C_Batch_Exists(cp_batch_number In Fun_Interface_Batches.batch_number%type) Is
641 Select *
642 From Fun_Interface_Batches
643 Where batch_number = cp_batch_number;
644
645 Cursor C_Batch_Id Is
646 Select Fun_Trx_Batches_S.nextval batch_id
647 From Dual;
648
649 -- Bug No: 6134848. 2 more condetions added to validate Party_usage_code.
650
651 Cursor C_Party(cp_party_name In Fun_Interface_Batches.initiator_name%type) Is
652 Select hzp.party_id
653 From Hz_Parties Hzp, hz_party_usg_assignments hu
654 Where hzp.party_type = 'ORGANIZATION'
655 And hzp.party_name = cp_party_name
656 And hzp.party_id = hu.party_id
657 And hu.party_usage_code = 'INTERCOMPANY_ORG';
658
659 --Bug No: 6134848 ends here
660
661 Cursor C_Le_Id(cp_le_party_id In Xle_Entity_Profiles.party_id%type) Is
662 Select legal_entity_id
663 From Xle_Firstparty_Information_V
664 Where party_id = cp_le_party_id;
665
666 Cursor C_Le_Name (cp_party_id In Xle_Entity_Profiles.party_id%type) Is
667 Select name
668 From xle_firstparty_information_v
669 Where party_id = cp_party_id;
670
671 Cursor C_Trx_Type (cp_trx_type_name In Fun_Interface_Batches.trx_type_name%type) Is
672 Select trx_type_code, trx_type_id, allow_invoicing_flag
673 From Fun_Trx_Types_Vl
674 Where trx_type_name = cp_trx_type_name;
675
676 Cursor C_Trx_Number_Exists(
677 cp_batch_id In Fun_Interface_Headers.batch_id%type,
678 cp_trx_number In Fun_Interface_Headers.trx_number%type) Is
679 Select *
680 From Fun_Interface_Headers
681 Where batch_id = cp_batch_id
682 And trx_number = cp_trx_number;
683
684 Cursor C_Trx_Id Is
685 Select Fun_Trx_Headers_S.nextval trx_id
686 From Dual;
687
688 Cursor C_Dupl_Reci_In_Batch(
689 cp_batch_id In Fun_Interface_Headers.batch_id%type,
690 cp_trx_number In Fun_Interface_Headers.trx_number%type,
691 cp_party_name In Fun_Interface_Headers.recipient_name%type) Is
692 Select count(distinct trx_number) dupl_reci_count
693 From Fun_Interface_Headers
694 Where batch_id = cp_batch_id
695 And trx_number <> cp_trx_number
696 And recipient_name = cp_party_name;
697
698 Cursor C_Dist_Lines_Exists(
699 cp_trx_id In Fun_Interface_Dist_Lines.trx_id%type,
700 cp_party_id In Fun_Interface_Dist_Lines.party_id%type,
701 cp_ccid In Fun_Interface_Dist_Lines.ccid%type,
702 cp_party_type_flag In Fun_Interface_Dist_Lines.party_type_flag%type,
703 cp_dist_number in Fun_Interface_Dist_Lines.dist_number%type) Is
704 Select *
705 From Fun_Interface_Dist_Lines
706 Where trx_id = cp_trx_id
707 And party_id = cp_party_id
708 And ccid = cp_ccid
709 And party_type_flag = cp_party_type_flag
710 And dist_number=cp_dist_number;
711
712 Cursor C_Dist_Id Is
713 Select Fun_Dist_Lines_S.nextval dist_id
714 From dual;
715
716 Cursor C_Ccid(cp_party_name In Fun_Interface_Batches.initiator_name%type) Is
717 Select chart_of_accounts_id
718 From Hz_Parties hzp,
719 xle_firstparty_information_v xfi ,
720 Gl_Ledger_Le_V led
721 Where hzp.party_name = cp_party_name
722 And fun_tca_pkg.get_le_id(hzp.party_id) = xfi.party_id
723 And xfi.legal_entity_id = led.legal_entity_id
724 And led.ledger_category_code = 'PRIMARY';
725
726 Cursor C_Flex_Info(cp_chart_of_accounts_id In Fnd_Id_Flex_Structures.id_flex_num%type) Is
727 Select fa.application_short_name appl_short_name
728 From Fnd_Id_Flex_Structures fs, Fnd_Application fa
729 Where fs.application_id = fa.application_id
730 And id_flex_num = cp_chart_of_accounts_id
731 And id_flex_code = 'GL#';
732
733 Cursor C_Sum_Dist(
734 cp_trx_id In Fun_Interface_Dist_Lines.trx_id%type,
735 cp_party_id In Fun_Interface_Dist_Lines.party_id%type,
736 cp_party_type_flag In Fun_Interface_Dist_Lines.party_type_flag%type) Is
737 Select nvl(Sum(nvl(amount_dr,0)),0) Dr_Sum,
738 nvl(Sum(nvl(amount_cr,0)),0) Cr_Sum
739 From Fun_Interface_Dist_Lines
740 Where trx_id = cp_trx_id
741 And party_id = cp_party_id
742 And party_type_flag = cp_party_type_flag;
743
744 l_count Number;
745 l_batch_insert Varchar2(1);
746 l_le_party_id Xle_Firstparty_Information_V.party_id%type;
747 l_from_le_name Fun_Interface_Batches.from_le_name%type;
748 l_reci_name Fun_Interface_Headers.recipient_name%type;
749 l_trx_insert Varchar2(1);
750 l_dup_reci_count Number;
751 l_to_le_name Fun_Interface_Headers.to_le_name%type;
752 l_init_dist_line_insert Varchar2(1);
753 l_init_chart_of_accounts Fnd_Id_Flex_Structures.id_flex_num%type;
754 l_reci_chart_of_accounts Fnd_Id_Flex_Structures.id_flex_num%type;
755 l_appl_short_name Fnd_Application.application_short_name%type;
756 l_reci_dist_line_insert Varchar2(1);
757 l_data varchar2(2000);
758 l_dr_sum Fun_Interface_Dist_Lines.amount_dr%type;
759 l_cr_sum Fun_Interface_Dist_Lines.amount_cr%type;
760 l_trx_id Fun_trx_headers.trx_id%type;
761 l_recipient_id Fun_trx_headers.recipient_id%type;
762 l_init_amount_cr Fun_Interface_Headers.init_amount_cr%type;
763 l_init_amount_dr Fun_Interface_Headers.init_amount_dr%type;
764 l_trx_number Fun_Interface_Headers.trx_number%type;
765
766 l_batch_rec FUN_TRX_PUB.full_batch_rec_type;
767 l_trx_tbl FUN_TRX_PUB.full_trx_tbl_type;
768 l_dist_lines_tbl FUN_TRX_PUB.full_dist_line_tbl_type;
769 l_length NUMBER;
770 l_num NUMBER; --6846666
771
772 Initiator_Excpt Exception;
773 Trx_Type_Excpt Exception;
774 Tran_Reci_Diff_Excpt Exception;
775 Tran_Dupl_Row_Excpt Exception;
776 Recipient_Excpt Exception;
777 Dupl_Reci_In_Batch Exception;
778 Amount_Req_Excpt Exception;
779 Init_Dupl_Row_Excpt Exception;
780 Init_Dist_Sum_Excpt Exception;
781 Init_Amount_Excpt Exception;
782 Con_Seg_Not_Req_Excpt Exception;
783 Con_Seg_Req_Excpt Exception;
784 Ccid_Gen_Excpt Exception;
785 Reci_Dupl_Row_Excpt Exception;
786 Reci_Dist_Sum_Excpt Exception;
787 Reci_Amount_Excpt Exception;
788 Trx_Num_Not_Numeric_Excpt Exception;
789 Batch_Num_Invalid_Excpt Exception; --6846666
790
791
792 Begin
793 l_count := 1;
794 select length(p_batch_number) into l_length from dual; --6846666
795 IF(l_length>20) Then --6846666
796 Raise Batch_Num_Invalid_Excpt;
797 End IF;
798 l_batch_rec.batch_number := p_batch_number;
799
800 For C_Batch_Exists_Rec In C_Batch_Exists(l_batch_rec.batch_number) Loop
801 l_batch_insert := 'N';
802 l_batch_rec.batch_id := C_Batch_Exists_Rec.batch_id;
803 End Loop;
804
805 If l_batch_rec.batch_id is null Then
806 l_batch_insert := 'Y';
807 For C_Batch_Id_Rec In C_Batch_Id Loop
808 l_batch_rec.batch_id := C_Batch_ID_Rec.batch_id;
809 End Loop;
810 End If;
811
812 For C_Party_Rec In C_Party(p_initiator_name) Loop
813 l_batch_rec.initiator_id := C_Party_Rec.party_id;
814 End Loop;
815
816 If l_batch_rec.initiator_id is null Then
817 Raise Initiator_Excpt;
818 End If;
819
820 l_le_party_id := fun_tca_pkg.get_le_id(l_batch_rec.initiator_id, sysdate);
821
822 For C_Le_Id_Rec In C_Le_Id(l_le_party_id) Loop
823 l_batch_rec.from_le_id := C_Le_Id_Rec.legal_entity_id;
824 End Loop;
825
826 For C_Le_Name_Rec In C_Le_Name(l_le_party_id) Loop
827 l_from_le_name := C_Le_Name_Rec.name;
828 End Loop;
829
830 l_batch_rec.from_ledger_id := fun_trx_entry_util.Get_Ledger_id(l_batch_rec.initiator_id,'ORGANIZATION');
831 l_batch_rec.currency_code := p_currency_code ;
832 l_batch_rec.exchange_rate_type := fun_system_options_pkg.get_exchg_rate_type;
833 l_batch_rec.description := p_description;
834 l_batch_rec.note := p_note;
835
836 For C_Trx_Type_Rec In C_Trx_Type( p_trx_type_name) Loop
837 l_batch_rec.trx_type_id := C_Trx_Type_Rec.trx_type_id;
838 l_batch_rec.trx_type_code := C_Trx_Type_Rec.trx_type_code;
839 l_trx_tbl(l_count).invoice_flag := C_Trx_Type_Rec.allow_invoicing_flag;
840 End Loop;
841
842 If l_batch_rec.trx_type_id is null Then
843 Raise Trx_Type_Excpt;
844 End If;
845
846 l_batch_rec.gl_date := trunc(p_gl_date);
847 l_batch_rec.batch_date := trunc(p_batch_date);
848
849 If fun_system_options_pkg.get_allow_reject = TRUE Then
850 l_batch_rec.reject_allow_flag := 'Y';
851 Else
852 l_batch_rec.reject_allow_flag := 'N';
853 End If;
854
855 l_batch_rec.Attribute1 := p_attribute1;
856 l_batch_rec.Attribute2 := p_attribute2;
857 l_batch_rec.Attribute3 := p_attribute3;
858 l_batch_rec.Attribute4 := p_attribute4;
859 l_batch_rec.Attribute5 := p_attribute5;
860 l_batch_rec.Attribute6 := p_attribute6;
861 l_batch_rec.Attribute7 := p_attribute7;
862 l_batch_rec.Attribute8 := p_attribute8;
863 l_batch_rec.Attribute9 := p_attribute9;
864 l_batch_rec.Attribute10 := p_attribute10;
865 l_batch_rec.Attribute11 := p_attribute11;
866 l_batch_rec.Attribute12 := p_attribute12;
867 l_batch_rec.Attribute13 := p_attribute13;
868 l_batch_rec.Attribute14 := p_attribute14;
869 l_batch_rec.Attribute15 := p_attribute15;
870 l_batch_rec.attribute_category := p_attribute_category;
871
872 l_trx_tbl(l_count).trx_number := p_trx_number;
873
874 -- Validate p_trx_number
875 BEGIN
876 l_num := replace(translate(p_trx_number,
877 'N01234567890','XNNNNNNNNNN'),'N',null);
878 EXCEPTION
879 WHEN OTHERS THEN
880 Raise Trx_Num_Not_Numeric_Excpt;
881 END;
882
883
884 For C_Trx_Number_Exists_Rec In C_Trx_Number_Exists(
885 l_batch_rec.batch_id,
886 l_trx_tbl(l_count).trx_number) Loop
887
888 If (C_Trx_Number_Exists_Rec.recipient_name <> p_recipient_name) Then
889 l_reci_name := C_Trx_Number_Exists_Rec.recipient_name;
890 Raise Tran_Reci_Diff_Excpt;
891 End If;
892
893 l_trx_insert := 'N';
894 l_trx_tbl(l_count).trx_id := C_Trx_Number_Exists_Rec.trx_id;
895 l_trx_tbl(l_count).recipient_id := C_Trx_Number_Exists_Rec.recipient_id;
896 l_trx_tbl(l_count).to_le_id := C_Trx_Number_Exists_Rec.to_le_id;
897 l_trx_tbl(l_count).to_ledger_id := C_Trx_Number_Exists_Rec.to_ledger_id;
898 l_trx_tbl(l_count).batch_id := C_Trx_Number_Exists_Rec.batch_id ;
899 l_trx_tbl(l_count).init_amount_cr := C_Trx_Number_Exists_Rec.init_amount_cr;
900 l_trx_tbl(l_count).init_amount_dr := C_Trx_Number_Exists_Rec.init_amount_dr;
901
902 If (nvl(l_trx_tbl(l_count).init_amount_cr,0) <> nvl(p_init_amount_cr,0) Or
903 nvl(l_trx_tbl(l_count).init_amount_dr,0) <> nvl(p_init_amount_dr,0)) Then
904 Raise Tran_Dupl_Row_Excpt;
905 End If;
906
907 l_trx_tbl(l_count).reci_amount_cr := C_Trx_Number_Exists_Rec.init_amount_cr;
908 l_trx_tbl(l_count).reci_amount_dr := C_Trx_Number_Exists_Rec.init_amount_dr;
909 l_trx_tbl(l_count).invoice_flag := C_Trx_Number_Exists_Rec.invoicing_rule_flag;
910 l_trx_tbl(l_count).from_recurring_trx_id := C_Trx_Number_Exists_Rec.from_recurring_trx_id;
911 l_trx_tbl(l_count).initiator_instance_flag := C_Trx_Number_Exists_Rec.initiator_instance_flag;
912 l_trx_tbl(l_count).recipient_instance_flag := C_Trx_Number_Exists_Rec.recipient_instance_flag;
913 l_trx_tbl(l_count).Attribute1 := C_Trx_Number_Exists_Rec.attribute1;
914 l_trx_tbl(l_count).Attribute2 := C_Trx_Number_Exists_Rec.attribute2;
915 l_trx_tbl(l_count).Attribute3 := C_Trx_Number_Exists_Rec.attribute3;
916 l_trx_tbl(l_count).Attribute4 := C_Trx_Number_Exists_Rec.attribute4;
917 l_trx_tbl(l_count).Attribute5 := C_Trx_Number_Exists_Rec.attribute5;
918 l_trx_tbl(l_count).Attribute6 := C_Trx_Number_Exists_Rec.attribute6;
919 l_trx_tbl(l_count).Attribute7 := C_Trx_Number_Exists_Rec.attribute7;
920 l_trx_tbl(l_count).Attribute8 := C_Trx_Number_Exists_Rec.attribute8;
921 l_trx_tbl(l_count).Attribute9 := C_Trx_Number_Exists_Rec.attribute9;
922 l_trx_tbl(l_count).Attribute10 := C_Trx_Number_Exists_Rec.attribute10;
923 l_trx_tbl(l_count).Attribute11 := C_Trx_Number_Exists_Rec.attribute11;
924 l_trx_tbl(l_count).Attribute12 := C_Trx_Number_Exists_Rec.attribute12;
925 l_trx_tbl(l_count).Attribute13 := C_Trx_Number_Exists_Rec.attribute13;
926 l_trx_tbl(l_count).Attribute14 := C_Trx_Number_Exists_Rec.attribute14;
927 l_trx_tbl(l_count).Attribute15 := C_Trx_Number_Exists_Rec.attribute15;
928 l_trx_tbl(l_count).attribute_category := C_Trx_Number_Exists_Rec.attribute_category;
929 End Loop;
930
931 If l_trx_tbl(l_count).trx_id is null Then
932 l_trx_insert := 'Y';
933 l_trx_tbl(l_count).batch_id := l_batch_rec.batch_id ;
934
935 For C_Trx_Id_Rec In C_Trx_Id Loop
936 l_trx_tbl(l_count).trx_id := C_Trx_Id_Rec.trx_id;
937 End Loop;
938
939 For C_Party_Rec In C_Party(p_recipient_name) Loop
940 l_trx_tbl(l_count).recipient_id := C_Party_Rec.party_id;
941 End Loop;
942
943 If l_trx_tbl(l_count).recipient_id is null Then
944 Raise Recipient_Excpt;
945 End If;
946
947
948 l_le_party_id := null;
949 l_le_party_id := Fun_Tca_Pkg.Get_Le_Id(l_trx_tbl(l_count).recipient_id, sysdate);
950 For C_Le_Id_Rec In C_Le_Id(l_le_party_id) Loop
951 l_trx_tbl(l_count).to_le_id := C_Le_Id_Rec.legal_entity_id;
952 End Loop;
953
954 For C_Le_Name_Rec In C_Le_Name(l_le_party_id) Loop
955 l_to_le_name := C_Le_Name_Rec.name;
956 End Loop;
957
958 l_trx_tbl(l_count).to_ledger_id := Fun_Trx_Entry_Util.Get_Ledger_Id(l_trx_tbl(l_count).recipient_id,'ORGANIZATION');
959
960 l_trx_tbl(l_count).init_amount_cr := p_init_amount_cr;
961 l_trx_tbl(l_count).init_amount_dr := p_init_amount_dr;
962 l_trx_tbl(l_count).reci_amount_cr := p_init_amount_cr;
963 l_trx_tbl(l_count).reci_amount_dr := p_init_amount_dr;
964
965 If (l_trx_tbl(l_count).init_amount_cr is null And
966 l_trx_tbl(l_count).init_amount_dr is null) Then
967 Raise Amount_Req_Excpt;
968 End If;
969
970 If (l_trx_tbl(l_count).init_amount_cr is not null And
971 l_trx_tbl(l_count).init_amount_dr is not null) Then
972 Raise Amount_Req_Excpt;
973 End If;
974
975 l_trx_tbl(l_count).initiator_instance_flag := 'N';
976 l_trx_tbl(l_count).recipient_instance_flag := 'N';
977 l_trx_tbl(l_count).Attribute1 := p_h_attribute1;
978 l_trx_tbl(l_count).Attribute2 := p_h_attribute2;
979 l_trx_tbl(l_count).Attribute3 := p_h_attribute3;
980 l_trx_tbl(l_count).Attribute4 := p_h_attribute4;
981 l_trx_tbl(l_count).Attribute5 := p_h_attribute5;
982 l_trx_tbl(l_count).Attribute6 := p_h_attribute6;
983 l_trx_tbl(l_count).Attribute7 := p_h_attribute7;
984 l_trx_tbl(l_count).Attribute8 := p_h_attribute8;
985 l_trx_tbl(l_count).Attribute9 := p_h_attribute9;
986 l_trx_tbl(l_count).Attribute10 := p_h_attribute10;
987 l_trx_tbl(l_count).Attribute11 := p_h_attribute11;
988 l_trx_tbl(l_count).Attribute12 := p_h_attribute12;
989 l_trx_tbl(l_count).Attribute13 := p_h_attribute13;
990 l_trx_tbl(l_count).Attribute14 := p_h_attribute14;
991 l_trx_tbl(l_count).Attribute15 := p_h_attribute15;
992 l_trx_tbl(l_count).attribute_category := p_h_attribute_category;
993 End If;
994
995 For C_Dist_Lines_Exists_Rec In C_Dist_Lines_Exists(
996 l_trx_tbl(l_count).trx_id,
997 l_batch_rec.initiator_id,
998 p_id_ccid,
999 'I', p_dist_number) Loop
1000
1001 l_init_dist_line_insert := 'N';
1002 l_dist_lines_tbl(l_count).trx_id := C_Dist_Lines_Exists_Rec.trx_id;
1003 l_dist_lines_tbl(l_count).dist_id := C_Dist_Lines_Exists_Rec.dist_id;
1004 l_dist_lines_tbl(l_count).dist_number := C_Dist_Lines_Exists_Rec.dist_number;
1005 l_dist_lines_tbl(l_count).party_id := C_Dist_Lines_Exists_Rec.party_id;
1006 l_dist_lines_tbl(l_count).party_type_flag := C_Dist_Lines_Exists_Rec.party_type_flag;
1007 l_dist_lines_tbl(l_count).dist_type_flag := C_Dist_Lines_Exists_Rec.dist_type_flag;
1008 l_dist_lines_tbl(l_count).amount_cr := C_Dist_Lines_Exists_Rec.amount_cr;
1009 l_dist_lines_tbl(l_count).amount_dr := C_Dist_Lines_Exists_Rec.amount_dr;
1010 l_dist_lines_tbl(l_count).ccid := C_Dist_Lines_Exists_Rec.ccid;
1011 l_dist_lines_tbl(l_count).description := C_Dist_Lines_Exists_Rec.description;
1012
1013 If (nvl(l_dist_lines_tbl(l_count).amount_cr,0) <> nvl(p_id_amount_cr,0) Or
1014 nvl(l_dist_lines_tbl(l_count).amount_dr,0) <> nvl(p_id_amount_dr,0) Or
1015 nvl(l_dist_lines_tbl(l_count).description, 'NULL') <> nvl(p_id_description, 'NULL')) Then
1016 Raise Init_Dupl_Row_Excpt;
1017 End If;
1018
1019 l_dist_lines_tbl(l_count).attribute1 := C_Dist_Lines_Exists_Rec.attribute1;
1020 l_dist_lines_tbl(l_count).attribute2 := C_Dist_Lines_Exists_Rec.attribute2;
1021 l_dist_lines_tbl(l_count).attribute3 := C_Dist_Lines_Exists_Rec.attribute3;
1022 l_dist_lines_tbl(l_count).attribute4 := C_Dist_Lines_Exists_Rec.attribute4;
1023 l_dist_lines_tbl(l_count).attribute5 := C_Dist_Lines_Exists_Rec.attribute5;
1024 l_dist_lines_tbl(l_count).attribute6 := C_Dist_Lines_Exists_Rec.attribute6;
1025 l_dist_lines_tbl(l_count).attribute7 := C_Dist_Lines_Exists_Rec.attribute7;
1026 l_dist_lines_tbl(l_count).attribute8 := C_Dist_Lines_Exists_Rec.attribute8;
1027 l_dist_lines_tbl(l_count).attribute9 := C_Dist_Lines_Exists_Rec.attribute9;
1028 l_dist_lines_tbl(l_count).attribute10 := C_Dist_Lines_Exists_Rec.attribute10;
1029 l_dist_lines_tbl(l_count).attribute11 := C_Dist_Lines_Exists_Rec.attribute11;
1030 l_dist_lines_tbl(l_count).attribute12 := C_Dist_Lines_Exists_Rec.attribute12;
1031 l_dist_lines_tbl(l_count).attribute13 := C_Dist_Lines_Exists_Rec.attribute13;
1032 l_dist_lines_tbl(l_count).attribute14 := C_Dist_Lines_Exists_Rec.attribute14;
1033 l_dist_lines_tbl(l_count).attribute15 := C_Dist_Lines_Exists_Rec.attribute15;
1034 l_dist_lines_tbl(l_count).attribute_category := p_id_attribute_category;
1035 End Loop;
1036
1037 If (l_dist_lines_tbl.Count = 0 and (p_id_ccid is not null or p_id_amount_dr is not null or p_id_amount_cr is not null) ) Then
1038
1039 l_init_dist_line_insert := 'Y';
1040 l_dist_lines_tbl(l_count).trx_id := l_trx_tbl(l_count).trx_id;
1041
1042 For C_Dist_Id_Rec In C_Dist_Id Loop
1043 l_dist_lines_tbl(l_count).dist_id := C_Dist_Id_Rec.dist_id;
1044 l_dist_lines_tbl(l_count).dist_number := p_dist_number;
1045 End Loop;
1046
1047 l_dist_lines_tbl(l_count).party_id := l_batch_rec.initiator_id;
1048 l_dist_lines_tbl(l_count).party_type_flag := 'I';
1049 l_dist_lines_tbl(l_count).dist_type_flag := 'L';
1050 l_dist_lines_tbl(l_count).amount_cr := p_id_amount_cr;
1051 l_dist_lines_tbl(l_count).amount_dr := p_id_amount_dr;
1052
1053 -- Modified on 16th April 2005
1054 l_dr_sum := 0;
1055 l_cr_sum := 0;
1056 For C_Sum_Dist_Rec in C_Sum_Dist(
1057 l_trx_tbl(l_count).trx_id,
1058 l_batch_rec.initiator_id,
1059 'I') Loop
1060 l_dr_sum := C_Sum_Dist_Rec.Dr_Sum;
1061 l_cr_sum := C_Sum_Dist_Rec.Cr_Sum;
1062 End Loop;
1063 -- Modified on 16th April 2005
1064 If l_trx_tbl(l_count).init_amount_cr is not null Then
1065 If (l_dist_lines_tbl(l_count).amount_dr is not null And
1066 l_dist_lines_tbl(l_count).amount_cr is null) Then
1067 -- Modified on 16th April 2005
1068 /* This validation is replaced by importer.
1069 If ((l_dist_lines_tbl(l_count).amount_dr + l_dr_sum)
1070 > l_trx_tbl(l_count).init_amount_cr) Then
1071 Raise Init_Dist_Sum_Excpt;
1072 End If; */
1073 NULL;
1074 -- Modified on 16th April 2005
1075 Else
1076 Raise Init_Amount_Excpt;
1077 End If;
1078 End If;
1079
1080 If l_trx_tbl(l_count).init_amount_dr is not null Then
1081 If (l_dist_lines_tbl(l_count).amount_cr is not null And
1082 l_dist_lines_tbl(l_count).amount_dr is null) Then
1083 -- Modified on 16th April 2005
1084 /* This validation is replaced by importer.
1085 If ((l_dist_lines_tbl(l_count).amount_cr + l_cr_sum) >
1086 l_trx_tbl(l_count).init_amount_dr) Then
1087 Raise Init_Dist_Sum_Excpt;
1088 End If; */
1089 NULL;
1090 -- Modified on 16th April 2005
1091 Else
1092 Raise Init_Amount_Excpt;
1093 End If;
1094 End If;
1095
1096 l_dist_lines_tbl(l_count).ccid := p_id_ccid;
1097 l_dist_lines_tbl(l_count).description := p_id_description;
1098 l_dist_lines_tbl(l_count).attribute1 := p_id_attribute1;
1099 l_dist_lines_tbl(l_count).attribute2 := p_id_attribute2;
1100 l_dist_lines_tbl(l_count).attribute3 := p_id_attribute3;
1101 l_dist_lines_tbl(l_count).attribute4 := p_id_attribute4;
1102 l_dist_lines_tbl(l_count).attribute5 := p_id_attribute5;
1103 l_dist_lines_tbl(l_count).attribute6 := p_id_attribute6;
1104 l_dist_lines_tbl(l_count).attribute7 := p_id_attribute7;
1105 l_dist_lines_tbl(l_count).attribute8 := p_id_attribute8;
1106 l_dist_lines_tbl(l_count).attribute9 := p_id_attribute9;
1107 l_dist_lines_tbl(l_count).attribute10 := p_id_attribute10;
1108 l_dist_lines_tbl(l_count).attribute11 := p_id_attribute11;
1109 l_dist_lines_tbl(l_count).attribute12 := p_id_attribute12;
1110 l_dist_lines_tbl(l_count).attribute13 := p_id_attribute13;
1111 l_dist_lines_tbl(l_count).attribute14 := p_id_attribute14;
1112 l_dist_lines_tbl(l_count).attribute15 := p_id_attribute15;
1113 l_dist_lines_tbl(l_count).attribute_category := p_id_attribute_category;
1114 l_dist_lines_tbl(l_count).dist_number := p_dist_number;
1115 End If;
1116
1117 For C_Ccid_Rec In C_Ccid(p_initiator_name) Loop
1118 l_init_chart_of_accounts := C_Ccid_rec.chart_of_accounts_id;
1119 End Loop;
1120
1121 For C_Ccid_Rec In C_Ccid(p_recipient_name) Loop
1122 l_reci_chart_of_accounts := C_Ccid_rec.chart_of_accounts_id;
1123 End Loop;
1124 -- start of recipient validation
1125 l_trx_id := l_trx_tbl(l_count).trx_id;
1126 l_recipient_id := l_trx_tbl(l_count).recipient_id;
1127 l_init_amount_cr := l_trx_tbl(l_count).init_amount_cr;
1128 l_init_amount_dr := l_trx_tbl(l_count).init_amount_dr;
1129 l_trx_number := l_trx_tbl(l_count).trx_number;
1130
1131 If( p_id_ccid is null and p_id_amount_dr is null and p_id_amount_cr is null) THEN
1132 l_count := l_count - 1;
1133 End If;
1134 If (p_rd_amount_dr is not null OR p_rd_amount_cr is not null OR
1135 p_rd_ccid_segments is not null OR p_rd_ccid is not null ) THEN
1136
1137
1138 If l_init_chart_of_accounts = l_reci_chart_of_accounts Then
1139 If p_rd_ccid_segments is not null Then
1140 Raise Con_Seg_Not_Req_Excpt;
1141 End if;
1142 l_dist_lines_tbl(l_count + 1).ccid := p_rd_ccid;
1143 Else
1144 If p_rd_ccid_segments is null Then
1145 Raise Con_Seg_Req_Excpt;
1146 End if;
1147
1148 For C_Flex_Info_Rec In C_Flex_Info(l_reci_chart_of_accounts) Loop
1149 l_appl_short_name := C_Flex_Info_Rec.appl_short_name;
1150 End Loop;
1151
1152 If Not Fnd_Flex_Keyval.Validate_Segs(
1153 'CREATE_COMBINATION', -- Operation
1154 l_appl_short_name, -- Application Short Name
1155 'GL#', -- Funds Flexfield Structure Code
1156 l_reci_chart_of_accounts, -- Structure Id
1157 p_rd_ccid_segments, -- Concatenated Segments
1158 'V', -- values
1159 sysdate, -- validation_date
1160 'ALL', -- displayable
1161 NULL, -- data_set
1162 NULL, -- vrule
1163 NULL, -- where_clause
1164 NULL, -- get_columns
1165 FALSE, -- allow_nulls
1166 FALSE, -- allow_orphans
1167 fnd_global.resp_appl_id,
1168 fnd_global.resp_id,
1169 fnd_global.user_id,
1170 'GL_CODE_COMBINATIONS', -- select_comb_from_view
1171 NULL, -- no_combmsg
1172 NULL -- where_clause_msg
1173 ) Then
1174 Raise Ccid_Gen_Excpt;
1175 Else
1176 l_dist_lines_tbl(l_count + 1).ccid := Fnd_Flex_Keyval.combination_id;
1177 End if;
1178 End If;
1179
1180 For C_Dist_Lines_Exists_Rec In C_Dist_Lines_Exists(
1181 l_trx_id,
1182 l_recipient_id,
1183 l_dist_lines_tbl(l_count + 1).ccid,
1184 'R', p_dist_number) Loop
1185
1186 l_reci_dist_line_insert := 'N';
1187 l_dist_lines_tbl(l_count + 1).dist_id := C_Dist_Lines_Exists_Rec.dist_id;
1188 l_dist_lines_tbl(l_count + 1).dist_number := C_Dist_Lines_Exists_Rec.dist_number;
1189 l_dist_lines_tbl(l_count + 1).trx_id := C_Dist_Lines_Exists_Rec.trx_id;
1190 l_dist_lines_tbl(l_count + 1).party_id := C_Dist_Lines_Exists_Rec.party_id;
1191 l_dist_lines_tbl(l_count + 1).party_type_flag := C_Dist_Lines_Exists_Rec.party_type_flag;
1192 l_dist_lines_tbl(l_count + 1).dist_type_flag := C_Dist_Lines_Exists_Rec.dist_type_flag;
1193 l_dist_lines_tbl(l_count + 1).amount_cr := C_Dist_Lines_Exists_Rec.amount_cr;
1194 l_dist_lines_tbl(l_count + 1).amount_dr := C_Dist_Lines_Exists_Rec.amount_dr;
1195 l_dist_lines_tbl(l_count + 1).ccid := C_Dist_Lines_Exists_Rec.ccid;
1196 l_dist_lines_tbl(l_count + 1).description := C_Dist_Lines_Exists_Rec.description;
1197 If (nvl(l_dist_lines_tbl(l_count + 1).amount_cr,0) <> nvl(p_rd_amount_cr,0) Or
1198 nvl(l_dist_lines_tbl(l_count + 1).amount_dr,0) <> nvl(p_rd_amount_dr,0) Or
1199 nvl(l_dist_lines_tbl(l_count + 1).description, 'NULL') <> nvl(p_rd_description, 'NULL')) Then
1200 Raise Reci_Dupl_Row_Excpt;
1201 End If;
1202 End Loop;
1203
1204 If l_dist_lines_tbl(l_count + 1).dist_id is null Then
1205 l_reci_dist_line_insert := 'Y';
1206 l_dist_lines_tbl(l_count + 1).trx_id := l_trx_id;
1207 For C_Dist_Id_Rec In C_Dist_Id Loop
1208 l_dist_lines_tbl(l_count + 1).dist_id := C_Dist_Id_Rec.dist_id;
1209 l_dist_lines_tbl(l_count + 1).dist_number := p_dist_number;
1210 End Loop;
1211
1212 l_dist_lines_tbl(l_count + 1).party_id := l_recipient_id;
1213 l_dist_lines_tbl(l_count + 1).party_type_flag := 'R';
1214 l_dist_lines_tbl(l_count + 1).dist_type_flag := 'L';
1215 l_dist_lines_tbl(l_count + 1).amount_cr := p_rd_amount_cr;
1216 l_dist_lines_tbl(l_count + 1).amount_dr := p_rd_amount_dr;
1217 l_dist_lines_tbl(l_count + 1).dist_number := p_dist_number;
1218
1219 -- Modified on 16th April 2005
1220 l_dr_sum := 0;
1221 l_cr_sum := 0;
1222 For C_Sum_Dist_Rec in C_Sum_Dist(
1223 l_trx_id,
1224 l_recipient_id,
1225 'R') Loop
1226 l_dr_sum := C_Sum_Dist_Rec.Dr_Sum;
1227 l_cr_sum := C_Sum_Dist_Rec.Cr_Sum;
1228 End Loop;
1229 -- Modified on 16th April 2005
1230 If l_init_amount_cr is not null Then
1231 If (l_dist_lines_tbl(l_count + 1).amount_cr is not null And
1232 l_dist_lines_tbl(l_count + 1).amount_dr is null) Then
1233 -- Modified on 16th April 2005
1234 /* This validation is replaced by importer.
1235 If ((l_dist_lines_tbl(l_count + 1 ).amount_cr + l_cr_sum)
1236 > l_init_amount_cr) Then
1237 Raise Reci_Dist_Sum_Excpt;
1238 End If;*/
1239 NULL;
1240 -- Modified on 16th April 2005
1241 Else
1242 Raise Reci_Amount_Excpt;
1243 End If;
1244 End If;
1245
1246 If l_init_amount_dr is not null Then
1247 If (l_dist_lines_tbl(l_count + 1).amount_dr is not null And
1248 l_dist_lines_tbl(l_count + 1).amount_cr is null) Then
1249 -- Modified on 16th April 2005
1250 /* This validation is replaced by importer.
1251 If ((l_dist_lines_tbl(l_count + 1 ).amount_dr + l_dr_sum)
1252 > l_init_amount_dr) Then
1253 Raise Reci_Dist_Sum_Excpt;
1254 End If; */
1255 NULL;
1256 -- Modified on 16th April 2005
1257 Else
1258 Raise Reci_Amount_Excpt;
1259 End If;
1260 End If;
1261
1262 l_dist_lines_tbl(l_count + 1).description := p_rd_description;
1263 End If;
1264 End If ;--end of recipient validation
1265 Validate_Record(
1266 l_batch_rec,
1267 l_trx_tbl,
1268 l_dist_lines_tbl,
1269 l_batch_insert);
1270
1271 Insert_Batch(
1272 l_batch_rec,
1273 p_initiator_name,
1274 p_trx_type_name,
1275 l_from_le_name,
1276 l_batch_insert);
1277
1278 If nvl(l_trx_insert,'N') = 'Y' Then
1279 Insert_Header(
1280 p_recipient_name,
1281 l_to_le_name,
1282 l_trx_tbl);
1283 End If;
1284
1285 If nvl(l_init_dist_line_insert,'N') = 'Y' Then
1286 Insert_Dists(
1287 l_dist_lines_tbl,
1288 l_count);
1289 End if;
1290
1291 If nvl(l_reci_dist_line_insert,'N') = 'Y' Then
1292 Insert_Dists(
1293 l_dist_lines_tbl,
1294 l_count + 1);
1295 End if;
1296 --Bug: 8966932
1297 --Commit;
1298 Exception
1299
1300 When Batch_Num_Invalid_Excpt Then --6846666
1301 Rollback;
1302 Fnd_Message.Set_Name('FUN','FUN_BATCH_NUM_INVALID');
1303 Fnd_Message.Raise_Error;
1304
1305 When Trx_Num_Not_Numeric_Excpt Then
1306 Rollback;
1307 Fnd_Message.Set_Name('FUN','FUN_TRX_NUM_NOT_NUMERIC');
1308 Fnd_Message.Raise_Error;
1309
1310 When Initiator_Excpt Then
1311 Rollback;
1312 Fnd_Message.Set_Name('FUN','FUN_API_INVALID_INITIATOR');
1313 Fnd_Message.Raise_Error;
1314 When Trx_Type_Excpt Then
1315 Rollback;
1316 Fnd_Message.Set_Name('FUN','FUN_TRX_TYPE_NOT_FOUND');
1317 Fnd_Message.Raise_Error;
1318 When Tran_Reci_Diff_Excpt Then
1319 Rollback;
1320 Fnd_Message.Set_Name('FUN','FUN_ADI_TRAN_RECI_DIFF');
1321 Fnd_Message.Set_Token('RECI_NAME', l_reci_name);
1322 Fnd_Message.Raise_Error;
1323 When Tran_Dupl_Row_Excpt Then
1324 Rollback;
1325 Fnd_Message.Set_Name('FUN','FUN_ADI_TRAN_DUP_ERROR');
1326 Fnd_Message.Set_Token('BATCH_NUMBER', l_batch_rec.batch_number);
1327 Fnd_Message.Set_Token('TRX_NUMBER', l_trx_tbl(l_count).trx_number);
1328 Fnd_Message.Set_Token('DEBIT', l_trx_tbl(l_count).init_amount_dr);
1329 Fnd_Message.Set_Token('CREDIT', l_trx_tbl(l_count).init_amount_cr);
1330 Fnd_Message.Raise_Error;
1331 When Recipient_Excpt Then
1332 Rollback;
1333 Fnd_Message.Set_Name('FUN','FUN_API_INVALID_RECIPIENT');
1334 Fnd_Message.Raise_Error;
1335 When Dupl_Reci_In_Batch Then
1336 Rollback;
1337 Fnd_Message.Set_Name('FUN', 'FUN_API_DUPLICATE_RECP');
1338 Fnd_Message.Raise_Error;
1339 When Amount_Req_Excpt Then
1340 Rollback;
1341 Fnd_Message.Set_Name('FUN','FUN_TRX_DR_CR_AMT');
1342 Fnd_Message.Raise_Error;
1343 When Init_Dupl_Row_Excpt Then
1344 Rollback;
1345 Fnd_Message.Set_Name('FUN','FUN_ADI_INIT_DUP_ERROR');
1346 Fnd_Message.Set_Token('DIST_NUMBER', p_dist_number);
1347 Fnd_Message.Set_Token('TRX_NUMBER', l_trx_tbl(l_count).trx_number);
1348 Fnd_Message.Set_Token('DEBIT', l_dist_lines_tbl(l_count).amount_dr);
1349 Fnd_Message.Set_Token('CREDIT', l_dist_lines_tbl(l_count).amount_cr);
1350 Fnd_Message.Set_Token('DESCRIPTION', l_dist_lines_tbl(l_count).description);
1351 Fnd_Message.Raise_Error;
1352 When Init_Dist_Sum_Excpt Then
1353 Rollback;
1354 Fnd_Message.Set_Name('FUN','FUN_ADI_INIT_SUM_ERROR');
1355 Fnd_Message.Raise_Error;
1356 When Init_Amount_Excpt Then
1357 Rollback;
1358 Fnd_Message.Set_Name('FUN','FUN_IC_INVALID_DRCR_DIST');
1359 Fnd_Message.Set_Token('TRX_NUMBER', p_trx_number);
1360 Fnd_Message.Raise_Error;
1361 When Con_Seg_Not_Req_Excpt Then
1362 Rollback;
1363 Fnd_Message.Set_Name('FUN','FUN_ADI_CON_SEGS_NOT_REQ');
1364 Fnd_Message.Raise_Error;
1365 When Con_Seg_Req_Excpt Then
1366 Rollback;
1367 Fnd_Message.Set_Name('FUN','FUN_ADI_CON_SEGS_REQ');
1368 Fnd_Message.Raise_Error;
1369 When Ccid_Gen_Excpt Then
1370 Rollback;
1371 Fnd_Message.Set_Name('FUN','FUN_ADI_CCID_ERROR');
1372 Fnd_Message.Set_Token('CON_SEGS', p_rd_ccid_segments);
1373 Fnd_Message.Raise_Error;
1374 When Reci_Dupl_Row_Excpt Then
1375 Rollback;
1376 Fnd_Message.Set_Name('FUN','FUN_ADI_RECI_DUP_ERROR');
1377 Fnd_Message.Set_Token('DIST_NUMBER', p_dist_number);
1378 Fnd_Message.Set_Token('TRX_NUMBER', l_trx_number);
1379 Fnd_Message.Set_Token('DEBIT', l_dist_lines_tbl(l_count + 1).amount_dr);
1380 Fnd_Message.Set_Token('CREDIT', l_dist_lines_tbl(l_count + 1 ).amount_cr);
1381 Fnd_Message.Set_Token('DESCRIPTION', l_dist_lines_tbl(l_count + 1 ).description);
1382 Fnd_Message.Raise_Error;
1383 When Reci_Dist_Sum_Excpt Then
1384 Rollback;
1385 Fnd_Message.Set_Name('FUN','FUN_ADI_RECI_SUM_ERROR');
1386 Fnd_Message.Raise_Error;
1387 When Reci_Amount_Excpt Then
1388 Rollback;
1389 Fnd_Message.Set_Name('FUN','FUN_ADI_INVALID_DRCR_DIST');
1390 Fnd_Message.Set_Token('TRX_NUMBER', p_trx_number);
1391 Fnd_Message.Raise_Error;
1392 When Fnd_Api.G_EXC_UNEXPECTED_ERROR Then
1393 Rollback;
1394 Get_Message_Text(l_data);
1395 Fnd_Message.Set_Name('FUN', 'FUN_ADI_ERROR');
1396 Fnd_Message.Set_Token('ERROR', l_data);
1397 Fnd_Message.Raise_Error;
1398 When Fnd_Api.G_EXC_ERROR Then
1399 Rollback;
1400 Get_Message_Text(l_data);
1401 Fnd_Message.Set_Name('FUN', 'FUN_ADI_ERROR');
1402 Fnd_Message.Set_Token('ERROR', l_data);
1403 When Others Then
1404 Rollback;
1405 Fnd_Message.Set_Name('FUN', 'FUN_ADI_ERROR');
1406 Fnd_Message.Set_Token('ERROR', sqlerrm);
1407 Fnd_Message.Raise_Error;
1408 End Upload_Batch;
1409
1410 --Bug: 10095345
1411
1412 Procedure Delete_Batch(
1413 p_batch_number In Fun_Interface_Batches.batch_number%type) IS
1414
1415
1416 BEGIN
1417
1418 DELETE Fun_Interface_Dist_Lines
1419 WHERE trx_id in (SELECT trx_id
1420 FROM Fun_Interface_Headers fih,
1421 fun_interface_batches fib
1422 WHERE fih.batch_id = fib.batch_id
1423 AND fib.source = 'Global Intercompany'
1424 AND fib.batch_number = p_batch_number);
1425
1426 DELETE Fun_Interface_Headers
1427 WHERE batch_id in (SELECT batch_id
1428 FROM fun_interface_batches
1429 WHERE source = 'Global Intercompany'
1430 AND batch_number = p_batch_number);
1431
1432 DELETE Fun_Interface_Controls
1433 WHERE GROUP_ID IN (SELECT GROUP_ID
1434 FROM fun_interface_batches
1435 WHERE source = 'Global Intercompany'
1436 AND batch_number = p_batch_number)
1437 AND SOURCE = 'Global Intercompany';
1438
1439 DELETE fun_interface_batches
1440 WHERE source = 'Global Intercompany'
1441 AND batch_number = p_batch_number;
1442
1443 COMMIT;
1444
1445 EXCEPTION
1446 WHEN OTHERS THEN
1447
1448 RAISE;
1449
1450 END;
1451
1452 --Bug: 10095345
1453 Procedure Import_Batch(
1454 p_batch_number In Fun_Interface_Batches.batch_number%type) IS
1455
1456 CURSOR c_dist_totals IS
1457 SELECT trxh.trx_number, party_type_flag, dist_amount,
1458 DECODE(init_amount_cr, null, init_amount_dr, init_amount_cr) trx_amount
1459 FROM fun_interface_headers trxH,
1460 (SELECT trxH.trx_id,
1461 fd.dist_type_flag,
1462 party_type_flag ,
1463 sum(decode(fd.amount_cr, null, fd.amount_dr, fd.amount_cr)) DIST_AMOUNT
1464 FROM fun_interface_headers trxH, FUN_INTERFACE_DIST_LINES fd
1465 WHERE trxH.trx_id = fd.trx_id
1466 AND dist_type_flag = 'L'
1467 GROUP BY trxH.trx_id,
1468 fd.dist_type_flag,
1469 party_type_flag
1470 ORDER BY trxH.trx_id) fun_dists
1471 WHERE fun_dists.trx_id = trxh.trx_id
1472 AND trxh.batch_id in (SELECT batch_id
1473 FROM fun_interface_batches
1474 WHERE source = 'Global Intercompany'
1475 AND batch_number = p_batch_number)
1476 ORDER BY party_type_flag;
1477
1478 l_batch_number Fun_Interface_Batches.batch_number%TYPE;
1479 l_trx_number NUMBER;
1480 INVALID_INIT_EXCPT EXCEPTION;
1481 INVALID_RECI_EXCPT EXCEPTION;
1482 v VARCHAR2(1000);
1483
1484 BEGIN
1485
1486 FOR r_dist_totals in c_dist_totals LOOP
1487
1488 l_trx_number := r_dist_totals.trx_number;
1489 IF r_dist_totals.dist_amount > r_dist_totals.trx_amount
1490 AND r_dist_totals.party_type_flag = 'R' THEN
1491
1492 RAISE INVALID_RECI_EXCPT;
1493
1494 ELSE
1495 IF r_dist_totals.dist_amount <> r_dist_totals.trx_amount
1496 AND r_dist_totals.party_type_flag = 'I' THEN
1497
1498 RAISE INVALID_INIT_EXCPT;
1499
1500 END IF;
1501 END IF;
1502
1503 END LOOP;
1504
1505 EXCEPTION
1506
1507 WHEN INVALID_INIT_EXCPT THEN
1508
1509 Delete_Batch(p_batch_number);
1510 FND_MESSAGE.Set_Name('FUN','FUN_IC_INI_HDR_DIST_MISMATCH');
1511 FND_MESSAGE.Set_Token ('TRX_NUMBER',l_trx_number);
1512 v := FND_MESSAGE.GET;
1513 Raise_Application_Error(-20000,v);
1514 WHEN INVALID_RECI_EXCPT THEN
1515
1516 Delete_Batch(p_batch_number);
1517 v :=Fnd_Message.Get_String('FUN','FUN_ADI_RECI_SUM_ERROR');
1518 Raise_Application_Error(-20000,'Transaction '||l_trx_number || ': '||v);
1519 WHEN OTHERS THEN
1520
1521 Delete_Batch(p_batch_number);
1522 RAISE;
1523 END;
1524 End FUN_WEBADI_PKG;
1525