ALTER PROCEDURE Uams.AMS_ReceiveAsset
(
@PurchaseOrderId int,
@InvoiceId int,
@GrossTotal decimal(18,2),
@LocationId int,
@CreatedBy int,
@DataTable_Asset as Uams.AMS_ReceiveAssetDetails_Type READONLY,
@msg varchar(50) output,
@ReceiptNo varchar(50) output
)
As
Begin
--Begin Transaction
--Begin Try
declare @ReceiveId int
declare @x int
if(not exists(select Receive_ID from Uams.AMS_ReceiveAssetMaster where Invoice_ID=@InvoiceId and Purchase_Order_ID=@PurchaseOrderId))
begin
set @x=(select isnull(max(Receive_ID),0) from Uams.AMS_ReceiveAssetMaster)
if(@x=0)
begin
set @x=1
end
else
begin
set @x=@x+1
end
set @ReceiptNo='Receipt/'+Convert(varchar(10),@x);
insert into Uams.AMS_ReceiveAssetMaster(Receipt_No,Invoice_ID,Purchase_Order_ID,Gross_Total,Location_ID,Created_On,Created_By,Is_Active,Is_Deleted)
values(@ReceiptNo,@InvoiceId,@PurchaseOrderId,@GrossTotal,@LocationId,GETDATE(),@CreatedBy,1,0)
if(@@ROWCOUNT>0)
begin
set @ReceiveId=@@Identity
insert into Uams.AMS_ReceiveAssetDetails(Asset_ID,Quantity,Unit_Price,Vat,Discount,Total_Price,Receive_ID,Status,Created_On,Created_By,Is_Active,Is_Deleted)
select Asset_ID,Quantity,Unit_Price,Vat,Discount,Total_Price,@ReceiveId,'Pending',GETDATE(),@CreatedBy,1,0 from @DataTable_Asset
set @msg='Asset received against Receipt No : Receipt/'+Convert(varchar(10),@ReceiveId)
end
begin
declare @ReceiveSum decimal(18,2)
declare @PurchaseSum decimal(18,2)
update Uams.AMS_InvoiceMaster set Status='Receive' where Invoice_ID=@InvoiceId and Purchase_Order_ID=@PurchaseOrderId
set @ReceiveSum=(select sum(Quantity) from Uams.AMS_ReceiveAssetDetails where Receive_ID=@ReceiveId)
set @PurchaseSum=(select sum(Quantity) from Uams.AMS_PurchaseItem where Purchase_Order_ID=@PurchaseOrderId)
if(@ReceiveSum=@PurchaseSum)
begin
update Uams.AMS_ReceiveAssetMaster set Status='FullyReceive' where Receive_ID=@ReceiveId
update Uams.AMS_PurchaseOrder set Status='FullyReceive' where Purchase_Order_ID=@PurchaseOrderId
end
else
begin
update Uams.AMS_ReceiveAssetMaster set Status='PartialReceive' where Receive_ID=@ReceiveId
update Uams.AMS_PurchaseOrder set Status='PartialReceive' where Purchase_Order_ID=@PurchaseOrderId
end
end
end
else
begin
set @ReceiptNo='No Receipt'
set @msg='Asset already received'
return
end
--Commit
--end Try
--Begin Catch
--Rollback
--End Catch
End
(
@PurchaseOrderId int,
@InvoiceId int,
@GrossTotal decimal(18,2),
@LocationId int,
@CreatedBy int,
@DataTable_Asset as Uams.AMS_ReceiveAssetDetails_Type READONLY,
@msg varchar(50) output,
@ReceiptNo varchar(50) output
)
As
Begin
--Begin Transaction
--Begin Try
declare @ReceiveId int
declare @x int
if(not exists(select Receive_ID from Uams.AMS_ReceiveAssetMaster where Invoice_ID=@InvoiceId and Purchase_Order_ID=@PurchaseOrderId))
begin
set @x=(select isnull(max(Receive_ID),0) from Uams.AMS_ReceiveAssetMaster)
if(@x=0)
begin
set @x=1
end
else
begin
set @x=@x+1
end
set @ReceiptNo='Receipt/'+Convert(varchar(10),@x);
insert into Uams.AMS_ReceiveAssetMaster(Receipt_No,Invoice_ID,Purchase_Order_ID,Gross_Total,Location_ID,Created_On,Created_By,Is_Active,Is_Deleted)
values(@ReceiptNo,@InvoiceId,@PurchaseOrderId,@GrossTotal,@LocationId,GETDATE(),@CreatedBy,1,0)
if(@@ROWCOUNT>0)
begin
set @ReceiveId=@@Identity
insert into Uams.AMS_ReceiveAssetDetails(Asset_ID,Quantity,Unit_Price,Vat,Discount,Total_Price,Receive_ID,Status,Created_On,Created_By,Is_Active,Is_Deleted)
select Asset_ID,Quantity,Unit_Price,Vat,Discount,Total_Price,@ReceiveId,'Pending',GETDATE(),@CreatedBy,1,0 from @DataTable_Asset
set @msg='Asset received against Receipt No : Receipt/'+Convert(varchar(10),@ReceiveId)
end
begin
declare @ReceiveSum decimal(18,2)
declare @PurchaseSum decimal(18,2)
update Uams.AMS_InvoiceMaster set Status='Receive' where Invoice_ID=@InvoiceId and Purchase_Order_ID=@PurchaseOrderId
set @ReceiveSum=(select sum(Quantity) from Uams.AMS_ReceiveAssetDetails where Receive_ID=@ReceiveId)
set @PurchaseSum=(select sum(Quantity) from Uams.AMS_PurchaseItem where Purchase_Order_ID=@PurchaseOrderId)
if(@ReceiveSum=@PurchaseSum)
begin
update Uams.AMS_ReceiveAssetMaster set Status='FullyReceive' where Receive_ID=@ReceiveId
update Uams.AMS_PurchaseOrder set Status='FullyReceive' where Purchase_Order_ID=@PurchaseOrderId
end
else
begin
update Uams.AMS_ReceiveAssetMaster set Status='PartialReceive' where Receive_ID=@ReceiveId
update Uams.AMS_PurchaseOrder set Status='PartialReceive' where Purchase_Order_ID=@PurchaseOrderId
end
end
end
else
begin
set @ReceiptNo='No Receipt'
set @msg='Asset already received'
return
end
--Commit
--end Try
--Begin Catch
--Rollback
--End Catch
End
No comments:
Post a Comment