RPA---Repayment pending Appropriation

In case the customer pays a single or multiple installment amounts before it is due, then the system applies the amount as per the appropriation sequence and any excess over and above that is stored as RPA - Repayment pending appropriation.

Which means that the customer does not get any interest benefit for the same. On the next due date the system uses the RPA amount for satisfying the arrears.

Any shortfall is then recovered from the CASA account (if a CASA draw down is set up), If there are no outstanding arrears then the entire payment is stored in the RPA. Thus the customer only gets the advantage of his arrears getting knocked off in time avoiding any penalty. RPA does not also result in the term or the installment getting reduced as the outstanding does not come down nor does the interest base. In Advance payment the system reduces the interest base on the same day (as defined in the schedule Type) thereby bringing down the interest burden as in case of partial payoff and also stores the advance amount as RPA (principal and interest RPA). Thus like in case of RPA the system recovers the principle and interest arrears on the due date. Thus the salary draw down set on a date before the due date of payment gets the advantage of advance payment as the system recovers the arrears of the next cycle.

RPA maybe widely used in Advanced repayment, which is not widely used.

Oracle Tips 20110614- about data type conversion

This post explains how to use the Explain plan for Oracle's SQL execution to debug sqls.
First create two tables:

create table char_tables (party_id varchar2(30), party_name varchar2(30))

create table number_tables (party_id number, party_name varchar2(30));

insert into number_tables select rownum, table_name from user_tables

insert into char_tables select rownum, table_name from user_tables

insert into char_tables select table_name, table_name|| rownum from user_tables

When issue the following SQL, there is an invalid number exception:
   select  n.party_id,c.party_id
 from  number_tables n,char_tables c
 where   n.party_id = c.party_id and n.party_id =1;

it maybe the data type conversion problem, but let's confirm it with explain plan.

explain plan
 for
select  n.party_id,c.party_id
 from  number_tables n,char_tables c
 where   n.party_id = c.party_id and n.party_id =1;
 commit;
 select * from table(dbms_xplan.display);
  The plan is as below:
  
  We can see from the plan that the sql engine try to convert the VARCHAR2 column into the NUMBER column.

  Let's try another sql:
  explain plan
  for
  delete from char_tables where party_id in (1,2,3);
  commit;
  select * from table(dbms_xplan.display);

the plan is as below:
  
  The SQL engine try to convert the column into the NUMBER type.

 EXPLAIN PLAN
 FOR 
DELETE FROM char_tables WHERE party_id not in (1,2,3);
  COMMIT;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  The plan is as below:
 
  For above, the NOT clause is translated into the <> operator.  This SQL doesn't work because some values of the party_id column in the char_tables CAN NOT BE CONVERTED INTO NUMBER.

Another SQL:
   explain plan
  for
  delete from char_tables where party_id in (1,2,3);
  commit;
  select * from table(dbms_xplan.display);

  The plan is as below:
 
  
   If the variable's type matches the column's type, no conversion is needed, otherwise, a conversion is applied.
Try to convert the VARCHAR2 into NUMBER.

  Let's considering the scenarios when indexes are used:
   create index char_index on char_tables(party_id) ;
   create index number_index on number_tables(party_id);

The SQL is as below:
   explain plan
  for
  delete from char_tables where party_id in (1,2,3);
  commit;
  select * from table(dbms_xplan.display);

 

  it's a full-table access query. The index is not used because the SQL was translated into the TO_NUMBER()...
 A function index ON TO_NUMBER(party_id) may works.

 Let's try the other SQL:
 
  It's not a full table access, but an index range access.

  So, in Oracle, since data conversion will be automatically executed, the developer has to pay attention to those default tips.

 

 
 

Arrears Appropriation Sequence

 

The sequence which determines the order in which arrears are to be paid in a loan account.

At the product level, the bank can set the rules for treatment of payment when the amount paid is less than the arrears due. A loan installment may only be partly paid by the customer in which case the system will require to know the o rder in which the existing arrears need to be set off. This order is specified in the arrears appropriation sequence. A separate sequence can be specified for regular loans and for non-performing loans.

The sequence makes use of the following abbreviations:

• I – Interest Arrears
• F – Fee Arrears
• S – Legal Fees Arrears
• O – Outgoing Arrears
• D – Suspended Fees
• E – Suspended legal fees
• G – Suspended Premium
• M – Suspended outgoings
• P – Premium Arrears
• N – Non Accrual Interest Arrears (Suspended Interest)
• U – Post Maturity interest Arrears
• T – Post Maturity – Non Accrual Interest arrears (Suspended PMI)
• C – Principal Arrears

It may also be possible that within a particular type of arrear, there maybe more than one installment due. In t his case the longest overdue first flag will specify in which order each each arrear will need to be set off within a date. Ticking this flag will indicate that the system will pick up the earliest date for which arrears are due and then set off arrears based on the appropriate sequence.

Example:

Appropriation sequence specified as – I F A C S O D G E M P L N U T

(Interest, Fees, Arrears on interest(penalty interest), Capital etc.)

Total arrears due – INR 15,000

Total amount paid on 20th May 2004 – INR 10000

The arrears on account and their appropriation as follows for longest overdue first flag not ticked:

Order the arrears by the sequence specified at the product level.

 

