[Home] [Help]
PACKAGE BODY: APPS.FUN_TRX_PVT
Source
1 PACKAGE BODY FUN_TRX_PVT AS
2 /* $Header: funtrxvalpvtb.pls 120.110.12020000.5 2013/02/15 07:13:27 srampure ship $ */
3
4 -- Note:
5 -- SETNAME if used on a namein that is not associated with a message
6 -- in database, then namein would become the message itself
7 -- Can use FND_MSG_PUB.get and ask it not to perform the translation for
8 -- recipient instance to send back message to initiator
9 -- Special handling when validating invoice_fule flag for 'M'
10 -- Not a concern anymore. BC would create a function to get the
11 -- invoicing rule by trx type and le and trx entry page and webadi
12 -- needs to call is_ar_valid to determine the invoicing flag
13 --Problem Need to add validations for all debits or all credits (+ve/-ve)
14 -- Problem Add details for transaction entry page
15 NO_RATE EXCEPTION;
16
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FUN_TRX_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(30) := 'FUNTRXVALPVTB.PLS';
19
20 G_LE_BSV_GT_INIT BOOLEAN := FALSE;
21 G_LEDGER_ID NUMBER := -1;
22 G_CHART_OF_ACCOUNTS_ID NUMBER := -1;
23 G_BAL_SEG_COLUMN_NAME VARCHAR2(25) := NULL;
24 G_LE_NAME VARCHAR2(60);
25 G_DEBUG VARCHAR2(5);
26
27 PROCEDURE Set_Return_Status
28 ( x_orig_status IN OUT NOCOPY VARCHAR2,
29 p_new_status IN VARCHAR2
30 ) IS
31 BEGIN
32 -- API body
33 IF (x_orig_status = FND_API.G_RET_STS_SUCCESS
34 AND p_new_status <> FND_API.G_RET_STS_SUCCESS) THEN
35 x_orig_status := p_new_status;
36 ELSIF (x_orig_status = FND_API.G_RET_STS_ERROR
37 AND p_new_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
38 x_orig_status := p_new_status;
39 END IF;
40 -- End of API body.
41 END Set_Return_Status;
42
43 --Bug:6998219. validate_org_assignment
44
45 PROCEDURE validate_org_assignment
46 ( x_return_status OUT NOCOPY VARCHAR2 ,
47 p_party_id IN NUMBER
48 ) IS
49 l_assignment_count NUMBER;
50 l_party_name VARCHAR2(360);
51 BEGIN
52
53 x_return_status := FND_API.G_RET_STS_SUCCESS;
54
55 SELECT PARTY_NAME INTO l_party_name from HZ_PARTIES
56 WHERE PARTY_ID = p_party_id;
57
58 SELECT count(*) into l_assignment_count
59 FROM HZ_PARTIES HZP,
60 HZ_PARTIES HZP2,
61 HZ_RELATIONSHIPS HZR,
62 HZ_ORG_CONTACTS HZC,
63 HZ_ORG_CONTACT_ROLES HZCR
64 WHERE HZR.RELATIONSHIP_CODE='CONTACT_OF'
65 AND HZR.RELATIONSHIP_TYPE='CONTACT'
66 AND HZR.DIRECTIONAL_FLAG='F'
67 AND HZR.SUBJECT_TABLE_NAME='HZ_PARTIES'
68 AND HZR.OBJECT_TABLE_NAME='HZ_PARTIES'
69 AND HZR.SUBJECT_TYPE='PERSON'
70 AND HZR.OBJECT_ID=HZP2.PARTY_ID
71 AND HZR.SUBJECT_ID=HZP.PARTY_ID
72 AND HZR.OBJECT_ID = p_party_id
73 AND HZC.PARTY_RELATIONSHIP_ID = HZR.RELATIONSHIP_ID
74 AND HZCR.ORG_CONTACT_ID = HZC.ORG_CONTACT_ID
75 AND HZCR.ROLE_TYPE = 'INTERCOMPANY_CONTACT_FOR'
76 AND FUN_SECURITY.IS_ACCESS_VALID(HZP.PARTY_ID, HZP2.PARTY_ID) = 'Y' -- Access1
77 AND DECODE(HZR.STATUS,'A','Y','I','N') = 'Y' -- status
78 AND HZR.ADDITIONAL_INFORMATION1 = 'Y' -- Notification
79 AND sysdate BETWEEN
80 nvl(HZR.start_date, sysdate -1)
81 AND nvl(HZR.end_date, sysdate + 1);
82
83 IF( l_assignment_count = 0) then
84 Set_Return_Status(x_orig_status => x_return_status,
85 p_new_status => FND_API.G_RET_STS_ERROR);
86 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
87 FND_MESSAGE.SET_NAME('FUN', 'FUN_NO_ORG_ASSIGNMENT');
88 FND_MESSAGE.SET_TOKEN('PARTY_NAME', l_party_name);
89 FND_MSG_PUB.Add;
90 END IF;
91 END IF;
92
93 EXCEPTION
94 WHEN NO_DATA_FOUND THEN
95
96 Set_Return_Status(x_orig_status => x_return_status,
97 p_new_status => FND_API.G_RET_STS_ERROR);
98 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
99 FND_MESSAGE.SET_NAME('FUN', 'FUN_NO_ORG_ASSIGNMENT');
100 FND_MESSAGE.SET_TOKEN('PARTY_NAME', l_party_name);
101 FND_MSG_PUB.Add;
102 END IF;
103
104 END;
105 --Bug:9104801
106 PROCEDURE adjust_dist_amount
107 ( p_trx_id IN NUMBER,
108 p_init_amount_cr IN NUMBER,
109 p_init_amount_dr IN NUMBER
110 ) IS
111 l_sum_amount_cr NUMBER;
112 l_sum_amount_dr NUMBER;
113 l_diff_amount NUMBER;
114 BEGIN
115 select sum(amount_cr), sum(amount_dr)
116 into l_sum_amount_cr, l_sum_amount_dr
117 from fun_dist_lines
118 where trx_id = p_trx_id
119 and dist_type_flag = 'L'
120 and party_type_flag = 'I';
121
122 IF l_sum_amount_cr is not null and l_sum_amount_cr <> 0 THEN
123 l_diff_amount := p_init_amount_dr - l_sum_amount_cr ;
124 update fun_dist_lines dist1
125 set dist1.amount_cr = dist1.amount_cr + l_diff_amount
126 where dist1.amount_cr = (select max(dist2.amount_cr) from fun_dist_lines dist2
127 where dist2.dist_type_flag = 'L'
128 and dist2.party_type_flag = 'I'
129 and dist2.trx_id = p_trx_id)
130 and rownum = 1
131 and dist1.dist_type_flag = 'L'
132 and dist1.party_type_flag = 'I'
133 and dist1.trx_id = p_trx_id;
134 END IF;
135 IF l_sum_amount_dr is not null and l_sum_amount_dr <> 0 THEN
136 l_diff_amount := p_init_amount_cr - l_sum_amount_dr;
137 update fun_dist_lines dist1
138 set dist1.amount_dr = dist1.amount_dr + l_diff_amount
139 where dist1.amount_dr = (select max(dist2.amount_dr) from fun_dist_lines dist2
140 where dist2.dist_type_flag = 'L'
141 and dist2.party_type_flag = 'I'
142 and dist2.trx_id = p_trx_id)
143 and rownum = 1
144 and dist1.dist_type_flag = 'L'
145 and dist1.party_type_flag = 'I'
146 and dist1.trx_id = p_trx_id;
147 END IF;
148 EXCEPTION
149 WHEN OTHERS THEN
150 NULL;
151 END adjust_dist_amount;
152
153 PROCEDURE Print
154 (
155 P_string IN VARCHAR2
156 ) IS
157
158
159 BEGIN
160
161 IF G_DEBUG = 'Y' THEN
162 fnd_file.put_line(FND_FILE.LOG, p_string);
163
164 END IF;
165
166 EXCEPTION
167 WHEN OTHERS THEN
168 APP_EXCEPTION.RAISE_EXCEPTION;
169 END Print;
170
171 PROCEDURE Is_Trx_Num_Unique
172 ( x_return_status OUT NOCOPY VARCHAR2,
173 p_batch_id IN number,
174 p_trx_tbl IN OUT NOCOPY TRX_TBL_TYPE) IS
175 l_count NUMBER;
176 BEGIN
177 Debug('IS_TRX_NUM_UNIQUE(+)');
178 x_return_status := FND_API.G_RET_STS_SUCCESS;
179 FOR I IN 1..p_trx_tbl.count LOOP
180 l_count :=0;
181 FOR J IN 1..p_trx_tbl.count LOOP
182 IF p_trx_tbl(J).batch_id=p_batch_id AND p_trx_tbl(J).trx_number = p_trx_tbl(I).trx_number THEN
183 l_count := l_count+1;
184 END IF;
185 IF l_count > 1 THEN
186 FND_MESSAGE.SET_NAME('FUN', 'FUN_DUPL_TRX_NUM');
187 FND_MESSAGE.SET_TOKEN('TRX_NUMBER',p_trx_tbl(J).trx_number);
188 FND_MSG_PUB.Add;
189 Set_Return_Status(x_orig_status => x_return_status,
190 p_new_status => FND_API.G_RET_STS_ERROR);
191 EXIT;
192 END IF;
193 END LOOP;
194 END LOOP;
195 Debug('IS_TRX_NUM_UNIQUE(-)');
196 END;
197
198
199
200 PROCEDURE Is_Batch_Num_Unique
201 ( x_return_status OUT NOCOPY VARCHAR2,
202 p_batch_number IN VARCHAR2,
203 p_initiator_id IN NUMBER
204 ) IS
205 l_count NUMBER;
206 CURSOR batch_num_csr IS
207 SELECT COUNT(*)
208 FROM fun_trx_batches
209 WHERE initiator_id = p_initiator_id
210 AND batch_number = p_batch_number;
211 BEGIN
212 Debug('IS_BATCH_NUM_UNIQUE(+)');
213 --7.2.1.3
214 x_return_status := FND_API.G_RET_STS_SUCCESS;
215 l_count := 0;
216 OPEN batch_num_csr;
217 FETCH batch_num_csr INTO l_count;
218 CLOSE batch_num_csr;
219 IF l_count > 0 OR p_batch_number IS NULL THEN
220 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
221 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_DUPLICATE_BATCH_NUM');
222 FND_MSG_PUB.Add;
223 END IF;
224 Set_Return_Status(x_orig_status => x_return_status,
225 p_new_status => FND_API.G_RET_STS_ERROR);
226 END IF;
227 Debug('IS_BATCH_NUM_UNIQUE(-)');
228 END;
229
230
231 PROCEDURE check_invoice_reqd_flag(p_init_party_id IN NUMBER,
232 p_init_le_id IN NUMBER,
233 p_reci_party_id IN NUMBER,
234 p_reci_le_id IN NUMBER,
235 p_ttyp_invoice_flag IN VARCHAR2,
236 x_invoice_required OUT NOCOPY VARCHAR2,
237 x_return_status OUT NOCOPY VARCHAR2)
238 IS
239
240 l_ini_le_id NUMBER;
241 l_trx_invoice_flag VARCHAR2(1);
242 l_ini_invoice_flag VARCHAR2(1);
243 l_rec_invoice_flag VARCHAR2(1);
244 l_return_status VARCHAR2(1);
245 l_le_error VARCHAR2(2000);
246
247 BEGIN
248 x_return_status := FND_API.G_RET_STS_SUCCESS;
249 x_invoice_required := 'N';
250
251 IF p_ttyp_invoice_flag = 'N'
252 THEN
253 -- Check if initiator requires invoicing
254 XLE_UTILITIES_GRP. Check_IC_Invoice_required(
255 x_return_status => l_return_status,
256 x_msg_data => l_le_error,
257 p_legal_entity_id => p_init_le_id,
258 p_party_id => p_init_party_id,
259 x_intercompany_inv => l_ini_invoice_flag);
260
261 IF l_ini_invoice_flag = FND_API.G_TRUE
262 THEN
263 x_invoice_required := 'Y';
264 ELSE
265 -- check if invoice is required for the recipient
266 XLE_UTILITIES_GRP. Check_IC_Invoice_required(
267 x_return_status => l_return_status,
268 x_msg_data => l_le_error,
269 p_legal_entity_id => p_reci_le_id,
270 p_party_id => p_reci_party_id,
271 x_intercompany_inv => l_rec_invoice_flag);
272
273 IF l_rec_invoice_flag = FND_API.G_TRUE
274 THEN
275 -- invoicing is required for the recipient
276 x_invoice_required := 'Y';
277 ELSE
278 x_invoice_required := 'N';
279 END IF; -- invoicing not required for recipient
280
281 END IF; -- invoicing enabled for inititator
282 ELSE
283 x_invoice_required := 'Y';
284
285 END IF; -- invoicing enabled for trx type
286
287
288 EXCEPTION
289 WHEN OTHERS
290 THEN
291 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
292 THEN
293 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
294 'fun.plsql.fun_wf_common.check_invoice_reqd_flag',
295 SQLERRM || ' Error occurred ');
296 END IF;
297
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
299
300 END check_invoice_reqd_flag;
301
302 PROCEDURE Is_Party_Valid
303 ( x_return_status OUT NOCOPY VARCHAR2 ,
304 p_party_id IN NUMBER,
305 p_le_id IN NUMBER,
306 p_ledger_id IN NUMBER,
307 p_instance IN VARCHAR2,
308 p_local IN VARCHAR2,
309 p_type IN VARCHAR2,
310 p_batch_date IN DATE,
311 p_trx_number IN VARCHAR2
312 ) IS
313 l_msr VARCHAR2(240);
314 l_return_status VARCHAR2(1);
315 l_count NUMBER;
316 l_le_start_dt DATE;
317 l_le_end_dt DATE;
318
319 CURSOR legal_entity_csr IS
320 SELECT le.le_effective_from, le.le_effective_to
321 FROM xle_firstparty_information_v le, hz_parties parties
322 WHERE parties.party_id = p_party_id
323 AND EXISTS (SELECT 1
324 FROM hz_party_usg_assignments hua
325 WHERE hua.party_id = parties.party_id
326 AND hua.party_usage_code = 'INTERCOMPANY_ORG')
327 AND fun_tca_pkg.get_le_id(p_party_id) = le.party_id
328 AND p_le_id = le.legal_entity_id;
329 --AND transacting_flag = p_instance; Problem here with transacting flag
330
331 BEGIN
332 Debug('IS_PARTY_VALID(+)');
333
334 -- 7.2.2.2
335 x_return_status := FND_API.G_RET_STS_SUCCESS;
336 l_return_status := FUN_TCA_PKG.is_intercompany_org_valid(p_party_id, p_batch_date);
337 IF l_return_status = 'N' THEN
338 --Bug 5144930. Added new error messages to display the start date and end date.
339 FUN_TCA_PKG.get_ic_org_valid_dates(p_party_id,l_le_start_dt, l_le_end_dt);
340 IF p_type = 'I' THEN
341 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
342 IF l_le_start_dt IS NULL AND l_le_end_dt IS NULL THEN
343 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_INITIATOR');
344 ELSE
345 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_INITIATOR_DATE');
346 FND_MESSAGE.SET_TOKEN('P_START_DATE',to_char(l_le_start_dt));
347 FND_MESSAGE.SET_TOKEN('P_END_DATE',to_char(l_le_end_dt));
348 END IF;
349 FND_MSG_PUB.Add;
350 END IF;
351 ELSE
352 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
353 IF l_le_start_dt IS NULL AND l_le_end_dt IS NULL THEN
354 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_RECIPIENT');
355 FND_MESSAGE.SET_TOKEN('TRX_NUMBER',p_trx_number);
356 ELSE
357 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_RECIPIENT_DATE');
358 FND_MESSAGE.SET_TOKEN('TRX_NUMBER',p_trx_number);
359 FND_MESSAGE.SET_TOKEN('P_START_DATE',to_char(l_le_start_dt));
360 FND_MESSAGE.SET_TOKEN('P_END_DATE',to_char(l_le_end_dt));
361 END IF;
362 FND_MSG_PUB.Add;
363 END IF;
364 END IF;
365
366 Set_Return_Status(x_orig_status => x_return_status,
367 p_new_status => FND_API.G_RET_STS_ERROR);
368 END IF;
369
370
371 --Problem: LE dependency
372 OPEN legal_entity_csr;
373 FETCH legal_entity_csr INTO l_le_start_dt, l_le_end_dt;
374
375 IF legal_entity_csr%NOTFOUND THEN
376 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
377 IF p_type = 'I' THEN
378 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_INITIATOR');
379 FND_MSG_PUB.Add;
380 ELSE
381 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_RECIPIENT');
382 FND_MESSAGE.SET_TOKEN('TRX_NUMBER',p_trx_number);
383 FND_MSG_PUB.Add;
384 END IF;
385 END IF;
386 Set_Return_Status(x_orig_status => x_return_status,
387 p_new_status => FND_API.G_RET_STS_ERROR);
388 END IF;
389
390 CLOSE legal_entity_csr;
391
392 -- Bug 3173783
393 IF NOT ( p_batch_date BETWEEN Nvl(l_le_start_dt, p_batch_date) AND Nvl(l_le_end_dt, p_batch_date))
394 THEN
395 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
396 IF p_type = 'I' THEN
397 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INACTIVE_INIT_LE');
398 FND_MSG_PUB.Add;
399 ELSE
400 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INACTIVE_RECI_LE');
401 FND_MESSAGE.SET_TOKEN('TRX_NUMBER', p_trx_number);
402 FND_MSG_PUB.Add;
403 END IF;
404 END IF;
405 Set_Return_Status(x_orig_status => x_return_status,
406 p_new_status => FND_API.G_RET_STS_ERROR);
407
408 END IF;
409
410 IF p_local = 'N' THEN
411 l_msr := Fun_Tca_Pkg.Get_System_Reference(p_party_id);
412 IF l_msr IS NULL THEN
413 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
414 FND_MESSAGE.SET_NAME('FUN', 'FUN_PARTY_NO_MSR');
415 FND_MSG_PUB.Add;
416 END IF;
417 Set_Return_Status(x_orig_status => x_return_status,
418 p_new_status => FND_API.G_RET_STS_ERROR);
419 END IF;
420 END IF;
421
422 Debug('IS_PARTY_VALID(-)');
423 EXCEPTION WHEN OTHERS THEN -- Problem here: Remove check
424 FND_MESSAGE.SET_NAME('FUN', 'ERROR_IN_IS_PARTY_VALID');
425 FND_MSG_PUB.ADD;
426
427 END;
428
429
430 -- Bidisha S, Modified this procedure to perform distribution validation
431 -- for the manual mode.
432 PROCEDURE Is_Init_Trx_Dist_Amt_Valid
433 ( x_return_status OUT NOCOPY VARCHAR2,
434 p_trx_amount_cr IN NUMBER,
435 p_trx_amount_dr IN NUMBER,
436 p_dist_lines_tbl IN OUT NOCOPY DIST_LINE_TBL_TYPE,
437 p_currency_code IN VARCHAR2,
438 p_trx_date IN DATE,
439 p_auto_proration_flag IN VARCHAR2,
440 p_trx_number IN VARCHAR2
441 ) IS
442 l_api_name CONSTANT VARCHAR2(30) := 'Is_Init_Trx_Dist_Amt_Valid';
443 l_boolean BOOLEAN;
444 l_count NUMBER;
445
446 l_dist_cr_type NUMBER := 0;
447 l_dist_dr_type NUMBER := 0;
448 l_dist_pos_type NUMBER := 0;
449 l_dist_neg_type NUMBER := 0;
450 l_dist_total_cr NUMBER := 0;
451 l_dist_total_dr NUMBER := 0;
452
453 BEGIN
454 Debug('Is_Init_Trx_Dist_Amt_Valid(+)');
455 x_return_status := FND_API.G_RET_STS_SUCCESS;
456
457 -- Perform the following validation only for manual mode
458 IF Nvl(p_auto_proration_flag, 'N') = 'N'
459 THEN
460 l_count := p_dist_lines_tbl.COUNT;
461 IF l_count > 0
462 THEN
463 FOR j IN 1..l_count LOOP
464 IF p_dist_lines_tbl(j).party_type = 'I' AND
465 p_dist_lines_tbl(j).dist_type = 'L'
466 THEN
467 IF NVL(p_dist_lines_tbl(j).amount_cr, 0) <> 0 THEN
468 l_dist_cr_type := 1;
469 IF p_dist_lines_tbl(j).amount_cr > 0 THEN
470 l_dist_pos_type := 1;
471 ELSE
472 l_dist_neg_type := 1;
473 END IF;
474 END IF;
475 IF NVL(p_dist_lines_tbl(j).amount_dr, 0) <> 0
476 THEN
477 l_dist_dr_type := 1;
478 IF p_dist_lines_tbl(j).amount_dr > 0 THEN
479 l_dist_pos_type := 1;
480 ELSE
481 l_dist_neg_type := 1;
482 END IF;
483 END IF;
484 l_dist_total_cr := l_dist_total_cr + NVL(p_dist_lines_tbl(j).amount_cr, 0);
485 l_dist_total_dr := l_dist_total_dr + NVL(p_dist_lines_tbl(j).amount_dr, 0);
486 END IF;
487 END LOOP;
488 END IF;
489
490 IF (p_trx_amount_cr <> l_dist_total_dr OR
491 p_trx_amount_dr <> l_dist_total_cr )
492 THEN
493 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
494 FND_MESSAGE.SET_NAME('FUN', 'FUN_IC_INI_HDR_DIST_MISMATCH');
495 FND_MESSAGE.SET_TOKEN('TRX_NUMBER',p_trx_number);
496 FND_MSG_PUB.Add;
497 END IF;
498 Set_Return_Status(x_orig_status => x_return_status,
499 p_new_status => FND_API.G_RET_STS_ERROR);
500 END IF;
501
502 IF l_dist_cr_type = l_dist_dr_type
503 THEN
504 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
505 THEN
506 FND_MESSAGE.SET_NAME('FUN', 'FUN_IC_INVALID_DRCR_DIST');
507 FND_MESSAGE.SET_TOKEN('TRX_NUMBER',p_trx_number);
508 FND_MSG_PUB.Add;
509 END IF;
510 Set_Return_Status(x_orig_status => x_return_status,
511 p_new_status => FND_API.G_RET_STS_ERROR);
512 END IF;
513 END IF; -- Manual Distribution validation
514
515 EXCEPTION
516 WHEN FND_API.G_EXC_ERROR THEN
517 x_return_status := FND_API.G_RET_STS_ERROR ;
518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
520 WHEN OTHERS THEN
521 FND_MSG_PUB.ADD;
522 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
523 IF FND_MSG_PUB.Check_Msg_Level
524 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
525 THEN
526 FND_MSG_PUB.Add_Exc_Msg
527 ( G_PKG_NAME ,
528 l_api_name
529 );
530 END IF;
531 Debug('Is_Init_Trx_Dist_Amt_Valid(-)');
532 END;
533
534 PROCEDURE Is_Init_Trx_Amt_Valid
535 ( x_return_status OUT NOCOPY VARCHAR2,
536 p_trx_amount_cr IN NUMBER,
537 p_trx_amount_dr IN NUMBER,
538 p_dist_lines_tbl IN OUT NOCOPY DIST_LINE_TBL_TYPE,
539 p_currency_code IN VARCHAR2,
540 p_trx_date IN DATE,
541 p_auto_proration_flag IN VARCHAR2,
542 p_trx_number IN VARCHAR2,
543 p_exchg_rate_type IN VARCHAR2
544 ) IS
545 l_rate NUMBER;
546 l_min_amt NUMBER := 0;
547 l_min_curr_code VARCHAR2(30);
548 l_conv_type VARCHAR2(30);
549 l_api_name CONSTANT VARCHAR2(30) := 'IS_INIT_TRX_AMT_VALID';
550 l_boolean BOOLEAN;
551 l_count NUMBER;
552
553
554 BEGIN
555 Debug('IS_INIT_TRX_VALID(+)');
556 x_return_status := FND_API.G_RET_STS_SUCCESS;
557
558 l_boolean := Fun_System_Options_Pkg.Get_Min_Trx_Amt(l_min_amt, l_min_curr_code);
559 l_conv_type := p_exchg_rate_type;
560 --No need to perform minimum transaction amount validation if the minimum amount
561 --is not entered.
562 IF l_min_amt = 0 OR l_min_amt IS NULL THEN
563 RETURN;
564 END IF;
565 IF ABS(NVL(p_trx_amount_cr, p_trx_amount_dr)) <
566 gl_currency_api.convert_amount(l_min_curr_code,
567 p_currency_code,
568 p_trx_date,
569 l_conv_type,
570 l_min_amt)
571 THEN
572 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
573 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_LINE_AMT_LESSTN_MIN');
574 FND_MESSAGE.SET_TOKEN('MIN_TRX_AMT', l_min_curr_code||' '||l_min_amt);
575 FND_MESSAGE.SET_TOKEN('TRX_NUMBER',p_trx_number);
576 FND_MSG_PUB.Add;
577 END IF;
578 Set_Return_Status(x_orig_status => x_return_status,
579 p_new_status => FND_API.G_RET_STS_ERROR);
580 END IF;
581 EXCEPTION
582 WHEN FND_API.G_EXC_ERROR THEN
583 x_return_status := FND_API.G_RET_STS_ERROR ;
584
585 WHEN gl_currency_api.NO_RATE THEN
586 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
587 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_CONV_RATE_NOT_FOUND');
588 FND_MSG_PUB.ADD;
589 END IF;
590 Set_Return_Status(x_orig_status => x_return_status,
591 p_new_status => FND_API.G_RET_STS_ERROR);
592 x_return_status := FND_API.G_RET_STS_ERROR ;
593
594 WHEN gl_currency_api.INVALID_CURRENCY THEN
595 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
596 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_CURRENCY');
597 FND_MSG_PUB.ADD;
598 END IF;
599 Set_Return_Status(x_orig_status => x_return_status,
600 p_new_status => FND_API.G_RET_STS_ERROR);
601 x_return_status := FND_API.G_RET_STS_ERROR ;
602
603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
605 WHEN OTHERS THEN
606 FND_MESSAGE.SET_NAME('FUN', 'ERROR_IN_IS_INIT_TRX_AMT_VALID'); -- Problem here: Remove check
607 FND_MSG_PUB.ADD;
608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
609 IF FND_MSG_PUB.Check_Msg_Level
610 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
611 THEN
612 FND_MSG_PUB.Add_Exc_Msg
613 ( G_PKG_NAME ,
614 l_api_name
615 );
616 END IF;--Bug 3603338
617 Debug('IS_INIT_TRX_VALID(-)');
618 END;
619
620
621 PROCEDURE Get_Valid_Bsvs
622 ( p_ledger_id NUMBER,
623 p_le_id NUMBER,
624 x_valid_bsvs OUT NOCOPY VARCHAR2
625 ) IS
626 CURSOR valid_bsv_csr IS
627 SELECT segment_value
628 FROM GL_LEDGER_LE_BSV_SPECIFIC_V
629 WHERE ledger_id = p_ledger_id
630 AND (legal_entity_id = p_le_id or legal_entity_id is null);
631 l_bal_seg_value VARCHAR2(25);
632 l_first BOOLEAN;
633 BEGIN
634 l_first := TRUE;
635 x_valid_bsvs := NULL;
636 OPEN valid_bsv_csr;
637 LOOP
638 FETCH valid_bsv_csr INTO l_bal_seg_value;
639 EXIT WHEN valid_bsv_csr%NOTFOUND;
640 IF l_first THEN
641 x_valid_bsvs := l_bal_seg_value;
642 l_first := FALSE;
643 ELSE
644 x_valid_bsvs := x_valid_bsvs || ', ' || l_bal_seg_value;
645 END IF;
646 END LOOP;
647 CLOSE valid_bsv_csr;
648
649 END;
650
651
652 PROCEDURE Is_Ccid_Valid
653 (
654 x_return_status OUT NOCOPY VARCHAR2,
655 p_ccid IN NUMBER,
656 p_le_id IN NUMBER,
657 p_ledger_id IN NUMBER
658 ) IS
659 l_return_status VARCHAR2(1);
660 l_bal_seg_value VARCHAR2(25);
661 l_rows_processed INT;
662 l_count NUMBER;
663 l_stmt_str VARCHAR2(300);
664 l_cur_hdl INT;
665 l_valid_bsvs VARCHAR2(2000);
666 l_bsv_val VARCHAR2(1);
667 l_bal_seg_column_name VARCHAR2(30);
668 CURSOR bsv_csr IS
669 SELECT COUNT(*)
670 FROM gl_ledger_le_bsv_gt gt
671 WHERE bal_seg_value = l_bal_seg_value
672 AND ledger_id = p_ledger_id
673 AND legal_entity_id = p_le_id;
674
675 BEGIN
676 Debug('IS_CCID_VALID(+)');
677 x_return_status := FND_API.G_RET_STS_SUCCESS;
678
679 --Validate only if the ledger is set up to have specific BSVs assigned
680 SELECT bal_seg_value_option_code, bal_seg_column_name, name
681 INTO l_bsv_val, l_bal_seg_column_name, g_le_name
682 FROM gl_ledgers
683 WHERE ledger_id=p_ledger_id;
684
685 IF l_bsv_val='I' THEN
686
687 execute immediate 'SELECT ' || l_bal_seg_column_name ||
688 ' FROM gl_code_combinations WHERE code_combination_id = :1'
689 INTO l_bal_seg_value
690 using p_ccid;
691
692 Select COUNT(*)
693 INTO l_count
694 from GL_LEDGER_LE_BSV_SPECIFIC_V
695 where segment_value = l_bal_seg_value
696 and ledger_id = p_ledger_id
697 and (legal_entity_id = p_le_id or legal_entity_id is null);
698
699 IF(l_count<1) THEN
700 Get_Valid_Bsvs(p_ledger_id => p_ledger_id,p_le_id => p_le_id,x_valid_bsvs => l_valid_bsvs);
701 FND_MESSAGE.SET_NAME('FUN', 'FUN_BSV_LE_NOT_ASSIGNED');
702 FND_MESSAGE.SET_TOKEN('LE', g_le_name);
703 FND_MESSAGE.SET_TOKEN('BSV', l_bal_seg_value);
704 FND_MESSAGE.SET_TOKEN('VALIDBSVS', l_valid_bsvs);
705 FND_MSG_PUB.Add;
706 Set_Return_Status(x_orig_status => x_return_status,
707 p_new_status => FND_API.G_RET_STS_ERROR);
708 END IF;
709 END IF;
710 Debug('IS_CCID_VALID(-)');
711 EXCEPTION
712 WHEN NO_DATA_FOUND THEN
713 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
714 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_CCID');
715 FND_MSG_PUB.Add;
716 END IF;
717
718
719 -- David Haimes 19 March 2004
720 -- Commenting out this procedure so it always returns success
721 -- It was not working for BSV-all Case which is used in FOcus Group
722 -- so PM requested we remove the check totally (see bug 3480343)
723 -- we need to handle this case if we want to re implement, but we prefer that
724 -- the lov are limited to teh appropriate bsv in the key flex UI rather than
725 -- do check here, if OA can give us that fix/enhance to their kff
726
727 /*
728 -- Initialization phase
729 IF g_le_bsv_gt_init = FALSE OR p_ledger_id <> g_ledger_id THEN
730 l_return_status := GL_MC_INFO.INIT_LEDGER_LE_BSV_GT(p_ledger_id);
731
732 SELECT chart_of_accounts_id, bal_seg_column_name, legal_entity_name
733 INTO g_chart_of_accounts_id, g_bal_seg_column_name, g_le_name
734 FROM gl_ledger_le_bsv_gt
735 WHERE ledger_id = p_ledger_id
736 AND ROWNUM < 2;
737
738 g_ledger_id := p_ledger_id;
739 g_le_bsv_gt_init := TRUE;
740 END IF;
741 l_cur_hdl := dbms_sql.open_cursor;
742 l_stmt_str := 'SELECT ' || g_bal_seg_column_name ||
743 ' FROM gl_code_combinations WHERE code_combination_id = ' ||
744 p_ccid;
745 dbms_sql.parse(l_cur_hdl, l_stmt_str, dbms_sql.native);
746 dbms_sql.define_column(l_cur_hdl, 1, l_bal_seg_value, 25);
747 l_rows_processed := dbms_sql.execute(l_cur_hdl);
748 IF dbms_sql.fetch_rows(l_cur_hdl) > 0 THEN
749 dbms_sql.column_value(l_cur_hdl, 1, l_bal_seg_value);
750 ELSE
751 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
752 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_CCID');
753 FND_MSG_PUB.Add;
754 END IF;
755 Set_Return_Status(x_orig_status => x_return_status,
756 p_new_status => FND_API.G_RET_STS_ERROR);
757 END IF;
758 dbms_sql.close_cursor(l_cur_hdl); -- close cursor
759 OPEN bsv_csr;
760 FETCH bsv_csr INTO l_count;
761 IF l_count < 1 THEN
762 IF FND_MSG_PUB.Check_Msg_Level(FND_API.G_RET_STS_UNEXP_ERROR) THEN
763 FND_MESSAGE.SET_NAME('FUN', 'UNEXP_ERROR_OCCURRED_IN_CCID_CHK');
764 FND_MSG_PUB.Add;
765 END IF;
766 Set_Return_Status(x_orig_status => x_return_status,
767 p_new_status => FND_API.G_RET_STS_UNEXP_ERROR);
768 END IF;
769 CLOSE bsv_csr;
770 Debug('IS_CCID_VALID(-)');
771 EXCEPTION
772 WHEN OTHERS THEN
773 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
774 Get_Valid_Bsvs(p_ledger_id => p_ledger_id,p_le_id => p_le_id,x_valid_bsvs => l_valid_bsvs);
775 FND_MESSAGE.SET_NAME('FUN', 'FUN_BSV_LE_NOT_ASSIGNED');
776 FND_MESSAGE.SET_TOKEN('LE', g_le_name);
777 FND_MESSAGE.SET_TOKEN('BSV', l_bal_seg_value);
778 FND_MESSAGE.SET_TOKEN('VALIDBSVS', l_valid_bsvs);
779 FND_MSG_PUB.Add;
780 END IF;
781 */
782 END;
783
784 PROCEDURE Is_Reci_Trx_Balance
785 (
786 x_return_status OUT NOCOPY VARCHAR2,
787 p_dist_lines_tbl IN OUT NOCOPY DIST_LINE_TBL_TYPE
788 ) IS
789 l_pay_dist_total_cr NUMBER := 0;
790 l_pay_dist_total_dr NUMBER := 0;
791 l_dist_line_total_cr NUMBER := 0;
792 l_dist_line_total_dr NUMBER := 0;
793 l_count NUMBER;
794 l_type NUMBER := 0;
795 l_type_success BOOLEAN := true;
796 l_pay_dist_cr_type NUMBER := 0;
797 l_dist_lines_cr_type NUMBER := 0;
798 l_pay_dist_dr_type NUMBER := 0;
799 l_dist_lines_dr_type NUMBER := 0;
800 l_pay_dist_pos_type NUMBER := 0;
801 l_pay_dist_neg_type NUMBER := 0;
802 l_dist_lines_pos_type NUMBER := 0;
803 l_dist_lines_neg_type NUMBER := 0;
804 l_pay_lines_count NUMBER := 0;
805 l_dist_lines_count NUMBER:= 0;
806 BEGIN
807 Debug('IS_RECI_TRX_BALANCE(+)');
808 -- 7.2.2.9
809 x_return_status := FND_API.G_RET_STS_SUCCESS;
810 l_count := p_dist_lines_tbl.COUNT;
811 IF l_count > 0 THEN
812 FOR j IN 1..l_count LOOP
813 IF p_dist_lines_tbl(j).party_type = 'R' THEN
814 IF p_dist_lines_tbl(j).dist_type = 'P' THEN
815 l_pay_lines_count := l_pay_lines_count + 1;
816 IF NVL(p_dist_lines_tbl(j).amount_cr, 0) <> 0 THEN
817 l_pay_dist_cr_type := 1;
818 IF p_dist_lines_tbl(j).amount_cr > 0 THEN
819 l_pay_dist_pos_type := 1;
820 ELSE
821 l_pay_dist_neg_type := 1;
822 END IF;
823 ELSE
824 l_pay_dist_dr_type := 1;
825 IF p_dist_lines_tbl(j).amount_dr > 0 THEN
826 l_pay_dist_pos_type := 1;
827 ELSE
828 l_pay_dist_neg_type := 1;
829 END IF;
830 END IF;
831 l_pay_dist_total_cr := l_pay_dist_total_cr + NVL(p_dist_lines_tbl(j).amount_cr, 0);
832 l_pay_dist_total_dr := l_pay_dist_total_dr + NVL(p_dist_lines_tbl(j).amount_dr, 0);
833 ELSE IF p_dist_lines_tbl(j).dist_type = 'L' THEN
834 l_dist_lines_count := l_dist_lines_count +1;
835 IF NVL(p_dist_lines_tbl(j).amount_cr, 0) <> 0 THEN
836 l_dist_lines_cr_type := 1;
837 IF p_dist_lines_tbl(j).amount_cr > 0 THEN
838 l_dist_lines_pos_type := 1;
839 ELSE
840 l_dist_lines_neg_type := 1;
841 END IF;
842 ELSE
843 l_dist_lines_dr_type := 1;
844 IF p_dist_lines_tbl(j).amount_dr > 0 THEN
845 l_dist_lines_pos_type := 1;
846 ELSE
847 l_dist_lines_neg_type := 1;
848 END IF;
849 END IF;
850 l_dist_line_total_cr := l_dist_line_total_cr + NVL(p_dist_lines_tbl(j).amount_cr, 0);
851 l_dist_line_total_dr := l_dist_line_total_dr + NVL(p_dist_lines_tbl(j).amount_dr, 0);
852 END IF;
853 END IF;
854 END IF;
855 END LOOP;
856 END IF;
857 IF (l_pay_dist_total_cr <> l_dist_line_total_dr OR
858 l_pay_dist_total_dr <> l_dist_line_total_cr) THEN
859 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
860 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INCOMLT_SUM_REC_DIST');
861 FND_MSG_PUB.Add;
862 END IF;
863 Set_Return_Status(x_orig_status => x_return_status,
864 p_new_status => FND_API.G_RET_STS_ERROR);
865 END IF;
866 IF l_pay_dist_cr_type = l_pay_dist_dr_type OR
867 l_dist_lines_cr_type = l_dist_lines_dr_type OR
868 l_pay_dist_pos_type = l_pay_dist_neg_type OR
869 l_dist_lines_pos_type = l_dist_lines_neg_type THEN
870 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
871 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_SIGNS_TRX_LINE');
872 FND_MSG_PUB.Add;
873 END IF;
874 Set_Return_Status(x_orig_status => x_return_status,
875 p_new_status => FND_API.G_RET_STS_ERROR);
876 END IF;
877 Debug('IS_RECI_TRX_BALANCE(-)');
878 END;
879
880 PROCEDURE Is_Batch_Balance
881 (
882 x_return_status OUT NOCOPY VARCHAR2,
883 p_init_dist_tbl IN OUT NOCOPY INIT_DIST_TBL_TYPE,
884 p_dist_lines_tbl IN OUT NOCOPY DIST_LINE_TBL_TYPE
885 ) IS
886 l_init_dist_total_cr NUMBER := 0;
887 l_init_dist_total_dr NUMBER := 0;
888 l_dist_line_total_cr NUMBER := 0;
889 l_dist_line_total_dr NUMBER := 0;
890 l_count NUMBER;
891 l_type NUMBER := 0;
892 l_type_success BOOLEAN := true;
893 l_init_dist_cr_type NUMBER := 0;
894 l_dist_lines_cr_type NUMBER := 0;
895 l_init_dist_dr_type NUMBER := 0;
896 l_dist_lines_dr_type NUMBER := 0;
897 l_init_dist_pos_type NUMBER := 0;
898 l_init_dist_neg_type NUMBER := 0;
899 l_dist_lines_pos_type NUMBER := 0;
900 l_dist_lines_neg_type NUMBER := 0;
901 BEGIN
902 Debug('IS_BATCH_BALANCE(+)');
903 -- 7.2.2.9
904 x_return_status := FND_API.G_RET_STS_SUCCESS;
905 l_count := p_init_dist_tbl.COUNT;
906 IF l_count > 0 THEN
907 FOR i IN 1..l_count LOOP
908 IF NVL(p_init_dist_tbl(i).amount_cr, 0) <> 0 THEN
909 l_init_dist_cr_type := 1;
910 IF p_init_dist_tbl(i).amount_cr > 0 THEN
911 l_init_dist_pos_type := 1;
912 ELSE
913 l_init_dist_neg_type := 1;
914 END IF;
915 ELSE
916 l_init_dist_dr_type := 1;
917 IF p_init_dist_tbl(i).amount_dr > 0 THEN
918 l_init_dist_pos_type := 1;
919 ELSE
920 l_init_dist_neg_type := 1;
921 END IF;
922 END IF;
923 l_init_dist_total_cr := l_init_dist_total_cr + NVL(p_init_dist_tbl(i).amount_cr, 0);
924 l_init_dist_total_dr := l_init_dist_total_dr + NVL(p_init_dist_tbl(i).amount_dr, 0);
925 END LOOP;
926 END IF;
927 l_count := p_dist_lines_tbl.COUNT;
928 IF l_count > 0 THEN
929 FOR j IN 1..l_count LOOP
930 IF p_dist_lines_tbl(j).dist_type = 'R' THEN
931 IF NVL(p_dist_lines_tbl(j).amount_cr, 0) <> 0 THEN
932 l_dist_lines_cr_type := 1;
933 IF p_dist_lines_tbl(j).amount_cr > 0 THEN
934 l_dist_lines_pos_type := 1;
935 ELSE
936 l_dist_lines_neg_type := 1;
937 END IF;
938 ELSE
939 l_dist_lines_dr_type := 1;
940 IF p_dist_lines_tbl(j).amount_dr > 0 THEN
941 l_dist_lines_pos_type := 1;
942 ELSE
943 l_dist_lines_neg_type := 1;
944 END IF;
945 END IF;
946 l_dist_line_total_cr := l_dist_line_total_cr + NVL(p_dist_lines_tbl(j).amount_cr, 0);
947 l_dist_line_total_dr := l_dist_line_total_dr + NVL(p_dist_lines_tbl(j).amount_dr, 0);
948 END IF;
949 END LOOP;
950 END IF;
951 IF (l_init_dist_total_cr <> l_dist_line_total_dr OR
952 l_init_dist_total_dr <> l_dist_line_total_cr) THEN
953 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
954 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INCOMLT_SUM_INI_DIST');
955 FND_MSG_PUB.Add;
956 END IF;
957 Set_Return_Status(x_orig_status => x_return_status,
958 p_new_status => FND_API.G_RET_STS_ERROR);
959 END IF;
960 IF l_init_dist_cr_type = l_init_dist_dr_type OR
961 l_dist_lines_cr_type = l_dist_lines_dr_type OR
962 l_init_dist_pos_type = l_init_dist_neg_type OR
963 l_dist_lines_pos_type = l_dist_lines_neg_type THEN
964 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
965 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_SIGNS_TRX_LINE');
966 FND_MSG_PUB.Add;
967 END IF;
968 Set_Return_Status(x_orig_status => x_return_status,
969 p_new_status => FND_API.G_RET_STS_ERROR);
970 END IF;
971 Debug('IS_BATCH_BALANCE(-)');
972 END;
973
974 -- Procedure added by Bidisha as part of the transaction UI enhancements
975 PROCEDURE Is_Auto_Batch_Balance (
976 x_return_status OUT NOCOPY VARCHAR2,
977 p_trx_tbl IN OUT NOCOPY TRX_TBL_TYPE,
978 p_init_dist_tbl IN OUT NOCOPY INIT_DIST_TBL_TYPE,
979 p_dist_lines_tbl IN OUT NOCOPY DIST_LINE_TBL_TYPE,
980 p_validate_dist IN VARCHAR2,
981 p_currency_code IN VARCHAR2)
982 IS
983
984 l_init_dist_total_cr NUMBER := 0;
985 l_init_dist_total_dr NUMBER := 0;
986 l_dist_line_total_cr NUMBER := 0;
987 l_dist_line_total_dr NUMBER := 0;
988 l_trx_total_cr NUMBER := 0;
989 l_trx_total_dr NUMBER := 0;
990
991 l_init_dist_cr_type NUMBER := 0;
992 l_trx_cr_type NUMBER := 0;
993 l_init_dist_dr_type NUMBER := 0;
994 l_trx_dr_type NUMBER := 0;
995 l_init_dist_pos_type NUMBER := 0;
996 l_init_dist_neg_type NUMBER := 0;
997 l_trx_pos_type NUMBER := 0;
998 l_trx_neg_type NUMBER := 0;
999
1000 l_count NUMBER := 0;
1001 l_mau NUMBER; -- minimum accountable units
1002
1003 BEGIN
1004 Debug('IS_AUTO_BATCH_BALANCE(+)');
1005
1006 x_return_status := FND_API.G_RET_STS_SUCCESS;
1007
1008 --Bug: 8712286. Get currency information from FND_CURRENCIES table
1009 SELECT nvl( minimum_accountable_unit, power( 10, (-1 * precision)))
1010 INTO l_mau
1011 FROM FND_CURRENCIES
1012 WHERE currency_code = p_currency_code;
1013
1014
1015 -- Sum the transaction Amounts
1016 l_count := p_trx_tbl.COUNT;
1017 IF l_count > 0
1018 THEN
1019 FOR i IN 1..l_count LOOP
1020 IF NVL(p_trx_tbl(i).init_amount_cr, 0) <> 0
1021 THEN
1022 l_trx_cr_type := 1;
1023 IF p_trx_tbl(i).init_amount_cr > 0 THEN
1024 l_trx_pos_type := 1;
1025 ELSE
1026 l_trx_neg_type := 1;
1027 END IF;
1028 END IF;
1029 IF NVL(p_trx_tbl(i).init_amount_dr, 0) <> 0
1030 THEN
1031 l_trx_dr_type := 1;
1032 IF p_trx_tbl(i).init_amount_dr > 0 THEN
1033 l_trx_pos_type := 1;
1034 ELSE
1035 l_trx_neg_type := 1;
1036 END IF;
1037 END IF;
1038 l_trx_total_cr := l_trx_total_cr + NVL(p_trx_tbl(i).init_amount_cr, 0);
1039 l_trx_total_dr := l_trx_total_dr + NVL(p_trx_tbl(i).init_amount_dr, 0);
1040 END LOOP;
1041 END IF;
1042
1043 -- Sum the Batch Distribution Amounts
1044 l_count := p_init_dist_tbl.COUNT;
1045 IF l_count > 0
1046 THEN
1047 FOR i IN 1..l_count LOOP
1048 IF NVL(p_init_dist_tbl(i).amount_cr, 0) <> 0
1049 THEN
1050 l_init_dist_cr_type := 1;
1051 IF p_init_dist_tbl(i).amount_cr > 0 THEN
1052 l_init_dist_pos_type := 1;
1053 ELSE
1054 l_init_dist_neg_type := 1;
1055 END IF;
1056 END IF;
1057 IF NVL(p_init_dist_tbl(i).amount_dr, 0) <> 0
1058 THEN
1059 l_init_dist_dr_type := 1;
1060 IF p_init_dist_tbl(i).amount_dr > 0 THEN
1061 l_init_dist_pos_type := 1;
1062 ELSE
1063 l_init_dist_neg_type := 1;
1064 END IF;
1065 END IF;
1066 --Bug: 8712286
1067 p_init_dist_tbl(i).amount_cr := ROUND(nvl(p_init_dist_tbl(i).amount_cr, 0)/l_mau)*l_mau;
1068 p_init_dist_tbl(i).amount_dr := ROUND(nvl(p_init_dist_tbl(i).amount_dr, 0)/l_mau)*l_mau;
1069
1070 l_init_dist_total_cr := l_init_dist_total_cr + NVL(p_init_dist_tbl(i).amount_cr, 0);
1071 l_init_dist_total_dr := l_init_dist_total_dr + NVL(p_init_dist_tbl(i).amount_dr, 0);
1072 END LOOP;
1073 END IF;
1074
1075 -- No validations on dist lines for now.
1076
1077 IF (p_validate_dist = 'Y') AND (l_trx_total_cr <> l_init_dist_total_dr OR
1078 l_trx_total_dr <> l_init_dist_total_cr )
1079 THEN
1080 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1081 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INCOMLT_SUM_INI_DIST');
1082 FND_MSG_PUB.Add;
1083 END IF;
1084 Set_Return_Status(x_orig_status => x_return_status,
1085 p_new_status => FND_API.G_RET_STS_ERROR);
1086 END IF;
1087
1088 IF l_trx_cr_type = l_trx_dr_type OR
1089 l_trx_pos_type = l_trx_neg_type
1090 THEN
1091 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1092 THEN
1093 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_SIGNS_TRX_LINE');
1094 FND_MSG_PUB.Add;
1095 END IF;
1096 Set_Return_Status(x_orig_status => x_return_status,
1097 p_new_status => FND_API.G_RET_STS_ERROR);
1098 END IF;
1099 Debug('IS_AUTO_BATCH_BALANCE(-)');
1100 END Is_Auto_Batch_Balance;
1101
1102
1103 PROCEDURE Is_Curr_Fld_Valid
1104 ( x_return_status OUT NOCOPY VARCHAR2,
1105 p_curr_code IN VARCHAR2,
1106 p_ledger_id IN NUMBER,
1107 p_trx_date IN DATE,
1108 p_exchange_rate_type IN VARCHAR2
1109 ) IS
1110 l_default_currency VARCHAR2(15);
1111 l_count NUMBER;
1112 l_conv_type VARCHAR2(30);
1113 l_rate NUMBER;
1114
1115 CURSOR currency_csr IS
1116 SELECT COUNT(*)
1117 FROM fnd_currencies_vl
1118 WHERE currency_code = p_curr_code
1119 AND enabled_flag = 'Y'
1120 AND nvl(start_date_active, p_trx_date) <= nvl(p_trx_date, sysdate)
1121 AND nvl(end_date_active, p_trx_date) >= nvl(p_trx_date, sysdate);
1122 /* use currency API
1123 CURSOR currency_rate_csr IS
1124 SELECT COUNT(*)
1125 FROM GL_DAILY_RATES_V
1126 WHERE conversion_date = p_trx_date
1127 AND exchange_rate_type = l_conv_type
1128 AND from_currency = p_curr_code
1129 AND to_currency = l_func_curr;
1130 */
1131 BEGIN
1132 Debug('IS_CURR_FLD_VALID(+)');
1133 -- 7.2.2.10
1134 x_return_status := FND_API.G_RET_STS_SUCCESS;
1135 l_default_currency := Fun_System_Options_Pkg.Get_Default_Currency;
1136 l_conv_type := p_exchange_rate_type;
1137 IF l_default_currency IS NOT NULL THEN
1138 IF p_curr_code <> l_default_currency THEN
1139 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1140 FND_MESSAGE.SET_NAME('FUN', 'FUN_CUR_NOTEQUAL_ENTRD_CUR');
1141 FND_MSG_PUB.Add;
1142 END IF;
1143 Set_Return_Status(x_orig_status => x_return_status,
1144 p_new_status => FND_API.G_RET_STS_ERROR);
1145 RETURN;
1146 END IF;
1147 END IF;
1148
1149 OPEN currency_csr; -- bug 5160257
1150 FETCH currency_csr INTO l_count;
1151 CLOSE currency_csr;
1152 IF l_count < 1 THEN
1153 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1154 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_CURRENCY');
1155 FND_MSG_PUB.Add;
1156 END IF;
1157 Set_Return_Status(x_orig_status => x_return_status,
1158 p_new_status => FND_API.G_RET_STS_ERROR);
1159 END IF;
1160 /* Not used, replaced by GL currency API
1161 OPEN currency_rate_csr;
1162 FETCH currency_rate_csr INTO l_count;
1163 CLOSE currency_rate_csr;
1164 IF l_count < 1 THEN
1165 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1166 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_CONV_RATE_NOT_FOUND');
1167 FND_MSG_PUB.Add;
1168 END IF;
1169 Set_Return_Status(x_orig_status => x_return_status,
1170 p_new_status => FND_API.G_RET_STS_ERROR);
1171 END IF;
1172 */
1173 l_rate := GL_CURRENCY_API.Get_Rate_Sql(p_ledger_id, p_curr_code, p_trx_date, l_conv_type);
1174
1175 IF l_rate = -1 THEN
1176 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1177 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_CONV_RATE_NOT_FOUND');
1178 FND_MSG_PUB.Add;
1179 END IF;
1180 ELSIF l_rate = -2 THEN
1181 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1182 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_CURRENCY');
1183 FND_MSG_PUB.Add;
1184 END IF;
1185 END IF;
1186 Debug('IS_CURR_FLD_VALID(-)');
1187 END;
1188
1189
1190 PROCEDURE Is_IC_Relationship_Valid
1191 ( x_return_status OUT NOCOPY VARCHAR2 ,
1192 p_initiator_id IN NUMBER,
1193 p_from_le_id IN NUMBER,
1194 p_batch_date IN DATE,
1195 p_trx_tbl IN OUT NOCOPY TRX_TBL_TYPE
1196 ) IS
1197 l_count NUMBER;
1198 i NUMBER := 1;
1199 BEGIN
1200 Debug('IS_IC_RELATIONSHIP_VALID(+)');
1201 x_return_status := FND_API.G_RET_STS_SUCCESS;
1202 -- 7.2.2.1
1203 l_count := p_trx_tbl.COUNT;
1204 WHILE l_count <> 0 AND i <= l_count LOOP
1205 IF p_from_le_id = p_trx_tbl(i).to_le_id THEN
1206 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1207 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_IC_RELATIONSHIP');
1208 FND_MSG_PUB.Add;
1209 END IF;
1210 Set_Return_Status(x_orig_status => x_return_status,
1211 p_new_status => FND_API.G_RET_STS_ERROR);
1212 END IF;
1213 i := i + 1;
1214 END LOOP;
1215 Debug('IS_IC_RELATIONSHIP_VALID(-)');
1216 END;
1217
1218 PROCEDURE Is_Reci_Not_Duplicated
1219 ( x_return_status OUT NOCOPY VARCHAR2 ,
1220 p_initiator_id IN NUMBER,
1221 p_trx_tbl IN OUT NOCOPY TRX_TBL_TYPE
1222 ) IS
1223 l_count NUMBER;
1224 i NUMBER := 1;
1225 j NUMBER := 2;
1226 BEGIN
1227 Debug('IS_RECI_NOT_DUPLICATED(+)');
1228 x_return_status := FND_API.G_RET_STS_SUCCESS;
1229 -- 7.2.1.4
1230 l_count := p_trx_tbl.COUNT;
1231 WHILE l_count <> 0 AND i <= l_count LOOP
1232 WHILE l_count <> 0 AND j <= l_count LOOP
1233 IF p_trx_tbl(i).recipient_id = p_initiator_id
1234 OR p_trx_tbl(i).recipient_id = p_trx_tbl(j).recipient_id THEN
1235 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1236 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_DUPLICATE_RECP');
1237 FND_MSG_PUB.Add;
1238 END IF;
1239 Set_Return_Status(x_orig_status => x_return_status,
1240 p_new_status => FND_API.G_RET_STS_ERROR);
1241 END IF;
1242 j := j + 1;
1243 END LOOP;
1244 i := i + 1;
1245 j := i + 1;
1246 END LOOP;
1247 Debug('IS_RECI_NOT_DUPLICATED(-)');
1248 END;
1249
1250
1251 PROCEDURE Is_Trx_Type_Valid
1252 ( x_return_status OUT NOCOPY VARCHAR2 ,
1253 p_trx_type_id IN NUMBER
1254 ) IS
1255 l_trx_type_code VARCHAR2(25);
1256 l_need_invoice VARCHAR2(1);
1257 l_enabled VARCHAR2(1);
1258 BEGIN
1259 Debug('IS_TRX_TYPE_VALID(+)');
1260 x_return_status := FND_API.G_RET_STS_SUCCESS;
1261 Fun_Trx_Types_Pub.get_trx_type_by_id(p_trx_type_id,
1262 l_trx_type_code,
1263 l_need_invoice,
1264 l_enabled);
1265 IF l_enabled = 'N' THEN
1266 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1267 FND_MESSAGE.SET_NAME('FUN','FUN_API_INVALID_TRX_TYPE');
1268 FND_MSG_PUB.Add;
1269 END IF;
1270 Set_Return_Status(x_orig_status => x_return_status,
1271 p_new_status => FND_API.G_RET_STS_ERROR);
1272 END IF;
1273 Debug('IS_TRX_TYPE_VALID(-)');
1274 END;
1275
1276 PROCEDURE Is_Init_GL_Date_Valid
1277 (
1278 x_return_status OUT NOCOPY VARCHAR2,
1279 p_from_le_id IN NUMBER,
1280 p_gl_date IN DATE,
1281 p_trx_type_id IN NUMBER
1282
1283 ) IS
1284 CURSOR period_open_csr IS
1285 SELECT count(*)
1286 FROM GL_PERIOD_STATUSES PST
1287 WHERE pst.application_id = 435
1288 AND pst.closing_status <> 'N'
1289 AND pst.adjustment_period_flag <> 'Y'
1290 AND pst.ledger_id = p_from_le_id;
1291 l_count NUMBER;
1292 l_result VARCHAR2(1);
1293 BEGIN
1294 Debug('IS_INIT_GL_DATE_VALID(+)');
1295 x_return_status := FND_API.G_RET_STS_SUCCESS;
1296 -- 7.2.2.3
1297 /* Problem: good to create a view for the periods
1298 copy from AR_PERIODS_V
1299 SELECT PST.PERIOD_NAME
1300 , PST.PERIOD_YEAR
1301 , PST.PERIOD_NUM
1302 , PST.START_DATE
1303 , PST.END_DATE
1304 FROM GL_PERIOD_STATUSES PST
1305 , AR_SYSTEM_PARAMETERS SP
1306 WHERE PST.SET_OF_BOOKS_ID = SP.SET_OF_BOOKS_ID
1307 AND PST.APPLICATION_ID = 222
1308 AND PST.CLOSING_STATUS <> 'N'
1309 AND PST.ADJUSTMENT_PERIOD_FLAG <> 'Y'
1310
1311 */
1312 /* Problem: Need to wait for Intercompany period to exist
1313 OPEN period_open_csr;
1314 FETCH period_open_csr INTO l_count;
1315 CLOSE period_open_csr;
1316 IF l_count < 1 THEN
1317 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1318 FND_MESSAGE.SET_NAME('FUN', 'FUN_PERIOD_NOT_OPEN');
1319 FND_MSG_PUB.Add;
1320 END IF;
1321 Set_Return_Status(x_orig_status => x_return_status,
1322 p_new_status => FND_API.G_RET_STS_ERROR);
1323 END IF;
1324 */
1325 l_result := fun_period_status_pkg.get_fun_prd_status(p_gl_date, p_trx_type_id);
1326 IF l_result <> 'O' THEN
1327 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1328 FND_MESSAGE.SET_NAME('FUN', 'FUN_PERIOD_NOT_OPEN');
1329 FND_MSG_PUB.Add;
1330 END IF;
1331 Set_Return_Status(x_orig_status => x_return_status,
1332 p_new_status => FND_API.G_RET_STS_ERROR);
1333 END IF;
1334
1335
1336 Debug('IS_INIT_GL_DATE_VALID(-)');
1337 END;
1338
1339 FUNCTION get_inter_intra return varchar2
1340 is
1341 l_inter_intra fun_system_options.allow_inter_intra%type;
1342 begin
1343 select allow_inter_intra into l_inter_intra from fun_system_options;
1344 return l_inter_intra;
1345 end;
1346
1347 PROCEDURE Init_Batch_Validate
1348 ( p_api_version IN NUMBER,
1349 p_init_msg_list IN VARCHAR2 ,
1350 p_validation_level IN NUMBER ,
1351 x_return_status OUT NOCOPY VARCHAR2,
1352 x_msg_count OUT NOCOPY NUMBER,
1353 x_msg_data OUT NOCOPY VARCHAR2,
1354 p_insert IN VARCHAR2 ,
1355 p_batch_rec IN OUT NOCOPY BATCH_REC_TYPE,
1356 p_trx_tbl IN OUT NOCOPY TRX_TBL_TYPE,
1357 p_init_dist_tbl IN OUT NOCOPY INIT_DIST_TBL_TYPE,
1358 p_dist_lines_tbl IN OUT NOCOPY DIST_LINE_TBL_TYPE
1359
1360 ) IS
1361 l_api_name CONSTANT VARCHAR2(30) := 'INIT_BATCH_VALIDATE';
1362 l_api_version CONSTANT NUMBER := 1.0;
1363 l_return_status VARCHAR(1);
1364 l_local VARCHAR(1) ;
1365 l_validate_dist VARCHAR2(1);
1366 l_msg_count number;
1367 l_msg_data varchar2(2000);
1368 l_intercompany_exception varchar2(1);
1369 l_from_ou_id number;
1370 l_invoicing_rule VARCHAR(1);
1371 l_creation_sign number;
1372 l_init_amount_dr number;
1373 l_init_amount_cr number;
1374 l_trx_type varchar2(4);
1375
1376 BEGIN
1377 l_local := 'Y';
1378 -- Standard call to check for call compatibility.
1379 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1380 p_api_version,
1381 l_api_name,
1382 G_PKG_NAME )
1383 THEN
1384 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1385 END IF;
1386
1387 -- Initialize message list if p_init_msg_list is set to TRUE.
1388 IF FND_API.to_Boolean( nvl(p_init_msg_list,FND_API.G_FALSE )) THEN
1389 FND_MSG_PUB.initialize;
1390 END IF;
1391
1392 -- Initialize API return status to success
1393 x_return_status := FND_API.G_RET_STS_SUCCESS;
1394
1395 -- API body
1396 Debug('INIT_BATCH_VALIDATE(+)');
1397 IF nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL) >= 50 THEN
1398
1399 l_validate_dist := 'N';
1400
1401 -- 7.2.1.2 Validate on batch number, initiator, currency, batch type,
1402 -- GL date, batch date
1403 IF (p_batch_rec.batch_number IS NULL OR
1404 p_batch_rec.initiator_id IS NULL OR
1405 p_batch_rec.currency_code IS NULL OR
1406 p_batch_rec.trx_type_id IS NULL OR
1407 p_batch_rec.gl_date IS NULL OR
1408 p_batch_rec.batch_date IS NULL) THEN
1409 Set_Return_Status(x_orig_status => x_return_status,
1410 p_new_status => FND_API.G_RET_STS_ERROR);
1411 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1412 FND_MESSAGE.SET_NAME('FUN', 'FUN_REQUIRED_FIELDS_INCOMPLETE');
1413 FND_MSG_PUB.Add;
1414 END IF;
1415 END IF;
1416 IF (nvl(p_insert,FND_API.G_TRUE) = FND_API.G_TRUE) THEN
1417 Is_Batch_Num_Unique(x_return_status => l_return_status,
1418 p_batch_number => p_batch_rec.batch_number,
1419 p_initiator_id => p_batch_rec.initiator_id);
1420 Set_Return_Status(x_orig_status => x_return_status,
1421 p_new_status => l_return_status);
1422 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1424 END IF;
1425 END IF;
1426
1427 --check for duplicate transaction number
1428 Is_trx_Num_Unique( x_return_status => l_return_status,
1429 p_batch_id => p_batch_rec.batch_id,
1430 p_trx_tbl => p_trx_tbl
1431 );
1432 Set_Return_Status(x_orig_status => x_return_status,
1433 p_new_status => l_return_status);
1434 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1436 END IF;
1437
1438
1439 /* Start of bug 5284760 */
1440 /* Fetch the operating unit id for the initiator party id */
1441 /* This is later passed to get_ar_trx_creation_sign to fetch the
1442 transaction creation sign */
1443 l_from_ou_id := Fun_Tca_Pkg.Get_OU_Id(p_batch_rec.initiator_id,
1444 p_batch_rec.batch_date);
1445 /* IF l_from_ou_id IS NOT NULL
1446 THEN
1447 -- Fetch the transaction creation sign of
1448 -- the associated AR transaction type
1449 l_creation_sign := FUN_TRX_TYPES_PUB.get_ar_trx_creation_sign(
1450 l_from_ou_id,
1451 p_batch_rec.trx_type_id,
1452 p_batch_rec.batch_date);
1453 END IF; */
1454
1455 /* End of bug 5284760 */
1456 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1457 FOR i IN 1..p_trx_tbl.COUNT LOOP
1458 -- see if any recipient is in local instance
1459 -- if yes, then need to check the MSR value
1460
1461 IF l_from_ou_id IS NOT NULL
1462 THEN
1463 -- Fetch the transaction creation sign of
1464 -- the associated AR transaction type
1465 -- ER: 8288979
1466 l_trx_type := 'INV';
1467 l_init_amount_dr := p_trx_tbl(i).init_amount_dr;
1468 l_init_amount_cr := p_trx_tbl(i).init_amount_cr;
1469 IF(l_init_amount_dr is null) THEN
1470 l_init_amount_dr := 0;
1471 END IF;
1472 IF(l_init_amount_cr is null) THEN
1473 l_init_amount_cr := 0;
1474 END IF;
1475 -- Credit memo transaction
1476 if( l_init_amount_dr < 0 OR l_init_amount_cr > 0) THEN
1477 l_trx_type := 'CM';
1478 END IF;
1479
1480 l_creation_sign := FUN_TRX_TYPES_PUB.get_ar_trx_creation_sign(
1481 l_from_ou_id,
1482 p_batch_rec.trx_type_id,
1483 p_batch_rec.batch_date,
1484 l_trx_type);
1485
1486 END IF;
1487 IF p_trx_tbl(i).recipient_instance = 'N' THEN
1488 l_local := 'N';
1489 END IF;
1490
1491 /* For bug 4724672 check if initiator and reci does not
1492 break intercompany exceptions */
1493
1494 XLE_UTILITIES_GRP.Is_Intercompany_LEID(
1495 p_api_version => l_api_version,
1496 p_init_msg_list => FND_API.G_FALSE,
1497 p_commit => FND_API.G_FALSE,
1498 x_return_status => l_return_status,
1499 x_msg_count => l_msg_count,
1500 x_msg_data => l_msg_data,
1501 p_legal_entity_id1 => p_batch_rec.from_le_id,
1502 p_legal_entity_id2 => p_trx_tbl(i).to_le_id,
1503 x_Intercompany => l_intercompany_exception) ;
1504
1505 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1507 END IF;
1508
1509 if(l_intercompany_exception = 'N') THEN
1510
1511 FND_MESSAGE.SET_NAME('FUN', 'FUN_INTERCOMPANY_EXCEPTION');
1512 FND_MESSAGE.SET_TOKEN('TRX_NUMBER',p_trx_tbl(i).trx_number);
1513 FND_MSG_PUB.Add;
1514 Set_Return_Status(x_orig_status => x_return_status,
1515 p_new_status => FND_API.G_RET_STS_ERROR);
1516 end if;
1517 /* end of bug 4724672 */
1518
1519 /* Start of bug 5284760 */
1520 /* Code to validate the sign of the amount entered against the
1521 AR transaction creation sign. This validation must be performance
1522 if invoicing is enabled at any of the following levels -
1523 transaction type, initiator or recipient */
1524
1525 /* l_invoicing_rule will be 'Y', if invoicing is enabled at
1526 transaction type level */
1527 l_invoicing_rule := p_trx_tbl(i).invoicing_rule;
1528
1529 /* If invoicing is not enabled at transaction level,
1530 check to see if it is enabled at initiator or recipient
1531 levels */
1532 IF l_invoicing_rule = 'N' THEN
1533 check_invoice_reqd_flag(p_init_party_id => p_batch_rec.initiator_id,
1534 p_init_le_id => p_batch_rec.from_le_id,
1535 p_reci_party_id => p_trx_tbl(i).recipient_id,
1536 p_reci_le_id => p_trx_tbl(i).to_le_id,
1537 p_ttyp_invoice_flag => 'N',
1538 x_invoice_required => l_invoicing_rule,
1539 x_return_status => l_return_status);
1540 Set_Return_Status(x_orig_status => x_return_status,
1541 p_new_status => l_return_status);
1542 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1543 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1544 END IF;
1545 IF l_invoicing_rule IS NULL THEN
1546 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1547 END IF;
1548 END IF;
1549
1550
1551
1552 /* If invoicing is enabled then validate the amount against
1553 the creation sign */
1554 IF l_invoicing_rule = 'Y' THEN
1555
1556 IF l_creation_sign IS NULL THEN
1557 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1558 IF l_trx_type = 'INV' THEN
1559
1560 FND_MESSAGE.SET_NAME('FUN', 'FUN_NO_TRX_TYPE_MAP');
1561 FND_MESSAGE.SET_TOKEN('TRX_TYPE','Invoice Type');
1562 ELSE
1563
1564 FND_MESSAGE.SET_NAME('FUN', 'FUN_NO_TRX_TYPE_MAP');
1565 FND_MESSAGE.SET_TOKEN('TRX_TYPE','Credit Memo Type');
1566 END IF;
1567 FND_MSG_PUB.Add;
1568 END IF;
1569 Set_Return_Status(x_orig_status => x_return_status,
1570 p_new_status => FND_API.G_RET_STS_ERROR);
1571 EXIT;
1572 END IF;
1573 /* If creation sign is positive and amount entered is
1574 negative, then throw an error*/
1575 IF (p_trx_tbl(i).init_amount_cr > 0 or p_trx_tbl(i).init_amount_dr < 0)
1576 and l_creation_sign = 1 THEN
1577 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1578 FND_MESSAGE.SET_NAME('FUN', 'FUN_TRX_TYPE_POS_AMT');
1579 FND_MESSAGE.SET_TOKEN('TRX_NUMBER',p_trx_tbl(i).trx_number);
1580 FND_MSG_PUB.Add;
1581 END IF;
1582 Set_Return_Status(x_orig_status => x_return_status,
1583 p_new_status => FND_API.G_RET_STS_ERROR);
1584 END IF;
1585 /* If creation sign is negative and amount entered is
1586 positive, then throw an error*/
1587 IF (p_trx_tbl(i).init_amount_dr > 0 or p_trx_tbl(i).init_amount_cr < 0)
1588 and l_creation_sign = -1 THEN
1589 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1590 FND_MESSAGE.SET_NAME('FUN', 'FUN_TRX_TYPE_NEG_AMT');
1591 FND_MESSAGE.SET_TOKEN('TRX_NUMBER',p_trx_tbl(i).trx_number);
1592 FND_MSG_PUB.Add;
1593 END IF;
1594 Set_Return_Status(x_orig_status => x_return_status,
1595 p_new_status => FND_API.G_RET_STS_ERROR);
1596 END IF;
1597 END IF;
1598 /* End of bug 5284760 */
1599 END LOOP;
1600 Is_Party_Valid(x_return_status => l_return_status,
1601 p_party_id => p_batch_rec.initiator_id,
1602 p_le_id => p_batch_rec.from_le_id,
1603 p_ledger_id => p_batch_rec.from_ledger_id,
1604 p_instance => 'Y',
1605 p_local => l_local,
1606 p_type => 'I',
1607 p_batch_date => p_batch_rec.gl_date,
1608 p_trx_number => NULL);
1609 Set_Return_Status(x_orig_status => x_return_status,
1610 p_new_status => l_return_status);
1611 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1613 END IF;
1614 Is_Trx_Type_Valid(x_return_status => l_return_status,
1615 p_trx_type_id => p_batch_rec.trx_type_id);
1616 Set_Return_Status(x_orig_status => x_return_status,
1617 p_new_status => l_return_status);
1618 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1620 END IF;
1621 Is_Init_GL_Date_Valid(x_return_status => l_return_status,
1622 p_from_le_id => p_batch_rec.from_le_id,
1623 p_gl_date => p_batch_rec.gl_date,
1624 p_trx_type_id => p_batch_rec.trx_type_id);
1625 Set_Return_Status(x_orig_status => x_return_status,
1626 p_new_status => l_return_status);
1627 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1629 END IF;
1630
1631 -- 7.2.2.6 Note: Do not need to check at least 1 dist line
1632 -- as we check total trx amounts = total dist amounts
1633 IF p_trx_tbl.count < 1 THEN
1634 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1635 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_RECI_LINE_NOT_FOUND');
1636 -- Problem: Double check with msg repository
1637 FND_MSG_PUB.Add;
1638 END IF;
1639 Set_Return_Status(x_orig_status => x_return_status,
1640 p_new_status => FND_API.G_RET_STS_ERROR);
1641 END IF;
1642
1643 IF nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL) >= FND_API.G_VALID_LEVEL_FULL THEN
1644
1645 l_validate_dist := 'Y';
1646
1647 Is_Curr_Fld_Valid(x_return_status => l_return_status,
1648 p_curr_code => p_batch_rec.currency_code,
1649 p_ledger_id => p_batch_rec.from_ledger_id,
1650 p_trx_date => p_batch_rec.gl_date,
1651 p_exchange_rate_type => p_batch_rec.exchange_rate_type);
1652 Set_Return_Status(x_orig_status => x_return_status,
1653 p_new_status => l_return_status);
1654 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1656 END IF;
1657
1658 END IF; -- end full validation
1659 END IF; -- end unique field success
1660 END IF; --Val level 50
1661
1662 -- Call a new procedure to validate automatic distribution
1663 -- mode batch
1664 IF Nvl(p_batch_rec.automatic_proration_flag,'N') = 'Y'
1665 THEN
1666 --Bug: 8712286. Passing currency_code.
1667 Is_Auto_Batch_Balance
1668 (x_return_status => l_return_status,
1669 p_trx_tbl => p_trx_tbl,
1670 p_init_dist_tbl => p_init_dist_tbl,
1671 p_dist_lines_tbl => p_dist_lines_tbl,
1672 p_validate_dist => l_validate_dist,
1673 p_currency_code => p_batch_rec.currency_code);
1674 END IF;
1675
1676 Debug('INIT_BATCH_VALIDATE(-)');
1677 -- End of API body.
1678 -- Standard call to get message count and if count is 1, get message info.
1679 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1680 p_data => x_msg_data);
1681 EXCEPTION
1682 WHEN FND_API.G_EXC_ERROR THEN
1683 x_return_status := FND_API.G_RET_STS_ERROR ;
1684 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1685 p_data => x_msg_data);
1686 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1688 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1689 p_data => x_msg_data);
1690 WHEN OTHERS THEN
1691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1692 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1693 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1694 END IF;
1695 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1696 p_data => x_msg_data);
1697
1698
1699 END Init_Batch_Validate;
1700
1701
1702 PROCEDURE Init_Trx_Validate
1703 ( p_api_version IN NUMBER,
1704 p_init_msg_list IN VARCHAR2 ,
1705 p_validation_level IN NUMBER ,
1706 x_return_status OUT NOCOPY VARCHAR2,
1707 x_msg_count OUT NOCOPY NUMBER,
1708 x_msg_data OUT NOCOPY VARCHAR2,
1709 p_trx_rec IN OUT NOCOPY TRX_REC_TYPE,
1710 p_dist_lines_tbl IN OUT NOCOPY DIST_LINE_TBL_TYPE,
1711 p_currency_code IN VARCHAR2,
1712 p_gl_date IN DATE,
1713 p_trx_date IN DATE,
1714 p_exchange_rate_type IN VARCHAR2
1715 ) IS
1716 l_api_name CONSTANT VARCHAR2(30) := 'INIT_TRX_VALIDATE';
1717 l_api_version CONSTANT NUMBER := 1.0;
1718 l_return_status VARCHAR(1);
1719 l_local VARCHAR(1) ;
1720 default_currency_code VARCHAR2(15) := fun_system_options_pkg.get_default_currency();
1721 BEGIN
1722 l_local := 'Y';
1723
1724 -- Standard call to check for call compatibility.
1725 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1726 p_api_version,
1727 l_api_name,
1728 G_PKG_NAME )
1729 THEN
1730 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1731 END IF;
1732
1733 -- Initialize message list if p_init_msg_list is set to TRUE.
1734 IF FND_API.to_Boolean( nvl(p_init_msg_list ,FND_API.G_FALSE)) THEN
1735 FND_MSG_PUB.initialize;
1736 END IF;
1737
1738 -- Initialize API return status to success
1739 x_return_status := FND_API.G_RET_STS_SUCCESS;
1740
1741 -- API body
1742 Debug('INIT_TRX_VALIDATE(+)');
1743 IF nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL)>= 50 THEN
1744 --check trx number
1745 IF p_trx_rec.trx_number IS NULL THEN
1746 Set_Return_Status(x_orig_status => x_return_status,
1747 p_new_status => FND_API.G_RET_STS_ERROR);
1748 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1749 FND_MESSAGE.SET_NAME('FUN', 'FUN_TRX_NUM_NULL');
1750 FND_MSG_PUB.Add;
1751 END IF;
1752 END IF;
1753 --Bug No. 5307996. Check for valid Currency code.
1754 IF (default_currency_code is not null AND default_currency_code <> p_currency_code) THEN
1755 --Bug No. 6311049.
1756 Set_Return_Status(x_orig_status => x_return_status,
1757 p_new_status => FND_API.G_RET_STS_ERROR);
1758 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1759 FND_MESSAGE.SET_NAME('FUN', 'FUN_ADI_INVALID_CURRENCY');
1760 FND_MESSAGE.SET_TOKEN('CURRENCY', default_currency_code);
1761 FND_MSG_PUB.Add;
1762 END IF;
1763 END IF;
1764 --End of Bug No. 5307996.
1765 --7.2.1.2 Validate line number, recipient id, line amount, header amount is null
1766 IF p_trx_rec.recipient_id IS NULL
1767 OR p_trx_rec.to_le_id IS NULL OR
1768 NOT ((p_trx_rec.init_amount_dr IS NULL AND
1769 p_trx_rec.init_amount_cr IS NOT NULL AND
1770 p_trx_rec.init_amount_cr <> 0)
1771 OR
1772 (p_trx_rec.init_amount_cr IS NULL AND
1773 p_trx_rec.init_amount_dr IS NOT NULL AND
1774 p_trx_rec.init_amount_dr <> 0)) THEN
1775 Set_Return_Status(x_orig_status => x_return_status,
1776 p_new_status => FND_API.G_RET_STS_ERROR);
1777 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1778 IF(p_trx_rec.init_amount_dr=0) THEN
1779 FND_MESSAGE.SET_NAME('FUN', 'FUN_NON_ZERO_AMOUNT');
1780 FND_MSG_PUB.Add;
1781 ELSE
1782 FND_MESSAGE.SET_NAME('FUN', 'FUN_REQUIRED_FIELDS_INCOMPLETE');
1783 FND_MESSAGE.SET_TOKEN('TRX_NUMBER',p_trx_rec.trx_number);
1784 FND_MSG_PUB.Add;
1785 END IF;
1786 END IF;
1787 END IF;
1788
1789 --check initiator and recipeint are not same
1790 IF p_trx_rec.recipient_id=p_trx_rec.initiator_id THEN
1791 Set_Return_Status(x_orig_status => x_return_status,
1792 p_new_status => FND_API.G_RET_STS_ERROR);
1793 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1794 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_CHANGE_INITIATOR');
1795 FND_MSG_PUB.Add;
1796 END IF;
1797 END IF;
1798
1799 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1800 IF (p_trx_rec.recipient_instance = 'N' OR
1801 p_trx_rec.initiator_instance = 'N') THEN
1802 l_local := 'N';
1803 END IF;
1804 Is_Party_Valid(x_return_status => l_return_status,
1805 p_party_id => p_trx_rec.recipient_id,
1806 p_le_id => p_trx_rec.to_le_id,
1807 p_ledger_id => p_trx_rec.to_ledger_id,
1808 p_instance => p_trx_rec.recipient_instance,
1809 p_local => l_local,
1810 p_type => 'R',
1811 p_batch_date => p_gl_date,
1812 p_trx_number => p_trx_rec.trx_number);
1813 Set_Return_Status(x_orig_status => x_return_status,
1814 p_new_status => l_return_status);
1815 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1816 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1817 END IF;
1818 END IF;
1819 --Bug: 6998219
1820 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1821 validate_org_assignment(x_return_status => l_return_status,
1822 p_party_id => p_trx_rec.recipient_id);
1823 Set_Return_Status(x_orig_status => x_return_status,
1824 p_new_status => l_return_status);
1825 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1827 END IF;
1828 END IF;
1829 Is_Init_Trx_Amt_Valid(x_return_status => l_return_status,
1830 p_trx_amount_cr => p_trx_rec.init_amount_cr,
1831 p_trx_amount_dr => p_trx_rec.init_amount_dr,
1832 p_dist_lines_tbl => p_dist_lines_tbl,
1833 p_currency_code => p_currency_code,
1834 p_trx_date => p_gl_date,
1835 p_auto_proration_flag => p_trx_rec.automatic_proration_flag,
1836 p_trx_number => p_trx_rec.trx_number,
1837 p_exchg_rate_type => p_exchange_rate_type); --9590987
1838 Set_Return_Status(x_orig_status => x_return_status, p_new_status => l_return_status);
1839 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1841 END IF;
1842 END IF;
1843 IF nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL)>= FND_API.G_VALID_LEVEL_FULL THEN
1844
1845 Is_AR_Valid(x_return_status => l_return_status,
1846 p_initiator_id => p_trx_rec.initiator_id,
1847 p_invoicing_rule => p_trx_rec.invoicing_rule,
1848 p_recipient_id => p_trx_rec.recipient_id,
1849 p_to_le_id => p_trx_rec.to_le_id,
1850 p_trx_date => p_gl_date);
1851
1852 IF l_return_status like 'L' THEN -- 6145670
1853 FND_MESSAGE.SET_NAME('FUN', 'FND_INTER_TRX_GLDATE');
1854 FND_MSG_PUB.Add;
1855 Set_Return_Status(x_orig_status => x_return_status, p_new_status => FND_API.G_RET_STS_ERROR);
1856
1857 END IF; -- 6145670
1858
1859
1860
1861 Set_Return_Status(x_orig_status => x_return_status, p_new_status => l_return_status);
1862 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1863 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1864 END IF;
1865
1866 -- Validate distributions if level is full
1867 Is_Init_Trx_Dist_Amt_Valid(x_return_status => l_return_status,
1868 p_trx_amount_cr => p_trx_rec.init_amount_cr,
1869 p_trx_amount_dr => p_trx_rec.init_amount_dr,
1870 p_dist_lines_tbl => p_dist_lines_tbl,
1871 p_currency_code => p_currency_code,
1872 p_trx_date => p_gl_date,
1873 p_auto_proration_flag => p_trx_rec.automatic_proration_flag,
1874 p_trx_number => p_trx_rec.trx_number);
1875 Set_Return_Status(x_orig_status => x_return_status, p_new_status => l_return_status);
1876 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1877 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1878 END IF;
1879
1880 END IF;
1881
1882 Debug('INIT_TRX_VALIDATE(-)');
1883 -- End of API body.
1884 -- Standard call to get message count and if count is 1, get message info.
1885 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1886 p_data => x_msg_data);
1887 EXCEPTION
1888 WHEN FND_API.G_EXC_ERROR THEN
1889 x_return_status := FND_API.G_RET_STS_ERROR ;
1890 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1891 p_data => x_msg_data);
1892 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1894 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1895 p_data => x_msg_data);
1896 WHEN OTHERS THEN
1897 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1898 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1899 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1900 END IF;
1901 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1902 p_data => x_msg_data);
1903 END Init_Trx_Validate;
1904
1905 PROCEDURE Init_Dist_Validate
1906 ( p_api_version IN NUMBER,
1907 p_init_msg_list IN VARCHAR2 ,
1908 p_validation_level IN NUMBER ,
1909 p_le_id IN NUMBER,
1910 p_ledger_id IN NUMBER,
1911 x_return_status OUT NOCOPY VARCHAR2,
1912 x_msg_count OUT NOCOPY NUMBER,
1913 x_msg_data OUT NOCOPY VARCHAR2,
1914 p_init_dist_rec IN OUT NOCOPY INIT_DIST_REC_TYPE
1915 ) IS
1916 l_api_name CONSTANT VARCHAR2(30) := 'INIT_DIST_VALIDATE';
1917 l_api_version CONSTANT NUMBER := 1.0;
1918 BEGIN
1919 -- Standard call to check for call compatibility.
1920 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1921 p_api_version,
1922 l_api_name,
1923 G_PKG_NAME )
1924 THEN
1925 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1926 END IF;
1927
1928 -- Initialize message list if p_init_msg_list is set to TRUE.
1929 IF FND_API.to_Boolean( nvl(p_init_msg_list,FND_API.G_FALSE) ) THEN
1930 FND_MSG_PUB.initialize;
1931 END IF;
1932
1933 -- Initialize API return status to success
1934 x_return_status := FND_API.G_RET_STS_SUCCESS;
1935
1936 -- API body
1937 Debug('INIT_DIST_VALIDATE(+)');
1938 -- Bug 7012449. Commented the If condition so that the ccid is validated
1939 -- under all conditions. Also added the negative ccid check.
1940 --IF nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL)>= 50 THEN
1941
1942 IF p_init_dist_rec.ccid IS NULL OR p_init_dist_rec.ccid <= 0 THEN
1943 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1944 --Bug: 6618396
1945 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_INIT_DIST_AC_CCID');
1946 -- Problem: Double check with msg repository
1947 FND_MSG_PUB.Add;
1948 END IF;
1949 Set_Return_Status(x_orig_status => x_return_status,
1950 p_new_status => FND_API.G_RET_STS_ERROR);
1951
1952 ELSE
1953 Is_Ccid_Valid(x_return_status => x_return_status, p_ccid => p_init_dist_rec.ccid,
1954 p_le_id => p_le_id, p_ledger_id => p_ledger_id);
1955 END IF;
1956 IF (NOT ((p_init_dist_rec.amount_dr IS NULL OR
1957 p_init_dist_rec.amount_dr = 0 ) AND
1958 p_init_dist_rec.amount_cr IS NOT NULL AND
1959 p_init_dist_rec.amount_cr <> 0)
1960 AND
1961 NOT ((p_init_dist_rec.amount_cr IS NULL OR
1962 p_init_dist_rec.amount_cr = 0 ) AND
1963 p_init_dist_rec.amount_dr IS NOT NULL AND
1964 p_init_dist_rec.amount_dr <> 0))
1965 OR
1966 p_init_dist_rec.line_number IS NULL THEN
1967 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1968 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_DRCR_BDIST_LINE');
1969 FND_MSG_PUB.Add;
1970 END IF;
1971 Set_Return_Status(x_orig_status => x_return_status,
1972 p_new_status => FND_API.G_RET_STS_ERROR);
1973 END IF;
1974 --END IF;
1975 --IF nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL)>= FND_API.G_VALID_LEVEL_FULL THEN
1976
1977 IF p_init_dist_rec.ccid IS NULL OR p_init_dist_rec.ccid <= 0 THEN
1978 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1979 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INCOMPLETE_DIST_ACCTN');
1980 FND_MSG_PUB.Add;
1981 END IF;
1982 Set_Return_Status(x_orig_status => x_return_status,
1983 p_new_status => FND_API.G_RET_STS_ERROR);
1984 END IF;
1985 --END IF;
1986 Debug('INIT_DIST_VALIDATE(-)');
1987 -- End of API body.
1988 -- Standard call to get message count and if count is 1, get message info.
1989 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1990 p_data => x_msg_data);
1991
1992 EXCEPTION
1993 WHEN FND_API.G_EXC_ERROR THEN
1994 x_return_status := FND_API.G_RET_STS_ERROR ;
1995 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1996 p_data => x_msg_data);
1997 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1998 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1999 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2000 p_data => x_msg_data);
2001 WHEN OTHERS THEN
2002 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2003 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2004 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2005 END IF;
2006 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2007 p_data => x_msg_data);
2008
2009
2010 END Init_Dist_Validate;
2011
2012 PROCEDURE Init_IC_Dist_Validate
2013 ( p_api_version IN NUMBER,
2014 p_init_msg_list IN VARCHAR2 ,
2015 p_validation_level IN NUMBER ,
2016 p_le_id IN NUMBER,
2017 p_ledger_id IN NUMBER,
2018 x_return_status OUT NOCOPY VARCHAR2,
2019 x_msg_count OUT NOCOPY NUMBER,
2020 x_msg_data OUT NOCOPY VARCHAR2,
2021 p_dist_line_rec IN OUT NOCOPY DIST_LINE_REC_TYPE
2022 ) IS
2023 l_api_name CONSTANT VARCHAR2(30) := 'INIT_IC_DIST_VALIDATE';
2024 l_api_version CONSTANT NUMBER := 1.0;
2025 BEGIN
2026 -- Standard call to check for call compatibility.
2027 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2028 p_api_version,
2029 l_api_name ,
2030 G_PKG_NAME )
2031 THEN
2032 Print ('Debug Init_Dist_Val >>> Unexpected Error 1 ');
2033 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2034 END IF;
2035
2036 -- Initialize message list if p_init_msg_list is set to TRUE.
2037 IF FND_API.to_Boolean( nvl(p_init_msg_list,FND_API.G_FALSE) ) THEN
2038 FND_MSG_PUB.initialize;
2039 END IF;
2040
2041 Print ('Debug Init_Dist_Val >>> Message List Initiated. ');
2042 Print ('Debug Init_Dist_Val >>> Return status '|| FND_API.G_RET_STS_SUCCESS );
2043
2044 -- Initialize API return status to success
2045 x_return_status := FND_API.G_RET_STS_SUCCESS;
2046
2047 -- API body
2048 Debug('INIT_IC_DIST_VALIDATE(+)');
2049
2050 Print ('Debug Init_Dist_Val >>> API Body Started');
2051
2052 -- Bug 7012449. Commented the If clause such that the ccid is validated
2053 -- Under all conditions. Also added negative ccid check.
2054
2055 --IF nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL)>= 50 THEN
2056
2057 Print ('Debug Init_Dist_Val >>> In If 1' );
2058
2059 IF p_dist_line_rec.ccid IS NULL OR p_dist_line_rec.ccid <= 0 THEN
2060 Print ('Debug Init_Dist_Val >>> In If 2' );
2061
2062 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2063 Print ('Debug Init_Dist_Val >>> In If 3' );
2064 --Bug: 6618396
2065 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_INIT_DIST_AC_CCID');
2066 FND_MESSAGE.SET_TOKEN('TRX_NUMBER', p_dist_line_rec.trx_number);
2067 -- Problem: Double check with msg repository
2068 FND_MSG_PUB.Add;
2069
2070 Print ('Debug Init_Dist_Val >>> End of If 3' );
2071 END IF;
2072 Set_Return_Status(x_orig_status => x_return_status,
2073 p_new_status => FND_API.G_RET_STS_ERROR);
2074
2075 Print ('Debug Init_Dist_Val >>> End If 2' );
2076 ELSE
2077
2078 Print ('Debug Init_Dist_Val >>> CCID ' || p_dist_line_rec.ccid );
2079 Print ('Debug Init_Dist_Val >>> le_id ' ||p_le_id);
2080 Print ('Debug Init_Dist_VAl >> ledger_id ' || p_ledger_id);
2081
2082 Is_Ccid_Valid(x_return_status => x_return_status, p_ccid => p_dist_line_rec.ccid,
2083 p_le_id => p_le_id, p_ledger_id => p_ledger_id);
2084
2085 END IF;
2086
2087 Print ('Debug Init_Dist_Val >>> End If 3' );
2088
2089 IF NOT (p_dist_line_rec.amount_dr IS NULL AND
2090 p_dist_line_rec.amount_cr IS NOT NULL AND
2091 p_dist_line_rec.amount_cr <> 0)
2092 AND
2093 NOT (p_dist_line_rec.amount_cr IS NULL AND
2094 p_dist_line_rec.amount_dr IS NOT NULL AND
2095 p_dist_line_rec.amount_dr <> 0) THEN
2096
2097 Print ('Debug Init_Dist_Val >>> In If 21' );
2098
2099 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2100
2101 Print ('Debug Init_Dist_Val >>> In If 22' );
2102
2103 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_DRCR_DIST_LINE');
2104 FND_MESSAGE.SET_TOKEN('TRX_NUMBER', p_dist_line_rec.trx_number);
2105 FND_MSG_PUB.Add;
2106 END IF;
2107
2108 Print ('Debug Init_Dist_Val >>> End If 22' );
2109
2110 Set_Return_Status(x_orig_status => x_return_status,
2111 p_new_status => FND_API.G_RET_STS_ERROR);
2112 END IF;
2113
2114 Print ('Debug Init_Dist_Val >>> End If 21' );
2115 Print ('Debug Init_Dist_Val >>> Party Type ' || p_dist_line_rec.party_type );
2116 Print ('Debug Init_Dist_Val >>> FND_API.G_RET_STS_ERROR ' || FND_API.G_RET_STS_ERROR );
2117
2118 IF p_dist_line_rec.party_type <> 'I' and p_dist_line_rec.party_type <> 'R' THEN
2119 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2120 FND_MESSAGE.SET_NAME('FUN', 'Party type for distributions has not been set correctly');
2121 END IF;
2122 Set_Return_Status(x_orig_status => x_return_status,
2123 p_new_status => FND_API.G_RET_STS_ERROR);
2124 END IF;
2125 --END IF;
2126
2127 Print ('Debug Init_Dist_Val >>> End If');
2128
2129 --IF nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL)>= FND_API.G_VALID_LEVEL_FULL THEN
2130 IF p_dist_line_rec.ccid IS NULL OR p_dist_line_rec.ccid <= 0 THEN
2131 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2132 --Bug: 6618396
2133 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_INIT_DIST_AC_CCID');
2134 FND_MESSAGE.SET_TOKEN('TRX_NUMBER', p_dist_line_rec.trx_number);
2135 END IF;
2136
2137 Print ('Debug Init_Dist_Val >>> x_return_status' || x_return_status );
2138 Set_Return_Status(x_orig_status => x_return_status,
2139 p_new_status => FND_API.G_RET_STS_ERROR);
2140 END IF;
2141 --END IF;
2142 Debug('INIT_IC_DIST_VALIDATE(-)');
2143 -- End of API body.
2144 -- Standard call to get message count and if count is 1, get message info.
2145
2146 Print ('Debug Init_Dist_Val >>> x_msg_count' || x_msg_count );
2147 Print ('Debug Init_Dist_Val >>> x_msg_data' || x_msg_data );
2148 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2149 p_data => x_msg_data);
2150
2151
2152 EXCEPTION
2153 WHEN FND_API.G_EXC_ERROR THEN
2154 Print ('Debug Init_Dist_Val G_EXEC_ERROR >>> x_msg_count' || x_msg_count );
2155 Print ('Debug Init_Dist_Val G_EXEC_ERROR >>> x_msg_data' || x_msg_data );
2156
2157 x_return_status := FND_API.G_RET_STS_ERROR ;
2158 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2159 p_data => x_msg_data);
2160 Print ('Debug Init_Dist_Val G_EXEC_ERROR >>>END ' );
2161 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2162 Print ('Debug Init_Dist_Val UNEXPECTED ERROR >>> x_msg_count' || x_msg_count );
2163 Print ('Debug Init_Dist_Val UNEXPECTED ERROR >>> x_msg_data' || x_msg_data );
2164
2165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2166 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2167 p_data => x_msg_data);
2168 Print ('Debug Init_Dist_Val UNEXPECTED ERROR >>>END ' );
2169 WHEN OTHERS THEN
2170 Print ('Debug Init_Dist_Val OTHER ERROR >>> x_msg_count' || x_msg_count );
2171 Print ('Debug Init_Dist_Val OTHER ERROR >>> x_msg_data' || x_msg_data );
2172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2173 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2174 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2175 END IF;
2176 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2177 p_data => x_msg_data);
2178 Print ('Debug Init_Dist_Val OTHER ERROR >>>END ' );
2179
2180 END Init_IC_Dist_Validate;
2181
2182
2183
2184
2185
2186 PROCEDURE Is_AR_Valid
2187 ( x_return_status OUT NOCOPY VARCHAR2 ,
2188 p_initiator_id IN NUMBER,
2189 p_invoicing_rule IN VARCHAR2,
2190 p_recipient_id IN NUMBER,
2191 p_to_le_id IN NUMBER,
2192 p_trx_date IN DATE
2193 ) IS
2194 l_from_le_id NUMBER;
2195 l_from_le_party_id NUMBER; -- <bug 3450031>
2196 l_from_ou_id NUMBER;
2197 l_to_ou_id NUMBER;
2198 --l_success VARCHAR2(1);
2199 l_cust_acct_id NUMBER;
2200 l_bill_to_site_id NUMBER;
2201 l_bill_site_use_id NUMBER;
2202 l_count NUMBER;
2203 l_msg_data VARCHAR2(2000);
2204 l_success BOOLEAN;
2205 l_invoice_required VARCHAR2(1);
2206 initiator_name HZ_PARTIES.PARTY_NAME%TYPE;
2207 recipient_name HZ_PARTIES.PARTY_NAME%TYPE;
2208
2209 CURSOR ou_valid_csr IS
2210 SELECT count(*)
2211 FROM hr_operating_units ou
2212 WHERE organization_id = l_from_ou_id
2213 AND date_from <= p_trx_date
2214 AND NVL(date_to, p_trx_date) >= p_trx_date; -- <bug 3450031>
2215 BEGIN
2216 -- 7.2.1.7
2217 -- Initialize API return status to success
2218 x_return_status := FND_API.G_RET_STS_SUCCESS;
2219
2220 -- API body
2221 Debug('IS_AR_VALID(+)');
2222 l_from_le_party_id := Fun_Tca_Pkg.Get_LE_Id(p_initiator_id, p_trx_date);
2223
2224 IF l_from_le_party_id IS NOT NULL THEN -- 6145670
2225
2226 SELECT legal_entity_id
2227 INTO l_from_le_id
2228 FROM xle_firstparty_information_v
2229 WHERE party_id = l_from_le_party_id;
2230
2231 check_invoice_reqd_flag(p_init_party_id => p_initiator_id,
2232 p_init_le_id => l_from_le_id,
2233 p_reci_party_id => p_recipient_id,
2234 p_reci_le_id => p_to_le_id,
2235 p_ttyp_invoice_flag => p_invoicing_rule,
2236 x_invoice_required => l_invoice_required,
2237 x_return_status => x_return_status);
2238
2239 IF l_invoice_required = 'Y' THEN
2240
2241 l_from_ou_id := Fun_Tca_Pkg.Get_OU_Id(p_initiator_id, p_trx_date);
2242 IF l_from_ou_id IS NULL THEN
2243 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2244 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_OU');
2245 FND_MSG_PUB.Add;
2246 END IF;
2247 Set_Return_Status(x_orig_status => x_return_status,
2248 p_new_status => FND_API.G_RET_STS_ERROR);
2249 END IF;
2250 -- 7.2.1.5
2251 OPEN ou_valid_csr;
2252 FETCH ou_valid_csr INTO l_count;
2253 CLOSE ou_valid_csr;
2254 IF l_count < 1 THEN
2255 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2256 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_OU');
2257 FND_MSG_PUB.Add;
2258 END IF;
2259 Set_Return_Status(x_orig_status => x_return_status,
2260 p_new_status => FND_API.G_RET_STS_ERROR);
2261 END IF;
2262 l_to_ou_id := Fun_Tca_Pkg.Get_OU_Id(p_recipient_id, p_trx_date);
2263
2264 -- To get the customer association, the transacting LE is the
2265 -- recipient LE.
2266 l_success := Fun_Trading_Relation.Get_Customer('INTERCOMPANY', p_to_le_id,
2267 l_from_le_id, l_to_ou_id, l_from_ou_id,
2268 p_recipient_id, p_initiator_id,
2269 l_msg_data,
2270 l_cust_acct_id,
2271 l_bill_to_site_id,
2272 l_bill_site_use_id);
2273
2274 IF NOT l_success THEN
2275 -- Bug: 5291584
2276 SELECT party_name
2277 INTO initiator_name
2278 FROM hz_parties
2279 WHERE party_id=p_initiator_id;
2280
2281 SELECT party_name
2282 INTO recipient_name
2283 FROM hz_parties
2284 WHERE party_id=p_recipient_id;
2285
2286 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2287 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_CUSTOMER');
2288 FND_MESSAGE.SET_TOKEN('INITIATOR_NAME',initiator_name);
2289 FND_MESSAGE.SET_TOKEN('RECIPIENT_NAME',recipient_name);
2290 FND_MSG_PUB.Add;
2291 END IF;
2292 Set_Return_Status(x_orig_status => x_return_status,
2293 p_new_status => FND_API.G_RET_STS_ERROR);
2294 END IF;
2295
2296 END IF;
2297 Debug('IS_AR_VALID(-)');
2298 Else -- 6145670
2299 x_return_status:='L';
2300 END IF; -- 6145670
2301 -- End of API body.
2302 END;
2303
2304
2305
2306
2307
2308 PROCEDURE Create_Reverse_Batch
2309 ( p_api_version IN NUMBER,
2310 p_init_msg_list IN VARCHAR2 ,
2311 p_commit IN VARCHAR2 ,
2312 p_validation_level IN NUMBER ,
2313 p_batch_id IN NUMBER,
2314 p_reversed_batch_number IN VARCHAR2,
2315 p_reversal_method IN VARCHAR2, -- 'SWITCH' OR 'SIGN'
2316 p_reversed_batch_date IN DATE,
2317 p_reversed_gl_date IN DATE,
2318 p_reversed_description IN VARCHAR2,
2319 x_return_status OUT NOCOPY VARCHAR2,
2320 x_reversed_batch_id IN OUT NOCOPY NUMBER
2321 ) IS
2322 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_REVERSE_BATCH';
2323 l_api_version CONSTANT NUMBER := 1.0;
2324 l_return_status VARCHAR(1);
2325 l_boolean BOOLEAN;
2326 l_reversed_batch_number VARCHAR2(20);
2327 l_initiator_id NUMBER;
2328 l_reversed_batch_id NUMBER;
2329 l_control_date_tbl FUN_SEQ.CONTROL_DATE_TBL_TYPE;
2330 l_control_date_rec FUN_SEQ.CONTROL_DATE_REC_TYPE;
2331 l_seq_version_id NUMBER;
2332 l_assignment_id NUMBER;
2333 l_error_code VARCHAR2(1000);
2334 l_wf_event_key VARCHAR2(200);
2335 l_from_le_id NUMBER;
2336 l_trx_type_id NUMBER;
2337
2338 --Bug:9104801
2339 l_trx_total_tbl TRX_TOTAL_TBL_TYPE;
2340 CURSOR l_trx_total_cursor(p_reversed_batch_id NUMBER) IS
2341 SELECT trx_id,
2342 sum(init_amount_cr),
2343 sum(init_amount_dr)
2344 FROM fun_trx_headers
2345 WHERE batch_id = p_reversed_batch_id
2346 GROUP by trx_id;
2347
2348 BEGIN
2349 Debug('CREATE_REVERSE_BATCH(+)');
2350 --Bug: 6625360.
2351 -- Initialize message list if p_init_msg_list is set to TRUE.
2352 IF FND_API.to_Boolean( nvl(p_init_msg_list ,FND_API.G_FALSE)) THEN
2353 FND_MSG_PUB.initialize;
2354 END IF;
2355 -- Initialize API return status to success
2356 x_return_status := FND_API.G_RET_STS_SUCCESS;
2357
2358 --Bug: 15962294. Validate period status for GL date.
2359
2360 SELECT from_le_id, trx_type_id
2361 INTO l_from_le_id, l_trx_type_id
2362 FROM fun_trx_batches
2363 WHERE batch_id = p_batch_id;
2364
2365 Is_Init_GL_Date_Valid(x_return_status => l_return_status,
2366 p_from_le_id => l_from_le_id,
2367 p_gl_date => p_reversed_gl_date,
2368 p_trx_type_id => l_trx_type_id);
2369
2370 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
2371 x_return_status := l_return_status;
2372 RETURN;
2373 END IF;
2374
2375 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2376 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2377
2378 END IF;
2379
2380 -- Verify that no transactions have already been reversed,
2381 -- and the batch is not a reversed batch itself
2382 -- Verify the status of the batch
2383 --Bug: 6625360.
2384 /* SELECT count(initiator_id) INTO l_initiator_id
2385 FROM fun_trx_batches batches
2386 WHERE batch_id = p_batch_id
2387 AND original_batch_id IS NULL
2388 AND reversed_batch_id IS NULL
2389 AND status = 'COMPLETE'
2390 AND NOT EXISTS (SELECT 'Transaction already reversed'
2391 FROM fun_trx_headers hdrs
2392 WHERE hdrs.batch_id = p_batch_id
2393 AND (hdrs.original_trx_id IS NOT NULL
2394 OR
2395 hdrs.reversed_trx_id IS NOT NULL));*/
2396 SELECT count(initiator_id) INTO l_initiator_id
2397 FROM fun_trx_batches batches
2398 WHERE batch_id = p_batch_id
2399 AND original_batch_id IS NULL
2400 AND reversed_batch_id IS NULL
2401 AND(
2402 (status in ('COMPLETE')
2403 AND NOT EXISTS (SELECT 'Transaction already reversed'
2404 FROM fun_trx_headers hdrs
2405 WHERE hdrs.batch_id = p_batch_id
2406 AND (hdrs.original_trx_id IS NOT NULL
2407 OR
2408 hdrs.reversed_trx_id IS NOT NULL))
2409 ) OR
2410 (
2411 NOT EXISTS (SELECT 'Transaction not reversed'
2412 FROM fun_trx_headers hdrs
2413 WHERE hdrs.batch_id = p_batch_id
2414 AND (hdrs.original_trx_id IS NOT NULL
2415 OR hdrs.reversed_trx_id IS NOT NULL)
2416 AND hdrs.status in ('COMPLETE', 'APPROVED'))
2417 )
2418 );
2419 IF l_initiator_id < 1 THEN
2420 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2421 FND_MESSAGE.SET_NAME('FUN', 'FUN_REVERSED_BATCH'); -- Problem here: Whether a msg is needed
2422 FND_MSG_PUB.Add;
2423 END IF;
2424 Set_Return_Status(x_orig_status => x_return_status,
2425 p_new_status => FND_API.G_RET_STS_ERROR);
2426 RETURN;
2427 END IF;
2428 -- Check batch numbering type
2429 l_boolean := fun_system_options_pkg.is_manual_numbering;
2430
2431
2432 -- Changing for reverse batch as the logic for automatic sequence generation is handled in UI
2433 IF (p_reversed_batch_number IS NOT NULL) THEN
2434 l_boolean := true;
2435 END IF;
2436 -- End of logic
2437
2438 IF l_boolean THEN
2439 l_reversed_batch_number := p_reversed_batch_number;
2440 ELSE
2441
2442 l_control_date_rec.date_type := 'CREATION_DATE';
2443 l_control_date_rec.date_value := sysdate;
2444 l_control_date_tbl(0) := l_control_date_rec;
2445 FUN_SEQ.GET_SEQUENCE_NUMBER('INTERCOMPANY_BATCH_SOURCE',
2446 'LOCAL',
2447 435,
2448 'FUN_TRX_BATCHES',
2449 'CREATION',
2450 null,
2451 l_control_date_tbl,
2452 'N',
2453 l_seq_version_id,
2454 l_reversed_batch_number,
2455 l_assignment_id,
2456 l_error_code);
2457 END IF;
2458
2459 -- Bug: 16198634. Added Select Stmt
2460 SELECT initiator_id
2461 INTO l_initiator_id
2462 FROM fun_trx_batches
2463 WHERE batch_id = p_batch_id;
2464
2465 -- Check uniqueness of the batch_number provided
2466 Is_Batch_Num_Unique(l_return_status,l_reversed_batch_number,l_initiator_id);
2467
2468 -- Bug: 16198634. Added If condition and return on Error
2469 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
2470 x_return_status := l_return_status;
2471 RETURN;
2472 END IF;
2473
2474 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2476 END IF;
2477 -- Get the next sequence for fun_batch_id
2478 SELECT fun_trx_batches_s.nextval INTO l_reversed_batch_id FROM dual;
2479 -- Update original batch with reversed_batch_id
2480 UPDATE fun_trx_batches
2481 SET reversed_batch_id = l_reversed_batch_id
2482 WHERE batch_id = p_batch_id;
2483 -- Insert into batch with orig_batch_id
2484 INSERT INTO fun_trx_batches(BATCH_ID,
2485 BATCH_NUMBER,
2486 INITIATOR_ID,
2487 FROM_LE_ID,
2488 FROM_LEDGER_ID,
2489 CONTROL_TOTAL,
2490 RUNNING_TOTAL_CR,
2491 RUNNING_TOTAL_DR,
2492 CURRENCY_CODE,
2493 EXCHANGE_RATE_TYPE,
2494 STATUS,
2495 DESCRIPTION,
2496 NOTE,
2497 TRX_TYPE_ID,
2498 TRX_TYPE_CODE,
2499 GL_DATE,
2500 BATCH_DATE,
2501 REJECT_ALLOW_FLAG,
2502 ORIGINAL_BATCH_ID,
2503 REVERSED_BATCH_ID,
2504 FROM_RECURRING_BATCH_ID,
2505 INITIATOR_SOURCE,
2506 ATTRIBUTE1,
2507 ATTRIBUTE2,
2508 ATTRIBUTE3,
2509 ATTRIBUTE4,
2510 ATTRIBUTE5,
2511 ATTRIBUTE6,
2512 ATTRIBUTE7,
2513 ATTRIBUTE8,
2514 ATTRIBUTE9,
2515 ATTRIBUTE10,
2516 ATTRIBUTE11,
2517 ATTRIBUTE12,
2518 ATTRIBUTE13,
2519 ATTRIBUTE14,
2520 ATTRIBUTE15,
2521 ATTRIBUTE_CATEGORY,
2522 CREATED_BY,
2523 CREATION_DATE,
2524 LAST_UPDATED_BY,
2525 LAST_UPDATE_DATE,
2526 LAST_UPDATE_LOGIN,
2527 auto_proration_flag)
2528 SELECT l_reversed_batch_id,
2529 l_reversed_batch_number,
2530 INITIATOR_ID,
2531 FROM_LE_ID,
2532 FROM_LEDGER_ID,
2533 NULL,
2534 DECODE(p_reversal_method, 'CHANGE', DECODE(RUNNING_TOTAL_CR, NULL, NULL,(-1) * (RUNNING_TOTAL_CR)),
2535 RUNNING_TOTAL_DR),
2536 DECODE(p_reversal_method, 'CHANGE', DECODE(RUNNING_TOTAL_DR, NULL, NULL,(-1) * (RUNNING_TOTAL_DR)),
2537 RUNNING_TOTAL_CR),
2538 CURRENCY_CODE,
2539 EXCHANGE_RATE_TYPE,
2540 'SENT',
2541 p_reversed_description,
2542 NULL,
2543 TRX_TYPE_ID,
2544 TRX_TYPE_CODE,
2545 p_reversed_gl_date,
2546 p_reversed_batch_date,
2547 REJECT_ALLOW_FLAG,
2548 p_batch_id,
2549 NULL,
2550 NULL,
2551 INITIATOR_SOURCE,
2552 ATTRIBUTE1,
2553 ATTRIBUTE2,
2554 ATTRIBUTE3,
2555 ATTRIBUTE4,
2556 ATTRIBUTE5,
2557 ATTRIBUTE6,
2558 ATTRIBUTE7,
2559 ATTRIBUTE8,
2560 ATTRIBUTE9,
2561 ATTRIBUTE10,
2562 ATTRIBUTE11,
2563 ATTRIBUTE12,
2564 ATTRIBUTE13,
2565 ATTRIBUTE14,
2566 ATTRIBUTE15,
2567 ATTRIBUTE_CATEGORY,
2568 fnd_global.user_id,
2569 sysdate,
2570 fnd_global.user_id,
2571 sysdate,
2572 fnd_global.user_id,
2573 auto_proration_flag
2574 FROM fun_trx_batches
2575 WHERE batch_id = p_batch_id;
2576 -- Insert into transaction with status sent, ignore the rejected ones
2577 INSERT INTO fun_trx_headers(TRX_ID,
2578 TRX_NUMBER,
2579 INITIATOR_ID,
2580 RECIPIENT_ID,
2581 TO_LE_ID,
2582 TO_LEDGER_ID,
2583 BATCH_ID,
2584 STATUS,
2585 INIT_AMOUNT_CR,
2586 INIT_AMOUNT_DR,
2587 RECI_AMOUNT_CR,
2588 RECI_AMOUNT_DR,
2589 AR_INVOICE_NUMBER,
2590 INVOICE_FLAG,
2591 APPROVER_ID,
2592 APPROVAL_DATE,
2593 ORIGINAL_TRX_ID,
2594 REVERSED_TRX_ID,
2595 FROM_RECURRING_TRX_ID,
2596 INITIATOR_INSTANCE_FLAG,
2597 RECIPIENT_INSTANCE_FLAG,
2598 REJECT_REASON,
2599 DESCRIPTION,
2600 INIT_WF_KEY,
2601 RECI_WF_KEY,
2602 ATTRIBUTE1,
2603 ATTRIBUTE2,
2604 ATTRIBUTE3,
2605 ATTRIBUTE4,
2606 ATTRIBUTE5,
2607 ATTRIBUTE6,
2608 ATTRIBUTE7,
2609 ATTRIBUTE8,
2610 ATTRIBUTE9,
2611 ATTRIBUTE10,
2612 ATTRIBUTE11,
2613 ATTRIBUTE12,
2614 ATTRIBUTE13,
2615 ATTRIBUTE14,
2616 ATTRIBUTE15,
2617 ATTRIBUTE_CATEGORY,
2618 CREATED_BY,
2619 CREATION_DATE,
2620 LAST_UPDATED_BY,
2621 LAST_UPDATE_DATE,
2622 LAST_UPDATE_LOGIN)
2623 SELECT fun_trx_headers_s.nextval,
2624 TRX_NUMBER, -- Problem: what to use
2625 INITIATOR_ID,
2626 RECIPIENT_ID,
2627 TO_LE_ID,
2628 TO_LEDGER_ID,
2629 l_reversed_batch_id,
2630 'SENT',
2631 DECODE(p_reversal_method, 'CHANGE', DECODE(INIT_AMOUNT_CR, NULL, NULL, (-1) *(INIT_AMOUNT_CR)),
2632 INIT_AMOUNT_DR),
2633 DECODE(p_reversal_method, 'CHANGE', DECODE(INIT_AMOUNT_DR, NULL, NULL, (-1) *(INIT_AMOUNT_DR)),
2634 INIT_AMOUNT_CR),
2635 DECODE(p_reversal_method, 'CHANGE', DECODE(RECI_AMOUNT_CR, NULL, NULL, (-1) *(RECI_AMOUNT_CR)),
2636 RECI_AMOUNT_DR),
2637 DECODE(p_reversal_method, 'CHANGE', DECODE(RECI_AMOUNT_DR, NULL, NULL, (-1) *(RECI_AMOUNT_DR)),
2638 RECI_AMOUNT_CR),
2639 NULL,
2640 INVOICE_FLAG,
2641 NULL,
2642 NULL,
2643 TRX_ID,
2644 NULL,
2645 NULL,
2646 INITIATOR_INSTANCE_FLAG,
2647 RECIPIENT_INSTANCE_FLAG,
2648 NULL,
2649 p_reversed_description,
2650 NULL,
2651 NULL,
2652 ATTRIBUTE1,
2653 ATTRIBUTE2,
2654 ATTRIBUTE3,
2655 ATTRIBUTE4,
2656 ATTRIBUTE5,
2657 ATTRIBUTE6,
2658 ATTRIBUTE7,
2659 ATTRIBUTE8,
2660 ATTRIBUTE9,
2661 ATTRIBUTE10,
2662 ATTRIBUTE11,
2663 ATTRIBUTE12,
2664 ATTRIBUTE13,
2665 ATTRIBUTE14,
2666 ATTRIBUTE15,
2667 ATTRIBUTE_CATEGORY,
2668 fnd_global.user_id,
2669 sysdate,
2670 fnd_global.user_id,
2671 sysdate,
2672 fnd_global.user_id
2673 FROM fun_trx_headers
2674 WHERE batch_id = p_batch_id
2675 AND STATUS in ('COMPLETE', 'APPROVED'); -- Bug: 6625360. AND STATUS = 'COMPLETE';
2676
2677 -- Update reversed_trx_id with fun_trx_headers
2678 UPDATE fun_trx_headers hdrs1
2679 SET (reversed_trx_id) = (SELECT trx_id
2680 FROM fun_trx_headers hdrs2
2681 WHERE hdrs2.original_trx_id = hdrs1.trx_id)
2682 WHERE hdrs1.batch_id = p_batch_id;
2683
2684 -- Insert into init_dist
2685 INSERT INTO fun_batch_dists(BATCH_DIST_ID,
2686 LINE_NUMBER,
2687 BATCH_ID,
2688 CCID,
2689 AMOUNT_CR,
2690 AMOUNT_DR,
2691 DESCRIPTION,
2692 CREATED_BY,
2693 CREATION_DATE,
2694 LAST_UPDATED_BY,
2695 LAST_UPDATE_DATE,
2696 LAST_UPDATE_LOGIN)
2697 SELECT fun_batch_dist_s.nextval,
2698 LINE_NUMBER,
2699 l_reversed_batch_id,
2700 CCID,
2701 DECODE(p_reversal_method, 'CHANGE', (-1) *(nvl(AMOUNT_CR,0)),
2702 AMOUNT_DR),
2703 DECODE(p_reversal_method, 'CHANGE', (-1) *(nvl(AMOUNT_DR,0)),
2704 AMOUNT_CR),
2705 DESCRIPTION,
2706 fnd_global.user_id,
2707 sysdate,
2708 fnd_global.user_id,
2709 sysdate,
2710 fnd_global.user_id
2711 FROM fun_batch_dists dist
2712 WHERE dist.batch_id = p_batch_id;
2713
2714
2715 -- Insert into trx_lines
2716 INSERT INTO fun_trx_lines(LINE_ID,
2717 TRX_ID,
2718 LINE_NUMBER,
2719 LINE_TYPE_FLAG,
2720 INIT_AMOUNT_CR,
2721 INIT_AMOUNT_DR,
2722 RECI_AMOUNT_CR,
2723 RECI_AMOUNT_DR,
2724 DESCRIPTION,
2725 CREATED_BY,
2726 CREATION_DATE,
2727 LAST_UPDATED_BY,
2728 LAST_UPDATE_DATE,
2729 LAST_UPDATE_LOGIN)
2730 SELECT fun_trx_lines_s.nextval,
2731 headers.trx_id,
2732 LINE_NUMBER,
2733 LINE_TYPE_FLAG,
2734 DECODE(p_reversal_method, 'CHANGE',DECODE(lines.INIT_AMOUNT_CR, NULL,NULL, (-1) * (lines.INIT_AMOUNT_CR)),
2735 lines.INIT_AMOUNT_DR),
2736 DECODE(p_reversal_method, 'CHANGE', DECODE(lines.INIT_AMOUNT_DR, NULL, NULL, (-1) *(lines.INIT_AMOUNT_DR)),
2737 lines.INIT_AMOUNT_CR),
2738 DECODE(p_reversal_method, 'CHANGE', DECODE(lines.RECI_AMOUNT_CR, NULL,NULL, (-1) * (lines.RECI_AMOUNT_CR)),
2739 lines.RECI_AMOUNT_DR),
2740 DECODE(p_reversal_method, 'CHANGE', DECODE(lines.RECI_AMOUNT_DR, NULL, NULL, (-1) * (lines.RECI_AMOUNT_DR)),
2741 lines.RECI_AMOUNT_CR),
2742 lines.DESCRIPTION,
2743 fnd_global.user_id,
2744 sysdate,
2745 fnd_global.user_id,
2746 sysdate,
2747 fnd_global.user_id
2748 FROM fun_trx_headers headers, fun_trx_lines lines
2749 WHERE headers.batch_id = l_reversed_batch_id
2750 AND headers.original_trx_id = lines.trx_id;
2751
2752 -- Insert into dist_lines
2753 INSERT INTO fun_dist_lines(DIST_ID,
2754 LINE_ID,
2755 DIST_NUMBER,
2756 PARTY_ID,
2757 PARTY_TYPE_FLAG,
2758 DIST_TYPE_FLAG,
2759 BATCH_DIST_ID,
2760 AMOUNT_CR,
2761 AMOUNT_DR,
2762 CCID,
2763 DESCRIPTION,
2764 AUTO_GENERATE_FLAG,
2765 ATTRIBUTE1,
2766 ATTRIBUTE2,
2767 ATTRIBUTE3,
2768 ATTRIBUTE4,
2769 ATTRIBUTE5,
2770 ATTRIBUTE6,
2771 ATTRIBUTE7,
2772 ATTRIBUTE8,
2773 ATTRIBUTE9,
2774 ATTRIBUTE10,
2775 ATTRIBUTE11,
2776 ATTRIBUTE12,
2777 ATTRIBUTE13,
2778 ATTRIBUTE14,
2779 ATTRIBUTE15,
2780 ATTRIBUTE_CATEGORY,
2781 CREATED_BY,
2782 CREATION_DATE,
2783 LAST_UPDATED_BY,
2784 LAST_UPDATE_DATE,
2785 LAST_UPDATE_LOGIN,
2786 trx_id)
2787 SELECT fun_dist_lines_s.nextval,
2788 reversed_lines.LINE_ID,
2789 orig_dists.DIST_NUMBER,
2790 orig_dists.PARTY_ID,
2791 orig_dists.PARTY_TYPE_FLAG,
2792 orig_dists.DIST_TYPE_FLAG,
2793 NULL,
2794 DECODE(p_reversal_method, 'CHANGE', DECODE(orig_dists.AMOUNT_CR, NULL, NULL, (-1) *(orig_dists.AMOUNT_CR)),
2795 orig_dists.AMOUNT_DR),
2796 DECODE(p_reversal_method, 'CHANGE', DECODE(orig_dists.AMOUNT_DR, NULL, NULL, (-1) *(orig_dists.AMOUNT_DR)),
2797 orig_dists.AMOUNT_CR),
2798 orig_dists.CCID,
2799 orig_dists.DESCRIPTION,
2800 orig_dists.AUTO_GENERATE_FLAG,
2801 orig_dists.ATTRIBUTE1,
2802 orig_dists.ATTRIBUTE2,
2803 orig_dists.ATTRIBUTE3,
2804 orig_dists.ATTRIBUTE4,
2805 orig_dists.ATTRIBUTE5,
2806 orig_dists.ATTRIBUTE6,
2807 orig_dists.ATTRIBUTE7,
2808 orig_dists.ATTRIBUTE8,
2809 orig_dists.ATTRIBUTE9,
2810 orig_dists.ATTRIBUTE10,
2811 orig_dists.ATTRIBUTE11,
2812 orig_dists.ATTRIBUTE12,
2813 orig_dists.ATTRIBUTE13,
2814 orig_dists.ATTRIBUTE14,
2815 orig_dists.ATTRIBUTE15,
2816 orig_dists.ATTRIBUTE_CATEGORY,
2817 fnd_global.user_id,
2818 sysdate,
2819 fnd_global.user_id,
2820 sysdate,
2821 fnd_global.user_id,
2822 reversed_hdrs.trx_id
2823 FROM fun_trx_headers reversed_hdrs,
2824 fun_trx_lines reversed_lines,
2825 --fun_batch_dists reversed_b_dists,
2826 fun_trx_lines orig_lines,
2827 fun_dist_lines orig_dists
2828 WHERE reversed_hdrs.batch_id = l_reversed_batch_id
2829 AND reversed_hdrs.trx_id = reversed_lines.trx_id
2830 AND reversed_hdrs.original_trx_id = orig_lines.trx_id
2831 AND orig_lines.line_id = orig_dists.line_id
2832 AND orig_dists.dist_type_flag='L';
2833
2834 --Bug: 9104801
2835
2836 OPEN l_trx_total_cursor(l_reversed_batch_id);
2837 FETCH l_trx_total_cursor BULK COLLECT INTO l_trx_total_tbl;
2838 CLOSE l_trx_total_cursor;
2839
2840 IF l_trx_total_tbl.COUNT > 0 THEN
2841 FOR i IN l_trx_total_tbl.first..l_trx_total_tbl.last LOOP
2842 adjust_dist_amount(l_trx_total_tbl(i).trx_id,
2843 l_trx_total_tbl(i).total_amount_cr,
2844 l_trx_total_tbl(i).total_amount_dr);
2845 END LOOP;
2846 END IF;
2847
2848 -- Raise event to send
2849 l_wf_event_key := fun_wf_common.generate_event_key (l_reversed_batch_id, NULL);
2850 fun_wf_common.raise_wf_bus_event(batch_id => l_reversed_batch_id,
2851 event_key => l_wf_event_key);
2852 x_return_status := FND_API.G_RET_STS_SUCCESS;
2853 Debug('CREATE_REVERSE_BATCH(-)');
2854
2855 END;
2856
2857 PROCEDURE Create_Reverse_Trx
2858 ( p_api_version IN NUMBER,
2859 p_init_msg_list IN VARCHAR2 ,
2860 p_commit IN VARCHAR2 ,
2861 p_validation_level IN NUMBER ,
2862 p_trx_tbl_id IN number_type,
2863 p_reversed_batch_number IN VARCHAR2,
2864 p_reversal_method IN VARCHAR2, -- 'SWITCH' OR 'SIGN'
2865 p_reversed_batch_date IN DATE,
2866 p_reversed_gl_date IN DATE,
2867 p_reversed_description IN VARCHAR2,
2868 x_return_status OUT NOCOPY VARCHAR2,
2869 x_reversed_batch_id IN OUT NOCOPY NUMBER
2870 ) IS
2871 l_initiator_id NUMBER;
2872 l_boolean BOOLEAN;
2873 l_reversed_batch_number VARCHAR2(20);
2874 l_control_date_tbl FUN_SEQ.CONTROL_DATE_TBL_TYPE;
2875 l_control_date_rec FUN_SEQ.CONTROL_DATE_REC_TYPE;
2876 l_seq_version_id NUMBER;
2877 l_assignment_id NUMBER;
2878 l_error_code VARCHAR2(1000);
2879 l_return_status VARCHAR2(1);
2880 l_reversed_batch_id NUMBER;
2881 l_wf_event_key VARCHAR2(200);
2882 l_batch_cr NUMBER;
2883 l_batch_dr NUMBER;
2884 l_total_batch_cr NUMBER :=0;
2885 l_total_batch_dr NUMBER :=0;
2886 l_from_le_id NUMBER;
2887 l_trx_type_id NUMBER;
2888
2889 --Bug: 9104801
2890 l_trx_total_tbl TRX_TOTAL_TBL_TYPE;
2891 CURSOR l_trx_total_cursor(p_reversed_batch_id NUMBER) IS
2892 SELECT trx_id,
2893 sum(init_amount_cr),
2894 sum(init_amount_dr)
2895 FROM fun_trx_headers
2896 WHERE batch_id = p_reversed_batch_id
2897 GROUP by trx_id;
2898
2899 BEGIN
2900 Debug('CREATE_REVERSE_TRX(+)');
2901
2902 -- Initialize message list if p_init_msg_list is set to TRUE.
2903 IF FND_API.to_Boolean( nvl(p_init_msg_list ,FND_API.G_FALSE)) THEN
2904 FND_MSG_PUB.initialize;
2905 END IF;
2906 -- Initialize API return status to success
2907 x_return_status := FND_API.G_RET_STS_SUCCESS;
2908
2909 --Bug: 15962294. Validate period status for GL date.
2910 SELECT from_le_id, trx_type_id
2911 INTO l_from_le_id, l_trx_type_id
2912 FROM fun_trx_batches txb, fun_trx_headers txh
2913 WHERE txb.batch_id = txh.batch_id
2914 AND txh.trx_id = p_trx_tbl_id(1);
2915
2916 Is_Init_GL_Date_Valid(x_return_status => l_return_status,
2917 p_from_le_id => l_from_le_id,
2918 p_gl_date => p_reversed_gl_date,
2919 p_trx_type_id => l_trx_type_id);
2920
2921
2922 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
2923 x_return_status := l_return_status;
2924 RETURN;
2925 END IF;
2926
2927 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2928 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2929
2930 END IF;
2931 -- Problem: Still waiting for HLD to complete this section
2932 -- Verify the status of the batch
2933 -- Insert into batch
2934 -- Insert into transaction
2935 -- Insert into init_dist
2936 -- Insert into dist_lines
2937 SELECT initiator_id INTO l_initiator_id
2938 FROM fun_trx_headers headers
2939 WHERE headers.trx_id = p_trx_tbl_id(1)
2940 AND headers.reversed_trx_id IS NULL
2941 AND headers.original_trx_id IS NULL
2942 AND headers.status in ('COMPLETE', 'APPROVED');--Bug: 6625360. AND headers.status = 'COMPLETE';
2943 IF l_initiator_id IS NULL THEN
2944 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2945 FND_MESSAGE.SET_NAME('FUN', 'Trx error'); -- Problem here: Whether a msg is needed
2946 FND_MSG_PUB.Add;
2947 END IF;
2948 Set_Return_Status(x_orig_status => x_return_status,
2949 p_new_status => FND_API.G_RET_STS_ERROR);
2950 RETURN;
2951 END IF;
2952 -- Check batch numbering type
2953 l_boolean := fun_system_options_pkg.is_manual_numbering;
2954
2955 -- Added code as when reversing batch number is generated in UI
2956 IF (p_reversed_batch_number IS NOT NULL) THEN
2957 l_boolean := true;
2958 END IF;
2959 -- End of extra code
2960
2961 IF l_boolean THEN
2962 l_reversed_batch_number := p_reversed_batch_number;
2963 ELSE
2964
2965 l_control_date_rec.date_type := 'CREATION_DATE';
2966 l_control_date_rec.date_value := sysdate;
2967 l_control_date_tbl(0) := l_control_date_rec;
2968 FUN_SEQ.GET_SEQUENCE_NUMBER('INTERCOMPANY_BATCH_SOURCE',
2969 'LOCAL',
2970 435,
2971 'FUN_TRX_BATCHES',
2972 'CREATION',
2973 null,
2974 l_control_date_tbl,
2975 'N',
2976 l_seq_version_id,
2977 l_reversed_batch_number,
2978 l_assignment_id,
2979 l_error_code);
2980 END IF;
2981 -- Check uniqueness of the batch_number provided
2982 Is_Batch_Num_Unique(l_return_status,l_reversed_batch_number,l_initiator_id);
2983 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2985 END IF;
2986 -- Get the next sequence for fun_batch_id
2987 SELECT fun_trx_batches_s.nextval INTO l_reversed_batch_id FROM dual;
2988
2989 -- Loop the trx_id table to get sum of debits and credits for txns to be reversed
2990 FOR i IN 1..p_trx_tbl_id.COUNT LOOP
2991
2992 SELECT nvl(h.INIT_AMOUNT_CR,0), nvl(h.INIT_AMOUNT_DR,0)
2993 INTO l_batch_cr, l_batch_dr
2994 FROM fun_trx_headers h
2995 WHERE h.trx_id=p_trx_tbl_id(i);
2996
2997 l_total_batch_cr:=l_total_batch_cr + l_batch_cr ;
2998 l_total_batch_dr:=l_total_batch_dr + l_batch_dr ;
2999
3000 END LOOP;
3001 -- end of loop to get sum of debits and credits for txns to be reversed
3002
3003 -- Insert into batch with orig_batch_id
3004 INSERT INTO fun_trx_batches(BATCH_ID,
3005 BATCH_NUMBER,
3006 INITIATOR_ID,
3007 FROM_LE_ID,
3008 FROM_LEDGER_ID,
3009 CONTROL_TOTAL,
3010 RUNNING_TOTAL_CR,
3011 RUNNING_TOTAL_DR,
3012 CURRENCY_CODE,
3013 EXCHANGE_RATE_TYPE,
3014 STATUS,
3015 DESCRIPTION,
3016 NOTE,
3017 TRX_TYPE_ID,
3018 TRX_TYPE_CODE,
3019 GL_DATE,
3020 BATCH_DATE,
3021 REJECT_ALLOW_FLAG,
3022 ORIGINAL_BATCH_ID,
3023 REVERSED_BATCH_ID,
3024 FROM_RECURRING_BATCH_ID,
3025 INITIATOR_SOURCE,
3026 ATTRIBUTE1,
3027 ATTRIBUTE2,
3028 ATTRIBUTE3,
3029 ATTRIBUTE4,
3030 ATTRIBUTE5,
3031 ATTRIBUTE6,
3032 ATTRIBUTE7,
3033 ATTRIBUTE8,
3034 ATTRIBUTE9,
3035 ATTRIBUTE10,
3036 ATTRIBUTE11,
3037 ATTRIBUTE12,
3038 ATTRIBUTE13,
3039 ATTRIBUTE14,
3040 ATTRIBUTE15,
3041 ATTRIBUTE_CATEGORY,
3042 CREATED_BY,
3043 CREATION_DATE,
3044 LAST_UPDATED_BY,
3045 LAST_UPDATE_DATE,
3046 LAST_UPDATE_LOGIN,
3047 auto_proration_flag)
3048 SELECT l_reversed_batch_id,
3049 l_reversed_batch_number,
3050 batches.INITIATOR_ID,
3051 batches.FROM_LE_ID,
3052 batches.FROM_LEDGER_ID,
3053 NULL,
3054 DECODE(p_reversal_method, 'CHANGE', (-1) *(l_total_batch_cr),
3055 l_total_batch_dr),
3056 DECODE(p_reversal_method, 'CHANGE', (-1) *(l_total_batch_dr),
3057 l_total_batch_cr),
3058 batches.CURRENCY_CODE,
3059 batches.EXCHANGE_RATE_TYPE,
3060 'SENT',
3061 p_reversed_description,
3062 NULL,
3063 batches.TRX_TYPE_ID,
3064 batches.TRX_TYPE_CODE,
3065 p_reversed_gl_date,
3066 p_reversed_batch_date,
3067 batches.REJECT_ALLOW_FLAG,
3068 batches.batch_id,
3069 NULL,
3070 NULL,
3071 batches.INITIATOR_SOURCE,
3072 batches.ATTRIBUTE1,
3073 batches.ATTRIBUTE2,
3074 batches.ATTRIBUTE3,
3075 batches.ATTRIBUTE4,
3076 batches.ATTRIBUTE5,
3077 batches.ATTRIBUTE6,
3078 batches.ATTRIBUTE7,
3079 batches.ATTRIBUTE8,
3080 batches.ATTRIBUTE9,
3081 batches.ATTRIBUTE10,
3082 batches.ATTRIBUTE11,
3083 batches.ATTRIBUTE12,
3084 batches.ATTRIBUTE13,
3085 batches.ATTRIBUTE14,
3086 batches.ATTRIBUTE15,
3087 batches.ATTRIBUTE_CATEGORY,
3088 fnd_global.user_id,
3089 sysdate,
3090 fnd_global.user_id,
3091 sysdate,
3092 fnd_global.user_id,
3093 batches.auto_proration_flag
3094 FROM fun_trx_batches batches, fun_trx_headers headers
3095 WHERE batches.batch_id = headers.batch_id
3096 AND headers.trx_id = p_trx_tbl_id(1);
3097
3098 -- Loop the trx_id table and insert reversed txns
3099 FOR i IN 1..p_trx_tbl_id.COUNT LOOP
3100
3101 -- Insert into transaction with status sent
3102 INSERT INTO fun_trx_headers(TRX_ID,
3103 TRX_NUMBER,
3104 INITIATOR_ID,
3105 RECIPIENT_ID,
3106 TO_LE_ID,
3107 TO_LEDGER_ID,
3108 BATCH_ID,
3109 STATUS,
3110 INIT_AMOUNT_CR,
3111 INIT_AMOUNT_DR,
3112 RECI_AMOUNT_CR,
3113 RECI_AMOUNT_DR,
3114 AR_INVOICE_NUMBER,
3115 INVOICE_FLAG,
3116 APPROVER_ID,
3117 APPROVAL_DATE,
3118 ORIGINAL_TRX_ID,
3119 REVERSED_TRX_ID,
3120 FROM_RECURRING_TRX_ID,
3121 INITIATOR_INSTANCE_FLAG,
3122 RECIPIENT_INSTANCE_FLAG,
3123 REJECT_REASON,
3124 DESCRIPTION,
3125 INIT_WF_KEY,
3126 RECI_WF_KEY,
3127 ATTRIBUTE1,
3128 ATTRIBUTE2,
3129 ATTRIBUTE3,
3130 ATTRIBUTE4,
3131 ATTRIBUTE5,
3132 ATTRIBUTE6,
3133 ATTRIBUTE7,
3134 ATTRIBUTE8,
3135 ATTRIBUTE9,
3136 ATTRIBUTE10,
3137 ATTRIBUTE11,
3138 ATTRIBUTE12,
3139 ATTRIBUTE13,
3140 ATTRIBUTE14,
3141 ATTRIBUTE15,
3142 ATTRIBUTE_CATEGORY,
3143 CREATED_BY,
3144 CREATION_DATE,
3145 LAST_UPDATED_BY,
3146 LAST_UPDATE_DATE,
3147 LAST_UPDATE_LOGIN)
3148 SELECT fun_trx_headers_s.nextval,
3149 TRX_NUMBER, -- Problem: what to use
3150 INITIATOR_ID,
3151 RECIPIENT_ID,
3152 TO_LE_ID,
3153 TO_LEDGER_ID,
3154 l_reversed_batch_id,
3155 'SENT',
3156 DECODE(p_reversal_method, 'CHANGE', DECODE(INIT_AMOUNT_CR, NULL, NULL, (-1) *(INIT_AMOUNT_CR)),
3157 INIT_AMOUNT_DR),
3158 DECODE(p_reversal_method, 'CHANGE', DECODE(INIT_AMOUNT_DR, NULL, NULL, (-1) *(INIT_AMOUNT_DR)),
3159 INIT_AMOUNT_CR),
3160 DECODE(p_reversal_method, 'CHANGE', DECODE(RECI_AMOUNT_CR, NULL, NULL, (-1) *(RECI_AMOUNT_CR)),
3161 RECI_AMOUNT_DR),
3162 DECODE(p_reversal_method, 'CHANGE', DECODE(RECI_AMOUNT_DR, NULL, NULL, (-1) *(RECI_AMOUNT_DR)),
3163 RECI_AMOUNT_CR),
3164 NULL,
3165 INVOICE_FLAG,
3166 NULL,
3167 NULL,
3168 TRX_ID,
3169 NULL,
3170 NULL,
3171 INITIATOR_INSTANCE_FLAG,
3172 RECIPIENT_INSTANCE_FLAG,
3173 NULL,
3174 p_reversed_description,
3175 NULL,
3176 NULL,
3177 ATTRIBUTE1,
3178 ATTRIBUTE2,
3179 ATTRIBUTE3,
3180 ATTRIBUTE4,
3181 ATTRIBUTE5,
3182 ATTRIBUTE6,
3183 ATTRIBUTE7,
3184 ATTRIBUTE8,
3185 ATTRIBUTE9,
3186 ATTRIBUTE10,
3187 ATTRIBUTE11,
3188 ATTRIBUTE12,
3189 ATTRIBUTE13,
3190 ATTRIBUTE14,
3191 ATTRIBUTE15,
3192 ATTRIBUTE_CATEGORY,
3193 fnd_global.user_id,
3194 sysdate,
3195 fnd_global.user_id,
3196 sysdate,
3197 fnd_global.user_id
3198 FROM fun_trx_headers
3199 WHERE trx_id = p_trx_tbl_id(i)
3200 AND STATUS in ('COMPLETE', 'APPROVED'); --Bug: 6625360. AND STATUS = 'COMPLETE';
3201 -- Update reversed_trx_id with fun_trx_headers
3202 UPDATE fun_trx_headers hdrs1
3203 SET (reversed_trx_id) = (SELECT trx_id
3204 FROM fun_trx_headers hdrs2
3205 WHERE hdrs2.original_trx_id = hdrs1.trx_id)
3206 WHERE hdrs1.trx_id = p_trx_tbl_id(i);
3207
3208 END LOOP;
3209 --End loop; which is looping trx_id table and inserting reversed txns
3210
3211 UPDATE fun_trx_batches
3212 SET RUNNING_TOTAL_CR=(Select SUM(nvl(INIT_AMOUNT_CR,0))
3213 from fun_trx_headers
3214 where batch_id=l_reversed_batch_id
3215 ),
3216 RUNNING_TOTAL_DR=(Select SUM(nvl(INIT_AMOUNT_DR,0))
3217 from fun_trx_headers
3218 where batch_id=l_reversed_batch_id
3219 )
3220 where batch_id=l_reversed_batch_id;
3221
3222 -- Insert into trx_lines.
3223 INSERT INTO fun_trx_lines(LINE_ID,
3224 TRX_ID,
3225 LINE_NUMBER,
3226 LINE_TYPE_FLAG,
3227 INIT_AMOUNT_CR,
3228 INIT_AMOUNT_DR,
3229 RECI_AMOUNT_CR,
3230 RECI_AMOUNT_DR,
3231 DESCRIPTION,
3232 CREATED_BY,
3233 CREATION_DATE,
3234 LAST_UPDATED_BY,
3235 LAST_UPDATE_DATE,
3236 LAST_UPDATE_LOGIN)
3237 SELECT fun_trx_lines_s.nextval,
3238 headers.trx_id,
3239 LINE_NUMBER,
3240 LINE_TYPE_FLAG,
3241 DECODE(p_reversal_method, 'CHANGE', DECODE(lines.INIT_AMOUNT_CR, NULL, NULL, (-1) * (lines.INIT_AMOUNT_CR)),
3242 lines.INIT_AMOUNT_DR),
3243 DECODE(p_reversal_method, 'CHANGE', DECODE(lines.INIT_AMOUNT_DR, NULL, NULL, (-1) * (lines.INIT_AMOUNT_DR)),
3244 lines.INIT_AMOUNT_CR),
3245 DECODE(p_reversal_method, 'CHANGE', DECODE(lines.RECI_AMOUNT_CR, NULL, NULL, (-1) * (lines.RECI_AMOUNT_CR)),
3246 lines.RECI_AMOUNT_DR),
3247 DECODE(p_reversal_method, 'CHANGE', DECODE(lines.RECI_AMOUNT_DR, NULL, NULL, (-1) * (lines.RECI_AMOUNT_DR)),
3248 lines.RECI_AMOUNT_CR),
3249
3250 lines.DESCRIPTION,
3251 fnd_global.user_id,
3252 sysdate,
3253 fnd_global.user_id,
3254 sysdate,
3255 fnd_global.user_id
3256 FROM fun_trx_headers headers, fun_trx_lines lines
3257 WHERE headers.batch_id = l_reversed_batch_id
3258 AND headers.original_trx_id = lines.trx_id;
3259
3260 -- Insert into dist_lines
3261 INSERT INTO fun_dist_lines(DIST_ID,
3262 LINE_ID,
3263 DIST_NUMBER,
3264 PARTY_ID,
3265 PARTY_TYPE_FLAG,
3266 DIST_TYPE_FLAG,
3267 BATCH_DIST_ID,
3268 AMOUNT_CR,
3269 AMOUNT_DR,
3270 CCID,
3271 DESCRIPTION,
3272 AUTO_GENERATE_FLAG,
3273 ATTRIBUTE1,
3274 ATTRIBUTE2,
3275 ATTRIBUTE3,
3276 ATTRIBUTE4,
3277 ATTRIBUTE5,
3278 ATTRIBUTE6,
3279 ATTRIBUTE7,
3280 ATTRIBUTE8,
3281 ATTRIBUTE9,
3282 ATTRIBUTE10,
3283 ATTRIBUTE11,
3284 ATTRIBUTE12,
3285 ATTRIBUTE13,
3286 ATTRIBUTE14,
3287 ATTRIBUTE15,
3288 ATTRIBUTE_CATEGORY,
3289 CREATED_BY,
3290 CREATION_DATE,
3291 LAST_UPDATED_BY,
3292 LAST_UPDATE_DATE,
3293 LAST_UPDATE_LOGIN,
3294 trx_id)
3295 SELECT fun_dist_lines_s.nextval,
3296 reversed_lines.LINE_ID,
3297 orig_dists.DIST_NUMBER,
3298 orig_dists.PARTY_ID,
3299 orig_dists.PARTY_TYPE_FLAG,
3300 orig_dists.DIST_TYPE_FLAG,
3301 NULL,
3302 DECODE(p_reversal_method, 'CHANGE', DECODE(orig_dists.AMOUNT_CR, NULL, NULL, (-1) *(orig_dists.AMOUNT_CR)),
3303 orig_dists.AMOUNT_DR),
3304 DECODE(p_reversal_method, 'CHANGE', DECODE(orig_dists.AMOUNT_DR, NULL, NULL, (-1) *(orig_dists.AMOUNT_DR)),
3305 orig_dists.AMOUNT_CR),
3306 orig_dists.CCID,
3307 orig_dists.DESCRIPTION,
3308 orig_dists.AUTO_GENERATE_FLAG,
3309 orig_dists.ATTRIBUTE1,
3310 orig_dists.ATTRIBUTE2,
3311 orig_dists.ATTRIBUTE3,
3312 orig_dists.ATTRIBUTE4,
3313 orig_dists.ATTRIBUTE5,
3314 orig_dists.ATTRIBUTE6,
3315 orig_dists.ATTRIBUTE7,
3316 orig_dists.ATTRIBUTE8,
3317 orig_dists.ATTRIBUTE9,
3318 orig_dists.ATTRIBUTE10,
3319 orig_dists.ATTRIBUTE11,
3320 orig_dists.ATTRIBUTE12,
3321 orig_dists.ATTRIBUTE13,
3322 orig_dists.ATTRIBUTE14,
3323 orig_dists.ATTRIBUTE15,
3324 orig_dists.ATTRIBUTE_CATEGORY,
3325 fnd_global.user_id,
3326 sysdate,
3327 fnd_global.user_id,
3328 sysdate,
3329 fnd_global.user_id,
3330 reversed_hdrs.trx_id
3331 FROM fun_trx_headers reversed_hdrs,
3332 fun_trx_lines reversed_lines,
3333 --fun_batch_dists reversed_b_dists,
3334 fun_trx_lines orig_lines,
3335 fun_dist_lines orig_dists
3336 WHERE reversed_hdrs.batch_id = l_reversed_batch_id
3337 AND reversed_hdrs.trx_id = reversed_lines.trx_id
3338 AND reversed_hdrs.original_trx_id = orig_lines.trx_id
3339 AND orig_lines.line_id = orig_dists.line_id
3340 AND orig_dists.dist_type_flag='L';
3341
3342 --Bug: 9104801
3343
3344 OPEN l_trx_total_cursor(l_reversed_batch_id);
3345 FETCH l_trx_total_cursor BULK COLLECT INTO l_trx_total_tbl;
3346 CLOSE l_trx_total_cursor;
3347
3348 IF l_trx_total_tbl.COUNT > 0 THEN
3349 FOR i IN l_trx_total_tbl.first..l_trx_total_tbl.last LOOP
3350 adjust_dist_amount(l_trx_total_tbl(i).trx_id,
3351 l_trx_total_tbl(i).total_amount_cr,
3352 l_trx_total_tbl(i).total_amount_dr);
3353 END LOOP;
3354 END IF;
3355
3356 -- Raise event to send
3357 l_wf_event_key := fun_wf_common.generate_event_key (l_reversed_batch_id, NULL);
3358 fun_wf_common.raise_wf_bus_event(batch_id => l_reversed_batch_id,
3359 event_key => l_wf_event_key);
3360 x_return_status := FND_API.G_RET_STS_SUCCESS;
3361 Debug('CREATE_REVERSE_TRX(-)');
3362 END;
3363
3364 PROCEDURE Update_Trx_Status
3365 ( p_api_version IN NUMBER,
3366 p_init_msg_list IN VARCHAR2 ,
3367 p_commit IN VARCHAR2,
3368 p_validation_level IN NUMBER ,
3369 x_return_status OUT NOCOPY VARCHAR2,
3370 x_msg_count OUT NOCOPY NUMBER,
3371 x_msg_data OUT NOCOPY VARCHAR2,
3372 p_trx_id IN NUMBER,
3373 p_update_status_to IN VARCHAR2
3374 ) IS
3375 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRX_STATUS';
3376 l_api_version CONSTANT NUMBER := 1.0;
3377 l_status VARCHAR2(15);
3378 l_batch_id NUMBER;
3379 l_count NUMBER;
3380 CURSOR trx_status_csr IS
3381 SELECT status
3382 FROM fun_trx_headers
3383 WHERE trx_id = p_trx_id FOR UPDATE;
3384 BEGIN
3385 -- Standard Start of API savepoint
3386 SAVEPOINT Update_Trx_Status;
3387
3388 -- Standard call to check for call compatibility.
3389 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3390 p_api_version,
3391 l_api_name,
3392 G_PKG_NAME )
3393 THEN
3394 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3395 END IF;
3396
3397 -- Initialize message list if p_init_msg_list is set to TRUE.
3398 IF FND_API.to_Boolean( nvl(p_init_msg_list ,FND_API.G_FALSE)) THEN
3399 FND_MSG_PUB.initialize;
3400 END IF;
3401
3402 -- Initialize API return status to success
3403 x_return_status := FND_API.G_RET_STS_SUCCESS;
3404
3405
3406 -- API body
3407 Debug('UPDATE_TRX_STATUS(+)');
3408 OPEN trx_status_csr;
3409 FETCH trx_status_csr INTO l_status;
3410 CLOSE trx_status_csr;
3411 -- This is for resolving issues of initiator and recipient updating
3412 -- status the same time
3413 IF (l_status = p_update_status_to OR
3414 (p_update_status_to = 'RECEIVED' AND l_status <> 'SENT')) THEN
3415 Debug('CONSISTENT STATUS');
3416 ELSIF (nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL)<> FND_API.G_VALID_LEVEL_FULL) OR
3417 (l_status = 'NEW' AND p_update_status_to = 'SENT') OR
3418 (l_status = 'SENT' AND p_update_status_to = 'ERROR') OR
3419 (l_status = 'RECEIVED' AND p_update_status_to = 'ERROR') OR
3420 (l_status = 'ERROR' AND p_update_status_to = 'SENT') OR
3421 (l_status = 'ERROR' AND p_update_status_to = 'DELETED') OR
3422 (l_status = 'SENT' AND p_update_status_to = 'RECEIVED') OR
3423 (l_status = 'SENT' AND p_update_status_to = 'APPROVED') OR
3424 (l_status = 'SENT' AND p_update_status_to = 'REJECTED') OR
3425 (l_status = 'RECEIVED' AND p_update_status_to = 'APPROVED') OR
3426 (l_status = 'RECEIVED' AND p_update_status_to = 'REJECTED') OR
3427 (l_status = 'APPROVED' AND p_update_status_to = 'XFER_INI_GL') OR
3428 (l_status = 'APPROVED' AND p_update_status_to = 'XFER_RECI_GL') OR
3429 (l_status = 'APPROVED' AND p_update_status_to = 'XFER_AR') OR
3430 (l_status = 'APPROVED' AND p_update_status_to = 'COMPLETE') OR
3431 (l_status = 'XFER_AR' AND p_update_status_to = 'COMPLETE') OR
3432 (l_status = 'XFER_INI_GL' AND p_update_status_to = 'COMPLETE') OR
3433 (l_status = 'XFER_RECI_GL' AND p_update_status_to = 'COMPLETE')
3434 THEN
3435 UPDATE fun_trx_headers
3436 SET status = p_update_status_to,last_updated_by = fnd_global.user_id
3437 WHERE trx_id = p_trx_id;
3438
3439 SELECT batch_id
3440 INTO l_batch_id
3441 FROM fun_trx_headers
3442 WHERE trx_id = p_trx_id;
3443
3444 IF (p_update_status_to = 'ERROR')
3445 THEN
3446 -- Update batch to ERROR if all
3447 -- transactions are at status ERROR
3448 UPDATE fun_trx_batches
3449 SET status = 'ERROR'
3450 WHERE batch_id = l_batch_id
3451 AND NOT EXISTS (SELECT 'X'
3452 FROM fun_trx_headers
3453 WHERE batch_id = l_batch_id
3454 AND status <> 'ERROR');
3455
3456 ELSIF (p_update_status_to IN ('COMPLETE', 'REJECTED'))
3457 THEN
3458 -- Update batch to COMPLETE if all
3459 -- transactions are at status COMPLETE or REJECTTED
3460 UPDATE fun_trx_batches
3461 SET status = 'COMPLETE'
3462 WHERE batch_id = l_batch_id
3463 AND NOT EXISTS (SELECT 'X'
3464 FROM fun_trx_headers
3465 WHERE batch_id = l_batch_id
3466 AND status NOT IN ('COMPLETE','REJECTED'));
3467
3468
3469 END IF;
3470 -- Standard check of p_commit.
3471 IF FND_API.To_Boolean( nvl(p_commit,FND_API.G_FALSE) ) THEN
3472 COMMIT WORK;
3473 END IF;
3474 ELSE
3475 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3476 FND_MESSAGE.SET_NAME('FUN', 'Can not update status given');
3477 FND_MSG_PUB.Add;
3478 END IF;
3479 Set_Return_Status(x_orig_status => x_return_status,
3480 p_new_status => FND_API.G_RET_STS_ERROR);
3481 END IF;
3482
3483 -- Standard call to get message count and if count is 1, get message info.
3484 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3485 Debug('UPDATE_TRX_STATUS(-)');
3486 EXCEPTION
3487 WHEN FND_API.G_EXC_ERROR THEN
3488 ROLLBACK TO Update_Trx_Status;
3489 x_return_status := FND_API.G_RET_STS_ERROR ;
3490 FND_MSG_PUB.Count_And_Get
3491 ( p_count => x_msg_count,
3492 p_data => x_msg_data );
3493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3494 ROLLBACK TO Update_Trx_Status;
3495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3496 FND_MSG_PUB.Count_And_Get
3497 ( p_count => x_msg_count,
3498 p_data => x_msg_data);
3499 WHEN OTHERS THEN
3500 ROLLBACK TO Update_Trx_Status;
3501 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3502 IF FND_MSG_PUB.Check_Msg_Level
3503 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3504 THEN
3505 FND_MSG_PUB.Add_Exc_Msg
3506 ( G_FILE_NAME,
3507 G_PKG_NAME,
3508 l_api_name
3509 );
3510 END IF;
3511 FND_MSG_PUB.Count_And_Get
3512 ( p_count => x_msg_count,
3513 p_data => x_msg_data
3514 );
3515
3516
3517
3518 END;
3519
3520 PROCEDURE Is_Payable_Acct_Valid
3521 (
3522 x_return_status OUT NOCOPY VARCHAR2,
3523 p_ccid IN NUMBER
3524 ) IS
3525 BEGIN
3526 -- Problem. Need to get more info from PM
3527 return;
3528 END Is_Payable_Acct_Valid;
3529
3530 PROCEDURE Is_Reci_Acct_Valid
3531 (
3532 p_le_id IN NUMBER,
3533 p_ledger_id IN NUMBER,
3534 x_return_status OUT NOCOPY VARCHAR2,
3535 p_dist_lines_tbl IN OUT NOCOPY DIST_LINE_TBL_TYPE
3536 ) IS
3537 l_count NUMBER;
3538 l_return_status VARCHAR(1);
3539 BEGIN
3540 l_count := p_dist_lines_tbl.COUNT;
3541 --Bug: 6618396. Initialize API return status to success
3542 x_return_status := FND_API.G_RET_STS_SUCCESS;
3543 IF l_count >= 1 THEN
3544 FOR i IN 1..l_count LOOP
3545
3546 IF p_dist_lines_tbl(i).party_type = 'R' THEN
3547 IF p_dist_lines_tbl(i).ccid IS NULL THEN
3548 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3549 --Bug: 6618396
3550 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_RECI_DIST_AC_CCID');
3551 FND_MESSAGE.SET_TOKEN('TRX_NUMBER', p_dist_lines_tbl(i).trx_number);
3552 -- Problem: Double check with msg repository
3553 FND_MSG_PUB.Add;
3554 END IF;
3555 Set_Return_Status(x_orig_status => x_return_status,
3556 p_new_status => FND_API.G_RET_STS_ERROR);
3557 l_return_status := x_return_status;
3558 ELSE
3559 Is_Ccid_Valid(x_return_status => x_return_status,
3560 p_ccid => p_dist_lines_tbl(i).ccid,
3561 p_le_id => p_le_id,
3562 p_ledger_id => p_ledger_id);
3563 --Bug:13348803
3564 IF(x_return_status = FND_API.G_RET_STS_ERROR) THEN
3565 l_return_status := x_return_status;
3566 END IF;
3567 END IF;
3568 END IF;
3569 END LOOP;
3570 IF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
3571 x_return_status := l_return_status;
3572 END IF;
3573 END IF;
3574 END Is_Reci_Acct_Valid;
3575
3576
3577 PROCEDURE Is_Reci_Trx_Dist_Amt_Valid
3578 ( x_return_status OUT NOCOPY VARCHAR2,
3579 p_trx_amount_cr IN NUMBER,
3580 p_trx_amount_dr IN NUMBER,
3581 p_dist_lines_tbl IN OUT NOCOPY DIST_LINE_TBL_TYPE
3582 ) IS
3583 l_api_name CONSTANT VARCHAR2(30) := 'Is_Reci_Trx_Dist_Amt_Valid';
3584 l_boolean BOOLEAN;
3585 l_count NUMBER;
3586
3587 l_dist_cr_type NUMBER := 0;
3588 l_dist_dr_type NUMBER := 0;
3589 l_dist_pos_type NUMBER := 0;
3590 l_dist_neg_type NUMBER := 0;
3591 l_dist_total_cr NUMBER := 0;
3592 l_dist_total_dr NUMBER := 0;
3593 l_trx_number NUMBER;
3594
3595 BEGIN
3596 Debug('Is_Init_Trx_Dist_Amt_Valid(+)');
3597 x_return_status := FND_API.G_RET_STS_SUCCESS;
3598
3599 -- Perform the following validation only for manual mode
3600 l_count := p_dist_lines_tbl.COUNT;
3601 IF l_count > 0
3602 THEN
3603 FOR j IN 1..l_count LOOP
3604
3605 IF p_dist_lines_tbl(j).party_type = 'R' AND
3606 p_dist_lines_tbl(j).dist_type = 'L'
3607 THEN
3608
3609 --Bug: 16229717.
3610 IF p_dist_lines_tbl(j).amount_cr is null AND p_dist_lines_tbl(j).amount_dr is null THEN
3611
3612 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_DRCR_BDIST_LINE');
3613 FND_MSG_PUB.Add;
3614 Set_Return_Status(x_orig_status => x_return_status,
3615 p_new_status => FND_API.G_RET_STS_ERROR);
3616 Return;
3617
3618 END IF;
3619
3620 IF NVL(p_dist_lines_tbl(j).amount_cr, 0) <> 0 THEN
3621 l_dist_cr_type := 1;
3622 IF p_dist_lines_tbl(j).amount_cr > 0 THEN
3623 l_dist_pos_type := 1;
3624 ELSE
3625 l_dist_neg_type := 1;
3626 END IF;
3627 END IF;
3628 IF NVL(p_dist_lines_tbl(j).amount_dr, 0) <> 0 THEN
3629 l_dist_dr_type := 1;
3630 IF p_dist_lines_tbl(j).amount_dr > 0 THEN
3631 l_dist_pos_type := 1;
3632 ELSE
3633 l_dist_neg_type := 1;
3634 END IF;
3635 END IF;
3636 l_dist_total_cr := l_dist_total_cr + NVL(p_dist_lines_tbl(j).amount_cr, 0);
3637 l_dist_total_dr := l_dist_total_dr + NVL(p_dist_lines_tbl(j).amount_dr, 0);
3638 END IF;
3639 END LOOP;
3640 END IF;
3641
3642 IF (p_trx_amount_cr <> l_dist_total_dr OR
3643 p_trx_amount_dr <> l_dist_total_cr )
3644 THEN
3645 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3646 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INCOMLT_SUM_REC_DIST');
3647 FND_MSG_PUB.Add;
3648 END IF;
3649 Set_Return_Status(x_orig_status => x_return_status,
3650 p_new_status => FND_API.G_RET_STS_ERROR);
3651 END IF;
3652
3653 IF l_dist_cr_type = l_dist_dr_type
3654 THEN
3655 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3656 THEN
3657 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_DRCR_BDIST_LINE');
3658 FND_MSG_PUB.Add;
3659 END IF;
3660 Set_Return_Status(x_orig_status => x_return_status,
3661 p_new_status => FND_API.G_RET_STS_ERROR);
3662 END IF;
3663
3664
3665 EXCEPTION
3666 WHEN FND_API.G_EXC_ERROR THEN
3667 x_return_status := FND_API.G_RET_STS_ERROR ;
3668 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3670 WHEN OTHERS THEN
3671 FND_MSG_PUB.ADD;
3672 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3673 IF FND_MSG_PUB.Check_Msg_Level
3674 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3675 THEN
3676 FND_MSG_PUB.Add_Exc_Msg
3677 ( G_PKG_NAME ,
3678 l_api_name
3679 );
3680 END IF;
3681 Debug('Is_Reci_Trx_Dist_Amt_Valid(-)');
3682 END Is_Reci_Trx_Dist_Amt_Valid;
3683
3684 PROCEDURE Recipient_Validate
3685 ( p_api_version IN NUMBER,
3686 p_init_msg_list IN VARCHAR2 ,
3687 p_validation_level IN NUMBER ,
3688 x_return_status OUT NOCOPY VARCHAR2,
3689 x_msg_count OUT NOCOPY NUMBER,
3690 x_msg_data OUT NOCOPY VARCHAR2,
3691 p_batch_rec IN OUT NOCOPY BATCH_REC_TYPE,
3692 p_trx_rec IN OUT NOCOPY TRX_REC_TYPE,
3693 p_dist_lines_tbl IN OUT NOCOPY DIST_LINE_TBL_TYPE
3694 )
3695 IS
3696 l_api_name CONSTANT VARCHAR2(30) := 'RECIPIENT_VALIDATE';
3697 l_api_version CONSTANT NUMBER := 1.0;
3698 l_return_status VARCHAR(1);
3699 l_result VARCHAR(1);
3700 --l_batch_rec Batch_Rec_Type;
3701 --l_trx_tab Trx_Tbl_Type;
3702 --l_lines_tab Dist_Line_Tbl_Type;
3703 l_count NUMBER;
3704 l_index NUMBER := 0;
3705 i NUMBER := 1;
3706 BEGIN
3707 -- Standard call to check for call compatibility.
3708 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3709 p_api_version,
3710 l_api_name,
3711 G_PKG_NAME )
3712 THEN
3713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3714 END IF;
3715 -- Initialize message list if p_init_msg_list is set to TRUE.
3716 IF FND_API.to_Boolean( nvl(p_init_msg_list ,FND_API.G_FALSE)) THEN
3717 FND_MSG_PUB.initialize;
3718 END IF;
3719 -- Initialize API return status to success
3720 x_return_status := FND_API.G_RET_STS_SUCCESS;
3721
3722 -- API body
3723 Debug('RECIPIENT_VALIDATE(+)');
3724 IF nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL)>= 50 THEN
3725
3726 is_ap_valid(x_return_status => l_return_status,
3727 p_initiator_id => p_batch_rec.initiator_id,
3728 p_invoicing_rule => p_trx_rec.invoicing_rule,
3729 p_recipient_id => p_trx_rec.recipient_id,
3730 p_to_le_id => p_trx_rec.to_le_id,
3731 p_trx_date => p_batch_rec.batch_date);
3732 set_return_status(x_orig_status => x_return_status,
3733 p_new_status => l_return_status);
3734 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3735 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3736 END IF;
3737 l_count := p_dist_lines_tbl.COUNT;
3738 IF l_count >= 1 THEN
3739 FOR i IN 1..l_count LOOP
3740 -- There should be atleast one line for recipient distribution
3741 Debug('3');
3742 IF p_dist_lines_tbl(i).dist_type = 'L' THEN
3743 l_index := i;
3744 END IF;
3745 END LOOP;
3746 END IF;
3747 IF l_index = 0 THEN
3748 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3749 FND_MESSAGE.SET_NAME('FUN', 'FUN_TRX_ENTRY_TRX_UP_NODATA');
3750 FND_MSG_PUB.Add;
3751 END IF;
3752 -- Made changes for bug 9341446
3753 Set_Return_Status(x_orig_status => x_return_status,
3754 p_new_status => 'I');
3755 --End Changes bug 9341446
3756 ELSE
3757 Is_Payable_Acct_Valid(x_return_status => l_return_status,
3758 p_ccid => p_dist_lines_tbl(l_index).ccid);
3759 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3760 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3761 END IF;
3762
3763 Is_Reci_Acct_Valid(x_return_status => l_return_status,
3764 p_le_id => p_trx_rec.to_le_id,
3765 p_ledger_id => p_trx_rec.to_ledger_id,
3766 p_dist_lines_tbl => p_dist_lines_tbl);
3767
3768 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3769 RAISE FND_API.G_EXC_ERROR;
3770 END IF;
3771
3772 Is_Reci_Trx_Dist_Amt_Valid
3773 ( x_return_status => l_return_status,
3774 p_trx_amount_cr => p_trx_rec.reci_amount_cr,
3775 p_trx_amount_dr => p_trx_rec.reci_amount_dr,
3776 p_dist_lines_tbl => p_dist_lines_tbl);
3777
3778 --Added for bug 9341446
3779 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3780 Set_Return_Status(x_orig_status => x_return_status,
3781 p_new_status => 'I');
3782 END IF;
3783 --End Changes bug 9341446
3784
3785 Set_Return_Status(x_orig_status => x_return_status,
3786 p_new_status => l_return_status);
3787 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3789 END IF;
3790 END IF;
3791 END IF;
3792 -- Bug: 11722204
3793
3794 IF nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL)> 50 THEN
3795
3796 FUN_WF_AUTHORIZATION_LIMIT.HAS_VALID_EXCHANGE_RATE(
3797 x_return_status => l_return_status,
3798 p_trx_id => p_trx_rec.trx_id);
3799
3800 Set_Return_Status(x_orig_status => x_return_status,
3801 p_new_status => l_return_status);
3802 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3804 END IF;
3805 END IF;
3806
3807 Debug('RECIPIENT_VALIDATE(-)');
3808 -- End of API body.
3809 -- Standard call to get message count and if count is 1, get message info.
3810 FND_MSG_PUB.Count_And_Get
3811 ( p_count => x_msg_count,
3812 p_data => x_msg_data
3813 );
3814 EXCEPTION
3815 WHEN FND_API.G_EXC_ERROR THEN
3816 x_return_status := FND_API.G_RET_STS_ERROR ;
3817 FND_MSG_PUB.Count_And_Get
3818 ( p_count => x_msg_count,
3819 p_data => x_msg_data
3820 );
3821 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3822 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3823 FND_MSG_PUB.Count_And_Get
3824 ( p_count => x_msg_count,
3825 p_data => x_msg_data
3826 );
3827 WHEN OTHERS THEN
3828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3829 IF FND_MSG_PUB.Check_Msg_Level
3830 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3831 THEN
3832 FND_MSG_PUB.Add_Exc_Msg
3833 ( G_PKG_NAME ,
3834 l_api_name
3835 );
3836 END IF;
3837 FND_MSG_PUB.Count_And_Get
3838 ( p_count => x_msg_count,
3839 p_data => x_msg_data
3840 );
3841
3842 /*
3843 is_init_party_valid(l_return_status => x_return_status
3844 l_batch_rec => p_batch_rec);
3845 set_return_status(x_return_status => x_orig_status,
3846 l_return_status => p_new_status);
3847
3848 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3849 RAISE FND_API.G_EXC_UNEXP_ERROR;
3850 END IF;
3851
3852
3853 is_reci_party_valid(l_return_status => x_return_status
3854 l_trx_rec =>
3855 p_trx_rec);
3856 set_return_status(x_return_status => x_orig_status,
3857 l_return_status => p_new_status);
3858
3859 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3860 RAISE FND_API.G_EXC_UNEXP_ERROR;
3861 END IF;
3862
3863
3864 is_batch_type_valid(l_return_status => x_return_status
3865 l_batch_rec
3866 => p_batch_rec);
3867 set_return_status(x_return_status => x_orig_status,
3868 l_return_status => p_new_status);
3869 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3870 RAISE FND_API.G_EXC_UNEXP_ERROR;
3871 END IF;
3872
3873 is_reci_gl_date_valid(l_return_status =>
3874 x_return_status,
3875 l_batch_rec
3876 => p_batch_rec
3877 l_trx_rec =>
3878 p_trx_rec);
3879 set_return_status(x_return_status => x_orig_status,
3880 l_return_status => p_new_status);
3881 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3882 RAISE FND_API.G_EXC_UNEXP_ERROR;
3883 END IF;
3884 END IF;
3885
3886
3887 IF nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL)>= FND_API.G_VALID_LEVEL_FULL THEN
3888 is_curr_fld_valid(l_return_status => x_return_status,
3889 l_batch_rec
3890 => p_batch_rec);
3891 set_return_status(x_return_status => x_orig_status,
3892 l_return_status => p_new_status);
3893 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3894 RAISE FND_API.G_EXC_UNEXP_ERROR;
3895 END IF;
3896
3897
3898 */
3899
3900 END RECIPIENT_VALIDATE;
3901
3902
3903 PROCEDURE Is_Ini_Reci_Trx_Dist_Amt_Valid
3904 ( x_return_status OUT NOCOPY VARCHAR2,
3905 p_trx_amount_cr IN NUMBER,
3906 p_trx_amount_dr IN NUMBER,
3907 p_dist_lines_tbl IN OUT NOCOPY DIST_LINE_TBL_TYPE
3908 ) IS
3909 l_api_name CONSTANT VARCHAR2(30) := 'Is_Ini_Reci_Trx_Dist_Amt_Valid';
3910 l_boolean BOOLEAN;
3911 l_count NUMBER;
3912
3913 l_dist_cr_type NUMBER := 0;
3914 l_dist_dr_type NUMBER := 0;
3915 l_dist_pos_type NUMBER := 0;
3916 l_dist_neg_type NUMBER := 0;
3917 l_dist_total_cr NUMBER := 0;
3918 l_dist_total_dr NUMBER := 0;
3919 l_trx_number NUMBER;
3920
3921 BEGIN
3922 Debug('Is_Init_Trx_Dist_Amt_Valid(+)');
3923 x_return_status := FND_API.G_RET_STS_SUCCESS;
3924
3925 -- Perform the following validation only for manual mode
3926 l_count := p_dist_lines_tbl.COUNT;
3927 IF l_count > 0
3928 THEN
3929 FOR j IN 1..l_count LOOP
3930
3931 IF p_dist_lines_tbl(j).party_type = 'R' AND
3932 p_dist_lines_tbl(j).dist_type = 'L'
3933 THEN
3934
3935 --Bug: 16229717.
3936 IF p_dist_lines_tbl(j).amount_cr is null AND p_dist_lines_tbl(j).amount_dr is null THEN
3937
3938 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_DRCR_BDIST_LINE');
3939 FND_MSG_PUB.Add;
3940 Set_Return_Status(x_orig_status => x_return_status,
3941 p_new_status => FND_API.G_RET_STS_ERROR);
3942 Return;
3943
3944 END IF;
3945
3946 IF NVL(p_dist_lines_tbl(j).amount_cr, 0) <> 0 THEN
3947 l_dist_cr_type := 1;
3948 IF p_dist_lines_tbl(j).amount_cr > 0 THEN
3949 l_dist_pos_type := 1;
3950 ELSE
3951 l_dist_neg_type := 1;
3952 END IF;
3953 END IF;
3954 IF NVL(p_dist_lines_tbl(j).amount_dr, 0) <> 0 THEN
3955 l_dist_dr_type := 1;
3956 IF p_dist_lines_tbl(j).amount_dr > 0 THEN
3957 l_dist_pos_type := 1;
3958 ELSE
3959 l_dist_neg_type := 1;
3960 END IF;
3961 END IF;
3962 l_dist_total_cr := l_dist_total_cr + NVL(p_dist_lines_tbl(j).amount_cr, 0);
3963 l_dist_total_dr := l_dist_total_dr + NVL(p_dist_lines_tbl(j).amount_dr, 0);
3964 END IF;
3965 END LOOP;
3966 END IF;
3967
3968
3969 IF (nvl(p_trx_amount_cr,0) < l_dist_total_dr OR
3970 nvl(p_trx_amount_dr,0) < l_dist_total_cr )
3971 THEN
3972 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3973 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INCOMLT_SUM_REC_DIST');
3974 FND_MSG_PUB.Add;
3975 END IF;
3976 Set_Return_Status(x_orig_status => x_return_status,
3977 p_new_status => FND_API.G_RET_STS_ERROR);
3978 END IF;
3979
3980 IF l_dist_cr_type = l_dist_dr_type
3981 THEN
3982 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3983 THEN
3984 FND_MESSAGE.SET_NAME('FUN', 'FUN_INVALID_DRCR_BDIST_LINE');
3985 FND_MSG_PUB.Add;
3986 END IF;
3987 Set_Return_Status(x_orig_status => x_return_status,
3988 p_new_status => FND_API.G_RET_STS_ERROR);
3989 END IF;
3990
3991
3992 EXCEPTION
3993 WHEN FND_API.G_EXC_ERROR THEN
3994 x_return_status := FND_API.G_RET_STS_ERROR ;
3995 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3996 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3997 WHEN OTHERS THEN
3998 FND_MSG_PUB.ADD;
3999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4000 IF FND_MSG_PUB.Check_Msg_Level
4001 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4002 THEN
4003 FND_MSG_PUB.Add_Exc_Msg
4004 ( G_PKG_NAME ,
4005 l_api_name
4006 );
4007 END IF;
4008 Debug('Is_Reci_Trx_Dist_Amt_Valid(-)');
4009 END Is_Ini_Reci_Trx_Dist_Amt_Valid;
4010
4011
4012 PROCEDURE Ini_Recipient_Validate
4013 ( p_api_version IN NUMBER,
4014 p_init_msg_list IN VARCHAR2 ,
4015 p_validation_level IN NUMBER ,
4016 x_return_status OUT NOCOPY VARCHAR2,
4017 x_msg_count OUT NOCOPY NUMBER,
4018 x_msg_data OUT NOCOPY VARCHAR2,
4019 p_batch_rec IN OUT NOCOPY BATCH_REC_TYPE,
4020 p_trx_rec IN OUT NOCOPY TRX_REC_TYPE,
4021 p_dist_lines_tbl IN OUT NOCOPY DIST_LINE_TBL_TYPE
4022 )
4023 IS
4024 l_api_name CONSTANT VARCHAR2(30) := 'INI_RECIPIENT_VALIDATE';
4025 l_api_version CONSTANT NUMBER := 1.0;
4026 l_return_status VARCHAR(1);
4027 l_result VARCHAR(1);
4028 --l_batch_rec Batch_Rec_Type;
4029 --l_trx_tab Trx_Tbl_Type;
4030 --l_lines_tab Dist_Line_Tbl_Type;
4031 l_count NUMBER;
4032 l_index NUMBER := 0;
4033 i NUMBER := 1;
4034 BEGIN
4035 -- Standard call to check for call compatibility.
4036 IF NOT FND_API.Compatible_API_Call ( l_api_version,
4037 p_api_version,
4038 l_api_name,
4039 G_PKG_NAME )
4040 THEN
4041 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4042 END IF;
4043 -- Initialize message list if p_init_msg_list is set to TRUE.
4044 IF FND_API.to_Boolean( nvl(p_init_msg_list ,FND_API.G_FALSE)) THEN
4045 FND_MSG_PUB.initialize;
4046 END IF;
4047 -- Initialize API return status to success
4048 x_return_status := FND_API.G_RET_STS_SUCCESS;
4049
4050 -- API body
4051 Debug('RECIPIENT_VALIDATE(+)');
4052 IF nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL)>= 50 THEN
4053
4054 l_count := p_dist_lines_tbl.COUNT;
4055 IF l_count >= 1 THEN
4056 FOR i IN 1..l_count LOOP
4057 -- There should be atleast one line for recipient distribution
4058 Debug('3');
4059 IF p_dist_lines_tbl(i).dist_type = 'L' THEN
4060 l_index := i;
4061 END IF;
4062 END LOOP;
4063 END IF;
4064 IF l_index <= 0 THEN
4065 null;
4066
4067 ELSE
4068 Is_Payable_Acct_Valid(x_return_status => l_return_status,
4069 p_ccid => p_dist_lines_tbl(l_index).ccid);
4070 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4071 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4072 END IF;
4073
4074 Is_Reci_Acct_Valid(x_return_status => l_return_status,
4075 p_le_id => p_trx_rec.to_le_id,
4076 p_ledger_id => p_trx_rec.to_ledger_id,
4077 p_dist_lines_tbl => p_dist_lines_tbl);
4078
4079 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4080 RAISE FND_API.G_EXC_ERROR;
4081 END IF;
4082
4083 Is_Ini_Reci_Trx_Dist_Amt_Valid
4084 ( x_return_status => l_return_status,
4085 p_trx_amount_cr => p_trx_rec.reci_amount_cr,
4086 p_trx_amount_dr => p_trx_rec.reci_amount_dr,
4087 p_dist_lines_tbl => p_dist_lines_tbl);
4088
4089 Set_Return_Status(x_orig_status => x_return_status,
4090 p_new_status => l_return_status);
4091 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4093 END IF;
4094 END IF;
4095 END IF;
4096 Debug('RECIPIENT_VALIDATE(-)');
4097 -- End of API body.
4098 -- Standard call to get message count and if count is 1, get message info.
4099 FND_MSG_PUB.Count_And_Get
4100 ( p_count => x_msg_count,
4101 p_data => x_msg_data
4102 );
4103 EXCEPTION
4104 WHEN FND_API.G_EXC_ERROR THEN
4105 x_return_status := FND_API.G_RET_STS_ERROR ;
4106 FND_MSG_PUB.Count_And_Get
4107 ( p_count => x_msg_count,
4108 p_data => x_msg_data
4109 );
4110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4112 FND_MSG_PUB.Count_And_Get
4113 ( p_count => x_msg_count,
4114 p_data => x_msg_data
4115 );
4116 WHEN OTHERS THEN
4117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4118 IF FND_MSG_PUB.Check_Msg_Level
4119 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4120 THEN
4121 FND_MSG_PUB.Add_Exc_Msg
4122 ( G_PKG_NAME ,
4123 l_api_name
4124 );
4125 END IF;
4126 FND_MSG_PUB.Count_And_Get
4127 ( p_count => x_msg_count,
4128 p_data => x_msg_data
4129 );
4130
4131 /*
4132 is_init_party_valid(l_return_status => x_return_status
4133 l_batch_rec => p_batch_rec);
4134 set_return_status(x_return_status => x_orig_status,
4135 l_return_status => p_new_status);
4136
4137 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4138 RAISE FND_API.G_EXC_UNEXP_ERROR;
4139 END IF;
4140
4141
4142 is_reci_party_valid(l_return_status => x_return_status
4143 l_trx_rec =>
4144 p_trx_rec);
4145 set_return_status(x_return_status => x_orig_status,
4146 l_return_status => p_new_status);
4147
4148 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4149 RAISE FND_API.G_EXC_UNEXP_ERROR;
4150 END IF;
4151
4152
4153 is_batch_type_valid(l_return_status => x_return_status
4154 l_batch_rec
4155 => p_batch_rec);
4156 set_return_status(x_return_status => x_orig_status,
4157 l_return_status => p_new_status);
4158 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4159 RAISE FND_API.G_EXC_UNEXP_ERROR;
4160 END IF;
4161
4162 is_reci_gl_date_valid(l_return_status =>
4163 x_return_status,
4164 l_batch_rec
4165 => p_batch_rec
4166 l_trx_rec =>
4167 p_trx_rec);
4168 set_return_status(x_return_status => x_orig_status,
4169 l_return_status => p_new_status);
4170 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4171 RAISE FND_API.G_EXC_UNEXP_ERROR;
4172 END IF;
4173 END IF;
4174
4175
4176 IF nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL)>= FND_API.G_VALID_LEVEL_FULL THEN
4177 is_curr_fld_valid(l_return_status => x_return_status,
4178 l_batch_rec
4179 => p_batch_rec);
4180 set_return_status(x_return_status => x_orig_status,
4181 l_return_status => p_new_status);
4182 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4183 RAISE FND_API.G_EXC_UNEXP_ERROR;
4184 END IF;
4185
4186
4187 */
4188
4189 END INI_RECIPIENT_VALIDATE;
4190
4191
4192 PROCEDURE init_Generate_Distributions (
4193 p_api_version IN NUMBER,
4194 p_init_msg_list IN VARCHAR2 ,
4195 x_return_status OUT NOCOPY VARCHAR2,
4196 x_msg_count OUT NOCOPY NUMBER,
4197 x_msg_data OUT NOCOPY VARCHAR2,
4198 p_batch_id IN NUMBER
4199 ) IS
4200 l_batch_rec batch_rec_type;
4201 CURSOR l_batch_cursor IS SELECT batch_id,
4202 batch_number,
4203 initiator_id,
4204 from_le_id,
4205 from_ledger_id,
4206 control_total,
4207 currency_code,
4208 exchange_rate_type,
4209 status,
4210 description,
4211 trx_type_id,
4212 trx_type_code,
4213 gl_date,
4214 batch_date,
4215 reject_allow_flag,
4216 from_recurring_batch_id,
4217 auto_proration_flag
4218 FROM fun_trx_batches
4219 WHERE batch_id = p_batch_id;
4220 l_trx_tbl TRX_TBL_TYPE;
4221 CURSOR l_trx_cursor IS SELECT trx_id,
4222 initiator_id,
4223 recipient_id,
4224 to_le_id,
4225 to_ledger_id,
4226 batch_id,
4227 status,
4228 init_amount_cr,
4229 init_amount_dr,
4230 reci_amount_cr,
4231 reci_amount_dr,
4232 ar_invoice_number,
4233 invoice_flag,
4234 approver_id,
4235 approval_date,
4236 original_trx_id,
4237 reversed_trx_id,
4238 from_recurring_trx_id,
4239 initiator_instance_flag,
4240 recipient_instance_flag,
4241 NULL,
4242 trx_number
4243 FROM fun_trx_headers
4244 WHERE batch_id = p_batch_id;
4245
4246 l_init_dist_tbl INIT_DIST_TBL_TYPE;
4247 CURSOR l_init_dist_cursor IS SELECT batch_dist_id,
4248 line_number,
4249 batch_id,
4250 ccid,
4251 amount_cr,
4252 amount_dr,
4253 description
4254 FROM fun_batch_dists
4255 WHERE batch_id = p_batch_id;
4256
4257 l_dist_lines_tbl DIST_LINE_TBL_TYPE;
4258 CURSOR l_dist_lines_cursor IS SELECT dists.dist_id,
4259 dists.dist_number,
4260 lines.trx_id,
4261 dists.line_id,
4262 dists.party_id,
4263 dists.party_type_flag,
4264 dists.dist_type_flag,
4265 dists.batch_dist_id,
4266 dists.amount_cr,
4267 dists.amount_dr,
4268 dists.ccid,
4269 hdrs.trx_number
4270 FROM fun_trx_headers hdrs,
4271 fun_trx_lines lines,
4272 fun_dist_lines dists
4273 WHERE hdrs.batch_id = p_batch_id
4274 AND hdrs.trx_id = lines.trx_id
4275 AND lines.line_id = dists.line_id
4276 AND dists.party_type_flag = 'I'
4277 AND dists.dist_type_flag = 'R';
4278
4279 BEGIN
4280 OPEN l_batch_cursor;
4281 FETCH l_batch_cursor INTO l_batch_rec;
4282 CLOSE l_batch_cursor;
4283
4284 OPEN l_trx_cursor;
4285 FETCH l_trx_cursor BULK COLLECT INTO l_trx_tbl;
4286 CLOSE l_trx_cursor;
4287
4288 OPEN l_init_dist_cursor;
4289 FETCH l_init_dist_cursor BULK COLLECT INTO l_init_dist_tbl;
4290 CLOSE l_init_dist_cursor;
4291
4292 OPEN l_dist_lines_cursor;
4293 FETCH l_dist_lines_cursor BULK COLLECT INTO l_dist_lines_tbl;
4294 CLOSE l_dist_lines_cursor;
4295
4296 init_generate_distributions(p_api_version,
4297 nvl(p_init_msg_list,FND_API.G_FALSE),
4298 x_return_status,
4299 x_msg_count,
4300 x_msg_data,
4301 l_batch_rec,
4302 l_trx_tbl,
4303 l_init_dist_tbl,
4304 l_dist_lines_tbl);
4305 END;
4306
4307 -- Problem: need to add distribution validation and distribution generation
4308 PROCEDURE Init_Generate_Distributions
4309 ( p_api_version IN NUMBER,
4310 p_init_msg_list IN VARCHAR2,
4311 x_return_status OUT NOCOPY VARCHAR2,
4312 x_msg_count OUT NOCOPY NUMBER,
4313 x_msg_data OUT NOCOPY VARCHAR2,
4314 p_batch_rec IN OUT NOCOPY BATCH_REC_TYPE,
4315 p_trx_tbl IN OUT NOCOPY TRX_TBL_TYPE,
4316 p_init_dist_tbl IN OUT NOCOPY INIT_DIST_TBL_TYPE,
4317 p_dist_lines_tbl IN OUT NOCOPY DIST_LINE_TBL_TYPE
4318
4319 ) IS
4320 l_api_name CONSTANT VARCHAR2(30) := 'INIT_GENERATE_DISTRIBUTIONS';
4321 l_api_version CONSTANT NUMBER := 1.0;
4322 l_return_status VARCHAR(1);
4323 l_gen_dist_lines_tbl DIST_LINE_TBL_TYPE;
4324 l_init_dist_count NUMBER;
4325 l_dist_lines_count NUMBER;
4326 l_mau NUMBER; -- minimum accountable units
4327 l_currency_code VARCHAR2(15);
4328 --l_remaining_amts NUMBER_TYPE;
4329 l_l_l_running_tot NUMBER;-- last line running total
4330 l_total NUMBER := 0;
4331 l_amount_cr NUMBER;
4332 l_amount_dr NUMBER;
4333 l_running_amount_cr NUMBER;
4334 l_running_amount_dr NUMBER;
4335 l_running_amt_tbl init_dist_tbl_type;
4336 l_line_id NUMBER;
4337 l_sign NUMBER := 1;
4338 l_sum_amount_cr NUMBER;
4339 l_sum_amount_dr NUMBER;
4340 l_diff_amount NUMBER;
4341
4342 BEGIN
4343 -- Standard call to check for call compatibility.
4344 IF NOT FND_API.Compatible_API_Call ( l_api_version,
4345 p_api_version,
4346 l_api_name,
4347 G_PKG_NAME )
4348 THEN
4349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4350 END IF;
4351 -- Initialize message list if p_init_msg_list is set to TRUE.
4352 IF FND_API.to_Boolean( nvl(p_init_msg_list ,FND_API.G_FALSE)) THEN
4353 FND_MSG_PUB.initialize;
4354 END IF;
4355 -- Initialize API return status to success
4356 x_return_status := FND_API.G_RET_STS_SUCCESS;
4357
4358 -- API body
4359 Debug('INIT_GENERATE_DISTRIBUTIONS(+)');
4360 l_init_dist_count := p_init_dist_tbl.count;
4361 l_dist_lines_count := p_dist_lines_tbl.count;
4362
4363 l_currency_code := p_batch_rec.currency_code;
4364 -- Get currency information from FND_CURRENCIES table
4365 SELECT nvl( minimum_accountable_unit, power( 10, (-1 * precision)))
4366 INTO l_mau
4367 FROM FND_CURRENCIES
4368 WHERE currency_code = l_currency_code;
4369
4370
4371 -- Delete the distributions generated for the initiator
4372 IF p_trx_tbl.COUNT > 0 THEN
4373 FOR i IN p_trx_tbl.first..p_trx_tbl.last LOOP
4374 DELETE FROM fun_dist_lines
4375 WHERE party_type_flag = 'I'
4376 AND dist_type_flag = 'L'
4377 --AND auto_generate_flag = 'Y'
4378 AND line_id IN
4379 (SELECT line_id
4380 FROM fun_trx_lines trx_lines
4381 WHERE trx_lines.trx_id = p_trx_tbl(i).trx_id);
4382 END LOOP;
4383 END IF;
4384
4385 -- Find the total amount of the initiator distributions
4386 FOR i IN 1..l_init_dist_count LOOP
4387 IF Nvl(p_init_dist_tbl(i).amount_cr,0) < 0
4388 OR Nvl(p_init_dist_tbl(i).amount_cr,0) < 0
4389 THEN
4390 l_sign := -1;
4391 END IF;
4392
4393 l_total := l_total + ABS(NVL(p_init_dist_tbl(i).amount_cr,0)) + ABS(NVL(p_init_dist_tbl(i).amount_dr,0));
4394 END LOOP;
4395
4396 l_total := l_total * l_sign;
4397
4398
4399 -- Perform proration for each initiator distributions and roundoff
4400 -- Simplified the logic as part of the trx entry enhancements
4401 -- Formula to prorate =
4402 -- Batch Distribution Amount x Recipient Amount / Total Batch Distributions
4403
4404 FOR t IN p_trx_tbl.first..p_trx_tbl.last
4405 LOOP
4406 SELECT line_id
4407 INTO l_line_id
4408 FROM fun_trx_lines
4409 WHERE trx_id = p_trx_tbl(t).trx_id;
4410
4411 FOR i IN 1..l_init_dist_count
4412 LOOP
4413 l_amount_cr := ROUND(((p_init_dist_tbl(i).amount_cr * p_trx_tbl(t).init_amount_dr)/l_total)/ l_mau ) * l_mau;
4414 l_amount_dr := ROUND(((p_init_dist_tbl(i).amount_dr * p_trx_tbl(t).init_amount_cr)/l_total)/ l_mau ) * l_mau;
4415
4416 INSERT INTO fun_dist_lines(DIST_ID,
4417 LINE_ID,
4418 DIST_NUMBER,
4419 PARTY_ID,
4420 PARTY_TYPE_FLAG,
4421 DIST_TYPE_FLAG,
4422 BATCH_DIST_ID,
4423 AMOUNT_CR,
4424 AMOUNT_DR,
4425 CCID,
4426 DESCRIPTION,
4427 AUTO_GENERATE_FLAG,
4428 CREATED_BY,
4429 CREATION_DATE,
4430 LAST_UPDATED_BY,
4431 LAST_UPDATE_DATE,
4432 LAST_UPDATE_LOGIN,
4433 trx_id)
4434 VALUES (fun_dist_lines_s.nextval,
4435 l_line_id,
4436 i,
4437 p_trx_tbl(t).initiator_id,
4438 'I',
4439 'L',
4440 p_init_dist_tbl(i).batch_dist_id,
4441 l_amount_cr,
4442 l_amount_dr,
4443 p_init_dist_tbl(i).ccid,
4444 p_init_dist_tbl(i).description,
4445 'Y',
4446 fnd_global.user_id,
4447 sysdate,
4448 fnd_global.user_id,
4449 sysdate,
4450 fnd_global.user_id,
4451 p_trx_tbl(t).trx_id);
4452 END LOOP; -- batch distributions
4453
4454 --Bug: 9104801
4455
4456 adjust_dist_amount(p_trx_tbl(t).trx_id,
4457 p_trx_tbl(t).init_amount_cr,
4458 p_trx_tbl(t).init_amount_dr);
4459
4460 END LOOP; -- recipient
4461
4462 Debug('INIT_GENERATE_DISTRIBUTIONS(-)');
4463 -- End of API body.
4464 -- Standard call to get message count and if count is 1, get message info.
4465 FND_MSG_PUB.Count_And_Get
4466 ( p_count => x_msg_count,
4467 p_data => x_msg_data
4468 );
4469
4470 EXCEPTION
4471 WHEN FND_API.G_EXC_ERROR THEN
4472 x_return_status := FND_API.G_RET_STS_ERROR ;
4473 FND_MSG_PUB.Count_And_Get
4474 ( p_count => x_msg_count,
4475 p_data => x_msg_data
4476 );
4477 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4479 FND_MSG_PUB.Count_And_Get
4480 ( p_count => x_msg_count,
4481 p_data => x_msg_data
4482 );
4483 WHEN OTHERS THEN
4484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4485 IF FND_MSG_PUB.Check_Msg_Level
4486 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4487 THEN
4488 FND_MSG_PUB.Add_Exc_Msg
4489 ( G_PKG_NAME ,
4490 l_api_name
4491 );
4492 END IF;
4493 FND_MSG_PUB.Count_And_Get
4494 ( p_count => x_msg_count,
4495 p_data => x_msg_data
4496 );
4497 END;
4498
4499
4500
4501
4502 -- Problem: Remove validations for AR period and AP period
4503 PROCEDURE Is_AP_Valid
4504 ( x_return_status OUT NOCOPY VARCHAR2,
4505 p_initiator_id IN NUMBER,
4506 p_invoicing_rule IN VARCHAR2,
4507 p_recipient_id IN NUMBER,
4508 p_to_le_id IN NUMBER,
4509 p_trx_date IN DATE
4510 ) IS
4511 l_from_le_id NUMBER;
4512 l_from_le_party_id NUMBER; -- <bug 3450031>
4513 l_from_ou_id NUMBER;
4514 l_to_ou_id NUMBER;
4515 l_success BOOLEAN;
4516 l_supplier_id NUMBER;
4517 l_pay_site_id NUMBER;
4518 l_msg_data VARCHAR2(2000);
4519 l_count NUMBER;
4520 CURSOR ou_valid_csr IS
4521 SELECT count(*)
4522 FROM hr_operating_units ou
4523 WHERE organization_id = l_to_ou_id
4524 AND date_from <= p_trx_date
4525 AND NVL(date_to, p_trx_date) >= p_trx_date; -- <bug 3450031>
4526
4527 /* Removed as this should not be validated here
4528 CURSOR period_open_csr IS
4529 SELECT count(*)
4530 FROM GL_PERIOD_STATUSES PST
4531 WHERE pst.application_id = 200
4532 AND pst.closing_status <> 'N'
4533 AND pst.adjustment_period_flag <> 'Y'
4534 AND pst.ledger_id = p_to_le_id; */
4535 BEGIN
4536 -- 7.3.1.4
4537 -- Initialize API return status to success
4538 x_return_status := FND_API.G_RET_STS_SUCCESS;
4539
4540 -- API body
4541 Debug('IS_AP_VALID(+)');
4542 IF p_invoicing_rule = 'Y' THEN
4543
4544 -- 7.3.1.3
4545 l_to_ou_id := Fun_Tca_Pkg.Get_OU_Id(p_recipient_id, p_trx_date);
4546 IF l_to_ou_id IS NULL THEN
4547 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4548 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_OU');
4549 FND_MSG_PUB.Add;
4550 END IF;
4551 Set_Return_Status(x_orig_status => x_return_status,
4552 p_new_status => FND_API.G_RET_STS_ERROR);
4553 Return;
4554 END IF;
4555 OPEN ou_valid_csr;
4556 FETCH ou_valid_csr INTO l_count;
4557 CLOSE ou_valid_csr;
4558 IF l_count < 1 THEN
4559 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4560 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_OU');
4561 FND_MSG_PUB.Add;
4562 END IF;
4563 Set_Return_Status(x_orig_status => x_return_status,
4564 p_new_status => FND_API.G_RET_STS_ERROR);
4565 Return;
4566 END IF;
4567
4568 -- <bug 3450031 start>
4569 l_from_le_party_id := Fun_Tca_Pkg.Get_LE_Id(p_initiator_id, p_trx_date);
4570
4571 SELECT legal_entity_id
4572 INTO l_from_le_id
4573 FROM xle_firstparty_information_v
4574 WHERE party_id = l_from_le_party_id;
4575 -- <bug 3450031 end>
4576
4577 l_from_ou_id := Fun_Tca_Pkg.Get_OU_Id(p_initiator_id, p_trx_date);
4578 IF l_from_ou_id IS NULL THEN
4579 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4580 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_OU');
4581 FND_MSG_PUB.Add;
4582 END IF;
4583 Set_Return_Status(x_orig_status => x_return_status,
4584 p_new_status => FND_API.G_RET_STS_ERROR);
4585 Return;
4586 END IF;
4587
4588 l_success := Fun_Trading_Relation.Get_Supplier('INTERCOMPANY', l_from_le_id,
4589 p_to_le_id,l_from_ou_id,
4590 l_to_ou_id,
4591 p_initiator_id, p_recipient_id,
4592 p_trx_date, l_msg_data,
4593 l_supplier_id, l_pay_site_id);
4594
4595 IF NOT l_success THEN
4596 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4597 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_INVALID_SUPPLIER');
4598 FND_MSG_PUB.Add;
4599 END IF;
4600 Set_Return_Status(x_orig_status => x_return_status,
4601 p_new_status => FND_API.G_RET_STS_ERROR);
4602 END IF;
4603 /* Removed, should not check here -- Check AP period is open or not
4604 OPEN period_open_csr;
4605 FETCH period_open_csr INTO l_count;
4606 CLOSE period_open_csr;
4607 IF l_count < 1 THEN
4608 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4609 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_AP_PERIOD_NOT_OPEN');
4610 FND_MSG_PUB.Add;
4611 END IF;
4612 Set_Return_Status(x_orig_status => x_return_status,
4613 p_new_status => FND_API.G_RET_STS_ERROR);
4614 END IF;
4615 */
4616 END IF;
4617 Debug('IS_AP_VALID(-)');
4618 -- End of API body.
4619 END Is_Ap_Valid;
4620
4621
4622
4623 PROCEDURE AR_Transfer_Validate
4624 ( p_api_version IN NUMBER,
4625 p_init_msg_list IN VARCHAR2 ,
4626 p_validation_level IN NUMBER ,
4627 x_return_status OUT NOCOPY VARCHAR2,
4628 x_msg_count OUT NOCOPY NUMBER,
4629 x_msg_data OUT NOCOPY VARCHAR2,
4630 p_batch_id IN NUMBER,
4631 p_trx_id IN NUMBER
4632 ) IS
4633 l_api_name CONSTANT VARCHAR2(30) := 'AR_TRANSFER_VALIDATE';
4634 l_api_version CONSTANT NUMBER := 1.0;
4635 l_return_status VARCHAR2(1);
4636 l_trx_type_id NUMBER;
4637 l_initiator_id NUMBER;
4638 l_trx_date DATE;
4639 l_memo_line_name VARCHAR2(100);
4640 l_ar_trx_type_name VARCHAR2(100);
4641 l_memo_line_id NUMBER;
4642 l_ar_trx_type_id NUMBER;
4643 l_default_term_id NUMBER;
4644 l_from_ou_id NUMBER;
4645 l_from_ledger_id NUMBER;
4646 l_recipient_id NUMBER;
4647 l_to_ou_id NUMBER;
4648 l_to_le_id NUMBER;
4649 l_count NUMBER;
4650 --ER: 8288979
4651 l_init_amount_dr number;
4652 l_init_amount_cr number;
4653 l_trx_type varchar2(4);
4654
4655
4656 CURSOR period_open_csr (p_trx_date DATE,
4657 p_ledger_id NUMBER) IS
4658 SELECT COUNT(*)
4659 FROM gl_period_statuses glps,
4660 gl_periods periods,
4661 gl_ledgers ledgers
4662 WHERE periods.period_set_name = ledgers.period_set_name
4663 AND TRUNC(p_trx_date) BETWEEN periods.start_date AND periods.end_date
4664 AND glps.period_name = periods.period_name
4665 AND glps.application_id = 222
4666 AND glps.set_of_books_id = ledgers.ledger_id
4667 AND glps.set_of_books_id = p_ledger_id
4668 AND ledgers.ledger_id = p_ledger_id
4669 AND glps.adjustment_period_flag <> 'Y'
4670 AND glps.closing_status IN ('O','F');
4671
4672 BEGIN
4673 -- Standard call to check for call compatibility.
4674 IF NOT FND_API.Compatible_API_Call ( l_api_version,
4675 p_api_version,
4676 l_api_name,
4677 G_PKG_NAME )
4678 THEN
4679 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4680 END IF;
4681
4682 -- Initialize message list if p_init_msg_list is set to TRUE.
4683 IF FND_API.to_Boolean( nvl(p_init_msg_list ,FND_API.G_FALSE)) THEN
4684 FND_MSG_PUB.initialize;
4685 END IF;
4686
4687 -- Initialize API return status to success
4688 x_return_status := FND_API.G_RET_STS_SUCCESS;
4689
4690 -- API body
4691 Debug('AR_TRANSFER_VALIDATE(+)');
4692
4693 -- Retrieve initiator, transaction type
4694 SELECT initiator_id, trx_type_id, batch_date, from_ledger_id
4695 INTO l_initiator_id, l_trx_type_id, l_trx_date, l_from_ledger_id
4696 FROM fun_trx_batches
4697 WHERE batch_id = p_batch_id;
4698
4699 SELECT recipient_id, to_le_id, init_amount_dr, init_amount_cr
4700 INTO l_recipient_id, l_to_le_id, l_init_amount_dr, l_init_amount_cr
4701 FROM fun_trx_headers
4702 WHERE trx_id = p_trx_id;
4703
4704 -- Retrieve Operating unit
4705 l_from_ou_id := Fun_Tca_Pkg.Get_OU_Id(l_initiator_id, l_trx_date);
4706 -- ER:8288979. Passing p_trx_id.
4707 -- Retrieve memo line, ar trx type
4708 fun_trx_types_pub.Get_Trx_Type_Map(l_from_ou_id, l_trx_type_id,
4709 l_trx_date, p_trx_id,
4710 l_memo_line_id, l_memo_line_name,
4711 l_ar_trx_type_id, l_ar_trx_type_name,
4712 l_default_term_id);
4713 IF l_memo_line_name IS NULL OR l_ar_trx_type_name IS NULL THEN
4714
4715 -- Problem need a message error code here
4716 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4717 -- ER: 8288979
4718 l_trx_type := 'INV';
4719 IF(l_init_amount_dr is null) THEN
4720 l_init_amount_dr := 0;
4721 END IF;
4722 IF(l_init_amount_cr is null) THEN
4723 l_init_amount_cr := 0;
4724 END IF;
4725 -- Credit memo transaction
4726 if( l_init_amount_dr < 0 OR l_init_amount_cr > 0) THEN
4727 l_trx_type := 'CM';
4728 END IF;
4729
4730 FND_MESSAGE.SET_NAME('FUN', 'FUN_NO_TRX_TYPE_MAP');
4731 IF l_trx_type = 'INV' THEN
4732 FND_MESSAGE.SET_TOKEN('TRX_TYPE','Invoice Type');
4733 ELSE
4734 FND_MESSAGE.SET_TOKEN('TRX_TYPE','Credit Memo Type');
4735 END IF;
4736 FND_MSG_PUB.Add;
4737 END IF;
4738 Set_Return_Status(x_orig_status => x_return_status, p_new_status => FND_API.G_RET_STS_ERROR);
4739 END IF;
4740
4741 OPEN period_open_csr(l_trx_date,
4742 l_from_ledger_id);
4743 FETCH period_open_csr INTO l_count;
4744 IF l_count < 1 THEN
4745 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4746 FND_MESSAGE.SET_NAME('FUN', 'FUN_API_AR_PERIOD_NOT_OPEN');
4747 FND_MSG_PUB.Add;
4748 END IF;
4749 Set_Return_Status(x_orig_status => x_return_status, p_new_status => FND_API.G_RET_STS_ERROR);
4750 END IF;
4751
4752 Is_AR_Valid(x_return_status => l_return_status,
4753 p_initiator_id => l_initiator_id,
4754 p_invoicing_rule => 'Y',
4755 p_recipient_id => l_recipient_id,
4756 p_to_le_id => l_to_le_id,
4757 p_trx_date => l_trx_date);
4758
4759 Set_Return_Status(x_orig_status => x_return_status, p_new_status => l_return_status);
4760
4761 Debug('AR_TRANSFER_VALIDATE(-)');
4762 -- End of API body.
4763 -- Standard call to get message count and if count is 1, get message info.
4764 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4765 p_data => x_msg_data);
4766 EXCEPTION
4767 WHEN FND_API.G_EXC_ERROR THEN
4768 x_return_status := FND_API.G_RET_STS_ERROR ;
4769 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4770 p_data => x_msg_data);
4771 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4772 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4773 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4774 p_data => x_msg_data);
4775 WHEN OTHERS THEN
4776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4777 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4778 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
4779 END IF;
4780 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4781 p_data => x_msg_data);
4782
4783
4784 END AR_Transfer_Validate;
4785
4786
4787 PROCEDURE Debug
4788 (
4789 p_message IN VARCHAR2
4790 ) IS
4791 BEGIN
4792 -- API body
4793 -- Problem: Use FND LOGGING mechanism?
4794 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
4795 FND_MESSAGE.SET_NAME('FUN', p_message);
4796 FND_MSG_PUB.Add;
4797 END IF;
4798
4799 -- End of API body.
4800 END Debug;
4801
4802 -- Procedure will be called from the Outbound Search page
4803 -- when a batch is DELETED (p_batch_id is passed)
4804 -- Or it will be called from the Outbound Create page when a
4805 -- transaction is DELETED (p_batch_id and p_trx_id is passed)
4806 PROCEDURE cancel_notifications (p_batch_id IN NUMBER,
4807 p_trx_id IN NUMBER,
4808 p_init_msg_list IN VARCHAR2 ,
4809 x_return_status OUT NOCOPY VARCHAR2,
4810 x_msg_count OUT NOCOPY NUMBER,
4811 x_msg_data OUT NOCOPY VARCHAR2)
4812 IS
4813
4814 TYPE notList IS TABLE OF NUMBER;
4815 l_notif_tbl notList;
4816 l_sql VARCHAR2(2000);
4817 l_api_name VARCHAR2(30) := 'cancel_notifications';
4818
4819
4820 BEGIN
4821 -- Initialize message list if p_init_msg_list is set to TRUE.
4822 IF FND_API.to_Boolean( nvl(p_init_msg_list ,FND_API.G_FALSE))
4823 THEN
4824 FND_MSG_PUB.initialize;
4825 END IF;
4826
4827 -- Initialize API return status to success
4828 x_return_status := FND_API.G_RET_STS_SUCCESS;
4829
4830 IF p_batch_id IS NULL AND p_trx_id IS NULL
4831 THEN
4832 x_return_status := FND_API.G_RET_STS_ERROR;
4833 RETURN;
4834 END IF;
4835
4836 -- Some activities may be COMPLETE but the notification would
4837 -- still be outstanding
4838 -- Once transactions have gone into GL, AP and AR workflows,
4839 -- they cannot be deleted
4840 l_sql := ' SELECT notif.notification_id ' ||
4841 ' FROM wf_item_activity_statuses wias, ' ||
4842 ' wf_notifications notif ' ||
4843 ' WHERE wias.item_type IN (''FUNRMAIN'', ''FUNIMAIN'', ' ||
4844 ' ''FUNRTVAL'') ' ||
4845 ' AND wias.notification_id = notif.notification_id ' ||
4846 ' AND notif.status = ''OPEN''';
4847
4848 -- Itemkey is in the format <batch_id>_<trx_id><sequence_number>
4849 IF p_trx_id IS NULL
4850 THEN
4851 l_sql := l_sql ||
4852 ' AND wias.item_key like '''||p_batch_id||'_%''';
4853 ELSE
4854 l_sql := l_sql ||
4855 ' AND wias.item_key like '''||p_batch_id||'_'||p_trx_id||'%''';
4856 END IF;
4857
4858 EXECUTE IMMEDIATE l_sql
4859 BULK COLLECT INTO l_notif_tbl;
4860
4861
4862 IF l_notif_tbl.COUNT > 0
4863 THEN
4864 FOR i IN l_notif_tbl.FIRST..l_notif_tbl.LAST
4865 LOOP
4866 wf_notification.cancel
4867 (l_notif_tbl(i),
4868 'Transaction Deleted');
4869
4870 END LOOP;
4871 END IF;
4872
4873 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4874 p_data => x_msg_data);
4875
4876 EXCEPTION
4877 WHEN FND_API.G_EXC_ERROR THEN
4878 x_return_status := FND_API.G_RET_STS_ERROR ;
4879 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4880 p_data => x_msg_data);
4881
4882 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4883 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4884 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4885 p_data => x_msg_data);
4886 WHEN OTHERS THEN
4887 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4888 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4889 THEN
4890 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
4891 END IF;
4892 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
4893 p_data => x_msg_data);
4894 END cancel_notifications;
4895
4896 FUNCTION getprecision(l_currency_code VARCHAR2) RETURN NUMBER
4897 IS
4898 l_precision NUMBER;
4899 BEGIN
4900
4901 SELECT PRECISION INTO l_precision FROM fnd_currencies WHERE currency_code=l_currency_code;
4902 RETURN l_precision;
4903 END;
4904
4905 END FUN_TRX_PVT;