Q5/ws_objects/Ew_ware.pbl.src/uf_warebalc_storage.srf

$PBExportHeader$uf_warebalc_storage.srf
global type uf_warebalc_storage from function_object
end type

forward prototypes
global function integer uf_warebalc_storage (integer arg_balctype, long arg_balcdateint, long arg_storageid, datetime arg_balcdate, ref string arg_msg)
end prototypes

global function integer uf_warebalc_storage (integer arg_balctype, long arg_balcdateint, long arg_storageid, datetime arg_balcdate, ref string arg_msg);//====================================================================
// Function: uf_warebalc_storage()
//--------------------------------------------------------------------
// Description: 
//--------------------------------------------------------------------
// Arguments: 
//		value    	integer 	arg_balctype  //1:结存,0:反结存		
//		value    	long    	arg_storageid		
//		value    	datetime	arg_balcdate 		
//		reference	string  	arg_msg      		
//--------------------------------------------------------------------
// Returns:  integer
//--------------------------------------------------------------------
// Author:	yyx		Date: 2004.02.27
//--------------------------------------------------------------------
// Modify History: 
//	
//====================================================================

Long rslt = 1,cnt
DateTime balc_date,null_dt
Long ls_balcdateint

IF arg_storageid <= 0 THEN
	arg_msg = '请选择仓库'
	rslt = 0
	GOTO ext
END IF

