eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ARP_ACCT_EVENT_PKG

Source


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