USE [AIRTRAK]
GO

/****** Object:  StoredProcedure [dbo].[AddShipmentAPARCost]    Script Date: 2/5/2024 9:50:01 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




/****** Object:  StoredProcedure [dbo].[AddShipmentAPARCost]    Script Date: 8/30/2017 12:38:47 PM ******/
ALTER           PROCEDURE [dbo].[AddShipmentAPARCost]
	@OrderNo bigint,
	@Vendor varchar (8),
	@VendorNo int,
	@ServiceId char (2),
	@Override char (1),
	@Quantity numeric (18, 3),
	@Rate numeric (18, 4),
	@Cost numeric (18, 2),
	@Extra numeric (18, 2),
	@Tax numeric (18, 2),
	@TotalCost numeric (18, 2),
	@RefNo varchar(35),
	@AutoCreate char(1),
	@VendorRate numeric (18, 4),
	@VendorCost numeric (18, 4),
	@APARType varchar(20),
	@FK_HandlingStation varchar(3),
	@UpdatedBy varchar(10),
	@Complete varchar(8),
	@AutoRateMin varchar(1),
	@EDI204Capable char(1),
	@ConsolNo int,
	--@STIServiceLevel char(1),
	@CostSeqNo int OUTPUT
AS
DECLARE 
	@SeqNo int,
	@RowCount int
SELECT @RowCount = Count(*), @SeqNo = Max(SeqNo) FROM tbl_ShipmentAPAR WHERE FK_OrderNo = @OrderNo AND (Consolidation <> 'Y' OR Consolidation IS NULL)
IF @RowCount = 0 
	SET @SeqNo = 1
ELSE
	SET @SeqNo = @SeqNo + 1

IF @SeqNo >= 9000
BEGIN
	--SET @SeqNo=1
	SELECT @SeqNo = Max(SeqNo) FROM tbl_ShipmentAPAR WHERE FK_OrderNo = @OrderNo AND SeqNo < 9000
	SET @SeqNo = @SeqNo + 1
END

IF @RefNo = ''
BEGIN
	DECLARE @DefaultHAWB AS char(1)
	SELECT @DefaultHAWB = DefaultHAWB FROM tbl_VendorServices WHERE PK_ServiceId = @ServiceId
	IF @DefaultHAWB = 'Y'
		SELECT @RefNo = Housebill FROM tbl_ShipmentHeader WHERE PK_OrderNo = @OrderNo
END

DECLARE @RemitNo int
DECLARE @CurrencyId char(3)
SELECT @RemitNo = RemitVendorNo, @CurrencyId = isnull(FK_CurrencyId,'') FROM tbl_Vendors WHERE VendorId = @Vendor
If @RemitNo=0 OR @RemitNo IS NULL
	SET @RemitNo=@VendorNo

DECLARE @ForeignCost decimal(18,2)
DECLARE @ForeignTotal decimal(18,2)
DECLARE @ForeignExtra decimal(18,2)
DECLARE @AsOfDate datetime
DECLARE @ConversionFactor decimal(18,8)

DECLARE @CompleteDate datetime
SET @CompleteDate = '1/1/1900'
if @complete='Y' SET @CompleteDate=GETDATE()

SET @AsOfDate = '1/1/1900'
SET @ConversionFactor = 0
SET @ForeignCost = 0
SET @ForeignTotal = 0
SET @ForeignExtra = 0

DECLARE @CurrencyConversionEnabled char(1)
SELECT @CurrencyConversionEnabled = ControlValue FROM tbl_Control WHERE PK_ControlNo = 952
IF @CurrencyConversionEnabled = 'Y'
	BEGIN
		DECLARE @BaseCurrencyId char(1)
		SELECT @BaseCurrencyId = FK_BaseCurrencyId FROM tbl_Companies
		IF @CurrencyId <> '' AND @CurrencyId <> @BaseCurrencyId
			BEGIN
				SELECT TOP 1 @ConversionFactor = isnull(ConversionFactor,0), @AsOfDate = isnull(AsOfDate,'1/1/1900') FROM tbl_CurrencyConversion WHERE FK_CurrencyId=@CurrencyId AND AsOfDate <= GETDATE() ORDER BY AsOfDate DESC
				IF @ConversionFactor > 0
					BEGIN
						SET @ForeignCost = @Cost
						SET @ForeignTotal = @TotalCost
						SET @ForeignExtra = @Extra
						SET @Cost = ROUND(@Cost * @ConversionFactor,2)
						SET @TotalCost = ROUND(@TotalCost * @ConversionFactor,2)
						SET @Extra = ROUND(@Extra * @ConversionFactor,2)
					END
			END
	END

BEGIN TRANSACTION
INSERT INTO tbl_ShipmentAPAR
(
	SeqNo,
	FK_OrderNo,
	FK_VendorId,
	FK_CustNo,
	FK_ServiceId,
	[Override],
	Quantity,
	Rate,
	Cost,
	Extra,
	Tax,
	Total,
	APARCode,
	RefNo,
	AutoCreate,
	APARType,
	Finalize,
	ReadyForInvoice,
	InvPrinted,
	Consolidation,
	ConsolNo,
	FK_HandlingStation,
	RemitVendorNo,
	UpdatedBy,
	UpdatedOn,
	CreateDateTime,
	Complete,
	AutoRateMinimum,
	EDI204Capable,
	Currency,
	CurrencyAsOfDate,
	CurrencyConversionFactor,
	CurrencyConversionFactorOverride,
	ForeignCurrencyCost,
	ForeignCurrencyTotal,
	ForeignCurrencyExtra
) 
VALUES
(
	@SeqNo,
	@OrderNo,
	@Vendor,
	@RemitNo,
	@ServiceId,
	@Override,
	@Quantity,
	@Rate,
	@Cost,
	@Extra,
	@Tax,
	@TotalCost,
	'V',
	@RefNo,
	@AutoCreate,
	@APARType,
	'N',
	'N',
	'N',
	'N',
	@ConsolNo,
	@FK_HandlingStation,
	@RemitNo,
	@UpdatedBy,
	GetDate(),
	GetDate(),
	@Complete,
	@AutoRateMin,
	@EDI204Capable,
	@CurrencyId,
	@AsOfDate,
	@ConversionFactor,
	'',
	@ForeignCost,
	@ForeignTotal,
	@ForeignExtra
)

INSERT INTO tbl_shipmentAPARAux
(
	APARSeqNo,
	FK_OrderNo,
	VendorCompleteDate,
	Consolno,
	APARCode
) 
VALUES
(
	@SeqNo,
	@OrderNo,
	@CompleteDate,
	'0',
	'V'
)


SET @CostSeqNo = @SeqNo
IF @@ERROR <> 0
	BEGIN
		ROLLBACK
	END
ELSE
	BEGIN
		COMMIT
	END
RETURN











GO