reservelistmaster.asp

印刷物の発注、在庫管理、出庫処理を一元管理

<%
Class ReserveListMaster
	'/**********************************************
	'/* 堷摉懳徾挔昜偺専嶕
	'/*
	'/**********************************************
	Public Function ReserveSerchList(l_db,l_sysid,l_coid,l_kind,l_listmanage_no,l_listcd,l_listeda,l_leav)

		Dim l_name
		Dim l_listSql

		'捠忢惪媮SQL
		If l_kind = CLM_METHOD_NOMAL Then
			'For Each l_name In Request.Form
			'	l_paramdic.add l_name,Request.Form(l_name)
			'Next
			l_listSql = "SELECT DISTINCT LT.LIST_MANAGE_NO," &_
						"LT.LIST_CD,LT.LIST_EDA," &_
						"LT.LIST_NAME,LT.PACKING_COUNT," &_
						"LT.ARRIVAL_WAIT," &_
						"(SELECT NAME " &_
						"   FROM CODE_M " &_
						"  WHERE SYSTEM_ID = LT.SYSTEM_ID " &_
						"    AND CLASSIFIED_CODE = '" & CD_LIST_UNIT & "' " &_
						"    AND CODE = LT.LIST_UNIT) AS LIST_UNIT," &_
						"NVL(LT.UNIT_PRICE,0) AS UNIT_PRICE," &_
						"LT.LEAV_DIRECTION," &_
						"LT.LEAV_DIRECTION_COUNT," &_
						"LT.CLAIM_PERIOD_START," &_
						"LT.CLAIM_PERIOD_END," &_
						"LT.CLAIM_PERIOD_FLAG " &_
						"FROM LISTBASE_T LT,ORDER_T OD,STOCK_T ST "&_
						"WHERE LT.SYSTEM_ID = '" & l_sysId & "' " &_
						"  AND LT.COMPANY_ID = (SELECT COMPANY_ID " &_
						"                         FROM COMPANY_M " &_
						"                        WHERE SYSTEM_ID = '" & l_sysId & "' " &_
						"                          AND COMPANY_KIND = '0') " &_
						"  AND LT.LIST_CD = '" & l_listcd & "' " &_
						"  AND LT.LIST_EDA = '" & l_listeda & "'" &_
						"  AND LT.LIST_STATUS IN('" & LSTS_APPROVE & "') "
			Select Case l_leav
				Case "0"
					l_listsql = l_listsql & "  AND LT.OPEN_AUTH_LEAV IN ('1','2','3')"
				Case "1"
					l_listsql = l_listsql & "  AND LT.OPEN_AUTH_LEAV IN ('1','2')"
				Case "2"
					l_listsql = l_listsql & "  AND LT.OPEN_AUTH_LEAV IN ('1')"
			End Select
			l_listsql = l_listsql & "  AND LT.DEL_FLG <> '1' " &_
						"  AND LT.SYSTEM_ID = OD.SYSTEM_ID(+) " &_
						"  AND LT.COMPANY_ID = OD.COMPANY_ID(+) " &_
						"  AND LT.LIST_CD = OD.LIST_CD(+) " &_
						"  AND LT.LIST_EDA = OD.LIST_EDA(+) " &_
						"  AND LT.SYSTEM_ID = ST.SYSTEM_ID(+) " &_
						"  AND LT.COMPANY_ID = ST.COMPANY_ID(+) " &_
						"  AND LT.LIST_CD = ST.LIST_CD(+) " &_
						"  AND LT.LIST_EDA = ST.LIST_EDA(+) " &_ 
						"  AND (OD.ORDER_STATUS IN ('" & OSTS_APPROVE_FIN &_
							"','" & OSTS_ORDER &_
							"','" & OSTS_ORDER_CANCEL &_
							"','" & OSTS_DELIVERY_INST &_
							"','" & OSTS_DELIVERY &_
							"','" & OSTS_DELIVERY_CONF &_
							"','" & OSTS_PAYMENT_CONF &_
							"','" & OSTS_PAYMENT_FIN & "') " &_
						" OR ST.STOCK_NUM>0 " &_
						" OR (ST.STOCK_NUM <= 0 " &_
						" AND LT.ARRIVAL_WAIT = '1')) "

		'娙堈拲暥SQL
		ElseIf l_kind = CLM_METHOD_KANI Then
			l_listSql = "SELECT DISTINCT LT.LIST_MANAGE_NO," &_
						"LT.LIST_CD,LT.LIST_EDA," &_
						"LT.LIST_NAME,LT.PACKING_COUNT," &_
						"LT.ARRIVAL_WAIT," &_
						"(SELECT NAME " &_
						"   FROM CODE_M " &_
						"  WHERE SYSTEM_ID = LT.SYSTEM_ID " &_
						"    AND CLASSIFIED_CODE = '" & CD_LIST_UNIT & "' " &_
						"    AND CODE = LT.LIST_UNIT) AS LIST_UNIT," &_
						"NVL(LT.UNIT_PRICE,0) AS UNIT_PRICE," &_
						"LT.LEAV_DIRECTION," &_
						"LT.LEAV_DIRECTION_COUNT," &_
						"LT.CLAIM_PERIOD_START," &_
						"LT.CLAIM_PERIOD_END," &_
						"LT.OPEN_AUTH_LEAV," &_
						"LT.CLAIM_PERIOD_FLAG "
						'"FROM LISTBASE_T LT,ORDER_T OD,STOCK_T ST "&_
			l_listSql = l_listSql & "FROM LISTBASE_T LT,STOCK_T ST "&_
						"WHERE LT.SYSTEM_ID = '" & l_sysId & "' " &_
						"  AND LT.COMPANY_ID = (SELECT COMPANY_ID " &_
						"                         FROM COMPANY_M " &_
						"                        WHERE SYSTEM_ID = '" & l_sysId & "' " &_
						"                          AND COMPANY_KIND = '0') " &_
						"  AND LT.LIST_CD = '" & l_listcd & "' " &_
						"  AND LT.DEL_FLG <> '" & FLG_ON & "' "
						'"  AND ((LT.CLAIM_PERIOD_START <= '" & Date & "'" &_
						'"  AND LT.CLAIM_PERIOD_FLAG = '" & FLG_OFF & "') OR " &_
						'"  (LT.CLAIM_PERIOD_FLAG = '" & FLG_ON & "')) " &_
						'"  AND LT.CLAIM_PERIOD_END >= '" & Date & "'" 		
						'"  AND LT.SYSTEM_ID = OD.SYSTEM_ID(+) " &_
						'"  AND LT.COMPANY_ID = OD.COMPANY_ID(+) " &_
						'"  AND LT.LIST_CD = OD.LIST_CD(+) " &_
			l_listSql = l_listSql & "  AND LT.SYSTEM_ID = ST.SYSTEM_ID(+) " &_
						"  AND LT.COMPANY_ID = ST.COMPANY_ID(+) " &_
						"  AND LT.LIST_CD = ST.LIST_CD(+) " &_
						"  AND LT.LIST_EDA = ST.LIST_EDA(+) " 
						
			'師僶乕僕儑儞偱偺堷偒摉偰偺嵺偺
			If l_listeda <> "" Then
				l_listSql = l_listSql & "AND LT.LIST_EDA = '" & l_listeda & "' "
			End If
			l_listSql = l_listSql & " AND LT.LIST_STATUS IN('" & LSTS_APPROVE & "') "
			'嶍彍 敪拲忬嫷偼側偟丂2005/12/5
			'l_listSql = l_listSql & "  AND (OD.ORDER_STATUS IN ('" & OSTS_APPROVE_FIN &_
			'				"','" & OSTS_ORDER &_
			'				"','" & OSTS_ORDER_CANCEL &_
			'				"','" & OSTS_DELIVERY_INST &_
			'				"','" & OSTS_DELIVERY &_
			'				"','" & OSTS_DELIVERY_CONF &_
			'				"','" & OSTS_PAYMENT_CONF &_
			'				"','" & OSTS_PAYMENT_FIN & "')" &_
			'			" OR ST.STOCK_NUM>0 " &_
			'			" OR (ST.STOCK_NUM <= 0 " &_
			'			" AND LT.ARRIVAL_WAIT = '1')) "
		'	Select Case l_leav
		'		Case "0"
		'			l_listsql = l_listsql & "  AND LT.OPEN_AUTH_LEAV IN ('1','2','3')"
		'		Case "1"
		'			l_listsql = l_listsql & "  AND LT.OPEN_AUTH_LEAV IN ('1','2')"
		'		Case "2"
		'			l_listsql = l_listsql & "  AND LT.OPEN_AUTH_LEAV IN ('1')"
		'	End Select
			l_listSql = l_listSql & " ORDER BY LT.LIST_EDA "
