篠崎愛(Shinozaki Ai) : 시노자키 아이

REPLACE  PROCEDURE LDVIDB.UP_ROUNGE_USE_CUST  

(

  IN ST_CD  CHAR(4)    --관리점코드     --원 소스 ST_CD

, IN LOUNGE_CD  CHAR(2)    --라운지코드  

, IN FAMY_GB CHAR(1) --가족구분  

, IN LOUNGE_ST CHAR(8)    --라운지이용기간 - 시작    

, IN LOUNGE_ET CHAR(8)    --라운지이용기간 - 종료    

)

/*

수정일: 2014-1-27

작성자: 김우종


시스템명: VIP

업무명: 마이포지션

화면ID: RG2300M

메뉴: 마이포지션 -> 우수고객서비사관리 -> 라운지이용내역조회


SP설명: 라운지 이용내역 조회

SP실행: CALL LDVIDB.UP_ROUNGE_USE_CUST('0001' , '1111', '1', '20111111' , '20141212');

*/

DYNAMIC RESULT SETS 2

BEGIN

BEGIN

  DECLARE cur1 CURSOR WITH RETURN ONLY FOR

LOCKING ROW FOR ACCESS

  SELECT

   SUBSTR(B.USE_DT,1, 4) || '-' || SUBSTR(B.USE_DT, 5,2) || '-' || SUBSTR(B.USE_DT, 7,2)

  AS USE_DT--이용일자       

    , SUBSTR(B.ETR_TM, 1, 2) || ':' || SUBSTR(B.ETR_TM, 3, 2)

    AS ETR_TM--입실시간 

, A.KOR_NM AS CUST_NM --고객이름

, A.CUST_NO AS CUST_NUM --고객번호

--, A.RGST_NO AS RGST_NO  

, (CASE A.FAMY_GB WHEN '1' THEN '본인' 

    WHEN '2' THEN '가족' 

   ELSE '' END) AS ME_FAMILY --가족구분

,(SELECT sel_lvl_nm   

FROM   LDVMDB.VI_SEL_LVL_CD_V

WHERE sel_flg_cd = '1' 

AND   use_yn = 'Y'

AND sel_lvl_cd = A.SEL_LVL_CD)                       AS RNK_NM --등급

, (SELECT b_code_nm  

   FROM LDVMDB.VI_BASE_CODE_V 

    WHERE p_code ='MY016'

    AND b_code = A.INNER_GRD)  AS INNER_RNK_NM --내부등급

        ,CASE B.MVG_ROOM_ST_CD WHEN '9999' THEN '전점'

          ELSE COALESCE ((SELECT store_nm

  FROM   LDVMDB.PO_ST_V

      WHERE  store_cd = B.MVG_ROOM_ST_CD),'')   END ST_CD --이용점

FROM LDVMDB.VI_MVG_CUST_V AS A 

INNER JOIN  LDVMDB.VI_MVG_ROOM_V AS B 

ON  A.CUST_NO = B.INTG_CUST_ID

WHERE  B.USE_DT BETWEEN :LOUNGE_ST AND :LOUNGE_ET --이용기간 

AND (ST_CD = :ST_CD OR :ST_CD = '9999')

   AND (B.LOUNGE_CD = :LOUNGE_CD OR  :LOUNGE_CD= '99') --라운지코드

AND (A.FAMY_GB = :FAMY_GB OR :FAMY_GB = '0') 

ORDER BY B.USE_DT DESC;

 OPEN cur1;

END;


BEGIN

DECLARE cur2 CURSOR WITH RETURN ONLY FOR

LOCKING ROW FOR ACCESS

SELECT COUNT(A.CUST_NO) AS CNT  

,  SUM(B.ACCMPNY_CNT)       AS CNT2   --동반고객수       

 FROM LDVMDB.VI_MVG_CUST_V AS A 

      INNER JOIN LDVMDB.VI_MVG_ROOM_V AS B 

   ON A.CUST_NO = B.INTG_CUST_ID  

WHERE  B.USE_DT BETWEEN :LOUNGE_ST AND :LOUNGE_ET --이용기간  

AND (ST_CD = :ST_CD OR :ST_CD = '9999')  

  AND B.LOUNGE_CD = :LOUNGE_CD OR  :LOUNGE_CD= '99' --라운지코드    

  AND (A.FAMY_GB = :FAMY_GB OR :FAMY_GB = '0');

OPEN CUR2;

END;

END;

신고

http://shinozakiai.tistory.com/trackback/89 관련글 쓰기

1

