[Home] [Help]
20: CURSOR get_last_payment_info(p_cust_acct_id NUMBER,
21: p_site_use_id NUMBER,
22: p_currency VARCHAR2) IS
23: SELECT last_payment_date,last_payment_number
24: FROM ar_trx_bal_summary
25: WHERE cust_account_id = p_cust_acct_id
26: AND site_use_id = nvl(p_site_use_id,-9999)
27: AND currency = p_currency
28: FOR UPDATE;
75: debug ('org_id ='||l_org_id);
76: END IF;
77:
78:
79: UPDATE ar_trx_bal_summary
80: set OP_CREDIT_MEMOS_VALUE
81: = nvl(OP_CREDIT_MEMOS_VALUE,0)
82: + DECODE(l_trx_class,'CM',
83: nvl(l_trx_amt,0),0)
124: and currency = l_trx_currency_code
125: and org_id = l_org_id;
126:
127: IF l_app_type = 'CASH' THEN
128: UPDATE ar_trx_bal_summary
129: set UNRESOLVED_CASH_VALUE = nvl(UNRESOLVED_CASH_VALUE,0)
130: + nvl(l_unresolved_cash_value,0),
131: UNRESOLVED_CASH_COUNT = nvl(UNRESOLVED_CASH_COUNT,0) -
132: nvl(l_unresolved_cash_count,0),
290: --
291: -- l_tot_inv_amt := 0;
292:
293:
294: UPDATE ar_trx_bal_summary
295: set BEST_CURRENT_RECEIVABLES
296: = nvl(BEST_CURRENT_RECEIVABLES,0)
297: +DECODE(sign(rec.due_date - trunc(sysdate)),-1,0,
298: rec.amount_due_original),
351: and org_id = rec.org_id;
352:
353: IF SQL%NOTFOUND THEN
354: /* bug4403146 : Modified the decode for getting OP_DEBIT_MEMOS_COUNT */
355: INSERT INTO ar_trx_bal_summary
356: (CUST_ACCOUNT_ID,
357: SITE_USE_ID,
358: ORG_ID,
359: CURRENCY,
558: debug ('site_use_id ='||l_site_use_id);
559: debug ('currency ='||l_currency_code);
560: debug ('org_id ='||l_org_id);
561: END IF;
562: UPDATE ar_trx_bal_summary
563: set BEST_CURRENT_RECEIVABLES
564: = nvl(BEST_CURRENT_RECEIVABLES,0)
565: + nvl(l_adj_amount,0),
566: OP_CREDIT_MEMOS_VALUE
605: and org_id = l_org_id;
606:
607: IF sql%NOTFOUND THEN
608:
609: INSERT INTO ar_trx_bal_summary
610: (CUST_ACCOUNT_ID,
611: SITE_USE_ID,
612: ORG_ID,
613: CURRENCY,
815:
816: /*
817: These variables are used to maintain the cumulative totals for the
818: data in application_info cursor, which will be used for updating the
819: ar_trx_bal_summary column.
820: */
821: l_prev_trx_customer_id number;
822: l_prev_trx_site_use_id number;
823: l_prev_trx_currency varchar2(30);
886:
887:
888: FOR i in get_last_payment_info(p_req_id)
889: LOOP
890: UPDATE ar_trx_bal_summary
891: set last_payment_amount = decode(sign(i.last_payment_date-last_payment_date),
892: -1,last_payment_amount,i.last_payment_amount),
893: last_payment_date =decode(sign(i.last_payment_date-last_payment_date),
894: -1,last_payment_date,i.last_payment_date),
902: and org_id = p_org_id
903: and currency = i.rcpt_currency;
904:
905: IF sql%notfound then
906: INSERT into ar_trx_bal_summary
907: (cust_account_id,
908: site_use_id,
909: org_id,
910: currency,
1155:
1156: FOR j in 1..l_ps_tab.COUNT
1157: LOOP
1158: --
1159: -- Update the AR_TRX_BAL_SUMMARY table
1160: --
1161: UPDATE ar_trx_bal_summary
1162: set BEST_CURRENT_RECEIVABLES
1163: = nvl(BEST_CURRENT_RECEIVABLES,0)
1157: LOOP
1158: --
1159: -- Update the AR_TRX_BAL_SUMMARY table
1160: --
1161: UPDATE ar_trx_bal_summary
1162: set BEST_CURRENT_RECEIVABLES
1163: = nvl(BEST_CURRENT_RECEIVABLES,0)
1164: +DECODE(sign(l_ps_tab(j).due_date - sysdate),-1,0,
1165: (l_ps_tab(j).amount_due_original
1184: and org_id = l_org_id;
1185:
1186: IF SQL%NOTFOUND THEN
1187:
1188: INSERT INTO ar_trx_bal_summary
1189: (CUST_ACCOUNT_ID,
1190: SITE_USE_ID,
1191: ORG_ID,
1192: CURRENCY,
1445:
1446: l_tot_inv_amt := 0;
1447:
1448: --
1449: -- Update the AR_TRX_BAL_SUMMARY table
1450: --
1451: UPDATE ar_trx_bal_summary
1452: set BEST_CURRENT_RECEIVABLES
1453: = nvl(BEST_CURRENT_RECEIVABLES,0)
1447:
1448: --
1449: -- Update the AR_TRX_BAL_SUMMARY table
1450: --
1451: UPDATE ar_trx_bal_summary
1452: set BEST_CURRENT_RECEIVABLES
1453: = nvl(BEST_CURRENT_RECEIVABLES,0)
1454: -DECODE(sign(l_history_rec.due_date - sysdate),-1,0,
1455: (l_history_rec.amount_due_original
1728: AND nvl(l_history_rec.amount_in_dispute,0) > 0 THEN
1729: l_inv_dispute_count := -1;
1730: END IF;
1731:
1732: UPDATE ar_trx_bal_summary
1733: set BEST_CURRENT_RECEIVABLES
1734: = nvl(BEST_CURRENT_RECEIVABLES,0)
1735: -DECODE(l_due_date_change,'+',
1736: (l_history_rec.amount_due_original
1957:
1958: --this is a credit memo so only one ps exists
1959:
1960: --
1961: -- Update the AR_TRX_BAL_SUMMARY table
1962: --
1963: UPDATE ar_trx_bal_summary
1964: set BEST_CURRENT_RECEIVABLES
1965: = nvl(BEST_CURRENT_RECEIVABLES,0)
1959:
1960: --
1961: -- Update the AR_TRX_BAL_SUMMARY table
1962: --
1963: UPDATE ar_trx_bal_summary
1964: set BEST_CURRENT_RECEIVABLES
1965: = nvl(BEST_CURRENT_RECEIVABLES,0)
1966: + DECODE(sign(l_ps_rec.due_date - sysdate),-1,0,
1967: (l_ps_rec.amount_due_original
2021: and org_id = l_org_id;
2022:
2023: IF SQL%NOTFOUND THEN
2024:
2025: INSERT INTO ar_trx_bal_summary
2026: (CUST_ACCOUNT_ID,
2027: SITE_USE_ID,
2028: ORG_ID,
2029: CURRENCY,
2291: FETCH get_trx_history INTO l_history_rec;
2292: CLOSE get_trx_history ;
2293:
2294: --
2295: -- Update the AR_TRX_BAL_SUMMARY table
2296: --
2297: UPDATE ar_trx_bal_summary
2298: set BEST_CURRENT_RECEIVABLES
2299: = nvl(BEST_CURRENT_RECEIVABLES,0)
2293:
2294: --
2295: -- Update the AR_TRX_BAL_SUMMARY table
2296: --
2297: UPDATE ar_trx_bal_summary
2298: set BEST_CURRENT_RECEIVABLES
2299: = nvl(BEST_CURRENT_RECEIVABLES,0)
2300: - DECODE(sign(l_history_rec.due_date - sysdate),-1,0,
2301: (l_history_rec.amount_due_original
2516:
2517: FOR j in 1..l_ps_tab.COUNT
2518: LOOP
2519: --
2520: -- Update the AR_TRX_BAL_SUMMARY table
2521: --
2522: UPDATE ar_trx_bal_summary
2523: set BEST_CURRENT_RECEIVABLES
2524: = nvl(BEST_CURRENT_RECEIVABLES,0)
2518: LOOP
2519: --
2520: -- Update the AR_TRX_BAL_SUMMARY table
2521: --
2522: UPDATE ar_trx_bal_summary
2523: set BEST_CURRENT_RECEIVABLES
2524: = nvl(BEST_CURRENT_RECEIVABLES,0)
2525: +DECODE(sign(l_ps_tab(j).due_date - sysdate),-1,0,
2526: l_ps_tab(j).amount_due_original),
2537: and org_id = l_org_id;
2538:
2539: IF SQL%NOTFOUND THEN
2540:
2541: INSERT INTO ar_trx_bal_summary
2542: (CUST_ACCOUNT_ID,
2543: SITE_USE_ID,
2544: ORG_ID,
2545: CURRENCY,
2754:
2755: l_tot_inv_amt := 0;
2756:
2757: --
2758: -- Update the AR_TRX_BAL_SUMMARY table
2759: --
2760: UPDATE ar_trx_bal_summary
2761: set BEST_CURRENT_RECEIVABLES
2762: = nvl(BEST_CURRENT_RECEIVABLES,0)
2756:
2757: --
2758: -- Update the AR_TRX_BAL_SUMMARY table
2759: --
2760: UPDATE ar_trx_bal_summary
2761: set BEST_CURRENT_RECEIVABLES
2762: = nvl(BEST_CURRENT_RECEIVABLES,0)
2763: -DECODE(sign(l_history_rec.due_date - sysdate),-1,0,
2764: l_history_rec.amount_due_original),
2956:
2957: FOR j in 1..l_ps_tab.COUNT
2958: LOOP
2959: --
2960: -- Update the AR_TRX_BAL_SUMMARY table
2961: --
2962: UPDATE ar_trx_bal_summary
2963: set BEST_CURRENT_RECEIVABLES
2964: = nvl(BEST_CURRENT_RECEIVABLES,0)
2958: LOOP
2959: --
2960: -- Update the AR_TRX_BAL_SUMMARY table
2961: --
2962: UPDATE ar_trx_bal_summary
2963: set BEST_CURRENT_RECEIVABLES
2964: = nvl(BEST_CURRENT_RECEIVABLES,0)
2965: +DECODE(sign(l_ps_tab(j).due_date - sysdate),-1,0,
2966: l_ps_tab(j).amount_due_original),
2978: and org_id = l_org_id;
2979:
2980: IF SQL%NOTFOUND THEN
2981:
2982: INSERT INTO ar_trx_bal_summary
2983: (CUST_ACCOUNT_ID,
2984: SITE_USE_ID,
2985: ORG_ID,
2986: CURRENCY,
3197:
3198: l_tot_inv_amt := 0;
3199:
3200: --
3201: -- Update the AR_TRX_BAL_SUMMARY table
3202: --
3203: UPDATE ar_trx_bal_summary
3204: set BEST_CURRENT_RECEIVABLES
3205: = nvl(BEST_CURRENT_RECEIVABLES,0)
3199:
3200: --
3201: -- Update the AR_TRX_BAL_SUMMARY table
3202: --
3203: UPDATE ar_trx_bal_summary
3204: set BEST_CURRENT_RECEIVABLES
3205: = nvl(BEST_CURRENT_RECEIVABLES,0)
3206: -DECODE(sign(l_history_rec.due_date - sysdate),-1,0,
3207: l_history_rec.amount_due_original),
3397: IF l_ps_exists THEN
3398: --this is a chargeback so only one ps exists
3399:
3400: --
3401: -- Update the AR_TRX_BAL_SUMMARY table
3402: --
3403:
3404: UPDATE ar_trx_bal_summary
3405: set BEST_CURRENT_RECEIVABLES
3400: --
3401: -- Update the AR_TRX_BAL_SUMMARY table
3402: --
3403:
3404: UPDATE ar_trx_bal_summary
3405: set BEST_CURRENT_RECEIVABLES
3406: = nvl(BEST_CURRENT_RECEIVABLES,0)
3407: +DECODE(sign(l_ps_rec.due_date - sysdate),-1,0,
3408: l_ps_rec.amount_due_original),
3419: and org_id = l_org_id;
3420:
3421: IF SQL%NOTFOUND THEN
3422:
3423: INSERT INTO ar_trx_bal_summary
3424: (CUST_ACCOUNT_ID,
3425: SITE_USE_ID,
3426: ORG_ID,
3427: CURRENCY,
3719: OPEN get_receipt_status (l_cash_receipt_id);
3720: FETCH get_receipt_status INTO l_receipt_status;
3721: CLOSE get_receipt_status;
3722:
3723: --Update ar_trx_bal_summary
3724: UPDATE ar_trx_bal_summary
3725: set unresolved_cash_value = nvl(unresolved_cash_value,0) +
3726: l_receipt_amount,
3727: unresolved_cash_count = nvl(unresolved_cash_count,0) + 1,
3720: FETCH get_receipt_status INTO l_receipt_status;
3721: CLOSE get_receipt_status;
3722:
3723: --Update ar_trx_bal_summary
3724: UPDATE ar_trx_bal_summary
3725: set unresolved_cash_value = nvl(unresolved_cash_value,0) +
3726: l_receipt_amount,
3727: unresolved_cash_count = nvl(unresolved_cash_count,0) + 1,
3728: last_payment_amount = DECODE(sign(l_receipt_date-last_payment_date),
3739: and org_id = l_org_id
3740: and currency = l_currency_code;
3741:
3742: IF sql%notfound then
3743: INSERT INTO ar_trx_bal_summary
3744: (CUST_ACCOUNT_ID,
3745: SITE_USE_ID,
3746: ORG_ID,
3747: CURRENCY,
3900: p_pmt_number IN VARCHAR2,
3901: p_pmt_date IN DATE,
3902: p_org_id IN NUMBER)IS
3903: select 'Y'
3904: from ar_trx_bal_summary
3905: where cust_account_id = p_customer_id
3906: and site_use_id = p_site_use_id
3907: and currency = p_currency
3908: and last_payment_number = p_pmt_number
4004: END IF;
4005:
4006: CLOSE is_this_last_payment;
4007:
4008: --Update ar_trx_bal_summary
4009: UPDATE ar_trx_bal_summary
4010: set unresolved_cash_value = nvl(unresolved_cash_value,0) -
4011: l_unresolved_cash,
4012: unresolved_cash_count = nvl(unresolved_cash_count,0) - 1,
4005:
4006: CLOSE is_this_last_payment;
4007:
4008: --Update ar_trx_bal_summary
4009: UPDATE ar_trx_bal_summary
4010: set unresolved_cash_value = nvl(unresolved_cash_value,0) -
4011: l_unresolved_cash,
4012: unresolved_cash_count = nvl(unresolved_cash_count,0) - 1,
4013: last_payment_amount = nvl(l_last_receipt_amount,last_payment_amount),
4205: ((l_history2_exists_flag = 'Y') OR (l_receipt_exists = 'Y'))
4206: THEN
4207:
4208: IF l_customer_id = l_hist_rec.customer_id THEN
4209: --Update ar_trx_bal_summary
4210: UPDATE ar_trx_bal_summary
4211: set unresolved_cash_value = nvl(unresolved_cash_value,0) +
4212: (l_receipt_amount - nvl(l_hist_rec.amount_due_original,0)),
4213: unresolved_cash_count = nvl(unresolved_cash_count,0),
4206: THEN
4207:
4208: IF l_customer_id = l_hist_rec.customer_id THEN
4209: --Update ar_trx_bal_summary
4210: UPDATE ar_trx_bal_summary
4211: set unresolved_cash_value = nvl(unresolved_cash_value,0) +
4212: (l_receipt_amount - nvl(l_hist_rec.amount_due_original,0)),
4213: unresolved_cash_count = nvl(unresolved_cash_count,0),
4214: last_payment_amount = l_receipt_amount,
4222: and org_id = l_org_id
4223: and currency = l_currency_code;
4224:
4225: IF sql%notfound then
4226: INSERT INTO ar_trx_bal_summary
4227: (CUST_ACCOUNT_ID,
4228: SITE_USE_ID,
4229: ORG_ID,
4230: CURRENCY,
4302: 1);
4303:
4304: END IF;
4305: ELSIF l_customer_id IS NULL AND l_hist_rec.customer_id IS NOT NULL THEN
4306: --Update ar_trx_bal_summary
4307: UPDATE ar_trx_bal_summary
4308: set unresolved_cash_value = nvl(unresolved_cash_value,0)
4309: - nvl(l_hist_rec.amount_due_original,0),
4310: unresolved_cash_count = nvl(unresolved_cash_count,0) -1,
4303:
4304: END IF;
4305: ELSIF l_customer_id IS NULL AND l_hist_rec.customer_id IS NOT NULL THEN
4306: --Update ar_trx_bal_summary
4307: UPDATE ar_trx_bal_summary
4308: set unresolved_cash_value = nvl(unresolved_cash_value,0)
4309: - nvl(l_hist_rec.amount_due_original,0),
4310: unresolved_cash_count = nvl(unresolved_cash_count,0) -1,
4311: last_payment_amount = nvl(l_hist_rec.amount_due_original,0),
4319: and org_id = l_org_id
4320: and currency = l_currency_code;
4321:
4322: IF sql%notfound then
4323: INSERT INTO ar_trx_bal_summary
4324: (CUST_ACCOUNT_ID,
4325: SITE_USE_ID,
4326: ORG_ID,
4327: CURRENCY,
4395: l_hist_rec.amount_due_original,
4396: 1);
4397: END IF;
4398: ELSIF l_customer_id IS NOT NULL AND l_hist_rec.customer_id IS NULL THEN
4399: --Update ar_trx_bal_summary
4400: UPDATE ar_trx_bal_summary
4401: set unresolved_cash_value = nvl(unresolved_cash_value,0)
4402: +l_receipt_amount ,
4403: unresolved_cash_count = nvl(unresolved_cash_count,0) +1,
4396: 1);
4397: END IF;
4398: ELSIF l_customer_id IS NOT NULL AND l_hist_rec.customer_id IS NULL THEN
4399: --Update ar_trx_bal_summary
4400: UPDATE ar_trx_bal_summary
4401: set unresolved_cash_value = nvl(unresolved_cash_value,0)
4402: +l_receipt_amount ,
4403: unresolved_cash_count = nvl(unresolved_cash_count,0) +1,
4404: last_payment_amount = l_receipt_amount,
4412: and org_id = l_org_id
4413: and currency = l_currency_code;
4414:
4415: IF sql%notfound then
4416: INSERT INTO ar_trx_bal_summary
4417: (CUST_ACCOUNT_ID,
4418: SITE_USE_ID,
4419: ORG_ID,
4420: CURRENCY,
4489: 1);
4490: END IF;
4491: ELSIF nvl(l_customer_id,0) <> nvl(l_hist_rec.customer_id,0) THEN
4492:
4493: --Update ar_trx_bal_summary
4494: UPDATE ar_trx_bal_summary
4495: set unresolved_cash_value = nvl(unresolved_cash_value,0)
4496: - nvl(l_hist_rec.amount_due_original,0),
4497: unresolved_cash_count = nvl(unresolved_cash_count,0) -1,
4490: END IF;
4491: ELSIF nvl(l_customer_id,0) <> nvl(l_hist_rec.customer_id,0) THEN
4492:
4493: --Update ar_trx_bal_summary
4494: UPDATE ar_trx_bal_summary
4495: set unresolved_cash_value = nvl(unresolved_cash_value,0)
4496: - nvl(l_hist_rec.amount_due_original,0),
4497: unresolved_cash_count = nvl(unresolved_cash_count,0) -1,
4498: last_payment_amount = nvl(l_hist_rec.amount_due_original,0),
4506: and org_id = l_org_id
4507: and currency = l_currency_code;
4508:
4509: IF sql%notfound then
4510: INSERT INTO ar_trx_bal_summary
4511: (CUST_ACCOUNT_ID,
4512: SITE_USE_ID,
4513: ORG_ID,
4514: CURRENCY,
4582: l_hist_rec.amount_due_original,
4583: 1);
4584: END IF;
4585:
4586: --Update ar_trx_bal_summary
4587: UPDATE ar_trx_bal_summary
4588: set unresolved_cash_value = nvl(unresolved_cash_value,0)
4589: +l_receipt_amount ,
4590: unresolved_cash_count = nvl(unresolved_cash_count,0)+1,
4583: 1);
4584: END IF;
4585:
4586: --Update ar_trx_bal_summary
4587: UPDATE ar_trx_bal_summary
4588: set unresolved_cash_value = nvl(unresolved_cash_value,0)
4589: +l_receipt_amount ,
4590: unresolved_cash_count = nvl(unresolved_cash_count,0)+1,
4591: last_payment_amount = l_receipt_amount,
4599: and org_id = l_org_id
4600: and currency = l_currency_code;
4601:
4602: IF sql%notfound then
4603: INSERT INTO ar_trx_bal_summary
4604: (CUST_ACCOUNT_ID,
4605: SITE_USE_ID,
4606: ORG_ID,
4607: CURRENCY,
4795: p_pmt_number IN VARCHAR2,
4796: p_pmt_date IN DATE,
4797: p_org_id IN NUMBER) IS
4798: SELECT 'Y'
4799: FROM ar_trx_bal_summary
4800: WHERE cust_account_id = p_customer_id
4801: AND site_use_id = p_site_use_id
4802: AND currency = p_currency
4803: AND last_payment_number = p_pmt_number
4912: END IF;
4913:
4914: CLOSE is_this_last_payment;
4915:
4916: --Update ar_trx_bal_summary
4917: UPDATE ar_trx_bal_summary
4918: SET unresolved_cash_value = NVL(unresolved_cash_value,0) -
4919: NVL(k.amount_due_original,0),
4920: unresolved_cash_count = NVL(unresolved_cash_count,0) - 1,
4913:
4914: CLOSE is_this_last_payment;
4915:
4916: --Update ar_trx_bal_summary
4917: UPDATE ar_trx_bal_summary
4918: SET unresolved_cash_value = NVL(unresolved_cash_value,0) -
4919: NVL(k.amount_due_original,0),
4920: unresolved_cash_count = NVL(unresolved_cash_count,0) - 1,
4921: last_payment_amount = NVL(l_last_receipt_amount,last_payment_amount),
5098: credit memo application
5099: AR_TRX_SUMMARY
5100: ==================
5101:
5102: AR_TRX_BAL_SUMMARY
5103: ==================
5104: 1) OP_INVOICES_VALUE
5105: 2) OP_INVOICES_COUNT
5106: 3) OP_DEBIT_MEMOS_VALUE
5303: credit memo application
5304: AR_TRX_SUMMARY
5305: ==================
5306:
5307: AR_TRX_BAL_SUMMARY
5308: ==================
5309: 1) OP_INVOICES_VALUE
5310: 2) OP_INVOICES_COUNT
5311: 3) OP_DEBIT_MEMOS_VALUE
5577: 10)COUNT_OF_INV_INST_PAID_LATE
5578: 11)COUNT_OF_DISC_INV_INST
5579: 12)DAYS_CREDIT_GRANTED_SUM
5580:
5581: AR_TRX_BAL_SUMMARY
5582: ==================
5583: 1) OP_INVOICES_VALUE
5584: 2) OP_INVOICES_COUNT
5585: 3) OP_DEBIT_MEMOS_VALUE
5958: 10)COUNT_OF_INV_INST_PAID_LATE
5959: 11)COUNT_OF_DISC_INV_INST
5960: 12)DAYS_CREDIT_GRANTED_SUM
5961:
5962: AR_TRX_BAL_SUMMARY
5963: ==================
5964: 1) OP_INVOICES_VALUE
5965: 2) OP_INVOICES_COUNT
5966: 3) OP_DEBIT_MEMOS_VALUE
7078: past_due_inv_inst_count
7079: past_due_inv_value
7080:
7081: In the original bug, lockbox receipts were corrupting these columns in
7082: ar_trx_bal_summary for partial receipts, multiple applications to one
7083: trx from different receipts, and receipts with apply_dates that
7084: occurr before the trx.due_date. */
7085: PROCEDURE refresh_counts(
7086: p_customer_id_tab IN ar_bus_event_sub_pvt.generic_id_type,
7096: debug ('ar_bus_event_sub_pvt.refresh_counts()+');
7097: END IF;
7098:
7099: FORALL i IN p_customer_id_tab.FIRST .. p_customer_id_tab.LAST
7100: update ar_trx_bal_summary main_sum
7101: set (op_invoices_count,
7102: op_invoices_value,
7103: past_due_inv_inst_count,
7104: past_due_inv_value,