'Response.Write l_listSql & "<br>"
		'弶婜堦惸攝晍SQL
		ElseIf l_kind = CLM_METHOD_ISSEI Then
			l_listSql = "SELECT DISTINCT LT.LIST_MANAGE_NO," &_
						"LT.LIST_CD,LT.LIST_EDA," &_
						"LT.LIST_NAME,LT.SET_COUNT," &_
						"LT.ARRIVAL_WAIT," &_
						"NVL(LT.UNIT_PRICE,0) AS UNIT_PRICE," &_
						"LT.LEAV_DIRECTION," &_
						"LT.LEAV_DIRECTION_COUNT," &_
						"LT.CLAIM_PERIOD_START," &_
						"LT.CLAIM_PERIOD_END," &_
						"LT.CLAIM_PERIOD_FLAG " &_
						"FROM LISTBASE_T LT,ORDER_T OD,STOCK_T ST "&_
						"WHERE LT.SYSTEM_ID = '" & l_sysId & "' " &_
						"  AND LT.COMPANY_ID = (SELECT COMPANY_ID " &_
						"                         FROM COMPANY_M " &_
						"                        WHERE SYSTEM_ID = '" & l_sysId & "' " &_
						"                          AND COMPANY_KIND = '0') " &_
						"  AND LT.LIST_CD = '" & l_listcd & "' " &_
					    "  AND LT.LIST_EDA = '" & l_listeda & "' " &_
						"  AND LT.DEL_FLG <> '1' " &_
						"  AND LT.OPEN_PERIOD_START <= '" & Date & "'" &_
						"  AND LT.OPEN_PERIOD_END >= '" & Date & "'" &_
						"  AND LT.LIST_STATUS IN('" & LSTS_APPROVE & "') "
			Select Case l_leav
				Case "0"
					l_listsql = l_listsql & "  AND LT.OPEN_AUTH_LEAV IN ('1','2','3')"
				Case "1"
					l_listsql = l_listsql & "  AND LT.OPEN_AUTH_LEAV IN ('1','2')"
				Case "2"
					l_listsql = l_listsql & "  AND LT.OPEN_AUTH_LEAV IN ('1')"
			End Select
				l_listsql = l_listsql & "  AND LT.SYSTEM_ID = OD.SYSTEM_ID(+) " &_
						"  AND LT.COMPANY_ID = OD.COMPANY_ID(+) " &_
						"  AND LT.LIST_CD = OD.LIST_CD(+) " &_
						"  AND LT.LIST_EDA = OD.LIST_EDA(+) " &_
						"  AND LT.SYSTEM_ID = ST.SYSTEM_ID(+) " &_
						"  AND LT.COMPANY_ID = ST.COMPANY_ID(+) " &_
						"  AND LT.LIST_CD = ST.LIST_CD(+) " &_
						"  AND LT.LIST_EDA = ST.LIST_EDA(+) " &_ 
						"  AND (OD.ORDER_STATUS IN ('" & OSTS_APPROVE_FIN &_
						"','" & OSTS_ORDER &_
						"','" & OSTS_ORDER_CANCEL &_
						"','" & OSTS_DELIVERY_INST &_
						"','" & OSTS_DELIVERY &_
						"','" & OSTS_DELIVERY_CONF &_
						"','" & OSTS_PAYMENT_CONF &_
						"','" & OSTS_PAYMENT_FIN & "') " &_
						" OR ST.STOCK_NUM >0) " 
		End If
