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