物料收发汇总表 正常查询后,在条件里再加过滤条件报错。
[code]
INSERT /*+ APPEND */ INTO TMP42387D76D6E711E680B902E0EC3
(FGUID,FMATERIALID,
FORDERBY,FSTOCKIO,FIOPRICE,FIOAMOUNT,FSTOCKORGID,FOWNERTYPEID,FOWNERID,FKEEPERTYPEID,FKEEPERID,FPRODUCEDATE,
FEXPIRYDATE,FBOMID,FAUXPROPID,FDATE,FCREATEDATE,FLOTNO,FSTOCKID,FDEPARTMENTID,FSTOCKSTATUSID,FSTOCKLOCID,
FFORMID,FBILLNAME,FBILLID,FBILLSEQID,FBILLNO,FBILLTYPE,FENTRYTABLE,FBILLENTRYID,FBASEQCQTY,FBASEQCPRICE,FSECQCQTY,
FBASEINQTY,FBASEINPRICE,FSECINQTY,FBASEOUTQTY,FBASEOUTPRICE,FSECOUTQTY,
FSTOCKORGNUMBER,FSTOCKORGNAME,
FSTOREURNUM,FSTOREURNOM,
FMATERIALNO,
FBASEUNITPRE,FSTOCKUNITPRE,FSECUNITPRE,
FMATERIALNUMBER,FMATERIALNAME,
FMATERIALMODEL,FERPCLSID,FMATERIALGROUP,
FMATERIALTYPENAME,FMATERIALTYPEID,
FSTOCKNUMBER,FSTOCKNAME,FSTOCKLOC,FSTOCKPOSNUMBER,
FSTOCKSTATUSNUMBER,FSTOCKSTATUSNAME,
FOWNERTYPENAME,FOWNERNUMBER,FOWNERNAME,
FKEEPERTYPENAME,FKEEPERNUMBER,FKEEPERNAME,
FBOMNO,FMTONO,
FBASEUNITID,FBASEUNITNAME,
FSTOCKUNITID,FSTOCKUNITNAME,
FSECUNITID,FSECUNITNAME)
SELECT SYS_GUID() AS FGUID,T0.FMATERIALID,
FORDERBY,FSTOCKIO,FIOPRICE,FIOAMOUNT,FSTOCKORGID,FOWNERTYPEID,FOWNERID,FKEEPERTYPEID,FKEEPERID,FPRODUCEDATE,
FEXPIRYDATE,FBOMID,FAUXPROPID,FDATE,T0.FCREATEDATE,FLOTNO,T0.FSTOCKID,T0.FDEPARTMENTID,T0.FSTOCKSTATUSID,T0.FSTOCKLOCID,
T0.FFORMID,FBILLNAME,FBILLID,FBILLSEQID,FBILLNO,T0.FBILLTYPE,FENTRYTABLE,FBILLENTRYID,FBASEQCQTY,FBASEQCPRICE,FSECQCQTY,
FBASEINQTY,FBASEINPRICE,FSECINQTY,FBASEOUTQTY,FBASEOUTPRICE,FSECOUTQTY,
TORG.FNUMBER AS FSTOCKORGNUMBER,TORGL.FNAME AS FSTOCKORGNAME,
NVL(TMS.FSTOREURNUM,0) AS FSTOREURNUM,NVL(TMS.FSTOREURNOM,0) AS FSTOREURNOM,
NVL(TM.FNUMBER,' ') AS FMATERIALNO,
NVL(TUB.FPRECISION,0) AS FBASEUNITPRE, NVL(TUS.FPRECISION,0) AS FSTOCKUNITPRE, NVL(TUA.FPRECISION,0) AS FSECUNITPRE,
TM.FNUMBER AS FMATERIALNUMBER,TML.FNAME AS FMATERIALNAME,
TML.FSPECIFICATION AS FMATERIALMODEL, TMB.FERPCLSID AS FERPCLSID,TMGL.FNAME AS FMATERIALGROUP,
NVL(TMTL.FNAME,'') AS FMATERIALTYPENAME,NVL(TMT.FCATEGORYID,0) AS FMATERIALTYPEID,
TBS.FNUMBER AS FSTOCKNUMBER,TBSL.FNAME AS FSTOCKNAME,
CAST('' AS NVARCHAR2(1500)) AS FSTOCKLOC,CAST('' AS NVARCHAR2(1500)) AS FSTOCKPOSNUMBER,
TBSS.FNUMBER AS FSTOCKSTATUSNUMBER,TBSSL.FNAME AS FSTOCKSTATUSNAME,
CASE T0.FOWNERTYPEID WHEN 'BD_Supplier' THEN '供应商' WHEN 'BD_Customer' THEN '客户' ELSE '业务组织' END AS FOWNERTYPENAME,TKW.FNumber AS FOWNERNumber,TKWL.FNAME AS FOWNERNAME,
CASE T0.FKEEPERTYPEID WHEN 'BD_Supplier' THEN '供应商' WHEN 'BD_Customer' THEN '客户' ELSE '业务组织' END AS FKEEPERTYPENAME,TKP.FNumber AS FKEEPERNumber,TKPL.FNAME AS FKEEPERNAME,
TBM.FNUMBER AS FBOMNO,T0.FMTONO,
NVL(TMB.FBASEUNITID,0) AS FBASEUNITID,TULB.FNAME AS FBASEUNITNAME,
NVL(TMS.FSTOREUNITID,0) AS FSTOCKUNITID,TULS.FNAME AS FSTOCKUNITNAME,
NVL(TMS.FAUXUNITID,0) AS FSECUNITID,TULA.FNAME AS FSECUNITNAME
FROM TMP42387D71D6E711E680B902E0EC3 T0
LEFT JOIN T_BD_MATERIAL TM ON T0.FMATERIALID=TM.FMATERIALID
LEFT JOIN T_BD_STOCK TBS ON T0.FSTOCKID=TBS.FSTOCKID
LEFT JOIN T_BD_MATERIALBASE TMB ON T0.FMATERIALID=TMB.FMATERIALID
LEFT JOIN T_ORG_ORGANIZATIONS TORG ON T0.FSTOCKORGID=TORG.FORGID
LEFT JOIN T_ORG_ORGANIZATIONS_L TORGL ON TORG.FORGID=TORGL.FORGID AND TORGL.FLOCALEID=:LocaleId
LEFT JOIN T_BD_MATERIAL_L TML ON T0.FMATERIALID=TML.FMATERIALID AND TML.FLOCALEID= :LocaleId
LEFT JOIN T_BD_MATERIALGROUP TMG ON TM.FMATERIALGROUP=TMG.FID
LEFT JOIN T_BD_MATERIALGROUP_L TMGL ON TMG.FID=TMGL.FID AND TMGL.FLOCALEID= :LocaleId
LEFT JOIN T_BD_MATERIALSTOCK TMS ON T0.FMATERIALID=TMS.FMATERIALID
LEFT JOIN T_BD_MATERIALCATEGORY TMT ON TMB.FCATEGORYID=TMT.FCATEGORYID
LEFT JOIN T_BD_MATERIALCATEGORY_L TMTL ON TMT.FCATEGORYID=TMTL.FCATEGORYID AND TMTL.FLOCALEID= :LocaleId
LEFT JOIN V_ITEMCLASS_KEEPER TKP ON T0.FKEEPERID=TKP.FITEMID
LEFT JOIN V_ITEMCLASS_KEEPER_L TKPL ON T0.FKEEPERID=TKPL.FITEMID AND TKPL.FLOCALEID= :LocaleId
LEFT JOIN V_ITEMCLASS_OWNER TKW ON T0.FOWNERID=TKW.FITEMID
LEFT JOIN V_ITEMCLASS_OWNER_L TKWL ON T0.FOWNERID=TKWL.FITEMID AND TKWL.FLOCALEID= :LocaleId
LEFT JOIN T_BD_STOCK_L TBSL ON T0.FSTOCKID=TBSL.FSTOCKID AND TBSL.FLOCALEID= :LocaleId
LEFT JOIN T_BD_STOCKSTATUS TBSS ON T0.FSTOCKSTATUSID=TBSS.FSTOCKSTATUSID
LEFT JOIN T_BD_STOCKSTATUS_L TBSSL ON TBSS.FSTOCKSTATUSID=TBSSL.FSTOCKSTATUSID AND TBSSL.FLOCALEID= :LocaleId
LEFT JOIN T_BD_UNIT TUB ON TMB.FBASEUNITID=TUB.FUNITID
LEFT JOIN T_BD_UNIT TUS ON TMS.FSTOREUNITID=TUS.FUNITID
LEFT JOIN T_BD_UNIT TUA ON TMS.FAUXUNITID=TUA.FUNITID
LEFT JOIN T_BD_UNIT_L TULB ON TMB.FBASEUNITID=TULB.FUNITID AND TULB.FLOCALEID= :LocaleId
LEFT JOIN T_BD_UNIT_L TULS ON TMS.FSTOREUNITID=TULS.FUNITID AND TULS.FLOCALEID= :LocaleId
LEFT JOIN T_BD_UNIT_L TULA ON TMS.FAUXUNITID=TULA.FUNITID AND TULA.FLOCALEID= :LocaleId
LEFT JOIN T_ENG_BOM TBM ON T0.FBOMID=TBM.FID
LEFT JOIN T_BAS_FLEXVALUESDETAIL FVD ON T0.FSTOCKLOCID = FVD.FID
WHERE T0.FSTOCKORGID=1
AND ((T0.FORDERBY=-200 AND T0.FFORMID is null AND T0.FDATE>=TO_DATE('2016-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND T0.FDATE
[/code]
推荐阅读