~sjz/TLM.git - Gitblit

			@@ -142,12 +142,12 @@
			SELECT SUM(C.QUANTITY) AS SQ_QTY, SUM(D.QUANTITY_OK) AS OK_QTY
			FROM MES_INV_ITEM_OUT_ITEMS C
			LEFT JOIN (
			SELECT ITEM_OUT_ID, ITEM_NO, PBILL_NO, RK_NO, SUM(QUANTITY) AS QUANTITY_OK
			SELECT ITEM_OUT_ID, ITEM_NO,ITEM_ID, PBILL_NO, RK_NO, SUM(QUANTITY) AS QUANTITY_OK
			FROM MES_INV_ITEM_OUT_C_DETAILS
			GROUP BY ITEM_OUT_ID, ITEM_NO, PBILL_NO, RK_NO
			GROUP BY ITEM_OUT_ID, ITEM_NO,ITEM_ID, PBILL_NO, RK_NO
			) D
			ON D.ITEM_OUT_ID = C.ITEM_OUT_ID
			AND D.ITEM_NO = C.ITEM_NO
			AND D.ITEM_ID = C.ITEM_ID
			AND D.RK_NO = C.RK_NO
			AND NVL(C.PBILL_NO, '0') = NVL(D.PBILL_NO, '0')
			WHERE C.ITEM_OUT_ID = {0}", mesInvItemOuts.Id);
			@@ -227,22 +227,24 @@
			FROM MES_INV_ITEM_OUT_ITEMS C
			LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY,
			ITEM_NO,
			ITEM_ID,
			PBILL_NO,
			RK_NO,
			WORK_LINE
			FROM MES_INV_ITEM_OUT_ITEMS
			GROUP BY ITEM_NO, PBILL_NO, WORK_LINE,RK_NO) U
			ON U.ITEM_NO = C.ITEM_NO
			GROUP BY ITEM_NO,ITEM_ID, PBILL_NO, WORK_LINE,RK_NO) U
			ON U.ITEM_ID = C.ITEM_ID
			AND U.WORK_LINE = C.WORK_LINE
			AND U.PBILL_NO = C.PBILL_NO
			AND U.RK_NO = C.RK_NO
			LEFT JOIN (SELECT SUM(QUANTITY) RK_QTY,
			ITEM_NO,
			ITEM_ID,
			EBELN,
			WORK_LINE,BILL_NO
			FROM MES_INV_ITEM_IN_C_ITEMS
			GROUP BY ITEM_NO, EBELN, WORK_LINE,BILL_NO) s1
			ON S1.ITEM_NO = C.ITEM_NO
			ON S1.ITEM_ID = C.ITEM_ID
			AND S1.WORK_LINE = C.WORK_LINE
			AND S1.EBELN = C.PBILL_NO
			AND S1.BILL_NO = C.RK_NO
			@@ -258,7 +260,7 @@

			// 判断行是否重复
			var cf_Num = Db.Queryable<MesInvItemOutItems>()
			.Where(s1 => s1.ItemNo == item.ItemNo &&
			.Where(s1 => s1.ItemId == item.ItemId &&
			s1.WorkLine == item.WorkLine &&
			s1.PbillNo == item.PbillNo &&
			s1.ItemOutId == mesInvItemOuts.Id &&
			@@ -429,7 +431,7 @@
			(b, bar, c, d) => new
			JoinQueryInfos(
			JoinType.Left, b.ItemBarcode == bar.ItemBarcode,
			JoinType.Left, b.ItemNo == c.ItemNo &&
			JoinType.Left, b.ItemId == c.Id &&
			b.Company == c.Company &&
			b.Factory == c.Factory,
			JoinType.Left, b.DepotCode == d.DepotCode &&
			@@ -463,16 +465,16 @@
			NVL(s1.RK_QTY, 0) - NVL(KT_QTY, 0) KT_QTY,
			R.DEPOT_SECTION_CODE, S.ITEM_MODEL, S.ITEM_NAME,
			K.CGB014, Q.STAFF_NAME, R.DEPOT_CODE
			FROM MES_INV_ITEM_OUT_ITEMS C JOIN MES_ITEMS S ON C.ITEM_NO = S.ITEM_NO
			LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY, ITEM_NO, PBILL_NO, WORK_LINE, RK_NO
			FROM MES_INV_ITEM_OUT_ITEMS C JOIN MES_ITEMS S ON C.ITEM_ID = S.ID
			LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY, ITEM_NO,ITEM_ID, PBILL_NO, WORK_LINE, RK_NO
			FROM MES_INV_ITEM_OUT_ITEMS
			GROUP BY ITEM_NO, PBILL_NO, WORK_LINE, RK_NO) U
			ON U.ITEM_NO = C.ITEM_NO AND U.WORK_LINE = C.WORK_LINE
			GROUP BY ITEM_NO,ITEM_ID, PBILL_NO, WORK_LINE, RK_NO) U
			ON U.ITEM_ID = C.ITEM_ID AND U.WORK_LINE = C.WORK_LINE
			AND U.PBILL_NO = C.PBILL_NO AND U.RK_NO = C.RK_NO
			LEFT JOIN (SELECT SUM(QUANTITY) RK_QTY, ITEM_NO, EBELN, WORK_LINE, BILL_NO
			LEFT JOIN (SELECT SUM(QUANTITY) RK_QTY, ITEM_NO,ITEM_ID, EBELN, WORK_LINE, BILL_NO
			FROM mes_inv_item_in_c_details
			GROUP BY ITEM_NO, EBELN, WORK_LINE, BILL_NO) s1
			ON S1.ITEM_NO = C.ITEM_NO AND S1.WORK_LINE = C.WORK_LINE
			GROUP BY ITEM_NO,ITEM_ID, EBELN, WORK_LINE, BILL_NO) s1
			ON S1.ITEM_ID = C.ITEM_ID AND S1.WORK_LINE = C.WORK_LINE
			AND S1.EBELN = C.PBILL_NO AND S1.BILL_NO = C.RK_NO
			LEFT JOIN MES_ROH_IN_DATA K
			ON K.BILL_NO = C.PBILL_NO AND C.WORK_LINE = K.PURCHASE_ORDER_LINE_NUMBER
			@@ -480,10 +482,10 @@
			left join MES_STAFF Q on H.PURCHASER = Q.STAFF_NAME
			LEFT JOIN MES_SUPPLIER T ON H.SUPPLIER = T.ID
			LEFT JOIN (select *
			from (SELECT row_number() over (partition by DEPOT_CODE, ITEM_NO, EBELN order by CREATE_DATE) rn, DEPOT_CODE, DEPOT_SECTION_CODE, ITEM_NO,
			from (SELECT row_number() over (partition by DEPOT_CODE,ITEM_ID, EBELN order by CREATE_DATE) rn, DEPOT_CODE, DEPOT_SECTION_CODE,ITEM_ID
			EBELN FROM mes_inv_item_in_c_details
			where EBELN is not null)
			where rn = 1 and ROWNUM = 1) R ON R.ITEM_NO = C.ITEM_NO) where 1=1 {0}",
			where rn = 1 and ROWNUM = 1) R ON R.ITEM_ID = C.ITEM_ID) where 1=1 {0}",
			where);
			var results = Db.Ado.SqlQuery<MESInvItemOutItems>(sql);
			return results;
			@@ -499,13 +501,13 @@
			FROM (SELECT f_get_section_code2('1000', '1000', b.ITEM_NO,'{0}') DEPOT,
			B.ITEM_NO, A.ITEM_MODEL,A.ITEM_NAME,to_char(nvl(b.QUANTITY, 0) - nvl(D.QUANTITY_OK, 0),'FM9999999990.00') QTY
			FROM MES_INV_ITEM_OUT_ITEMS B LEFT JOIN mes_ITEMS A
			ON B.ITEM_NO = A.ITEM_NO LEFT JOIN (SELECT ITEM_OUT_ID,
			ITEM_NO,PBILL_NO,SUM(QUANTITY) QUANTITY_OK,RK_NO
			ON B.ITEM_ID = A.ID LEFT JOIN (SELECT ITEM_OUT_ID,
			ITEM_NO,ITEM_ID,PBILL_NO,SUM(QUANTITY) QUANTITY_OK,RK_NO
			FROM MES_INV_ITEM_OUT_C_DETAILS
			WHERE ITEM_OUT_ID = {1}
			GROUP BY ITEM_OUT_ID,ITEM_NO,PBILL_NO,RK_NO) D
			GROUP BY ITEM_OUT_ID,ITEM_NO,ITEM_ID,PBILL_NO,RK_NO) D
			ON D.ITEM_OUT_ID = B.ITEM_OUT_ID
			AND D.ITEM_NO = B.ITEM_NO
			AND D.ITEM_ID = B.ITEM_ID
			AND D.PBILL_NO = B.PBILL_NO
			AND D.RK_NO = B.RK_NO
			WHERE B.ITEM_OUT_ID = {1}
			@@ -545,21 +547,21 @@
			to_char(b.CREATE_DATE, 'yyyy-mm-dd') as CREATE_DATE
			from mes_inv_item_in_c_details b
			left join mes_inv_item_ins a on b.item_in_id = a.id
			LEFT JOIN (select s.item_no, s.work_line, s.Work_no,
			LEFT JOIN (select s.item_no,s.item_id, s.work_line, s.Work_no,
			sum(s.quantity) quantity, S.RK_NO
			from mes_inv_item_out_c_details s
			group by s.item_no, s.work_line, s.work_no, S.RK_NO) s1
			group by s.item_no,s.item_id, s.work_line, s.work_no, S.RK_NO) s1
			ON NVL(B.work_LINE, '0') = NVL(s1.work_LINE, '0')
			and B.work_no = s1.work_no
			and B.item_no = s1.item_no
			and B.item_id = s1.item_id
			AND B.BILL_NO = S1.RK_NO
			LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY, ITEM_NO, PBILL_NO, WORK_LINE, RK_NO
			LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY, ITEM_NO,ITEM_ID, PBILL_NO, WORK_LINE, RK_NO
			FROM MES_INV_ITEM_OUT_ITEMS
			GROUP BY ITEM_NO, PBILL_NO, WORK_LINE, RK_NO) U
			ON U.ITEM_NO = B.ITEM_NO
			GROUP BY ITEM_NO,ITEM_ID, PBILL_NO, WORK_LINE, RK_NO) U
			ON U.ITEM_ID = B.ITEM_ID
			AND U.WORK_LINE = B.WORK_LINE
			AND U.PBILL_NO = B.WORK_NO AND U.RK_NO = B.BILL_NO
			left join mes_items s2 on b.item_no = s2.item_no
			left join mes_items s2 on b.item_id = s2.id
			left join purdha da on da.dha001 = b.cbill_no
			WHERE A.BILL_NO LIKE 'Q%'
			AND A.CBILL_NO like 'L%'