[Home] [Help]
207: l_fin_charge_date := p_fin_charge_date;
208: l_worker_number := p_worker_number;
209: l_total_workers := p_total_workers;
210:
211: Insert into ar_late_charge_trx_t
212: (late_charge_trx_id,
213: customer_id,
214: customer_site_use_id,
215: currency_code,
575: CURSOR recordPerCust(l_line_type IN VARCHAR2) IS
576: SELECT count(*) reccount,sum(amount_due_original) total_due_org,
577: CUSTOMER_ID,CUSTOMER_SITE_USE_ID,SCHEDULE_DAYS_START,SCHEDULE_DAYS_TO,CURRENCY_CODE,
578: LATE_CHARGE_AMOUNT,LATE_CHARGE_TYPE,ORG_ID
579: FROM AR_LATE_CHARGE_TRX_T
580: WHERE INTEREST_TYPE = 'CHARGE_PER_TIER'
581: /*and LATE_CHARGE_TYPE = l_charge_type /*'INV'*/
582: AND CHARGE_LINE_TYPE = l_line_type /*'INTEREST' /* l_line_type*/
583: AND OVERDUE_LATE_PAY_AMOUNT > 0
597:
598: CURSOR trx_t(l_line_type IN VARCHAR2) IS
599: SELECT LATE_CHARGE_TRX_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,SCHEDULE_DAYS_START,SCHEDULE_DAYS_TO,CURRENCY_CODE,
600: LATE_CHARGE_TYPE,ORG_ID,AMOUNT_DUE_ORIGINAL
601: FROM AR_LATE_CHARGE_TRX_T
602: WHERE INTEREST_TYPE = 'CHARGE_PER_TIER'
603: /* and LATE_CHARGE_TYPE = l_charge_type /*'INV'*/
604: AND CHARGE_LINE_TYPE = l_line_type /*'INTEREST'; /* l_line_type;*/
605: AND OVERDUE_LATE_PAY_AMOUNT > 0
626: );
627: TYPE rec_per_cust_tier is table of rec_per_customer index by binary_integer;
628: p_rec_cust_per_tier rec_per_cust_tier;
629:
630: TYPE cust_trx_id is table of ar_late_charge_trx_t.late_charge_trx_id%TYPE INDEX BY BINARY_INTEGER;
631: p_cust_sites_id cust_trx_id;
632:
633:
634: TYPE lc_per_trx is table of ar_late_charge_trx_t.late_charge_amount%type INDEX BY BINARY_INTEGER;
630: TYPE cust_trx_id is table of ar_late_charge_trx_t.late_charge_trx_id%TYPE INDEX BY BINARY_INTEGER;
631: p_cust_sites_id cust_trx_id;
632:
633:
634: TYPE lc_per_trx is table of ar_late_charge_trx_t.late_charge_amount%type INDEX BY BINARY_INTEGER;
635: p_lc_per_trx lc_per_trx;
636:
637: i number;
638: j number;
643: END IF;
644:
645: /*Bug 8559863 Restrict to 0 out late charge for charge per tier case. It results in inaccurate late charges
646: for other scenario. Fixed as part of this bug*/
647: UPDATE ar_late_charge_trx_t SET late_charge_amount = 0 where amount_due_original < 0
648: AND INTEREST_TYPE = 'CHARGE_PER_TIER';
649: i := 0;
650: FOR recpertier in recordPerCust(p_line_type)
651: LOOP
712: debug('LC Amt ' || p_lc_per_trx(rec));
713: END LOOP;
714: END IF;
715: FORALL trxindex in p_lc_per_trx.FIRST..p_lc_per_trx.LAST
716: UPDATE ar_late_charge_trx_t set late_charge_amount = p_lc_per_trx(trxindex)
717: WHERE late_charge_trx_id = p_cust_sites_id(trxindex);
718: END IF;
719: IF l_debug_flag = 'Y' THEN
720: debug('update interest amount-');
1944: | |
1945: | This procedure calculates the overdue balance of the debit items and applies those |
1946: | against the credit items in the order of the due_date. The Interest Amount is then |
1947: | calculated on the remaining amount of those debit items and inserted into |
1948: | ar_late_charge_trx_t |
1949: | |
1950: | PSEUDO CODE/LOGIC |
1951: | |
1952: | a) Get the overdue balances of the debit items as sum of |
1998: l_fin_charge_date := p_fin_charge_date;
1999: l_worker_number := p_worker_number;
2000: l_total_workers := p_total_workers;
2001:
2002: insert into ar_late_charge_trx_t
2003: (late_charge_trx_id,
2004: customer_id,
2005: customer_site_use_id,
2006: currency_code,
2769: | DESCRIPTION |
2770: | |
2771: | This procedure calculates the overdue balance of the debit and credit items. The |
2772: | Interest Amount is then calculated on the overdue balance and inserted into |
2773: | ar_late_charge_trx_t |
2774: | |
2775: | PSEUDO CODE/LOGIC |
2776: | |
2777: | a) Get the overdue balances of the items as sum of |
2813: l_fin_charge_date := p_fin_charge_date;
2814: l_worker_number := p_worker_number;
2815: l_total_workers := p_total_workers;
2816: /*Bug10335029 Changed sqls to have comparision of tolerance only if its populated else ignore*/
2817: insert into ar_late_charge_trx_t
2818: (late_charge_trx_id,
2819: customer_id,
2820: customer_site_use_id,
2821: currency_code,
3644: | |
3645: | This procedure finds out the late payments on any debit item. By late payment, we |
3646: | mean the applications done on it after the due_date / last finance charge date . |
3647: | Interest Amount is then calculated on the overdue balance and inserted into |
3648: | ar_late_charge_trx_t |
3649: | |
3650: | PSEUDO CODE/LOGIC |
3651: | |
3652: | a) The Receipt Date is used for finding out the late applications on a debit item. So, |
3723: | |
3724: | Note that the interest will NOT be calculated on the over applied amount. |
3725: *=========================================================================================*/
3726: /*Bug 8277068 Corrected interest days calclulation to pick least from eff_apply_date and charge schedule end date*/
3727: insert into ar_late_charge_trx_t
3728: (late_charge_trx_id,
3729: customer_id,
3730: customer_site_use_id,
3731: currency_code,
4131: | DESCRIPTION |
4132: | |
4133: | This procedure computes the average daily balance of the debit items and calculates |
4134: | Interest on this average daily balance and inserts the records into |
4135: | ar_late_charge_trx_t |
4136: | |
4137: | PSEUDO CODE/LOGIC |
4138: | |
4139: | |
4193: l_total_workers := p_total_workers;
4194:
4195: /* Insert records for the system option set up RUN DATE TO RUN DATE */
4196:
4197: insert into ar_late_charge_trx_t
4198: (late_charge_trx_id,
4199: customer_id,
4200: customer_site_use_id,
4201: currency_code,
4569: l_fin_charge_date) = 'Y');
4570:
4571: /* Insert records for the system option set up DUE DATE TO RUN DATE */
4572:
4573: insert into ar_late_charge_trx_t
4574: (late_charge_trx_id,
4575: customer_id,
4576: customer_site_use_id,
4577: currency_code,
5228: | |
5229: | This procedure calculates the penalty against a payment schedule by adding the |
5230: | all the interest charged against it. The penalty is either a fixed amount or a |
5231: | percentage of the interest charge. The calculated Penalty is inserted back into |
5232: | ar_late_charge_trx_t |
5233: | |
5234: | PSEUDO CODE/LOGIC |
5235: | |
5236: | |
5241: | |
5242: | NOTES |
5243: | |
5244: | Since we are storing the balance as of the receipt date in the Original balance column |
5245: | in ar_late_charge_trx_t (in the case of LATE payments) , this column is not inserted |
5246: | for PENALTY lines. The receipt_date is also inserted as NULL as there can be multiple |
5247: | receipts against one payment schedule, but only one penalty line |
5248: | |
5249: | MODIFICATION HISTORY |
5260: IF l_debug_flag = 'Y' THEN
5261: debug( 'ar_calc_late_charge.insert_penalty_lines()+' );
5262: END IF;
5263:
5264: insert into ar_late_charge_trx_t
5265: ( late_charge_trx_id,
5266: customer_id,
5267: customer_site_use_id,
5268: currency_code,
5409: decode(trx.charge_type,'AVERAGE_DAILY_BALANCE',trx.interest_days,1) interest_days,
5410: decode(trx.charge_type,'AVERAGE_DAILY_BALANCE',trx.charge_type, NULL) charge_type,
5411: cust_site.penalty_type
5412: from ar_lc_cust_sites_t cust_site,
5413: ar_late_charge_trx_t trx,
5414: ar_charge_schedule_hdrs sched_hdrs,
5415: ar_charge_schedule_lines sched_lines,
5416: ar_aging_bucket_lines bucket_lines
5417: where cust_site.customer_id = trx.customer_id
5560: and mod(nvl(hdr.customer_site_use_id,0),p_total_workers) =
5561: decode(p_total_workers,p_worker_number,0,p_worker_number)
5562: and batch.request_id <> l_request_id
5563: and exists (select late_charge_trx_id
5564: from ar_late_charge_trx_t trx
5565: where trx.customer_id = hdr.customer_id
5566: and trx.customer_site_use_id = hdr.customer_site_use_id
5567: and trx.currency_code = hdr.currency_code
5568: and nvl(trx.legal_entity_id,-99) = nvl(hdr.legal_entity_id,-99)
5807: trx.late_charge_type,
5808: trx.org_id,
5809: trx.display_flag
5810: from ar_interest_lines lines,
5811: ar_late_charge_trx_t trx
5812: where lines.payment_schedule_id = trx.payment_schedule_id
5813: and lines.org_id = trx.org_id
5814: and lines.type=trx.charge_type
5815: and not exists (select interest_header_id
5820: /*Bug fix 5290709: If display flag is No, we should not consider this record if there is another record
5821: existing with display flag Yes */
5822: and ((trx.display_flag = 'Y' and sign(trx.late_charge_amount) <> 0)
5823: OR (trx.display_flag = 'N' and not exists (select 1
5824: from ar_late_charge_trx_t trx1
5825: where trx1.payment_schedule_id = trx.payment_schedule_id
5826: and trx1.display_flag = 'Y')))
5827: and nvl(lines.cash_receipt_id,-1) = nvl(trx.cash_receipt_id,-1)
5828: group by lines.interest_header_id,
6106: trx.late_charge_type,
6107: trx.org_id,
6108: trx.display_flag,
6109: decode(trx.late_charge_type,'INV',-99,trx.payment_schedule_id) payment_schedule_id
6110: from ar_late_charge_trx_t trx
6111: where mod(nvl(trx.customer_site_use_id,0),p_total_workers) =
6112: decode(p_total_workers,p_worker_number,0,p_worker_number)
6113: /*Bug fix 5290709: If display flag is No, we should not consider this record if there is another record
6114: existing with display flag Yes */
6113: /*Bug fix 5290709: If display flag is No, we should not consider this record if there is another record
6114: existing with display flag Yes */
6115: and ((trx.display_flag = 'Y' and sign(trx.late_charge_amount) <> 0)
6116: OR (trx.display_flag = 'N' and not exists (select 1
6117: from ar_late_charge_trx_t trx1
6118: where trx1.payment_schedule_id = trx.payment_schedule_id
6119: and trx1.display_flag = 'Y')))
6120: group by trx.customer_id,
6121: trx.customer_site_use_id,
6126: trx.display_flag,
6127: decode(trx.late_charge_type,'INV',-99,trx.payment_schedule_id))a)hdr,
6128: (select trx.payment_schedule_id,
6129: sum(trx.late_charge_amount) total_interest
6130: from ar_late_charge_trx_t trx
6131: where trx.charge_line_type = 'INTEREST'
6132: and mod(nvl(trx.customer_site_use_id,0),p_total_workers) =
6133: decode(p_total_workers,p_worker_number,0,p_worker_number)
6134: group by trx.payment_schedule_id) int_tab,
6131: where trx.charge_line_type = 'INTEREST'
6132: and mod(nvl(trx.customer_site_use_id,0),p_total_workers) =
6133: decode(p_total_workers,p_worker_number,0,p_worker_number)
6134: group by trx.payment_schedule_id) int_tab,
6135: ar_late_charge_trx_t trx
6136: /* Apply the invoice level tolerances */
6137: where decode(trx.class,
6138: 'CM', nvl(trx.original_balance,0),
6139: 'PMT',nvl(trx.original_balance,0),
6167: /*Bug fix 5290709: If display flag is No, we should not consider this record if there is another record
6168: existing with display flag Yes */
6169: and ((trx.display_flag = 'Y' and sign(trx.late_charge_amount) <> 0)
6170: OR (trx.display_flag = 'N' and not exists (select 1
6171: from ar_late_charge_trx_t trx1
6172: where trx1.payment_schedule_id = trx.payment_schedule_id
6173: and trx1.display_flag = 'Y')))
6174: and hdr.payment_schedule_id = decode(trx.late_charge_type,'INV',-99,trx.payment_schedule_id)) a ) b);
6175:
6339: trx.legal_entity_id,
6340: trx.late_charge_type,
6341: trx.org_id,
6342: decode(trx.late_charge_type,'INV',-99,trx.payment_schedule_id) payment_schedule_id
6343: from ar_late_charge_trx_t trx
6344: where mod(nvl(trx.customer_site_use_id,0),p_total_workers) =
6345: decode(p_total_workers,p_worker_number,0,p_worker_number)
6346: group by trx.customer_id,
6347: trx.customer_site_use_id,
6351: trx.org_id,
6352: decode(trx.late_charge_type,'INV',-99,trx.payment_schedule_id))a)hdr,
6353: (select trx.payment_schedule_id,
6354: sum(trx.late_charge_amount) total_interest
6355: from ar_late_charge_trx_t trx
6356: where trx.charge_line_type = 'INTEREST'
6357: and mod(nvl(trx.customer_site_use_id,0),1) =
6358: decode(1,1,0,1)
6359: group by trx.payment_schedule_id) int_tab,
6356: where trx.charge_line_type = 'INTEREST'
6357: and mod(nvl(trx.customer_site_use_id,0),1) =
6358: decode(1,1,0,1)
6359: group by trx.payment_schedule_id) int_tab,
6360: ar_late_charge_trx_t trx
6361: /* Apply Min Interest charge tolerance */
6362: where decode(trx.charge_line_type,
6363: 'INTEREST', nvl(trx.min_interest_charge,0),
6364: 'PENALTY',nvl(trx.late_charge_amount,0)) <= nvl(trx.late_charge_amount,0)
6770: min_fc_invoice_overdue_type,
6771: min_fc_invoice_amount,
6772: min_fc_invoice_percent,
6773: charge_line_type
6774: from ar_late_charge_trx_t
6775: order by customer_id,
6776: customer_site_use_id,
6777: currency_code,
6778: legal_entity_id,
7255: insert_penalty_lines(p_worker_number => l_worker_number,
7256: p_total_workers => l_total_workers);
7257: /* Enhacement 6469663*/
7258: update_interest_amt('PENALTY');
7259: /* If there are records in ar_late_charge_trx_t, insert data into the preview tables
7260: ar_interest_batches, ar_interest_headers and ar_interest_lines */
7261:
7262: select count(*)
7263: into l_count_int_lines
7260: ar_interest_batches, ar_interest_headers and ar_interest_lines */
7261:
7262: select count(*)
7263: into l_count_int_lines
7264: from ar_late_charge_trx_t;
7265:
7266: debug('Number of records in the interim transactions table : '|| l_count_int_lines);
7267:
7268: IF l_count_int_lines > 0 THEN
7307:
7308: /* delete the data in the interim tables before commit */
7309: delete from ar_lc_cust_sites_t;
7310:
7311: delete from ar_late_charge_trx_t;
7312:
7313: COMMIT;
7314:
7315: /* If the program is run in Final Mode, invoke the api call so that the final
7332: /* delete the data in the interim tables before commit */
7333:
7334: delete from ar_lc_cust_sites_t;
7335:
7336: delete from ar_late_charge_trx_t;
7337:
7338: END IF;
7339:
7340:
7348: EXCEPTION WHEN OTHERS THEN
7349: /* delete the data in the interim tables before commit */
7350: delete from ar_lc_cust_sites_t;
7351:
7352: delete from ar_late_charge_trx_t;
7353:
7354: END create_late_charge_document;
7355:
7356: /*========================================================================+