IF arg_balctype = 1 THEN
	ls_balcdateint = Year(Date(arg_balcdate)) * 10000 + Month(Date(arg_balcdate)) * 100 + Day(Date(arg_balcdate))
	balc_date = DateTime(Date(arg_balcdate),Time(0))
	
	SELECT count(*) INTO :cnt
		FROM u_warebalc
		WHERE balcdate = :balc_date
		And storageid = :arg_storageid;
	IF cnt <> 0 THEN
		rslt = 0
		arg_msg = "仓库指定日期已进行过结存操作"
		GOTO ext
	END IF
	
	//检查单据
	IF uf_warebalc_check_inoutflag(arg_storageid,arg_balcdate,arg_msg) = 0 THEN
		rslt = 0
		GOTO ext
	END IF
	
	//处理异常数据
	IF sys_option_balc_clear0amt = 1 THEN
		UPDATE u_warebalc
			SET desamt = desamt + balcamt,
			desamt_notax = desamt_notax + balcamt_notax,
			balcamt_notax = 0,
			balcamt = 0
			WHERE (balcqty = 0)
			AND (balcamt <> 0)
			And (balcdateint = 0);
		IF sqlca.SQLCode <> 0 THEN
			rslt = 0
			arg_msg = '更新结存金额失败,'+sqlca.SQLErrText
			GOTO ext
		END IF
	END IF
	
	UPDATE u_warebalc
		SET balcdateint = :ls_balcdateint,
		balcdate = :arg_balcdate
		WHERE balcdateint = 0
		And storageid = :arg_storageid;
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = "更新仓库结存标记失败. ~n 原因:"+sqlca.SQLErrText
		GOTO ext
	END IF
	
	INSERT INTO u_warebalc
		(balcdateint,
		sptid_cusid,
		storageid,
		mtrlid,
		bgqty,
		bgaddqty,
		bgamt,
		bgamt_notax,
		incqty,
		incaddqty,
		incamt,
		desqty,
		desaddqty,
		desamt,
		balcqty,
		balcaddqty,
		balcamt,
		balcamt_notax,
		pypk,
		pypkaddqty,
		scid,
		status,
		woodcode,
		pcode)
		SELECT 0,
		u_warebalc.sptid_cusid,
		u_warebalc.storageid,
		u_warebalc.mtrlid,
		u_warebalc.balcqty,
		u_warebalc.balcaddqty,
		case when :sys_option_balc_clear0amt = 0 then u_warebalc.balcamt else case when (u_warebalc.balcqty = 0 OR u_warebalc.balcamt < 0)  then 0 else u_warebalc.balcamt END END,
		case when :sys_option_balc_clear0amt = 0 then u_warebalc.balcamt_notax else case when (u_warebalc.balcqty = 0 OR u_warebalc.balcamt_notax < 0)  then 0 else u_warebalc.balcamt_notax END END,
		0,
		0,
		0,
		0,
		0,
		0,
		u_warebalc.balcqty,
		u_warebalc.balcaddqty,
		case when :sys_option_balc_clear0amt = 0 then u_warebalc.balcamt else case when (u_warebalc.balcqty = 0 OR u_warebalc.balcamt < 0)  then 0 else u_warebalc.balcamt END END,
		case when :sys_option_balc_clear0amt = 0 then u_warebalc.balcamt_notax else case when (u_warebalc.balcqty = 0 OR u_warebalc.balcamt_notax < 0)  then 0 else u_warebalc.balcamt_notax END END,
		0,
		0,
		u_warebalc.scid,
		u_warebalc.status,
		u_warebalc.woodcode,
		u_warebalc.pcode
		FROM u_warebalc
		WHERE ( (abs(u_warebalc.bgqty)+abs(u_warebalc.desqty)+abs(u_warebalc.incqty)+abs(u_warebalc.balcqty) <> 0) OR (abs(u_warebalc.bgamt)+ abs(u_warebalc.incamt)+abs(u_warebalc.desamt)+abs(u_warebalc.balcamt) <> 0) )
		AND ( u_warebalc.storageid = :arg_storageid )
		And ( u_warebalc.balcdateint = :ls_balcdateint );
		
	IF sqlca.SQLCode <> 0 THEN
		arg_msg = "结存操作失败 ~n原因:"+sqlca.SQLErrText
		rslt = 0
		GOTO ext
	END IF
	
	// 写入日表 upartbalc
	UPDATE u_inware
		SET balcflag = 1 ,
		u_inware.balcdateint = :ls_balcdateint
		WHERE ( u_inware.flag = 1 ) AND
		( u_inware.balcflag = 0 ) AND
		( u_inware.storageid = :arg_storageid ) AND
		( u_inware.balcdateint = 0 );
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = "结存操作失败 ~n原因:"+sqlca.SQLErrText
		GOTO ext
	END IF
	UPDATE u_outware
		SET balcflag = 1  ,
		u_outware.balcdateint = :ls_balcdateint
		WHERE ( u_outware.flag = 1 ) AND
		( u_outware.balcflag = 0 ) AND
		( u_outware.storageid = :arg_storageid ) AND
		( u_outware.balcdateint = 0 );
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = "结存操作失败 ~n原因:"+sqlca.SQLErrText
		GOTO ext
	END IF
	
	// //检查外协单据
	//	IF uf_wfjg_warebalc_check_inoutflag(arg_balcdate,arg_msg) = 0 THEN
	//		rslt = 0
	//		GOTO ext
	//	END IF
	//	//	
	//	
	//	UPDATE ow_wfjgbalc
	//		SET balcdateint = :ls_balcdateint,
	//		balcdate = :arg_balcdate
	//		Where balcdateint = 0;
	//		
	//	IF sqlca.SQLCode <> 0 THEN
	//		rslt = 0
	//		arg_msg = "更新外协供应商仓库结存标记失败. ~n 原因:"+sqlca.SQLErrText
	//		GOTO ext
	//	END IF
	//	
	//	INSERT INTO ow_wfjgbalc
	//		(scid,
	//		balcdateint,
	//		mtrlid,
	//		sptid,
	//		status,
	//		woodcode,
	//		bgqty,
	//		bgamt,
	//		outqty,
	//		outamt,
	//		reoutqty,
	//		reoutamt,
	//		thqty,
	//		thamt,
	//		inqty,
	//		inamt,
	//		balcqty,
	//		balcamt)
	//		SELECT ow_wfjgbalc.scid,
	//		0,
	//		ow_wfjgbalc.mtrlid,
	//		ow_wfjgbalc.sptid,
	//		ow_wfjgbalc.status,
	//		ow_wfjgbalc.woodcode,
	//		ow_wfjgbalc.balcqty,
	//		ow_wfjgbalc.balcamt,
	//		0,
	//		0,
	//		0,
	//		0,
	//		0,
	//		0,
	//		0,
	//		0,
	//		ow_wfjgbalc.balcqty,
	//		ow_wfjgbalc.balcamt
	//		FROM ow_wfjgbalc
	//		WHERE  (( abs(ow_wfjgbalc.bgqty)+abs(ow_wfjgbalc.outqty)+abs(ow_wfjgbalc.reoutqty)+abs(ow_wfjgbalc.thqty)+abs(ow_wfjgbalc.inqty)+abs(ow_wfjgbalc.balcqty) <> 0  )
	//		OR (abs(ow_wfjgbalc.bgamt)+abs(ow_wfjgbalc.outamt)+abs(ow_wfjgbalc.reoutamt)+abs(ow_wfjgbalc.thamt)+abs(ow_wfjgbalc.inamt)+abs(ow_wfjgbalc.balcamt) <> 0  ))
	//		And ( ow_wfjgbalc.balcdateint = :ls_balcdateint ) ;
	//		
	//	
	//	update ow_wfjg_in set balcflag=1 
	//			where flag=1 and ( ow_wfjg_in.balcflag = 0 ) AND 	
	//			( ow_wfjg_in.balcdateint = 0 );
	//	IF sqlca.SQLCode <> 0 THEN
	//		rslt = 0
	//		arg_msg = "结存操作失败 ~n原因:"+sqlca.SQLErrText
	//		GOTO ext
	//	END IF
	//	
	//	update ow_wfjg_out set balcflag=1 
	//			where flag=1 and ( ow_wfjg_out.balcflag = 0 ) AND 	
	//			( ow_wfjg_out.balcdateint = 0 );
	//	IF sqlca.SQLCode <> 0 THEN
	//		rslt = 0
	//		arg_msg = "结存操作失败 ~n原因:"+sqlca.SQLErrText
	//		GOTO ext
	//	END IF
	
