[Home] [Help]
PACKAGE BODY: APPS.FUN_WEBADI_PKG
Source
1 Package Body FUN_WEBADI_PKG As
2 -- $Header: funwadib.pls 120.13.12010000.3 2008/09/02 07:49:31 srampure 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;
373 l_trx_tbl(l_count).approver_id := p_trx_tbl(l_count).approver_id;
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;
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;
472 1.0,
469 l_msg_data := null;
470
471 Fun_Trx_Pvt.Init_Trx_Validate (
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 If l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR Then
484 Raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
485 Elsif l_return_status = Fnd_Api.G_RET_STS_ERROR Then
486 Raise Fnd_Api.G_EXC_ERROR;
487 End If;
488
489 If p_dist_lines_tbl is not null Then
490 For l_count In 1..p_dist_lines_tbl.count Loop
491 l_dist_lines_rec_type.dist_id := p_dist_lines_tbl(l_count).dist_id;
492 l_dist_lines_rec_type.line_id := p_dist_lines_tbl(l_count).line_id;
493 l_dist_lines_rec_type.party_id := p_dist_lines_tbl(l_count).party_id;
494 l_dist_lines_rec_type.party_type := p_dist_lines_tbl(l_count).party_type_flag;
495 l_dist_lines_rec_type.dist_type := p_dist_lines_tbl(l_count).dist_type_flag;
496 l_dist_lines_rec_type.batch_dist_id := p_dist_lines_tbl(l_count).batch_dist_id;
497 l_dist_lines_rec_type.amount_cr := p_dist_lines_tbl(l_count).amount_cr;
498 l_dist_lines_rec_type.amount_dr := p_dist_lines_tbl(l_count).amount_dr;
499 l_dist_lines_rec_type.ccid := p_dist_lines_tbl(l_count).ccid;
500
501 l_return_status := null;
502 l_msg_count := null;
503 l_msg_data := null;
504
505
506 -- 24-10-2007 Changes made by MAKANSAl for Bug # 6249898
507 -- If the distribution line has the party type as 'R' then the recipient
508 -- legal entity id is passed so that the validation for BSV linkage
509 -- is successfully.
510
511 If l_dist_lines_rec_type.party_type = 'R' Then
512
513 --Fectch the recipient Legal Entity Id
514 l_le_party_id := null;
515 l_le_party_id := Fun_Tca_Pkg.Get_Le_Id(l_dist_lines_rec_type.party_id, sysdate);
516
517 For C_Le_Id_Rec In C_Le_Id(l_le_party_id) Loop
518 l_to_le_id := C_Le_Id_Rec.legal_entity_id;
519 End Loop;
520
521 -- Pass Recipient Legal entity Id
522
523 Fun_Trx_Pvt.Init_IC_Dist_Validate (
524 1.0,
525 Fnd_Api.G_TRUE,
526 l_validation_level,
527 l_to_le_id,
528 l_trx_rec_type.to_ledger_id,
529 --p_batch_rec.from_ledger_id,
530 l_return_status,
531 l_msg_count,
532 l_msg_data,
533 l_dist_lines_rec_type);
534 Else
535
536 -- Changes complete for Bug # 6249898
537
538 Fun_Trx_Pvt.Init_IC_Dist_Validate (
539 1.0,
540 Fnd_Api.G_TRUE,
541 l_validation_level,
542 p_batch_rec.from_le_id,
543 p_batch_rec.from_ledger_id,
544 l_return_status,
545 l_msg_count,
546 l_msg_data,
547 l_dist_lines_rec_type);
548
549 End If;
550
551 If l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR Then
552 Raise Fnd_Api.G_EXC_UNEXPECTED_ERROR;
553 Elsif l_return_status = Fnd_Api.G_RET_STS_ERROR Then
554 Raise Fnd_Api.G_EXC_ERROR;
555 End If;
556 End Loop;
557 End If;
558 End Validate_Record;
559
560 Procedure Get_Message_Text(p_data In Out NOCOPY Varchar2) Is
561 Begin
562 p_data := Fnd_Msg_Pub.Get(
563 p_msg_index => Fnd_Msg_Pub.G_FIRST,
564 p_encoded => Fnd_Api.G_FALSE);
565 End Get_Message_Text;
566
567 Procedure Upload_Batch(
568 p_batch_number In Fun_Interface_Batches.batch_number%type,
569 p_initiator_name In Fun_Interface_Batches.initiator_name%type,
570 p_currency_code In Fun_Interface_Batches.currency_code%type,
571 p_batch_date In Fun_Interface_Batches.batch_date%type,
572 p_gl_date In Fun_Interface_Batches.gl_date%type,
573 p_trx_type_name In Fun_Interface_Batches.trx_type_name%type,
574 p_description In Fun_Interface_Batches.description%type,
575 p_note In Fun_Interface_Batches.note%type,
576 p_attribute1 In Fun_Interface_Batches.attribute1%type,
577 p_attribute2 In Fun_Interface_Batches.attribute2%type,
578 p_attribute3 In Fun_Interface_Batches.attribute3%type,
579 p_attribute4 In Fun_Interface_Batches.attribute4%type,
580 p_attribute5 In Fun_Interface_Batches.attribute5%type,
581 p_attribute6 In Fun_Interface_Batches.attribute6%type,
582 p_attribute7 In Fun_Interface_Batches.attribute7%type,
583 p_attribute8 In Fun_Interface_Batches.attribute8%type,
584 p_attribute9 In Fun_Interface_Batches.attribute9%type,
585 p_attribute10 In Fun_Interface_Batches.attribute10%type,
586 p_attribute11 In Fun_Interface_Batches.attribute11%type,
587 p_attribute12 In Fun_Interface_Batches.attribute12%type,
588 p_attribute13 In Fun_Interface_Batches.attribute13%type,
589 p_attribute14 In Fun_Interface_Batches.attribute14%type,
593 p_recipient_name In Fun_Interface_Headers.recipient_name%type,
590 p_attribute15 In Fun_Interface_Batches.attribute15%type,
591 p_attribute_category In Fun_Interface_Batches.attribute_category%type,
592 p_trx_number In Fun_Interface_Headers.trx_number%type,
594 p_init_amount_dr In Fun_Interface_Headers.init_amount_dr%type,
595 p_init_amount_cr In Fun_Interface_Headers.init_amount_cr%type,
596 p_h_attribute1 In Fun_Interface_Headers.attribute1%type,
597 p_h_attribute2 In Fun_Interface_Headers.attribute2%type,
598 p_h_attribute3 In Fun_Interface_Headers.attribute3%type,
599 p_h_attribute4 In Fun_Interface_Headers.attribute4%type,
600 p_h_attribute5 In Fun_Interface_Headers.attribute5%type,
601 p_h_attribute6 In Fun_Interface_Headers.attribute6%type,
602 p_h_attribute7 In Fun_Interface_Headers.attribute7%type,
603 p_h_attribute8 In Fun_Interface_Headers.attribute8%type,
604 p_h_attribute9 In Fun_Interface_Headers.attribute9%type,
605 p_h_attribute10 In Fun_Interface_Headers.attribute10%type,
606 p_h_attribute11 In Fun_Interface_Headers.attribute11%type,
607 p_h_attribute12 In Fun_Interface_Headers.attribute12%type,
608 p_h_attribute13 In Fun_Interface_Headers.attribute13%type,
609 p_h_attribute14 In Fun_Interface_Headers.attribute14%type,
610 p_h_attribute15 In Fun_Interface_Headers.attribute15%type,
611 p_h_attribute_category In Fun_Interface_Headers.attribute_category%type,
612 p_id_ccid In Fun_Interface_Dist_Lines.ccid%type,
613 p_id_amount_dr In Fun_Interface_Dist_Lines.amount_dr%type,
614 p_id_amount_cr In Fun_Interface_Dist_Lines.amount_cr%type,
615 p_id_description In Fun_Interface_Dist_Lines.description%type,
616 p_id_attribute1 In Fun_Interface_Dist_Lines.attribute1%type,
617 p_id_attribute2 In Fun_Interface_Dist_Lines.attribute2%type,
618 p_id_attribute3 In Fun_Interface_Dist_Lines.attribute3%type,
619 p_id_attribute4 In Fun_Interface_Dist_Lines.attribute4%type,
620 p_id_attribute5 In Fun_Interface_Dist_Lines.attribute5%type,
621 p_id_attribute6 In Fun_Interface_Dist_Lines.attribute6%type,
622 p_id_attribute7 In Fun_Interface_Dist_Lines.attribute7%type,
623 p_id_attribute8 In Fun_Interface_Dist_Lines.attribute8%type,
624 p_id_attribute9 In Fun_Interface_Dist_Lines.attribute9%type,
625 p_id_attribute10 In Fun_Interface_Dist_Lines.attribute10%type,
626 p_id_attribute11 In Fun_Interface_Dist_Lines.attribute11%type,
627 p_id_attribute12 In Fun_Interface_Dist_Lines.attribute12%type,
628 p_id_attribute13 In Fun_Interface_Dist_Lines.attribute13%type,
629 p_id_attribute14 In Fun_Interface_Dist_Lines.attribute14%type,
630 p_id_attribute15 In Fun_Interface_Dist_Lines.attribute15%type,
631 p_id_attribute_category In Fun_Interface_Dist_Lines.attribute_category%type,
632 p_rd_ccid_segments In Varchar2,
633 p_rd_ccid In Fun_Interface_Dist_Lines.ccid%type,
634 p_rd_amount_dr In Fun_Interface_Dist_Lines.amount_dr%type,
635 p_rd_amount_cr In Fun_Interface_Dist_Lines.amount_cr%type,
636 p_rd_description In Fun_Interface_Dist_Lines.description%type,
637 p_dist_number In Fun_Interface_Dist_Lines.dist_number%type) Is
638
639 Cursor C_Batch_Exists(cp_batch_number In Fun_Interface_Batches.batch_number%type) Is
640 Select *
641 From Fun_Interface_Batches
642 Where batch_number = cp_batch_number;
643
644 Cursor C_Batch_Id Is
645 Select Fun_Trx_Batches_S.nextval batch_id
646 From Dual;
647
648 -- Bug No: 6134848. 2 more condetions added to validate Party_usage_code.
649
650 Cursor C_Party(cp_party_name In Fun_Interface_Batches.initiator_name%type) Is
651 Select hzp.party_id
652 From Hz_Parties Hzp, hz_party_usg_assignments hu
653 Where hzp.party_type = 'ORGANIZATION'
654 And hzp.party_name = cp_party_name
655 And hzp.party_id = hu.party_id
656 And hu.party_usage_code = 'INTERCOMPANY_ORG';
657
658 --Bug No: 6134848 ends here
659
660 Cursor C_Le_Id(cp_le_party_id In Xle_Entity_Profiles.party_id%type) Is
661 Select legal_entity_id
662 From Xle_Firstparty_Information_V
663 Where party_id = cp_le_party_id;
664
665 Cursor C_Le_Name (cp_party_id In Xle_Entity_Profiles.party_id%type) Is
666 Select name
667 From xle_firstparty_information_v
668 Where party_id = cp_party_id;
669
670 Cursor C_Trx_Type (cp_trx_type_name In Fun_Interface_Batches.trx_type_name%type) Is
671 Select trx_type_code, trx_type_id, allow_invoicing_flag
672 From Fun_Trx_Types_Vl
673 Where trx_type_name = cp_trx_type_name;
674
675 Cursor C_Trx_Number_Exists(
676 cp_batch_id In Fun_Interface_Headers.batch_id%type,
677 cp_trx_number In Fun_Interface_Headers.trx_number%type) Is
678 Select *
679 From Fun_Interface_Headers
680 Where batch_id = cp_batch_id
681 And trx_number = cp_trx_number;
682
686
683 Cursor C_Trx_Id Is
684 Select Fun_Trx_Headers_S.nextval trx_id
685 From Dual;
687 Cursor C_Dupl_Reci_In_Batch(
688 cp_batch_id In Fun_Interface_Headers.batch_id%type,
689 cp_trx_number In Fun_Interface_Headers.trx_number%type,
690 cp_party_name In Fun_Interface_Headers.recipient_name%type) Is
691 Select count(distinct trx_number) dupl_reci_count
692 From Fun_Interface_Headers
693 Where batch_id = cp_batch_id
694 And trx_number <> cp_trx_number
695 And recipient_name = cp_party_name;
696
697 Cursor C_Dist_Lines_Exists(
698 cp_trx_id In Fun_Interface_Dist_Lines.trx_id%type,
699 cp_party_id In Fun_Interface_Dist_Lines.party_id%type,
700 cp_ccid In Fun_Interface_Dist_Lines.ccid%type,
701 cp_party_type_flag In Fun_Interface_Dist_Lines.party_type_flag%type,
702 cp_dist_number in Fun_Interface_Dist_Lines.dist_number%type) Is
703 Select *
704 From Fun_Interface_Dist_Lines
705 Where trx_id = cp_trx_id
706 And party_id = cp_party_id
707 And ccid = cp_ccid
708 And party_type_flag = cp_party_type_flag
709 And dist_number=cp_dist_number;
710
711 Cursor C_Dist_Id Is
712 Select Fun_Dist_Lines_S.nextval dist_id
713 From dual;
714
715 Cursor C_Ccid(cp_party_name In Fun_Interface_Batches.initiator_name%type) Is
716 Select chart_of_accounts_id
717 From Hz_Parties hzp,
718 xle_firstparty_information_v xfi ,
719 Gl_Ledger_Le_V led
720 Where hzp.party_name = cp_party_name
721 And fun_tca_pkg.get_le_id(hzp.party_id) = xfi.party_id
722 And xfi.legal_entity_id = led.legal_entity_id
723 And led.ledger_category_code = 'PRIMARY';
724
725 Cursor C_Flex_Info(cp_chart_of_accounts_id In Fnd_Id_Flex_Structures.id_flex_num%type) Is
726 Select fa.application_short_name appl_short_name
727 From Fnd_Id_Flex_Structures fs, Fnd_Application fa
728 Where fs.application_id = fa.application_id
729 And id_flex_num = cp_chart_of_accounts_id
730 And id_flex_code = 'GL#';
731
732 Cursor C_Sum_Dist(
733 cp_trx_id In Fun_Interface_Dist_Lines.trx_id%type,
734 cp_party_id In Fun_Interface_Dist_Lines.party_id%type,
735 cp_party_type_flag In Fun_Interface_Dist_Lines.party_type_flag%type) Is
736 Select nvl(Sum(nvl(amount_dr,0)),0) Dr_Sum,
737 nvl(Sum(nvl(amount_cr,0)),0) Cr_Sum
738 From Fun_Interface_Dist_Lines
739 Where trx_id = cp_trx_id
740 And party_id = cp_party_id
741 And party_type_flag = cp_party_type_flag;
742
743 l_count Number;
744 l_batch_insert Varchar2(1);
745 l_le_party_id Xle_Firstparty_Information_V.party_id%type;
746 l_from_le_name Fun_Interface_Batches.from_le_name%type;
747 l_reci_name Fun_Interface_Headers.recipient_name%type;
748 l_trx_insert Varchar2(1);
749 l_dup_reci_count Number;
750 l_to_le_name Fun_Interface_Headers.to_le_name%type;
751 l_init_dist_line_insert Varchar2(1);
752 l_init_chart_of_accounts Fnd_Id_Flex_Structures.id_flex_num%type;
753 l_reci_chart_of_accounts Fnd_Id_Flex_Structures.id_flex_num%type;
754 l_appl_short_name Fnd_Application.application_short_name%type;
755 l_reci_dist_line_insert Varchar2(1);
756 l_data varchar2(2000);
757 l_dr_sum Fun_Interface_Dist_Lines.amount_dr%type;
758 l_cr_sum Fun_Interface_Dist_Lines.amount_cr%type;
759 l_trx_id Fun_trx_headers.trx_id%type;
760 l_recipient_id Fun_trx_headers.recipient_id%type;
761 l_init_amount_cr Fun_Interface_Headers.init_amount_cr%type;
762 l_init_amount_dr Fun_Interface_Headers.init_amount_dr%type;
763 l_trx_number Fun_Interface_Headers.trx_number%type;
764
765 l_batch_rec FUN_TRX_PUB.full_batch_rec_type;
766 l_trx_tbl FUN_TRX_PUB.full_trx_tbl_type;
767 l_dist_lines_tbl FUN_TRX_PUB.full_dist_line_tbl_type;
768
769 l_num NUMBER;
770
771 Initiator_Excpt Exception;
772 Trx_Type_Excpt Exception;
773 Tran_Reci_Diff_Excpt Exception;
774 Tran_Dupl_Row_Excpt Exception;
775 Recipient_Excpt Exception;
776 Dupl_Reci_In_Batch Exception;
777 Amount_Req_Excpt Exception;
778 Init_Dupl_Row_Excpt Exception;
779 Init_Dist_Sum_Excpt Exception;
780 Init_Amount_Excpt Exception;
781 Con_Seg_Not_Req_Excpt Exception;
782 Con_Seg_Req_Excpt Exception;
783 Ccid_Gen_Excpt Exception;
784 Reci_Dupl_Row_Excpt Exception;
785 Reci_Dist_Sum_Excpt Exception;
786 Reci_Amount_Excpt Exception;
787 Trx_Num_Not_Numeric_Excpt Exception;
788
789 Begin
790 l_count := 1;
791 l_batch_rec.batch_number := p_batch_number;
792
793 For C_Batch_Exists_Rec In C_Batch_Exists(l_batch_rec.batch_number) Loop
794 l_batch_insert := 'N';
795 l_batch_rec.batch_id := C_Batch_Exists_Rec.batch_id;
796 End Loop;
797
798 If l_batch_rec.batch_id is null Then
799 l_batch_insert := 'Y';
800 For C_Batch_Id_Rec In C_Batch_Id Loop
801 l_batch_rec.batch_id := C_Batch_ID_Rec.batch_id;
802 End Loop;
803 End If;
804
805 For C_Party_Rec In C_Party(p_initiator_name) Loop
806 l_batch_rec.initiator_id := C_Party_Rec.party_id;
807 End Loop;
808
812
809 If l_batch_rec.initiator_id is null Then
810 Raise Initiator_Excpt;
811 End If;
813 l_le_party_id := fun_tca_pkg.get_le_id(l_batch_rec.initiator_id, sysdate);
814
815 For C_Le_Id_Rec In C_Le_Id(l_le_party_id) Loop
816 l_batch_rec.from_le_id := C_Le_Id_Rec.legal_entity_id;
817 End Loop;
818
819 For C_Le_Name_Rec In C_Le_Name(l_le_party_id) Loop
820 l_from_le_name := C_Le_Name_Rec.name;
821 End Loop;
822
823 l_batch_rec.from_ledger_id := fun_trx_entry_util.Get_Ledger_id(l_batch_rec.initiator_id,'ORGANIZATION');
824 l_batch_rec.currency_code := p_currency_code ;
825 l_batch_rec.exchange_rate_type := fun_system_options_pkg.get_exchg_rate_type;
826 l_batch_rec.description := p_description;
827 l_batch_rec.note := p_note;
828
829 For C_Trx_Type_Rec In C_Trx_Type( p_trx_type_name) Loop
830 l_batch_rec.trx_type_id := C_Trx_Type_Rec.trx_type_id;
831 l_batch_rec.trx_type_code := C_Trx_Type_Rec.trx_type_code;
832 l_trx_tbl(l_count).invoice_flag := C_Trx_Type_Rec.allow_invoicing_flag;
833 End Loop;
834
835 If l_batch_rec.trx_type_id is null Then
836 Raise Trx_Type_Excpt;
837 End If;
838
839 l_batch_rec.gl_date := trunc(p_gl_date);
840 l_batch_rec.batch_date := trunc(p_batch_date);
841
842 If fun_system_options_pkg.get_allow_reject = TRUE Then
843 l_batch_rec.reject_allow_flag := 'Y';
844 Else
845 l_batch_rec.reject_allow_flag := 'N';
846 End If;
847
848 l_batch_rec.Attribute1 := p_attribute1;
849 l_batch_rec.Attribute2 := p_attribute2;
850 l_batch_rec.Attribute3 := p_attribute3;
851 l_batch_rec.Attribute4 := p_attribute4;
852 l_batch_rec.Attribute5 := p_attribute5;
853 l_batch_rec.Attribute6 := p_attribute6;
854 l_batch_rec.Attribute7 := p_attribute7;
855 l_batch_rec.Attribute8 := p_attribute8;
856 l_batch_rec.Attribute9 := p_attribute9;
857 l_batch_rec.Attribute10 := p_attribute10;
858 l_batch_rec.Attribute11 := p_attribute11;
859 l_batch_rec.Attribute12 := p_attribute12;
860 l_batch_rec.Attribute13 := p_attribute13;
861 l_batch_rec.Attribute14 := p_attribute14;
862 l_batch_rec.Attribute15 := p_attribute15;
863 l_batch_rec.attribute_category := p_attribute_category;
864
865 l_trx_tbl(l_count).trx_number := p_trx_number;
866
867 -- Validate p_trx_number
868 BEGIN
869 l_num := replace(translate(p_trx_number,
870 'N01234567890','XNNNNNNNNNN'),'N',null);
871 EXCEPTION
872 WHEN OTHERS THEN
873 Raise Trx_Num_Not_Numeric_Excpt;
874 END;
875
876
877 For C_Trx_Number_Exists_Rec In C_Trx_Number_Exists(
878 l_batch_rec.batch_id,
879 l_trx_tbl(l_count).trx_number) Loop
880
881 If (C_Trx_Number_Exists_Rec.recipient_name <> p_recipient_name) Then
882 l_reci_name := C_Trx_Number_Exists_Rec.recipient_name;
883 Raise Tran_Reci_Diff_Excpt;
884 End If;
885
886 l_trx_insert := 'N';
887 l_trx_tbl(l_count).trx_id := C_Trx_Number_Exists_Rec.trx_id;
888 l_trx_tbl(l_count).recipient_id := C_Trx_Number_Exists_Rec.recipient_id;
889 l_trx_tbl(l_count).to_le_id := C_Trx_Number_Exists_Rec.to_le_id;
890 l_trx_tbl(l_count).to_ledger_id := C_Trx_Number_Exists_Rec.to_ledger_id;
891 l_trx_tbl(l_count).batch_id := C_Trx_Number_Exists_Rec.batch_id ;
892 l_trx_tbl(l_count).init_amount_cr := C_Trx_Number_Exists_Rec.init_amount_cr;
893 l_trx_tbl(l_count).init_amount_dr := C_Trx_Number_Exists_Rec.init_amount_dr;
894
895 If (nvl(l_trx_tbl(l_count).init_amount_cr,0) <> nvl(p_init_amount_cr,0) Or
896 nvl(l_trx_tbl(l_count).init_amount_dr,0) <> nvl(p_init_amount_dr,0)) Then
897 Raise Tran_Dupl_Row_Excpt;
898 End If;
899
900 l_trx_tbl(l_count).reci_amount_cr := C_Trx_Number_Exists_Rec.init_amount_cr;
901 l_trx_tbl(l_count).reci_amount_dr := C_Trx_Number_Exists_Rec.init_amount_dr;
902 l_trx_tbl(l_count).invoice_flag := C_Trx_Number_Exists_Rec.invoicing_rule_flag;
903 l_trx_tbl(l_count).from_recurring_trx_id := C_Trx_Number_Exists_Rec.from_recurring_trx_id;
904 l_trx_tbl(l_count).initiator_instance_flag := C_Trx_Number_Exists_Rec.initiator_instance_flag;
905 l_trx_tbl(l_count).recipient_instance_flag := C_Trx_Number_Exists_Rec.recipient_instance_flag;
906 l_trx_tbl(l_count).Attribute1 := C_Trx_Number_Exists_Rec.attribute1;
907 l_trx_tbl(l_count).Attribute2 := C_Trx_Number_Exists_Rec.attribute2;
908 l_trx_tbl(l_count).Attribute3 := C_Trx_Number_Exists_Rec.attribute3;
909 l_trx_tbl(l_count).Attribute4 := C_Trx_Number_Exists_Rec.attribute4;
910 l_trx_tbl(l_count).Attribute5 := C_Trx_Number_Exists_Rec.attribute5;
911 l_trx_tbl(l_count).Attribute6 := C_Trx_Number_Exists_Rec.attribute6;
912 l_trx_tbl(l_count).Attribute7 := C_Trx_Number_Exists_Rec.attribute7;
913 l_trx_tbl(l_count).Attribute8 := C_Trx_Number_Exists_Rec.attribute8;
914 l_trx_tbl(l_count).Attribute9 := C_Trx_Number_Exists_Rec.attribute9;
915 l_trx_tbl(l_count).Attribute10 := C_Trx_Number_Exists_Rec.attribute10;
916 l_trx_tbl(l_count).Attribute11 := C_Trx_Number_Exists_Rec.attribute11;
917 l_trx_tbl(l_count).Attribute12 := C_Trx_Number_Exists_Rec.attribute12;
918 l_trx_tbl(l_count).Attribute13 := C_Trx_Number_Exists_Rec.attribute13;
922 End Loop;
919 l_trx_tbl(l_count).Attribute14 := C_Trx_Number_Exists_Rec.attribute14;
920 l_trx_tbl(l_count).Attribute15 := C_Trx_Number_Exists_Rec.attribute15;
921 l_trx_tbl(l_count).attribute_category := C_Trx_Number_Exists_Rec.attribute_category;
923
924 If l_trx_tbl(l_count).trx_id is null Then
925 l_trx_insert := 'Y';
926 l_trx_tbl(l_count).batch_id := l_batch_rec.batch_id ;
927
928 For C_Trx_Id_Rec In C_Trx_Id Loop
929 l_trx_tbl(l_count).trx_id := C_Trx_Id_Rec.trx_id;
930 End Loop;
931
932 For C_Party_Rec In C_Party(p_recipient_name) Loop
933 l_trx_tbl(l_count).recipient_id := C_Party_Rec.party_id;
934 End Loop;
935
936 If l_trx_tbl(l_count).recipient_id is null Then
937 Raise Recipient_Excpt;
938 End If;
939
940
941 l_le_party_id := null;
942 l_le_party_id := Fun_Tca_Pkg.Get_Le_Id(l_trx_tbl(l_count).recipient_id, sysdate);
943 For C_Le_Id_Rec In C_Le_Id(l_le_party_id) Loop
944 l_trx_tbl(l_count).to_le_id := C_Le_Id_Rec.legal_entity_id;
945 End Loop;
946
947 For C_Le_Name_Rec In C_Le_Name(l_le_party_id) Loop
948 l_to_le_name := C_Le_Name_Rec.name;
949 End Loop;
950
951 l_trx_tbl(l_count).to_ledger_id := Fun_Trx_Entry_Util.Get_Ledger_Id(l_trx_tbl(l_count).recipient_id,'ORGANIZATION');
952
953 l_trx_tbl(l_count).init_amount_cr := p_init_amount_cr;
954 l_trx_tbl(l_count).init_amount_dr := p_init_amount_dr;
955 l_trx_tbl(l_count).reci_amount_cr := p_init_amount_cr;
956 l_trx_tbl(l_count).reci_amount_dr := p_init_amount_dr;
957
958 If (l_trx_tbl(l_count).init_amount_cr is null And
959 l_trx_tbl(l_count).init_amount_dr is null) Then
960 Raise Amount_Req_Excpt;
961 End If;
962
963 If (l_trx_tbl(l_count).init_amount_cr is not null And
964 l_trx_tbl(l_count).init_amount_dr is not null) Then
965 Raise Amount_Req_Excpt;
966 End If;
967
968 l_trx_tbl(l_count).initiator_instance_flag := 'N';
969 l_trx_tbl(l_count).recipient_instance_flag := 'N';
970 l_trx_tbl(l_count).Attribute1 := p_h_attribute1;
971 l_trx_tbl(l_count).Attribute2 := p_h_attribute2;
972 l_trx_tbl(l_count).Attribute3 := p_h_attribute3;
973 l_trx_tbl(l_count).Attribute4 := p_h_attribute4;
974 l_trx_tbl(l_count).Attribute5 := p_h_attribute5;
975 l_trx_tbl(l_count).Attribute6 := p_h_attribute6;
976 l_trx_tbl(l_count).Attribute7 := p_h_attribute7;
977 l_trx_tbl(l_count).Attribute8 := p_h_attribute8;
978 l_trx_tbl(l_count).Attribute9 := p_h_attribute9;
979 l_trx_tbl(l_count).Attribute10 := p_h_attribute10;
980 l_trx_tbl(l_count).Attribute11 := p_h_attribute11;
981 l_trx_tbl(l_count).Attribute12 := p_h_attribute12;
982 l_trx_tbl(l_count).Attribute13 := p_h_attribute13;
983 l_trx_tbl(l_count).Attribute14 := p_h_attribute14;
984 l_trx_tbl(l_count).Attribute15 := p_h_attribute15;
985 l_trx_tbl(l_count).attribute_category := p_h_attribute_category;
986 End If;
987
988 For C_Dist_Lines_Exists_Rec In C_Dist_Lines_Exists(
989 l_trx_tbl(l_count).trx_id,
990 l_batch_rec.initiator_id,
991 p_id_ccid,
992 'I', p_dist_number) Loop
993
994 l_init_dist_line_insert := 'N';
995 l_dist_lines_tbl(l_count).trx_id := C_Dist_Lines_Exists_Rec.trx_id;
996 l_dist_lines_tbl(l_count).dist_id := C_Dist_Lines_Exists_Rec.dist_id;
997 l_dist_lines_tbl(l_count).dist_number := C_Dist_Lines_Exists_Rec.dist_number;
998 l_dist_lines_tbl(l_count).party_id := C_Dist_Lines_Exists_Rec.party_id;
999 l_dist_lines_tbl(l_count).party_type_flag := C_Dist_Lines_Exists_Rec.party_type_flag;
1000 l_dist_lines_tbl(l_count).dist_type_flag := C_Dist_Lines_Exists_Rec.dist_type_flag;
1001 l_dist_lines_tbl(l_count).amount_cr := C_Dist_Lines_Exists_Rec.amount_cr;
1002 l_dist_lines_tbl(l_count).amount_dr := C_Dist_Lines_Exists_Rec.amount_dr;
1003 l_dist_lines_tbl(l_count).ccid := C_Dist_Lines_Exists_Rec.ccid;
1004 l_dist_lines_tbl(l_count).description := C_Dist_Lines_Exists_Rec.description;
1005
1006 If (nvl(l_dist_lines_tbl(l_count).amount_cr,0) <> nvl(p_id_amount_cr,0) Or
1007 nvl(l_dist_lines_tbl(l_count).amount_dr,0) <> nvl(p_id_amount_dr,0) Or
1008 nvl(l_dist_lines_tbl(l_count).description, 'NULL') <> nvl(p_id_description, 'NULL')) Then
1009 Raise Init_Dupl_Row_Excpt;
1010 End If;
1011
1012 l_dist_lines_tbl(l_count).attribute1 := C_Dist_Lines_Exists_Rec.attribute1;
1013 l_dist_lines_tbl(l_count).attribute2 := C_Dist_Lines_Exists_Rec.attribute2;
1014 l_dist_lines_tbl(l_count).attribute3 := C_Dist_Lines_Exists_Rec.attribute3;
1015 l_dist_lines_tbl(l_count).attribute4 := C_Dist_Lines_Exists_Rec.attribute4;
1016 l_dist_lines_tbl(l_count).attribute5 := C_Dist_Lines_Exists_Rec.attribute5;
1017 l_dist_lines_tbl(l_count).attribute6 := C_Dist_Lines_Exists_Rec.attribute6;
1018 l_dist_lines_tbl(l_count).attribute7 := C_Dist_Lines_Exists_Rec.attribute7;
1019 l_dist_lines_tbl(l_count).attribute8 := C_Dist_Lines_Exists_Rec.attribute8;
1020 l_dist_lines_tbl(l_count).attribute9 := C_Dist_Lines_Exists_Rec.attribute9;
1021 l_dist_lines_tbl(l_count).attribute10 := C_Dist_Lines_Exists_Rec.attribute10;
1022 l_dist_lines_tbl(l_count).attribute11 := C_Dist_Lines_Exists_Rec.attribute11;
1023 l_dist_lines_tbl(l_count).attribute12 := C_Dist_Lines_Exists_Rec.attribute12;
1024 l_dist_lines_tbl(l_count).attribute13 := C_Dist_Lines_Exists_Rec.attribute13;
1028 End Loop;
1025 l_dist_lines_tbl(l_count).attribute14 := C_Dist_Lines_Exists_Rec.attribute14;
1026 l_dist_lines_tbl(l_count).attribute15 := C_Dist_Lines_Exists_Rec.attribute15;
1027 l_dist_lines_tbl(l_count).attribute_category := p_id_attribute_category;
1029
1030 If (l_dist_lines_tbl.Count = 0 and p_id_ccid is not null) Then
1031 l_init_dist_line_insert := 'Y';
1032 l_dist_lines_tbl(l_count).trx_id := l_trx_tbl(l_count).trx_id;
1033
1034 For C_Dist_Id_Rec In C_Dist_Id Loop
1035 l_dist_lines_tbl(l_count).dist_id := C_Dist_Id_Rec.dist_id;
1036 l_dist_lines_tbl(l_count).dist_number := p_dist_number;
1037 End Loop;
1038
1039 l_dist_lines_tbl(l_count).party_id := l_batch_rec.initiator_id;
1040 l_dist_lines_tbl(l_count).party_type_flag := 'I';
1041 l_dist_lines_tbl(l_count).dist_type_flag := 'L';
1042 l_dist_lines_tbl(l_count).amount_cr := p_id_amount_cr;
1043 l_dist_lines_tbl(l_count).amount_dr := p_id_amount_dr;
1044
1045 -- Modified on 16th April 2005
1046 l_dr_sum := 0;
1047 l_cr_sum := 0;
1048 For C_Sum_Dist_Rec in C_Sum_Dist(
1049 l_trx_tbl(l_count).trx_id,
1050 l_batch_rec.initiator_id,
1051 'I') Loop
1052 l_dr_sum := C_Sum_Dist_Rec.Dr_Sum;
1053 l_cr_sum := C_Sum_Dist_Rec.Cr_Sum;
1054 End Loop;
1055 -- Modified on 16th April 2005
1056 If l_trx_tbl(l_count).init_amount_cr is not null Then
1057 If (l_dist_lines_tbl(l_count).amount_dr is not null And
1058 l_dist_lines_tbl(l_count).amount_cr is null) Then
1059 -- Modified on 16th April 2005
1060 If ((l_dist_lines_tbl(l_count).amount_dr + l_dr_sum)
1061 > l_trx_tbl(l_count).init_amount_cr) Then
1062 Raise Init_Dist_Sum_Excpt;
1063 End If;
1064 -- Modified on 16th April 2005
1065 Else
1066 Raise Init_Amount_Excpt;
1067 End If;
1068 End If;
1069
1070 If l_trx_tbl(l_count).init_amount_dr is not null Then
1071 If (l_dist_lines_tbl(l_count).amount_cr is not null And
1072 l_dist_lines_tbl(l_count).amount_dr is null) Then
1073 -- Modified on 16th April 2005
1074 If ((l_dist_lines_tbl(l_count).amount_cr + l_cr_sum) >
1075 l_trx_tbl(l_count).init_amount_dr) Then
1076 Raise Init_Dist_Sum_Excpt;
1077 End If;
1078 -- Modified on 16th April 2005
1079 Else
1080 Raise Init_Amount_Excpt;
1081 End If;
1082 End If;
1083
1084 l_dist_lines_tbl(l_count).ccid := p_id_ccid;
1085 l_dist_lines_tbl(l_count).description := p_id_description;
1086 l_dist_lines_tbl(l_count).attribute1 := p_id_attribute1;
1087 l_dist_lines_tbl(l_count).attribute2 := p_id_attribute2;
1088 l_dist_lines_tbl(l_count).attribute3 := p_id_attribute3;
1089 l_dist_lines_tbl(l_count).attribute4 := p_id_attribute4;
1090 l_dist_lines_tbl(l_count).attribute5 := p_id_attribute5;
1091 l_dist_lines_tbl(l_count).attribute6 := p_id_attribute6;
1092 l_dist_lines_tbl(l_count).attribute7 := p_id_attribute7;
1093 l_dist_lines_tbl(l_count).attribute8 := p_id_attribute8;
1094 l_dist_lines_tbl(l_count).attribute9 := p_id_attribute9;
1095 l_dist_lines_tbl(l_count).attribute10 := p_id_attribute10;
1096 l_dist_lines_tbl(l_count).attribute11 := p_id_attribute11;
1097 l_dist_lines_tbl(l_count).attribute12 := p_id_attribute12;
1098 l_dist_lines_tbl(l_count).attribute13 := p_id_attribute13;
1099 l_dist_lines_tbl(l_count).attribute14 := p_id_attribute14;
1100 l_dist_lines_tbl(l_count).attribute15 := p_id_attribute15;
1101 l_dist_lines_tbl(l_count).attribute_category := p_id_attribute_category;
1102 l_dist_lines_tbl(l_count).dist_number := p_dist_number;
1103 End If;
1104
1105 For C_Ccid_Rec In C_Ccid(p_initiator_name) Loop
1106 l_init_chart_of_accounts := C_Ccid_rec.chart_of_accounts_id;
1107 End Loop;
1108
1109 For C_Ccid_Rec In C_Ccid(p_recipient_name) Loop
1110 l_reci_chart_of_accounts := C_Ccid_rec.chart_of_accounts_id;
1111 End Loop;
1112 -- start of recipient validation
1113 l_trx_id := l_trx_tbl(l_count).trx_id;
1114 l_recipient_id := l_trx_tbl(l_count).recipient_id;
1115 l_init_amount_cr := l_trx_tbl(l_count).init_amount_cr;
1116 l_init_amount_dr := l_trx_tbl(l_count).init_amount_dr;
1117 l_trx_number := l_trx_tbl(l_count).trx_number;
1118 If(p_id_ccid is null) THEN
1119 l_count := l_count - 1;
1120 End If;
1121 If (p_rd_amount_dr is not null OR p_rd_amount_cr is not null OR
1122 p_rd_ccid_segments is not null OR p_rd_ccid is not null ) THEN
1123
1124
1125 If l_init_chart_of_accounts = l_reci_chart_of_accounts Then
1126 If p_rd_ccid_segments is not null Then
1127 Raise Con_Seg_Not_Req_Excpt;
1128 End if;
1129 l_dist_lines_tbl(l_count + 1).ccid := p_rd_ccid;
1130 Else
1131 If p_rd_ccid_segments is null Then
1132 Raise Con_Seg_Req_Excpt;
1133 End if;
1134
1135 For C_Flex_Info_Rec In C_Flex_Info(l_reci_chart_of_accounts) Loop
1136 l_appl_short_name := C_Flex_Info_Rec.appl_short_name;
1137 End Loop;
1138
1139 If Not Fnd_Flex_Keyval.Validate_Segs(
1140 'CREATE_COMBINATION', -- Operation
1144 p_rd_ccid_segments, -- Concatenated Segments
1141 l_appl_short_name, -- Application Short Name
1142 'GL#', -- Funds Flexfield Structure Code
1143 l_reci_chart_of_accounts, -- Structure Id
1145 'V', -- values
1146 sysdate, -- validation_date
1147 'ALL', -- displayable
1148 NULL, -- data_set
1149 NULL, -- vrule
1150 NULL, -- where_clause
1151 NULL, -- get_columns
1152 FALSE, -- allow_nulls
1153 FALSE, -- allow_orphans
1154 fnd_global.resp_appl_id,
1155 fnd_global.resp_id,
1156 fnd_global.user_id,
1157 'GL_CODE_COMBINATIONS', -- select_comb_from_view
1158 NULL, -- no_combmsg
1159 NULL -- where_clause_msg
1160 ) Then
1161 Raise Ccid_Gen_Excpt;
1162 Else
1163 l_dist_lines_tbl(l_count + 1).ccid := Fnd_Flex_Keyval.combination_id;
1164 End if;
1165 End If;
1166
1167 For C_Dist_Lines_Exists_Rec In C_Dist_Lines_Exists(
1168 l_trx_id,
1169 l_recipient_id,
1170 l_dist_lines_tbl(l_count + 1).ccid,
1171 'R', p_dist_number) Loop
1172
1173 l_reci_dist_line_insert := 'N';
1174 l_dist_lines_tbl(l_count + 1).dist_id := C_Dist_Lines_Exists_Rec.dist_id;
1175 l_dist_lines_tbl(l_count + 1).dist_number := C_Dist_Lines_Exists_Rec.dist_number;
1176 l_dist_lines_tbl(l_count + 1).trx_id := C_Dist_Lines_Exists_Rec.trx_id;
1177 l_dist_lines_tbl(l_count + 1).party_id := C_Dist_Lines_Exists_Rec.party_id;
1178 l_dist_lines_tbl(l_count + 1).party_type_flag := C_Dist_Lines_Exists_Rec.party_type_flag;
1179 l_dist_lines_tbl(l_count + 1).dist_type_flag := C_Dist_Lines_Exists_Rec.dist_type_flag;
1180 l_dist_lines_tbl(l_count + 1).amount_cr := C_Dist_Lines_Exists_Rec.amount_cr;
1181 l_dist_lines_tbl(l_count + 1).amount_dr := C_Dist_Lines_Exists_Rec.amount_dr;
1182 l_dist_lines_tbl(l_count + 1).ccid := C_Dist_Lines_Exists_Rec.ccid;
1183 l_dist_lines_tbl(l_count + 1).description := C_Dist_Lines_Exists_Rec.description;
1184 If (nvl(l_dist_lines_tbl(l_count + 1).amount_cr,0) <> nvl(p_rd_amount_cr,0) Or
1185 nvl(l_dist_lines_tbl(l_count + 1).amount_dr,0) <> nvl(p_rd_amount_dr,0) Or
1186 nvl(l_dist_lines_tbl(l_count + 1).description, 'NULL') <> nvl(p_rd_description, 'NULL')) Then
1187 Raise Reci_Dupl_Row_Excpt;
1188 End If;
1189 End Loop;
1190
1191 If l_dist_lines_tbl(l_count + 1).dist_id is null Then
1192 l_reci_dist_line_insert := 'Y';
1193 l_dist_lines_tbl(l_count + 1).trx_id := l_trx_id;
1194 For C_Dist_Id_Rec In C_Dist_Id Loop
1195 l_dist_lines_tbl(l_count + 1).dist_id := C_Dist_Id_Rec.dist_id;
1196 l_dist_lines_tbl(l_count + 1).dist_number := p_dist_number;
1197 End Loop;
1198
1199 l_dist_lines_tbl(l_count + 1).party_id := l_recipient_id;
1200 l_dist_lines_tbl(l_count + 1).party_type_flag := 'R';
1201 l_dist_lines_tbl(l_count + 1).dist_type_flag := 'L';
1202 l_dist_lines_tbl(l_count + 1).amount_cr := p_rd_amount_cr;
1203 l_dist_lines_tbl(l_count + 1).amount_dr := p_rd_amount_dr;
1204 l_dist_lines_tbl(l_count + 1).dist_number := p_dist_number;
1205
1206 -- Modified on 16th April 2005
1207 l_dr_sum := 0;
1208 l_cr_sum := 0;
1209 For C_Sum_Dist_Rec in C_Sum_Dist(
1210 l_trx_id,
1211 l_recipient_id,
1212 'R') Loop
1213 l_dr_sum := C_Sum_Dist_Rec.Dr_Sum;
1214 l_cr_sum := C_Sum_Dist_Rec.Cr_Sum;
1215 End Loop;
1216 -- Modified on 16th April 2005
1217 If l_init_amount_cr is not null Then
1218 If (l_dist_lines_tbl(l_count + 1).amount_cr is not null And
1219 l_dist_lines_tbl(l_count + 1).amount_dr is null) Then
1220 -- Modified on 16th April 2005
1221 If ((l_dist_lines_tbl(l_count + 1 ).amount_cr + l_cr_sum)
1222 > l_init_amount_cr) Then
1223 Raise Reci_Dist_Sum_Excpt;
1224 End If;
1228 End If;
1225 -- Modified on 16th April 2005
1226 Else
1227 Raise Reci_Amount_Excpt;
1229 End If;
1230
1231 If l_init_amount_dr is not null Then
1232 If (l_dist_lines_tbl(l_count + 1).amount_dr is not null And
1233 l_dist_lines_tbl(l_count + 1).amount_cr is null) Then
1234 -- Modified on 16th April 2005
1235 If ((l_dist_lines_tbl(l_count + 1 ).amount_dr + l_dr_sum)
1236 > l_init_amount_dr) Then
1237 Raise Reci_Dist_Sum_Excpt;
1238 End If;
1239 -- Modified on 16th April 2005
1240 Else
1241 Raise Reci_Amount_Excpt;
1242 End If;
1243 End If;
1244
1245 l_dist_lines_tbl(l_count + 1).description := p_rd_description;
1246 End If;
1247 End If ;--end of recipient validation
1248 Validate_Record(
1249 l_batch_rec,
1250 l_trx_tbl,
1251 l_dist_lines_tbl,
1252 l_batch_insert);
1253
1254 Insert_Batch(
1255 l_batch_rec,
1256 p_initiator_name,
1257 p_trx_type_name,
1258 l_from_le_name,
1259 l_batch_insert);
1260
1261 If nvl(l_trx_insert,'N') = 'Y' Then
1262 Insert_Header(
1263 p_recipient_name,
1264 l_to_le_name,
1265 l_trx_tbl);
1266 End If;
1267
1268 If nvl(l_init_dist_line_insert,'N') = 'Y' Then
1269 Insert_Dists(
1270 l_dist_lines_tbl,
1271 l_count);
1272 End if;
1273
1274 If nvl(l_reci_dist_line_insert,'N') = 'Y' Then
1275 Insert_Dists(
1276 l_dist_lines_tbl,
1277 l_count + 1);
1278 End if;
1279
1280 Commit;
1281 Exception
1282 When Trx_Num_Not_Numeric_Excpt Then
1283 Rollback;
1284 Fnd_Message.Set_Name('FUN','FUN_TRX_NUM_NOT_NUMERIC');
1285 Fnd_Message.Raise_Error;
1286
1287 When Initiator_Excpt Then
1288 Rollback;
1289 Fnd_Message.Set_Name('FUN','FUN_API_INVALID_INITIATOR');
1290 Fnd_Message.Raise_Error;
1291 When Trx_Type_Excpt Then
1292 Rollback;
1293 Fnd_Message.Set_Name('FUN','FUN_TRX_TYPE_NOT_FOUND');
1294 Fnd_Message.Raise_Error;
1295 When Tran_Reci_Diff_Excpt Then
1296 Rollback;
1297 Fnd_Message.Set_Name('FUN','FUN_ADI_TRAN_RECI_DIFF');
1298 Fnd_Message.Set_Token('RECI_NAME', l_reci_name);
1299 Fnd_Message.Raise_Error;
1300 When Tran_Dupl_Row_Excpt Then
1301 Rollback;
1302 Fnd_Message.Set_Name('FUN','FUN_ADI_TRAN_DUP_ERROR');
1303 Fnd_Message.Set_Token('TRX_NUMBER', l_trx_tbl(l_count).trx_number);
1304 Fnd_Message.Set_Token('DEBIT', l_trx_tbl(l_count).init_amount_dr);
1305 Fnd_Message.Set_Token('CREDIT', l_trx_tbl(l_count).init_amount_cr);
1306 Fnd_Message.Raise_Error;
1307 When Recipient_Excpt Then
1308 Rollback;
1309 Fnd_Message.Set_Name('FUN','FUN_API_INVALID_RECIPIENT');
1310 Fnd_Message.Raise_Error;
1311 When Dupl_Reci_In_Batch Then
1312 Rollback;
1313 Fnd_Message.Set_Name('FUN', 'FUN_API_DUPLICATE_RECP');
1314 Fnd_Message.Raise_Error;
1315 When Amount_Req_Excpt Then
1316 Rollback;
1317 Fnd_Message.Set_Name('FUN','FUN_TRX_DR_CR_AMT');
1318 Fnd_Message.Raise_Error;
1319 When Init_Dupl_Row_Excpt Then
1320 Rollback;
1321 Fnd_Message.Set_Name('FUN','FUN_ADI_INIT_DUP_ERROR');
1322 Fnd_Message.Set_Token('TRX_NUMBER', l_trx_tbl(l_count).trx_number);
1323 Fnd_Message.Set_Token('DEBIT', l_dist_lines_tbl(l_count).amount_dr);
1324 Fnd_Message.Set_Token('CREDIT', l_dist_lines_tbl(l_count).amount_cr);
1325 Fnd_Message.Set_Token('DESCRIPTION', l_dist_lines_tbl(l_count).description);
1326 Fnd_Message.Raise_Error;
1327 When Init_Dist_Sum_Excpt Then
1328 Rollback;
1329 Fnd_Message.Set_Name('FUN','FUN_ADI_INIT_SUM_ERROR');
1330 Fnd_Message.Raise_Error;
1331 When Init_Amount_Excpt Then
1332 Rollback;
1333 Fnd_Message.Set_Name('FUN','FUN_IC_INVALID_DRCR_DIST');
1334 Fnd_Message.Set_Token('TRX_NUMBER', p_trx_number);
1335 Fnd_Message.Raise_Error;
1336 When Con_Seg_Not_Req_Excpt Then
1337 Rollback;
1338 Fnd_Message.Set_Name('FUN','FUN_ADI_CON_SEGS_NOT_REQ');
1339 Fnd_Message.Raise_Error;
1340 When Con_Seg_Req_Excpt Then
1341 Rollback;
1342 Fnd_Message.Set_Name('FUN','FUN_ADI_CON_SEGS_REQ');
1343 Fnd_Message.Raise_Error;
1344 When Ccid_Gen_Excpt Then
1345 Rollback;
1346 Fnd_Message.Set_Name('FUN','FUN_ADI_CCID_ERROR');
1347 Fnd_Message.Set_Token('CON_SEGS', p_rd_ccid_segments);
1348 Fnd_Message.Raise_Error;
1349 When Reci_Dupl_Row_Excpt Then
1350 Rollback;
1351 Fnd_Message.Set_Name('FUN','FUN_ADI_RECI_DUP_ERROR');
1352 Fnd_Message.Set_Token('TRX_NUMBER', l_trx_number);
1353 Fnd_Message.Set_Token('DEBIT', l_dist_lines_tbl(l_count + 1).amount_dr);
1354 Fnd_Message.Set_Token('CREDIT', l_dist_lines_tbl(l_count + 1 ).amount_cr);
1358 Rollback;
1355 Fnd_Message.Set_Token('DESCRIPTION', l_dist_lines_tbl(l_count + 1 ).description);
1356 Fnd_Message.Raise_Error;
1357 When Reci_Dist_Sum_Excpt Then
1359 Fnd_Message.Set_Name('FUN','FUN_ADI_RECI_SUM_ERROR');
1360 Fnd_Message.Raise_Error;
1361 When Reci_Amount_Excpt Then
1362 Rollback;
1363 Fnd_Message.Set_Name('FUN','FUN_ADI_INVALID_DRCR_DIST');
1364 Fnd_Message.Set_Token('TRX_NUMBER', p_trx_number);
1365 Fnd_Message.Raise_Error;
1366 When Fnd_Api.G_EXC_UNEXPECTED_ERROR Then
1367 Rollback;
1368 Get_Message_Text(l_data);
1369 Fnd_Message.Set_Name('FUN', 'FUN_ADI_ERROR');
1370 Fnd_Message.Set_Token('ERROR', l_data);
1371 Fnd_Message.Raise_Error;
1372 When Fnd_Api.G_EXC_ERROR Then
1373 Rollback;
1374 Get_Message_Text(l_data);
1375 Fnd_Message.Set_Name('FUN', 'FUN_ADI_ERROR');
1376 Fnd_Message.Set_Token('ERROR', l_data);
1377 When Others Then
1378 Rollback;
1379 Fnd_Message.Set_Name('FUN', 'FUN_ADI_ERROR');
1380 Fnd_Message.Set_Token('ERROR', sqlerrm);
1381 Fnd_Message.Raise_Error;
1382 End Upload_Batch;
1383 End FUN_WEBADI_PKG;
1384