with Costs as
(
select SUM(totalCost) as costValue,originId,originType from CostInTransLine group by originId ,originType
union all
select SUM(totalCost) as costValue,originId,originType from CostOutTransLine where originType <> 'StockTransfer' group by originId,originType
)
,
Acc as (
select SUM(debitLocalAmount) as ledgerValue,originId,originType from LedgerTransLine group by originId,originType
)
select  c.originType,c.originId,ledgerValue,costValue,abs(ledgerValue-costValue) diff from Costs c left join Acc on acc.originId = c.originId where ABS(c.costValue-acc.ledgerValue)>0.1
order by diff desc

update r set transStatus = 'Retry',regenerateLedgerReq = 1, reFetchAccConfig = 1
from InvTransReq r inner join EntitySystemEntry e on e.targetId = r.originId
left join DocumentTerm t on t.id = e.term_id
 where r.transStatus = 'Processed' and r.requestType <> 'Delete'
 and t.code = 'term_code_here'

update InvTransReq set transStatus = 'Retry',regenerateLedgerReq = 1, reFetchAccConfig = 1 
                   where transStatus = 'Processed' and requestType <> 'Delete'

update InvTransReq set transStatus = 'Retry',regenerateLedgerReq = 1, reFetchAccConfig = 1 
                   where transStatus = 'Processed' and requestType <> 'Delete'

select origintype,origincode from QtyTransLine r left join EntitySystemEntry e on e.targetId = r.originId
where e.id is null  or e.fileStatus = 'Cancelled'
union all
select origintype,origincode from ReservationTransLine r left join EntitySystemEntry e on e.targetId = r.originId
where e.id is null  or e.fileStatus = 'Cancelled'
union all
select origintype,origincode from CostInTransLine r left join EntitySystemEntry e on e.targetId = r.originId
where e.id is null  or e.fileStatus = 'Cancelled'
union all
select origintype,origincode from CostOutTransLine r left join EntitySystemEntry e on e.targetId = r.originId
where e.id is null  or e.fileStatus = 'Cancelled'

update r set requestType = 'Delete',costTransStatus = 'Retry',qtyTransStatus = 'Retry',transStatus = 'Retry'
 from InvTransReq r inner join QtyTransLine l on l.requestId = r.id
 left join EntitySystemEntry e on e.targetId = l.originId
where e.id is null or e.fileStatus = 'Cancelled'
go
update r set requestType = 'Delete',costTransStatus = 'Retry',qtyTransStatus = 'Retry',transStatus = 'Retry'
 from InvTransReq r inner join ReservationTransLine l on l.requestId = r.id
 left join EntitySystemEntry e on e.targetId = l.originId
where e.id is null or e.fileStatus = 'Cancelled'
go
update r set requestType = 'Delete',costTransStatus = 'Retry',qtyTransStatus = 'Retry',transStatus = 'Retry'
 from InvTransReq r inner join costintransline l on l.requestId = r.id
 left join EntitySystemEntry e on e.targetId = l.originId
where e.id is null or e.fileStatus = 'Cancelled'
go
update r set requestType = 'Delete',costTransStatus = 'Retry',qtyTransStatus = 'Retry',transStatus = 'Retry'
 from InvTransReq r inner join costouttransline l on l.requestId = r.id
 left join EntitySystemEntry e on e.targetId = l.originId
where e.id is null or e.fileStatus = 'Cancelled'

with x as (
select item_id,originType,originId,originCode,overdraftSatisCost,overdraftSatisQty,overdraftDetails,strSequence,sum(coalesce(netCost,0)-coalesce(overdraftSatisCost,0)) over(partition by dimensionCost order by strSequence  ROWS UNBOUNDED PRECEDING) currentCost,coalesce(currentNetCost,0) currentNetCost,
coalesce(currentNetQty,0) currentNetQty  ,dimensionCost

 from CostInTransLine c left join LegalEntity le on le.id = c.legalEntity_id 
--where valueDate < '20180101'
),
minByItem as (select min(strSequence) minStr,item_id from x 
where abs(currentCost-currentNetCost) > 0.0001
group by dimensionCost,item_id
)
select distinct originType,originId,valueDate from minByItem mi left join CostInTransLine cin on cin.strSequence = mi.minStr

