Home » SQL & PL/SQL » SQL & PL/SQL » is there any better way of doing this (oracle 10g)
is there any better way of doing this [message #287784] Thu, 13 December 2007 08:00 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi,

The below procedure is used to return 3 result sets(ref cursors) and one PROC_STATUS_OUT ref cursor
which is used to check whether the procedure success or fail.
And this code is working absolutly fine but Please let me know is there any other alter native way ?

CREATE OR REPLACE PACKAGE BODY HLDNG_INQRY_PKG IS

PROCEDURE HLDNG_INQRY_PROC(PLCY_NBR_IN IN VARCHAR2,
PLCY_INFO_OUT OUT SYS_REFCURSOR,
RIDER_INFO_OUT OUT SYS_REFCURSOR,
PARTY_INFO_OUT OUT SYS_REFCURSOR,
PROC_STATUS_OUT OUT SYS_REFCURSOR) IS
-- Declare Exceptions
NO_DATA_FOUND_EXCEPTION EXCEPTION;
MULTIPLE_PLCY_EXIST_EXCEPTION EXCEPTION;
INTERNAL_EXCEPTION EXCEPTION;

-- Declare Local variables
V_PLCY_CNT NUMBER DEFAULT 0;
V_PROC_STATUS_CD VARCHAR2(1);
V_PROC_ERROR_DESC VARCHAR2(500);

--- Table Types
PLCY_INFO_TAB HLDNG_INQRY_PLCY_INFO_TBL_TYPE := HLDNG_INQRY_PLCY_INFO_TBL_TYPE();
RIDER_INFO_TAB HLDNG_INQRY_RIDR_INFO_TBL_TYPE := HLDNG_INQRY_RIDR_INFO_TBL_TYPE();
PARTY_INFO_TAB HLDNG_INQRY_PRTY_INFO_TBL_TYPE := HLDNG_INQRY_PRTY_INFO_TBL_TYPE();
PROC_STATUS_TAB PACKAGE_STATUS_INF_TBL_TYPE := PACKAGE_STATUS_INF_TBL_TYPE();

BEGIN
-- Check for Policy, if it exists and is Only One Policy
SELECT COUNT(DISTINCT PLCY_NBR || CO_CD) INTO V_PLCY_CNT FROM COV_DIM WHERE PLCY_NBR = PLCY_NBR_IN;

IF (V_PLCY_CNT = 1) THEN
-- Call GET_PLCY_INFO_PROC to populate Policy Information
GET_PLCY_INFO_PROC(PLCY_NBR_IN, PLCY_INFO_TAB, V_PROC_STATUS_CD, V_PROC_ERROR_DESC);

IF (V_PROC_STATUS_CD = 'S') THEN
-- Call GET_PARTY_INFO_PROC to populate Party Information
GET_PARTY_INFO_PROC(PLCY_NBR_IN, PARTY_INFO_TAB, V_PROC_STATUS_CD, V_PROC_ERROR_DESC);

IF (V_PROC_STATUS_CD = 'S') THEN
-- Call GET_RIDER_INFO_PROC to populate Party Information
GET_RIDER_INFO_PROC(PLCY_NBR_IN, RIDER_INFO_TAB, V_PROC_STATUS_CD, V_PROC_ERROR_DESC);

IF (V_PROC_STATUS_CD = 'S') THEN
-- Set the reference cursors for the POlicy, Party and Riders Information
OPEN PLCY_INFO_OUT FOR
SELECT * FROM TABLE(CAST(PLCY_INFO_TAB AS HLDNG_INQRY_PLCY_INFO_TBL_TYPE));

OPEN PARTY_INFO_OUT FOR
SELECT * FROM TABLE(CAST(PARTY_INFO_TAB AS HLDNG_INQRY_PRTY_INFO_TBL_TYPE));

OPEN RIDER_INFO_OUT FOR
SELECT * FROM TABLE(CAST(RIDER_INFO_TAB AS HLDNG_INQRY_RIDR_INFO_TBL_TYPE));

V_PROC_STATUS_CD := 'S';

PROC_STATUS_TAB.EXTEND;
PROC_STATUS_TAB(1) := PACKAGE_STATUS_INF_OBJ_TYPE(V_PROC_STATUS_CD,
V_PROC_ERROR_DESC,
V_PLCY_CNT);

OPEN PROC_STATUS_OUT FOR
SELECT * FROM TABLE(CAST(PROC_STATUS_TAB AS PACKAGE_STATUS_INF_TBL_TYPE));

ELSE
RAISE INTERNAL_EXCEPTION;
END IF;
ELSE
RAISE INTERNAL_EXCEPTION;
END IF;
ELSE
RAISE INTERNAL_EXCEPTION;
END IF;

ELSIF (V_PLCY_CNT = 0) THEN

RAISE NO_DATA_FOUND_EXCEPTION;
ELSE

RAISE MULTIPLE_PLCY_EXIST_EXCEPTION;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND_EXCEPTION THEN

V_PROC_STATUS_CD := 'N';
V_PROC_ERROR_DESC := 'No Data Found for Policy Number : ' || PLCY_NBR_IN;
-- Set the reference cursors for the POlicy, Party and Riders Information
OPEN PLCY_INFO_OUT FOR
SELECT * FROM TABLE(CAST(PLCY_INFO_TAB AS HLDNG_INQRY_PLCY_INFO_TBL_TYPE));
OPEN PARTY_INFO_OUT FOR
SELECT * FROM TABLE(CAST(PARTY_INFO_TAB AS HLDNG_INQRY_PRTY_INFO_TBL_TYPE));
OPEN RIDER_INFO_OUT FOR
SELECT * FROM TABLE(CAST(RIDER_INFO_TAB AS HLDNG_INQRY_RIDR_INFO_TBL_TYPE));

PROC_STATUS_TAB.EXTEND;
PROC_STATUS_TAB(1) := PACKAGE_STATUS_INF_OBJ_TYPE(V_PROC_STATUS_CD, V_PROC_ERROR_DESC, V_PLCY_CNT);

OPEN PROC_STATUS_OUT FOR
SELECT * FROM TABLE(CAST(PROC_STATUS_TAB AS PACKAGE_STATUS_INF_TBL_TYPE));

WHEN MULTIPLE_PLCY_EXIST_EXCEPTION THEN

V_PROC_STATUS_CD := 'M';
V_PROC_ERROR_DESC := V_PLCY_CNT || ' Policies exist for Policy Number : ' || PLCY_NBR_IN;

-- Set the reference cursors for the POlicy, Party and Riders Information
OPEN PLCY_INFO_OUT FOR
SELECT * FROM TABLE(CAST(PLCY_INFO_TAB AS HLDNG_INQRY_PLCY_INFO_TBL_TYPE));
OPEN PARTY_INFO_OUT FOR
SELECT * FROM TABLE(CAST(PARTY_INFO_TAB AS HLDNG_INQRY_PRTY_INFO_TBL_TYPE));
OPEN RIDER_INFO_OUT FOR
SELECT * FROM TABLE(CAST(RIDER_INFO_TAB AS HLDNG_INQRY_RIDR_INFO_TBL_TYPE));

PROC_STATUS_TAB.EXTEND;
PROC_STATUS_TAB(1) := PACKAGE_STATUS_INF_OBJ_TYPE(V_PROC_STATUS_CD, V_PROC_ERROR_DESC, V_PLCY_CNT);

OPEN PROC_STATUS_OUT FOR
SELECT * FROM TABLE(CAST(PROC_STATUS_TAB AS PACKAGE_STATUS_INF_TBL_TYPE));

WHEN INTERNAL_EXCEPTION THEN
-- Set the reference cursors for the POlicy, Party and Riders Information
OPEN PLCY_INFO_OUT FOR
SELECT * FROM TABLE(CAST(PLCY_INFO_TAB AS HLDNG_INQRY_PLCY_INFO_TBL_TYPE));
OPEN PARTY_INFO_OUT FOR
SELECT * FROM TABLE(CAST(PARTY_INFO_TAB AS HLDNG_INQRY_PRTY_INFO_TBL_TYPE));
OPEN RIDER_INFO_OUT FOR
SELECT * FROM TABLE(CAST(RIDER_INFO_TAB AS HLDNG_INQRY_RIDR_INFO_TBL_TYPE));

PROC_STATUS_TAB.EXTEND;
PROC_STATUS_TAB(1) := PACKAGE_STATUS_INF_OBJ_TYPE(V_PROC_STATUS_CD, V_PROC_ERROR_DESC, V_PLCY_CNT);

OPEN PROC_STATUS_OUT FOR
SELECT * FROM TABLE(CAST(PROC_STATUS_TAB AS PACKAGE_STATUS_INF_TBL_TYPE));

WHEN OTHERS THEN

V_PROC_STATUS_CD := 'F';
V_PROC_ERROR_DESC := 'Error Occured in HLDNG_INQRY_PROC. SQL Error Code = ' || SQLCODE ||
' Error Msg : ' || SUBSTR(SQLERRM, 1, 400);

-- Set the reference cursors for the POlicy, Party and Riders Information
OPEN PLCY_INFO_OUT FOR
SELECT * FROM TABLE(CAST(PLCY_INFO_TAB AS HLDNG_INQRY_PLCY_INFO_TBL_TYPE));
OPEN PARTY_INFO_OUT FOR
SELECT * FROM TABLE(CAST(PARTY_INFO_TAB AS HLDNG_INQRY_PRTY_INFO_TBL_TYPE));
OPEN RIDER_INFO_OUT FOR
SELECT * FROM TABLE(CAST(RIDER_INFO_TAB AS HLDNG_INQRY_RIDR_INFO_TBL_TYPE));

PROC_STATUS_TAB.EXTEND;
PROC_STATUS_TAB(1) := PACKAGE_STATUS_INF_OBJ_TYPE(V_PROC_STATUS_CD, V_PROC_ERROR_DESC, V_PLCY_CNT);

OPEN PROC_STATUS_OUT FOR
SELECT * FROM TABLE(CAST(PROC_STATUS_TAB AS PACKAGE_STATUS_INF_TBL_TYPE));
END;

PROCEDURE GET_PLCY_INFO_PROC(PLCY_NBR_IN IN VARCHAR2,
PLCY_INFO_TAB IN OUT HLDNG_INQRY_PLCY_INFO_TBL_TYPE,
PROC_STATUS_CD OUT VARCHAR2,
PROC_ERROR_DESC OUT VARCHAR2) IS
-- Get Policy Information
CURSOR PLCY_INFO_CUR IS
SELECT *
FROM (SELECT A.PLCY_NBR,
A.CO_CD,
A.ELMNT_NBR,
DECODE(B.GL_LEGAL_ENTITY_ID,
'202',
'GLIC',
'203',
'GLICNY',
'205',
'GLAIC',
'TRA',
'TRAVELLERS',
B.GL_LEGAL_ENTITY_ID) GL_LEGAL_ENTITY_ID,
PLCY_STATUS_CD || PLCY_TERMNTN_REASN_CD PLCY_STATUS_CD,
C.PRDCT_CATGRY_NAME,
TO_CHAR(A.OUT_OF_FORCE_SET_DT, DATE_FORMATTER) OUT_OF_FORCE_SET_DT,
A.APPLCTN_ST_CD,
TO_CHAR(A.RISK_COMMNCD_DT, DATE_FORMATTER) RISK_COMMNCD_DT,
HI_DATE PLCY_MAILED_DT, -- Need To Implement
DECODE(A.PYMT_FREQ_CD, 'A', 1, 'M', 12, 'Q', 4, 'S', 2, 0) * D.TOTL_PREM_INSMNT_AMT CRRNT_ANNUAL_PREM_AMT,
D.TOTL_PREM_INSMNT_AMT,
A.PYMT_FREQ_CD,
D.PYMT_METHD_CD,
TO_CHAR(A.PD_TO_DT, DATE_FORMATTER) PD_TO_DT,
TO_CHAR(D.PREM_BILLD_TO_DT, DATE_FORMATTER) PREM_BILLD_TO_DT,
C.PLAN_CD,
A.PARTNRSHP_CONVRSN_IND,
A.CONDTL_COV_CD,
HI_DATE LST_PYMT_APL_DT, -- Need To Implement
TO_CHAR(A.APPLCTN_DT, DATE_FORMATTER) APPLCTN_DT,
TO_CHAR(A.HOME_OFFC_RCVD_DT, DATE_FORMATTER) HOME_OFFC_RCVD_DT,
A.CASH_RCVD_AMT,
ROW_NUMBER() OVER(PARTITION BY A.PLCY_NBR, A.CO_CD ORDER BY C.BASIC_RIDER_IND, A.ELMNT_NBR) SEQ_NBR,
0 NEW_BALANCE_AMT -- Need To Implement
FROM COV_DIM A,
LEGAL_ENTITY_DIM B,
PRDCT_DIM C,
CLR_BILLNG_INFORMTN D
WHERE A.PLCY_NBR = PLCY_NBR_IN AND A.CO_CD = B.SRC_CO_CD AND B.SRC_SYSTM_CD = 'C' AND
A.PRDCT_DIM_ID = C.PRDCT_DIM_ID AND C.BASIC_RIDER_IND IN ('B', 'C') -- JUST TAKE THE BASE COVERAGE
AND A.PLCY_NBR = D.PLCY_NBR AND A.CO_CD = D.CO_CD)
WHERE SEQ_NBR = 1;

-- Local variables
V_CNT NUMBER DEFAULT 0;

BEGIN
FOR REC IN PLCY_INFO_CUR
LOOP
V_CNT := V_CNT + 1;
PLCY_INFO_TAB.EXTEND;
PLCY_INFO_TAB(V_CNT) := HLDNG_INQRY_PLCY_INF_OBJ_TYPE(REC.PLCY_NBR,
REC.GL_LEGAL_ENTITY_ID,
REC.PLCY_STATUS_CD,
REC.PRDCT_CATGRY_NAME,
REC.OUT_OF_FORCE_SET_DT,
REC.APPLCTN_ST_CD,
REC.RISK_COMMNCD_DT,
REC.PLCY_MAILED_DT,
REC.CRRNT_ANNUAL_PREM_AMT,
REC.TOTL_PREM_INSMNT_AMT,
REC.PYMT_FREQ_CD,
REC.PYMT_METHD_CD,
REC.PD_TO_DT,
REC.PREM_BILLD_TO_DT,
REC.PLAN_CD,
REC.CO_CD,
REC.PARTNRSHP_CONVRSN_IND,
REC.CONDTL_COV_CD,
REC.LST_PYMT_APL_DT,
REC.APPLCTN_DT,
REC.HOME_OFFC_RCVD_DT,
REC.CASH_RCVD_AMT,
REC.NEW_BALANCE_AMT);
END LOOP;

PROC_STATUS_CD := 'S';

EXCEPTION
WHEN OTHERS THEN
PROC_STATUS_CD := 'F';
PROC_ERROR_DESC := 'Error Occured in GET_PLCY_INFO_PROC. SQL Error Code = ' || SQLCODE ||
' Error Msg : ' || SUBSTR(SQLERRM, 1, 400);
END;

PROCEDURE GET_RIDER_INFO_PROC(PLCY_NBR_IN IN VARCHAR2,
RIDER_INFO_TAB IN OUT HLDNG_INQRY_RIDR_INFO_TBL_TYPE,
PROC_STATUS_CD OUT VARCHAR2,
PROC_ERROR_DESC OUT VARCHAR2) IS

-- Get riders information
CURSOR RIDER_INFO_CUR IS
SELECT A.PLCY_NBR,
A.CO_CD,
A.ELMNT_NBR,
ROW_NUMBER() OVER(PARTITION BY A.PLCY_NBR, A.CO_CD, A.ELMNT_NBR ORDER BY A.PLCY_NBR, A.CO_CD, A.ELMNT_NBR, A.COMMS_SPLIT_NBR) AS COMMS_SPLIT_NBR,
A.CLNT_REF_NBR INSURD_CLNT_REF_NBR,
'I' || A.PRIMRY_SECDRY_LIFE_IND INSURD_ROLE_CD,
C.LAST_NAME || ',' || C.FIRST_NAME || ',' || C.MIDDLE_NAME || ',' || C.SUFFIX_NAME INSURD_FULL_NAME,
D.ISSUE_AGE,
E.CLNT_REF_NBR AGNT_CLNT_REF_NBR,
DECODE(C.PRSNL_BIZ_CD,
'C',
F.FIRST_NAME || F.LAST_NAME,
E.AGNT_LAST_NAME || ',' || E.AGNT_FIRST_NAME || ',' || E.AGNT_MIDDLE_INITL_NAME || ',' || '') AGNT_FULL_NAME,

A.AGNT_SHARE_RATE * 100 AS AGNT_SHARE_RATE,
B.VAL_CD,
B.TBL_CD_SHORT_DESC,
B.TBL_CD_LONG_DESC,
A.COV_STATUS_CD,
TO_CHAR(D.RISK_COMMNCD_DT, DATE_FORMATTER) RISK_COMMNCD_DT,
TO_CHAR(D.OUT_OF_FORCE_SET_DT, DATE_FORMATTER) OUT_OF_FORCE_SET_DT,
D.CRRNT_PLCY_LIFTIM_MAX_AMT,
D.CRRNT_PLCY_DEDCTBL_AMT,
B.HOME_HLTH_CARE_PCTG_NBR,
D.CRRNT_BENE_AMT,
D.BENE_PERIOD_CD,
D.MAX_DAYS_PAYABL,
B.RIDER_TYPE_CD,
D.WAITNG_PERIOD_DURATN,
D.ORIGNL_BENE_AMT,
D.ORIGNL_PLCY_LIFTIM_MAX_AMT,
D.ORIGNL_PLCY_DEDCTBL_AMT,
B.INFLTN_GUARD_CD,
D.ORIGNL_ANNUAL_PREM_AMT,
D.MODAL_PREM_AMT,
B.NON_FORFTR_IND
FROM CLEAR_DAILY_FACT_VW A,
PRDCT_DIM B,
CLNT_DIM C,
COV_DIM D,
AGNT_DIM E,
CLNT_DETL_MV F
WHERE A.PRDCT_DIM_ID = B.PRDCT_DIM_ID AND A.CLNT_REF_NBR = C.CLNT_REF_NBR AND
A.COV_DIM_ID = D.COV_DIM_ID AND A.AGNT_DIM_ID = E.AGNT_DIM_ID AND
E.CLNT_REF_NBR = F.CLNT_REF_NBR AND E.MAIL_REF_NBR = F.LOC_REF_NBR AND A.PLCY_NBR = PLCY_NBR_IN
ORDER BY PLCY_NBR,
CO_CD,
ELMNT_NBR,
COMMS_SPLIT_NBR;

-- Local variables
V_CNT NUMBER DEFAULT 0;

BEGIN
FOR REC IN RIDER_INFO_CUR
LOOP
V_CNT := V_CNT + 1;
RIDER_INFO_TAB.EXTEND;
RIDER_INFO_TAB(V_CNT) := HLDNG_INQRY_RIDR_INF_OBJ_TYPE(REC.PLCY_NBR,
REC.CO_CD,
REC.ELMNT_NBR,
REC.COMMS_SPLIT_NBR,
REC.INSURD_CLNT_REF_NBR,
REC.INSURD_ROLE_CD,
REC.INSURD_FULL_NAME,
REC.ISSUE_AGE,
REC.AGNT_CLNT_REF_NBR,
REC.AGNT_FULL_NAME,
REC.AGNT_SHARE_RATE,
REC.VAL_CD,
REC.TBL_CD_SHORT_DESC,
REC.TBL_CD_LONG_DESC,
REC.COV_STATUS_CD,
REC.RISK_COMMNCD_DT,
REC.OUT_OF_FORCE_SET_DT,
REC.CRRNT_PLCY_LIFTIM_MAX_AMT,
REC.CRRNT_PLCY_DEDCTBL_AMT,
REC.HOME_HLTH_CARE_PCTG_NBR,
REC.CRRNT_BENE_AMT,
REC.BENE_PERIOD_CD,
REC.MAX_DAYS_PAYABL,
REC.RIDER_TYPE_CD,
REC.WAITNG_PERIOD_DURATN,
REC.ORIGNL_BENE_AMT,
REC.ORIGNL_PLCY_LIFTIM_MAX_AMT,
REC.ORIGNL_PLCY_DEDCTBL_AMT,
REC.INFLTN_GUARD_CD,
REC.ORIGNL_ANNUAL_PREM_AMT,
REC.MODAL_PREM_AMT,
REC.NON_FORFTR_IND);
END LOOP;

PROC_STATUS_CD := 'S';

EXCEPTION
WHEN OTHERS THEN
PROC_STATUS_CD := 'F';
PROC_ERROR_DESC := 'Error Occured in GET_RIDER_INFO_PROC. SQL Error Code = ' || SQLCODE ||
' Error Msg : ' || SUBSTR(SQLERRM, 1, 400);
END;

PROCEDURE GET_PARTY_INFO_PROC(PLCY_NBR_IN IN VARCHAR2,
PARTY_INFO_TAB IN OUT HLDNG_INQRY_PRTY_INFO_TBL_TYPE,
PROC_STATUS_CD OUT VARCHAR2,
PROC_ERROR_DESC OUT VARCHAR2) IS

-- Get Insured Information
CURSOR INSURED_PARTY_INFO_CUR IS
SELECT *
FROM (SELECT 'P' ACORD_PARTY_TYPE_CODE,
'INSURED' PARTY_TYPE_CD,
'I' || A.PRIMRY_SECDRY_LIFE_IND PARTY_ROLE_CD,
A.CLNT_REF_NBR,
C.AARP_MBRSHP_NBR,
B.LAST_NAME || ',' || B.FIRST_NAME || ',' || B.MIDDLE_NAME || ',' || B.SUFFIX_NAME FULL_NAME,
REPLACE(B.SSN_NBR, '-', NULL) SSN_NBR,
B.FIRST_NAME,
B.MIDDLE_NAME,
B.LAST_NAME,
B.TITLE_NAME,
B.SUFFIX_NAME,
A.MARITL_STATUS_CD,
B.GENDER_CD,
TO_CHAR(B.BIRTH_DT, DATE_FORMATTER) BIRTH_DT,
'BILLING' ADDR_TYPE_CD,
B.LINE_1_ADDR,
B.LINE_2_ADDR,
B.LINE_3_ADDR,
B.LINE_4_ADDR,
B.CITY_NAME,
B.STATE_CD,
B.ZIP_CD,
SUBSTR(REPLACE(B.DAY_PHONE_NBR, '-', NULL), 1, 3) AREA_CD_DAY_PHONE_NBR,
SUBSTR(REPLACE(B.DAY_PHONE_NBR, '-', NULL), 4, 7) DAY_PHONE_NBR,
SUBSTR(REPLACE(B.EVNG_PHONE_NBR, '-', NULL), 1, 3) AREA_CD_EVE_PHONE_NBR,
SUBSTR(REPLACE(B.EVNG_PHONE_NBR, '-', NULL), 4, 7) EVENG_PHONE_NBR,
NULL AGNT_NBR, -- NA For Insured
NULL CO_CD, -- NA For Insured
NULL CHNL_CD, -- NA For Insured
NULL CHNL_DESC, -- NA For Insured
ROW_NUMBER() OVER(PARTITION BY A.PRIMRY_SECDRY_LIFE_IND ORDER BY PRIMRY_SECDRY_LIFE_IND, A.CLNT_REF_NBR) SEQ
FROM CLEAR_DAILY_FACT_VW A,
CLNT_DIM B,
COV_DIM C
WHERE A.CLNT_REF_NBR = B.CLNT_REF_NBR AND A.COV_DIM_ID = C.COV_DIM_ID AND
A.PLCY_NBR = PLCY_NBR_IN)
WHERE SEQ = 1
ORDER BY PARTY_ROLE_CD;

-- Get Agent Information
CURSOR WRIT_AGENT_PARTY_INFO_CUR IS
SELECT *
FROM (SELECT C.PRSNL_BIZ_CD ACORD_PARTY_TYPE_CODE,
'AGENT' PARTY_TYPE_CD,
'AW' PARTY_ROLE_CD,
B.CLNT_REF_NBR,
NULL AARP_MBRSHP_NBR, -- NA For Agent
DECODE(C.PRSNL_BIZ_CD,
'C',
C.FIRST_NAME || C.LAST_NAME,
B.AGNT_LAST_NAME || ',' || B.AGNT_FIRST_NAME || ',' || B.AGNT_MIDDLE_INITL_NAME || ',' || '') FULL_NAME,
REPLACE(B.AGNT_SSN_NBR, '-', NULL) SSN_NBR,
B.AGNT_FIRST_NAME FIRST_NAME,
B.AGNT_MIDDLE_INITL_NAME MIDDLE_NAME,
B.AGNT_LAST_NAME LAST_NAME,
C.TITLE_NAME,
C.SUFFIX_NAME,
NULL MARITL_STATUS_CD, --NA For Agent
DECODE(C.PRSNL_BIZ_CD, 'C', 'U', C.GENDER_CD) GENDER_CD,
TO_CHAR(DECODE(C.PRSNL_BIZ_CD, 'C', TO_DATE(HI_DATE, 'YYYY-MM-DD'), C.BIRTH_DT),
DATE_FORMATTER) BIRTH_DT,
'BUSINESS' ADDR_TYPE_CD,
B.LINE_1_ADDR,
B.LINE_2_ADDR,
B.LINE_3_ADDR,
NULL LINE_4_ADDR, -- NA For Agent
B.CITY_NAME,
B.ST_CD STATE_CD,
B.ZIP_5_CD || NVL2(B.ZIP_4_CD, '-' || B.ZIP_4_CD, '') ZIP_CD,
SUBSTR(REPLACE(B.DAY_PHONE_NBR, '-', NULL), 1, 3) AREA_CD_DAY_PHONE_NBR,
SUBSTR(REPLACE(B.DAY_PHONE_NBR, '-', NULL), 4, 7) DAY_PHONE_NBR,
SUBSTR(REPLACE(B.EVNG_PHONE_NBR, '-', NULL), 1, 3) AREA_CD_EVE_PHONE_NBR,
SUBSTR(REPLACE(B.EVNG_PHONE_NBR, '-', NULL), 4, 7) EVENG_PHONE_NBR,
B.AGNT_NBR,
B.CO_CD,
B.CHNL_CD,
'' CHNL_DESC,
ROW_NUMBER() OVER(PARTITION BY B.CLNT_REF_NBR ORDER BY B.CLNT_REF_NBR) SEQ
FROM CLEAR_DAILY_FACT_VW A,
AGNT_DIM B,
CLNT_DETL_MV C --,chnl_dim c
WHERE A.PLCY_NBR = PLCY_NBR_IN AND A.AGNT_DIM_ID = B.AGNT_DIM_ID AND
B.CLNT_REF_NBR = C.CLNT_REF_NBR AND B.MAIL_REF_NBR = C.LOC_REF_NBR)
WHERE SEQ = 1;

-- Get Servicing Agent Information
CURSOR SVC_AGENT_PARTY_INFO_CUR IS
SELECT *
FROM (SELECT C.PRSNL_BIZ_CD ACORD_PARTY_TYPE_CODE,
'AGENT' PARTY_TYPE_CD,
'AS' PARTY_ROLE_CD,
B.CLNT_REF_NBR,
NULL AARP_MBRSHP_NBR, -- NA For Agent
DECODE(C.PRSNL_BIZ_CD,
'C',
C.FIRST_NAME || C.LAST_NAME,
B.AGNT_LAST_NAME || ',' || B.AGNT_FIRST_NAME || ',' || B.AGNT_MIDDLE_INITL_NAME || ',' || '') FULL_NAME,
REPLACE(B.AGNT_SSN_NBR, '-', NULL) SSN_NBR,
B.AGNT_FIRST_NAME FIRST_NAME,
B.AGNT_MIDDLE_INITL_NAME MIDDLE_NAME,
B.AGNT_LAST_NAME LAST_NAME,
C.TITLE_NAME,
C.SUFFIX_NAME,
NULL MARITL_STATUS_CD, --NA For Agent
DECODE(C.PRSNL_BIZ_CD, 'C', 'U', C.GENDER_CD) GENDER_CD,
TO_CHAR(DECODE(C.PRSNL_BIZ_CD, 'C', TO_DATE(HI_DATE, 'YYYY-MM-DD'), C.BIRTH_DT),
DATE_FORMATTER) BIRTH_DT,
'BUSINESS' ADDR_TYPE_CD,
B.LINE_1_ADDR,
B.LINE_2_ADDR,
B.LINE_3_ADDR,
NULL LINE_4_ADDR, -- NA For Agent
B.CITY_NAME,
B.ST_CD STATE_CD,
B.ZIP_5_CD || NVL2(B.ZIP_4_CD, '-' || B.ZIP_4_CD, '') ZIP_CD,
SUBSTR(REPLACE(B.DAY_PHONE_NBR, '-', NULL), 1, 3) AREA_CD_DAY_PHONE_NBR,
SUBSTR(REPLACE(B.DAY_PHONE_NBR, '-', NULL), 4, 7) DAY_PHONE_NBR,
SUBSTR(REPLACE(B.EVNG_PHONE_NBR, '-', NULL), 1, 3) AREA_CD_EVE_PHONE_NBR,
SUBSTR(REPLACE(B.EVNG_PHONE_NBR, '-', NULL), 4, 7) EVENG_PHONE_NBR,
B.AGNT_NBR,
B.CO_CD,
B.CHNL_CD,
'' CHNL_DESC,
ROW_NUMBER() OVER(PARTITION BY B.CLNT_REF_NBR ORDER BY B.CLNT_REF_NBR) SEQ
FROM COV_DIM A,
AGNT_DIM B,
CLNT_DETL_MV C --,chnl_dim c
WHERE A.PLCY_NBR = PLCY_NBR_IN AND A.SVCNG_AGNT_NBR = B.AGNT_NBR AND A.CO_CD = B.CO_CD AND
B.CLNT_REF_NBR = C.CLNT_REF_NBR AND B.MAIL_REF_NBR = C.LOC_REF_NBR)
WHERE SEQ = 1;

-- Local variables
V_CNT NUMBER DEFAULT 0;
V_SVC_AGNT_NBR VARCHAR2(10);
V_PARTY_ROLE_CODE VARCHAR2(3);
V_SVC_AGNT_IND VARCHAR2(1) DEFAULT 'N';
BEGIN
-- Get Servicing Agent Number
SELECT DISTINCT COV_DIM.SVCNG_AGNT_NBR INTO V_SVC_AGNT_NBR FROM COV_DIM WHERE PLCY_NBR = PLCY_NBR_IN;

-- Populate Insured Information
FOR REC IN INSURED_PARTY_INFO_CUR
LOOP
V_CNT := V_CNT + 1;
PARTY_INFO_TAB.EXTEND;
PARTY_INFO_TAB(V_CNT) := HLDNG_INQRY_PRTY_INF_OBJ_TYPE(REC.ACORD_PARTY_TYPE_CODE,
REC.PARTY_TYPE_CD,
REC.PARTY_ROLE_CD,
REC.CLNT_REF_NBR,
REC.AARP_MBRSHP_NBR,
REC.FULL_NAME,
REC.SSN_NBR,
REC.FIRST_NAME,
REC.MIDDLE_NAME,
REC.LAST_NAME,
REC.TITLE_NAME,
REC.SUFFIX_NAME,
REC.MARITL_STATUS_CD,
REC.GENDER_CD,
REC.BIRTH_DT,
REC.ADDR_TYPE_CD,
REC.LINE_1_ADDR,
REC.LINE_2_ADDR,
REC.LINE_3_ADDR,
REC.LINE_4_ADDR,
REC.CITY_NAME,
REC.STATE_CD,
REC.ZIP_CD,
REC.AREA_CD_DAY_PHONE_NBR,
REC.DAY_PHONE_NBR,
REC.AREA_CD_EVE_PHONE_NBR,
REC.EVENG_PHONE_NBR,
REC.AGNT_NBR, -- AGNT_NBR NA for Insured
REC.CO_CD, -- CO_CD NA for Insured
REC.CHNL_CD, -- CHNL_CD NA for Insured
REC.CHNL_DESC); --CHNL_DESC NA for Insured
END LOOP;

-- Populate Writing Agent Information
FOR REC IN WRIT_AGENT_PARTY_INFO_CUR
LOOP
V_CNT := V_CNT + 1;
PARTY_INFO_TAB.EXTEND;

IF (V_SVC_AGNT_NBR = REC.AGNT_NBR) THEN
V_PARTY_ROLE_CODE := 'ASW';
V_SVC_AGNT_IND := 'Y';
ELSE
V_PARTY_ROLE_CODE := REC.PARTY_ROLE_CD;
END IF;

PARTY_INFO_TAB(V_CNT) := HLDNG_INQRY_PRTY_INF_OBJ_TYPE(REC.ACORD_PARTY_TYPE_CODE,
REC.PARTY_TYPE_CD,
V_PARTY_ROLE_CODE,
REC.CLNT_REF_NBR,
REC.AARP_MBRSHP_NBR,
REC.FULL_NAME,
REC.SSN_NBR,
REC.FIRST_NAME,
REC.MIDDLE_NAME,
REC.LAST_NAME,
REC.TITLE_NAME,
REC.SUFFIX_NAME,
REC.MARITL_STATUS_CD,
REC.GENDER_CD,
REC.BIRTH_DT,
REC.ADDR_TYPE_CD,
REC.LINE_1_ADDR,
REC.LINE_2_ADDR,
REC.LINE_3_ADDR,
REC.LINE_4_ADDR,
REC.CITY_NAME,
REC.STATE_CD,
REC.ZIP_CD,
REC.AREA_CD_DAY_PHONE_NBR,
REC.DAY_PHONE_NBR,
REC.AREA_CD_EVE_PHONE_NBR,
REC.EVENG_PHONE_NBR,
REC.AGNT_NBR,
REC.CO_CD,
REC.CHNL_CD,
REC.CHNL_DESC);

END LOOP;

-- Populate Servicing Agent Information Only If Servicing Agent is Not same as Writing Agent
IF (V_SVC_AGNT_IND = 'N') THEN
FOR REC IN SVC_AGENT_PARTY_INFO_CUR
LOOP
V_CNT := V_CNT + 1;
PARTY_INFO_TAB.EXTEND;

PARTY_INFO_TAB(V_CNT) := HLDNG_INQRY_PRTY_INF_OBJ_TYPE(REC.ACORD_PARTY_TYPE_CODE,
REC.PARTY_TYPE_CD,
REC.PARTY_ROLE_CD,
REC.CLNT_REF_NBR,
REC.AARP_MBRSHP_NBR,
REC.FULL_NAME,
REC.SSN_NBR,
REC.FIRST_NAME,
REC.MIDDLE_NAME,
REC.LAST_NAME,
REC.TITLE_NAME,
REC.SUFFIX_NAME,
REC.MARITL_STATUS_CD,
REC.GENDER_CD,
REC.BIRTH_DT,
REC.ADDR_TYPE_CD,
REC.LINE_1_ADDR,
REC.LINE_2_ADDR,
REC.LINE_3_ADDR,
REC.LINE_4_ADDR,
REC.CITY_NAME,
REC.STATE_CD,
REC.ZIP_CD,
REC.AREA_CD_DAY_PHONE_NBR,
REC.DAY_PHONE_NBR,
REC.AREA_CD_EVE_PHONE_NBR,
REC.EVENG_PHONE_NBR,
REC.AGNT_NBR,
REC.CO_CD,
REC.CHNL_CD,
REC.CHNL_DESC);

END LOOP;
END IF;

PROC_STATUS_CD := 'S';

EXCEPTION
WHEN OTHERS THEN
PROC_STATUS_CD := 'F';
PROC_ERROR_DESC := 'Error Occured in GET_PARTY_INFO_PROC. SQL Error Code = ' || SQLCODE ||
' Error Msg : ' || SUBSTR(SQLERRM, 1, 400);
END;

END HLDNG_INQRY_PKG;
Re: is there any better way of doing this [message #287788 is a reply to message #287784] Thu, 13 December 2007 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, there is too few lines, I suggest you add some more.

Regards
Michel
Re: is there any better way of doing this [message #287796 is a reply to message #287784] Thu, 13 December 2007 08:46 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


I would suggest you to make sure that you post only the small form of code / Psuedocode in case you have a large script , so that it becomes readable for others.

Also explain why do you feel the need for an alternative though existsing code works fine for you .

Thumbs Up
Rajuvan.
Previous Topic: Calculating from multiple tables
Next Topic: Error in executing procedure
Goto Forum:
  


Current Time: Tue Dec 06 04:10:00 CST 2016

Total time taken to generate the page: 0.18832 seconds