create or replace procedure hkeam_p_bookkeeping_detail(my_cursor out sys_refcursor, assetinfo_assetid_in in varchar2)
is
begin
open my_cursor for
select assetorder.orderdate 领用时间_订单时间
,assetorder.orderprincipal 领用人,
(select dep.depname from ams_bd_department dep where dep.depid = (
select emp.depid from ams_bd_employee emp where emp.employeeid = (
select ord.orderprincipal from ams_sm_assetorder ord where ord.orderid =
(select * from (select ordlist.orderid from ams_sm_orderlist ordlist,ams_sm_assetorder assetorder
where ordlist.assetid = 'assetid1' and ordlist.orderid = assetorder.orderid order by assetorder.orderdate ) where rownum < 2)))) 信用部门
,(select count(*) 保养次数 from ams_msm_maintaininfo a where a.assetid =assetinfo_assetid_in and a.maintype='养护') 保养次数
,(select b.mcost from ams_msm_maintaininfo b where b.assetid =assetinfo_assetid_in and b.maintype='养护')保养费用
,(select count(*) 保养次数 from ams_msm_maintaininfo c where c.assetid =assetinfo_assetid_in and c.maintype='维修') 维修次数
,(select d.mcost from ams_msm_maintaininfo d where d.assetid =assetinfo_assetid_in and d.maintype='维修')维修费用
from ams_sm_assetinfo t
,ams_sm_assetorder assetorder
,ams_sm_orderlist orderlist
where
t.assetid = assetinfo_assetid_in
and
(orderlist.assetid (+)= assetinfo_assetid_in
and orderlist.assetid(+)= assetorder.orderid) --注意顺序
order by assetorder.orderdate desc;
end hkeam_p_bookkeeping_detail;
create or replace procedure hkeam_p_bookkeeping_list (outputlist out sys_refcursor, indexpagenum in number ,perpagelimitcount in number)
is
begin
open outputlist for
select * from (
select distinct t.id, t.assetid 资产编号
,t.assetname 资产名称
,t.assetsize 资产型号
,assettype.atypename 资产类型_车辆_设备
,sup.suppliername 对应供应商
,assetorder.enteyd 入库时间
,storem.storename 所在仓库名称
,devicemessage.state 资产状态,stockinfo.qty 资产数量
from ams_sm_assetinfo t
,ams_bd_assettype assettype
,ams_bd_supplier sup
,AMS_SM_ASSETORDER assetorder,AMS_SM_ORDERLIST orserlist
,AMS_SM_STOCKINFO stockinfo,AMS_BD_STOREM storem
,AMS_MSM_DEVICEMESSAGE devicemessage
where
assettype.atypeid (+)= t.atypeid
and sup.supplierid (+)= t.supplierid
and (orserlist.assetid (+)= t.assetid and assetorder.orderid(+)=orserlist.orderid )
and (stockinfo.assetid (+)= t.assetid and storem.storeid(+)= stockinfo.storeid)
and devicemessage.assetid (+)= t.assetid
union
select distinct t.id, t.assetid 资产编号
,t.assetname 资产名称
,t.assetsize 资产型号
,assettype.atypename 资产类型_车辆_设备
,sup.suppliername 对应供应商
,assetorder.enteyd 入库时间
,storem.storename 所在仓库名称
,carmessage.state 资产状态,stockinfo.qty 资产数量
from ams_sm_assetinfo t
,ams_bd_assettype assettype
,ams_bd_supplier sup
,AMS_SM_ASSETORDER assetorder,AMS_SM_ORDERLIST orserlist
,AMS_SM_STOCKINFO stockinfo,AMS_BD_STOREM storem
,ams_vm_carmessage carmessage
where
assettype.atypeid (+)= t.atypeid
and sup.supplierid (+)= t.supplierid
and (orserlist.assetid (+)= t.assetid and assetorder.orderid (+)= orserlist.orderid )
and (stockinfo.assetid (+)= t.assetid and storem.storeid (+)= stockinfo.storeid)
and carmessage.aseetid (+)= t.assetid
order by id desc
)where
rownum <= indexpagenum* perpagelimitcount and rownum >= (indexpagenum-1)* perpagelimitcount
;
end hkeam_p_bookkeeping_list;
create or replace procedure hkeam_p_bookkeeping_search (outputlist out sys_refcursor, assetid_in in varchar2,assetname_in in varchar2,assetsize_in in varchar2)
is
begin
open outputlist for
select distinct t.id, t.assetid 资产编号
,t.assetname 资产名称
,t.assetsize 资产型号
,assettype.atypename 资产类型_车辆_设备
,sup.suppliername 对应供应商
,assetorder.enteyd 入库时间
,storem.storename 所在仓库名称
,devicemessage.state 资产状态,stockinfo.qty 资产数量
from ams_sm_assetinfo t
,ams_bd_assettype assettype
,ams_bd_supplier sup
,AMS_SM_ASSETORDER assetorder,AMS_SM_ORDERLIST orserlist
,AMS_SM_STOCKINFO stockinfo,AMS_BD_STOREM storem
,AMS_MSM_DEVICEMESSAGE devicemessage
where
assettype.atypeid (+)= t.atypeid
and sup.supplierid (+)= t.supplierid
and (orserlist.assetid (+)= t.assetid and assetorder.orderid(+)=orserlist.orderid )
and (stockinfo.assetid (+)= t.assetid and storem.storeid(+)= stockinfo.storeid)
and devicemessage.assetid (+)= t.assetid
and t.assetid like sys.standard.CONCAT(sys.standard.CONCAT('%',assetid_in),'%')
and t.assetname like sys.standard.CONCAT(sys.standard.CONCAT('%',assetname_in),'%')
and t.assetsize like sys.standard.CONCAT(sys.standard.CONCAT('%',assetsize_in),'%')
--order by t.id desc
union all
select distinct t.id, t.assetid 资产编号
,t.assetname 资产名称
,t.assetsize 资产型号
,assettype.atypename 资产类型_车辆_设备
,sup.suppliername 对应供应商
,assetorder.enteyd 入库时间
,storem.storename 所在仓库名称
,carmessage.state 资产状态,stockinfo.qty 资产数量
from ams_sm_assetinfo t
,ams_bd_assettype assettype
,ams_bd_supplier sup
,AMS_SM_ASSETORDER assetorder,AMS_SM_ORDERLIST orserlist
,AMS_SM_STOCKINFO stockinfo,AMS_BD_STOREM storem
,ams_vm_carmessage carmessage
where
assettype.atypeid (+)= t.atypeid
and sup.supplierid (+)= t.supplierid
and (orserlist.assetid (+)= t.assetid and assetorder.orderid (+)= orserlist.orderid )
and (stockinfo.assetid (+)= t.assetid and storem.storeid (+)= stockinfo.storeid)
and carmessage.aseetid (+)= t.assetid
and t.assetid like sys.standard.CONCAT(sys.standard.CONCAT('%',assetid_in),'%')
and t.assetname like sys.standard.CONCAT(sys.standard.CONCAT('%',assetname_in),'%')
and t.assetsize like sys.standard.CONCAT(sys.standard.CONCAT('%',assetsize_in),'%')
order by id desc
;
end hkeam_p_bookkeeping_search;
create or replace procedure hkeam_p_bookkeeping_list_size (size_out out varchar2)
is
begin
select count(*) into size_out from (
select distinct t.id, t.assetid 资产编号
,t.assetname 资产名称
,t.assetsize 资产型号
,assettype.atypename 资产类型_车辆_设备
,sup.suppliername 对应供应商
,assetorder.enteyd 入库时间
,storem.storename 所在仓库名称
,devicemessage.state 资产状态,stockinfo.qty 资产数量
from ams_sm_assetinfo t
,ams_bd_assettype assettype
,ams_bd_supplier sup
,AMS_SM_ASSETORDER assetorder,AMS_SM_ORDERLIST orserlist
,AMS_SM_STOCKINFO stockinfo,AMS_BD_STOREM storem
,AMS_MSM_DEVICEMESSAGE devicemessage
where
assettype.atypeid (+)= t.atypeid
and sup.supplierid (+)= t.supplierid
and (orserlist.assetid (+)= t.assetid and assetorder.orderid(+)=orserlist.orderid )
and (stockinfo.assetid (+)= t.assetid and storem.storeid(+)= stockinfo.storeid)
and devicemessage.assetid (+)= t.assetid
union
select distinct t.id, t.assetid 资产编号
,t.assetname 资产名称
,t.assetsize 资产型号
,assettype.atypename 资产类型_车辆_设备
,sup.suppliername 对应供应商
,assetorder.enteyd 入库时间
,storem.storename 所在仓库名称
,carmessage.state 资产状态,stockinfo.qty 资产数量
from ams_sm_assetinfo t
,ams_bd_assettype assettype
,ams_bd_supplier sup
,AMS_SM_ASSETORDER assetorder,AMS_SM_ORDERLIST orserlist
,AMS_SM_STOCKINFO stockinfo,AMS_BD_STOREM storem
,ams_vm_carmessage carmessage
where
assettype.atypeid (+)= t.atypeid
and sup.supplierid (+)= t.supplierid
and (orserlist.assetid (+)= t.assetid and assetorder.orderid (+)= orserlist.orderid )
and (stockinfo.assetid (+)= t.assetid and storem.storeid (+)= stockinfo.storeid)
and carmessage.aseetid (+)= t.assetid
order by id desc
);
end hkeam_p_bookkeeping_list_size;
is
begin
open my_cursor for
select assetorder.orderdate 领用时间_订单时间
,assetorder.orderprincipal 领用人,
(select dep.depname from ams_bd_department dep where dep.depid = (
select emp.depid from ams_bd_employee emp where emp.employeeid = (
select ord.orderprincipal from ams_sm_assetorder ord where ord.orderid =
(select * from (select ordlist.orderid from ams_sm_orderlist ordlist,ams_sm_assetorder assetorder
where ordlist.assetid = 'assetid1' and ordlist.orderid = assetorder.orderid order by assetorder.orderdate ) where rownum < 2)))) 信用部门
,(select count(*) 保养次数 from ams_msm_maintaininfo a where a.assetid =assetinfo_assetid_in and a.maintype='养护') 保养次数
,(select b.mcost from ams_msm_maintaininfo b where b.assetid =assetinfo_assetid_in and b.maintype='养护')保养费用
,(select count(*) 保养次数 from ams_msm_maintaininfo c where c.assetid =assetinfo_assetid_in and c.maintype='维修') 维修次数
,(select d.mcost from ams_msm_maintaininfo d where d.assetid =assetinfo_assetid_in and d.maintype='维修')维修费用
from ams_sm_assetinfo t
,ams_sm_assetorder assetorder
,ams_sm_orderlist orderlist
where
t.assetid = assetinfo_assetid_in
and
(orderlist.assetid (+)= assetinfo_assetid_in
and orderlist.assetid(+)= assetorder.orderid) --注意顺序
order by assetorder.orderdate desc;
end hkeam_p_bookkeeping_detail;
create or replace procedure hkeam_p_bookkeeping_list (outputlist out sys_refcursor, indexpagenum in number ,perpagelimitcount in number)
is
begin
open outputlist for
select * from (
select distinct t.id, t.assetid 资产编号
,t.assetname 资产名称
,t.assetsize 资产型号
,assettype.atypename 资产类型_车辆_设备
,sup.suppliername 对应供应商
,assetorder.enteyd 入库时间
,storem.storename 所在仓库名称
,devicemessage.state 资产状态,stockinfo.qty 资产数量
from ams_sm_assetinfo t
,ams_bd_assettype assettype
,ams_bd_supplier sup
,AMS_SM_ASSETORDER assetorder,AMS_SM_ORDERLIST orserlist
,AMS_SM_STOCKINFO stockinfo,AMS_BD_STOREM storem
,AMS_MSM_DEVICEMESSAGE devicemessage
where
assettype.atypeid (+)= t.atypeid
and sup.supplierid (+)= t.supplierid
and (orserlist.assetid (+)= t.assetid and assetorder.orderid(+)=orserlist.orderid )
and (stockinfo.assetid (+)= t.assetid and storem.storeid(+)= stockinfo.storeid)
and devicemessage.assetid (+)= t.assetid
union
select distinct t.id, t.assetid 资产编号
,t.assetname 资产名称
,t.assetsize 资产型号
,assettype.atypename 资产类型_车辆_设备
,sup.suppliername 对应供应商
,assetorder.enteyd 入库时间
,storem.storename 所在仓库名称
,carmessage.state 资产状态,stockinfo.qty 资产数量
from ams_sm_assetinfo t
,ams_bd_assettype assettype
,ams_bd_supplier sup
,AMS_SM_ASSETORDER assetorder,AMS_SM_ORDERLIST orserlist
,AMS_SM_STOCKINFO stockinfo,AMS_BD_STOREM storem
,ams_vm_carmessage carmessage
where
assettype.atypeid (+)= t.atypeid
and sup.supplierid (+)= t.supplierid
and (orserlist.assetid (+)= t.assetid and assetorder.orderid (+)= orserlist.orderid )
and (stockinfo.assetid (+)= t.assetid and storem.storeid (+)= stockinfo.storeid)
and carmessage.aseetid (+)= t.assetid
order by id desc
)where
rownum <= indexpagenum* perpagelimitcount and rownum >= (indexpagenum-1)* perpagelimitcount
;
end hkeam_p_bookkeeping_list;
create or replace procedure hkeam_p_bookkeeping_search (outputlist out sys_refcursor, assetid_in in varchar2,assetname_in in varchar2,assetsize_in in varchar2)
is
begin
open outputlist for
select distinct t.id, t.assetid 资产编号
,t.assetname 资产名称
,t.assetsize 资产型号
,assettype.atypename 资产类型_车辆_设备
,sup.suppliername 对应供应商
,assetorder.enteyd 入库时间
,storem.storename 所在仓库名称
,devicemessage.state 资产状态,stockinfo.qty 资产数量
from ams_sm_assetinfo t
,ams_bd_assettype assettype
,ams_bd_supplier sup
,AMS_SM_ASSETORDER assetorder,AMS_SM_ORDERLIST orserlist
,AMS_SM_STOCKINFO stockinfo,AMS_BD_STOREM storem
,AMS_MSM_DEVICEMESSAGE devicemessage
where
assettype.atypeid (+)= t.atypeid
and sup.supplierid (+)= t.supplierid
and (orserlist.assetid (+)= t.assetid and assetorder.orderid(+)=orserlist.orderid )
and (stockinfo.assetid (+)= t.assetid and storem.storeid(+)= stockinfo.storeid)
and devicemessage.assetid (+)= t.assetid
and t.assetid like sys.standard.CONCAT(sys.standard.CONCAT('%',assetid_in),'%')
and t.assetname like sys.standard.CONCAT(sys.standard.CONCAT('%',assetname_in),'%')
and t.assetsize like sys.standard.CONCAT(sys.standard.CONCAT('%',assetsize_in),'%')
--order by t.id desc
union all
select distinct t.id, t.assetid 资产编号
,t.assetname 资产名称
,t.assetsize 资产型号
,assettype.atypename 资产类型_车辆_设备
,sup.suppliername 对应供应商
,assetorder.enteyd 入库时间
,storem.storename 所在仓库名称
,carmessage.state 资产状态,stockinfo.qty 资产数量
from ams_sm_assetinfo t
,ams_bd_assettype assettype
,ams_bd_supplier sup
,AMS_SM_ASSETORDER assetorder,AMS_SM_ORDERLIST orserlist
,AMS_SM_STOCKINFO stockinfo,AMS_BD_STOREM storem
,ams_vm_carmessage carmessage
where
assettype.atypeid (+)= t.atypeid
and sup.supplierid (+)= t.supplierid
and (orserlist.assetid (+)= t.assetid and assetorder.orderid (+)= orserlist.orderid )
and (stockinfo.assetid (+)= t.assetid and storem.storeid (+)= stockinfo.storeid)
and carmessage.aseetid (+)= t.assetid
and t.assetid like sys.standard.CONCAT(sys.standard.CONCAT('%',assetid_in),'%')
and t.assetname like sys.standard.CONCAT(sys.standard.CONCAT('%',assetname_in),'%')
and t.assetsize like sys.standard.CONCAT(sys.standard.CONCAT('%',assetsize_in),'%')
order by id desc
;
end hkeam_p_bookkeeping_search;
create or replace procedure hkeam_p_bookkeeping_list_size (size_out out varchar2)
is
begin
select count(*) into size_out from (
select distinct t.id, t.assetid 资产编号
,t.assetname 资产名称
,t.assetsize 资产型号
,assettype.atypename 资产类型_车辆_设备
,sup.suppliername 对应供应商
,assetorder.enteyd 入库时间
,storem.storename 所在仓库名称
,devicemessage.state 资产状态,stockinfo.qty 资产数量
from ams_sm_assetinfo t
,ams_bd_assettype assettype
,ams_bd_supplier sup
,AMS_SM_ASSETORDER assetorder,AMS_SM_ORDERLIST orserlist
,AMS_SM_STOCKINFO stockinfo,AMS_BD_STOREM storem
,AMS_MSM_DEVICEMESSAGE devicemessage
where
assettype.atypeid (+)= t.atypeid
and sup.supplierid (+)= t.supplierid
and (orserlist.assetid (+)= t.assetid and assetorder.orderid(+)=orserlist.orderid )
and (stockinfo.assetid (+)= t.assetid and storem.storeid(+)= stockinfo.storeid)
and devicemessage.assetid (+)= t.assetid
union
select distinct t.id, t.assetid 资产编号
,t.assetname 资产名称
,t.assetsize 资产型号
,assettype.atypename 资产类型_车辆_设备
,sup.suppliername 对应供应商
,assetorder.enteyd 入库时间
,storem.storename 所在仓库名称
,carmessage.state 资产状态,stockinfo.qty 资产数量
from ams_sm_assetinfo t
,ams_bd_assettype assettype
,ams_bd_supplier sup
,AMS_SM_ASSETORDER assetorder,AMS_SM_ORDERLIST orserlist
,AMS_SM_STOCKINFO stockinfo,AMS_BD_STOREM storem
,ams_vm_carmessage carmessage
where
assettype.atypeid (+)= t.atypeid
and sup.supplierid (+)= t.supplierid
and (orserlist.assetid (+)= t.assetid and assetorder.orderid (+)= orserlist.orderid )
and (stockinfo.assetid (+)= t.assetid and storem.storeid (+)= stockinfo.storeid)
and carmessage.aseetid (+)= t.assetid
order by id desc
);
end hkeam_p_bookkeeping_list_size;