REPLACE  PROCEDURE LDVIDB.UP_NOTICE_GETNOTICELIST   --Notice_GetNoticeList

(

  IN SearchOp VARCHAR(7)

 ,IN SearchText VARCHAR(1000)

)

/*

수정일: 2014-1-10

작성자: 김우종


시스템명: VIP

업무명:  시스템관리

화면ID: CM0100M

메뉴: 시스템관리 -> 공지사항 관리 -> 공지사항 


SP설명: 권한 코드

SP실행: CALL LDVIDB.UP_NOTICE_GETNOTICELIST('ALL','명동점');

*/


DYNAMIC RESULT SETS 1

BEGIN


  DECLARE   STRSQL     VARCHAR(8000);

  SET STRSQL    =  ' ';

  

IF SearchOp = 'ALL'  THEN-- 전체

SET STRSQL =

'SELECT 

  notice_id

   ,sys_cd

   , CASE a.sys_cd WHEN ''00'' THEN ''전체''

     ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm    

   ,Ntitle

   , usr_id

   , SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || ''  ~  '' ||

     SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2)  as term

   FROM LDVMDB.VI_NOTICE_V a

   WHERE use_yn = ''Y''

   ORDER BY  notice_id DESC; ';

END IF;


IF SearchOp = 'TITLE' THEN-- 제목

SET STRSQL =

'SELECT 

notice_id

,sys_cd

   , CASE a.sys_cd WHEN ''00'' THEN ''전체''

     ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm

, NTitle

, usr_id

, SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || ''  ~  '' ||

 SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2)  as term

FROM LDVMDB.VI_NOTICE_V a

WHERE

    use_yn = ''Y'' AND NTitle LIKE ''%' || SearchText || '%''

ORDER BY  notice_id DESC;';

END IF;


IF  SearchOp = 'CONTE'  THEN-- 내용

SET STRSQL = 

'SELECT 

notice_id

,sys_cd

   , CASE a.sys_cd WHEN ''00'' THEN ''전체''

     ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm

, NTitle

, usr_id

   , SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || ''  ~  '' ||

     SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2)  as term

FROM LDVMDB.VI_NOTICE_V a

WHERE

use_yn = ''Y'' AND contents LIKE ''%' || SearchText || '%''

ORDER BY  notice_id DESC;';

END IF;


IF SearchOp = 'USER' THEN  -- 사용자

SET STRSQL = 

'SELECT 

notice_id

,sys_cd

   , CASE a.sys_cd WHEN ''00'' THEN ''전체''

     ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm

, NTitle

, usr_id

   , SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || ''  ~  '' ||

     SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2)  as term

FROM LDVMDB.VI_NOTICE_V a

WHERE

use_yn = ''Y'' AND usr_id LIKE ''%' || SearchText || '%''

ORDER BY  notice_id DESC;';

END IF;

IF SearchOp = 'Total' THEN  -- 전체 공지만 조회

SET STRSQL = 

'SELECT 

notice_id

,sys_cd

   , CASE a.sys_cd WHEN ''00'' THEN ''전체''

     ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm

, NTitle

, usr_id

   , SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || ''  ~  '' ||

     SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2)  as term

FROM LDVMDB.VI_NOTICE_V a

WHERE

use_yn = ''Y'' AND usr_id LIKE ''%' || SearchText || '%''

AND sys_cd = 00

ORDER BY  notice_id DESC;';

END IF;

IF SearchOp = 'Sys' THEN  -- 시스템 공지만 조회

SET STRSQL = 

'SELECT 

notice_id

,sys_cd

   , CASE a.sys_cd WHEN ''00'' THEN ''전체''

     ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm

, NTitle

, usr_id

   , SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || ''  ~  '' ||

     SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2)  as term

FROM LDVMDB.VI_NOTICE_V a

WHERE

use_yn = ''Y'' 

AND usr_id LIKE ''%' || SearchText || '%''

AND sys_cd <> ''00''

ORDER BY  notice_id DESC;';

END IF;


BEGIN

DECLARE CUR1 CURSOR WITH RETURN ONLY TO CLIENT FOR SQL_STATEMENT;

PREPARE SQL_STATEMENT FROM STRSQL;

OPEN CUR1;

END;

END;

신고

http://shinozakiai.tistory.com/trackback/88 관련글 쓰기

Comment +0

WHERE  B.USE_DT BETWEEN :LOUNGE_ST AND :LOUNGE_ET --이용기간 




시작 시간 종료 시간 사이에 값 조회 .

신고

http://shinozakiai.tistory.com/trackback/87 관련글 쓰기

Comment +0

티스토리 툴바