CREATE PROCEDURE dbo.USP_PMG3110_SELECT ( @LANGKIND NVARCHAR(3), -- 언어종류( 필수, KOR, CHS, ENG, JPN 등) @CO_CD NVARCHAR(4), -- 회사코드( 필수 ) @DIV_CDS NTEXT, -- 사업장 @DEPT_CDS NTEXT, -- 부서 @EMP_CDS NTEXT, -- 사원 @RCV_DT_FR NVARCHAR(8), -- 입고기간 FROM( 필수 ) @RCV_DT_TO NVARCHAR(8), -- 입고기간 TO( 필수 ) @TR_CDS NTEXT, -- 거래처 @WH_CDS NTEXT, -- 입고창고 @PO_FGS NTEXT, -- 거래구분 @PLN_CD_FG NVARCHAR(1), -- 담당구분( 0. 품목담당, 1. 거래처담당, 2. 실적담당 ) @PLN_CDS NTEXT, -- 담당 @NB_DC_FG NVARCHAR(1), -- 입고번호/비고LIKE구분( 0. 입고번호, 1. 비고(내역) ) @NB_DC_LIKE NVARCHAR(60), -- 입고번호/비고LIKE @ITEM_CD_FG NVARCHAR(1), -- 품번조회구분 @ITEM_CD_LIKE NVARCHAR(40), -- 품번/품명/규격 LIKE @ITEM_CD_FR NVARCHAR(30), -- 품번From @ITEM_CD_TO NVARCHAR(30), -- 품번To @ITEM_CD_MULTI NTEXT, -- 품번Multi @MGMT_CDS NTEXT, -- 관리구분 @PJT_CDS NTEXT, -- 프로젝트 @TRADE_GRP_CDS NTEXT, -- 거래처분류 @AREA_CDS NTEXT, -- 지역분류 @AREA_GRP_CDS NTEXT, -- 지역그룹 @PLNS_CDS NTEXT, -- 담당그룹 @ITEMGRP_CDS NTEXT, -- 품목군 @Z1_MGM_CDS NTEXT, -- 대분류 @Z2_MGM_CDS NTEXT, -- 중분류 @Z3_MGM_CDS NTEXT, -- 소분류 @ACCT_FGS NTEXT, -- 계정구분 @ODR_FGS NTEXT, -- 조달구분 @EXCH_CDS NTEXT -- 환종 ) WITH ENCRYPTION AS /*************************************************************************/ --설 명 : 구매/자재관리 - 매입미마감현황 - 조회 --수 정 자 : 심 재 석 <- 심 재 석 <- 심 재 석 <- 심 재 석 <- 심 재 석 <- --수정일자 : 2011/11/25 <- 2008/09/03 <- 2008/06/09 <- 2008/03/04 <- 2007/10/02 <- --수정내역 : 2006/10/20 : 신규 -- 2007/02/28 : 잔량, 공급가, 부가세, 합계 계산 산식 변경 -- 2007/08/23 : @ITEM_STD_CDS 추가 -- 2007/10/02 : 잔량 > 0 -> 잔량 <> 0 수정, MAP_FG 조건 제거 -- 2008/03/04 : 공급가,부가세,합계액 변경( 잔량 = 0 인 경우 입고내역 그대로 ) -- 2008/06/09 : USP_PMG3110_BY_SELECT -> dbo.USP_PMG3110_SELECT -- 2008/09/03 : RCV_YM, REMARK_DC_H, MEMO_CD, CHECK_PEN 추가 등, @ITEM_STD_CDS 제거 - 박성수 -- 2011/11/25 : A.EXCH_UM -> ( CASE WHEN A.PO_FG = '0' THEN NULL ELSE A.EXCH_UM END ) EXCH_UM -- A.EXCH_AM -> ( CASE WHEN A.PO_FG = '0' THEN NULL ELSE A.EXCH_AM END ) EXCH_AM /*************************************************************************/ BEGIN SET NOCOUNT ON DECLARE @P_ERR_MSG NVARCHAR(255), --에러메시지 @P_IS_FSTLANG INT, --자국어여부(0:자국어/1.외국어) @P_SYSCFG_CTR_NB_06 INT, @P_SYSCFG_CTR_NB_07 INT, @P_SYSCFG_FG_TY_10 INT IF( ISNULL(@LANGKIND,'') = '' ) BEGIN SET @P_ERR_MSG = DBO.UFN_GET_STRING_BY_LANGKIND( @LANGKIND, '언어종류는 필수입니다.' ) GOTO ERROR END IF( ISNULL(@CO_CD,'') = '' ) BEGIN SET @P_ERR_MSG = DBO.UFN_GET_STRING_BY_LANGKIND( @LANGKIND, '회사코드는 필수입니다.' ) GOTO ERROR END IF( ISNULL(@RCV_DT_FR,'') = '' OR ISNULL(@RCV_DT_TO,'') = '' ) BEGIN SET @P_ERR_MSG = DBO.UFN_GET_STRING_BY_LANGKIND( @LANGKIND, '입고기간은 필수입니다.' ) GOTO ERROR END SELECT @P_SYSCFG_CTR_NB_06 = CAST( CTR_NB AS INT ) FROM SYSCFG WHERE CO_CD = @CO_CD AND MODULE_CD = 'S' AND CTR_CD = '06' IF( @P_SYSCFG_CTR_NB_06 IS NULL OR @P_SYSCFG_CTR_NB_06 < 0 OR @P_SYSCFG_CTR_NB_06 > 4 ) SET @P_SYSCFG_CTR_NB_06 = 0 SELECT @P_SYSCFG_CTR_NB_07 = CAST( CTR_NB AS INT ) FROM SYSCFG WHERE CO_CD = @CO_CD AND MODULE_CD = 'S' AND CTR_CD = '07' IF( @P_SYSCFG_CTR_NB_07 IS NULL OR @P_SYSCFG_CTR_NB_07 < 0 OR @P_SYSCFG_CTR_NB_07 > 4 ) SET @P_SYSCFG_CTR_NB_07 = 4 SELECT @P_SYSCFG_FG_TY_10 = CAST( FG_TY AS INT ) FROM SYSCFG WHERE CO_CD = @CO_CD AND MODULE_CD = 'S' AND CTR_CD = '10' IF( @P_SYSCFG_FG_TY_10 IS NULL ) SET @P_SYSCFG_FG_TY_10 = 0 SELECT A.RCV_NB RCV_NB, LEFT( A.RCV_DT, 6 ) RCV_YM, A.RCV_DT RCV_DT, A.TR_CD TR_CD, STRADE.ATTR_NM ATTR_NM, --거래처명 STRADE.ATTR_NMK ATTR_NMK, --거래처명(외국어) A.PO_FG PO_FG, PO_FG.FLAG_NM PO_FG_NM, --거래구분명 A.EXCH_CD EXCH_CD, A.WH_CD WH_CD, SBASELOC.BASELOC_NM WH_NM, --창고명 SBASELOC.BASELOC_NMK WH_NMK, --창고명(외국어) A.PLN_CD PLN_CD, LPLNNERCD.PLN_NM PLN_NM, --담당자명 LPLNNERCD.PLN_NMK PLN_NMK, --담당자명(외국어) A.REMARK_DC_H REMARK_DC_H, A.RCV_SQ RCV_SQ, A.ITEM_CD ITEM_CD, SITEM.ITEM_NM ITEM_NM, SITEM.ITEM_NMK ITEM_NMK, SITEM.ITEM_DC ITEM_DC, SITEM.ITEM_DCK ITEM_DCK, SITEM.UNITMANG_DC UNITMANG_DC, A.REST_QT PO_QT, A.RCV_UM RCV_UM, ( CASE WHEN A.PO_FG = '0' THEN NULL ELSE A.EXCH_UM END ) EXCH_UM, A.RCVG_AM RCVG_AM, A.RCVV_AM RCVV_AM, A.RCVH_AM RCVH_AM, ( CASE WHEN A.PO_FG = '0' THEN NULL ELSE A.EXCH_AM END ) EXCH_AM, A.MGMT_CD MGMT_CD, LCTRL_MGM_D_LP.MGM_NM MGMT_NM, --관리구분명 LCTRL_MGM_D_LP.MGM_NMK MGMT_NMK, --관리구분명(외국어) A.PJT_CD PJT_CD, SPJT.PJT_NM PJT_NM, --프로젝트명 SPJT.PJT_NMK PJT_NMK, --프로젝트명(외국어) A.REMARK_DC REMARK_DC, A.MEMO_CD MEMO_CD, A.CHECK_PEN CHECK_PEN FROM ( SELECT LSTOCK.RCV_NB RCV_NB, LSTOCK.RCV_DT RCV_DT, LSTOCK.TR_CD TR_CD, LSTOCK.PO_FG PO_FG, LSTOCK.EXCH_CD EXCH_CD, LSTOCK.WH_CD WH_CD, LSTOCK.PLN_CD PLN_CD, LSTOCK.REMARK_DC REMARK_DC_H, LSTOCK_D.RCV_SQ RCV_SQ, LSTOCK_D.ITEM_CD ITEM_CD, ( LSTOCK_D.PO_QT - ISNULL( LSTOCK_D.CLS_QT, 0 ) ) REST_QT, LSTOCK_D.EXCH_UM EXCH_UM, ( CASE WHEN ISNULL( LSTOCK_D.CLS_QT, 0 ) = 0 THEN LSTOCK_D.EXCH_AM ELSE ( ( LSTOCK_D.PO_QT - ISNULL( LSTOCK_D.CLS_QT, 0 ) ) * LSTOCK_D.EXCH_UM ) END ) EXCH_AM, ( CASE WHEN LSTOCK.UMVAT_FG = '1' THEN LSTOCK_D.VAT_UM ELSE LSTOCK_D.RCV_UM END ) RCV_UM, ( CASE WHEN ISNULL( LSTOCK_D.CLS_QT, 0 ) = 0 THEN LSTOCK_D.RCVG_AM ELSE ( CASE WHEN LSTOCK.UMVAT_FG = '1' THEN DBO.UFN_ROUND( DBO.UFN_ROUND( ( LSTOCK_D.PO_QT - ISNULL( LSTOCK_D.CLS_QT, 0 ) ) * LSTOCK_D.VAT_UM, @P_SYSCFG_CTR_NB_06, @P_SYSCFG_FG_TY_10 ) / ( 1 + ISNULL( SVATCFG.VAT_RT, 0.1 ) ), @P_SYSCFG_CTR_NB_06, @P_SYSCFG_FG_TY_10 ) ELSE DBO.UFN_ROUND( ( LSTOCK_D.PO_QT - ISNULL( LSTOCK_D.CLS_QT, 0 ) ) * LSTOCK_D.RCV_UM, @P_SYSCFG_CTR_NB_06, @P_SYSCFG_FG_TY_10 ) END ) END ) RCVG_AM, ( CASE WHEN ISNULL( LSTOCK_D.CLS_QT, 0 ) = 0 THEN LSTOCK_D.RCVV_AM ELSE ( CASE WHEN LSTOCK.UMVAT_FG = '1' THEN DBO.UFN_ROUND( ( LSTOCK_D.PO_QT - ISNULL( LSTOCK_D.CLS_QT, 0 ) ) * LSTOCK_D.VAT_UM, @P_SYSCFG_CTR_NB_06, @P_SYSCFG_FG_TY_10 ) - DBO.UFN_ROUND( DBO.UFN_ROUND( ( LSTOCK_D.PO_QT - ISNULL( LSTOCK_D.CLS_QT, 0 ) ) * LSTOCK_D.VAT_UM, @P_SYSCFG_CTR_NB_06, @P_SYSCFG_FG_TY_10 ) / ( 1 + ISNULL( SVATCFG.VAT_RT, 0.1 ) ), @P_SYSCFG_CTR_NB_06, @P_SYSCFG_FG_TY_10 ) ELSE DBO.UFN_ROUND( DBO.UFN_ROUND( ( LSTOCK_D.PO_QT - ISNULL( LSTOCK_D.CLS_QT, 0 ) ) * LSTOCK_D.RCV_UM, @P_SYSCFG_CTR_NB_06, @P_SYSCFG_FG_TY_10 ) * ISNULL( SVATCFG.VAT_RT, 0.1 ), @P_SYSCFG_CTR_NB_06, @P_SYSCFG_FG_TY_10 ) END ) END ) RCVV_AM, ( CASE WHEN ISNULL( LSTOCK_D.CLS_QT, 0 ) = 0 THEN LSTOCK_D.RCVH_AM ELSE ( CASE WHEN LSTOCK.UMVAT_FG = '1' THEN DBO.UFN_ROUND( ( LSTOCK_D.PO_QT - ISNULL( LSTOCK_D.CLS_QT, 0 ) ) * LSTOCK_D.VAT_UM, @P_SYSCFG_CTR_NB_06, @P_SYSCFG_FG_TY_10 ) ELSE DBO.UFN_ROUND( ( LSTOCK_D.PO_QT - ISNULL( LSTOCK_D.CLS_QT, 0 ) ) * LSTOCK_D.RCV_UM, @P_SYSCFG_CTR_NB_06, @P_SYSCFG_FG_TY_10 ) + DBO.UFN_ROUND( DBO.UFN_ROUND( ( LSTOCK_D.PO_QT - ISNULL( LSTOCK_D.CLS_QT, 0 ) ) * LSTOCK_D.RCV_UM, @P_SYSCFG_CTR_NB_06, @P_SYSCFG_FG_TY_10 ) * ISNULL( SVATCFG.VAT_RT, 0.1 ), @P_SYSCFG_CTR_NB_06, @P_SYSCFG_FG_TY_10 ) END ) END ) RCVH_AM, LSTOCK_D.MGMT_CD MGMT_CD, LSTOCK_D.PJT_CD PJT_CD, LSTOCK_D.REMARK_DC REMARK_DC, ISNULL( LSTOCK_D.MEMO_CD, LSTOCK.MEMO_CD ) MEMO_CD, ISNULL( LSTOCK_D.CHECK_PEN, LSTOCK.CHECK_PEN ) CHECK_PEN FROM LSTOCK INNER JOIN LSTOCK_D ON LSTOCK_D.CO_CD = LSTOCK.CO_CD AND LSTOCK_D.RCV_NB = LSTOCK.RCV_NB LEFT OUTER JOIN LTRADEMGM ON LTRADEMGM.CO_CD = LSTOCK.CO_CD AND LTRADEMGM.TR_CD = LSTOCK.TR_CD LEFT OUTER JOIN SITEM ON SITEM.CO_CD = LSTOCK_D.CO_CD AND SITEM.ITEM_CD = LSTOCK_D.ITEM_CD LEFT OUTER JOIN ( SELECT VAT_CD, ISNULL(MAX(VAT_RT), 0) / 100.0 AS VAT_RT FROM SVATCFG WHERE VAT_FG IN ( '2', '4' ) GROUP BY VAT_CD ) AS SVATCFG ON SVATCFG.VAT_CD = LSTOCK.VAT_FG WHERE LSTOCK.CO_CD = @CO_CD AND ( LSTOCK_D.PO_QT - ISNULL( LSTOCK_D.CLS_QT, 0 ) ) <> 0 AND ( CHARINDEX( '|', ISNULL( @DIV_CDS, '' ) ) = 0 OR EXISTS( SELECT 1 FROM UFN_MULTI_PARAMETERS( @DIV_CDS ) WHERE STR_PARAMETER = LSTOCK.DIV_CD ) ) AND ( CHARINDEX( '|', ISNULL( @DEPT_CDS, '' ) ) = 0 OR EXISTS( SELECT 1 FROM UFN_MULTI_PARAMETERS( @DEPT_CDS ) WHERE STR_PARAMETER = LSTOCK.DEPT_CD ) ) AND ( CHARINDEX( '|', ISNULL( @EMP_CDS, '' ) ) = 0 OR EXISTS( SELECT 1 FROM UFN_MULTI_PARAMETERS( @EMP_CDS ) WHERE STR_PARAMETER = LSTOCK.EMP_CD ) ) AND LSTOCK.RCV_DT >= ISNULL(@RCV_DT_FR, '00000000') AND LSTOCK.RCV_DT <= ISNULL(@RCV_DT_TO, '99999999') AND ( CHARINDEX( '|', ISNULL( @TR_CDS, '' ) ) = 0 OR EXISTS( SELECT 1 FROM UFN_MULTI_PARAMETERS( @TR_CDS ) WHERE STR_PARAMETER = LSTOCK.TR_CD ) ) AND ( CHARINDEX( '|', ISNULL( @WH_CDS, '' ) ) = 0 OR EXISTS( SELECT 1 FROM UFN_MULTI_PARAMETERS( @WH_CDS ) WHERE STR_PARAMETER = LSTOCK.WH_CD ) ) AND ( CHARINDEX( '|', ISNULL( @PO_FGS, '' ) ) = 0 OR EXISTS( SELECT 1 FROM UFN_MULTI_PARAMETERS( @PO_FGS ) WHERE STR_PARAMETER = LSTOCK.PO_FG ) ) AND ( CASE WHEN CHARINDEX( '|', ISNULL( @PLN_CDS, '' ) ) = 0 OR ISNULL( @PLN_CD_FG, '' ) NOT IN ( '0', '1', '2' ) THEN 1 WHEN EXISTS ( SELECT 1 FROM UFN_MULTI_PARAMETERS(@PLN_CDS) WHERE ( @PLN_CD_FG = '0' AND STR_PARAMETER = SITEM.PURPLN_CD ) OR ( @PLN_CD_FG = '1' AND STR_PARAMETER = LTRADEMGM.PURPLN_CD ) OR ( @PLN_CD_FG = '2' AND STR_PARAMETER = LSTOCK.PLN_CD ) ) THEN 1 ELSE 0 END ) = 1 AND ( CASE WHEN ISNULL( @NB_DC_LIKE, '' ) = '' OR ISNULL( @NB_DC_FG, '' ) NOT IN ( '0', '1' ) THEN 1 WHEN @NB_DC_FG = '0' AND LSTOCK.RCV_NB LIKE '%' + ISNULL( @NB_DC_LIKE, '' ) + '%' THEN 1 WHEN @NB_DC_FG = '1' AND LSTOCK_D.REMARK_DC LIKE '%' + ISNULL( @NB_DC_LIKE, '' ) + '%' THEN 1 ELSE 0 END ) = 1 AND ( DBO.UFN_COMMON_BPM_LIKE_SITEM( @LANGKIND, LSTOCK_D.ITEM_CD, SITEM.ITEM_NM, SITEM.ITEM_NMK, SITEM.ITEM_DC, SITEM.ITEM_DCK, @ITEM_CD_FG, @ITEM_CD_LIKE, @ITEM_CD_FR, @ITEM_CD_TO, @ITEM_CD_MULTI ) ) = 1 AND ( CHARINDEX( '|', ISNULL( @MGMT_CDS, '' ) ) = 0 OR EXISTS ( SELECT 1 FROM UFN_MULTI_PARAMETERS( @MGMT_CDS ) WHERE STR_PARAMETER = LSTOCK_D.MGMT_CD ) ) AND ( CHARINDEX( '|', ISNULL( @PJT_CDS, '' ) ) = 0 OR EXISTS ( SELECT 1 FROM UFN_MULTI_PARAMETERS( @PJT_CDS ) WHERE STR_PARAMETER = LSTOCK_D.PJT_CD ) ) AND ( CHARINDEX( '|', ISNULL( @TRADE_GRP_CDS, '' ) ) = 0 OR EXISTS ( SELECT 1 FROM UFN_MULTI_PARAMETERS( @TRADE_GRP_CDS ) WHERE STR_PARAMETER = LTRADEMGM.TRADE_GRP ) ) AND ( CHARINDEX( '|', ISNULL( @AREA_CDS, '' ) ) = 0 OR EXISTS ( SELECT 1 FROM UFN_MULTI_PARAMETERS( @AREA_CDS ) WHERE STR_PARAMETER = LTRADEMGM.AREA_CD ) ) AND ( CHARINDEX( '|', ISNULL( @AREA_GRP_CDS, '' ) ) = 0 OR EXISTS ( SELECT 1 FROM UFN_MULTI_PARAMETERS( @AREA_GRP_CDS ) WHERE STR_PARAMETER = LTRADEMGM.AREA_GRP ) ) AND ( CASE WHEN CHARINDEX( '|', ISNULL( @PLNS_CDS, '' ) ) = 0 OR ISNULL( @PLN_CD_FG, '' ) NOT IN ( '0', '1', '2' ) THEN 1 WHEN EXISTS ( SELECT 1 FROM LPLNNERCD WHERE CO_CD = @CO_CD AND ( ( @PLN_CD_FG = '0' AND PLN_CD = SITEM.PURPLN_CD ) OR ( @PLN_CD_FG = '1' AND PLN_CD = LTRADEMGM.PURPLN_CD ) OR ( @PLN_CD_FG = '2' AND PLN_CD = LSTOCK.PLN_CD ) ) AND PLNS_CD IN ( SELECT STR_PARAMETER FROM UFN_MULTI_PARAMETERS(@PLNS_CDS) ) ) THEN 1 ELSE 0 END ) = 1 AND ( CHARINDEX( '|', ISNULL( @ITEMGRP_CDS, '' ) ) = 0 OR EXISTS ( SELECT 1 FROM UFN_MULTI_PARAMETERS( @ITEMGRP_CDS ) WHERE STR_PARAMETER = SITEM.ITEMGRP_CD ) ) AND ( CHARINDEX( '|', ISNULL( @Z1_MGM_CDS, '' ) ) = 0 OR EXISTS ( SELECT 1 FROM UFN_MULTI_PARAMETERS( @Z1_MGM_CDS ) WHERE STR_PARAMETER = SITEM.L_CD ) ) AND ( CHARINDEX( '|', ISNULL( @Z2_MGM_CDS, '' ) ) = 0 OR EXISTS ( SELECT 1 FROM UFN_MULTI_PARAMETERS( @Z2_MGM_CDS ) WHERE STR_PARAMETER = SITEM.M_CD ) ) AND ( CHARINDEX( '|', ISNULL( @Z3_MGM_CDS, '' ) ) = 0 OR EXISTS ( SELECT 1 FROM UFN_MULTI_PARAMETERS( @Z3_MGM_CDS ) WHERE STR_PARAMETER = SITEM.S_CD ) ) AND ( CHARINDEX( '|', ISNULL( @ACCT_FGS, '' ) ) = 0 OR EXISTS ( SELECT 1 FROM UFN_MULTI_PARAMETERS( @ACCT_FGS ) WHERE STR_PARAMETER = SITEM.ACCT_FG ) ) AND ( CHARINDEX( '|', ISNULL( @ODR_FGS, '' ) ) = 0 OR EXISTS ( SELECT 1 FROM UFN_MULTI_PARAMETERS( @ODR_FGS ) WHERE STR_PARAMETER = SITEM.ODR_FG ) ) AND ( CHARINDEX( '|', ISNULL( @EXCH_CDS, '' ) ) = 0 OR EXISTS ( SELECT 1 FROM UFN_MULTI_PARAMETERS( @EXCH_CDS ) WHERE STR_PARAMETER = LSTOCK.EXCH_CD ) ) ) A LEFT OUTER JOIN STRADE ON STRADE.CO_CD = @CO_CD AND STRADE.TR_CD = A.TR_CD LEFT OUTER JOIN UFN_FLAGS( @LANGKIND, 'PO_FG', @CO_CD ) PO_FG ON PO_FG.FLAG_CD = A.PO_FG LEFT OUTER JOIN SBASELOC ON SBASELOC.CO_CD = @CO_CD AND SBASELOC.BASELOC_CD = A.WH_CD LEFT OUTER JOIN LPLNNERCD ON LPLNNERCD.CO_CD = @CO_CD AND LPLNNERCD.PLN_CD = A.PLN_CD LEFT OUTER JOIN SITEM ON SITEM.CO_CD = @CO_CD AND SITEM.ITEM_CD = A.ITEM_CD LEFT OUTER JOIN LCTRL_MGM_D LCTRL_MGM_D_LP ON LCTRL_MGM_D_LP.CO_CD = @CO_CD AND LCTRL_MGM_D_LP.CTRL_CD = 'LP' AND LCTRL_MGM_D_LP.MGM_CD = A.MGMT_CD LEFT OUTER JOIN SPJT ON SPJT.CO_CD = @CO_CD AND SPJT.PJT_CD = A.PJT_CD ORDER BY RCV_NB, RCV_SQ SET NOCOUNT OFF RETURN ERROR: SET NOCOUNT OFF RAISERROR( @P_ERR_MSG, 16, 1 ) RETURN END