Due Date	Arrear Type	Amount Due	Order of Set off	Amount of Appropriation	Cumulative Amount setoff
31-Mar-2004	C - Capital	4000	5	3500	10000
31-Mar-2004	I - Interest	2000	1	2000	2000
30-Apr-2004	A – Arrears on Interest	100	4	1000	6500
30-Apr-2004	C - Capital	4500	None	None	None (since there is no money for this arrear)
30-Apr-2004	I - Interest	3000	2	3000	5000
15-May-2004	F - Fees	500	3	500	500

 

The arrears on account and their appropriation as follows if longest overdue first flag is ticked:

Order the arrears based on the due date of the arrear as well as the sequence specified at the product level.

Due Date	Arrear Type	Amount Due	Order of Set off	Amount of Appropriation	Cumulative Amount setoff
31-Mar-2004	C - Capital	4000	2	4000	6000
31-Mar-2004	I - Interest	2000	1	2000	2000
30-Apr-2004	A – Arrears on Interest	100	5	500	10000
30-Apr-2004	C - Capital	4500	None	None	None (there is no money for this arrear)
30-Apr-2004	I - Interest	3000	3	3000	9000
15-May-2004	F - Fees	500	4	500	9500

Algorithm of Fibonacci numbers

 long fibonacci(int n){
  assert(n>=0);
  if(n == 0) return 1;
  if(n == 1) return 1;
  return fibonacci(n-1) + fibonacci(n-2);
}

long fibonacci(int n){
  if(n ==0 ) return 1;
  long[] fibs = new long[n+1];
  fibs[0] = 1;
  fibs[0] = 2;
  for(int i=2;i<=n;i++){
    fibs[i] = fibs[i-1] + fibs[i-2];
  }
  return fibs[n];
}

long fibonacci(int n){
  if(n==0) || (n==1) return 1;
  int sum = 0;
  int number0 = 1;
  int number1 = 1;
  for(int i=2;i<=n;i++){
    sum = number0 + number1;
    number0 = number1;
    number1 = sum;
  }
  return sum;
}

CASA: Current And Saving Account

RD in FCR is implemented as a variant of CASA.

For CASA. there are four things to do when configure a CASA account and make a transaction.

1. CASA definition

CASA definitions include the following sides: 

    A. Product Definitions

    B. Bank Parameters

    C. RD Product Rates

    D. RD Grace Days

2. Account Opening Process

     The Account opening process will use Branch code and Customer Information from the CIF module to open an account for the customer.

     A. Open Account

     B. A/C operating Instructions

     C. A/C Reporting Code/User Define Fields

     D. Account Memo

3.  Online Transactions

    Online transaction includes the following categories:

      A. Credit

           Cash/Single Cheque/Data Entry for Consolidated/Postdated(Deposit/ Cancel) / Purchase Cheque/ Liquidating BC/Liquidating DD/ Liquidating TC/ Purchase TC/ Redemption by Transfer/ Transferring Funds/ Disbursement by Transfer/ Making Loan Rescission/Loans Refund/Miscellaneous Credit/ IDC/ODC

      B. Debit

           Cash/Single Cheque/ Sale TC/Issue BC/Sale DD/Sale TT/Transferring Funds/Payin by Transfer/Paying Installment by Transfer/Making Partial Pay-off of Loan/Setting a Loan by Fund Transfer/Loan Advance Payment by Transfer/Miscellaneous Debit/Common Billing Transactions/Bill Payment

      C. Inquiry

           Account Balance/Account Statement/Inward Check status/ Account number checkdigit/Account status Audit Trail/SWIFT Statement Details

      D. Passbook:

           Issue New Passbook/Inquiring on passbook/passbook balance check digit.

      E. Fund Management / Account Instructions

           Hold Funds/Delete Earmark/Standing Instructions/Sweep-out/Sweep-in/Group Accounts/Inward Direct Debit/Account cheque purchase limit/Sweep out execution to CASA/TD/ SC for Sweep out/ Sweep-in processing –TD Redemption/Sweep-in Processing –Unmark lien/Standing Instruction Execution/Hold Funds Expiry

      F. Statement

            Special Frequency/Account Address Maintenance/Statement generation

      G. Cheque Book

             Check Box Request/Check Book Maintenance/Maintaining Stop cheque instruction

      H. Collateral & Limit

            Limit Collateral Inquiry/OD Limit Master Maintenance/Account Collateral/OD Limit Expiry Processing/Identifying Limits Expiring next month

      I. Account Closure

             Account close/Close out withdrawal by cash/close-out withdrawal by banker’s check/Close out withdrawal by GL

      J. Recurring Deposit (RD)

          RD Schedual Inquiry

      K. Other

          Maintaining the Account Master/Account Master Attribute/Account Status/SC Account NSF Preference/Interest Adjustment/Account SC waiver/Interest Freeze/JPA account cross reference

4. Batch Transactions

       A. NPA (Non-performing Asset, popularly known as Bad Loan).

               Reminder Processing/NPA classification processing/NPA Reserve Creation/NPA Appropriation

       B,. Interest and charges

               Interest Accrual/Reset ADB Statistics/ Interest Capitalization/ SC capitalization/ SC processing for transactions/SC/Interest – Internal Transfer processing.

      C. Others

                Transaction posting/Other House Keeping/RD Maturity Processing/GL handoff/Dormancy Processing/Combine Stream/Unclaimed Processing/TDS Processing/Dormancy Automatic re-activation.