with x as (
select item_id,originType,originId,originCode,overdraftSatisCost,overdraftSatisQty,overdraftDetails,strSequence,sum(coalesce(netCost,0)-coalesce(overdraftSatisCost,0)) over(partition by dimensionCost order by strSequence  ROWS UNBOUNDED PRECEDING) currentCost,coalesce(currentNetCost,0) currentNetCost,
coalesce(currentNetQty,0) currentNetQty  ,dimensionCost

 from CostInTransLine c left join LegalEntity le on le.id = c.legalEntity_id 
--where valueDate < '20180101'
),
minByItem as (select min(strSequence) minStr,item_id from x 
where abs(currentCost-currentNetCost) > 0.0001
group by dimensionCost,item_id
)
, reproceess as (select distinct originType,originId from minByItem mi left join CostInTransLine cin on cin.strSequence = mi.minStr)
update r set transStatus = 'Retry', costTransStatus = 'Retry' from reproceess rp inner join InvTransReq  r on r.originId = rp.originId

with x as (
select item_id,originType,originId,originCode,overdraftSatisCost,overdraftSatisQty,overdraftDetails,strSequence,sum(coalesce(netCost,0)-coalesce(overdraftSatisCost,0)) over(partition by dimensionCost order by strSequence  ROWS UNBOUNDED PRECEDING) currentCost,coalesce(currentNetCost,0) currentNetCost,
coalesce(currentNetQty,0) currentNetQty 
 from CostInTransLine c left join LegalEntity le on le.id = c.legalEntity_id 
-- where valueDate < '20180101'
 -- item_id = 0xFFFF00015B5DDFD135000700FF106A84
),
minByItem as (select min(strSequence) minStr,item_id from x 
where currentCost<>currentNetCost
group by item_id
)
select originType,originId, * from minByItem mi left join CostInTransLine cin on cin.strSequence = mi.minStr
/*
select currentCost-currentNetCost costDiff, *
from x
where currentCost<>currentNetCost
order by item_id,strSequence*/

declare @valueDate as date = '20211231';
declare @invnetoryAccountCode as nvarchar(255) = '1207%' ;
with costs as (
select w.code wcode,w.id wid,w.name1 wname,originType,originCode,originId, netCost,totalCost*-1 totalCost,c.netQty  netQty ,i.id item,i.code itemCode,i.name1 itemName,b.name1 branch,leg.name1 legal,leg.id legalEntityId , valueDate

from CostInTransLine c
 left join InvItem i on i.id = c.item_id 
 	left join UOM U on U.id= i.primBaseUnit_id
 
 left join Branch b on b.id = c.branch_id
  left join LegalEntity leg on leg.id = c.legalEntity_id
  left join warehouse w on w.id = c.warehouse_id
where c.valuedate <=@valueDate

union all
select w.code wcode,w.id wid,w.name1 wname,originType,originCode,originId,netCost*-1 netCost,totalCost*-1 totalCost,c.netQty*-1 netQty ,i.id item,i.code itemCode,i.name1 itemName,b.name1 branch,leg.name1 legal,leg.id legalEntityId, valueDate

from CostOutTransLine c 
 left join InvItem i on i.id = c.item_id 
 	left join UOM U on U.id= i.primBaseUnit_id
 
 left join Branch b on b.id = c.branch_id
  left join LegalEntity leg on leg.id = c.legalEntity_id
  left join warehouse w on w.id = c.warehouse_id

where c.valuedate <=@valueDate
union all
select w.code wcode,w.id wid,w.name1 wname,originType,originCode,originId,outCost*-1 netCost,outCost*-1 totalCost,c.outQty  netQty ,i.id item,i.code itemCode,i.name1 itemName,b.name1 branch,leg.name1 legal,leg.id legalEntityId , valueDate

from fifocosttransline c
 left join InvItem i on i.id = c.item_id 
 	left join UOM U on U.id= i.primBaseUnit_id
 
 left join Branch b on b.id = c.branch_id
  left join LegalEntity leg on leg.id = c.legalEntity_id
  left join warehouse w on w.id = c.warehouse_id
where c.valuedate <=@valueDate and outQty > 0

union all
select w.code wcode,w.id wid,w.name1 wname,originType,originCode,originId,inCost netCost,incost totalCost,c.inQty netQty ,i.id item,i.code itemCode,i.name1 itemName,b.name1 branch,leg.name1 legal,leg.id legalEntityId, valueDate 

from fifocosttransline c 
 left join InvItem i on i.id = c.item_id 
 	left join UOM U on U.id= i.primBaseUnit_id
 
 left join Branch b on b.id = c.branch_id
  left join LegalEntity leg on leg.id = c.legalEntity_id
  left join warehouse w on w.id = c.warehouse_id

where c.valuedate <=@valueDate and inQty > 0
)
,
fromLedger as (
select sum(debitLocalAmount-creditLocalAmount) total,subsidiaryCode,subsidiaryId
,originId
from LedgerTransLine l
left join Account acc on acc.id = l.account_id
where acc.code like @invnetoryAccountCode
and l.valueDate <=@valueDate
group by subsidiaryCode,subsidiaryId
,originId
)
/*
select * from costs where item = 0xFFFF00016EEFACAFE2000000FF19F35D
order by strSequence,netQty
*/
, fromCost as (
select -- wcode, sum(netCost) cost,sum(netQty)qty,item,itemCode,itemName ,legalEntityId,min(valueDate) minDate,max(valueDate) maxDate,min(strSequence) minSeq,dimensionCost
wid,sum(totalCost) cost
,originId
from costs 
group by wid
,originId
)
, costledger as (
select distinct wid,originId from fromCost
union
select distinct subsidiaryId,originId from fromLedger
)
select sum(coalesce(c.cost,0)) c,sum(coalesce(l.total,0)) l,abs(abs(sum(coalesce(c.cost,0)))-abs(sum(coalesce(l.total,0)))) diff,cl.wid,w.code,w.name1 
,cl.originId,e.targetType,e.code
from costledger cl
left join fromCost c on c.wid = cl.wid and c.originId = cl.originId
left join fromLedger l on l.subsidiaryId = cl.wid and l.originId = cl.originId
left join Warehouse w on w.id = cl.wid
left join EntitySystemEntry e on e.targetId = cl.originId
group by cl.wid ,w.code,w.name1
,cl.originId,e.targetType,e.code
having abs(abs(sum(coalesce(c.cost,0)))-abs(sum(coalesce(l.total,0))))>1 
order by diff desc

