17,078
社区成员
发帖
与我相关
我的任务
分享
with cftb as
(
select
xmjbxx.werks,
t001w.name1,
xmjbxx.pspid,
xmjbxx.proj_post1,
xmjbxx.posid dt_wbsid,
xmjbxx.prps_post1,
prps.posid wbs_id,
prps.post1 wbs_name,
eban.banfn, -- 采购申请号
eban.bnfpo, -- 采购申请行项目号
eban.matnr, -- 物料编码
eban.txz01, -- 物料名称
nvl2(wlpzb.mm_id,1,0) sw_id_manger,
nvl2(wlpzb.mm_id,1,eban.menge) menge,
eban.meins, -- 计量单位
eban.preis, -- 需求单价
nvl2(wlpzb.mm_id,1,eban.menge)*eban.preis xqje, -- 需求金额
ekpo.ebeln, -- 采购订单号
ekpo.ebelp, -- 采购订单行项目号
ekpo.brtwr/ekpo.menge hsdj -- 订单含税单价
from view_erp_ebkn ebkn -- 采购订单与wbs关联关系
left join view_erp_prps prps on prps.pspnr = ebkn.ps_psp_pnr -- WBS(工作中断结构) 元素主数据
inner join t_sbwcetj_xmjbxx xmjbxx on substr(prps.posid,1,12) = xmjbxx.pspid -- 统计范围表
left join view_erp_t001w t001w on t001w.werks = xmjbxx.werks -- 工厂/分支机构
left join view_erp_eban eban on eban.banfn = ebkn.banfn and eban.bnfpo = ebkn.bnfpo -- 预购订单表 (匹配订单编号 和 项目编号)
left join odssjqc.t_wlpzb wlpzb on wlpzb.mm_id = eban.matnr -- 21736
left join ( select * from view_erp_ekpo where loekz = ' ' and pstyp <> 9 ) ekpo on ekpo.banfn = ebkn.banfn and ekpo.bnfpo = ebkn.bnfpo -- 订单表
where eban.loekz = ' ' and eban.frgkz = 'O'
connect by wlpzb.mm_id is not null and eban.banfn = prior eban.banfn and eban.bnfpo = prior eban.bnfpo and level <= eban.menge and prior dbms_random.value is not null
)
select
tt.*
,case when tt.shsl = '0' then '0' when tt.menge = tt.shsl then '1' else '2' end shbs
,case when tt.shsl = '0' then '0' when tt.menge = tt.shsl then '1' else '2' end azbs
,tt.shsl azsl -- 安装数量
,tt.shsj azsj -- 安装时间
from
(
select
sys_guid() xh,
yqctb.werks,
yqctb.name1,
yqctb.pspid,
yqctb.proj_post1, -- XH,WERKS,NAME1,PSPID,PROJ_POST1,
yqctb.dt_wbsid,
yqctb.prps_post1,
yqctb.wbs_id,
yqctb.wbs_name,
yqctb.banfn, -- 采购申请号 -- DT_WBSID,PRPS_POST1,WBS_ID,WBS_NAME,BANFN,
yqctb.bnfpo, -- 采购申请行项目号
yqctb.matnr, -- 物料编码
yqctb.txz01, -- 物料名称
yqctb.sw_id_manger,
yqctb.menge, -- BNFPO,MATNR,TXZ01,SW_ID_MANGER,MENGE,
yqctb.meins, -- 计量单位
nvl(yqctb.preis,0), -- 需求单价
nvl(yqctb.xqje,0), -- 需求金额
yqctb.ebeln, -- 采购订单号
yqctb.ebelp, -- 采购订单行项目号 -- MEINS,PREIS,XQJE,EBELN,EBELP,
nvl(yqctb.hsdj,0), -- 订单含税单价
yqctb.sw_id,
hwjj01.zfhtzdh, -- 物资交接单号
bama.pm_id, -- 设备编号
devi.erp_equitype, -- 设备类型 -- HSDJ,SW_ID,ZFHTZDH,PM_ID,ERP_EQUITYPE,
devi.erp_equiname, -- 设备类型名称
bama.pmc_id, -- 调度编号
bama.aa_id, -- 资产编号
nvl(anln.zsum,0) zzje, -- 转资金额
nvl2(thpr.banfn,1,nvl2(yqctb.ebeln,1,0)) z_tjcgbs, -- ERP_EQUINAME,PMC_ID,AA_ID,ZZJE,Z_TJCGBS,
nvl2(twprr.banfn,1,nvl2(yqctb.ebeln,1,0)) z_zbwcbs, -- 招标完成标识
nvl2(bmgt0022.zsw_id,1,0) fhbs,
nvl2(zyitem.zsw_id,1,0) pdbs, -- Z_ZBWCBS,SHBS,FHBS,AZBS,PDBS,
nvl2(bama.pmc_id, nvl2(bama.pm_id,1,0), 0) tybs,
'185_ODSWLMO1_DBJob' zname,
sysdate ztime -- TYBS,ZNAME,ZTIME
,kpfseg.bldat shsj -- 收货时间
,decode(yqctb.sw_id_manger,'1',nvl2(bmgt0021.zsw_id,1,0),nvl(msegc.cou,0)) shsl -- 到货数量
from (
select distinct cftb.*,tsip.sw_id from cftb
left join t_sw_id_po tsip on tsip.po = cftb.ebeln and tsip.po_item = cftb.ebelp -- 实物ID与采购订单对应表
where tsip.sw_id is not null
union all
select cftb.*,tsip.sw_id from cftb
left join t_sw_id_po tsip on tsip.po = cftb.ebeln and tsip.po_item = cftb.ebelp -- 实物ID与采购订单对应表
where tsip.sw_id is null
) yqctb
left join view_erp_zmmjyhwjj01 hwjj01 on hwjj01.ebeln = yqctb.ebeln and hwjj01.ebelp = yqctb.ebelp
left join odsbmgt.t_bama bama on bama.sw_id = yqctb.sw_id
left join view_erp_mara mara on mara.matnr = bama.mm_id
left join odsbmgt.t_matnr_devicetype devi on mara.matkl = devi.matkl
left join view_erp_zfi_fzzz_anln anln on anln.anln1 = bama.aa_id
left join (select distinct banfn,bnfpo from view_erp_zmmjy_th_pr) thpr on thpr.banfn = yqctb.banfn and thpr.bnfpo = yqctb.bnfpo
left join odsviewdata.view_erp_zmmjy_tw_prr twprr on twprr.banfn = yqctb.banfn and twprr.bnfpo = yqctb.bnfpo
left join (select distinct zsw_id from view_erp_zmm_bmgt_002 where bwart = '105') bmgt0021 on bmgt0021.zsw_id = yqctb.sw_id
left join (select distinct zsw_id from view_erp_zmm_bmgt_002 where bwart = '281') bmgt0022 on bmgt0022.zsw_id = yqctb.sw_id
left join (select distinct swid from bmgt_zjc_azb ) bzazb on bzazb.swid = yqctb.sw_id
left join view_erp_zfi26t_ysqc_item zyitem on zyitem.zsw_id = yqctb.sw_id
left join (
-- 取最早时间
select min(mkpf.bldat) bldat,mseg.ebeln,mseg.ebelp from view_erp_mkpf mkpf
left join view_erp_mseg mseg on mkpf.mblnr = mseg.mblnr
group by mseg.ebeln,mseg.ebelp
) kpfseg on kpfseg.ebeln = yqctb.ebeln and kpfseg.ebelp = yqctb.ebelp
left join ( select count(*) cou,ebeln,ebelp from view_erp_mseg where bwart in ('101','105') group by ebeln,ebelp ) msegc
on msegc.ebeln=yqctb.ebeln and msegc.ebelp = yqctb.ebelp
) tt
;