ftm 外贸计算:

set_ftm_apply_head

- 总吨数:totalqty=sum(d.qty)

set_ftm_apply_detail(ad)

采购数量:qty
采购单价:price
- 金额:itemamt=qty*price
重量(公斤) :purweight
已拆数量(吨):splitqty
- [residualqty]剩余拆分量:purweight-splitqty

set_ftm_order_head(oh)

- 金额:orderamt=sum(d.qty*d.price)

set_ftm_order_detail(od)

采购数量:qty
采购单价:price
- 磅数量:poundqty=qty*2.2046226
- 金额:itemamt=qty*price

ftm_sal_order_head(soh)

汇率:exchangerate
- 销售总数(t):totalqty=sum(sod.qty*nd.saltokgunitrate)/1000
- 销总金额:totalamt=sum(sod.itemamt)
- 代理吨利润:tonagencyprofit 

ftm_sal_order_detail(sod)

销售数量(kg):qty
销售单价(kg):price
- 金额:itemamt=qty*price
发货数量:consingqty
发货件数:consingboxs
[boxs]销售件数(销量四舍五入):'0'(nd.boxqty = 0!nd.consignboxs+nd.overflowqty(iswhole ='t'!.qty/nd.boxqty))
- [ceilboxs]销售余量向上取整件数:ceil(qty-consingqty)/nd.boxqty)
- [cmpboxs]比较数:nd.库存件数(2)(ceilboxs>nd.库存件数(2)!ceilboxs)
- [consingboxs]本单最大可提货件数:nd.库存件数(2)(iswhole='t'|cmpboxs>nd.库存件数(2)-5!cmpboxs)
- [consingqty]本单最大可提货数量:0(orderstatus=('1','4')|consignstatus = '3'!(nd.onhandqty-nd.consignoccupyqty(consingboxs=nd.库存件数(2)!consingboxs*nd.boxqty))
- [maxboxs]最大件数:nd.onhandboxs-sum(ceil(qty-consingqty/nd.boxqty)(qty>consingqty!0))
- [maxqty]最大数:nd.onhandqty-nd.consignoccupyqty-(nd.saloccupyqty(nd.boxqty<=0!sum(ceil((qty-consingqty)/nd.boxqty)*nd.boxqty(qty>consingqty&&soh.consignstatus=('1','2')&&soh.orderstatus=('2','3')!0)))
- [requiresbalance]本单需要尾款:consingqty*price-保证金占用
- [earnestamt]首缴保证金:itemamt/soh.totalamt*soh.earnestamt(soh.orderstatus=('1','2')|保证金初始)

set_ftm_lading_head

- 总金额(本币):functcurramt=sum(d.functcurramt)
- 总金额:ladingamt=sum(d.qty*d.price)

set_ftm_lading_detail(ld)

采购数量:qty
核销数量:offsetqty
采购数量:qty
采购单价:price
汇率:exchangerate
- 金额:itemamt=qty*price
磅单价:poundprice
- [unoffsetqty]未核销:qty-offsetqty
- 价格(本币):functcurrprice=poundprice*exchangerate
- 金额(本币):functcurramt=itemamt*exchangerate

set_ftm_neworder_head(nh)

谈判汇率:negotiexchrate
是否现货:isspotgoods
- 总金额:orderamt=sum(d.itemamt)
- 总金额(本币):functcurramt=总金额*negotiexchrate
- [costcexchrate]汇率:negotiexchrate(currencycodedom=currencycode!1)   

set_ftm_neworder_detail(nd)

库存单位转换比:unitrate
- 采购数:qty=purweight/unitrate(qty=0!qty)
- 采购重量(kg):purweight=(qty*unitratepurweight=0!purweight)
采购单价:price
- 发货数:arrivalqty=arrivalweight/unitrate(arrivalqty=0!arrivalqty)
- 发货重量:arrivalweight=arrivalqty*unitrate(arrivalweight=0!arrivalweight)
要货单位到销售单位转换:purtosalunitrate(kg)
发货件数:arrivalboxs  
- 提货数:consignqty=arrivalweight/unitrate(arrivalqty=0!arrivalqty)*purtosalunitrate(salunitcode!='KG'!arrivalqty*unitrate(arrivalweight=0)arrivalweight)
- [consigntqty]发货重量(吨):arrivalweight/1000
提货件数:consignboxs(单据件数)
掏箱件数:drawoutboxs
出库数:outqty=sum(no.outqty-no.inqty)
出库件数:outboxs=sum(no.outboxs-no.inboxs)
调整数:adjustqtys=um(no.inqty-no.outqty)
调整件数:adjustboxs=sum(no.inboxs-no.outboxs),
- 单件重:boxqty=consignqty/consignboxs(consignboxs>0!0)
- 入库数:inqty=consignqty*arrivalboxs/consignboxs(arrivalboxs>0&consignboxs>0!0)
库存单位转换比:invunitrate
- [intqty]入库重量(吨):(inqty+drawoutqty)*saltokgunitrate/1000
- [lasttqty]可用重量(吨):intqty(intqty>0!consigntqty)
- 掏箱数量:drawoutqty=consignqty-入库数(consignboxs=drawoutboxs+arrivalboxs&arrivalboxs>0!consignqty*drawoutboxs/consignboxs)(drawoutboxs>0&consignboxs>0!0)
- 溢短装件数:overflowqty=arrivalboxs+drawoutboxs+adjustboxs-consignboxs((drawoutboxs >0!arrivalboxs>0)&consignboxs>0!0)
- 溢短装数:overqty=入库数+掏箱数量+adjustqty-consignqty((drawoutboxs>0|arrivalboxs>0)&consignboxs>0&arrivalboxs+drawoutboxs+adjustboxs-consignboxs!=0!0)
- 库存数(含未发货):onhandqty=consignqty(consignqty>0!qty*purtosalunitrate(lower(salunitcode)!='kg'!purweight))-outqty+溢短装数    剩余量=发货或采购量-出库量+溢短装 || (arrivalqty>0!0)
- [onhandboxs]库存件数:.consignboxs+overflowqty-outboxs[-consignoccupyboxs(2)]
万洲吨均销价:rvlprice
JV第三方销售单价:jvprice
- 要货单位到kg转换:unitrate=0.4535924(lower(unitcode)='lb')|1000(lower(unitcode) ='t')!1
清关费:clearancefee
出库件数:outboxs
销售到公斤转换:saltokgunitrate
增值税:addtaxrate
关税:tariff
保质期(天):expirydate
最早生产日期:startproductdate
- 提货未出库占用件数:consignoccupyboxs=sum(cd.boxs-cd.outboxs(cd.boxs>cd.outboxs!0))
- 提货未出库占用数量(kg):consignoccupyqty=sum(cd.qty-cd.outqty(cd.qty>cd.outqty!0))
- 提货未出库占用金额:consignoccupyamt=consignoccupyqty*cd.price*ch.exchangerate
- 销售占用数量(kg)saloccupyqty=sum(sod.qty-sod.consingqty(sod.qty>sod.consingqty!0))
- 销售占用件数:saloccupyboxs=sum(sod.boxs-sod.consingboxs(sod.boxs>sod.consingboxs!0))
- 销售占用金额:saloccupyamt=saloccupyqty*sod.price*soh.exchangerate
- 销售占用数量(随件均数刷新):saloccupyrealqty=saloccupyqty(boxqty<=0!sum(((sod.qty - sod.consingqty)/boxqty)*boxqty)
- 销售总数量:totalsalqty=sum(sod.qty-sod.consingqty||cd.outqty(ch.isclose ='t'!cd.qty)
- 销售总金额:totalsalamt=sum((sod.qty-sod.consingqty)*sod.price*soh.exchangerate||cd.outqty*cd.price*ch.exchangerate(ch.isclose ='t'!cd.qty*cd.price*ch.exchangerate))
- 到期日:expiringdate=startproductdate::timestamp + (expirydate::float/360 || ' year')::interval- '1 day':: interval(expirydate>360!startproductdate::timestamp + (expirydate || ' day')::interval)
- 金额:itemamt=qty*price  
- 入库重量(公斤):inqty*saltokgunitrate
- [trvlprice]JV第三方销售单价(本币/吨):1000*jvprice*h.negotiexchrate/unitrate(h.收货人-('cofcomeat','ershangdhm')!汇率*rvlprice)
- [insurance]保险金额:发票金额*1.1
- [premium]保费:保险金额*0.0003
- [tariffprice]关税额(吨):tariff(国储&hs.tarifftype='2'!0)
- [tariffrate]税率:tariff(国储&hs.tarifftype=null|hs.tarifftype='1'!0)
- [trvlcostprice]外贸成本(元/吨):JV第三方销售单价(h.isspotgoods='t'!JV第三方销售单价*(税率+1)+关税额+clearancefee*(1+addtaxrate))
- [marketprice]当期市场参考价(元/吨):ip.marketprice(ip.marketprice!=null!dip.marketprice)
- [wmprofit]外贸预计利润(元/吨):null(当期市场参考价=null!(当期市场参考价-外贸成本)/(1+addtaxrate))
- [price]海外采购单价:(1000*d.price*(1/d.unitrate)
- [tcostprice]万洲成本价:海外采购单价*nh.negotiexchrate*(税率+1)+关税额(+clearancefee)*(1+addtaxrate)(h.isspotgoods='f'!0)
- [havebox]可售件数:consignboxs+overflowqty-saloccupyboxs-consignoccupyboxs-outboxs
- [haveqty]可售数量:onhandqty-consignoccupyqty - saloccupyrealqty
- [billtype]业务类型:(1:期货,2:自贸区业务,3:供应链企业4:现货)
- [billqty]发货数量:arrivalqty(arrivalqty>0!qty)
- [billweight]发货重量:arrivalweight(arrivalweight>0!purweight)
- [rvlamt]发票金额:JV第三方销售单价* 发货重量
- [rvlamt]发票金额:(billtype=('1','4'):rvlprice*发货重量/1000)(billtype='2':jvprice*发货重量/1000(jvprice >100!jvprice*发货数量))(billtype='3':rvllbprice*发货数量(rvllbprice>0!rvlprice*发货重量/1000))
- 入账金额:accountamt=rvlprice*发货数量/1000(billtype=('1','2')!accountamt)
- [saloccupyboxsvarpre]销售占用件数预算:saloccupyboxs(boxqty<=0!saloccupyrealqty/boxqty)
- [consignoccupyboxsvar]提货占用:onhandboxs(onhandboxs-consignoccupyboxs<0!consignoccupyboxs)
- [saloccupyboxsvar]销售占用件数最终:onhandboxs-consignoccupyboxsva(onhandboxs-consignoccupyboxsvar-saloccupyboxsvarpre<0!saloccupyboxsvarpre)
- [haveboxvar]可销件数:onhandboxs-saloccupyboxsvarpre-consignoccupyboxsvar
- [saloccupyqtyvarpre]销售占用数量预算:saloccupyqty(boxqty<=0!saloccupyrealqty)
- [consignoccupyqtyvar]提货占用数量:onhandqty(onhandqty-consignoccupyqty<0!consignoccupyqty)
- [saloccupyqtyvar]销售占用数量最终:onhandqty-consignoccupyqtyvar(onhandqty-consignoccupyqtyvar-saloccupyqtyvarpre<0!saloccupyqtyvarpre)
- [haveqtyvar]可销数量:onhandqty-consignoccupyqtyvar-saloccupyqtyvarpre
- [tonagencyprofit]代理吨均利润:max(soh.tonagencyprofit)
wzaddprice:万洲加价(元/吨)
- [wzprofit]万洲利润(万元):wzaddprice*arrivalweight/1000/10000
销售占用数量(kg):saloccupyqty 
- [totalsaltqty]销售情况-数量(吨):ceil(saloccupyqty/boxqty)*boxqty+totalsalqty-saloccupyqty(boxqty*saloccupyqty >0!totalsalqty)(iswhole='t'&lasttqty>totalsalqty!lasttqty)
- [totalsaltprice]销售情况-均价(元/吨):totalsalamt*1000/totalsalqty(totalsalqty>0!0)
- [salprofit]销售情况-销售利润(万元):tonagencyprofit*totalsaltqty/10000(flowto='19')!0(flowto-（'内传客户','16')|isstoreup=t)!totalsaltqty*(totalsaltprice-trvlcostprice)/10000/(1+addtaxrate)
- [cansalqty]销售情况-可销售量(吨):0(lasttqty-totalsaltqty<0!lasttqty-totalsaltqty)
- [totalarrivalweight]总发货重量:sum(arrivalweight)
- [generatetwo]:sum(fr.countamt)*arrivalweight/totalarrivalweight
- [generateone]:sum(fr.countamt)
- [generate]已生成费用:generateone+generatetwo(generateone!=null&generatetwo!=null!generateone+0(generateone!=null!generatetwo+0(generatetwo!=null!null)))
- [apportionmentfee]分摊费用:sum(frd.feeamt)
- [tonsfee]吨均费用:apportionmentfee/(arrivalweight/1000)
- [surplus]节余:tonsfee-clearancefee
报表清关费:rpclearancefee
目标销价:targetprice
内转价:innerprice
[arrivalweight]已到货(吨):arrivalweight(consignboxs>0!purweight)(靠港\入库日期有效!0)
[weight]后续到货(吨):0(靠港\入库日期有效!arrivalweight(consignboxs>0!purweight))

ftm_report_forward_detail(rfd)

结转数量:forwardqty

set_ftm_hs_code(hs)

关税类型(1:税率,2:吨税额):tarifftype

set_ftm_item_price(ip,默认取价厂号:dip)

行情销价:marketprice

ftm_sal_consign_head(ch)

exchangerate
totalqty=sum(cd.qty*nd.saltokgunitrate)/1000
totalamt=sum(nd.itemamt)

ftm_sal_consign_detail(cd)

发货单价(kg):price
- 发货金额:itemamt=qty*price
发货数量:qty=sod.consingqty
出库数量:outqty=sum(no.outqty*nd.saltokgunitrate)/1000)
发货件数:boxs=sod.consingboxs
出库件数:outboxs
开票数量:receiveqty
抄箱数量:copyqty
- [bookqty]账面库存:nd.arrivalweight-(sum(receiveqty[>0]|copyqty[>0]))(nd.inspectwaredate有效!0)
- [stockdifference]库存差异量:账面库存-nd.库存数
- [orderearnestamt]该单总金额:e.billflag='1':sum(e.earnestamt)
- [cosignearnestamt]非改单总金额:sum(earnestamt)
- [balanceqty]平均数:sod.qty-sod.transqty-非改单总qty
- 保证金(根据本次发货在销售订单明细中销售数量占比算出):earnestamt=orderearnestamt*cd.qty/balanceqty||orderearnestamt*qty/sod.qty
- 发货金额:itemamt=qty*sod.price

set_ftm_neworder_outstock(no)

抄箱数量:copyqty
入库数:inqty
入库件数:inboxs
出库数:outqty
出库件数:outboxs

set_ftm_handbook(fh)

进口数量:importqty
出口数量(销售数乘定额得出的原料数):exportqty
单价:price
- [balanceamt]手册余额:(importqty-exportqty)*price

ftm_sal_transfer_head(th)

- 总金额:totalamt=sum(td.itemamt)

ftm_sal_transfer_detail(td)

金额:itemamt

set_ftm_neworder_check(nk)

- [ydjustqty]溢短盘盈量:adjustqty(adjusttype='0'!0)
- [numberadjustqty]库存调整:adjustqty(adjusttype='1'!0)
- [cmadjustqty]抄码盘盈量:adjustqty(adjusttype='2'!0)
- [lhadjustqty]路耗盘盈量:adjustqty(adjusttype='3'!0)
- [floatjustqty]浮动量:adjustqty(adjusttype='4'!0)

ftm_order_stock_daily(sd)

期初数:preqty=inqty-outqty
期初件数:preboxs=inboxs-outboxs
期初盘数:predisks=0
出库数:outqty=sum(no.outqty)
出库件数:outboxs=sum(no.outboxs)
出库盘数:outdisks=0
入库数:inqty=nd.arrivalboxs*nd.arrivalweight/nd.consignboxs(nd.salunitcode = 'KG'!ndarrivalboxs * (nd.arrivalqty/nd.consignboxs)*nd.purtosalunitrate) || sum(no.inqty)
入库件数:inboxs=nd.arrivalboxs || sum(no.outboxs)
入库盘数:indisks=0

set_inv_ftm_adjust(fa)

确认调整件数:checkqty
- [outqty]出库数量:checkqt*(nd.qty-nd.outqty)/(nd.boxs-nd.outboxs)

set_inv_trans_detail(td)

- 库存单位数量:invqty=nd.inqty*nd.invunitrate
- 库存单位价格:invprice=nd.itemamt/(nd.inqty*nd.invunitrate)

ftm_sal_earnestamt(e)

[fastearnestamt]首缴保证金:sum(earnestamt(billtype ='1'!0))
[addearnestamt]追缴保证金:sum(earnestamt(billtype ='2'!0))
[releaseamt]提货转货款:sum(earnestamt*(-1)(billtype ='3'!0))
[currearnestamt]当前占用:sum(earnestamt(billflag='1'!0))
保证金:earnestamt=soh.earnestamt*sod.itemamt/sum(sod.itemamt)

ftm_fee_define(fd)

发票税率:taxrate

set_ftm_charge_estimated

预估费用税率:taxrate
归集重量(冷藏费为约当数):goodsweight
统一标准值:standardvalue
预估总金额:estimatedtotalamt=goodsweight*standardvalue
预估不含税金额:estimatednotaxamt=estimatedtotalamt*(1-taxrate)
预估税金:estimatedtaxamt=estimatedtotalamt*taxrate
归集货值:goodsamt=goodsweight*standardvalue

set_ftm_charge_estimated_detail

重量(冷藏费为约当数):goodsweight
预估费用税率:taxrate
货值:goodsamt=goodsweight*taxrate
预估总金额:estimatedtotalamt=goodsweight*fce.standardvalue
预估税金:estimatedtaxamt=goodsweight*taxrate
预估不含税金额:estimatednotaxamt=estimatedtotalamt*(1-taxrate)

set_ftm_charge_estimated_stock

库存重量:goodsweight
[chargeamt]费用金额:sum(goodsweight)*(fts.standardvalue!ft.standardvalue!0)

set_ftm_taxcharge_std(fts)

标准值:standardvalue

set_ftm_taxcharge(ft)

统一标准值:standardvalue

set_ftm_invoice_head

不含税金额:notaxamt=sum(fr.invoicenotaxamt)
税金:taxamt=sum(fr.invoicetaxamt)
总金额totalamt=sum(fr.invoicetotalamt)

ftm_fee_record(fr)

确认金额:feeamt
原金额:countamt
发票不含税金额:invoicenotaxamt=sum(invoicenotaxamt)||feeamt/(fd.taxrate +1)
发票税金:invoicetaxamt=sum(invoicetaxamt)||feeamt-feeamt/(fd.taxrate +1)

ftm_fee_record_detail(frd)

发票税率:invoicetaxrate
发票总金额:invoicetotalamt
发票不含税金额:invoicenotaxamt=invoicetotalamt/(1+invoicetaxrate)
发票税金:invoicetaxamt=invoicetotalamt-invoicenotaxamt

set_ftm_inner_price(fip)

price

ftm_report_detail(rd)

重量吨:onhandton
成本总额(万元):rvlcostamt
行情销售总额(万元):marketamt
万州利润(万元):wzprofit
利润(万元):marketprofit
万州吨均利润:wzaddprice
- [onwayflag]在途:1(orderstatus='在途'!0)
- [invflag库存/回库:1(orderstatus=('库存','回库')!0)
- [salflag]已销售:1(orderstatus='已销售'!0)
#库存:
- [invweight]重量吨:sum(invflag*onhandton)
- [invcostamt]成本总额(万元):sum(invflag*rvlcostamt)
- [invmarketamt]行情销售总额(万元):sum(invflag*marketamt)
- [invwzprofit]万州利润(万元):sum(invflag*wzprofit)
- [invprofit]利润(万元):sum(invflag*marketprofit)
- [invcostprice]订单成本(元/吨):invcostamt*10000/invweight(invweight>0!0)
- [invmarketprice]行情销价(元/吨):invmarketamt*10000/invweight(invweight>0!0)
#在途:
- [onwayweight]重量吨:sum(onwayflag*onhandton)
- [onwaycostamt]成本总额(万元):sum(onwayflag*rvlcostamt)
- [onwaymarketamt]:sum(onwayflag*marketamt)
- [onwaywzprofit]万州利润(万元):sum(onwayflag*wzprofit)
- [onwayprofit]利润(万元):sum(onwayflag*marketprofit)
- [onwaycostprice]订单成本(元/吨):onwaycostamt*10000/onwayweight(onwayweight>0!0)
- [onwaymarketprice]行情销价(元/吨):onwaymarketamt*10000/onwayweight(onwayweight>0!0)
#已销售:
- [salweight]重量吨:sum(salflag*onhandton)
- [salwzprofit]万州利润(万元):sum(salflag*wzprofit)
- [salprofit]利润(万元):sum(salflag*marketprofit)
- [salcostamt]:sum(salflag*rvlcostamt)
- [salmarketamt]:sum(salflag*marketamt)
- [salcostprice]订单成本(元/吨):salcostamt*10000/salweight(salweight>0!0)
- [salmarketprice]行情销价(元/吨):salmarketamt*10000/salweight(salweight>0!0)
#潜盈亏合计:
- [orderweight]重量吨:invweight+onwayweight
- [orderwzprofit]万州利润(万元):invwzprofit+onwaywzprofit
- [orderprofit]利润(万元):invprofit+onwayprofit
#总合计:
- [totalweight]重量吨:orderweight+salweight
- [totalprofit]利润(万元):orderprofit+salprofit
#剩余外销货权:
- [cansalweight]重量吨:sum(cansalqty)
#外销已预售订单:
- [presalweight]重量吨:sum(presalqty)
- [presalwzprofit]万州利润(万元):sum(wzaddprice*presalqty)/10000
- [presalprofit]利润(万元):sum(presalprofit)

库存 在途

orderstatus-('B','C','D','E','F','G','H')&onhandqty>0&结案
库存:inspectwaredate有效&nd.orderstatus!='E'
在途:inspectwaredate无效|nd.orderstatus='E'
## set_ftm_neworder_detail 
[tonagencyprofit]代理吨均利润:max(soh.tonagencyprofit)
[onhandton]吨数:onhandqty*saltokgunitrate/1000
[clearancefee]清关费:clearancefee(flowto = '19'!rpclearancefee(rpclearancefee>0!)clearancefee)
[costcexchrate]汇率:nh.costcexchrate
[trvlprice]双汇外贸采购价:1000*jvprice/unitrate(nd.consignee-('cofcomeat','ershangdhm')!rvlprice)
[rmbtrprice]JV第三方销售单价(本币/吨):nd.(原)trvlprice
[rvlcostamt]成本总额(万元):nd.trvlcostprice*onhandqty*saltokgunitrate/10000000
[premktprice]未签订部分价格:targetprice(flowto = '19'!innerprice(innerprice>0!fip.price)(内传客户!ip.marketprice(ip.marketprice!=nulldip.marketprice)))
[totalsalprice]销售订单销价:nd.totalsaltprice
[marketamt]行情销售总额(万元):onhandton*nd.trvlcostprice/10000(国储!totalsalprice*onhandqty/(10000*1000)(整销!onhandqty*(saloccupyamt+consignoccupyamt)/(saloccupyqty+consignoccupyqty)/10000(saloccupyqty+consignoccupyqty>onhandqty!(premktprice*(onhandqty-saloccupyqty-consignoccupyqty)*saltokgunitrate/1000+saloccupyamt+consignoccupyamt)/10000)))
[marketprice]行情价格(吨/万元):nd.trvlcostprice(flowto-('16','22','23')|国储!totalsalprice(整销!premktprice(saloccupyamt+consignoccupyamt=0!marketamt*10000 /(onhandqty/1000))))
[marketprofit]行情销售毛利(万元):onhandton*(targetprice-nd.trvlcostprice)/(1+addtaxrate)(flowto='19'!0(flowto-('16','22','23')|国储!(marketamt-rvlcostamt)/(1+addtaxrate)))
[presalqty]预售数(吨):onhandton(flowto-('19','18')|国储!0(内传客户!（onhandqty/1000(saloccupyqty+consignoccupyqty>onhandqty|整销!（saloccupyqty+consignoccupyqty)/1000)*saltokgunitrate)))
[presalamt]预售额(万元):marketprice*presalqty/10000(flowto-('19','18')!marketamt(国储!totalsalprice*presalqty/10000(整销!0(内传客户!onhandqty * (saloccupyamt+consignoccupyamt)/(saloccupyqty+consignoccupyqty)/10000(saloccupyqty+consignoccupyqty >onhandqty!(saloccupyamt+consignoccupyamt)/10000)))))
[presalprice]预售j价格(吨/万元):marketprice(flowto-('19','18')|国储!nd.trvlcostprice(flowto-('16','22','23')!totalsalprice(整销!0(内传客户!presalamt*10000/presalqty(presalqty>0)))))
[presalwzprofit]万洲预售毛利(万元):presalqty*wzaddprice/10000
[presalprofit]预售毛利(万元):onhandton*(targetprice-nd.trvlcostprice)/(1+addtaxrate)(flowto='19'!0(内传客户!0(flowto-('16','22','23')|国储!(presalamt-(presalqty*nd.trvlcostprice/10000))/(1+addtaxrate))))
[cansalqty]剩余外销货权(吨):0(内传客户|国储!0(saloccupyqty+consignoccupyqty>onhandqty!(onhandqty-saloccupyqty-consignoccupyqty)*saltokgunitrate/1000))
[salprice]销售部销价:nd.trvlcostprice(flowto-('16','22','23')|国储!1000*totalsalamt/totalsalqty(totalsalqty>0!0))

回库

reportstatus='1'&consignstatus='2'
##set_ftm_neworder_detail
[reinvqty]回库数:cd.outqty*saltokgunitrate
[onhandton]吨数:reinvqty/1000
[clearancefee]清关费:clearancefee(flowto = '19'!rpclearancefee(rpclearancefee>0!)clearancefee)
[costcexchrate]汇率:nh.costcexchrate
[trvlprice]双汇外贸采购价:1000*jvprice/unitrate(nd.consignee-('cofcomeat','ershangdhm')!rvlprice)
[rmbtrprice]JV第三方销售单价(本币/吨):nd.(原)trvlprice
[rvlcostamt]成本总额(万元):nd.trvlcostprice*onhandton/10000
[marketamt]行情销售总额(万元):onhandton*nd.trvlcostprice/10000(国储!(cd.outqty* cd.price*ch.exchangerate)/10000)
[marketprice]行情价格(吨/万元):nd.trvlcostprice(flowto-('16','22','23')|国储!1000*cd.price*ch.exchangerate/saltokgunitrate)
[marketprofit]行情销售毛利(万元):onhandton*(targetprice-nd.trvlcostprice)/(1+addtaxrate)(flowto='19'!0(flowto-('16','22','23')|国储!(marketamt-rvlcostamt)/(1+addtaxrate)))
[presalqty]预售数(吨):0(内传客户!onhandton)
[presalamt]预售额(万元):0(内传客户!marketamt)
[presalprice]预售j价格(吨/万元):0(内传客户!marketprice)
[presalwzprofit]万洲预售毛利(万元):presalqty*wzaddprice/10000
[presalprofit]预售毛利(万元):onhandton*(targetprice-nd.trvlcostprice)/(1+addtaxrate)(flowto='19'!0(内传客户|国储!marketprofit))
[cansalqty]剩余外销货权(吨):0
[salprice]销售部销价:nd.trvlcostprice(flowto-('16','22','23')|国储!1000*totalsalamt/totalsalqty(totalsalqty>0!0)）

已销售

reportstatus='2'&consignstatus='2'
##set_ftm_neworder_detail
[reinvqty]回库数:cd.outqty*saltokgunitrate
[onhandton]吨数:reinvqty/1000
[clearancefee]清关费:clearancefee(flowto = '19'!rpclearancefee(rpclearancefee>0!clearancefee))
[costcexchrate]汇率:nh.costcexchrate
[trvlprice]双汇外贸采购价:1000*jvprice/unitrate(nd.consignee-('cofcomeat','ershangdhm')!rvlprice)
[rmbtrprice]JV第三方销售单价(本币/吨):nd.(原)trvlprice
[rvlcostamt]成本总额(万元):nd.trvlcostprice*onhandton/10000
[marketamt]行情销售总额(万元):(cd.outqty* cd.price*ch.exchangerate)/10000
[marketprice]行情价格(吨/万元):nd.trvlcostprice(flowto-('16','22','23')|国储!1000*cd.price*ch.exchangerate/saltokgunitrate)
[marketprofit]行情销售毛利(万元):onhandton*(targetprice-nd.trvlcostprice)/(1+addtaxrate)(flowto='19'!0(flowto-('16','22','23')|国储!(marketamt-rvlcostamt)/(1+addtaxrate)))
[presalqty]预售数(吨):0
[presalamt]预售额(万元):0
[presalprice]预售j价格(吨/万元):0
[presalwzprofit]万洲预售毛利(万元):0
[presalprofit]预售毛利(万元):0
[cansalqty]剩余外销货权(吨):0
[salprice]销售部销价:nd.trvlcostprice(flowto-('16','22','23')|国储!1000*totalsalamt/totalsalqty(totalsalqty>0!0))

潜盈亏汇总日账

##set_ftm_neworder_detail
[clearancefee]清关费:rpclearancefee(rpclearancefee>0!clearancefee)
[costcexchrate]汇率:nh.costcexchrate
[marketprice]行情价格(吨/万元):nd.trvlcostprice(flowto-('16','22','23')|国储!targetprice(flowto='19'!innerprice(innerprice>0!fip.price)(flowto-('2','3','4','5','14','15')!ip.marketprice(ip.marketprice!=nulldip.marketprice))))
[salweight]已销售-重量吨:0
[salcostamt]已销售-订单金额(元):nd.trvlcostprice*salweight/1000  
[salamt]已销售-行情金额(元):marketprice*salweight/1000
[presalweight]外销签单-重量:saloccupyqty*saltokgunitrate
[presalcostamt]外销签单-成本金额:nd.trvlcostprice*saloccupyqty*saltokgunitrate/1000
[presalamt]外销签单-销售金额:saloccupyamt
[outweight]出库重量:outqty*saltokgunitrate
#已销售
[marketprice]行情价格(吨/万元):cd.price*1000*ch.exchangerate
[salweight]已销售-重量吨:cd.outqty*saltokgunitrate
[salamt]已销售-行情金额(元):cd.price*ch.exchangerate*cd.outqty
#回库
[marketprice]行情价格(吨/万元):cd.price*1000*ch.exchangerate
[salweight]已销售-重量吨:0
[salamt]已销售-行情金额(元):0
[presalweight]外销签单-重量:(0(否直接提货!cd.outqty(结案!cd.qty)))*saltokgunitrate
[presalcostamt]外销签单-成本金额:(0(否直接提货!cd.outqty(结案!cd.qty)))*saltokgunitrate*nd.trvlcostprice
[presalamt]外销签单-销售金额:(0(否直接提货!cd.outqty(结案!cd.qty)))*cd.price*ch.exchangerate
[invweight]库存-重量吨:0
[selfflag]自清关:0,非自清关:1
##以上合记
[invweight]库存-重量吨:sum(invweight(invweight>0!0))/1000
[invcostprice]库存-订单成本(元/吨):sum(invweight(invweight>0!0)*nd.trvlcostprice)/ sum(invweight(invweight>0!0))(sum(invweight(invweight>0!0))>0!0)
[invmarketprice]库存-行情销价(元/吨):sum(invweight(invweight>0!0)*marketprice)/ sum(invweight(invweight>0!0))(sum(invweight(invweight>0!0))>0!0)
[invprofit]库存-利润(万元):sum(profitflag*(marketprice-trvlcostprice)*(invweight(invweight>0!0))/(1+addtaxrate))/(10000*1000)
[invwzprofit]库存-万洲毛利(万元):sum(selfflag*wzaddprice*(invweight(invweight>0!0))/1000)/10000
[onwayweight]在途-重量吨:sum(onwayweight)/1000
[onwaycostprice]在途-订单成本(元/吨):sum(onwayweight*trvlcostprice)/sum(onwayweight)(sum(onwayweight)>0!0)
[onwaymarketprice]在途-行情销价(元/吨):sum(onwayweight*marketprice)/sum(onwayweight)(sum(onwayweight)>0!0)
[onwayprofit]/在途-利润(万元):sum(profitflag*(marketprice-trvlcostprice)*onwayweight/(1+addtaxrate))/(10000*1000)
[onwaywzprofit]在途-万洲毛利(万元):sum(selfflag*wzaddprice*onwayweight/1000)/10000
[salweight]已销售-重量吨:sum(salweight)/1000
[salcostprice]已销售-订单成本(元/吨):1000*sum(salcostamt)/sum(salweight)(sum(salweight)>0!0)
[salmarketprice]已销售-行情销价(元/吨):1000*sum(salamt)/sum(salweight)(sum(salweight)>0!0)
[salprofit]已销售-利润(万元):sum(profitflag*(salamt-salcostamt)/(1+addtaxrate))/10000
[orderweight]潜盈亏合计-重量吨:invweight+onwayweight
[orderprofit]潜盈亏合计-利润(万元):invprofit+onwayprofit
[orderwzprofit]潜盈亏合计-万洲毛利(万元):invwzprofit+onwaywzprofit
[totalweight]总合计-重量吨:salweight+orderweight
[totalprofit]总合计-外贸利润(万元):salprofit+orderprofit
[presalweight]已签单-重量吨:sum(presalweight)/1000
[presalprofit]已签单-利润(万元):sum(profitflag*(presalamt-presalcostamt)/(1+addtaxrate))/10000
[presalwzprofit]已签单-万洲毛利(万元):sum(selfflag*wzaddprice*presalweight/1000)/10000

成本利润预测

##set_ftm_neworder_detail
#库存:customsreleasedate!=null&isspotgoods='f'
[clearancefee]清关费:400
[qty]:unitrate*(arrivalqty-outqty)
[tqty]:qty/1000
[tprice]罗特克斯采购价:1000*price/unitrate
[trvlprice]双汇外贸采购价:rvlprice
[tcostprice]吨均成本(元/吨):tprice*nh.negotiexchrate*(税率+1)+关税额(+clearancefee)*(1+addtaxrate)
[targetprice]目标销价(元/吨):ip.targetprice(ip.targetprice!=null!dip.targetprice)
[trvlcostprice]吨均含税成本:rvlprice(nd.consignee-('cofcomeat','ershangdhm')!(clearancefee*trvlprice*(tariffrate+1)+ tariffprice+clearancefee)*(1+addtaxrate))
[marketamt]行情销售总额(万元):marketprice*tqty/10000
[targetamt]目标销售总额(万元):targetamt*tqty/10000
[costamt]成本总额(万元):tcostprice*tqty/10000
[wmprofit]外贸利润(万元):0
[rvlcostamt]成本总额(万元):trvlcostprice*tqty/10000
[targetprofit]目标利润(万元):(targetprice-trvlcostprice)*tqty/(1+addtaxrate)/10000
[wzprofit]万洲利润(万元):tqty*wzaddprice
[marketprofit]行情利润:(marketprice-trvlcostprice)*tqty/(1+addtaxrate)/10000
#在途:customsreleasedate=null&isspotgoods='f'
[clearancefee]清关费:clearancefee
[qty]:arrivalqty*unitrate(arrivalqty>0!qty*unitrate)
同库存
#现货:orderstatus=('B','C','D','E','F','G','H')&isspotgoods='t' 
[clearancefee]清关费:0
[costcexchrate]冗余(成本汇率):0
[tprice]冗余:0
[tariffprice]冗余:0
[tariffrate]冗余:0
[tcostprice]冗余:0
[trvlcostprice]吨均成本(元/吨):trvlprice
[wmprofit]外贸利润(万元):marketamt-costamt
[rvlcostamt]冗余:0
[targetprofit]:(targetprice-trvlcostprice)*tqty/10000
[wzprofit]万洲利润(万元):0
[marketprofit]行情利润:(marketprice-trvlcostprice)*tqty/10000
同库存

风控

##ftm_sal_order_detail
#明细:
[status]:
nh.orderstatus='B'   [noship]
nh.orderstatus='C'  [onway]
nh.orderstatus='D'  [onport]
nh.orderstatus=('E','F','G','H')&提货通知时间=null  [clear]
nh.orderstatus=('E','F','G','H')&提货通知时间!=null  [canconsign]
[salweight]销售数量(吨):qty*nd.saltokgunitrate/1000
[salprice]:销价(吨):1000*.price*soh.exchangerate/nd.saltokgunitrate
[earnestamt]保证金占用:ftm_sal_earnestamt(billflag = '1')
[consignweight]已提货数量(吨):consingqty*nd.saltokgunitrate/1000
[outweight]出库重量(吨):(nd.outqty+nd.consignoccupyqty)*nd.saltokgunitrate/1000
[invweight]库存重量(吨):nd.onhandqty *nd.saltokgunitrate/1000
[canconsignweight]可提货库存(吨):nd.onhandqty*nd.saltokgunitrate/1000
[noconsignweight]剩余提货量(吨):0(canconsignweight<=0!salweight-consignweight(canconsignweight>salweight-consignweight!canconsignweight))
[marketprice]当前行情销价(元/吨):nd.targetprice(nd.flowto='19'!ip.marketprice(ip.marketprice!=null!dip.marketprice))
[salamt]销售金额(元):noconsignweight*salprice
[marketamt]行情金额(元):noconsignweight*marketprice
[custprofitloss]客户盈亏(元):marketamt-salamt
[shprofitloss]双汇盈亏(元):custprofitloss+earnestamt
[reductionrate]价格降幅(%):100-100*marketprice/salprice(salamt>0!0)
[earnestamtrate]保证金比例(%):100*earnestamt/salamt(salamt>0!0)
[balancerate]剩余保证金比例(%):earnestamtrate-reductionrate
#客户商品汇总:
[earnestamt]保证金占用:销售保证金-提货保证金
[marketamt]行情金额(元):canconsignweight*marketprice
[diffprice]价格差异(元/吨):marketprice-salprice
[noshipweight]未发运:sum(salweight(status='noship'!0))
[onwayweight]在途:sum(salweight(status='onway'!0))
[onportweight]靠港:sum(salweight(status='onport'!0))
[clearweight]清关入库:sum(salweight(status='clear'!0))
[onnoconsignweight]可提货-合同期内:sum(noconsignweight(status='canconsign'&合同期内!0))
[overnoconsignweight]可提货-合同期外:sum(noconsignweight(status='canconsign'&合同期外!0))
[totalweight]总计(吨):sum(salweight(status!='canconsign'!noconsignweight))
[salamt]总金额(元):sum((salweight(status!='canconsign'!noconsignweight))*salprice)
[salprice]销价(元/吨):salamt/totalweight
[marketamt]行情总金额(元):sum((salweight(status!='canconsign'!noconsignweight))*marketprice)
[marketprice]行情(元/吨):marketamt/totalweight
[diffprice]价格差异(元/吨):marketprice-salprice
[earnestamt]保证金:sum(earnestamt)
[costprice]扣除保证金成本:(salamt-earnestamt)/totalweight(totalweight>0!0)
[custprofitloss]客户盈亏(元):marketamt-salamt
[shprofitloss]双汇盈亏(元):custprofitloss+earnestamt
[reductionrate]价格降幅(%):100-100*marketprice/salprice(salamt>0!0)
[earnestamtrate]保证金比例(%):100*earnestamt/salamt(salamt>0!0)
[balancerate]风险占比(%):100-(100*shprofitloss/earnestamt)(earnestamt>0!0)
#客户汇总:
[earnestamt]保证金占用:销售保证金-提货保证金
[diffprice]价格差异(元/吨):marketprice-salprice
[noshipweight]未发运:sum(salweight(status='noship'!0))
[onwayweight]在途:sum(salweight(status='onway'!0))
[onportweight]靠港:sum(salweight(status='onport'!0))
[clearweight]清关入库:sum(salweight(status='clear'!0))
[onnoconsignweight]可提货-合同期内:sum(noconsignweight(status='canconsign'&合同期内!0))
[overnoconsignweight]可提货-合同期外:sum(noconsignweight(status='canconsign'&合同期外!0))
[totalweight]总计(吨):sum(salweight(status!='canconsign'!noconsignweight))
[salamt]总金额(元):sum((salweight(status!='canconsign'!noconsignweight))*salprice)
[salprice]销价(元/吨):salamt/totalweight
[marketamt]行情总金额(元):sum((salweight(status!='canconsign'!noconsignweight))*marketprice)
[marketprice]行情(元/吨):marketamt/totalweight
[diffprice]价格差异(元/吨):marketprice-salprice
[earnestamt]保证金:sum(earnestamt)
[costprice]扣除保证金成本:(salamt-earnestamt)/totalweight(totalweight>0!0)
[custprofitloss]客户盈亏(元):marketamt-salamt
[shprofitloss]双汇盈亏(元):custprofitloss+earnestamt
[reductionrate]价格降幅(%):100-100*marketprice/salprice(salamt>0!0)
[earnestamtrate]保证金比例(%):100*earnestamt/salamt(salamt>0!0)
[balancerate]风险占比(%):100-(100*shprofitloss/earnestamt)(earnestamt>0!0)

采购日报

##set_ftm_neworder_detail
[qty]采购重量:purweight*(-1(nh.结案!1))
[tprice]海外采购吨价:1000*price*(1/unitrate)
[tcostprice]万洲成本价:tprice*nh.negotiexchrate*(税率+1)+关税额(+clearancefee)*(1+addtaxrate)(国储=‘f’&未经万州=‘f’!0)
[trvlcostprice]外贸成本价:trvlprice(国储=‘t'!trvlprice(h.isspotgoods='t'!trvlprice*(税率+1)+关税额+clearancefee*(1+addtaxrate)))

口岸日报

##set_ftm_neworder_head
[noreleaset]未放行未入库:取靠港正清关状态且放行日期为空的总柜数
[noreleasefourt]未放行未入库(4):取靠港正清关状态且放行日期为空报表日期-靠港期+1>4的总柜数
[releaset]已放行未入库:取海关放行、商检放行状态且入库日期为空的总柜数
[releasefourt]已放行未入库(4):取海关放行、商检放行状态，报表日期-靠港期+1>4且入库日期为空的总柜数
[total]合计:未放行未入库总柜数+已放行未入库总柜数
[nocertificatet]放行后未出证:取海关放行、商检放行状态、没有出证日期的总柜数
[montht]至本月底靠港柜数:取海上在途状态和未发运状态且靠港期在本月的所有单据
##ftm_pur_cols_change
(newdata=null|olddata=null)&!(newdata=null&olddata=null)&不结案&不是自贸区业务&创建日期=今天
[inspectwaredayboxs]当日入库柜数:sum(-1(newdata=null!1)(colname='inspectwaredate'!0))
[certificatedayboxs]当日出征柜数:sum(-1(newdata=null!1)(colname='autoimportdate'!0))
[autoimportdayboxs]当日放行柜数:sum(-1(newdata=null!1)(colname='autoimportdate'&nh.supervisiondate!=null|colname='customsreleasedate'&supervisiondate=null!0))