[Home] [Help]
PACKAGE BODY: APPS.ARP_CASH_BASIS_ACCOUNTING
Source
1 PACKAGE BODY arp_cash_basis_accounting AS
2 /* $Header: ARPLCBPB.pls 120.15 2006/02/24 22:42:40 kmaheswa 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 DocSequenceId ar_cash_receipts.doc_sequence_id%TYPE,
11 DocSequenceValue ar_cash_receipts.doc_sequence_value%TYPE,
12 PayFromCustomer ar_cash_receipts.pay_from_customer%TYPE,
13 CurrencyCode ar_cash_receipts.currency_code%TYPE,
14 ExchangeRate NUMBER
15 );
16 --
17 -- RECORD holder of information about the Trx to which the application
18 -- is being applied when CM_PSID_Flag is 'N'
19 -- If the CM_PSID_Flag is 'Y', this means that the PaymentScheduleId holds
20 -- the ps_id of the CM if the application_type is 'CM', but the class
21 -- and the TrxNumber still holds the invoice that the CM applies to.
22 --
23 TYPE TrxType IS RECORD
24 (
25 PaymentScheduleId ar_payment_schedules.payment_schedule_id%TYPE,
26 CmPsIdFlag VARCHAR2(1),
27 Class ar_payment_schedules.class%TYPE,
28 TrxNumber ra_customer_trx.trx_number%TYPE,
29 OrgId ra_customer_trx.org_id%TYPE
30 );
31 --
32 -- RECORD holder for pertinent information from a receivable application
33 -- of status = 'APP'
34 TYPE ApplicationType IS RECORD
35 (
36 ReceivableApplicationId ar_receivable_applications.receivable_application_id%TYPE,
37 GLDate DATE, -- the gl date of the application
38 UssglTransactionCode ar_receivable_applications.ussgl_transaction_code%TYPE,
39 AppType ar_receivable_applications.application_type%TYPE
40 );
41 --
42 -- holds ApplicationAmount values
43 --
44 TYPE ApplicationAmountType IS RECORD
45 (
46 Amount NUMBER,
47 AmountAppFrom NUMBER,
48 AcctdAmount NUMBER,
49 LineApplied NUMBER,
50 TaxApplied NUMBER,
51 FreightApplied NUMBER,
52 ChargesApplied NUMBER
53 );
54 --
55 TYPE IdType IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
56 TYPE AmountType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
57 TYPE VC15Type IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
58 TYPE VC3Type IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
59 --
60 --
61 ArpcbpError EXCEPTION;
62 PRAGMA EXCEPTION_INIT( ArpcbpError, -20000 );
63 --
64 -- private procedures
65 --
66 --
67 -- Procedures to write Record Types using dbms_output
68 --
69 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
70
71 PROCEDURE Output( p IN ReceiptType ) IS
72 BEGIN
73 IF PG_DEBUG in ('Y', 'C') THEN
74 arp_standard.debug('Output: ' || 'Receipt Type' );
75 arp_standard.debug('Output: ' || 'CashReceiptId:'||p.CashReceiptId );
76 arp_standard.debug('Output: ' || 'ReceiptNumber:'||p.ReceiptNumber );
77 arp_standard.debug('Output: ' || 'DocSequenceId:'||p.DocSequenceId );
78 arp_standard.debug('Output: ' || 'DocSequenceValue:'||p.DocSequenceValue );
79 arp_standard.debug('Output: ' || 'PayFromCustomer:'||p.PayFromCustomer );
80 arp_standard.debug('Output: ' || 'CurrencyCode:'||p.CurrencyCode );
81 arp_standard.debug('Output: ' || 'ExchangeRate:'||p.ExchangeRate );
82 arp_standard.debug('Output: ' || '' );
83 END IF;
84 END;
85 --
86 PROCEDURE Output( p IN TrxType ) IS
87 BEGIN
88 IF PG_DEBUG in ('Y', 'C') THEN
89 arp_standard.debug('Output: ' || 'TrxType' );
90 arp_standard.debug('Output: ' || 'CmPsIdFlag:'||p.CmPsIdFlag );
91 arp_standard.debug('Output: ' || 'PaymentScheduleId:'||p.PaymentScheduleId );
92 arp_standard.debug('Output: ' || 'Class:'||p.Class );
93 arp_standard.debug('Output: ' || 'TrxNumber:'||p.TrxNumber );
94 arp_standard.debug('Output: ' || 'OrgId:'||p.OrgId );
95 arp_standard.debug('Output: ' || '' );
96 END IF;
97 END;
98 --
99 PROCEDURE Output( p IN ApplicationType ) IS
100 BEGIN
101 IF PG_DEBUG in ('Y', 'C') THEN
102 arp_standard.debug('Output: ' || 'ApplicationType' );
103 arp_standard.debug('Output: ' || 'ReceivableApplicationId:'||p.ReceivableApplicationId );
104 arp_standard.debug('Output: ' || 'GLDate:'||p.GLDate );
105 arp_standard.debug('Output: ' || 'UssglTransactionCode:'||p.UssglTransactionCode );
106 arp_standard.debug('Output: ' || 'AppType:'||p.AppType );
107 arp_standard.debug('Output: ' || '' );
108 END IF;
109 END;
110 --
111 PROCEDURE Output( p IN ApplicationAmountType ) IS
112 BEGIN
113 IF PG_DEBUG in ('Y', 'C') THEN
114 arp_standard.debug('Output: ' || 'ApplicationAmountType' );
115 arp_standard.debug('Output: ' || 'Amount:'||p.Amount );
116 arp_standard.debug('Output: ' || 'AmountAppfrom :'||p.AmountAppFrom);
117 arp_standard.debug('Output: ' || 'AcctdAmount:'||p.AcctdAmount );
118 arp_standard.debug('Output: ' || 'LineApplied:'||p.LineApplied );
119 arp_standard.debug('Output: ' || 'TaxApplied:'||p.TaxApplied );
120 arp_standard.debug('Output: ' || 'FreightApplied:'||p.FreightApplied );
121 arp_standard.debug('Output: ' || 'ChargesApplied:'||p.ChargesApplied );
122 arp_standard.debug('Output: ' || '' );
123 END IF;
124 END;
125 --
126 --
127 /*---------------------------------------------------------------------------*
128 | PRIVATE PROCEDURE |
129 | CreateInterface |
130 | DESCRIPTION |
131 | Insert record into gl_interface |
132 | PARAMETERS |
133 | |
134 | EXCEPTIONS RAISED |
135 | |
136 | ERRORS RAISED |
137 | |
138 | KNOWN BUGS |
139 | |
140 | NOTES |
141 | |
142 | HISTORY |
143 | 13-JUL-2004 Hiroshi Yoshihara bug3692482 Created |
144 *---------------------------------------------------------------------------*/
145 PROCEDURE CreateInterface( p_interface_rec IN gl_interface%ROWTYPE) IS
146 BEGIN
147 INSERT INTO gl_interface
148 (
149 created_by,
150 date_created,
151 status,
152 actual_flag,
153 group_id,
154 set_of_books_id,
155 user_je_source_name,
156 user_je_category_name,
157 accounting_date,
158 subledger_doc_sequence_id,
159 subledger_doc_sequence_value,
160 ussgl_transaction_code,
161 currency_code,
162 code_combination_id,
163 entered_dr,
164 entered_cr,
165 accounted_dr,
166 accounted_cr,
167 reference1,
168 reference10,
169 reference21,
170 reference22,
171 reference23,
172 reference24,
173 reference25,
174 reference26,
175 reference27,
176 reference28,
177 reference29,
178 reference30
179 )
180 VALUES
181 (
182 p_interface_rec.created_by,
183 p_interface_rec.date_created,
184 p_interface_rec.status,
185 p_interface_rec.actual_flag,
186 p_interface_rec.group_id,
187 p_interface_rec.set_of_books_id,
188 p_interface_rec.user_je_source_name,
189 p_interface_rec.user_je_category_name,
190 p_interface_rec.accounting_date,
191 p_interface_rec.subledger_doc_sequence_id,
192 p_interface_rec.subledger_doc_sequence_value,
193 p_interface_rec.ussgl_transaction_code,
194 p_interface_rec.currency_code,
195 p_interface_rec.code_combination_id,
196 p_interface_rec.entered_dr,
197 p_interface_rec.entered_cr,
198 p_interface_rec.accounted_dr,
199 p_interface_rec.accounted_cr,
200 p_interface_rec.reference1,
201 p_interface_rec.reference10,
202 p_interface_rec.reference21,
203 p_interface_rec.reference22,
204 p_interface_rec.reference23,
205 p_interface_rec.reference24,
206 p_interface_rec.reference25,
207 p_interface_rec.reference26,
208 p_interface_rec.reference27,
209 p_interface_rec.reference28,
210 p_interface_rec.reference29,
211 p_interface_rec.reference30
212 );
213 EXCEPTION
214 WHEN OTHERS THEN
215 arp_standard.debug( 'Exception:CreateInterface:' );
216 RAISE;
217 END;
218 --
219 --
220 --
221 /*---------------------------------------------------------------------------*
222 | PRIVATE PROCEDURE |
223 | CurrentCBDApplications |
224 | |
225 | DESCRIPTION |
226 | Populates the TABLE types passed to the procedure with the total |
227 | amounts of a given type (LINE, TAX, FREIGHT, CHARGES, INVOICE) |
228 | that are currently applied to a given payment schedule. |
229 | The information is extracted from the ar_cash_basis_distributions |
230 | table, and is returned ordered by source ('GL' then 'ADJ') and |
231 | source_id (ra_cust_trx_line_gl_dist_id or adjustment_id ) |
232 | PARAMETERS |
233 | p_Post RECORD type that contains posting parameters
234 | p_ps_id Payment Schedule Id for which current |
235 | applications are required |
236 | p_type The type of current applications required - |
237 | LINE, TAX, FREIGHT, CHARGES, INVOICE |
238 | Source OUT PL/SQL TABLE for the source of the line |
239 | SourceId OUT PL/SQL TABLE for the source id of the line |
240 | Amount OUT PL/SQL TABLE for the amount of the line |
241 | NextElement OUT BINARY_INTEGER Stores the Next Element to be |
242 | populated in the PL/SQL table (also, the number |
243 | of elements in the table |
244 | TotalAmount SUM of the Amounts |
245 | |
246 | EXCEPTIONS RAISED |
247 | |
248 | ERRORS RAISED |
249 | |
250 | KNOWN BUGS |
251 | |
252 | NOTES |
253 | |
254 | HISTORY |
255 | 23-Jul-1993 Alan Fothergill Created |
256 *---------------------------------------------------------------------------*/
257 PROCEDURE CurrentCBDApplications( p_Post IN PostingParametersType,
258 p_ps_id IN NUMBER,
259 p_type IN VARCHAR2,
260 Source OUT NOCOPY VC3Type,
261 SourceId OUT NOCOPY IdType,
262 Amount OUT NOCOPY AmountType,
263 NextElement OUT NOCOPY BINARY_INTEGER,
264 TotalAmount OUT NOCOPY NUMBER,
265 TotalUnallocatedAmt OUT NOCOPY NUMBER
266 ) IS
267 l_TotalAmount NUMBER := 0;
268 l_TotalUnallocatedAmt NUMBER := 0;
269 l_NextElement BINARY_INTEGER := 0;
270
271 CURSOR CCA IS
272 SELECT SUM( cbd.amount ) Amount,
273 cbd.source Source,
274 cbd.source_id SourceId,
275 NVL(SUM( DECODE(cbd.source,
276 'UNA', cbd.amount, 0 )),0) UnallocatedAmt
277 FROM ar_cash_basis_distributions cbd
278 WHERE cbd.payment_schedule_id = p_ps_id
279 AND cbd.type = p_type
280 AND cbd.posting_control_id+0 > 0
281 GROUP BY cbd.source,
282 cbd.source_id
283 ORDER BY DECODE( cbd.source, 'GL', 1,
284 'ADJ',2,
285 'UNA',3 ),
286 cbd.source_id;
287 --{BUG4301323
288 /*
289 CURSOR CCA_MRC IS
290 SELECT SUM( cbd.amount ) Amount,
291 cbd.source Source,
292 cbd.source_id SourceId,
293 NVL(SUM( DECODE(cbd.source,
294 'UNA', cbd.amount, 0 )),0) UnallocatedAmt
295 FROM ar_cash_basis_dists_mrc_v cbd
296 WHERE cbd.payment_schedule_id = p_ps_id
297 AND cbd.type = p_type
298 AND cbd.posting_control_id+0 > 0
299 GROUP BY cbd.source,
300 cbd.source_id
301 ORDER BY DECODE( cbd.source, 'GL', 1,
302 'ADJ',2,
303 'UNA',3 ),
304 cbd.source_id;
305 */
306 PROCEDURE Output( p_RCa IN CCA%ROWTYPE ) IS
307 BEGIN
308 IF PG_DEBUG in ('Y', 'C') THEN
309 arp_standard.debug('Output: ' || 'CCA%ROWTYPE' );
310 arp_standard.debug('Output: ' || 'Amount:'||p_RCa.Amount );
311 arp_standard.debug('Output: ' || 'Source:'||p_RCA.Source );
312 arp_standard.debug('Output: ' || 'SourceId:'||p_RCa.SourceId );
313 arp_standard.debug('Output: ' || '--------------------------------' );
314 END IF;
315 END Output;
316
317 BEGIN
318 -- bug3769452 modified IF condition
319 IF (NVL(p_Post.SetOfBooksType,'P') <> 'R')
320 THEN
321 /* Primary SOB */
322 FOR RCA IN CCA LOOP
323 BEGIN
324 Source( l_NextElement ) := RCA.Source;
325 SourceId( l_NextElement ) := RCA.SourceId;
326 Amount( l_NextElement ) := RCA.Amount;
327
328 l_TotalAmount := l_TotalAmount + RCA.Amount;
329 l_NextElement := l_NextElement + 1;
330 l_TotalUnallocatedAmt := l_TotalUnallocatedAmt + RCA.UnallocatedAmt;
331
332 EXCEPTION
333 WHEN OTHERS THEN
334 arp_standard.debug( 'Exception:CurrentCBDApplications.Loop:');
335 Output( Rca );
336 RAISE;
337 END;
338 END LOOP;
339 TotalAmount := l_TotalAmount;
340 NextElement := l_NextElement;
341 TotalUnallocatedAmt := l_TotalUnallocatedAmt;
342 ELSE /* Reporting */
343 --{BUG4301323
344 NULL;
345 /*
346 FOR RCA IN CCA_MRC LOOP
347 BEGIN
348 Source( l_NextElement ) := RCA.Source;
349 SourceId( l_NextElement ) := RCA.SourceId;
350 Amount( l_NextElement ) := RCA.Amount;
351
352 l_TotalAmount := l_TotalAmount + RCA.Amount;
353 l_NextElement := l_NextElement + 1;
354 l_TotalUnallocatedAmt := l_TotalUnallocatedAmt + RCA.UnallocatedAmt;
355
356 EXCEPTION
357 WHEN OTHERS THEN
358 arp_standard.debug( 'Exception:CurrentCBDApplications.Loop:');
359 Output( Rca );
360 RAISE;
361 END;
362 END LOOP;
363 */
364 END IF;
365 EXCEPTION
366 WHEN OTHERS THEN
367 arp_standard.debug( ' Exception:CurrentCBDApplications:' );
368 arp_standard.debug( 'l_NextElement:'||l_NextElement );
369 arp_standard.debug( 'l_TotalAmount:'||l_TotalAmount );
370 RAISE;
371 END;
372 --
373 --
374 --
375 --
376 /*---------------------------------------------------------------------------*
377 | PRIVATE PROCEDURE |
378 | CurrentRevDistribution |
379 | DESCRIPTION |
380 | Populates PL/SQL tables with the current 'revenue' distribution of |
381 | the given Payment Schedule for a given type. |
382 | For type 'LINE' the distributions include ra_cust_trx_line_gl_dist |
383 | records of account_class 'REV' and adjustments where |
384 | line_adjusted IS NOT NULL |
385 | For type 'TAX' the distributions include ra_cust_trx_line_gl_dist |
386 | records of account_class 'TAX' and adjustments where |
387 | tax_adjusted IS NOT NULL |
388 | For type 'FREIGHT' the distributions include ra_cust_trx_line_gl_dist|
389 | records of account_class 'FREIGHT' and adjustments where |
390 | freight_adjusted IS NOT NULL |
391 | For type 'CHARGES' the distributions include adjustments where |
392 | receivables_charges_adjusted IS NOT NULL |
393 | For type 'INVOICE' the distributions include all |
394 | ra_cust_trx_line_gl_dist records and all adjustments |
395 | The lines are returned ordered by Source ('GL' then 'ADJ' and then |
396 | source_id (ra_cust_trx_line_gl_dist_id or adjustment_id ) |
397 | |
398 | PARAMETERS |
399 | p_Post RECORD type that contains posting parameters |
400 | p_ps_id payment_schedule_id for which distribution is required |
401 | p_type type of distributions required LINE, TAX, FREIGHT, |
402 | CHARGES or INVOICE |
403 | NextElement Next element to be populated in table (also number of |
404 | elements in table) |
405 | Source OUT PL/SQL TABLE for the source of the line |
406 | SourceId OUT PL/SQL TABLE for the source id of the line |
407 | Amount OUT PL/SQL TABLE for the amount of the line |
408 | NextElement OUT BINARY_INTEGER Stores the Next Element to be |
409 | populated in the PL/SQL table (also, the number |
410 | of elements in the table |
411 | TotalAmount SUM of the Amounts |
412 | EXCEPTIONS RAISED |
413 | |
414 | ERRORS RAISED |
415 | |
416 | KNOWN BUGS |
417 | |
418 | NOTES |
419 | |
420 | HISTORY |
421 | 23-Jul-1993 Alan Fothergill Created |
422 *---------------------------------------------------------------------------*/
423 PROCEDURE CurrentRevDistribution ( p_Post IN PostingParametersType,
424 p_ps_id IN NUMBER,
425 p_type IN VARCHAR2, -- 'LINE' 'TAX' 'FREIGHT' 'CHARGES' 'INVOICE'
426 NextElement OUT NOCOPY BINARY_INTEGER,
427 Source OUT NOCOPY VC3Type,
428 SourceId OUT NOCOPY IdType,
429 Ccid OUT NOCOPY IdType,
430 AccntClass OUT NOCOPY VC15Type,
431 Amount OUT NOCOPY AmountType,
432 TotalAmount OUT NOCOPY NUMBER
433 ) IS
434 l_customer_trx_id NUMBER(15);
435 l_term_fraction NUMBER;
436 l_currency_code VARCHAR2(15);
437 l_Amount NUMBER;
438 l_AmountReconcile NUMBER;
439 l_FirstInstallmentCode VARCHAR2(12);
440 l_NextElement BINARY_INTEGER := 0;
441 l_TotalAmount NUMBER := 0;
442 l_FirstInstallmentFlag VARCHAR2(1);
443 charges_adjusted NUMBER := 0;
444
445 CURSOR gl_dist_cursor( cp_ctid NUMBER, cp_type VARCHAR2 ) IS
446 SELECT ctlgd.cust_trx_line_gl_dist_id,
447 ctlgd.amount amount,
448 ctlgd.code_combination_id ccid,
449 substrb(decode(ctlgd.account_class,
450 'REV','LINE',
451 ctlgd.account_class),1,15) accntclass
452 FROM ra_cust_trx_line_gl_dist ctlgd
453 WHERE ctlgd.customer_trx_id = cp_ctid
454 AND ctlgd.account_class
455 IN ( 'REV', 'TAX', 'FREIGHT','CHARGES')
456 AND ctlgd.account_class = DECODE
457 (
458 cp_type,
459 'LINE', 'REV',
460 'TAX', 'TAX',
461 'FREIGHT', 'FREIGHT',
462 'CHARGES', 'CHARGES',
463 ctlgd.account_class
464 )
465 AND ctlgd.cust_trx_line_gl_dist_id+0 < p_Post.NxtCustTrxLineGlDistId
466 ORDER BY ctlgd.cust_trx_line_gl_dist_id;
467
468 CURSOR adj_cursor( cp_ps_id NUMBER, cp_type VARCHAR2 ) IS
469 SELECT a.adjustment_id adjustment_id,
470 DECODE
471 (
472 cp_type,
473 'LINE', nvl(a.line_adjusted,0),
474 'TAX', nvl(a.tax_adjusted,0),
475 'FREIGHT', nvl(a.freight_adjusted,0),
476 'CHARGES', nvl(a.receivables_charges_adjusted,0),
477 a.amount
478 ) amount,
479 a.code_combination_id ccid,
480 substrb(a.type,1,15) accntclass
481 FROM ar_adjustments a,
482 ra_customer_trx ct,
483 ra_cust_trx_types ctt
484 WHERE a.payment_schedule_id = cp_ps_id
485 AND a.receivables_trx_id <> -1
486 AND a.type = cp_type
487 AND a.status = 'A'
488 AND a.customer_trx_id = ct.customer_trx_id
489 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
490 AND a.adjustment_id+0 < p_Post.NxtAdjustmentId
491 ORDER BY a.adjustment_id;
492
493 BEGIN
494 -- first get the ps details
495 /* Bug 2967037 - no need to retrofit this - we are not using any MRC columns */
496 BEGIN
497 SELECT ps.customer_trx_id,
498 NVL(tl.relative_amount, 100 )/NVL( t.base_amount, 100 ),
499 t.first_installment_code,
500 ps.invoice_currency_code,
501 NVL
502 (
503 DECODE
504 (
505 p_Type,
506 'LINE', ps.amount_line_items_original,
507 'TAX', ps.tax_original,
508 'FREIGHT', ps.freight_original,
509 'CHARGES', ps.receivables_charges_charged,
510 'INVOICE', ps.amount_due_original,
511 0
512 ),
513 0
514 ),
515 DECODE
516 (
517 MIN(tl_first.sequence_num),
518 tl.sequence_num, 'Y',
519 'N'
520 ) first_installment_flag
521 INTO l_customer_trx_id,
522 l_term_fraction,
523 l_FirstInstallmentCode,
524 l_currency_code,
525 l_AmountReconcile,
526 l_FirstInstallmentFlag
527 FROM ar_payment_schedules ps,
528 ra_terms t,
529 ra_terms_lines tl,
530 ra_terms_lines tl_first
531 WHERE ps.payment_schedule_id = p_ps_id
532 AND tl.term_id(+) = ps.term_id
533 AND tl.sequence_num(+) = ps.terms_sequence_number
534 AND t.term_id(+) = tl.term_id
535 AND tl_first.term_id(+) = t.term_id
536 GROUP BY ps.customer_trx_id,
537 tl.relative_amount,
538 t.base_amount,
539 t.first_installment_code,
540 ps.invoice_currency_code,
541 ps.amount_line_items_original,
542 ps.tax_original,
543 ps.freight_original,
544 ps.receivables_charges_charged,
545 ps.amount_due_original,
546 tl.sequence_num;
547 EXCEPTION
548 WHEN OTHERS THEN
549 arp_standard.debug( 'Exception:CurrentRevDistribution.Select PS Details:' );
550 RAISE;
551 END;
552
553 FOR GlDistRecord IN gl_dist_cursor( l_customer_trx_id, p_type ) LOOP
554 IF l_FirstInstallmentFlag = 'Y'
555 AND l_FirstInstallmentCode = 'INCLUDE'
556 AND p_Type IN ('TAX','FREIGHT') THEN
557 l_Amount := GlDistRecord.Amount;
558 ELSE
559 l_Amount := arpcurr.CurrRound( GlDistRecord.amount * l_term_fraction, l_currency_code );
560 END IF;
561 Amount( l_NextElement ) := l_Amount;
562 Source( l_NextElement ) := 'GL';
563 SourceId( l_NextElement ) := GlDistRecord.cust_trx_line_gl_dist_id;
564 Ccid( l_NextElement ) := GlDistRecord.ccid;
565 AccntClass( l_NextElement ):= GlDistRecord.accntclass;
566 l_TotalAmount := l_TotalAmount + l_Amount;
567 l_NextElement := l_NextElement + 1;
568 END LOOP;
569
570
571 IF l_NextElement <> 0
572 THEN
573 IF ( p_type = 'CHARGES' ) AND ( l_TotalAmount <> 0 )
574 THEN
575 SELECT nvl(sum(nvl(receivables_charges_adjusted,0)),0)
576 INTO charges_adjusted
577 FROM ar_adjustments
578 WHERE payment_schedule_id = p_ps_id
579 AND status = 'A'
580 AND type in ('INVOICE','CHARGES');
581
582 l_AmountReconcile := l_AmountReconcile - charges_adjusted;
583 END IF;
584
585 IF ( p_type = 'CHARGES' ) AND ( l_TotalAmount = 0 )
586 THEN
587 l_AmountReconcile := 0;
588 END IF;
589
590 -- place the reconcile amount on to the last distribution
591 Amount( l_NextElement-1) := l_Amount + l_AmountReconcile - l_TotalAmount;
592 l_TotalAmount := l_AmountReconcile;
593 END IF;
594
595 -- next get adjustments that are NOT receipt-related
596 FOR AdjRecord IN adj_cursor( p_ps_id, p_type ) LOOP
597 Amount( l_NextElement ) := AdjRecord.amount;
598 Source( l_NextElement ) := 'ADJ';
599 SourceId( l_NextElement ) := AdjRecord.adjustment_id;
600 Ccid( l_NextElement ) := AdjRecord.ccid;
601 AccntClass( l_NextElement ) := AdjRecord.accntclass;
602 l_TotalAmount := l_TotalAmount + AdjRecord.Amount;
603 l_NextElement := l_NextElement + 1;
604 END LOOP;
605
606 TotalAmount := l_TotalAmount;
607 NextElement := l_NextElement;
608
609 EXCEPTION
610 WHEN OTHERS THEN
611 arp_standard.debug( 'Exception:CurrentRevDistribution:' );
612 arp_standard.debug( 'l_customer_trx_id:'||l_customer_trx_id );
613 arp_standard.debug( 'l_term_fraction:'||l_term_fraction );
614 arp_standard.debug( 'l_currency_code:'||l_currency_code );
615 arp_standard.debug( 'l_Amount:'||l_Amount );
616 arp_standard.debug( 'l_AmountReconcile:'||l_AmountReconcile );
617 arp_standard.debug( 'l_FirstInstallmentCode:'||l_FirstInstallmentCode );
618 arp_standard.debug( 'l_NextElement:'||l_NextElement );
619 arp_standard.debug( 'l_TotalAmount:'||l_TotalAmount );
620 arp_standard.debug( 'l_FirstInstallmentFlag:'||l_FirstInstallmentFlag );
621 RAISE;
622 END;
623
624 --
625 /*---------------------------------------------------------------------------*
626 | PRIVATE PROCEDURE |
627 | CreateDistribution |
628 | DESCRIPTION |
629 | Creates a distribution by inserting a record into |
630 | ar_cash_basis_distributions, and a record into gl_interface |
631 | PARAMETERS |
632 | |
633 | EXCEPTIONS RAISED |
634 | |
635 | ERRORS RAISED |
636 | |
637 | KNOWN BUGS |
638 | |
639 | NOTES |
640 | |
641 | HISTORY |
642 | 23-Jul-1993 Alan Fothergill Created |
643 *---------------------------------------------------------------------------*/
644 PROCEDURE CreateDistribution( p_Post IN PostingParametersType,
645 p_Receipt IN ReceiptType,
646 p_Trx IN TrxType,
647 p_App IN ApplicationType,
648 p_Amount IN NUMBER,
649 p_AcctdAmount IN NUMBER,
650 p_Source IN VARCHAR2,
651 p_SourceId IN NUMBER,
652 p_Type IN VARCHAR2,
653 p_Ccid IN NUMBER,
654 p_AccntClass IN VARCHAR2,
655 p_AmountAppFrom IN NUMBER ) IS
656 CashBasisDistributionId ar_cash_basis_distributions.cash_basis_distribution_id%TYPE;
657 l_gl_interface gl_interface%ROWTYPE;
658 l_gl_interface_null gl_interface%ROWTYPE;
659 BEGIN
660 IF p_Amount = 0 AND p_AcctdAmount = 0 THEN
661 RETURN;
662 END IF;
663
664 SELECT ar_cash_basis_distributions_s.NEXTVAL
665 INTO CashBasisDistributionId
666 FROM dual;
667
668 BEGIN
669
670 IF (NVL(p_Post.SetOfBooksType,'P') <> 'R')
671 THEN
672 /* Primary SOB */
673 INSERT INTO ar_cash_basis_distributions
674 (
675 cash_basis_distribution_id,
676 created_by,
677 creation_date,
678 last_updated_by,
679 last_update_date,
680 receivable_application_id,
681 source,
682 source_id,
683 type,
684 payment_schedule_id,
685 gl_date,
686 currency_code,
687 amount,
688 acctd_amount,
689 code_combination_id,
690 posting_control_id,
691 gl_posted_date,
692 receivable_application_id_cash,
693 org_id
694 )
695 VALUES
696 (
697 CashBasisDistributionId,
698 p_Post.CreatedBy,
699 TRUNC( SYSDATE ),
700 p_Post.CreatedBy,
701 TRUNC( SYSDATE ),
702 p_App.ReceivableApplicationId,
703 p_Source,
704 p_SourceId,
705 p_Type,
706 p_Trx.PaymentScheduleId,
707 p_App.GlDate,
708 p_Receipt.CurrencyCode,
709 p_Amount,
710 p_AcctdAmount,
711 p_Ccid,
712 p_Post.PostingControlId,
713 p_Post.GlPostedDate,
714 NULL,
715 p_Trx.OrgId
716 );
717
718 ELSE
719 /* Reporting */
720 --{BUG4301323
721 NULL;
722 -- INSERT INTO ar_mc_cash_basis_dists_all
723 -- (
724 -- set_of_books_id,
725 -- cash_basis_distribution_id,
726 -- created_by,
727 -- creation_date,
728 -- last_updated_by,
729 -- last_update_date,
730 -- receivable_application_id,
731 -- source,
732 -- source_id,
733 -- type,
734 -- payment_schedule_id,
735 -- gl_date,
736 -- currency_code,
737 -- amount,
738 -- acctd_amount,
739 -- code_combination_id,
740 -- posting_control_id,
741 -- gl_posted_date,
742 -- receivable_application_id_cash,
743 -- org_id
744 -- )
745 -- VALUES
746 -- (
747 -- p_Post.SetOfBooksId,
748 -- CashBasisDistributionId,
749 -- p_Post.CreatedBy,
750 -- TRUNC( SYSDATE ),
751 -- p_Post.CreatedBy,
752 -- TRUNC( SYSDATE ),
753 -- p_App.ReceivableApplicationId,
754 -- p_Source,
755 -- p_SourceId,
756 -- p_Type,
757 -- p_Trx.PaymentScheduleId,
758 -- p_App.GlDate,
759 -- p_Receipt.CurrencyCode,
760 -- p_Amount,
761 -- p_AcctdAmount,
762 -- p_Ccid,
763 -- p_Post.PostingControlId,
764 -- p_Post.GlPostedDate,
765 -- NULL,
766 -- ar_mc_info.org_id
767 -- );
768 --}
769 END IF;
770
771 EXCEPTION
772 WHEN OTHERS THEN
773 arp_standard.debug( 'Exception:CreateDistribution.InsertCBD:' );
774 RAISE;
775 END;
776
777 --{BUG4301323
778 -- Execute only for Primary
779 IF (NVL(p_Post.SetOfBooksType,'P') <> 'R')
780 THEN
781
782 BEGIN
783 /* bug3692482 replace insert stmt with CreateInterface procedure */
784 l_gl_interface := l_gl_interface_null;
785
786 l_gl_interface.created_by := p_Post.CreatedBy;
787 l_gl_interface.date_created := TRUNC( SYSDATE );
788 l_gl_interface.status := 'NEW';
789 l_gl_interface.actual_flag := 'A';
790 l_gl_interface.group_id := p_Post.PostingControlId;
791 l_gl_interface.set_of_books_id := p_Post.SetOfBooksId;
792 l_gl_interface.user_je_source_name := p_Post.UserSource;
793
794 IF p_AmountAppFrom IS NULL
795 THEN
796 l_gl_interface.user_je_category_name := p_Post.UserTrade ;
797 ELSE
798 l_gl_interface.user_je_category_name := p_Post.UserCcurr ;
799 END IF;
800
801 l_gl_interface.accounting_date := p_App.GlDate;
802 l_gl_interface.subledger_doc_sequence_id := p_Receipt.DocSequenceId;
803 l_gl_interface.subledger_doc_sequence_value := p_Receipt.DocSequenceValue;
804 l_gl_interface.ussgl_transaction_code := p_App.UssglTransactionCode;
805 l_gl_interface.currency_code := p_Receipt.CurrencyCode;
806 l_gl_interface.code_combination_id := p_Ccid;
807
808 IF p_Amount < 0
809 THEN
810 l_gl_interface.entered_dr := -p_Amount;
811 ELSE
812 l_gl_interface.entered_cr := p_Amount;
813 END IF;
814
815 IF p_AcctdAmount < 0
816 THEN
817 l_gl_interface.accounted_dr := -p_AcctdAmount;
818 ELSE
819 l_gl_interface.accounted_cr := p_AcctdAmount;
820 END IF;
821
822 l_gl_interface.reference1 := 'AR '||p_Post.PostingControlId;
823
824 IF p_Post.SummaryFlag = 'Y'
825 THEN
826 l_gl_interface.reference10 := NULL;
827 ELSE
828 IF P_App.AppType = 'CM'
829 THEN
830 l_gl_interface.reference10 := 'CM '||p_Receipt.ReceiptNumber||p_Post.NlsAppApplied||' '||p_Trx.Class|| ' '||p_trx.TrxNumber;
831 ELSE
832 l_gl_interface.reference10 := p_Post.NlsPreTradeApp||' '||p_Receipt.ReceiptNumber||p_Post.NlsAppApplied||' '||p_Trx.Class||' '||p_trx.TrxNumber||p_Post.NlsPostTradeApp;
833 END IF;
834 END IF;
835
836 l_gl_interface.reference21 := p_Post.PostingControlId;
837 l_gl_interface.reference22 := p_Receipt.CashReceiptId;
838 l_gl_interface.reference23 := CashBasisDistributionId;
839 l_gl_interface.reference24 := p_Receipt.ReceiptNumber;
840 l_gl_interface.reference25 := p_Trx.TrxNumber;
841 l_gl_interface.reference26 := p_Trx.Class;
842 l_gl_interface.reference27 := p_Receipt.PayFromCustomer;
843
844 IF P_App.AppType = 'CM'
845 THEN
846 l_gl_interface.reference28 := 'CMAPP';
847 ELSIF P_App.AppType = 'CASH'
848 THEN
849 IF p_AmountAppFrom IS NULL
850 THEN
851 l_gl_interface.reference28 := 'TRADE';
852 ELSE
853 l_gl_interface.reference28 := 'CCURR';
854 END IF;
855 END IF;
856
857 IF P_App.AppType = 'CASH'
858 THEN
859 IF p_AmountAppFrom IS NULL
860 THEN
861 l_gl_interface.reference29 := 'TRADE_APP_'||p_Trx.Class||'_'||p_Source||'_'||p_Type;
862 ELSE
863 l_gl_interface.reference29 := 'CCURR_APP_'||p_Trx.Class||'_'||p_Source||'_'||p_Type;
864 END IF;
865 ELSIF P_App.AppType = 'CM'
866 THEN
867 IF p_Trx.CmPsIdFlag = 'Y'
868 THEN
869 l_gl_interface.reference29 := 'CMAPP_REC_CM_'||p_Source||'_'||p_Type;
870 ELSE
871 l_gl_interface.reference29 := 'CMAPP_APP_'||p_Trx.Class||'_'||p_Source||'_'||p_Type;
872 END IF;
873 END IF;
874
875 l_gl_interface.reference30 := 'AR_CASH_BASIS_DISTRIBUTIONS';
876
877 CreateInterface( l_gl_interface );
878
879 EXCEPTION
880 WHEN OTHERS THEN
881 arp_standard.debug( 'Exception:CreateDistribution.InsertGl:' );
882 RAISE;
883 END;
884
885 END IF;
886 --}
887
888 EXCEPTION
889 WHEN OTHERS THEN
890 arp_standard.debug( 'Exception:CreateDistribution:' );
891 RAISE;
892 END;
893 --
894 --
895 /*---------------------------------------------------------------------------*
896 | PRIVATE PROCEDURE |
897 | DistributeApplicationType |
898 | DESCRIPTION |
899 | An amount of a certain type is distributed to a payment schedule |
900 | The procedure works by calculating what the pro-rated distributions |
901 | should be when the current application is included, working out |
902 | what the current applications are, and creating a distribution |
903 | for the difference. |
904 | Distributions are calculated and made at the line (gl dist or |
905 | adjustment) level (rather than the account level) |
906 | PARAMETERS |
907 | |
908 | EXCEPTIONS RAISED |
909 | |
910 | ERRORS RAISED |
911 | |
912 | KNOWN BUGS |
913 | |
914 | NOTES |
915 | |
916 | HISTORY |
917 | 23-Jul-1993 Alan Fothergill Created |
918 | 25-Aug-1993 Alan Fothergill If the total of distributions of the |
919 | invoice is zero, then post the |
920 | application to the CBPBALANCE account|
921 *---------------------------------------------------------------------------*/
922 PROCEDURE DistributeApplicationType( p_Post IN PostingParametersType,
923 p_Receipt IN ReceiptType,
924 p_Trx IN TrxType,
925 p_App IN ApplicationType,
926 p_Type IN VARCHAR2,
927 p_Amount IN NUMBER,
928 p_AcctdAmount IN NUMBER,
929 p_AmountAppFrom IN NUMBER ) IS
930 CBD_Source VC3Type;
931 CBD_SourceId IdType;
932 CBD_Amount AmountType;
933 CBD_NextElement BINARY_INTEGER;
934 CBD_TotalAmount NUMBER;
935 CBD_TotalUnallocatedAmt NUMBER;
936 --
937 CRD_Source VC3Type;
938 CRD_SourceId IdType;
939 CRD_Amount AmountType;
940 CRD_Ccid IdType;
941 CRD_AccntClass VC15Type;
942 CRD_NextElement BINARY_INTEGER;
943 CRD_TotalAmount NUMBER;
944 --
945 CBD_i BINARY_INTEGER;
946 CRD_i BINARY_INTEGER;
947 --
948 NewAppToLine NUMBER; -- the amount that will be applied to a line
949 -- after the current application has been made
950 RunningNewAppToLine NUMBER := 0; -- this is the running total of NewAppToLine
951 -- the final NewAppToLine is adjusted
952 -- so that the value of RunningNewAppToLine is equal to
953 -- GrandTotalApplied
954 GrandTotalApplied NUMBER;
955 AppToLineThisTime NUMBER; -- the actual amount posted, and stored in ar_cash_basis_distributions
956 AcctdAppToLineThisTime NUMBER;
957 RunningAppToLineThisTime NUMBER := 0;
958 RunningAcctdAppToLineThisTime NUMBER := 0;
959 -- Bug 1829871
960 MultipleAdjustmentsPresent BOOLEAN;
961 NumberOfAdjustments NUMBER := 0;
962 -- End Bug 1829871
963 BEGIN
964 CurrentRevDistribution ( p_Post,
965 p_Trx.PaymentScheduleId,
966 p_Type,
967 CRD_NextElement,
968 CRD_Source,
969 CRD_SourceId,
970 CRD_Ccid,
971 CRD_AccntClass,
972 CRD_Amount,
973 CRD_TotalAmount );
974 --
975 CurrentCBDApplications( p_Post,
976 p_Trx.PaymentScheduleId,
977 p_Type,
978 CBD_Source,
979 CBD_SourceId,
980 CBD_Amount,
981 CBD_NextElement,
982 CBD_TotalAmount,
983 CBD_TotalUnallocatedAmt );
984 --
985 CBD_i := 0;
986 CRD_i := 0;
987 GrandTotalApplied := CBD_TotalAmount + p_Amount;
988 --
989 --Bug 1829871
990 -- if number of adjustments are more than one, do the changes suggested
991 -- in bug 1397969
992 WHILE CRD_i <> CRD_NextElement
993 LOOP
994 if(CRD_Source(CRD_i) ='ADJ') then
995 NumberOfAdjustments := NumberOfAdjustments +1;
996 end if;
997 CRD_i := CRD_i + 1;
998 END LOOP;
999 if NumberOfAdjustments >1 then
1000 MultipleAdjustmentsPresent := TRUE;
1001 end if;
1002 CBD_i := 0;
1003 CRD_i := 0;
1004 -- End Bug 1829871
1005
1006 WHILE CRD_i <> CRD_NextElement
1007 LOOP
1008 IF ( CRD_i = CRD_NextElement - 1 ) AND
1009 ( CRD_TotalAmount <> 0 ) AND
1010 ( CBD_TotalUnallocatedAmt = 0 )
1011 THEN
1012 -- this is the final distribution if Total Revenue Distribution is non-zero
1013 -- and Cash Basis Clearing account is zero
1014 NewAppToLine := GrandTotalApplied - RunningNewAppToLine;
1015 ELSE
1016 IF CRD_TotalAmount = 0
1017 THEN
1018 NewAppToLine := 0;
1019 ELSE
1020 NewAppToLine := arpcurr.CurrRound( CRD_Amount( CRD_i ) * GrandTotalApplied/
1021 CRD_TotalAmount, p_Receipt.CurrencyCode );
1022
1023 -- if pro-rating formula comes up with an amount greater that the grandtotalapplied
1024 -- just make the 2 amounts equal
1025 -- Bug 1829871
1026 if(MultipleAdjustmentsPresent) then
1027 if abs(NewAppToLine) > abs(GrandTotalApplied) then
1028 NewApptoLine := GrandTotalApplied;
1029 -- 1397969 : since we've used up GrandTotalApplied, set CRD_TotalAmount to zero
1030 -- so that no additional entries are created
1031 CRD_TotalAmount := 0;
1032 end if;
1033 end if;
1034 END IF;
1035 END IF;
1036 RunningNewAppToLine := RunningNewAppToLine + NewAppToLine;
1037 --
1038 IF CBD_i <> CBD_NextElement AND
1039 CBD_Source( CBD_i ) = CRD_Source( CRD_i ) AND
1040 CBD_SourceId( CBD_i ) = CRD_SourceId( CRD_i )
1041 -- the above is acceptable. If the first boolean fails, then
1042 -- the remainder will not be executed. Therefore, at the limit
1043 -- when CBD_i = CBD_NextElement, the remaining expressions will not
1044 -- be evaluated (which would have caused an unitilaised element
1045 -- to be accessed)
1046 THEN
1047 -- amount to apply this time is equal to what the total application
1048 -- should be, minus the amount already applied
1049 AppToLineThisTime := NewAppToLine - CBD_Amount( CBD_i );
1050 CBD_i := CBD_i + 1;
1051 ELSE
1052 -- amount to apply this time is equal to what the total amount should
1053 -- be, because there is not an existing appliation to the line
1054 AppToLineThisTime := NewAppToLine;
1055 END IF;
1056 AcctdAppToLineThisTime := arpcurr.ReconcileAcctdAmounts( p_Receipt.ExchangeRate,
1057 p_Amount, -- total of distributions reconciles to the applied amount
1058 p_AcctdAmount,
1059 AppToLineThisTime,
1060 RunningAppToLineThisTime,
1061 RunningAcctdAppToLineThisTime );
1062 --
1063 IF AcctdAppToLineThisTime <> 0
1064 THEN
1065 CreateDistribution( p_Post,
1066 p_Receipt,
1067 p_Trx,
1068 p_App,
1069 AppToLineThisTime,
1070 AcctdAppToLineThisTime,
1071 CRD_Source( CRD_i ),
1072 CRD_SourceId( CRD_i ),
1073 p_Type,
1074 CRD_Ccid( CRD_i ),
1075 CRD_AccntClass( CRD_i ),
1076 p_AmountAppFrom );
1077 END IF;
1078 CRD_i := CRD_i + 1;
1079 END LOOP;
1080 --
1081 -- Now if Total Revenue Distribution is zero
1082 -- OR if Cash Basis Clearing account is non-zero,
1083 -- Then we need to post to the Cash Basis Clearing account
1084 --
1085 IF ( CRD_TotalAmount = 0 ) OR
1086 ( CBD_TotalUnallocatedAmt <> 0 )
1087 THEN
1088 NewAppToLine := GrandTotalApplied - RunningNewAppToLine;
1089 RunningNewAppToLine := RunningNewAppToLine + NewAppToLine;
1090 AppToLineThisTime := NewAppToLine - CBD_TotalUnallocatedAmt;
1091 AcctdAppToLineThisTime :=
1092 arpcurr.ReconcileAcctdAmounts(
1093 p_Receipt.ExchangeRate,
1094 p_Amount, -- total of distributions reconciles to the applied amount
1095 p_AcctdAmount,
1096 AppToLineThisTime,
1097 RunningAppToLineThisTime,
1098 RunningAcctdAppToLineThisTime );
1099 --
1100 IF AcctdAppToLineThisTime <> 0
1101 THEN
1102 --
1103 CreateDistribution( p_Post,
1104 p_Receipt,
1105 p_Trx,
1106 p_App,
1107 AppToLineThisTime,
1108 AcctdAppToLineThisTime,
1109 'UNA',
1110 p_Post.SetOfBooksId,
1111 p_Type,
1112 p_Post.UnallocatedRevCcid,
1113 'INVOICE',
1114 p_AmountAppFrom );
1115 END IF;
1116 END IF;
1117 EXCEPTION
1118 WHEN OTHERS THEN
1119 arp_standard.debug( 'Exception:DistributeApplicationType:' );
1120 RAISE;
1121 END;
1122 --
1123 --
1124 --
1125 /*---------------------------------------------------------------------------*
1126 | PRIVATE PROCEDURE |
1127 | DistributeLTFApplication |
1128 | DESCRIPTION |
1129 | Distribute the Line, Tax, Freight and Charges of an application |
1130 | separately |
1131 | PARAMETERS |
1132 | |
1133 | EXCEPTIONS RAISED |
1134 | |
1135 | ERRORS RAISED |
1136 | |
1137 | KNOWN BUGS |
1138 | |
1139 | NOTES |
1140 | |
1141 | HISTORY |
1142 | 23-Jul-1993 Alan Fothergill Created |
1143 *---------------------------------------------------------------------------*/
1144 PROCEDURE DistributeLTFApplication( p_Post IN PostingParametersType,
1145 p_Receipt IN ReceiptType,
1146 p_Trx IN TrxType,
1147 p_App IN ApplicationType,
1148 p_AppAmount IN ApplicationAmountType ) IS
1149 RunningTotalAmount NUMBER := 0;
1150 RunningTotalAcctdAmount NUMBER := 0;
1151 AcctdAmount NUMBER;
1152 SurplusAmount NUMBER;
1153 BEGIN
1154 IF p_AppAmount.ChargesApplied <> 0 THEN
1155 AcctdAmount := arpcurr.ReconcileAcctdAmounts( p_Receipt.ExchangeRate,
1156 p_AppAmount.Amount,
1157 p_AppAmount.AcctdAmount,
1158 p_AppAmount.ChargesApplied,
1159 RunningTotalAmount,
1160 RunningTotalAcctdAmount );
1161 DistributeApplicationType( p_Post, p_Receipt, p_Trx, p_App, 'CHARGES', p_AppAmount.ChargesApplied, AcctdAmount,
1162 p_AppAmount.AmountAppFrom);
1163 END IF;
1164 --
1165 IF p_AppAmount.FreightApplied <> 0 THEN
1166 AcctdAmount := arpcurr.ReconcileAcctdAmounts( p_Receipt.ExchangeRate,
1167 p_AppAmount.Amount,
1168 p_AppAmount.AcctdAmount,
1169 p_AppAmount.FreightApplied,
1170 RunningTotalAmount,
1171 RunningTotalAcctdAmount );
1172 DistributeApplicationType( p_Post, p_Receipt, p_Trx, p_App, 'FREIGHT', p_AppAmount.FreightApplied, AcctdAmount,
1173 p_AppAmount.AmountAppFrom);
1174 END IF;
1175 --
1176 IF p_AppAmount.TaxApplied <> 0 THEN
1177 AcctdAmount := arpcurr.ReconcileAcctdAmounts( p_Receipt.ExchangeRate,
1178 p_AppAmount.Amount,
1179 p_AppAmount.AcctdAmount,
1180 p_AppAmount.TaxApplied,
1181 RunningTotalAmount,
1182 RunningTotalAcctdAmount );
1183 DistributeApplicationType( p_Post, p_Receipt,p_Trx, p_App, 'TAX', p_AppAmount.TaxApplied, AcctdAmount,
1184 p_AppAmount.AmountAppFrom);
1185 END IF;
1186 --
1187 IF p_AppAmount.LineApplied <> 0 THEN
1188 AcctdAmount := arpcurr.ReconcileAcctdAmounts( p_Receipt.ExchangeRate,
1189 p_AppAmount.Amount,
1190 p_AppAmount.AcctdAmount,
1191 p_AppAmount.LineApplied,
1192 RunningTotalAmount,
1193 RunningTotalAcctdAmount );
1194 DistributeApplicationType( p_Post, p_Receipt, p_Trx, p_App, 'LINE', p_AppAmount.LineApplied, AcctdAmount,
1195 p_AppAmount.AmountAppFrom);
1196 END IF;
1197 --
1198 --
1199 --
1200 EXCEPTION
1201 WHEN OTHERS THEN
1202 arp_standard.debug( 'Exception:DistributeLTFApplication:' );
1203 RAISE;
1204 END;
1205 --
1206 --
1207 -- post ar_receivable_applications that have status UNAPP, UNID, ACC,OTHER ACC
1208 --
1209 /*---------------------------------------------------------------------------*
1210 | PRIVATE PROCEDURE |
1211 | PostNonDistApplications |
1212 | DESCRIPTION |
1213 | post unposted ar_receivable_applications records |
1214 | |
1215 | |
1216 | PARAMETERS |
1217 | |
1218 | EXCEPTIONS RAISED |
1219 | |
1220 | ERRORS RAISED |
1221 | |
1222 | KNOWN BUGS |
1223 | |
1224 | NOTES |
1225 | |
1226 | HISTORY |
1227 | 23-Jul-1993 Alan Fothergill Created |
1228 | 20-Aug-1993 Alan Fothergill Placed exception handler around insert |
1229 | statement |
1230 | 16-JAN-2002 R Kader Modified the cursor CRa to fetch the |
1231 | ACTIVITY records also. |
1232 | See bug 2177009 / 2187023 for details |
1233 | 03-JUN-2003 M Raymond Removed MRC schema dependency
1234 | 03-DEC-2004 M Raymond Changed source of AmountAppFrom
1235 | for MRC rows - see bug 3904994 for dets.
1236 *---------------------------------------------------------------------------*/
1237 PROCEDURE PostNonDistApplications( p_Post IN PostingParametersType ) IS
1238 CURSOR CRa IS
1239 SELECT ra.ROWID RaRowid,
1240 cr.cash_receipt_id CashReceiptId,
1241 cr.receipt_number ReceiptNumber,
1242 cr.doc_sequence_id CrDocSequenceId,
1243 cr.doc_sequence_value CrDocSequenceValue,
1244 cr.pay_from_customer PayFromCustomer,
1245 cr.currency_code CurrencyCode,
1246 ra.receivable_application_id ReceivableApplicationId,
1247 ra.gl_date GlDate,
1248 ra.ussgl_transaction_code UssglTransactionCode,
1249 ra.amount_applied Amount,
1250 ra.amount_applied_from AmountAppFrom,
1251 ra.acctd_amount_applied_from AcctdAmount,
1252 ra.code_combination_id CodeCombinationId,
1253 ra.status Status
1254 FROM ar_receivable_applications ra,
1255 ar_cash_receipts cr
1256 WHERE ra.posting_control_id = p_Post.UnpostedPostingControlId
1257 AND ra.gl_date BETWEEN p_Post.GlDateFrom
1258 AND p_Post.GlDateTo
1259 AND nvl(ra.postable,'Y') = 'Y'
1260 AND nvl(ra.confirmed_flag,'Y') = 'Y'
1261 AND ra.status <> 'APP' -- Bug 2187023
1262 AND ra.application_type||'' = 'CASH'
1263 AND cr.cash_receipt_id = ra.cash_receipt_id
1264 AND ra.receivable_application_id+0 < p_Post.NxtReceivableApplicationId
1265 FOR UPDATE OF ra.receivable_application_id;
1266
1267 /* Bug 3904994 - changed amount_applied_from from
1268 ra_mrc to ra table. The corresponding MRC view
1269 also relies upon the primary table for this column */
1270
1271 --{BUG4301323
1272 /*
1273 CURSOR CRa_mrc IS
1274 SELECT ra_mrc.ROWID RaRowid,
1275 cr.cash_receipt_id CashReceiptId,
1276 cr.receipt_number ReceiptNumber,
1277 cr.doc_sequence_id CrDocSequenceId,
1278 cr.doc_sequence_value CrDocSequenceValue,
1279 cr.pay_from_customer PayFromCustomer,
1280 cr.currency_code CurrencyCode,
1281 ra.receivable_application_id ReceivableApplicationId,
1282 ra.gl_date GlDate,
1283 ra.ussgl_transaction_code UssglTransactionCode,
1284 ra_mrc.amount_applied Amount,
1285 ra.amount_applied_from AmountAppFrom,
1286 ra_mrc.acctd_amount_applied_from AcctdAmount,
1287 ra.code_combination_id CodeCombinationId,
1288 ra_mrc.status Status
1289 FROM ar_receivable_applications ra,
1290 ar_mc_receivable_apps ra_mrc,
1291 ar_cash_receipts cr
1292 WHERE ra_mrc.posting_control_id = p_Post.UnpostedPostingControlId
1293 AND ra.gl_date BETWEEN p_Post.GlDateFrom
1294 AND p_Post.GlDateTo
1295 AND nvl(ra.postable,'Y') = 'Y'
1296 AND nvl(ra.confirmed_flag,'Y') = 'Y'
1297 AND ra.status <> 'APP' -- Bug 2187023
1298 AND ra.application_type||'' = 'CASH'
1299 AND cr.cash_receipt_id = ra.cash_receipt_id
1300 AND ra.receivable_application_id+0 < p_Post.NxtReceivableApplicationId
1301 AND ra.receivable_application_id = ra_mrc.receivable_application_id
1302 AND ra_mrc.set_of_books_id = p_Post.SetOfBooksId
1303 FOR UPDATE OF ra_mrc.receivable_application_id;
1304 */
1305
1306 l_Count NUMBER :=0;
1307
1308 l_gl_interface gl_interface%ROWTYPE;
1309 l_gl_interface_null gl_interface%ROWTYPE;
1310 BEGIN
1311 arp_standard.debug( ' ' );
1312 arp_standard.debug( ' AR_RECEIVABLE_APPLICATIONS (non-app)...' );
1313
1314 -- bug3769452 modified IF condition
1315 IF (p_Post.SetOfBooksType <> 'R')
1316 THEN
1317
1318 arp_standard.debug(' Primary sob');
1319
1320 FOR RRa IN CRa
1321 LOOP
1322 BEGIN
1323 /* bug3692482 replace insert stmt with CreateInterface procedure */
1324 l_gl_interface := l_gl_interface_null ;
1325
1326 l_gl_interface.created_by := p_Post.CreatedBy;
1327 l_gl_interface.date_created := TRUNC( SYSDATE );
1328 l_gl_interface.status := 'NEW';
1329 l_gl_interface.actual_flag := 'A';
1330 l_gl_interface.group_id := p_Post.PostingControlId;
1331 l_gl_interface.set_of_books_id := p_Post.SetOfBooksId;
1332 l_gl_interface.user_je_source_name := p_Post.UserSource;
1333
1334 IF RRa.AmountAppFrom IS NULL
1335 THEN
1336 l_gl_interface.user_je_category_name := p_Post.UserTrade ;
1337 ELSE
1338 l_gl_interface.user_je_category_name := p_Post.UserCcurr ;
1339 END IF;
1340
1341 l_gl_interface.accounting_date := RRa.GlDate;
1342 l_gl_interface.subledger_doc_sequence_id := RRA.CrDocSequenceId;
1343 l_gl_interface.subledger_doc_sequence_value := RRa.CrDocSequenceValue;
1344 l_gl_interface.ussgl_transaction_code := RRa.UssglTransactionCode;
1345 l_gl_interface.Currency_code := RRa.CurrencyCode;
1346 l_gl_interface.code_combination_id := RRa.CodeCombinationId;
1347
1348 IF RRa.amount < 0
1349 THEN
1350 l_gl_interface.entered_dr := -RRa.amount;
1351 ELSE
1352 l_gl_interface.entered_cr := RRa.amount;
1353 END IF;
1354
1355 IF RRa.AcctdAmount < 0
1356 THEN
1357 l_gl_interface.accounted_dr := -RRa.AcctdAmount;
1358 ELSE
1359 l_gl_interface.accounted_cr := RRa.AcctdAmount;
1360 END IF;
1361
1362 l_gl_interface.reference1 :=
1363 'AR '||to_char(p_Post.PostingControlId);
1364
1365 IF p_Post.SummaryFlag = 'Y'
1366 THEN
1367 l_gl_interface.reference10 := NULL;
1368 ELSE
1369 l_gl_interface.reference10 := p_Post.NlsPreTradeApp||' '||RRa.ReceiptNumber;
1370 IF RRa.Status = 'ACC'
1371 THEN
1372 l_gl_interface.reference10 :=
1373 l_gl_interface.reference10 || p_Post.NlsAppOnAcc;
1374 ELSIF RRa.Status = 'OTHER ACC'
1375 THEN
1376 l_gl_interface.reference10 :=
1377 l_gl_interface.reference10 || p_Post.NlsAppOtherAcc;
1378 ELSIF RRa.Status = 'UNAPP'
1379 THEN
1380 l_gl_interface.reference10 :=
1381 l_gl_interface.reference10 || p_Post.NlsAppUnapp;
1382 ELSIF RRa.Status = 'UNID'
1383 THEN
1384 l_gl_interface.reference10 :=
1385 l_gl_interface.reference10 || p_Post.NlsAppUnid;
1386 ELSIF RRa.Status = 'ACTIVITY'
1387 THEN
1388 l_gl_interface.reference10 :=
1389 l_gl_interface.reference10 || p_Post.NlsAppActivity;
1390 END IF;
1391
1392 l_gl_interface.reference10 :=
1393 l_gl_interface.reference10 || p_Post.NlsPostTradeApp;
1394 END IF;
1395
1396 l_gl_interface.reference21 := p_Post.PostingControlId;
1397 l_gl_interface.reference22 := RRa.CashReceiptId;
1398 l_gl_interface.reference23 := RRa.ReceivableApplicationId;
1399 l_gl_interface.reference24 := RRa.ReceiptNumber;
1400 l_gl_interface.reference25 := NULL;
1401 l_gl_interface.reference26 := NULL;
1402 l_gl_interface.reference27 := RRa.PayFromCustomer;
1403
1404 IF RRa.AmountAppFrom IS NULL
1405 THEN
1406 l_gl_interface.reference28 := 'TRADE';
1407 l_gl_interface.reference29 := 'TRADE_APP';
1408 ELSE
1409 l_gl_interface.reference28 := 'CCURR';
1410 l_gl_interface.reference29 := 'CCURR_APP';
1411 END IF;
1412 l_gl_interface.reference30 := 'AR_RECEIVABLE_APPLICATIONS' ;
1413
1414 CreateInterface(l_gl_interface) ;
1415
1416 EXCEPTION
1417 WHEN OTHERS THEN
1418 arp_standard.debug( 'Exception:PostNonDistApplications.INSERT:' );
1419 arp_standard.debug('RRa.CashReceiptId:'||RRa.CashReceiptId );
1420 arp_standard.debug('RRa.ReceiptNumber:'||RRa.ReceiptNumber );
1421 arp_standard.debug('RRa.CrDocSequenceId:'||RRa.CrDocSequenceId );
1422 arp_standard.debug('RRa.CrDocSequenceValue:'||RRa.CrDocSequenceValue );
1423 arp_standard.debug('RRa.PayFromCustomer:'||RRa.PayFromCustomer );
1424 arp_standard.debug('RRa.CurrencyCode:'||RRa.CurrencyCode );
1425 arp_standard.debug('RRa.ReceivableApplicationId:'||RRa.ReceivableApplicationId );
1426 arp_standard.debug('RRa.GlDate:'||RRa.GlDate );
1427 arp_standard.debug('RRa.UssglTransactionCode:'||RRa.UssglTransactionCode );
1428 arp_standard.debug('RRa.Amount:'||RRa.Amount );
1429 arp_standard.debug('RRa.AcctdAmount:'||RRa.AcctdAmount );
1430 arp_standard.debug('RRa.CodeCombinationId:'||RRa.CodeCombinationId );
1431 arp_standard.debug('RRa.Status:'||RRa.Status );
1432 RAISE;
1433 END;
1434
1435 UPDATE ar_receivable_applications
1436 SET posting_control_id = p_Post.PostingControlId,
1437 gl_posted_date = p_Post.GlPostedDate
1438 WHERE rowid = RRa.RaRowid;
1439
1440 l_Count := l_Count + 1;
1441 END LOOP;
1442
1443 /* reporting sob */
1444 ELSE
1445 NULL;
1446
1447 -- arp_standard.debug(' Reporting sob');
1448 -- FOR RRa IN CRa_mrc
1449 -- LOOP
1450 -- BEGIN
1451 /* bug3692482 replace insert stmt with CreateInterface procedure */
1452 -- l_gl_interface := l_gl_interface_null ;
1453
1454 -- l_gl_interface.created_by := p_Post.CreatedBy;
1455 -- l_gl_interface.date_created := TRUNC( SYSDATE );
1456 -- l_gl_interface.status := 'NEW';
1457 -- l_gl_interface.actual_flag := 'A';
1458 -- l_gl_interface.group_id := p_Post.PostingControlId;
1459 -- l_gl_interface.set_of_books_id := p_Post.SetOfBooksId;
1460 -- l_gl_interface.user_je_source_name := p_Post.UserSource;
1461
1462 -- IF RRa.AmountAppFrom IS NULL
1463 -- THEN
1464 -- l_gl_interface.user_je_category_name := p_Post.UserTrade ;
1465 -- ELSE
1466 -- l_gl_interface.user_je_category_name := p_Post.UserCcurr ;
1467 -- END IF;
1468
1469 -- l_gl_interface.accounting_date := RRa.GlDate;
1470 -- l_gl_interface.subledger_doc_sequence_id := RRA.CrDocSequenceId;
1471 -- l_gl_interface.subledger_doc_sequence_value := RRa.CrDocSequenceValue;
1472 -- l_gl_interface.ussgl_transaction_code := RRa.UssglTransactionCode;
1473 -- l_gl_interface.Currency_code := RRa.CurrencyCode;
1474 -- l_gl_interface.code_combination_id := RRa.CodeCombinationId;
1475
1476 -- IF RRa.amount < 0
1477 -- THEN
1478 -- l_gl_interface.entered_dr := -RRa.amount;
1479 -- ELSE
1480 -- l_gl_interface.entered_cr := RRa.amount;
1481 -- END IF;
1482
1483 -- IF RRa.AcctdAmount < 0
1484 -- THEN
1485 -- l_gl_interface.accounted_dr := -RRa.AcctdAmount;
1486 -- ELSE
1487 -- l_gl_interface.accounted_cr := RRa.AcctdAmount;
1488 -- END IF;
1489
1490 -- l_gl_interface.reference1 :=
1491 -- 'AR '||to_char(p_Post.PostingControlId);
1492
1493 -- IF p_Post.SummaryFlag = 'Y'
1494 -- THEN
1495 -- l_gl_interface.reference10 := NULL;
1496 -- ELSE
1497 -- l_gl_interface.reference10 := p_Post.NlsPreTradeApp||' '||RRa.ReceiptNumber;
1498 -- IF RRa.Status = 'ACC'
1499 -- THEN
1500 -- l_gl_interface.reference10 :=
1501 -- l_gl_interface.reference10 || p_Post.NlsAppOnAcc;
1502 -- ELSIF RRa.Status = 'OTHER ACC'
1503 -- THEN
1504 -- l_gl_interface.reference10 :=
1505 -- l_gl_interface.reference10 || p_Post.NlsAppOtherAcc;
1506 -- ELSIF RRa.Status = 'UNAPP'
1507 -- THEN
1508 -- l_gl_interface.reference10 :=
1509 -- l_gl_interface.reference10 || p_Post.NlsAppUnapp;
1510 -- ELSIF RRa.Status = 'UNID'
1511 -- THEN
1512 -- l_gl_interface.reference10 :=
1513 -- l_gl_interface.reference10 || p_Post.NlsAppUnid;
1514 -- ELSIF RRa.Status = 'ACTIVITY'
1515 -- THEN
1516 -- l_gl_interface.reference10 :=
1517 -- l_gl_interface.reference10 || p_Post.NlsAppActivity;
1518 -- END IF;
1519
1520 -- l_gl_interface.reference10 :=
1521 -- l_gl_interface.reference10 || p_Post.NlsPostTradeApp;
1522 -- END IF;
1523
1524 -- l_gl_interface.reference21 := p_Post.PostingControlId;
1525 -- l_gl_interface.reference22 := RRa.CashReceiptId;
1526 -- l_gl_interface.reference23 := RRa.ReceivableApplicationId;
1527 -- l_gl_interface.reference24 := RRa.ReceiptNumber;
1528 -- l_gl_interface.reference25 := NULL;
1529 -- l_gl_interface.reference26 := NULL;
1530 -- l_gl_interface.reference27 := RRa.PayFromCustomer;
1531
1532
1533 -- IF RRa.AmountAppFrom IS NULL
1534 -- THEN
1535 -- l_gl_interface.reference28 := 'TRADE';
1536 -- l_gl_interface.reference29 := 'TRADE_APP';
1537 -- ELSE
1538 -- l_gl_interface.reference28 := 'CCURR';
1539 -- l_gl_interface.reference29 := 'CCURR_APP';
1540 -- END IF;
1541
1542 -- l_gl_interface.reference30 := 'AR_RECEIVABLE_APPLICATIONS' ;
1543
1544 -- CreateInterface(l_gl_interface) ;
1545
1546 -- EXCEPTION
1547 -- WHEN OTHERS THEN
1548 -- arp_standard.debug( 'Exception:PostNonDistApplications.INSERT:' );
1549 -- arp_standard.debug('RRa.CashReceiptId:'||RRa.CashReceiptId );
1550 -- arp_standard.debug('RRa.ReceiptNumber:'||RRa.ReceiptNumber );
1551 -- arp_standard.debug('RRa.CrDocSequenceId:'||RRa.CrDocSequenceId );
1552 -- arp_standard.debug('RRa.CrDocSequenceValue:'||RRa.CrDocSequenceValue );
1553 -- arp_standard.debug('RRa.PayFromCustomer:'||RRa.PayFromCustomer );
1554 -- arp_standard.debug('RRa.CurrencyCode:'||RRa.CurrencyCode );
1555 -- arp_standard.debug('RRa.ReceivableApplicationId:'||RRa.ReceivableApplicationId );
1556 -- arp_standard.debug('RRa.GlDate:'||RRa.GlDate );
1557 -- arp_standard.debug('RRa.UssglTransactionCode:'||RRa.UssglTransactionCode );
1558 -- arp_standard.debug('RRa.Amount:'||RRa.Amount );
1559 -- arp_standard.debug('RRa.AcctdAmount:'||RRa.AcctdAmount );
1560 -- arp_standard.debug('RRa.CodeCombinationId:'||RRa.CodeCombinationId );
1561 -- arp_standard.debug('RRa.Status:'||RRa.Status );
1562 -- RAISE;
1563 -- END;
1564
1565 -- UPDATE ar_mc_receivable_apps
1566 -- SET posting_control_id = p_Post.PostingControlId,
1567 -- gl_posted_date = p_Post.GlPostedDate
1568 -- WHERE rowid = RRa.RaRowid;
1569
1570 -- l_Count := l_Count + 1;
1571
1572 -- END LOOP;
1573 --}
1574 END IF;
1575
1576 arp_standard.debug( ' '||l_Count||' lines posted' );
1577 EXCEPTION
1578 WHEN OTHERS THEN
1579 arp_standard.debug( 'Exception:PostNonDistApplications:' );
1580 RAISE;
1581 END;
1582 --
1583 /*---------------------------------------------------------------------------*
1584 | PRIVATE PROCEDURE |
1585 | PostDistributedApplications |
1586 | |
1587 | DESCRIPTION |
1588 | post unposted ar_receivable_applications records |
1589 | We need to have ORDER BY clause in the select statement because |
1590 | when comparing with Journal Entry report, they need to match, |
1591 | If order by is not used, there will be rounding difference. |
1592 | |
1593 | PARAMETERS |
1594 | |
1595 | EXCEPTIONS RAISED |
1596 | |
1597 | ERRORS RAISED |
1598 | |
1599 | KNOWN BUGS |
1600 | |
1601 | NOTES |
1602 | |
1603 | HISTORY |
1604 | 23-Jul-1993 Alan Fothergill Created |
1605 | 22-NOV-2000 M Raymond Changed exchange rate calc in cursor
1606 | for receipts (CRa) to utilize the
1607 | trans_to_receipt_rate from the
1608 | ar_receivable_applications table.
1609 | See bug 1429867 for details.
1610 | New exchange rate is calculated as:
1611 | NVL(crh.exchange_rate,1) *
1612 | NVL(ra.trans_to_receipt_rate,1)
1613 | 18-APR-01 S.Nambiar Modified the sql to fetch activity record
1614 | also
1615 | 16-JAN-2002 R Kader Modified the cursor CRa not to fetch
1616 | ACTIVITY records
1617 | See bug 2177009 / 2187023 for details
1618 | 03-JUN-2003 M Raymond Removed MRC schema dependency
1619 | this one looks iffy!
1620 *---------------------------------------------------------------------------*/
1621 PROCEDURE PostDistributedApplications( p_Post IN PostingParametersType ) IS
1622 CURSOR CRa IS
1623 SELECT ra.ROWID ra_rowid,
1624 DECODE(
1625 ra.application_type,
1626 'CM', ctcm.customer_trx_id,
1627 'CASH',cr.cash_receipt_id ) CashReceiptId,
1628 DECODE(
1629 ra.application_type,
1630 'CM', ctcm.trx_number,
1631 'CASH',cr.receipt_number ) ReceiptNumber,
1632 DECODE(
1633 ra.application_type,
1634 'CM', ctcm.doc_sequence_id,
1635 'CASH',cr.doc_sequence_id ) CrDocSequenceId,
1636 DECODE(
1637 ra.application_type,
1638 'CM', ctcm.doc_sequence_value,
1639 'CASH',cr.doc_sequence_value ) CrDocSequenceValue,
1640 DECODE(
1641 ra.application_type,
1642 'CM', ctcm.bill_to_customer_id,
1643 'CASH',cr.pay_from_customer ) PayFromCustomer,
1644 /*Bug3235636 ct.invoice_currency_code commented to take it from
1645 get_currency_code*/
1646 /*ct.invoice_currency_code CurrencyCode,*/
1647 /*For Bug 4936298 change ar_ta_util_pub to arpt_sql_func_util*/
1648 arpt_sql_func_util.get_currency_code(ra.application_type,ra.status,
1649 'CURR_',cr.currency_code,ct.invoice_currency_code) CurrencyCode,
1650 DECODE(
1651 ra.application_type,
1652 'CM', NVL(ctcm.exchange_rate,1),
1653 'CASH',NVL(crh.exchange_rate,1) *
1654 NVL(ra.trans_to_receipt_rate, 1)) ExchangeRate,
1655 DECODE(
1656 l.lookup_code,
1657 '1', 'N',
1658 '2', 'Y'
1659 ) CmPsIdFlag,
1660 DECODE(
1661 l.lookup_code,
1662 '1', ra.applied_payment_schedule_id,
1663 '2', ra.payment_schedule_id
1664 ) PaymentScheduleId,
1665 ctt.type Class,
1666 ct.trx_number TrxNumber,
1667 ra.receivable_application_id ReceivableApplicationId,
1668 ra.gl_date GlDate,
1669 ra.ussgl_transaction_code UssglTransactionCode,
1670 ra.application_type AppType,
1671 DECODE(
1672 l.lookup_code,
1673 '1', ra.amount_applied,
1674 '2', -ra.amount_applied
1675 ) Amount,
1676 DECODE(
1677 ra.application_type,
1678 'CM',null,
1679 'CASH',ra.amount_applied_from
1680 ) AmountAppFrom,
1681
1682 DECODE(
1683 l.lookup_code,
1684 '1', ra.acctd_amount_applied_from,
1685 '2', -ra.acctd_amount_applied_from
1686 ) AcctdAmount,
1687 DECODE(
1688 l.lookup_code,
1689 '1', NVL(ra.line_applied,0),
1690 '2', NVL(-ra.line_applied,0)
1691 ) LineApplied,
1692 DECODE(
1693 l.lookup_code,
1694 '1', NVL(ra.tax_applied,0),
1695 '2', NVL(-ra.tax_applied,0)
1696 ) TaxApplied,
1697 DECODE(
1698 l.lookup_code,
1699 '1', NVL(ra.freight_applied,0),
1700 '2', NVL(-ra.freight_applied,0)
1701 ) FreightApplied,
1702 DECODE(
1703 l.lookup_code,
1704 '1', NVL(ra.receivables_charges_applied,0),
1705 '2', NVL(-ra.receivables_charges_applied,0)
1706 ) ChargesApplied,
1707 ct.org_id OrgId
1708 FROM ar_receivable_applications ra,
1709 ra_cust_trx_types ctt,
1710 ra_customer_trx ct,
1711 ar_cash_receipts cr,
1712 ar_cash_receipt_history crh,
1713 ra_customer_trx ctcm,
1714 ar_lookups l
1715 WHERE ra.posting_control_id = p_Post.UnpostedPostingControlId
1716 AND ra.gl_date BETWEEN p_Post.GlDateFrom
1717 AND p_Post.GlDateTo
1718 AND nvl(ra.postable,'Y') = 'Y'
1719 AND nvl(ra.confirmed_flag,'Y') = 'Y'
1720 AND ra.status||'' = 'APP' -- Bug 2187023
1721 AND ra.cash_receipt_id = cr.cash_receipt_id(+)
1722 AND ra.cash_receipt_history_id = crh.cash_receipt_history_id(+)
1723 AND ra.customer_trx_id = ctcm.customer_trx_id(+)
1724 AND ctcm.previous_customer_trx_id IS NULL
1725 AND ra.applied_customer_trx_id = ct.customer_trx_id
1726 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
1727 AND l.lookup_type = 'AR_CARTESIAN_JOIN'
1728 AND (
1729 ( l.lookup_code ='1' )
1730 OR
1731 ( l.lookup_code = '2'
1732 AND
1733 ra.application_type = 'CM' )
1734 )
1735 AND ra.receivable_application_id+0 < p_Post.NxtReceivableApplicationId
1736 ORDER BY ra.receivable_application_id, l.lookup_code
1737 FOR UPDATE OF ra.receivable_application_id;
1738
1739 --{BUG4301323
1740 -- CURSOR CRa_mrc IS
1741 -- SELECT ra.ROWID ra_rowid,
1742 -- DECODE(
1743 -- ra.application_type,
1744 -- 'CM', ctcm.customer_trx_id,
1745 -- 'CASH',cr.cash_receipt_id ) CashReceiptId,
1746 -- DECODE(
1747 -- ra.application_type,
1748 -- 'CM', ctcm.trx_number,
1749 -- 'CASH',cr.receipt_number ) ReceiptNumber,
1750 -- DECODE(
1751 -- ra.application_type,
1752 -- 'CM', ctcm.doc_sequence_id,
1753 -- 'CASH',cr.doc_sequence_id ) CrDocSequenceId,
1754 -- DECODE(
1755 -- ra.application_type,
1756 -- 'CM', ctcm.doc_sequence_value,
1757 -- 'CASH',cr.doc_sequence_value ) CrDocSequenceValue,
1758 -- DECODE(
1759 -- ra.application_type,
1760 -- 'CM', ctcm.bill_to_customer_id,
1761 -- 'CASH',cr.pay_from_customer ) PayFromCustomer,
1762 -- /*Bug3235636 ct.invoice_currency_code commented to take it from
1763 -- get_currency_code*/
1764 -- /*ct.invoice_currency_code CurrencyCode,*/
1765 -- ar_ta_util_pub.get_currency_code(ra.application_type,ra.status,
1766 -- 'CURR_',cr.currency_code,ct.invoice_currency_code) CurrencyCode,
1767 -- DECODE(
1768 -- ra.application_type,
1769 -- 'CM', NVL(ctcm.exchange_rate,1),
1770 -- 'CASH',NVL(crh.exchange_rate,1) *
1771 -- NVL(ra.trans_to_receipt_rate, 1)) ExchangeRate,
1772 -- DECODE(
1773 -- l.lookup_code,
1774 -- '1', 'N',
1775 -- '2', 'Y'
1776 -- ) CmPsIdFlag,
1777 -- DECODE(
1778 -- l.lookup_code,
1779 -- '1', ra.applied_payment_schedule_id,
1780 -- '2', ra.payment_schedule_id
1781 -- ) PaymentScheduleId,
1782 -- ctt.type Class,
1783 -- ct.trx_number TrxNumber,
1784 -- ra.receivable_application_id ReceivableApplicationId,
1785 -- ra.gl_date GlDate,
1786 -- ra.ussgl_transaction_code UssglTransactionCode,
1787 -- ra.application_type AppType,
1788 -- DECODE(
1789 -- l.lookup_code,
1790 -- '1', ra.amount_applied,
1791 -- '2', -ra.amount_applied
1792 -- ) Amount,
1793 -- DECODE(
1794 -- ra.application_type,
1795 -- 'CM',null,
1796 -- 'CASH',ra.amount_applied_from
1797 -- ) AmountAppFrom,
1798 -- DECODE(
1799 -- l.lookup_code,
1800 -- '1', ra.acctd_amount_applied_from,
1801 -- '2', -ra.acctd_amount_applied_from
1802 -- ) AcctdAmount,
1803 -- DECODE(
1804 -- l.lookup_code,
1805 -- '1', NVL(ra.line_applied,0),
1806 -- '2', NVL(-ra.line_applied,0)
1807 -- ) LineApplied,
1808 -- DECODE(
1809 -- l.lookup_code,
1810 -- '1', NVL(ra.tax_applied,0),
1811 -- '2', NVL(-ra.tax_applied,0)
1812 -- ) TaxApplied,
1813 -- DECODE(
1814 -- l.lookup_code,
1815 -- '1', NVL(ra.freight_applied,0),
1816 -- '2', NVL(-ra.freight_applied,0)
1817 -- ) FreightApplied,
1818 -- DECODE(
1819 -- l.lookup_code,
1820 -- '1', NVL(ra.receivables_charges_applied,0),
1821 -- '2', NVL(-ra.receivables_charges_applied,0)
1822 -- ) ChargesApplied
1823 -- FROM ar_receivable_apps_mrc_v ra,
1824 -- ra_cust_trx_types ctt,
1825 -- ra_customer_trx ct,
1826 -- ar_cash_receipts cr,
1827 -- ar_cash_receipt_hist_mrc_v crh,
1828 -- ra_customer_trx_mrc_v ctcm,
1829 -- ar_lookups l
1830 -- WHERE ra.posting_control_id = p_Post.UnpostedPostingControlId
1831 -- AND ra.gl_date BETWEEN p_Post.GlDateFrom
1832 -- AND p_Post.GlDateTo
1833 -- AND nvl(ra.postable,'Y') = 'Y'
1834 -- AND nvl(ra.confirmed_flag,'Y') = 'Y'
1835 -- AND ra.status||'' = 'APP' -- Bug 2187023
1836 -- AND ra.cash_receipt_id = cr.cash_receipt_id(+)
1837 -- AND ra.cash_receipt_history_id = crh.cash_receipt_history_id(+)
1838 -- AND ra.customer_trx_id = ctcm.customer_trx_id(+)
1839 -- AND ctcm.previous_customer_trx_id IS NULL
1840 -- AND ra.applied_customer_trx_id = ct.customer_trx_id
1841 -- AND ct.cust_trx_type_id = ctt.cust_trx_type_id
1842 -- AND l.lookup_type = 'AR_CARTESIAN_JOIN'
1843 -- AND (
1844 -- ( l.lookup_code ='1' )
1845 -- OR
1846 -- ( l.lookup_code = '2'
1847 -- AND
1848 -- ra.application_type = 'CM' )
1849 -- )
1850 -- AND ra.receivable_application_id+0 < p_Post.NxtReceivableApplicationId
1851 -- ORDER BY ra.receivable_application_id, l.lookup_code
1852 -- FOR UPDATE OF ra.receivable_application_id;
1853
1854
1855 l_Rowid ROWID;
1856 l_Receipt ReceiptType;
1857 l_Trx TrxType;
1858 l_App ApplicationType;
1859 l_AppAmount ApplicationAmountType;
1860 l_Count NUMBER :=0;
1861 BEGIN
1862 arp_standard.debug( ' ' );
1863 arp_standard.debug( ' AR_RECEIVABLE_APPLICATION (app)...' );
1864
1865 -- bug3769452 modified IF condition
1866 IF (NVL(p_Post.SetOfBooksType,'P') <> 'R')
1867 THEN
1868 arp_standard.debug(' Primary sob');
1869
1870 OPEN CRa;
1871 LOOP
1872 FETCH CRa
1873 INTO l_rowid,
1874 l_Receipt.CashReceiptId,
1875 l_Receipt.ReceiptNumber,
1876 l_Receipt.DocSequenceId,
1877 l_Receipt.DocSequenceValue,
1878 l_Receipt.PayFromCustomer,
1879 l_Receipt.CurrencyCode,
1880 l_Receipt.ExchangeRate,
1881 l_Trx.CmPsIdFlag,
1882 l_Trx.PaymentScheduleId,
1883 l_Trx.Class,
1884 l_Trx.TrxNumber,
1885 l_App.ReceivableApplicationId,
1886 l_App.GlDate,
1887 l_App.UssglTransactionCode,
1888 l_App.AppType,
1889 l_AppAmount.Amount,
1890 l_AppAmount.AmountAppfrom,
1891 l_AppAmount.AcctdAmount,
1892 l_AppAmount.LineApplied,
1893 l_AppAmount.TaxApplied,
1894 l_AppAmount.FreightApplied,
1895 l_AppAmount.ChargesApplied,
1896 l_trx.OrgId;
1897 EXIT WHEN CRa%NOTFOUND;
1898
1899 IF (l_Trx.Class = 'CM') OR (l_Trx.CmPsIdFlag = 'Y')
1900 THEN
1901 DistributeApplicationType( p_Post, l_Receipt, l_Trx, l_App, 'INVOICE', l_AppAmount.Amount, l_AppAmount.AcctdAmount,
1902 null );
1903 ELSE
1904 DistributeLTFApplication( p_Post, l_Receipt, l_Trx, l_App, l_AppAmount );
1905 END IF;
1906
1907 IF l_Trx.CmPsIdFlag <> 'Y'
1908 THEN
1909
1910 UPDATE ar_receivable_applications
1911 SET posting_control_id = p_Post.PostingControlId,
1912 gl_posted_date = p_Post.GlPostedDate
1913 WHERE rowid = l_Rowid;
1914
1915 l_Count := l_Count + 1;
1916
1917 END IF;
1918
1919 END LOOP;
1920 CLOSE Cra;
1921
1922 /* reporting sob */
1923 ELSE
1924 --{BUG4301323
1925 NULL;
1926 -- arp_standard.debug(' Reporting sob');
1927 -- OPEN CRa_mrc;
1928 -- LOOP
1929 -- FETCH CRa_mrc
1930 -- INTO l_rowid,
1931 -- l_Receipt.CashReceiptId,
1932 -- l_Receipt.ReceiptNumber,
1933 -- l_Receipt.DocSequenceId,
1934 -- l_Receipt.DocSequenceValue,
1935 -- l_Receipt.PayFromCustomer,
1936 -- l_Receipt.CurrencyCode,
1937 -- l_Receipt.ExchangeRate,
1938 -- l_Trx.CmPsIdFlag,
1939 -- l_Trx.PaymentScheduleId,
1940 -- l_Trx.Class,
1941 -- l_Trx.TrxNumber,
1942 -- l_App.ReceivableApplicationId,
1943 -- l_App.GlDate,
1944 -- l_App.UssglTransactionCode,
1945 -- l_App.AppType,
1946 -- l_AppAmount.Amount,
1947 -- l_AppAmount.AmountAppfrom,
1948 -- l_AppAmount.AcctdAmount,
1949 -- l_AppAmount.LineApplied,
1950 -- l_AppAmount.TaxApplied,
1951 -- l_AppAmount.FreightApplied,
1952 -- l_AppAmount.ChargesApplied;
1953 -- EXIT WHEN CRa_mrc%NOTFOUND;
1954
1955 -- IF (l_Trx.Class = 'CM') OR (l_Trx.CmPsIdFlag = 'Y')
1956 -- THEN
1957 -- DistributeApplicationType( p_Post, l_Receipt, l_Trx, l_App, 'INVOICE', l_AppAmount.Amount, l_AppAmount.AcctdAmount,
1958 -- null );
1959 -- ELSE
1960 -- DistributeLTFApplication( p_Post, l_Receipt, l_Trx, l_App, l_AppAmount );
1961 -- END IF;
1962
1963 -- IF l_Trx.CmPsIdFlag <> 'Y'
1964 -- THEN
1965
1966 -- UPDATE ar_mc_receivable_apps
1967 -- SET posting_control_id = p_Post.PostingControlId,
1968 -- gl_posted_date = p_Post.GlPostedDate
1969 -- WHERE rowid = l_Rowid;
1970
1971 -- l_Count := l_Count + 1;
1972
1973 -- END IF;
1974
1975 -- END LOOP;
1976 -- CLOSE Cra_mrc;
1977 --}
1978 END IF;
1979
1980 arp_standard.debug( ' '||l_Count||' lines posted' );
1981 EXCEPTION
1982 WHEN OTHERS THEN
1983 arp_standard.debug( 'Exception:PostDistributedApplications:' );
1984 Output( l_AppAmount );
1985 Output( l_App );
1986 Output( l_Trx );
1987 Output( l_Receipt );
1988 RAISE;
1989 END;
1990 --
1991 --
1992 -- finds unposted cash receipt history records in the period.
1993 --
1994 --
1995 /*---------------------------------------------------------------------------*
1996 | PRIVATE PROCEDURE |
1997 | PostCashReceiptHistory |
1998 | DESCRIPTION |
1999 | Posts unposted cash receipt history records |
2000 | PARAMETERS |
2001 | |
2002 | EXCEPTIONS RAISED |
2003 | |
2004 | ERRORS RAISED |
2005 | |
2006 | KNOWN BUGS |
2007 | |
2008 | NOTES |
2009 | This is implemented as two cursors one to select cash receipt history|
2010 | the other to select reversals. It had to be implemented this way |
2011 | because FOR UPDATE OF is not allowed in a UNION |
2012 | The two selects must be maintained in parallel, as the InsertIntoGl |
2013 | relies on the ROWTYPE of each select cursor being the same |
2014 | |
2015 | HISTORY |
2016 | 23-Jul-1993 Alan Fothergill Created |
2017 | 21-Mar-1995 C Aldamiz Modified for 10.6
2018 | 03-JUN-2003 M Raymond Removed MRC schema dependency
2019 *---------------------------------------------------------------------------*/
2020 PROCEDURE PostCashReceiptHistory( p_Post IN PostingParametersType ) IS
2021 CURSOR CCrh IS
2022 SELECT crh.ROWID CrhRowid,
2023 crh.cash_receipt_history_id CashReceiptHistoryId,
2024 crh.cash_receipt_id CashReceiptId,
2025 cr.receipt_number ReceiptNumber,
2026 cr.pay_from_customer PayFromCustomer,
2027 DECODE
2028 (
2029 cr.type,
2030 'MISC', 'MISC',
2031 'TRADE'
2032 ) ModifiedType,
2033 nvl(d.amount_dr, -d.amount_cr) Amount,
2034 nvl(d.acctd_amount_dr, -d.acctd_amount_cr) AcctdAmount,
2035 d.code_combination_id AccountCodeCombinationId,
2036 crh.gl_date GlDate,
2037 cr.currency_code CurrencyCode,
2038 DECODE
2039 (
2040 cr.type,
2041 'MISC', p_Post.UserMisc,
2042 p_Post.UserTrade
2043 ) Category,
2044 cr.doc_sequence_id DocSequenceId,
2045 cr.doc_sequence_value DocSequenceValue,
2046 cr.ussgl_transaction_code UssglTransactionCode,
2047 d.source_type SourceType
2048 FROM ar_cash_receipt_history crh,
2049 ar_cash_receipts cr,
2050 ar_distributions d
2051 WHERE crh.gl_date BETWEEN p_Post.GlDateFrom
2052 AND p_Post.GlDateTo
2053 AND crh.posting_control_id = p_Post.UnpostedPostingControlId
2054 AND crh.postable_flag = 'Y'
2055 AND cr.cash_receipt_id = crh.cash_receipt_id
2056 AND crh.cash_receipt_history_id+0 < p_Post.NxtCashReceiptHistoryId
2057 AND crh.cash_receipt_history_id = d.source_id
2058 AND d.source_table = 'CRH'
2059 FOR UPDATE OF crh.cash_receipt_history_id;
2060 --{BUG4301323
2061 -- CURSOR CCrh_rsob IS
2062 -- SELECT crh_mc.ROWID CrhRowid,
2063 -- crh_mc.cash_receipt_history_id CashReceiptHistoryId,
2064 -- crh.cash_receipt_id CashReceiptId,
2065 -- cr.receipt_number ReceiptNumber,
2066 -- cr.pay_from_customer PayFromCustomer,
2067 -- DECODE
2068 -- (
2069 -- cr.type,
2070 -- 'MISC', 'MISC',
2071 -- 'TRADE'
2072 -- ) ModifiedType,
2073 -- nvl(d.amount_dr, -d.amount_cr) Amount,
2074 -- nvl(d.acctd_amount_dr, -d.acctd_amount_cr) AcctdAmount,
2075 -- d.code_combination_id AccountCodeCombinationId,
2076 -- crh.gl_date GlDate,
2077 -- cr.currency_code CurrencyCode,
2078 -- DECODE
2079 -- (
2080 -- cr.type,
2081 -- 'MISC', p_Post.UserMisc,
2082 -- p_Post.UserTrade
2083 -- ) Category,
2084 -- cr.doc_sequence_id DocSequenceId,
2085 -- cr.doc_sequence_value DocSequenceValue,
2086 -- cr.ussgl_transaction_code UssglTransactionCode,
2087 -- d.source_type SourceType
2088 -- FROM ar_mc_cash_receipt_hist crh_mc,
2089 -- ar_cash_receipt_history crh,
2090 -- ar_cash_receipts cr,
2091 -- ar_distributions_mrc_v d
2092 -- WHERE crh.gl_date BETWEEN p_Post.GlDateFrom
2093 -- AND p_Post.GlDateTo
2094 -- AND crh_mc.posting_control_id = p_Post.UnpostedPostingControlId
2095 -- AND crh.postable_flag = 'Y'
2096 -- AND cr.cash_receipt_id = crh.cash_receipt_id
2097 -- AND crh.cash_receipt_history_id+0 < p_Post.NxtCashReceiptHistoryId
2098 -- AND crh.cash_receipt_history_id = d.source_id
2099 -- AND d.source_table = 'CRH'
2100 -- AND d.set_of_books_id = crh_mc.set_of_books_id
2101 -- AND crh.cash_receipt_history_id = crh_mc.cash_receipt_history_id
2102 -- AND crh_mc.set_of_books_id = p_Post.SetOfBooksId
2103 -- FOR UPDATE OF crh_mc.cash_receipt_history_id;
2104 --}
2105 RCrh CCrh%ROWTYPE;
2106 l_Count NUMBER :=0;
2107
2108 PROCEDURE InsertIntoGl( RCrh IN CCrh%ROWTYPE ) IS
2109 l_gl_interface gl_interface%ROWTYPE ;
2110 l_gl_interface_null gl_interface%ROWTYPE ;
2111 BEGIN
2112 /* bug3692482 replace insert stmt with CreateInterface procedure */
2113 l_gl_interface := l_gl_interface_null;
2114
2115 l_gl_interface.created_by := p_Post.CreatedBy;
2116 l_gl_interface.date_created := TRUNC( SYSDATE );
2117 l_gl_interface.status := 'NEW';
2118 l_gl_interface.actual_flag := 'A';
2119 l_gl_interface.group_id := p_Post.PostingControlId;
2120 l_gl_interface.set_of_books_id := p_Post.SetOfBooksId;
2121 l_gl_interface.user_je_source_name := p_Post.UserSource;
2122 l_gl_interface.user_je_category_name := RCrh.Category;
2123 l_gl_interface.accounting_date := RCrh.GlDate;
2124 l_gl_interface.subledger_doc_sequence_id := RCrh.DocSequenceId;
2125 l_gl_interface.subledger_doc_sequence_value := RCrh.DocSequenceValue;
2126 l_gl_interface.ussgl_transaction_code := RCrh.UssglTransactionCode;
2127 l_gl_interface.Currency_code := RCrh.CurrencyCode;
2128 l_gl_interface.code_combination_id := RCrh.AccountCodeCombinationId;
2129
2130 IF RCrh.amount < 0
2131 THEN
2132 l_gl_interface.entered_cr := -RCrh.amount;
2133 ELSE
2134 l_gl_interface.entered_dr := RCrh.amount;
2135 END IF;
2136
2137 IF RCrh.AcctdAmount < 0
2138 THEN
2139 l_gl_interface.accounted_cr := -RCrh.AcctdAmount;
2140 ELSE
2141 l_gl_interface.accounted_dr := RCrh.AcctdAmount;
2142 END IF;
2143
2144 l_gl_interface.reference1 := 'AR '||p_Post.PostingControlId;
2145
2146 IF p_Post.SummaryFlag = 'Y'
2147 THEN
2148 l_gl_interface.reference10 := NULL;
2149 ELSE
2150 l_gl_interface.reference10 := p_Post.NlsPreReceipt||' '||RCrh.ReceiptNumber||' '||p_Post.NlsPostReceipt ;
2151 END IF;
2152
2153 l_gl_interface.reference21 := p_Post.PostingControlId;
2154 l_gl_interface.reference22 := RCrh.CashReceiptId;
2155 l_gl_interface.reference23 := RCrh.CashReceiptHistoryId;
2156 l_gl_interface.reference24 := RCrh.ReceiptNumber;
2157 l_gl_interface.reference27 := RCrh.PayFromCustomer;
2158 l_gl_interface.reference28 := RCrh.ModifiedType;
2159 l_gl_interface.reference29 := RCrh.ModifiedType||'_'||RCrh.SourceType;
2160 l_gl_interface.reference30 := 'AR_CASH_RECEIPT_HISTORY' ;
2161
2162 CreateInterface( l_gl_interface );
2163
2164 EXCEPTION
2165 WHEN OTHERS THEN
2166 arp_standard.debug( 'InsertIntoGl:' );
2167 RAISE;
2168 END;
2169 --
2170 -- This is the actual PostCashReceiptHistory body
2171 --
2172 BEGIN
2173 arp_standard.debug( ' ' );
2174 arp_standard.debug( ' AR_CASH_RECEIPT_HISTORY...' );
2175
2176 -- bug3769452 modified IF condition
2177 IF (p_Post.SetOfBooksType <> 'R')
2178 THEN
2179 arp_standard.debug(' Primary sob');
2180 OPEN CCrh;
2181 LOOP
2182 FETCH CCrh
2183 INTO RCrh;
2184 EXIT WHEN CCrh%NOTFOUND;
2185 InsertIntoGl( RCrh );
2186 UPDATE ar_cash_receipt_history
2187 SET posting_control_id = p_Post.PostingControlId,
2188 gl_posted_date = p_Post.GlPostedDate
2189 WHERE ROWID = RCrh.CrhRowid;
2190 l_Count := l_Count + 1;
2191 END LOOP;
2192 CLOSE CCrh;
2193
2194 /* reporting sob */
2195 ELSE
2196 --{BUG4301323
2197 NULL;
2198 -- arp_standard.debug(' Reporting sob');
2199 -- OPEN CCrh_rsob;
2200 -- LOOP
2201 -- FETCH CCrh_rsob
2202 -- INTO RCrh;
2203 -- EXIT WHEN CCrh_rsob%NOTFOUND;
2204 -- InsertIntoGl( RCrh );
2205 -- UPDATE ar_mc_cash_receipt_hist
2206 -- SET posting_control_id = p_Post.PostingControlId,
2207 -- gl_posted_date = p_Post.GlPostedDate
2208 -- WHERE ROWID = RCrh.CrhRowid;
2209 -- l_Count := l_Count + 1;
2210 -- END LOOP;
2211 -- CLOSE CCrh_rsob;
2212 --}
2213 END IF;
2214
2215 EXCEPTION
2216 WHEN OTHERS THEN
2217 arp_standard.debug( 'PostCashReceiptHistory:' );
2218 RAISE;
2219 END;
2220 --
2221 --
2222 /*---------------------------------------------------------------------------*
2223 | PRIVATE PROCEDURE |
2224 | PostMiscCashDistributions |
2225 | DESCRIPTION |
2226 | post unposted ar_misc_cash_distributions records |
2227 | within the posting range |
2228 | |
2229 | PARAMETERS |
2230 | |
2231 | EXCEPTIONS RAISED |
2232 | |
2233 | ERRORS RAISED |
2234 | |
2235 | KNOWN BUGS |
2236 | |
2237 | NOTES |
2238 | |
2239 | HISTORY |
2240 | 23-Jul-1993 Alan Fothergill Created |
2241 | 03-JUN-2003 M Raymond Removed MRC schema dependency
2242 *---------------------------------------------------------------------------*/
2243 PROCEDURE PostMiscCashDistributions( p_Post IN PostingParametersType ) IS
2244 CURSOR CMcd IS
2245 SELECT mcd.ROWID McdRowid,
2246 mcd.misc_cash_distribution_id MiscCashDistributionId,
2247 cr.cash_receipt_id CashReceiptId,
2248 cr.receipt_number ReceiptNumber,
2249 mcd.amount amount,
2250 mcd.acctd_amount acctd_amount,
2251 mcd.code_combination_id code_combination_id,
2252 mcd.gl_date gl_date,
2253 cr.currency_code currency_code,
2254 p_Post.UserMisc category,
2255 cr.doc_sequence_id doc_sequence_id,
2256 cr.doc_sequence_value doc_sequence_value,
2257 mcd.ussgl_transaction_code ussgl_transaction_code
2258 FROM ar_misc_cash_distributions mcd,
2259 ar_cash_receipts cr
2260 WHERE mcd.posting_control_id = p_Post.UnpostedPostingControlId
2261 AND mcd.gl_date BETWEEN p_Post.GlDateFrom
2262 AND p_Post.GlDateTo
2263 AND cr.cash_receipt_id = mcd.cash_receipt_id
2264 AND mcd.misc_cash_distribution_id+0 < p_Post.NxtMiscCashDistributionId
2265 FOR UPDATE OF mcd.misc_cash_distribution_id;
2266 --{BUG4301323
2267 /*
2268 CURSOR CMcd_mrc IS
2269 SELECT mcd_mrc.ROWID McdRowid,
2270 mcd_mrc.misc_cash_distribution_id MiscCashDistributionId,
2271 cr.cash_receipt_id CashReceiptId,
2272 cr.receipt_number ReceiptNumber,
2273 mcd_mrc.amount amount,
2274 mcd_mrc.acctd_amount acctd_amount,
2275 mcd.code_combination_id code_combination_id,
2276 mcd.gl_date gl_date,
2277 cr.currency_code currency_code,
2278 p_Post.UserMisc category,
2279 cr.doc_sequence_id doc_sequence_id,
2280 cr.doc_sequence_value doc_sequence_value,
2281 mcd.ussgl_transaction_code ussgl_transaction_code
2282 FROM ar_misc_cash_distributions mcd,
2283 ar_mc_misc_cash_dists mcd_mrc,
2284 ar_cash_receipts cr
2285 WHERE mcd.posting_control_id = p_Post.UnpostedPostingControlId
2286 AND mcd.gl_date BETWEEN p_Post.GlDateFrom
2287 AND p_Post.GlDateTo
2288 AND cr.cash_receipt_id = mcd.cash_receipt_id
2289 AND mcd.misc_cash_distribution_id+0 < p_Post.NxtMiscCashDistributionId
2290 AND mcd.misc_cash_distribution_id = mcd_mrc.misc_cash_distribution_id
2291 AND mcd_mrc.set_of_books_id = p_Post.SetOfBooksId
2292 FOR UPDATE OF mcd_mrc.misc_cash_distribution_id;
2293 */
2294
2295 l_Count NUMBER :=0;
2296
2297 l_gl_interface gl_interface%ROWTYPE;
2298 l_gl_interface_null gl_interface%ROWTYPE;
2299
2300 BEGIN
2301 arp_standard.debug( ' ' );
2302 arp_standard.debug( ' AR_MISC_CASH_DISTRIBUTIONS...' );
2303
2304 -- bug3769452 modified IF condition
2305 IF (p_Post.SetOfBooksType <> 'R')
2306 THEN
2307 arp_standard.debug(' Primary sob');
2308 FOR RMcd IN CMcd
2309 LOOP
2310 /* bug3692482 replace insert stmt with CreateInterface procedure */
2311 l_gl_interface := l_gl_interface_null;
2312
2313 /* first create the debit in gl_interface to the
2314 account_code_combination_id */
2315 l_gl_interface.created_by := p_Post.CreatedBy;
2316 l_gl_interface.date_created := TRUNC( SYSDATE );
2317 l_gl_interface.status := 'NEW';
2318 l_gl_interface.actual_flag := 'A';
2319 l_gl_interface.group_id := p_Post.PostingControlId;
2320 l_gl_interface.set_of_books_id := p_Post.SetOfBooksId;
2321 l_gl_interface.user_je_source_name := p_Post.UserSource;
2322 l_gl_interface.user_je_category_name := RMcd.category;
2323 l_gl_interface.accounting_date := RMcd.gl_date;
2324 l_gl_interface.subledger_doc_sequence_id := RMcd.doc_sequence_id;
2325 l_gl_interface.subledger_doc_sequence_value := RMcd.doc_sequence_value;
2326 l_gl_interface.ussgl_transaction_code :=
2327 RMcd.ussgl_transaction_code;
2328 l_gl_interface.currency_code := RMcd.currency_code;
2329 l_gl_interface.code_combination_id := RMcd.code_combination_id;
2330
2331 IF RMcd.amount < 0
2332 THEN
2333 l_gl_interface.entered_dr := -RMcd.amount;
2334 ELSE
2335 l_gl_interface.entered_cr := RMcd.amount;
2336 END IF;
2337
2338 IF RMcd.Acctd_Amount < 0
2339 THEN
2340 l_gl_interface.accounted_dr := -RMcd.acctd_amount;
2341 ELSE
2342 l_gl_interface.accounted_cr := RMcd.acctd_amount;
2343 END IF;
2344
2345 l_gl_interface.reference1 := 'AR '||p_Post.PostingControlId;
2346
2347 IF p_Post.SummaryFlag = 'Y'
2348 THEN
2349 l_gl_interface.reference10 := NULL;
2350 ELSE
2351 l_gl_interface.reference10 := p_Post.NlsPreMiscDist||' '||RMcd.ReceiptNumber||p_Post.NlsPostMiscDist;
2352 END IF;
2353
2354 l_gl_interface.reference21 := p_Post.PostingControlId;
2355 l_gl_interface.reference22 := RMcd.CashReceiptId;
2356 l_gl_interface.reference23 := RMcd.MiscCashDistributionId;
2357 l_gl_interface.reference24 := RMcd.ReceiptNumber;
2358 l_gl_interface.reference28 := 'MISC';
2359 l_gl_interface.reference29 := 'MISC_MISC';
2360 l_gl_interface.reference30 := 'AR_MISC_CASH_DISTRIBUTIONS';
2361
2362 CreateInterface( l_gl_interface );
2363
2364 --
2365 UPDATE ar_misc_cash_distributions
2366 SET posting_control_id = p_Post.PostingControlId,
2367 gl_posted_date = p_Post.GlPostedDate
2368 WHERE ROWID = RMcd.McdRowid;
2369 l_Count := l_Count + 1;
2370 END LOOP;
2371 arp_standard.debug( ' '||l_Count||' lines posted' );
2372 ELSE
2373 /* Reporting SOB */
2374 --{BUG4301323
2375 NULL;
2376
2377 -- arp_standard.debug(' Reporting sob');
2378 -- FOR RMcd IN CMcd_mrc
2379 -- LOOP
2380 /* bug3692482 replace insert stmt with CreateInterface procedure */
2381 l_gl_interface := l_gl_interface_null ;
2382
2383 /* first create the debit in gl_interface to the
2384 account_code_combination_id */
2385
2386 -- l_gl_interface.created_by := p_Post.CreatedBy;
2387 -- l_gl_interface.date_created := TRUNC( SYSDATE );
2388 -- l_gl_interface.status := 'NEW';
2389 -- l_gl_interface.actual_flag := 'A';
2390 -- l_gl_interface.group_id := p_Post.PostingControlId;
2391 -- l_gl_interface.set_of_books_id := p_Post.SetOfBooksId;
2392 -- l_gl_interface.user_je_source_name := p_Post.UserSource;
2393 -- l_gl_interface.user_je_category_name := RMcd.category;
2394 -- l_gl_interface.accounting_date := RMcd.gl_date;
2395 -- l_gl_interface.subledger_doc_sequence_id := RMcd.doc_sequence_id;
2396 -- l_gl_interface.subledger_doc_sequence_value := RMcd.doc_sequence_value;
2397 -- l_gl_interface.ussgl_transaction_code :=
2398 --RMcd.ussgl_transaction_code;
2399 -- l_gl_interface.currency_code := RMcd.currency_code;
2400 -- l_gl_interface.code_combination_id := RMcd.code_combination_id;
2401
2402 -- IF RMcd.amount < 0
2403 -- THEN
2404 -- l_gl_interface.entered_dr := -RMcd.amount;
2405 -- ELSE
2406 -- l_gl_interface.entered_cr := RMcd.amount;
2407 -- END IF;
2408
2409 -- IF RMcd.Acctd_Amount < 0
2410 -- THEN
2411 -- l_gl_interface.accounted_dr := -RMcd.acctd_amount;
2412 -- ELSE
2413 -- l_gl_interface.accounted_cr := RMcd.acctd_amount;
2414 -- END IF;
2415
2416 -- l_gl_interface.reference1 := 'AR '||p_Post.PostingControlId;
2417
2418 -- IF p_Post.SummaryFlag = 'Y'
2419 -- THEN
2420 -- l_gl_interface.reference10 := NULL;
2421 -- ELSE
2422 -- l_gl_interface.reference10 := p_Post.NlsPreMiscDist||' '||RMcd.ReceiptNumber||p_Post.NlsPostMiscDist;
2423 -- END IF;
2424
2425 -- l_gl_interface.reference21 := p_Post.PostingControlId;
2426 -- l_gl_interface.reference22 := RMcd.CashReceiptId;
2427 -- l_gl_interface.reference23 := RMcd.MiscCashDistributionId;
2428 -- l_gl_interface.reference24 := RMcd.ReceiptNumber;
2429 -- l_gl_interface.reference28 := 'MISC';
2430 -- l_gl_interface.reference29 := 'MISC_MISC';
2431 -- l_gl_interface.reference30 := 'AR_MISC_CASH_DISTRIBUTIONS';
2432
2433 -- CreateInterface( l_gl_interface ) ;
2434
2435 --
2436 -- UPDATE ar_mc_misc_cash_dists
2437 -- SET posting_control_id = p_Post.PostingControlId,
2438 -- gl_posted_date = p_Post.GlPostedDate
2439 -- WHERE ROWID = RMcd.McdRowid;
2440 -- l_Count := l_Count + 1;
2441 -- END LOOP;
2442 -- arp_standard.debug( ' '||l_Count||' lines posted' );
2443 --}
2444 END IF;
2445
2446 EXCEPTION
2447 WHEN OTHERS THEN
2448 arp_standard.debug( 'PostMiscCashDistributions:' );
2449 RAISE;
2450 END;
2451 --
2452 --
2453 -- rollback any posting activity that is related to the given
2454 -- balance id
2455 --
2456 /*---------------------------------------------------------------------------*
2457 | PRIVATE PROCEDURE |
2458 | ClearOOB |
2459 | DESCRIPTION |
2460 | rollback (by deleting and updating) any posting activity that is |
2461 | related to the given balance id |
2462 | PARAMETERS |
2463 | |
2464 | EXCEPTIONS RAISED |
2465 | |
2466 | ERRORS RAISED |
2467 | |
2468 | KNOWN BUGS |
2469 | |
2470 | NOTES |
2471 | |
2472 | HISTORY |
2473 | 23-Jul-1993 Alan Fothergill Created |
2474 | 03-JUN-2003 M Raymond Removed MRC schema dependency
2475 *---------------------------------------------------------------------------*/
2476 PROCEDURE ClearOOB( p_Post IN PostingParametersType,
2477 p_BalanceId IN NUMBER,
2478 p_CategoryCode IN VARCHAR2 ) IS
2479 BEGIN
2480 IF ( p_CategoryCode = 'TRADE') OR
2481 (p_CategoryCode = 'CROSS CURR') OR
2482 ( p_CategoryCode = 'MISC' )
2483 THEN
2484
2485 -- bug3769452 modified IF condition
2486 IF (p_Post.SetOfBooksType <> 'R')
2487 THEN
2488 UPDATE ar_cash_receipt_history
2489 SET gl_posted_date = NULL,
2490 posting_control_id = p_Post.UnpostedPostingControlId
2491 WHERE posting_control_id = p_Post.PostingControlId
2492 AND cash_receipt_id = p_BalanceId;
2493
2494 UPDATE ar_cash_receipt_history
2495 SET reversal_gl_posted_date = NULL,
2496 reversal_posting_control_id = p_Post.UnpostedPostingControlId
2497 WHERE reversal_posting_control_id = p_Post.PostingControlId
2498 AND cash_receipt_id = p_BalanceId;
2499 /* reporting sob */
2500 ELSE
2501 --{BUG4301323
2502 NULL;
2503 /*
2504 UPDATE ar_mc_cash_receipt_hist
2505 SET gl_posted_date = NULL,
2506 posting_control_id = p_Post.UnpostedPostingControlId
2507 WHERE posting_control_id = p_Post.PostingControlId
2508 AND cash_receipt_id = p_BalanceId
2509 AND set_of_books_id = p_Post.SetOfBooksId;
2510
2511 UPDATE ar_mc_cash_receipt_hist
2512 SET reversal_gl_posted_date = NULL,
2513 reversal_posting_control_id = p_Post.UnpostedPostingControlId
2514 WHERE reversal_posting_control_id = p_Post.PostingControlId
2515 AND cash_receipt_id = p_BalanceId
2516 AND set_of_books_id = p_Post.SetOfBooksId;
2517 */
2518
2519 END IF;
2520 END IF;
2521 --
2522 IF p_CategoryCode = 'MISC'
2523 THEN
2524 -- bug3769452 modified IF condition
2525 IF (p_Post.SetOfBooksType <> 'R')
2526 THEN
2527 UPDATE ar_misc_cash_distributions
2528 SET gl_posted_date = NULL,
2529 posting_control_id = p_Post.UnpostedPostingControlId
2530 WHERE posting_control_id = p_Post.PostingControlId
2531 AND cash_receipt_id = p_BalanceId;
2532 /* reporting sob */
2533 ELSE
2534 --{BUG4301323
2535 NULL;
2536 /*
2537 UPDATE ar_mc_misc_cash_dists
2538 SET gl_posted_date = NULL,
2539 posting_control_id = p_Post.UnpostedPostingControlId
2540 WHERE posting_control_id = p_Post.PostingControlId
2541 AND cash_receipt_id = p_BalanceId
2542 AND set_of_books_id = p_Post.SetOfBooksID;
2543 */
2544 END IF;
2545
2546 END IF;
2547
2548 IF ( p_CategoryCode = 'TRADE') OR
2549 ( p_CategoryCode = 'CMAPP' )
2550 THEN
2551 -- bug3769452 modified IF condition
2552 IF (p_Post.SetOfBooksType <> 'R')
2553 THEN
2554 UPDATE ar_receivable_applications
2555 SET gl_posted_date = NULL,
2556 posting_control_id = p_Post.UnpostedPostingControlId
2557 WHERE posting_control_id = p_Post.PostingControlId
2558 AND decode(p_CategoryCode,
2559 'CMAPP',customer_trx_id,
2560 'TRADE', cash_receipt_id) = p_BalanceId;
2561
2562 DELETE FROM ar_cash_basis_distributions
2563 WHERE cash_basis_distribution_id IN (
2564 SELECT reference23
2565 FROM gl_interface
2566 WHERE reference22 = p_BalanceId
2567 AND reference28 = p_CategoryCode
2568 AND set_of_books_id = p_Post.SetOfBooksId
2569 AND group_id = p_Post.PostingControlId
2570 AND user_je_source_name = p_Post.UserSource
2571 AND reference30 = 'AR_CASH_BASIS_DISTRIBUTIONS'
2572 );
2573 /* reporting sob */
2574 ELSE
2575 --{BUG4301323
2576 NULL;
2577 /*
2578 UPDATE ar_mc_receivable_apps
2579 SET gl_posted_date = NULL,
2580 posting_control_id = p_Post.UnpostedPostingControlId
2581 WHERE posting_control_id = p_Post.PostingControlId
2582 AND set_of_books_id = p_Post.SetOfBooksId
2583 AND receivable_application_id IN (
2584 SELECT ra.receivable_application_id
2585 FROM ar_receivable_applications ra
2586 WHERE p_CategoryCode = 'CMAPP'
2587 AND ra.customer_trx_id = p_BalanceId
2588 UNION
2589 SELECT ra.receivable_application_id
2590 FROM ar_receivable_applications ra
2591 WHERE p_CategoryCode = 'TRADE'
2592 AND ra.cash_receipt_id = p_BalanceId);
2593 */
2594 /* Able to use MRC view here because view contains
2595 only one table */
2596 /*
2597 DELETE FROM ar_mc_cash_basis_dists_all
2598 WHERE set_of_books_id = p_Post.SetOfBooksId
2599 AND cash_basis_distribution_id IN (
2600 SELECT reference23
2601 FROM gl_interface
2602 WHERE reference22 = p_BalanceId
2603 AND reference28 = p_CategoryCode
2604 AND set_of_books_id = p_Post.SetOfBooksId
2605 AND group_id = p_Post.PostingControlId
2606 AND user_je_source_name = p_Post.UserSource
2607 AND reference30 = 'AR_CASH_BASIS_DISTRIBUTIONS'
2608 );
2609 */
2610 END IF;
2611 END IF;
2612
2613 DELETE FROM gl_interface
2614 WHERE reference22 = p_BalanceId
2615 AND reference28 = p_CategoryCode
2616 AND set_of_books_id = p_Post.SetOfBooksId
2617 AND group_id = p_Post.PostingControlId
2618 AND user_je_source_name = p_Post.UserSource;
2619
2620 --
2621 EXCEPTION
2622 WHEN OTHERS THEN
2623 IF PG_DEBUG in ('Y', 'C') THEN
2624 arp_standard.debug( 'ClearOOB' );
2625 END IF;
2626 RAISE;
2627 END ClearOOB;
2628
2629 --
2630 --
2631 --
2632 --
2633 /*---------------------------------------------------------------------------*
2634 | PRIVATE PROCEDURE |
2635 | CheckBalance |
2636 | DESCRIPTION |
2637 | Checks that the records inserted into gl_interface balance for each |
2638 | BalanceId (reference22). |
2639 | Any BalanceId that fails to balance will be reported on |
2640 | (via arp_standard.debug), and will be deleted with ClearOOB |
2641 | PARAMETERS |
2642 | |
2643 | EXCEPTIONS RAISED |
2644 | |
2645 | ERRORS RAISED |
2646 | |
2647 | KNOWN BUGS |
2648 | |
2649 | NOTES |
2650 | |
2651 | HISTORY |
2652 | 23-Jul-1993 Alan Fothergill Created |
2653 *---------------------------------------------------------------------------*/
2654 PROCEDURE CheckBalance( p_Post IN PostingParametersType ) IS
2655 CURSOR CBal IS
2656 SELECT MIN(i.currency_code) CurrencyCode,
2657 i.reference22 BalanceId,
2658 i.reference28 CategoryCode,
2659 SUM(nvl(i.entered_dr,0)) SumEnteredDr,
2660 SUM(nvl(i.entered_cr,0)) SumEnteredCr,
2661 SUM(nvl(i.accounted_dr,0)) SumAccountedDr,
2662 SUM(nvl(i.accounted_cr,0)) SumAccountedCr
2663 FROM gl_interface i
2664 WHERE i.group_id = p_Post.PostingControlId
2665 AND i.user_je_source_name = p_Post.UserSource
2666 AND i.set_of_books_id = p_Post.SetOfBooksId
2667 AND i.accounting_date BETWEEN p_Post.GlDateFrom
2668 AND p_Post.GlDateTo
2669 GROUP BY i.reference28,
2670 i.reference22
2671 HAVING ( nvl(decode(i.reference28,'CCURR',
2672 0,sum(nvl(entered_dr,0))),0)<>nvl(decode(i.reference28,'CCURR',
2673 0,sum(nvl(entered_cr,0))),0)
2674 OR SUM( NVL(i.accounted_dr,0)) <> SUM( NVL(i.accounted_cr, 0)));
2675 --
2676 CURSOR CInt( p_BalanceId NUMBER, p_CategoryCode VARCHAR2 ) IS
2677 SELECT i.entered_dr EnteredDr,
2678 i.entered_cr EnteredCr,
2679 i.accounted_dr AccountedDr,
2680 i.accounted_cr AccountedCr,
2681 i.reference30 TableName,
2682 i.reference23 Id
2683 FROM gl_interface i
2684 WHERE i.group_id = p_Post.PostingControlId
2685 AND i.user_je_source_name = p_Post.UserSource
2686 AND set_of_books_id = p_Post.SetOfBooksId
2687 AND i.reference22 = p_BalanceId
2688 AND i.reference28 = p_CategoryCode
2689 ORDER BY i.reference30,
2690 i.reference23;
2691 --
2692 l_ReceivableApplicationId ar_receivable_applications.receivable_application_id%TYPE;
2693 BEGIN
2694 --
2695 arp_standard.debug( ' ----------------------------------------------------' );
2696 arp_standard.debug( ' Checking DR/CR balance...' );
2697 arp_standard.debug( '' );
2698 --
2699 FOR RBal IN CBal
2700 LOOP
2701 arp_standard.debug( 'Out Of balance:'||Rbal.CurrencyCode||' BalanceId:'||RBal.BalanceId );
2702 FOR RInt IN CInt( RBal.BalanceId, Rbal.CategoryCode )
2703 LOOP
2704 IF RInt.TableName = 'AR_CASH_BASIS_DISTRIBUTIONS'
2705 THEN
2706
2707 IF p_Post.SetOfBooksType <> 'R'
2708 THEN
2709 /* Primary */
2710 SELECT cbd.receivable_application_id
2711 INTO l_ReceivableApplicationId
2712 FROM ar_cash_basis_distributions cbd
2713 WHERE cbd.cash_basis_distribution_id = RInt.Id;
2714 ELSE
2715 --{BUG4301323
2716 NULL;
2717 /* Reporting */
2718 -- SELECT cbd.receivable_application_id
2719 -- INTO l_ReceivableApplicationId
2720 -- FROM ar_mc_cash_basis_dists_all cbd
2721 -- WHERE cbd.cash_basis_distribution_id = RInt.Id
2722 -- AND cbd.set_of_books_id = p_Post.SetOfBooksId;
2723 --}
2724 END IF;
2725 ELSE
2726 l_ReceivableApplicationId := NULL;
2727 END IF;
2728 arp_standard.debug( RPAD( Rint.TableName, 30)||
2729 RPAD( RInt.Id, 15 )||
2730 LPAD( NVL(TO_CHAR(RInt.EnteredDr), ' '),15)||
2731 LPAD( NVL(TO_CHAR(RInt.EnteredCr), ' '),15)||
2732 LPAD( NVL(TO_CHAR(RInt.AccountedDr), ' '),15)||
2733 LPAD( NVL(TO_CHAR(RInt.AccountedCr), ' '),15)||
2734 ' '||l_ReceivableApplicationId );
2735 END LOOP;
2736 arp_standard.debug( RPAD( 'SUM:', 30)||
2737 RPAD( ' ', 15 )||
2738 LPAD( NVL(TO_CHAR(RBal.SumEnteredDr), ' '),15)||
2739 LPAD( NVL(TO_CHAR(RBal.SumEnteredCr), ' '),15)||
2740 LPAD( NVL(TO_CHAR(RBal.SumAccountedDr), ' '),15)||
2741 LPAD( NVL(TO_CHAR(RBal.SumAccountedCr), ' '),15) );
2742 arp_standard.debug( '--------------------------------------------------------------------------------------------------------------------' );
2743 ClearOOB( p_Post, RBal.BalanceId, RBal.CategoryCode );
2744 END LOOP;
2745 EXCEPTION
2746 WHEN OTHERS THEN
2747 arp_standard.debug( 'CheckBalance:' );
2748 RAISE;
2749 END;
2750 --
2751 PROCEDURE CheckUpgradedCustomer(p_FromRel9 OUT NOCOPY VARCHAR2) IS
2752 l_ColumnId NUMBER :=0;
2753 --
2754 CURSOR SelColumn IS
2755 SELECT column_id
2756 FROM user_tab_columns
2757 WHERE table_name = 'AR_CASH_BASIS_DISTRIBUTIONS'
2758 AND column_name = 'CUSTOMER_TRX_LINE_ID';
2759 --
2760 BEGIN
2761
2762 OPEN SelColumn;
2763 FETCH SelColumn into l_ColumnId;
2764
2765 IF SelColumn%NOTFOUND
2766 THEN
2767 p_FromRel9 := 'N';
2768 ELSE
2769 p_FromRel9 := 'Y';
2770 END IF;
2771 CLOSE SelColumn;
2772 EXCEPTION
2773 WHEN OTHERS THEN
2774 arp_standard.debug( 'CheckUpgradedCustomer:' );
2775 RAISE;
2776 END;
2777
2778 --
2779 --
2780 PROCEDURE Post( p_Post IN OUT NOCOPY PostingParametersType ) IS
2781 l_FromRel9 VARCHAR2(1);
2782 BEGIN
2783 CheckUpgradedCustomer( l_FromRel9 );
2784 IF l_FromRel9 = 'Y'
2785 THEN
2786 arp_standard.fnd_message('AR_WWS_CASH_BASIS');
2787 ELSE
2788
2789 /* Bug 2977037 - set p_Post.SetOfBooksType here */
2790 SELECT nvl(mrc_sob_type_code,'P')
2791 INTO p_Post.SetOfBooksType
2792 FROM gl_sets_of_books
2793 WHERE set_of_books_id = p_Post.SetOfBooksID;
2794
2795 --{BUG4301323
2796 IF p_Post.SetOfBooksType = 'P' THEN
2797 PostCashReceiptHistory( p_Post );
2798 PostMiscCashDistributions( p_Post );
2799 PostNonDistApplications( p_Post );
2800 PostDistributedApplications( p_Post );
2801 END IF;
2802 --}
2803 IF p_Post.ChkBalance = 'Y'
2804 THEN
2805 --{BUG4301323
2806 IF p_Post.SetOfBooksType = 'P' THEN
2807 CheckBalance( p_Post );
2808 END IF;
2809 --}
2810 END IF;
2811 END IF;
2812 EXCEPTION
2813 WHEN OTHERS THEN
2814 arp_standard.debug( 'Exception:arp_cash_basis_accounting.Post( p_Post ):'||sqlerrm );
2815 RAISE_APPLICATION_ERROR( -20000, sqlerrm||'$Revision: 120.15 $:Post( p_Post ):' );
2816 END;
2817 --
2818 --
2819 PROCEDURE Post( p_PostingControlId NUMBER,
2820 p_FuncCurr VARCHAR2,
2821 p_ChkBalance VARCHAR2,
2822 p_GlDateFrom DATE,
2823 p_GlDateTo DATE,
2824 p_SetOfBooksId NUMBER,
2825 p_UnallocatedRevCcid NUMBER,
2826 p_GlPostedDate DATE,
2827 p_CreatedBy NUMBER,
2828 p_UserSource VARCHAR2,
2829 p_UserTrade VARCHAR2,
2830 p_UserMisc VARCHAR2,
2831 p_UserCcurr VARCHAR2,
2832 p_NxtCashReceiptHistoryId NUMBER,
2833 p_NxtReceivableApplicationId NUMBER,
2834 p_NxtMiscCashDistributionId NUMBER,
2835 p_NxtAdjustmentId NUMBER,
2836 p_NxtCustTrxLineGlDistId NUMBER,
2837 p_SummaryFlag VARCHAR2,
2838 p_NlsPreReceipt VARCHAR2,
2839 p_NlsPostReceipt VARCHAR2,
2840 p_NlsPreMiscDist VARCHAR2,
2841 p_NlsPostMiscDist VARCHAR2,
2842 p_NlsPreTradeApp VARCHAR2,
2843 p_NlsPostTradeApp VARCHAR2,
2844 p_NlsPreReceiptGl VARCHAR2,
2845 p_NlsPostReceiptGl VARCHAR2,
2846 p_NlsAppOnacc VARCHAR2,
2847 p_NlsAppOtheracc VARCHAR2,
2848 p_NlsAppUnapp VARCHAR2,
2849 p_NlsAppUnid VARCHAR2,
2850 p_NlsAppApplied VARCHAR2,
2851 p_NlsAppActivity VARCHAR2,
2852 p_UnpostedPostingControlId ar_posting_control.posting_control_id%TYPE ) IS
2853 l_Post PostingParametersType;
2854 BEGIN
2855 l_Post.PostingControlId := p_PostingControlId;
2856 l_Post.FuncCurr := p_FuncCurr;
2857 l_Post.ChkBalance := p_ChkBalance;
2858 l_Post.GlDateFrom := p_GlDateFrom;
2859 l_Post.GlDateTo := p_GlDateTo;
2860 l_Post.SetOfBooksId := p_SetOfBooksId;
2861 l_Post.UnallocatedRevCcid := p_UnallocatedRevCcid;
2862 l_Post.GlPostedDate := p_GlPostedDate;
2863 l_Post.CreatedBy := p_CreatedBy;
2864 l_Post.UserSource := p_UserSource;
2865 l_Post.UserTrade := p_UserTrade;
2866 l_Post.UserMisc := p_UserMisc;
2867 l_Post.UserCcurr := p_UserCcurr;
2868 l_Post.NxtCashReceiptHistoryId := p_NxtCashReceiptHistoryId;
2869 l_Post.NxtReceivableApplicationId := p_NxtReceivableApplicationId;
2870 l_Post.NxtMiscCashDistributionId := p_NxtMiscCashDistributionId;
2871 l_Post.NxtAdjustmentId := p_NxtAdjustmentId;
2872 l_Post.NxtCustTrxLineGlDistId := p_NxtCustTrxLineGlDistId;
2873 l_Post.SummaryFlag := p_SummaryFlag;
2874 l_Post.NlsPreReceipt := p_NlsPreReceipt;
2875 l_Post.NlsPostReceipt := p_NlsPostReceipt;
2876 l_Post.NlsPreMiscDist := p_NlsPreMiscDist;
2877 l_Post.NlsPostMiscDist := p_NlsPostMiscDist;
2878 l_Post.NlsPreTradeApp := p_NlsPreTradeApp;
2879 l_Post.NlsPostTradeApp := p_NlsPostTradeApp;
2880 l_Post.NlsPreReceiptGl := p_NlsPreReceiptGl;
2881 l_Post.NlsPostReceiptGl := p_NlsPostReceiptGl;
2882 l_Post.NlsAppOnacc := p_NlsAppOnacc;
2883 l_Post.NlsAppOtheracc := p_NlsAppOnacc;
2884 l_Post.NlsAppUnapp := p_NlsAppUnapp;
2885 l_Post.NlsAppUnid := p_NlsAppUnid;
2886 l_Post.NlsAppApplied := p_NlsAppApplied;
2887 l_Post.NlsAppActivity := p_NlsAppActivity;
2888 l_Post.UnpostedPostingControlId := p_UnpostedPostingControlId;
2889 --
2890 Post( l_Post );
2891 EXCEPTION
2892 WHEN OTHERS THEN
2893 arp_standard.debug( 'Exception:arp_cash_basis_accounting.Post( ... ):'||sqlerrm );
2894 RAISE_APPLICATION_ERROR( -20000, sqlerrm||'$Revision: 120.15 $:Post( ... ):' );
2895 END;
2896 --
2897 END arp_cash_basis_accounting;