Introduction of EMI and Implementation

EMI is a calculated amount that is paid by user every term.

Type of EMI

• Fix EMI
• Equal Principal EMI
• Staggered EMI

Fix EMI: In this EMI ,Amount is calculated by formulla.

Formulla:

ROUND( ((pi_amount) * (pi_roi/(l_rate*100))) * (power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) / ((power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) -1))

Equal Principal EMI: In this EMI ,Loan Amount is divide by tenure of loan after that Interest can be add on that calculated amount.

Eg: Loan Amount =500000 , Tenure=12 (EMI)
So Equal Principal EMI= Loan Amount/Tenure + Interest

Formulla: ROUND((pi_amount)/pi_emi_tenure)

Staggered EMI: In this EMI ,EMI is calculated as Equal Principal EMI. But client give Principal according to their suitability and interest are same as paid by client.

Function:(In Oracle)

CREATE OR REPLACE FUNCTION Galaxy_Cc_Calculator
(
pi_amount number DEFAULT NULL,
pi_roi  number DEFAULT NULL,
pi_emi_tenure number DEFAULT NULL,
pi_payment_type VARCHAR2 DEFAULT NULL,
pi_emi_type VARCHAR2 DEFAULT NULL,
pi_calc_type VARCHAR2 DEFAULT NULL,
pi_find VARCHAR2 DEFAULT NULL
)
RETURN number
AS
l_rate number;
l_principal number;
l_returnString VARCHAR2(500);
BEGIN
l_principal:=0;
-- Begin Set Rate Emi Quaterly /HAlfyearly/Monthly
select
CASE pi_payment_type
WHEN 'Half Yearly' THEN 2
WHEN 'Quaterly' THEN 3
ELSE 12 END into l_rate from dual;
-- End Set Rate Emi Quaterly /HAlfyearly/Monthly
-- Begin IRR Calculator
IF(pi_calc_type='IRR' OR pi_calc_type='ROI' OR pi_calc_type='UPFRONT') THEN
-- Begin Fixed Emi
IF(pi_emi_type='FIXED') THEN
select
CASE pi_find
WHEN 'EMI' THEN
--ROUND( ((pi_amount) * (pi_roi/(l_rate*100))) * (power((1 + (pi_roi/(l_rate*100))),l_rate)) / ((power((1 + (pi_roi/(l_rate*100))),l_rate)) -1))
ROUND( ((pi_amount) * (pi_roi/(l_rate*100))) * (power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) / ((power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) -1))
WHEN 'INTEREST' THEN
ROUND((pi_amount) * (pi_roi/(l_rate*100)))
WHEN 'PRINCIPAL' THEN
--((ROUND( ((pi_amount) * (pi_roi/(l_rate*100))) * (power((1 + (pi_roi/(l_rate*100))),l_rate)) / ((power((1 + (pi_roi/(l_rate*100))),l_rate)) -1)))- (ROUND((pi_amount) * (pi_roi/(l_rate*100)))))
((ROUND( ((pi_amount) * (pi_roi/(l_rate*100))) * (power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) / ((power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) -1)))- (ROUND((pi_amount) * (pi_roi/(l_rate*100)))))
WHEN 'EVALUATED PRINCIPAL' THEN
--ROUND((pi_amount)-(((ROUND( ((pi_amount) * (pi_roi/(l_rate*100))) * (power((1 + (pi_roi/(l_rate*100))),l_rate)) / ((power((1 + (pi_roi/(l_rate*100))),l_rate)) -1)))- (ROUND((pi_amount) * (pi_roi/(l_rate*100)))))))
ROUND((pi_amount)-(((ROUND( ((pi_amount) * (pi_roi/(l_rate*100))) * (power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) / ((power((1 + (pi_roi/(l_rate*100))),pi_emi_tenure)) -1)))- (ROUND((pi_amount) * (pi_roi/(l_rate*100)))))))
ELSE
0
END
into l_principal from dual;
END IF;
-- End Fixed Emi
-- Begin Fixed Principal Emi
IF(pi_emi_type='EQUAL PRINCIPAL') THEN
select
CASE pi_find
WHEN 'EMI' THEN
ROUND((pi_amount)/pi_emi_tenure)
WHEN 'INTEREST' THEN
ROUND((pi_amount) * (pi_roi/(l_rate*100)))
WHEN 'CASH FLOW' THEN
ROUND(((pi_amount)/pi_emi_tenure)+ ((pi_amount) * (pi_roi/(l_rate*100))))
WHEN 'EVALUATED PRINCIPAL' THEN
ROUND((pi_amount)-((pi_amount)/pi_emi_tenure))
ELSE
0
END
into l_principal from dual;
END IF;
-- End PrincipalFixed Emi
-- Begin Staggered EMI Emi
IF(pi_emi_type='STAGGERED EMI') THEN
select
CASE pi_find
WHEN 'EMI' THEN
ROUND((pi_amount) * (pi_roi/(l_rate*100)) +((pi_amount)/pi_emi_tenure) )
WHEN 'INTEREST' THEN
ROUND((pi_amount) * (pi_roi/(l_rate*100)))
WHEN 'CASH FLOW' THEN
ROUND((pi_amount)/pi_emi_tenure)
WHEN 'EVALUATED PRINCIPAL' THEN
ROUND((pi_amount) - ((pi_amount)/pi_emi_tenure))
ELSE
0
END
into l_principal from dual;
END IF;
-- End Staggered EMI Emi
END IF;
-- End IRR Calculator
RETURN l_principal;
EXCEPTION
WHEN OTHERS THEN
l_returnString := 'EXCEPTION';
RETURN l_returnString;
END;
/
***********
Procedure
***********
CREATE OR REPLACE PROCEDURE Galaxy_Cc_Get_Fixed_Emi
(
pi_userid IN VARCHAR2,
pi_principal IN NUMBER,
pi_interest IN NUMBER,
pi_tenure IN NUMBER,
pi_emitype IN VARCHAR2,
pi_charges IN NUMBER,
pi_repaymentmethod  IN VARCHAR2,
pi_calctype IN VARCHAR2,
pi_IRR IN NUMBER DEFAULT 0,
po_cursor OUT Plms_Package.ref_cursor,
po_message  OUT VARCHAR2,
po_upfrontcharges OUT VARCHAR2
)
AS
l_principal NUMBER :=0;
l_interest NUMBER :=0;
l_emi   NUMBER :=0;
l_amnt NUMBER :=0;
l_Actprncpl NUMBER :=0;
p_amount_array XIRR_T_AMOUNT_ARRAY;
p_date_array Xirr_p_date_array;
l_guess NUMBER := 0.0;
l_xirr_value NUMBER := 0.0;
l_nominal_months INT := 12;
l_nominal_value NUMBER := 0.0;
creation_table plms_Table80;
recordcount NUMBER:=1;
l_count NUMBER:=1;
l_date VARCHAR2(50);
l_outflow NUMBER;
l_counter NUMBER:=1;
l_roi NUMBER :=1;
BEGIN
creation_table := NEW plms_Table80();
p_amount_array := XIRR_T_AMOUNT_ARRAY();
p_date_array := Xirr_p_date_array();
OPEN po_cursor FOR SELECT * FROM DUAL;
po_message :='SUCCESS';
--Begins finding Upfront Charges logic 1
IF pi_calctype ='UPFRONT' THEN
IF(pi_charges=0) THEN
l_outflow:=pi_principal*.1;
p_amount_array.extend(pi_tenure+1);
p_date_array.extend(pi_tenure+1);
p_amount_array(l_count):= -l_outflow;
p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
l_count:=2;
ELSE
l_outflow:=pi_principal-pi_charges;
p_amount_array.extend(pi_tenure+1);
p_amount_array(l_count):= -l_outflow;
p_date_array.extend(pi_tenure+1);
p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
l_count:=2;
END IF ;
l_nominal_months:=pi_tenure;
END IF;
SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'mon'),1),'dd-Mon-rrrr') INTO l_date FROM dual;
--END finding Upfront Charges
--Begin IRR Calc Type
IF (pi_calctype='IRR') THEN
--Begin EMI Type
IF (pi_emitype='FIXED EMI') THEN
--Begin Loop
FOR i IN 1 .. pi_tenure
LOOP
--EMI AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','EMI') INTO l_emi FROM dual ;
IF i=1 THEN
--INTEREST AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','INTEREST') INTO l_interest FROM dual;
--PRINCIPAL AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','PRINCIPAL') INTO l_principal FROM dual;
--POS OUTSTAINDING AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
ELSE
--INTEREST AMOUNT
SELECT Galaxy_Cc_Calculator(l_Actprncpl,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','INTEREST') INTO l_interest FROM dual;
--PRINCIPAL AMOUNT
SELECT (ROUND(l_emi - l_interest))INTO l_principal FROM dual;
--POS OUTSTAINDING AMOUNT
SELECT (ROUND(l_Actprncpl - (l_emi - l_interest) ))INTO l_Actprncpl FROM dual;
IF i=pi_tenure THEN
l_principal:=l_principal+l_Actprncpl;
l_Actprncpl:=0;
END IF;
END IF;
creation_table.EXTEND;
creation_table(recordcount) := Plms_Object80( i, l_emi,l_interest,l_principal,l_Actprncpl,
NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL);
recordcount:=recordcount +1;
END LOOP;
--End Loop
OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
T1.Param1 "EMI",
T1.Param2 "INTEREST",
T1.Param3 "PRINCIPAL",
T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
END IF;
--End EMI Type
--Begin EQUAL PRINCIPAL EMI Type
IF (pi_emitype='EQUAL PRINCIPAL EMI') THEN
--Begin Loop
FOR i IN 1 .. pi_tenure LOOP
--PRINCIPAL AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EMI') INTO l_emi FROM dual ;
IF i=1 THEN
--INTEREST AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
--EMI AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','CASH FLOW') INTO l_principal FROM dual;
--POS OUTSTAINDING AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
ELSE
--INTEREST AMOUNT
SELECT Galaxy_Cc_Calculator(l_Actprncpl,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
--EMI AMOUNT
SELECT (ROUND(l_emi + l_interest))INTO l_principal FROM dual;
--POS OUTSTAINDING AMOUNT
SELECT (ROUND(l_Actprncpl - (l_emi) ))INTO l_Actprncpl FROM dual;
IF i=pi_tenure THEN
l_emi:=l_emi - (ABS(l_Actprncpl));
l_principal:=l_principal+l_Actprncpl;
l_Actprncpl:=0;
END IF;
END IF;
creation_table.EXTEND;
creation_table(recordcount) := Plms_Object80(i, l_emi,l_interest,l_principal,l_Actprncpl,
NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL);
recordcount:=recordcount +1;
END LOOP;
--End Loop
OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
T1.Param1 "PRINCIPAL",
T1.Param2 "INTEREST",
T1.Param3 "EMI",
T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
END IF;
--End EQUAL PRINCIPAL EMI Type
--Begin STAGGERED EMI Type
IF (pi_emitype='STAGGERED EMI') THEN
--Begin Loop
FOR i IN 1 .. pi_tenure LOOP
--PRINCIPAL AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','CASH FLOW') INTO l_principal FROM dual;
IF i=1 THEN
--EMI AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','EMI') INTO l_emi FROM dual ;
--INTEREST AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','INTEREST') INTO l_interest FROM dual;
--POS OUTSTAINDING AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
ELSE
--INTEREST AMOUNT
SELECT Galaxy_Cc_Calculator(l_Actprncpl,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','INTEREST') INTO l_interest FROM dual;
--EMI AMOUNT
SELECT ROUND(l_principal + l_interest )INTO l_emi FROM dual;
--POS OUTSTAINDING AMOUNT
SELECT ROUND(l_Actprncpl-l_principal)INTO l_Actprncpl FROM dual;
IF i=pi_tenure THEN
l_principal:=l_principal - (ABS(l_Actprncpl));
l_Actprncpl:=l_Actprncpl+l_principal;
l_Actprncpl:=0;
END IF;
END IF;
creation_table.EXTEND;
creation_table(recordcount) := Plms_Object80( i, l_emi,l_interest,l_principal,l_Actprncpl,
NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL);
recordcount:=recordcount +1;
END LOOP;
--End Loop
OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
T1.Param1 "EMI",
T1.Param2 "INTEREST",
T1.Param3 "PRINCIPAL",
T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
END IF;
--End STAGGERED EMI Type
select TO_CHAR(0) into po_upfrontcharges from dual;
END IF;
--End IRR Calc Type
--Begin ROI Calc Type
IF (pi_calctype='ROI') THEN
--Begin EMI Type
IF (pi_emitype='FIXED EMI') THEN
--Extra Loop
while(l_roi!= 0)
LOOP
p_amount_array := XIRR_T_AMOUNT_ARRAY();
p_date_array := Xirr_p_date_array();
l_count:=1;
--Begins finding ROI logic 1
IF pi_calctype ='ROI' THEN
IF(pi_charges=0) THEN
l_outflow:=pi_principal;
p_amount_array.extend(pi_tenure+1);
p_date_array.extend(pi_tenure+1);
p_amount_array(l_count):= -l_outflow;
p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
l_count:=2;
ELSE
l_outflow:=pi_principal-pi_charges;
p_amount_array.extend(pi_tenure+1);
p_amount_array(l_count):= -l_outflow;
p_date_array.extend(pi_tenure+1);
p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
l_count:=2;
END IF ;
l_nominal_months:=pi_tenure;
END IF;
SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'mon'),1),'dd-Mon-rrrr') INTO l_date FROM dual;
--END finding ROI logic 1
--Begin Loop
FOR i IN 1 .. pi_tenure
LOOP
--EMI AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'FIXED','IRR','EMI') INTO l_emi FROM dual ;
IF i=1 THEN
--INTEREST AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'FIXED','IRR','INTEREST') INTO l_interest FROM dual;
--PRINCIPAL AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'FIXED','IRR','PRINCIPAL') INTO l_principal FROM dual;
--POS OUTSTAINDING AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'FIXED','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
ELSE
--INTEREST AMOUNT
SELECT Galaxy_Cc_Calculator(l_Actprncpl,l_roi,pi_tenure,pi_repaymentmethod,'FIXED','IRR','INTEREST') INTO l_interest FROM dual;
--PRINCIPAL AMOUNT
SELECT (ROUND(l_emi - l_interest))INTO l_principal FROM dual;
--POS OUTSTAINDING AMOUNT
SELECT (ROUND(l_Actprncpl - (l_emi - l_interest) ))INTO l_Actprncpl FROM dual;
IF i=pi_tenure THEN
l_principal:=l_principal+l_Actprncpl;
l_Actprncpl:=0;
END IF;
END IF;
creation_table.EXTEND;
creation_table(recordcount) := Plms_Object80( i, l_emi,l_interest,l_principal,l_Actprncpl,
NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL);
recordcount:=recordcount +1;
--Logic 2 for Finding ROI
p_date_array(l_count):= TO_DATE(l_date,'dd-Mon-rrrr');
p_amount_array(l_count):= l_emi;
l_count:=l_count+1;
SELECT ADD_MONTHS(TRUNC(TO_DATE(l_date,'dd-Mon-rrrr'),'mon'),1) INTO l_date FROM dual;
--End Logic 2 for finding ROI
END LOOP;
--End Loop
--Logic 3 for Finding ROI
l_guess:=0.1;
SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
IF (pi_IRR = l_nominal_value) THEN
-- l_outflow:=TO_CHAR(l_nominal_value);
select TO_CHAR(l_roi) into po_upfrontcharges from dual;
OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
T1.Param1 "EMI",
T1.Param2 "INTEREST",
T1.Param3 "PRINCIPAL",
T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
GOTO gotoEndLoop;
ELSE
FOR i IN 1 .. recordcount
LOOP
creation_table.DELETE(i);
END LOOP;
END IF;
l_roi:=l_roi+1;
--Logic 3 for Finding ROI
END LOOP;
---End Extra Loop
END IF;
--End Fixed EMI Type
--Begin EQUAL PRINCIPAL EMI Type
IF (pi_emitype='EQUAL PRINCIPAL EMI') THEN
--Begin Extra Loop
WHILE(l_roi!= 0)
LOOP
p_amount_array := XIRR_T_AMOUNT_ARRAY();
p_date_array := Xirr_p_date_array();
l_count:=1;
--Begins finding ROI logic 1
IF pi_calctype ='ROI' THEN
IF(pi_charges=0) THEN
l_outflow:=pi_principal;
p_amount_array.extend(pi_tenure+1);
p_date_array.extend(pi_tenure+1);
p_amount_array(l_count):= -l_outflow;
p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
l_count:=2;
ELSE
l_outflow:=pi_principal-pi_charges;
p_amount_array.extend(pi_tenure+1);
p_amount_array(l_count):= -l_outflow;
p_date_array.extend(pi_tenure+1);
p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
l_count:=2;
END IF ;
l_nominal_months:=pi_tenure;
END IF;
SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'mon'),1),'dd-Mon-rrrr') INTO l_date FROM dual;
--END finding ROI logic 1
--Begin Loop
--Principal
SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EMI') INTO l_emi FROM dual ;
FOR i IN 1 .. pi_tenure LOOP
IF i=1 THEN
--Interest
SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
--EMI AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','CASH FLOW') INTO l_principal FROM dual;
--POS OUTSTAINDING
SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
ELSE
--Interest
SELECT Galaxy_Cc_Calculator(l_Actprncpl,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
--EMI AMOUNT (PRINCIPAL+INTEREST)
SELECT (ROUND(l_emi + l_interest))INTO l_principal FROM dual;
--POS OUTSTAINDING(--POS OUTSTANDING-PRINCIPAL)
SELECT (ROUND(l_Actprncpl - (l_emi) ))INTO l_Actprncpl FROM dual;
--FOR LAST ROW TO MATCH THE --POS OUTSTANDING
IF i=pi_tenure THEN
l_emi:=l_emi - (ABS(l_Actprncpl));
l_principal:=l_principal+l_Actprncpl;
l_Actprncpl:=0;
END IF;
END IF;
creation_table.EXTEND;
creation_table(recordcount) := Plms_Object80(i, l_emi,l_interest,l_principal,l_Actprncpl,
NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL);
recordcount:=recordcount +1;
--Logic 2 for Finding ROI
p_date_array(l_count):= TO_DATE(l_date,'dd-Mon-rrrr');
p_amount_array(l_count):= l_principal/*(EMI AMOUNT)*/;
l_count:=l_count+1;
SELECT ADD_MONTHS(TRUNC(TO_DATE(l_date,'dd-Mon-rrrr'),'mon'),1) INTO l_date FROM dual;
--End Logic 2 for finding ROI
END LOOP;
--End Loop
l_guess:=0.1;
SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
IF (pi_IRR = l_nominal_value) THEN
SELECT TO_CHAR(l_roi) INTO po_upfrontcharges FROM dual;
OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
T1.Param1 "PRINCIPAL",
T1.Param2 "INTEREST",
T1.Param3 "EMI",
T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
l_roi:=0;
GOTO gotoEndLoop;
ELSE
FOR i IN 1 .. recordcount
LOOP
creation_table.DELETE(i);
END LOOP;
l_roi:=l_roi+1;
END IF;
END LOOP;
---End Extra Loop
END IF;
--End EQUAL PRINCIPAL EMI Type
--Begin STAGGERED EMI Type
IF (pi_emitype='STAGGERED EMI') THEN
--Begin Extra Loop
WHILE(l_roi!= 0)
LOOP
p_amount_array := XIRR_T_AMOUNT_ARRAY();
p_date_array := Xirr_p_date_array();
l_count:=1;
--Begins finding ROI logic 1
IF pi_calctype ='ROI' THEN
IF(pi_charges=0) THEN
l_outflow:=pi_principal;
p_amount_array.extend(pi_tenure+1);
p_date_array.extend(pi_tenure+1);
p_amount_array(l_count):= -l_outflow;
p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
l_count:=2;
ELSE
l_outflow:=pi_principal-pi_charges;
p_amount_array.extend(pi_tenure+1);
p_amount_array(l_count):= -l_outflow;
p_date_array.extend(pi_tenure+1);
p_date_array(l_count):= TO_DATE('01-Jan-2015','dd-Mon-rrrr');
l_count:=2;
END IF ;
l_nominal_months:=pi_tenure;
END IF;
SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'mon'),1),'dd-Mon-rrrr') INTO l_date FROM dual;
--END finding ROI logic 1
--Begin Loop
--Principal
SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EMI') INTO l_emi FROM dual ;
FOR i IN 1 .. pi_tenure LOOP
IF i=1 THEN
--Interest
SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
--EMI AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','CASH FLOW') INTO l_principal FROM dual;
--POS OUTSTAINDING
SELECT Galaxy_Cc_Calculator(pi_principal,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
ELSE
--Interest
SELECT Galaxy_Cc_Calculator(l_Actprncpl,l_roi,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
--EMI AMOUNT (PRINCIPAL+INTEREST)
SELECT (ROUND(l_emi + l_interest))INTO l_principal FROM dual;
--POS OUTSTAINDING(--POS OUTSTANDING-PRINCIPAL)
SELECT (ROUND(l_Actprncpl - (l_emi) ))INTO l_Actprncpl FROM dual;
--FOR LAST ROW TO MATCH THE --POS OUTSTANDING
IF i=pi_tenure THEN
l_principal:=l_principal - (ABS(l_Actprncpl));
l_principal:=l_principal+l_Actprncpl;
l_Actprncpl:=0;
END IF;
END IF;
creation_table.EXTEND;
creation_table(recordcount) := Plms_Object80(i, l_emi,l_interest,l_principal,l_Actprncpl,
NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL);
recordcount:=recordcount +1;
--Logic 2 for Finding ROI
p_date_array(l_count):= TO_DATE(l_date,'dd-Mon-rrrr');
p_amount_array(l_count):= l_principal/*(EMI AMOUNT)*/;
l_count:=l_count+1;
SELECT ADD_MONTHS(TRUNC(TO_DATE(l_date,'dd-Mon-rrrr'),'mon'),1) INTO l_date FROM dual;
--End Logic 2 for finding ROI
END LOOP;
--End Loop
--Logic 3 for Finding ROI
l_guess:=0.1;
SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
IF (pi_IRR = l_nominal_value) THEN
SELECT TO_CHAR(l_roi) INTO po_upfrontcharges FROM dual;
OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
T1.Param1 "PRINCIPAL",
T1.Param2 "INTEREST",
T1.Param3 "EMI",
T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
l_roi:=0;
GOTO gotoEndLoop;
ELSE
FOR i IN 1 .. recordcount
LOOP
creation_table.DELETE(i);
END LOOP;
l_roi:=l_roi+1;
END IF;
--End Logic 3 for finding ROI
END LOOP;
---End Extra Loop
END IF;
--End STAGGERED EMI Type
END IF;
--End ROI Calc Type
--Begin If Calc Type is UPFRONT
IF (pi_calctype='UPFRONT') THEN
--Begin EMI Type
IF (pi_emitype='FIXED EMI') THEN
--Begin Loop
FOR i IN 1 .. pi_tenure
LOOP
--EMI AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','EMI') INTO l_emi FROM dual ;
IF i=1 THEN
--INTEREST AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','INTEREST') INTO l_interest FROM dual;
--PRINCIPAL AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','PRINCIPAL') INTO l_principal FROM dual;
--POS OUTSTAINDING(--POS OUTSTANDING-PRINCIPAL)
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
ELSE
--INTEREST AMOUNT
SELECT Galaxy_Cc_Calculator(l_Actprncpl,pi_interest,pi_tenure,pi_repaymentmethod,'FIXED','IRR','INTEREST') INTO l_interest FROM dual;
--PRINCIPAL AMOUNT
SELECT (ROUND(l_emi - l_interest))INTO l_principal FROM dual;
--POS OUTSTAINDING(--POS OUTSTANDING-PRINCIPAL)
SELECT (ROUND(l_Actprncpl - (l_emi - l_interest) ))INTO l_Actprncpl FROM dual;
IF i=pi_tenure THEN
l_principal:=l_principal+l_Actprncpl;
l_Actprncpl:=0;
END IF;
END IF;
creation_table.EXTEND;
creation_table(recordcount) := Plms_Object80( i, l_emi,l_interest,l_principal,l_Actprncpl,
NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL);
recordcount:=recordcount +1;
--Begins finding Upfront Charges logic 2
p_date_array(l_count):= TO_DATE(l_date,'dd-Mon-rrrr');
p_amount_array(l_count):= l_emi;
l_count:=l_count+1;
SELECT ADD_MONTHS(TRUNC(TO_DATE(l_date,'dd-Mon-rrrr'),'mon'),1) INTO l_date FROM dual;
END LOOP;
--End finding Upfront Charges logic 2
--Begins finding Upfront Charges logic 3
l_guess:=0.1;
SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
l_outflow := 0;
IF (pi_IRR=l_nominal_value) THEN
--terminate the loop
po_message:='SUCCESS';
ELSIF (pi_IRR > l_nominal_value) THEN
-- increase the outflow
l_outflow := l_outflow+pi_principal*.025;
WHILE ROUND(pi_IRR,0)> ROUND(l_nominal_value,0)
LOOP
l_outflow := l_outflow+pi_principal*.025;
p_amount_array(1):=-l_outflow;
SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
l_counter:=l_counter+1;
IF l_counter = 100000 THEN
GOTO gotoEndLoop;
END IF;
END LOOP;
ELSIF (pi_IRR < l_nominal_value) THEN
-- decrease the outflow
l_outflow:=0;
l_outflow := l_outflow+(pi_principal-(pi_principal*.3));
WHILE ROUND(pi_IRR,0)< ROUND(l_nominal_value,0)
LOOP
l_outflow := l_outflow+(pi_principal*.0005);
p_amount_array(1):= l_outflow*(-1);
l_xirr_value:=0;
SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
l_counter:=l_counter+1;
IF l_counter = 100000 THEN
GOTO gotoEndLoop;
END IF;
END LOOP;
END IF;
--Begins finding Upfront Charges logic 3
--End Loop
OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
T1.Param1 "EMI",
T1.Param2 "INTEREST",
T1.Param3 "PRINCIPAL",
T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
select TO_CHAR(pi_principal-l_outflow) into po_upfrontcharges from dual;
END IF;
--End EMI Type
--Begin EQUAL PRINCIPAL EMI Type
IF (pi_emitype='EQUAL PRINCIPAL EMI') THEN
--Begin Loop
FOR i IN 1 .. pi_tenure LOOP
--PRINCIPAL AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EMI') INTO l_emi FROM dual ;
IF i=1 THEN
--INTEREST AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
--EMI AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','CASH FLOW') INTO l_principal FROM dual;
--POS OUTSTAINDING(--POS OUTSTANDING-PRINCIPAL)
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
ELSE
--INTEREST AMOUNT
SELECT Galaxy_Cc_Calculator(l_Actprncpl,pi_interest,pi_tenure,pi_repaymentmethod,'EQUAL PRINCIPAL','IRR','INTEREST') INTO l_interest FROM dual;
--EMI AMOUNT
SELECT (ROUND(l_emi + l_interest))INTO l_principal FROM dual;
--POS OUTSTAINDING(--POS OUTSTANDING-PRINCIPAL)
SELECT (ROUND(l_Actprncpl - (l_emi) ))INTO l_Actprncpl FROM dual;
IF i=pi_tenure THEN
l_emi:=l_emi - (ABS(l_Actprncpl));
l_principal:=l_principal+l_Actprncpl;
l_Actprncpl:=0;
END IF;
END IF;
creation_table.EXTEND;
creation_table(recordcount) := Plms_Object80(i, l_emi,l_interest,l_principal,l_Actprncpl,
NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL);
recordcount:=recordcount +1;
--Begins finding Upfront Charges logic 2
p_date_array(l_count):= TO_DATE(l_date,'dd-Mon-rrrr');
p_amount_array(l_count):= l_emi;
l_count:=l_count+1;
SELECT ADD_MONTHS(TRUNC(TO_DATE(l_date,'dd-Mon-rrrr'),'mon'),1) INTO l_date FROM dual;
END LOOP;
--End finding Upfront Charges logic 2
--Begins finding Upfront Charges logic 3
l_guess:=0.1;
SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
l_outflow := 0;
IF (pi_IRR=l_nominal_value) THEN
--terminate the loop
po_message:='SUCCESS';
ELSIF (pi_IRR > l_nominal_value) THEN
-- increase the outflow
l_outflow := l_outflow+pi_principal*.025;
WHILE ROUND(pi_IRR,0)>=ROUND(l_nominal_value,0)
LOOP
l_outflow := l_outflow+pi_principal*.025;
p_amount_array(1):=-l_outflow;
SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
l_counter:=l_counter+1;
IF l_counter = 100000 THEN
GOTO gotoEndLoop;
END IF;
END LOOP;
ELSIF (pi_IRR < l_nominal_value) THEN
-- decrease the outflow
l_outflow:=0;
l_outflow := l_outflow+(pi_principal-(pi_principal*.3));
WHILE ROUND(pi_IRR,0)<=ROUND(l_nominal_value,0)
LOOP
l_outflow := l_outflow+(pi_principal*.0005);
p_amount_array(1):= l_outflow*(-1);
l_xirr_value:=0;
SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
l_counter:=l_counter+1;
IF l_counter = 100000 THEN
GOTO gotoEndLoop;
END IF;
END LOOP;
END IF;
--Begins finding Upfront Charges logic 3
--End Loop
OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
T1.Param1 "PRINCIPAL",
T1.Param2 "INTEREST",
T1.Param3 "EMI",
T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
select TO_CHAR(pi_principal-l_outflow) into po_upfrontcharges from dual;
END IF;
--End EQUAL PRINCIPAL EMI Type
--STAGGERED EMI Type
IF (pi_emitype='STAGGERED EMI') THEN
--Begin Loop
FOR i IN 1 .. pi_tenure LOOP
--PRINCIPAL AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','CASH FLOW') INTO l_principal FROM dual;
IF i=1 THEN
--EMI AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','EMI') INTO l_emi FROM dual ;
--INTEREST AMOUNT
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','INTEREST') INTO l_interest FROM dual;
--POS OUTSTAINDING(--POS OUTSTANDING-PRINCIPAL)
SELECT Galaxy_Cc_Calculator(pi_principal,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','EVALUATED PRINCIPAL') INTO l_Actprncpl FROM dual;
ELSE    --INTEREST AMOUNT
SELECT Galaxy_Cc_Calculator(l_Actprncpl,pi_interest,pi_tenure,pi_repaymentmethod,'STAGGERED EMI','IRR','INTEREST') INTO l_interest FROM dual;
--EMI AMOUNT
SELECT ROUND(l_principal + l_interest )INTO l_emi FROM dual;
--POS OUTSTAINDING(--POS OUTSTANDING-PRINCIPAL)
SELECT ROUND(l_Actprncpl-l_principal)INTO l_Actprncpl FROM dual;
IF i=pi_tenure THEN
l_principal:=l_principal - (ABS(l_Actprncpl));
l_Actprncpl:=l_Actprncpl+l_principal;
l_Actprncpl:=0;
END IF;
END IF;
creation_table.EXTEND;
creation_table(recordcount) := Plms_Object80( i, l_emi,l_interest,l_principal,l_Actprncpl,
NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL);
recordcount:=recordcount +1;
--Begins finding Upfront Charges logic 2
p_date_array(l_count):= TO_DATE(l_date,'dd-Mon-rrrr');
p_amount_array(l_count):= l_emi;
l_count:=l_count+1;
SELECT ADD_MONTHS(TRUNC(TO_DATE(l_date,'dd-Mon-rrrr'),'mon'),1) INTO l_date FROM dual;
END LOOP;
--End finding Upfront Charges logic 2
--Begins finding Upfront Charges logic 3
l_guess:=0.1;
SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
l_outflow := 0;
IF (pi_IRR=l_nominal_value) THEN
--terminate the loop
po_message:='SUCCESS';
ELSIF (pi_IRR > l_nominal_value) THEN
-- increase the outflow
l_outflow := l_outflow+pi_principal*.025;
WHILE ROUND(pi_IRR,0)>=ROUND(l_nominal_value,0)
LOOP
l_outflow := l_outflow+pi_principal*.025;
p_amount_array(1):=-l_outflow;
SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
l_counter:=l_counter+1;
IF l_counter = 100000 THEN
GOTO gotoEndLoop;
END IF;
END LOOP;
ELSIF (pi_IRR < l_nominal_value) THEN
-- decrease the outflow
l_outflow:=0;
l_outflow := l_outflow+(pi_principal-(pi_principal*.3));
WHILE ROUND(pi_IRR,0)<=ROUND(l_nominal_value,0)
LOOP
l_outflow := l_outflow+(pi_principal*.0005);
p_amount_array(1):= l_outflow*(-1);
l_xirr_value:=0;
SELECT Galaxy_Cc_Xirr(p_date_array, p_amount_array,l_guess) INTO l_xirr_value FROM dual;
SELECT Galaxy_Cc_Xirr_Nominal(l_xirr_value,l_nominal_months) INTO l_nominal_value FROM dual;
l_counter:=l_counter+1;
IF l_counter = 100000 THEN
GOTO gotoEndLoop;
END IF;
END LOOP;
END IF;
--Begins finding Upfront Charges logic 3
--End Loop
OPEN Po_Cursor FOR SELECT T1.Param0 "S.NO",
T1.Param1 "EMI",
T1.Param2 "INTEREST",
T1.Param3 "PRINCIPAL",
T1.Param4 "LOAN AMOUNT (POS)"FROM TABLE(creation_table) T1;
select TO_CHAR(pi_principal-l_outflow) into po_upfrontcharges from dual;
END IF;
--End EQUAL PRINCIPAL EMI Type
END IF;
--End Calc Type
--End If Calc Type is UPFRONT
<<gotoEndLoop>>
po_message := 'SUCCESS';
EXCEPTION
WHEN OTHERS THEN
po_message := 'Error' || SUBSTR(SQLERRM, 1, 100);
ROLLBACK;
Plms_Errorslog('Galaxy_Cc_Get_Fixed_Emi',po_message,pi_userid,NULL,NULL,po_message );
IF(po_message <>'SUCCESS') THEN
po_message :='Error in ErrorLog Proc and ' || po_message;
ROLLBACK;
RETURN;
END IF;
END;

And call the function in Procedure.Get all the detail in well way.