[MSSQL] OPEN Query

/* =============================================================================
프로그램ID : dbo.UP_ECS_ECL_MDMAFCSubList_Q
업  무  명 :  *** 데이터 조회
프로그램명 : *** 데이터 조회
최초작성일 : 2019.07.22
개  발  자 : 정동출
실      행  : [dbo].[UP_ECS_ECL_MDMAFCSubList_Q] 'HD4-1810-NW-454-01','1','PP0300F100' 
============================================================================= */
-- 2020.01.14 정동출 TEST_PKG_NO(TABLE : TB_ECS_MDM_PI_ISO_TST_PACKAGE) 데이터 조회 변경(UP_ECS_BAT_MDM_PI_ISO_AM_DAILY_U 배치)
ALTER PROC [dbo].[UP_ECS_ECL_MDMAFCSubList_Q]
@AFC_ISO_NO varchar(100)
,@AFC_ISO_REV  varchar(10)
,@DP_NAME  varchar(50)
AS                        
BEGIN

IF OBJECT_ID('tempdb..#FILTB') IS NOT NULL 
DROP TABLE  #FILTB;
IF OBJECT_ID('tempdb..#SUB_TABLE') IS NOT NULL 
DROP TABLE  #SUB_TABLE;
SELECT A.LINE_LIST
     , A.PART_NAME
 , A.AFC_ISO_NO
 , A.AFC_ISO_REV
 , ISNULL(A.DP_NAME,'') AS DP_NAME
 , ISNULL(A.CON_ISO_NO,'') AS CON_ISO_NO
 , ISNULL(A.SPOOL_NO,'') AS SPOOL_NO
 , ISNULL(A.TEST_PACKAGE,'') AS TEST_PKG_NO
 , ISNULL(A.ITEM_CREATED_TIME,'') AS ITEM_CREATED_TIME
 , CASE WHEN B.SCHD_DT IS NULL OR LEN(B.SCHD_DT)!=8 THEN ''
   ELSE SUBSTRING(B.SCHD_DT,1,4)+'-'+SUBSTRING(B.SCHD_DT,5,2)+'-'+SUBSTRING(B.SCHD_DT,7,2) END AS DP_PLAN_DT 
 , CASE WHEN B.GB = 'P' THEN 
  CASE WHEN (SELECT COUNT(ACTL_DT) FROM [SWP_KPI].[dbo].[TB_PLM_KPI_DP1_AGGR]                          
  WHERE GB = 'A' AND TOT_SER = 1 
    AND PROJ_NO = B.PROJ_NO
    AND DP_ACTV = B.DP_ACTV) >= B.PLN_DWG_BYVR 
   THEN (SELECT CASE WHEN MAX(ACTL_DT) IS NULL OR LEN(MAX(ACTL_DT))!=8 THEN ''
              ELSE SUBSTRING(MAX(ACTL_DT),1,4)+'-'+SUBSTRING(MAX(ACTL_DT),5,2)+'-'+SUBSTRING(MAX(ACTL_DT),7,2) END
         FROM [SWP_KPI].[dbo].[TB_PLM_KPI_DP1_AGGR]
  WHERE GB = 'A' AND TOT_SER = 1 AND PROJ_NO = B.PROJ_NO AND DP_ACTV = B.DP_ACTV)
   ELSE '' END                       
     ELSE 
CASE WHEN B.ACTL_DT IS NULL OR LEN(B.ACTL_DT)!=8 THEN ''
ELSE SUBSTRING(B.ACTL_DT,1,4)+'-'+SUBSTRING(B.ACTL_DT,5,2)+'-'+SUBSTRING(B.ACTL_DT,7,2) END
   END 
AS DP_ACTION_DT
, CASE WHEN B.SCHD_DT IS NULL THEN NULL ELSE B.PLN_DWG_BYVR END AS DP_PLAN_CNT
, CASE WHEN B.SCHD_DT IS NULL THEN NULL ELSE
  (SELECT COUNT(ACTL_DT)          
  FROM [SWP_KPI].[dbo].[TB_PLM_KPI_DP1_AGGR]
 WHERE GB = 'A' AND TOT_SER = 1 AND PROJ_NO = B.PROJ_NO AND DP_ACTV = B.DP_ACTV) 
   END
   AS DP_ACTION_CNT
  ,'Not found' AS CFILEYN 
  ,'Not found' AS SFILEYN  
  ,CONVERT(VARCHAR(100), ' ') AS CFILEOBJID
  ,CONVERT(VARCHAR(100), ' ')  AS SFILEOBJID  
  ,CONVERT(NVARCHAR(1000), ' ')  AS CFILENAME 
  ,CONVERT(NVARCHAR(1000), ' ')  AS SFILENAME 
