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, -- -- purchasing application number 
Eban. Bnfpo, -- -- purchasing application line item number 
Eban. Matnr, -- -- material code 
Eban. Txz01, -- -- material name 
Sw_id_manger nvl2 (WLPZB mm_id, 1, 0), 
Nvl2 (WLPZB. Mm_id, 1, eban menge) menge, 
Eban. Meins, -- -- the measuring unit 
Eban. Preis, -- -- demand price 
Nvl2 (WLPZB. Mm_id, 1, eban menge) * eban preis xqje, - demand amount 
Ekpo. Ebeln, -- -- purchase order number 
Ekpo. Ebelp, -- -- purchase order line item number 
Ekpo. BRTWR/ekpo. Menge HSDJ - order tax price 
The from view_erp_ebkn ebkn - purchase order relationship with WBS 
Left the join view_erp_prps PRPS on PRPS. PSPNR=ebkn. Ps_psp_pnr -- -- the WBS (work interrupt structure) master data elements 
Inner join t_sbwcetj_xmjbxx XMJBXX on substr (PRPS) posid, 1, 12).=XMJBXX pspid - statistical range table 
Left the join view_erp_t001w t001w on t001w. Werks=XMJBXX. Werks -- -- branch factory/
Left the join view_erp_eban eban on eban. Banfn=ebkn. Banfn and eban. Bnfpo=ebkn. Bnfpo -- -- pre-orders for table order number and item number (matching) 
Left the join odssjqc. T_wlpzb WLPZB on WLPZB. Mm_id=eban. Matnr - 21736 
Left the join (select * from view_erp_ekpo where loekz='and pstyp & lt;> 9) ekpo on ekpo. Banfn=ebkn. Banfn and ekpo. Bnfpo=ebkn. Bnfpo -- -- order table 
Where eban. Loekz='and eban. FRGKZ=' O '
Connect by WLPZB. Mm_id is not null and eban. Banfn=the prior eban. Banfn and eban. Bnfpo=the prior eban. Bnfpo and level & lt;=eban. Menge and the prior dbms_random. The 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 - installation number 
Tt. SHSJ azsj - installation time 
The 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 purchasing application, PRPS_POST1, WBS_ID, WBS_NAME, banfn, 
Yqctb. Bnfpo, -- -- purchasing application line item number 
Yqctb. Matnr, -- -- material code 
Yqctb. Txz01, -- -- material name 
Yqctb sw_id_manger, 
Yqctb. Menge, -- -- BNFPO, MATNR TXZ01, SW_ID_MANGER, menge, 
Yqctb. Meins, -- -- the measuring unit 
NVL (yqctb. Preis, 0), - demand price 
NVL (yqctb. Xqje, 0), - demand amount 
Yqctb. Ebeln, -- -- purchase order number 
Yqctb ebelp, - purchase order line item number - MEINS, PREIS, XQJE, EBELN ebelp,, 
NVL (yqctb. HSDJ, 0), tax - order unit price 
Yqctb sw_id, 
Hwjj01. ZFHTZDH, -- -- goods receipt number 
Bama. Pm_id, -- -- equipment number 
Devi. Erp_equitype - device type - HSDJ, SW_ID, ZFHTZDH, PM_ID, erp_equitype, 
Devi. Erp_equiname, -- -- equipment type name 
Bama. Pmc_id, -- -- dispatch number 
Bama. Aa_id - asset number 
NVL (anln. Zsum, 0) zzje, turn - amount 
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, - bid logo 
FHBS nvl2 (bmgt0022 zsw_id, 1, 0), 
PDBS nvl2 (zyitem zsw_id, 1, 0), - 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 - receiving time 
, decode (yqctb sw_id_manger, '1', nvl2 (bmgt0021 zsw_id, 1, 0), NVL (msegc cou, 0)) SHSL - the arrival of the goods quantity 
The from (
Select distinct CFTB. *, tsip sw_id from CFTB 
Left the join t_sw_id_po tsip on tsip. Po=CFTB. Ebeln and tsip. Po_item=CFTB. Ebelp -- -- physical ID table corresponding to the purchase order 
Where tsip. Sw_id is not null 
Union all 
The select CFTB. *, tsip sw_id from CFTB 
Left the join t_sw_id_po tsip on tsip. Po=CFTB. Ebeln and tsip. Po_item=CFTB. Ebelp -- -- physical ID table corresponding to the purchase order 
Where tsip. Sw_id is null 
) yqctb 
Left the join view_erp_zmmjyhwjj01 hwjj01 on hwjj01. Ebeln=yqctb. Ebeln and hwjj01. Ebelp=yqctb. Ebelp 
Left the join odsbmgt. T_bama bama on bama. Sw_id=yqctb. Sw_id 
Left the join view_erp_mara mara on mara. Matnr=bama. Mm_id 
Left the join odsbmgt. T_matnr_devicetype devi on mara. Matkl=devi. Matkl 
Left the join view_erp_zfi_fzzz_anln anln on anln. Anln1=bama. Aa_id 
Left the join (select distinct banfn, bnfpo from view_erp_zmmjy_th_pr) THPR on THPR. Banfn=yqctb. Banfn and THPR. Bnfpo=yqctb. Bnfpo 
Left the join odsviewdata. View_erp_zmmjy_tw_prr TWPRR on TWPRR. Banfn=yqctb. Banfn and TWPRR. Bnfpo=yqctb. Bnfpo 
Left the join (select distinct zsw_id from view_erp_zmm_bmgt_002 where bwart='105') bmgt0021 on bmgt0021. Zsw_id=yqctb. Sw_id 
Left the join (select distinct zsw_id from view_erp_zmm_bmgt_002 where bwart='281') bmgt0022 on bmgt0022. Zsw_id=yqctb. Sw_id 
Left the join (select distinct swid from bmgt_zjc_azb) bzazb on bzazb. Swid=yqctb. Sw_id 
Left the join view_erp_zfi26t_ysqc_item zyitem on zyitem. Zsw_id=yqctb. Sw_id 
Left the join (
- take the first time 
The select min (MKPF bldat) bldat, mseg. Ebeln, mseg. Ebelp from view_erp_mkpf MKPF 
Left the join view_erp_mseg mseg on MKPF. MBLNR=mseg. MBLNR 
Group by mseg ebeln, mseg. Ebelp 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull