[Home] [Help]
PACKAGE BODY: APPS.ARP_ACCT_EVENT_PKG
Source
1 PACKAGE BODY arp_acct_event_pkg AS
2 /* $Header: ARXLUTB.pls 120.11 2008/02/07 10:03:20 arnkumar ship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5
6 --{Local routines
7 PROCEDURE log(
8 message IN VARCHAR2,
9 newline IN BOOLEAN DEFAULT TRUE) IS
10 BEGIN
11 IF message = 'NEWLINE' THEN
12 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
13 ELSIF (newline) THEN
14 FND_FILE.put_line(fnd_file.log,message);
15 ELSE
16 FND_FILE.put(fnd_file.log,message);
17 END IF;
18 IF PG_DEBUG = 'Y' THEN
19 ARP_STANDARD.DEBUG(message);
20 END IF;
21 END log;
22
23 PROCEDURE out(
24 message IN VARCHAR2,
25 newline IN BOOLEAN DEFAULT TRUE) IS
26 BEGIN
27 IF message = 'NEWLINE' THEN
28 FND_FILE.NEW_LINE(FND_FILE.output, 1);
29 ELSIF (newline) THEN
30 FND_FILE.put_line(fnd_file.output,message);
31 ELSE
32 FND_FILE.put(fnd_file.output,message);
33 END IF;
34 END out;
35
36 PROCEDURE outandlog(
37 message IN VARCHAR2,
38 newline IN BOOLEAN DEFAULT TRUE) IS
39 BEGIN
40 out(message, newline);
41 log(message, newline);
42 END outandlog;
43
44
45 PROCEDURE validate_parameter
46 (p_start_date IN DATE,
47 p_end_date IN DATE,
48 p_org_id IN NUMBER DEFAULT NULL,
49 p_type IN VARCHAR2,
50 p_entity IN VARCHAR2,
51 x_return_status IN OUT NOCOPY VARCHAR2)
52 IS
53 CURSOR c_ou(p_org_id NUMBER) IS
54 SELECT org_id
55 FROM ar_system_parameters_all
56 WHERE org_id = p_org_id;
57 l_org_id NUMBER;
58 BEGIN
59 IF p_start_date IS NULL THEN
60 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
61 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'p_start_date' );
62 FND_MSG_PUB.ADD;
63 x_return_status := FND_API.G_RET_STS_ERROR;
64 END IF;
65
66 IF p_end_date IS NULL THEN
67 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
68 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'p_end_date' );
69 FND_MSG_PUB.ADD;
70 x_return_status := FND_API.G_RET_STS_ERROR;
71 END IF;
72
73 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
74 IF p_start_date < p_end_date THEN
75 arp_standard.debug(' end date should be greater than the start date');
76 fnd_message.set_name('AR', 'HZ_API_DATE_GREATER');
77 fnd_message.set_token('DATE2', p_end_date);
78 fnd_message.set_token('DATE1', p_start_date);
79 fnd_msg_pub.add;
80 x_return_status := fnd_api.g_ret_sts_error;
81 END IF;
82 END IF;
83
84 IF p_org_id IS NOT NULL THEN
85 OPEN c_ou(p_org_id);
86 FETCH c_ou INTO l_org_id;
87 IF c_ou%NOTFOUND THEN
88 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
89 FND_MESSAGE.SET_TOKEN('FK', 'org id');
90 FND_MESSAGE.SET_TOKEN('COLUMN', 'org_id');
91 FND_MESSAGE.SET_TOKEN('TABLE', 'ar_system_parameters_all');
92 FND_MSG_PUB.ADD;
93 x_return_status := FND_API.G_RET_STS_ERROR;
94 END IF;
95 CLOSE c_ou;
96 END IF;
97
98 IF p_entity = 'RA_CUST_TRX_LINE_GL_DIST_ALL' THEN
99 IF p_type NOT IN ('INV', 'CM', 'CB', 'DM', 'DEP', 'GUAR', 'ALL','INVDEPGUAR') THEN
100 FND_MESSAGE.SET_NAME( 'AR', 'AR_ONLY_VALUE_ALLOWED' );
101 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'trx_type' );
102 FND_MESSAGE.SET_TOKEN( 'VALUES', 'INV,CM,CB,DM,DEP,GUAR,ALL,INVDEPGUAR');
103 FND_MSG_PUB.ADD;
104 x_return_status := FND_API.G_RET_STS_ERROR;
105 END IF;
106 ELSIF p_entity = 'AR_CASH_RECEIPT_HISTORY_ALL' THEN
107 IF p_type NOT IN ('CASH', 'MISC', 'ALL') THEN
108 FND_MESSAGE.SET_NAME( 'AR', 'AR_ONLY_VALUE_ALLOWED' );
109 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'trx_type' );
110 FND_MESSAGE.SET_TOKEN( 'VALUES', 'CASH,MISC,ALL');
111 FND_MSG_PUB.ADD;
112 x_return_status := FND_API.G_RET_STS_ERROR;
113 END IF;
114 ELSIF p_entity = 'AR_RECEIVABLE_APPLICATIONS_ALL' THEN
115 IF p_type NOT IN ('APP', 'CMAPP', 'ALL') THEN
116 FND_MESSAGE.SET_NAME( 'AR', 'AR_ONLY_VALUE_ALLOWED' );
117 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'trx_type' );
118 FND_MESSAGE.SET_TOKEN( 'VALUES', 'APP,CMAPP,ALL');
119 FND_MSG_PUB.ADD;
120 x_return_status := FND_API.G_RET_STS_ERROR;
121 END IF;
122 END IF;
123
124 END;
125 --}
126
127
128 PROCEDURE update_dates_for_trx_event
129 (p_source_id_int_1 IN NUMBER,
130 p_trx_number IN VARCHAR2,
131 p_legal_entity_id IN NUMBER,
132 p_ledger_id IN NUMBER,
133 p_org_id IN NUMBER,
134 p_event_id IN NUMBER,
135 p_valuation_method IN VARCHAR2,
136 p_entity_type_code IN VARCHAR2,
137 p_event_type_code IN VARCHAR2,
138 p_curr_event_date IN DATE,
139 p_event_date IN DATE,
140 p_status IN VARCHAR2,
141 p_action IN VARCHAR2,
142 p_curr_trx_date IN DATE,
143 p_transaction_date IN DATE,
144 x_event_id OUT NOCOPY NUMBER)
145 IS
146 CURSOR c IS
147 SELECT gld.cust_trx_line_gl_dist_id dist_ctlgd_id,
148 gld.gl_date dist_gl_date,
149 gld.account_set_flag dist_account_set_flag,
150 trx.customer_trx_id trx_trx_id,
151 trx.complete_flag trx_complete_flag,
152 trx.trx_date trx_trx_date,
153 trx.invoicing_rule_id trx_invoicing_rule_id,
154 ctt.post_to_gl trx_post_to_gl,
155 xet.entity_id ent_entity_id,
156 ev.event_id trx_event_id,
157 ev.event_date trx_event_date,
158 ev.event_status_code trx_event_status,
159 ev.transaction_date trx_ev_trx_date,
160 gld.event_id dist_event_id,
161 distev.event_status_code dist_event_status,
162 distev.event_date dist_event_date,
163 distev.transaction_date dist_ev_trx_date
164 FROM ra_customer_trx trx,
165 ra_cust_trx_line_gl_dist gld,
166 ra_cust_trx_types ctt,
167 xla_transaction_entities_upg xet,
168 xla_events ev,
169 xla_events distev
170 WHERE trx.customer_trx_id = p_source_id_int_1
171 AND trx.customer_trx_id = gld.customer_trx_id
172 AND gld.account_class = 'REC'
173 AND gld.posting_control_id = -3
174 AND gld.latest_rec_flag = 'Y'
175 AND ctt.cust_trx_type_id = trx.cust_trx_type_id
176 AND trx.SET_OF_BOOKS_ID = xet.LEDGER_ID
177 AND xet.application_id = 222
178 AND nvl(xet.source_id_int_1, -99) = trx.customer_trx_id
179 AND xet.entity_code = 'TRANSACTIONS'
180 AND xet.entity_id = ev.entity_id
181 AND ev.application_id = 222
182 AND ev.event_date = gld.gl_date(+)
183 AND distev.application_id(+) = 222
184 AND gld.event_id = distev.event_id(+)
185 ORDER BY DECODE(gld.account_set_flag,'N',1,2) asc;
186
187 l_rec c%ROWTYPE;
188 l_upg_trx_date DATE := FND_API.G_MISS_DATE;
189 l_upg_gl_date DATE := FND_API.G_MISS_DATE;
190
191 l_event_source_info xla_events_pub_pkg.t_event_source_info;
192 l_event_id NUMBER;
193 l_valuation_method VARCHAR2(10);
194 l_event_info_t xla_events_pub_pkg.t_array_event_info;
195 l_security xla_events_pub_pkg.t_security;
196
197 not_suffisant_info EXCEPTION;
198 done EXCEPTION;
199 not_supported_action EXCEPTION;
200 more_than_one_event EXCEPTION;
201 no_event_found EXCEPTION;
202 ent_ev_no_exist EXCEPTION;
203
204 BEGIN
205 arp_standard.debug('do_on_existing_events+');
206 arp_standard.debug(' p_trx_number :'||p_trx_number);
207 arp_standard.debug(' p_legal_entity_id :'||p_legal_entity_id);
208 arp_standard.debug(' p_ledger_id :'||p_ledger_id);
209 arp_standard.debug(' p_org_id :'||p_org_id);
210 arp_standard.debug(' p_event_id :'||p_event_id);
211 arp_standard.debug(' p_valuation_method :'||p_valuation_method);
212 arp_standard.debug(' p_entity_type_code :'||p_entity_type_code);
213 arp_standard.debug(' p_event_type_code :'||p_event_type_code);
214 arp_standard.debug(' p_status :'||p_status);
215 arp_standard.debug(' p_action :'||p_action);
216 arp_standard.debug(' p_event_date :'||p_event_date);
217 arp_standard.debug(' p_curr_event_date :'||p_curr_event_date);
218 arp_standard.debug(' p_curr_trx_date :'||p_curr_trx_date);
219
220 -- get the eve
221 IF p_ledger_id IS NULL OR p_org_id IS NULL THEN
222 RAISE not_suffisant_info;
223 END IF;
224
225 OPEN c;
226 FETCH c INTO l_rec;
227 IF c%NOTFOUND THEN
228 RAISE ent_ev_no_exist;
229 END IF;
230 CLOSE c;
231
232
233 l_event_source_info.application_id := 222;
234 l_event_source_info.legal_entity_id := p_legal_entity_id;
235 l_event_source_info.ledger_id := p_ledger_id;
236 l_event_source_info.entity_type_code := p_entity_type_code;
237 l_event_source_info.transaction_number:= p_trx_number;
238 l_event_source_info.source_id_int_1 := p_source_id_int_1;
239
240 l_security.security_id_int_1 := p_org_id;
241
242
243 arp_standard.debug(' dist_ctlgd_id :'|| l_rec.dist_ctlgd_id );
244 arp_standard.debug(' dist_gl_date :'|| l_rec.dist_gl_date );
245 arp_standard.debug(' dist_account_set_flag :'|| l_rec.dist_account_set_flag );
246 arp_standard.debug(' trx_trx_id :'|| l_rec.trx_trx_id );
247 arp_standard.debug(' trx_complete_flag :'|| l_rec.trx_complete_flag );
248 arp_standard.debug(' trx_trx_date :'|| l_rec.trx_trx_date );
249 arp_standard.debug(' trx_invoicing_rule_id :'|| l_rec.trx_invoicing_rule_id );
250 arp_standard.debug(' trx_post_to_gl :'|| l_rec.trx_post_to_gl );
251 arp_standard.debug(' ent_entity_id :'|| l_rec.ent_entity_id );
252 arp_standard.debug(' trx_event_id :'|| l_rec.trx_event_id );
253 arp_standard.debug(' trx_event_date :'|| l_rec.trx_event_date );
254 arp_standard.debug(' trx_event_status :'|| l_rec.trx_event_status );
255 arp_standard.debug(' trx_ev_trx_date :'|| l_rec.trx_ev_trx_date );
256 arp_standard.debug(' dist_event_id :'|| l_rec.dist_event_id );
257 arp_standard.debug(' dist_event_status :'|| l_rec.dist_event_status );
258 arp_standard.debug(' dist_event_date :'|| l_rec.dist_event_date );
259 arp_standard.debug(' dist_ev_trx_date :'|| l_rec.dist_ev_trx_date );
260
261
262 IF l_rec.dist_account_set_flag = 'Y' THEN
263 --
264 -- Case the transaction with rule
265 -- Rev Rec has not run
266 -- Only one event should exist and no denormalization on distributions
267 --
268 -- User transaction level event
269 --
270 l_event_id := l_rec.trx_event_id;
271
272
273 ELSIF l_rec.dist_account_set_flag = 'N' THEN
274 --
275 -- Case the transaction with rule REVREC run or none rule based trx
276 -- one single tied back to rec distribution should exist
277 --
278 -- distribution level event_id
279 l_event_id := l_rec.dist_event_id;
280
281 END IF;
282
283
284 --
285 --Determination of the dates
286 --
287 IF (p_curr_event_date <> p_event_date AND
288 p_curr_event_date IS NOT NULL AND
289 p_event_date IS NOT NULL )
290 OR
291 (p_curr_event_date IS NULL AND p_event_date IS NOT NULL)
292 OR
293 (p_curr_event_date IS NOT NULL AND p_event_date IS NULL)
294 THEN
295 l_upg_gl_date := p_event_date;
296 END IF;
297
298
299 IF (p_curr_trx_date <> p_event_date AND
300 p_curr_trx_date IS NOT NULL AND
301 p_transaction_date IS NOT NULL )
302 OR
303 (p_curr_trx_date IS NOT NULL AND p_transaction_date IS NULL)
304 OR
305 (p_curr_trx_date IS NULL AND p_transaction_date IS NOT NULL)
306 THEN
307 l_upg_trx_date := p_transaction_date;
308 END IF;
309
310
311
312 IF l_upg_trx_date <> FND_API.G_MISS_DATE OR l_upg_gl_date <> FND_API.G_MISS_DATE THEN
313 arp_standard.debug(' call update event with at transaction level');
314 xla_events_pub_pkg.update_event
315 (p_event_source_info => l_event_source_info,
316 p_event_id => l_event_id,
317 p_event_date => p_event_date,
318 p_valuation_method => p_valuation_method,
319 p_transaction_date => p_transaction_date,
320 p_security_context => l_security);
321 END IF;
322
323 arp_standard.debug('do_on_existing_events-');
324
325 EXCEPTION
326 WHEN ent_ev_no_exist THEN
327 arp_standard.debug(' EXCEPTION ent_ev_no_exist - no event update required');
328 WHEN no_event_found THEN
329 arp_standard.debug(' EXCEPTION no_event_found - no event update required');
330 WHEN more_than_one_event THEN
331 arp_standard.debug(' EXCEPTION more_than_one_event can not update');
332 WHEN not_suffisant_info THEN
333 arp_standard.debug(' EXCEPTION not_suffisant_info do_on_existing_events has done nothing');
334 WHEN done THEN
335 arp_standard.debug(' do_on_existing_events has done '||p_action);
336 arp_standard.debug('do_on_existing_events has done -');
337 WHEN not_supported_action THEN
338 arp_standard.debug(' EXCEPTION not_supported_action do_on_existing_events '||p_action);
339 END;
340
341
342
343 PROCEDURE get_ar_trx_event_info
344 (p_entity_code IN VARCHAR2,
345 p_source_int_id IN NUMBER)
346 IS
347 BEGIN
348 arp_standard.debug('get_ar_trx_event_info +');
349 IF p_entity_code = 'TRANSACTIONS' THEN
350 --
351 -- If the transaction is rule based then no records will be inserted by
352 -- by this statement if REVREC has not run.
353 -- If gl_date is null the transaction is non postable
354 -- there is no need to verify the xla upgrade
355 --
356 INSERT INTO ar_detect_gt(gl_date ,
357 source_int_id ,
358 entity_code ,
359 event_id ,
360 from_application)
361 SELECT DISTINCT a.gl_date ,
362 a.trx_id ,
363 p_entity_code ,
364 a.event_id ,
365 'AR'
366 FROM
367 (SELECT d.gl_date gl_date,
368 d.customer_trx_id trx_id,
369 d.event_id event_id
370 FROM ra_cust_trx_line_gl_dist d
371 WHERE customer_trx_id = p_source_int_id
372 AND account_set_flag = 'N'
373 AND gl_date IS NOT NULL
374 UNION ALL
375 SELECT ra.gl_date gl_date,
376 ra.customer_trx_id trx_id,
377 ra.event_id event_id
378 FROM ra_customer_trx trx,
379 ra_cust_trx_types ctt,
380 ar_receivable_applications ra
381 WHERE trx.customer_trx_id = p_source_int_id
382 AND ctt.cust_trx_type_id= trx.cust_trx_type_id
383 AND ctt.org_id = trx.org_id
384 AND ctt.type = 'CM'
385 AND trx.customer_trx_id = ra.customer_trx_id
386 AND ra.status = 'APP') a;
387
388 -- Need to insert the CMAPP events
389
390
391 ELSIF p_entity_code = 'RECEIPTS' THEN
392
393 INSERT INTO ar_detect_gt(gl_date ,
394 source_int_id ,
395 entity_code ,
396 event_id ,
397 from_application)
398 SELECT DISTINCT gl_date,
399 cash_receipt_id,
400 p_entity_code,
401 event_id,
402 'AR'
403 FROM
404 (SELECT gl_date gl_date,
405 cash_receipt_id cash_receipt_id,
406 event_id event_id
407 FROM ar_cash_receipt_history crh
408 WHERE cash_receipt_id = p_source_int_id
409 UNION ALL
410 SELECT ra.gl_date gl_date,
411 ra.cash_receipt_id cash_receipt_id,
412 ra.event_id event_id
413 FROM ar_receivable_applications ra
414 WHERE ra.cash_receipt_id = p_source_int_id
415 AND ra.status = 'APP');
416
417
418 ELSIF p_entity_code = 'ADJUSTMENTS' THEN
419
420 INSERT INTO ar_detect_gt(gl_date ,
421 source_int_id ,
422 entity_code ,
423 event_id ,
424 from_application)
425 SELECT gl_date,
426 adjustment_id,
427 p_entity_code,
428 event_id,
429 'AR'
430 FROM ar_adjustments crh
431 WHERE adjustment_id = p_source_int_id;
432
433 ELSIF p_entity_code = 'BILLS_RECEIVABLE' THEN
434
435 INSERT INTO ar_detect_gt(gl_date ,
436 source_int_id ,
437 entity_code ,
438 event_id ,
439 from_application)
440 SELECT DISTINCT gl_date,
441 customer_trx_id,
442 p_entity_code,
443 event_id,
444 'AR'
445 FROM ar_transaction_history crh
446 WHERE customer_trx_id = p_source_int_id;
447
448
449 END IF;
450 arp_standard.debug('get_ar_trx_event_info -');
451 END;
452
453
454
455
456 PROCEDURE get_xla_event_info
457 (p_entity_code IN VARCHAR2,
458 p_source_int_id IN NUMBER)
459 IS
460 BEGIN
461 arp_standard.debug('get_xla_event_info +');
462 arp_standard.debug(' p_entity_code :'||p_entity_code);
463 arp_standard.debug(' p_source_int_id :'||p_source_int_id);
464
465 INSERT INTO ar_detect_gt(gl_date ,
466 source_int_id ,
467 entity_code ,
468 event_id ,
469 from_application)
470 SELECT e.event_date,
471 t.source_id_int_1,
472 t.entity_code,
473 e.event_id,
474 'XLA'
475 FROM xla_events e,
476 xla_transaction_entities_upg t
477 WHERE t.application_id = 222
478 AND t.ledger_id = arp_global.set_of_books_id
479 AND t.entity_code = p_entity_code
480 AND nvl(t.source_id_int_1,-99) = p_source_int_id
481 AND t.entity_id = e.entity_id
482 AND e.application_id = 222;
483
484 arp_standard.debug('get_xla_event_info -');
485 END;
486
487
488 PROCEDURE ar_event_existence
489 (p_entity_code IN VARCHAR2,
490 p_source_int_id IN NUMBER,
491 x_result OUT NOCOPY VARCHAR2)
492 IS
493 CURSOR c IS
494 SELECT gl_date
495 FROM ar_detect_gt
496 WHERE source_int_id = p_source_int_id
497 AND entity_code = p_entity_code
498 AND from_application = 'AR'
499 AND event_id IS NULL;
500
501 l_date DATE;
502 BEGIN
503 arp_standard.debug('ar_event_existence +');
504 arp_standard.debug(' p_entity_code :'||p_entity_code);
505 arp_standard.debug(' p_source_int_id :'||p_source_int_id);
506 OPEN c;
507 FETCH c INTO l_date;
508 IF c%NOTFOUND THEN
509 x_result := 'Y';
510 ELSE
511 x_result := 'N';
512 END IF;
513 CLOSE c;
514 arp_standard.debug(' x_result :'||x_result);
515 arp_standard.debug('ar_event_existence -');
516 END;
517
518
519
520 PROCEDURE ar_xla_all_match_exist
521 (p_entity_code IN VARCHAR2,
522 p_source_int_id IN NUMBER,
523 x_result OUT NOCOPY VARCHAR2)
524 IS
525 CURSOR c IS
526 SELECT a1.gl_date
527 FROM ar_detect_gt a1
528 WHERE a1.source_int_id = p_source_int_id
529 AND a1.entity_code = p_entity_code
530 AND a1.from_application = 'AR'
531 AND NOT EXISTS
532 (SELECT a2.gl_date
533 FROM ar_detect_gt a2
534 WHERE a2.source_int_id = a1.source_int_id
535 AND a2.entity_code = a1.entity_code
536 AND a2.from_application = 'XLA'
537 AND a2.gl_date = a1.gl_date);
538 l_date DATE;
539 BEGIN
540 arp_standard.debug('ar_xla_all_match_exist +');
541 OPEN c;
542 FETCH c INTO l_date;
543 IF c%NOTFOUND THEN
544 x_result := 'Y';
545 ELSE
546 x_result := 'N';
547 END IF;
548 CLOSE c;
549 arp_standard.debug(' l_date :'||l_date);
550 arp_standard.debug(' x_result :'||x_result);
551 arp_standard.debug('ar_xla_all_match_exist -');
552 END;
553
554
555
556
557 PROCEDURE upgrade_status_per_doc
558 (p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
559 p_entity_code IN VARCHAR2,
560 p_source_int_id IN NUMBER,
561 x_upgrade_status OUT NOCOPY VARCHAR2,
562 x_return_status OUT NOCOPY VARCHAR2,
563 x_msg_count OUT NOCOPY NUMBER,
564 x_msg_data OUT NOCOPY VARCHAR2)
565 IS
566 x_result VARCHAR2(30);
567 end_execution EXCEPTION;
568 BEGIN
569 arp_standard.debug('upgrade_status_per_doc +');
570 arp_standard.debug(' p_entity_code :'||p_entity_code);
571 arp_standard.debug(' p_source_int_id:'||p_source_int_id);
572 x_return_status := fnd_api.g_ret_sts_success;
573
574 -- initialize message list if p_init_msg_list is set to TRUE.
575 IF FND_API.to_Boolean(p_init_msg_list) THEN
576 FND_MSG_PUB.initialize;
577 END IF;
578
579 IF p_entity_code NOT IN
580 ('TRANSACTIONS','RECEIPTS',
581 'ADJUSTMENTS', 'BILLS_RECEIVABLE')
582 THEN
583 arp_standard.debug('Value for entity code should be TRANSACTIONS RECEIPTS ADJUSTMENTS BILLS_RECEIVABLE');
584 FND_MESSAGE.SET_NAME( 'AR', 'AR_ONLY_VALUE_ALLOWED' );
585 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'ENTITY_CODE' );
586 FND_MESSAGE.SET_TOKEN( 'VALUES', 'TRANSACTIONS,RECEIPTS,ADJUSTMENTS,BILLS_RECEIVABLE');
587 FND_MSG_PUB.ADD;
588 x_return_status := FND_API.G_RET_STS_ERROR;
589 RAISE fnd_api.G_EXC_ERROR;
590 END IF;
591
592 DELETE FROM ar_detect_gt
593 WHERE entity_code = p_entity_code
594 AND source_int_id = p_source_int_id;
595
596 -- 1 Get AR info
597 get_ar_trx_event_info
598 (p_entity_code => p_entity_code,
599 p_source_int_id => p_source_int_id);
600
601 -- 2 check if all AR infor has the event_id
602 ar_event_existence
603 (p_entity_code => p_entity_code,
604 p_source_int_id => p_source_int_id,
605 x_result => x_result);
606
607 IF x_result = 'Y' THEN
608 x_upgrade_status := 'Y';
609 RAISE end_execution;
610 END IF;
611
612 -- 3 Get XLA events
613 get_xla_event_info
614 (p_entity_code => p_entity_code,
615 p_source_int_id => p_source_int_id);
616
617 -- 4 Check if all gl date has a event
618 ar_xla_all_match_exist
619 (p_entity_code => p_entity_code,
620 p_source_int_id => p_source_int_id,
621 x_result => x_result);
622
623 IF x_result = 'Y' THEN
624 x_upgrade_status := 'Y';
625 ELSE
626 x_upgrade_status := 'N';
627 END IF;
628
629 arp_standard.debug(' x_upgrade_status :'||x_upgrade_status);
630 arp_standard.debug('upgrade_status_per_doc -');
631 EXCEPTION
632 WHEN end_execution THEN
633 arp_standard.debug(' x_upgrade_status :'||x_upgrade_status);
634 WHEN fnd_api.G_EXC_ERROR THEN
635 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
636 p_count => x_msg_count,
637 p_data => x_msg_data);
638 WHEN OTHERS THEN
639 arp_standard.debug('EXCEPTION OTHERS in upgrade_status_per_doc:'||SQLERRM);
640 x_return_status := fnd_api.g_ret_sts_unexp_error;
641 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
642 fnd_message.set_token('ERROR' ,SQLERRM);
643 fnd_msg_pub.add;
644 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
645 p_count => x_msg_count,
646 p_data => x_msg_data);
647 END;
648
649
650
651
652 PROCEDURE r12_adj_in_xla
653 (p_start_date IN DATE,
654 p_end_date IN DATE,
655 p_org_id IN NUMBER DEFAULT NULL,
656 x_return_status OUT NOCOPY VARCHAR2,
657 x_msg_count OUT NOCOPY NUMBER,
658 x_msg_data OUT NOCOPY VARCHAR2)
659 IS
660 CURSOR c_adj(p_start_date IN DATE,
661 p_end_date IN DATE,
662 p_org_id IN NUMBER)
663 IS
664 SELECT adj.adjustment_id
665 FROM ar_distributions_all ard,
666 ar_adjustments_all adj
667 WHERE ard.source_table = 'ADJ'
668 AND ard.source_id = adj.adjustment_id
669 AND adj.gl_date BETWEEN p_start_date AND p_end_date
670 AND adj.posting_control_id = -3
671 AND NVL(p_org_id,adj.org_id) = adj.org_id
672 AND adj.status = 'A'
673 AND NOT EXISTS
674 (SELECT NULL
675 FROM xla_distribution_links lk,
676 xla_ae_lines ae
677 WHERE ard.line_id = lk.source_distribution_id_num_1
678 AND lk.application_id = 222
679 AND lk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
680 AND ae.application_id = 222
681 AND lk.ae_header_id = ae.ae_header_id
682 AND lk.ae_line_num = ae.ae_line_num);
683 l_adj_id NUMBER;
684 l_text VARCHAR2(2000);
685 BEGIN
686 x_return_status := FND_API.G_RET_STS_SUCCESS;
687 validate_parameter
688 (p_start_date => p_start_date,
689 p_end_date => p_end_date,
690 p_org_id => p_org_id,
691 p_type => 'ADJ',
692 p_entity => 'AR_ADJUSTMENTS_ALL',
693 x_return_status => x_return_status);
694 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
695 RAISE fnd_api.g_exc_error;
696 END IF;
697 OPEN c_adj(p_start_date, p_end_date,p_org_id);
698 FETCH c_adj INTO l_adj_id;
699 IF c_adj%FOUND THEN
700 l_text := ' There is at least one adjustment not posted and does not have a xla distribution paired.
701 That is the adjustment does not have accounting created - adjustment_id :'||l_adj_id;
702 log(l_text);
703 FND_MESSAGE.SET_NAME('AR','AR_SUBMIT_ACCT_REQ');
704 FND_MSG_PUB.ADD;
705 x_return_status := FND_API.G_RET_STS_ERROR;
706 END IF;
707 CLOSE c_adj;
708 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
709 RAISE fnd_api.g_exc_error;
710 END IF;
711 EXCEPTION
712 WHEN fnd_api.g_exc_error THEN
713 fnd_msg_pub.count_and_get(
714 p_encoded => fnd_api.g_false,
715 p_count => x_msg_count,
716 p_data => x_msg_data);
717 log(x_msg_data);
718 WHEN OTHERS THEN
719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
720 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
721 FND_MESSAGE.SET_TOKEN('ERROR' ,'Insert_Head_Row:'||SQLERRM);
722 FND_MSG_PUB.ADD;
723 FND_MSG_PUB.Count_And_Get(
724 p_encoded => FND_API.G_FALSE,
725 p_count => x_msg_count,
726 p_data => x_msg_data);
727 log(x_msg_data);
728 END;
729
730
731 --
732 --
733 -- p_type INV, CM, CB, DM, DEP, GUAR, ALL ,INVDEPGUAR
734 --
735 --
736 PROCEDURE r12_trx_in_xla
737 (p_start_date IN DATE,
738 p_end_date IN DATE,
739 p_type IN VARCHAR2,
740 p_org_id IN NUMBER DEFAULT NULL,
741 x_return_status OUT NOCOPY VARCHAR2,
742 x_msg_count OUT NOCOPY NUMBER,
743 x_msg_data OUT NOCOPY VARCHAR2)
744 IS
745 CURSOR c_trx(p_start_date IN DATE,
746 p_end_date IN DATE,
747 p_org_id IN NUMBER,
748 p_type IN VARCHAR2)
749 IS
750 SELECT ctlgd.customer_trx_id
751 FROM ra_cust_trx_line_gl_dist_all ctlgd,
752 ra_customer_trx_all trx,
753 ra_cust_trx_types_all tty
754 WHERE ctlgd.gl_date BETWEEN p_start_date AND p_end_date
755 AND ctlgd.posting_control_id = -3
756 AND NVL(p_org_id,ctlgd.org_id) = p_org_id
757 AND ctlgd.account_set_flag = 'N'
758 AND ctlgd.customer_trx_id = trx.customer_trx_id
759 AND trx.cust_trx_type_id = tty.cust_trx_type_id
760 AND tty.org_id = trx.org_id
761 AND tty.post_to_gl = 'Y'
762 AND DECODE(p_type,
763 'ALL',tty.type,
764 'INVDEPGUAR',DECODE( tty.type,'INV','INV',
765 'DEP','DEP',
766 'GUAR','GUAR','EXCLUDE'),
767 p_type) = tty.type
768 AND NOT EXISTS
769 (SELECT NULL
770 FROM xla_distribution_links lk,
771 xla_ae_lines ae
772 WHERE ctlgd.cust_trx_line_gl_dist_id = lk.source_distribution_id_num_1
773 AND lk.application_id = 222
774 AND lk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
775 AND ae.application_id = 222
776 AND lk.ae_header_id = ae.ae_header_id
777 AND lk.ae_line_num = ae.ae_line_num);
778 l_trx_id NUMBER;
779 l_text VARCHAR2(2000);
780 l_tag VARCHAR2(80);
781 BEGIN
782 x_return_status := FND_API.G_RET_STS_SUCCESS;
783 validate_parameter
784 (p_start_date => p_start_date,
785 p_end_date => p_end_date,
786 p_org_id => p_org_id,
787 p_type => p_type,
788 p_entity => 'RA_CUST_TRX_LINE_GL_DIST_ALL',
789 x_return_status => x_return_status);
790 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
791 RAISE fnd_api.g_exc_error;
792 END IF;
793 IF p_type = 'INV' THEN l_tag := 'Sales Invoice';
794 ELSIF p_type = 'CM' THEN l_tag := 'Credit Memo';
795 ELSIF p_type = 'DM' THEN l_tag := 'Debit Memo';
796 ELSIF p_type = 'DEP' THEN l_tag := 'Deposite';
797 ELSIF p_type = 'GUAR' THEN l_tag := 'Guarantee';
798 ELSE l_tag := 'Transaction';
799 END IF;
800 OPEN c_trx(p_start_date,p_end_date,p_org_id, p_type);
801 FETCH c_trx INTO l_trx_id;
802 IF c_trx%FOUND THEN
803 l_text := ' There is at least one '|| l_tag ||' not posted but does not have at least xla distribution paired.
804 The '|| l_tag ||' does not have accounting created - customer_trx_id :'||l_trx_id;
805 log(l_text);
806 FND_MESSAGE.SET_NAME('AR','AR_SUBMIT_ACCT_REQ');
807 FND_MSG_PUB.ADD;
808 x_return_status := FND_API.G_RET_STS_ERROR;
809 END IF;
810 CLOSE c_trx;
811 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
812 RAISE fnd_api.g_exc_error;
813 END IF;
814 EXCEPTION
815 WHEN fnd_api.g_exc_error THEN
816 fnd_msg_pub.count_and_get(
817 p_encoded => fnd_api.g_false,
818 p_count => x_msg_count,
819 p_data => x_msg_data);
820 log(x_msg_data);
821 WHEN OTHERS THEN
822 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
823 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
824 FND_MESSAGE.SET_TOKEN('ERROR' ,'Insert_Head_Row:'||SQLERRM);
825 FND_MSG_PUB.ADD;
826 FND_MSG_PUB.Count_And_Get(
827 p_encoded => FND_API.G_FALSE,
828 p_count => x_msg_count,
829 p_data => x_msg_data);
830 log(x_msg_data);
831 END;
832
833
834 --
835 -- p_type CASH, MISC, ALL
836 --
837 PROCEDURE r12_crh_in_xla
838 (p_start_date IN DATE,
839 p_end_date IN DATE,
840 p_org_id IN NUMBER DEFAULT NULL,
841 p_type IN VARCHAR2 DEFAULT 'ALL',
842 x_return_status OUT NOCOPY VARCHAR2,
843 x_msg_count OUT NOCOPY NUMBER,
844 x_msg_data OUT NOCOPY VARCHAR2)
845 IS
846 CURSOR c_recp(p_start_date IN DATE,
847 p_end_date IN DATE,
848 p_org_id IN NUMBER,
849 p_type IN VARCHAR2)
850 IS
851 SELECT crh.cash_receipt_id
852 FROM ar_distributions_all ard,
853 ar_cash_receipt_history_all crh,
854 ar_cash_receipts_all cr
855 WHERE ard.source_table = 'CRH'
856 AND ard.source_id = crh.cash_receipt_history_id
857 AND crh.gl_date BETWEEN p_start_date AND p_end_date
858 AND crh.posting_control_id = -3
859 AND NVL(p_org_id,crh.org_id) = crh.org_id
860 AND crh.cash_receipt_id = cr.cash_receipt_id
861 AND DECODE(p_type,
862 'ALL',cr.type,
863 p_type) = cr.type
864 AND NOT EXISTS
865 (SELECT NULL
866 FROM xla_distribution_links lk,
867 xla_ae_lines ae
868 WHERE ard.line_id = lk.source_distribution_id_num_1
869 AND lk.application_id = 222
870 AND lk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
871 AND ae.application_id = 222
872 AND lk.ae_header_id = ae.ae_header_id
873 AND lk.ae_line_num = ae.ae_line_num);
874 l_cr_id NUMBER;
875 l_text VARCHAR2(2000);
876 l_tag VARCHAR2(80);
877 BEGIN
878 x_return_status := FND_API.G_RET_STS_SUCCESS;
879 validate_parameter
880 (p_start_date => p_start_date,
881 p_end_date => p_end_date,
882 p_org_id => p_org_id,
883 p_type => p_type,
884 p_entity => 'AR_CASH_RECEIPT_HISTORY_ALL',
885 x_return_status => x_return_status);
886 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
887 RAISE fnd_api.g_exc_error;
888 END IF;
889 IF p_type = 'CASH' THEN
890 l_tag := 'Trade Receipt';
891 ELSE
892 l_tag := 'Miscellenaous Receipt';
893 END IF;
894 OPEN c_recp(p_start_date,p_end_date, p_org_id,p_type);
895 FETCH c_recp INTO l_cr_id;
896 IF c_recp%FOUND THEN
897 l_text := ' There is at least one '|| l_tag ||' not posted and does not have a xla distribution paired.
898 That is the '|| l_tag ||' does not have accounting created - cash_receipt_id :'||l_cr_id;
899 log(l_text);
900 FND_MESSAGE.SET_NAME('AR','AR_SUBMIT_ACCT_REQ');
901 FND_MSG_PUB.ADD;
902 x_return_status := FND_API.G_RET_STS_ERROR;
903 END IF;
904 CLOSE c_recp;
905 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
906 RAISE fnd_api.g_exc_error;
907 END IF;
908 EXCEPTION
909 WHEN fnd_api.g_exc_error THEN
910 fnd_msg_pub.count_and_get(
911 p_encoded => fnd_api.g_false,
912 p_count => x_msg_count,
913 p_data => x_msg_data);
914 log(x_msg_data);
915 WHEN OTHERS THEN
916 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
917 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
918 FND_MESSAGE.SET_TOKEN('ERROR' ,'Insert_Head_Row:'||SQLERRM);
919 FND_MSG_PUB.ADD;
920 FND_MSG_PUB.Count_And_Get(
921 p_encoded => FND_API.G_FALSE,
922 p_count => x_msg_count,
923 p_data => x_msg_data);
924 log(x_msg_data);
925 END;
926
927
928
929
930 --
931 -- p_type APP, CMAPP, ALL
932 --
933 PROCEDURE r12_app_in_xla
934 (p_start_date IN DATE,
935 p_end_date IN DATE,
936 p_org_id IN NUMBER DEFAULT NULL,
937 p_type IN VARCHAR2 DEFAULT 'ALL',
938 x_return_status OUT NOCOPY VARCHAR2,
939 x_msg_count OUT NOCOPY NUMBER,
940 x_msg_data OUT NOCOPY VARCHAR2)
941 IS
942 CURSOR c_app(p_start_date IN DATE,
943 p_end_date IN DATE,
944 p_org_id IN NUMBER,
945 p_type IN VARCHAR2)
946 IS
947 SELECT ra.receivable_application_id
948 FROM ar_distributions_all ard,
949 ar_receivable_applications_all ra
950 WHERE ard.source_table = 'RA'
951 AND ra.status = 'APP'
952 AND ard.source_id = ra.receivable_application_id
953 AND ra.gl_date BETWEEN p_start_date AND p_end_date
954 AND ra.posting_control_id = -3
955 AND NVL(p_org_id,ra.org_id) = ra.org_id
956 AND DECODE(p_type,'ALL',p_type,
957 DECODE(ra.cash_receipt_id,NULL,
958 DECODE(ra.customer_trx_id,NULL,NULL,'CMAPP'),
959 'APP')) = p_type
960 AND NOT EXISTS
961 (SELECT NULL
962 FROM xla_distribution_links lk,
963 xla_ae_lines ae
964 WHERE ard.line_id = lk.source_distribution_id_num_1
965 AND lk.application_id = 222
966 AND lk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
967 AND ae.application_id = 222
968 AND lk.ae_header_id = ae.ae_header_id
969 AND lk.ae_line_num = ae.ae_line_num);
970 l_ra_id NUMBER;
971 l_text VARCHAR2(2000);
972 l_tag VARCHAR2(80);
973 BEGIN
974 x_return_status := FND_API.G_RET_STS_SUCCESS;
975 validate_parameter
976 (p_start_date => p_start_date,
977 p_end_date => p_end_date,
978 p_org_id => p_org_id,
979 p_type => p_type,
980 p_entity => 'AR_RECEIVABLE_APPLICATIONS_ALL',
981 x_return_status => x_return_status);
982 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
983 RAISE fnd_api.g_exc_error;
984 END IF;
985 IF p_type = 'APP' THEN
986 l_tag := 'Cash Receipt Application';
987 ELSIF p_type = 'CMAPP' THEN
988 l_tag := 'Credit Memo Application';
989 ELSE
990 l_tag := 'Application';
991 END IF;
992 OPEN c_app(p_start_date,p_end_date, p_org_id, p_type);
993 FETCH c_app INTO l_ra_id;
994 IF c_app%FOUND THEN
995 l_text := ' There is at least one '||l_tag||' not posted and does not have a xla distribution paired.
996 That is the '|| l_tag ||' does not have accounting created - receivable_application_id :'||l_ra_id;
997 log(l_text);
998 FND_MESSAGE.SET_NAME('AR','AR_SUBMIT_ACCT_REQ');
999 FND_MSG_PUB.ADD;
1000 x_return_status := FND_API.G_RET_STS_ERROR;
1001 END IF;
1002 CLOSE c_app;
1003 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1004 RAISE fnd_api.g_exc_error;
1005 END IF;
1006 EXCEPTION
1007 WHEN fnd_api.g_exc_error THEN
1008 fnd_msg_pub.count_and_get(
1009 p_encoded => fnd_api.g_false,
1010 p_count => x_msg_count,
1011 p_data => x_msg_data);
1012 log(x_msg_data);
1013 WHEN OTHERS THEN
1014 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1015 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1016 FND_MESSAGE.SET_TOKEN('ERROR' ,'Insert_Head_Row:'||SQLERRM);
1017 FND_MSG_PUB.ADD;
1018 FND_MSG_PUB.Count_And_Get(
1019 p_encoded => FND_API.G_FALSE,
1020 p_count => x_msg_count,
1021 p_data => x_msg_data);
1022 log(x_msg_data);
1023 END;
1024
1025
1026
1027 PROCEDURE r12_th_in_xla
1028 (p_start_date IN DATE,
1029 p_end_date IN DATE,
1030 p_org_id IN NUMBER DEFAULT NULL,
1031 x_return_status OUT NOCOPY VARCHAR2,
1032 x_msg_count OUT NOCOPY NUMBER,
1033 x_msg_data OUT NOCOPY VARCHAR2)
1034 IS
1035 CURSOR c(p_start_date IN DATE,
1036 p_end_date IN DATE,
1037 p_org_id IN NUMBER)
1038 IS
1039 SELECT th.customer_trx_id
1040 FROM ar_distributions_all ard,
1041 ar_transaction_history_all th
1042 WHERE ard.source_table = 'TH'
1043 AND ard.source_id = th.transaction_history_id
1044 AND th.gl_date BETWEEN p_start_date AND p_end_date
1045 AND th.posting_control_id = -3
1046 AND NVL(p_org_id,th.org_id) = th.org_id
1047 AND NOT EXISTS
1048 (SELECT NULL
1049 FROM xla_distribution_links lk,
1050 xla_ae_lines ae
1051 WHERE ard.line_id = lk.source_distribution_id_num_1
1052 AND lk.application_id = 222
1053 AND lk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
1054 AND ae.application_id = 222
1055 AND lk.ae_header_id = ae.ae_header_id
1056 AND lk.ae_line_num = ae.ae_line_num);
1057 l_trx_id NUMBER;
1058 l_text VARCHAR2(2000);
1059 BEGIN
1060 x_return_status := FND_API.G_RET_STS_SUCCESS;
1061 validate_parameter
1062 (p_start_date => p_start_date,
1063 p_end_date => p_end_date,
1064 p_org_id => p_org_id,
1065 p_type => 'BILL',
1066 p_entity => 'AR_TRANSACTION_HISTORY_ALL',
1067 x_return_status => x_return_status);
1068 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1069 RAISE fnd_api.g_exc_error;
1070 END IF;
1071 OPEN c(p_start_date, p_end_date,p_org_id);
1072 FETCH c INTO l_trx_id;
1073 IF c%FOUND THEN
1074 l_text := ' There is at least one bill not posted and does not have a xla distribution paired.
1075 That is the bill does not have accounting created - customer_trx_id :'||l_trx_id;
1076 log(l_text);
1077 FND_MESSAGE.SET_NAME('AR','AR_SUBMIT_ACCT_REQ');
1078 FND_MSG_PUB.ADD;
1079 x_return_status := FND_API.G_RET_STS_ERROR;
1080 END IF;
1081 CLOSE c;
1082 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1083 RAISE fnd_api.g_exc_error;
1084 END IF;
1085 EXCEPTION
1086 WHEN fnd_api.g_exc_error THEN
1087 fnd_msg_pub.count_and_get(
1088 p_encoded => fnd_api.g_false,
1089 p_count => x_msg_count,
1090 p_data => x_msg_data);
1091 log(x_msg_data);
1092 WHEN OTHERS THEN
1093 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1094 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1095 FND_MESSAGE.SET_TOKEN('ERROR' ,'Insert_Head_Row:'||SQLERRM);
1096 FND_MSG_PUB.ADD;
1097 FND_MSG_PUB.Count_And_Get(
1098 p_encoded => FND_API.G_FALSE,
1099 p_count => x_msg_count,
1100 p_data => x_msg_data);
1101 log(x_msg_data);
1102 END;
1103
1104
1105
1106 PROCEDURE r12_dist_in_xla
1107 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1108 p_start_date IN DATE,
1109 p_end_date IN DATE,
1110 p_xla_post_status IN VARCHAR2 DEFAULT 'Y',
1111 p_inv_flag IN VARCHAR2 DEFAULT 'Y',
1112 p_dm_flag IN VARCHAR2 DEFAULT 'Y',
1113 p_cb_flag IN VARCHAR2 DEFAULT 'Y',
1114 p_cm_flag IN VARCHAR2 DEFAULT 'Y',
1115 p_cmapp_flag IN VARCHAR2 DEFAULT 'Y',
1116 p_adj_flag IN VARCHAR2 DEFAULT 'Y',
1117 p_recp_flag IN VARCHAR2 DEFAULT 'Y',
1118 p_misc_flag IN VARCHAR2 DEFAULT 'Y',
1119 p_bill_flag IN VARCHAR2 DEFAULT 'Y',
1120 p_org_id IN NUMBER DEFAULT NULL,
1121 x_return_status OUT NOCOPY VARCHAR2,
1122 x_msg_count OUT NOCOPY NUMBER,
1123 x_msg_data OUT NOCOPY VARCHAR2)
1124 IS
1125 l_return_status VARCHAR2(10);
1126 l_msg_count NUMBER;
1127 l_msg_data VARCHAR2(2000);
1128 only_posted_jle EXCEPTION;
1129 BEGIN
1130 log('r12_dist_in_xla +');
1131 log(' p_start_date :'||p_start_date);
1132 log(' p_end_date :'||p_end_date);
1133 log(' p_xla_post_status :'||p_xla_post_status);
1134 log(' p_inv_flag :'||p_inv_flag);
1135 log(' p_dm_flag :'||p_dm_flag);
1136 log(' p_cb_flag :'||p_cb_flag);
1137 log(' p_cm_flag :'||p_cm_flag);
1138 log(' p_cmapp_flag :'||p_cmapp_flag);
1139 log(' p_adj_flag :'||p_adj_flag);
1140 log(' p_recp_flag :'||p_recp_flag);
1141 log(' p_misc_flag :'||p_misc_flag);
1142 log(' p_bill_flag :'||p_bill_flag);
1143 log(' p_org_id :'||p_org_id);
1144 x_return_status := fnd_api.g_ret_sts_success;
1145 l_return_status := fnd_api.g_ret_sts_success;
1146 IF fnd_api.to_boolean(p_init_msg_list) THEN
1147 fnd_msg_pub.initialize;
1148 END IF;
1149 IF p_xla_post_status = 'Y' THEN
1150 RAISE only_posted_jle;
1151 END IF;
1152 validate_parameter
1153 (p_start_date => p_start_date,
1154 p_end_date => p_end_date,
1155 p_org_id => p_org_id,
1156 p_type => 'NOT_SPECIFIED',
1157 p_entity => 'NOT_SPECIFIED',
1158 x_return_status => l_return_status);
1159 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1160 RAISE fnd_api.g_exc_error;
1161 END IF;
1162 --
1163 --Running for transactions
1164 --
1165 IF (p_inv_flag = 'Y' AND p_dm_flag = 'Y' AND p_cb_flag = 'Y' AND p_cm_flag = 'Y') THEN
1166 r12_trx_in_xla
1167 (p_start_date => p_start_date,
1168 p_end_date => p_end_date,
1169 p_type => 'ALL',
1170 p_org_id => p_org_id,
1171 x_return_status => l_return_status,
1172 x_msg_count => l_msg_count,
1173 x_msg_data => l_msg_data);
1174 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1175 x_return_status := l_return_status;
1176 RAISE fnd_api.g_exc_error;
1177 END IF;
1178 ELSE
1179 IF (p_inv_flag = 'Y') THEN
1180 r12_trx_in_xla
1181 (p_start_date => p_start_date,
1182 p_end_date => p_end_date,
1183 p_type => 'INVDEPGUAR',
1184 p_org_id => p_org_id,
1185 x_return_status => l_return_status,
1186 x_msg_count => l_msg_count,
1187 x_msg_data => l_msg_data);
1188 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1189 x_return_status := l_return_status;
1190 RAISE fnd_api.g_exc_error;
1191 END IF;
1192 END IF;
1193 IF (p_dm_flag = 'Y') THEN
1194 r12_trx_in_xla
1195 (p_start_date => p_start_date,
1196 p_end_date => p_end_date,
1197 p_type => 'DM',
1198 p_org_id => p_org_id,
1199 x_return_status => l_return_status,
1200 x_msg_count => l_msg_count,
1201 x_msg_data => l_msg_data);
1202 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1203 x_return_status := l_return_status;
1204 RAISE fnd_api.g_exc_error;
1205 END IF;
1206 END IF;
1207 IF (p_cb_flag = 'Y') THEN
1208 r12_trx_in_xla
1209 (p_start_date => p_start_date,
1210 p_end_date => p_end_date,
1211 p_type => 'CB',
1212 p_org_id => p_org_id,
1213 x_return_status => l_return_status,
1214 x_msg_count => l_msg_count,
1215 x_msg_data => l_msg_data);
1216 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1217 x_return_status := l_return_status;
1218 RAISE fnd_api.g_exc_error;
1219 END IF;
1220 END IF;
1221 IF (p_cm_flag = 'Y') THEN
1222 r12_trx_in_xla
1223 (p_start_date => p_start_date,
1224 p_end_date => p_end_date,
1225 p_type => 'CM',
1226 p_org_id => p_org_id,
1227 x_return_status => l_return_status,
1228 x_msg_count => l_msg_count,
1229 x_msg_data => l_msg_data);
1230 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1231 x_return_status := l_return_status;
1232 RAISE fnd_api.g_exc_error;
1233 END IF;
1234 END IF;
1235 END IF;
1236 --
1237 --Running for adjustment
1238 --
1239 IF p_adj_flag = 'Y' THEN
1240 r12_adj_in_xla
1241 (p_start_date => p_start_date,
1242 p_end_date => p_end_date,
1243 p_org_id => p_org_id,
1244 x_return_status => l_return_status,
1245 x_msg_count => l_msg_count,
1246 x_msg_data => l_msg_data);
1247 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1248 x_return_status := l_return_status;
1249 RAISE fnd_api.g_exc_error;
1250 END IF;
1251 END IF;
1252 --
1253 --Running for receipts
1254 --
1255 IF p_recp_flag = 'Y' AND p_misc_flag = 'Y' THEN
1256 r12_crh_in_xla
1257 (p_start_date => p_start_date,
1258 p_end_date => p_end_date,
1259 p_org_id => p_org_id,
1260 p_type => 'ALL',
1261 x_return_status => l_return_status,
1262 x_msg_count => l_msg_count,
1263 x_msg_data => l_msg_data);
1264 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1265 x_return_status := l_return_status;
1266 RAISE fnd_api.g_exc_error;
1267 END IF;
1268 ELSE
1269 IF p_recp_flag = 'Y' THEN
1270 r12_crh_in_xla
1271 (p_start_date => p_start_date,
1272 p_end_date => p_end_date,
1273 p_org_id => p_org_id,
1274 p_type => 'CASH',
1275 x_return_status => l_return_status,
1276 x_msg_count => l_msg_count,
1277 x_msg_data => l_msg_data);
1278 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1279 x_return_status := l_return_status;
1280 RAISE fnd_api.g_exc_error;
1281 END IF;
1282 END IF;
1283 IF p_misc_flag = 'Y' THEN
1284 r12_crh_in_xla
1285 (p_start_date => p_start_date,
1286 p_end_date => p_end_date,
1287 p_org_id => p_org_id,
1288 p_type => 'MISC',
1289 x_return_status => l_return_status,
1290 x_msg_count => l_msg_count,
1291 x_msg_data => l_msg_data);
1292 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1293 x_return_status := l_return_status;
1294 RAISE fnd_api.g_exc_error;
1295 END IF;
1296 END IF;
1297 END IF;
1298 --
1299 --Running for applications
1300 --
1301 IF p_recp_flag = 'Y' AND p_cmapp_flag = 'Y' THEN
1302 r12_app_in_xla
1303 (p_start_date => p_start_date,
1304 p_end_date => p_end_date,
1305 p_org_id => p_org_id,
1306 p_type => 'ALL',
1307 x_return_status => l_return_status,
1308 x_msg_count => l_msg_count,
1309 x_msg_data => l_msg_data);
1310 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1311 x_return_status := l_return_status;
1312 RAISE fnd_api.g_exc_error;
1313 END IF;
1314 ELSE
1315 IF p_recp_flag = 'Y' THEN
1316 r12_app_in_xla
1317 (p_start_date => p_start_date,
1318 p_end_date => p_end_date,
1319 p_org_id => p_org_id,
1320 p_type => 'APP',
1321 x_return_status => l_return_status,
1322 x_msg_count => l_msg_count,
1323 x_msg_data => l_msg_data);
1324 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1325 x_return_status := l_return_status;
1326 RAISE fnd_api.g_exc_error;
1327 END IF;
1328 END IF;
1329 IF p_cmapp_flag = 'Y' THEN
1330 r12_app_in_xla
1331 (p_start_date => p_start_date,
1332 p_end_date => p_end_date,
1333 p_org_id => p_org_id,
1334 p_type => 'CMAPP',
1335 x_return_status => l_return_status,
1336 x_msg_count => l_msg_count,
1337 x_msg_data => l_msg_data);
1338 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1339 x_return_status := l_return_status;
1340 RAISE fnd_api.g_exc_error;
1341 END IF;
1342 END IF;
1343 END IF;
1344 --
1345 --Running for bill
1346 --
1347 IF p_bill_flag = 'Y' THEN
1348 r12_th_in_xla
1349 (p_start_date => p_start_date,
1350 p_end_date => p_end_date,
1351 p_org_id => p_org_id,
1352 x_return_status => l_return_status,
1353 x_msg_count => l_msg_count,
1354 x_msg_data => l_msg_data);
1355 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1356 x_return_status := l_return_status;
1357 RAISE fnd_api.g_exc_error;
1358 END IF;
1359 END IF;
1360 log('r12_dist_in_xla -');
1361 EXCEPTION
1362 WHEN only_posted_jle THEN
1363 log('the user wants to see only posted documents, no check to verify data in XLA');
1364 WHEN fnd_api.g_exc_error THEN
1365 x_msg_count := l_msg_count;
1366 IF l_msg_count > 1 THEN
1367 FOR i IN 1..l_msg_count LOOP
1368 l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
1369 x_msg_data := x_msg_data||'-'||l_msg_data;
1370 log(l_msg_data);
1371 END LOOP;
1372 ELSE
1373 x_msg_data := l_msg_data;
1374 END IF;
1375 WHEN OTHERS THEN
1376 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1377 log('EXCEPTION OTHERS in r12_dist_in_xla :'||SQLERRM);
1378 END;
1379
1380
1381
1382 PROCEDURE update_cr_dist
1383 ( p_ledger_id IN NUMBER
1384 ,p_source_id_int_1 IN NUMBER
1385 ,p_third_party_merge_date IN DATE
1386 ,p_original_third_party_id IN NUMBER
1387 ,p_original_site_id IN NUMBER
1388 ,p_new_third_party_id IN NUMBER
1389 ,p_new_site_id IN NUMBER
1390 ,p_create_update IN VARCHAR2 DEFAULT 'U'
1391 ,p_entity_code IN VARCHAR2 DEFAULT 'RECEIPTS'
1392 ,p_type_of_third_party_merge IN VARCHAR2 DEFAULT 'PARTIAL'
1393 ,p_mapping_flag IN VARCHAR2 DEFAULT 'N'
1394 ,p_execution_mode IN VARCHAR2 DEFAULT 'SYNC'
1395 ,p_accounting_mode IN VARCHAR2 DEFAULT 'F'
1396 ,p_transfer_to_gl_flag IN VARCHAR2 DEFAULT 'Y'
1397 ,p_post_in_gl_flag IN VARCHAR2 DEFAULT 'Y'
1398 ,p_third_party_type IN VARCHAR2 DEFAULT 'C'
1399 ,x_errbuf OUT NOCOPY VARCHAR2
1400 ,x_retcode OUT NOCOPY VARCHAR2
1401 ,x_event_ids OUT NOCOPY xla_third_party_merge_pub.t_event_ids
1402 ,x_request_id OUT NOCOPY NUMBER)
1403 IS
1404 creation_mode EXCEPTION;
1405 no_existing_account EXCEPTION;
1406 nullify_customer EXCEPTION;
1407 BEGIN
1408 arp_standard.debug('update_cr_dist +');
1409 arp_standard.debug(' p_ledger_id :'||p_ledger_id);
1410 arp_standard.debug(' p_source_id_int_1 :'||p_source_id_int_1);
1411 arp_standard.debug(' p_original_third_party_id:'||p_original_third_party_id);
1412 arp_standard.debug(' p_original_site_id :'||p_original_site_id);
1413 arp_standard.debug(' p_new_third_party_id :'||p_new_third_party_id);
1414 arp_standard.debug(' p_original_site_id :'||p_original_site_id);
1415 arp_standard.debug(' p_third_party_merge_date :'||p_third_party_merge_date);
1416 arp_standard.debug(' p_create_update :'||p_create_update);
1417 arp_standard.debug(' p_entity_code :'||p_entity_code);
1418 arp_standard.debug(' p_type_of_third_party_merge:'||p_type_of_third_party_merge);
1419 arp_standard.debug(' p_mapping_flag :'||p_mapping_flag);
1420 arp_standard.debug(' p_execution_mode :'||p_execution_mode);
1421 arp_standard.debug(' p_accounting_mode :'||p_accounting_mode);
1422 arp_standard.debug(' p_transfer_to_gl_flag :'||p_transfer_to_gl_flag);
1423 arp_standard.debug(' p_post_in_gl_flag :'||p_post_in_gl_flag);
1424 arp_standard.debug(' p_third_party_type :'||p_third_party_type);
1425
1426 -- No need to create a merge event in creation mode
1427 IF p_create_update = 'C' THEN
1428 arp_standard.debug('Creation mode');
1429 RAISE creation_mode;
1430 END IF;
1431
1432 -- This is when the receipt has been created a unidentified
1433 IF p_original_third_party_id IS NULL THEN
1434 arp_standard.debug('No original account');
1435 RAISE no_existing_account;
1436 END IF;
1437
1438 -- This is when user null out the customer on the receipt
1439 -- IF p_new_third_party_id IS NULL THEN
1440 -- RAISE nullify_customer;
1441 -- END IF;
1442
1443 IF p_original_third_party_id <> p_new_third_party_id OR
1444 p_new_third_party_id IS NULL
1445 THEN
1446
1447 INSERT INTO xla_events_gt
1448 (APPLICATION_ID
1449 ,LEDGER_ID
1450 ,ENTITY_CODE
1451 ,SOURCE_ID_INT_1
1452 ,VALUATION_METHOD)
1453 VALUES
1454 (222 --APPLICATION_ID
1455 ,p_ledger_id --LEDGER_ID
1456 ,p_entity_code --ENTITY_CODE
1457 ,p_source_id_int_1 --p_cash_receipt_id --SOURCE_ID_INT_1
1458 ,NULL); --VALUATION_METHOD
1459
1460 xla_third_party_merge_pub.third_party_merge
1461 ( x_errbuf => x_errbuf
1462 ,x_retcode => x_retcode
1463 ,x_event_ids => x_event_ids
1464 ,x_request_id => x_request_id
1465 ,p_source_application_id => 222
1466 ,p_application_id => 222
1467 ,p_ledger_id => p_ledger_id
1468 ,p_third_party_merge_date => p_third_party_merge_date
1469 ,p_third_party_type => p_third_party_type
1470 ,p_original_third_party_id => p_original_third_party_id
1471 ,p_original_site_id => p_original_site_id
1472 ,p_new_third_party_id => p_new_third_party_id
1473 ,p_new_site_id => p_new_site_id
1474 ,p_type_of_third_party_merge => p_type_of_third_party_merge
1475 ,p_mapping_flag => p_mapping_flag
1476 ,p_execution_mode => p_execution_mode
1477 ,p_accounting_mode => p_accounting_mode
1478 ,p_transfer_to_gl_flag => p_transfer_to_gl_flag
1479 ,p_post_in_gl_flag => p_post_in_gl_flag);
1480
1481 IF x_retcode = 'S' AND p_entity_code = 'RECEIPTS' THEN
1482 UPDATE ar_distributions
1483 SET third_party_id = p_new_third_party_id
1484 ,third_party_sub_id = p_new_site_id
1485 WHERE
1486 ( SOURCE_TABLE, SOURCE_ID ) IN
1487 ( SELECT 'CRH', CASH_RECEIPT_HISTORY_ID
1488 FROM AR_CASH_RECEIPT_HISTORY
1489 WHERE CASH_RECEIPT_ID = p_source_id_int_1
1490 UNION ALL
1491 SELECT 'RA', RECEIVABLE_APPLICATION_ID
1492 FROM AR_RECEIVABLE_APPLICATIONS
1493 WHERE CASH_RECEIPT_ID = p_source_id_int_1 )
1494 AND source_type NOT IN ('UNID');
1495 ELSE
1496 RAISE fnd_api.g_exc_error;
1497
1498 END IF;
1499
1500 END IF;
1501
1502 arp_standard.debug('update_cr_dist -');
1503
1504 EXCEPTION
1505
1506 WHEN creation_mode THEN
1507 arp_standard.debug('EXCEPTION creation_mode : CREATION MODE');
1508 x_errbuf := 'CREATION MODE';
1509 x_retcode := 'S';
1510 arp_standard.debug('update_cr_dist -');
1511
1512 WHEN no_existing_account THEN
1513 arp_standard.debug('EXCEPTION no_existing_account : NO ORIGINAL CUSTOMER');
1514 x_errbuf := 'NO ORIGINAL CUSTOMER';
1515 x_retcode := 'S';
1516 arp_standard.debug('update_cr_dist -');
1517
1518 WHEN OTHERS THEN
1519 arp_standard.debug('EXCEPTION OTHERS :'||SQLERRM);
1520 x_errbuf := 'SQLERRM';
1521 x_retcode := 'U';
1522
1523 END;
1524
1525
1526
1527 PROCEDURE check_period_open
1528 (p_entity_id IN NUMBER,
1529 x_return_status OUT NOCOPY VARCHAR2,
1530 x_msg_count OUT NOCOPY NUMBER,
1531 x_msg_data OUT NOCOPY VARCHAR2)
1532 IS
1533 CURSOR c_verif_date(p_entity_id IN NUMBER) IS
1534 SELECT e.event_date
1535 FROM xla_events e,
1536 xla_transaction_entities_upg t
1537 WHERE e.application_id = 222
1538 AND e.entity_id = p_entity_id
1539 AND t.application_id = 222
1540 AND t.entity_id = e.entity_id
1541 -- AND e.process_status_code = 'U'
1542 AND e.event_status_code IN ('U','I')
1543 AND NOT EXISTS
1544 (SELECT 'Y'
1545 FROM gl_period_statuses glp
1546 WHERE glp.application_id = 222
1547 AND e.event_date BETWEEN glp.start_date AND glp.end_date
1548 AND glp.set_of_books_id = t.ledger_id
1549 AND glp.closing_status IN ('O','F'));
1550
1551 l_date DATE;
1552 BEGIN
1553 arp_standard.debug('check_period_open +');
1554 x_return_status := FND_API.g_ret_sts_success;
1555
1556 OPEN c_verif_date(p_entity_id);
1557 FETCH c_verif_date INTO l_date;
1558 IF c_verif_date%FOUND THEN
1559 x_return_status := FND_API.g_ret_sts_error;
1560 arp_standard.debug(' event_date :' || l_date);
1561 FND_MESSAGE.SET_NAME('AR','AR_NOT_OPEN_PERIOD_EXISTS');
1562 FND_MSG_PUB.ADD; --BUG#5386043
1563 END IF;
1564 CLOSE c_verif_date;
1565
1566 IF x_return_status <> FND_API.g_ret_sts_success THEN
1567 RAISE fnd_api.G_EXC_ERROR;
1568 END IF;
1569 arp_standard.debug('check_period_open -');
1570 EXCEPTION
1571 WHEN fnd_api.G_EXC_ERROR THEN
1572 arp_standard.debug('EXCEPTION G_EXC_ERROR');
1573 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1574 p_count => x_msg_count,
1575 p_data => x_msg_data);
1576 WHEN OTHERS THEN
1577 arp_standard.debug('EXCEPTION OTHERS:'|| SQLERRM);
1578 x_return_status := FND_API.g_ret_sts_error;
1579 x_msg_data := SQLERRM;
1580 x_msg_count := 1;
1581 END;
1582
1583
1584 END;