[Home] [Help]
PACKAGE BODY: APPS.AR_TRX_SUMMARY_PKG
Source
1 PACKAGE BODY AR_TRX_SUMMARY_PKG AS
2 /* $Header: ARCMUPGB.pls 120.21 2007/09/12 17:48:08 mraymond noship $ */
3
4 PROCEDURE insert_conc_req IS
5 BEGIN
6 DELETE from AR_CONC_PROCESS_REQUESTS
7 where CONCURRENT_PROGRAM_NAME = 'ARSUMREF';
8
9 INSERT INTO AR_CONC_PROCESS_REQUESTS
10 (CONCURRENT_PROGRAM_NAME, REQUEST_ID)
11 values ('ARSUMREF',FND_GLOBAL.conc_request_id);
12
13 COMMIT;
14 END;
15
16 PROCEDURE refresh_all(
17 errbuf IN OUT NOCOPY VARCHAR2,
18 retcode IN OUT NOCOPY VARCHAR2
19 ) IS
20 l_program_start_date DATE;
21 l_return BOOLEAN;
22 v_cursor NUMBER;
23 v_return_code INTEGER;
24 v_cursor1 NUMBER;
25 v_return_code1 INTEGER;
26 text VARCHAR2(4000);
27 l_string VARCHAR2(4000);
28
29 CURSOR get_raised_events IS
30 SELECT *
31 FROM ar_sum_ref_event_hist;
32
33 l_list WF_PARAMETER_LIST_T;
34 l_param WF_PARAMETER_T;
35 l_po_value VARCHAR2(10);
36 BEGIN
37 insert_conc_req;
38 fnd_file.put_line(fnd_file.log,'AR_TRX_SUMMARY_PKG.refresh_all(+)');
39 l_po_value := fnd_profile.value('AR_CMGT_ALLOW_SUMMARY_TABLE_REFRESH');
40 IF nvl(l_po_value,'N') = 'Y' THEN
41
42 DELETE from ar_trx_bal_summary;
43
44 SELECT sysdate INTO l_program_start_date FROM dual;
45
46 INSERT INTO AR_TRX_BAL_SUMMARY
47 (CUST_ACCOUNT_ID,
48 SITE_USE_ID,
49 CURRENCY,
50 ORG_ID,
51 LAST_UPDATE_DATE,
52 LAST_UPDATED_BY,
53 CREATION_DATE,
54 CREATED_BY,
55 LAST_UPDATE_LOGIN,
56 OP_INVOICES_VALUE,
57 OP_INVOICES_COUNT,
58 OP_CREDIT_MEMOS_VALUE,
59 OP_CREDIT_MEMOS_COUNT,
60 OP_DEPOSITS_VALUE,
61 OP_DEPOSITS_COUNT,
62 OP_CHARGEBACK_VALUE,
63 OP_CHARGEBACK_COUNT,
64 OP_DEBIT_MEMOS_VALUE,
65 OP_DEBIT_MEMOS_COUNT,
66 OP_BILLS_RECEIVABLES_VALUE,
67 OP_BILLS_RECEIVABLES_COUNT,
68 UNRESOLVED_CASH_VALUE,
69 UNRESOLVED_CASH_COUNT,
70 PAST_DUE_INV_VALUE,
71 PAST_DUE_INV_INST_COUNT,
72 INV_AMT_IN_DISPUTE,
73 DISPUTED_INV_COUNT,
74 BEST_CURRENT_RECEIVABLES,
75 RECEIPTS_AT_RISK_VALUE,
76 LAST_PAYMENT_AMOUNT,
77 LAST_PAYMENT_DATE,
78 LAST_PAYMENT_NUMBER,
79 PENDING_ADJ_VALUE
80 )
81 (SELECT D.CUSTOMER_ID,
82 D.CUSTOMER_SITE_USE_ID,
83 D.CURRENCY_CODE,
84 D.ORG_ID,
85 SYSDATE,
86 -2003,
87 SYSDATE,
88 -2003,
89 -2003,
90 nvl(SUM(D.OP_INV_SUM),0) OP_INV_SUM,
91 nvl(SUM(D.OP_INV_COUNT),0) OP_INV_COUNT,
92 nvl(SUM(D.OP_CM_SUM),0) OP_CM_SUM,
93 nvl(SUM(D.OP_CM_COUNT),0) OP_CM_COUNT,
94 nvl(SUM(D.OP_DEP_SUM),0) OP_DEP_SUM,
95 nvl(SUM(D.OP_DEP_COUNT),0) OP_DEP_COUNT,
96 nvl(SUM(D.OP_CB_SUM),0) OP_CB_SUM,
97 nvl(SUM(D.OP_CB_COUNT),0) OP_CB_COUNT,
98 nvl(SUM(D.OP_DM_SUM),0) OP_DM_SUM,
99 nvl(SUM(D.OP_DM_COUNT),0) OP_DM_COUNT,
100 nvl(SUM(D.OP_BR_SUM),0) OP_BR_SUM,
101 nvl(SUM(D.OP_BR_COUNT),0) OP_BR_COUNT,
102 nvl(SUM(D.UNRESOLVED_CASH_VALUE),0) UNRESOLVED_CASH_VALUE,
103 nvl(SUM(D.UNRESOLVED_CASH_COUNT),0) UNRESOLVED_CASH_COUNT,
104 nvl(SUM(D.PAST_DUE_INV_VALUE),0) PAST_DUE_INV_VALUE,
105 nvl(SUM(D.PAST_DUE_INV_COUNT),0) PAST_DUE_INV_COUNT,
106 nvl(SUM(D.INV_AMT_IN_DISPUTE),0) INV_AMT_IN_DISPUTE,
107 nvl(SUM(D.INV_DISPUTE_COUNT),0) INV_DISPUTE_COUNT,
108 nvl(SUM(D.BEST_CURRENT_RECEIVABLES),0) BEST_CURRENT_RECEIVABLES,
109 nvl(SUM(D.RECEIPT_AT_RISK_AMT),0) RECEIPT_AT_RISK_AMT,
110 nvl(SUM(D.LAST_RECEIPT_AMOUNT),0) LAST_RECEIPT_AMOUNT,
111 MAX(D.LAST_RECEIPT_DATE) LAST_RECEIPT_DATE,
112 nvl(MAX(D.LAST_RECEIPT_NUMBER),0) LAST_RECEIPT_NUMBER,
113 nvl(SUM(D.PENDING_ADJ_AMT),0) PENDING_ADJ_AMT
114 FROM (
115 SELECT C.CUSTOMER_ID,
116 nvl(C.CUSTOMER_SITE_USE_ID,-99) CUSTOMER_SITE_USE_ID,
117 C.INVOICE_CURRENCY_CODE CURRENCY_CODE,
118 C.ORG_ID,
119 SUM(DECODE(CLASS,'INV', C.AMOUNT_DUE_REMAINING,0)) OP_INV_SUM,
120 COUNT(DECODE(CLASS,'INV', DECODE(C.STATUS,'OP',
121 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_INV_COUNT,
122 SUM(DECODE(CLASS,'CM', C.AMOUNT_DUE_REMAINING,0) ) OP_CM_SUM,
123 COUNT(DECODE(CLASS,'CM', DECODE(C.STATUS,'OP',
124 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_CM_COUNT,
125 SUM(DECODE(CLASS,'CB', C.AMOUNT_DUE_REMAINING,0)) OP_CB_SUM,
126 COUNT(DECODE(CLASS,'CB',DECODE(C.STATUS, 'OP',
127 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_CB_COUNT,
128 SUM(DECODE(C.CLASS,'DEP', C.AMOUNT_DUE_REMAINING) ) OP_DEP_SUM,
129 COUNT(DECODE(C.CLASS,'DEP', DECODE(C.STATUS ,'OP',
130 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_DEP_COUNT,
131 SUM(DECODE(C.CLASS,'DM', C.AMOUNT_DUE_REMAINING ,0)) OP_DM_SUM,
132 COUNT(DECODE(C.CLASS,'DM', DECODE(C.STATUS, 'OP',
133 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_DM_COUNT,
134 SUM(DECODE(C.CLASS,'BR', C.AMOUNT_DUE_REMAINING, NULL)) OP_BR_SUM,
135 COUNT(DECODE(C.CLASS,'BR', DECODE(C.STATUS, 'OP',
136 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_BR_COUNT,
137 SUM(DECODE(C.CLASS,'PMT', C.AMOUNT_DUE_REMAINING, NULL)) UNRESOLVED_CASH_VALUE,
138 COUNT(DECODE(C.CLASS,'PMT', DECODE(C.STATUS, 'OP',
139 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) UNRESOLVED_CASH_COUNT,
140 SUM(DECODE(CLASS,'INV',DECODE(C.STATUS, 'OP',
141 DECODE(SIGN(TRUNC(SYSDATE) -
142 TRUNC(NVL(C.DUE_DATE, SYSDATE))),1,
143 (C.AMOUNT_DUE_ORIGINAL - NVL(C.AMOUNT_APPLIED,0)
144 +NVL(C.AMOUNT_ADJUSTED,0)),
145 0),0),0)) PAST_DUE_INV_VALUE,
146 COUNT(DECODE(C.CLASS,'INV',DECODE(C.STATUS, 'OP',
147 DECODE(SIGN(TRUNC(SYSDATE) -
148 TRUNC(NVL(C.DUE_DATE, SYSDATE))),1,
149 C.PAYMENT_SCHEDULE_ID,
150 NULL),NULL),NULL)) PAST_DUE_INV_COUNT,
151 SUM(DECODE(CLASS,'INV',C.AMOUNT_IN_DISPUTE,0)) INV_AMT_IN_DISPUTE,
152 COUNT(DECODE(C.CLASS,'INV',DECODE(C.AMOUNT_IN_DISPUTE,
153 NULL,NULL,0,NULL,C.PAYMENT_SCHEDULE_ID),
154 NULL)) INV_DISPUTE_COUNT,
155 SUM(DECODE(C.CLASS,
156 'INV', 1,
157 'DM', 1,
158 'CB', 1,
159 'DEP', 1,
160 'BR', 1,
161 0)
162 * DECODE(SIGN(C.DUE_DATE-SYSDATE),
163 -1,0,C.AMOUNT_DUE_REMAINING ))
164 BEST_CURRENT_RECEIVABLES,
165 0 RECEIPT_AT_RISK_AMT ,
166 0 LAST_RECEIPT_AMOUNT,
167 TO_DATE(NULL) LAST_RECEIPT_DATE,
168 NULL LAST_RECEIPT_NUMBER,
169 SUM(C.AMOUNT_ADJUSTED_PENDING) PENDING_ADJ_AMT
170 FROM AR_PAYMENT_SCHEDULES_ALL C
171 WHERE c.customer_id >0
172 GROUP BY C.CUSTOMER_ID,
173 C.CUSTOMER_SITE_USE_ID,
174 C.INVOICE_CURRENCY_CODE ,
175 C.ORG_ID
176 UNION ALL
177 SELECT A1.CUSTOMER_ID,
178 A1.CUSTOMER_SITE_USE_ID,
179 A1.CURRENCY,
180 A1.ORG_ID ,
181 0 OP_INV_SUM,
182 0 OP_INV_COUNT,
183 0 OP_CM_SUM,
184 0 OP_CM_COUNT,
185 0 OP_CB_SUM,
186 0 OP_CB_COUNT,
187 0 OP_DEP_SUM,
188 0 OP_DEP_COUNT,
189 0 OP_DM_SUM,
190 0 OP_DM_COUNT,
191 0 OP_BR_SUM,
192 0 OP_BR_COUNT,
193 0 UNRESOLVED_CASH_VALUE,
194 0 UNRESOLVED_CASH_COUNT,
195 0 PAST_DUE_INV_VALUE,
196 0 PAST_DUE_INV_COUNT,
197 0 INV_AMT_IN_DISPUTE,
198 0 INV_DISPUTE_COUNT,
199 0 BEST_CURRENT_RECEIVABLES_ADO,
200 0 RECEIPT_AT_RISK_AMT,
201 B.AMOUNT * -1 LAST_RECEIPT_AMOUNT,
202 B.RECEIPT_DATE LAST_RECEIPT_DATE,
203 B.RECEIPT_NUMBER LAST_RECEIPT_NUMBER,
204 0 PENDING_ADJ_AMT
205 FROM (
206 select a.customer_id,
207 a.customer_site_use_id,
208 a.currency,
209 a.org_id,
210 max(b.cash_receipt_id) last_cash_receipt_id
211 from (
212 SELECT CR.PAY_FROM_CUSTOMER CUSTOMER_ID,
213 NVL(CR.CUSTOMER_SITE_USE_ID,-99) CUSTOMER_SITE_USE_ID,
214 CR.CURRENCY_CODE CURRENCY,
215 CR.ORG_ID,
216 MAX(CR.RECEIPT_DATE) LAST_CASH_RECEIPT_DATE
217 FROM AR_CASH_RECEIPTS_ALL CR
218 WHERE NVL(CR.CONFIRMED_FLAG, 'Y') = 'Y'
219 AND CR.REVERSAL_DATE IS NULL
220 AND CR.PAY_FROM_CUSTOMER > 0
221 AND CR.TYPE = 'CASH'
222 GROUP BY CR.PAY_FROM_CUSTOMER,
223 NVL(CR.CUSTOMER_SITE_USE_ID,-99),
224 CR.CURRENCY_CODE,
225 CR.ORG_ID) a,
226 ar_cash_receipts_all b
227 where a.last_cash_receipt_date = b.receipt_date
228 and a.CUSTOMER_id = b.pay_from_customer
229 and a.customer_site_use_id = nvl(b.customer_site_use_id,-99)
230 and a.currency = b.currency_code
231 and a.org_id = b.org_id
232 group by a.customer_id,
233 a.customer_site_use_id,
234 a.currency,
235 a.org_id) a1,
236 AR_CASH_RECEIPTS_ALL B
237 WHERE a1.LAST_CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
238 UNION ALL
239 SELECT CR.PAY_FROM_CUSTOMER CUSTOMER_ID,
240 NVL(CR.CUSTOMER_SITE_USE_ID,-99) CUSTOMER_SITE_USE_ID,
241 CR.CURRENCY_CODE CURRENCY_CODE,
242 CR.ORG_ID ORG_ID,
243 0 OP_INV_SUM,
244 0 OP_INV_COUNT,
245 0 OP_CM_SUM,
246 0 OP_CM_COUNT,
247 0 OP_CB_SUM,
248 0 OP_CB_COUNT,
249 0 OP_DEP_SUM,
250 0 OP_DEP_COUNT,
251 0 OP_DM_SUM,
252 0 OP_DM_COUNT,
253 0 OP_BR_SUM,
254 0 OP_BR_COUNT,
255 0 UNRESOLVED_CASH_VALUE,
256 0 UNRESOLVED_CASH_COUNT,
257 0 PAST_DUE_INV_VALUE,
258 0 PAST_DUE_INV_COUNT,
259 0 INV_AMT_IN_DISPUTE,
260 0 INV_DISPUTE_COUNT,
261 0 BEST_CURRENT_RECEIVABLES_ADO,
262 SUM(DECODE(RAP.APPLIED_PAYMENT_SCHEDULE_ID, -2, NULL, CRH.AMOUNT))
263 RECEIPT_AT_RISK_AMT,
264 0 LAST_RECEIPT_AMOUNT,
265 TO_DATE(NULL) LAST_RECEIPT_DATE,
266 NULL LAST_RECEIPT_NUMBER,
267 0 PENDING_ADJ_AMT
268 FROM AR_CASH_RECEIPTS_ALL CR,
269 AR_CASH_RECEIPT_HISTORY_ALL CRH,
270 AR_RECEIVABLE_APPLICATIONS_ALL RAP
271 WHERE NVL(CR.CONFIRMED_FLAG, 'Y') = 'Y'
272 AND CR.REVERSAL_DATE IS NULL
273 AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
274 AND CR.PAY_FROM_CUSTOMER > 0
275 AND CRH.CURRENT_RECORD_FLAG = 'Y'
276 AND CRH.STATUS NOT IN (DECODE (CRH.FACTOR_FLAG, 'Y', 'RISK_ELIMINATED',
277 'N', 'CLEARED'), 'REVERSED')
278 AND RAP.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
279 AND RAP.APPLIED_PAYMENT_SCHEDULE_ID(+) = -2
280 GROUP BY CR.PAY_FROM_CUSTOMER,NVL(CR.CUSTOMER_SITE_USE_ID,-99),
281 CR.ORG_ID,CR.CURRENCY_CODE
282 ) D
283 GROUP BY D.CUSTOMER_ID,D.CUSTOMER_SITE_USE_ID,D.CURRENCY_CODE,D.ORG_ID);
284
285 COMMIT;
286
287
288 IF AR_CMGT_CREDIT_REQUEST_API.is_Credit_Management_Installed()
289 THEN
290 DELETE from ar_trx_summary;
291
292 INSERT into ar_trx_summary
293 (CUST_ACCOUNT_ID,
294 SITE_USE_ID,
295 CURRENCY,
296 ORG_ID,
297 AS_OF_DATE,
298 last_update_date,
299 last_updated_by,
300 creation_date,
301 created_by,
302 last_update_login,
303 TOTAL_INVOICES_VALUE,
304 TOTAL_INVOICES_COUNT,
305 TOTAL_CREDIT_MEMOS_VALUE ,
306 TOTAL_CREDIT_MEMOS_COUNT,
307 TOTAL_CHARGEBACK_VALUE,
308 TOTAL_CHARGEBACK_COUNT,
309 TOTAL_DEPOSITS_VALUE,
310 TOTAL_DEPOSITS_COUNT,
311 TOTAL_DEBIT_MEMOS_VALUE,
312 TOTAL_DEBIT_MEMOS_COUNT,
313 TOTAL_BILLS_RECEIVABLES_VALUE,
314 TOTAL_BILLS_RECEIVABLES_COUNT,
315 TOTAL_CASH_RECEIPTS_VALUE,
316 TOTAL_CASH_RECEIPTS_COUNT,
317 COUNT_OF_DISC_INV_INST,
318 DAYS_CREDIT_GRANTED_SUM,
319 COUNT_OF_INV_INST_PAID_LATE,
320 COUNT_OF_TOT_INV_INST_PAID,
321 INV_PAID_AMOUNT,
322 INV_INST_PMT_DAYS_SUM,
323 NSF_STOP_PAYMENT_COUNT,
324 NSF_STOP_PAYMENT_AMOUNT,
325 SUM_APP_AMT,
326 TOTAL_EARNED_DISC_VALUE,
327 TOTAL_EARNED_DISC_COUNT,
328 TOTAL_UNEARNED_DISC_VALUE,
329 TOTAL_UNEARNED_DISC_COUNT,
330 SUM_APP_AMT_DAYS_LATE,
331 TOTAL_ADJUSTMENTS_VALUE,
332 TOTAL_ADJUSTMENTS_COUNT)
333 ( select D.customer_id,
334 D.customer_site_use_id,
335 D.currency_code,
336 D.org_id,
337 D.as_of_date,
338 sysdate,
339 -2003,
340 sysdate,
341 -2003,
342 -2003,
343 sum(decode(D.TOT_INV_SUM,0,null,D.TOT_INV_SUM)) TOT_INV_SUM,
344 sum(decode(D.TOT_INV_COUNT,0,null,D.TOT_INV_COUNT)) TOT_INV_COUNT,
345 SUM(decode(D.TOT_CM_SUM,0,null,D.TOT_CM_SUM)) TOT_CM_SUM,
346 SUM(decode(D.TOT_CM_COUNT,0,null,D.TOT_CM_COUNT)) TOT_CM_COUNT,
347 sum(decode(D.TOT_CB_SUM,0,null,D.TOT_CB_SUM)) TOT_CB_SUM,
348 SUM(decode(D.TOT_CB_COUNT,0,null,D.TOT_CB_COUNT)) TOT_CB_COUNT,
349 SUM(decode(D.TOT_DEP_SUM,0,null,D.TOT_DEP_SUM)) TOT_DEP_SUM,
350 SUM(decode(D.TOT_DEP_COUNT,0,null,D.TOT_DEP_COUNT)) TOT_DEP_COUNT,
351 SUM(decode(D.TOT_DM_SUM,0,null,D.TOT_DM_SUM)) TOT_DM_SUM,
352 SUM(decode(D.TOT_DM_COUNT,0,null,D.TOT_DM_COUNT)) TOT_DM_COUNT,
353 SUM(decode(D.TOT_BR_SUM,0,null,D.TOT_BR_SUM)) TOT_BR_SUM,
354 SUM(decode(D.TOT_BR_COUNT,0,null,D.TOT_BR_COUNT)) TOT_BR_COUNT,
355 SUM(decode(D.TOT_PMT_SUM,0,null,D.TOT_PMT_SUM)) TOT_PMT_SUM,
356 SUM(decode(D.TOT_PMT_COUNT,0,null,D.TOT_PMT_COUNT)) TOT_PMT_COUNT,
357 SUM(decode(D.disc_inv_inst_count,0,null,D.disc_inv_inst_count)) disc_inv_inst_count,
358 SUM(decode(D.days_credit_granted_sum,0,null,D.days_credit_granted_sum)) days_credit_granted_sum,
359 SUM(decode(D.COUNT_OF_INV_INST_PAID_LATE,0,null,D.COUNT_OF_INV_INST_PAID_LATE)) COUNT_OF_INV_INST_PAID_LATE,
360 SUM(decode(D.COUNT_OF_TOT_INV_INST_PAID,0,null,D.COUNT_OF_TOT_INV_INST_PAID)) COUNT_OF_TOT_INV_INST_PAID,
361 SUM(decode(D.INV_PAID_AMOUNT,0,null,D.INV_PAID_AMOUNT)) INV_PAID_AMOUNT,
362 SUM(decode(D.inv_inst_pmt_days_sum,0,null,D.inv_inst_pmt_days_sum)) inv_inst_pmt_days_sum,
363 sum(decode(D.NSF_STOP_PAYMENT_COUNT,0,null,D.NSF_STOP_PAYMENT_COUNT)) NSF_STOP_PAYMENT_COUNT,
364 sum(decode(D.NSF_STOP_PAYMENT_AMOUNT,0,null,D.NSF_STOP_PAYMENT_AMOUNT)) NSF_STOP_PAYMENT_AMOUNT,
365 sum(decode(D.sum_amt_applied,0,null,D.sum_amt_applied)) sum_amt_applied,
366 sum(decode(D.edisc_taken,0,null,D.edisc_taken)) edisc_taken,
367 sum(decode(D.edisc_taken,0,null,D.edisc_count)) edisc_count,
368 sum(decode(D.unedisc_taken,0,null,D.unedisc_taken)) unedisc_taken,
369 sum(decode(D.unedisc_taken,0,null,D.unedisc_count)) unedisc_count,
370 sum(decode(D.app_amt_days_late,0,null,D.app_amt_days_late)) app_amt_days_late,
371 sum(decode(D.adj_amount,0,null,D.adj_amount)) adj_amount,
372 sum(decode(D.adj_count,0,null,D.adj_count)) adj_count
373 from ( select C.customer_id,
374 C.customer_site_use_id,
375 C.currency_code,
376 C.org_id,
377 C.trx_date as_of_date,
378 sum(DECODE(C.class,'INV',C.amount_due_original,0 )) TOT_INV_SUM,
379 count(decode(C.class,'INV',C.payment_schedule_id,null)) TOT_INV_COUNT,
380 sum(DECODE(C.class,'CM',C.amount_due_original,0 )) TOT_CM_SUM,
381 count(decode(C.class,'CM',C.payment_schedule_id,null)) TOT_CM_COUNT,
382 sum(DECODE(C.class,'CB',C.amount_due_original,0 )) TOT_CB_SUM,
383 count(decode(C.class,'CB',C.payment_schedule_id,null)) TOT_CB_COUNT,
384 sum(DECODE(C.class,'DEP',C.amount_due_original,0 )) TOT_DEP_SUM,
385 count(decode(C.class,'DEP',C.payment_schedule_id,null)) TOT_DEP_COUNT,
386 sum(DECODE(C.class,'DM',C.amount_due_original,0 )) TOT_DM_SUM,
387 count(decode(C.class,'DM',C.payment_schedule_id,null)) TOT_DM_COUNT,
388 sum(DECODE(C.class,'BR',C.amount_due_original,0)) TOT_BR_SUM,
389 count(decode(C.class,'BR',C.payment_schedule_id,null)) TOT_BR_COUNT,
390 sum(DECODE(C.class,'PMT',C.amount_due_original,0 )) TOT_PMT_SUM,
391 count(decode(C.class,'PMT',C.payment_schedule_id,null)) TOT_PMT_COUNT,
392 sum(DECODE(C.class, 'INV', DECODE((nvl(C.edisc_taken,0) +
393 nvl(C.unedisc_taken,0)), 0, 0, 1),0)) DISC_INV_INST_COUNT,
394 sum(decode(C.class,'INV',((C.due_date - C.trx_date)*(nvl(C.amount_due_original,0)+
395 nvl(C.ADJ_AMOUNT,0))),null)) DAYS_CREDIT_GRANTED_SUM,
396 sum(decode(C.class,'INV',
397 DECODE(sign(NVL(C.AMOUNT_APPLIED,0)),0,null,
398 DECODE(SIGN((C.AMOUNT_DUE_ORIGINAL
399 - NVL(C.AMOUNT_APPLIED,0)
400 - nvl(C.edisc_taken,0)
401 - nvl(C.unedisc_taken,0)
402 + NVL(C.ADJ_AMOUNT,0))),SIGN(C.AMOUNT_DUE_ORIGINAL),
403 null,
404 decode(sign(C.due_date - C.actual_date_closed),
405 -1, 1,null))),null)) COUNT_OF_INV_INST_PAID_LATE,
406 sum(decode(C.class,'INV',
407 DECODE(sign(NVL(C.AMOUNT_APPLIED,0)),0,null,
408 DECODE(SIGN((C.AMOUNT_DUE_ORIGINAL
409 - NVL(C.AMOUNT_APPLIED,0)
410 - nvl(C.edisc_taken,0)
411 - nvl(C.unedisc_taken,0)
412 + NVL(C.ADJ_AMOUNT,0))),SIGN(C.AMOUNT_DUE_ORIGINAL)
413 ,null,
414 1)),null)) COUNT_OF_TOT_INV_INST_PAID,
415 sum(decode(C.class,'INV',DECODE(SIGN((C.AMOUNT_DUE_ORIGINAL
416 - NVL(C.AMOUNT_APPLIED,0)
417 - nvl(C.edisc_taken,0)
418 - nvl(C.unedisc_taken,0)
419 + NVL(C.ADJ_AMOUNT,0))),SIGN(C.AMOUNT_DUE_ORIGINAL),
420 null,nvl(C.amount_applied,0)),null)) INV_PAID_AMOUNT,
421 sum(decode(C.class,'INV',1,null)) COUNT_OF_TOT_INV_INST,
422 0 inv_inst_pmt_days_sum,
423 0 NSF_STOP_PAYMENT_COUNT,
424 0 NSF_STOP_PAYMENT_AMOUNT,
425 0 sum_amt_applied,
426 0 edisc_taken,
427 0 edisc_count,
428 0 unedisc_taken,
429 0 unedisc_count,
430 0 app_amt_days_late,
431 0 ADJ_AMOUNT,
432 0 ADJ_COUNT
433 FROM (
434 SELECT A.CUSTOMER_ID,
435 A.CUSTOMER_SITE_USE_ID,
436 A.CURRENCY_CODE,
437 A.ORG_ID ,
438 A.CLASS,
439 A.DUE_DATE,
440 A.TRX_DATE,
441 A.actual_date_closed,
442 A.PAYMENT_SCHEDULE_ID,
443 A.AMOUNT_DUE_ORIGINAL,
444 A.AMOUNT_IN_DISPUTE,
445 A.AMOUNT_APPLIED,
446 A.edisc_taken,
447 A.unedisc_taken,
448 SUM(ADJ.AMOUNT) ADJ_AMOUNT
449 FROM (
450 SELECT PS.CUSTOMER_ID,
451 NVL(PS.CUSTOMER_SITE_USE_ID,-99) CUSTOMER_SITE_USE_ID,
452 PS.INVOICE_CURRENCY_CODE CURRENCY_CODE,
453 PS.ORG_ID,
454 PS.CLASS,
455 nvl(trx_hist.amount_in_dispute, ps.amount_in_dispute) AMOUNT_IN_DISPUTE,
456 nvl(trx_hist.due_date, ps.due_date) DUE_DATE,
457 PS.AMOUNT_DUE_ORIGINAL,
458 PS.TRX_DATE,
459 PS.actual_date_closed,
460 PS.PAYMENT_SCHEDULE_ID,
461 SUM( RA.AMOUNT_APPLIED) AMOUNT_APPLIED,
462 sum(decode(ps.class, 'INV',
463 decode(ra.earned_discount_taken,0,
464 null,ra.earned_discount_taken), null)) edisc_taken,
465 sum(decode(ps.class, 'INV',
466 decode(ra.unearned_discount_taken,0,
467 null,ra.unearned_discount_taken), null)) unedisc_taken
468 FROM AR_PAYMENT_SCHEDULES_all ps,
469 AR_RECEIVABLE_APPLICATIONS_ALL RA,
470 ( select history_id, payment_schedule_id, due_date, amount_in_dispute
471 from ar_trx_summary_hist
472 where history_id in
473 (select max(history_id)
474 from ar_trx_summary_hist
475 where nvl(complete_flag,'N') = 'N'
476 and creation_date <= l_program_start_date
477 group by payment_schedule_id)) TRX_HIST
478 WHERE RA.APPLIED_PAYMENT_SCHEDULE_ID(+) = PS.PAYMENT_SCHEDULE_ID
479 AND PS.payment_schedule_id = TRX_HIST.payment_schedule_id(+)
480 AND RA.CREATION_DATE(+) <= l_program_start_date
481 AND RA.DISPLAY(+) = 'Y'
482 AND RA.STATUS(+) = 'APP'
483 AND PS.CUSTOMER_ID > 0
484 and ra.apply_date(+) >= add_months(sysdate, -24)
485 AND ps.trx_date >= add_months(sysdate, -24)
486 AND PS.CREATION_DATE <= l_program_start_date
487 GROUP BY PS.CUSTOMER_ID, NVL(PS.CUSTOMER_SITE_USE_ID,-99),
488 PS.INVOICE_CURRENCY_CODE, PS.ORG_ID,
489 PS.CLASS, PS.TRX_DATE,nvl(trx_hist.due_date, ps.due_date),
490 PS.AMOUNT_DUE_ORIGINAL,
491 nvl(trx_hist.amount_in_dispute, ps.amount_in_dispute),
492 ps.actual_date_closed, PS.PAYMENT_SCHEDULE_ID
493 ) A,
494 AR_ADJUSTMENTS_ALL ADJ
495 WHERE A.PAYMENT_SCHEDULE_ID = ADJ.PAYMENT_SCHEDULE_ID(+)
496 AND ADJ.CREATION_DATE (+) <= l_program_start_date
497 AND ADJ.STATUS(+) = 'A'
498 GROUP BY A.CUSTOMER_ID, A.CUSTOMER_SITE_USE_ID,
499 A.CURRENCY_CODE, A.ORG_ID,
500 A.CLASS, A.TRX_DATE,A.DUE_DATE,
501 A.AMOUNT_DUE_ORIGINAL, A.AMOUNT_IN_DISPUTE,
502 A.actual_date_closed,A.AMOUNT_APPLIED,
503 A.edisc_taken,A.unedisc_taken,
504 A.PAYMENT_SCHEDULE_ID
505 ) C
506 group by C.customer_id,
507 C.customer_site_use_id,
508 C.currency_code,
509 C.org_id,
510 C.trx_date
511 UNION
512 select cr.pay_from_customer customer_id,
513 nvl(cr.customer_site_use_id,-99) customer_site_use_id,
514 cr.currency_code invoice_currency_code,
515 cr.org_id,
516 cr.reversal_date as_of_date,
517 0 TOT_INV_SUM,
518 0 TOT_INV_COUNT,
519 0 TOT_CM_SUM,
520 0 TOT_CM_COUNT,
521 0 TOT_CB_SUM,
522 0 TOT_CB_COUNT,
523 0 TOT_DEP_SUM,
524 0 TOT_DEP_COUNT,
525 0 TOT_DM_SUM,
526 0 TOT_DM_COUNT,
527 0 TOT_BR_SUM,
528 0 TOT_BR_COUNT,
529 0 TOT_PMT_SUM,
530 0 TOT_PMT_COUNT,
531 0 disc_inv_inst_count,
532 0 days_credit_granted_sum,
533 0 COUNT_OF_INV_INST_PAID_LATE,
534 0 COUNT_OF_TOT_INV_INST_PAID,
535 0 INV_PAID_AMOUNT,
536 0 COUNT_OF_TOT_INV_INST,
537 0 inv_inst_pmt_days,
538 count(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT,
539 sum(cr.amount) NSF_STOP_PAYMENT_AMOUNT,
540 0 sum_amt_applied,
541 0 edisc_taken,
542 0 edisc_count,
543 0 unedisc_taken,
544 0 unedisc_count,
545 0 app_amt_days_late,
546 0 adj_amount,
547 0 adj_count
548 from ar_cash_receipts_all cr,
549 ar_cash_receipt_history_all crh
550 where cr.cash_receipt_id = crh.cash_receipt_id
551 and crh.current_record_flag = 'Y'
552 and crh.status = 'REVERSED'
553 and crh.creation_date <= l_program_start_date
554 and cr.status = 'REV'
555 and cr.reversal_category = 'NSF'
556 and cr.reversal_date > add_months(sysdate, -24)
557 /*bug#5378555---------------------------------------------------------------------+
558 |kjoshi included condition of cr.pay_from_customer > 0 to exclude 'MISC' receipts |
559 +---------------------------------------------------------------------------------*/
560 and nvl(cr.pay_from_customer,0) > 0
561 group by cr.pay_from_customer,
562 nvl(cr.customer_site_use_id,-99),
563 cr.currency_code,
564 cr.org_id,
565 cr.reversal_date
566 UNION
567 select customer_id,
568 customer_site_use_id,
569 invoice_currency_code,
570 org_id,
571 apply_date as_of_date,
572 0 TOT_INV_SUM,
573 0 TOT_INV_COUNT,
574 0 TOT_CM_SUM,
575 0 TOT_CM_COUNT,
576 0 TOT_CB_SUM,
577 0 TOT_CB_COUNT,
578 0 TOT_DEP_SUM,
579 0 TOT_DEP_COUNT,
580 0 TOT_DM_SUM,
581 0 TOT_DM_COUNT,
582 0 TOT_BR_SUM,
583 0 TOT_BR_COUNT,
584 0 TOT_PMT_SUM,
585 0 TOT_PMT_COUNT,
586 0 disc_inv_inst_count,
587 0 days_credit_granted_sum,
588 0 COUNT_OF_INV_INST_PAID_LATE,
589 0 COUNT_OF_TOT_INV_INST_PAID,
590 0 INV_PAID_AMOUNT,
591 0 COUNT_OF_TOT_INV_INST,
592 sum(decode(inv_inst_pmt_days,0,null,inv_inst_pmt_days)) inv_inst_pmt_days,
593 0 NSF_STOP_PAYMENT_COUNT,
594 0 NSF_STOP_PAYMENT_AMOUNT,
595 sum(decode(sum_amt_applied,0,null,sum_amt_applied)) sum_amt_applied,
596 sum(decode(edisc_taken,0,null,edisc_taken)) edisc_taken,
597 sum(decode(edisc_taken,0,null,edisc_count)) edisc_count,
598 sum(decode(unedisc_taken,0,null,unedisc_taken)) unedisc_taken,
599 sum(decode(unedisc_taken,0,null,unedisc_count)) unedisc_count,
600 sum(decode(app_amt_days_late,0,null,app_amt_days_late)) app_amt_days_late,
601 0 adj_amount,
602 0 adj_count
603 from ( select ps.customer_id,
604 ps.customer_site_use_id,
605 ps.invoice_currency_code,
606 ps.org_id,
607 trunc(ra.apply_date) apply_date,
608 ra.cash_receipt_id,
609 ra.applied_payment_schedule_id,
610 sum(decode(ps.class, 'INV',ra.amount_applied,0)) sum_amt_applied,
611 sum(decode(ps.class, 'INV',((ra.apply_date - (ps.trx_date + nvl(rt.printing_lead_days,0)))
612 * (nvl(ra.amount_applied,0))),null)) inv_inst_pmt_days,
613 sum(decode(ps.class, 'INV', decode(ra.earned_discount_taken,0,null,ra.earned_discount_taken), null)) edisc_taken,
614 sum(decode(ps.class, 'INV',decode(nvl(ra.earned_discount_taken,0),0,null,1),null)) edisc_count,
615 sum(decode(ps.class, 'INV', decode(ra.unearned_discount_taken,0,null,ra.unearned_discount_taken), null)) unedisc_taken,
616 sum(decode(ps.class, 'INV',decode(nvl(ra.unearned_discount_taken,0),0,null,1),null)) unedisc_count,
617 sum(decode(ps.class, 'INV',
618 (ra.apply_date - nvl(trx_hist.due_date, ps.due_date))* ra.amount_applied, null)) app_amt_days_late
619 from ar_payment_schedules_all ps,
620 ( select history_id, payment_schedule_id, due_date, amount_in_dispute
621 from ar_trx_summary_hist
622 where history_id in
623 (select max(history_id)
624 from ar_trx_summary_hist
625 where nvl(complete_flag,'N') = 'N'
626 and creation_date <= l_program_start_date
627 group by payment_schedule_id)) TRX_HIST,
628 ra_terms_b rt,
629 ar_receivable_applications_all ra
630 where ps.payment_schedule_id = ra.applied_payment_schedule_id
631 and trx_hist.payment_schedule_id(+) = ps.payment_schedule_id
632 and ps.customer_id > 0
633 and ps.term_id = rt.term_id(+)
634 and ra.creation_date <= l_program_start_date
635 and ra.status = 'APP'
636 and ra.display = 'Y'
637 and ra.application_type = 'CASH'
638 and ra.apply_date >= add_months(sysdate, -24)
639 group by ps.customer_id,
640 ps.customer_site_use_id,
641 ps.invoice_currency_code,
642 ps.org_id,
643 trunc(ra.apply_date),
644 ra.cash_receipt_id,
645 ra.applied_payment_schedule_id
646 )
647 group by customer_id,
648 customer_site_use_id,
649 invoice_currency_code,
650 org_id,
651 apply_date
652 UNION
653 select ps.customer_id,
654 ps.customer_site_use_id,
655 ps.invoice_currency_code,
656 ps.org_id,
657 adj.apply_date as_of_date,
658 0 TOT_INV_SUM,
659 0 TOT_INV_COUNT,
660 0 TOT_CM_SUM,
661 0 TOT_CM_COUNT,
662 0 TOT_CB_SUM,
663 0 TOT_CB_COUNT,
664 0 TOT_DEP_SUM,
665 0 TOT_DEP_COUNT,
666 0 TOT_DM_SUM,
667 0 TOT_DM_COUNT,
668 0 TOT_BR_SUM,
669 0 TOT_BR_COUNT,
670 0 TOT_PMT_SUM,
671 0 TOT_PMT_COUNT,
672 0 disc_inv_inst_count,
673 0 days_credit_granted_sum,
674 0 COUNT_OF_INV_INST_PAID_LATE,
675 0 COUNT_OF_TOT_INV_INST_PAID,
676 0 INV_PAID_AMOUNT,
677 0 COUNT_OF_TOT_INV_INST,
678 0 inv_inst_pmt_days,
679 0 NSF_STOP_PAYMENT_COUNT,
680 0 NSF_STOP_PAYMENT_AMOUNT,
681 0 sum_amt_applied,
682 0 edisc_taken,
683 0 edisc_count,
684 0 unedisc_taken,
685 0 unedisc_count,
686 0 app_amt_days_late,
687 sum(adj.amount) adj_amount,
688 count(adjustment_id) adj_count
689 from ar_payment_schedules_all ps,
690 ar_adjustments_all adj
691 where ps.payment_schedule_id = adj.payment_schedule_id
692 and adj.receivables_trx_id(+) > 0
693 and ps.trx_date > add_months(sysdate, -24)
694 and ps.creation_date <= l_program_start_date
695 and adj.creation_date <= l_program_start_date
696 and adj.status = 'A'
697 and adj.apply_date > add_months(sysdate, -24)
698 group by ps.customer_id,
699 ps.customer_site_use_id,
700 ps.invoice_currency_code,
701 ps.org_id,
702 adj.apply_date
703 ) D
704 group by D.customer_id,
705 D.customer_site_use_id,
706 D.currency_code,
707 D.org_id,
708 D.as_of_date);
709 COMMIT;
710
711
712 /*--------------------------------------------+
713 | |
714 | LOGIC TO UPDATE THE LARGEST INV INFO IN |
715 | AR_TRX_SUMMARY TABLE |
716 | |
717 +--------------------------------------------*/
718
719 declare
720 v_cursor1 NUMBER;
721 v_cursor2 NUMBER;
722 v_BatchSize INTEGER := 1000;
723 v_NumRows INTEGER;
724 v_customer_id DBMS_SQL.NUMBER_TABLE;
725 v_site_use_id DBMS_SQL.NUMBER_TABLE;
726 v_currency_code DBMS_SQL.VARCHAR2_TABLE;
727 v_trx_date DBMS_SQL.DATE_TABLE;
728 v_amount DBMS_SQL.NUMBER_TABLE;
729 v_cust_trx_id DBMS_SQL.NUMBER_TABLE;
730 v_return_code INTEGER;
731 text_select VARCHAR2(4000);
732 text_update VARCHAR2(4000);
733 begin
734 text_select :=
735 'SELECT customer_id, customer_site_use_id,
736 invoice_currency_code, trunc(trx_date), amount,customer_trx_id
737 FROM (
738 select customer_id, customer_site_use_id,
739 invoice_currency_code,
740 trx_date, amount,customer_trx_id,
741 RANK() OVER (PARTITION BY customer_id,
742 customer_site_use_id,
743 invoice_currency_code,
744 trx_date
745 ORDER BY amount desc, trx_date desc,
746 customer_trx_id desc) rank_amount
747 from ( select customer_id,customer_site_use_id,
748 invoice_currency_code,customer_trx_id,
749 trx_date,SUM(amount_due_original) amount
750 from ar_payment_schedules_all
751 where class = '||''''||'INV'||''''||
752 ' and customer_id > 0
753 and trx_date >= add_months(sysdate, -24)
754 group by customer_id,customer_site_use_id,
755 invoice_currency_code, trx_date, customer_trx_id
756 )
757 )
758 WHERE rank_amount = 1';
759
760 text_update := 'Update ar_trx_summary set LARGEST_INV_AMOUNT = :amount,
761 LARGEST_INV_CUST_TRX_ID = :cust_trx_id,
762 LARGEST_INV_DATE = :trx_date,
763 LAST_UPDATE_DATE = sysdate,
764 LAST_UPDATED_BY = FND_GLOBAL.user_id,
765 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
766 where cust_account_id = :customer_id
767 and SITE_USE_ID = :site_use_id
768 and CURRENCY = :currency_code
769 and AS_OF_DATE = :trx_date';
770
771 v_cursor1 := dbms_sql.open_cursor;
772 v_cursor2 := dbms_sql.open_cursor;
773
774 dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
775 dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
776
777 dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
778 dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
779 dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
780 dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
781 dbms_sql.define_array(v_cursor1,5,v_amount,v_BatchSize,1);
782 dbms_sql.define_array(v_cursor1,6,v_cust_trx_id,v_BatchSize,1);
783
784 v_return_code := dbms_sql.execute(v_cursor1);
785
786 --This is the fetch loop. Each call to FETCH_ROWS will retrive v_BatchSize
787 --rows of data. The loop is over when FETCH_ROWS returns a value< v_BatchSize.
788
789 LOOP
790
791 v_customer_id.delete;
792 v_site_use_id.delete;
793 v_currency_code.delete;
794 v_trx_date.delete;
795 v_cust_trx_id.delete;
796 v_amount.delete;
797
798 v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
799 DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
800 DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
801 DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
802 DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
803 DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_amount);
804 DBMS_SQL.COLUMN_VALUE(v_cursor1,6,v_cust_trx_id);
805
806 --The special case of v_NumRows = 0 needs to be checked here. This
807 --means that the previous fetch returned all the remaining rows and
808 --therefore we are done with the loop.
809
810 if (v_NumRows = 0) then
811 EXIT;
812 end if;
813
814 --Use BIND_ARRAYS to specify the input variables for the insert.
815 --only elements 1..V_NumRows will be used.
816
817 DBMS_SQL.BIND_ARRAY(v_cursor2,':amount',v_amount);
818 DBMS_SQL.BIND_ARRAY(v_cursor2,':cust_trx_id',v_cust_trx_id);
819 DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
820 DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
821 DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
822 DBMS_SQL.BIND_ARRAY(v_cursor2,':trx_date',v_trx_date);
823
824 v_return_code := DBMS_SQL.EXECUTE(v_cursor2);
825
826 EXIT WHEN v_NumRows < v_BatchSize;
827
828 END LOOP;
829 COMMIT;
830 DBMS_SQL.CLOSE_CURSOR(v_cursor1);
831 DBMS_SQL.CLOSE_CURSOR(v_cursor2);
832
833 end;
834
835
836 /*--------------------------------------------+
837 | |
838 | LOGIC TO UPDATE THE HIGHWATER MARK BALANCE |
839 | IN AR_TRX_SUMMARY |
840 | |
841 +--------------------------------------------*/
842
843 declare
844 v_cursor1 NUMBER;
845 v_cursor2 NUMBER;
846 v_BatchSize INTEGER := 1000;
847 v_NumRows INTEGER;
848 v_customer_id DBMS_SQL.NUMBER_TABLE;
849 v_site_use_id DBMS_SQL.NUMBER_TABLE;
850 v_currency_code DBMS_SQL.VARCHAR2_TABLE;
851 v_trx_date DBMS_SQL.DATE_TABLE;
852 v_cum_balance DBMS_SQL.NUMBER_TABLE;
853 v_return_code INTEGER;
854 text_select VARCHAR2(4000);
855 text_update VARCHAR2(4000);
856 begin
857 text_select :=
858 'select customer_id, customer_site_use_id, invoice_currency_code,
859 as_of_date , cum_balance
860 from (
861 select customer_id, customer_site_use_id, invoice_currency_code,
862 as_of_date , sum(net_amount) OVER (PARTITION BY customer_id,
863 customer_site_use_id, invoice_currency_code
864 ORDER BY customer_id, customer_site_use_id,
865 invoice_currency_code ROWS UNBOUNDED PRECEDING) cum_balance
866 from (
867 select customer_id, customer_site_use_id, invoice_currency_code,
868 as_of_date , sum(net_amount) net_amount
869 from
870 (select ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
871 ps.trx_date as_of_date, sum(ps.amount_due_original) net_amount
872 from ar_payment_schedules_all ps
873 where ps.class in ('||''''||'INV'||''''||','
874 ||''''||'CM'||''''||','
875 ||''''||'DM'||''''||','
876 ||''''||'DEP'||''''||','
877 ||''''||'BR'||''''||','
878 ||''''||'CB'||''''||')
879 and ps.customer_id > 0
880 group by ps.customer_id, ps.customer_site_use_id,
881 ps.invoice_currency_code, ps.trx_date
882 union
883 select ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
884 ra.apply_date as_of_date,
885 sum(-ra.amount_applied
886 -nvl(ra.earned_discount_taken,0)
887 -nvl(ra.unearned_discount_taken,0)) net_amount
888 from ar_payment_schedules_all ps,
889 ar_receivable_applications_all ra
890 where ps.payment_schedule_id = ra.applied_payment_schedule_id
891 and ps.customer_id > 0
892 and ra.status = '||''''||'APP'||''''||'
893 and nvl(ra.confirmed_flag,'||''''||'Y'||''''||') = '||''''||'Y'||''''||'
894 and ps.class in ('||''''||'INV'||''''||','
895 ||''''||'CM'||''''||','
896 ||''''||'DM'||''''||','
897 ||''''||'DEP'||''''||','
898 ||''''||'BR'||''''||','
899 ||''''||'CB'||''''||')
900 group by ps.customer_id, ps.customer_site_use_id,
901 ps.invoice_currency_code, ra.apply_date
902 union all
903 select ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
904 adj.apply_date as_of_date, sum(adj.amount)
905 from ar_payment_schedules_all ps,
906 ar_adjustments_all adj
907 where ps.payment_schedule_id = adj.payment_schedule_id
908 and ps.class in ('||''''||'INV'||''''||','
909 ||''''||'CM'||''''||','
910 ||''''||'DM'||''''||','
911 ||''''||'DEP'||''''||','
912 ||''''||'BR'||''''||','
913 ||''''||'CB'||''''||')
914 and adj.status = '||''''||'A'||''''||'
915 and ps.customer_id > 0
916 group by ps.customer_id, ps.customer_site_use_id,
917 ps.invoice_currency_code, adj.apply_date
918 union all
919 select ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
920 ra.apply_date as_of_date,
921 sum(nvl(ra.amount_applied_from, ra.amount_applied)
922 + nvl(ra.earned_discount_taken,0)
923 + nvl(ra.unearned_discount_taken,0)) net_amount
924 from ar_payment_schedules_all ps,
925 ar_receivable_applications_all ra
926 where ps.payment_schedule_id = ra.payment_schedule_id
927 and ps.class in ('||''''||'CM'||''''||')
928 and ra.status = '||''''||'APP'||''''||'
929 and nvl(ra.confirmed_flag,'||''''||'Y'||''''||') = '||''''||'Y'||''''||'
930 group by ps.customer_id, ps.customer_site_use_id,
931 ps.invoice_currency_code, ra.apply_date
932 )
933 group by customer_id, customer_site_use_id, invoice_currency_code,
934 as_of_date
935 order by customer_id, customer_site_use_id, invoice_currency_code,
936 as_of_date )
937 )
938 where as_of_date > add_months(sysdate , -24)';
939
940 text_update :=
941 'Update ar_trx_summary
942 set OP_BAL_HIGH_WATERMARK = :cum_balance,
943 OP_BAL_HIGH_WATERMARK_DATE = :as_of_date,
944 LAST_UPDATE_DATE = sysdate,
945 LAST_UPDATED_BY = FND_GLOBAL.user_id,
946 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
947 where cust_account_id = :customer_id
948 and SITE_USE_ID = :site_use_id
949 and CURRENCY = :currency_code
950 and AS_OF_DATE = :as_of_date';
951
952 v_cursor1 := dbms_sql.open_cursor;
953 v_cursor2 := dbms_sql.open_cursor;
954
955 dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
956 dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
957
958 dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
959 dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
960 dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
961 dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
962 dbms_sql.define_array(v_cursor1,5,v_cum_balance,v_BatchSize,1);
963
964 v_return_code := dbms_sql.execute(v_cursor1);
965
966 --This is the fetch loop. Each call to FETCH_ROWS will retrive v_BatchSize
967 --rows of data. The loop is over when FETCH_ROWS returns a value< v_BatchSize.
968
969 LOOP
970
971 v_customer_id.delete;
972 v_site_use_id.delete;
973 v_currency_code.delete;
974 v_trx_date.delete;
975 v_cum_balance.delete;
976
977 v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
978 DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
979 DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
980 DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
981 DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
982 DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_cum_balance);
983
984 --The special case of v_NumRows = 0 needs to be checked here. This
985 --means that the previous fetch returned all the remaining rows and
986 --therefore we are done with the loop.
987
988 if (v_NumRows = 0) then
989 EXIT;
990 end if;
991
992 --Use BIND_ARRAYS to specify the input variables for the insert.
993 --only elements 1..V_NumRows will be used.
994
995 DBMS_SQL.BIND_ARRAY(v_cursor2,':cum_balance',v_cum_balance);
996 DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
997 DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
998 DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
999 DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
1000 DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
1001
1002 v_return_code := DBMS_SQL.EXECUTE(v_cursor2);
1003
1004 EXIT WHEN v_NumRows < v_BatchSize;
1005 COMMIT;
1006 END LOOP;
1007 COMMIT;
1008 DBMS_SQL.CLOSE_CURSOR(v_cursor1);
1009 DBMS_SQL.CLOSE_CURSOR(v_cursor2);
1010
1011 end;
1012
1013 END IF; --is credit management installed
1014
1015 DELETE from AR_CONC_PROCESS_REQUESTS
1016 where request_id = FND_GLOBAL.conc_request_id;
1017
1018 COMMIT;
1019
1020 /* Process the business events that have been raised running the run of this
1021 concurrent program so far */
1022
1023 FOR l_be_hist_rec in get_raised_events LOOP
1024
1025 -- initialization of object variables
1026 l_list := WF_PARAMETER_LIST_T();
1027
1028 -- add more parameters to the parameters list
1029 IF l_be_hist_rec.customer_trx_id IS NOT NULL THEN
1030 wf_event.AddParameterToList(p_name => 'CUSTOMER_TRX_ID',
1031 p_value => l_be_hist_rec.customer_trx_id,
1032 p_parameterlist => l_list);
1033 END IF;
1034
1035 IF l_be_hist_rec.payment_schedule_id IS NOT NULL THEN
1036 wf_event.AddParameterToList(p_name => 'PAYMENT_SCHEDULE_ID',
1037 p_value => l_be_hist_rec.customer_trx_id,
1038 p_parameterlist => l_list);
1039 END IF;
1040
1041 IF l_be_hist_rec.CASH_RECEIPT_ID IS NOT NULL THEN
1042 wf_event.AddParameterToList(p_name => 'CASH_RECEIPT_ID',
1043 p_value => l_be_hist_rec.CASH_RECEIPT_ID,
1044 p_parameterlist => l_list);
1045 END IF;
1046
1047 IF l_be_hist_rec.RECEIVABLE_APPLICATION_ID IS NOT NULL THEN
1048 wf_event.AddParameterToList(p_name => 'RECEIVABLE_APPLICATION_ID',
1049 p_value => l_be_hist_rec.RECEIVABLE_APPLICATION_ID,
1050 p_parameterlist => l_list);
1051 END IF;
1052
1053 IF l_be_hist_rec.ADJUSTMENT_ID IS NOT NULL THEN
1054 wf_event.AddParameterToList(p_name => 'ADJUSTMENT_ID',
1055 p_value => l_be_hist_rec.ADJUSTMENT_ID,
1056 p_parameterlist => l_list);
1057 END IF;
1058
1059 IF l_be_hist_rec.HISTORY_ID IS NOT NULL THEN
1060
1061 IF l_be_hist_rec.ADJUSTMENT_ID IS NOT NULL THEN
1062
1063 wf_event.AddParameterToList(p_name => 'APPROVAL_ACTN_HIST_ID',
1064 p_value => l_be_hist_rec.HISTORY_ID,
1065 p_parameterlist => l_list);
1066
1067 ELSE
1068 wf_event.AddParameterToList(p_name => 'HISTORY_ID',
1069 p_value => l_be_hist_rec.HISTORY_ID,
1070 p_parameterlist => l_list);
1071 END IF;
1072 END IF;
1073
1074 IF l_be_hist_rec.REQUEST_ID IS NOT NULL THEN
1075 wf_event.AddParameterToList(p_name => 'REQUEST_ID',
1076 p_value => l_be_hist_rec.REQUEST_ID,
1077 p_parameterlist => l_list);
1078 END IF;
1079
1080 -- Raise Event
1081 AR_CMGT_EVENT_PKG.raise_event(
1082 p_event_name => l_be_hist_rec.business_event_name,
1083 p_event_key => l_be_hist_rec.event_key,
1084 p_parameters => l_list );
1085
1086 l_list.DELETE;
1087 END LOOP;
1088
1089 /* 6310241 - Clean out the AR_SUM_REF_EVENT_HIST table */
1090 DELETE FROM AR_SUM_REF_EVENT_HIST;
1091
1092 l_return := fnd_profile.save('AR_CMGT_ALLOW_SUMMARY_TABLE_REFRESH','N','APPL',222);
1093
1094 ELSE
1095 fnd_file.put_line(fnd_file.log,'The profile AR_CMGT_ALLOW_SUMMARY_TABLE_REFRESH = N');
1096
1097 DELETE from AR_CONC_PROCESS_REQUESTS
1098 WHERE REQUEST_ID = FND_GLOBAL.conc_request_id;
1099 COMMIT;
1100 END IF;
1101
1102 /* over commit to insure that deleted rows are recorded */
1103 COMMIT;
1104
1105 fnd_file.put_line(fnd_file.log,'AR_TRX_SUMMARY_PKG.refresh_all(-)');
1106 EXCEPTION
1107 WHEN others THEN
1108 raise;
1109 END refresh_all;
1110
1111 END AR_TRX_SUMMARY_PKG;