Q5/ws_objects/Ew_bsnobj.pbl.src/f_cmp_fifo_warebalc.srf

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

forward prototypes
global function integer f_cmp_fifo_warebalc (long arg_scid, long arg_mtrlwareid, boolean arg_ifcommit, ref transaction commit_transaction, ref string arg_msg)
end prototypes

global function integer f_cmp_fifo_warebalc (long arg_scid, long arg_mtrlwareid, boolean arg_ifcommit, ref transaction commit_transaction, ref string arg_msg);Long rslt = 1


Long ll_i
Decimal ld_newcost // 最新成本价
Decimal ld_bgqty,ld_bgamt,ld_incqty,ld_incamt,ld_desqty,ld_desamt
Decimal ld_balcqty,ld_balcamt
Long ll_mtrlid
String ls_status,ls_pcode,ls_woodcode
String ls_mtrlcode
Decimal ld_noallocqty,ld_wareamt
Decimal ld_noallocqty_sum,ld_wareamt_sum
Long  ll_sptid
decimal ld_incamt_notax,ld_desamt_notax,ld_wareamt_notax

Long cur_storageid



//
////结存查库存

//库存计结存
Long cnt,ll_scid
datastore ds_mtrlware


SELECT   storageid  INTO :cur_storageid 
	From u_mtrlware  Where scid = :arg_scid And mtrlwareid = :arg_mtrlwareid Using commit_transaction;
	




ds_mtrlware = Create datastore
ds_mtrlware.DataObject = 'ds_warebalc_view_cmpl_mtrlware_to_balc3' //ds_warebalc_view_cmpl_mtrlware_to_balc2
ds_mtrlware.SetTransObject(  commit_transaction)
ds_mtrlware.Retrieve(cur_storageid,arg_scid,arg_mtrlwareid) //
ds_mtrlware.AcceptText()

//IF ld_noallocqty_sum <> ds_mtrlware.Object.sumqty[1] OR &
//	ld_wareamt_sum <> ds_mtrlware.Object.sumamt[1] THEN

SELECT scid INTO :ll_scid
	FROM u_storage
	Where storageid = :cur_storageid  Using commit_transaction;
IF commit_transaction.SQLCode <> 0 THEN
	rslt = 0
	arg_msg = '查询仓库所属分部失败'
	GOTO ext
END IF



