Below is the SQL package which is going to be a part of a script. In this package the cursor used is fetching sufficient records as required, but not working when I am incorporating in this package. On changing the cursor while putting less where conditions, it works just fine. But somehow this is the correct cursor.
SET serveroutput ON;
CREATE OR REPLACE PACKAGE CUSTOM.FINPACK_WELDUMMY AS
PROCEDURE FINPROC_WELDUMMY(inp_str VARCHAR2,
out_retCode OUT NUMBER,
out_rec OUT VARCHAR2);
END FINPACK_WELDUMMY;
/
CREATE OR REPLACE PACKAGE BODY CUSTOM.FINPACK_WELDUMMY AS
-------------------------------------------------------------------
-- Declaring the variables to be used in the Procedure
-------------------------------------------------------------------
outArr TBAADM.basp0099.ArrayType;
v_bodDate DATE;
v_bank_id TBAADM.GAM.BANK_ID%type;
v_acid VARCHAR2(11 CHAR);
v_lacid VARCHAR2(11 CHAR);
v_cif_id VARCHAR2(50 CHAR);
v_acct_cls_flg VARCHAR2(1);
v_schm_code VARCHAR2(20);
v_address_id NUMBER(38);
v_count VARCHAR2(3 CHAR);
v_advance_instl NUMBER(20,4);
v_agreement_no VARCHAR2(200);
v_amtfin NUMBER(20,4);
v_tenure NUMBER(3);
v_title NVARCHAR2(75);
v_fname NVARCHAR2(120);
v_mname NVARCHAR2(120);
v_lname NVARCHAR2(150);
v_address1 NVARCHAR2(200 CHAR);
v_address2 NVARCHAR2(200 CHAR);
v_address3 NVARCHAR2(200 CHAR);
v_address4 VARCHAR2(80 CHAR);
v_landmark VARCHAR2(100);
v_city NVARCHAR2(200 CHAR);
v_statedesc NVARCHAR2(200 CHAR);
v_zipcode NVARCHAR2(100 CHAR);
v_branchdesc VARCHAR2(132 CHAR);
v_instl_num NUMBER(3);
v_due_date DATE;
v_instl_amt NUMBER(20,4);
v_dmd_amt NUMBER(20,2);
v_dflt_principal_flow_id VARCHAR2(100);
v_acct_crncy_code VARCHAR2(3 CHAR);
v_princomp NUMBER(20,4);
v_intcomp NUMBER(20,4);
v_endbal NUMBER(20,2);
v_repaymode CHAR(3);
v_effrate VARCHAR2(100);
v_promotionid VARCHAR2(200);
v_cust_segment_code VARCHAR2(200);
v_cust_service_code VARCHAR2(200);
v_product_pre_fix VARCHAR2(200);
v_dob VARCHAR2(200);
v_product VARCHAR2(16 CHAR);
v_vendor VARCHAR2(200);
v_pre_printed_stationary VARCHAR2(200);
v_file_attchment_name VARCHAR2(200);
v_psw VARCHAR2(200);
v_identifier VARCHAR2(200);
v_identifier2 VARCHAR2(200);
v_amort_printing VARCHAR2(200);
v_month VARCHAR2(200);
v_premi1 VARCHAR2(200);
v_preemi2 VARCHAR2(200);
v_disbursed_amount VARCHAR2(200);
v_awb_no VARCHAR2(200);
v_email NVARCHAR2(75);
v_area VARCHAR2(200);
v_sc_code VARCHAR2(200);
v_mobile_no NVARCHAR2(37.5);
v_sol_id VARCHAR2(8 CHAR);
-------------------------------------------------------------------
--Cursor declaration
--This cursor will fetch all the LOAN accounts.
-------------------------------------------------------------------
CURSOR getCustDetail
(
v_bodDate DATE,
v_bank_id TBAADM.GAM.BANK_ID%TYPE
) IS
SELECT A.ACID, A.TRAN_DATE, B.SRL_NUM, B.INTEREST_AMOUNT, B.SHDL_BALANCE, C.DFLT_PRINCIPAL_FLOW_ID, D.SOL_ID, D.ACCT_CLS_FLG, D.SCHM_CODE, D.ACID, D.ACCT_CRNCY_CODE, D.CIF_ID
FROM TBAADM.LTD A, TBAADM.LSBT B, TBAADM.LSP C, TBAADM.GAM D
WHERE A.TRAN_DATE = TO_DATE(v_bodDate,'DD-MM-YYYY') - 1
AND D.ACID = A.ACID
AND B.ACID = A.ACID
AND A.FLOW_ID = C.DFLT_DISBMNT_FLOW_ID
AND D.SCHM_CODE = C.SCHM_CODE
AND A.BANK_ID = D.BANK_ID AND B.BANK_ID = D.BANK_ID
AND C.BANK_ID = D.BANK_ID
AND A.DEL_FLG = 'N' AND C.DEL_FLG = 'N'
AND D.DEL_FLG = 'N'
AND D.SCHM_TYPE = 'LAA'
AND A.ENTITY_CRE_FLG = 'Y'
AND D.ENTITY_CRE_FLG = 'Y';
-----------------------------------------------------------
------------------ Procedure Body 1------------------------
-----------------------------------------------------------
PROCEDURE FINPROC_WELDUMMY(inp_str IN VARCHAR2,
out_retCode OUT NUMBER,
out_rec OUT VARCHAR2) AS
BEGIN
-- Your existing code goes here
out_rec := NULL;
out_retCode := 0;
TBAADM.basp0099.formInputArr (inp_str, OutArr);
v_bodDate := TO_DATE(OutArr(0),'DD-MM-YYYY');
v_bank_id := OutArr(1);
IF(NOT getCustDetail%ISOPEN) THEN
--{
OPEN getCustDetail(v_bodDate,v_bank_id);
--}
END IF;
IF getCustDetail%ISOPEN THEN
--{
FETCH getCustDetail INTO v_lacid, v_due_date, v_count, v_intcomp, v_princomp, v_dflt_principal_flow_id, v_sol_id, v_acct_cls_flg, v_schm_code, v_acid, v_acct_crncy_code, v_cif_id;
--}
END IF;
IF getCustDetail%NOTFOUND THEN
--{
CLOSE getCustDetail;
out_retCode := 1;
DBMS_OUTPUT.PUT_LINE('out_retCode: ' || out_retCode);
RETURN;
--}
END IF;
--------------------------------------------------------
-- FECTHING ACCOUNT DETAILS AFTER CURSOR
--------------------------------------------------------
BEGIN
SELECT SOL_DESC
INTO v_branchdesc
FROM TBAADM.SOL
WHERE SOL_ID = v_sol_id
AND DEL_FLG = 'N'
AND BANK_ID = v_bank_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_branchdesc := NULL;
END;
BEGIN
SELECT
nvl(
abs(INTEREST_RATE), 0
)
INTO
v_effrate
FROM
TBAADM.EIT
WHERE
ENTITY_ID = v_acid
AND BANK_ID = v_bank_id
AND ENTITY_TYPE = 'ACCNT';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_effrate := NULL;
END;
BEGIN
SELECT
nvl(
SUM(DMD_AMT), 0
)
INTO v_dmd_amt
FROM
TBAADM.LDT
WHERE
ACID = v_acid
AND BANK_ID = v_bank_id
AND DMD_FLOW_ID = v_dflt_principal_flow_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_dmd_amt := NULL;
END;
BEGIN
SELECT UPFRONT_INSTL_INT_AMT, DIS_AMT
INTO v_advance_instl, v_amtfin
FROM TBAADM.LAM
WHERE ACID = v_acid
AND DEL_FLG = 'N'
AND ENTITY_CRE_FLG = 'Y'
AND BANK_ID = v_bank_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_advance_instl := NULL;
v_amtfin := NULL;
END;
BEGIN
SELECT NUM_OF_DMDS, FLOW_AMT, NUM_OF_FLOWS
INTO v_instl_num, v_instl_amt, v_tenure
FROM TBAADM.LRS
WHERE ACID = v_acid
AND DEL_FLG = 'N'
AND BANK_ID = v_bank_id
AND FLOW_ID = v_dflt_principal_flow_id
AND ENTITY_CRE_FLG = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_instl_num := NULL;
v_instl_amt := NULL;
v_tenure := NULL;
END;
BEGIN
SELECT A.SEGMENTATION_CLASS, A.SUBSEGMENT, B.SALUTATION, B.CUST_FIRST_NAME, B.CUST_MIDDLE_NAME, B.CUST_LAST_NAME, B.EMAIL, B.PHONE_CELL, B.CUST_DOB
INTO v_cust_segment_code,v_cust_service_code, v_title, v_fname, v_mname, v_lname, v_email, v_mobile_no, v_dob
FROM CRMUSER.PSYCHOGRAPHIC A,CRMUSER.ACCOUNTS B
WHERE A.ACCOUNTID = B.ACCOUNTID
AND A.BANK_ID = v_bank_id AND B.BANK_ID = v_bank_id
AND A.ORGKEY = v_cif_id AND B.ORGKEY = v_cif_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_cust_segment_code := 'NA';
v_cust_service_code := 'NA';
v_title := 'NA';
v_fname := 'NA';
v_mname := 'NA';
v_lname := 'NA';
v_email := 'NA';
v_mobile_no := 'NA';
v_dob := 'NA';
END;
BEGIN
SELECT ADDRESS_LINE1,ADDRESS_LINE2, ADDRESS_LINE3, CITY, STATE, ZIP
INTO v_address1, v_address2, v_address3, v_city, v_statedesc, v_zipcode
FROM CRMUSER.ADDRESS
WHERE ACCOUNTID = v_acid
AND BANK_ID = v_bank_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_address1 := 'NA';
v_address2 := 'NA';
v_address3 := 'NA';
v_city := 'NA';
v_statedesc := NULL;
v_zipcode := NULL;
END;
BEGIN
v_address4 := NULL;
v_landmark := NULL;
v_vendor := NULL;
v_pre_printed_stationary := NULL;
v_file_attchment_name := NULL;
v_psw := NULL;
v_identifier := NULL;
v_month := NULL;
v_amort_printing := 'Y';
v_premi1 := 'NA';
v_preemi2 :='NA';
v_disbursed_amount :='NA';
v_awb_no :='NA';
v_area :='NA';
v_sc_code :='NA';
END;
out_rec := v_count || '|' || v_advance_instl || '|' || v_agreement_no || '|' || v_amtfin || '|' || v_tenure || '|' || v_title || '|' || v_fname || '|' || v_mname || '|' || v_lname || '|' || v_address1 || '|' || v_address2 || '|' || v_address3 || '|' || v_address4 || '|' || v_landmark || '|' || v_city || '|' || v_statedesc || '|' || v_zipcode || '|' || v_branchdesc || '|' || v_instl_num || '|' || v_due_date || '|' || v_instl_amt || '|' || v_dmd_amt || '|' || v_dflt_principal_flow_id || '|' || v_princomp || '|' || v_intcomp || '|' || v_endbal || '|' || v_repaymode || '|' || v_effrate || '|' || v_promotionid || '|' || v_cust_segment_code || '|' || v_cust_service_code || '|' || v_product_pre_fix || '|' || v_dob || '|' || v_product|| '|' || v_vendor || '|' || v_pre_printed_stationary || '|' || v_file_attchment_name || '|' || v_psw || '|' || v_identifier || '|' || v_identifier2 || '|' || v_amort_printing || '|' || v_month || '|' || v_premi1 || '|' || v_preemi2 || '|' || v_disbursed_amount || '|' || v_awb_no || '|' || v_email
|| '|' || v_area || '|' || v_sc_code || '|' || v_mobile_no || '|' || v_sol_id;
END FINPROC_WELDUMMY;
END FINPACK_WELDUMMY;
/
-------------------------------------------------------
-- Execution grants are given to Tbacust Tbautil Tbagen
-------------------------------------------------------
/
DROP SYNONYM CUSTOM.C_FINPACK_WELDUMMY;
/
CREATE SYNONYM CUSTOM.C_FINPACK_WELDUMMY FOR CUSTOM.FINPACK_WELDUMMY;
/
GRANT EXECUTE ON CUSTOM.FINPACK_WELDUMMY TO TBAGEN,TBAUTIL,TBAADM;
/
SHOW ERRORS;
I could not refine the cursor more because my senior has asked me to keep it like this only.
2
You are declaring the variables and cursor in the package and not in the procedure within the package. Move all the variable and cursor declarations into the procedure (between the AS and BEGIN keywords).