declare @onDate as date = '20231231'
declare @inventoryAccountsPrefix as nvarchar(30) = '1106'
;
with Costs1 as
(
select SUM(totalCost) as inCost,0 outCost
from CostInTransLine 
where valueDate<=@onDate

union all
select 0 inCost, SUM(totalCost) as outCost
from CostOutTransLine 
where valueDate<=@onDate
),
Costs as (
select SUM(inCost) -sum(outCost) costs
from Costs1 

)
,
Acc as (
select SUM(debitLocalAmount) - sum(creditLocalAmount) acc
from LedgerTransLine l left join Account acc on acc.id = l.account_id
where acc.code like @inventoryAccountsPrefix + '%' and originType in ('StockIssue','StockReceipt','StockTransfer','CostRevaluation')
and valueDate<=@onDate
)
select costs,acc,costs-acc diff from acc inner join costs on 1 = 1

declare @onDate as date = '20231231'
declare @inventoryAccountsPrefix as nvarchar(30) = '1106'
;
with Costs1 as
(
select SUM(totalCost) as inCost,0 outCost,originId,originType,valueDate 
from CostInTransLine 
where valueDate <=@onDate
group by originId ,originType,valueDate
union all
select 0 inCost, SUM(totalCost) as outCost,originId,originType,valueDate 
from CostOutTransLine 
where valueDate <=@onDate
group by originId,originType ,valueDate
),
Costs as (
select SUM(inCost) as inCost,sum(outCost) outCost,originId,originType,valueDate 
from Costs1 
group by originId ,originType,valueDate
)
,
Acc as (
select SUM(debitLocalAmount) as dr,sum(creditLocalAmount) cr,originId,originType 
from LedgerTransLine l left join Account acc on acc.id = l.account_id
where acc.code like @inventoryAccountsPrefix+'%' and originType in ('StockIssue','StockReceipt','StockTransfer','CostRevaluation')
and valueDate <=@onDate
group by originId,originType
)

select case when c.originType <> 'CostRevaluation' 
then abs( sum(dr-inCost))+abs(sum(cr-outCost))
else abs( sum(dr-cr-(inCost-outCost))) end
diff ,c.originId,c.originType,
sum(sum(case when c.originType <> 'CostRevaluation' 
then  dr-inCost+cr-outCost
else dr-cr-(inCost-outCost) end)) over (partition by 1) tt

from Costs c left join Acc on acc.originId = c.originId 
left join InvTransReq r on r.originId = c.originId 

group by c.originId,c.originType
having  case when c.originType <> 'CostRevaluation' 
then abs( sum(dr-inCost))+abs(sum(cr-outCost))
else abs( sum(dr-cr-(inCost-outCost))) end >= 0.01

order by diff desc

