Q5/ws_objects/Ew_sale_ls.pbl.src/f_find_mtrlware_ls.srf

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

forward prototypes
global function integer f_find_mtrlware_ls (string arg_mtrlcode, long arg_storageid, long arg_sptid, ref s_mtrlware_noalloc_array arg_s_mtrlware)
end prototypes

global function integer f_find_mtrlware_ls (string arg_mtrlcode, long arg_storageid, long arg_sptid, ref s_mtrlware_noalloc_array arg_s_mtrlware);Int rslt = 1
Long cnt = 0

String ls_like_mtrlcode
String ls_mtrlcode,ls_mtrlname,ls_unit,ls_mtrlmode,ls_mtrltype
String ls_plancode,ls_status,ls_woodcode,ls_pcode
Long ls_mtrlwareid,ls_mtrlid,ls_storageid,ll_sptid
Dec ls_noallocqty,ls_noauditingqty,ls_planprice,ls_cost,ls_lmsaleprice
String ls_mtrlsectype,ls_zxmtrlmode
Dec ld_cost_temp
Boolean if_find = FALSE
Dec ll_packqty
String ls_prdpackcode
Dec ld_noallocaddqty
Int li_Dxflag
Dec ld_wareamt
string ls_barcode

if arg_mtrlcode = '' then 
	rslt = 0
	GOTO ext
end if

IF Pos(Trim(arg_mtrlcode),'%') = 0 THEN
	ls_like_mtrlcode = '%'+ Trim(arg_mtrlcode) + '%'
ELSE
	ls_like_mtrlcode = Trim(arg_mtrlcode)
END IF

SELECT count(*) INTO :cnt
	FROM u_mtrlware, u_mtrldef
	WHERE ( u_mtrldef.mtrlid = u_mtrlware.mtrlid )
	AND ( u_mtrlware.sptid = :arg_sptid OR :arg_sptid = -1)
	AND ( u_mtrlware.storageid = :arg_storageid )
	AND ( u_mtrldef.mtrlcode = :arg_mtrlcode OR u_mtrldef.mtrlname = :arg_mtrlcode OR u_mtrldef.barcode = :arg_mtrlcode );
IF sqlca.SQLCode <> 0 THEN
	rslt = 0
	GOTO ext
ELSE
	IF cnt = 0 THEN
		SELECT count(*) INTO :cnt
			FROM u_mtrlware, u_mtrldef
			WHERE ( u_mtrldef.mtrlid = u_mtrlware.mtrlid )
			AND ( u_mtrlware.sptid = :arg_sptid OR :arg_sptid = -1)
			AND ( u_mtrlware.storageid = :arg_storageid )
			AND ( u_mtrldef.mtrlcode Like :ls_like_mtrlcode OR u_mtrldef.mtrlname Like :ls_like_mtrlcode OR u_mtrldef.barcode Like :ls_like_mtrlcode );
		IF sqlca.SQLCode <> 0 THEN
			rslt = 0
			GOTO ext
		ELSE
				SELECT top 1 u_mtrlware.mtrlwareid ,
					u_mtrlware.mtrlid,
					u_mtrldef.mtrlcode,
					u_mtrldef.mtrlname,
					u_mtrldef.unit,
					u_mtrlware.noallocqty,
					u_mtrlware.planprice,
					u_mtrlware.storageid,
					u_mtrlware.plancode,
					u_mtrldef.mtrlmode,
					u_mtrldef.mtrlsectype,
					u_mtrldef.zxmtrlmode,
					isnull(uv_mtrlware_noauditingqty.noauditingqty,0),
					u_mtrldef.lmsaleprice,
					u_mtrlware.status,
					u_mtrlware.woodcode,
					u_mtrlware.pcode,
					u_mtrlware.cost,
					cost = case u_mtrlware.noallocqty when 0 then 0 else u_mtrlware.wareamt/u_mtrlware.noallocqty END,
					u_mtrlware.sptid,
					u_mtrldef.packqty,
					u_mtrldef.prdpackcode,
					u_mtrlware.noallocaddqty,
					u_mtrlware.Dxflag,
					u_mtrlware.wareamt,
					u_mtrldef.barcode,
					u_mtrldef.mtrltype
					INTO	:ls_mtrlwareid,
					:ls_mtrlid,
					:ls_mtrlcode,
					:ls_mtrlname,
					:ls_unit,
					:ls_noallocqty,
					:ls_planprice,
					:ls_storageid,
					:ls_plancode,
					:ls_mtrlmode,
					:ls_mtrlsectype,
					:ls_zxmtrlmode,
					:ls_noauditingqty,
					:ls_lmsaleprice,
					:ls_status,
					:ls_woodcode,
					:ls_pcode,
					:ld_cost_temp,
					:ls_cost,
					:ll_sptid,
					:ll_packqty,
					:ls_prdpackcode,
					:ld_noallocaddqty,
					:li_Dxflag,
					:ld_wareamt,
					:ls_barcode,
					:ls_mtrltype
					FROM u_mtrldef,
					u_mtrlware left OUTER join u_storage
					on ( u_mtrlware.storageid = u_storage.storageid)
					left OUTER join uv_mtrlware_noauditingqty
					on ( u_mtrlware.mtrlwareid = uv_mtrlware_noauditingqty.mtrlwareid)
					WHERE ( u_mtrldef.mtrlid = u_mtrlware.mtrlid )
					AND ( u_mtrlware.sptid = :arg_sptid OR :arg_sptid = -1)
					AND ( u_mtrlware.storageid = :arg_storageid )
					AND ( u_mtrldef.mtrlcode Like :ls_like_mtrlcode OR u_mtrldef.mtrlname Like :ls_like_mtrlcode OR u_mtrldef.barcode Like :ls_like_mtrlcode )
					order by u_mtrlware.noallocqty desc;
				IF sqlca.SQLCode <> 0 THEN
					rslt = 0
					GOTO ext
				ELSE
					if_find = TRUE
				END IF
