[Home] [Help]
PACKAGE BODY: APPS.AR_TRX_SUMMARY_PKG
Source
1 PACKAGE BODY AR_TRX_SUMMARY_PKG AS
2 /* $Header: ARCMUPGB.pls 120.25.12020000.2 2012/07/11 18:10:23 rravikir ship $ */
3
4 /* Globals */
5 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
6 TYPE l_cust_id_type IS TABLE OF
7 ar_payment_schedules_all.customer_id%type
8 INDEX BY BINARY_INTEGER;
9
10 t_cust_id l_cust_id_type;
11
12 SUCCESS CONSTANT NUMBER:=0;
13 WARNING CONSTANT NUMBER:=1;
14 FAILURE CONSTANT NUMBER:=2;
15
16 /* 6149811 - declarations to allow early (re)use */
17 PROCEDURE block_events(p_action IN VARCHAR2,
18 p_request_id IN NUMBER);
19 PROCEDURE clear_summary_tables(p_table_to_clear IN VARCHAR2);
20 PROCEDURE submit_held_events;
21 /* 6149811 - end early declarations */
22
23 PROCEDURE refresh_all(
24 errbuf IN OUT NOCOPY VARCHAR2,
25 retcode IN OUT NOCOPY VARCHAR2
26 ) IS
27 l_program_start_date DATE;
28 l_return BOOLEAN;
29 v_cursor NUMBER;
30 v_return_code INTEGER;
31 v_cursor1 NUMBER;
32 v_return_code1 INTEGER;
33 text VARCHAR2(4000);
34 l_string VARCHAR2(4000);
35 l_po_value VARCHAR2(10);
36 l_at_risk_exists VARCHAR2(1);
37 BEGIN
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
41 IF nvl(l_po_value,'N') IN ('Y','A') THEN
42
43 /* 6149811 - clear summary table data */
44 block_events('BLOCK',FND_GLOBAL.conc_request_id);
45
46 clear_summary_tables('S'); -- only clear ar_trx_summary
47
48 l_program_start_date := sysdate;
49
50 /* 6149811 - start parallel query */
51 EXECUTE IMMEDIATE 'alter session enable parallel dml';
52 EXECUTE IMMEDIATE 'alter session force parallel query';
53
54 /* 8713252 - Changed from INSERT to MERGE */
55 MERGE INTO ar_trx_bal_summary t
56 USING (SELECT D.CUSTOMER_ID,
57 D.CUSTOMER_SITE_USE_ID,
58 D.CURRENCY_CODE,
59 D.ORG_ID,
60 nvl(SUM(D.OP_INV_SUM),0) OP_INV_SUM,
61 nvl(SUM(D.OP_INV_COUNT),0) OP_INV_COUNT,
62 nvl(SUM(D.OP_CM_SUM),0) OP_CM_SUM,
63 nvl(SUM(D.OP_CM_COUNT),0) OP_CM_COUNT,
64 nvl(SUM(D.OP_DEP_SUM),0) OP_DEP_SUM,
65 nvl(SUM(D.OP_DEP_COUNT),0) OP_DEP_COUNT,
66 nvl(SUM(D.OP_CB_SUM),0) OP_CB_SUM,
67 nvl(SUM(D.OP_CB_COUNT),0) OP_CB_COUNT,
68 nvl(SUM(D.OP_DM_SUM),0) OP_DM_SUM,
69 nvl(SUM(D.OP_DM_COUNT),0) OP_DM_COUNT,
70 nvl(SUM(D.OP_BR_SUM),0) OP_BR_SUM,
71 nvl(SUM(D.OP_BR_COUNT),0) OP_BR_COUNT,
72 nvl(SUM(D.UNRESOLVED_CASH_VALUE),0) UNRESOLVED_CASH_VALUE,
73 nvl(SUM(D.UNRESOLVED_CASH_COUNT),0) UNRESOLVED_CASH_COUNT,
74 nvl(SUM(D.PAST_DUE_INV_VALUE),0) PAST_DUE_INV_VALUE,
75 nvl(SUM(D.PAST_DUE_INV_COUNT),0) PAST_DUE_INV_COUNT,
76 nvl(SUM(D.INV_AMT_IN_DISPUTE),0) INV_AMT_IN_DISPUTE,
77 nvl(SUM(D.INV_DISPUTE_COUNT),0) INV_DISPUTE_COUNT,
78 nvl(SUM(D.BEST_CURRENT_RECEIVABLES),0) BEST_CURRENT_RECEIVABLES,
79 nvl(SUM(D.RECEIPT_AT_RISK_AMT),0) RECEIPT_AT_RISK_AMT,
80 nvl(SUM(D.LAST_RECEIPT_AMOUNT),0) LAST_RECEIPT_AMOUNT,
81 MAX(D.LAST_RECEIPT_DATE) LAST_RECEIPT_DATE,
82 nvl(MAX(D.LAST_RECEIPT_NUMBER),'0') LAST_RECEIPT_NUMBER,
83 nvl(SUM(D.PENDING_ADJ_AMT),0) PENDING_ADJ_AMT
84 FROM (
85 SELECT C.CUSTOMER_ID,
86 nvl(C.CUSTOMER_SITE_USE_ID,-99) CUSTOMER_SITE_USE_ID,
87 C.INVOICE_CURRENCY_CODE CURRENCY_CODE,
88 C.ORG_ID,
89 SUM(DECODE(C.CLASS,'INV', C.AMOUNT_DUE_REMAINING,0)) OP_INV_SUM,
90 COUNT(DECODE(C.CLASS,'INV', DECODE(C.STATUS,'OP',
91 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_INV_COUNT,
92 SUM(DECODE(C.CLASS,'CM', C.AMOUNT_DUE_REMAINING,0) ) OP_CM_SUM,
93 COUNT(DECODE(C.CLASS,'CM', DECODE(C.STATUS,'OP',
94 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_CM_COUNT,
95 SUM(DECODE(C.CLASS,'CB', C.AMOUNT_DUE_REMAINING,0)) OP_CB_SUM,
96 COUNT(DECODE(C.CLASS,'CB',DECODE(C.STATUS, 'OP',
97 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_CB_COUNT,
98 SUM(DECODE(C.CLASS,'DEP', C.AMOUNT_DUE_REMAINING) ) OP_DEP_SUM,
99 COUNT(DECODE(C.CLASS,'DEP', DECODE(C.STATUS ,'OP',
100 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_DEP_COUNT,
101 SUM(DECODE(C.CLASS,'DM', C.AMOUNT_DUE_REMAINING ,0)) OP_DM_SUM,
102 COUNT(DECODE(C.CLASS,'DM', DECODE(C.STATUS, 'OP',
103 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_DM_COUNT,
104 SUM(DECODE(C.CLASS,'BR', C.AMOUNT_DUE_REMAINING, NULL)) OP_BR_SUM,
105 COUNT(DECODE(C.CLASS,'BR', DECODE(C.STATUS, 'OP',
106 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_BR_COUNT,
107 SUM(DECODE(C.CLASS,'PMT', C.AMOUNT_DUE_REMAINING * -1, NULL)) UNRESOLVED_CASH_VALUE,
108 COUNT(DECODE(C.CLASS,'PMT', DECODE(C.STATUS, 'OP',
109 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) UNRESOLVED_CASH_COUNT,
110 SUM(DECODE(C.CLASS,'INV',DECODE(C.STATUS, 'OP',
111 DECODE(SIGN(TRUNC(SYSDATE) -
112 TRUNC(NVL(C.DUE_DATE, SYSDATE))),1,
113 (C.AMOUNT_DUE_ORIGINAL
114 - NVL(C.AMOUNT_APPLIED,0)
115 + NVL(C.AMOUNT_ADJUSTED,0)
116 + NVL(C.AMOUNT_CREDITED,0)),
117 0),0),0)) PAST_DUE_INV_VALUE,
118 COUNT(DECODE(C.CLASS,'INV',DECODE(C.STATUS, 'OP',
119 DECODE(SIGN(TRUNC(SYSDATE) -
120 TRUNC(NVL(C.DUE_DATE, SYSDATE))),1,
121 C.PAYMENT_SCHEDULE_ID,
122 NULL),NULL),NULL)) PAST_DUE_INV_COUNT,
123 SUM(DECODE(CLASS,'INV',C.AMOUNT_IN_DISPUTE,0)) INV_AMT_IN_DISPUTE,
124 COUNT(DECODE(C.CLASS,'INV',DECODE(C.AMOUNT_IN_DISPUTE,
125 NULL,NULL,0,NULL,C.PAYMENT_SCHEDULE_ID),
126 NULL)) INV_DISPUTE_COUNT,
127 SUM(DECODE(C.CLASS,
128 'INV', 1,
129 'DM', 1,
130 'CB', 1,
131 'DEP', 1,
132 'BR', 1,
133 0)
134 * DECODE(SIGN(C.DUE_DATE-SYSDATE),
135 -1,0,C.AMOUNT_DUE_REMAINING )) BEST_CURRENT_RECEIVABLES,
136 0 RECEIPT_AT_RISK_AMT ,
137 0 LAST_RECEIPT_AMOUNT,
138 TO_DATE(NULL) LAST_RECEIPT_DATE,
139 NULL LAST_RECEIPT_NUMBER,
140 SUM(C.AMOUNT_ADJUSTED_PENDING) PENDING_ADJ_AMT
141 FROM AR_PAYMENT_SCHEDULES_ALL C
142 WHERE C.payment_schedule_id > 0
143 AND C.customer_id is not null
144 AND C.org_id is not null
145 GROUP BY C.CUSTOMER_ID,
146 C.CUSTOMER_SITE_USE_ID,
147 C.INVOICE_CURRENCY_CODE ,
148 C.ORG_ID ) D
149 GROUP BY D.CUSTOMER_ID,D.CUSTOMER_SITE_USE_ID,D.CURRENCY_CODE,D.ORG_ID) a
150 ON ( a.CUSTOMER_ID = t.CUST_ACCOUNT_ID
151 AND a.CUSTOMER_SITE_USE_ID = t.SITE_USE_ID
152 AND a.CURRENCY_CODE = t.CURRENCY
153 AND a.ORG_ID = t.ORG_ID
154 )
155 WHEN MATCHED THEN
156 UPDATE
157 SET
158 LAST_UPDATE_DATE = SYSDATE,
159 LAST_UPDATED_BY = -2003,
160 LAST_UPDATE_LOGIN = -2003,
161 OP_INVOICES_VALUE = a.op_inv_sum,
162 OP_INVOICES_COUNT = a.op_inv_count,
163 OP_CREDIT_MEMOS_VALUE = a.op_cm_sum,
164 OP_CREDIT_MEMOS_COUNT = a.op_cm_count,
165 OP_DEPOSITS_VALUE = a.op_dep_sum,
166 OP_DEPOSITS_COUNT = a.op_dep_count,
167 OP_CHARGEBACK_VALUE = a.op_cb_sum,
168 OP_CHARGEBACK_COUNT = a.op_cb_count,
169 OP_DEBIT_MEMOS_VALUE = a.op_dm_sum,
170 OP_DEBIT_MEMOS_COUNT = a.op_dm_count,
171 OP_BILLS_RECEIVABLES_VALUE = a.op_br_sum,
172 OP_BILLS_RECEIVABLES_COUNT = a.op_br_count,
173 UNRESOLVED_CASH_VALUE = a.unresolved_cash_value,
174 UNRESOLVED_CASH_COUNT = a.unresolved_cash_count,
175 PAST_DUE_INV_VALUE = a.past_due_inv_value,
176 PAST_DUE_INV_INST_COUNT= a.past_due_inv_count,
177 INV_AMT_IN_DISPUTE = a.inv_amt_in_dispute,
178 DISPUTED_INV_COUNT = a.inv_dispute_count,
179 BEST_CURRENT_RECEIVABLES = a.best_current_receivables,
180 PENDING_ADJ_VALUE = a.pending_adj_amt
181 WHEN NOT MATCHED THEN
182 INSERT
183 (CUST_ACCOUNT_ID,
184 SITE_USE_ID,
185 CURRENCY,
186 ORG_ID,
187 LAST_UPDATE_DATE,
188 LAST_UPDATED_BY,
189 CREATION_DATE,
190 CREATED_BY,
191 LAST_UPDATE_LOGIN,
192 OP_INVOICES_VALUE,
193 OP_INVOICES_COUNT,
194 OP_CREDIT_MEMOS_VALUE,
195 OP_CREDIT_MEMOS_COUNT,
196 OP_DEPOSITS_VALUE,
197 OP_DEPOSITS_COUNT,
198 OP_CHARGEBACK_VALUE,
199 OP_CHARGEBACK_COUNT,
200 OP_DEBIT_MEMOS_VALUE,
201 OP_DEBIT_MEMOS_COUNT,
202 OP_BILLS_RECEIVABLES_VALUE,
203 OP_BILLS_RECEIVABLES_COUNT,
204 UNRESOLVED_CASH_VALUE,
205 UNRESOLVED_CASH_COUNT,
206 PAST_DUE_INV_VALUE,
207 PAST_DUE_INV_INST_COUNT,
208 INV_AMT_IN_DISPUTE,
209 DISPUTED_INV_COUNT,
210 BEST_CURRENT_RECEIVABLES,
211 PENDING_ADJ_VALUE,
212 LAST_PAYMENT_AMOUNT,
213 LAST_PAYMENT_NUMBER)
214 VALUES
215 (a.customer_id,
216 a.customer_site_use_id,
217 a.currency_code,
218 a.org_id,
219 sysdate,
220 -2003,
221 sysdate,
222 -2003,
223 -2003,
224 a.op_inv_sum,
225 a.op_inv_count,
226 a.op_cm_sum,
227 a.op_cm_count,
228 a.op_dep_sum,
229 a.op_dep_count,
230 a.op_cb_sum,
231 a.op_cb_count,
232 a.op_dm_sum,
233 a.op_dm_count,
234 a.op_br_sum,
235 a.op_br_count,
236 a.unresolved_cash_value,
237 a.unresolved_cash_count,
238 a.past_due_inv_value,
239 a.past_due_inv_count,
240 a.inv_amt_in_dispute,
241 a.inv_dispute_count,
242 a.best_current_receivables,
243 a.pending_adj_amt,
244 a.last_receipt_amount,
245 a.last_receipt_number);
246
247 /* We have to issue a commit or the next statement will
248 raise an ORA-12838 */
249 COMMIT;
250
251 /* 8713252 - Now update last_payment_amounts */
252
253 /* 8784962 - Added WHEN NOT MATCHED to meet 9i requirements,
254 that code should never execute */
255 merge into AR_TRX_BAL_SUMMARY t
256 using (SELECT
257 A1.CUSTOMER_ID,
258 A1.CUSTOMER_SITE_USE_ID,
259 A1.CURRENCY,
260 A1.ORG_ID,
261 nvl(sum(B.AMOUNT),0) LAST_RECEIPT_AMOUNT,
262 max(B.RECEIPT_DATE) LAST_RECEIPT_DATE,
263 nvl(max(B.RECEIPT_NUMBER),0) LAST_RECEIPT_NUMBER
264 FROM
265 (select
266 cr.pay_from_customer customer_id,
267 nvl(cr.customer_site_use_id, -99) customer_site_use_id,
268 cr.currency_code currency,
269 cr.org_id org_id,
270 to_number(substr(max(to_char(cr.receipt_date, 'YYYYMMDD') ||
271 ltrim(to_char(cr.cash_receipt_id,
272 '0999999999999999999999'))),9)) last_cash_receipt_id
273 from ar_cash_receipts_all cr
274 where NVL(cr.confirmed_flag, 'Y') = 'Y'
275 and cr.reversal_date is null
276 and cr.type = 'CASH'
277 and cr.pay_from_customer IS NOT NULL
278 group by pay_from_customer, customer_site_use_id,
279 currency_code, org_id) a1,
280 AR_CASH_RECEIPTS_ALL B
281 WHERE a1.LAST_CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
282 GROUP BY A1.CUSTOMER_ID,
283 A1.CUSTOMER_SITE_USE_ID,
284 A1.CURRENCY,
285 A1.ORG_ID) a
286 ON ( a.CUSTOMER_ID = t.CUST_ACCOUNT_ID
287 AND a.CUSTOMER_SITE_USE_ID = t.SITE_USE_ID
288 AND a.CURRENCY = t.CURRENCY
289 AND a.ORG_ID = t.ORG_ID
290 )
291 WHEN MATCHED THEN UPDATE
292 SET t.LAST_PAYMENT_AMOUNT = a.LAST_RECEIPT_AMOUNT,
293 t.LAST_PAYMENT_DATE = a.LAST_RECEIPT_DATE,
294 t.LAST_PAYMENT_NUMBER = a.LAST_RECEIPT_NUMBER
295 WHEN NOT MATCHED THEN INSERT
296 (CUST_ACCOUNT_ID,
297 SITE_USE_ID,
298 CURRENCY,
299 ORG_ID,
300 LAST_UPDATE_DATE,
301 LAST_UPDATED_BY,
302 CREATION_DATE,
303 CREATED_BY,
304 LAST_UPDATE_LOGIN,
305 LAST_PAYMENT_AMOUNT,
306 LAST_PAYMENT_DATE,
307 LAST_PAYMENT_NUMBER)
308 VALUES
309 (-1 * ar_trx_summary_hist_s.nextval,
310 -999,
311 a.currency,
312 -999,
313 sysdate,
314 -2003,
315 sysdate,
316 -2003,
317 -2003,
318 a.last_receipt_amount,
319 a.last_receipt_date,
320 a.last_receipt_number);
321
322 /* 8713252 - Detect receipts at risk and set receipts_at_risk_value
323 only if they exist */
324 BEGIN
325
326 SELECT 'Y'
327 INTO l_at_risk_exists
328 FROM DUAL
329 WHERE EXISTS (
330 SELECT 'at risk receipt'
331 FROM AR_CASH_RECEIPTS_ALL CR,
332 AR_CASH_RECEIPT_HISTORY_ALL CRH
333 WHERE NVL(CR.CONFIRMED_FLAG, 'Y') = 'Y'
334 AND CR.REVERSAL_DATE IS NULL
335 AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
336 AND CRH.CURRENT_RECORD_FLAG = 'Y'
337 AND CRH.STATUS NOT IN (
338 DECODE (CRH.FACTOR_FLAG, 'Y', 'RISK_ELIMINATED',
339 'N', 'CLEARED'), 'REVERSED'));
340
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 l_at_risk_exists := 'N';
344 END;
345
346 IF l_at_risk_exists = 'Y'
347 THEN
348
349 /* We have to issue a commit or the next statement will
350 raise an ORA-12838 */
351 COMMIT;
352
353 merge into AR_TRX_BAL_SUMMARY t
354 using (SELECT CR.PAY_FROM_CUSTOMER CUSTOMER_ID,
355 NVL(CR.CUSTOMER_SITE_USE_ID,-99) CUSTOMER_SITE_USE_ID,
356 CR.CURRENCY_CODE CURRENCY,
357 CR.ORG_ID ORG_ID,
358 SUM(DECODE(RAP.APPLIED_PAYMENT_SCHEDULE_ID,-2,NULL,
359 CRH.AMOUNT)) RECEIPTS_AT_RISK_VALUE
360 FROM AR_CASH_RECEIPTS_ALL CR,
361 AR_CASH_RECEIPT_HISTORY_ALL CRH,
362 AR_RECEIVABLE_APPLICATIONS_ALL RAP
363 WHERE NVL(CR.CONFIRMED_FLAG, 'Y') = 'Y'
364 AND CR.REVERSAL_DATE IS NULL
365 AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
366 AND CRH.CURRENT_RECORD_FLAG = 'Y'
367 AND CRH.STATUS NOT IN
368 (DECODE (CRH.FACTOR_FLAG, 'Y', 'RISK_ELIMINATED',
369 'N', 'CLEARED'), 'REVERSED')
370 AND RAP.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
371 AND RAP.APPLIED_PAYMENT_SCHEDULE_ID(+) = -2
372 AND CR.PAY_FROM_CUSTOMER IS NOT NULL
373 GROUP BY CR.PAY_FROM_CUSTOMER,
374 NVL(CR.CUSTOMER_SITE_USE_ID,-99),
375 CR.CURRENCY_CODE,
376 CR.ORG_ID) a
377 ON ( a.CUSTOMER_ID = t.CUST_ACCOUNT_ID
378 AND a.CUSTOMER_SITE_USE_ID = t.SITE_USE_ID
379 AND a.CURRENCY = t.CURRENCY
380 AND a.ORG_ID = t.ORG_ID
381 )
382 WHEN MATCHED THEN UPDATE
383 SET t.RECEIPTS_AT_RISK_VALUE = a.RECEIPTS_AT_RISK_VALUE
384 WHEN NOT MATCHED THEN INSERT
385 (CUST_ACCOUNT_ID,
386 SITE_USE_ID,
387 CURRENCY,
388 ORG_ID,
389 LAST_UPDATE_DATE,
390 LAST_UPDATED_BY,
391 CREATION_DATE,
392 CREATED_BY,
393 LAST_UPDATE_LOGIN,
394 RECEIPTS_AT_RISK_VALUE)
395 VALUES
396 (-1 * ar_trx_summary_hist_s.nextval,
397 -888,
398 a.currency,
399 -999,
400 sysdate,
401 -2003,
402 sysdate,
403 -2003,
404 -2003,
405 a.receipts_at_risk_value);
406
407 END IF;
408
409 COMMIT;
410
411
412 IF AR_CMGT_CREDIT_REQUEST_API.is_Credit_Management_Installed()
413 THEN
414
415 INSERT into ar_trx_summary
416 (CUST_ACCOUNT_ID,
417 SITE_USE_ID,
418 CURRENCY,
419 ORG_ID,
420 AS_OF_DATE,
421 last_update_date,
422 last_updated_by,
423 creation_date,
424 created_by,
425 last_update_login,
426 TOTAL_INVOICES_VALUE,
427 TOTAL_INVOICES_COUNT,
428 TOTAL_CREDIT_MEMOS_VALUE ,
429 TOTAL_CREDIT_MEMOS_COUNT,
430 TOTAL_CHARGEBACK_VALUE,
431 TOTAL_CHARGEBACK_COUNT,
432 TOTAL_DEPOSITS_VALUE,
433 TOTAL_DEPOSITS_COUNT,
434 TOTAL_DEBIT_MEMOS_VALUE,
435 TOTAL_DEBIT_MEMOS_COUNT,
436 TOTAL_BILLS_RECEIVABLES_VALUE,
437 TOTAL_BILLS_RECEIVABLES_COUNT,
438 TOTAL_CASH_RECEIPTS_VALUE,
439 TOTAL_CASH_RECEIPTS_COUNT,
440 COUNT_OF_DISC_INV_INST,
441 DAYS_CREDIT_GRANTED_SUM,
442 COUNT_OF_INV_INST_PAID_LATE,
443 COUNT_OF_TOT_INV_INST_PAID,
444 INV_PAID_AMOUNT,
445 INV_INST_PMT_DAYS_SUM,
446 NSF_STOP_PAYMENT_COUNT,
447 NSF_STOP_PAYMENT_AMOUNT,
448 SUM_APP_AMT,
449 TOTAL_EARNED_DISC_VALUE,
450 TOTAL_EARNED_DISC_COUNT,
451 TOTAL_UNEARNED_DISC_VALUE,
452 TOTAL_UNEARNED_DISC_COUNT,
453 SUM_APP_AMT_DAYS_LATE,
454 TOTAL_ADJUSTMENTS_VALUE,
455 TOTAL_ADJUSTMENTS_COUNT)
456 ( select D.customer_id,
457 D.customer_site_use_id,
458 D.currency_code,
459 D.org_id,
460 D.as_of_date,
461 sysdate,
462 -2003,
463 sysdate,
464 -2003,
465 -2003,
466 sum(decode(D.TOT_INV_SUM,0,null,D.TOT_INV_SUM)) TOT_INV_SUM,
467 sum(decode(D.TOT_INV_COUNT,0,null,D.TOT_INV_COUNT)) TOT_INV_COUNT,
468 SUM(decode(D.TOT_CM_SUM,0,null,D.TOT_CM_SUM)) TOT_CM_SUM,
469 SUM(decode(D.TOT_CM_COUNT,0,null,D.TOT_CM_COUNT)) TOT_CM_COUNT,
470 sum(decode(D.TOT_CB_SUM,0,null,D.TOT_CB_SUM)) TOT_CB_SUM,
471 SUM(decode(D.TOT_CB_COUNT,0,null,D.TOT_CB_COUNT)) TOT_CB_COUNT,
472 SUM(decode(D.TOT_DEP_SUM,0,null,D.TOT_DEP_SUM)) TOT_DEP_SUM,
473 SUM(decode(D.TOT_DEP_COUNT,0,null,D.TOT_DEP_COUNT)) TOT_DEP_COUNT,
474 SUM(decode(D.TOT_DM_SUM,0,null,D.TOT_DM_SUM)) TOT_DM_SUM,
475 SUM(decode(D.TOT_DM_COUNT,0,null,D.TOT_DM_COUNT)) TOT_DM_COUNT,
476 SUM(decode(D.TOT_BR_SUM,0,null,D.TOT_BR_SUM)) TOT_BR_SUM,
477 SUM(decode(D.TOT_BR_COUNT,0,null,D.TOT_BR_COUNT)) TOT_BR_COUNT,
478 SUM(decode(D.TOT_PMT_SUM,0,null,D.TOT_PMT_SUM)) TOT_PMT_SUM,
479 SUM(decode(D.TOT_PMT_COUNT,0,null,D.TOT_PMT_COUNT)) TOT_PMT_COUNT,
480 SUM(decode(D.disc_inv_inst_count,0,null,D.disc_inv_inst_count)) disc_inv_inst_count,
481 SUM(decode(D.days_credit_granted_sum,0,null,D.days_credit_granted_sum)) days_credit_granted_sum,
482 SUM(decode(D.COUNT_OF_INV_INST_PAID_LATE,0,null,D.COUNT_OF_INV_INST_PAID_LATE)) COUNT_OF_INV_INST_PAID_LATE,
483 SUM(decode(D.COUNT_OF_TOT_INV_INST_PAID,0,null,D.COUNT_OF_TOT_INV_INST_PAID)) COUNT_OF_TOT_INV_INST_PAID,
484 SUM(decode(D.INV_PAID_AMOUNT,0,null,D.INV_PAID_AMOUNT)) INV_PAID_AMOUNT,
485 SUM(decode(D.inv_inst_pmt_days_sum,0,null,D.inv_inst_pmt_days_sum)) inv_inst_pmt_days_sum,
486 sum(decode(D.NSF_STOP_PAYMENT_COUNT,0,null,D.NSF_STOP_PAYMENT_COUNT)) NSF_STOP_PAYMENT_COUNT,
487 sum(decode(D.NSF_STOP_PAYMENT_AMOUNT,0,null,D.NSF_STOP_PAYMENT_AMOUNT)) NSF_STOP_PAYMENT_AMOUNT,
488 sum(decode(D.sum_amt_applied,0,null,D.sum_amt_applied)) sum_amt_applied,
489 sum(decode(D.edisc_taken,0,null,D.edisc_taken)) edisc_taken,
490 sum(decode(D.edisc_taken,0,null,D.edisc_count)) edisc_count,
491 sum(decode(D.unedisc_taken,0,null,D.unedisc_taken)) unedisc_taken,
492 sum(decode(D.unedisc_taken,0,null,D.unedisc_count)) unedisc_count,
493 sum(decode(D.app_amt_days_late,0,null,D.app_amt_days_late)) app_amt_days_late,
494 sum(decode(D.adj_amount,0,null,D.adj_amount)) adj_amount,
495 sum(decode(D.adj_count,0,null,D.adj_count)) adj_count
496 from ( select C.customer_id,
497 C.customer_site_use_id,
498 C.currency_code,
499 C.org_id,
500 C.trx_date as_of_date,
501 sum(DECODE(C.class,'INV',C.amount_due_original,0 )) TOT_INV_SUM,
502 count(decode(C.class,'INV',C.payment_schedule_id,null)) TOT_INV_COUNT,
503 sum(DECODE(C.class,'CM',C.amount_due_original,0 )) TOT_CM_SUM,
504 count(decode(C.class,'CM',C.payment_schedule_id,null)) TOT_CM_COUNT,
505 sum(DECODE(C.class,'CB',C.amount_due_original,0 )) TOT_CB_SUM,
506 count(decode(C.class,'CB',C.payment_schedule_id,null)) TOT_CB_COUNT,
507 sum(DECODE(C.class,'DEP',C.amount_due_original,0 )) TOT_DEP_SUM,
508 count(decode(C.class,'DEP',C.payment_schedule_id,null)) TOT_DEP_COUNT,
509 sum(DECODE(C.class,'DM',C.amount_due_original,0 )) TOT_DM_SUM,
510 count(decode(C.class,'DM',C.payment_schedule_id,null)) TOT_DM_COUNT,
511 sum(DECODE(C.class,'BR',C.amount_due_original,0)) TOT_BR_SUM,
512 count(decode(C.class,'BR',C.payment_schedule_id,null)) TOT_BR_COUNT,
513 sum(DECODE(C.class,'PMT',C.amount_due_original * -1 ,0 )) TOT_PMT_SUM,
514 count(decode(C.class,'PMT',C.payment_schedule_id,null)) TOT_PMT_COUNT,
515 sum(DECODE(C.class, 'INV', DECODE((nvl(C.edisc_taken,0) +
516 nvl(C.unedisc_taken,0)), 0, 0, 1),0)) DISC_INV_INST_COUNT,
517 sum(decode(C.class,'INV',((C.due_date - C.trx_date)*(nvl(C.amount_due_original,0)+
518 nvl(C.ADJ_AMOUNT,0))),null)) DAYS_CREDIT_GRANTED_SUM,
519 sum(decode(C.class,'INV',
520 DECODE(sign(NVL(C.AMOUNT_APPLIED,0)),0,null,
521 DECODE(SIGN((C.AMOUNT_DUE_ORIGINAL
522 - NVL(C.AMOUNT_APPLIED,0)
523 - nvl(C.edisc_taken,0)
524 - nvl(C.unedisc_taken,0)
525 + NVL(C.ADJ_AMOUNT,0))),SIGN(C.AMOUNT_DUE_ORIGINAL),
526 null,
527 decode(sign(C.due_date - C.actual_date_closed),
528 -1, 1,null))),null)) COUNT_OF_INV_INST_PAID_LATE,
529 sum(decode(C.class,'INV',
530 DECODE(sign(NVL(C.AMOUNT_APPLIED,0)),0,null,
531 DECODE(SIGN((C.AMOUNT_DUE_ORIGINAL
532 - NVL(C.AMOUNT_APPLIED,0)
533 - nvl(C.edisc_taken,0)
534 - nvl(C.unedisc_taken,0)
535 + NVL(C.ADJ_AMOUNT,0))),SIGN(C.AMOUNT_DUE_ORIGINAL)
536 ,null,
537 1)),null)) COUNT_OF_TOT_INV_INST_PAID,
538 sum(decode(C.class,'INV',DECODE(SIGN((C.AMOUNT_DUE_ORIGINAL
539 - NVL(C.AMOUNT_APPLIED,0)
540 - nvl(C.edisc_taken,0)
541 - nvl(C.unedisc_taken,0)
542 + NVL(C.ADJ_AMOUNT,0))),SIGN(C.AMOUNT_DUE_ORIGINAL),
543 null,nvl(C.amount_applied,0)),null)) INV_PAID_AMOUNT,
544 sum(decode(C.class,'INV',1,null)) COUNT_OF_TOT_INV_INST,
545 0 inv_inst_pmt_days_sum,
546 0 NSF_STOP_PAYMENT_COUNT,
547 0 NSF_STOP_PAYMENT_AMOUNT,
548 0 sum_amt_applied,
549 0 edisc_taken,
550 0 edisc_count,
551 0 unedisc_taken,
552 0 unedisc_count,
553 0 app_amt_days_late,
554 0 ADJ_AMOUNT,
555 0 ADJ_COUNT
556 FROM (
557 SELECT A.CUSTOMER_ID,
558 A.CUSTOMER_SITE_USE_ID,
559 A.CURRENCY_CODE,
560 A.ORG_ID ,
561 A.CLASS,
562 A.DUE_DATE,
563 A.TRX_DATE,
564 A.actual_date_closed,
565 A.PAYMENT_SCHEDULE_ID,
566 A.AMOUNT_DUE_ORIGINAL,
567 A.AMOUNT_IN_DISPUTE,
568 A.AMOUNT_APPLIED,
569 A.edisc_taken,
570 A.unedisc_taken,
571 SUM(ADJ.AMOUNT) ADJ_AMOUNT
572 FROM (
573 SELECT PS.CUSTOMER_ID,
574 NVL(PS.CUSTOMER_SITE_USE_ID,-99) CUSTOMER_SITE_USE_ID,
575 PS.INVOICE_CURRENCY_CODE CURRENCY_CODE,
576 PS.ORG_ID,
577 PS.CLASS,
578 ps.amount_in_dispute AMOUNT_IN_DISPUTE,
579 ps.due_date DUE_DATE,
580 PS.AMOUNT_DUE_ORIGINAL,
581 PS.TRX_DATE,
582 PS.actual_date_closed,
583 PS.PAYMENT_SCHEDULE_ID,
584 SUM( RA.AMOUNT_APPLIED) AMOUNT_APPLIED,
585 sum(decode(ps.class, 'INV',
586 decode(ra.earned_discount_taken,0,
587 null,ra.earned_discount_taken), null)) edisc_taken,
588 sum(decode(ps.class, 'INV',
589 decode(ra.unearned_discount_taken,0,
590 null,ra.unearned_discount_taken), null)) unedisc_taken
591 FROM AR_PAYMENT_SCHEDULES_all ps,
592 AR_RECEIVABLE_APPLICATIONS_ALL RA
593 WHERE RA.APPLIED_PAYMENT_SCHEDULE_ID(+) = PS.PAYMENT_SCHEDULE_ID
594 AND RA.CREATION_DATE(+) <= l_program_start_date
595 AND RA.DISPLAY(+) = 'Y'
596 AND RA.STATUS(+) = 'APP'
597 AND PS.CUSTOMER_ID > 0
598 and ra.apply_date(+) >= add_months(sysdate, -24)
599 AND ps.trx_date >= add_months(sysdate, -24)
600 AND PS.CREATION_DATE <= l_program_start_date
601 GROUP BY PS.CUSTOMER_ID, NVL(PS.CUSTOMER_SITE_USE_ID,-99),
602 PS.INVOICE_CURRENCY_CODE, PS.ORG_ID,
603 PS.CLASS, PS.TRX_DATE, ps.due_date,
604 PS.AMOUNT_DUE_ORIGINAL,
605 ps.amount_in_dispute,
606 ps.actual_date_closed, PS.PAYMENT_SCHEDULE_ID
607 ) A,
608 AR_ADJUSTMENTS_ALL ADJ
609 WHERE A.PAYMENT_SCHEDULE_ID = ADJ.PAYMENT_SCHEDULE_ID(+)
610 AND ADJ.CREATION_DATE (+) <= l_program_start_date
611 AND ADJ.STATUS(+) = 'A'
612 GROUP BY A.CUSTOMER_ID, A.CUSTOMER_SITE_USE_ID,
613 A.CURRENCY_CODE, A.ORG_ID,
614 A.CLASS, A.TRX_DATE,A.DUE_DATE,
615 A.AMOUNT_DUE_ORIGINAL, A.AMOUNT_IN_DISPUTE,
616 A.actual_date_closed,A.AMOUNT_APPLIED,
617 A.edisc_taken,A.unedisc_taken,
618 A.PAYMENT_SCHEDULE_ID
619 ) C
620 group by C.customer_id,
621 C.customer_site_use_id,
622 C.currency_code,
623 C.org_id,
624 C.trx_date
625 UNION
626 select cr.pay_from_customer customer_id,
627 nvl(cr.customer_site_use_id,-99) customer_site_use_id,
628 cr.currency_code invoice_currency_code,
629 cr.org_id,
630 cr.reversal_date as_of_date,
631 0 TOT_INV_SUM,
632 0 TOT_INV_COUNT,
633 0 TOT_CM_SUM,
634 0 TOT_CM_COUNT,
635 0 TOT_CB_SUM,
636 0 TOT_CB_COUNT,
637 0 TOT_DEP_SUM,
638 0 TOT_DEP_COUNT,
639 0 TOT_DM_SUM,
640 0 TOT_DM_COUNT,
641 0 TOT_BR_SUM,
642 0 TOT_BR_COUNT,
643 0 TOT_PMT_SUM,
644 0 TOT_PMT_COUNT,
645 0 disc_inv_inst_count,
646 0 days_credit_granted_sum,
647 0 COUNT_OF_INV_INST_PAID_LATE,
648 0 COUNT_OF_TOT_INV_INST_PAID,
649 0 INV_PAID_AMOUNT,
650 0 COUNT_OF_TOT_INV_INST,
651 0 inv_inst_pmt_days,
652 count(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT,
653 sum(cr.amount) NSF_STOP_PAYMENT_AMOUNT,
654 0 sum_amt_applied,
655 0 edisc_taken,
656 0 edisc_count,
657 0 unedisc_taken,
658 0 unedisc_count,
659 0 app_amt_days_late,
660 0 adj_amount,
661 0 adj_count
662 from ar_cash_receipts_all cr,
663 ar_cash_receipt_history_all crh
664 where cr.cash_receipt_id = crh.cash_receipt_id
665 and crh.current_record_flag = 'Y'
666 and crh.status = 'REVERSED'
667 and crh.creation_date <= l_program_start_date
668 and cr.status = 'REV'
669 and cr.reversal_category = 'NSF'
670 and cr.reversal_date > add_months(sysdate, -24)
671 and nvl(cr.pay_from_customer,0) > 0
672 group by cr.pay_from_customer,
673 nvl(cr.customer_site_use_id,-99),
674 cr.currency_code,
675 cr.org_id,
676 cr.reversal_date
677 UNION
678 select customer_id,
679 customer_site_use_id,
680 invoice_currency_code,
681 org_id,
682 apply_date as_of_date,
683 0 TOT_INV_SUM,
684 0 TOT_INV_COUNT,
685 0 TOT_CM_SUM,
686 0 TOT_CM_COUNT,
687 0 TOT_CB_SUM,
688 0 TOT_CB_COUNT,
689 0 TOT_DEP_SUM,
690 0 TOT_DEP_COUNT,
691 0 TOT_DM_SUM,
692 0 TOT_DM_COUNT,
693 0 TOT_BR_SUM,
694 0 TOT_BR_COUNT,
695 0 TOT_PMT_SUM,
696 0 TOT_PMT_COUNT,
697 0 disc_inv_inst_count,
698 0 days_credit_granted_sum,
699 0 COUNT_OF_INV_INST_PAID_LATE,
700 0 COUNT_OF_TOT_INV_INST_PAID,
701 0 INV_PAID_AMOUNT,
702 0 COUNT_OF_TOT_INV_INST,
703 sum(decode(inv_inst_pmt_days,0,null,inv_inst_pmt_days)) inv_inst_pmt_days,
704 0 NSF_STOP_PAYMENT_COUNT,
705 0 NSF_STOP_PAYMENT_AMOUNT,
706 sum(decode(sum_amt_applied,0,null,sum_amt_applied)) sum_amt_applied,
707 sum(decode(edisc_taken,0,null,edisc_taken)) edisc_taken,
708 sum(decode(edisc_taken,0,null,edisc_count)) edisc_count,
709 sum(decode(unedisc_taken,0,null,unedisc_taken)) unedisc_taken,
710 sum(decode(unedisc_taken,0,null,unedisc_count)) unedisc_count,
711 sum(decode(app_amt_days_late,0,null,app_amt_days_late)) app_amt_days_late,
712 0 adj_amount,
713 0 adj_count
714 from ( select ps.customer_id,
715 ps.customer_site_use_id,
716 ps.invoice_currency_code,
717 ps.org_id,
718 trunc(ra.apply_date) apply_date,
719 ra.cash_receipt_id,
720 ra.applied_payment_schedule_id,
721 sum(decode(ps.class, 'INV',ra.amount_applied,0)) sum_amt_applied,
722 sum(decode(ps.class, 'INV',((ra.apply_date - (ps.trx_date + nvl(rt.printing_lead_days,0)))
723 * (nvl(ra.amount_applied,0))),null)) inv_inst_pmt_days,
724 sum(decode(ps.class, 'INV', decode(ra.earned_discount_taken,0,null,ra.earned_discount_taken), null)) edisc_taken,
725 sum(decode(ps.class, 'INV',decode(nvl(ra.earned_discount_taken,0),0,null,1),null)) edisc_count,
726 sum(decode(ps.class, 'INV', decode(ra.unearned_discount_taken,0,null,ra.unearned_discount_taken), null)) unedisc_taken,
727 sum(decode(ps.class, 'INV',decode(nvl(ra.unearned_discount_taken,0),0,null,1),null)) unedisc_count,
728 sum(decode(ps.class, 'INV',
729 (ra.apply_date - ps.due_date)* ra.amount_applied, null)) app_amt_days_late
730 from ar_payment_schedules_all ps,
731 ra_terms_b rt,
732 ar_receivable_applications_all ra
733 where ps.payment_schedule_id = ra.applied_payment_schedule_id
734 and ps.customer_id > 0
735 and ps.term_id = rt.term_id(+)
736 and ra.creation_date <= l_program_start_date
737 and ra.status = 'APP'
738 and ra.display = 'Y'
739 and ra.application_type = 'CASH'
740 and ra.apply_date >= add_months(sysdate, -24)
741 group by ps.customer_id,
742 ps.customer_site_use_id,
743 ps.invoice_currency_code,
744 ps.org_id,
745 trunc(ra.apply_date),
746 ra.cash_receipt_id,
747 ra.applied_payment_schedule_id
748 )
749 group by customer_id,
750 customer_site_use_id,
751 invoice_currency_code,
752 org_id,
753 apply_date
754 UNION
755 select ps.customer_id,
756 ps.customer_site_use_id,
757 ps.invoice_currency_code,
758 ps.org_id,
759 adj.apply_date as_of_date,
760 0 TOT_INV_SUM,
761 0 TOT_INV_COUNT,
762 0 TOT_CM_SUM,
763 0 TOT_CM_COUNT,
764 0 TOT_CB_SUM,
765 0 TOT_CB_COUNT,
766 0 TOT_DEP_SUM,
767 0 TOT_DEP_COUNT,
768 0 TOT_DM_SUM,
769 0 TOT_DM_COUNT,
770 0 TOT_BR_SUM,
771 0 TOT_BR_COUNT,
772 0 TOT_PMT_SUM,
773 0 TOT_PMT_COUNT,
774 0 disc_inv_inst_count,
775 0 days_credit_granted_sum,
776 0 COUNT_OF_INV_INST_PAID_LATE,
777 0 COUNT_OF_TOT_INV_INST_PAID,
778 0 INV_PAID_AMOUNT,
779 0 COUNT_OF_TOT_INV_INST,
780 0 inv_inst_pmt_days,
781 0 NSF_STOP_PAYMENT_COUNT,
782 0 NSF_STOP_PAYMENT_AMOUNT,
783 0 sum_amt_applied,
784 0 edisc_taken,
785 0 edisc_count,
786 0 unedisc_taken,
787 0 unedisc_count,
788 0 app_amt_days_late,
789 sum(adj.amount) adj_amount,
790 count(adjustment_id) adj_count
791 from ar_payment_schedules_all ps,
792 ar_adjustments_all adj
793 where ps.payment_schedule_id = adj.payment_schedule_id
794 and adj.receivables_trx_id(+) > 0
795 and ps.trx_date > add_months(sysdate, -24)
796 and ps.creation_date <= l_program_start_date
797 and adj.creation_date <= l_program_start_date
798 and adj.status = 'A'
799 and adj.apply_date > add_months(sysdate, -24)
800 group by ps.customer_id,
801 ps.customer_site_use_id,
802 ps.invoice_currency_code,
803 ps.org_id,
804 adj.apply_date
805 ) D
806 group by D.customer_id,
807 D.customer_site_use_id,
808 D.currency_code,
809 D.org_id,
810 D.as_of_date);
811 COMMIT;
812
813 /* 6149811 - stop parallel processing now */
814 EXECUTE IMMEDIATE 'alter session disable parallel query';
815
816 /*--------------------------------------------+
817 | |
818 | LOGIC TO UPDATE THE LARGEST INV INFO IN |
819 | AR_TRX_SUMMARY TABLE |
820 | |
821 +--------------------------------------------*/
822
823 declare
824 v_cursor1 NUMBER;
825 v_cursor2 NUMBER;
826 v_BatchSize INTEGER := 1000;
827 v_NumRows INTEGER;
828 v_customer_id DBMS_SQL.NUMBER_TABLE;
829 v_site_use_id DBMS_SQL.NUMBER_TABLE;
830 v_currency_code DBMS_SQL.VARCHAR2_TABLE;
831 v_trx_date DBMS_SQL.DATE_TABLE;
832 v_amount DBMS_SQL.NUMBER_TABLE;
833 v_cust_trx_id DBMS_SQL.NUMBER_TABLE;
834 v_return_code INTEGER;
835 text_select VARCHAR2(4000);
836 text_update VARCHAR2(4000);
837 begin
838 text_select :=
839 'SELECT customer_id, customer_site_use_id,
840 invoice_currency_code, trunc(trx_date), amount,customer_trx_id
841 FROM (
842 select customer_id, customer_site_use_id,
843 invoice_currency_code,
844 trx_date, amount,customer_trx_id,
845 RANK() OVER (PARTITION BY customer_id,
846 customer_site_use_id,
847 invoice_currency_code,
848 trx_date
849 ORDER BY amount desc, trx_date desc,
850 customer_trx_id desc) rank_amount
851 from ( select customer_id,customer_site_use_id,
852 invoice_currency_code,customer_trx_id,
853 trx_date,SUM(amount_due_original) amount
854 from ar_payment_schedules_all
855 where class = '||''''||'INV'||''''||
856 ' and customer_id > 0
857 and trx_date >= add_months(sysdate, -24)
858 group by customer_id,customer_site_use_id,
859 invoice_currency_code, trx_date, customer_trx_id
860 )
861 )
862 WHERE rank_amount = 1';
863
864 text_update := 'Update ar_trx_summary
865 set LARGEST_INV_AMOUNT = :amount,
866 LARGEST_INV_CUST_TRX_ID = :cust_trx_id,
867 LARGEST_INV_DATE = :trx_date,
868 LAST_UPDATE_DATE = sysdate,
869 LAST_UPDATED_BY = FND_GLOBAL.user_id,
870 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
871 where cust_account_id = :customer_id
872 and SITE_USE_ID = :site_use_id
873 and CURRENCY = :currency_code
874 and AS_OF_DATE = :trx_date';
875
876 v_cursor1 := dbms_sql.open_cursor;
877 v_cursor2 := dbms_sql.open_cursor;
878
879 dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
880 dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
881
882 dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
883 dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
884 dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
885 dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
886 dbms_sql.define_array(v_cursor1,5,v_amount,v_BatchSize,1);
887 dbms_sql.define_array(v_cursor1,6,v_cust_trx_id,v_BatchSize,1);
888
889 v_return_code := dbms_sql.execute(v_cursor1);
890
891 --This is the fetch loop. Each call to FETCH_ROWS will retrive v_BatchSize
892 --rows of data. The loop is over when FETCH_ROWS returns a value< v_BatchSize.
893
894 LOOP
895
896 v_customer_id.delete;
897 v_site_use_id.delete;
898 v_currency_code.delete;
899 v_trx_date.delete;
900 v_cust_trx_id.delete;
901 v_amount.delete;
902
903 v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
904 DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
905 DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
906 DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
907 DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
908 DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_amount);
909 DBMS_SQL.COLUMN_VALUE(v_cursor1,6,v_cust_trx_id);
910
911 --The special case of v_NumRows = 0 needs to be checked here. This
912 --means that the previous fetch returned all the remaining rows and
913 --therefore we are done with the loop.
914
915 if (v_NumRows = 0) then
916 EXIT;
917 end if;
918
919 --Use BIND_ARRAYS to specify the input variables for the insert.
920 --only elements 1..V_NumRows will be used.
921
922 DBMS_SQL.BIND_ARRAY(v_cursor2,':amount',v_amount);
923 DBMS_SQL.BIND_ARRAY(v_cursor2,':cust_trx_id',v_cust_trx_id);
924 DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
925 DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
926 DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
927 DBMS_SQL.BIND_ARRAY(v_cursor2,':trx_date',v_trx_date);
928
929 v_return_code := DBMS_SQL.EXECUTE(v_cursor2);
930
931 EXIT WHEN v_NumRows < v_BatchSize;
932 COMMIT;
933 END LOOP;
934 COMMIT;
935 DBMS_SQL.CLOSE_CURSOR(v_cursor1);
936 DBMS_SQL.CLOSE_CURSOR(v_cursor2);
937
938 END;
939
940
941 /*--------------------------------------------+
942 | |
943 | LOGIC TO UPDATE THE HIGHWATER MARK BALANCE |
944 | IN AR_TRX_SUMMARY |
945 | |
946 +--------------------------------------------*/
947
948 declare
949 v_cursor1 NUMBER;
950 v_cursor2 NUMBER;
951 v_BatchSize INTEGER := 1000;
952 v_NumRows INTEGER;
953 v_customer_id DBMS_SQL.NUMBER_TABLE;
954 v_site_use_id DBMS_SQL.NUMBER_TABLE;
955 v_currency_code DBMS_SQL.VARCHAR2_TABLE;
956 v_trx_date DBMS_SQL.DATE_TABLE;
957 v_cum_balance DBMS_SQL.NUMBER_TABLE;
958 v_return_code INTEGER;
959 text_select VARCHAR2(4000);
960 text_update VARCHAR2(4000);
961 begin
962 text_select :=
963 'select customer_id, customer_site_use_id, invoice_currency_code,
964 as_of_date , cum_balance
965 from (
966 select customer_id, customer_site_use_id, invoice_currency_code,
967 as_of_date , sum(net_amount) OVER (PARTITION BY customer_id,
968 customer_site_use_id, invoice_currency_code
969 ORDER BY customer_id, customer_site_use_id,
970 invoice_currency_code ROWS UNBOUNDED PRECEDING) cum_balance
971 from (
972 select customer_id, customer_site_use_id, invoice_currency_code,
973 as_of_date , sum(net_amount) net_amount
974 from
975 (select ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
976 ps.trx_date as_of_date, sum(ps.amount_due_original) net_amount
977 from ar_payment_schedules_all ps
978 where ps.class in ('||''''||'INV'||''''||','
979 ||''''||'CM'||''''||','
980 ||''''||'DM'||''''||','
981 ||''''||'DEP'||''''||','
982 ||''''||'BR'||''''||','
983 ||''''||'CB'||''''||')
984 and ps.customer_id > 0
985 group by ps.customer_id, ps.customer_site_use_id,
986 ps.invoice_currency_code, ps.trx_date
987 union all
988 select ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
989 ra.apply_date as_of_date,
990 sum(-ra.amount_applied
991 -nvl(ra.earned_discount_taken,0)
992 -nvl(ra.unearned_discount_taken,0)) net_amount
993 from ar_payment_schedules_all ps,
994 ar_receivable_applications_all ra
995 where ps.payment_schedule_id = ra.applied_payment_schedule_id
996 and ps.customer_id > 0
997 and ra.status = '||''''||'APP'||''''||'
998 and ra.application_type = '||''''||'CASH'||''''||'
999 and nvl(ra.confirmed_flag,'||''''||'Y'||''''||') = '||''''||'Y'||''''||'
1000 and ps.class in ('||''''||'INV'||''''||','
1001 ||''''||'CM'||''''||','
1002 ||''''||'DM'||''''||','
1003 ||''''||'DEP'||''''||','
1004 ||''''||'BR'||''''||','
1005 ||''''||'CB'||''''||')
1006 group by ps.customer_id, ps.customer_site_use_id,
1007 ps.invoice_currency_code, ra.apply_date
1008 union all
1009 select ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
1010 adj.apply_date as_of_date, sum(adj.amount)
1011 from ar_payment_schedules_all ps,
1012 ar_adjustments_all adj
1013 where ps.payment_schedule_id = adj.payment_schedule_id
1014 and ps.class in ('||''''||'INV'||''''||','
1015 ||''''||'CM'||''''||','
1016 ||''''||'DM'||''''||','
1017 ||''''||'DEP'||''''||','
1018 ||''''||'BR'||''''||','
1019 ||''''||'CB'||''''||')
1020 and adj.status = '||''''||'A'||''''||'
1021 and ps.customer_id > 0
1022 group by ps.customer_id, ps.customer_site_use_id,
1023 ps.invoice_currency_code, adj.apply_date
1024 )
1025 group by customer_id, customer_site_use_id, invoice_currency_code,
1026 as_of_date
1027 order by customer_id, customer_site_use_id, invoice_currency_code,
1028 as_of_date )
1029 )
1030 where as_of_date > add_months(sysdate , -24)';
1031
1032 text_update :=
1033 'Update ar_trx_summary
1034 set OP_BAL_HIGH_WATERMARK = :cum_balance,
1035 OP_BAL_HIGH_WATERMARK_DATE = :as_of_date,
1036 LAST_UPDATE_DATE = sysdate,
1037 LAST_UPDATED_BY = FND_GLOBAL.user_id,
1038 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1039 where cust_account_id = :customer_id
1040 and SITE_USE_ID = :site_use_id
1041 and CURRENCY = :currency_code
1042 and AS_OF_DATE = :as_of_date';
1043
1044 v_cursor1 := dbms_sql.open_cursor;
1045 v_cursor2 := dbms_sql.open_cursor;
1046
1047 dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
1048 dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
1049
1050 dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
1051 dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
1052 dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
1053 dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
1054 dbms_sql.define_array(v_cursor1,5,v_cum_balance,v_BatchSize,1);
1055
1056 v_return_code := dbms_sql.execute(v_cursor1);
1057
1058 --This is the fetch loop. Each call to FETCH_ROWS will retrive v_BatchSize
1059 --rows of data. The loop is over when FETCH_ROWS returns a value< v_BatchSize.
1060
1061 LOOP
1062
1063 v_customer_id.delete;
1064 v_site_use_id.delete;
1065 v_currency_code.delete;
1066 v_trx_date.delete;
1067 v_cum_balance.delete;
1068
1069 v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
1070 DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
1071 DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
1072 DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
1073 DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
1074 DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_cum_balance);
1075
1076 --The special case of v_NumRows = 0 needs to be checked here. This
1077 --means that the previous fetch returned all the remaining rows and
1078 --therefore we are done with the loop.
1079
1080 if (v_NumRows = 0) then
1081 EXIT;
1082 end if;
1083
1084 --Use BIND_ARRAYS to specify the input variables for the insert.
1085 --only elements 1..V_NumRows will be used.
1086
1087 DBMS_SQL.BIND_ARRAY(v_cursor2,':cum_balance',v_cum_balance);
1088 DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
1089 DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
1090 DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
1091 DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
1092 DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
1093
1094 v_return_code := DBMS_SQL.EXECUTE(v_cursor2);
1095
1096 EXIT WHEN v_NumRows < v_BatchSize;
1097 COMMIT;
1098 END LOOP;
1099 COMMIT;
1100 DBMS_SQL.CLOSE_CURSOR(v_cursor1);
1101 DBMS_SQL.CLOSE_CURSOR(v_cursor2);
1102
1103 end;
1104 ELSE
1105 /*
1106 If credit Management is not installed, the parallel dml operation should be disabled(which is already enabled)
1107 If the dml operations are not disabled then ORA-12839 error will be thrown
1108 */
1109 EXECUTE IMMEDIATE 'alter session disable parallel query';
1110
1111 END IF; --is credit management installed
1112
1113 /* 6149811 - remove ar_conc_process_req row
1114 and submit child process to submit the events that
1115 were held during runtime */
1116 block_events('UNBLOCK',FND_GLOBAL.conc_request_id);
1117 submit_held_events;
1118
1119 IF l_po_value = 'Y'
1120 THEN
1121 l_return := fnd_profile.save('AR_CMGT_ALLOW_SUMMARY_TABLE_REFRESH',
1122 'N','APPL',222);
1123 END IF;
1124
1125 ELSE
1126 fnd_file.put_line(fnd_file.log,'The profile AR_CMGT_ALLOW_SUMMARY_TABLE_REFRESH = N');
1127
1128 END IF;
1129
1130 /* over commit to insure that deleted rows are recorded */
1131 COMMIT;
1132
1133 fnd_file.put_line(fnd_file.log,'AR_TRX_SUMMARY_PKG.refresh_all(-)');
1134 EXCEPTION
1135 WHEN others THEN
1136 raise;
1137 END refresh_all;
1138 --------------------------------------------------------------
1139 /* Bug 6149811 - multthreading and performance enhancements
1140 7518998 - allow small vs large customer list for perf
1141 p_list_size = ALL or ACTIVE */
1142 --------------------------------------------------------------
1143 PROCEDURE collect_customers(
1144 p_max_workers IN NUMBER,
1145 p_worker_number IN NUMBER,
1146 p_list_size IN VARCHAR2 DEFAULT 'ALL',
1147 p_cust_id IN OUT NOCOPY l_cust_id_type) IS
1148
1149 CURSOR c_cust_all IS
1150 SELECT DISTINCT customer_id
1151 FROM ar_payment_schedules_all
1152 WHERE MOD(customer_id, p_max_workers) = p_worker_number
1153 AND payment_schedule_id > 0;
1154
1155 CURSOR c_cust_active IS
1156 SELECT DISTINCT customer_id
1157 FROM ar_payment_schedules_all
1158 WHERE MOD(customer_id, p_max_workers) = p_worker_number
1159 AND payment_schedule_id > 0
1160 AND trx_date > add_months(sysdate, -24);
1161
1162 l_rows NUMBER;
1163 BEGIN
1164 arp_standard.debug('arp_trx_summary_pkg.collect_customers()+');
1165 arp_standard.debug(' p_worker_number = ' || p_worker_number);
1166 arp_standard.debug(' p_list_size = ' || p_list_size);
1167
1168 /* The processing of ar_trx_bal_summary requires all customers,
1169 but the one for ar_trx_summary only requires active customers.
1170 So we can rebuild the list for each table separately and
1171 significantly cust the discarded data from the ar_trx_summary
1172 routine(s) */
1173 p_cust_id.delete;
1174
1175 IF p_list_size = 'ALL'
1176 THEN
1177 /* ALL, consider any customer represented in PS table */
1178 OPEN c_cust_all;
1179 FETCH c_cust_all BULK COLLECT INTO p_cust_id;
1180 l_rows := c_cust_all%ROWCOUNT;
1181 CLOSE c_cust_all;
1182
1183 ELSE
1184 /* ACTIVE, meaning with PS rows < 24 months old */
1185 OPEN c_cust_active;
1186 FETCH c_cust_active BULK COLLECT INTO p_cust_id;
1187 l_rows := c_cust_active%ROWCOUNT;
1188 CLOSE c_cust_active;
1189
1190 /* Populate GT table for use in HWM and Largest INV subroutines */
1191 FORALL i IN p_cust_id.FIRST .. p_cust_id.LAST
1192 INSERT INTO ar_cust_search_gt
1193 (customer_id)
1194 VALUES(p_cust_id(i));
1195
1196 /* FOR i IN p_cust_id.FIRST .. p_cust_id.LAST
1197 LOOP
1198 arp_standard.debug(' p_cust_id(' || i || ') = ' || p_cust_id(i));
1199 END LOOP; */
1200
1201 END IF;
1202
1203
1204 /* Display number of customers in conc log */
1205 fnd_file.put_line(FND_FILE.LOG, ' worker ' || p_worker_number ||
1206 ' of ' || p_max_workers || ' number of customers: ' ||
1207 l_rows);
1208
1209 arp_standard.debug(' count of distinct customers = ' || l_rows);
1210 arp_standard.debug('arp_trx_summary_pkg.collect_customers()-');
1211 END collect_customers;
1212
1213
1214 /* 8784962 - Allow for call to this function that only clears
1215 ar_trx_summary or both ar_trx_bal_summary and ar_trx_summary.
1216
1217 legal values are A(all), B(bal only), S(summary only) */
1218
1219 PROCEDURE clear_summary_tables(p_table_to_clear IN VARCHAR2) IS
1220 l_status VARCHAR2(1); -- junk variable
1221 l_industry VARCHAR2(1); -- junk variable
1222 l_schema VARCHAR2(30);
1223 BEGIN
1224 IF FND_INSTALLATION.get_app_info('AR', l_status, l_industry, l_schema)
1225 THEN
1226 IF PG_DEBUG in ('Y', 'C') THEN
1227 arp_standard.debug('Retrieved schema for AR : ' || l_schema);
1228 END IF;
1229 ELSE
1230 IF PG_DEBUG in ('Y', 'C') THEN
1231 arp_standard.debug('Problem retrieving AR schema name from fnd_installation');
1232 END IF;
1233 arp_standard.debug('EXCEPTION: arp_trx_summary_pkg.clear_summary_tables');
1234 RETURN;
1235 END IF;
1236
1237 arp_standard.debug('Table to clear = ' || p_table_to_clear);
1238
1239 /* If schema is set, clear the tables */
1240 IF l_schema IS NOT NULL
1241 THEN
1242 IF PG_DEBUG in ('Y','C')
1243 THEN
1244 arp_standard.debug('truncating table data');
1245 END IF;
1246 IF p_table_to_clear IN ('A','B')
1247 THEN
1248 EXECUTE IMMEDIATE 'truncate table ' || l_schema || '.AR_TRX_BAL_SUMMARY';
1249 END IF;
1250
1251 IF p_table_to_clear IN ('A','S')
1252 THEN
1253 EXECUTE IMMEDIATE 'truncate table ' || l_schema || '.AR_TRX_SUMMARY';
1254 END IF;
1255 END IF;
1256
1257 END clear_summary_tables;
1258
1259 PROCEDURE clear_summary_by_customer(p_cust_id IN l_cust_id_type) IS
1260 BEGIN
1261 IF PG_DEBUG in ('Y','C')
1262 THEN
1263 arp_standard.debug('ar_trx_summary_pkg.clear_summary_by_customer()+');
1264 END IF;
1265
1266 FORALL i IN 1..p_cust_id.COUNT
1267 DELETE FROM AR_TRX_BAL_SUMMARY
1268 WHERE cust_account_id = p_cust_id(i);
1269
1270 FORALL i IN 1..p_cust_id.COUNT
1271 DELETE FROM AR_TRX_SUMMARY
1272 WHERE cust_account_id = p_cust_id(i);
1273
1274 IF PG_DEBUG in ('Y','C')
1275 THEN
1276 arp_standard.debug('ar_trx_summary_pkg.clear_summary_by_customer()-');
1277 END IF;
1278 END clear_summary_by_customer;
1279
1280 PROCEDURE submit_child_workers(p_max_workers IN NUMBER,
1281 p_skip_secondary_processes IN VARCHAR2,
1282 p_fast_delete IN VARCHAR2) IS
1283 l_reqid NUMBER;
1284 l_program VARCHAR2(30) := 'ARSUMREFX' ;
1285 l_appl_short VARCHAR2(30) := 'AR' ;
1286
1287 BEGIN
1288 IF PG_DEBUG in ('Y','C')
1289 THEN
1290 arp_standard.debug('ar_trx_summary_pkg.submit_child_workers()+');
1291 END IF;
1292
1293 FOR i IN 1..(p_max_workers - 1) LOOP
1294 l_reqid := FND_REQUEST.SUBMIT_REQUEST (
1295 application=>l_appl_short,
1296 program=>l_program,
1297 sub_request=>FALSE,
1298 argument1=>p_max_workers,
1299 argument2=>i,
1300 argument3=>p_skip_secondary_processes,
1301 argument4=>p_fast_delete );
1302 END LOOP;
1303
1304 /* forced commit to get child workers active */
1305 COMMIT;
1306
1307 IF PG_DEBUG in ('Y','C')
1308 THEN
1309 arp_standard.debug('ar_trx_summary_pkg.submit_child_workers()-');
1310 END IF;
1311
1312 END submit_child_workers;
1313
1314 PROCEDURE submit_held_events IS
1315 l_reqid NUMBER;
1316 l_program VARCHAR2(30) := 'ARSUMREFEV' ;
1317 l_appl_short VARCHAR2(30) := 'AR' ;
1318
1319 BEGIN
1320 IF PG_DEBUG in ('Y','C')
1321 THEN
1322 arp_standard.debug('ar_trx_summary_pkg.submit_held_events()+');
1323 END IF;
1324
1325 l_reqid := FND_REQUEST.SUBMIT_REQUEST (
1326 application=>l_appl_short,
1327 program=>l_program,
1328 sub_request=>FALSE);
1329
1330 IF PG_DEBUG in ('Y','C')
1331 THEN
1332 arp_standard.debug(' request_id = ' || l_reqid);
1333 arp_standard.debug('ar_trx_summary_pkg.submit_held_events()-');
1334 END IF;
1335
1336 END submit_held_events;
1337
1338 PROCEDURE block_events(p_action IN VARCHAR2,
1339 p_request_id IN NUMBER) IS
1340 BEGIN
1341 IF PG_DEBUG in ('Y','C')
1342 THEN
1343 arp_standard.debug('ar_trx_summary_pkg.block_events()+');
1344 arp_standard.debug(' p_action = ' || p_action);
1345 arp_standard.debug(' p_request_id = ' || p_request_id);
1346 END IF;
1347
1348 IF p_action = 'BLOCK'
1349 THEN
1350 INSERT INTO AR_CONC_PROCESS_REQUESTS
1351 (CONCURRENT_PROGRAM_NAME, REQUEST_ID)
1352 VALUES ('ARSUMREF',p_request_id);
1353 ELSIF p_action = 'UNBLOCK'
1354 THEN
1355 DELETE FROM AR_CONC_PROCESS_REQUESTS
1356 WHERE CONCURRENT_PROGRAM_NAME = 'ARSUMREF'
1357 AND REQUEST_ID = p_request_id;
1358 ELSE
1359 IF PG_DEBUG in ('Y','C')
1360 THEN
1361 arp_standard.debug('EXCEPTION: Invalid p_action value');
1362 END IF;
1363 END IF;
1364
1365 COMMIT;
1366
1367 IF PG_DEBUG in ('Y','C')
1368 THEN
1369 arp_standard.debug('ar_trx_summary_pkg.block_events()-');
1370 END IF;
1371 END;
1372
1373 PROCEDURE load_trx_bal_summary(p_cust_id IN l_cust_id_type)
1374 IS
1375 BEGIN
1376 IF PG_DEBUG in ('Y','C')
1377 THEN
1378 arp_standard.debug('ar_trx_summary_pkg.load_trx_bal_summary()+');
1379 END IF;
1380
1381 FORALL i IN 1..p_cust_id.COUNT
1382 INSERT INTO AR_TRX_BAL_SUMMARY
1383 (CUST_ACCOUNT_ID,
1384 SITE_USE_ID,
1385 CURRENCY,
1386 ORG_ID,
1387 LAST_UPDATE_DATE,
1388 LAST_UPDATED_BY,
1389 CREATION_DATE,
1390 CREATED_BY,
1391 LAST_UPDATE_LOGIN,
1392 OP_INVOICES_VALUE,
1393 OP_INVOICES_COUNT,
1394 OP_CREDIT_MEMOS_VALUE,
1395 OP_CREDIT_MEMOS_COUNT,
1396 OP_DEPOSITS_VALUE,
1397 OP_DEPOSITS_COUNT,
1398 OP_CHARGEBACK_VALUE,
1399 OP_CHARGEBACK_COUNT,
1400 OP_DEBIT_MEMOS_VALUE,
1401 OP_DEBIT_MEMOS_COUNT,
1402 OP_BILLS_RECEIVABLES_VALUE,
1403 OP_BILLS_RECEIVABLES_COUNT,
1404 UNRESOLVED_CASH_VALUE,
1405 UNRESOLVED_CASH_COUNT,
1406 PAST_DUE_INV_VALUE,
1407 PAST_DUE_INV_INST_COUNT,
1408 INV_AMT_IN_DISPUTE,
1409 DISPUTED_INV_COUNT,
1410 BEST_CURRENT_RECEIVABLES,
1411 RECEIPTS_AT_RISK_VALUE,
1412 LAST_PAYMENT_AMOUNT,
1413 LAST_PAYMENT_DATE,
1414 LAST_PAYMENT_NUMBER,
1415 PENDING_ADJ_VALUE
1416 )
1417 (SELECT D.CUSTOMER_ID,
1418 D.CUSTOMER_SITE_USE_ID,
1419 D.CURRENCY_CODE,
1420 D.ORG_ID,
1421 SYSDATE,
1422 -2003,
1423 SYSDATE,
1424 -2003,
1425 -2003,
1426 nvl(SUM(D.OP_INV_SUM),0) OP_INV_SUM,
1427 nvl(SUM(D.OP_INV_COUNT),0) OP_INV_COUNT,
1428 nvl(SUM(D.OP_CM_SUM),0) OP_CM_SUM,
1429 nvl(SUM(D.OP_CM_COUNT),0) OP_CM_COUNT,
1430 nvl(SUM(D.OP_DEP_SUM),0) OP_DEP_SUM,
1431 nvl(SUM(D.OP_DEP_COUNT),0) OP_DEP_COUNT,
1432 nvl(SUM(D.OP_CB_SUM),0) OP_CB_SUM,
1433 nvl(SUM(D.OP_CB_COUNT),0) OP_CB_COUNT,
1434 nvl(SUM(D.OP_DM_SUM),0) OP_DM_SUM,
1435 nvl(SUM(D.OP_DM_COUNT),0) OP_DM_COUNT,
1436 nvl(SUM(D.OP_BR_SUM),0) OP_BR_SUM,
1437 nvl(SUM(D.OP_BR_COUNT),0) OP_BR_COUNT,
1438 nvl(SUM(D.UNRESOLVED_CASH_VALUE),0) UNRESOLVED_CASH_VALUE,
1439 nvl(SUM(D.UNRESOLVED_CASH_COUNT),0) UNRESOLVED_CASH_COUNT,
1440 nvl(SUM(D.PAST_DUE_INV_VALUE),0) PAST_DUE_INV_VALUE,
1441 nvl(SUM(D.PAST_DUE_INV_COUNT),0) PAST_DUE_INV_COUNT,
1442 nvl(SUM(D.INV_AMT_IN_DISPUTE),0) INV_AMT_IN_DISPUTE,
1443 nvl(SUM(D.INV_DISPUTE_COUNT),0) INV_DISPUTE_COUNT,
1444 nvl(SUM(D.BEST_CURRENT_RECEIVABLES),0) BEST_CURRENT_RECEIVABLES,
1445 nvl(SUM(D.RECEIPT_AT_RISK_AMT),0) RECEIPT_AT_RISK_AMT,
1446 nvl(SUM(D.LAST_RECEIPT_AMOUNT),0) LAST_RECEIPT_AMOUNT,
1447 MAX(D.LAST_RECEIPT_DATE) LAST_RECEIPT_DATE,
1448 nvl(MAX(D.LAST_RECEIPT_NUMBER),0) LAST_RECEIPT_NUMBER,
1449 nvl(SUM(D.PENDING_ADJ_AMT),0) PENDING_ADJ_AMT
1450 FROM (
1451 SELECT C.CUSTOMER_ID,
1452 nvl(C.CUSTOMER_SITE_USE_ID,-99) CUSTOMER_SITE_USE_ID,
1453 C.INVOICE_CURRENCY_CODE CURRENCY_CODE,
1454 C.ORG_ID,
1455 SUM(DECODE(CLASS,'INV', C.AMOUNT_DUE_REMAINING,0)) OP_INV_SUM,
1456 COUNT(DECODE(CLASS,'INV', DECODE(C.STATUS,'OP',
1457 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_INV_COUNT,
1458 SUM(DECODE(CLASS,'CM', C.AMOUNT_DUE_REMAINING,0) ) OP_CM_SUM,
1459 COUNT(DECODE(CLASS,'CM', DECODE(C.STATUS,'OP',
1460 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_CM_COUNT,
1461 SUM(DECODE(CLASS,'CB', C.AMOUNT_DUE_REMAINING,0)) OP_CB_SUM,
1462 COUNT(DECODE(CLASS,'CB',DECODE(C.STATUS, 'OP',
1463 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_CB_COUNT,
1464 SUM(DECODE(C.CLASS,'DEP', C.AMOUNT_DUE_REMAINING) ) OP_DEP_SUM,
1465 COUNT(DECODE(C.CLASS,'DEP', DECODE(C.STATUS ,'OP',
1466 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_DEP_COUNT,
1467 SUM(DECODE(C.CLASS,'DM', C.AMOUNT_DUE_REMAINING ,0)) OP_DM_SUM,
1468 COUNT(DECODE(C.CLASS,'DM', DECODE(C.STATUS, 'OP',
1469 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_DM_COUNT,
1470 SUM(DECODE(C.CLASS,'BR', C.AMOUNT_DUE_REMAINING, NULL)) OP_BR_SUM,
1471 COUNT(DECODE(C.CLASS,'BR', DECODE(C.STATUS, 'OP',
1472 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) OP_BR_COUNT,
1473 SUM(DECODE(C.CLASS,'PMT', C.AMOUNT_DUE_REMAINING * -1, NULL)) UNRESOLVED_CASH_VALUE,
1474 COUNT(DECODE(C.CLASS,'PMT', DECODE(C.STATUS, 'OP',
1475 C.PAYMENT_SCHEDULE_ID,NULL),NULL)) UNRESOLVED_CASH_COUNT,
1476 SUM(DECODE(CLASS,'INV',DECODE(C.STATUS, 'OP',
1477 DECODE(SIGN(TRUNC(SYSDATE) -
1478 TRUNC(NVL(C.DUE_DATE, SYSDATE))),1,
1479 (C.AMOUNT_DUE_ORIGINAL
1480 - NVL(C.AMOUNT_APPLIED,0)
1481 + NVL(C.AMOUNT_ADJUSTED,0)
1482 + NVL(C.AMOUNT_CREDITED,0)),
1483 0),0),0)) PAST_DUE_INV_VALUE,
1484 COUNT(DECODE(C.CLASS,'INV',DECODE(C.STATUS, 'OP',
1485 DECODE(SIGN(TRUNC(SYSDATE) -
1486 TRUNC(NVL(C.DUE_DATE, SYSDATE))),1,
1487 C.PAYMENT_SCHEDULE_ID,
1488 NULL),NULL),NULL)) PAST_DUE_INV_COUNT,
1489 SUM(DECODE(CLASS,'INV',C.AMOUNT_IN_DISPUTE,0)) INV_AMT_IN_DISPUTE,
1490 COUNT(DECODE(C.CLASS,'INV',DECODE(C.AMOUNT_IN_DISPUTE,
1491 NULL,NULL,0,NULL,C.PAYMENT_SCHEDULE_ID),
1492 NULL)) INV_DISPUTE_COUNT,
1493 SUM(DECODE(C.CLASS,
1494 'INV', 1,
1495 'DM', 1,
1496 'CB', 1,
1497 'DEP', 1,
1498 'BR', 1,
1499 0)
1500 * DECODE(SIGN(C.DUE_DATE-SYSDATE),
1501 -1,0,C.AMOUNT_DUE_REMAINING ))
1502 BEST_CURRENT_RECEIVABLES,
1503 0 RECEIPT_AT_RISK_AMT ,
1504 0 LAST_RECEIPT_AMOUNT,
1505 TO_DATE(NULL) LAST_RECEIPT_DATE,
1506 NULL LAST_RECEIPT_NUMBER,
1507 SUM(C.AMOUNT_ADJUSTED_PENDING) PENDING_ADJ_AMT
1508 FROM AR_PAYMENT_SCHEDULES_ALL C
1509 WHERE c.customer_id = p_cust_id(i)
1510 GROUP BY C.CUSTOMER_ID,
1511 C.CUSTOMER_SITE_USE_ID,
1512 C.INVOICE_CURRENCY_CODE ,
1513 C.ORG_ID
1514 UNION ALL
1515 SELECT /*+ LEADING a1 INDEX (B ar_cash_receipts_u1) */
1516 A1.CUSTOMER_ID,
1517 A1.CUSTOMER_SITE_USE_ID,
1518 A1.CURRENCY,
1519 A1.ORG_ID ,
1520 0 OP_INV_SUM,
1521 0 OP_INV_COUNT,
1522 0 OP_CM_SUM,
1523 0 OP_CM_COUNT,
1524 0 OP_CB_SUM,
1525 0 OP_CB_COUNT,
1526 0 OP_DEP_SUM,
1527 0 OP_DEP_COUNT,
1528 0 OP_DM_SUM,
1529 0 OP_DM_COUNT,
1530 0 OP_BR_SUM,
1531 0 OP_BR_COUNT,
1532 0 UNRESOLVED_CASH_VALUE,
1533 0 UNRESOLVED_CASH_COUNT,
1534 0 PAST_DUE_INV_VALUE,
1535 0 PAST_DUE_INV_COUNT,
1536 0 INV_AMT_IN_DISPUTE,
1537 0 INV_DISPUTE_COUNT,
1538 0 BEST_CURRENT_RECEIVABLES_ADO,
1539 0 RECEIPT_AT_RISK_AMT,
1540 B.AMOUNT LAST_RECEIPT_AMOUNT,
1541 B.RECEIPT_DATE LAST_RECEIPT_DATE,
1542 B.RECEIPT_NUMBER LAST_RECEIPT_NUMBER,
1543 0 PENDING_ADJ_AMT
1544 FROM (
1545 select /*+ INDEX (cr ar_cash_receipts_n2) */
1546 cr.pay_from_customer customer_id,
1547 nvl(cr.customer_site_use_id, -99) customer_site_use_id,
1548 cr.currency_code currency,
1549 cr.org_id,
1550 to_number(substr(max(
1551 to_char(cr.receipt_date, 'YYYYMMDD') ||
1552 ltrim(to_char(cr.cash_receipt_id, '0999999999999999999999'))),9)) last_cash_receipt_id
1553 from ar_cash_receipts_all cr
1554 where NVL(cr.confirmed_flag, 'Y') = 'Y'
1555 and cr.reversal_date is null
1556 and cr.pay_from_customer = p_cust_id(i)
1557 and cr.type = 'CASH'
1558 group by pay_from_customer, customer_site_use_id, currency_code, org_id) a1,
1559 AR_CASH_RECEIPTS_ALL B
1560 WHERE a1.LAST_CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
1561 UNION ALL
1562 SELECT /*+ LEADING(cr) INDEX(cr,AR_CASH_RECEIPTS_N2) */
1563 CR.PAY_FROM_CUSTOMER CUSTOMER_ID,
1564 NVL(CR.CUSTOMER_SITE_USE_ID,-99) CUSTOMER_SITE_USE_ID,
1565 CR.CURRENCY_CODE CURRENCY_CODE,
1566 CR.ORG_ID ORG_ID,
1567 0 OP_INV_SUM,
1568 0 OP_INV_COUNT,
1569 0 OP_CM_SUM,
1570 0 OP_CM_COUNT,
1571 0 OP_CB_SUM,
1572 0 OP_CB_COUNT,
1573 0 OP_DEP_SUM,
1574 0 OP_DEP_COUNT,
1575 0 OP_DM_SUM,
1576 0 OP_DM_COUNT,
1577 0 OP_BR_SUM,
1578 0 OP_BR_COUNT,
1579 0 UNRESOLVED_CASH_VALUE,
1580 0 UNRESOLVED_CASH_COUNT,
1581 0 PAST_DUE_INV_VALUE,
1582 0 PAST_DUE_INV_COUNT,
1583 0 INV_AMT_IN_DISPUTE,
1584 0 INV_DISPUTE_COUNT,
1585 0 BEST_CURRENT_RECEIVABLES_ADO,
1586 SUM(DECODE(RAP.APPLIED_PAYMENT_SCHEDULE_ID, -2, NULL, CRH.AMOUNT))
1587 RECEIPT_AT_RISK_AMT,
1588 0 LAST_RECEIPT_AMOUNT,
1589 TO_DATE(NULL) LAST_RECEIPT_DATE,
1590 NULL LAST_RECEIPT_NUMBER,
1591 0 PENDING_ADJ_AMT
1592 FROM AR_CASH_RECEIPTS_ALL CR,
1593 AR_CASH_RECEIPT_HISTORY_ALL CRH,
1594 AR_RECEIVABLE_APPLICATIONS_ALL RAP
1595 WHERE NVL(CR.CONFIRMED_FLAG, 'Y') = 'Y'
1596 AND CR.REVERSAL_DATE IS NULL
1597 AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
1598 AND CR.PAY_FROM_CUSTOMER = p_cust_id(i)
1599 AND CRH.CURRENT_RECORD_FLAG = 'Y'
1600 AND CRH.STATUS NOT IN (DECODE (CRH.FACTOR_FLAG, 'Y', 'RISK_ELIMINATED',
1601 'N', 'CLEARED'), 'REVERSED')
1602 AND RAP.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
1603 AND RAP.APPLIED_PAYMENT_SCHEDULE_ID(+) = -2
1604 GROUP BY CR.PAY_FROM_CUSTOMER,NVL(CR.CUSTOMER_SITE_USE_ID,-99),
1605 CR.ORG_ID,CR.CURRENCY_CODE
1606 ) D
1607 GROUP BY D.CUSTOMER_ID,D.CUSTOMER_SITE_USE_ID,D.CURRENCY_CODE,D.ORG_ID);
1608
1609 IF PG_DEBUG in ('Y','C')
1610 THEN
1611 arp_standard.debug('ar_trx_summary_pkg.load_trx_bal_summary()-');
1612 END IF;
1613
1614 END load_trx_bal_summary;
1615
1616 PROCEDURE load_trx_summary(p_cust_id IN l_cust_id_type)
1617 IS
1618 BEGIN
1619 IF PG_DEBUG in ('Y','C')
1620 THEN
1621 arp_standard.debug('ar_trx_summary_pkg.load_trx_summary()+');
1622 END IF;
1623
1624 /* Dev Note: I think I can further simplify this code .. particularly
1625 the logic for:
1626 COUNT_OF_INV_INST_PAID_LATE
1627 COUNT_OF_TOT_INV_INST_PAID
1628 INV_PAID_AMOUNT */
1629
1630 FORALL i IN 1..p_cust_id.COUNT
1631 INSERT into ar_trx_summary
1632 (CUST_ACCOUNT_ID,
1633 SITE_USE_ID,
1634 CURRENCY,
1635 ORG_ID,
1636 AS_OF_DATE,
1637 last_update_date,
1638 last_updated_by,
1639 creation_date,
1640 created_by,
1641 last_update_login,
1642 TOTAL_INVOICES_VALUE,
1643 TOTAL_INVOICES_COUNT,
1644 TOTAL_CREDIT_MEMOS_VALUE ,
1645 TOTAL_CREDIT_MEMOS_COUNT,
1646 TOTAL_CHARGEBACK_VALUE,
1647 TOTAL_CHARGEBACK_COUNT,
1648 TOTAL_DEPOSITS_VALUE,
1649 TOTAL_DEPOSITS_COUNT,
1650 TOTAL_DEBIT_MEMOS_VALUE,
1651 TOTAL_DEBIT_MEMOS_COUNT,
1652 TOTAL_BILLS_RECEIVABLES_VALUE,
1653 TOTAL_BILLS_RECEIVABLES_COUNT,
1654 TOTAL_CASH_RECEIPTS_VALUE,
1655 TOTAL_CASH_RECEIPTS_COUNT,
1656 COUNT_OF_DISC_INV_INST,
1657 DAYS_CREDIT_GRANTED_SUM,
1658 COUNT_OF_INV_INST_PAID_LATE,
1659 COUNT_OF_TOT_INV_INST_PAID,
1660 INV_PAID_AMOUNT,
1661 INV_INST_PMT_DAYS_SUM,
1662 NSF_STOP_PAYMENT_COUNT,
1663 NSF_STOP_PAYMENT_AMOUNT,
1664 SUM_APP_AMT,
1665 TOTAL_EARNED_DISC_VALUE,
1666 TOTAL_EARNED_DISC_COUNT,
1667 TOTAL_UNEARNED_DISC_VALUE,
1668 TOTAL_UNEARNED_DISC_COUNT,
1669 SUM_APP_AMT_DAYS_LATE,
1670 TOTAL_ADJUSTMENTS_VALUE,
1671 TOTAL_ADJUSTMENTS_COUNT)
1672 ( select D.customer_id,
1673 D.customer_site_use_id,
1674 D.currency_code,
1675 D.org_id,
1676 D.as_of_date,
1677 sysdate,
1678 -2003,
1679 sysdate,
1680 -2003,
1681 -2003,
1682 sum(decode(D.TOT_INV_SUM,0,null,D.TOT_INV_SUM)) TOT_INV_SUM,
1683 sum(decode(D.TOT_INV_COUNT,0,null,D.TOT_INV_COUNT)) TOT_INV_COUNT,
1684 SUM(decode(D.TOT_CM_SUM,0,null,D.TOT_CM_SUM)) TOT_CM_SUM,
1685 SUM(decode(D.TOT_CM_COUNT,0,null,D.TOT_CM_COUNT)) TOT_CM_COUNT,
1686 sum(decode(D.TOT_CB_SUM,0,null,D.TOT_CB_SUM)) TOT_CB_SUM,
1687 SUM(decode(D.TOT_CB_COUNT,0,null,D.TOT_CB_COUNT)) TOT_CB_COUNT,
1688 SUM(decode(D.TOT_DEP_SUM,0,null,D.TOT_DEP_SUM)) TOT_DEP_SUM,
1689 SUM(decode(D.TOT_DEP_COUNT,0,null,D.TOT_DEP_COUNT)) TOT_DEP_COUNT,
1690 SUM(decode(D.TOT_DM_SUM,0,null,D.TOT_DM_SUM)) TOT_DM_SUM,
1691 SUM(decode(D.TOT_DM_COUNT,0,null,D.TOT_DM_COUNT)) TOT_DM_COUNT,
1692 SUM(decode(D.TOT_BR_SUM,0,null,D.TOT_BR_SUM)) TOT_BR_SUM,
1693 SUM(decode(D.TOT_BR_COUNT,0,null,D.TOT_BR_COUNT)) TOT_BR_COUNT,
1694 SUM(decode(D.TOT_PMT_SUM,0,null,D.TOT_PMT_SUM)) TOT_PMT_SUM,
1695 SUM(decode(D.TOT_PMT_COUNT,0,null,D.TOT_PMT_COUNT)) TOT_PMT_COUNT,
1696 SUM(decode(D.disc_inv_inst_count,0,null,D.disc_inv_inst_count)) disc_inv_inst_count,
1697 SUM(decode(D.days_credit_granted_sum,0,null,D.days_credit_granted_sum)) days_credit_granted_sum,
1698 SUM(decode(D.COUNT_OF_INV_INST_PAID_LATE,0,null,D.COUNT_OF_INV_INST_PAID_LATE)) COUNT_OF_INV_INST_PAID_LATE,
1699 SUM(decode(D.COUNT_OF_TOT_INV_INST_PAID,0,null,D.COUNT_OF_TOT_INV_INST_PAID)) COUNT_OF_TOT_INV_INST_PAID,
1700 SUM(decode(D.INV_PAID_AMOUNT,0,null,D.INV_PAID_AMOUNT)) INV_PAID_AMOUNT,
1701 SUM(decode(D.inv_inst_pmt_days_sum,0,null,D.inv_inst_pmt_days_sum)) inv_inst_pmt_days_sum,
1702 sum(decode(D.NSF_STOP_PAYMENT_COUNT,0,null,D.NSF_STOP_PAYMENT_COUNT)) NSF_STOP_PAYMENT_COUNT,
1703 sum(decode(D.NSF_STOP_PAYMENT_AMOUNT,0,null,D.NSF_STOP_PAYMENT_AMOUNT)) NSF_STOP_PAYMENT_AMOUNT,
1704 sum(decode(D.sum_amt_applied,0,null,D.sum_amt_applied)) sum_amt_applied,
1705 sum(decode(D.edisc_taken,0,null,D.edisc_taken)) edisc_taken,
1706 sum(decode(D.edisc_taken,0,null,D.edisc_count)) edisc_count,
1707 sum(decode(D.unedisc_taken,0,null,D.unedisc_taken)) unedisc_taken,
1708 sum(decode(D.unedisc_taken,0,null,D.unedisc_count)) unedisc_count,
1709 sum(decode(D.app_amt_days_late,0,null,D.app_amt_days_late)) app_amt_days_late,
1710 sum(decode(D.adj_amount,0,null,D.adj_amount)) adj_amount,
1711 sum(decode(D.adj_count,0,null,D.adj_count)) adj_count
1712 from ( select C.customer_id,
1713 C.customer_site_use_id,
1714 C.currency_code,
1715 C.org_id,
1716 C.trx_date as_of_date,
1717 sum(DECODE(C.class,'INV',C.amount_due_original,0 )) TOT_INV_SUM,
1718 count(decode(C.class,'INV',C.payment_schedule_id,null)) TOT_INV_COUNT,
1719 sum(DECODE(C.class,'CM',C.amount_due_original,0 )) TOT_CM_SUM,
1720 count(decode(C.class,'CM',C.payment_schedule_id,null)) TOT_CM_COUNT,
1721 sum(DECODE(C.class,'CB',C.amount_due_original,0 )) TOT_CB_SUM,
1722 count(decode(C.class,'CB',C.payment_schedule_id,null)) TOT_CB_COUNT,
1723 sum(DECODE(C.class,'DEP',C.amount_due_original,0 )) TOT_DEP_SUM,
1724 count(decode(C.class,'DEP',C.payment_schedule_id,null)) TOT_DEP_COUNT,
1725 sum(DECODE(C.class,'DM',C.amount_due_original,0 )) TOT_DM_SUM,
1726 count(decode(C.class,'DM',C.payment_schedule_id,null)) TOT_DM_COUNT,
1727 sum(DECODE(C.class,'BR',C.amount_due_original,0)) TOT_BR_SUM,
1728 count(decode(C.class,'BR',C.payment_schedule_id,null)) TOT_BR_COUNT,
1729 sum(DECODE(C.class,'PMT',C.amount_due_original * -1 ,0 )) TOT_PMT_SUM,
1730 count(decode(C.class,'PMT',C.payment_schedule_id,null)) TOT_PMT_COUNT,
1731 sum(DECODE(C.class, 'INV', DECODE((nvl(C.edisc_taken,0) +
1732 nvl(C.unedisc_taken,0)), 0, 0, 1),0)) DISC_INV_INST_COUNT,
1733 sum(decode(C.class,'INV',((C.due_date - C.trx_date)*(nvl(C.amount_due_original,0)+
1734 nvl(C.ADJ_AMOUNT,0))),null)) DAYS_CREDIT_GRANTED_SUM,
1735 sum(decode(C.class,'INV',
1736 DECODE(sign(NVL(C.AMOUNT_APPLIED,0)),0,null,
1737 DECODE(SIGN((C.AMOUNT_DUE_ORIGINAL
1738 - NVL(C.AMOUNT_APPLIED,0)
1739 - nvl(C.edisc_taken,0)
1740 - nvl(C.unedisc_taken,0)
1741 + NVL(C.ADJ_AMOUNT,0))),SIGN(C.AMOUNT_DUE_ORIGINAL),
1742 null,
1743 decode(sign(C.due_date - C.actual_date_closed),
1744 -1, 1,null))),null)) COUNT_OF_INV_INST_PAID_LATE,
1745 sum(decode(C.class,'INV',
1746 DECODE(sign(NVL(C.AMOUNT_APPLIED,0)),0,null,
1747 DECODE(SIGN((C.AMOUNT_DUE_ORIGINAL
1748 - NVL(C.AMOUNT_APPLIED,0)
1749 - nvl(C.edisc_taken,0)
1750 - nvl(C.unedisc_taken,0)
1751 + NVL(C.ADJ_AMOUNT,0))),SIGN(C.AMOUNT_DUE_ORIGINAL)
1752 ,null,
1753 1)),null)) COUNT_OF_TOT_INV_INST_PAID,
1754 sum(decode(C.class,'INV',DECODE(SIGN((C.AMOUNT_DUE_ORIGINAL
1755 - NVL(C.AMOUNT_APPLIED,0)
1756 - nvl(C.edisc_taken,0)
1757 - nvl(C.unedisc_taken,0)
1758 + NVL(C.ADJ_AMOUNT,0))),SIGN(C.AMOUNT_DUE_ORIGINAL),
1759 null,nvl(C.amount_applied,0)),null)) INV_PAID_AMOUNT,
1760 sum(decode(C.class,'INV',1,null)) COUNT_OF_TOT_INV_INST,
1761 0 inv_inst_pmt_days_sum,
1762 0 NSF_STOP_PAYMENT_COUNT,
1763 0 NSF_STOP_PAYMENT_AMOUNT,
1764 0 sum_amt_applied,
1765 0 edisc_taken,
1766 0 edisc_count,
1767 0 unedisc_taken,
1768 0 unedisc_count,
1769 0 app_amt_days_late,
1770 0 ADJ_AMOUNT,
1771 0 ADJ_COUNT
1772 FROM (
1773 SELECT A.CUSTOMER_ID,
1774 A.CUSTOMER_SITE_USE_ID,
1775 A.CURRENCY_CODE,
1776 A.ORG_ID ,
1777 A.CLASS,
1778 A.DUE_DATE,
1779 A.TRX_DATE,
1780 A.actual_date_closed,
1781 A.PAYMENT_SCHEDULE_ID,
1782 A.AMOUNT_DUE_ORIGINAL,
1783 A.AMOUNT_IN_DISPUTE,
1784 A.AMOUNT_APPLIED,
1785 A.edisc_taken,
1786 A.unedisc_taken,
1787 SUM(ADJ.amount) adj_amount
1788 FROM (
1789 SELECT PS.CUSTOMER_ID,
1790 NVL(PS.CUSTOMER_SITE_USE_ID,-99) CUSTOMER_SITE_USE_ID,
1791 PS.INVOICE_CURRENCY_CODE CURRENCY_CODE,
1792 PS.ORG_ID,
1793 PS.CLASS,
1794 PS.DUE_DATE DUE_DATE,
1795 PS.TRX_DATE,
1796 PS.actual_date_closed,
1797 PS.PAYMENT_SCHEDULE_ID,
1798 PS.AMOUNT_DUE_ORIGINAL,
1799 PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE,
1800 SUM( RA.AMOUNT_APPLIED) AMOUNT_APPLIED,
1801 sum(decode(ps.class, 'INV',
1802 decode(ra.earned_discount_taken,0,
1803 null,ra.earned_discount_taken), null)) edisc_taken,
1804 sum(decode(ps.class, 'INV',
1805 decode(ra.unearned_discount_taken,0,
1806 null,ra.unearned_discount_taken), null)) unedisc_taken
1807 FROM AR_PAYMENT_SCHEDULES_all ps,
1808 AR_RECEIVABLE_APPLICATIONS_ALL RA
1809 WHERE RA.APPLIED_PAYMENT_SCHEDULE_ID(+) = PS.PAYMENT_SCHEDULE_ID
1810 AND RA.DISPLAY(+) = 'Y'
1811 AND RA.STATUS(+) = 'APP'
1812 AND PS.CUSTOMER_ID = p_cust_id(i)
1813 AND RA.APPLY_DATE(+) >= add_months(sysdate, -24)
1814 AND PS.TRX_DATE >= add_months(sysdate, -24)
1815 GROUP BY PS.CUSTOMER_ID, NVL(PS.CUSTOMER_SITE_USE_ID,-99),
1816 PS.INVOICE_CURRENCY_CODE, PS.ORG_ID,
1817 PS.CLASS, PS.TRX_DATE, PS.DUE_DATE,
1818 PS.AMOUNT_DUE_ORIGINAL,
1819 PS.amount_in_dispute,
1820 ps.actual_date_closed, PS.PAYMENT_SCHEDULE_ID
1821 ) A,
1822 AR_ADJUSTMENTS_ALL ADJ
1823 WHERE A.PAYMENT_SCHEDULE_ID = ADJ.PAYMENT_SCHEDULE_ID(+)
1824 AND ADJ.STATUS(+) = 'A'
1825 GROUP BY A.CUSTOMER_ID, A.CUSTOMER_SITE_USE_ID,
1826 A.CURRENCY_CODE, A.ORG_ID,
1827 A.CLASS, A.TRX_DATE,A.DUE_DATE,
1828 A.AMOUNT_DUE_ORIGINAL, A.AMOUNT_IN_DISPUTE,
1829 A.actual_date_closed,A.AMOUNT_APPLIED,
1830 A.edisc_taken,A.unedisc_taken,
1831 A.PAYMENT_SCHEDULE_ID
1832 ) C
1833 group by C.customer_id,
1834 C.customer_site_use_id,
1835 C.currency_code,
1836 C.org_id,
1837 C.trx_date
1838 UNION
1839 select cr.pay_from_customer customer_id,
1840 nvl(cr.customer_site_use_id,-99) customer_site_use_id,
1841 cr.currency_code invoice_currency_code,
1842 cr.org_id,
1843 cr.reversal_date as_of_date,
1844 0 TOT_INV_SUM,
1845 0 TOT_INV_COUNT,
1846 0 TOT_CM_SUM,
1847 0 TOT_CM_COUNT,
1848 0 TOT_CB_SUM,
1849 0 TOT_CB_COUNT,
1850 0 TOT_DEP_SUM,
1851 0 TOT_DEP_COUNT,
1852 0 TOT_DM_SUM,
1853 0 TOT_DM_COUNT,
1854 0 TOT_BR_SUM,
1855 0 TOT_BR_COUNT,
1856 0 TOT_PMT_SUM,
1857 0 TOT_PMT_COUNT,
1858 0 disc_inv_inst_count,
1859 0 days_credit_granted_sum,
1860 0 COUNT_OF_INV_INST_PAID_LATE,
1861 0 COUNT_OF_TOT_INV_INST_PAID,
1862 0 INV_PAID_AMOUNT,
1863 0 COUNT_OF_TOT_INV_INST,
1864 0 inv_inst_pmt_days,
1865 count(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT,
1866 sum(cr.amount) NSF_STOP_PAYMENT_AMOUNT,
1867 0 sum_amt_applied,
1868 0 edisc_taken,
1869 0 edisc_count,
1870 0 unedisc_taken,
1871 0 unedisc_count,
1872 0 app_amt_days_late,
1873 0 adj_amount,
1874 0 adj_count
1875 from ar_cash_receipts_all cr,
1876 ar_cash_receipt_history_all crh
1877 where cr.cash_receipt_id = crh.cash_receipt_id
1878 and crh.current_record_flag = 'Y'
1879 and crh.status = 'REVERSED'
1880 and cr.status = 'REV'
1881 and cr.reversal_category = 'NSF'
1882 and cr.reversal_date > add_months(sysdate, -24)
1883 and cr.pay_from_customer = p_cust_id(i)
1884 group by cr.pay_from_customer,
1885 nvl(cr.customer_site_use_id,-99),
1886 cr.currency_code,
1887 cr.org_id,
1888 cr.reversal_date
1889 UNION
1890 select customer_id,
1891 customer_site_use_id,
1892 invoice_currency_code,
1893 org_id,
1894 apply_date as_of_date,
1895 0 TOT_INV_SUM,
1896 0 TOT_INV_COUNT,
1897 0 TOT_CM_SUM,
1898 0 TOT_CM_COUNT,
1899 0 TOT_CB_SUM,
1900 0 TOT_CB_COUNT,
1901 0 TOT_DEP_SUM,
1902 0 TOT_DEP_COUNT,
1903 0 TOT_DM_SUM,
1904 0 TOT_DM_COUNT,
1905 0 TOT_BR_SUM,
1906 0 TOT_BR_COUNT,
1907 0 TOT_PMT_SUM,
1908 0 TOT_PMT_COUNT,
1909 0 disc_inv_inst_count,
1910 0 days_credit_granted_sum,
1911 0 COUNT_OF_INV_INST_PAID_LATE,
1912 0 COUNT_OF_TOT_INV_INST_PAID,
1913 0 INV_PAID_AMOUNT,
1914 0 COUNT_OF_TOT_INV_INST,
1915 sum(decode(inv_inst_pmt_days,0,null,inv_inst_pmt_days)) inv_inst_pmt_days,
1916 0 NSF_STOP_PAYMENT_COUNT,
1917 0 NSF_STOP_PAYMENT_AMOUNT,
1918 sum(decode(sum_amt_applied,0,null,sum_amt_applied)) sum_amt_applied,
1919 sum(decode(edisc_taken,0,null,edisc_taken)) edisc_taken,
1920 sum(decode(edisc_taken,0,null,edisc_count)) edisc_count,
1921 sum(decode(unedisc_taken,0,null,unedisc_taken)) unedisc_taken,
1922 sum(decode(unedisc_taken,0,null,unedisc_count)) unedisc_count,
1923 sum(decode(app_amt_days_late,0,null,app_amt_days_late)) app_amt_days_late,
1924 0 adj_amount,
1925 0 adj_count
1926 from ( select ps.customer_id,
1927 ps.customer_site_use_id,
1928 ps.invoice_currency_code,
1929 ps.org_id,
1930 trunc(ra.apply_date) apply_date,
1931 ra.cash_receipt_id,
1932 ra.applied_payment_schedule_id,
1933 sum(decode(ps.class, 'INV',ra.amount_applied,0)) sum_amt_applied,
1934 sum(decode(ps.class, 'INV',((ra.apply_date - (ps.trx_date + nvl(rt.printing_lead_days,0)))
1935 * (nvl(ra.amount_applied,0))),null)) inv_inst_pmt_days,
1936 sum(decode(ps.class, 'INV', decode(ra.earned_discount_taken,0,null,ra.earned_discount_taken), null)) edisc_taken,
1937 sum(decode(ps.class, 'INV',decode(nvl(ra.earned_discount_taken,0),0,null,1),null)) edisc_count,
1938 sum(decode(ps.class, 'INV', decode(ra.unearned_discount_taken,0,null,ra.unearned_discount_taken), null)) unedisc_taken,
1939 sum(decode(ps.class, 'INV',decode(nvl(ra.unearned_discount_taken,0),0,null,1),null)) unedisc_count,
1940 sum(decode(ps.class, 'INV',
1941 (ra.apply_date - ps.due_date) * ra.amount_applied, null)) app_amt_days_late
1942 from ar_payment_schedules_all ps,
1943 ra_terms_b rt,
1944 ar_receivable_applications_all ra
1945 where ps.payment_schedule_id = ra.applied_payment_schedule_id
1946 and ps.customer_id = p_cust_id(i)
1947 and ps.term_id = rt.term_id(+)
1948 and ra.status = 'APP'
1949 and ra.display = 'Y'
1950 and ra.application_type = 'CASH'
1951 and ra.apply_date >= add_months(sysdate, -24)
1952 group by ps.customer_id,
1953 ps.customer_site_use_id,
1954 ps.invoice_currency_code,
1955 ps.org_id,
1956 trunc(ra.apply_date),
1957 ra.cash_receipt_id,
1958 ra.applied_payment_schedule_id
1959 )
1960 group by customer_id,
1961 customer_site_use_id,
1962 invoice_currency_code,
1963 org_id,
1964 apply_date
1965 UNION
1966 select ps.customer_id,
1967 ps.customer_site_use_id,
1968 ps.invoice_currency_code,
1969 ps.org_id,
1970 adj.apply_date as_of_date,
1971 0 TOT_INV_SUM,
1972 0 TOT_INV_COUNT,
1973 0 TOT_CM_SUM,
1974 0 TOT_CM_COUNT,
1975 0 TOT_CB_SUM,
1976 0 TOT_CB_COUNT,
1977 0 TOT_DEP_SUM,
1978 0 TOT_DEP_COUNT,
1979 0 TOT_DM_SUM,
1980 0 TOT_DM_COUNT,
1981 0 TOT_BR_SUM,
1982 0 TOT_BR_COUNT,
1983 0 TOT_PMT_SUM,
1984 0 TOT_PMT_COUNT,
1985 0 disc_inv_inst_count,
1986 0 days_credit_granted_sum,
1987 0 COUNT_OF_INV_INST_PAID_LATE,
1988 0 COUNT_OF_TOT_INV_INST_PAID,
1989 0 INV_PAID_AMOUNT,
1990 0 COUNT_OF_TOT_INV_INST,
1991 0 inv_inst_pmt_days,
1992 0 NSF_STOP_PAYMENT_COUNT,
1993 0 NSF_STOP_PAYMENT_AMOUNT,
1994 0 sum_amt_applied,
1995 0 edisc_taken,
1996 0 edisc_count,
1997 0 unedisc_taken,
1998 0 unedisc_count,
1999 0 app_amt_days_late,
2000 sum(adj.amount) adj_amount,
2001 count(adjustment_id) adj_count
2002 from ar_payment_schedules_all ps,
2003 ar_adjustments_all adj
2004 where ps.customer_id = p_cust_id(i)
2005 and ps.payment_schedule_id = adj.payment_schedule_id
2006 and adj.receivables_trx_id(+) > 0
2007 and ps.trx_date > add_months(sysdate, -24)
2008 and adj.status = 'A'
2009 and adj.apply_date > add_months(sysdate, -24)
2010 group by ps.customer_id,
2011 ps.customer_site_use_id,
2012 ps.invoice_currency_code,
2013 ps.org_id,
2014 adj.apply_date
2015 ) D
2016 group by D.customer_id,
2017 D.customer_site_use_id,
2018 D.currency_code,
2019 D.org_id,
2020 D.as_of_date);
2021
2022 IF PG_DEBUG in ('Y','C')
2023 THEN
2024 arp_standard.debug('ar_trx_summary_pkg.load_trx_summary()-');
2025 END IF;
2026 END load_trx_summary;
2027
2028 PROCEDURE load_largest_inv_info
2029 IS
2030 v_cursor1 NUMBER;
2031 v_cursor2 NUMBER;
2032 v_BatchSize INTEGER := 1000;
2033 v_NumRows INTEGER;
2034 v_customer_id DBMS_SQL.NUMBER_TABLE;
2035 v_site_use_id DBMS_SQL.NUMBER_TABLE;
2036 v_currency_code DBMS_SQL.VARCHAR2_TABLE;
2037 v_trx_date DBMS_SQL.DATE_TABLE;
2038 v_amount DBMS_SQL.NUMBER_TABLE;
2039 v_cust_trx_id DBMS_SQL.NUMBER_TABLE;
2040 v_return_code INTEGER;
2041 text_select VARCHAR2(4000);
2042 text_update VARCHAR2(4000);
2043
2044 BEGIN
2045 IF PG_DEBUG in ('Y','C')
2046 THEN
2047 arp_standard.debug('ar_trx_summary_pkg.load_largest_inv_info()+');
2048 END IF;
2049
2050 text_select :=
2051 'SELECT customer_id, customer_site_use_id,
2052 invoice_currency_code, trunc(trx_date), amount,customer_trx_id
2053 FROM (
2054 select customer_id, customer_site_use_id,
2055 invoice_currency_code,
2056 trx_date, amount,customer_trx_id,
2057 RANK() OVER (PARTITION BY customer_id,
2058 customer_site_use_id,
2059 invoice_currency_code,
2060 trx_date
2061 ORDER BY amount desc, trx_date desc,
2062 customer_trx_id desc) rank_amount
2063 from ( select ps.customer_id, ps.customer_site_use_id,
2064 ps.invoice_currency_code, ps.customer_trx_id,
2065 ps.trx_date, SUM(ps.amount_due_original) amount
2066 from ar_payment_schedules_all ps,
2067 ar_cust_search_gt gt
2068 where ps.customer_id = gt.customer_id
2069 and ps.class = '||''''||'INV'||''''||
2070 ' and trx_date >= add_months(sysdate, -24)
2071 group by ps.customer_id, ps.customer_site_use_id,
2072 ps.invoice_currency_code, ps.trx_date, ps.customer_trx_id
2073 )
2074 )
2075 WHERE rank_amount = 1';
2076
2077 IF PG_DEBUG in ('Y','C')
2078 THEN
2079 arp_standard.debug(text_select);
2080 END IF;
2081
2082 text_update := 'Update /*+ INDEX(ats AR_TRX_SUMMARY_U1) */ ar_trx_summary ats
2083 set LARGEST_INV_AMOUNT = :amount,
2084 LARGEST_INV_CUST_TRX_ID = :cust_trx_id,
2085 LARGEST_INV_DATE = :trx_date,
2086 LAST_UPDATE_DATE = sysdate,
2087 LAST_UPDATED_BY = FND_GLOBAL.user_id,
2088 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
2089 where cust_account_id = :customer_id
2090 and SITE_USE_ID = :site_use_id
2091 and CURRENCY = :currency_code
2092 and AS_OF_DATE = :trx_date';
2093
2094 v_cursor1 := dbms_sql.open_cursor;
2095 v_cursor2 := dbms_sql.open_cursor;
2096
2097 dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
2098 dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
2099
2100 dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
2101 dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
2102 dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
2103 dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
2104 dbms_sql.define_array(v_cursor1,5,v_amount,v_BatchSize,1);
2105 dbms_sql.define_array(v_cursor1,6,v_cust_trx_id,v_BatchSize,1);
2106
2107 v_return_code := dbms_sql.execute(v_cursor1);
2108
2109 --This is the fetch loop. Each call to FETCH_ROWS will retrive v_BatchSize
2110 --rows of data. The loop is over when FETCH_ROWS returns a value< v_BatchSize.
2111
2112 LOOP
2113
2114 v_customer_id.delete;
2115 v_site_use_id.delete;
2116 v_currency_code.delete;
2117 v_trx_date.delete;
2118 v_cust_trx_id.delete;
2119 v_amount.delete;
2120
2121 v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
2122 DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
2123 DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
2124 DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
2125 DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
2126 DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_amount);
2127 DBMS_SQL.COLUMN_VALUE(v_cursor1,6,v_cust_trx_id);
2128
2129 --The special case of v_NumRows = 0 needs to be checked here. This
2130 --means that the previous fetch returned all the remaining rows and
2131 --therefore we are done with the loop.
2132
2133 if (v_NumRows = 0) then
2134 EXIT;
2135 end if;
2136
2137 --Use BIND_ARRAYS to specify the input variables for the insert.
2138 --only elements 1..V_NumRows will be used.
2139
2140 DBMS_SQL.BIND_ARRAY(v_cursor2,':amount',v_amount);
2141 DBMS_SQL.BIND_ARRAY(v_cursor2,':cust_trx_id',v_cust_trx_id);
2142 DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
2143 DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
2144 DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
2145 DBMS_SQL.BIND_ARRAY(v_cursor2,':trx_date',v_trx_date);
2146
2147 v_return_code := DBMS_SQL.EXECUTE(v_cursor2);
2148
2149 EXIT WHEN v_NumRows < v_BatchSize;
2150
2151 END LOOP;
2152 DBMS_SQL.CLOSE_CURSOR(v_cursor1);
2153 DBMS_SQL.CLOSE_CURSOR(v_cursor2);
2154
2155 IF PG_DEBUG in ('Y','C')
2156 THEN
2157 arp_standard.debug('ar_trx_summary_pkg.load_largest_inv_info()-');
2158 END IF;
2159 END load_largest_inv_info;
2160
2161 PROCEDURE load_high_watermark IS
2162 v_cursor1 NUMBER;
2163 v_cursor2 NUMBER;
2164 v_BatchSize INTEGER := 1000;
2165 v_NumRows INTEGER;
2166 v_customer_id DBMS_SQL.NUMBER_TABLE;
2167 v_site_use_id DBMS_SQL.NUMBER_TABLE;
2168 v_currency_code DBMS_SQL.VARCHAR2_TABLE;
2169 v_trx_date DBMS_SQL.DATE_TABLE;
2170 v_cum_balance DBMS_SQL.NUMBER_TABLE;
2171 v_return_code INTEGER;
2172 text_select VARCHAR2(4000);
2173 text_update VARCHAR2(4000);
2174
2175 BEGIN
2176 IF PG_DEBUG in ('Y','C')
2177 THEN
2178 arp_standard.debug('ar_trx_summary_pkg.load_high_watermark()+');
2179 END IF;
2180
2181 /* 7518998 - Changed first subquery to UNION ALL, forced
2182 ra rows to be CASH, and completely removed CM app subquery */
2183 text_select :=
2184 'with cust_list as
2185 (select /*+ cardinality(g,1) */ customer_id from ar_cust_search_gt g)
2186 select customer_id, customer_site_use_id, invoice_currency_code,
2187 as_of_date , cum_balance
2188 from (
2189 select customer_id, customer_site_use_id, invoice_currency_code,
2190 as_of_date , sum(net_amount) OVER (PARTITION BY customer_id,
2191 customer_site_use_id, invoice_currency_code
2192 ORDER BY customer_id, customer_site_use_id,
2193 invoice_currency_code ROWS UNBOUNDED PRECEDING) cum_balance
2194 from (
2195 select customer_id, customer_site_use_id, invoice_currency_code,
2196 as_of_date , sum(net_amount) net_amount
2197 from
2198 (select ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
2199 ps.trx_date as_of_date, sum(ps.amount_due_original) net_amount
2200 from ar_payment_schedules_all ps
2201 where ps.class in ('||''''||'INV'||''''||','
2202 ||''''||'CM'||''''||','
2203 ||''''||'DM'||''''||','
2204 ||''''||'DEP'||''''||','
2205 ||''''||'BR'||''''||','
2206 ||''''||'CB'||''''||')
2207 and ps.customer_id in (select customer_id from cust_list)
2208 group by ps.customer_id, ps.customer_site_use_id,
2209 ps.invoice_currency_code, ps.trx_date
2210 union all
2211 select ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
2212 ra.apply_date as_of_date,
2213 sum(-ra.amount_applied
2214 -nvl(ra.earned_discount_taken,0)
2215 -nvl(ra.unearned_discount_taken,0)) net_amount
2216 from ar_payment_schedules_all ps,
2217 ar_receivable_applications_all ra
2218 where ps.payment_schedule_id = ra.applied_payment_schedule_id
2219 and ps.customer_id in (select customer_id from cust_list)
2220 and ra.status = '||''''||'APP'||''''||'
2221 and ra.application_type = '||''''||'CASH'||''''||'
2222 and nvl(ra.confirmed_flag,'||''''||'Y'||''''||') = '||''''||'Y'||''''||'
2223 and ps.class in ('||''''||'INV'||''''||','
2224 ||''''||'CM'||''''||','
2225 ||''''||'DM'||''''||','
2226 ||''''||'DEP'||''''||','
2227 ||''''||'BR'||''''||','
2228 ||''''||'CB'||''''||')
2229 group by ps.customer_id, ps.customer_site_use_id,
2230 ps.invoice_currency_code, ra.apply_date
2231 union all
2232 select ps.customer_id, ps.customer_site_use_id, ps.invoice_currency_code,
2233 adj.apply_date as_of_date, sum(adj.amount)
2234 from ar_payment_schedules_all ps,
2235 ar_adjustments_all adj
2236 where ps.payment_schedule_id = adj.payment_schedule_id
2237 and ps.class in ('||''''||'INV'||''''||','
2238 ||''''||'CM'||''''||','
2239 ||''''||'DM'||''''||','
2240 ||''''||'DEP'||''''||','
2241 ||''''||'BR'||''''||','
2242 ||''''||'CB'||''''||')
2243 and adj.status = '||''''||'A'||''''||'
2244 and ps.customer_id in (select customer_id from cust_list)
2245 group by ps.customer_id, ps.customer_site_use_id,
2246 ps.invoice_currency_code, adj.apply_date
2247 )
2248 group by customer_id, customer_site_use_id, invoice_currency_code,
2249 as_of_date
2250 order by customer_id, customer_site_use_id, invoice_currency_code,
2251 as_of_date )
2252 )
2253 where as_of_date > add_months(sysdate , -24)';
2254
2255 IF PG_DEBUG in ('Y','C')
2256 THEN
2257 arp_standard.debug(text_select);
2258 END IF;
2259
2260 text_update :=
2261 'Update /*+ INDEX(ats AR_TRX_SUMMARY_U1) */ ar_trx_summary ats
2262 set OP_BAL_HIGH_WATERMARK = :cum_balance,
2263 OP_BAL_HIGH_WATERMARK_DATE = :as_of_date,
2264 LAST_UPDATE_DATE = sysdate,
2265 LAST_UPDATED_BY = FND_GLOBAL.user_id,
2266 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
2267 where cust_account_id = :customer_id
2268 and SITE_USE_ID = :site_use_id
2269 and CURRENCY = :currency_code
2270 and AS_OF_DATE = :as_of_date';
2271
2272 v_cursor1 := dbms_sql.open_cursor;
2273 v_cursor2 := dbms_sql.open_cursor;
2274
2275 dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
2276 dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
2277
2278 dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
2279 dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
2280 dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
2281 dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
2282 dbms_sql.define_array(v_cursor1,5,v_cum_balance,v_BatchSize,1);
2283
2284 v_return_code := dbms_sql.execute(v_cursor1);
2285
2286 --This is the fetch loop. Each call to FETCH_ROWS will retrive v_BatchSize
2287 --rows of data. The loop is over when FETCH_ROWS returns a value< v_BatchSize.
2288
2289 LOOP
2290
2291 v_customer_id.delete;
2292 v_site_use_id.delete;
2293 v_currency_code.delete;
2294 v_trx_date.delete;
2295 v_cum_balance.delete;
2296
2297 v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
2298 DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
2299 DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
2300 DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
2301 DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
2302 DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_cum_balance);
2303
2304 --The special case of v_NumRows = 0 needs to be checked here. This
2305 --means that the previous fetch returned all the remaining rows and
2306 --therefore we are done with the loop.
2307
2308 if (v_NumRows = 0) then
2309 EXIT;
2310 end if;
2311
2312 --Use BIND_ARRAYS to specify the input variables for the insert.
2313 --only elements 1..V_NumRows will be used.
2314
2315 DBMS_SQL.BIND_ARRAY(v_cursor2,':cum_balance',v_cum_balance);
2316 DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
2317 DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
2318 DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
2319 DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
2320 DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
2321
2322 v_return_code := DBMS_SQL.EXECUTE(v_cursor2);
2323
2324 EXIT WHEN v_NumRows < v_BatchSize;
2325
2326 END LOOP;
2327
2328 DBMS_SQL.CLOSE_CURSOR(v_cursor1);
2329 DBMS_SQL.CLOSE_CURSOR(v_cursor2);
2330
2331 IF PG_DEBUG in ('Y','C')
2332 THEN
2333 arp_standard.debug('ar_trx_summary_pkg.load_high_watermark()-');
2334 END IF;
2335
2336 END load_high_watermark;
2337
2338 PROCEDURE refresh_summary_data(
2339 errbuf IN OUT NOCOPY VARCHAR2,
2340 retcode IN OUT NOCOPY VARCHAR2,
2341 p_max_workers IN NUMBER,
2342 p_worker_number IN NUMBER,
2343 p_skip_secondary_processes IN VARCHAR2 DEFAULT NULL,
2344 p_fast_delete IN VARCHAR2 DEFAULT 'Y'
2345 ) IS
2346
2347 l_worker_number NUMBER;
2348 l_max_workers NUMBER;
2349 l_po_value VARCHAR2(10);
2350 l_return BOOLEAN;
2351 BEGIN
2352 IF PG_DEBUG in ('Y','C')
2353 THEN
2354 arp_standard.debug('ar_trx_summary_pkg.refresh_summary_data()+');
2355 arp_standard.debug(' p_skip_secondary_processes = ' || p_skip_secondary_processes);
2356 END IF;
2357
2358 /* Check profile, if set to N, then terminate program */
2359 l_po_value := fnd_profile.value('AR_CMGT_ALLOW_SUMMARY_TABLE_REFRESH');
2360
2361 IF nvl(l_po_value,'N') = 'N'
2362 THEN
2363 fnd_file.put_line(fnd_file.log,
2364 'The profile AR_CMGT_ALLOW_SUMMARY_TABLE_REFRESH = N');
2365 IF PG_DEBUG in ('Y','C')
2366 THEN
2367 arp_standard.debug(' AR_CMGT_ALLOW_SUMMARY_TABLE_REFRESH = N');
2368 arp_standard.debug('ar_trx_summary_pkg.refresh_summary_data()-');
2369 END IF;
2370
2371 retcode := SUCCESS;
2372 RETURN;
2373 END IF;
2374
2375 /* Initialize worker settings */
2376 IF p_max_workers IS NULL
2377 THEN
2378 l_max_workers := 1;
2379 ELSE
2380 l_max_workers := p_max_workers;
2381 END IF;
2382
2383 IF p_worker_number IS NULL
2384 THEN
2385 l_worker_number := 0; -- zero is the master
2386 ELSIF p_worker_number > p_max_workers - 1
2387 THEN
2388 return;
2389 ELSE
2390 l_worker_number := p_worker_number;
2391 END IF;
2392
2393 IF PG_DEBUG in ('Y','C')
2394 THEN
2395 arp_standard.debug(' l_max_workers = ' || l_max_workers);
2396 arp_standard.debug(' l_worker_number = ' || l_worker_number);
2397 END IF;
2398
2399 /* so now we should have l_max_workers as some integer
2400 and l_worker_number is zero for master and 1 through l_max_workers -1
2401 (0 through 3)
2402
2403 The only differences between zero and the other workers is that zero will
2404 truncate the tables, submit the others, and submit held events.
2405 */
2406
2407 /* Clear the tables and submit the others */
2408 IF l_worker_number = 0
2409 THEN
2410 /* Dump the summary tables */
2411 IF p_fast_delete = 'Y'
2412 THEN
2413 clear_summary_tables('A'); -- clear both tables
2414 END IF;
2415
2416 /* Now submit the other workers */
2417 IF l_max_workers > 1
2418 THEN
2419 submit_child_workers(l_max_workers,p_skip_secondary_processes,
2420 p_fast_delete);
2421 END IF;
2422 END IF;
2423
2424 /* From this point on, all logic is processed by all workers
2425 and there is no special treatment for worker zero
2426 */
2427
2428 /* block all events until this worker completes */
2429 block_events('BLOCK',FND_GLOBAL.conc_request_id);
2430
2431 /* Collect customers for processing (ALL) */
2432 collect_customers(l_max_workers, l_worker_number,
2433 'ALL', t_cust_id);
2434
2435 /* Handle local delete when p_fast_delete = 'N'
2436 Note that this does not commit changes until the worker
2437 completes */
2438 IF NVL(p_fast_delete,'N') <> 'Y'
2439 THEN
2440 clear_summary_by_customer(t_cust_id);
2441 END IF;
2442
2443 load_trx_bal_summary(t_cust_id);
2444
2445 /* Check if OCM is installed/setup first before
2446 executing trx_summary functions */
2447 IF ar_cmgt_credit_request_api.is_credit_management_installed
2448 THEN
2449 /* Collect customers for processing (ACTIVE) */
2450 collect_customers(l_max_workers, l_worker_number,
2451 'ACTIVE', t_cust_id);
2452
2453 load_trx_summary(t_cust_id);
2454
2455 /* Following two procedures use ar_cust_search_gt content */
2456 /* p_skip_secondary_processes gives us an easy way to determine
2457 which of these processes is consuming the most time. This would
2458 be a simple way to bypass these if the customer was absolutely not
2459 using them */
2460 IF NVL(p_skip_secondary_processes,'NONE') NOT IN ('ALL','LOAD_LARGEST')
2461 THEN
2462 load_largest_inv_info;
2463 END IF;
2464
2465 IF NVL(p_skip_secondary_processes,'NONE') NOT IN ('ALL','HIGH_WATERMARK')
2466 THEN
2467 load_high_watermark;
2468 END IF;
2469 END IF;
2470
2471 /* unblock events for this worker */
2472 block_events('UNBLOCK',FND_GLOBAL.conc_request_id);
2473
2474 /* Need to process held events here.. not sure how yet */
2475 IF l_worker_number = 0
2476 THEN
2477 submit_held_events;
2478 END IF;
2479
2480 /* Set profile back to N */
2481 IF l_po_value = 'Y'
2482 THEN
2483 l_return := fnd_profile.save('AR_CMGT_ALLOW_SUMMARY_TABLE_REFRESH',
2484 'N','APPL',222);
2485 END IF;
2486
2487 /* Final commit of new data */
2488 COMMIT;
2489
2490 IF PG_DEBUG in ('Y','C')
2491 THEN
2492 arp_standard.debug('ar_trx_summary_pkg.refresh_summary_data()-');
2493 END IF;
2494
2495 retcode := SUCCESS;
2496 RETURN;
2497
2498 END refresh_summary_data;
2499
2500 PROCEDURE process_held_events(
2501 errbuf IN OUT NOCOPY VARCHAR2,
2502 retcode IN OUT NOCOPY VARCHAR2) IS
2503
2504 CURSOR get_raised_events IS
2505 SELECT *
2506 FROM ar_sum_ref_event_hist;
2507
2508 l_list WF_PARAMETER_LIST_T;
2509 l_status VARCHAR2(1); -- junk variable
2510 l_industry VARCHAR2(1); -- junk variable
2511 l_schema VARCHAR2(30);
2512 l_count NUMBER := 0;
2513 BEGIN
2514 fnd_file.put_line(fnd_file.log,'arp_trx_summary_pkg.process_held_events()+');
2515
2516 IF PG_DEBUG in ('Y','C')
2517 THEN
2518 arp_standard.debug('ar_trx_summary_pkg.process_held_events()+');
2519 END IF;
2520
2521 /* Process the business events that have been raised running the run of this
2522 concurrent program so far */
2523
2524 FOR l_be_hist_rec in get_raised_events LOOP
2525
2526 -- initialization of object variables
2527 l_list := WF_PARAMETER_LIST_T();
2528
2529 -- add more parameters to the parameters list
2530 IF l_be_hist_rec.customer_trx_id IS NOT NULL
2531 THEN
2532 wf_event.AddParameterToList(p_name => 'CUSTOMER_TRX_ID',
2533 p_value => l_be_hist_rec.customer_trx_id,
2534 p_parameterlist => l_list);
2535 END IF;
2536
2537 IF l_be_hist_rec.payment_schedule_id IS NOT NULL
2538 THEN
2539 wf_event.AddParameterToList(p_name => 'PAYMENT_SCHEDULE_ID',
2540 p_value => l_be_hist_rec.customer_trx_id,
2541 p_parameterlist => l_list);
2542 END IF;
2543
2544 IF l_be_hist_rec.CASH_RECEIPT_ID IS NOT NULL
2545 THEN
2546 wf_event.AddParameterToList(p_name => 'CASH_RECEIPT_ID',
2547 p_value => l_be_hist_rec.CASH_RECEIPT_ID,
2548 p_parameterlist => l_list);
2549 END IF;
2550
2551 IF l_be_hist_rec.RECEIVABLE_APPLICATION_ID IS NOT NULL
2552 THEN
2553 wf_event.AddParameterToList(p_name => 'RECEIVABLE_APPLICATION_ID',
2554 p_value => l_be_hist_rec.RECEIVABLE_APPLICATION_ID,
2555 p_parameterlist => l_list);
2556 END IF;
2557
2558 IF l_be_hist_rec.ADJUSTMENT_ID IS NOT NULL
2559 THEN
2560 wf_event.AddParameterToList(p_name => 'ADJUSTMENT_ID',
2561 p_value => l_be_hist_rec.ADJUSTMENT_ID,
2562 p_parameterlist => l_list);
2563 END IF;
2564
2565 IF l_be_hist_rec.HISTORY_ID IS NOT NULL
2566 THEN
2567
2568 IF l_be_hist_rec.ADJUSTMENT_ID IS NOT NULL
2569 THEN
2570
2571 wf_event.AddParameterToList(p_name => 'APPROVAL_ACTN_HIST_ID',
2572 p_value => l_be_hist_rec.HISTORY_ID,
2573 p_parameterlist => l_list);
2574 ELSE
2575 wf_event.AddParameterToList(p_name => 'HISTORY_ID',
2576 p_value => l_be_hist_rec.HISTORY_ID,
2577 p_parameterlist => l_list);
2578 END IF;
2579 END IF;
2580
2581 IF l_be_hist_rec.REQUEST_ID IS NOT NULL
2582 THEN
2583 wf_event.AddParameterToList(p_name => 'REQUEST_ID',
2584 p_value => l_be_hist_rec.REQUEST_ID,
2585 p_parameterlist => l_list);
2586 END IF;
2587
2588 -- Raise Event
2589 AR_CMGT_EVENT_PKG.raise_event(
2590 p_event_name => l_be_hist_rec.business_event_name,
2591 p_event_key => l_be_hist_rec.event_key,
2592 p_parameters => l_list );
2593
2594 l_list.DELETE;
2595 l_count := l_count + 1;
2596 END LOOP;
2597
2598 fnd_file.put_line(fnd_file.log,' events processed = ' || l_count);
2599
2600 /* Clean out the AR_SUM_REF_EVENT_HIST table */
2601 IF FND_INSTALLATION.get_app_info('AR', l_status, l_industry, l_schema)
2602 THEN
2603 IF PG_DEBUG in ('Y', 'C') THEN
2604 arp_standard.debug('Retrieved schema for AR : ' || l_schema);
2605 END IF;
2606 ELSE
2607 IF PG_DEBUG in ('Y', 'C') THEN
2608 arp_standard.debug('Problem retrieving AR schema name from fnd_installation');
2609 END IF;
2610 arp_standard.debug('EXCEPTION: arp_trx_summary_pkg.process_held_events');
2611 RETURN;
2612 END IF;
2613
2614 /* If schema is set, clear event table */
2615 IF l_schema IS NOT NULL
2616 THEN
2617 /* clear the event holding table as well */
2618 EXECUTE IMMEDIATE 'truncate table ' || l_schema || '.AR_SUM_REF_EVENT_HIST';
2619 END IF;
2620
2621 /* Delete any remaining rows (for refresh) from conc table
2622 This is really just precautionary in that (in theory), no events
2623 should be held at this point. */
2624 DELETE FROM AR_CONC_PROCESS_REQUESTS
2625 WHERE CONCURRENT_PROGRAM_NAME = 'ARSUMREF';
2626
2627 COMMIT;
2628
2629 fnd_file.put_line(fnd_file.log,'arp_trx_summary_pkg.process_held_events()-');
2630
2631 IF PG_DEBUG in ('Y','C')
2632 THEN
2633 arp_standard.debug('ar_trx_summary_pkg.process_held_events()-');
2634 END IF;
2635 END process_held_events;
2636
2637 END AR_TRX_SUMMARY_PKG;