1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115
| execute('declare @UserIDIS int=isnull(( select userid from '+@serveur+'.'+@baseprod+'.dbo.users where login=''InterfaceIS'' and UserOrgID='+@orgid+'),1)
;with T As ( select isnull(A.BaseUnitAssetID,A2.BaseUnitAssetID) as BaseUnitAssetID,e.EmployeeID,E.LocationID,E.DepartmentID,E.AccountId
, (select top 1 DateJour from zIndicateurCalendrier where Ouvre=1 and DateJour>db.shipmentdate) as dateIntervention,isnull(a.LineType,a2.linetype) as LineType,S.orgId ,S.swapIDAUto,S.ordernumber,da.orderlineid
from T_IS_DOTATION D
join ' +@serveur+'.'+@baseProd+'.dbo.zswap S on S.swapIDAUto=D.ticketnumber and S.orgId ='+@orgid+'
join T_IS_DOTATION_bl DB on DB.IS_DOTATIONid=D.IS_DOTATIONid
join T_IS_DOTATION_asset DA on DA.IS_DOTATION_BLid=DB.IS_DOTATION_BLid
join ' +@serveur+'.'+@baseProd+'.dbo.Employee E on E.Code=DA.codeDestinataire and E.OrgID ='+@orgid+'
left join ' +@serveur+'.'+@baseProd+'.dbo.Asset a on a.OrgID ='+@orgid+' and a.SerialNumber=DA.serialnumber
left join ' +@serveur+'.'+@baseProd+'.dbo.asset A2 on A2.SerialNumber=right(DA.serialnumber,8) and A2.OrgID=648 and A.assetid is null
where D.a_traiter=1
and DB.a_traiter=1
and DA.a_traiter=1
and da.serialnumber<> ''''
and D.ticketnumber='+@swapid +'
and D.nomfic='''+@nomfic+'''
and (A.assetid is not NULL or a2.assetid is not null)
)
-----------------------------------------
insert into ' +@serveur+'.'+@baseProd+'.dbo.zStockAssetAffectes
(buAssetId, cmdUserOrderLineId, employeeId, customerRef, customerRef2, reference1, locationId, departmentId, accountId, cmdUserOrderNumber, retourStockDate, retourStockMode
,retourStockUserId, affectedByUserId, affectedDate, lineType, buMasterCible, orgid,swapidauto,RetourStockSwapidAuto,affectedByUserId)
select T.BaseUnitAssetID as buAssetId
,t.orderlineid as cmdUserOrderLineId
,t.employeeid as employeeid
,null as customerRef
,null as customerRef2
,null as reference1
,t.locationid as locationid
,t.departmentid as departmentid
,t.accountid as accountid
,t.ordernumber as cmdUserOrderNumber
,null as retourStockDate
,null as retourStockMode
,null as retourStockUserId
,null as affectedByUserId
,T.dateIntervention as affectedDate
,T.linetype as lineType
,null as buMasterCible
,T.orgId as orgid
,T.swapIDAUto as swapIDauto
,null as RetourStockSwapidAuto
,@UserIDIS as affectedByUserId
from T
')
--------------------------------------------------------------------------------------------------------------
-- Non serialisé --
--------------------------------------------------------------------------------------------------------------
execute('declare @UserIDIS int=isnull(( select userid from '+@serveur+'.'+@baseprod+'.dbo.users where login=''InterfaceIS'' and UserOrgID='+@orgid+'),1)
;with T_MIT as (
select S.code as stock,case when '''+@Itemstock+''' =''itemclass'' then A.ItemClass
when '''+@Itemstock+''' =''itemcategory'' then A.itemcategory
when '''+@Itemstock+''' =''iteminfo'' then A.iteminfo
when '''+@Itemstock+''' =''itemtype'' then A.itemtype
end as ItemStock
,A.DiscoveryLink,A.ProductCode,A.SerialNumber,A.LineType,A.BaseUnitAssetID
,ROW_NUMBER() over (PARTITION by S.Code,A.ItemClass,A.ProductCode order by S.code,A.ItemClass,A.ProductCode ) num
from ' +@serveur+'.'+@baseProd+'.dbo.asset A
join ' +@serveur+'.'+@baseProd+'.dbo.AssetAccountCharge aac on aac.AssetID=a.AssetID
join ' +@serveur+'.'+@baseProd+'.dbo.Account acc on acc.AccountID=aac.AccountID
join ' +@serveur+'.'+@baseProd+'.dbo.Stock S on S.accountId =acc.AccountID
where A.orgid ='+@orgid+'
)
,T_IS as (
select e.EmployeeID,E.LocationID,E.DepartmentID,E.AccountId, (select top 1 DateJour from zIndicateurCalendrier where Ouvre=1 and DateJour>db.shipmentdate) as dateIntervention,S.orgId ,S.swapIDAUto,DA.codeproduct,D.codestock,DA.itemstock
,ROW_NUMBER() over (PARTITION by D.codestock,DA.itemstock,DA.codeproduct order by D.codestock,DA.itemstock,DA.codeproduct ) num
,s.ordernumber,da.orderlineid
from T_IS_DOTATION D
join ' +@serveur+'.'+@baseProd+'.dbo.zswap S on S.swapIDAUto=D.ticketnumber and S.orgId ='+@orgid+'
join T_IS_DOTATION_bl DB on DB.IS_DOTATIONid=D.IS_DOTATIONid
join T_IS_DOTATION_asset DA on DA.IS_DOTATION_BLid=DB.IS_DOTATION_BLid
join ' +@serveur+'.'+@baseProd+'.dbo.Employee E on E.Code=DA.codeDestinataire and E.OrgID ='+@orgid+'
where D.a_traiter=1
and DB.a_traiter=1
and DA.a_traiter=1
and da.serialnumber=''''
and D.ticketnumber='+@swapid+'
and D.nomfic='''+@nomfic+'''
)
,T As (
select T_MIT.BaseUnitAssetID,T_IS.EmployeeID,T_IS.LocationID,T_IS.DepartmentID,T_IS.AccountId, T_IS.dateIntervention,T_MIT.LineType,T_IS.orgId ,T_IS.swapIDAUto,t_is.ordernumber,t_is.orderlineid
from T_IS
join T_MIT on T_IS.codeproduct=T_MIT.ProductCode and T_IS.codestock=T_MIT.stock and T_IS.itemstock=T_MIT.ItemStock and T_IS.num=T_MIT.num
)
-------------------------------------------------------
insert into ' +@serveur+'.'+@baseProd+'.dbo.zStockAssetAffectes
(buAssetId, cmdUserOrderLineId, employeeId, customerRef, customerRef2, reference1, locationId, departmentId, accountId, cmdUserOrderNumber, retourStockDate, retourStockMode
,retourStockUserId, affectedByUserId, affectedDate, lineType, buMasterCible, orgid,swapidauto,RetourStockSwapidAuto,affectedByUserId)
select T.BaseUnitAssetID as buAssetId
,T.orderlineid as cmdUserOrderLineId
,t.employeeid as employeeid
,null as customerRef
,null as customerRef2
,null as reference1
,t.locationid as locationid
,t.departmentid as departmentid
,t.accountid as accountid
,t.ordernumber as cmdUserOrderNumber
,null as retourStockDate
,null as retourStockMode
,null as retourStockUserId
,null as affectedByUserId
,T.dateIntervention as affectedDate
,T.linetype as lineType
,null as buMasterCible
,T.orgId as orgid
,T.swapIDAUto as swapIDauto
,null as RetourStockSwapidAuto
,@UserIDIS as affectedByUserId
from T
') |
Partager