//			END IF
		END IF
	ELSE
		SELECT top 1 u_mtrlware.mtrlwareid ,
			u_mtrlware.mtrlid,
			u_mtrldef.mtrlcode,
			u_mtrldef.mtrlname,
			u_mtrldef.unit,
			u_mtrlware.noallocqty,
			u_mtrlware.planprice,
			u_mtrlware.storageid,
			u_mtrlware.plancode,
			u_mtrldef.mtrlmode,
			u_mtrldef.mtrlsectype,
			u_mtrldef.zxmtrlmode,
			isnull(uv_mtrlware_noauditingqty.noauditingqty,0),
			u_mtrldef.lmsaleprice,
			u_mtrlware.status,
			u_mtrlware.woodcode,
			u_mtrlware.pcode,
			u_mtrlware.cost,
			cost = case u_mtrlware.noallocqty when 0 then 0 else u_mtrlware.wareamt/u_mtrlware.noallocqty END,
			u_mtrlware.sptid,
			u_mtrldef.packqty,
			u_mtrldef.prdpackcode,
			u_mtrlware.noallocaddqty,
			u_mtrlware.Dxflag,
			u_mtrlware.wareamt,
			u_mtrldef.barcode,
			u_mtrldef.mtrltype
			INTO	:ls_mtrlwareid,
			:ls_mtrlid,
			:ls_mtrlcode,
			:ls_mtrlname,
			:ls_unit,
			:ls_noallocqty,
			:ls_planprice,
			:ls_storageid,
			:ls_plancode,
			:ls_mtrlmode,
			:ls_mtrlsectype,
			:ls_zxmtrlmode,
			:ls_noauditingqty,
			:ls_lmsaleprice,
			:ls_status,
			:ls_woodcode,
			:ls_pcode,
			:ld_cost_temp,
			:ls_cost,
			:ll_sptid,
			:ll_packqty,
			:ls_prdpackcode,
			:ld_noallocaddqty,
			:li_Dxflag,
			:ld_wareamt,
			:ls_barcode,
			:ls_mtrltype
			FROM u_mtrldef,
			u_mtrlware left OUTER join u_storage
			on ( u_mtrlware.storageid = u_storage.storageid)
			left OUTER join uv_mtrlware_noauditingqty
			on ( u_mtrlware.mtrlwareid = uv_mtrlware_noauditingqty.mtrlwareid)
			WHERE ( u_mtrldef.mtrlid = u_mtrlware.mtrlid )
			AND ( u_mtrlware.sptid = :arg_sptid OR :arg_sptid = -1)
			AND ( u_mtrlware.storageid = :arg_storageid )
			AND ( u_mtrldef.mtrlcode = :arg_mtrlcode OR u_mtrldef.mtrlname = :arg_mtrlcode OR u_mtrldef.barcode = :arg_mtrlcode  );
		IF sqlca.SQLCode <> 0 THEN
			rslt = 0
			GOTO ext
		ELSE
			if_find = TRUE
		END IF
		
	END IF
END IF

IF if_find THEN
	arg_s_mtrlware.u_mtrldef_mtrlcode[1] = ls_mtrlcode
	arg_s_mtrlware.u_mtrldef_mtrlname[1] = ls_mtrlname
	arg_s_mtrlware.u_mtrldef_unit[1] = ls_unit
	arg_s_mtrlware.u_mtrldef_mtrlmode[1] = ls_mtrlmode
	arg_s_mtrlware.u_mtrldef_zxmtrlmode[1] = ls_zxmtrlmode
	arg_s_mtrlware.u_mtrldef_mtrlsectype[1] = ls_mtrlsectype
	arg_s_mtrlware.plancode[1] = ls_plancode
	arg_s_mtrlware.status[1] = ls_status
	arg_s_mtrlware.woodcode[1] = ls_woodcode
	arg_s_mtrlware.pcode[1] = ls_pcode
	arg_s_mtrlware.mtrlid[1] = ls_mtrlid
	arg_s_mtrlware.mtrlwareid[1] = ls_mtrlwareid
	arg_s_mtrlware.storageid[1] = ls_storageid
	arg_s_mtrlware.noallocqty[1] = ls_noallocqty
	arg_s_mtrlware.noauditingqty[1] = ls_noauditingqty
	arg_s_mtrlware.planprice[1] = ls_planprice
	arg_s_mtrlware.sptid[1] = ll_sptid
	arg_s_mtrlware.u_mtrldef_packqty[1] = ll_packqty
	arg_s_mtrlware.u_mtrldef_prdpackcode[1] = ls_prdpackcode
	arg_s_mtrlware.noallocaddqty[1] = ld_noallocaddqty
	arg_s_mtrlware.Dxflag[1] =  li_Dxflag
	arg_s_mtrlware.wareamt[1] =  ld_wareamt
	arg_s_mtrlware.barcode[1] =  ls_barcode
	arg_s_mtrlware.u_mtrldef_mtrltype[1] =  ls_mtrltype
	IF ls_noallocqty = 0  THEN
		arg_s_mtrlware.cost[1] = ld_cost_temp
	ELSE
		arg_s_mtrlware.cost[1] = ls_cost
	END IF
	arg_s_mtrlware.lmsaleprice[1] = ls_lmsaleprice
END IF

ext:
RETURN rslt













end function