INTO #SUB_TABLE         
FROM 
(
SELECT A.PROJ_NO
 , A.LINE_LIST
 , A.PART_NAME
 , A.AFC_ISO_NO
 , A.AFC_ISO_REV
 , CASE WHEN B.CON_ISO_NO IS NULL THEN ''
   ELSE 'T'+SUBSTRING(B.CON_ISO_NO,1,5)+@DP_NAME+'P1' END
   AS DP_NAME
 , B.CON_ISO_NO
 , B.SPOOL_NO
 , IFTB.SHI_PACKAGE AS TEST_PACKAGE
 , B.ITEM_CREATED_TIME
  FROM TB_ECS_MDM_PI_ISO_REV AS A
JOIN TB_ECS_MDM_PI_ISO_AM AS B
ON A.PART_NAME = B.ENG_MODEL_REF
LEFT JOIN TB_ECS_MDM_PI_ISO_TST_PACKAGE AS IFTB
ON B.CON_ISO_NO =IFTB.CON_ISO_NO
WHERE A.PROJ_NO = 'SN2235'
  AND A.AFC_ISO_NO = @AFC_ISO_NO
  AND A.AFC_ISO_REV = @AFC_ISO_REV
) AS A
LEFT OUTER JOIN [SWP_KPI].[dbo].[TB_PLM_KPI_DP1_AGGR] AS B
ON A.PROJ_NO = B.PROJ_NO
AND A.DP_NAME = B.DP_ACTV
AND B.GB = 'P'