with Costs as
(
select SUM(totalCost) as costValue,originId,originType from CostInTransLine group by originId ,originType
union all
select SUM(totalCost) as costValue,originId,originType from CostOutTransLine where originType <> 'StockTransfer' group by originId,originType
)
,
Acc as (
select SUM(debitLocalAmount) as ledgerValue,originId,originType from LedgerTransLine group by originId,originType
)

update r set transStatus = 'Retry',regenerateLedgerReq = 1,priority = 3 from Costs c left join Acc on acc.originId = c.originId left join InvTransReq r on r.originId = c.originId
where ABS(c.costValue-acc.ledgerValue)>0.1 and r.requestType <> 'Delete'

with costs as (
select w.code wcode,w.name1 wname, netCost,c.netQty  netQty ,i.id item,i.code itemCode,b.name1 branch,leg.name1 legal,leg.id legalEntityId 
from CostInTransLine c
 left join InvItem i on i.id = c.item_id 
 	left join UOM U on U.id= i.primBaseUnit_id
 
 left join Branch b on b.id = c.branch_id
  left join LegalEntity leg on leg.id = c.legalEntity_id
  left join warehouse w on w.id = c.warehouse_id
--where c.valuedate <'20180101'

union all
select w.code wcode,w.name1 wname,netCost*-1 netCost,c.netQty*-1 netQty ,i.id item,i.code itemCode,b.name1 branch,leg.name1 legal,leg.id legalEntityId from CostOutTransLine c 
 left join InvItem i on i.id = c.item_id 
 	left join UOM U on U.id= i.primBaseUnit_id
 
 left join Branch b on b.id = c.branch_id
  left join LegalEntity leg on leg.id = c.legalEntity_id
  left join warehouse w on w.id = c.warehouse_id

--where c.valuedate <'20180101'
)
select sum(netCost) cost,sum(netQty)qty,item,itemCode ,legalEntityId from costs 

group by item,itemCode,legalEntityId
having sum(netQty) = 0 and abs(sum(netCost)) > 0.01
order by abs(sum(netCost)) desc

with delivery as (
select sum(cin.netCost) receiptCost,po.id from ProductionOrder po inner join ProductDelivery pod on pod.productionOrder_id = po.id inner join StockReceipt sr on sr.fromDoc_id = pod.id
inner join CostInTransLine cin on cin.originId = sr.id 
group by po.id
),
issue as (
select sum(cout.netCost) materialCost,po.id from ProductionOrder po inner join RawMaterialIssue pod on pod.productionOrder_id = po.id inner join StockIssue sr on sr.fromDoc_id = pod.id
inner join CostOutTransLine cout on cout.originId = sr.id
group by po.id
),
ret as (
select sum(cin.netCost) retCost,po.id from ProductionOrder po inner join RawMaterialReturn pod on pod.productionOrder_id = po.id inner join StockReceipt sr on sr.fromDoc_id = pod.id
inner join CostInTransLine cin on cin.originId = sr.id 
group by po.id
),
resources as(
select sum(l.total) resourceCost,po.id from ProductionOrder po inner join ResourceVoucher v on v.productionOrder_id = po.id left join ResourceVoucherLine l on l.resourceVoucher_id = v.id
group by po.id
)
select cv.entityType, cv.id,po.valueDate, receiptCost,materialCost+resourceCost-coalesce(retCost,0),receiptCost-(materialCost+resourceCost-coalesce(retCost,0)),issue.materialCost,ret.retCost,resources.resourceCost from ProductionOrder po left join
delivery on delivery.id = po.id left join issue on issue.id = po.id left join ret on ret.id = po.id 
left join resources on resources.id = po.id
left join OrderCloseVoucher cv on cv.productionOrder_id = po.id
where abs(receiptCost-(materialCost+resourceCost-coalesce(retCost,0)))>1.5 and cv.id is not null

with toReproocess as (
select  distinct l.originType,l.originId,l.valueDate
 from FifoCostTransLine l 
left join FifoCostMatcher mi on mi.inLine_id = l.id
left join FifoCostMatcher mo on mo.outLine_id = l.id
group by l.originType,l.originId,l.remainingInQty,l.remainingOutQty,l.inQty,l.outQty,l.item_id,l.id,l.valueDate
having l.remainingInQty <> l.inQty - sum(coalesce(mi.consumedQty,0))
 or l.remainingOutQty <> l.outQty - sum(coalesce(mo.consumedQty,0))
 order by l.valueDate desc
 )
 update r set r.transstatus = 'Retry',r.costtransstatus = 'Retry' from toReproocess t inner join InvTransReq r on r.originId = t.originId

declare @annualIncome decimal(10,2) = 400000