XinYu_LZMES/.svn/pristine/13/137260c9fbe5222066ad6b2bcac57b3cceee68cf.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="成品库库存数据查询">
	<query id="UIM010230_01.SELECT"  desc="成品库库存数据查询">
		<![CDATA[
		--SELECT * FROM (
		SELECT /*(CASE
         WHEN XB_PACKAGE.GF_COMNNAME_FIND('E01002', A.CUR_PROG_CD) =
              '连退成品充当待机' AND a.DEFECTIVE = 'Y' then
          '1'
         ELSE
          '0'
       END)  erji,*/
       A.OLD_SAMPL_NO
       --XB_PACKAGE.GF_COMNNAME_FIND('A01004' , A.PRODNM_CD)  PRODNM_CD 
      ,
       A.SPEC_STL_GRD,
       (SELECT T.SM_CFNM
          FROM TBZ00_COMMCD T
         WHERE T.LG_CD = 'E01010'
           AND T.SM_CD = A.NONORD_CAUSE_CD) NONORD_CAUSE_CD
       
      ,
       A.COIL_THK,
       A.COIL_WTH,
       A.COIL_INDIA,
      -- A.PACKAGE_FL PACKAGE_FL,
       A.INSTR_COIL_THK,
       A.INSTR_COIL_WTH,
       A.ACT_WGT,
       A.DCS_THK,
       A.DCS_WTH,
       A.FB_YN,
       A.FB_REASON,
       A.DEFECTIVE,
       DECODE(A.ORD_FL, '1', '订单材', '2', '余材', '', '未判') ORD_FL,
       A.ORD_NO,
       A.ORD_SEQ,
       A.CUR_LOAD_LOC,
       DECODE(A.TOT_DEC_GRD,
              '1',
              '合格',
              '2',
              '不合格',
              '3',
              '次品',
              '4',
              '废品') TOT_DEC_GRD,
       DECODE(A.INGR_DEC_GRD,
              '0',
              '综合判定取消',
              '1',
              '合格',
              '2',
              '不合格') INGR_DEC_GRD,
       DECODE(A.MATLQLTY_DEC_GRD,
              '0',
              '无性能要求',
              '1',
              '合格',
              '2',
              '不合格') MATRL_END_CAUSE_CD,
       DECODE(A.SIZE_DEC_RST, '1', '合格', '2', '不合格') SIZE_DEC_RST,
       DECODE(A.EXTSHAPE_DEC_GRD,
              '1',
              '合格',
              '2',
              '订单外合格',
              '3',
              '次品',
              '4',
              '废品',
              '待判') EXTSHAPE_DEC_GRD,
       DECODE(A.WGT_DEC_RST, '1', '合格', '2', '不合格') WGT_DEC_RST,
       C_PKG_UIM.GET_STDDATESTR(A.YARD_ENTRY_DTIME) YARD_ENTRY_DTIME,
       C_PKG_UIM.GET_STDTIMESTR(A.YARD_ENTRY_USE_TIME) YARD_ENTRY_USE_TIME,
       (SELECT USERNAME
          FROM CORE_APP_USER U
         WHERE U.USERID = A.YARD_ENTRY_REG) YARD_ENTRY_REG,
       DECODE(A.YARD_ENTRY_SHIFT,
              '0',
              '常白班',
              '1',
              '早班',
              '2',
              '中班',
              '3',
              '晚班') YARD_ENTRY_SHIFT,
       DECODE(A.YARD_ENTRY_GROUP,
              '0',
              '常白班',
              '1',
              '甲班',
              '2',
              '乙班',
              '3',
              '丙班',
              '4',
              '丁班') YARD_ENTRY_GROUP,
       SUBSTR(B.SLAB_NO, 0, 10) CHARGE_NO,
       (SELECT T.SM_CFNM
          FROM TBZ00_COMMCD T
         WHERE T.LG_CD = 'A01012'
           AND T.SM_CD = C.DLIV_TP) DLIV_TP,
       (SELECT T.CUST_NM
          FROM TBZ00_CUSTOMER T
         WHERE T.CUST_CD = D.ORDCUST_CD) ORDCUST_CD
       /*,DECODE(A.CUR_PROG_CD,'DRC','连退卷充当待机','DBA','重卷指示待机','DBB','重卷作业待机'
                      ,'DCA','包装指示待机','DCB','包装作业待机','DED','综合判定待机','DFA','发货指示待机'
                      ,'DFB','发货待机','CCA','连退作业指示待机','CCB','连退作业待机','CCC','连退卷取待机'
                      ) CUR_PROG_CD*/,
       (CASE
         WHEN XB_PACKAGE.GF_COMNNAME_FIND('E01002', A.CUR_PROG_CD) =
              '连退成品充当待机' AND a.DEFECTIVE = 'Y' then
          '二级库存'
         ELSE
          XB_PACKAGE.GF_COMNNAME_FIND('E01002', a.CUR_PROG_CD)
       END)
       
       CUR_PROG_CD,
       C_PKG_UIM.GET_STDDATESTR(C.ORD_DEVLMT_DATE) ORD_DEVLMT_DATE,
       E.SMP_SEND_DTIME,
       E.SMP_WORK_DTIME,
       TO_CHAR(TO_DATE(A.TOT_DEC_DTIME, 'YYYY-MM-DD HH24:MI:SS'),
               'YYYY-MM-DD HH24:MI:SS') QLTY_DCS_DTIME,
       E.SMP_INTO_DTIME,
       TO_CHAR(ROUND(((TO_DATE(E.SMP_SEND_DTIME, 'YYYY-MM-DD HH24:MI:SS') -
                     TO_DATE(A.MILL_DTIME, 'YYYY-MM-DD HH24:MI:SS')) *
                     86400) / 3600,
                     1)) GET_SEND_DTIME,
       TO_CHAR(ROUND(((TO_DATE(A.TOT_DEC_DTIME, 'YYYY-MM-DD HH24:MI:SS') -
                     TO_DATE(E.SMP_SEND_DTIME, 'YYYY-MM-DD HH24:MI:SS')) *
                     86400) / 3600,
                     1)) TO_YANG_DTIME,
       C_PKG_UIM.GET_STDTIMESTR(A.MILL_DTIME) MILL_DTIME,
       C.XSQY,
       TO_CHAR(TO_DATE(J.DLIV_DIR_DATE, 'YYYY-MM-DD'), 'YYYY-MM-DD') || '/' || '' AFFIRMTIME,
       C_PKG_UIM.GET_STDDATESTR(C.ORD_PLAN_DATA) ORD_PLAN_DATA,
       pkg_quality_comm.FZ00_COMM('A01010',A.PACKAGE_LEVEL) PACKAGE_LEVEL,
       DECODE(A.CAL_TYPE, '2', '返修', '3', '过渡', NULL) RCAL,
       A.ORG_ORD_NO,
       A.ORG_ORD_SEQ,
       A.BEF_STL_GRD,
       A.EXTSHAPE_QUALITY,
       F.CAL_NO,
       H.STL_GRD,
       B.SLAB_DCS_DETAIL BPBZ,
       k.C_EXTSHAPE_REQ,
       K.C_PRODNM_CD,
       K.MK_CONTENT,-----------订单注备
      -- A.PRODNM_CD,
/*  XB_PACKAGE.GF_COMNNAME_FIND('B02001', A.CRK_CD1) || CASE
         WHEN A.CRK_CD2 IS NOT NULL THEN
          '+'
         ELSE
          ' '
       END || XB_PACKAGE.GF_COMNNAME_FIND('B02001', A.CRK_CD2) COIL_CRK,*/
       XB_PACKAGE.GF_COMNNAME_FIND('B02001', A.CRK_CD1) CRK_CD1,
       REGEXP_SUBSTR(A.CRK_CD1_TYPE, '[【][A-Za-z0-9,-]+[】]$') CRK_CD1_TYPE,
       XB_PACKAGE.GF_COMNNAME_FIND('B02001', A.CRK_CD2) CRK_CD2,
       REGEXP_SUBSTR(A.CRK_CD2_TYPE, '[【][A-Za-z0-9,-]+[】]$') CRK_CD2_TYPE,
       XB_PACKAGE.GF_COMNNAME_FIND('B02001', A.CRK_CD3) CRK_CD3,
       REGEXP_SUBSTR(A.CRK_CD3_TYPE, '[【][A-Za-z0-9,-]+[】]$') CRK_CD3_TYPE,
       XB_PACKAGE.GF_COMNNAME_FIND('B02001', A.CRK_CD4) CRK_CD4,
       REGEXP_SUBSTR(A.CRK_CD4_TYPE, '[【][A-Za-z0-9,-]+[】]$') CRK_CD4_TYPE,
       XB_PACKAGE.GF_COMNNAME_FIND('B02001', A.CRK_CD5) CRK_CD5,
       REGEXP_SUBSTR(A.CRK_CD5_TYPE, '[【][A-Za-z0-9,-]+[】]$') CRK_CD5_TYPE
       ,TA.*
       ,K.ROLL_SURFACE_YN  --辊面要求
       ,K.ANNOITA_YN  --涂油要求
       ,(CASE WHEN A.ORD_FL = '2' THEN '' ELSE
         K.C_PRODNM_CD || '/' || C.C_EXTSHAPE_REQ || '/' ||
         SUBSTR(PKG_QUALITY_COMM.FZ00_COMM('A01010', C.PAKMTH_TP), 0, 1) ||
         '/大于' || C.PROD_WGT_MIN || '小于' || C.PROD_WGT_MAX || '/' ||
         PKG_QUALITY_COMM.FZ00_COMM('A01010', C.PAKMTH_TP) || '/厚度(' ||
         K.C_ORD_THK_MIN || '~' || K.C_ORD_THK_MAX || ')/宽度(' ||
         K.C_ORD_WTH_MIN || '~' || K.C_ORD_WTH_MAX || ')/内径(' ||
         C.C_ORD_INDIA || ')' END) JISHUYAOQIU
  FROM C_TBC02_COIL_COMM A,
       TBG02_SLAB_COMM B,
       TBA01_ORD_LINE C,
       TBA01_ORD_COMM D,
       TBJ01_DLIV_DIR J,
       TBB02_LOT_COM E,
       TBE02_ORD_PRC K,
       (SELECT CK.STL_GRD      STL_GRD,
               CK.SPEC_STL_GRD SPEC_STL_GRD,
               CT.C_COIL_NO    C_COIL_NO
          FROM C_TBK02_COIL_COMM CK, C_TBF03_SPEC_MILL CT
         WHERE CK.OLD_SAMPL_NO = CT.COIL_NO) H,
       (SELECT MAX(CAL_NO) CAL_NO, EXIT_COIL_NO
          FROM L_TBF03_SPEC_MILL
         GROUP BY EXIT_COIL_NO) F
         ,(SELECT T1.OLD_SAMPL_NO  OLDSAMPLNO,
         	      MAX(DECODE(SUBSTR(T2.QLTY_CD, 1, 2) || T3.MS_QLTY_RP,
                  'YS000',
                  T2.QLTY_VAL_WK,
                  NULL)) LC_YSREL --屈服强度REL   ReL
      ,
       MAX(DECODE(SUBSTR(T2.QLTY_CD, 1, 2) || T3.MS_QLTY_RP,
                  'YS0.2',
                  T2.QLTY_VAL_WK,
                  NULL)) LC_YSRP --屈服强度Rp0.2   Rp0.2
      ,
       MAX(DECODE(SUBSTR(T2.QLTY_CD, 1, 2) || T3.MS_QLTY_RP,
                  'EL050',
                  T2.QLTY_VAL_WK,
                  NULL)) LC_EL50 --伸长率A50  伸长率A50
      ,
       MAX(DECODE(SUBSTR(T2.QLTY_CD, 1, 2) || T3.MS_QLTY_RP,
                  'EL080',
                  T2.QLTY_VAL_WK,
                  NULL)) LC_EL80 --伸长率A50  伸长率A80
      ,
       MAX(DECODE(SUBSTR(T2.QLTY_CD, 1, 2) || T3.MS_QLTY_RP,
                  'EL000',
                  T2.QLTY_VAL_WK,
                  NULL)) LC_EL --伸长率A(非定标) 伸长率A
      ,
       MAX(DECODE(SUBSTR(T2.QLTY_CD, 1, 2), 'TE', T2.QLTY_VAL_WK, NULL)) LC_TE --抗拉强度                            抗拉强度
      ,
       MAX(DECODE(T2.QLTY_CD, 'TT09', T2.QLTY_VAL_WK, NULL)) LC_R --r值
      ,
       MAX(DECODE(T2.QLTY_CD, 'TT20', T2.QLTY_VAL_WK, NULL)) LC_N --n值
      ,
       MAX(DECODE(T2.QLTY_CD, 'TT19', T2.QLTY_VAL_WK, NULL)) LC_BH --烘烤硬化
       ,
       MAX(DECODE(T2.QLTY_CD, 'DC01', T2.QLTY_VAL_WK, NULL)) LC_TS --铁损  
      ,
       MAX(DECODE(T2.QLTY_CD, 'DC02', T2.QLTY_VAL_WK, NULL)) LC_CG -- 磁感
      ,
       ROUND(AVG(CASE WHEN SUBSTR(T4.QLTY_CD,1,2) = 'HD' THEN T4.QLTY_VAL_WK ELSE NULL END),1)  LC_YD  ---硬度

  FROM C_TBC02_COIL_COMM T1, TBB02_WORK_QLTY T2 , TBB01_SEQ_QLTY  T3  , TBB02_REWORK_QLTY T4
 WHERE T1.SMP_NO = T2.SMP_NO
   AND T1.SMP_CUT_LOC = T2.SMP_CUT_LOC
   AND T1.SMP_NO = T4.SMP_NO(+)
   AND T1.SMP_CUT_LOC = T4.SMP_CUT_LOC(+)
   AND T2.QLTY_CD     = T3.QLTY_CD
   AND T1.COIL_STAT='2'
   GROUP BY T1.OLD_SAMPL_NO) TA
 WHERE '1' = '1'
   AND A.SLAB_NO = B.SLAB_NO(+)
   AND A.ORD_NO = C.ORD_NO(+)
   AND A.ORD_SEQ = C.ORD_SEQ(+)
   AND A.ORD_NO = D.ORD_NO(+)
   AND A.SMP_NO = E.SMP_NO(+)
   AND A.ORD_NO = K.ORD_NO(+)
   AND A.ORD_SEQ = K.ORD_SEQ(+)
   AND A.DLIV_DIRNO = J.DLIV_DIRNO(+)
   AND A.OLD_SAMPL_NO = F.EXIT_COIL_NO(+)
   AND A.COIL_STAT = '2'
   AND A.PACKAGE_FL='0'   --中间库只存放未包装的
   AND A.OLD_SAMPL_NO = TA.OLDSAMPLNO(+)
   AND NVL(A.EXTSHAPE_QUALITY,'NULLP') NOT IN('FP','CP')---屏蔽废次卷
   AND SUBSTR(A.OLD_SAMPL_NO, 1, 12) = H.C_COIL_NO(+)
   --AND A.CUR_PROG_CD  NOT IN ('DFA'  ,'DFB')
   AND A.OLD_SAMPL_NO || '&' LIKE '%' || ? || '%'
   AND A.SPEC_STL_GRD || '&' LIKE ? || '%'
   AND A.CUR_LOAD_LOC LIKE ? || '%'
   AND A.COIL_THK >= NVL(?, '000')
   AND A.COIL_THK <= NVL(?, '999')
   AND A.COIL_WTH >= NVL(?, '00000')
   AND A.COIL_WTH <= NVL(?, '99999')
   AND SUBSTR(NVL(A.YARD_ENTRY_DTIME, 99999999999999), 1, 8) >= NVL(?, 0)
   AND SUBSTR(NVL(A.YARD_ENTRY_DTIME, 0), 1, 8) <= NVL(?, 99999999999999)
   AND A.ORD_NO || '&' LIKE ? || '%'
   AND A.CUR_PROG_CD || '&' LIKE ? || '%'
   AND C.ORD_DEVLMT_DATE || '&' LIKE ? || '%'
   AND E.SMP_SEND_DTIME || '&' LIKE ? || '%'
   AND A.PRODNM_CD || '&' LIKE ? || '%'
   AND A.ORD_FL || '&' LIKE ? || '%'  --)T WHERE T.PACKAGE_FL='0' OR T.ERJI='1'

		 ]]>
	</query>
</queryMap>