IF EXISTS(SELECT * FROM #SUB_TABLE)
BEGIN

DECLARE @LINE_LIST varchar(100),
@PART_NAME varchar(256),
@AFC_ISO_NO_S varchar(100),
@AFC_ISO_REV_S varchar(10),
@DP_NAME_S varchar(200),
@CON_ISO_NO  varchar(100),
@SPOOL_NO  varchar(100),
@TEST_PACKAGE varchar(100),
@ITEM_CREATED_TIME varchar(100),
@DP_PLAN_DT varchar(100),
@DP_ACTION_DT varchar(100),
@DP_PLAN_CNT INT = NULL,
@DP_ACTION_CNT INT = NULL,
@CFILEYN CHAR(10),
@SFILEYN CHAR(10),
@CFILEOBJID varchar(100),
@SFILEOBJID varchar(100),
@CFILENAME nvarchar(1000),
@SFILENAME nvarchar(1000);

DECLARE @rownum INT

--FILE OBJ 호출 파라메터
DECLARE @PROJ_NO NVARCHAR(10)
DECLARE @OBJID_FILE nvarchar(100); 
DECLARE @SQLString NVARCHAR(MAX);
DECLARE @ParmDefinition nvarchar(4000);
DECLARE @OBJ_ID varchar(30);
DECLARE @STRCONDITION NVARCHAR(100);

 SET @rownum = 0;

  DECLARE page_cursor CURSOR FOR
  SELECT * FROM #SUB_TABLE;

  OPEN page_cursor      /* 커서를오픈한다 */
  FETCH NEXT FROM page_cursor 
INTO @LINE_LIST,
@PART_NAME,
@AFC_ISO_NO_S,
@AFC_ISO_REV_S,
@DP_NAME,
@CON_ISO_NO  ,
@SPOOL_NO  ,
@TEST_PACKAGE ,
@ITEM_CREATED_TIME,
@DP_PLAN_DT,
@DP_ACTION_DT,
@DP_PLAN_CNT,
@DP_ACTION_CNT,
@CFILEYN,
@SFILEYN,
@CFILEOBJID,
@SFILEOBJID,
@CFILENAME,
@SFILENAME;
  WHILE  @@FETCH_STATUS = 0
  BEGIN
SET @rownum = @rownum + 1;
--생산 iso no. 파일 업데이트
IF(ISNULL(@CFILEYN,'Not found') = 'Not found')
BEGIN

SET @PROJ_NO='SN2235'
SET @STRCONDITION = CONCAT(RTRIM(LTRIM(@CON_ISO_NO)), '-DWG-R%');

IF OBJECT_ID('tempdb..#FILTB') IS NOT NULL 
DROP TABLE  #FILTB;
CREATE TABLE #FILTB
(
STM_DCMTM_ID varchar(100),
ATCH_FILE_NM nvarchar(MAX)
);

SET @SQLString = 'SELECT * FROM
OPENQUERY(ERPIF,''SELECT D.STM_DCMTM_ID, ((TRIM(D.STM_FILE_NM) || ''''.'''' ||TRIM(D.APPL))) AS ATCH_FILE_NM
FROM    PLDTB_ISSUE_FILE_MAST IFM
,PLDTB_DWG_MAST       DM
,PLJTB_PROJ_DP        DP
,PLDTB_ISSUE_FILE     D
WHERE 1=1
AND     IFM.FIN_VER_YN  = ''''Y''''
AND     IFM.FIN_DW_NO   = DM.DW_NO
AND     DM.PROJ_NO     = DP.PROJ_NO 
AND     DM.DP_ACTV     = DP.DP_ACTV
AND     (DP.CTL_KEY     IN (''''DS04'''',''''DS05'''') OR (DP.CTL_KEY = ''''DS03'''' AND DP.DP_GB = ''''Y''''))
AND     IFM.DOC_NO      = D.DOC_NO
AND     IFM.DOC_PART    = D.DOC_PART
AND     IFM.DOC_TP      = D.DOC_TP
AND     IFM.DOC_VER     = D.DOC_VER
AND     IFM.DWG_KND NOT IN (''''B1'''',''''B2'''')
AND     IFM.FIN_DW_NO = D.DW_NO
AND DP.PROJ_NO = ''''' + @PROJ_NO +'''''
AND DP.DWG_KND NOT IN (SELECT CD FROM CMCTB_CDNM WHERE  CD_CLF =''''PLJP49'''' AND DEL_YN = ''''N'''' AND CD2 = ''''F'''')
AND DP.DWG_KND NOT IN (''''S1'''',''''SS'''') 
AND D.FILE_GB_CD NOT IN (''''B'''')
AND DP.DSN_SKL  = ''''P1''''
AND D.STM_FILE_NM LIKE ''''' + @STRCONDITION +'''''
'')'

INSERT INTO #FILTB
EXECUTE sp_executesql @SQLString

IF(Exists(SELECT * FROM #FILTB))
BEGIN
UPDATE PTB
SET CFILEYN ='Open'
,CFILEOBJID = FILETB.STM_DCMTM_ID
,CFILENAME=CONVERT(VARCHAR(100),FILETB.ATCH_FILE_NM)
FROM #SUB_TABLE AS PTB
JOIN (SELECT @AFC_ISO_NO_S AS AFC_ISO_NO, @AFC_ISO_REV_S AS AFC_ISO_REV , @CON_ISO_NO AS CON_ISO_NO , SEL.STM_DCMTM_ID, SEL.ATCH_FILE_NM
FROM
(SELECT * FROM #FILTB) AS SEL
) AS FILETB
ON PTB.AFC_ISO_NO = FILETB.AFC_ISO_NO
AND PTB.AFC_ISO_REV = FILETB.AFC_ISO_REV
AND PTB.CON_ISO_NO = FILETB.CON_ISO_NO
END
END
--SPOOL no. 파일 업데이트
IF(ISNULL(@SFILEYN,'Not found') = 'Not found')
BEGIN
SET @PROJ_NO='SN2235'
SET @STRCONDITION = CONCAT(RTRIM(LTRIM(@SPOOL_NO)), '%');

IF OBJECT_ID('tempdb..#SFILTB') IS NOT NULL 
DROP TABLE  #SFILTB;
CREATE TABLE #SFILTB
(
STM_DCMTM_ID varchar(100),
ATCH_FILE_NM nvarchar(MAX)
);

SET @SQLString = 'SELECT * FROM
OPENQUERY(ERPIF,''SELECT D.STM_DCMTM_ID, ((TRIM(D.STM_FILE_NM) || ''''.'''' ||TRIM(D.APPL))) AS ATCH_FILE_NM
FROM    PLDTB_ISSUE_FILE_MAST IFM
,PLDTB_DWG_MAST       DM
,PLJTB_PROJ_DP        DP
,PLDTB_ISSUE_FILE     D
WHERE 1=1
AND     IFM.FIN_VER_YN  = ''''Y''''
AND     IFM.FIN_DW_NO   = DM.DW_NO
AND     DM.PROJ_NO     = DP.PROJ_NO 
AND     DM.DP_ACTV     = DP.DP_ACTV
AND     (DP.CTL_KEY     IN (''''DS04'''',''''DS05'''') OR (DP.CTL_KEY = ''''DS03'''' AND DP.DP_GB = ''''Y''''))
AND     IFM.DOC_NO      = D.DOC_NO
AND     IFM.DOC_PART    = D.DOC_PART
AND     IFM.DOC_TP      = D.DOC_TP
AND     IFM.DOC_VER     = D.DOC_VER
AND     IFM.DWG_KND NOT IN (''''B1'''',''''B2'''')
AND     IFM.FIN_DW_NO = D.DW_NO
AND DP.PROJ_NO = ''''' + @PROJ_NO +'''''
AND DP.DWG_KND NOT IN (SELECT CD FROM CMCTB_CDNM WHERE  CD_CLF =''''PLJP49'''' AND DEL_YN = ''''N'''' AND CD2 = ''''F'''')
AND DP.DWG_KND NOT IN (''''S1'''',''''SS'''') 
AND D.FILE_GB_CD NOT IN (''''B'''')
AND DP.DSN_SKL  = ''''P1''''
AND D.STM_FILE_NM LIKE ''''' + @STRCONDITION +'''''
'')'

INSERT INTO #SFILTB
EXECUTE sp_executesql @SQLString

IF(Exists(SELECT * FROM #SFILTB))
BEGIN
UPDATE PTB
SET SFILEYN ='Open'
,SFILEOBJID = FILETB.STM_DCMTM_ID
,SFILENAME=CONVERT(VARCHAR(100),FILETB.ATCH_FILE_NM)
FROM #SUB_TABLE AS PTB
JOIN (SELECT @AFC_ISO_NO_S AS AFC_ISO_NO, @AFC_ISO_REV_S AS AFC_ISO_REV , @SPOOL_NO AS SPOOL_NO , SEL.STM_DCMTM_ID, SEL.ATCH_FILE_NM
FROM
(SELECT * FROM #SFILTB) AS SEL
) AS FILETB
ON PTB.AFC_ISO_NO = FILETB.AFC_ISO_NO
AND PTB.AFC_ISO_REV = FILETB.AFC_ISO_REV
AND PTB.SPOOL_NO = FILETB.SPOOL_NO
END

END

FETCH NEXT FROM page_cursor
INTO @LINE_LIST,
@PART_NAME,
@AFC_ISO_NO_S,
@AFC_ISO_REV_S,
@DP_NAME_S,
@CON_ISO_NO  ,
@SPOOL_NO  ,
@TEST_PACKAGE ,
@ITEM_CREATED_TIME,
@DP_PLAN_DT,
@DP_ACTION_DT,
@DP_PLAN_CNT,
@DP_ACTION_CNT,
@CFILEYN,
@SFILEYN,
@CFILEOBJID,
@SFILEOBJID,
@CFILENAME,
@SFILENAME;
  END

  CLOSE page_cursor
DEALLOCATE page_cursor

SELECT * FROM #SUB_TABLE
END
ELSE
BEGIN
SELECT A.LINE_LIST
 , CASE WHEN ISNULL(A.PART_NAME,'') = '' THEN ''
   ELSE 'Not found on AM DB.' END AS PART_NAME
 , A.AFC_ISO_NO
 , A.AFC_ISO_REV
 , ISNULL(A.DP_NAME,'') AS DP_NAME
 , '' AS CON_ISO_NO
 , ISNULL(A.SPOOL_NO,'') AS SPOOL_NO
 , ISNULL(A.TEST_PACKAGE,'') AS TEST_PACKAGE
 , ISNULL(A.ITEM_CREATED_TIME,'') AS ITEM_CREATED_TIME
 , CASE WHEN B.SCHD_DT IS NULL OR LEN(B.SCHD_DT)!=8 THEN ''
   ELSE SUBSTRING(B.SCHD_DT,1,4)+'-'+SUBSTRING(B.SCHD_DT,5,2)+'-'+SUBSTRING(B.SCHD_DT,7,2) END AS DP_PLAN_DT      
 , CASE WHEN B.GB = 'P' THEN 
  CASE WHEN (SELECT COUNT(ACTL_DT) FROM [SWP_KPI].[dbo].[TB_PLM_KPI_DP1_AGGR]                          
  WHERE GB = 'A' AND TOT_SER = 1 
    AND PROJ_NO = B.PROJ_NO
    AND DP_ACTV = B.DP_ACTV) >= B.PLN_DWG_BYVR 
   THEN (SELECT CASE WHEN MAX(ACTL_DT) IS NULL OR LEN(MAX(ACTL_DT))!=8 THEN ''
  ELSE SUBSTRING(MAX(ACTL_DT),1,4)+'-'+SUBSTRING(MAX(ACTL_DT),5,2)+'-'+SUBSTRING(MAX(ACTL_DT),7,2) END
 FROM [SWP_KPI].[dbo].[TB_PLM_KPI_DP1_AGGR]
  WHERE GB = 'A' AND TOT_SER = 1 AND PROJ_NO = B.PROJ_NO AND DP_ACTV = B.DP_ACTV)
   ELSE '' END                       
     ELSE 
CASE WHEN B.ACTL_DT IS NULL OR LEN(B.ACTL_DT)!=8 THEN ''
ELSE SUBSTRING(B.ACTL_DT,1,4)+'-'+SUBSTRING(B.ACTL_DT,5,2)+'-'+SUBSTRING(B.ACTL_DT,7,2) END
   END 
AS DP_ACTION_DT
, CASE WHEN B.SCHD_DT IS NULL THEN NULL ELSE B.PLN_DWG_BYVR END AS DP_PLAN_CNT
, CASE WHEN B.SCHD_DT IS NULL THEN NULL ELSE
  (SELECT COUNT(ACTL_DT)          
  FROM [SWP_KPI].[dbo].[TB_PLM_KPI_DP1_AGGR]
 WHERE GB = 'A' AND TOT_SER = 1 AND PROJ_NO = B.PROJ_NO AND DP_ACTV = B.DP_ACTV) 
   END 
   AS DP_ACTION_CNT          
  ,'Not found' AS CFILEYN 
  ,'Not found' AS SFILEYN  
  ,CONVERT(VARCHAR(100), '') AS CFILEOBJID
  ,CONVERT(VARCHAR(100), '')  AS SFILEOBJID 
  ,CONVERT(NVARCHAR(1000), '')  AS CFILENAME 
  ,CONVERT(NVARCHAR(1000), '')  AS SFILENAME
FROM 
(
SELECT A.LINE_LIST
 , A.PART_NAME
 , A.AFC_ISO_NO
 , A.AFC_ISO_REV
 , CASE WHEN B.DELEGATE_BLOCK1 IS NULL THEN ''
   ELSE 'T'+B.DELEGATE_BLOCK1+@DP_NAME+'P1' END
   AS DP_NAME
 , '' AS CON_ISO_NO
 , '' AS SPOOL_NO
 , '' AS TEST_PACKAGE
 , '' AS ITEM_CREATED_TIME
  FROM TB_ECS_MDM_PI_ISO_REV AS A
LEFT OUTER JOIN TB_ECS_MDM_PI_ISO_CD AS B
    ON A.ISSUED_BY = B.ISSUED_BY
AND A.DESIGN_AREA LIKE (REPLACE(B.DESIGN_AREA,'*','%'))
WHERE A.AFC_ISO_NO = @AFC_ISO_NO
  AND A.AFC_ISO_REV = @AFC_ISO_REV
) AS A
LEFT OUTER JOIN [SWP_KPI].[dbo].[TB_PLM_KPI_DP1_AGGR] AS B
ON B.PROJ_NO = 'SN2235'
AND A.DP_NAME = B.DP_ACTV
AND B.GB = 'P'
END

IF OBJECT_ID('tempdb..#FILTB') IS NOT NULL 
DROP TABLE  #FILTB;
IF OBJECT_ID('tempdb..#SFILTB') IS NOT NULL 
DROP TABLE  #SFILTB;
IF OBJECT_ID('tempdb..#SUB_TABLE') IS NOT NULL 
DROP TABLE  #SUB_TABLE;

END

 

--참조 사이트

MSSQL OPENQUERY 사용하기 - ③ (UNION / JOIN / 조건 UPDATE)