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