[Oracle] 오라클 쿼리 SP_CONTRACT_VALUE_ITEM_INSERT& SP_CONTRACT_VALUE_ITEM_UPDATE

영업관리팀에서 잦은 CONTRACT_VALUE 테이블 데이터 일괄처리 요청으로 인하여 새 프로그램을 만들어 주었다.

프론트 = 파워빌더

백엔드 = 오라클(SP)

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

 

SP_CONTRACT_VALUE_ITEM_INSERT 쿼리

 

CREATE OR REPLACE PROCEDURE SP_CONTRACT_VALUE_ITEM_INSERT
(OLD_ITEM_CODE CONTRACT_VALUE.ITEM_CODE%TYPE
,NEW_ITEM_CODE CONTRACT_VALUE.ITEM_CODE%TYPE
,CHANGE_EMP CONTRACT_VALUE.LASTEMP%TYPE
,OLD_TERM_TO DATE
,NEW_TERM_FROM DATE
)
IS
BEGIN
             
       INSERT INTO CONTRACT_VALUE
       SELECT SALES_CODE     
       ,CUST_CODE      
       ,NEW_ITEM_CODE      
       ,BUSINESS_GUBUN 
       ,BASE_AMT       
       ,ORDER_AMT      
       ,LOWEST_AMT     
       ,CAMT_PRCTYPE   
       ,CONTRACT_AMT   
       ,RND_RATE       
       ,NEW_TERM_FROM      
       ,TERM_TO        
       ,SYSDATE    
       ,CONTRACT_GUBUN 
       ,MON_SOMO       
       ,DRAFT_DATE     
       ,DRAFT_NO       
       ,USE_YN         
       ,CHANGE_EMP       
       ,SYSDATE       
       ,CHANGE_EMP        
       ,SYSDATE        
       ,CONTRACT_TYPE_1
       ,CONTRACT_TYPE_2
       ,CONTRACT_TYPE_3
       ,CONTRACT_TYPE_4
       ,CONTRACT_TYPE_5
       ,CONTRACT_TYPE_6
       ,CONTRACT_TYPE_7
       FROM CONTRACT_VALUE
       WHERE TERM_TO = OLD_TERM_TO
       AND ITEM_CODE = OLD_ITEM_CODE;
       
       COMMIT;
       
END;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

 

SP_CONTRACT_VALUE_ITEM_UPDATE 쿼리

 

CREATE OR REPLACE PROCEDURE SP_CONTRACT_VALUE_ITEM_UPDATE
(OLD_ITEM_CODE CONTRACT_VALUE.ITEM_CODE%TYPE
,CHANGE_EMP CONTRACT_VALUE.LASTEMP%TYPE
,OLD_TERM_TO DATE
,NEW_TERM_TO DATE
)
IS
BEGIN
      
       UPDATE CONTRACT_VALUE
       SET TERM_TO = NEW_TERM_TO
       ,CHANGE_DATE = SYSDATE
       ,LASTEMP = CHANGE_EMP
       ,LASTYMD = SYSDATE
       WHERE TERM_TO = OLD_TERM_TO
       AND ITEM_CODE = OLD_ITEM_CODE;
       
       COMMIT;
            
END;