ELSE
	SELECT count(*) INTO :cnt FROM u_inware
		WHERE flag  = 1
		AND ( balcdateint > :arg_balcdateint OR balcdateint = 0 )
		And storageid = :arg_storageid;
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '仓库反结存失败,查询进仓单结存日期后是否有单失败'
		GOTO ext
	END IF
	
	IF cnt > 0 THEN
		rslt = 0
		arg_msg = '仓库反结存失败,进仓单结存日期后已经有单审核,如果要反结存请将进仓单反审核'
		GOTO ext
	END IF
	
	
	cnt = 0
	SELECT count(*) INTO :cnt FROM u_outware
		WHERE flag  = 1
		AND ( balcdateint > :arg_balcdateint OR balcdateint = 0 )
		And storageid = :arg_storageid;
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '仓库反结存失败,查询出仓单结存日期后是否有单失败'
		GOTO ext
	END IF
	
	IF cnt > 0 THEN
		rslt = 0
		arg_msg = '仓库反结存失败,出仓单结存日期后已经有单审核,如果要反结存请将出仓单反审核'
		GOTO ext
	END IF
	
	// 写入日表 upartbalc
	UPDATE u_inware
		SET balcflag = 0 ,
		u_inware.balcdateint = 0
		WHERE ( u_inware.flag = 1 ) AND
		( u_inware.balcflag = 1 ) AND
		( u_inware.storageid = :arg_storageid ) AND
		( u_inware.balcdateint = :arg_balcdateint );
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = "反结存操作失败 ~n原因:"+sqlca.SQLErrText
		GOTO ext
	END IF
	
	UPDATE u_outware
		SET balcflag = 0  ,
		u_outware.balcdateint = 0
		WHERE ( u_outware.flag = 1 ) AND
		( u_outware.balcflag = 1 ) AND
		( u_outware.storageid = :arg_storageid ) AND
		( u_outware.balcdateint = :arg_balcdateint );
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = "反结存操作失败 ~n原因:"+sqlca.SQLErrText
		GOTO ext
	END IF
	
	//-----------------------------------------	
	DELETE FROM u_warebalc
		WHERE balcdateint = 0
		And storageid = :arg_storageid;
	IF sqlca.SQLCode <> 0 THEN
		arg_msg = '反结存操作 ~n原因:'+sqlca.SQLErrText
		rslt = 0
		GOTO ext
	END IF
	
	SetNull(null_dt)
	UPDATE u_warebalc
		SET balcdateint = 0,
		balcdate = :null_dt
		WHERE balcdateint = :arg_balcdateint
		And storageid = :arg_storageid;
	IF sqlca.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = "更新仓库结存标记失败. ~n 原因:"+sqlca.SQLErrText
		GOTO ext
	END IF
END IF

ext:
IF rslt = 0 THEN
	ROLLBACK;
ELSE
	COMMIT;
END IF

RETURN rslt




end function