[ORACLE]커서를 이용한 프로시저

PROCEDURE SP_CRSS_LN_COMPLETE
 /*  SPC 채권계산서 신고 후 총 채권액 계산 */
(
 p_id     in CRSS_LN_MAIN.resident_no%TYPE, -- 주민번호
 p_result  out varchar2
)
is
 duplicate_action EXCEPTION; 

 v_gikwan_key1  CRSS_PCM_GIKWAN.gikwan_key1%TYPE;     -- 상록수 기관대표코드
 
 CURSOR cur_ln_main(v_id CRSS_LN_MAIN.RESIDENT_NO%TYPE, v_org_cd CRSS_LN_MAIN.FI_ORG_CD%TYPE) IS
 SELECT RECP_NO, RESIDENT_NO, LN_ACCT_NO
 FROM CRSS_LN_MAIN
 WHERE RESIDENT_NO = v_id
 AND  FI_ORG_CD = v_org_cd
 AND  INPUT_G = '1';
 
 row_ln_main cur_ln_main%rowtype;
begin
 v_gikwan_key1 := '9031030';
 
 OPEN cur_ln_main(p_id, v_gikwan_key1);
 FETCH cur_ln_main INTO row_ln_main;                                 -- 커서 비교(중복 확인)
 if cur_ln_main%FOUND then
     CLOSE cur_ln_main; 
  RAISE duplicate_action; 
 end if;
    CLOSE cur_ln_main;

 -- SPC 채권현황 취합 (채권별 취합)
/* INSERT INTO CRSS_LN_MAIN
 SELECT NVL(a.RECP_NO,' '), a.RESIDENT_NO, a.CUST_NAME, '9' INSU_TYPE,
  c.CHAE_NUM, DECODE(c.LOAN_G,'1','신용카드','2','대출',' '),
  d.gikwan_key1, d.gikwan_key2, d.gikwan_key3, d.join_gb,
  c.LOAN_START_D, c.LOAN_END_D, c.LAST_CALC_D,
  c.LOAN_AMT, c.REST_PRN_AMT, c.REST_FEE_AMT,
  c.REST_INT_AMT+GET_INTEREST_I00(c.CHAE_NO, c.CREDITOR_G, c.CHAE_NUM, to_char(to_date(a.insp_recp_date1,'yyyymmdd')+7,'yyyymmdd')),
  c.REST_PRE_AMT,c.REST_AMT, c.INT_RATE, decode(trim(c.USE_G),'1',decode(trim(c.USEDTL_G),'3','3','2'),'2',decode(trim(c.USEDTL_G),'3','3','9','3','2'),'1'),'생략',
  decode(trim(c.SANGHWAN_G),'1','1','2','4','3','5','4','2','4'),decode(trim(c.SANGHWAN_G),'1','만기일시상환','2','기타','3','원금균등분할상환','4','원리금균등분할상환','기타'),
  'Y',DECODE(c.SURETY_G,'1','2','3'), decode(c.CHAE_G,'5','Y','N'), 'N', 0, to_char(sysdate,'yyyymmdd'),
  '1', d.gikwan_nm1, d.gikwan_nm2
 FROM CRSS_DEBT_CUST a, DETAIL_I00 c, CRSS_PCM_GIKWAN d
 WHERE A.RESIDENT_NO = C.CHAE_NO
 AND D.GIKWAN_KEY1 = v_gikwan_key1
 AND A.RESIDENT_NO = p_id; */

 -- SPC 채권현황 취합 (합계 취합)   
 INSERT INTO CRSS_LN_MAIN
 SELECT NVL(MAX(a.RECP_NO),' '), a.RESIDENT_NO, MAX(a.CUST_NAME), '9' INSU_TYPE,
  a.RESIDENT_NO || '1', '상각채권',
  MAX(d.gikwan_key1), MAX(d.gikwan_key2), MAX(d.gikwan_key3), MAX(d.join_gb),
  MIN(c.LOAN_START_D), MAX(c.LOAN_END_D), MAX(NVL(c.LAST_CALC_D, c.LISU_D)),
  SUM(NVL(c.LOAN_AMT,0)), SUM(NVL(c.REST_PRN_AMT,0)), SUM(NVL(c.REST_FEE_AMT,0)),
  SUM(NVL(c.REST_INT_AMT,0))+SUM(GET_INTEREST_I00(c.CHAE_NO, c.CREDITOR_G, c.CHAE_NUM, to_char(sysdate+7,'yyyymmdd'))),
  SUM(NVL(c.REST_PRE_AMT,0))+MAX(NVL(b.REST_PRE2_AMT,0))+GET_USERPRE_I00(a.RESIDENT_NO),
  SUM(NVL(c.REST_PRN_AMT,0))+SUM(NVL(c.REST_FEE_AMT,0))+
  SUM(NVL(c.REST_INT_AMT,0))+SUM(GET_INTEREST_I00(c.CHAE_NO, c.CREDITOR_G, c.CHAE_NUM, to_char(sysdate+7,'yyyymmdd')))+
  SUM(NVL(c.REST_PRE_AMT,0))+MAX(NVL(b.REST_PRE2_AMT,0))+GET_USERPRE_I00(a.RESIDENT_NO),
  6,' ','생략',' ',' ',
  'Y','3', 'Y', 'Y', 0, to_char(sysdate,'yyyymmdd'),
  '1', MAX(d.gikwan_nm1), MAX(d.gikwan_nm2)
 FROM CRSS_DEBT_CUST a, SUMMARY_I00 b, DETAIL_I00 c, CRSS_PCM_GIKWAN d
 WHERE A.RESIDENT_NO = B.ID
 AND A.RESIDENT_NO = C.CHAE_NO
 AND D.GIKWAN_KEY1 = v_gikwan_key1
 AND A.RESIDENT_NO = p_id
 GROUP BY A.RESIDENT_NO;
 