'Response.Write l_listSql & "<br>"
		Set ReserveSerchList = l_db.ProcRecordset(l_listSql,10,0)
	End Function

	'/*******************************
	'/* 嵼屔悢偺庢摼
	'/*
	'/*
	'/*
	'/*******************************
	Public Function GetWearStock(l_db,l_sysId,l_coId,l_listcd,l_listeda,l_quantity,l_num,l_reserve_num,l_deli_place)
		Dim l_strSql

'		l_strSql = "SELECT " &_
'				   "A.SYSTEM_ID," &_
'				   "A." & l_deli_place & " AS DELIVERY_PLACE," &_
'				   "NVL(A." & l_reserve_num & ",0) + " & l_quantity & " AS RESERVE_COUNT," &_
'				   "NVL(A." & l_num & ",0) AS NUM_COUNT," &_
'				   "NVL(A.STOCK_NUM,0) AS STOCK_NUM," &_
'				   "B.COMPANY_ID," &_
'				   "B.COMPANY_NAME " &_
'				   "FROM STOCK_T A,COMPANY_M B " &_
'				   "WHERE A.SYSTEM_ID = '" & l_sysId & "' " &_
'				   "AND A.SYSTEM_ID = B.SYSTEM_ID " &_
'				   "AND A.COMPANY_ID = '" & l_coId & "' " &_
'				   "AND A.LIST_CD = '" & l_listcd & "' " 
'				If l_listeda <> "" Then
'					l_strSql = l_strSql & "AND A.LIST_EDA = '" & l_listeda & "' "
'				End If
'				l_strSql = l_strSql & "AND A." & l_num & " - NVL(A. " & l_reserve_num & ",0) >= " & l_quantity  & " " &_
'						   "AND A.SYSTEM_ID = B.SYSTEM_ID " &_
'						   "AND A." & l_deli_place & " = B.COMPANY_ID " &_
'						   "AND B.STATUS = '1' " &_
'						   "FOR UPDATE OF A." & l_num 
		l_strsql = "SELECT S_T." & l_num & "," &_
				   "       S_T." & l_deli_place & " AS DELIVERY_PLACE," &_
				   "       C_M.COMPANY_ID," &_
				   "       C_M.COMPANY_NAME," &_
				   "       W_T.QUANTITY AS RESERVE_QUANTITY," &_
				   "       D_T.QUANTITY2 AS RESERVE_QUANTITY2 " &_
				   "  FROM STOCK_T S_T,COMPANY_M C_M," 

		'攦偄暔偐偛僥乕僽儖偺悢検
		l_strsql = l_strsql & "(SELECT NVL(SUM(QUANTITY),0) AS QUANTITY " &_
				   			  "   FROM WORK_DATACLAIMORDER_T " &_
				   			  "  WHERE LIST_CD = '" & l_listcd & "' " 
		If l_listeda <> "" Then
			l_strsql = l_strsql & "    AND LIST_EDA = '" & l_listeda & "'"
		End If

		l_strsql = l_strsql & " AND WAREHOUSE_ID = (" 

		'
		l_strsql = l_strsql & "SELECT " & l_deli_place & " " &_
							  "  FROM STOCK_T " &_
							  " WHERE SYSTEM_ID = '" & l_sysId & "' " &_
							  "    AND LIST_CD = '" & l_listcd & "' " 
		If l_listeda <> "" Then
			l_strsql = l_strsql &  "    AND LIST_EDA = '" & l_listeda & "')"
		End If
		l_strsql = l_strsql & ") W_T," 

		'帒椏惪媮敪拲儅僗僞偺悢検
		l_strsql = l_strsql & "(SELECT NVL(SUM(QUANTITY),0) AS QUANTITY2 " &_
				   "   FROM DATACLAIMORDER_M " &_
				   "  WHERE LIST_CD = '" & l_listcd & "' " 
		If l_listeda <> "" Then
			l_strsql = l_strsql &  "    AND LIST_EDA = '" & l_listeda & "' "
		End If
		l_strsql = l_strsql & "    AND CLAIM_SITUATION IN" &_
				   "    ('" & CLM_SITUATION_UNSHIPPING & "'," &_
				   "     '" & CLM_SITUATION_WAIT & "'," &_
				   "     '" & CLM_SITUATION_WORKING & "') " &_
				   "               AND WAREHOUSE_ID =" &_
				   "           (SELECT " & l_deli_place & " " &_
				   "              FROM STOCK_T " &_
				   "             WHERE SYSTEM_ID = '" & l_sysId & "' " &_
				   "               AND LIST_CD = '" & l_listcd & "' " 
		If l_listeda <> "" Then
			l_strsql = l_strsql &  "    AND LIST_EDA = '" & l_listeda & "' "
		End If
		l_strsql = l_strsql & ") " &_
				   "     AND DEL_FLG = '" & FLG_OFF & "') D_T " &_
				   " WHERE S_T.SYSTEM_ID = '" & l_sysId & "' " &_
				   "  AND S_T.SYSTEM_ID = C_M.SYSTEM_ID " &_
				   "  AND S_T." & l_deli_place & " = C_M.COMPANY_ID " &_
				   "  AND S_T.LIST_CD = '" & l_listcd & "' " 
		If l_listeda <> "" Then
			l_strsql = l_strsql & "  AND S_T.LIST_EDA = '" & l_listeda & "' "
		End If
		l_strsql = l_strsql & "  AND S_T." & l_num & " - W_T.QUANTITY - D_T.QUANTITY2 >=" & l_quantity &_
					" AND S_T.DEL_FLG = '" & FLG_OFF & "' " &_
					" AND S_T.DEL_FLG = C_M.DEL_FLG "


