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