[Home] [Help]
PACKAGE BODY: APPS.ARP_CASH_BASIS_JE_REPORT
Source
1 PACKAGE BODY arp_cash_basis_je_report AS
2 /* $Header: ARPLCBJB.pls 120.5 2005/06/14 18:49:55 vcrisost ship $ */
3
4 -- RECORD holder for pertinent information about the cash receipt that drives
5 -- the posting of an application
6 TYPE ReceiptType IS RECORD
7 (
8 CashReceiptId ar_cash_receipts.cash_receipt_id%TYPE,
9 ReceiptNumber ar_cash_receipts.receipt_number%TYPE,
10 PayFromCustomer ar_cash_receipts.pay_from_customer%TYPE,
11 CustomerNumber hz_cust_accounts.account_number%TYPE,
12 CurrencyCode ar_cash_receipts.currency_code%TYPE,
13 ExchangeRate NUMBER
14 );
15 --
16 -- RECORD holder of information about the Trx to which the application
17 -- is being applied when CM_PSID_Flag is 'N'
18 -- If the CM_PSID_Flag is 'Y', this means that the PaymentScheduleId holds
19 -- the ps_id of the CM if the application_type is 'CM', but the class
20 -- and the TrxNumber still holds the invoice that the CM applies to.
21 --
22 TYPE TrxType IS RECORD
23 (
24 PaymentScheduleId ar_payment_schedules.payment_schedule_id%TYPE,
25 CmPsIdFlag VARCHAR2(1),
26 TrxNumber ra_customer_trx.trx_number%TYPE,
27 OrgId ra_customer_trx.org_id%TYPE
28 );
29 --
30 -- RECORD holder for pertinent information from a receivable application
31 -- of status = 'APP'
32 TYPE ApplicationType IS RECORD
33 (
34 ReceivableApplicationId ar_receivable_applications.receivable_application_id%TYPE,
35 GLDate DATE, -- the gl date of the application
36 TrxDate DATE, -- the apply date of the application
37 AppType ar_receivable_applications.application_type%TYPE,
38 CatMeaning ar_lookups.meaning%TYPE,
39 PostingControlId ar_receivable_applications.posting_control_id%TYPE
40 );
41 --
42 -- holds ApplicationAmount values
43 --
44 TYPE ApplicationAmountType IS RECORD
45 (
46 Amount NUMBER,
47 AcctdAmount NUMBER,
48 LineApplied NUMBER,
49 TaxApplied NUMBER,
50 FreightApplied NUMBER,
51 ChargesApplied NUMBER
52 );
53 --
54 TYPE IdType IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
55 TYPE AmountType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
56 TYPE VC15Type IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
57 TYPE VC3Type IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
58 --
59 --
60 ArpcbpError EXCEPTION;
61 PRAGMA EXCEPTION_INIT( ArpcbpError, -20000 );
62 --
63 -- private procedures
64 --
65 --
66 -- Procedures to write Record Types using dbms_output
67 --
68 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
69
70 PROCEDURE Output( p IN ReceiptType ) IS
71 BEGIN
72 IF PG_DEBUG in ('Y', 'C') THEN
73 arp_standard.debug('Output: ' || 'Receipt Type' );
74 arp_standard.debug('Output: ' || 'CashReceiptId:'||p.CashReceiptId );
75 arp_standard.debug('Output: ' || 'ReceiptNumber:'||p.ReceiptNumber );
76 arp_standard.debug('Output: ' || 'PayFromCustomer:'||p.PayFromCustomer );
77 arp_standard.debug('Output: ' || 'CustomerNumber:'||p.CustomerNumber );
78 arp_standard.debug('Output: ' || 'CurrencyCode:'||p.CurrencyCode );
79 arp_standard.debug('Output: ' || 'ExchangeRate:'||p.ExchangeRate );
80 arp_standard.debug('Output: ' || '' );
81 END IF;
82 END;
83 --
84 PROCEDURE Output( p IN TrxType ) IS
85 BEGIN
86 IF PG_DEBUG in ('Y', 'C') THEN
87 arp_standard.debug('Output: ' || 'TrxType' );
88 arp_standard.debug('Output: ' || 'CmPsIdFlag:'||p.CmPsIdFlag );
89 arp_standard.debug('Output: ' || 'PaymentScheduleId:'||p.PaymentScheduleId );
90 arp_standard.debug('Output: ' || 'TrxNumber:'||p.TrxNumber );
91 arp_standard.debug('Output: ' || 'OrgId:'||p.orgid);
92 arp_standard.debug('Output: ' || '' );
93 END IF;
94 END;
95 --
96 PROCEDURE Output( p IN ApplicationType ) IS
97 BEGIN
98 IF PG_DEBUG in ('Y', 'C') THEN
99 arp_standard.debug('Output: ' || 'ApplicationType' );
100 arp_standard.debug('Output: ' || 'ReceivableApplicationId:'||p.ReceivableApplicationId );
101 arp_standard.debug('Output: ' || 'GLDate:'||p.GLDate );
102 arp_standard.debug('Output: ' || 'TrxDate:'||p.TrxDate );
103 arp_standard.debug('Output: ' || 'AppType:'||p.AppType );
104 arp_standard.debug('Output: ' || 'CatMeaning:'||p.CatMeaning );
105 arp_standard.debug('Output: ' || 'PostingControlId:'||p.PostingControlId );
106 arp_standard.debug('Output: ' || '' );
107 END IF;
108 END;
109 --
110 PROCEDURE Output( p IN ApplicationAmountType ) IS
111 BEGIN
112 IF PG_DEBUG in ('Y', 'C') THEN
113 arp_standard.debug('Output: ' || 'ApplicationAmountType' );
114 arp_standard.debug('Output: ' || 'Amount:'||p.Amount );
115 arp_standard.debug('Output: ' || 'AcctdAmount:'||p.AcctdAmount );
116 arp_standard.debug('Output: ' || 'LineApplied:'||p.LineApplied );
117 arp_standard.debug('Output: ' || 'TaxApplied:'||p.TaxApplied );
118 arp_standard.debug('Output: ' || 'FreightApplied:'||p.FreightApplied );
119 arp_standard.debug('Output: ' || 'ChargesApplied:'||p.ChargesApplied );
120 arp_standard.debug('Output: ' || '' );
121 END IF;
122 END;
123 --
124 --
125 --
126 /*---------------------------------------------------------------------------*
127 | PRIVATE PROCEDURE |
128 | CurrentCBDApplications |
129 | |
130 | DESCRIPTION |
131 | Populates the TABLE types passed to the procedure with the total |
132 | amounts of a given type (LINE, TAX, FREIGHT, CHARGES, INVOICE) |
133 | that are currently applied to a given payment schedule. |
134 | The information is extracted from the ar_cash_basis_distributions |
135 | table, and is returned ordered by source ('GL' then 'ADJ') and |
136 | source_id (ra_cust_trx_line_gl_dist_id or adjustment_id ) |
137 | PARAMETERS |
138 | p_ps_id Payment Schedule Id for which current |
139 | applications are required |
140 | p_type The type of current applications required - |
141 | LINE, TAX, FREIGHT, CHARGES, INVOICE |
142 | Source OUT PL/SQL TABLE for the source of the line |
143 | SourceId OUT PL/SQL TABLE for the source id of the line |
144 | Amount OUT PL/SQL TABLE for the amount of the line |
145 | NextElement OUT BINARY_INTEGER Stores the Next Element to be |
146 | populated in the PL/SQL table (also, the number |
147 | of elements in the table |
148 | TotalAmount SUM of the Amounts |
149 | |
150 | EXCEPTIONS RAISED |
151 | |
152 | ERRORS RAISED |
153 | |
154 | KNOWN BUGS |
155 | |
156 | NOTES |
157 | |
158 | HISTORY |
159 | 23-Jul-1993 Alan Fothergill Created |
160 *---------------------------------------------------------------------------*/
161 PROCEDURE CurrentCBDApplications( p_ps_id IN NUMBER,
162 p_type IN VARCHAR2,
163 p_req_id IN NUMBER,
164 Source OUT NOCOPY VC3Type,
165 SourceId OUT NOCOPY IdType,
166 Amount OUT NOCOPY AmountType,
167 NextElement OUT NOCOPY BINARY_INTEGER,
168 TotalAmount OUT NOCOPY NUMBER,
169 TotalUnallocatedAmt OUT NOCOPY NUMBER
170 ) IS
171 l_TotalAmount NUMBER := 0;
172 l_TotalUnallocatedAmt NUMBER := 0;
173 l_NextElement BINARY_INTEGER := 0;
174 --
175 CURSOR CCA IS
176 SELECT SUM( cbd.amount ) Amount,
177 cbd.source Source,
178 cbd.source_id SourceId,
179 NVL(SUM( DECODE(cbd.source,
180 'UNA', cbd.amount, 0 )),0) UnallocatedAmt
181 FROM ar_cash_basis_distributions cbd
182 WHERE cbd.payment_schedule_id = p_ps_id
183 AND cbd.type = p_type
184 AND (cbd.posting_control_id+0 > 0
185 or
186 cbd.posting_control_id+0 = - ( p_req_id +100 ))
187 GROUP BY cbd.source,
188 cbd.source_id
189 ORDER BY DECODE( cbd.source, 'GL', 1,
190 'ADJ',2,
191 'UNA',3 ),
192 cbd.source_id;
193 --
194 PROCEDURE Output( p_RCa IN CCA%ROWTYPE ) IS
195 BEGIN
196 IF PG_DEBUG in ('Y', 'C') THEN
197 arp_standard.debug('Output: ' || 'CCA%ROWTYPE' );
198 arp_standard.debug('Output: ' || 'Amount:'||p_RCa.Amount );
199 arp_standard.debug('Output: ' || 'Source:'||p_RCA.Source );
200 arp_standard.debug('Output: ' || 'SourceId:'||p_RCa.SourceId );
201 arp_standard.debug('Output: ' || '--------------------------------' );
202 END IF;
203 END Output;
204 --
205 BEGIN
206 FOR RCA IN CCA LOOP
207 BEGIN
208 Source( l_NextElement ) := RCA.Source;
209 SourceId( l_NextElement ) := RCA.SourceId;
210 Amount( l_NextElement ) := RCA.Amount;
211 --
212 l_TotalAmount := l_TotalAmount + RCA.Amount;
213 l_NextElement := l_NextElement + 1;
214 l_TotalUnallocatedAmt := l_TotalUnallocatedAmt + RCA.UnallocatedAmt;
215 --
216 EXCEPTION
217 WHEN OTHERS THEN
218 arp_standard.debug( 'Exception:CurrentCBDApplications.Loop:');
219 Output( Rca );
220 RAISE;
221 END;
222 END LOOP;
223 TotalAmount := l_TotalAmount;
224 NextElement := l_NextElement;
225 TotalUnallocatedAmt := l_TotalUnallocatedAmt;
226 EXCEPTION
227 WHEN OTHERS THEN
228 arp_standard.debug( ' Exception:CurrentCBDApplications:' );
229 arp_standard.debug( 'l_NextElement:'||l_NextElement );
230 arp_standard.debug( 'l_TotalAmount:'||l_TotalAmount );
231 RAISE;
232 END;
233 --
234 --
235 --
236 --
237 /*---------------------------------------------------------------------------*
238 | PRIVATE PROCEDURE |
239 | CurrentRevDistribution |
240 | DESCRIPTION |
241 | Populates PL/SQL tables with the current 'revenue' distribution of |
242 | the given Payment Schedule for a given type. |
243 | For type 'LINE' the distributions include ra_cust_trx_line_gl_dist |
244 | records of account_class 'REV' and adjustments where |
245 | line_adjusted IS NOT NULL |
246 | For type 'TAX' the distributions include ra_cust_trx_line_gl_dist |
247 | records of account_class 'TAX' and adjustments where |
248 | tax_adjusted IS NOT NULL |
249 | For type 'FREIGHT' the distributions include ra_cust_trx_line_gl_dist|
250 | records of account_class 'FREIGHT' and adjustments where |
251 | freight_adjusted IS NOT NULL |
252 | For type 'CHARGES' the distributions include adjustments where |
253 | receivables_charges_adjusted IS NOT NULL |
254 | For type 'INVOICE' the distributions include all |
255 | ra_cust_trx_line_gl_dist records and all adjustments |
256 | The lines are returned ordered by Source ('GL' then 'ADJ' and then |
257 | source_id (ra_cust_trx_line_gl_dist_id or adjustment_id ) |
258 | |
259 | PARAMETERS |
260 | p_Report RECORD type that contains posting parameters |
261 | p_ps_id payment_schedule_id for which distribution is required |
262 | p_type type of distributions required LINE, TAX, FREIGHT, |
263 | CHARGES or INVOICE |
264 | NextElement Next element to be populated in table (also number of |
265 | elements in table) |
266 | Source OUT PL/SQL TABLE for the source of the line |
267 | SourceId OUT PL/SQL TABLE for the source id of the line |
268 | Amount OUT PL/SQL TABLE for the amount of the line |
269 | NextElement OUT BINARY_INTEGER Stores the Next Element to be |
270 | populated in the PL/SQL table (also, the number |
271 | of elements in the table |
272 | TotalAmount SUM of the Amounts |
273 | EXCEPTIONS RAISED |
274 | |
275 | ERRORS RAISED |
276 | |
277 | KNOWN BUGS |
278 | |
279 | NOTES |
280 | |
281 | HISTORY |
282 | 23-Jul-1993 Alan Fothergill Created |
283 *---------------------------------------------------------------------------*/
284 PROCEDURE CurrentRevDistribution ( p_Report IN ReportParametersType,
285 p_ps_id IN NUMBER,
286 p_type IN VARCHAR2, -- 'LINE' 'TAX' 'FREIGHT' 'CHARGES' 'INVOICE'
287 NextElement OUT NOCOPY BINARY_INTEGER,
288 Source OUT NOCOPY VC3Type,
289 SourceId OUT NOCOPY IdType,
290 Ccid OUT NOCOPY IdType,
291 AccntClass OUT NOCOPY VC15Type,
292 Amount OUT NOCOPY AmountType,
293 TotalAmount OUT NOCOPY NUMBER
294 ) IS
295 l_customer_trx_id NUMBER(15);
296 l_term_fraction NUMBER;
297 l_currency_code VARCHAR2(15);
298 l_Amount NUMBER;
299 l_AmountReconcile NUMBER;
300 l_FirstInstallmentCode VARCHAR2(12);
301 l_NextElement BINARY_INTEGER := 0;
302 l_TotalAmount NUMBER := 0;
303 l_FirstInstallmentFlag VARCHAR2(1);
304 charges_adjusted NUMBER := 0;
305 --
306 CURSOR gl_dist_cursor( cp_ctid NUMBER, cp_type VARCHAR2 ) IS
307 SELECT ctlgd.cust_trx_line_gl_dist_id,
308 ctlgd.amount amount,
309 ctlgd.code_combination_id ccid,
310 substrb(decode(ctlgd.account_class,
311 'REV','LINE',
312 ctlgd.account_class),1,15) accntclass
313 FROM ra_cust_trx_line_gl_dist ctlgd
314 WHERE ctlgd.customer_trx_id = cp_ctid
315 AND ctlgd.account_class IN ( 'REV', 'TAX', 'FREIGHT','CHARGES' ) -- we are only interested in these classes
316 AND ctlgd.account_class = DECODE
317 (
318 cp_type,
319 'LINE', 'REV',
320 'TAX', 'TAX',
321 'FREIGHT', 'FREIGHT',
322 'CHARGES', 'CHARGES',
323 ctlgd.account_class
324 )
325 AND ctlgd.cust_trx_line_gl_dist_id+0 < p_Report.NxtCustTrxLineGlDistId
326 ORDER BY ctlgd.cust_trx_line_gl_dist_id;
327 --
328 CURSOR adj_cursor( cp_ps_id NUMBER, cp_type VARCHAR2 ) IS
329 SELECT a.adjustment_id adjustment_id,
330 DECODE
331 (
332 cp_type,
333 'LINE', a.line_adjusted,
334 'TAX', a.tax_adjusted,
335 'FREIGHT', a.freight_adjusted,
336 'CHARGES', a.receivables_charges_adjusted,
337 a.amount
338 ) amount,
339 a.code_combination_id ccid,
340 substrb(a.type,1,15) accntclass
341 FROM ar_adjustments a,
342 ra_customer_trx ct,
343 ra_cust_trx_types ctt
344 WHERE a.payment_schedule_id = cp_ps_id
345 AND a.receivables_trx_id <> -1
346 AND a.status = 'A'
347 AND a.customer_trx_id = ct.customer_trx_id
348 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
349 AND (
350 ( ctt.creation_sign = 'N'
351 AND
352 DECODE
353 (
354 cp_type,
355 'LINE', a.line_adjusted,
356 'TAX', a.tax_adjusted,
357 'FREIGHT', a.freight_adjusted,
358 'CHARGES', a.receivables_charges_adjusted,
359 a.amount
360 ) < 0
361 )
362 OR
363 ( ctt.creation_sign <> 'N'
364 AND
365 DECODE
366 (
367 cp_type,
368 'LINE', a.line_adjusted,
369 'TAX', a.tax_adjusted,
370 'FREIGHT', a.freight_adjusted,
371 'CHARGES', a.receivables_charges_adjusted,
372 a.amount
373 ) > 0
374 )
375 )
376 AND a.adjustment_id+0 < p_Report.NxtAdjustmentId
377 ORDER BY a.adjustment_id;
378 --
379 BEGIN
380 -- first get the ps details
381 BEGIN
382 SELECT ps.customer_trx_id,
383 NVL(tl.relative_amount, 100 )/NVL( t.base_amount, 100 ),
384 t.first_installment_code,
385 ps.invoice_currency_code,
386 NVL
387 (
388 DECODE
389 (
390 p_Type,
391 'LINE', ps.amount_line_items_original,
392 'TAX', ps.tax_original,
393 'FREIGHT', ps.freight_original,
394 'CHARGES', ps.receivables_charges_charged,
395 'INVOICE', ps.amount_due_original,
396 0
397 ),
398 0
399 ),
400 DECODE
401 (
402 MIN(tl_first.sequence_num),
403 tl.sequence_num, 'Y',
404 'N'
405 ) first_installment_flag
406 INTO l_customer_trx_id,
407 l_term_fraction,
408 l_FirstInstallmentCode,
409 l_currency_code,
410 l_AmountReconcile,
411 l_FirstInstallmentFlag
412 FROM ar_payment_schedules ps,
413 ra_terms t,
414 ra_terms_lines tl,
415 ra_terms_lines tl_first
416 WHERE ps.payment_schedule_id = p_ps_id
417 AND tl.term_id(+) = ps.term_id
418 AND tl.sequence_num(+) = ps.terms_sequence_number
419 AND t.term_id(+) = tl.term_id
420 AND tl_first.term_id(+) = t.term_id
421 GROUP BY ps.customer_trx_id,
422 tl.relative_amount,
423 t.base_amount,
424 t.first_installment_code,
425 ps.invoice_currency_code,
426 ps.amount_line_items_original,
427 ps.tax_original,
428 ps.freight_original,
429 ps.receivables_charges_charged,
430 ps.amount_due_original,
431 tl.sequence_num;
432 EXCEPTION
433 WHEN OTHERS THEN
434 arp_standard.debug( 'Exception:CurrentRevDistribution.Select PS Details:' );
435 RAISE;
436 END;
437 --
438 FOR GlDistRecord IN gl_dist_cursor( l_customer_trx_id, p_type ) LOOP
439 IF l_FirstInstallmentFlag = 'Y' AND l_FirstInstallmentCode = 'INCLUDE' AND p_Type IN ('TAX','FREIGHT') THEN
440 l_Amount := GlDistRecord.Amount;
441 ELSE
442 l_Amount := arpcurr.CurrRound( GlDistRecord.amount * l_term_fraction, l_currency_code );
443 END IF;
444 Amount( l_NextElement ) := l_Amount;
445 Source( l_NextElement ) := 'GL';
446 SourceId( l_NextElement ) := GlDistRecord.cust_trx_line_gl_dist_id;
447 Ccid( l_NextElement ) := GlDistRecord.ccid;
448 AccntClass( l_NextElement ) := GlDistRecord.accntclass;
449 l_TotalAmount := l_TotalAmount + l_Amount;
450 l_NextElement := l_NextElement + 1;
451 END LOOP;
452 --
453 IF l_NextElement <> 0
454 THEN
455 IF ( p_type = 'CHARGES' ) AND ( l_TotalAmount <> 0 )
456 THEN
457 SELECT nvl(sum(nvl(receivables_charges_adjusted,0)),0)
458 INTO charges_adjusted
459 FROM ar_adjustments
460 WHERE payment_schedule_id = p_ps_id
461 AND status = 'A'
462 AND type in ('INVOICE','CHARGES');
463
464 l_AmountReconcile := l_AmountReconcile - charges_adjusted;
465 END IF;
466
467 IF ( p_type = 'CHARGES' ) AND ( l_TotalAmount = 0 )
468 THEN
469 l_AmountReconcile := 0;
470 END IF;
471
472 -- place the reconcile amount on to the last distribution
473 Amount( l_NextElement-1) := l_Amount +
474 l_AmountReconcile - l_TotalAmount;
475 l_TotalAmount := l_AmountReconcile;
476 END IF;
477 --
478 -- next get any adjustments
479 FOR AdjRecord IN adj_cursor( p_ps_id, p_type ) LOOP
480 Amount( l_NextElement ) := AdjRecord.amount;
481 Source( l_NextElement ) := 'ADJ';
482 SourceId( l_NextElement ) := AdjRecord.adjustment_id;
483 Ccid( l_NextElement ) := AdjRecord.ccid;
484 AccntClass( l_NextElement ) := AdjRecord.accntclass;
485 l_TotalAmount := l_TotalAmount + AdjRecord.Amount;
486 l_NextElement := l_NextElement + 1;
487 END LOOP;
488 --
489 -- if the total amount comes to zero, report on this
490 -- IF l_TotalAmount = 0
491 -- THEN
492 -- arp_standard.debug( 'On ps_id:'||p_ps_id||' for Type:'||p_Type||' the Total Distribution=0');
493 -- arp_standard.debug( 'CurrentRevDistribution: TotalAmount = 0' );
494 -- arp_standard.debug( 'p_ps_id:'||p_ps_id );
495 -- arp_standard.debug( 'p_type:'||p_type );
496 -- END IF;
497 TotalAmount := l_TotalAmount;
498 NextElement := l_NextElement;
499 EXCEPTION
500 WHEN OTHERS THEN
501 arp_standard.debug( 'Exception:CurrentRevDistribution:' );
502 arp_standard.debug( 'l_customer_trx_id:'||l_customer_trx_id );
503 arp_standard.debug( 'l_term_fraction:'||l_term_fraction );
504 arp_standard.debug( 'l_currency_code:'||l_currency_code );
505 arp_standard.debug( 'l_Amount:'||l_Amount );
506 arp_standard.debug( 'l_AmountReconcile:'||l_AmountReconcile );
507 arp_standard.debug( 'l_FirstInstallmentCode:'||l_FirstInstallmentCode );
508 arp_standard.debug( 'l_NextElement:'||l_NextElement );
509 arp_standard.debug( 'l_TotalAmount:'||l_TotalAmount );
510 arp_standard.debug( 'l_FirstInstallmentFlag:'||l_FirstInstallmentFlag );
511 RAISE;
512 END;
513 --
514 --
515 /*---------------------------------------------------------------------------*
516 | PRIVATE PROCEDURE |
517 | CreateInterim |
518 | DESCRIPTION |
519 | Inserts a record into ar_journal_interim |
520 | PARAMETERS |
521 | |
522 | EXCEPTIONS RAISED |
523 | |
524 | ERRORS RAISED |
525 | |
526 | KNOWN BUGS |
527 | |
528 | NOTES |
529 | |
530 | HISTORY |
531 | 02-Jul-2004 Hiroshi Yoshihara bug3718694 Created |
532 *---------------------------------------------------------------------------*/
533 PROCEDURE CreateInterim( p_interim_rec IN ar_journal_interim%ROWTYPE) IS
534 BEGIN
535 INSERT INTO
536 ar_journal_interim
537 (
538 status,
539 actual_flag,
540 request_id,
541 created_by,
542 date_created,
543 set_of_books_id,
544 je_source_name,
545 je_category_name,
546 transaction_date,
547 accounting_date,
548 currency_code,
549 code_combination_id,
550 entered_dr,
551 entered_cr,
552 accounted_dr,
553 accounted_cr,
554 reference10,
555 reference21,
556 reference22,
557 reference23,
558 reference24,
559 reference25,
560 reference26,
561 reference27,
562 reference28,
563 reference29,
564 reference30,
565 org_id
566 )
567 VALUES
568 (
569 p_interim_rec.status,
570 p_interim_rec.actual_flag,
571 p_interim_rec.request_id,
572 p_interim_rec.created_by,
573 p_interim_rec.date_created,
574 p_interim_rec.set_of_books_id,
575 p_interim_rec.je_source_name,
576 p_interim_rec.je_category_name,
577 p_interim_rec.transaction_date,
578 p_interim_rec.accounting_date,
579 p_interim_rec.currency_code,
580 p_interim_rec.code_combination_id,
581 p_interim_rec.entered_dr,
582 p_interim_rec.entered_cr,
583 p_interim_rec.accounted_dr,
584 p_interim_rec.accounted_cr,
585 p_interim_rec.reference10,
586 p_interim_rec.reference21,
587 p_interim_rec.reference22,
588 p_interim_rec.reference23,
589 p_interim_rec.reference24,
590 p_interim_rec.reference25,
591 p_interim_rec.reference26,
592 p_interim_rec.reference27,
593 p_interim_rec.reference28,
594 p_interim_rec.reference29,
595 p_interim_rec.reference30,
596 p_interim_rec.org_id
597 );
598 EXCEPTION
599 WHEN OTHERS THEN
600 arp_standard.debug( 'Exception:CreateInterim:' );
601 RAISE;
602 END;
603 --
604 --
605 /*---------------------------------------------------------------------------*
606 | PRIVATE PROCEDURE |
607 | CreateDistribution |
608 | DESCRIPTION |
609 | Creates a distribution by inserting a record into |
610 | ar_cash_basis_distributions, and a record into ar_journal_interim |
611 | PARAMETERS |
612 | |
613 | EXCEPTIONS RAISED |
614 | |
615 | ERRORS RAISED |
616 | |
617 | KNOWN BUGS |
618 | |
619 | NOTES |
620 | |
621 | HISTORY |
622 | 23-Jul-1993 Alan Fothergill Created |
623 *---------------------------------------------------------------------------*/
624 PROCEDURE CreateDistribution( p_Report IN ReportParametersType,
625 p_Receipt IN ReceiptType,
626 p_Trx IN TrxType,
627 p_App IN ApplicationType,
628 p_Amount IN NUMBER,
629 p_AcctdAmount IN NUMBER,
630 p_Source IN VARCHAR2,
631 p_SourceId IN NUMBER,
632 p_Type IN VARCHAR2,
633 p_Ccid IN NUMBER,
634 p_AccntClass IN VARCHAR2 ) IS
635 CashBasisDistributionId ar_cash_basis_distributions.cash_basis_distribution_id%TYPE;
636
637
638 BEGIN
639 IF p_Amount = 0 AND p_AcctdAmount = 0 THEN
640 RETURN;
641 END IF;
642 --
643 -- If the record has been posted, then just select the records from cash basis distribution table
644 --
645 IF p_App.PostingControlId > 0
646 THEN
647 BEGIN
648 INSERT INTO
649 ar_journal_interim
650 (
651 status,
652 actual_flag,
653 request_id,
654 created_by,
655 date_created,
656 set_of_books_id,
657 je_source_name,
658 je_category_name,
659 transaction_date,
660 accounting_date,
661 currency_code,
662 code_combination_id,
663 entered_dr,
664 entered_cr,
665 accounted_dr,
666 accounted_cr,
667 reference10,
668 reference21,
669 reference22,
670 reference23,
671 reference24,
672 reference25,
673 reference26,
674 reference27,
675 reference28,
676 reference29,
677 reference30,
678 org_id
679 )
680 SELECT
681 'NEW', -- status
682 'A', -- actual flag
683 p_Report.ReqId, -- request_id
684 p_Report.CreatedBy, -- created_by
685 TRUNC( SYSDATE ), -- date_created
686 p_Report.SetOfBooksId, -- set_of_books_id
687 'Receivables', -- user_je_source_name
688 'Trade Receipts', -- user_je_category_name
689 p_App.TrxDate, -- trx_date
690 p_App.GlDate, -- accounting_date
691 p_Receipt.CurrencyCode, -- currency_code
692 cbd.code_combination_id, -- code_combination_id
693 DECODE
694 (
695 SIGN( cbd.amount ),
696 -1, -cbd.amount,
697 NULL
698 ), -- entered_dr
699 DECODE
700 (
701 SIGN( cbd.amount ),
702 -1, NULL,
703 cbd.amount
704 ), -- entered_cr
705 DECODE
706 (
707 SIGN( cbd.acctd_amount ),
708 -1, -cbd.acctd_amount,
709 NULL
710 ), -- accounted_dr
711 DECODE
712 (
713 SIGN( cbd.acctd_amount ),
714 -1, NULL,
715 cbd.acctd_amount
716 ), -- accounted_cr
717 p_App.CatMeaning, -- reference10,
718 p_Report.ReqId, -- reference21,
719 p_Receipt.CashReceiptId, -- reference22,
720 cbd.cash_basis_distribution_id, -- reference23,
721 p_Receipt.ReceiptNumber, -- reference24,
722 p_Trx.TrxNumber, -- reference25,
723 p_Receipt.CustomerNumber, -- reference26,
724 p_Receipt.PayFromCustomer, -- reference27,
725 DECODE(
726 P_App.AppType,
727 'CM', 'CMAPP',
728 'CASH','TRADE' ), -- reference28,
729 DECODE(
730 P_App.AppType,
731 'CASH', 'TRADE_APP',
732 'CM', DECODE(
733 p_Trx.CmPsIdFlag,
734 'Y', 'CMAPP_REC',
735 'CMAPP_APP' )), -- reference29,
736 'AR_CASH_BASIS_DISTRIBUTIONS', -- reference30
737 cbd.org_id
738 FROM ar_cash_basis_distributions cbd
739 WHERE cbd.posting_control_id+0 = p_App.PostingControlId
740 AND cbd.receivable_application_id = p_App.ReceivableApplicationId
741 AND cbd.payment_schedule_id = p_Trx.PaymentScheduleId
742 AND cbd.type = p_Type;
743 EXCEPTION
744 WHEN OTHERS THEN
745 arp_standard.debug( 'Exception:CreateDistribution.InsertPostedAR:' );
746 RAISE;
747 END;
748 ELSE
749 --
750 SELECT ar_cash_basis_distributions_s.NEXTVAL
751 INTO CashBasisDistributionId
752 FROM dual;
753 --
754 BEGIN
755 --
756 -- Posting Control Id is -(req_id+100) is used to be an identifier
757 -- such that we can delete these records at the end of the process
758 -- We need to add 100 because pst_contrl_id of -1 to -100 are reserved
759 -- for other usage
760 --
761 INSERT INTO ar_cash_basis_distributions
762 (
763 cash_basis_distribution_id,
764 created_by,
765 creation_date,
766 last_updated_by,
767 last_update_date,
768 receivable_application_id,
769 source,
770 source_id,
771 type,
772 payment_schedule_id,
773 gl_date,
774 currency_code,
775 amount,
776 acctd_amount,
777 code_combination_id,
778 posting_control_id,
779 gl_posted_date,
780 org_id
781 )
782 VALUES
783 (
784 CashBasisDistributionId,
785 p_Report.CreatedBy,
786 TRUNC( SYSDATE ),
787 p_Report.CreatedBy,
788 TRUNC( SYSDATE ),
789 p_App.ReceivableApplicationId,
790 p_Source,
791 p_SourceId,
792 p_Type,
793 p_Trx.PaymentScheduleId,
794 p_App.GlDate,
795 p_Receipt.CurrencyCode,
796 p_Amount,
797 p_AcctdAmount,
798 p_Ccid,
799 - ( p_Report.ReqId +100 ),
800 TRUNC( SYSDATE ),
801 p_trx.OrgId
802 );
803 EXCEPTION
804 WHEN OTHERS THEN
805 arp_standard.debug( 'Exception:CreateDistribution.InsertCBD:' );
806 RAISE;
807 END;
808 --
809 -- bug3718694 Call CreateInterim procedure to insert record into
810 -- ar_journal_interim .
811 DECLARE
812 l_interim_rec ar_journal_interim%ROWTYPE;
813 BEGIN
814 l_interim_rec.status := 'NEW';
815 l_interim_rec.actual_flag := 'A';
816 l_interim_rec.request_id := p_Report.ReqId;
817 l_interim_rec.created_by := p_Report.CreatedBy;
818 l_interim_rec.date_created := TRUNC( SYSDATE );
819 l_interim_rec.set_of_books_id := p_Report.SetOfBooksId;
820 l_interim_rec.je_source_name := 'Receivables';
821 l_interim_rec.je_category_name := 'Trade Receipts';
822 l_interim_rec.transaction_date := p_App.TrxDate;
823 l_interim_rec.accounting_date := p_App.GlDate;
824 l_interim_rec.currency_code := p_Receipt.CurrencyCode;
825 l_interim_rec.code_combination_id := p_Ccid;
826
827 IF p_Amount < 0
828 THEN
829 l_interim_rec.entered_dr := -p_Amount ;
830 ELSE
831 l_interim_rec.entered_cr := p_Amount ;
832 END IF;
833
834 IF p_AcctdAmount < 0
835 THEN
836 l_interim_rec.accounted_dr := -p_AcctdAmount ;
837 ELSE
838 l_interim_rec.accounted_cr := p_AcctdAmount ;
839 END IF;
840
841 l_interim_rec.reference10 := p_App.CatMeaning;
842 l_interim_rec.reference21 := p_Report.ReqId;
843 l_interim_rec.reference22 := p_Receipt.CashReceiptId;
844 l_interim_rec.reference23 := CashBasisDistributionId;
845 l_interim_rec.reference24 := p_Receipt.ReceiptNumber;
846 l_interim_rec.reference25 := p_Trx.TrxNumber;
847 l_interim_rec.reference26 := p_Receipt.CustomerNumber;
848 l_interim_rec.reference27 := p_Receipt.PayFromCustomer;
849
850 IF P_App.AppType = 'CM'
851 THEN
852 l_interim_rec.reference28 := 'CMAPP';
853 ELSIF P_App.AppType = 'CASH'
854 THEN
855 l_interim_rec.reference28 := 'TRADE';
856 END IF;
857
858 IF P_App.AppType = 'CASH'
859 THEN
860 l_interim_rec.reference29 := 'TRADE_APP';
861 ELSIF P_App.AppType = 'CM'
862 THEN
863 IF p_Trx.CmPsIdFlag = 'Y'
864 THEN
865 l_interim_rec.reference29 := 'CMAPP_REC';
866 ELSE
867 l_interim_rec.reference29 := 'CMAPP_APP';
868 END IF;
869 END IF;
870
871 l_interim_rec.reference30 := 'AR_CASH_BASIS_DISTRIBUTIONS';
872 l_interim_rec.org_id := p_trx.OrgId;
873
874 CreateInterim ( l_interim_rec );
875 EXCEPTION
876 WHEN OTHERS THEN
877 arp_standard.debug( 'Exception:CreateDistribution.InsertAR:' );
878 RAISE;
879 END;
880 END IF;
881 EXCEPTION
882 WHEN OTHERS THEN
883 arp_standard.debug( 'Exception:CreateDistribution:' );
884 RAISE;
885 END;
886 --
887 --
888 /*---------------------------------------------------------------------------*
889 | PRIVATE PROCEDURE |
890 | DistributeApplicationType |
891 | DESCRIPTION |
892 | An amount of a certain type is distributed to a payment schedule |
893 | The procedure works by calculating what the pro-rated distributions |
894 | should be when the current application is included, working out |
895 | what the current applications are, and creating a distribution |
896 | for the difference. |
897 | Distributions are calculated and made at the line (gl dist or |
898 | adjustment) level (rather than the account level) |
899 | PARAMETERS |
900 | |
901 | EXCEPTIONS RAISED |
902 | |
903 | ERRORS RAISED |
904 | |
905 | KNOWN BUGS |
906 | |
907 | NOTES |
908 | |
909 | HISTORY |
910 | 23-Jul-1993 Alan Fothergill Created |
911 | 25-Aug-1993 Alan Fothergill If the total of distributions of the |
912 | invoice is zero, then post the |
913 | application to the CBPBALANCE account|
914 *---------------------------------------------------------------------------*/
915 PROCEDURE DistributeApplicationType( p_Report IN ReportParametersType,
916 p_Receipt IN ReceiptType,
917 p_Trx IN TrxType,
918 p_App IN ApplicationType,
919 p_Type IN VARCHAR2,
920 p_Amount IN NUMBER,
921 p_AcctdAmount IN NUMBER ) IS
922 CBD_Source VC3Type;
923 CBD_SourceId IdType;
924 CBD_Amount AmountType;
925 CBD_NextElement BINARY_INTEGER;
926 CBD_TotalAmount NUMBER;
927 CBD_TotalUnallocatedAmt NUMBER;
928 --
929 CRD_Source VC3Type;
930 CRD_SourceId IdType;
931 CRD_Amount AmountType;
932 CRD_Ccid IdType;
933 CRD_AccntClass VC15Type;
934 CRD_NextElement BINARY_INTEGER;
935 CRD_TotalAmount NUMBER;
936 --
937 CBD_i BINARY_INTEGER;
938 CRD_i BINARY_INTEGER;
939 --
940 NewAppToLine NUMBER; -- the amount that will be applied to a line
941 -- after the current application has been made
942 RunningNewAppToLine NUMBER := 0; -- this is the running total of NewAppToLine
943 -- the final NewAppToLine is adjusted
944 -- so that the value of RunningNewAppToLine is equal to
945 -- GrandTotalApplied
946 GrandTotalApplied NUMBER;
947 AppToLineThisTime NUMBER; -- the actual amount posted, and stored in ar_cash_basis_distributions
948 AcctdAppToLineThisTime NUMBER;
949 RunningAppToLineThisTime NUMBER := 0;
950 RunningAcctdAppToLineThisTime NUMBER := 0;
951 BEGIN
952 --
953 -- If the record has been posted, then just select the records from cash basis distribution table
954 --
955 IF p_App.PostingControlId > 0
956 THEN
957 CreateDistribution( p_Report,
958 p_Receipt,
959 p_Trx,
960 p_App,
961 p_Amount,
962 p_AcctdAmount,
963 '0',0,p_Type,0,'0' );
964 ELSE
965 CurrentRevDistribution ( p_Report,
966 p_Trx.PaymentScheduleId,
967 p_Type,
968 CRD_NextElement,
969 CRD_Source,
970 CRD_SourceId,
971 CRD_Ccid,
972 CRD_AccntClass,
973 CRD_Amount,
974 CRD_TotalAmount );
975 --
976 CurrentCBDApplications( p_Trx.PaymentScheduleId,
977 p_Type,
978 p_Report.ReqId,
979 CBD_Source,
980 CBD_SourceId,
981 CBD_Amount,
982 CBD_NextElement,
983 CBD_TotalAmount,
984 CBD_TotalUnallocatedAmt );
985 --
986 CBD_i := 0;
987 CRD_i := 0;
988 GrandTotalApplied := CBD_TotalAmount + p_Amount;
989 --
990 WHILE CRD_i <> CRD_NextElement
991 LOOP
992 IF ( CRD_i = CRD_NextElement - 1 ) AND
993 ( CRD_TotalAmount <> 0 ) AND
994 ( CBD_TotalUnallocatedAmt = 0 )
995 THEN
996 -- this is the final distribution if Total Revenue Distribution is non-zero
997 -- and Cash Basis Clearing account is zero
998 NewAppToLine := GrandTotalApplied - RunningNewAppToLine;
999 ELSE
1000 IF CRD_TotalAmount = 0
1001 THEN
1002 NewAppToLine := 0;
1003 ELSE
1004 NewAppToLine := arpcurr.CurrRound( CRD_Amount( CRD_i ) * GrandTotalApplied/
1005 CRD_TotalAmount, p_Receipt.CurrencyCode );
1006 END IF;
1007 END IF;
1008 RunningNewAppToLine := RunningNewAppToLine + NewAppToLine;
1009 --
1010 IF CBD_i <> CBD_NextElement AND
1011 CBD_Source( CBD_i ) = CRD_Source( CRD_i ) AND
1012 CBD_SourceId( CBD_i ) = CRD_SourceId( CRD_i )
1013 -- the above is acceptable. If the first boolean fails, then
1014 -- the remainder will not be executed. Therefore, at the limit
1015 -- when CBD_i = CBD_NextElement, the remaining expressions will not
1016 -- be evaluated (which would have caused an unitilaised element
1017 -- to be accessed)
1018 THEN
1019 -- amount to apply this time is equal to what the total application
1020 -- should be, minus the amount already applied
1021 AppToLineThisTime := NewAppToLine - CBD_Amount( CBD_i );
1022 CBD_i := CBD_i + 1;
1023 ELSE
1024 -- amount to apply this time is equal to what the total amount should
1025 -- be, because there is not an existing appliation to the line
1026 AppToLineThisTime := NewAppToLine;
1027 END IF;
1028 AcctdAppToLineThisTime := arpcurr.ReconcileAcctdAmounts( p_Receipt.ExchangeRate,
1029 p_Amount, -- total of distributions reconciles to the applied amount
1030 p_AcctdAmount,
1031 AppToLineThisTime,
1032 RunningAppToLineThisTime,
1033 RunningAcctdAppToLineThisTime );
1034 --
1035 IF AcctdAppToLineThisTime <> 0
1036 THEN
1037 CreateDistribution( p_Report,
1038 p_Receipt,
1039 p_Trx,
1040 p_App,
1041 AppToLineThisTime,
1042 AcctdAppToLineThisTime,
1043 CRD_Source( CRD_i ),
1044 CRD_SourceId( CRD_i ),
1045 p_Type,
1046 CRD_Ccid( CRD_i ),
1047 CRD_AccntClass( CRD_i ) );
1048 END IF;
1049 CRD_i := CRD_i + 1;
1050 END LOOP;
1051 --
1052 -- Now if Total Revenue Distribution is zero
1053 -- OR if Cash Basis Clearing account is non-zero,
1054 -- Then we need to post to the Cash Basis Clearing account
1055 --
1056 IF ( CRD_TotalAmount = 0 ) OR
1057 ( CBD_TotalUnallocatedAmt <> 0 )
1058 THEN
1059 NewAppToLine := GrandTotalApplied - RunningNewAppToLine;
1060 RunningNewAppToLine := RunningNewAppToLine + NewAppToLine;
1061 AppToLineThisTime := NewAppToLine - CBD_TotalUnallocatedAmt;
1062 AcctdAppToLineThisTime := arpcurr.ReconcileAcctdAmounts( p_Receipt.ExchangeRate,
1063 p_Amount, -- total of distributions reconciles to the applied amount
1064 p_AcctdAmount,
1065 AppToLineThisTime,
1066 RunningAppToLineThisTime,
1067 RunningAcctdAppToLineThisTime );
1068 --
1069 IF AcctdAppToLineThisTime <> 0
1070 THEN
1071 --
1072 CreateDistribution( p_Report,
1073 p_Receipt,
1074 p_Trx,
1075 p_App,
1076 AppToLineThisTime,
1077 AcctdAppToLineThisTime,
1078 'UNA',
1079 p_Report.SetOfBooksId,
1080 p_Type,
1081 p_Report.UnallocatedRevCcid,
1082 'INVOICE' );
1083 END IF;
1084 END IF;
1085 END IF;
1086 EXCEPTION
1087 WHEN OTHERS THEN
1088 arp_standard.debug( 'Exception:DistributeApplicationType:' );
1089 RAISE;
1090 END;
1091 --
1092 --
1093 --
1094 /*---------------------------------------------------------------------------*
1095 | PRIVATE PROCEDURE |
1096 | DistributeLTFApplication |
1097 | DESCRIPTION |
1098 | Distribute the Line, Tax, Freight and Charges of an application |
1099 | separately |
1100 | PARAMETERS |
1101 | |
1102 | EXCEPTIONS RAISED |
1103 | |
1104 | ERRORS RAISED |
1105 | |
1106 | KNOWN BUGS |
1107 | |
1108 | NOTES |
1109 | |
1110 | HISTORY |
1111 | 23-Jul-1993 Alan Fothergill Created |
1112 *---------------------------------------------------------------------------*/
1113 PROCEDURE DistributeLTFApplication( p_Report IN ReportParametersType,
1114 p_Receipt IN ReceiptType,
1115 p_Trx IN TrxType,
1116 p_App IN ApplicationType,
1117 p_AppAmount IN ApplicationAmountType ) IS
1118 RunningTotalAmount NUMBER := 0;
1119 RunningTotalAcctdAmount NUMBER := 0;
1120 AcctdAmount NUMBER;
1121 SurplusAmount NUMBER;
1122 BEGIN
1123 IF p_AppAmount.ChargesApplied <> 0 THEN
1124 AcctdAmount := arpcurr.ReconcileAcctdAmounts( p_Receipt.ExchangeRate,
1125 p_AppAmount.Amount,
1126 p_AppAmount.AcctdAmount,
1127 p_AppAmount.ChargesApplied,
1128 RunningTotalAmount,
1129 RunningTotalAcctdAmount );
1130 DistributeApplicationType( p_Report, p_Receipt, p_Trx, p_App, 'CHARGES', p_AppAmount.ChargesApplied, AcctdAmount );
1131 END IF;
1132 --
1133 IF p_AppAmount.FreightApplied <> 0 THEN
1134 AcctdAmount := arpcurr.ReconcileAcctdAmounts( p_Receipt.ExchangeRate,
1135 p_AppAmount.Amount,
1136 p_AppAmount.AcctdAmount,
1137 p_AppAmount.FreightApplied,
1138 RunningTotalAmount,
1139 RunningTotalAcctdAmount );
1140 DistributeApplicationType( p_Report, p_Receipt, p_Trx, p_App, 'FREIGHT', p_AppAmount.FreightApplied, AcctdAmount );
1141 END IF;
1142 --
1143 IF p_AppAmount.TaxApplied <> 0 THEN
1144 AcctdAmount := arpcurr.ReconcileAcctdAmounts( p_Receipt.ExchangeRate,
1145 p_AppAmount.Amount,
1146 p_AppAmount.AcctdAmount,
1147 p_AppAmount.TaxApplied,
1148 RunningTotalAmount,
1149 RunningTotalAcctdAmount );
1150 DistributeApplicationType( p_Report, p_Receipt,p_Trx, p_App, 'TAX', p_AppAmount.TaxApplied, AcctdAmount );
1151 END IF;
1152 --
1153 IF p_AppAmount.LineApplied <> 0 THEN
1154 AcctdAmount := arpcurr.ReconcileAcctdAmounts( p_Receipt.ExchangeRate,
1155 p_AppAmount.Amount,
1156 p_AppAmount.AcctdAmount,
1157 p_AppAmount.LineApplied,
1158 RunningTotalAmount,
1159 RunningTotalAcctdAmount );
1160 DistributeApplicationType( p_Report, p_Receipt, p_Trx, p_App, 'LINE', p_AppAmount.LineApplied, AcctdAmount );
1161 END IF;
1162 --
1163 --
1164 -- if RunningTotalAmount doesn't equal the Amount on the application, then report on this, and
1165 -- treat the difference as a 'LINE' application
1166 --
1167 /*
1168 SurplusAmount := p_AppAmount.Amount - RunningTotalAmount;
1169 IF SurplusAmount <> 0 THEN
1170 arp_standard.debug( 'DistributeLTFApplication' );
1171 arp_standard.debug( 'LTF Charges doesn''t equal application amount for ra_id:'||p_App.ReceivableApplicationId );
1172 arp_standard.debug( '----------------------------------------' );
1173 AcctdAmount := arpcurr.ReconcileAcctdAmounts( p_Receipt.ExchangeRate,
1174 p_AppAmount.Amount,
1175 p_AppAmount.AcctdAmount,
1176 SurplusAmount,
1177 RunningTotalAmount,
1178 RunningTotalAcctdAmount );
1179 DistributeApplicationType( p_Report, p_Receipt, p_Trx, p_App, 'LINE', SurplusAmount, AcctdAmount );
1180 END IF;
1181 */
1182 --
1183 --
1184 EXCEPTION
1185 WHEN OTHERS THEN
1186 arp_standard.debug( 'Exception:DistributeLTFApplication:' );
1187 RAISE;
1188 END;
1189 --
1190 --
1191 -- post ar_receivable_applications that have status UNAPP, UNID, ACC
1192 --
1193 /*---------------------------------------------------------------------------*
1194 | PRIVATE PROCEDURE |
1195 | ReportNonDistApplications |
1196 | DESCRIPTION |
1197 | non-APP ar_receivable_applications records |
1198 | |
1199 | |
1200 | PARAMETERS |
1201 | |
1202 | EXCEPTIONS RAISED |
1203 | |
1204 | ERRORS RAISED |
1205 | |
1206 | KNOWN BUGS |
1207 | |
1208 | NOTES |
1209 | |
1210 | HISTORY |
1211 | 12-Apr-1994 D Chu Created |
1212 *---------------------------------------------------------------------------*/
1213 PROCEDURE ReportNonDistApplications( p_Report IN ReportParametersType ) IS
1214 CURSOR CRa IS
1215 SELECT ra.ROWID RaRowid,
1216 cr.cash_receipt_id CashReceiptId,
1217 cr.receipt_number ReceiptNumber,
1218 cr.pay_from_customer PayFromCustomer,
1219 cust.account_number CustomerNumber,
1220 cr.currency_code CurrencyCode,
1221 ra.receivable_application_id ReceivableApplicationId,
1222 ra.gl_date GlDate,
1223 ra.apply_date TrxDate,
1224 ra.amount_applied Amount,
1225 ra.amount_applied_from AmountAppFrom,
1226 ra.acctd_amount_applied_from AcctdAmount,
1227 ra.code_combination_id CodeCombinationId,
1228 ra.status Status,
1229 l_cat.meaning CatMeaning,
1230 cr.org_id OrgId
1231 FROM ar_receivable_applications ra,
1232 ar_cash_receipts cr,
1233 hz_cust_accounts cust,
1234 ar_lookups l_cat
1235 WHERE ra.gl_date BETWEEN p_Report.GlDateFrom
1236 AND p_Report.GLDateTo
1237 AND nvl(ra.postable,'Y') = 'Y'
1238 AND nvl(ra.confirmed_flag,'Y') = 'Y'
1239 AND ra.status <> 'APP'
1240 AND cr.cash_receipt_id = ra.cash_receipt_id
1241 AND cr.pay_from_customer = cust.cust_account_id
1242 AND l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
1243 AND l_cat.lookup_code = decode(ra.amount_applied_from,
1244 null,'TRADE_APP','CCURR_APP')
1245 AND cr.currency_code = DECODE( p_Report.Currency,
1246 null,cr.currency_code,
1247 p_Report.Currency)
1248 AND ra.application_type||'' = 'CASH'
1249 AND (p_Report.Trade = 'Y'
1250 OR p_Report.Ccurr = 'Y')
1251 AND ( ra.posting_control_id = DECODE( p_Report.PostedStatus,
1252 'BOTH', ra.posting_control_id,
1253 'UNPOSTED', -3,
1254 -8888 )
1255 OR
1256 ra.posting_control_id <> decode( p_Report.PostedStatus,
1257 'BOTH', -8888,
1258 'POSTED', -3,
1259 ra.posting_control_id) )
1260 AND NVL(ra.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
1261 BETWEEN
1262 DECODE( p_Report.PostedStatus,
1263 'BOTH', nvl(ra.gl_posted_date,to_date('01-01-1952',
1264 'DD-MM-YYYY')),
1265 'UNPOSTED',nvl(ra.gl_posted_date,to_date('01-01-1952',
1266 'DD-MM-YYYY')),
1267 'POSTED', decode( p_Report.PostedDateFrom ,
1268 null, nvl(ra.gl_posted_date,to_date('01-01-1952',
1269 'DD-MM-YYYY')),
1270 p_Report.PostedDateFrom))
1271 AND
1272 DECODE( p_Report.PostedStatus,
1273 'BOTH', nvl(ra.gl_posted_date,to_date('01-01-1952',
1274 'DD-MM-YYYY')),
1275 'UNPOSTED',nvl(ra.gl_posted_date,to_date('01-01-1952',
1276 'DD-MM-YYYY')),
1277 'POSTED', decode( p_Report.PostedDateTo,
1278 null, nvl(ra.gl_posted_date,to_date('01-01-1952',
1279 'DD-MM-YYYY')),
1280 p_Report.PostedDateTo))
1281 AND ra.receivable_application_id+0 < p_Report.NxtReceivableApplicationId;
1282 --
1283 l_Count NUMBER :=0;
1284
1285 BEGIN
1286 arp_standard.debug( ' ' );
1287 arp_standard.debug( ' AR_RECEIVABLE_APPLICATIONS (non-app)...' );
1288 FOR RRa IN CRa
1289 LOOP
1290 -- bug3718694 Call CreateInterim procedure to insert record into
1291 -- ar_journal_interim .
1292 DECLARE
1293 l_interim_rec ar_journal_interim%ROWTYPE;
1294
1295 BEGIN
1296 l_interim_rec.status := 'NEW';
1297 l_interim_rec.actual_flag := 'A';
1298 l_interim_rec.request_id := p_Report.ReqId;
1299 l_interim_rec.created_by := p_Report.CreatedBy;
1300 l_interim_rec.date_created := TRUNC( SYSDATE );
1301 l_interim_rec.set_of_books_id := p_Report.SetOfBooksId;
1302 l_interim_rec.je_source_name := 'Receivables';
1303
1304 IF RRa.AmountAppFrom IS NULL
1305 THEN
1306 l_interim_rec.je_category_name := 'Trade Receipts';
1307 ELSE
1308 l_interim_rec.je_category_name := 'Cross Currency';
1309 END IF;
1310
1311 l_interim_rec.transaction_date := RRa.TrxDate;
1312 l_interim_rec.accounting_date := RRa.GlDate;
1313 l_interim_rec.currency_code := RRa.CurrencyCode;
1314 l_interim_rec.code_combination_id := RRa.CodeCombinationId;
1315
1316 IF RRa.amount < 0
1317 THEN
1318 l_interim_rec.entered_dr := -nvl(RRa.AmountAppFrom,RRa.amount);
1319 ELSE
1320 l_interim_rec.entered_cr := nvl(RRa.AmountAppFrom,RRa.amount);
1321 END IF;
1322
1323 IF RRa.AcctdAmount < 0
1324 THEN
1325 l_interim_rec.accounted_dr := -RRa.AcctdAmount;
1326 ELSE
1327 l_interim_rec.accounted_cr := RRa.AcctdAmount;
1328 END IF;
1329
1330 l_interim_rec.reference10 := RRa.CatMeaning;
1331 l_interim_rec.reference21 := p_Report.ReqId;
1332 l_interim_rec.reference22 := RRa.CashReceiptId;
1333 l_interim_rec.reference23 := RRa.ReceivableApplicationId;
1334 l_interim_rec.reference24 := RRa.ReceiptNumber;
1335 l_interim_rec.reference25 := NULL;
1336 l_interim_rec.reference26 := RRa.CustomerNumber;
1337 l_interim_rec.reference27 := RRa.PayFromCustomer;
1338
1339 IF RRa.AmountAppFrom IS NULL
1340 THEN
1341 l_interim_rec.reference28 := 'TRADE';
1342 ELSE
1343 l_interim_rec.reference28 := 'CROSS CURR';
1344 END IF;
1345
1346 IF RRa.AmountAppFrom IS NULL
1347 THEN
1348 l_interim_rec.reference29 := 'TRADE_APP';
1349 ELSE
1350 l_interim_rec.reference29 := 'CCURR_APP';
1351 END IF;
1352
1353 l_interim_rec.reference30 := 'AR_RECEIVABL_APPLICATIONS';
1354 l_interim_rec.org_id := RRa.OrgId;
1355
1356 CreateInterim ( l_interim_rec ) ;
1357
1358 EXCEPTION
1359 WHEN OTHERS THEN
1360 arp_standard.debug( 'Exception:ReportNonDistApplications.INSERT:' );
1361 arp_standard.debug('RRa.CashReceiptId:'||RRa.CashReceiptId );
1362 arp_standard.debug('RRa.ReceiptNumber:'||RRa.ReceiptNumber );
1363 arp_standard.debug('RRa.PayFromCustomer:'||RRa.PayFromCustomer );
1364 arp_standard.debug('RRa.CustomerNumber:'||RRa.CustomerNumber );
1365 arp_standard.debug('RRa.CurrencyCode:'||RRa.CurrencyCode );
1366 arp_standard.debug('RRa.ReceivableApplicationId:'||RRa.ReceivableApplicationId );
1367 arp_standard.debug('RRa.GlDate:'||RRa.GlDate );
1368 arp_standard.debug('RRa.TrxDate:'||RRa.TrxDate );
1369 arp_standard.debug('RRa.Amount:'||RRa.Amount );
1370 arp_standard.debug('RRa.AcctdAmount:'||RRa.AcctdAmount );
1371 arp_standard.debug('RRa.CodeCombinationId:'||RRa.CodeCombinationId );
1372 arp_standard.debug('RRa.Status:'||RRa.Status );
1373 arp_standard.debug('RRa.CatMeaning:'||RRa.CatMeaning );
1374 RAISE;
1375 END;
1376 --
1377 l_Count := l_Count + 1;
1378 END LOOP;
1379 arp_standard.debug( ' '||l_Count||' lines selected' );
1380 EXCEPTION
1381 WHEN OTHERS THEN
1382 arp_standard.debug( 'Exception:ReportNonDistApplications:' );
1383 RAISE;
1384 END;
1385 --
1386 /*---------------------------------------------------------------------------*
1387 | PRIVATE PROCEDURE |
1388 | ReportDistributedApplications |
1389 | |
1390 | DESCRIPTION |
1391 | post unposted ar_receivable_applications records |
1392 | post unposted ar_receivable_applications records |
1393 | We need to have ORDER BY clause in the select statement because |
1394 | when comparing with GL Transfer entries, they need to match, |
1395 | If order by is not used, there will be rounding difference. |
1396 | |
1397 | PARAMETERS |
1398 | |
1399 | EXCEPTIONS RAISED |
1400 | |
1401 | ERRORS RAISED |
1402 | |
1403 | KNOWN BUGS |
1404 | |
1405 | NOTES |
1406 | |
1407 | HISTORY |
1408 | 23-Jul-1993 Alan Fothergill Created |
1409 *---------------------------------------------------------------------------*/
1410 PROCEDURE ReportDistributedApplications( p_Report IN ReportParametersType ) IS
1411 CURSOR CRa IS
1412 SELECT ra.ROWID ra_rowid,
1413 DECODE(
1414 ra.application_type,
1415 'CM', ctcm.customer_trx_id,
1416 'CASH',cr.cash_receipt_id ) CashReceiptId,
1417 DECODE(
1418 ra.application_type,
1419 'CM', ctcm.trx_number,
1420 'CASH',cr.receipt_number ) ReceiptNumber,
1421 DECODE(
1422 ra.application_type,
1423 'CM', ctcm.bill_to_customer_id,
1424 'CASH',cr.pay_from_customer ) PayFromCustomer,
1425 cust.account_number CustomerNumber,
1426 ct.invoice_currency_code CurrencyCode,
1427 DECODE(
1428 ra.application_type,
1429 'CM', NVL(ctcm.exchange_rate,1),
1430 'CASH',NVL(crh.exchange_rate,1) ) ExchangeRate,
1431 DECODE(
1432 l.lookup_code,
1433 '1', 'N',
1434 '2', 'Y'
1435 ) CmPsIdFlag,
1436 DECODE(
1437 l.lookup_code,
1438 '1', ra.applied_payment_schedule_id,
1439 '2', ra.payment_schedule_id
1440 ) PaymentScheduleId,
1441 ctt.type Class,
1442 ct.trx_number TrxNumber,
1443 ra.receivable_application_id ReceivableApplicationId,
1444 ra.apply_date TrxDate,
1445 ra.gl_date GlDate,
1446 ra.application_type AppType,
1447 DECODE(
1448 l.lookup_code,
1449 '1', ra.amount_applied,
1450 '2', -ra.amount_applied
1451 ) Amount,
1452 DECODE(
1453 l.lookup_code,
1454 '1', ra.acctd_amount_applied_from,
1455 '2', -ra.acctd_amount_applied_from
1456 ) AcctdAmount,
1457 DECODE(
1458 l.lookup_code,
1459 '1', NVL(ra.line_applied,0),
1460 '2', NVL(-ra.line_applied,0)
1461 ) LineApplied,
1462 DECODE(
1463 l.lookup_code,
1464 '1', NVL(ra.tax_applied,0),
1465 '2', NVL(-ra.tax_applied,0)
1466 ) TaxApplied,
1467 DECODE(
1468 l.lookup_code,
1469 '1', NVL(ra.freight_applied,0),
1470 '2', NVL(-ra.freight_applied,0)
1471 ) FreightApplied,
1472 DECODE(
1473 l.lookup_code,
1474 '1', NVL(ra.receivables_charges_applied,0),
1475 '2', NVL(-ra.receivables_charges_applied,0)
1476 ) ChargesApplied,
1477 l_cat.meaning CatMeaning,
1478 ra.posting_control_id PostingControlId,
1479 ct.org_id OrgID
1480 FROM ar_receivable_applications ra,
1481 ra_cust_trx_types ctt,
1482 ra_customer_trx ct,
1483 ar_cash_receipts cr,
1484 ar_cash_receipt_history crh,
1485 ra_customer_trx ctcm,
1486 ar_lookups l,
1487 ar_lookups l_cat,
1488 hz_cust_accounts cust
1489 WHERE ra.gl_date BETWEEN p_Report.GlDateFrom
1490 AND p_Report.GLDateTo
1491 AND nvl(ra.postable,'Y') = 'Y'
1492 AND nvl(ra.confirmed_flag,'Y') = 'Y'
1493 AND ra.status||'' = 'APP'
1494 AND ra.cash_receipt_id = cr.cash_receipt_id(+)
1495 AND ra.cash_receipt_history_id = crh.cash_receipt_history_id(+)
1496 AND ra.customer_trx_id = ctcm.customer_trx_id(+)
1497 AND ctcm.previous_customer_trx_id IS NULL
1498 AND ra.applied_customer_trx_id = ct.customer_trx_id
1499 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
1500 AND l.lookup_type = 'AR_CARTESIAN_JOIN'
1501 AND (
1502 ( l.lookup_code ='1' )
1503 OR
1504 ( l.lookup_code = '2'
1505 AND
1506 ra.application_type = 'CM' )
1507 )
1508 AND cust.cust_account_id = DECODE( ra.application_type,
1509 'CM', ctcm.bill_to_customer_id,
1510 cr.pay_from_customer )
1511 AND l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
1512 AND l_cat.lookup_code = decode( ra.application_type,
1513 'CM', decode( l.lookup_code,
1514 '1', 'CMAPP_APP',
1515 '2', 'CMAPP_REC'),
1516 'TRADE_APP')
1517 AND ct.invoice_currency_code = DECODE( p_Report.Currency,
1518 null,ct.invoice_currency_code,
1519 p_Report.Currency)
1520 AND ( ( p_Report.Trade = 'Y' AND ra.application_type||'' = 'CASH' )
1521 OR
1522 ( p_Report.CMApp = 'Y' AND ra.application_type||'' = 'CM' ) )
1523 AND ( ra.posting_control_id = DECODE( p_Report.PostedStatus,
1524 'BOTH', ra.posting_control_id,
1525 'UNPOSTED', -3,
1526 -8888 )
1527 OR
1528 ra.posting_control_id <> decode( p_Report.PostedStatus,
1529 'BOTH', -8888,
1530 'POSTED', -3,
1531 ra.posting_control_id) )
1532 AND NVL(ra.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
1533 BETWEEN
1534 DECODE( p_Report.PostedStatus,
1535 'BOTH', nvl(ra.gl_posted_date,to_date('01-01-1952',
1536 'DD-MM-YYYY')),
1537 'UNPOSTED',nvl(ra.gl_posted_date,to_date('01-01-1952',
1538 'DD-MM-YYYY')),
1539 'POSTED', decode( p_Report.PostedDateFrom ,
1540 null, nvl(ra.gl_posted_date,to_date('01-01-1952',
1541 'DD-MM-YYYY')),
1542 p_Report.PostedDateFrom))
1543 AND
1544 DECODE( p_Report.PostedStatus,
1545 'BOTH', nvl(ra.gl_posted_date,to_date('01-01-1952',
1546 'DD-MM-YYYY')),
1547 'UNPOSTED',nvl(ra.gl_posted_date,to_date('01-01-1952',
1548 'DD-MM-YYYY')),
1549 'POSTED', decode( p_Report.PostedDateTo,
1550 null, nvl(ra.gl_posted_date,to_date('01-01-1952',
1551 'DD-MM-YYYY')),
1552 p_Report.PostedDateTo))
1553 AND ra.receivable_application_id+0 < p_Report.NxtReceivableApplicationId
1554 ORDER BY ra.receivable_application_id, l.lookup_code;
1555 --
1556 l_Rowid ROWID;
1557 l_Receipt ReceiptType;
1558 l_Trx TrxType;
1559 l_App ApplicationType;
1560 l_AppAmount ApplicationAmountType;
1561 l_Count NUMBER :=0;
1562 l_Class VARCHAR2(20);
1563 BEGIN
1564 arp_standard.debug( ' ' );
1565 arp_standard.debug( ' AR_RECEIVABLE_APPLICATION (app)...' );
1566 OPEN CRa;
1567 LOOP
1568 FETCH CRa
1569 INTO l_rowid,
1570 l_Receipt.CashReceiptId,
1571 l_Receipt.ReceiptNumber,
1572 l_Receipt.PayFromCustomer,
1573 l_Receipt.CustomerNumber,
1574 l_Receipt.CurrencyCode,
1575 l_Receipt.ExchangeRate,
1576 l_Trx.CmPsIdFlag,
1577 l_Trx.PaymentScheduleId,
1578 l_Class,
1579 l_Trx.TrxNumber,
1580 l_App.ReceivableApplicationId,
1581 l_App.TrxDate,
1582 l_App.GlDate,
1583 l_App.AppType,
1584 l_AppAmount.Amount,
1585 l_AppAmount.AcctdAmount,
1586 l_AppAmount.LineApplied,
1587 l_AppAmount.TaxApplied,
1588 l_AppAmount.FreightApplied,
1589 l_AppAmount.ChargesApplied,
1590 l_App.CatMeaning,
1591 l_App.PostingControlId,
1592 l_Trx.OrgId;
1593 EXIT WHEN CRa%NOTFOUND;
1594 --
1595 IF (l_Class = 'CM') OR (l_Trx.CmPsIdFlag = 'Y')
1596 THEN
1597 DistributeApplicationType( p_Report, l_Receipt, l_Trx, l_App, 'INVOICE', l_AppAmount.Amount, l_AppAmount.AcctdAmount );
1598 ELSE
1599 DistributeLTFApplication( p_Report, l_Receipt, l_Trx, l_App, l_AppAmount );
1600 END IF;
1601 --
1602 IF l_Trx.CmPsIdFlag <> 'Y'
1603 THEN
1604 l_Count := l_Count + 1;
1605 END IF;
1606 --
1607 END LOOP;
1608 CLOSE Cra;
1609 arp_standard.debug( ' '||l_Count||' lines selected' );
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 arp_standard.debug( 'Exception:ReportDistributedApplications:' );
1613 Output( l_AppAmount );
1614 Output( l_App );
1615 Output( l_Trx );
1616 Output( l_Receipt );
1617 RAISE;
1618 END;
1619 --
1620 --
1621 -- finds unposted cash receipt history records in the period.
1622 --
1623 --
1624 /*---------------------------------------------------------------------------*
1625 | PRIVATE PROCEDURE |
1626 | ReportCashReceiptHistory |
1627 | DESCRIPTION |
1628 | cash receipt history records |
1629 | PARAMETERS |
1630 | |
1631 | EXCEPTIONS RAISED |
1632 | |
1633 | ERRORS RAISED |
1634 | |
1635 | KNOWN BUGS |
1636 | |
1637 | NOTES |
1638 | This is implemented as two cursors one to select cash receipt history|
1639 | the other to select reversals. It had to be implemented this way |
1640 | because FOR UPDATE OF is not allowed in a UNION |
1641 | The two selects must be maintained in parallel, as the InsertIntoAR |
1642 | relies on the ROWTYPE of each select cursor being the same |
1643 | |
1644 | HISTORY |
1645 | 12-Apr-1994 D Chu Created |
1646 | 21-Mar-1995 C Aldamiz Modified for 10.6 |
1647 *---------------------------------------------------------------------------*/
1648 PROCEDURE ReportCashReceiptHistory( p_Report IN ReportParametersType ) IS
1649 CURSOR CCrh IS
1650 SELECT crh.ROWID CrhRowid,
1651 crh.cash_receipt_history_id CashReceiptHistoryId,
1652 crh.cash_receipt_id CashReceiptId,
1653 cr.receipt_number ReceiptNumber,
1654 cr.pay_from_customer PayFromCustomer,
1655 cust.account_number CustomerNumber,
1656 DECODE
1657 (
1658 cr.type,
1659 'MISC', 'MISC',
1660 'TRADE'
1661 ) ModifiedType,
1662 nvl(d.amount_dr, -d.amount_cr) Amount,
1663 nvl(d.acctd_amount_dr, -d.acctd_amount_cr) AcctdAmount,
1664 d.code_combination_id AccountCodeCombinationId,
1665 crh.gl_date GlDate,
1666 crh.trx_date TrxDate,
1667 cr.currency_code CurrencyCode,
1668 DECODE
1669 (
1670 cr.type,
1671 'MISC', 'Misc Receipts',
1672 'Trade Receipts'
1673 ) Category,
1674 l_cat.meaning CatMeaning,
1675 d.source_type SourceType,
1676 cr.org_id OrgId
1677 FROM ar_cash_receipt_history crh,
1678 ar_cash_receipts cr,
1679 hz_cust_accounts cust,
1680 ar_lookups l_cat,
1681 ar_distributions d
1682 WHERE crh.gl_date BETWEEN p_Report.GlDateFrom
1683 AND p_Report.GLDateTo
1684 AND crh.postable_flag = 'Y'
1685 AND cr.cash_receipt_id = crh.cash_receipt_id
1686 AND cust.cust_account_id(+) = cr.pay_from_customer
1687 AND l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
1688 AND l_cat.lookup_code = decode( cr.type,
1689 'MISC', 'MISC_',
1690 'TRADE_')||'CASH'
1691 AND cr.currency_code = DECODE( p_Report.Currency,
1692 null,cr.currency_code,
1693 p_Report.Currency)
1694 AND ( ( p_Report.Trade = 'Y' AND cr.type = 'CASH' )
1695 OR
1696 ( p_Report.Misc = 'Y' AND cr.type = 'MISC' ) )
1697 AND ( crh.posting_control_id = DECODE( p_Report.PostedStatus,
1698 'BOTH', crh.posting_control_id,
1699 'UNPOSTED', -3,
1700 -8888 )
1701 OR
1702 crh.posting_control_id <> decode( p_Report.PostedStatus,
1703 'BOTH', -8888,
1704 'POSTED', -3,
1705 crh.posting_control_id))
1706 AND NVL(crh.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
1707 BETWEEN
1708 DECODE( p_Report.PostedStatus,
1709 'BOTH', nvl(crh.gl_posted_date,to_date('01-01-1952',
1710 'DD-MM-YYYY')),
1711 'UNPOSTED',nvl(crh.gl_posted_date,to_date('01-01-1952',
1712 'DD-MM-YYYY')),
1713 'POSTED', decode( p_Report.PostedDateFrom ,
1714 null, nvl(crh.gl_posted_date,to_date('01-01-1952',
1715 'DD-MM-YYYY')),
1716 p_Report.PostedDateFrom))
1717 AND
1718 DECODE( p_Report.PostedStatus,
1719 'BOTH', nvl(crh.gl_posted_date,to_date('01-01-1952',
1720 'DD-MM-YYYY')),
1721 'UNPOSTED',nvl(crh.gl_posted_date,to_date('01-01-1952',
1722 'DD-MM-YYYY')),
1723 'POSTED', decode( p_Report.PostedDateTo,
1724 null, nvl(crh.gl_posted_date,to_date('01-01-1952',
1725 'DD-MM-YYYY')),
1726 p_Report.PostedDateTo))
1727 AND crh.cash_receipt_history_id = d.source_id
1728 AND d.source_table = 'CRH'
1729 AND crh.cash_receipt_history_id+0 < p_Report.NxtCashReceiptHistoryId;
1730
1731 --
1732 RCrh CCrh%ROWTYPE;
1733 l_Count NUMBER :=0;
1734 --
1735 -- bug3718694 Call CreateInterim procedure to insert record into
1736 -- ar_journal_interim .
1737 PROCEDURE InsertIntoAR( RCrh IN CCrh%ROWTYPE ) IS
1738 l_interim_rec ar_journal_interim%ROWTYPE;
1739 BEGIN
1740 l_interim_rec.status := 'NEW';
1741 l_interim_rec.actual_flag := 'A';
1742 l_interim_rec.request_id := p_Report.ReqId;
1743 l_interim_rec.created_by := p_Report.CreatedBy;
1744 l_interim_rec.date_created := TRUNC( SYSDATE );
1745 l_interim_rec.set_of_books_id := p_Report.SetOfBooksId;
1746 l_interim_rec.je_source_name := 'Receivables';
1747 l_interim_rec.je_category_name := RCrh.Category;
1748 l_interim_rec.transaction_date := RCrh.TrxDate;
1749 l_interim_rec.accounting_date := RCrh.GlDate;
1750 l_interim_rec.currency_code := RCrh.CurrencyCode;
1751 l_interim_rec.code_combination_id := RCrh.AccountCodeCombinationId;
1752
1753 IF RCrh.Amount < 0
1754 THEN
1755 l_interim_rec.entered_cr := -RCrh.Amount ;
1756 ELSE
1757 l_interim_rec.entered_dr := RCrh.Amount ;
1758 END IF;
1759
1760 IF RCrh.AcctdAmount < 0
1761 THEN
1762 l_interim_rec.accounted_cr := -RCrh.AcctdAmount ;
1763 ELSE
1764 l_interim_rec.accounted_dr := RCrh.AcctdAmount ;
1765 END IF;
1766
1767 l_interim_rec.reference10 := RCrh.CatMeaning;
1768 l_interim_rec.reference21 := p_Report.ReqId;
1769 l_interim_rec.reference22 := RCrh.CashReceiptId;
1770 l_interim_rec.reference23 := RCrh.CashReceiptHistoryId;
1771 l_interim_rec.reference24 := RCrh.ReceiptNumber;
1772 l_interim_rec.reference25 := NULL;
1773 l_interim_rec.reference26 := RCrh.CustomerNumber;
1774 l_interim_rec.reference27 := RCrh.PayFromCustomer;
1775 l_interim_rec.reference28 := RCrh.ModifiedType;
1776 l_interim_rec.reference29 := RCrh.ModifiedType||'_'||RCrh.SourceType;
1777 l_interim_rec.reference30 := 'AR_CASH_RECEIPT_HISTORY';
1778
1779 l_interim_rec.org_id := RCrh.OrgId;
1780
1781 CreateInterim(l_interim_rec) ;
1782
1783 EXCEPTION
1784 WHEN OTHERS THEN
1785 arp_standard.debug( 'InsertIntoAR:' );
1786 RAISE;
1787 END;
1788 --
1789 -- This is the actual ReportCashReceiptHistory body
1790 --
1791 BEGIN
1792 arp_standard.debug( ' ' );
1793 arp_standard.debug( ' AR_CASH_RECEIPT_HISTORY...' );
1794 OPEN CCrh;
1795 LOOP
1796 FETCH CCrh
1797 INTO RCrh;
1798 EXIT WHEN CCrh%NOTFOUND;
1799 InsertIntoAR( RCrh );
1800 l_Count := l_Count + 1;
1801 END LOOP;
1802 CLOSE CCrh;
1803 arp_standard.debug( ' '||l_Count||' lines selected' );
1804 --
1805 EXCEPTION
1806 WHEN OTHERS THEN
1807 arp_standard.debug( 'ReportCashReceiptHistory:' );
1808 RAISE;
1809 END;
1810 --
1811 --
1812 /*---------------------------------------------------------------------------*
1813 | PRIVATE PROCEDURE |
1814 | ReportMiscCashDistributions |
1815 | DESCRIPTION |
1816 | post unposted ar_misc_cash_distributions records |
1817 | within the posting range |
1818 | |
1819 | PARAMETERS |
1820 | |
1821 | EXCEPTIONS RAISED |
1822 | |
1823 | ERRORS RAISED |
1824 | |
1825 | KNOWN BUGS |
1826 | |
1827 | NOTES |
1828 | |
1829 | HISTORY |
1830 | 12-Apr-1994 D Chu Created |
1831 *---------------------------------------------------------------------------*/
1832 PROCEDURE ReportMiscCashDistributions( p_Report IN ReportParametersType ) IS
1833 CURSOR CMcd IS
1834 SELECT mcd.ROWID McdRowid,
1835 mcd.misc_cash_distribution_id MiscCashDistributionId,
1836 cr.cash_receipt_id CashReceiptId,
1837 cr.receipt_number ReceiptNumber,
1838 mcd.amount amount,
1839 mcd.acctd_amount acctd_amount,
1840 mcd.code_combination_id code_combination_id,
1841 mcd.gl_date gl_date,
1842 mcd.apply_date trx_date,
1843 cr.currency_code currency_code,
1844 'Misc Receipts' category,
1845 l_cat.meaning CatMeaning,
1846 cr.org_id OrgId
1847 FROM ar_misc_cash_distributions mcd,
1848 ar_cash_receipts cr,
1849 ar_lookups l_cat
1850 WHERE mcd.gl_date BETWEEN p_Report.GlDateFrom
1851 AND p_Report.GLDateTo
1852 AND cr.cash_receipt_id = mcd.cash_receipt_id
1853 AND l_cat.lookup_type = 'ARRGTA_FUNCTION_MAPPING'
1854 AND l_cat.lookup_code = 'MISC_MISC'
1855 AND cr.currency_code = DECODE( p_Report.Currency,
1856 null,cr.currency_code,
1857 p_Report.Currency)
1858 AND p_Report.Misc = 'Y'
1859 AND ( mcd.posting_control_id = DECODE( p_Report.PostedStatus,
1860 'BOTH', mcd.posting_control_id,
1861 'UNPOSTED', -3,
1862 -8888 )
1863 OR
1864 mcd.posting_control_id <> decode( p_Report.PostedStatus,
1865 'BOTH', -8888,
1866 'POSTED', -3,
1867 mcd.posting_control_id) )
1868 AND NVL(mcd.gl_posted_date,to_date('01-01-1952','DD-MM-YYYY'))
1869 BETWEEN
1870 DECODE( p_Report.PostedStatus,
1871 'BOTH', nvl(mcd.gl_posted_date,to_date('01-01-1952',
1872 'DD-MM-YYYY')),
1873 'UNPOSTED',nvl(mcd.gl_posted_date,to_date('01-01-1952',
1874 'DD-MM-YYYY')),
1875 'POSTED', decode( p_Report.PostedDateFrom ,
1876 null, nvl(mcd.gl_posted_date,to_date('01-01-1952',
1877 'DD-MM-YYYY')),
1878 p_Report.PostedDateFrom))
1879 AND
1880 DECODE( p_Report.PostedStatus,
1881 'BOTH', nvl(mcd.gl_posted_date,to_date('01-01-1952',
1882 'DD-MM-YYYY')),
1883 'UNPOSTED',nvl(mcd.gl_posted_date,to_date('01-01-1952',
1884 'DD-MM-YYYY')),
1885 'POSTED', decode( p_Report.PostedDateTo,
1886 null, nvl(mcd.gl_posted_date,to_date('01-01-1952',
1887 'DD-MM-YYYY')),
1888 p_Report.PostedDateTo))
1889 AND mcd.misc_cash_distribution_id+0 < p_Report.NxtMiscCashDistributionId;
1890 --
1891 l_Count NUMBER :=0;
1892
1893 -- bug3718694
1894 l_interim_rec ar_journal_interim%ROWTYPE;
1895 l_interim_rec_null ar_journal_interim%ROWTYPE;
1896 BEGIN
1897 arp_standard.debug( ' ' );
1898 arp_standard.debug( ' AR_MISC_CASH_DISTRIBUTIONS...' );
1899 FOR RMcd IN CMcd
1900 LOOP
1901 -- bug3718694 Call CreateInterim procedure to insert record into
1902 -- ar_journal_interim .
1903 l_interim_rec := l_interim_rec_null ;
1904
1905 l_interim_rec.status := 'NEW';
1906 l_interim_rec.actual_flag := 'A';
1907 l_interim_rec.request_id := p_Report.ReqId;
1908 l_interim_rec.created_by := p_Report.CreatedBy;
1909 l_interim_rec.date_created := TRUNC( SYSDATE );
1910 l_interim_rec.set_of_books_id := p_Report.SetOfBooksId;
1911 l_interim_rec.je_source_name := 'Receivables';
1912 l_interim_rec.je_category_name := RMcd.category;
1913 l_interim_rec.transaction_date := RMcd.trx_date;
1914 l_interim_rec.accounting_date := RMcd.gl_date;
1915 l_interim_rec.currency_code := RMcd.currency_code;
1916 l_interim_rec.code_combination_id := RMcd.code_combination_id;
1917
1918 IF RMcd.amount < 0
1919 THEN
1920 l_interim_rec.entered_dr := -RMcd.amount ;
1921 ELSE
1922 l_interim_rec.entered_cr := RMcd.amount ;
1923 END IF;
1924
1925 IF RMcd.acctd_amount < 0
1926 THEN
1927 l_interim_rec.accounted_dr := -RMcd.acctd_amount ;
1928 ELSE
1929 l_interim_rec.accounted_cr := RMcd.acctd_amount ;
1930 END IF;
1931
1932 l_interim_rec.reference10 := RMcd.CatMeaning;
1933 l_interim_rec.reference21 := p_Report.ReqId;
1934 l_interim_rec.reference22 := RMcd.CashReceiptId;
1935 l_interim_rec.reference23 := RMcd.MiscCashDistributionId;
1936 l_interim_rec.reference24 := RMcd.ReceiptNumber;
1937 l_interim_rec.reference28 := 'MISC';
1938 l_interim_rec.reference29 := 'MISC_MISC';
1939 l_interim_rec.reference30 := 'AR_MISC_CASH_DISTRIBUTIONS';
1940 l_interim_rec.org_id := RMcd.OrgId;
1941
1942 CreateInterim (l_interim_rec);
1943 --
1944 l_Count := l_Count + 1;
1945 END LOOP;
1946 arp_standard.debug( ' '||l_Count||' lines selected' );
1947 EXCEPTION
1948 WHEN OTHERS THEN
1949 arp_standard.debug( 'ReportMiscCashDistributions:' );
1950 RAISE;
1951 END;
1952 --
1953 --
1954 -- rollback any posting activity that is related to the given
1955 -- balance id
1956 --
1957 /*---------------------------------------------------------------------------*
1958 | PRIVATE PROCEDURE |
1959 | ClearOOB |
1960 | DESCRIPTION |
1961 | rollback (by deleting and updating) any posting activity that is |
1962 | related to the given balance id |
1963 | PARAMETERS |
1964 | |
1965 | EXCEPTIONS RAISED |
1966 | |
1967 | ERRORS RAISED |
1968 | |
1969 | KNOWN BUGS |
1970 | |
1971 | NOTES |
1972 | |
1973 | HISTORY |
1974 | 23-Jul-1993 Alan Fothergill Created |
1975 *---------------------------------------------------------------------------*/
1976 PROCEDURE ClearOOB( p_Report IN ReportParametersType,
1977 p_BalanceId IN NUMBER,
1978 p_CategoryCode IN VARCHAR2 ) IS
1979 BEGIN
1980 --
1981 DELETE FROM ar_journal_interim
1982 WHERE reference22 = p_BalanceId
1983 AND reference28 = p_CategoryCode
1984 AND set_of_books_id = p_Report.SetOfBooksId
1985 AND request_id = p_Report.ReqId;
1986 --
1987 EXCEPTION
1988 WHEN OTHERS THEN
1989 IF PG_DEBUG in ('Y', 'C') THEN
1990 arp_standard.debug( 'ClearOOB' );
1991 END IF;
1992 RAISE;
1993 END ClearOOB;
1994
1995 --
1996 --
1997 --
1998 --
1999 /*---------------------------------------------------------------------------*
2000 | PRIVATE PROCEDURE |
2001 | CheckBalance |
2002 | DESCRIPTION |
2003 | Checks that the records inserted into ar_journal_interim balance for each |
2004 | BalanceId (reference22). |
2005 | Any BalanceId that fails to balance will be reported on |
2006 | (via arp_standard.debug), and will be deleted with ClearOOB |
2007 | PARAMETERS |
2008 | |
2009 | EXCEPTIONS RAISED |
2010 | |
2011 | ERRORS RAISED |
2012 | |
2013 | KNOWN BUGS |
2014 | |
2015 | NOTES |
2016 | |
2017 | HISTORY |
2018 | 23-Jul-1993 Alan Fothergill Created |
2019 *---------------------------------------------------------------------------*/
2020 PROCEDURE CheckBalance( p_Report IN ReportParametersType ) IS
2021 CURSOR CBal IS
2022 SELECT MIN(i.currency_code) CurrencyCode,
2023 i.reference22 BalanceId,
2024 i.reference28 CategoryCode,
2025 SUM(nvl(i.entered_dr,0)) SumEnteredDr,
2026 SUM(nvl(i.entered_cr,0)) SumEnteredCr,
2027 SUM(nvl(i.accounted_dr,0)) SumAccountedDr,
2028 SUM(nvl(i.accounted_cr,0)) SumAccountedCr
2029 FROM ar_journal_interim i
2030 WHERE i.request_id = p_Report.ReqId
2031 AND i.set_of_books_id = p_Report.SetOfBooksId
2032 GROUP BY i.reference28,
2033 i.reference22
2034 HAVING SUM( NVL(i.entered_dr,0) ) <> SUM( NVL(i.entered_cr, 0 ))
2035 OR SUM( NVL(i.accounted_dr,0)) <> SUM( NVL(i.accounted_cr, 0));
2036 --
2037 CURSOR CInt( p_BalanceId NUMBER, p_CategoryCode VARCHAR2 ) IS
2038 SELECT i.entered_dr EnteredDr,
2039 i.entered_cr EnteredCr,
2040 i.accounted_dr AccountedDr,
2041 i.accounted_cr AccountedCr,
2042 i.reference30 TableName,
2043 i.reference23 Id
2044 FROM ar_journal_interim i
2045 WHERE i.request_id = p_Report.ReqId
2046 AND i.set_of_books_id = p_Report.SetOfBooksId
2047 AND i.reference22 = p_BalanceId
2048 AND i.reference28 = p_CategoryCode
2049 ORDER BY i.reference30,
2050 i.reference23;
2051 --
2052 l_ReceivableApplicationId ar_receivable_applications.receivable_application_id%TYPE;
2053 BEGIN
2054 --
2055 arp_standard.debug( ' ----------------------------------------------------' );
2056 arp_standard.debug( ' Checking DR/CR balance...' );
2057 arp_standard.debug( '' );
2058 --
2059 FOR RBal IN CBal
2060 LOOP
2061 arp_standard.debug( 'Out Of balance:'||Rbal.CurrencyCode||' BalanceId:'||RBal.BalanceId );
2062 FOR RInt IN CInt( RBal.BalanceId, Rbal.CategoryCode )
2063 LOOP
2064 IF RInt.TableName = 'AR_CASH_BASIS_DISTRIBUTIONS'
2065 THEN
2066 SELECT cbd.receivable_application_id
2067 INTO l_ReceivableApplicationId
2068 FROM ar_cash_basis_distributions cbd
2069 WHERE cbd.cash_basis_distribution_id = RInt.Id;
2070 ELSE
2071 l_ReceivableApplicationId := NULL;
2072 END IF;
2073 arp_standard.debug( RPAD( Rint.TableName, 30)||
2074 RPAD( RInt.Id, 15 )||
2075 LPAD( NVL(TO_CHAR(RInt.EnteredDr), ' '),15)||
2076 LPAD( NVL(TO_CHAR(RInt.EnteredCr), ' '),15)||
2077 LPAD( NVL(TO_CHAR(RInt.AccountedDr), ' '),15)||
2078 LPAD( NVL(TO_CHAR(RInt.AccountedCr), ' '),15)||
2079 ' '||l_ReceivableApplicationId );
2080 END LOOP;
2081 arp_standard.debug( RPAD( 'SUM:', 30)||
2082 RPAD( ' ', 15 )||
2083 LPAD( NVL(TO_CHAR(RBal.SumEnteredDr), ' '),15)||
2084 LPAD( NVL(TO_CHAR(RBal.SumEnteredCr), ' '),15)||
2085 LPAD( NVL(TO_CHAR(RBal.SumAccountedDr), ' '),15)||
2086 LPAD( NVL(TO_CHAR(RBal.SumAccountedCr), ' '),15) );
2087 arp_standard.debug( '--------------------------------------------------------------------------------------------------------------------' );
2088 ClearOOB( p_Report, RBal.BalanceId, RBal.CategoryCode );
2089 END LOOP;
2090 EXCEPTION
2091 WHEN OTHERS THEN
2092 arp_standard.debug( 'CheckBalance:' );
2093 RAISE;
2094 END;
2095 --
2096 --
2097 -- Delete from ar_cash_basis_distributions for records inserted
2098 --
2099 /*---------------------------------------------------------------------------*
2100 | PRIVATE PROCEDURE |
2101 | DeleteFromCBD |
2102 | DESCRIPTION |
2103 | Delete recrods from ar_cash_basis_distributions inserted this run |
2104 | PARAMETERS |
2105 | |
2106 | EXCEPTIONS RAISED |
2107 | |
2108 | ERRORS RAISED |
2109 | |
2110 | KNOWN BUGS |
2111 | |
2112 | NOTES |
2113 | |
2114 | HISTORY |
2115 | 13-Apr-1994 D Chu Created |
2116 *---------------------------------------------------------------------------*/
2117 PROCEDURE DeleteFromCBD( p_Report IN ReportParametersType ) IS
2118 BEGIN
2119 --
2120 DELETE FROM ar_cash_basis_distributions
2121 WHERE posting_control_id = - ( p_Report.ReqId +100 );
2122 --
2123 EXCEPTION
2124 WHEN OTHERS THEN
2125 IF PG_DEBUG in ('Y', 'C') THEN
2126 arp_standard.debug( 'DeleteFromCBD' );
2127 END IF;
2128 RAISE;
2129 END DeleteFromCBD;
2130 --
2131 --
2132 PROCEDURE CheckUpgradedCustomer(p_FromRel9 OUT NOCOPY VARCHAR2) IS
2133 l_ColumnId NUMBER :=0;
2134 --
2135 CURSOR SelColumn IS
2136 SELECT column_id
2137 FROM user_tab_columns
2138 WHERE table_name = 'AR_CASH_BASIS_DISTRIBUTIONS'
2139 AND column_name = 'CUSTOMER_TRX_LINE_ID';
2140 --
2141 BEGIN
2142
2143 OPEN SelColumn;
2144 FETCH SelColumn into l_ColumnId;
2145
2146 IF SelColumn%NOTFOUND
2147 THEN
2148 p_FromRel9 := 'N';
2149 ELSE
2150 p_FromRel9 := 'Y';
2151 END IF;
2152 CLOSE SelColumn;
2153 EXCEPTION
2154 WHEN OTHERS THEN
2155 arp_standard.debug( 'CheckUpgradedCustomer:' );
2156 RAISE;
2157 END;
2158
2159 --
2160 --
2161 PROCEDURE Report( p_Report IN ReportParametersType ) IS
2162 l_FromRel9 VARCHAR2(1);
2163 BEGIN
2164 CheckUpgradedCustomer( l_FromRel9 );
2165 IF l_FromRel9 = 'Y'
2166 THEN
2167 arp_standard.fnd_message('AR_WWS_CASH_BASIS');
2168 ELSE
2169 IF p_Report.Trade = 'Y' OR
2170 p_Report.Misc = 'Y' OR
2171 p_Report.Ccurr = 'Y' OR
2172 p_Report.CMApp= 'Y'
2173 THEN
2174 --
2175 IF p_Report.Trade = 'Y' OR
2176 p_Report.Ccurr = 'Y' OR
2177 p_Report.Misc = 'Y'
2178 THEN
2179 ReportCashReceiptHistory( p_Report );
2180 END IF;
2181 --
2182 IF p_Report.Misc = 'Y'
2183 THEN
2184 ReportMiscCashDistributions( p_Report );
2185 END IF;
2186 --
2187 IF p_Report.Trade = 'Y' OR
2188 p_Report.Ccurr = 'Y'
2189 THEN
2190 ReportNonDistApplications( p_Report );
2191 END IF;
2192 --
2193 IF p_Report.Trade = 'Y' OR
2194 p_Report.Ccurr = 'Y' OR
2195 p_Report.CMApp = 'Y'
2196 THEN
2197 ReportDistributedApplications( p_Report );
2198 END IF;
2199 --
2200 IF p_Report.ChkBalance = 'Y' AND
2201 p_Report.PostedStatus <> 'POSTED'
2202 THEN
2203 CheckBalance( p_Report );
2204 END IF;
2205 DeleteFromCBD( p_Report );
2206 END IF;
2207 END IF;
2208 EXCEPTION
2209 WHEN OTHERS THEN
2210 arp_standard.debug( 'Exception:arp_cash_basis_je_report.Report( p_Report ):'||sqlerrm );
2211 RAISE_APPLICATION_ERROR( -20000, sqlerrm||'$Revision: 120.5 $:Report( p_Report ):' );
2212 END;
2213 --
2214 --
2215 PROCEDURE Report( p_ReqId NUMBER,
2216 p_ChkBalance VARCHAR2,
2217 p_GlDateFrom DATE,
2218 p_GlDateTo DATE,
2219 p_SetOfBooksId NUMBER,
2220 p_UnallocatedRevCcid NUMBER,
2221 p_CreatedBy NUMBER,
2222 p_NxtCashReceiptHistoryId NUMBER,
2223 p_NxtReceivableApplicationId NUMBER,
2224 p_NxtMiscCashDistributionId NUMBER,
2225 p_NxtAdjustmentId NUMBER,
2226 p_NxtCustTrxLineGlDistId NUMBER,
2227 p_Currency VARCHAR2,
2228 p_Inv VARCHAR2,
2229 p_DM VARCHAR2,
2230 p_CB VARCHAR2,
2231 p_CM VARCHAR2,
2232 p_CMApp VARCHAR2,
2233 p_Adj VARCHAR2,
2234 p_Trade VARCHAR2,
2235 p_Misc VARCHAR2,
2236 p_Ccurr VARCHAR2,
2237 p_PostedStatus VARCHAR2,
2238 p_PostedDateFrom DATE,
2239 p_PostedDateTo DATE ) IS
2240 l_Report ReportParametersType;
2241 BEGIN
2242 l_Report.ReqId := p_ReqId;
2243 l_Report.ChkBalance := p_ChkBalance;
2244 l_Report.GlDateFrom := p_GlDateFrom;
2245 l_Report.GlDateTo := p_GlDateTo;
2246 l_Report.SetOfBooksId := p_SetOfBooksId;
2247 l_Report.UnallocatedRevCcid := p_UnallocatedRevCcid;
2248 l_Report.CreatedBy := p_CreatedBy;
2249 l_Report.NxtCashReceiptHistoryId := p_NxtCashReceiptHistoryId;
2250 l_Report.NxtReceivableApplicationId := p_NxtReceivableApplicationId;
2251 l_Report.NxtMiscCashDistributionId := p_NxtMiscCashDistributionId;
2252 l_Report.NxtAdjustmentId := p_NxtAdjustmentId;
2253 l_Report.NxtCustTrxLineGlDistId := p_NxtCustTrxLineGlDistId;
2254 l_Report.Currency := p_Currency;
2255 l_Report.Inv := p_Inv;
2256 l_Report.DM := p_DM;
2257 l_Report.CB := p_CB;
2258 l_Report.CM := p_CM;
2259 l_Report.CMApp := p_CMApp;
2260 l_Report.Adj := p_Adj;
2261 l_Report.Trade := p_Trade;
2262 l_Report.Misc := p_Misc;
2263 l_Report.Ccurr := p_Ccurr;
2264 l_Report.PostedStatus := p_PostedStatus;
2265 l_Report.PostedDateFrom := p_PostedDateFrom;
2266 l_Report.PostedDateTo := p_PostedDateTo;
2267 --
2268 Report( l_Report );
2269 EXCEPTION
2270 WHEN OTHERS THEN
2271 arp_standard.debug( 'Exception:arp_cash_basis_je_report.Report( ... ):'||sqlerrm );
2272 RAISE_APPLICATION_ERROR( -20000, sqlerrm||'$Revision: 120.5 $:Report( ... ):' );
2273 END;
2274 --
2275 END arp_cash_basis_je_report;