'response.write l_strSql & "<br>"
		Set GetWearStock = l_db.ProcRecordset(l_strSql,10,0)

	End Function

	'/* 庴晅斣崋庢摼
	Public Function GetOrderNo(l_db,l_sysId)
		Dim l_recepNoSql
		Dim l_orderno
		Dim l_rset

		'-----2006/1/6 捛壛 1/11 曄峏-----
		'-----2006/1/11 曄峏-----
		l_recepNoSql = "select TRIM(MAX(SUBSTR(RECEPT_NO,0,2))) AS MAX_NO,"&_
					   "       TRIM(TO_CHAR(SYSDATE,'YY')) AS YY " &_
					   "  FROM DATACLAIMORDER_M " &_
					   " WHERE SYSTEM_ID  = '" & l_sysId & "' " &_
					   "FOR UPDATE OF DATACLAIMORDER_M "

		Set l_rset = l_db.ProcRecordset(l_recepNoSql,10,0)
		
		If l_rset.Fields.Item("MAX_NO") = l_rset.Fields.Item("YY") Then
			l_recepNoSql = "SELECT TRIM(TO_CHAR(NVL(MAX(SUBSTR(RECEPT_NO,0,8)),0)+1,'00000000')) AS RECEPT_NO " &_
						   "FROM DATACLAIMORDER_M " &_
						   "WHERE SYSTEM_ID  = '" & l_sysId & "' " &_
						   "FOR UPDATE OF DATACLAIMORDER_M "
			Set l_rset = l_db.ProcRecordset(l_recepNoSql,10,0)
			GetOrderNo = l_rset.Fields.Item("RECEPT_NO")
		Else
			GetOrderNo = l_rset.Fields.Item("YY") & "000001"
		End If
		'----------------------

		'l_recepNoSql = "SELECT TRIM(TO_CHAR(SYSDATE,'YY'))||" &_