FOR ll_i = 1 To ds_mtrlware.RowCount()
	ll_mtrlid = 0
	ls_mtrlcode = ''
	ls_status = ''
	ls_woodcode = ''
	ls_pcode = ''
	ld_noallocqty = 0
	ld_wareamt = 0
	ll_sptid = 0
	cnt = 0
	
	ls_mtrlcode = ds_mtrlware.Object.u_mtrldef_mtrlcode[ll_i]
	ll_mtrlid = ds_mtrlware.Object.mtrlid[ll_i]
	
	ls_status = ds_mtrlware.Object.status[ll_i]
	ls_woodcode = ds_mtrlware.Object.woodcode[ll_i]
	ls_pcode = ds_mtrlware.Object.pcode[ll_i]
	ld_noallocqty = ds_mtrlware.Object.qty[ll_i]
	ld_wareamt = ds_mtrlware.Object.amt[ll_i]
	ll_sptid = ds_mtrlware.Object.u_mtrlware_sptid[ll_i]
	ld_wareamt_notax = ds_mtrlware.Object.amt_notax[ll_i]
	
	
	
	SELECT  sum(Round(u_inwaremx.qty * u_inwaremx.cost,2))  ,
		sum(u_inwaremx.qty),
		sum(Round(u_inwaremx.qty * u_inwaremx.cost_notax,2))
		INTO :ld_incamt,:ld_incqty,:ld_incamt_notax
		FROM u_inwaremx,u_inware
		WHERE u_inwaremx.scid = u_inware.scid
		AND u_inwaremx.inwareid = u_inware.inwareid
		AND u_inware.storageid = :cur_storageid
		AND u_inware.balcdateint = 0
		AND u_inwaremx.mtrlid = :ll_mtrlid
		AND u_inwaremx.status = :ls_status
		AND u_inwaremx.woodcode = :ls_woodcode
		AND u_inwaremx.pcode = :ls_pcode
		AND u_inwaremx.sptid_cusid = :ll_sptid
		And u_inware.flag = 1 Using commit_transaction;
	IF commit_transaction.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '查询进仓失败,物料:'+ls_mtrlcode+','+sys_option_change_status+':'+ls_status+','+sys_option_change_woodcode+':'+ls_woodcode+','+sys_option_change_pcode+':'+ls_pcode
		GOTO ext
	END IF
	
	SELECT   sum(u_outwaremx.costamt) ,
		sum(u_outwaremx.qty),
		sum(Round(u_outwaremx.qty * u_outwaremx.cost_notax,2))
		INTO :ld_desamt,:ld_desqty,:ld_desamt_notax
		FROM u_outwaremx,u_outware
		WHERE u_outwaremx.scid = u_outware.scid
		AND u_outwaremx.outwareid = u_outware.outwareid
		AND u_outware.storageid = :cur_storageid
		AND u_outware.balcdateint = 0
		AND u_outwaremx.mtrlid = :ll_mtrlid
		AND u_outwaremx.status = :ls_status
		AND u_outwaremx.woodcode = :ls_woodcode
		AND u_outwaremx.pcode = :ls_pcode
		AND u_outwaremx.sptid = :ll_sptid
		And u_outware.flag = 1 Using commit_transaction;
	IF commit_transaction.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '查询出仓失败,物料:'+ls_mtrlcode+','+sys_option_change_status+':'+ls_status+','+sys_option_change_woodcode+':'+ls_woodcode+','+sys_option_change_pcode+':'+ls_pcode
		GOTO ext
	END IF
	
	IF IsNull(ld_desqty) THEN ld_desqty = 0
	IF IsNull(ld_incqty) THEN ld_incqty = 0
	IF IsNull(ld_desamt) THEN ld_desamt = 0
	IF IsNull(ld_incamt) THEN ld_incamt = 0
	IF IsNull(ld_desamt_notax) THEN ld_desamt_notax = 0	
	IF IsNull(ld_incamt_notax) THEN ld_incamt_notax = 0	
	UPDATE u_warebalc
		SET balcqty = :ld_noallocqty,
		balcamt = :ld_wareamt,
		balcamt_notax = :ld_wareamt_notax,
		bgqty = :ld_noallocqty + :ld_desqty - :ld_incqty,
		bgamt = :ld_wareamt + :ld_desamt - :ld_incamt,
		bgamt_notax = :ld_wareamt_notax + :ld_desamt_notax - :ld_incamt_notax,
		incqty = :ld_incqty,
		incamt = :ld_incamt,
		incamt_notax = :ld_incamt_notax,
		desqty = :ld_desqty,
		desamt = :ld_desamt,
		desamt_notax = :ld_desamt_notax
		WHERE mtrlid = :ll_mtrlid
		AND balcdateint = 0
		AND Storageid = :cur_storageid
		AND status = :ls_status
		AND woodcode = :ls_woodcode
		AND pcode = :ls_pcode
		And sptid_cusid = :ll_sptid Using commit_transaction;
	IF commit_transaction.SQLCode <> 0 THEN
		rslt = 0
		arg_msg = '更新结存失败,物料:'+ls_mtrlcode+','+sys_option_change_status+':'+ls_status+','+sys_option_change_woodcode+':'+ls_woodcode+','+sys_option_change_pcode+':'+ls_pcode+"  "+sqlca.sqlerrtext
		GOTO ext
	ELSEIF  commit_transaction.SQLNRows = 0 THEN
		INSERT INTO u_warebalc
			(scid,
			balcdateint,
			storageid,
			mtrlid,
			status,
			woodcode,
			pcode,
			bgqty,
			bgamt,
			incqty,
			incamt,
			desqty,
			desamt,
			balcqty,
			balcamt,
			sptid_cusid,
			balcamt_notax,
			bgamt_notax,
			incamt_notax,
			desamt_notax)
			VALUES
			(:sys_scid,0,
			:cur_storageid,
			:ll_mtrlid,
			:ls_status,
			:ls_woodcode,
			:ls_pcode,
			:ld_noallocqty + :ld_desqty - :ld_incqty,
			:ld_wareamt + :ld_desamt - :ld_incamt,
			:ld_incqty,
			:ld_incamt,
			:ld_desqty,
			:ld_desamt,
			:ld_noallocqty,
			:ld_wareamt,
			:ll_sptid,
			:ld_wareamt_notax,
			:ld_wareamt_notax + :ld_desamt_notax - :ld_incamt_notax,
			:ld_incamt_notax,
			:ld_desamt_notax) Using commit_transaction;
		IF commit_transaction.SQLCode <> 0 THEN
			rslt = 0
			arg_msg = '新加结存失败,物料:'+ls_mtrlcode+','+sys_option_change_status+':'+ls_status+','+sys_option_change_woodcode+':'+ls_woodcode+','+sys_option_change_pcode+':'+ls_pcode
			GOTO ext
		END IF
	END IF
	
NEXT
//END IF







ext:
IF rslt = 0 THEN
	ROLLBACK Using commit_transaction;
	
	
ELSEIF rslt = 1 And arg_ifcommit THEN
	COMMIT Using commit_transaction;
END IF

Destroy ds_mtrlware
RETURN rslt

end function