Description |
Create a new tab called Vouchers and get the results of this query
select E.strEntityNo + ' '+ E.strName Vendor, L.strLocationName Destination, IR.strReceiptNumber "Receipt No", IR.dtmReceiptDate "Receipt Date", IR.strBillOfLading BOL, IR.strReceiptType "Receipt Type",
case
when IR.strReceiptType = 'Purchase Order' then (select PO.strPurchaseOrderNumber from tblPOPurchase PO where PO.intPurchaseId = IRD.intOrderId)
else (select CH.strContractNumber from tblCTContractHeader CH where CH.intContractHeaderId = IRD.intOrderId) end "Order No",
I.strDescription Product,
IRD.dblUnitCost "Unit Cost",
IRD.dblOpenReceive "Qty Received",
IRD.dblLineTotal "Receipt Amount",
ISNULL(IRD.dblBillQty,0) "Qty Vouchered",
ISNULL(IRD.dblBillQty,0) "Voucher Amount"
(IRD.dblOpenReceive - ISNULL(IRD.dblBillQty,0)) "Qty to Voucher",
((IRD.dblLineTotal/IRD.dblOpenReceive)*(IRD.dblOpenReceive - ISNULL(IRD.dblBillQty,0))) "Amount to Voucher"
from tblICInventoryReceiptItem IRD
inner join tblICInventoryReceipt IR on IR.intInventoryReceiptId = IRD.intInventoryReceiptId
inner join tblICItem I on IRD.intItemId = I.intItemId
left join tblEntity E on IR.intEntityVendorId = E.intEntityId
left join tblSMCompanyLocation L on IR.intLocationId = L.intCompanyLocationId
where IR.ysnPosted = 1
--and IRD.dblOpenReceive > 0
--and IRD.intItemId = 1
--and (IRD.dblOpenReceive - ISNULL(IRD.dblBillQty,0)) > 0
|