/* MIN(c.INT_RATE),' ','생략',' ',' ',*/
 
 /* 총액 계산
 update crss_ln_main
 set ln_tot_amt = ln_new_amt+ln_int+ln_dint+ln_cst
 where resident_no = p_id;*/
 
 -- 보증인 현황
/* INSERT INTO CRSS_LN_GUAR
 SELECT NVL(a.RECP_NO,' '), a.RESIDENT_NO, a.CUST_NAME, c.CHAE_NUM,
   DECODE(c.LOAN_G,'1','신용카드','2','대출',' '),
   d.gikwan_key2, d.gikwan_key3, b.ID, b.WIIM_NAME,
   '', to_char(sysdate,'yyyymmdd'), '1', d.gikwan_nm1, d.gikwan_nm2
 FROM CRSS_DEBT_CUST a, SURETY_INFO b, DETAIL_I00 c, CRSS_PCM_GIKWAN d
 WHERE B.CREDITOR_CODE = 'i00'
 AND B.CHAE_NO = C.CREDITOR_G || C.CHAE_NUM
 AND A.RESIDENT_NO = C.CHAE_NO
 AND D.GIKWAN_KEY1 = v_gikwan_key1
 AND A.RESIDENT_NO = p_id;  */ 
 
 -- 채권기관의견
 INSERT INTO CRSS_LN_OPN
 SELECT a.RECP_NO, a.RESIDENT_NO, a.CUST_NAME,
   d.gikwan_key2, d.gikwan_key3, '정상 상환 가능하다고 사료됨', '', '',
   to_char(sysdate,'yyyymmdd'), '1', d.gikwan_nm1, d.gikwan_nm2
 FROM CRSS_DEBT_CUST a, CRSS_PCM_GIKWAN d
 WHERE D.GIKWAN_KEY1 = v_gikwan_key1
 AND A.RESIDENT_NO = p_id;
 
 -- 상환액 계산
 UPDATE CRSS_DEBT_CUST A
 SET (REAL_DEBT_AMT, DEDUCT_RATE, PRN_DEDUCT_RATE, insp_adj_date1) =
 (SELECT MAX(상환액) 상환액,
   ROUND(((MAX(채무총액)-MAX(상환액))/DECODE(MAX(채무총액),0,1,MAX(채무총액)))*100,2) 감면율,
   DECODE( SIGN(MAX(원금)-MAX(상환액)), -1, 0, ROUND(((MAX(원금)-MAX(상환액))/DECODE(MAX(원금),0,1,MAX(원금)))*100,2)) 원금감면율,
   to_char(sysdate,'yyyymmdd')
 FROM
 (SELECT ROUND(SUM(NVL(LN_NEW_AMT,0)+NVL(LN_CST,0))*(1-DECODE(SIGN(15000000-SUM(NVL(LN_NEW_AMT,0)+NVL(LN_CST,0))),1,SUM(NVL(LN_NEW_AMT,0)+NVL(LN_CST,0))*0.00000002,0.3)),0) 상환액,
   SUM(NVL(LN_NEW_AMT,0)+NVL(LN_CST,0)+NVL(LN_INT,0)+NVL(LN_DINT,0)) 채무총액,
   SUM(NVL(LN_NEW_AMT,0)+NVL(LN_CST,0)) 원금
  FROM CRSS_LN_MAIN
  WHERE RESIDENT_NO = p_id
  UNION ALL
  SELECT ROUND(SUM(NVL(LN_NEW_AMT,0)+NVL(LN_CST,0)+NVL(LN_INT,0)+NVL(LN_DINT,0))/2,0) 상환액, 0 채무총액, 0 원금
  FROM CRSS_LN_MAIN
  WHERE RESIDENT_NO = p_id) A)
 WHERE RESIDENT_NO = p_id;    
 
 --협약가입기관의 총매무액, 협약가입기관수 업데이트
 UPDATE CRSS_DEBT_CUST
 SET (DEBT_AMT, FI_CNT) =
 (
  SELECT TRUNC(SUM(LN_TOT_AMT)/10000,0), COUNT(RESIDENT_NO)
  FROM CRSS_LN_MAIN WHERE NVL(ENTRY_GB,'0')='1' AND RESIDENT_NO=p_id
 )
 WHERE RESIDENT_NO=p_id;
 
 --협약기관중 최대채무액 업데이트
 UPDATE CRSS_DEBT_CUST
 SET (FI_DEBT_MAX_AMT) =
 (
  SELECT TRUNC(MAX(LN_TOT_AMT)/10000,0)
  FROM CRSS_LN_MAIN WHERE NVL(ENTRY_GB,'0')='1' AND RESIDENT_NO=p_id
 )
 WHERE RESIDENT_NO=p_id;
  
 --비협약기관 총채무액 업데이트
 UPDATE CRSS_DEBT_CUST
 SET (FI_CNVN_DEBT_AMT) =
 (
  SELECT TRUNC(SUM(LN_NEW_AMT)/10000,0)
  FROM CRSS_LN_CNVN WHERE RESIDENT_NO=p_id
 )
 WHERE RESIDENT_NO=p_id;  

 commit; 
 p_result := 'success';

   
exception
 when duplicate_action then
  rollback;
  p_result := 'derror';  -- 잘못된 호출
    when others then
        rollback;   
  p_result := 'uerror';  -- 알수없는 오류

end;