XinYu_LZMES/.svn/pristine/59/59c2008630e59e7fce8e5941774926f08540cf18.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="中间库盘入盘出">
	<query id="UIM010290_03.SELECT"  desc="查询可发量">
		<![CDATA[
		 	SELECT 'FALSE' CHK,
               --T.OLD_SAMPL_NO COIL_NO,
               T.CUR_LOAD_LOC,
               T.COIL_THK THICK,
               T.COIL_WTH WIDTH,
               T.COIL_LEN LENGTH,
               T.EXTSHAPE_QUALITY,
               T.EXTSHAPE_DEC_GRD,
               T.ACT_WGT WEIGHT,
               T.SPEC_STL_GRD,
               L.YCSCCZYJ,
               L.YCJSCZYJ,
               decode(t.PACKAGE_FL,'0','未包装','1','已包装','') PACKAGE_FL,
               (SELECT tt.SM_CFNM FROM tbz00_commcd tt
                    WHERE tt.LG_CD = 'E01002' and tt.SM_CD = t.CUR_PROG_CD)  CUR_PROG_CD,        
               (SELECT tt.SM_CFNM FROM tbz00_commcd tt
               WHERE tt.LG_CD = 'E01010' and tt.SM_CD = t.NONORD_CAUSE_CD)  NONORD_CAUSE_CD, 
               T.ORG_ORD_NO ORD_NO,
               T.ORG_ORD_SEQ ORD_SEQ,
               C_PKG_UIM.GET_STDDATESTR(T.MILL_DTIME) PRODUCTED_TIME,
               decode(T.CAL_TYPE,'2','√','3','√') RCAL
               ,t.fb_yn
               ,t.FB_REASON
               ,T.DEFECTIVE
               ,TA.*
          FROM C_TBC02_COIL_COMM T,LZ_YCCZYJ L
               ,(
                          SELECT T1. LC_COIL_NO COIL_NO,
                 MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP,
                            'YS000',
                            T2.QLTY_VAL_WK,
                            NULL)) LC_YSREL --屈服强度REL   ReL
                ,
                 MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP,
                            'YS0.2',
                            T2.QLTY_VAL_WK,
                            NULL)) LC_YSRP --屈服强度Rp0.2   Rp0.2
                ,
                 MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP,
                            'EL050',
                            T2.QLTY_VAL_WK,
                            NULL)) LC_EL50 --伸长率A50  伸长率A50
                ,
                 MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP,
                            'EL080',
                            T2.QLTY_VAL_WK,
                            NULL)) LC_EL80 --伸长率A50  伸长率A80
                ,
                 MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.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 --烘烤硬化

            FROM (SELECT LC.OLD_SAMPL_NO LC_COIL_NO --连退卷号
                         
                        ,
                         GC.CHARGE_NO,
                         GC.LST_INGR_CD
                         
                        ,
                         LC.SMP_NO,
                         LC.SMP_CUT_LOC
                  
                    FROM TBG02_CHARGE_COMM_D GC,
                         TBH02_COIL_COMM     HC,
                         C_TBL02_COIL_COMM   SC,
                         C_TBC02_COIL_COMM   LC
                   WHERE GC.CHARGE_NO = SUBSTR(HC.SLAB_NO, 1, 10)
                     AND HC.SLAB_NO = SC.SLAB_NO
                     AND HC.SLAB_NO = LC.SLAB_NO
                     AND SUBSTR(LC.OLD_SAMPL_NO, 1, 12) = SC.COIL_NO
                     AND LC.CUR_PROG_CD IS NOT NULL
                     --AND LENGTH(LC.OLD_SAMPL_NO) = 15
                     AND LC.OLD_SAMPL_NO LIKE :1 || '%'
                     AND LC.COIL_STAT = '2'
           		     AND LC.CUR_LOAD_LOC IS not NULL
           		     AND LC.ORD_FL = '2'
                  
                  ) T1,
                 TBB02_WORK_QLTY T2,
                 TBB02_WORK_INGR T3,
                 TBB01_SEQ_QLTY T4
           WHERE T1.SMP_NO = T2.SMP_NO(+)
             AND T1.SMP_CUT_LOC = T2.SMP_CUT_LOC(+)
             AND T1.CHARGE_NO = T3.CHARGE_NO
             AND T1.LST_INGR_CD = T3.PROC_CD || T3.CHEM_SEQ
             AND T2.QLTY_CD = T4.QLTY_CD(+)
           GROUP BY T1.LC_COIL_NO
                    
                   ,
                    T1.CHARGE_NO,
                    T1.LST_INGR_CD
                    
                   ,
                    T1.SMP_NO,
                    T1.SMP_CUT_LOC

           ORDER BY T1.LC_COIL_NO

               ) TA
         WHERE T.COIL_STAT = '2'
           AND T.CUR_LOAD_LOC IS not NULL
           AND T.OLD_SAMPL_NO = L.COIL_NO(+)
           AND SUBSTR(T.MILL_DTIME,1,8)||'&' between :2||'000000' AND :3||'999999'
           AND T.OLD_SAMPL_NO||'&' LIKE :4||'%'
           AND T.ORD_FL = '2'
           AND T.OLD_SAMPL_NO  = TA.COIL_NO
		 ]]>
	</query>
	<query id="UIM010290_02.SELECT"  desc="查询可发量">
		<![CDATA[
		 	SELECT 'FALSE' CHK,
		       T.OLD_SAMPL_NO COIL_NO,
		       T.CUR_LOAD_LOC,
		       T.COIL_THK THICK,
		       T.COIL_WTH WIDTH,
		       T.COIL_LEN LENGTH,
		       T.ACT_WGT WEIGHT,
		       T.FB_REASON,
		       T.SPEC_STL_GRD,
		       (SELECT tt.SM_CFNM FROM tbz00_commcd tt
                WHERE tt.LG_CD = 'E01002' and tt.SM_CD = t.CUR_PROG_CD)  CUR_PROG_CD, 
		       T.ORG_ORD_NO ORD_NO,
		       T.ORG_ORD_SEQ ORD_SEQ,
		       C_PKG_UIM.GET_STDDATESTR(T.MILL_DTIME) PRODUCTED_TIME,
		       decode(T.CAL_TYPE,'2','√','3','√') RCAL
		       ,t.fb_yn
		       ,T.DEFECTIVE
		  FROM C_TBL02_COIL_COMM T
		 WHERE T.COIL_STAT = '2'
		   AND T.CUR_LOAD_LOC IS not NULL
		   AND SUBSTR(T.MILL_DTIME,1,8)||'&' LIKE :1||'%'
		   AND T.OLD_SAMPL_NO||'&' LIKE :2||'%'
		   and T.ORD_FL = '2'
		 ]]>
	</query>
	<query id="UIM010290_01.SELECT"  desc="查询可发量">
		<![CDATA[
		 	SELECT 'FALSE' CHK,
		       T.OLD_SAMPL_NO COIL_NO,
		       T.COIL_THK THICK,
		       T.COIL_WTH WIDTH,
		       T.COIL_LEN LENGTH,
		       T.ACT_WGT WEIGHT,
		       T.SPEC_STL_GRD,
		       T.CUR_LOAD_LOC,
		       (SELECT tt.SM_CFNM FROM tbz00_commcd tt
                WHERE tt.LG_CD = 'E01002' and tt.SM_CD = t.CUR_PROG_CD)  CUR_PROG_CD, 
		       T.ORG_ORD_NO ORD_NO,
		       T.ORG_ORD_SEQ ORD_SEQ,
		       C_PKG_UIM.GET_STDDATESTR(T.MILL_DTIME) PRODUCTED_TIME,
		       '' RCAL
		       ,t.fb_yn
		       ,T.ORD_FL
		       ,(select (SELECT H.USERNAME
                  FROM CORE_APP_USER H
                 WHERE H.USERID = z.REG_ID
                    OR H.CERTIFICATE = z.REG_ID AND ROWNUM=1)
          from TBE04_OPT_HIS z
         where z.RESERVE_FIELD1 = t.OLD_SAMPL_NO
           and z.REG_PGM_ID = 'FBCOIL'
           AND ROWNUM = 1) FBREG,
       (select Z.RESERVE_FIELD11
          from TBE04_OPT_HIS z
         where z.RESERVE_FIELD1 = t.OLD_SAMPL_NO
           and z.REG_PGM_ID = 'FBCOIL'
           AND ROWNUM = 1) FBTIME
           
		  FROM c_tbk02_coil_comm T
		 WHERE T.COIL_STAT = '2'
		   AND T.CUR_LOAD_LOC IS not NULL
		   AND SUBSTR(T.MILL_DTIME,1,8)||'&' LIKE :1||'%'
		   AND T.OLD_SAMPL_NO||'&' LIKE :2||'%'
		   AND T.CUR_PROG_CD <>'PCB'
		   AND NVL(CR_PLANT_TRS_CLF,'Y') LIKE :3||'%'
		  -- and T.ORD_FL = '2' 20220314王振基要求可查订单材
		 ]]>
	</query>
	<query id="UIM010290_04.SELECT"  desc="酸洗二级查询">
		<![CDATA[
		 SELECT 'FALSE' CHK,
       T.OLD_SAMPL_NO COIL_NO,
       T.CUR_LOAD_LOC,
       T.COIL_THK THICK,
       T.COIL_WTH WIDTH,
       T.COIL_LEN LENGTH,
       T.EXTSHAPE_QUALITY,
       T.EXTSHAPE_DEC_GRD,
       T.ACT_WGT WEIGHT,
       T.SPEC_STL_GRD,
       L.YCSCCZYJ,
       L.YCJSCZYJ,
       decode(t.PACKAGE_FL, '0', '未包装', '1', '已包装', '') PACKAGE_FL,
       (SELECT tt.SM_CFNM
          FROM tbz00_commcd tt
         WHERE tt.LG_CD = 'E01002'
           and tt.SM_CD = t.CUR_PROG_CD) CUR_PROG_CD,
       (SELECT tt.SM_CFNM
          FROM tbz00_commcd tt
         WHERE tt.LG_CD = 'E01010'
           and tt.SM_CD = t.NONORD_CAUSE_CD) NONORD_CAUSE_CD,
       T.ORG_ORD_NO ORD_NO,
       T.ORG_ORD_SEQ ORD_SEQ,
       C_PKG_UIM.GET_STDDATESTR(T.MILL_DTIME) PRODUCTED_TIME,
       decode(T.CAL_TYPE, '2', '√', '3', '√') RCAL,
       t.fb_yn,
       t.FB_REASON,
       T.DEFECTIVE,
       TA.*
  FROM C_TBl02_COIL_COMM T,LZ_YCCZYJ L,
       (SELECT T1. LC_COIL_NO ,
               MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP,
                          'YS000',
                          T2.QLTY_VAL_WK,
                          NULL)) LC_YSREL --屈服强度REL   ReL
              ,
               MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP,
                          'YS0.2',
                          T2.QLTY_VAL_WK,
                          NULL)) LC_YSRP --屈服强度Rp0.2   Rp0.2
              ,
               MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP,
                          'EL050',
                          T2.QLTY_VAL_WK,
                          NULL)) LC_EL50 --伸长率A50  伸长率A50
              ,
               MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.MS_QLTY_RP,
                          'EL080',
                          T2.QLTY_VAL_WK,
                          NULL)) LC_EL80 --伸长率A50  伸长率A80
              ,
               MAX(DECODE(SUBSTR(T4.QLTY_CD, 1, 2) || T4.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 --烘烤硬化
        
          FROM (SELECT LC.OLD_SAMPL_NO LC_COIL_NO --连退卷号
                       
                      ,
                       GC.CHARGE_NO,
                       GC.LST_INGR_CD
                       
                      ,
                       LC.SMP_NO,
                       LC.SMP_CUT_LOC
                
                  FROM TBG02_CHARGE_COMM_D GC,
                       TBH02_COIL_COMM     HC,
                       
                       C_TBL02_COIL_COMM LC
                 WHERE GC.CHARGE_NO = SUBSTR(HC.SLAB_NO, 1, 10)
                      
                   AND HC.SLAB_NO = LC.SLAB_NO
                      
                   AND LC.CUR_PROG_CD IS NOT NULL
                      --AND LENGTH(LC.OLD_SAMPL_NO) = 15
                   AND LC.OLD_SAMPL_NO LIKE :1 || '%'
                   AND LC.COIL_STAT = '2'
                   AND LC.CUR_LOAD_LOC IS not NULL
                   AND LC.ORD_FL = '2'
                
                ) T1,
               TBB02_WORK_QLTY T2,
               TBB02_WORK_INGR T3,
               TBB01_SEQ_QLTY T4
         WHERE T1.SMP_NO = T2.SMP_NO(+)
           AND T1.SMP_CUT_LOC = T2.SMP_CUT_LOC(+)
           AND T1.CHARGE_NO = T3.CHARGE_NO
           AND T1.LST_INGR_CD = T3.PROC_CD || T3.CHEM_SEQ
           AND T2.QLTY_CD = T4.QLTY_CD(+)
         GROUP BY T1.LC_COIL_NO,
                  T1.CHARGE_NO,
                  T1.LST_INGR_CD,
                  T1.SMP_NO,
                  T1.SMP_CUT_LOC
         ORDER BY T1.LC_COIL_NO
        
        ) TA
 WHERE T.COIL_STAT = '2'
   AND T.CUR_LOAD_LOC IS not NULL  
   AND SUBSTR(T.MILL_DTIME,1,8)||'&' between :2||'000000' AND :3||'999999'
           AND T.OLD_SAMPL_NO||'&' LIKE :4||'%'
   AND T.ORD_FL = '2'
   and T.LINE_TP='S'
   AND T.OLD_SAMPL_NO = TA.LC_COIL_NO(+)
   AND T.OLD_SAMPL_NO = L.COIL_NO(+)
		 ]]>
	</query>
	<query id="UIM010290_03.CALL" desc="" fetchSize="10">
		<![CDATA[
			 {call C_PKG_UIM.PRO_PEND_COIL03(:1,:2,:3,:4,:5,:6,:7,:8)}
		]]>
	</query>
	<query id="UIM010290_02.CALL" desc="" fetchSize="10">
		<![CDATA[
			 {call C_PKG_UIM.PRO_PEND_COIL02(:1,:2,:3,:4,:5,:6,:7)}
		]]>
	</query>
	<query id="UIM010290_01.CALL" desc="" fetchSize="10">
		<![CDATA[
			 {call C_PKG_UIM.PRO_PEND_COIL01(:1,:2,:3,:4,:5,:6,:7)}
		]]>
	</query>
	<query id="UIM010290_04.CALL" desc="" fetchSize="10">
		<![CDATA[
			 {call C_PKG_UIM.PRO_DEFECTIVE_COIL(:1,:2,:3,:4,:5,:6,:7)}
		]]>
	</query>
	<query id="UIM010290_05.CALL" desc="" fetchSize="10">
		<![CDATA[
			 {call C_PKG_UIM.PRO_DEFECTIVE_COIL02(:1,:2,:3,:4,:5,:6)}
		]]>
	</query>
	<query id="UIM010290_06.CALL" desc="录入余材处置意见">
		{call LZ_CZYJLR(?,?,?,?,?)}
	</query>
</queryMap>