SELECT a.id,
a.a_id,
d.pcontract_id,
a.e_style,
a.status,
a.emp_id,
c.dep_id,
c.agentemp_id,
c.agentdep_id,
b.sup_id,
f.sup_name,
b.mete_id,
mete_name = g.name,
mete_desc = g.desc_name,
e.pcontract_ratestatus,
pcontract_rate = CASE WHEN a.productclass='固定资产' then 0.0000 else e.pcontract_rate end,
d.pcontract_currency,
mete_price = convert(decimal(18,6),round(b.mete_rateprice/(1.00+e.pcontract_rate),6)),
b.mete_rateprice,
payment_metequantity = b.payment_metequantity,
payment_receivedquantity = e.pitem_quantity,
payment_metetotalamount=convert(decimal(18,4),round(sum(convert(decimal(18,6),round(b.mete_rateprice/(1.00+ case when a.productclass='固定资产' then 0.0000 else e.pcontract_rate end),6))*b.payment_metequantity),2)),
e.pitem_quantitypercent,
payment_pcontract_max=convert(decimal(18,6),round(e.pitem_quantity*(1+e.pitem_quantitypercent),6)),
payment_usedquantity = round(sum(j.payment_quantity),6),
payment_usedamount_hk= round(sum(j.payment_amount),6),
payment_usedamount =round(sum(j.payment_amount+j.payment_metetax),6),
payment_totaltranscost =( select round(sum(pcontract_transcost),2) from d_pcontract where pur_id=a.id),
payment_usedtranscost=(select sum(payment_transcosttotal) from d_paymentapplypurcontent where d_paymentapplypurcontent.pur_id=d.pur_id and d_paymentapplypurcontent.payment_type=0),
payment_marginpercent = d.pcontract_marginpercent,
payment_usedmargin = round(sum(CASE WHEN i.payment_margin IS NULL THEN 0 ELSE i.payment_margin END),6),
term_id = d.term_id,
a.productclass,a.subjectnum,f.sup_account,f.sup_bankname,f.sup_bankaddress,
k.sup_bamount
FROM ((dbo.vp_pur_mete_id a join dbo.vp_payment_receivelist b on a.pur_id = b.pur_id and b.o_mete_id = a.mete_id join dbo.vp_agentemp c on a.emp_id = c.emp_id
join dbo.d_pcontract d on a.e_style = 2 and d.pur_id = a.id and d.sup_id = b.sup_id
and d.pcontract_status = 2 and d.pcontract_id = b.pcontract_id
join dbo.d_pcontractcontent e on d.pcontract_id = e.pcontract_id and b.mete_id = e.pitem_id and e.o_pitem_id = a.mete_id and
b.pcontract_id = e.pcontract_id
join dbo.c_supplier f on b.sup_id = f.sup_id and d.sup_id = f.sup_id
join dbo.vp_mete_item g on b.mete_id = g.id and e.pitem_id = g.id)
left join c_supplybalance k on a.pur_id = k.pur_id and
b.pur_id = k.pur_id and
b.sup_id = k.sup_id and
b.pcontract_id = k.pcontract_id and
d.pur_id = k.pur_id and
d.pcontract_id = k.pcontract_id and
d.sup_id = k.sup_id and
e.pcontract_id = k.pcontract_id and
f.sup_id = k.sup_id)
left join
(dbo.d_paymentapplysupplycontent h join dbo.d_paymentapplypurcontent i on
rtrim(ltrim(h.payment_id)) = rtrim(ltrim(i.payment_id)) and
rtrim(ltrim(h.payment_invoiceid)) = rtrim(ltrim(i.payment_invoiceid))
join dbo.d_paymentapplymetecontent j on rtrim(ltrim(h.payment_id)) = rtrim(ltrim(j.payment_id)) and
rtrim(ltrim(i.payment_id)) = rtrim(ltrim(j.payment_id)) and
rtrim(ltrim(h.payment_invoiceid)) = rtrim(ltrim(j.payment_invoiceid)) and
rtrim(ltrim(i.payment_invoiceid)) = rtrim(ltrim(j.payment_invoiceid)) and
rtrim(ltrim(i.pur_id)) = rtrim(ltrim(j.pur_id)) and rtrim(ltrim(i.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) ) on
rtrim(ltrim(a.id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(a.id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(b.pur_id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(b.pcontract_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(b.sup_id)) = rtrim(ltrim(h.sup_id)) and
rtrim(ltrim(b.mete_id)) = rtrim(ltrim(j.mete_id)) and
rtrim(ltrim(b.pur_id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(d.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(d.sup_id)) = rtrim(ltrim(h.sup_id)) and
rtrim(ltrim(d.pur_id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(d.pcontract_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(d.pur_id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(d.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(e.pcontract_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(e.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(e.pitem_id)) = rtrim(ltrim(j.mete_id))
WHERE a.status = 12 and a.e_style = 2 and
(SELECT count(pur_id) FROM dbo.d_paymentapplypurcontent u,dbo.d_paymentapply v
WHERE u.pur_id = a.id and u.pcontract_id = d.pcontract_id and u.payment_id = v.payment_id and v.payment_status >1 and u.payment_type = 0) = 0
group by a.id,d.pur_id,
a.a_id,
d.pcontract_id,
a.e_style,
a.status,
a.emp_id,
c.dep_id,
c.agentemp_id,
c.agentdep_id,
b.sup_id,
f.sup_name,
b.mete_id,
g.name,
g.desc_name,
e.pcontract_ratestatus,
--pcontract_rate = CASE WHEN a.productclass='固定资产' then 0.0000 else e.pcontract_rate end,
d.pcontract_currency,
e.pcontract_rate,
b.mete_rateprice,
b.payment_metequantity,
e.pitem_quantity,
e.pitem_quantitypercent,
e.pitem_quantitypercent,
d.pcontract_transcost,
d.pcontract_marginpercent,
d.term_id,
a.productclass,a.subjectnum,f.sup_account,f.sup_bankname,f.sup_bankaddress,
k.sup_bamount
a.a_id,
d.pcontract_id,
a.e_style,
a.status,
a.emp_id,
c.dep_id,
c.agentemp_id,
c.agentdep_id,
b.sup_id,
f.sup_name,
b.mete_id,
mete_name = g.name,
mete_desc = g.desc_name,
e.pcontract_ratestatus,
pcontract_rate = CASE WHEN a.productclass='固定资产' then 0.0000 else e.pcontract_rate end,
d.pcontract_currency,
mete_price = convert(decimal(18,6),round(b.mete_rateprice/(1.00+e.pcontract_rate),6)),
b.mete_rateprice,
payment_metequantity = b.payment_metequantity,
payment_receivedquantity = e.pitem_quantity,
payment_metetotalamount=convert(decimal(18,4),round(sum(convert(decimal(18,6),round(b.mete_rateprice/(1.00+ case when a.productclass='固定资产' then 0.0000 else e.pcontract_rate end),6))*b.payment_metequantity),2)),
e.pitem_quantitypercent,
payment_pcontract_max=convert(decimal(18,6),round(e.pitem_quantity*(1+e.pitem_quantitypercent),6)),
payment_usedquantity = round(sum(j.payment_quantity),6),
payment_usedamount_hk= round(sum(j.payment_amount),6),
payment_usedamount =round(sum(j.payment_amount+j.payment_metetax),6),
payment_totaltranscost =( select round(sum(pcontract_transcost),2) from d_pcontract where pur_id=a.id),
payment_usedtranscost=(select sum(payment_transcosttotal) from d_paymentapplypurcontent where d_paymentapplypurcontent.pur_id=d.pur_id and d_paymentapplypurcontent.payment_type=0),
payment_marginpercent = d.pcontract_marginpercent,
payment_usedmargin = round(sum(CASE WHEN i.payment_margin IS NULL THEN 0 ELSE i.payment_margin END),6),
term_id = d.term_id,
a.productclass,a.subjectnum,f.sup_account,f.sup_bankname,f.sup_bankaddress,
k.sup_bamount
FROM ((dbo.vp_pur_mete_id a join dbo.vp_payment_receivelist b on a.pur_id = b.pur_id and b.o_mete_id = a.mete_id join dbo.vp_agentemp c on a.emp_id = c.emp_id
join dbo.d_pcontract d on a.e_style = 2 and d.pur_id = a.id and d.sup_id = b.sup_id
and d.pcontract_status = 2 and d.pcontract_id = b.pcontract_id
join dbo.d_pcontractcontent e on d.pcontract_id = e.pcontract_id and b.mete_id = e.pitem_id and e.o_pitem_id = a.mete_id and
b.pcontract_id = e.pcontract_id
join dbo.c_supplier f on b.sup_id = f.sup_id and d.sup_id = f.sup_id
join dbo.vp_mete_item g on b.mete_id = g.id and e.pitem_id = g.id)
left join c_supplybalance k on a.pur_id = k.pur_id and
b.pur_id = k.pur_id and
b.sup_id = k.sup_id and
b.pcontract_id = k.pcontract_id and
d.pur_id = k.pur_id and
d.pcontract_id = k.pcontract_id and
d.sup_id = k.sup_id and
e.pcontract_id = k.pcontract_id and
f.sup_id = k.sup_id)
left join
(dbo.d_paymentapplysupplycontent h join dbo.d_paymentapplypurcontent i on
rtrim(ltrim(h.payment_id)) = rtrim(ltrim(i.payment_id)) and
rtrim(ltrim(h.payment_invoiceid)) = rtrim(ltrim(i.payment_invoiceid))
join dbo.d_paymentapplymetecontent j on rtrim(ltrim(h.payment_id)) = rtrim(ltrim(j.payment_id)) and
rtrim(ltrim(i.payment_id)) = rtrim(ltrim(j.payment_id)) and
rtrim(ltrim(h.payment_invoiceid)) = rtrim(ltrim(j.payment_invoiceid)) and
rtrim(ltrim(i.payment_invoiceid)) = rtrim(ltrim(j.payment_invoiceid)) and
rtrim(ltrim(i.pur_id)) = rtrim(ltrim(j.pur_id)) and rtrim(ltrim(i.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) ) on
rtrim(ltrim(a.id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(a.id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(b.pur_id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(b.pcontract_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(b.sup_id)) = rtrim(ltrim(h.sup_id)) and
rtrim(ltrim(b.mete_id)) = rtrim(ltrim(j.mete_id)) and
rtrim(ltrim(b.pur_id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(d.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(d.sup_id)) = rtrim(ltrim(h.sup_id)) and
rtrim(ltrim(d.pur_id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(d.pcontract_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(d.pur_id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(d.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(e.pcontract_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(e.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(e.pitem_id)) = rtrim(ltrim(j.mete_id))
WHERE a.status = 12 and a.e_style = 2 and
(SELECT count(pur_id) FROM dbo.d_paymentapplypurcontent u,dbo.d_paymentapply v
WHERE u.pur_id = a.id and u.pcontract_id = d.pcontract_id and u.payment_id = v.payment_id and v.payment_status >1 and u.payment_type = 0) = 0
group by a.id,d.pur_id,
a.a_id,
d.pcontract_id,
a.e_style,
a.status,
a.emp_id,
c.dep_id,
c.agentemp_id,
c.agentdep_id,
b.sup_id,
f.sup_name,
b.mete_id,
g.name,
g.desc_name,
e.pcontract_ratestatus,
--pcontract_rate = CASE WHEN a.productclass='固定资产' then 0.0000 else e.pcontract_rate end,
d.pcontract_currency,
e.pcontract_rate,
b.mete_rateprice,
b.payment_metequantity,
e.pitem_quantity,
e.pitem_quantitypercent,
e.pitem_quantitypercent,
d.pcontract_transcost,
d.pcontract_marginpercent,
d.term_id,
a.productclass,a.subjectnum,f.sup_account,f.sup_bankname,f.sup_bankaddress,
k.sup_bamount
SELECT a.id,a.a_id,d.delivery_id,a.e_style,a.status,a.emp_id,c.dep_id,c.agentemp_id,c.agentdep_id,
b.sup_id,f.sup_name,b.mete_id,mete_name = g.name,mete_desc = g.desc_name,e.delivery_ratestatus,
pcontract_rate = CASE WHEN a.productclass='固定资产' then 0.0000 else e.delivery_rate end,
d.delivery_currency,
mete_price = convert(decimal(18,6),round(b.mete_rateprice/(1+e.delivery_rate),6)),--b.mete_price,
b.mete_rateprice,
payment_metequantity =b.payment_metequantity,
payment_receivedquantity = e.pitem_quantity ,
payment_metetotalamount=convert(decimal(18,4),round(sum(convert(decimal(18,6),round(b.mete_rateprice/(1.00+CASE WHEN a.productclass='固定资产' then 0.0000 else e.delivery_rate end),6))*b.payment_metequantity),2)),
e.pitem_quantitypercent,
payment_pcontract_max=convert(decimal(18,6),e.pitem_quantity*(1+e.pitem_quantitypercent)),
payment_usedquantity = round(sum(CASE WHEN j.payment_quantity IS NULL THEN 0 ELSE j.payment_quantity END),6),
payment_usedamount_hk=round(sum(CASE WHEN j.payment_amount IS NULL THEN 0 ELSE j.payment_amount END),6),
payment_usedamount = round(sum(CASE WHEN j.payment_amount+j.payment_metetax IS NULL THEN 0 ELSE j.payment_amount+j.payment_metetax END),6),
-- payment_totaltranscost = CASE WHEN d.delivery_transcost IS NULL THEN 0 ELSE d.delivery_transcost END,
payment_totaltranscost = ( select sum(delivery_transcost) from d_partialdelivery where pur_id=a.id),
payment_usedtranscost=(select sum(payment_transcosttotal) from d_paymentapplypurcontent where d_paymentapplypurcontent.pur_id=d.pur_id),
payment_marginpercent = CASE WHEN d.delivery_marginpercent IS NULL THEN 0 ELSE d.delivery_marginpercent END,
payment_usedmargin = round(sum(CASE WHEN i.payment_margin IS NULL THEN 0 ELSE i.payment_margin END),6),
term_id = d.term_id,
a.productclass,a.subjectnum,f.sup_account,f.sup_bankname,f.sup_bankaddress,k.sup_bamount
FROM ((dbo.vp_pur_mete_id a join dbo.vp_payment_receivelist b on a.pur_id = b.pur_id and b.o_mete_id = a.mete_id
join dbo.vp_agentemp c on a.emp_id = c.emp_id
join dbo.d_partialdelivery d on a.e_style = 1 and d.pur_id = a.id and d.sup_id = b.sup_id
and d.delivery_status = 3 and d.delivery_id = b.pcontract_id
join dbo.d_partialdeliverycontent e on d.delivery_id = e.delivery_id and b.mete_id = e.pitem_id and e.o_pitem_id = a.mete_id and
e.delivery_id = b.pcontract_id join dbo.c_supplier f on b.sup_id = f.sup_id and d.sup_id = f.sup_id
join dbo.vp_mete_item g on b.mete_id = g.id and e.pitem_id = g.id)
left join c_supplybalance k on a.pur_id = k.pur_id and
b.pur_id = k.pur_id and
b.sup_id = k.sup_id and
b.pcontract_id = k.pcontract_id and
d.pur_id = k.pur_id and
d.delivery_id = k.pcontract_id and
d.sup_id = k.sup_id and
e.delivery_id = k.pcontract_id and
f.sup_id = k.sup_id)
left join
(dbo.d_paymentapplysupplycontent h join dbo.d_paymentapplypurcontent i on
rtrim(ltrim(h.payment_id)) = rtrim(ltrim(i.payment_id)) and
rtrim(ltrim(h.payment_invoiceid)) = rtrim(ltrim(i.payment_invoiceid))
join dbo.d_paymentapplymetecontent j on rtrim(ltrim(h.payment_id)) = rtrim(ltrim(j.payment_id)) and
rtrim(ltrim(i.payment_id)) = rtrim(ltrim(j.payment_id)) and
rtrim(ltrim(h.payment_invoiceid)) = rtrim(ltrim(j.payment_invoiceid)) and
rtrim(ltrim(i.payment_invoiceid)) = rtrim(ltrim(j.payment_invoiceid)) and
rtrim(ltrim(i.pur_id)) = rtrim(ltrim(j.pur_id)) and rtrim(ltrim(i.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) ) on
rtrim(ltrim(a.id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(a.id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(b.pur_id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(b.sup_id)) = rtrim(ltrim(h.sup_id)) and
rtrim(ltrim(b.pcontract_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(b.mete_id)) = rtrim(ltrim(j.mete_id)) and
rtrim(ltrim(b.pur_id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(b.pcontract_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(d.sup_id)) = rtrim(ltrim(h.sup_id)) and
rtrim(ltrim(d.pur_id)) = rtrim(ltrim(i.pur_id)) and
rtrim(ltrim(d.delivery_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(d.pur_id)) = rtrim(ltrim(j.pur_id)) and
rtrim(ltrim(d.delivery_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(e.delivery_id)) = rtrim(ltrim(i.pcontract_id)) and
rtrim(ltrim(e.delivery_id)) = rtrim(ltrim(j.pcontract_id)) and
rtrim(ltrim(e.pitem_id)) = rtrim(ltrim(j.mete_id))
WHERE a.status = 12 and
a.e_style =1 and
(SELECT count(pur_id) FROM dbo.d_paymentapplypurcontent u,dbo.d_paymentapply v
WHERE u.pur_id = a.id and u.pcontract_id = d.delivery_id and u.payment_id = v.payment_id and v.payment_status >1 and u.payment_type = 0) = 0
group by a.id,d.pur_id,a.a_id,d.delivery_id,a.e_style,a.status,a.emp_id,c.dep_id,c.agentemp_id,c.agentdep_id,
b.sup_id,f.sup_name,b.mete_id,g.name, g.desc_name,e.delivery_ratestatus,
e.delivery_rate,
d.delivery_currency,
e.delivery_rate,
b.mete_rateprice,
b.payment_metequantity,
e.pitem_quantity ,
e.pitem_quantitypercent,
d.delivery_transcost,
d.delivery_marginpercent,
d.term_id,
a.productclass,a.subjectnum,f.sup_account,f.sup_bankname,f.sup_bankaddress,k.sup_bamount